Новости:

Из правил форума: Тема должна отражать суть вопроса, топики типа "help please" будут удаляться!

Главное меню

Быстродействие вычислений в excel

Автор silentland, 13.08.2011, 20:13

« назад - далее »

silentland

Скопировал в ячейку такую формулу:

{=G15-СУММ((ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(B4;;;СТРОКА(B4:B1048576)-СТРОКА(B3)))<(СУММ(B4:B1048576)-СУММ(A4:A1048576)))*B4:B1048576*(C4:C1048576="X"))+СУММ((ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(B4;;;СТРОКА(B4:B1048576)-СТРОКА(B3)))>(СУММ(B4:B1048576)-СУММ(A4:A1048576)))*((ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(B4;;;СТРОКА(B4:B1048576)-СТРОКА(B3)))-A4:A1048576)<(СУММ(B4:B1048576)-СУММ(A4:A1048576)))*((СУММ(B4:B1048576)-СУММ(A4:A1048576))-ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(B4;;;СТРОКА(A4:A1048576)-СТРОКА(B3)))+A4:A1048576)*(C4:C1048576="X"))}

Значение вычислялось секунд 5. Это из-за того, что задал диапазон А4:А1048576. Естественно, Эксель проверял каждую ячейку. Как сделать, чтобы пустые ячейки не просчитывались?


Теоретический, вопрос:
Допустим три колонки листа заняты значениями (пустые ячейки тоже могут встречаться):

1200  300  гвозди
4500   90  гвозди

230   50  шифер
...

Все расчеты делаются на основе этих значений. В список периодически добавляются новые строчки.
Как писать формулы без ущерба для быстродействия, какими принципами руководствоваться?

Serge 007

Цитата: silentland от 13.08.2011, 20:13
Как писать формулы без ущерба для быстродействия, какими принципами руководствоваться?
silentland, формулы пишутся по уже имеющимся данным. Если данные расположены не так как ожидает этого Excel, то и приходится писать подобные формулы :)

Принципов расположения данных всего два:
1. 80/20
2. См. первый принцип ;)

Что такое 80/20?
ЦитироватьВозможно, самое важное правило при разработке электронной таблицы —  учитывать долгосрочные перспективы и не предполагать, что в нее никогда не понадобится добавлять данные или формулы, так как шансов, что вам это понадобится, все же больше. Помня об этом, вы должны потратить приблизительно 80 % времени на планирование электронной таблицы и приблизительно 20 % времени на ее реализацию. Хотя на первый взгляд это может показаться чрезвычайно  неэффективным, уверяем вас, что долгосрочные выгоды перевесят краткосрочные  мучения, а планирование станет намного проще после того, как вы некоторое время потренируетесь. Помните, что электронныетаблицы предназначены для того, чтобы пользователи могли легко получать правильную информацию, а не только для ее представления, причем представление это будет эффективным и правильным только один раз в жизни.
Рейна Холи, Девид Холи "Excel Hacks".

По Вашей формуле. Формулы массива сами по себе "тяжёлые", потому как пересчитывают ВЕСЬ указанный массив, независимо от того пустые в нём ячейки или нет. СМЕЩ - волатильная функция, которая заставляет пересчитываться формулы, в которые она вложена при ЛЮБОМ изменении на листе. Кроме того, задав диапазон в два целых столбца Вы заставляете Экс при каждом пересчёте работать с более чем двумя миллионами ячеек! 5 секунд - это ещё мало, наверное у Вас комп неплохой, а мой ноут такое просто не потянет...

По "Теоретическому вопросу". Excel - это программа для электронных таблиц, а не для баз данных. Если Ваша БД насчитывает сотни тысяч записей, то разумней пользоваться Access, он для этого предназначен.



Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

silentland

Цитата разумная, но руководствоваться ей следует лишь тогда когда точно известно ТЗ и ты профессионал в Экселе. Иначе планирование — пустая трата времени :-)

В том-то и дело, что БД не подразумевает так много записей, сотня тысяч — максимум; скорее всего и 10 000 не наберется, поэтому не хочется тратить ресурсы на пересчет всего диапазона.

А если формулы массивов и смещений заменить добавлением столбцов с промежуточными вычислениями — это будет быстрее? Яркий пример: таблица пользователя MCH в предыдущем посте: https://forum.msexcel.ru/microsoft_excel/vychislenie_summy_chisel_ne_prevyshayuschey_zadannoe_znacheniie-t6181.0.html;msg34285#msg34285, где одна и таже задача решается с доп. столбцом и формулой массива. Там что будет быстрее?

