VBA или сложные формулы? ИЛИ VBA или стандатные методы?

[IgorStorm]

Здравствуйте, господа знатоки.
Задался вопросом эффективного изучения Excel. Главный критерий эффективности – скорость работы в нем и относительная легкость восприятия (не красота, не крутость написания мегаформул, а именно скорость (читай производительноть). Сейчас нахожусь на этапе когда немного знаю основные формулы, умею их несложно комбинировать и знаю немного VBA могу создавать несложные/средние макросы. Нужен совет, на что лучше сделать упор: на дальнее изучение формул и «родных» методов Excel или все же лучше углубиться в VBA и забыть про стандартные средства? Хотел бы услышать ответы, на ряд возникших у меня вопросов. Извиняюсь, если получилось сумбурно - это просто поток мыслей из оперативной памяти мозга, без копания глубже.

Поясню:

Формулы вида: =ВПР(A2;ДВССЫЛ("'"&ИНДЕКС($E$2:$E$5;ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(ДВССЫЛ("'"&$E$2:$E$5&"'!A1:A50");A2)>0;0))&"'!A:B");2;0)

И меня вызывают не больше ассоциаций, чем набор иероглифов. Стоит ли тратить время на то, чтобы я достигнуть такого уровня мастерства, при котором их сам могу составлять, либо же лучше решать научится такие задачи средствами VBA? Так получится универсальнее и быстрее? Т.е. лучше ли выучить сотню родных функций Excel с их логикой и потом из них компоновать такие (сколько времени нужно на ее ввод?) либо же лучше создавать свои?
Верно, ли то, что 50% случаев необходимость ввода сложных формул возникает из-за плохой структуры первоначальных вводных данных и то что средствами VBA их лучше сначала структурировать, а затем применить расчеты попроще?
Верно ли то, что в остальных 50% необходимость ввода сложных формул возникает из-за желания пользователей получать результат в нелогичном виде (не как проще, а как красивее), и что лучше средствами VBA из первоначальных данных сделать готовый отчет с типовой структурой, чем неочевидной логикой формул пытаться вставить данные в заранее разработанную форму типового отчета?
Верно, ли то, что средствами VBA всегда удобнее  и быстрее проводить массивные расчеты, применив выгрузку данных с листа в программный массив и там проводя вычисления, чем пытаться решить ту же задачу формулами листа?
Помимо этого я понимаю, что VBA может намного больше, чем стандартные функции Excel и если в нем достичь определенного мастерства, то можно будет решать задачи гораздо эффективнее этих стандартных функций (может даже самому писать их аналоги-велосипеды). Я прав
Также я понимаю, что когда у меня будет достаточно разработок на VBA,я смогу их применять как части конструктора для решения большинства возникающих задач, т.е. разработка станет очень быстрой. Формулы между тем постоянного нужно приспосабливать под новые условия и с ними подобие "библиотеки кодов" не прокатит? Я прав?
Так как же добиться эффективного профессионализма здесь-  просто уметь делать в наше время недостаточно, нужно это делать быстро, а что лучше поможет делать быстро? Подскажите проф. советами.

[vikttur]

Начинал с формул, потом увлекся VBA.
Сейчас сложные работы выполняю с использованием и того, и другого.  Иногда требование к работе - без использования VBA, иногда применение функций листа может стать ущербным.
По сути своей функции листа - те же микропрограммы со своей логикой работы. В некоторых случаях выгоднее применять формулы, в некоторых - VBA.
Например, финансовое моделирование. Для каждого типа кредита нужно выводить свои величины. Вместо того, чтобы каждый раз пересчитывать, например, аннуитетный коэффициент - формулы на листе. И эти данные для обработки загоняются в массив в готовом виде.

Использование сложных формул. Часто пользователи боятся дополнительных столбцов, "лишних" вычислений, не понимая, что это разгружает вычисления, делает формулы проще для понимания.
Формулы работают быстрее. Но если их много, добавляют веса файлу и тормозов в работе.

О больших массивах. Если нужно найти максимальное значение в огромном массиве или посчитать определенные значения (СЧЕТЕСЛИ) - совсем не обязательно прибегать к VBA. Но если идет анализ такого массива с перебором элементов или перемещением больших блоков данных - да, просим поработать код.

О структуризации данных. Неправильная структура плохо воспринимается и формулами, и VBA. Вначале нужно продумать, а уже потом работать с формулами и VBA. Хотя... немного не так - продумывать структуру нужно параллельно с логикой работы формул/макросов.

Резюме. Писал-написал, а конкретного - ничего. Потому что опыт, который "сын ошибок трудных", подскажет, чем и как работать. И чем дальше, тем качественнее его подсказки.

просто уметь делать в наше время недостаточно, нужно это делать быстро

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

[Блымц]

