Страницы: 1 2 След.
RSS
Теоретический вопрос что лучше большие формулы или вспомогательные ячейки?
 
уважаемые форумчане подскажите как с точки зрения скорости пересчета книги эксель и "веса" файла лучше поступать:
- использовать в одной ячейке сложную формулу которая сама строит массивы высчитывает средние величины и ограничения и совершает иные действия с этими массивами,
- или же лучше упростить формулу введя вспомогательный столбец с промежуточными расчетами на который впоследствии будут ссылаться формулы?

P.s.
а что скажете про использование имен в формулах? тормозят ли они вычисления? или может утяжеляют файл?
 
Вопрос неоднозначный. Довольно часто разбиение одной мега-формулы на несколько промежуточных ускоряют вычисления и облегчают файл. Но не всегда. В практической работе чураться доп. столбцов не стоит.

Тоже самое с именами. Особенно, если в именах абсолютные ссылки; если ссылки в именах относительные, то не так все просто...
Изменено: Михаил С. - 24.12.2013 21:00:11
 
Вспомогательные ячейки
Век живи, век учись - два века проживешь!
 
Цитата
с точки зрения скорости пересчета книги эксель и "веса" файла лучше
использовать макросы ))
 
Цитата
Blood81 пишет: - использовать в одной ячейке сложную формулу которая сама строит массивы высчитывает средние величины и ограничения и совершает иные действия с этими массивами,
Разбивал 3-4 формулы, которые считают одну и ту же сцепку (&  ;)  несколько раз - вынес эту сцепку в отдельный столбец - на скорости пересчета никак не отразилось (как висел файл пару минут при больших объемах, так и висит - считал по таймеру примерно то же время), а таблица стала более громоздкой.
 
modcom, считать один раз или одно и то же несколько раз - одинаково? Вы не ощутили изменений - да, может быть.
Похоже, проблема не только в той части, которую Вы изменили.
 
Могу выразить мысль примерными словами Джона Уокенбаха, мегаформулы обычно ускоряют процесс пересчетов (незначительно, но на очень больших объемах становится заметнее), и занимают меньше места, нежели дополнительные столбцы/строки с вспомогательными формулами, но у мегаформул один большой недостаток: если вдруг понадобится через какой-то промежуток времени ее дополнить/поправить, то даже для автора, ее создавшего, это может оказаться очень трудной задачей. И в принципе я согласен с этими словами, скорость пересчетов проверить не мог, нет у меня таких больших объемов информации на работе, а вот поправить формулу (когда возникает новый нюанс и его нужно дополнить в формулу) становится очень нелегким делом, приходится вспоминать, что для чего и главное зачем отдельными частями мегаформулы считается/проверяется/и т.д. У меня бывают формулы длиной более 300 знаков, с вложенными функциями по 10-20 штук. )
 
Цитата
мегаформулы обычно ускоряют процесс пересчетов
Не всегда. Если одна формула обсчитывает большой массив - возможно, да.
Пример.
Очень частая задача на форумах: извлечь в отдельный столбец данные из большой таблицы по какому-либо критерию (уникальные, длина строки, только блондинок...).
а) формула массива. Если значение диапазона соответствует критерию, записываем в массив номер строки. Далее по номеру строки извлекаем данные в столбец. Что имеем? Одинаковые вычисления во всех ячейках, ведь каждая формула вычисляет массив нужных номеров строк. Сложная формула. Но нет лишних столбцов (и почему у некоторых они вызывают страх?)
б) доп.столбец с пометкой нужных строк. Извлечение с помощью простой ВПР. Что имеем? Легкость вычислений, очень простые формулы, визуальное определение нужных строк. В наличии доп. столбец (можно скрыть). Вспомогательный столбец может быть использован другими формулами.
в) формула массива в диапазон, а не отдельно-поячеечно. Теоретически - одна формула, вычисления только раз, но результат разносится по ячейкам диапазона. На практике не проверял. Что имеем? Что-то среднее между предыдущими двумя.

