Рубрики
Формулы

Формула EOMONTH (КОНМЕСЯЦА)

Часто готовите отчеты, в которых указывается последний день месяца? Формула EOMONTH (КОНМЕСЯЦА) как будто специально создана для вас. Она возвращает дату, соответствующую последнему дню месяца. Причем необязательно текущего месяца, например, предыдущего или следующего, или шесть месяцев назад — решать вам.

Синтаксис у нее предельно простой: =EOMONTH(start_date;months). Оба параметра обязательные.

start_date — дата, от которой начинать считать. Здесь надо указать ссылку на другую ячейку с датой или дату в формате Excel (например, 24 декабря 2011 года — это 40901). Второй вариант неудобный, поэтому я предлагаю использовать формулу DATE, а в скобках указать год, месяц и день (например 24 декабря 2011 года будет выглядеть как DATE(2011;12;24)

months — на сколько месяцев сдвигать дату. 0 — это текущий месяц, 1 — следующий, -1 — предыдущий, -3 — три месяца назад и т.п.

Пример: =EOMONTH(DATE(2011;12;24);-3) вернет нам 30.09.2011. Логика расчета следующая. Дата — в декабре 2011 года, отсчитываем три месяца назад — (1) ноябрь, (2) октябрь, (3) сентябрь. Берем последний день месяца — получаем 30 сентября 2011 года.

Рубрики
Формулы

SUM (СУММ) против SUBTOTAL (ПРОМЕЖУТОЧНЫЕ.ИТОГИ)

В excel есть несколько вариантов суммирования с помощью функций. Обычно, при нажатии на автосумму программа вставляет функцию SUM (СУММ). Если на листе стоит фильтр, то программа автоматически вставит функцию SUBTOTAL (ПРОМЕЖУТОЧНЫЕ.ИТОГИ). Эти функции, хоть и делают похожие вычисления, все же существенно отличаются. И умение пользоваться ими правильно может сэкономить вам много времени.

Функция SUM (СУММ) работает очень просто — она суммирует все диапазоны, указанные в скобках. Диапазонов может быть несколько: их необходимо указывать через точку с запятой (или запятую, в зависимости от ваших настроек системы — об этом подробнее в этой статье).

Функция SUBTOTAL (ПРОМЕЖУТОЧНЫЕ.ИТОГИ) умеет гораздо больше: суммировать, умножать и др. В этой статье я остановлюсь на суммировании. Для этого в скобках первым параметром нужно указать 9. Другие параметры можно посмотреть в справке по функции.

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

Второе — если в диапазон промежуточных итогов попадает ячейка, рассчитанная с помощью промежуточных итогов, то она пропускается при вычислениях.

Это удобно для вычислений таблиц следующего толка, когда необходимо рассчитать промежуточные итоги для категорий и подкатегорий. Смотрите пример ниже.

Эту же таблицу я прикладываю в виде файла электронной таблицы. Обратите внимание, что итоги для категорий рассчитаны двумя разными способами для сравнения: с помощью функции SUBTOTAL и функции SUM. По-моему, вероятность ошибки при работе с SUBTOTAL гораздо ниже. Как только мы начинаем суммировать подитоги категорий с помощью SUM или просто складывая ячейки, появляется серьезная возможность пропустить один из таких итогов, особенно если категорий 20-30. Кроме того, разобраться с такой формулой будет довольно сложно (особенно, если она с ошибкой).

Если у вас остались вопросы по этой или другим темам, пожалуйста, оставляйте их в комментариях или присылайте на электронную почту edward@youcanexcel.ru.

Рубрики
Формулы

Имена (Names) для ссылки на диапазон

Microsoft Excel позволяет присваивать диапазонам ячеек имена, удобные для пользователя. Мне всегда казалось, что это не особенно полезная функциональность, пока я не перевелся в экономический отдел.

Частью моей работы стала прогонка большого количества экономических сценариев, которые строятся на различных данных в помощью одной и той же расчетной модели. Эти модели разделены в разные файлы, что довольно удобно. Нет необходимости хранить расчеты для каждого сценария. Более того, если позже вы захотите обсчитать те же данные с помощью другой (или измененной) модели, это даст вам определенную гибкость.

Именно для этого функциональность имен просто незаменима.

Немого технических деталей

Для того чтобы присвоить диапазону имя, выделите его и введите имя в блоке для имен (Name box) слева от строки формул. Если такого имени еще нет в книге, то оно будет присвоено данному диапазону. Если есть — диапазон с этим именем будет выделен (для этих целей можно нажать на кнопку выпадающего списка и выбрать имя там).

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

Чтобы изменить диапазоны с именами или удалить их, на ленте Formula нажмите на кнопку Names Manager в блоке Defined Names (или просто Ctrl + F3). В появившемся окне вы можете создавать, изменять и удалять имена, присвоенные диапазонам данных.

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

Преимущества имен перед обычными ссылками

Во-первых, имена обычно присваиваются таким образом, чтобы по смыслу соответствовать диапазону данных. Например, создавая имя для выручки из файла с данными, я бы присвоил ей имя D_Revenue, где D — data file, а revenue — соответственно, выручка. Но это меньшее из преимуществ (у него есть и обратная сторона — формулы становятся существенно длиннее, особенно, когда надо просуммировать 5-10 строк с разными именами, идущие подряд).

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

Часто входные данные для наших расчетов приходят из других отделов. Вы можете договориться, чтобы те данные, которые вы используете из этих файлов были поименованы, и имена не менялись из месяца в месяц и в разных версиях отчета. Тогда для того чтобы подтянуть новые данные в ваш расчет, вам необходимо будет просто обновить ссылки на внешний файл (10-15 секунд). Об этом в отдельной статье.

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

Рубрики
Формулы

Относительные и абсолютные ссылки

Вы часто сталкиваетесь с проблемой, что при копировании ячеек, которые ссылаются на другие, формула уплывает в сторону или вверх/вниз. Надоело переделывать ссылки в каждой ячейке? Эта статья для вас!

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

Для данной цели в Microsoft Excel предусмотрена функциональность относительных и абсолютных ссылок.

Относительная ссылка выглядит приблизительно так «=B4». Такая ссылка при копировании будет уезжать. Предположим, что вы скопировали такую ячейку на одну ячейку вправо. Ссылка получится «=C4». Это может быть и удобно и неудобно в зависимости от поставленных целей.

Абсолютная ссылка при копировании в сторону или вверх/вниз меняться не будет, она будет ссылаться на ту же самую ячейку. Записана она будет так «$B$4».

Бывают также частично абсолютные ссылки. Для наглядности я обычно объясняю их сущность с помощью якоря. Знак $ в ссылке — это якорь. Если поставить его перед буквой («$B4»), то ссылка зацепится за столбец, и при копировании в любую сторону буква столбца меняться не будет; но при копировании по вертикали цифра в ссылке будет меняться. Если поставить якорь перед цифрой («B$4»), то якорь зацепится за цифру, и при копировании вверх или вниз цифра в ссылке сохранится, но при копировании по горизонтали буква будет меняться.

Посмотрите на пример внизу. В нем было сделано следующее: блок цифр «Исходные данные» — это данные, на которые будут ссылаться разные ячейки. Желтая ячейка в каждом из блоков со ссылками ссылается на ячейку «B4». Я копировал эту ячейку во все стороны вокруг нее. Вот, что из этого получилось.

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

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

Рубрики
Формулы

Суммирование с условием (SUMIFS — СУММЕСЛИМН)

Данная статья написана по мотивам и в продолжение статьи о поисковых формулах. SUMIFS — это еще одна формула, которая пользуется бешенной популярностью в повседневной работе. Данная формула позволяет просуммировать некоторые значения, для которых выполняется одно или несколько условий. Итак, как же она работает.

Что делает:

Проверяет некий диапазон на выполнение условия — суммирует ячейки соответствующие тем, где условие выполняется. Что особенно приятно, условий может быть несколько.

Синтаксис: =SUMIFS(Sum_range;Criteria_range1;Criteria1…)

Sum_range — что суммируем,

Criteria_range1 — где проверяем, выполняется ли условие,

Criteria1 — собственно, условие,

Criteria_range2 — где проверяем, выполняется ли условие,

Criteria2 — собственно, условие, и т.д. столько условий, сколько необходимо проверить (в разумных пределах, конечно).

Как работает:

(1) Идет в первую ячейку диапазона Criteria_range1, проверяет, выполняется ли условие Criteria1. Если выполняется, идет в первую ячейку диапазона Criteria_range2, проверяет, выполняется ли для нее условие Criteria2. Операцию повторяет для каждого введенного условия. Если все условия выполнились, прибавляет к результату первую ячейку из диапазона Sum_range.

(2) Идет во вторую ячейку диапазона Criteria_range1, проверяет, выполняется ли условие Criteria1. Если выполняется, идет во вторую ячейку диапазона Criteria_range2, проверяет, выполняется ли для нее условие Criteria2. Операцию повторяет для каждого введенного условия. Если все условия выполнились, прибавляет к результату вторую ячейку из диапазона Sum_range.

(3) И так далее, пока не проверит весь условный диапазон.

Что иметь в виду:

(1) Формула реально проверяет ВЕСЬ условный диапазон. Если в таблице сто тысяч строк и вы прописали 10 условий, формула может считаться довольно долго. Особенно, если она повторяется пятьдесят тысяч раз. Для примера, у меня в работе была таблица, которая пятьдесят тысяч раз считала SUMIFS по трем условиям из таблицы в стопятьдесят тысяч строк. Это какой-то ужас! Такая таблица на двухядерном компе с 2ГБ оперативы считается от 30 до 40 минут. Поэтому, если есть возможность оптимизировать такую формулу — лучше это сделать. Об обтимизации поисковых формул читайте в одной из следующих статей.

(2) Посколько формула проверяет несколько диапазонов и всегда берет из суммируемого диапазона СООТВЕТСТВУЮЩЕЕ значение, то все диапазоны должны быть равны по размеру (что совершенно не значит, что они должны быть одинакого направлены).

(3) Условие — это не обязательно совпадение параметра (хоть это и наиболее частый вариант). Можно построить формулу, чтобы она проверяла, больше ли значение, чем нужное нам. Например, указать условие «>»&A2. Эта формула будет проверять, больше ли значение, чем значение в A2.

(4) В отличие от поисковых формул (vlookup и hlookup), если формула не находит совпадений, она не выдает ошибку, а возвращает ноль.

Nota bene. Коллеги, которые долго работали в Excel до 2007 версии, частно не знают этой формулы и пользуются формулой SUMIF (СУММЕСЛИ). Формула очень схожа с SUMIFS. Я нашел два очень существенных отличия. Первое, SUMIF имеет другой синтаксис (другой порядок членов), что может запутать. Второе, SUMIF позволяет сумировать проверяя не более одного критерия, что может оказаться существенным ограничением. Производительность при проверке одного условия у них приблизительно одинаковая. Поэтому лично я рекомендую пользоваться только SUMIFS — она может все то же, что и SUMIF, и даже больше. Кроме того использование одной и той же формулы в идентичных случаях ведет к унификации, что не бывает лишним.

Пример смотрите в приложенном файле.

Подробнее о различных формулах и функциях в Microsoft Excel вы можете прочитать в книге Формулы в Microsoft Excel 2010 Джона Уоренбаха. Книга описывает, как эффективно использовать различные функции и как эффективно сочетать их в формулах. В ней также описаны специальные формулы, такие как финансовые и формулы массива. Книга учит эффективно использовать сводные таблицы и диаграммы. Она определенно стоит того, чтобы иметь ее в своей библиотеке.

Также обратите внимание на книгу Microsoft Excel 2010. Библия пользователя. Это действительно Библия пользователя. Если у вас есть вопрос, то вы наверняка найдете на него ответ в этой книге.

Рубрики
Формулы

Поисковые формулы (vlookup — ВПР, hlookup — ГПР)

Пожалуй, одна из самых полезных и ходовых формул в работе — vlookup (ВПР). Она же вызывает самое большое количество вопросов и сложностей. Данная статья описывает, как можно пользоваться этой формулой (а также ее аналогом для горизонтального поиска — hlookup — ГПР), какие чаще всего возникают проблемы, и как с ними бороться.

Зачем она нужна? Формула vlookup ищет некоторое значение в таблице. Если находит, то выводит из той же строчки значение, которое находится в нужной нам колонке. Если не находит, то выдает ошибку #N/A (#Н/Д)

Пример использования: у нас есть список номенклатурных номеров товаров, к ним необходимо подтянуть названия из таблицы с номенклатурными номерами;

Синтаксис: =VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup)

