Получение элемента из набора по номеру функцией ВЫБОР (CHOOSE)

Как и в случае с уже ранее разбиравшейся ранее функцией СУММПРОИЗВ (SUMPRODUCT), эта функция, на первый взгляд, кажется примитивной и банальной. Но первое мнение обманчиво, поверьте мне :)

Основное назначение функции ВЫБОР - это извлекать из набора нужный нам элемент по его номеру (индексу). Синтаксис этой функции в базовом варианте прост:

=ВЫБОР(Номер_элемента; Элемент1; Элемент2 ... )

где

  • Номер_элемента - порядковый номер элемента, который нам нужен (начиная с 1)
  • Элемент 1, 2... - список элементов (максимум 254)

Например, если у нас есть список городов и мы хотим получить N-й по счету из них, то можно наваять что-то типа:

Классический сценарий применения ВЫБОР

Само-собой, что-то похожее можно сделать и другими способами. Например, если бы список элементов был не прописан прямо в функции, а содержался в ячейках листа, то проще и правильнее было бы воспользоваться функцией ИНДЕКС (INDEX), которую мы уже разбирали:

ИНДЕКС вместо ВЫБОР

Однако, в некоторых случаях, оказывается удобнее все-таки использовать именно ВЫБОР, а не альтернативы - иногда проще прописать массив значений в формулу сразу и жестко, чем пытаться вычислять его элементы "на лету" или хранить где-то на листе. Давайте разберем пару примеров, для наглядности.

Вот так, например, можно реализовать формулой определение количества рабочих дней в текущем месяце (значения взяты из производственного календаря за 2017 год для примера):

Подсчет количества рабочих дней в месяц

Очень похожим образом можно сделать определение номера квартала обычного и финансового года (начинается в июле) для заданной даты:

Номер обычного и финансового квартала

"Неплохо, но не вау" - скажете вы и будете правы. Дальше - интереснее.

Работа с диапазонами и функциями

На самом деле, в качестве элементов списка могут выступать не только числа или текст, но и ячейки и даже целые диапазоны. А это уже открывает простор для более серьезных конструкций. Допустим, нам нужно просуммировать данные квартала по его номеру:

ВЫБОР и работа с диапазонами

Функция ВЫБОР, в данном случае, выдает на выходе ссылку на диапазон-столбец, а функция СУММ потом складывает все его ячейки. В некотором смысле, получается альтернатива функциям ДВССЫЛ (INDIRECT) и СМЕЩ (OFFSET), которые тоже могли бы помочь в такой ситуации.

Вложенные функции

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

Вычисление разных итогов с ВЫБОР

Компактная замена классической функции проверки условий ЕСЛИ (IF), которую тут пришлось бы еще и вкладывать друг в друга два раза.

Склеивание диапазонов

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

Склеивание диапазонов функцией ВЫБОР

На практике, такой трюк бывает удобно использовать, чтобы виртуально, прямо в формуле, а не на листе, переставить местами столбцы и реализовать, например, трюк с "левым ВПР", никак не меняя при этом саму таблицу:

Левый ВПР с ВЫБОР

Ссылки по теме


14.04.2017 16:36:40
Николай, то что Вы раскрыли в этой статье про функцию ВЫБОР – обычно не раскрывают в открытом доступе..
Это платный контент, о котором обычно рассказывают на продвинутых тренингах по Excel..
Боюсь тогда предположить, что вы рассказываете на своих тренингах?...
02.08.2017 00:10:15
Николай, подскажите, пожалуйста функция "ВЫБОР" будет ли работать с "умной" таблицей точно также как это умеют делать функции "ИНДЕКС" и "ПОИСКПОЗ"?