В прошлый раз мы начали изучать функцию ПЕРЕКЛЮЧ, которая является неплохой альтернативой вложенным функциям ЕСЛИ и функции ЕСЛИМН. Сегодня продолжим разбирать варианты её применения. Мы рассмотрели примеры использования в качестве аргумента Выражение ссылки на ячейку и ИСТИНУ. Теперь давайте вставим туда формулу.
Пример 3: Сравнение результата формулы со списком значений
Этот пример будет полезен тем, кто хочет проверить за своим расчетным отделом правильность начисления своей зарплаты. У нас есть список дат, когда мы работали и известно сколько и в какой день было отработано часов. Известно, что в будний день наша ставка за час составляет 100 рублей, в субботу час оплачивается по 150 рублей, а в воскресенье по 200 рублей. Нам нужно зная эти вводные посчитать свою зарплату.
Логика нужной нам формулы ясна. С помощью функции ПЕРЕКЛЮЧ мы будем перебирать дни недели и в соответствующий день умножать количество отработанных часов на ставку.
Но вот в чём загвоздка. У нас есть только даты, но не дни недели. Неужели мы полезем в календарь и будем печатать вручную дни недели в Excel? Нет, конечно. Для этого есть функция ДЕНЬНЕД.
Функция ДЕНЬНЕД возвращает, как это понятно из её названия, день недели из даты. Но делает она это не в виде названия дня, а в виде числа.
Вот её синтаксис:
=ДЕНЬНЕД(дата_в_числовом_формате; [тип])
Как видите, из обязательных аргументов у неё только дата, которую она преобразует в номер дня недели. Правда, нюанс в том, что в Excel по-умолчанию первым днём недели считается воскресенье. Поэтому, если ваша дата выпадает на воскресенье, то вы получите 1, понедельник - 2, вторник - 3 и так далее.
Но вы можете нумерацию дней недели переделать под себя. Второй необязательный аргумент у функции - [тип], отвечает за это переключение. При вводе этого аргумента автоматически появится подсказка с вариантами. Вам останется только выбрать нужный вам. Например, если вы выберете тип 2, то первым днём недели станет понедельник, вторник - 2 и так далее.
В будущем мы ещё подробно поговорим о функциях дат и времени, а пока вернёмся к нашему примеру. Итак, разобравшись с определением дней недели пишем нашу формулу:
=B2*ПЕРЕКЛЮЧ(ДЕНЬНЕД(A2;2);6;150;7;200;100)
6 - это суббота, возвращаем 150 рублей. 7 - воскресенье - 200 рублей. Все остальные дни - 100 рублей. Затем получившееся число умножаем на ячейку B2, где у нас количество отработанных часов.
Таким образом, в этом примере мы использовали в качестве аргумента выражение функции ПЕРЕКЛЮЧ формулу, которая находила номер дня недели.
Пример 4: Выбор формулы из выпадающего списка
Вот интересный пример, с помощью функции ПЕРЕКЛЮЧ вы можете выбрать нужную формулу из выпадающего списка значений.
Допустим вы уже год ведёте канал на Дзене и отслеживаете его статистику. В частности, количество дочитываний ваших материалов. У вас есть разбивка данных по месяцам, но вам интересно было бы с помощь инструментов Excel как-то обработать эту статистику. Например узнать сумму дочитываний за все месяцы, среднее количество, месяц с максимальным и минимальным количеством.
Для красоты и порядка у нас есть выпадающий список этих значений и нам надо, чтобы в зависимости от выбранного, в одной ячейке менялась формула и пересчитывала результат.
Пара замечаний. Во-первых, я надеюсь, вы, конечно, понимаете, что все наши примеры довольно утрированны в своей простоте. В реальной жизни нам не нужно писать формулы, чтобы посмотреть статистические данные для 12 значений. Намного быстрее просто выделить их мышкой и посмотреть готовые ответы в нижней панели Excel. Но когда в реальной жизни вы столкнётесь с таблицами, содержащими сотни и тысячи строк данных, то тогда вы оцените по достоинству всю мощь и красоту формул Excel.
А во-вторых, если вы не умеете делать выпадающие списки, то не сможете самостоятельно повторить этот пример. Их создание немного выбивается из тематики изучения формул и функций, и больше относится к инструментам Excel. Если вам это интересно, то я мог бы в качестве побочного материала рассказать все способы создания выпадающих списков. Напишите об этом в комментариях. А пока вы можете познакомиться с этим примером скачав файл по ссылке.
Вернёмся к нашему примеру. В ячейку C3 мы должны написать следующую формулу:
=ПЕРЕКЛЮЧ(B3;
"Сумма";СУММ(F3:F14);
"Среднее";СРЗНАЧ(F3:F14);
"Максимальное";МАКС(F3:F14);
"Минимальное";МИН(F3:F14);
"")
Итак, ячейка B3 у нас содержит выпадающий список. Функция ПЕРЕКЛЮЧ проверяет, что из этого списка выбрано в данный момент. Если выбрано "Сумма", то с она с помощью функции СУММ суммирует все значения с января по декабрь. Если выбрано "Среднее", то с помощью функции СРЗНАЧ находит среднее арифметическое значение дочитываний. Если выбрано "Мксимальное" или "Минимальное", то с помощью функций МАКС и МИН соответственно находит месяц с максимальным и месяц с минимальным количеством дочитываний.
Обратите внимания, что последним, значением по умолчанию, мы выбрали пустую строку (""). Формула вернёт её в том случае, если мы ничего не выберем в выпадающем списке. Соответственно ничего в ячейке C3 и не отобразится.
Давайте посмотрим, что у нас получилось.
Когда мы выбрали "Сумма", то результатом оказалось 17702. Именно столько дочитываний было на канале за год.
При выборе "Среднее", мы получили 1475 дочитывания в среднем за месяц.
При выборе "Минимальное" мы узнали, что всего лишь 451 дочитывание было самым минимальным значением за год. Действительно, это было в сентябре.
Ну и максимальное значение - 2407 в мае.
А если мы в не выберем ни одно значение в выпадающем списке, то и вернётся пустая строка.
А какие показатели у вашего канала? Хвалитесь в комментариях))
На этом мы заканчиваем рассматривать функцию ПЕРЕКЛЮЧ. Надеюсь теперь она станет вам верным помощником в вашей работе. А в следующий раз мы научимся работать с ошибками с помощью логических функций.
Не переключайтесь;)
