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

ЛогоMicrosoft Excel – довольно мощный редактор для работы с таблицами. При этом его возможности не ограничиваются только этим. Работать с текстом можно и в редакторе Word, но в нем нет динамики. Именно поэтому для анализа информации лучше всего использовать Эксель. В данной статье мы рассмотрим функцию ЕСЛИ с несколькими условиями. В Excel стандартных примеров нет, поэтому будем использовать что-то своё.

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

Таблица зарплат

Функция ЕСЛИ

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

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)

Полное описание можно увидеть в окне «Вставка функции».

  1. Нажмите на иконку
  2. Выберите категорию «Полный алфавитный перечень».
  3. Найдите там пункт «ЕСЛИ».
  4. Сразу после этого вы увидите описание функции.

Описание

Далее появится окно, в котором требуется указать «Аргументы функции» (логическое выражение, значение если истина и значение если ложь).

Аргументы функции

В качестве примера добавим столбец с премией для учителей высшей категории.

Столбец с премией

Затем необходимо выполнить следующие действия.

  1. Перейдите на первую ячейку. Нажмите на иконку «Fx». Найдите там функцию «ЕСЛИ» (её можно отыскать в категории «Полный алфавитный указатель»). Затем кликните на кнопку «OK».

Полный алфавитный указатель

  1. В результате этого появится следующее окно.

Аргументы функции в окне

  1. В поле логическое выражение введите следующую формулу.
D3="Высшая"

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

  1. После подстановки вы увидите, что данное выражение ложно.

Выражение ложно

  1. Затем указываем значения дли «Истины» и «Лжи». В первом случае какое-то число, а во втором – ноль.

Значения

  1. После этого мы увидим, что логический смысл выражения будет ложным.

Ложный смысл

  1. Для сохранения нажимаем на кнопку «OK».

Клик по ОК

  1. В результате использования этой функции вы увидите следующее.

Итоговая таблица

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

  1. Наведите курсор в правый нижний угол первой ячейки.
  2. Если вы сделали всё правильно, то он превратится в черный крестик.

Черный крестик

  1. Теперь сделайте левый клик мыши и не отпуская палец опуститесь до конца таблицы.

Спуск указателя

  1. В результате этого вы увидите следующее.

Новые значения

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

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

Использование условия «И»

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

Читайте также:  Конвертация Excel в PDF

Для этого достаточно выполнить следующие действия.

  1. Кликните на первую ячейку в столбце «Премия».
  2. Затем нажмите на иконку «Fx».

Премия

  1. Сразу после этого появится окно с используемой функцией со всеми указанными аргументами. Таким образом редактировать намного проще – непосредственно в ячейке.

Окно с функцией

  1. В графе логическое выражение укажите следующую формулу. Для сохранения изменений нажмите на кнопку «OK».
И(D3="Высшая";E3="Математика")

Нажатие на OK

  1. Скопируйте эту формулу во все остальные ячейки.

Копирование функции

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

Одиночная премия

Использование условия «ИЛИ»

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

  1. Перейдите в первую ячейку.
  2. Кликните на иконку «Fx».

Клик по OK

  1. Текущее логическое выражение нас не устраивает.

Логическое выражение

  1. Нужно будет поменять его на следующее.
ИЛИ(D3="Первая";D3="Вторая")

Вторая

  1. Сразу после этого мы увидим, что у первого преподавателя появилась премия, поскольку у него первая категория.

Премия преподавателя

  1. Дублируем эту формулу в остальные ячейки.

Дублирование

В результате этого мы увидим следующее.

Результат

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

СУММЕСЛИ

В качестве демонстрации данного инструмента попробуем посчитать общую зарплату для учителей первой категории. Для этого создадим еще одну строку.

СУММЕСЛИ

Затем выполним следующие шаги.

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

Вывод суммы

  1. Сразу после этого вы увидите окно, в котором нужно указать аргументы функции:
    • диапазон;
    • критерий;
    • диапазон суммирования.

Диапазоны

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

