5 основ Excel (обучение): как написать формулу, как посчитать сумму, сложение с условием, счет строк и пр

5 основ Excel (обучение): как написать формулу, как посчитать сумму, сложение с условием, счет строк и пр.

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

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

То есть эта статья будет что-то мини гайда по обучению самому нужному для работы (точнее, чтобы начать пользоваться Excel и почувствовать всю мощь этого продукта!) .

Возможно, что прочти подобную статью лет 17-20 назад, я бы сам намного быстрее начал пользоваться Excel (и сэкономил бы кучу своего времени для решения «простых» задач. 👌

Обучение основам Excel: ячейки и числа

Примечание : все скриншоты ниже представлены из программы Excel 2016 (как одной из самой новой на сегодняшний день).

Многие начинающие пользователи, после запуска Excel — задают один странный вопрос: «ну и где тут таблица?». Между тем, все клеточки, что вы видите после запуска программы — это и есть одна большая таблица!

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

слева : в ячейке (A1) написано простое число «6». Обратите внимание, когда вы выбираете эту ячейку, то в строке формулы (Fx) показывается просто число «6».
справа : в ячейке (C1) с виду тоже простое число «6», но если выбрать эту ячейку, то вы увидите формулу «=3+3» — это и есть важная фишка в Excel!

Просто число (слева) и посчитанная формула (справа)

👉 Суть в том, что Excel может считать как калькулятор, если выбрать какую нибудь ячейку, а потом написать формулу, например «=3+5+8» (без кавычек). Результат вам писать не нужно — Excel посчитает его сам и отобразит в ячейке (как в ячейке C1 в примере выше)!

Но писать в формулы и складывать можно не просто числа, но и числа, уже посчитанные в других ячейках. На скриншоте ниже в ячейке A1 и B1 числа 5 и 6 соответственно. В ячейке D1 я хочу получить их сумму — можно написать формулу двумя способами:

первый: «=5+6» (не совсем удобно, представьте, что в ячейке A1 — у нас число тоже считается по какой-нибудь другой формуле и оно меняется. Не будете же вы подставлять вместо 5 каждый раз заново число?!);
второй: «=A1+B1» — а вот это идеальный вариант, просто складываем значение ячеек A1 и B1 (несмотря даже какие числа в них!).

Сложение ячеек, в которых уже есть числа

Распространение формулы на другие ячейки

В примере выше мы сложили два числа в столбце A и B в первой строке. Но строк то у нас 6, и чаще всего в реальных задачах сложить числа нужно в каждой строке! Чтобы это сделать, можно:

в строке 2 написать формулу «=A2+B2» , в строке 3 — «=A3+B3» и т.д. (это долго и утомительно, этот вариант никогда не используют) ;
выбрать ячейку D1 (в которой уже есть формула) , затем подвести указатель мышки к правому уголку ячейки, чтобы появился черный крестик (см. скрин ниже) . Затем зажать левую кнопку и растянуть формулу на весь столбец. Удобно и быстро! ( Примечание : так же можно использовать для формул комбинации Ctrl+C и Ctrl+V (скопировать и вставить соответственно)) .

Кстати, обратите внимание на то, что Excel сам подставил формулы в каждую строку. То есть, если сейчас вы выберите ячейку, скажем, D2 — то увидите формулу «=A2+B2» (т.е. Excel автоматически подставляет формулы и сразу же выдает результат) .

Как задать константу (ячейку, которая не будет меняться при копировании формулы)

Довольно часто требуется в формулах (когда вы их копируете), чтобы какой-нибудь значение не менялось. Скажем простая задача: перевести цены в долларах в рубли. Стоимость рубля задается в одной ячейке, в моем примере ниже — это G2.

Далее в ячейке E2 пишется формула «=D2*G2» и получаем результат. Только вот если растянуть формулу, как мы это делали до этого, в других строках результата мы не увидим, т.к. Excel в строку 3 поставит формулу «D3*G3», в 4-ю строку: «D4*G4» и т.д. Надо же, чтобы G2 везде оставалась G2.

Чтобы это сделать — просто измените ячейку E2 — формула будет иметь вид «=D2*$G$2». Т.е. значок доллара $ — позволяет задавать ячейку, которая не будет меняться, когда вы будете копировать формулу (т.е. получаем константу, пример ниже) .

Константа / в формуле ячейка не изменяется

Читайте так же:

Массив интервалов в excel

Как посчитать сумму (формулы СУММ и СУММЕСЛИМН)

Можно, конечно, составлять формулы в ручном режиме, печатая «=A1+B1+C1» и т.п. Но в Excel есть более быстрые и удобные инструменты.

Один из самых простых способов сложить все выделенные ячейки — это использовать опцию автосуммы (Excel сам напишет формулу и вставить ее в ячейку) .

Что нужно сделать, чтобы посчитать сумму определенных ячеек:

сначала выделяем ячейки (см. скрин ниже 👇) ;
далее открываем раздел «Формулы» ;
следующий шаг жмем кнопку «Автосумма» . Под выделенными вами ячейками появиться результат из сложения;
если выделить ячейку с результатом (в моем случае — это ячейка E8) — то вы увидите формулу «=СУММ(E2:E7)» .
таким образом, написав формулу «=СУММ(xx)» , где вместо xx поставить (или выделить) любые ячейки, можно считать самые разнообразные диапазоны ячеек, столбцов, строк.

Автосумма выделенных ячеек

Как посчитать сумму с каким-нибудь условием

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

Я в своей таблицы буду использовать всего 7 строк (для наглядности) , реальная же таблица может быть намного больше. Предположим, нам нужно посчитать всю прибыль, которую сделал «Саша». Как будет выглядеть формула:

» =СУММЕСЛИМН( F2:F7 ; A2:A7 ;»Саша») » — ( прим .: обратите внимание на кавычки для условия — они должны быть как на скрине ниже, а не как у меня сейчас написано на блоге) . Так же обратите внимание, что Excel при вбивании начала формулы (к примеру «СУММ. «), сам подсказывает и подставляет возможные варианты — а формул в Excel’e сотни!;
F2:F7 — это диапазон, по которому будут складываться (суммироваться) числа из ячеек;
A2:A7 — это столбик, по которому будет проверяться наше условие;
«Саша» — это условие, те строки, в которых в столбце A будет «Саша» будут сложены (обратите внимание на показательный скриншот ниже) .

Сумма с условием

Примечание : условий может быть несколько и проверять их можно по разным столбцам.

Как посчитать количество строк (с одним, двумя и более условием)

Довольно типичная задача: посчитать не сумму в ячейках, а количество строк, удовлетворяющих какомe-либо условию.

Ну, например, сколько раз имя «Саша» встречается в таблице ниже (см. скриншот). Очевидно, что 2 раза (но это потому, что таблица слишком маленькая и взята в качестве наглядного примера). А как это посчитать формулой?

«=СЧЁТЕСЛИ( A2:A7 ; A2 )» — где:

A2:A7 — диапазон, в котором будут проверяться и считаться строки;
A2 — задается условие (обратите внимание, что можно было написать условие вида «Саша», а можно просто указать ячейку).

Результат показан в правой части на скрине ниже.

Количество строк с одним условием

Теперь представьте более расширенную задачу: нужно посчитать строки, где встречается имя «Саша», и где в столбце «B» будет стоять цифра «6». Забегая вперед, скажу, что такая строка всего лишь одна (скрин с примером ниже) .

Формула будет иметь вид:

=СЧЁТЕСЛИМН( A2:A7 ; A2 ; B2:B7 ;»6″) — (прим.: обратите внимание на кавычки — они должны быть как на скрине ниже, а не как у меня) , где:

A2:A7 ; A2 — первый диапазон и условие для поиска (аналогично примеру выше);

B2:B7 ;»6″ — второй диапазон и условие для поиска (обратите внимание, что условие можно задавать по разному: либо указывать ячейку, либо просто написано в кавычках текст/число).

Счет строк с двумя и более условиями

Как посчитать процент от суммы

Тоже довольно распространенный вопрос, с которым часто сталкиваюсь. Вообще, насколько я себе представляю, возникает он чаще всего — из-за того, что люди путаются и не знают, что от чего ищут процент (да и вообще, плохо понимают тему процентов (хотя я и сам не большой математик, и все таки. ☝) ).

👉 В помощь!
Как посчитать проценты: от числа, от суммы чисел и др. [в уме, на калькуляторе и с помощью Excel] — заметка для начинающих

Самый простой способ, в котором просто невозможно запутаться — это использовать правило «квадрата», или пропорции.

Вся суть приведена на скрине ниже: если у вас есть общая сумма, допустим в моем примере это число 3060 — ячейка F8 (т.е. это 100% прибыль, и какую то ее часть сделал «Саша», нужно найти какую. ).

По пропорции формула будет выглядеть так: =F10*G8/F8 (т.е. крест на крест: сначала перемножаем два известных числа по диагонали, а затем делим на оставшееся третье число).

В принципе, используя это правило, запутаться в процентах практически невозможно 👌.

Пример решения задач с процентами

Собственно, на этом я завершаю данную статью. Не побоюсь сказать, что освоив все, что написано выше (а приведено здесь всего лишь «пяток» формул) — Вы дальше сможете самостоятельно обучаться Excel, листать справку, смотреть, экспериментировать, и анализировать. 👌

Скажу даже больше, все что я описал выше, покроет многие задачи, и позволит решать всё самое распространенное, над которым часто ломаешь голову (если не знаешь возможности Excel) , и даже не догадывается как быстро это можно сделать. ✔

Читайте так же:

Мода в excel формула

Несколько IFS в Excel

В Microsoft Excel IF функция является одной из наиболее важных функций, которая в основном используется для проверки логического условия (ИСТИНА или ЛОЖЬ). Функция IFS проверяет, соблюдается одно или несколько условий или нет, и соответственно возвращает значение, соответствующее первому ИСТИННОМУ условию.

аргументы

Logical _test: это логическое выражение, в котором оно может быть «ИСТИНА» или «ЛОЖЬ».
Value_if_true: Это вернет значение как TRUE, если указанное условие выполнено.
Value_if_false: возвращает значение как FALSE, если указанное условие не выполняется.

Например, если у нас есть формула as = IF (A2 = 2, «TRUE», «FALSE»), что означает, что IF A2 = 2, если функция вернет TRUE или же она вернет FALSE.

Как использовать функцию IF в Excel?

Несколько IFS в Excel очень просто и легко. Давайте разберемся с несколькими IFS в Excel, как показано ниже.

Вы можете скачать этот шаблон с несколькими IFS Excel здесь — Шаблон с несколькими IFS Excel

Несколько IFS в Excel — Пример № 1

В Excel мы можем найти функцию IF, которая относится к группе условий LOGICAL в меню FORMULAS, которое показано на снимке экрана ниже.

Использование функции IF в Excel

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

Здесь мы будем использовать условие ЕСЛИ, чтобы узнать статус учащегося, получил ли он статус ПРОЙДЕН или НЕ УКАЗАН, выполнив следующие шаги.

Сначала выберите ячейку E2.
Примените условие ЕСЛИ, как показано ниже
= ЕСЛИ (D2> С2, »PASS», »провал»)

Здесь мы использовали условие IF, проверяя, если 20> 100, то Excel вернет статус «FAIL» или «PASS».
Примените формулу и нажмите ввод, чтобы мы получили вывод следующим образом.

Результат

Использование нескольких IFS в Excel с TEXT

Мы можем использовать несколько операторов IFS в Excel для проверки двух или более условий одновременно, используя один и тот же синтаксис условия IF. В приведенном ниже примере мы увидим, как применять несколько IFS.

Несколько IFS в Excel — Пример № 2

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

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

Сначала выберите ячейку E4, которая является ничем иным, как столбцом Status.
Теперь примените функцию Multiple IF, проверив условие, если TOTAL SEATS = SEATS SOLD тогда нам нужно получить статус «BUS BOOKED» или если TOTAL SEATS меньше, чем SEATS SOLD, нам нужно получить статус «OVERBOOKED», иначе мы упомянет статус «ПРОСТРАНСТВЕННОЕ ПРОСТРАНСТВО»
Мы собираемся применить вышеуказанное условие с использованием нескольких IFS.
Сначала вставьте оператор IF в E4
Тип Открывающая скобка и выберите C4.
Примените условие как C4 = D4 (ОБЩИЕ МЕСТА = ПРОДАННЫЕ МЕСТА), чем в двойных кавычках введите текст как «BUS BOOKED»
Вставьте запятую после этого.
Теперь вставьте другое условие IF и откройте скобки, указав это, проверив условие как C5
Затем закройте скобку условия IF для нескольких IFS.
Если мы объединим вышеприведенный IF, мы получим инструкцию Multiple IFS, которая показана ниже.
= IF (C5 = D5, «ЗАБРОНИРОВАНА НА ШИНУ», IF (C5

Как только мы применим Multiple IFS, мы получим следующий статус вывода, который показан ниже.

На приведенном выше снимке экрана мы получили статус «SPACE AVAILABLE», потому что условие IF будет проверять первое условие, если IF 400 = 390, первое условие неверно, поэтому в Excel будет проверяться другое условие IF, если IF 400 <390, тогда мы должны отображать статус как «ПРОСТРАНСТВО ДОСТУПНО», здесь условие ИСТИНА, так что если условие будет отображать статус «ПРОСТРАНСТВО ДОСТУПНО»
Перетащите формулу для всех ячеек, чтобы мы получили следующий результат, показанный ниже.

Результат

Несколько IFS в Excel — Пример № 3

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

Использование нескольких IFS с использованием числового значения

Рассмотрим приведенный ниже пример, в котором показаны ППМ и ЦЕНА ПРОДАЖИ, где нам нужно выяснить закрывающий ФИО для Amazon. Здесь мы будем использовать Множественный IFS, чтобы получить ЦЕНУ ЗАКРЫТИЯ для ППМ и ЦЕНЫ ПРОДАЖИ, выполнив следующие шаги.

Сначала выберите ячейку F3
Примените оператор условия Multiple IF, проверив условие, если IF MRP меньше 250, укажите плату за закрытие как ноль или IF MRP меньше 500, укажите цену закрытия как пять (5) или укажите цену закрытия как десять (10),
Затем нам нужно применить условие множественной IF для платы за продажу, проверив условие, так как цена продажи IF меньше 250, нам нужно получить цену закрытия, так как нам нужно получить нулевую (0) или цену продажи IF меньше 500 плата за закрытие — пять (5), иначе нам нужно получить плату за закрытие — десять (10).
Мы собираемся применить вышеупомянутые два условия, используя множественный IFS в обоих столбцах.
Сначала вставьте оператор IF в F3.
Тип Открывающая скобка и выберите D3.
Примените условие как D3 <250 MRP <250, затем отобразите плату за закрытие как Ноль и вставьте запятую после этого.
Теперь вставьте еще одно условие ЕСЛИ и откройте скобки, указав, что, проверив условие как D3 <500, отобразите комиссию за закрытие как 5 или же как комиссию за закрытие как 10.
Затем закройте скобку условия IF для нескольких IFS.
Если мы объединим вышеприведенный IF, мы получим инструкцию Multiple IFS, которая показана ниже.
= IF (D3 <250, 0, IF (D3 <500, 5, 10)), что показано на скриншоте ниже.

Читайте так же:

Как включить режим турбо в яндекс браузере

Как только мы применим Multiple IFS, мы получим следующий статус вывода, который показан ниже.

На приведенном выше снимке экрана мы получили плату за закрытие для ППМ как 10 как? Поскольку первое условие ЕСЛИ проверит первое условие как ЕСЛИ <250, мы получим плату за закрытие как Ноль, здесь условие ИСТИННО, и Excel проверит другое условие ЕСЛИ, в котором говорится, что ЕСЛИ ПП <500, мы получим плату за закрытие. как пять или иначе, если условие IF будет отображать плату за закрытие как 10.
Здесь, в этом примере, MRP равен 500, что проверяет условие как 500 <500, здесь оно более или менее равно условию, поэтому если условие IF отобразит плату за закрытие как 10.
Примените другой Многократный IF для платы за цену продажи как = IF (E3 <250, 0, IF (E3 <500, 5, 10))
Перетащите формулу для всех ячеек, чтобы получить следующий результат.

Результат

Что нужно помнить о множественных IFS в Excel

Когда мы используем строку в Excel, Multiple IFS гарантирует, что мы всегда вводим текст в двойных кавычках, иначе оператор выдаст ошибку как #NAME?
При использовании Multiple IFS убедитесь, что мы закрыли несколько открывающих и закрывающих скобок, иначе мы получим предупреждение об ошибке, указывающее, что примененная формула неверна.

СУММЕСЛИ с несколькими критериями

Если вам необходимо суммировать числа, основанные на нескольких критериях, вы можете использовать функцию СУММЕСЛИМН.

В показанном примере, формула в G5 является:

Первым диапазоном (D5: D12) являются ячейки суммы, называется «диапазон суммы».

Критерии поставляются в парах (диапазон / критериев).

Парой Первый критерий является (B5: B12 / «синий»). Это означает, что ячейки в B5: B12 должны быть равны «синий».

Пара Второй критерий представляет собой (C5: C12 / «> 15»). Ячейки в C5: C12 должны также быть> 15.

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

Сумма, если один критерий в нескольких столбцах

=СУММПРОИЗВ((ранг_критериев= «красный» ) * ( суммарный_диапазон ))

Подсчитывая несколько столбцов условно, используя один или несколько критериев, вы можете использовать формулу, основанную на функции СУММПРОИЗВ. В показанном примере, формула в Н4:

Это первое выражение в СУММПРОИЗВ является критерием, проверяя, если клетки в B5: B10 содержат «красный». Результатом является массив значений ИСТИНА, ЛОЖЬ:

Это значение умножается на значения в диапазоне С5: Е10:

Результатом внутри СУММПРОИЗВ является:

который рассчитывает 24, сумма всех значений в С5: Е10, где B5: B10 = «красный».

Сумма, если равно одному из нескольких критериев

=СУММПРОИЗВ (СУММЕСЛИ ( диапазон_условия ; условие ; диапазон_суммирования ))

В показанном примере, формула в Н4:

Функция СУММЕСЛИ принимает три аргумента: диапазон, критерии и суммарный_диапазон.

Для диапазона, мы используем B5: B11. Эти ячейки содержат значения, которые мы тестируем по нескольким критериям.

Для критериев, мы используем именованный диапазон «вещи» (E5: E7). Этот диапазон содержит 3 значения, которые мы используем в качестве критерия. Этот диапазон может быть расширен за счет включения дополнительных критериев, по мере необходимости.

Для суммарного_диапазона, мы используем C5: C11, который содержит числовые значения.

Потому как мы даем СУММЕСЛИ более чем один критерий, он будет рассчитывать несколько результатов — один результат для каждого значения в «вещи». Результаты рассчитываются в массиве следующим образом:.

СУММПРОИЗВ затем суммирует все элементы в массиве и рассчитывает конечный результат, 102.

Функция Эксель ЕСЛИ: учимся ставить условия

Здравствуйте, друзья. Часто необходимо вывести значение в зависимости от содержания другой ячейки. Когда используется формула ЕСЛИ, Эксель может гибко решать подобные задачи, в чем вы сейчас убедитесь.

Назначение и синтаксис функции

Вы часто спрашиваете меня, как выглядит формула Эксель «Если то», сегодня отвечаю на ваши вопросы. Действия с условием выполняются функцией ЕСЛИ:

Читайте так же:

Где находится корень сайта wordpress

=ЕСЛИ(условие ; значение если истина ; значение если ложь)

Разбираемся с аргументами:

Условие – логическое выражение, которое будет проверять функция. Здесь можно использовать операторы:
- = — равно
- <> — не равно
- < — меньше
- > — больше
- <= — меньше или равно
- >= — больше или равно
Кроме того, можете применять функции И() и ИЛИ().
- Значение если истина – значение, которое вернет формула, если выполнится условие
- Значение если ложь – значение, которое вернет формула, если условие не выполнится
Например, формула =ЕСЛИ(A1=100;«Готово»;«В процессе») выведет в ячейку слово «Готово», если в А1 содержится число 100. Или выведет «В процессе», когда в А1 не 100.
Функция И() нужна для проверки двух условий одновременно, перечисленных через точку с запятой. Например, формула =И(A1>10;B1<30) вернет значение ИСТИНА только тогда, когда выполнятся оба условия: значение в ячейке А1 будет больше 10, а в ячейке B1 – меньше 30.
Функция ИЛИ() вернет истину, когда выполнится хотя бы одно из условий. То есть, формула =ИЛИ(A1>10;B1<30) даст истину, когда выполнится или первое условие, или второе, или оба.
Теперь практикуемся. На основе одной таблицы рассмотрим несколько примеров. Пусть у нас есть перечень студентов и их оценки за экзамен.
Функция ЕСЛИ с одним условием
Это самый простой пример. Задача стоит: тем студентам, у которых оценка выше сорока баллов – поставить отметку «Зачет», остальным – не ставить никаких отметок. Напишем формулу: =ЕСЛИ(B3>40;»Зачёт»;»») . Скопируем её во все строки таблицы:

Функция ЕСЛИ с несколькими условиями
Расширяем задачу. Некоторые студенты получили «автомат» по итогам семестра. Теперь зачет нужно поставить еще и тем студентам, у которых в поле «Автомат» стоит плюс. Воспользуемся описанной выше функцией ИЛИ: =ЕСЛИ(ИЛИ(B3>40;C3=»+»);»Зачёт»;»») . Результат таков:

ЕСЛИ значение принадлежит диапазону
Теперь такой пример. Нужна формула в Эксель, если значение лежит в диапазоне, то отправить студента на пересдачу. Пусть это будет 20-40 баллов. Пользуемся уже знакомой функцией И(), чтобы задать несколько условий.

Здесь я применил условное форматирование, чтобы выделить отобранные строчки цветом.
Функция ЕСЛИ со вложенным условием
Последний пример. Нужно студентам проставить либо «Зачет», когда они получили более 40 баллов, либо «Отчисление», когда заработано менее 20 баллов. Мы уже писали функцию, где проставляли «Зачет». Теперь ее модифицируем.
То есть, когда студент не получил зачет, нужно снова проверить, если его балл менее 20 – отчислить. Следовательно, в аргумент «Значение если ложь» запишем еще одну, вложенную функцию ЕСЛИ. Вот так: =ЕСЛИ(ИЛИ(B3>40;C3=»+»);»Зачёт»;ЕСЛИ(B3<20;»Отчисление»;»»)) .

Мы рассмотрели достаточно примеров, чтобы вы понимали, как работает функция, теперь применяйте ее на своих проектах.
Так же, будут очень полезны статьи о логических функциях в Экселе и проверках на тип содержимого.
А если что-то не получится – задавайте вопросы в комментариях. До встречи!
Функция ЕСЛИ в Excel с несколькими условиями. Примеры использования вложенных функций ЕСЛИ
Мы уже рассматривали пример с функцией ЕСЛИ, но часто требуется использовать несколько условий ЕСЛИ, то есть вкладывать несколько ЕСЛИ в одну формулу. В этом случае у многих возникают вопросы как это реализовать. Задача возникает очень часто и давно напрашивалась к написанию. На самом деле ничего сложного нет, в этом примере мы рассмотрим пример Функции ЕСЛИ с несколькими условиями подробно, чтобы у вас было четкое понимание как это использовать для других примеров.
Пример задачи с использование нескольких функцией ЕСЛИ: У нас есть отчет по продажам продавцов. План продаж считается по количеству проданных единиц товара и составляет от 18 до 20шт. Каждый сотрудник получает оплату 2000 рублей. За каждую дополнительно проданную единицу товара сверх плана сотрудник получает 25 рублей, а за невыполнение плана — штрафуется по 50 рублей за единицу (например, продал 16 шт, недобрав 2 шт до минимального плана 18 шт, следовательно он получает 2000 рублей минус 2*50 рублей то есть 1900 рублей). Нам необходимо рассчитать оплату для каждого сотрудника.
Для удобства вынесем все условия в отдельную таблицу, чтобы при необходимости мы могли быстро их менять если, например, поменяется оплата или план
Нам требуется заполнить столбец «Оплата». У нас несколько условий, поэтому одним если обойтись не получится. Вот как мы будет рассматривать эти условия
- если продаж больше 20, то считаем оплату за перевыполнение, иначе проверяем
- если продаж меньше 18, то считаем оплату за невыполнение, иначе было от 18 до 20 продаж и сумма выплаты составляет 2000 рублей
Для удобство понимания, в первое время лучше использовать диалоговые окна. В дальнейшем вы сможете писать функцию сразу в строке формул. Для начала мы посчитаем оплату для первого сотрудника Алексей, но сразу учтем и пропишем формулу таким образом, чтобы можно было применить эту формулу и для других сотрудников, протянув ее вниз.
Итак, вставьте курсор в ячейку C3 и нажмите на значок выбора функций, перейдите в категорию «Логические» и выберите функцию ЕСЛИ и нажмите «ОК» (см. на рисунок)
Откроется диалоговое окно функции если.
Лог_выражение — это то что мы будет проверять. Поместите курсор в данное поле. В нашем случае как вы помните мы сначала проверяем продажи больше 20 (то есть больше плана) или нет. Продажи Алексея у нас в ячейке B3 — кликните мышкой на B3 и это выражение появится в этом поле (можно просто прописать B3, но удобнее выбирать). Далее нам необходимо проверить эти продажи Алексея с планом. Напишем B3> и выберем ячейку с планом G2, У нас должно получиться выражение B3>G2
Для того, чтобы в дальнейшем применить данную формулу для других сотрудников, протянув ее вниз, нам необходимо закрепить ссылку в ячейке G2, чтобы она стало абсолютной (то есть не менялась, когда мы будет протягивать формулу). Для этого необходимо прописать знаки доллара перед буквой и цифрой или только перед цифрой, так как мы не будет протягивать формулу влево или вправо. Удобнее всего зафиксировать ячейку просто выделив в формуле нужную ячейку и нажав клавишу F4
Должно получиться вот так B3>$G$2 или так B3>G$2
Обратите внимание, Excel автоматически вычисляет выражение и отображает его справа. В нашем случае Excel вычислил, что выражение — ИСТИНА, то есть действительно Алексей сделал продаж в количестве 35 шт, что больше верхней границе плана 20 шт. Обратите внимание, что если бы у Алексея было бы недовыполнение плана, то формулу мы бы все равно писали точно так же — гипотетически рассуждая, как если бы у Алексея был бы выполнен план.
Поехали дальше, переходим ко второму аргументу функции.
Значение_если_истина. Переведите курс в это поле. Тут нам необходимо рассчитать оплату сотруднику, если он выполнил план. Стандартная оплата 2000 (F4) плюс так как мы рассматриваем ситуацию когда план перевыполнен, нужно прибавить переработку. Для этого вычтем из всего продаж план и получим количество продаж сверх нормы (B3-G2) и умножим их на оплату за перевыполнение 25 рублей (F5). В итоге получаем следующий расчет 2000+(35-20)*25 то есть F4+(B3-G2)*F5
так же не забываем, что данные из таблицы условий нам нужно закрепить, чтобы они сдвигались при протягивании формулы. Для этого выделяем нужные значения и нажимаем F4
Получаем итоговый расчет: $F$4+(B3-$G$2)*$F$5
Переходим к следующему полю.
Значение_если_ложь. Итак, выше мы рассчитали оплату сотруднику, если план продаж выполнен (то есть если наше выражение в первом поле верно — возвращает истину). Если нет, то осталось два варианта: либо сотрудник точно выполнил план продаж (от 18 до 20шт), либо недовыполнил. Снова два условия, потребуется вложенная ЕСЛИ. Будет легче, если мы сначала проверим условие невыполнение плана, а оставшийся вариант будет выполнение плана.
В этом поле можно написать вложенную функцию ЕСЛИ вручную, но это не очень удобно, поэтому мы снова воспользуемся диалоговыми окнами. Перейдите в ячейку Значение_если_ложь откройте раскрывающийся список последних использованных функции в правом углу и выберите ЕСЛИ
После этого у вас снова появится новое диалоговое окно функции ЕСЛИ. Не надо паниковать предыдущая информация не стерлась, а просто свернулась. Вы можете это видеть в строке формул. Это сделано для удобство.
Заполняем поле лог_выражение, как вы помните мы проверяем условие о невыполнение плана сотрудником. То есть прописываем (выбираем) B3<F2 (нижняя граница выполнения плана). Снова закрепляем ссылку на F2 с помощью клавиши F4 и получаем выражение: B3<$F$2
Далее поле Значение_если_истина. То есть если действительно B3<F2 (то есть если Алексей не выполнил план), мы рассчитывает ему оплату как сотруднику, который не выполнил план. Она рассчитываться так: оплата 2000 рублей (F4) минус 50 рублей за каждую единицу невыполнения плана. Чтобы посчитать количество единиц, которое не хватило сотруднику, нужно отнять от нижний границы плана F2 итоговый результат по продажам B3 и умножить на 50 рублей (F6).
Получаем следующую формулу: $F$4-($F$2-B3)*$F$6
Отлично. Если план перевыполнен, то мы посчитали оплату, если не выполнен, мы проверяем сотрудника на невыполнения плана. Если он не выполнил план, то мы считаем ему оплату. Если же он и не перевыполнил план и не недовыполнил план, то значил от сделал ровно план от 18 до 20 шт.
Поэтому в следующем поле мы рассчитываем оплату сотруднику, когда он выполнил план, то есть он получает 2000 рублей (ячейка F4). Переходим в поле Значение_если_ложь и прописываем (выбираем) F4. Не забываем закрепить ссылку — $F$4
Вот так будет выглядеть вложенное ЕСЛИ
Нажимаем «Ок» — формула готова. Вот так выглядит полная формула:
С помощью диалоговых окон очень просто пользоваться функцией ЕСЛИ и при необходимости не запутаться с вложенными ЕСЛИ, главное изначально подумать о последовательности проверки ЕСЛИ.
голоса
Рейтинг статьи