Компьютер, да, не слабый. 8 ГБ оперативки заполнились сразу же.

Serge 007

Цитата: silentland от 13.08.2011, 21:16
Цитата разумная, но руководствоваться ей следует лишь тогда когда точно известно ТЗ и ты профессионал в Экселе. Иначе планирование — пустая трата времени :-)
Нет. Это как раз для начинающих :)
Я понимаю, все должны набить синяки и шишки, но ведь можно учиться и на чужом опыте ;)

Цитата: silentland от 13.08.2011, 21:16
БД не подразумевает так много записей, сотня тысяч — максимум; скорее всего и 10 000 не наберется, поэтому не хочется тратить ресурсы на пересчет всего диапазона.
Тогда используйте сводную. По быстродействию ей равных нет, ни среди формул, ни в макросах.

Цитата: silentland от 13.08.2011, 21:16
А если формулы массивов и смещений заменить добавлением столбцов с промежуточными вычислениями — это будет быстрее?
Если в промежуточных вычислениях не используются "тяжёлые" формулы, то да.
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

silentland

#4
Сводная — мощная штука, но не для всего подходит.

Попробовал сейчас «проятнуть» простенькую формулу на 200 000 строк, так компьютер минут 10 вис :-) И потом этот файл стал столько же по времени открываться.
Понимаю, что условия и задачи могут быть самые разные, но примерно (по опыту) с каким количеством данных Эксель может работать более или менее вменяемо, 10 000, 50 000 строк?

И как запретить вычисление, если в ячейках пусто? Например, если в 100 000 ячеек должно быть вычисление типа СУММ(Аn;Bn*Cn) есть ли смысл делать проверку ЕСЛИ(ЕПУСТО(An);;СУММ(Аn;Bn*Cn))?

Или, например, если сделаю столбец с промежуточными вычислениями размером А1:А1000, но с задумкой увеличить его при надобности, есть ли смысл в других формулах использовать его величину как величину массивов?

Serge 007

Цитата: silentland от 13.08.2011, 22:14
Сводная — мощная штука, но не для всего подходит.
Естественно! Она подходит только для правильно структурированных данных, т.е если сначала подумал, а потом сделал, то сводная - самое оно :)
Если сначала сделал, а думать вообще не стал - то трёхэтажные формулы массива - единственное что поможет ;)

Цитата: silentland от 13.08.2011, 22:14
Понимаю, что условия и задачи могут быть самые разные, но примерно (по опыту) с каким количеством данных Эксель может работать более или менее вменяемо, 10 000, 50 000 строк?
Если не вдаваться в подробности, то одна две, максимум пять тысяч строк (в зависимости от количества столбцов).

Цитата: silentland от 13.08.2011, 22:14И как запретить вычисление, если в ячейках пусто? Например, если в 100 000 ячеек должно быть вычисление типа СУММ(Аn;Bn*Cn) есть ли смысл делать проверку ЕСЛИ(ЕПУСТО(An);;СУММ(Аn;Bn*Cn))?
Есть, да ещё какой! ЕСЛИ не даёт выполняться условию аргумента. Т.е. если Аn пусто, перемножения Bn*Cn не происходит!
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

silentland

Цитата: Serge 007 от 13.08.2011, 22:34
Естественно! Она подходит только для правильно структурированных данных, т.е если сначала подумал, а потом сделал, то сводная - самое оно

Это точно не моя ситуация. Пока пробую решать с помощью Экселя интеллектуальные задачи, например, формирование баланса на основе проводок. Понимаю, что не самый лучший инструмент, но для начала сойдет :-)

Цитата: Serge 007 от 13.08.2011, 22:34
Если не вдаваться в подробности, то одна две, максимум пять тысяч строк (в зависимости от количества столбцов).

Если рассматривать, например, бухгалтерские проводки, то 5000 записей едва ли хватит. Наверное, каждый год придется новую книгу заводить.


Serge 007

Цитата: silentland от 13.08.2011, 22:52
Пока пробую решать с помощью Экселя интеллектуальные задачи, например, формирование баланса на основе проводок. Понимаю, что не самый лучший инструмент, но для начала сойдет :-)
Почему же? Это как раз для Экса. Но повторюсь - данные надо структурировать правильно. Тогда и миллион строк не помеха.

