:: Visual Foxpro, Foxpro for DOS
• Войти
Страницы:  1 2
Заполнение переменной при формировании формулы Excel
descent52

Сообщений: 675
Откуда: Саров
Дата регистрации: 26.07.2007
descent52
15.12.10 10:34:01
Добрый день.
Заполняю лист Excel объёмами и формулами. Формулы загоняю в переменные:
frml1=frml1+"G"+Alltrim(Str(i))+"*H"+Alltrim(Str(i))
Всё работает, летает и шуршит.
И в конце листа надо вставить итоговую формулу по разным ячейкам нарастающим итогом:
frml_1=frml_1+"H"+Alltrim(Str(i))+"+"
...
frml_1=frml_1+"H"+Alltrim(Str(i))

Если строк заполнения не очень много, то итоговая формула в итоге имеет следующий вид на листе экселя
"=H6+H7+H8+H9+H10+H11+H12+H13+H16+H19+H22+H25+H28+H29+H32+H33+H34+H35+H36+H39+H42+H45+H46+H47+H48+H51+H52 "
А вот если строк за 700 , то переменная сваливается- OLE error code 0x8007000e: Недостаточно памяти для завершения операции
Пытаюсь обойти это при помощи TEXTMERGE, но пока не удачно.
Подскажите, как обойти этот момент
Ratings: 0 negative/0 positive
Re: Заполнение переменной при формировании формулы Excel
ssa

Сообщений: 13008
Откуда: Москва
Дата регистрации: 23.03.2005
ssa
15.12.10 10:38:10
descent52
Добрый день.
Заполняю лист Excel объёмами и формулами. Формулы загоняю в переменные:
frml1=frml1+"G"+Alltrim(Str(i))+"*H"+Alltrim(Str(i))
Всё работает, летает и шуршит.
И в конце листа надо вставить итоговую формулу по разным ячейкам нарастающим итогом:
frml_1=frml_1+"H"+Alltrim(Str(i))+"+"
...
frml_1=frml_1+"H"+Alltrim(Str(i))

Если строк заполнения не очень много, то итоговая формула в итоге имеет следующий вид на листе экселя
"=H6+H7+H8+H9+H10+H11+H12+H13+H16+H19+H22+H25+H28+H29+H32+H33+H34+H35+H36+H39+H42+H45+H46+H47+H48+H51+H52 "
А вот если строк за 700 , то переменная сваливается- OLE error code 0x8007000e: Недостаточно памяти для завершения операции
Пытаюсь обойти это при помощи TEXTMERGE, но пока не удачно.
Подскажите, как обойти этот момент
Ограничение на длину строки для Excel Textmerge не решит. Как, впрочем, и другие танцы со стороны фокса. Мне вот непонятно, а зачем повторно суммировать все, если можно для итогов суммировать только подитоги?

------------------
Лень - это неосознанная мудрость.
Ratings: 0 negative/0 positive
Re: Заполнение переменной при формировании формулы Excel
Penner

Сообщений: 4102
Откуда: Muenster
Дата регистрации: 26.04.2002
Penner
15.12.10 10:38:17
=SUMME(H6:H16)+SUMME(H32:H35)...


------------------
Никогда не откладывайте на завтра, то, о чем можно забыть навсегда




Исправлено 1 раз(а). Последнее : Penner, 15.12.10 10:40
Ratings: 0 negative/0 positive
Re: Заполнение переменной при формировании формулы Excel
descent52

Сообщений: 675
Откуда: Саров
Дата регистрации: 26.07.2007
descent52
15.12.10 10:41:02
Если считать только подитоги, то и подитогов выше крыши. Всё равно вываливается ошибка. Слишком много подитогов и итогов.
Значит танцы с бубном мне не помогут?
Ratings: 0 negative/0 positive
Re: Заполнение переменной при формировании формулы Excel
Penner

Сообщений: 4102
Откуда: Muenster
Дата регистрации: 26.04.2002
Penner
15.12.10 10:43:13
может проще тогда посчитать сумму в фоксе?


------------------
Никогда не откладывайте на завтра, то, о чем можно забыть навсегда
Ratings: 0 negative/0 positive
Re: Заполнение переменной при формировании формулы Excel
ssa

Сообщений: 13008
Откуда: Москва
Дата регистрации: 23.03.2005
ssa
15.12.10 10:46:22
descent52
Если считать только подитоги, то и подитогов выше крыши. Всё равно вываливается ошибка. Слишком много подитогов и итогов.
А подитоги для подитогов? Кроме двух уровней ничего представить невозможно?
Цитата:
Значит танцы с бубном мне не помогут?
Увы.

