Мегаформулы

Одна из особенностей Excel заключается в возможности самостоятельно «конструировать решение» любых задач. Формулы, с которыми вам приходится работать, можно вкладывать одна в другую, тем самым создавая абсолютно новые МЕГАФОРМУЛЫ, которые будут идеально соответствовать требуемому решению.

Давайте разберём примеры в файле:   

У нас есть база данных (А1:В7), что являет собой наименование дней недели с их нумерацией по порядку. Нам необходимо ввести в ячейку D1 сокращённое название дня и получить в ячейке Е1 его номер по порядку. Если вы попробуете вставлять названия, то увидите результат, 1 – 7 или Ошибка в случае несоответствия значения. Причём Пн и ПН распознаётся одинаково.

Теперь давайте внимательно изучим формулу в Е1:

=ЕСЛИ(D1="пн";1;ЕСЛИ(D1="вт";2;ЕСЛИ(D1="ср";3;ЕСЛИ(D1="чт";4;ЕСЛИ(D1="пт";5;ЕСЛИ(D1="сб";6;ЕСЛИ(D1="вс";7;"Ошибка")))))))

Здесь используется базовая формула ЕСЛИ:

=ЕСЛИ(D3="пн";1;0)  читаем

Если  D3 равняется тексту "пн" текст обязательно в кавычках, тогда будет 1 а если не равняется, тогда 0 причём условия разделены   ;

А теперь откинемся на спинку кресла (по рекомендации Microsoft) и поразмышляем. Если текст равен "пн" тогда понятно, что будет 1. А что если текст не будет равен "пн" и вместо стандартного ответа 0 подставить новую формулу ЕСЛИ, но с такими же условиями только на текст "вт"??? Получаем:

=ЕСЛИ(D1="пн";1;ЕСЛИ(D1="вт";2;0))

где жирным шрифтом покрашена первичная формула, а наклонённым – вложенная формула.

Таким образом, вкладывая формулы одна в другую (как матрёшки), мы заставляем мегаформулу анализировать и «перескакивать» с одного условия на другое, пока не будет найдено соответствие. Если вы проанализируете мегаформулу в Е1, то увидите этот эффект. Спасибо Билу Гейтсу за то, что при редактировании мегаформулы, кавычки разграничения формул окрашиваются в разные цвета – это помогает :)))

Однако приятное не вечно и все наши мегаформулы не могут превышать размер в 1024 знака :(((

Пример «комбинированной» мегаформулы представлен в ячейке Н2. Используя формулу ЕОШИБКА, происходит поиск соответствия номера дня его названию в неделе.

Справка:  ЕОШИБКА( ) = ЛОЖЬ или ИСТИНА эта формула определяет содержание значения в любой ячейке или вложенной формуле на наличие ошибочного значения (значение ссылается на любое значение ошибки (#Н/Д, #ЗНАЧ! , #ССЫЛ! , #ДЕЛ/0! , #ЧИСЛО! , #ИМЯ? или #ПУСТО!).

Разберём формулу:

=ЕСЛИ(ЕОШИБКА(ВПР(G1;A1:B7;2;0))=ЛОЖЬ;ВПР(G1;A1:B7;2;0);"Ошибка")

ВПР(G1;A1:B7;2;0) ищет соответствия номера названию дня. Однако, если значение не соответствует базе данных A1:B7, то выдаётся ошибка #Н/Д. Чтоб избежать этой ошибки, мы вкладываем формулу ВПР в формулу ЕОШИБКА и определяем, что формула ЕСЛИ, в которой и происходит вся эта канитель, увидит наличие ошибки =ИСТИНА, тогда просто напишет "Ошибка". Ну а если значение будет правильным в ВПР т.е. =ЛОЖЬ, тогда следующее условие формулы ЕСЛИ покажет результат этой формулы ВПР.

Короче всё очень просто, главное не запутаться и лучше создавать мегаформулы сначала по частям и протестировав их – собирать в единую мегаформулу.

Всё как анекдоте про борца на ринге: Сделал бросок, вижу перед собой чью-то задницу, укусил – оказалась своя.