Содержание:
Вчера на марафоне 30 функций Excel за 30 дней мы разобрались в деталях нашей операционной среды с помощью функции ИНФОРМАЦИЯ (ИНФОРМ) и обнаружила, что больше не может помочь нам с проблемами памяти. Ни наша, ни эксельская память!
На пятый день марафона мы изучим функцию ВЫБЕРИТЕ (ВЫБОР). Эта функция относится к категории Ссылки и массивы, он возвращает значение из списка возможных вариантов в соответствии с числовым индексом. Стоит отметить, что в большинстве случаев лучше выбрать другую функцию, например, ИНДЕКС (ИНДЕКС) и МАТЧ (Более раскрыто) или ВПР (ВПР). Мы рассмотрим эти функции позже в этом марафоне.
Итак, обратимся к имеющейся у нас информации и примерам по функции ВЫБЕРИТЕ (ВЫБОР), посмотрим в действии, а также отметим слабые места. Если у вас есть другие советы и примеры для этой функции, поделитесь ими в комментариях.
Функция 05: ВЫБЕРИТЕ
Функция ВЫБЕРИТЕ (SELECT) возвращает значение из списка, выбирая его по числовому индексу.
Как использовать функцию ВЫБРАТЬ?
Функция ВЫБЕРИТЕ (SELECT) может вернуть элемент в списке по определенному номеру, например:
- По номеру месяца верните номер финансового квартала.
- По дате начала рассчитайте дату следующего понедельника.
- По номеру магазина покажите объем продаж.
Синтаксис ВЫБЕРИТЕ
Функция ВЫБЕРИТЕ (SELECT) имеет следующий синтаксис:
CHOOSE(index_num,value1,value2,…)
ВЫБОР(номер_индекса;значение1;значение2;…)
- номер_индекса (index_number) должно находиться в диапазоне от 1 до 254 (или от 1 до 29 в Excel 2003 и более ранних версиях).
- номер_индекса (index_number) можно ввести в функцию как число, формулу или ссылку на другую ячейку.
- номер_индекса (index_number) будет округлено до ближайшего целого числа.
- Аргументы ценностное (значение) может быть числом, ссылкой на ячейку, именованным диапазоном, функцией или текстом.
Ловушки ВЫБИРАЙТЕ (ВЫБОР)
В Excel 2003 и более ранних версиях функция ВЫБЕРИТЕ (SELECT) поддерживал только 29 аргументов. ценностное (значение).
Гораздо удобнее искать по списку на листе, чем вводить все элементы в формулу. С функциями ВПР (ВПР) или МАТЧ (MATCH) Вы можете обращаться к спискам значений, расположенным в листах Excel.
Пример 1: финансовый квартал по номеру месяца
Функция ВЫБЕРИТЕ (SELECT) отлично работает с простыми списками чисел в качестве значений. Например, если ячейка B2 содержит номер месяца, функция ВЫБЕРИТЕ (SELECT) может рассчитать, к какому финансовому кварталу он принадлежит. В следующем примере финансовый год начинается в июле.
В формуле перечислены 12 значений, соответствующих месяцам с 1 по 12. Финансовый год начинается в июле, поэтому месяцы 7, 8 и 9 попадают в первый квартал. В таблице ниже вы можете увидеть номер финансового квартала под каждым номером месяца.
В функции ВЫБЕРИТЕ (ВЫБРАТЬ) Номера кварталов необходимо вводить в том порядке, в котором они указаны в таблице. Например, в списке значений функции ВЫБЕРИТЕ (ВЫБРАТЬ) в позициях 7, 8 и 9 (июль, август и сентябрь) должны быть номером 1.
=CHOOSE(C2,3,3,3,4,4,4,1,1,1,2,2,2)
=ВЫБОР(C2;2;3;3;3;4;4;4;1;1;1;2;2;2)
Введите номер месяца в ячейку C2 и функцию ВЫБЕРИТЕ (ВЫБРАТЬ) рассчитает номер финансового квартала в ячейке C3.
Пример 2. Вычислить дату следующего понедельника.
Функция ВЫБЕРИТЕ (SELECT) может работать в сочетании с функцией WEEKDAY (DAYWEEK) для расчета будущих дат. Например, если вы являетесь членом клуба, который собирается каждый понедельник вечером, то, зная сегодняшнюю дату, вы можете рассчитать дату следующего понедельника.
На рисунке ниже показаны порядковые номера каждого дня недели. Столбец H для каждого дня недели содержит количество дней, которые нужно добавить к текущей дате, чтобы получить следующий понедельник. Например, вам нужно добавить к воскресенью только один день. А если сегодня понедельник, то до следующего понедельника еще семь дней.
Если текущая дата находится в ячейке C2, то формула в ячейке C3 использует функции WEEKDAY (ДЕНЬ) и ВЫБЕРИТЕ (ВЫБРАТЬ), чтобы рассчитать дату следующего понедельника.
=C2+CHOOSE(WEEKDAY(C2),1,7,6,5,4,3,2)
=C2+ВЫБОР(ДЕНЬНЕД(C2);1;7;6;5;4;3;2)
Пример 3: Показать объем продаж для выбранного магазина
Вы можете использовать функцию ВЫБЕРИТЕ (ВЫБРАТЬ) в сочетании с другими функциями, такими как SUM (СУММ). В этом примере мы получим итоги продаж по конкретному магазину, указав его номер в функции ВЫБЕРИТЕ (SELECT) в качестве аргумента, а также список диапазонов данных для каждого магазина для расчета итоговых значений.
В нашем примере номер магазина (101, 102 или 103) вводится в ячейку C2. Чтобы получить значение индекса, например 1, 2 или 3, вместо 101, 102 или 103, используйте формулу: =С2-100.
Данные о продажах для каждого магазина представлены в отдельном столбце, как показано ниже.
Внутри функции SUM (СУММ) функция будет выполнена первой ВЫБЕРИТЕ (SELECT), который вернет желаемый диапазон суммирования, соответствующий выбранному хранилищу.
=SUM(CHOOSE(C2-100,C7:C9,D7:D9,E7:E9))
=СУММ(ВЫБОР(C2-100;C7:C9;D7:D9;E7:E9))
Это пример ситуации, когда гораздо эффективнее использовать другие функции, такие как ИНДЕКС (ИНДЕКС) и МАТЧ (ПОИСК). Позже в нашем марафоне мы увидим, как они работают.