------------------
Лень - это неосознанная мудрость.
Ratings: 0 negative/0 positive
Re: Заполнение переменной при формировании формулы Excel
pioner-v

Сообщений: 1656
Дата регистрации: 01.05.2010
pioner-v
15.12.10 10:46:37
descent52
Добрый день.
Заполняю лист Excel объёмами и формулами. Формулы загоняю в переменные:
frml1=frml1+"G"+Alltrim(Str(i))+"*H"+Alltrim(Str(i))
Всё работает, летает и шуршит.
И в конце листа надо вставить итоговую формулу по разным ячейкам нарастающим итогом:
frml_1=frml_1+"H"+Alltrim(Str(i))+"+"
...
frml_1=frml_1+"H"+Alltrim(Str(i))

Если строк заполнения не очень много, то итоговая формула в итоге имеет следующий вид на листе экселя
"=H6+H7+H8+H9+H10+H11+H12+H13+H16+H19+H22+H25+H28+H29+H32+H33+H34+H35+H36+H39+H42+H45+H46+H47+H48+H51+H52 "
А вот если строк за 700 , то переменная сваливается- OLE error code 0x8007000e: Недостаточно памяти для завершения операции
Пытаюсь обойти это при помощи TEXTMERGE, но пока не удачно.
Подскажите, как обойти этот момент

А что делаете-то? Пустографку документа EXCEL для последующего заполнения его "вручную"?
Ratings: 0 negative/0 positive
Re: Заполнение переменной при формировании формулы Excel
descent52

Сообщений: 675
Откуда: Саров
Дата регистрации: 26.07.2007
descent52
15.12.10 10:51:07
Да я бы рад вместо формулы поставить итоговую цифру из фокса. Но экономистам нужна формула.

Вроде как осенило.
Таблица из экселя представляет собой вывод объёмов по предприятиям. Для каждого предприятия в ячейке F есть строка-Итого по предприятию.
И вот такую формулу можно втюхнуть
=СУММЕСЛИ(F4:G905;"=Итого по предприятию";H4:H905)
Ratings: 0 negative/0 positive
Re: Заполнение переменной при формировании формулы Excel
pioner-v

Сообщений: 1656
Дата регистрации: 01.05.2010
pioner-v
15.12.10 11:08:21
descent52
Да я бы рад вместо формулы поставить итоговую цифру из фокса. Но экономистам нужна формула.
...

Нельзя пользователю-экономисту давать право корректировать выходной документ.
Будет "бардак" ...
Ratings: 0 negative/0 positive
Re: Заполнение переменной при формировании формулы Excel
descent52

Сообщений: 675
Откуда: Саров
Дата регистрации: 26.07.2007
descent52
15.12.10 13:33:58
pioner-v
Нельзя пользователю-экономисту давать право корректировать выходной документ.
Будет "бардак" ...
Согласен. Предлагаю увольнять всех экономистов в конце года, ибо житья от них нет.

Подскажите, что нужно сделать в фоксе, чтобы при вставке формулы SUM или SUMIF в ячейку Экселя из фокса, формула автоматически рассчитывалась при загрузке Экселя?
Если в ячейке стоит простенькая формула, по типу '=G61*H61' , то эксель ее рассчитывает. А вот как только ставлю формулу '=SUM(H3:H5)' или '=СУММЕСЛИ(F6:G630;"Итого по потребителю";H6:H630)' то эксель на экране вместо результата выдает #ИМЯ?
И чтобы результат посчитался, надо встать на эту формулу и нажать 'интер'.
Ratings: 0 negative/0 positive
Re: Заполнение переменной при формировании формулы Excel
ssa

Сообщений: 13008
Откуда: Москва
Дата регистрации: 23.03.2005
ssa
15.12.10 13:48:42
descent52
Если в ячейке стоит простенькая формула, по типу '=G61*H61' , то эксель ее рассчитывает. А вот как только ставлю формулу '=SUM(H3:H5)' или '=СУММЕСЛИ(F6:G630;"Итого по потребителю";H6:H630)' то эксель на экране вместо результата выдает #ИМЯ?
Обычно это говорит об ошибке в формуле. Вы этого не знали? Или думаете, что excel для разнооборазия просто врет?
Цитата:
И чтобы результат посчитался, надо встать на эту формулу и нажать 'интер'.
Вот и ищите разницу между тем, что вставляете и тем, что таки потом оказывается в ячейке.

------------------
Лень - это неосознанная мудрость.
Ratings: 0 negative/0 positive
Re: Заполнение переменной при формировании формулы Excel
descent52