Приведенная вами для примера, формула - неплохой шанс довести до исступления человека, который попробует в ней разобраться. Существенно облегчите ему (и себе!) жизнь, если будете пользоваться именованными диапазонами и разобьёте формулу на 2-3 (промежуточные результаты также порекомендовал бы именовать).
Использование многократно встроенных различных функций - это контролеНЕпригодно! Ни о каких модификациях вычислительной схемы мечтать в таких случаях не приходится. Так... задачка-однодневка.
Безусловно, удобнее использовать VBA! Причем обязательно использовать пользовательские формы. Работа будет приносить удовольствие!
(Я вашей "болезнью" с громоздкими формулами переболел лет 25 назад. Однажды, в полном озверении, бросил всё и научился создавать пользовательские формы. Это совсем несложно!)

[Дмитрий Щербаков(The_Prist)]

Безусловно, удобнее использовать VBA!

Ага. Особенно если файл будут использовать много пользователей, которые не знают как ярлыки листов отобразить, не то что макросы разрешить. Плюс в некоторых организациях выполнение макросов вообще запрещено на уровне групповой политики. Как решение для себя - да, оптимально и то не всегда в плане скорости вычислений.
А формы больше нужны для взаимодействия с пользователями и проверки вводимых значений.

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

Именованные формулы да, могут облегчить читабельность и дальнейшее "обслуживание" формулы если знать все принципы работы с именами. Однако для понимания другого человека формула не станет проще, а даже наоборот - если человек не знаком вообще с именами - для него станет большой загадкой что такое "_часть1", "_часть2" и т.п.

Как итог: ко всему надо подходит исходя из ситуации и конкретных задач. Где-то лучше формулы, где-то VBA, где-то что-то другое.

[Блымц]

много пользователей, которые не знают как ярлыки листов отобразить

Такие пользователи НИКОГДА не будут читать содержание формулы. И, тем более, пытаться редактировать формулы.
Лет 50 назад меня учили, что программой, ясной для любого дурака, будут пользоваться только дураки. Кажется, правильно учили.

что такое "_часть1", "_часть2" и т.п.

Присвоение таких малоинформативных имен не остроумно и мало отличимо от ссылок на адреса ячеек или диапазонов.

Где-то лучше формулы, где-то VBA, где-то что-то другое.

1. Если пользователь задумался о макросах или VBA, значит он намерен многократно пользоваться вычислительной схемой.
2. При многократном использовании возникает (собственный жизненный опыт) модифицировать вычислительный процесс. Посему разбирательство с вложенными функциями, которые вложены в другие вложенные функции, также вложенные.... вызовет оторопь, отвращение и т.п.
3. Я честно признался, что тоже "болел" ранее такими формулами-"матрешками". И пришел к выводу, что не стоит лениться - лучше пользоваться VBA с "недлинными" строками кода - затраченное время с лихвой окупится при (см.п.2)
Хотя, конечно, дело вкуса: мазохизм не запрещен

[Дмитрий Щербаков(The_Prist)]

Присвоение таких малоинформативных имен не остроумно и мало отличимо от ссылок на адреса ячеек или диапазонов

ну да. Я же должен был Вам прям реальные имена сейчас сходу придумать. Вы хоть фантазию включайте, когда обсуждение ведете Я же написал русским языком - пользователь, который не знает что такое именованный диапазон никогда не догадается, где эти имена искать. И для него еще большей загадкой будет это имя, чем стандартная функция. Ну читать же надо, что пишут, а не в одних своих мыслях вариться.

Если пользователь задумался о макросах или VBA

Опять же - читать надо не свое, а написанное, раз уж критиковать взялись. Я написал:

Особенно если файл будут использовать много пользователей

А такие файлы как правило создает один человек и пользователь при этом вообще ни о чем не думает. Я уже отсюда вижу, что у Вас нет опыта разработки файлов даже внутри небольшой компании для определенного коллектива. А реалии жизни как правило таковы, что сидит куча пользователей и один "спец" создает файл для их в нем работы. Тут уже не до опросов того, о чем там пользователи думают. Тут приходится считаться со многими факторами.

Предположу, что написанное Вами выше - это лишь изложение мыслей одинокого пользователя, которому совершенно нет нужды создавать файлы для других и политика ИТ внутри компании не наложила на Вас ограничений.

Поэтому повторюсь: выбор средств всегда должен исходить из ситуации, а не наоборот. Если делать наоборот - то непременно окажетесь в ситуации, когда все сделали на VBA, а работать в этом кроме Вас никто и не может. Что в итоге? Инициатива имеет инициатора, потому что либо все переделывать, либо одному делать всю работу, которая должна была быть сделана другими с помощью этого файла. И мазохизмом здесь будет как раз не учитывать все реалии и делать "как мне хочется".

[Блымц]

Почти всё понял.
Однако, должен, заметить, что вряд ли "одинокий пользователь" смог бы создать пакеты прикладных программ, которые уже много лет (больше 20) используются в Москве, в Шанхае, в Упсале и в Нагое.
Так как привык обсуждать суть дела, а не оппонента, то свое мнение оставлю при себе и  полемику прекращаю. Честь имею!