Цитата: silentland от 13.08.2011, 22:52
Если рассматривать, например, бухгалтерские проводки, то 5000 записей едва ли хватит. Наверное, каждый год придется новую книгу заводить.
А это совсем плохое решение :(
Форумы ежедневно пестрят темами типа "Помогите собрать данные воедино с нескольких листов/книг".
Ни в коем случае не разбивайте БД!
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

silentland

#8
Цитата: Serge 007 от 13.08.2011, 23:08
Почему же? Это как раз для Экса. Но повторюсь - данные надо структурировать правильно. Тогда и миллион строк не помеха.
Хм... Например в одном посте решал задачу: https://forum.msexcel.ru/microsoft_excel/avtomaticheskoe_raspredelenie_pribyli_slozhno-t6176.0.html
Это как раз для баланса. Смысл в том, что у проводок, обозначающих займ, есть срок погашения, а в проводках обозначающих погашение займов не содержится сведений какой займ погашается, Ексель должен сам это определить и распихать долги в краткосрочные и долгосрочные строки баланса. По сути программа должна принять управленческое решение (пока логика в том, что первыми погашаются наиболее ранние займы).

Цитата: silentland от 13.08.2011, 22:52
Ни в коем случае не разбивайте БД!
Как же тогда быть? Разве так сложно вывести данные из нескольких книг?

Добавка:
А как проверять ячейки на пустоту в формулах массивов? ЕСЛИ(ЕПУСТО(An);;СУММ(Аn;Bn*Cn)) там не прокатывает

Serge 007

Цитата: silentland от 13.08.2011, 23:25
Это как раз для баланса.
Повторяю: при правильной структуре данных...
В той теме у Вас беспредел в ячейках :)

Цитата: silentland от 13.08.2011, 23:25Разве так сложно вывести данные из нескольких книг?
А Вы попробуйте ;)
Без макросов. И учитывайте что большинство формул с закрытыми книгами не работает!


Цитата: silentland от 13.08.2011, 23:25
А как проверять ячейки на пустоту в формулах массивов. ЕСЛИ(ЕПУСТО(An);;СУММ(Аn;Bn*Cn)) там не прокатывает
Прокатывает :)
Только не An надо писать, а массив.
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

silentland

#10
Цитата: Serge 007 от 13.08.2011, 23:34
Повторяю: при правильной структуре данных...
В той теме у Вас беспредел в ячейках :)
Да, беспредел. И от него никуда не деться. Дело в том, что данные вбиваются в непосредственно в Эксель в удобном пользователю виде.
Например, так:


Структурировать их — это отдельная задача. Как это обычно делается?

Цитата: Serge 007 от 13.08.2011, 23:34
А Вы попробуйте ;)
Без макросов. И учитывайте что большинство формул с закрытыми книгами не работает!
Закрытые книги? Это что такое? Это если они не запущены на компе?

P.S. Не работает :-(
{СУММ(B4:B140000)} выдает сумму, а
{=ЕСЛИ(ЕПУСТО(B4:B140000);;СУММ(B4:B140000))} выдает 0

Serge 007

Цитата: silentland от 13.08.2011, 23:45
Да, беспредел. И от него никуда не деться. Дело в том, что данные вбиваются в непосредственно в Эксель в удобном пользователю виде.
Структурировать их — это отдельная задача. Как это обычно делается?
Точно :-) Ключевые слова здесь:" в удобном пользователю виде".
А должно быть: "в удобном Excel виде" ;)
Работает-то с данными не пользователь, а Excel, пользователь только результат видит. И что бы удобно было всем, располагаем данные в любой из нормальных форм, см здесь и здесь, а пользователю делаем красивую ИТОГОВУЮ табличку (но никак не ИСХОДНУЮ!!!)

Закрытые книги - это закрытые книги, т.е. те, которые не открыты.
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

silentland

Помню-помню, на экзамене как раз вопрос про нормализацию попался :-) Попробую нормализовать, правда, пока плохо представляю как это в Экселе сделать.

Кстати, проверка в массиве не работает:

{СУММ(B4:B140000)} выдает сумму, а
{=ЕСЛИ(ЕПУСТО(B4:B140000);;СУММ(B4:B140000))} выдает 0

Serge 007

Цитата: silentland от 14.08.2011, 00:07
{СУММ(B4:B140000)} выдает сумму, а
{=ЕСЛИ(ЕПУСТО(B4:B140000);;СУММ(B4:B140000))} выдает 0
См вложение
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

Serge 007

Я бы даже использовал такую формулу:
=СУММПРОИЗВ((A1:A3<>"")*A1:A3)
Её не надо вводить тремя пальцами :)
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390