Не бойся идти не туда – бойся никуда не идти.
(Д. Емец)


суббота, 28 апреля 2012 г.

Использование переноса строк при записи сложных формул в Microsoft Excel

Однажды на форум OsZone.net обратился пользователь, с просьбой помочь в вычислениях значений суммы знаменателей чисел, записанных в виде дробей в текстовом формате по определенному условию. Формула получилась довольно громоздкой, и трудной для восприятия и понимания.

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

=СУММПРОИЗВ(--((J9:U9)="ТО")*ПСТР(D9;ПОИСК("/";D9)+1;10)+((J9:U9)="ТР")*ПСТР(E9;ПОИСК("/";E9)+1;10)+((J9:U9)="К")*ПСТР(F9;ПОИСК("/";F9)+1;10))

получилось приличное

=СУММПРОИЗВ(--
((J9:U9)="ТО")*ПСТР(D9;ПОИСК("/";D9)+1;10)+
((J9:U9)="ТР")*ПСТР(E9;ПОИСК("/";E9)+1;10)+
((J9:U9)="К")*ПСТР(F9;ПОИСК("/";F9)+1;10)
)

Я не ставил своей целью вдаваться в подробности вычислений. Если хотите, можете разобраться сами.
Перенос строк в ячейках Microsoft Excel делается просто - сочетанием клавиш Alt + Enter
P.S.
Дальнейшая оптимизация (не без помощи знатоков) привела к следующим решениям:

=СУММПРОИЗВ(СЧЁТЕСЛИ($J9:$U9;$D$7:$F$7);--ЗАМЕНИТЬ($D9:$F9;1;ПОИСК("/";$D9:$F9);))

Две формулы ниже - формулы массива, после окончания записи в ячейку вместо Enter нужно нажать сочетание клавиш Ctrl+Shift+Enter - (CSE). После ввода у формулы появляются фигурные скобки

=СУММ(ЕСЛИ(D$7:F$7=ТРАНСП(J9:U9);--(ЗАМЕНИТЬ(D9:F9;1;ПОИСК("/";D9:F9);))))

В остальные ячейки формула вводится "протягиванием" по диапазону.
Наконец - шедевр:

=СУММ(ПСТР(D9:F9;ПОИСК("/";D9:F9)+1;9)*СЧЁТЕСЛИ(J9:U9;$D$7:$F$7))

Комментариев нет:

Отправить комментарий