Lookup_value — искомое значение — то, что мы ищем, в примере — конкретный номенклатурный номер.

Table_array — таблица, в которой мы будет искать совпадение. Что важно: первая колонка таблицы — это колонка, в которой мы будем искать lookup_value. Таблица может состоять из нескольких колонок, которые должны включать в себя ту, из которой мы будем брать значение. То есть для примера диапазон должен состоять из нескольких колонок, первой из которых будет номенклатурный номер, а последней — название товара.

Col_index_num — номер колонки в таблице Table_array. Первой колонкой будет колонка, в которой производится поиск. Второй — следующая, справа от нее. И так далее.

Range_lookup — искать точное совпадение или нет? Поставить FALSE — ЛОЖЬ — 0 для поиска точного совпадение или TRUE — ИСТИНА — 1 для неточного совпадения. На практике обычно используется точный поиск — не могу представить, для чего может потребоваться приблизительный поиск.

Предлагаю разобрать эту формулу на примере.

Дано:

Две таблицы. В первой есть код, к нему необходимо подтянуть наименование из второй таблицы. Вторая таблица содержит две колонки — код и наименование.

Решение:

Во второй колонке (где мы хотим получить наименование) прописываем следующую формулу:

=vlookup(A6;D:E;2;0)

(1) Что ищем — A6 — то есть код «1005».