Для разминки извилин хитро закрученные формулы - самое оно. Но для практического применения мне больше по душе вариант с доп. столбцом.
 
Мне представляется, что большее количество задействованных ячеек (доп.столбцы и пр.) увеличивают, во-первых, цепочку пересчета книги (условно говоря, "грязными" становится больше ячеек), а во-вторых, увеличивает размер файла (и на больших файлах может быть проблемой, если есть ограничения ресурсов) - на каждую задействованную ячейку идет также куча доп.информации о форматах и пр.

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

А вообще всё зависит от конкретной ситуации. Формула массива в каждой ячейке длинного списка, обрабатывающая весь список сразу, может так тормознуть, что мало не покажется.
F1 творит чудеса
 
Лучше написать функцию на VBA и в нее впихнуть все, что нужно
 
Не всегда в VBA "щасстье" :)
 
Цитата
VistaSV30 написал: Лучше написать функцию на VBA
Для себя - возможно будет проще. Быстрее? В большинстве стандартных задач нет. Универсальнее? Ни в коем случае. Да, VBA может значительно упростить многие задачи и с его помощью можно сделать даже то, что без него в Excel придется делать либо руками, либо очень долго.
Но ведь для собственных целей и так можно что угодно использовать - никто не ограничивает. А вот для распространения среди других пользователей - не всегда макросы лучше встроенных средств. Надо не забывать и о том, что по умолчанию макросы отключены. И рассказать каждому как их разрешить дело не такое простое. Многие просто могут плюнуть на это.
Поэтому всегда надо исходить из ситуации. И однозначного ответа на то, что лучше, под любые задачи не будет никогда. В каких-то случаях выгоднее использовать макросы, в каких-то мегаформулы, в каких-то вычисления в доп.столбцах. Чаще применяются комбинации всего перечисленного.
При этом не поверите - не всегда скорость на первом месте. Порой люди предпочтут удобство и эстетическую составляющую, чем выигрыш в 2 минуты в расчетах. В разумных пределах, конечно.  
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Цитата
vikttur написал: В наличии доп. столбец (можно скрыть).
Я бы так не делал... Все чаще, не люблю скрытые столбцы... Для отчетного вида да, но если придется потом этот файл дальше крутить, будут вылазить #знач и прочее(сами знаете).
Цитата
The_Prist написал: Поэтому всегда надо исходить из ситуации.
Согласен с этим полностью, нужно быть объективным в любой ситуации и находить кратчайший путь для решения проблемы.
 
Цитата
не люблю скрытые столбцы... будут вылазить #знач и прочее
Неприятности появляются независимо от того, скрыты столбцы или нет. Это больше от незнания.
 
vikttur, Получается, каждый раз при открытии нового для себя документа, нужно просматривать все листы, на наличие скрытых столбцов?  
 
В новом для Вас документе могут быть скрытые столбцы, невидимые комментарии  к ячейкам, скрытые (или нулевого размера) объекты, макросы, спрятанные листы, имена и проч. страшны-ы-ы-ые! вещи
Волка Скрытого бояться - Excel'ем не пользоваться :)
 
vikttur, шож Вы такое на ночь глядя рассказываете. Я ж теперь спать спокойно не смогу - кошмары мучить будут. :)

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Медитация и 5-7 мин. с  книгой Excel возле лотоса поможет :)
 
О, гуру! Спасибо за Ваши наставления. (а если у меня нет лотоса, возле которого я мог бы погрузиться в чтения сего писания, то можно я в позе лотоса почитаю?) =))

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
vikttur, Так предположим, файл этот, со скрытыми. Попадает скажем к руководству и нет спеца рядом, который бы подсказал, где именованные диапазоны смотреть или скрытые столбцы, смысл от таких манипуляций?
И для меня допустим, это не страшные вещи... Я смотрю со стороны удобства для работы с файлом других людей. На работе несколько раз было так, что по незнанию начальник транспортного, начал обвинять что глюки файла вызваны наличием в нем формул, которые он не понимает. Все что нам не понятно, нас пугает. Файлы с макросами от партнеров, менеджеры даже не смотрят, они их боятся трогать. А сколько раз я слышал возглас: "Нет, ничего не меняй, не надо, вдруг что-то собьется...".
Изменено: Юрий Гриднев - 13.12.2015 14:36:59
 
