Трюк №78. Как в Excel строить мегаформулы

Мегаформулы — формулы внутри формул внутри формулы — могут свести сума даже бывалого ветерана Excel. Однако, имея немного предусмотрительности и шаг за шагом продвигаясь к желаемой формуле, вы сможете приручить эти сложные мегаформулы и обращаться с ними без страха.

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

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

Предположим, есть длинный список имен людей, каждое из которых состоит из имени, отчества и фамилии, то есть по одному полному имени на ячейку. Задача состоит в том, чтобы написать формулу в соседнем столбце, которая будет извлекать только фамилию человека, который позвонил в отель Скандинавия и забронировал шикарный номер люкс.

Следовательно, надо найти начало фамилии, третьего слова в ячейке. Фактически, это означает поиск позиции второго пробела в ячейке. В Excel нет стандартной встроенной функции для автоматического поиска второго пробела в ячейке, но можно воспользоваться функцией НАЙТИ (FIND), применив ее так, чтобы получить желаемый результат.

В ячейку А1 введите имя David John Hawley (или любое другое имя, состоящее из трех слов). В ячейку С1 введите следующую функцию: =FIND(" ";A1), в русской версии Excel =НАЙТИ(" ";A1). Функция НАЙТИ (FIND) ищет одну текстовую строку искомый_текст (find_text) внутри другой текстовой строки текст_для_поиска (within_text) и возвращает число, обозначающее начальную позицию искомый_текст (find_text), если отсчет начинается с первого символа текст_для_поиска (within_text). Синтаксис функции =FIND(find_text;within_text,;start_num), в русской версии Excel =НАЙТИ(искомый_текст;текст_для_поиска;нач_позиция).

Эта функция вернет начальную позицию первого символа пробела в ячейке А1, так как приказали ей найти «» (пробел) в ячейке А1. Если в ячейке находится текст David John Hawley, то функция вернет значение 6. Но вы ищете второй пробел, а не первый. Теперь нужно использовать число, возвращенное формулой в ячейке С1, как начальную позицию для другой функции НАЙТИ (FIND), чтобы найти второй символ пробела. Так, в ячейке С2 введите формулу =FIND(" ";A1;C1+1), в русской версии Excel =НАЙТИ(" ";A1;C1+1). Обратите внимание, что в этот раз вы передали функции НАЙТИ (FIND) третий аргумент, начальную позицию, найденную С1 (в данном примере равную 6), к которой прибавлена единица; она будет служить начальной точкой для функции НАЙТИ (FIND), начиная с которой она будет искать пробел. Вторая функция возвратит значение, обозначающее позицию второго символа пробела.

Вооружившись этими функциями, вы хотите, чтобы следующая функция выбрала все символы до конца строки текста. Воспользуйтесь функцией ПСТР (MID), которая предназначена для выделения диапазона символов из строки. В ячейке СЗ введите следующую формулу: =MID(A1;C2+1;256), в русской версии Excel =ПСТР(А1;С2+1;256). Функция ПСТР (MID) возвращает указанное количество символов текстовой строки, начиная с указанной позиции. Вот ее синтаксис: MID(text;start_num;num_chars), в русской версии Excel ПСТР(текст;начальная_позиция;количество_символов). Вы приказали функции ПСТР (MID) выбрать 256 символов из ячейки А1, начиная с первого символа после второго пробела в текстовой строке. Число 256 используется только чтобы гарантировать, что, независимо от длины фамилии (предполагая, что она все же короче 256 символов), вы получите фамилию человека полностью.

При наличии всех составляющих настало время построить вложенную формулу, которой вы так боялись всего несколько минут назад, целиком. Все, что нужно сделать, — это заменить все ссылки на ячейки (кроме А1) внутри функций на формулы в этих ячейках. Это делается путем копирования и вставки в строке формул.

Щелкните ячейку С2, в строке формул выделите функцию и скопируйте ее целиком, кроме знака =, вот так: FIND(" ".A1;C1+1), в русской версии Excel НАЙТИ(" ";A1;C1+1). Нажмите клавишу Enter, чтобы покинуть ячейку, и вы окажетесь в ячейке С3. Выделив ячейку С3, в строке формул выделите ссылку на ячейку С2 и вставьте (сочетание клавиш Ctrl+V) функцию НАЙТИ (FIND), которую только что скопировали. Нажмите клавишу Enter. Функция в ячейке СЗ должна выглядеть так: =MID(A1;FIND(" ";А1;С1+1)+1;256), в русской версии Excel =ПСТР(А1;НАЙТИ(" ";А1;С1+1)+1;256).

Теперь необходимо заменить ссылку на ячейку С1 функцией, которая находится в ячейке С1. Выделите ячейку С1, выделите в строке формул формулу, опустив символ =, щелкните на кнопке Копировать (Сору), а затем дважды нажмите клавишу Enter, чтобы попасть обратно в ячейку С3. Находясь в ячейке С3, выделите С1 в строке формул и вставьте только что скопированную функцию НАЙТИ (FIND). Нажмите клавишу Enter.

Теперь осталось вырезать ячейку С3 и вставить ее в ячейку В1, а затем удалить формулы, оставшиеся в ячейках С1 и С2. Вы должны получить итоговую формулу, которая выглядит так: =MID(A1;FIND(" ";A1;FIND(" ";А1)+1)+1;256), в русской версии Excel =ПСТР(А1;НАЙТИ(" ";А1;НАЙТИ(" ";A1)+1)+1;256).

Следуя этой концепции, вы должны понять, как конструировать мегаформулы, используя разнообразные функции Excel. Сначала необходимо запланировать путь, по которому вы собираетесь идти к цели, а затем в индивидуальных ячейках получать необходимые результаты. Наконец, нужно заменить все ссылки на ячейки функциями, которые находятся в этих ячейках. Если получилось больше семи уровней вложенности, понадобится функция ДВССЫЛ (INDIRECT), описанная в разделе «Трюк №74. Отображение отрицательных значений времени».