Получение элемента из набора по номеру функцией ВЫБОР (CHOOSE)
Как и в случае с уже ранее разбиравшейся ранее функцией СУММПРОИЗВ (SUMPRODUCT), эта функция, на первый взгляд, кажется примитивной и банальной. Но первое мнение обманчиво, поверьте мне :)
Основное назначение функции ВЫБОР - это извлекать из набора нужный нам элемент по его номеру (индексу). Синтаксис этой функции в базовом варианте прост:
=ВЫБОР(Номер_элемента; Элемент1; Элемент2 ... )
где
- Номер_элемента - порядковый номер элемента, который нам нужен (начиная с 1)
- Элемент 1, 2... - список элементов (максимум 254)
Например, если у нас есть список городов и мы хотим получить N-й по счету из них, то можно наваять что-то типа:
Само-собой, что-то похожее можно сделать и другими способами. Например, если бы список элементов был не прописан прямо в функции, а содержался в ячейках листа, то проще и правильнее было бы воспользоваться функцией ИНДЕКС (INDEX), которую мы уже разбирали:
Однако, в некоторых случаях, оказывается удобнее все-таки использовать именно ВЫБОР, а не альтернативы - иногда проще прописать массив значений в формулу сразу и жестко, чем пытаться вычислять его элементы "на лету" или хранить где-то на листе. Давайте разберем пару примеров, для наглядности.
Вот так, например, можно реализовать формулой определение количества рабочих дней в текущем месяце (значения взяты из производственного календаря за 2017 год для примера):
Очень похожим образом можно сделать определение номера квартала обычного и финансового года (начинается в июле) для заданной даты:
"Неплохо, но не вау" - скажете вы и будете правы. Дальше - интереснее.
Работа с диапазонами и функциями
На самом деле, в качестве элементов списка могут выступать не только числа или текст, но и ячейки и даже целые диапазоны. А это уже открывает простор для более серьезных конструкций. Допустим, нам нужно просуммировать данные квартала по его номеру:
Функция ВЫБОР, в данном случае, выдает на выходе ссылку на диапазон-столбец, а функция СУММ потом складывает все его ячейки. В некотором смысле, получается альтернатива функциям ДВССЫЛ (INDIRECT) и СМЕЩ (OFFSET), которые тоже могли бы помочь в такой ситуации.
Вложенные функции
Дальше-больше. В качестве списка элементов могут быть не только диапазоны, а функции. Например, можно на выбор вычислять сумму, среднее и медиану для заданного диапазона, переключаясь между этими функциями на лету:
Компактная замена классической функции проверки условий ЕСЛИ (IF), которую тут пришлось бы еще и вкладывать друг в друга два раза.
Склеивание диапазонов
Если в наборе перечислены диапазоны, а в качестве номера извлекаемого элемента - не просто число, а массив констант в фигурных скобках, то функция ВЫБОР выдаст на выходе массив, представляющий собой склейку соответствующих диапазонов. Т.е. в приведенном выше примере про кварталы, можно посчитать сумму за первый и третий кварталы одной формулой:
На практике, такой трюк бывает удобно использовать, чтобы виртуально, прямо в формуле, а не на листе, переставить местами столбцы и реализовать, например, трюк с "левым ВПР", никак не меняя при этом саму таблицу:
Ссылки по теме
- Зачем нужна функция СУММПРОИЗВ (SUMPRODUCT)
- Как извлекать значения левее поискового столбца (левый ВПР)
Это платный контент, о котором обычно рассказывают на продвинутых тренингах по Excel..
Боюсь тогда предположить, что вы рассказываете на своих тренингах?...