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

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

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

Функция ЕСЛИ

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

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

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

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

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

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

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

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

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

3. В поле логическое выражение введите следующую формулу.

[kod]D3=»Высшая»[/kod]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

4. В графе логическое выражение укажите следующую формулу. Для сохранения изменений нажмите на кнопку «OK».

[kod]И(D3=»Высшая»;E3=»Математика»)[/kod]

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

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

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

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

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

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

4. Нужно будет поменять его на следующее.

[kod]ИЛИ(D3=»Первая»;D3=»Вторая»)[/kod]

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

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

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

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

СУММЕСЛИ

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

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

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

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

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

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

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

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

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

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

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

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

СУММЕСЛИМН

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

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

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

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

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

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

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

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

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

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

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

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

СЧЕТЕСЛИ

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

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

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

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

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

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

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

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

СЧЕТЕСЛИМН

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

5. В третьем параметре укажите цифру «0». Для сохранения нажмите на кнопку «OK».

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

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

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

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

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

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

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

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

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

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

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

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

8. Введите следующий код:

[kod]Sub ProverkaPoiskaUchiteley()[/kod]

[kod]If [F17] = 0 Then[/kod]

[kod]MsgBox «Учителя не найдены»[/kod]

[kod]End If[/kod]

[kod]If [F17] > 0 Then[/kod]

[kod]MsgBox «Учителя найдены»[/kod]

[kod]End If[/kod]

[kod]End Sub[/kod]

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

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

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

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