(2) Где ищем (первая колонка должна быть с кодами, последняя — с искомым значением или после него) — D:E — то есть колонки от D до E — то есть всю таблицу, начиная с кодов и по наименование.

(3) Из какой колонки берем значение — 2. Считаем колонки: 1 — код, 2 — наименование. Посколько нам нужно наименование, то ставим значение 2.

(4) искать точное совпадение или нет? Да — ставим 0 (с таким же успехом можно было поставить FALSE или ЛОЖЬ, если вам не повезло работать в русском экселе).

Как работает:

Программа перебирает значение в колонке D и проверяет, совпадает ли оно с искомым значением «1005».

D1 — «Таблица с данными» не равно «1005» — пропускаем.

D2 — «» не равно «1005» — пропускаем.

D3 — «Код» не равно «1005» — пропускаем.

D4 — «1001» не равно «1005» — пропускаем.

D5 — «1002» не равно «1005» — пропускаем.

D6 — «1003» не равно «1005» — пропускаем.

D7 — «1004» не равно «1005» — пропускаем.

D8 — «1005» равно «1005» — отсчитываем вторую колонку (первая — D, вторая — E). Из той же строчки, где нашли значение «1005» из второй колонки (E) берем значение. Это «персик». Дальше не ищем.

То есть в ячейке B6 получится значение «персик».

