30 функций Excel за 30 дней: ВЫБОР (CHOOSE)

Вчера в марафоне 30 функций Excel за 30 дней мы выяснили детали нашей операционной среды с помощью функции INFO (ИНФОРМ) и обнаружили, что она больше не сможет помочь нам в вопросах, связанных с памятью. Ни с нашей, ни с памятью Excel!

На пятый день марафона мы займёмся изучением функции CHOOSE (ВЫБОР). Эта функция относится к категории Ссылки и массивы, она возвращает значение из списка возможных вариантов в соответствии с числовым индексом. Стоит отметить, что в большинстве случаев лучше выбрать другую функцию, например, INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ) или VLOOKUP (ВПР). Мы рассмотрим эти функции позже в рамках данного марафона.

Итак, давайте обратимся к имеющейся у нас информации и примерам по функции CHOOSE (ВЫБОР), посмотрим ее в деле, а также отметим слабые места. Если у Вас есть другие подсказки и примеры по этой функции, пожалуйста, делитесь ими в комментариях.

Функция 05: CHOOSE (ВЫБОР)

Функция CHOOSE (ВЫБОР) возвращает значение из списка, выбирая его в соответствии с числовым индексом.

Как можно использовать функцию CHOOSE (ВЫБОР)?

Функция CHOOSE (ВЫБОР) может вернуть позицию из списка, находящуюся под определенным номером, например:

• По номеру месяца вернуть номер финансового квартала.
• Отталкиваясь от начальной даты, вычислить дату следующего понедельника.
• По номеру магазина показать сумму продаж.

Синтаксис CHOOSE (ВЫБОР)

Функция CHOOSE (ВЫБОР) имеет следующий синтаксис:

CHOOSE(index_num,value1,value2,…)
ВЫБОР(номер_индекса;значение1;значение2;…)

• index_num (номер_индекса) должен быть между 1 и 254 (или от 1 до 29 в Excel 2003 и более ранних версиях).
• index_num (номер_индекса) может быть введён в функцию в виде числа, формулы или ссылки на другую ячейку.
• index_num (номер_индекса) будет округлен до ближайшего меньшего целого.
• аргументами value (значение) могут быть числа, ссылки на ячейки, именованные диапазоны, функции или текст.

Ловушки CHOOSE (ВЫБОР)

В Excel 2003 и более ранних версиях функция CHOOSE (ВЫБОР) поддерживала лишь 29 аргументов value (значение).

Выполнять поиск по списку гораздо удобнее на рабочем листе, чем вносить все элементы в формулу. С помощью функций VLOOKUP (ВПР) или MATCH (ПОИСКПОЗ) Вы можете ссылаться на списки значений, расположенные на листах Excel.

Пример 1: Финансовый квартал по номеру месяца

Функция CHOOSE (ВЫБОР) отлично работает с простыми списками чисел в качестве значений. Например, если ячейка B2 содержит номер месяца, функция CHOOSE (ВЫБОР) может вычислить, к какому финансовому кварталу он относится. В следующем примере финансовый год начинается в июле.

В формуле перечислено 12 значений, соответствующих месяцам от 1 до 12. Финансовый год начинается в июле, так что месяцы 7, 8 и 9 попадают в первый квартал. В таблице, представленной ниже, Вы можете увидеть номер финансового квартала под каждым номером месяца.

В функцию CHOOSE (ВЫБОР) номер квартала необходимо вводить в том порядке, в каком они расположены в таблице. Например, в списке значений функции CHOOSE (ВЫБОР) в позициях 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, и функция CHOOSE (ВЫБОР) вычислит номер финансового квартала в ячейке C3.

Пример 2: Рассчитываем дату следующего понедельника

Функция CHOOSE (ВЫБОР) может работать в сочетании с функцией WEEKDAY (ДЕНЬНЕД), чтобы вычислить грядущие даты. Например, если Вы состоите в клубе, который собирается каждый понедельник вечером, то, зная сегодняшнюю дату, Вы можете рассчитать дату следующего понедельника.

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

Если текущая дата записана в ячейке C2, то формула в ячейке C3 использует функции WEEKDAY (ДЕНЬНЕД) и CHOOSE (ВЫБОР) для расчёта даты следующего понедельника.

=C2+CHOOSE(WEEKDAY(C2),1,7,6,5,4,3,2)
=C2+ВЫБОР(ДЕНЬНЕД(C2);1;7;6;5;4;3;2)

Пример 3: Покажем сумму продаж для выбранного магазина

Вы можете использовать функцию CHOOSE (ВЫБОР) в сочетании с другими функциями, например, SUM (СУММ). В этом примере мы получим итоги продаж по определённому магазину, задав его номер в функции CHOOSE (ВЫБОР) в качестве аргумента, а также перечислив диапазоны данных по каждому магазину для подсчёта итогов.

В нашем примере номер магазина (101, 102 или 103) введён в ячейке C2. Чтобы получить значение индекса, такое как 1, 2 или 3, вместо 101, 102 или 103, используйте формулу: =C2-100.

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

Внутри функции SUM (СУММ) в первую очередь будет выполнена функция CHOOSE (ВЫБОР), которая вернет требуемый диапазон для суммирования, соответствующий выбранному магазину.

=SUM(CHOOSE(C2-100,C7:C9,D7:D9,E7:E9))
=СУММ(ВЫБОР(C2-100;C7:C9;D7:D9;E7:E9))

Это пример ситуации, когда гораздо эффективнее использовать другие функции, такие как INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ). Далее в нашем марафоне мы увидим, как они работают.