трюк №78

Мегаформули - формули всередині формул всередині формули - можуть звести з розуму навіть бувалого ветерана 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. Відображення від'ємних значень часу ».