Excel - это просто!

A+ A A-

Как читать сложные формулы

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

Эту статью я хочу посвятить не тому, как писать сложные формулы. Поставим себя на место тех людей, которым мы эти файлы рассылаем. Более того, представим, что результаты расчетов показались нам сомнительными и теперь нам надо разобраться в том, что кто-то сделал.

Еще один важный момент, который я хотел бы осветить перед тем, как приступить к главному вопросу статьи. Давайте делать подарки нашим преемникам - вставлять комментарии в формулы. О том, как это делается, подробнее в этой статье.

Возьмем формулу как пример из статьи Как перевести дату из текстового формата в формат даты и рассмотрим ее подробнее.

Дано: дата в текстовом формате в виде «M/D/YYY», например, «8/5/2011» или пятое августа 2011 года. Необходимо: перевести ее в формат даты, который может участвовать в расчетах.

Ячейке с входными данными присвоено имя «date_input_4».

Формула:

=IFERROR(DATE(RIGHT(date_input_4;4); LEFT(date_input_4; SEARCH("/";date_input_4)-1); MID(date_input_4; SEARCH("/";date_input_4)+1; SEARCH("/";date_input_4; SEARCH("/";date_input_4)+1)-SEARCH("/";date_input_4)-1)); "Input format error")

Первое впечатление – без ста граммов не разберешься! Но на работе пить нельзя под страхом смертной казни, поэтому снимаем пиджак и прыгаем в пропасть, на дне которой плещутся непонятные обрывки формул. Предлагаю сделать это в несколько шагов.

Первое, говорим большое спасибо разработчикам офиса (начиная с 2007 версии; если у вас более ранняя версия – я вам очень сочувствую). В офисе 2007 и дальше, когда мы входим в редактирование формулы (F2), он автоматически подсвечивает ссылки на ячейки (или имена) на этом листе разными цветами. В нашем случае ссылка всего на одну ячейку, но ее выделение синим цветом очень поможет нам отделить ее визуально от функций. Формула будет выглядеть так:

=IFERROR(DATE(RIGHT(date_input_4;4); LEFT(date_input_4;SEARCH("/";date_input_4)-1); MID(date_input_4; SEARCH("/";date_input_4)+1; SEARCH("/";date_input_4; SEARCH("/";date_input_4)+1)-SEARCH("/";date_input_4)-1)); "Input format error")

Второе, не все об этом знают (на мой взгляд это очень полезное знание), текст внутри ячейки можно разделить на несколько абзацев. Для этого необходимо поставить курсор в то место, где надо поставить абзац, и нажать Alt + Enter. И что самое приятное, если такие абзацы не разделяют название функций, ссылки и имена, то они никак не влияют на расчеты. И это отлично!

Предлагаю разделить нашу формулу на несколько составных частей. Для этого сначала необходимо бегло разобрать структуру формулы. Итак, что мы в ней видим. IFERROR (ЕСЛИОШИБКА) - проверка на ошибку. Это мой бзик, в принципе, если бы ее здесь не стояло, то в случае ошибки формула бы выдавала ошибку #VALUE!. Здесь же ее можно удалить в целях отпимизации формул.

Дальше стоит формула DATE (ДАТА), которая собирает дату (в формате даты) из трех переменных: год, месяц, день (именно в таком порядке). Вот на них мы формулу и разобьем. Несколько раз нажимаем Alt + Enter, растягиваем строку формул (Formulabar), чтобы вся формула была видна одновременно, и получаем следующее (кстати, именно в таком виде она была оригинально написана в статье Как перевести дату из текстового формата в формат даты).

=IFERROR(DATE(
RIGHT(date_input_4;4);
LEFT(date_input_4;SEARCH("/";date_input_4)-1);
MID(date_input_4;SEARCH("/";date_input_4)+1;SEARCH("/";date_input_4;SEARCH("/";date_input_4)+1)-SEARCH("/";date_input_4)-1));
"Input format error")

Если мы нажмем Enterи применим изменения формулы, ее значение не должно измениться.