Сообщений: 675
Откуда: Саров
Дата регистрации: 26.07.2007
descent52
15.12.10 13:58:26
[quote ssa]Обычно это говорит об ошибке в формуле. Вы этого не знали? Или думаете, что excel для разнооборазия просто врет?[quote]
В том то и дело что нет никакой ошибки. В ячейке стоит формула
=СУММЕСЛИ(F6:G63;"Итого по потребителю";H6:H63)
но отображается как #ИМЯ?
Если я нажимаю мышкой на ячейку два раза и нажимаю интер, то формула рассчитывается и выдает правильное значение.
Ratings: 0 negative/0 positive
Re: Заполнение переменной при формировании формулы Excel
ssa

Сообщений: 13008
Откуда: Москва
Дата регистрации: 23.03.2005
ssa
15.12.10 14:03:08
descent52
но отображается как #ИМЯ?
Это сообщение об ошибке!
Цитата:
Если я нажимаю мышкой на ячейку два раза и нажимаю интер, то формула рассчитывается и выдает правильное значение.
Вы думаете, что повторение несколько раз одного утверждения изменит истинность утверждения? Или изменит поведение Excel. Или вы просто не читали, что я написал?

------------------
Лень - это неосознанная мудрость.
Ratings: 0 negative/0 positive
Re: Заполнение переменной при формировании формулы Excel
AleksM

Сообщений: 17881
Дата регистрации: 11.11.2003
AleksM
15.12.10 16:28:30
Приведи код которым прописывается формула СУММАЕСЛИ в ячейку.


------------------
Лучше переесть, чем недоспать.
Не спеши, а то успеешь.
Ratings: 0 negative/0 positive
Re: Заполнение переменной при формировании формулы Excel
Igor Korolyov
Автор

Сообщений: 34580
Дата регистрации: 28.05.2002
Igor Korolyov
15.12.10 16:36:00
1) Формулу присваивать не свойству .Value, а свойству .Formula - в английской транскрипции, или в .FormulaLocal в локализованной (хотя это извращение - мало ли какой "русский язык" экселя у пользователя окажется).
2) Формулы удобнее писать в нотации R1C1 а не A1 - она достаточно простая для понимания и очень удобна для "относительной" адресации ячеек - плюс не нужно городить мега-функции по переводу номера колонки в соответствующие экселевские "буквы". Соответственно использовать свойство .FormulaR1C1
3) Изучить функцию экселя Промежуточные.Итоги (SUBTOTAL) и одноименный инструмент экселя (метод Subtotal объекта Range при автоматизации) - он позволяет избавиться от подобных идиотских километровых формул. Формула позволяет просто пропускать при суммировании ячейки, где находится не число, а такая же формула. А инструмент Subtotal позволяет автоматически расставить соответствующие формулы (добавив при этом строки для промежуточных и окончательных итогов), и создать "структуру" документа - для удобного "схлопывания/разворачивания" простыни данных по различным уровням группировки. На форуме обсуждалось, как использовать всё это из фокса.


------------------
WBR, Igor
Ratings: 0 negative/0 positive
Re: Заполнение переменной при формировании формулы Excel
descent52

Сообщений: 675
Откуда: Саров
Дата регистрации: 26.07.2007
descent52
15.12.10 16:38:23
Итак пробую
tmp1='"'
tmp="SUMIF(F6:G"+ALLTRIM(STR(i))+";"+tmp1+"Итого по потребителю"+tmp1+";H6:H"+ALLTRIM(STR(i))+")"
.Cells(i+1,8).Formula="="+tmp

итак пробую
tmp1='"'
.Cells(i+1,8).Formula="=SUMIF(F6:G"+ALLTRIM(STR(i))+";"+tmp1+"Итого по потребителю"+tmp1+";H6:H"+ALLTRIM(STR(i))+")"

всё равно выдает #ИМЯ?
Если я нажимаю мышкой на ячейку и два раза и кликаю, то формула рассчитывается и выдает правильное значение.

В идеале хочется формулу =СУММЕСЛИ(F6:G447;"Итого по потребителю";H4:H447)
Ratings: 0 negative/0 positive
Re: Заполнение переменной при формировании формулы Excel
AleksM

Сообщений: 17881
Дата регистрации: 11.11.2003
AleksM
15.12.10 16:47:17
Проверь тест: 


LOCAL loXls as Excel.Application, loBook as Excel.Workbook, loSheet as Excel.Worksheet



loXls = CREATEOBJECT("Excel.Application")

loXls.Visible = .T.

loBook = loXls.Workbooks.Add()

loSheet = loBook.Worksheets(1)



FOR i=1 TO 10

    loSheet.Cells(i,1)=TRANSFORM(i)

    loSheet.Cells(i,2)=i

NEXT i



loSheet.Cells(i,2).Formula='=SUMIF(A1:A'+TRANSFORM(i-1)+',"2",B1:B'+TRANSFORM(i-1)+')'


