Позвольте начать эту статью с нескольких очевидных предпосылок:
во-первых, в эксель встроено несколько функций, которые выдают своим результатом TRUE (ИСТИНА) или FALSE (ЛОЖЬ),
во-вторых, TRUE (ИСТИНА) математически равно единице, а FALSE (ЛОЖЬ) — нулю,
в-третьих, эти значения можно использовать в вычислениях,
и последнее, чтобы получить значения TRUE (ИСТИНА) или FALSE (ЛОЖЬ), необязательно прибегать к помощи функций, достаточно сравнить два значения.
Так, например, такая формула многим может показаться дикой, но в экселе она работает: «=(2*2)=5». Соответственно, она будет выдавать результатом FALSE (ЛОЖЬ). Вместо цифр можно подставлять ссылки на другие ячейки. Для чего это может потребоваться и как это использовать. Позвольте сделать для вас пару иллюстрированных примеров.
Пример первый. Взять значение если выполняется условие
Предположим, что у нас есть список транзакций, часть из которых носит характер прямых затрат, а часть — косвенных. Надо разделить эти затраты на две колонки.
В этом примере колонки A:D — даны, E:F — расчетные.
Формула, которая видна в строке формул — как раз-таки делает расчет для ячеек в колонках E:F. Давайте разберем, как она работает.
Выражение $D:$D=$1:$1 сравнивает значение в колонке D со значением в строке 1. Если оно совпадает, то формула выдает TRUE (ИСТИНА или единица), если нет — FALSE (ЛОЖЬ или ноль). Если умножить ее на сумму из колонки C, то мы получим сумму при совпадении вышеуказанных столбца и строки.
Пример второй. Значения с разным параметром обрабатываются по-разному
Давайте усложним предыдущий пример. Предположим, нам надо рассчитать сумму отнесенных затрат: если затраты прямые — берем всю сумму, если косвенные — только 30% от этой суммы.
Здесь используются два взаимоисключающих условия (значение в колонке D не может быть равно и «Прям» и «Косв» для одной строки), поэтому задвоения затрат не произойдет. Первая часть формулы проверяет на то, являются ли затраты прямыми: если да, то берет всю сумму. Вторая часть, соответственно, проверяет, являются ли они косвенными: если да, то она берет 30% от суммы.
Обратите внимание, что формула разбита на две строки. Это сделано, чтобы было удобнее ее читать. Подробнее о чтении сложных формул — в этой статье.
Пример третий. Взять значение, если есть совпадение в другом списке
Предположим, что у нас где-то отдельно есть список счетов, которые мы хотим взять в учет для дальнейших расчетов. Обычно такой счет будет на отдельном листе рабочей книги. Но для примера я его вставил на тот же лист. Пожалуйста, в работе не делайте так, старайтесь по жизни руководствоваться принципом «Один лист — одна таблица!». Всегда!
Небольшая вводная по использованным функциям. VLOOKUP (ВПР) — ищет значение соответствующее искомому в заданном диапазоне (подробнее об использовании этой функции читайте в этой статье). Она выдает значение или ошибку #N/A, если совпадения нет.
Функция ISNA выдает TRUE (ИСТИНА), если параметр равен #N/A, FALSE (ЛОЖЬ) в обратном случае.
Функция NOT делает из TRUE — FALSE и наоборот.
Таким образом, NOT(ISNA(VLOOKUP(B:B;F:F;1;FALSE))) проверяет, есть ли в колонке F значение из колонки B: если есть, она выдает TRUE, если нет — FALSE. А если это умножить на сумму, то в ячейке будет сумма, только если счет есть в «другом списке».
Если у вас остались вопросы по этой теме или есть вопрос по любой другой, пожалуйста, задавайте их в комментариях, на страницах наших групп ВКонтакте или на Facebook или присылайте их мне на E-mail edward@youcanexcel.ru.