Цитата
Юрий Гриднев написал: смысл от таких манипуляций?
А в том и смысл, чтобы непосвященные не имели доступ к этим данным. Если начнете когда-то делать нормальные визуальные приложения для распространения файла среди многих пользователей - поймете для чего такие манипуляции делаются разработчиками. Чтобы пользователь по незнанию своему диапазон не удалил или объект или данные в нужных столбцах не затер.

К тому же, то о чем Виктор написал так же может происходить по вине самих пользователей от незнания или же в результате сбоя файла.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Я в целом согласен с Дмитрием. Я думаю вот как. В целом нет задачи, которую можно сделать формулами и нельзя сделать в VBA, но вот обратное - неверно. Выбор инструмента зависит от множества факторов. Кому-то скорость, кому-то удобность, кому-то ещё что. В VBA можно сделать вещи, которые нельзя сделать формулами (например, какой-либо сложный парсинг текста или определение цвета ячеек). Мне, например, удобнее и проще сделать что-либо в VBA. Не надо забывать, что увеличение количества ячеек с формулами и тем более с "тяжёлыми" формулами отрицательно влияет на работу с книгой. Многие пытаются сделать из Экселя базу данных, что в корне неверно. Распространённый случай - "добавление" строк с помощью формул, когда на втором листе, где должны появляться новые строки из первого листа, используются формулы. Человек, делающий это, даже не представляет, как потом ему будет неудобно работать - и совсем запутается (была практика).
Поэтому для определённой задачи необходимо выбрать тот инструмент, который наиболее оптимально подходит для решения задачи. Подстраиваться под знания пользователя может быть опасным, так как в дальнейшем самому разработчику придётся ковыряться в трёхэтажных формулах, когда изменится бизнес-логика - и эта логику не удастся реализовать  формулами.
There is no knowledge that is not power
 
Совершенно очевидно, что понятие "лучше" субъективно как, впрочем и "больших" :) Это о вопросе.
В отношении скорости пересчета того, что можно делать формулами, мое мнение: при прочих равных, VBA медленнее или максимум равен по скорости в пересчете, кроме случаев с рекурсией или повторными расчетами. От того и пишутся надстройки с дополнительными функциями не на VBA и не на VB.NET, а на XLL. Повторные расчеты (т.е. многократное использование одной и той же субформулы массива) в формулах массива  - главный бич, плюс возможность прекращения расчета по условию. В таких случаях VBA пользуется переменными (эквивалент доп. столбцов упомянутых vikttur) и возможностью в любой момент остановить перебор значений по условию, что часто экономит время. В любом случае, длина формулы как таковая или ее замысловатость на скорость пересчета не влияет или влияет незначительно. Гораздо важнее наличие вышеупомянутых повторных расчетов, а также выбор функций (некоторые более оптимизированы для работы с массивами). Дополнительные сложности создает неоправданное использование летучих функций, которые могут сделать медленные расчеты просто кромешным адом.

Вот тут много теории по вопросу оптимизации формул и их скорости (ссылки на разделы - в шапке каждой страницы):
http://www.decisionmodels.com/calcsecrets.htm
http://www.decisionmodels.com/optspeed.htm
http://www.decisionmodels.com/memlimits.htm

А тут и про формулы и про VBA UDF:
https://fastexcel.wordpress.com/
KL
 
Цитата
[USER=718]KL[/USER] написал:
От того и пишутся надстройки с дополнительными функциями не на VBA и не на VB.NET, а на XLL.
Ну да. А можно сразу на ассемблере писать. Много ли таких писателей на XLL? Есть ли у нас такие на форуме, которые пишут для заказчиков XLL функции? Чтобы писать XLL, нужно знать C/C++, а многие ли будут кидаться изучать C++ ради написания какой-то функции на XLL, время вычисления которой быть может немногим больше, чем написать её на VBA. Так что не надо принижать значение VBA - это более доступный инструмент.
И кстати, насколько я понял (читал книгу "Financial Applications Using Excel Add-in Development in C and C++" и справку официальную), XLL ограничен математическими манипуляциями, то есть XLL не рулит ячейками, селектами, цветами, фильтрами, сводными и так далее. :)
Изменено: SuperCat - 07.01.2016 17:56:58
There is no knowledge that is not power
 
