Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Курилка
Страницы: 1
RSS
Проблемы отладки формул в Excel
 
#1
06.05.2025 17:25:00

Добрый день! Столкнулся с проблемой в  Excel: пишешь формулу, вроде всё логично, но результат получается  совершенно неожиданным. Начинается адская отладка через "Вычислить  формулу" — интерфейс этого инструмента просто убийственный:  микроскопический шрифт, крошечное окно, а при работе с большими  формулами или массивами хочется лезть на стену. Постоянные прыжки экрана  при каждом шаге, а если в формуле есть массивы с кучей нулей (типа  0:0:0…1), то поиск ошибки превращается в квест на выживание.

Вопрос:  как с этим справляются профессионалы? Может, есть специальные  надстройки или методы для визуализации вычислений? Например, в Office  365 при наведении курсора можно видеть промежуточные результаты, но это  не работает с внешними ссылками или массивами. Интересно, гуру Excel  просто запоминают структуру формул или у них есть свои лайфхаки?

Буду благодарен за советы, как эффективно отлаживать формулы, особенно в сложных сценариях. Спасибо!
 
 
 
#2
06.05.2025 17:31:21
в копилку будущих ответов )
правильный шрифт в строке формул

ну и ещё оттуда же:
наглядность в сложных формулах
Изменено: nilske - 06.05.2025 17:32:57
 
 
 
#3
06.05.2025 17:53:32
У меня нет проблем с интерфейсом строки формул: меня устраивает  стандартный шрифт, и я не люблю, когда формулы растягиваются на весь  экран, как в VBA. С расстановкой скобок и аргументов тоже всё в порядке —  подсветка парных скобок и подсказки вида (диапазон; условие1; условие2) вполне удобны.
Например: Есть сложная формула, которая должна возвращать дату, но вместо этого выдает неожиданный результат. Пример формулы:
=ЕСЛИ(ИЛИ([@[Фамилия Имя Отчество]]="";[@[Основное подразделение]]="Уволенные сотрудники");"";LET(V_CD;'Табель общий'!$AD$2;V_DATA; 'https://d.docs.live.net/D817D0C0318673E3/Табели/2025/[Employees Vajak.xlsm]Графики отпусков'!$D$4:$HK$100;V_ROW; ПОИСКПОЗ([@[Фамилия Имя Отчество]];'https://d.docs.live.net/D817D0C0318673E3/Табели/2025/[Employees Vajak.xlsm]Графики отпусков'!$A$4:$A$100;0);V_COL;СУММПРОИЗВ((ГОД(V_CD)=ГОД(V_DATA))*(МЕСЯЦ(V_CD)=МЕСЯЦ(V_DATA))*(ОСТАТ(СТОЛБЕЦ(V_DATA)-СТОЛБЕЦ('https://d.docs.live.net/D817D0C0318673E3/Табели/2025/[Employees Vajak.xlsm]Графики отпусков'!$D$4)+2;2)=1)*(СТОЛБЕЦ(V_DATA)-СТОЛБЕЦ('https://d.docs.live.net/D817D0C0318673E3/Табели/2025/[Employees Vajak.xlsm]Графики отпусков'!$D$4)+1)*(СТРОКА(V_DATA)=V_ROW+3));V_COL_N;СУММПРОИЗВ((ГОД(V_CD)=ГОД(V_DATA))*((МЕСЯЦ(V_CD)+1)=МЕСЯЦ(V_DATA))*(ОСТАТ(СТОЛБЕЦ(V_DATA)-СТОЛБЕЦ('https://d.docs.live.net/D817D0C0318673E3/Табели/2025/[Employees Vajak.xlsm]Графики отпусков'!$D$4)+2;2)=1)*(СТОЛБЕЦ(V_DATA)-СТОЛБЕЦ('https://d.docs.live.net/D817D0C0318673E3/Табели/2025/[Employees Vajak.xlsm]Графики отпусков'!$D$4)+1)*(СТРОКА(V_DATA)=V_ROW+3));V_COL_VS;СУММПРОИЗВ((ГОД(V_CD)=ГОД(V_DATA))*(МЕСЯЦ(V_CD)=МЕСЯЦ(V_DATA))*(ОСТАТ(СТОЛБЕЦ(V_DATA)-СТОЛБЕЦ('https://d.docs.live.net/D817D0C0318673E3/Табели/2025/[Employees Vajak.xlsm]Графики отпусков'!$D$4)+1;2)=1)*(СТОЛБЕЦ(V_DATA)-СТОЛБЕЦ('https://d.docs.live.net/D817D0C0318673E3/Табели/2025/[Employees Vajak.xlsm]Графики отпусков'!$D$4)+1)*(СТРОКА(V_DATA)=V_ROW+3));V_RESULT;ЕСЛИ(V_COL>0;ИНДЕКС(V_DATA;V_ROW;V_COL);ЕСЛИ(И(V_COL=0;V_COL_VS<>0);ИНДЕКС(V_DATA;V_ROW;V_COL_N);""));ЕСЛИ(И(V_COL=0;V_COL_N=0);"";V_RESULT)))
Как определить, на каком этапе формула дает сбой?
 
 
 
#4
06.05.2025 18:09:03
В таких случаях обычно сокращают большие диапазоны внутри формулы на 3-5 ячеек, чтобы через Вычислить формулу можно было проследить логику вычислений. Так же помогает разбиение формулы на блоки, чтобы определить, верно ли каждый блок отрабатывает.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#5
06.05.2025 20:27:27
mabusaco, хороший вопрос, особенно при использовании новомодных LET и старых добрых массивных, когда результат не вычислить в строке формул или результат не один , а массив. Тут надо творчески подходить и разобрав на боки как написал Дмитрий, проверять и собирать корректные результаты до сбоя.
По вопросам из тем форума, личку не читаю.
 
 
 
#6
06.05.2025 20:31:10
Есть ли какие-нибудь секретные шаолиньские техники - например, правка чего-то в реестре или волшебные комбинации клавиш, чтобы по нажатию "вуаля" - "Вычислить формулу" на полный экран? Или может что-нибудь стороннее с похожим функционалом?
 
 
 
#7
06.05.2025 22:47:14
mabusaco,  вот совсем непонятно что под
Цитата
mabusaco написал:
по нажатию "вуаля" - "Вычислить формулу" на полный экран
подразумевается.
Техники применяемые порой таковы что порой сознательно генерируется ошибка но потом она обрабатывается.  А волшебная кнопка - это оплата тому кто сделает все по заданию и выдаст корректную формулу.
По вопросам из тем форума, личку не читаю.
 
 
 
#8
07.05.2025 11:52:18
Цитата
mabusaco:   по нажатию "вуаля" - "Вычислить формулу"
Клавиша F9 в строке формул (предварительно выделив часть формулы, которую надо вычислить)
Вообще с появлением динамических массивов это стало удобнее в разы - почти на лету можно посмотреть и оценить тот или иной массив
Что касается LET, то я всегда отлаживаю все в последнем аргументе (а рабочая формула временно в предпоследнем)
 
 
 
#9
26.05.2025 08:15:10
mabusaco, есть очень продвинутый способ! Переписать всё на VBA и отлаживать через клавишу F8!
 
 
 
#10
26.05.2025 09:16:51
Кстати в Npp недавно появилась подсветка синтаксиса формул Excel.
 
 
 
Страницы: 1
Читают тему
Наверх