Есть несколько важных вещей, на которые стоит обратить внимание, если вы собираетесь эффективно пользоваться формулой.

(1) Формула действительно перебирает все значения в колонке D. Если массив большой (а зачастую это может быть до миллиона значений), это может занимать какое-то время. Поэтому при использовании формулы стоит задумываться об оптимизации производительности. Об этом в одной из следующих статей.

(2) Формула выдает результат, соответствующий первому совпадению. Если один и тот же код встречается несколько раз и разными наименованиями, то формула во всех случаях будет выдавать значение, соответствующее первому совпадению.

(3) Искомое значение ОБЯЗАТЕЛЬНО должно быть в ПЕРВОЙ колонке таблицы. Если изначальная таблица, в которой вы собираетесь искать значение имеет колонку с результатом левее, чем та, по которой вы собираетесь искать, то одну из колонок придется переставить.

(4) Искомое значение должно быть в одном формате в таблице, где мы указываем, что искать, и в таблице, где мы ищем. Это самая частая проблема, с которой сталкиваются пользователи формулы vlookup. Предположим, что код 1005 в первой таблице представлен в формате числа, а во второй — в текстовом формате (это может случиться, например, при выгрузке номенклатуры из 1С или SAP). Формула не будет находить значение и будет выдавать ошибку #N/A, хотя поиск (CTRL + F) будет находить это значение. Для того чтобы решить эту проблему можно сделать одну из двух вещей: привести их к одному формату с помощью формул VALUE или TEXT (об этом в другой статье), или изменить формат. Обратите внимание, что если число записано как текст, то недостаточно просто изменить формат ячейки на числовой. Потребуется также зайти в каждую ячейку со значением и нажать Enter (применить изменение). Это не вариант, если у вас номенклатура, которая состоит за нескольких десятков тысяч наименований. Но как всегда есть обходной путь. В этом случае это функционал Найти и заменить — Find and replace (Ctrl + H). Обычно номенклатурный номер начинается с одной и той же цифры (в моем примере — с 1). Выделяем колонку, в которой необходимо изменить формат, и заменяем эту цифру на нее же (1 на 1). Формат изменится.