Рамка выделения

  1. Как только вы отпустите палец, всё вернется в прежний вид, а нужный диапазон подставится автоматически.

Подставка диапазона

  1. В графе «Критерий» нужно указать слово «Первая», так как мы считаем зарплату именно этих преподавателей.

Критерий

  1. Затем нам необходимо выделить графу зарплата. Но перед этим кликаем на последнее поле – нужно, чтобы оно стало активным.

Диапазон суммирования

  1. Только после этого можно указывать нужный диапазон.

Указание диапазона

  1. Как только вы отпустите палец, увидите следующее.

Значения диапазона

  1. Для сохранения формулы достаточно кликнуть на кнопку «OK».

Сохранение формулы

В результате мы увидим, что общая зарплата учителей с первой категорией составила 1200.

ЗП в 1200

СУММЕСЛИМН

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

Для этого необходимо сделать следующее.

  1. Добавим строчку, где будем производить расчёты.

Строка расчетов

  1. Переходим в нужную клетку и кликаем на иконку «Fx». Находим нужную функцию и нажимаем на кнопку «OK».

Задание функции

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

Графа зарплаты

  1. Для этого достаточно выделить нужные ячейки и в результате этого они подставятся автоматически.

Подстановка ячеек

  1. Теперь указываем «Диапазон условия 1». Например, здесь можно указать категорию сотрудников. Перед выделением обязательно кликните на это поле, иначе выделенные ячейки попадут в первую строку. Сразу после этого вы увидите новое поле для ввода.
Читайте также:  Автозамена в Эксель

Диапазон условия 1

  1. Введите туда слово «Первая». Сразу после этого вы увидите поля для второго условия.

Ввод Первая

  1. Делаем клик на поле «Диапазон условия 2». Затем выделяем столбик с предметом.

Диапазон условия 2

  1. В строку «Условие 2» напишите нужный вам предмет. В данном случае – «Математика». Для того чтобы увидеть корректность вашей формулы, достаточно будет кликнуть в любое уже ранее указанное поле. В результате этого второе условие окажется в кавычках (согласно правилам) и вы увидите результат данной функции.

Математика

Для сохранения формулы необходимо нажать на кнопку «OK».

Сохраняем формулу

В итоге вы увидите следующее.

Результат работы

СЧЕТЕСЛИ

В редакторе Эксель для расчётов количества ячеек можно использовать формулу «СЧЕТ». Но как быть, если нужно посчитать не все ячейки? Более того, позиция нужных клеток заранее неизвестна, поскольку таблица может содержать большое количество данных.

В таких случаях нужно использовать СЧЕТЕСЛИ. В качестве примера посчитаем, сколько именно сотрудников имеют высшую категорию. Для этого нужно сделать следующее.

  1. Добавим новую строку. Сделаем активной нужную ячейку и кликнем на иконку вставки функции.

СЧЕТЕСЛИ

  1. В появившемся окне находим нужную формулу и нажимаем на кнопку «OK».

Нажатие на кнопку OK

  1. Затем вас попросят указать диапазон и критерий отбора.

Диапазон и критерий

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

Выделение ячеек

  1. Затем в поле критерий указываем слово «Высшая». Для сохранения нажмите на кнопку «OK».

Высшая

  1. В результате этого вы увидите следующее.

Количество учителей

СЧЕТЕСЛИМН

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

  1. Добавляем новую строку для расчётов. Кликаем на нужную ячейку и вызываем окно «Вставка функции». Находим нужную и кликаем на кнопку «OK».

СЧЕТЕСЛИМН

  1. В графу «Диапазон условия» указываем поле «Категория». Для этого достаточно выделить нужные ячейки.

Указание диапазона условий

  1. После клика в поле «Условие 1» у вас появится строка для второго диапазона.

Строка второго диапазона

  1. Введите нужную категорию учителя. В данном случае – «Высшая».

Категория Высшая

  1. После этого сделайте клик в поле «Диапазон условия 2» и выделите столбец с названием предмета.

