Функция ЕСЛИ в Excel с примерами
Сегодня мы рассмотрим функцию ЕСЛИ.
Функция ЕСЛИ часто используется в Excel для решения многих задач. Знать ее очень полезно. В данной статье мы попробуем рассказать про ее работу на простых примерах, достаточно один раз разобраться с конструкцией функцией ЕСЛИ и вы сможете применять ее и в самых сложных вариантах.
Если вам требуется использовать функцию ЕСЛИ несколько раз в одной формуле, то используйте статью про вложенные ЕСЛИ.
Если вам необходима проверка условий большого количества однотипных ЕСЛИ, то возможно вам будет полезна статья про альтернативный способ с использованием ВПР.
Функция ЕСЛИ проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет.
Синтаксис функции ЕСЛИ очень простой:
ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь])
лог_выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.
Что это значит? Выражение при вычислении дает значение ИСТИНА если это выражение верно.
В этой части необходимо проверить на соответствие выражения.
Например:
=ЕСЛИ(А1=10; [значение_если_истина]; [значение_если_ложь]) — если А1 равно 10, то выражение А1=10 даст значение ИСТИНА, а если не равно 10, то ЛОЖЬ
Другой пример
=ЕСЛИ(А1>30; [значение_если_истина]; [значение_если_ложь]) — если в ячейки А1 число больше 30, то А1>30 вернет ИСТИНА, а если меньше, то ЛОЖЬ
Еще пример
=ЕСЛИ(С1=”Да”; [значение_если_истина]; [значение_если_ложь]) — если в ячейки C1 содержится слово “Да” то выражение вернет значение ИСТИНА, а если нет, то С1=”Да” вернет ЛОЖЬ
Надеюсь с этим понятно, поехали дальше. Рассмотрим следующие компоненты функции ЕСЛИ
=ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь])
значение_если_истина, значение_если_ложь – как видно из их названия, это то что необходимо сделать в зависимости от того, что вернул лог выражения: ИСТИНА и ЛОЖЬ
Пример использования функции ЕСЛИ в Excel
Рассмотрим использование функции ЕСЛИ на практическом примере.
У нас есть таблица заказов, которую мы использовали при рассмотрении работы функции ВПР. Нам необходимо заполнить столбец по заказам Ведер (ошибочно на картинке указано «Заказы Cтолов»), то есть необходимо выбрать только заказы с Ведрами. Это можно сделать различными способами, но мы с вами будет использовать функцию ЕСЛИ, чтобы показать ее работу на примере. (см.рисунок)
Для решения поставленной задачи напишем формулу с использованием функции ЕСЛИ
=ЕСЛИ(A3="Ведро";D3;"-")
и нажмем Enter
Как вы смогли заметить аргументы функции ЕСЛИ разделены точкой с запятой.
Итак, первый аргумент (лог выражения) A3=»Ведро» проверяет содержится ли в ячейке А3 слово «Ведро», если содержится, то выполняется второй аргумент функции ЕСЛИ (значение_если_истина), в нашем случае это D3 (т.е стоимость заказа), если в ячейка А3 не равна слову «Ведро», то выполняется третий аргумент функции ЕСЛИ (значение_если_ложь), в нашем случае это «-» ( т.е будет написано тире).
Таким образом, в ячейки E3 появится значение D3, т.е число 240.
Далее необходимо протянуть формулу вниз для остальных значений. Для этого необходимо выделить ячейку Е3 и потянуть вниз за нижний правый угол (смотрите рисунок 3)
Итоговый результат работы функции ЕСЛИ вы можете посмотреть на рисунке 4. Функцию ЕСЛИ можно использовать в очень многих ситуациях. Находить ошибки, находить уникальные значения в списке, использовать многократные проверки на выполнение условий. Разбор всех этих приемов Excel ждет вас впереди.
Если статья была Вам полезна, то будем благодарны, если Вы поделитесь ей со своими друзьями с помощью кнопок расположенных ниже.
Спасибо за внимание.
Как подставить значение в Excel
Виктор Бухтеев
2.8K
Обсудить
Личный опыт #Программы #Microsoft
6 мин.
чтения
Подстановка значений в Microsoft Excel подразумевает замену одной части текста на другую с выводом результата в отдельной ячейке. Это избавит вас от необходимости самостоятельно вносить одни и те же коррективы в большое количество полей. Осуществляется подобная манипуляция с использованием всего одной функции, но далее я расскажу еще и о ее модернизации для тех, кто исправляет ошибочный импорт, переводя текстовые значения в числовые.
Когда это может понадобиться?
Отвечу на самый главный вопрос по теме: «Когда может понадобиться подстановка значений?». Выполнение этой операции в первую очередь подразумевает замену любых символов. Например, вы импортировали диапазон значений из другой программы, где для разделения дробной части вместо запятой используется точка. Соответственно, Excel подумает, что это текстовые значения, и откажется использовать их при создании функций.
Вы можете исправить все это вручную, удаляя старые знаки и добавляя новые, после чего проверить настройки формата каждой ячейки.
Однако это делается долго и неудобно. Проще создать новый столбец с использованием функций. ПОДСТАВИТЬ и ЗНАЧЕН. Так вы создадите столбец с правильными данными, которые можно использовать для любых целей, включая копирование и удаление всего лишнего.
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Подписаться
Использование функции ПОДСТАВИТЬ
Для начала разберемся с тем, как работает синтаксис основной функции – ПОДСТАВИТЬ. Объявим ее и рассмотрим каждый аргумент, чтобы при дальнейшем использовании у вас не возникло никаких проблем.
-
Для начала выберите необходимую клетку, нажмите по ней левой кнопкой мыши для активации и объявите функцию =ПОДСТАВИТЬ().
-
В скобках напишите номер ячейки, с которой нужно взять исходный текст.
-
Поставьте точку с запятой для отделения аргументов, в кавычках напишите символ или несколько символов, от которых хотите избавиться.
-
Снова отделите аргумент и в кавычках напишите новый символ для замены.
-
Это вся формула, поэтому можете нажать Enter и посмотреть, что получилось в итоге.
Для наглядности оставляю используемую мной формулу целиком, чтобы вы могли редактировать ее под себя и не вводить каждый символ вручную. Это поможет значительно сэкономить время.
=ПОДСТАВИТЬ(A2;".";",")
Добавление функции ЗНАЧЕН
Теперь модернизируем формулу, чтобы в итоге получить числовое значение, а не текст, как в исходном варианте. Для этого понадобится функция ЗНАЧЕН, которую просто нужно вписать в строку, поставив дополнительные кавычки.
-
Выполните редактирование, чтобы в итоге получить примерно такой результат: =ЗНАЧЕН(ПОДСТАВИТЬ(A2;».»;»,»)).
-
Нажмите Enter для применения изменений и обратите внимание на то, что теперь числа отображаются справа в ячейке, что говорит об успешной смене формата с текстового на числовой.
-
Остается только растянуть формулу на все остальные клетки, чтобы завершить замену в массиве данных. Зажмите правый нижний угол ячейки и потяните вниз.
-
Посмотрите на таблицу и удалите лишнюю информацию, которая после преобразования больше не понадобится.
Работа с массивами данных в Microsoft Excel часто заставляет юзера вносить правки в текст, что особенно актуально при импорте дробных чисел. Теперь вы узнали, что решить проблему можно при помощи всего одной формулы, состоящей из двух функций. Используйте это, чтобы оптимизировать взаимодействие с электронной таблицей.
Личный опыт
Наши постоянные авторы и читатели делятся лайфхаками, основанными на личном опыте. Полная свобода самовыражения.
Рекомендуем
Как рассчитать PV в Excel
Приведенная стоимость (PV) — это текущая стоимость ожидаемого будущего потока денежных средств.
Формула для расчета PV в Excel: = PV(ставка, nper, pmt, [fv], [type]).
Ключевые выводы
- Приведенная стоимость (ТС) — это текущая стоимость потока денежных потоков.
- Анализ PV используется для оценки ряда активов, от акций и облигаций до недвижимости и аннуитетов.
- PV можно рассчитать в Excel по формуле =PV(ставка, nper, pmt, [fv], [type]).
- Если FV опущен, необходимо включить PMT или наоборот, но можно включить и то, и другое.
- NPV отличается от PV тем, что учитывает первоначальную сумму инвестиций.
Формула для PV в Excel
Опять же, формула для расчета PV в Excel:
=PV(коэффициент, nper, pmt, [fv], [type]).
Входные данные для формулы приведенной стоимости (PV) в Excel включают следующее:
- СТАВКА = Процентная ставка за период
- КПЕР = Количество периодов платежей
- ПЛТ = Сумма, выплачиваемая за каждый период (если опущено — предполагается, что она равна 0 и должна быть включена БС)
- [БС] = Будущая стоимость инвестиции (если опущен — предполагается, что он равен 0, и должен быть включен ПЛТ)
- [ТИП] = Когда производятся платежи (0, или если опущен — считается, что в конце периода, или 1 — предполагается, что в начало периода)
Некоторые ключевые моменты, которые следует помнить для формул PV, — это то, что любые выплаченные деньги (оттоки) должны быть отрицательными числами.
Деньги в (приток) являются положительными числами.
Формула NPV и PV в Excel
Хотя вы можете рассчитать PV в Excel, вы также можете рассчитать чистую приведенную стоимость (NPV). Текущая стоимость представляет собой дисконтированные будущие денежные потоки. Чистая приведенная стоимость представляет собой разницу между ТС денежных потоков и ТС оттоков денежных средств.
Большая разница между PV и NPV заключается в том, что NPV учитывает первоначальные инвестиции. Формула NPV для Excel использует ставку дисконтирования и ряд денежных оттоков и притоков.
Ключевые различия между NPV и PV:
- Формула PV в Excel может использоваться только с постоянными денежными потоками, которые не меняются.
- NPV можно использовать с переменными денежными потоками.
- PV можно использовать для регулярных аннуитетов (платежи в конце периода) и аннуитетов к оплате (платежи в начале периода).
- Чистая приведенная стоимость может использоваться только для платежей или денежных потоков на конец периода.
Пример формулы PV в Excel
Если вы ожидаете, что через 10 лет на вашем банковском счете будет 50 000 долларов при процентной ставке 5%, вы можете рассчитать сумму, которую нужно инвестировать сегодня для достижения этой цели.
Вы можете пометить ячейку A1 в Excel «Годы». Кроме того, в ячейке B1 введите количество лет (в данном случае 10). Пометьте ячейку A2 «Процентная ставка» и введите 5% в ячейку B2 (0,05). Теперь в ячейке A3 назовите ее «Будущая стоимость» и поместите 50 000 долларов в ячейку B3.
Встроенная функция PV может легко рассчитать текущую стоимость с помощью данной информации. Введите «текущая стоимость» в ячейку A4, а затем введите формулу PV в ячейку B4, = PV (ставка, кпер, pmt, [fv], [type], которая в нашем примере равна «= PV (B2, B1, 0,В3)».
Поскольку промежуточных платежей нет, в качестве аргумента «ПЛТ» используется 0. Текущая стоимость рассчитывается как (30 695,66 долларов США), поскольку вам нужно будет положить эту сумму на свой счет; это считается оттоком денежных средств и поэтому отображается как отрицательное.
Особые указания
Для формулы PV в Excel, если процентная ставка и сумма платежа основаны на разных периодах, необходимо внести коррективы. Популярным изменением, которое необходимо для того, чтобы формула PV в Excel работала, является изменение годовой процентной ставки на ставку за период. Это делается путем деления годовой ставки на количество периодов в году.
Например, если ваш платеж по формуле PV производится ежемесячно, вам нужно преобразовать годовую процентную ставку в месячную, разделив ее на 12. Кроме того, для КПЕР, который представляет собой количество периодов, если вы собираете аннуитетный платеж ежемесячно в течение четырех лет, КПЕР равен 12, умноженным на 4, или 48.
В чем разница между текущей стоимостью (PV) и будущей стоимостью (FV)?
Приведенная стоимость использует временную стоимость денег для дисконтирования будущих сумм денег или денежных потоков до их сегодняшней стоимости.
Это связано с тем, что деньги сегодня имеют большую покупательную способность, чем та же сумма денег в будущем. Используя ту же логику в другом направлении, будущая стоимость (FV) берет стоимость денег сегодня и прогнозирует, какой будет их покупательная способность в какой-то момент в будущем.
Почему важна текущая стоимость?
Приведенная стоимость важна для оценки активов или инвестиций сегодня, которые будут проданы в будущем или которые принесут прибыль или денежные потоки, которые будут выплачены в будущем. Поскольку транзакции происходят в настоящем, эти будущие денежные потоки или доходы необходимо рассматривать, но с использованием стоимости сегодняшних денег.
Когда вам может понадобиться рассчитать текущую стоимость?
Расчет приведенной стоимости довольно распространен. Любой актив, по которому выплачиваются проценты, например облигация, аннуитет, аренда или недвижимость, будет оцениваться с использованием его чистой приведенной стоимости. Акции также часто оцениваются на основе приведенной стоимости их будущей прибыли или потоков дивидендов с использованием анализа дисконтированных денежных потоков (DCF).
Две функции, которые вы ДОЛЖНЫ знать для возврата значений из таблиц Excel
«Экспорт в Excel — третья по распространенности кнопка в приложениях бизнес-аналитики… после OK и Cancel».
— Роб Колли, один из инженеров-основателей PowerPivot в Microsoft, а ныне руководитель PowerPivotPro.com.
Правдива или нет шутка Роба — а он говорит, что это, скорее всего, правда — она иллюстрирует постоянную проблему: как пользователям Excel лучше всего организовывать бизнес-данные и составлять отчеты в Excel?
В моей статье «Знакомство с базами данных, дружественными к Excel» объясняются четыре основных способа хранения данных в виде базы данных Excel. А в Как настроить сводную таблицу как базу данных, удобную для Excel, я показал, как это сделать с помощью сводных таблиц.
Теперь пришло время сделать следующий шаг: вернуть данные из таблиц Excel и других баз данных Excel в ваши отчеты и анализы.
Вы будете использовать две ключевые функции для возврата значений из таблиц Excel и других баз данных: СУММЕСЛИМН и СУММПРОИЗВ.
Функция рабочего листа СУММЕСЛИМН
Функция СУММЕСЛИ уже давно используется в Excel. Но у СУММЕСЛИ была серьезная проблема: она могла вернуть СУММ на основе только одного критерия. Итак, в Excel 2007 Microsoft представила функцию СУММЕСЛИМН, которая может использовать любое количество критериев.
Функции упорядочивают свои аргументы по-разному:
- =СУММЕСЛИ(диапазон_критериев, диапазон_сумм)
- =СУММЕСЛИМН(диапазон_суммы, диапазон_критериев,критерий,…)
Функция СУММЕСЛИМН может иметь любое количество наборов диапазонов_критериев и критериев. (Возможно, существует верхний предел, но если он и есть, я сомневаюсь, что вы когда-нибудь его достигнете.)
Во избежание путаницы я рекомендую полностью отказаться от использования функции СУММЕСЛИ, даже если вам нужно использовать только один критерий. Таким образом, вы привыкнете к использованию СУММЕСЛИМН и всегда будете знать правильную последовательность аргументов в своей функции.
Функция рабочего листа СУММПРОИЗВ
Официально функция СУММПРОИЗВ имеет следующие аргументы:
=СУММПРОИЗВ(массив1, массив2, массив3,…)
Однако вам, вероятно, будет проще использовать ее следующим образом:
=СУММПРОИЗВ(массив1*массив2*массив3)
Например, если вы хотите найти общее значение всех черных шляп в ваших данных, вы можете использовать следующую формулу:
=СУММПРОИЗВ(Суммы* (Products=”Hats”)*(Colors=”Black”))
(Примечание. Если вы скопируете формулы с кавычками из этого сообщения блога на свой лист, вы получите сообщение об ошибке, поскольку HTML обычно использует другой символ для кавычки, чем в Excel. Поэтому в Excel вам нужно заменить кавычки в формуле стандартным символом кавычки.)
В формуле Количества – это столбец значений в вашей таблице продуктов и цветов продуктов, а Товары – это столбец продуктов, в который входят шляпы.
Следующая часть формулы, (Products=”Hats”) , сначала преобразуется в столбец ИСТИНА, где продукт равен шляпам в определенных ячейках, и ЛОЖЬ, когда продукт не равен шляпам в других ячейках.
Затем, когда вы умножаете этот второй массив на столбец Amounts , значения TRUE и FALSE работают так, как если бы они были столбцом значений 1 (один) и 0 (ноль). Таким образом, результатом умножения является массив значений, где произведение равно шляпам, и нулей, где произведение не равно шляпам.
Затем, когда вы умножаете на массив (Цвета = «Черный») , единственным ненулевым значением в конечном массиве является то, где исходное значение в столбце «Суммы» было ненулевым, а «Продукт» — это шляпа, а Цвет – черный.
Наконец, функция СУММПРОИЗВ добавляет этот массив результатов, давая вам общие значения для всех черных шляп.
СУММЕСЛИМН против СУММПРОИЗВ, что следует использовать?
Если в формуле можно использовать обе функции, я рекомендую вам использовать СУММЕСЛИМН, так как он вычисляет быстрее и использует меньше памяти.
Но есть еще одна причина для использования СУММЕСЛИМН: он принимает подстановочные знаки в значениях критериев. Например, критерий «h*» принимает все элементы, начинающиеся с буквы «h». (СУММЕСЛИМН игнорирует регистр в своих критериях.)
Однако функция СУММПРОИЗВ предлагает значительное преимущество: может использовать вычисления.
Например, предположим, что у вас есть таблица продаж по датам, и вы хотите найти средние продажи по средам. Вы не можете использовать СУММЕСЛИМН или СРЗНАЧЕСЛИМН для этого вычисления, потому что в вашей таблице нет столбца, указывающего день недели. Но вы можете использовать СУММПРОИЗВ следующим образом:
=СУММПРОИЗВ(Сумма*(ДЕНЬНЕД(ДатаВремя)=4))
/СУММПРОИЗВ((ДЕНЬНЕД(ДатаВремя)=4)*1)
(Хотя я заключил эту формулу в две строки, на самом деле ее нужно ввести в одну длинную строку.)
Первая строка формулы возвращает сумму всех продаж в среду.
Вторая строка возвращает количество найденных сред. Он делает это путем создания столбца значений ИСТИНА и ЛОЖЬ, которые преобразуются в единицы и нули, когда мы умножаем на 1.
(Добавление нуля сделало бы то же самое.) Затем вторая строка находит число сред, складывая все числа. те в колонке.
Наконец, формула делит первый результат (сумма продаж по средам) на второй (количество сред с продажами), чтобы получить среднее значение продаж по средам.
Обратите внимание, что некоторые функции нельзя использовать в функции СУММПРОИЗВ, но вы можете использовать большинство из них.
Примеры СУММЕСЛИМН и СУММПРОИЗВ
Возвратим данные из этой таблицы Excel с именем Продажи.
Для первых нескольких примеров я настроил четыре ячейки с именами Product, Color, StartDate и EndDate.
Кроме того, как и выше, я перенесу большинство формул в несколько строк, чтобы они поместились на странице. Но вы, конечно, введете их в одну строку.
Предположим, ячейка «Продукт» содержит текст «Галстуки», а ячейка «Цвет» — «Черный». Обе эти формулы вернут 9, общее количество для всех Black Ties:
=СУММЕСЛИМН(Продажи[Суммы], Продажи[Продукты],Продукт, Продажи[Цвета],Цвет)
=СУММПРОИЗВ(Продажи[Суммы]*( Sales[Products]=Product)*(Sales[Colors]=Color))
Предположим, ячейка StartDate содержит дату 01.
=СУММЕСЛИМН(Продажи[Суммы], Продажи[ДатаВремя]»,>=»&ДатаНачала, Продажи[ДатаВремя]»,<"&КонечнаяДата)
=СУММПРОИЗВ(Продажи[Суммы]*(Продажи[ДатаВремя]>=ДатаНачала) * Теперь давайте объединим эти наборы формул, чтобы получить общее количество всех галстуков Black ties, проданных в январе: [Цвета],Цвет, Продажи[ДатаВремя]»,>=»&ДатаНачала, Продажи[ДатаВремя]»,<"&КонечнаяДата) =СУММПРОИЗВ(Продажи[Суммы]*(Продажи[Продукты]=Продукт)* (я снова обернул эти формулы, но на самом деле вы должны ввести каждую из них одним длинным line.) Предположим, по какой-то причине нам нужно получить сумму всех продуктов, названия которых заканчиваются на «ts». =СУММЕСЛИМН(Продажи[Объемы], Продажи[Продукты],Продукт2) С СУММПРОИЗВ мы можем использовать строковые функции для возврата одного и того же результата.
(Sales[DateTimes]
(Sales[Colors]=Color)*(Sales[DateTimes]>=StartDate)*
(Sales[DateTimes]
С СУММЕСЛИМН мы могли бы использовать подстановочные знаки. Здесь ячейка с именем Продукт2 содержит текст «*ts» (то есть у нас есть звездочка, *, за которой следует «ts»):