Hlookup — формула абсолютно аналогичная vlookup, только данные во второй таблице должны быть представлены не в колонках, а в строках и, соответственно, третий параметр — не номер колонки, а номер строки, из которой необходимо брать значение.

Эти и многие другие формулы описаны в книге Формулы в Microsoft Excel 2010. Книга — наш лучший помощник в обучении — почему бы не иметь хорошую книгу дома или на работе.

Рубрики
Формулы

Русские аналоги английских формул для MS Excel 2007

Какой офис стоит у вас на работе? А какой дома? Проблемы с тем, что не можете найти дома русский аналог для функции vlookup, sumifs или даже irr? Это не должно быть проблемой для файлов, скопированных с работы и открываемых дома и наоборот. Эксель сам преобразует формулы в нужные на другом языке. А вот если вы пишете новую таблицу, это может стать серьезной проблемой.

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

Навскидку, единственная формула, которой я часто пользуюсь, но не нашел в этом списке – IFERROR (ЕСЛИОШИБКА).

Рубрики
Формулы

Как перевести дату из текстового формата в формат даты

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

Я реализовал перевод с помощью формулы. Вопрос немножко усложнялся еще тем фактом, что дата была представлена в нестандартном формате, а именно «М/Д/ГГГГ». Предлагаю вашему вниманию приложенный файл с реализацией данной формулы.

Она получилась немножко замухренной и работатет только при допущении, что в тексте с датой нет пробелов (в случае с заполнением данных программой проблем быть не должно, если нет, то лучше выделить диапазон данных с датами, нажать «Найти и заменить» и заменить символ пробела на пустую строку — так мы точно обезопасим себя от ошибок формата).

Подробный разбор данной формулы представлен в статье Как читать сложные формулы.

Рубрики
Формулы

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

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

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

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

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

Дано: дата в текстовом формате в виде «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) необходимое нам количество символов. Остальное читаем изнутри.

Со строкой, из которой брать символы, все понятно – это наши входные данные.
SEARCH(«/»;date_input_4) – это номер символа “/”. Соответственно, SEARCH(«/»;date_input_4)+1 – это номер первого символа после “/”.
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, что и требовалось.

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

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