Клик по Диапазон условия 2

  1. Затем в последнее поле указываем слово «Математика». Для сохранения нажимаем на кнопку «OK».

Слово Математика

  1. Результат будет следующим.

Учителя с высшей категорией

Расчёт произошел корректно. В нашей таблице всего 1 преподаватель математики с высшей категорией.

Функция ПОИСКПОЗ

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

Для этого нужно сделать следующее.

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

Окно вставки функции

  1. В появившемся окне нужно будет указать:
    • искомое значение;
    • просматриваемый массив;
    • тип сопоставления.

Данные для вставки

  1. В первое поле достаточно написать слово «География», поскольку именно этого преподавателя мы ищем.

География

  1. Затем нужно выделить столбец с названием предметов (предварительно кликнув на второе поле).

Выделение нужного диапазона

  1. В третьем параметре укажите цифру «0». Для сохранения нажмите на кнопку «OK».
Читайте также:  Как добавить строку в Экселе

Цифра «0»

  1. В результате этого мы увидели, что ячейка со словом «География» находится под номером 8 (среди указанного диапазона).

8 номер

Более подробно про третий параметр можно прочитать на официальном сайте центра поддержки компании Microsoft. Там написано следующее.

Справка

Использование условий в VBA

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

Для этого необходимо выполнить следующие шаги.

  1. По умолчанию вкладка с макросами скрыта от пользователей. Её нужно открыть. Нажмите на пункт меню «Файл».

Файл

  1. Перейдите в раздел «Параметры».

Параметры

  1. В появившемся окне выберите категорию «Настроить ленту». Затем поставьте галочку возле пункта «Разработчик». Для сохранения нажмите на кнопку «OK».

Настроить ленту

  1. Сразу после этого вы увидите, что указанная вкладка появилась на панели инструментов.

Вкладка появилась

  1. Перейдите на неё и нажмите на кнопку «Visual Basic».

Visual Basic

  1. Сразу после этого появится окно для написания кода.

Окно ввода кода

  1. В левой части экрана находится список объектов в вашем файле. Выберите ваш текущий лист.

Выбор листа

  1. Введите следующий код:
Sub ProverkaPoiskaUchiteley() If [F17] = 0 Then MsgBox "Учителя не найдены" End If If [F17] > 0 Then MsgBox "Учителя найдены" End If End Sub

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

Sub ProverkaPoiskaUchiteley

  1. Закройте этот редактор. Теперь кликните на иконку «Макросы».

Макросы

  1. В появившемся окне нажмите на кнопку «Выполнить».

Выполнить

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

Число больше нуля

  1. Если вы измените значение этой ячейки на «0», то увидите совсем другой результат.

Замена значения

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

  1. Снова нажмите на иконку «Макросы». В появившемся окне нажмите на кнопку «Параметры».

Настройки

  1. Сразу после этого вам предложат указать какую-нибудь кнопку и описание к этому макросу.

Описание макроса

  1. Сочетания клавиш необязательно должны быть только с клавишей Ctrl. Можно использовать дополнительное сочетание с кнопкой Shift. В качестве примера назначим комбинацию Ctrl+ Shift+ E. Для сохранения нажимаем на «OK».

Сохранение

  1. Закройте это окошко. Теперь нажмите на сочетание клавиш Ctrl+Shift+ E. В результате этого вы увидите сообщение о результате проверки. Так намного удобнее, чем каждый раз заходить в меню.

Поиск

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

Заключение

В данной статье были рассмотрены основные принципы работы с функциями категории «ЕСЛИ». Каждая из них была детально разобрана на конкретном примере. Если у вас что-то не получается, возможно, вы выделяете не те ячейки или составляете неправильное условие.

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

Видеоинструкция

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

Автор статьи
Никита Жданов
Специалист лаборатории os-helper.ru по тестированию программного обеспечения.
Об авторе
Написано статей
52

Ставь 5 звезд! Мы старались.

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

Добавить комментарий

Ваш e-mail не будет опубликован.