SuperCat, вы явно реагируете на что-то наболевшее для вас, а не на то, что я написал. Вы несомненно больший "VBA-нацци" чем я, "но зачем же стулья ломать". Может прочтете повнимательнее? На всякий случай даю ориентировку

1.
Цитата
KL написал:
В отношении скорости пересчета того, что можно делать формулами
2. Какое отношение имеет кол-во программирующих к скорости? Но я знаком с некоторыми программистами-любителями в XLL. Я не агитировал вас за то, чтобы переходить с VBA на XLL

3. Про чисто математические манипуляции вас обманули, но у функций листа нет задачи ни возможности (за редкими исключениями) манипулировать объектами в любом случае

4. Констатировать одно из очевидных преимуществ формул в их естественной среде, не для всех означает принижать значение VBA. Это, не говоря уже о том, что я прямо упомянул, где у VBA значительные преимущества перед формулами в области расчетов.

5. Фанатизм трудно переубеждать, но, если есть желание, можем поговорить о конкретных примерах (мне не впервой участвовать в подобных дискуссиях :))

:)
Изменено: KL - 07.01.2016 19:16:32
KL
 
Да, я съехал с темы, но упоминание про XLL немного неуместно здесь - не правда ли? Да, кто-то пишет, но XLL - это совсем "низко" (достаточно просто полистать ту книгу, которую я обозначил).
There is no knowledge that is not power
 
Цитата
SuperCat написал: про XLL немного неуместно здесь - не правда ли?
Неправда :) Про XLL - это частный аргумент для иллюстрации моего утверждения о более низкой скорости VBA при прочих равных условиях. Ни больше ни меньше :) И да, есть исключения.
KL
 
Был бы премного благодарен, если бы Вы указали на очень хорошие источники, которые описывали бы разработку XLL с нуля (можно и на английском). :)
В указанной мною книге я не сразу понял, что манипулировать селектами, фильтрами и прочими объектами в XLL нельзя. В общем, чтобы понять-что из этой книги, надо её перечитывать несколько раз. Поэтому хотелось бы материал попонятней. :)
Изменено: SuperCat - 07.01.2016 21:36:44
There is no knowledge that is not power
 
Я конечно далеко не эксперт в XLL - пока только начинаю баловаться, но, похоже, ничего лучше, чем Financial applications using excel add-in development in C/C++ Стива Далтона, не найти. Это была рекомендация Чарльза Уильямса (https://fastexcel.wordpress.com/) мне во всяком случае. Если не хочется работать во фреймворке C++, то есть вариант делать это из под .NET в одном из следующих приложений к VS:

Excel DNA (бесплатно) - http://exceldna.codeplex.com/)
Addin Express (совсем не бесплатно) - https://www.add-in-express.com/add-in-net/index.php

Для меня оказалось удобнее с Addin Express, т.к. я уже баловался там с COM-Addins :) Но он конкретно дорогой, я его купил, когда нужно было для проекта.
KL
 
KL, спасибо. Только не понял про фреймворк C++ - там же для разработки XLL и одного C достаточно без каких-либо фреймворков. :)
Да, эту же книгу я упоминал и читал. Написано много, но мне кажется, что она плохо структурирована. По крайней мере, у меня такое первое впечатление. :)
Так-то я тоже пробовал, но всё дело в знании C - там же, например, работа со строками не такая, как в VBA. В общем, я так понял, окромя этой книги ничего вразумительного нет. Ну а про официальную справку и говорить нечего - там вообще не разберёшься. :)
There is no knowledge that is not power
Страницы: 1 2 След.
Наверх