------------------
Лучше переесть, чем недоспать.
Не спеши, а то успеешь.
Ratings: 0 negative/0 positive
Re: Заполнение переменной при формировании формулы Excel
Igor Korolyov
Автор

Сообщений: 34580
Дата регистрации: 28.05.2002
Igor Korolyov
15.12.10 17:39:45
#DEFINE xlSum -4157

LOCAL ln1, ln2, ln3, lcUser, lcGroup, loExcel, loWB, loSheet, lnRows, laTotalList[2]

PRIVATE paData

CREATE CURSOR tmp (group C(20), user C(20), date d, quantity B(2))

RAND(1)

FOR ln1 = 1 TO 4

 lcGroup = "Группа № " + TRANSFORM(m.ln1)

 FOR ln2 = 1 TO 3

  lcUser = GETWORDNUM("Иванов,Петров,Сидоров", m.ln2, ",")

  FOR ln3 = 1 TO RAND()*15

   INSERT INTO tmp (group, user, date, quantity) VALUES (m.lcGroup, m.lcUser, DATE() + m.ln3, ROUND(RAND()*1000, 2))

  ENDFOR

 ENDFOR

ENDFOR



loExcel = CREATEOBJECT("Excel.Application")

loWB = loExcel.Workbooks.Add()

loSheet = loWB.Sheets(1)

DIMENSION paData[5]

paData[1] = "Группа"

paData[2] = "Потребитель"

paData[3] = "Дата"

paData[4] = "Количество"

paData[5] = "Сумма"

WITH loSheet

 .Range(.Cells(1,1), .Cells(1, 5)).Value = PassArray("paData")

 SELECT group, user, date, quantity ;

  FROM tmp ;

  ORDER BY group, user, date ;

  INTO ARRAY paData

 lnRows = _TALLY

 laTotalList[1] = 4

 laTotalList[2] = 5

 .Range(.Cells(2, 1), .Cells(m.lnRows + 1, 4)).Value = PassArray("paData")

 .Range(.Cells(2, 5), .Cells(m.lnRows + 1, 5)).FormulaR1C1 = "=RC[-1]*100"

 WITH .Range(.Cells(1, 1), .Cells(m.lnRows + 1, 5))

  .Subtotal(1, xlSum, @laTotalList, .T.)

  .Subtotal(2, xlSum, @laTotalList, .F.)

 ENDWITH

 .Range(.Cells(1, 1), .Cells(1, 5)).EntireColumn.AutoFit()

ENDWITH

loExcel.Visible = .T.



PROCEDURE PassArray(tcArrayName)

RETURN @&tcArrayName

ENDPROC

Со сложными (многоуровневыми) итогами получается не очень красиво - эксель тупит на "общих" итоговых строках (переставляет их местами), но такой отчёт уже проще довести до ума, чем всё поячеечно забивать, даже если удастся обойтись без "километровых" формул.


------------------
WBR, Igor
Ratings: 0 negative/0 positive
Re: Заполнение переменной при формировании формулы Excel
AleksM

Сообщений: 17881
Дата регистрации: 11.11.2003
AleksM
15.12.10 17:52:26
Промежуточные итоги тянут за собой наличие столбцов по изменениям данных в которых они формируются, а это не всегда допустимо в отчете. Их (эти столбцы) нужно либо скрывать, либо копировать результат в другое место как значения, ну и т.д. и т.п.
Хотя не спорю инструмен полезный и нужный.


------------------
Лучше переесть, чем недоспать.
Не спеши, а то успеешь.




Исправлено 1 раз(а). Последнее : AleksM, 15.12.10 17:53
Ratings: 0 negative/0 positive
Re: Заполнение переменной при формировании формулы Excel
Igor Korolyov
Автор

Сообщений: 34580
Дата регистрации: 28.05.2002
Igor Korolyov
15.12.10 18:18:36
Инструмент автоматического построения промежуточных итогов - да, нуждается в таких полях. Сама же функция рабочего листа SUBTOTAL(), которой этот инструмент широко пользуется, совершенно нет. Ей можно подсовывать произвольный диапазон, от этого её работа не изменится. Просто достаточно утомительно самому вставлять строки для промежуточных итогов и прописывать формулы в них параллельно подсчитывая размеры "поддиапазонов суммирования". Хотя это вполне возможно, но уж от написания примера избавь меня


------------------
WBR, Igor
Ratings: 0 negative/0 positive
Страницы:  1 2


:: Список Форумов•Список тем•Поиск•Войти
Извините, только зарегистрированные пользователи могут оставлять сообщения в этом форуме.

On-line: 55 (Гостей: 55)
© 2000-2024 Fox Club 
Яндекс.Метрика