Третье, разберемся в том, что получилось, поблочно.
RIGHT(date_input_4;4);
Год. Функция RIGHT (ПРАВСИМВ) просто берет последние четыре символа из даты (хотелось бы верить, что в дате не будет пробела в конце, иначе ошибка в расчете нам обеспечена – отсюда вопрос, хотим ли мы инкорпорировать проверку на пробелы в формулу или решим потенциальную проблему простой заменой пробела на пустую строку? – решать вам, я решил этого не делать).

LEFT(date_input_4;SEARCH("/";date_input_4)-1);
Месяц. Когда я вижу вложенные формулы, я читаю их изнутри, называя каждую часть как-нибудь понятно для себя. В данном случае, SEARCH("/";date_input_4) (ПОИСК) выдает номер символа “/” в строке (2, в нашем случае). SEARCH("/";date_input_4)-1 – то же, но на один символ раньше. Этот блок я назову для себя «Номер последнего символа до “/”».

Дальше, формула LEFT (ЛЕВСИМВ), берет из входных данных слева все символы до Номера последнего символа до “/”, включительно. В нашем случае, она берет один символ слева, то есть 8.

MID(date_input_4;SEARCH("/";date_input_4)+1;SEARCH("/";date_input_4;SEARCH("/";date_input_4)+1)-SEARCH("/";date_input_4)-1));
День. Вот это уже поинтереснее. Ее бы уже и саму стоит разложить на несколько частей, но мы этого делать не станем. Сама по себе формула MID (ПСТР) берет (1) из некоторой строки, (2) начиная с некоторого символа, (3) необходимое нам количество символов. Остальное читаем изнутри.

  1. Со строкой, из которой брать символы, все понятно – это наши входные данные.
  2. SEARCH("/";date_input_4) – это номер символа “/”. Соответственно, SEARCH("/";date_input_4)+1 – это номер первого символа после “/”.
  3. SEARCH("/";date_input_4;SEARCH("/";date_input_4)+1) – ищет в нашей строке “/”, начиная с первого симвода после “/” или, другими словами, ищет второй “/”. Назовем эту часть формулы номер второго символа “/” (что важно, от начала строки). Если вычесть из нее SEARCH("/";date_input_4) (то есть номер первого символа “/”), то получится количество символов от первого симвода “/” до второго, включая один из них и исключая другой. Вычитаем из этого количества еще 1, и получаем количество символов между первым и вторым символом “/”.

То есть день получился равен (1) выбранным из строки с датой, начиная (2) с первого символа после “/”, (3) нескольким символам между первым и вторым символом “/”. То есть 5, в нашем примере.

Что мы имеем в итоге. Формула проверяет на ошибку, в случае ошибки выдает "Inputformaterror". Если ошибки нет, то она формирует дату (в формате даты) из года, месяца и дня, которые она выбирает из текста строки с помощью текстовых формул RIGHT, LEFT, MID и SEARCH. День месяц и год выбираются корректно. Получается 05.08.2011, что и требовалось.

Резюмируя вышесказанное, я рекомендую, читая сложные формулы, делать это в несколько шагов:

  1. поблагодарить Мелкософт за хорошо следанную работу (можно пропустить, принимая во внимание, сколько они за эту работу хотят);
  2. определить основную структуру формулы и разделить ее на блоки построчно;
  3. разобраться в каждом блоке, читая формулы наизнанку и давая блокам понятные себе имена; и
  4. озвучить себе, что получилось в итоге.
По оценкам экспертов только десять процентов пользователей MS Excel умеют граммотно пользоваться формулами рабочего листа. Если вы чувствуете, что вы не среди этих десяти процентов, книга Формулы в Microsoft Excel 2010 для вас. Не пожалейте времени на ее прочтение - она того стоит!

Последние новости

Как быстро удалить тысячи строк из громадной таблицы по филь…

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

О нас

Напишите мне edward@youcanexcel.ru

О проекте

Копирование материалов сайта разрешено только с согласия владельца с размещением ссылки на источник.

Веб-сайт оптимизирован под разрешение не менее 1024x768.

Сайт отражается некорректно? Должно быть вы пользуетесь Internet explorer. Установите себе хороший браузер!