Microsoft Excel – довольно мощный редактор для работы с таблицами. При этом его возможности не ограничиваются только этим. Работать с текстом можно и в редакторе Word, но в нем нет динамики. Именно поэтому для анализа информации лучше всего использовать Эксель. В данной статье мы рассмотрим функцию ЕСЛИ с несколькими условиями. В Excel стандартных примеров нет, поэтому будем использовать что-то своё.
В качестве примера создадим таблицу зарплаты для учителей в зависимости от их категории и будем использовать эти данные для различных манипуляций.
Принцип действия довольно простой. Вы указываете какое-нибудь условие и что нужно делать в случаях истины и лжи.
ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)Полное описание можно увидеть в окне «Вставка функции».
В качестве примера добавим столбец с премией для учителей высшей категории.
Затем необходимо выполнить следующие действия.
В качестве адреса указываем ячейку, в которой содержится категория преподавателя.
В нашей таблице много записей. Нужно сделать так, чтобы на всем диапазоне этого столбца была точно такая же формула, только с поправкой на индекс (номер строки). Для этого выполните следующие шаги.
Благодаря этой формуле произошла выборка нужных учителей и им была начислена премия. В итоге в список попало только два человека, поскольку только у них высшая категория.
В данном случае информации не так много. А теперь представьте, что такая таблица будет огромной. Ведь в организации всегда работает большое количество людей. Если работать в редакторе Word и делать такое сравнение квалификации сотрудников вручную, то кто-нибудь (вследствие ошибок, связанных с человеческим фактором) будет выпадать из списка. Формула в Экселе никогда не ошибется.
Как правило, очень часто приходится использовать различные дополнительные проверки. Например, можно посчитать премию не просто для учителей высшей категории, а конкретно для математиков.
Для этого достаточно выполнить следующие действия.
В результате этого мы увидим следующее.
Благодаря данному оператору мы смогли сделать сложный запрос и рассчитать премию для разной категории сотрудников.
В качестве демонстрации данного инструмента попробуем посчитать общую зарплату для учителей первой категории. Для этого создадим еще одну строку.
Затем выполним следующие шаги.
В результате мы увидим, что общая зарплата учителей с первой категорией составила 1200.
Для этого необходимо сделать следующее.
Для сохранения формулы необходимо нажать на кнопку «OK».
В итоге вы увидите следующее.
В редакторе Эксель для расчётов количества ячеек можно использовать формулу «СЧЕТ». Но как быть, если нужно посчитать не все ячейки? Более того, позиция нужных клеток заранее неизвестна, поскольку таблица может содержать большое количество данных.
В таких случаях нужно использовать СЧЕТЕСЛИ. В качестве примера посчитаем, сколько именно сотрудников имеют высшую категорию. Для этого нужно сделать следующее.
Расчёт произошел корректно. В нашей таблице всего 1 преподаватель математики с высшей категорией.
Иногда при составлении сложных формул нужно определить ячейку, в которой находится нужный текст или какое-нибудь числовое или иное значение. В качестве примера в данной таблице можно будет определить, где именно находится учитель географии.
Для этого нужно сделать следующее.
Более подробно про третий параметр можно прочитать на официальном сайте центра поддержки компании Microsoft. Там написано следующее.
При помощи макросов можно сделать дополнительную проверку различных значений. Например, вы можете узнать, были ли найдены люди по вашему запросу. Разумеется, в данном случае с маленькой таблицей и так всё видно. Но как быть, если данных очень много?
Для этого необходимо выполнить следующие шаги.
Одна из самых популярных функций в приложении Excel – ЕСЛИ . Это логическое сравнение значения и получаемого результата. Если говорить проще, то функция может звучать так:
ЕСЛИ условие истинно, то выполняем это, а иначе делаем что-либо еще
Синтаксис этой функции достаточно прост:
ЕСЛИ (логическое_условие; значение_в_случае_ИСТИНА; значение в случае_ЛОЖЬ)
Разберем подробнее:
На скриншотах ниже показан самый просто вариант использования. Мы сначала проверяем
заданное условие А1>25
. ЕСЛИ это требование выполняется
, тогда выводим в ячейку «больше 25
», иначе «меньше или равно 25
».
Теперь более сложное задание. Ниже мы рассмотрим пример переаттестации сотрудников предприятия. Изначально таблица выглядит так:
Нам необходимо в столбце С вывести результаты переаттестации, которые могут принимать бинарное значение: СДАЛ или НЕ СДАЛ . Критерии у нас будут такими: кто набрал более 45 балов , тот считается сдавшим экзамен, ну а все остальные нет.
Для выполнения задачи необходимо :
При необходимости, функцию ЕСЛИ можно вложить друг в друга. Таким образом, мы расширим варианты решений.
Для примера, возьмем переаттестацию сотрудников, которую рассмотрели раньше. Изменим критерии результата и выставим каждому оценку : Плохо, Хорошо и Отлично. Отлично будем ставить, когда баллы превысят 60 . Оценку Хорошо можно будет получить, набрав от 45 до 60 балов . Ну и в остальных случаях ставим Плохо .
Как видно из примера, вместо второго и третьего значения функции можно подставлять условие. Таким способом добавляем необходимое число вложений. Однако стоит отметить , что после добавления 3-5 вложений работать с формулой станет практически невозможно, т.к. она будет очень громоздкой.
К примеру, есть необходимость просуммировать каждую вторую ячейку столбца. В этом случае поможет использование оператора СУММЕСЛИ
. Он позволит работать только с теми столбцами, которые попадают под наши требования. Мы будет рассматривать случай, когда необходимо суммировать элементы в нечетных строках.
Для решения задачи, нам потребуется вставить дополнительный столбец и пронумеровать строки 1 и 0. Именно эти данные мы будем использовать в нашей формуле. В примере видно, какую формулу мы использовали.
Синтаксис : СУММЕСЛИ(диапазон_ячеек; используемое_условие; [диапазон_суммирования])
В этом случае мы можем посчитать, сколько раз заданный элемент встречается на листе. Для этого используем функцию СЧЕТЕСЛИ
. Она считает количество ячеек, которые совпадают
с заданным значением.
В этом примере мы считаем, сколько клиентов находится в каждом городе. В формуле мы задаем первым диапазон, а вторым значения, которые следует искать.
В примере мы постараемся определить усредненный объем дохода
от клиентов по каждому городу. Для этого поделим суммарный доход города на количество клиентов.
Если у нас есть постоянно обновляемый список данных, в котором по мере роста могут появляться дубликаты , то поиск вхождений в этом списке может оказаться затруднительным. Для решения этой задачи проще всего воспользоваться функцией ВПР и СЧЕТЕСЛИ .
Для начала, добавим
дополнительный столбец, который добавить порядковый номер вхождения к имени клиента. В результате, первое упоминание элемента будет выдавать Имя1, следующее Имя2 и т.д.
Используем поиск по клиенту Кристина Агилера. Используя формулу ВПР («Кристина Агилера3», диапазон_поиска, 2, ЛОЖЬ), мы получим третий номер этого клиента. В формуле, последним значением ставим ЛОЖЬ, поскольку список не сортирован, и нам необходимо точное совпадение элементов.
Версии до EXCEL 2003 включительно поддерживали до 7 уровней вложенности ЕСЛИ. Начиная с версии Excel 2007, это ограничение убрали . Но стоит заметить, что ниже 3-4 уровня вложенности мало кто опускается.
Для того чтобы уменьшить количество использования вложений ЕСЛИ, можно использовать функцию ВЫБОР . Она работает со значениями или действиями из заданного списка по номеру индекса.
Синтаксис : ВЫБОР (номер_индекса; значение_индекса1; значение_индекса2; [значение_индекса3];…)
Для примера, функция ВЫБОР (1; «Третий»; «Второй»; «Первый») , вернет нам слово «Третий », если мы ее добавим в ячейку.
Стоит отметить, что имеются некоторые ограничения. В частности, в качестве индекса могут выступать только числа
.
Сегодня мы рассмотрим функцию ЕСЛИ .
Функция ЕСЛИ часто используется в Excel для решения многих задач. Знать ее очень полезно. В данной статье мы попробуем рассказать про ее работу на простых примерах, достаточно один раз разобраться с конструкцией функцией ЕСЛИ и вы сможете применять ее и в самых сложных вариантах.
Функция ЕСЛИ проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет.
Синтаксис функции ЕСЛИ очень простой:
ЕСЛИ(лог_выражение ; [значение_если_истина ]; [значение_если_ложь ])
лог_выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.
Что это значит? Выражение при вычислении дает значение ИСТИНА если это выражение верно.
В этой части необходимо проверить на соответствие выражения.
Например:
ЕСЛИ(А1=10 ; [значение_если_истина]; [значение_если_ложь]) - если А1 равно 10, то выражение А1=10 даст значение ИСТИНА, а если не равно 10, то ЛОЖЬ
Другой пример
ЕСЛИ(А1>30 ; [значение_если_истина]; [значение_если_ложь]) - если в ячейки А1 число больше 30, то А1>30 вернет ИСТИНА, а если меньше, то ЛОЖЬ
Еще пример
ЕСЛИ(С1=”Да” ; [значение_если_истина]; [значение_если_ложь]) - если в ячейки C1 содержится слово “Да” то выражение вернет значение ИСТИНА, а если нет, то С1=”Да” вернет ЛОЖЬ
ЕСЛИ(лог_выражение ; [значение_если_истина ]; [значение_если_ложь ])
значение_если_истина , значение_если_ложь – как видно из их названия, это то что необходимо сделать в зависимости от того, что вернул лог выражения : ИСТИНА и ЛОЖЬ
Рассмотрим использование функции ЕСЛИ на практическом примере. У нас есть таблица заказов, которую мы использовали при рассмотрении работы . Нам необходимо заполнить столбец по заказам Ведер (ошибочно на картинке указано «Заказы Cтолов»), то есть необходимо выбрать только заказы с Ведрами. Это можно сделать различными способами, но мы с вами будет использовать функцию ЕСЛИ, чтобы показать ее работу на примере. (см.рисунок)
Для решения поставленной задачи напишем формулу с использованием функции ЕСЛИ
ЕСЛИ(A3="Ведро";D3;"-")
Как вы смогли заметить аргументы функции ЕСЛИ разделены точкой с запятой.
Итак, первый аргумент (лог выражения ) A3="Ведро" проверяет содержится ли в ячейке А3 слово «Ведро», если содержится, то выполняется второй аргумент функции ЕСЛИ (значение_если_истина ), в нашем случае это D3 (т.е стоимость заказа), если в ячейка А3 не равна слову «Ведро», то выполняется третий аргумент функции ЕСЛИ (значение_если_ложь ), в нашем случае это «-» (т.е будет написано тире).
Таким образом, в ячейки E3 появится значение D3, т.е число 240.
Полный синтаксис (нажмите, чтобы раскрыть)
Описание:
Оператор Если управляет выполнением программы, основываясь на результате одного или более логических выражений. Оператор может содержать любое количество групп операторов, возглавляемых конструкциями ИначеЕсли - Тогда .
Синтаксис:
Параметры:
Описание:
Позволяет вычислить одно из двух заданных выражений в зависимости от результата вычисления логического выражения.
Синтаксис:
? (< Логическое выражение> , < Выражение 1 > , < Выражение 2 > ) |
Параметры:
Возвращаемое значение:
Результат вычисления одного из результирующих выражений.
&НаКлиенте Процедура ВыполнитьКод(Команда) /// Как составить оператор Если в 1с 8.3, 8.2 Если 1 > 0 Тогда // Истина // блок операторов Сообщить("Компьютер выполнит все команды из этого блока." ) ; Сообщить("Один больше нуля." ) ; КонецЕсли ; Если 1 < 0 Тогда // Ложь Сообщить("Один меньше нуля." ) ; Иначе Сообщить("Сработает именно эта ветка условного оператора (#А)." ) ; Сообщить("Один больше нуля." ) ; КонецЕсли ; Если 1 < 0 Тогда // Ложь Сообщить("Один меньше нуля." ) ; ИначеЕсли 1 = 0 Тогда // Ложь Сообщить("Один равен нулю." ) ; Иначе Сообщить("Сработает именно эта ветка условного оператора (#Б)." ) ; Сообщить("Один больше нуля." ) ; КонецЕсли ; /// Как составить тернарный оператор? в 1с 8.3, 8.2 Текст = ? (1 > 2 , "Один больше двух." , "Один не больше двух." ) ; Сообщить(Текст) ; // выведет "Один не больше двух." КонецПроцедуры /// Скачать и выполнить эти примеры на компьютере |