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

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

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

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

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

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

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

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

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

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

«Хорошо, но не вау» — скажете вы и будете правы. Далее — увлекательнее.

Работа с спектрами и функциями

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как можно применять функцию CHOOSE (ВЫБОР)?

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

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

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

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

• 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 попадают в 1-ый квартал. В таблице, представленной ниже, Вы сможете узреть номер денежного квартала под каждым номером месяца.

В функцию 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 (ВЫБОР) для расчёта даты последующего пн..

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

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

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

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

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

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

Функция Excel: подбор параметра

Программка Excel веселит собственных юзеров обилием нужных инструментов и функций. К одной из таковых, непременно, можно отнести Подбор параметра. Этот инструмент дозволяет отыскать изначальное значение исходя из конечного, которое планируется получить. Давайте разберемся, как работать с данной функцией в Эксель.

Для чего нужна функция

Как было уже выше упомянуто, задачка функции Подбор параметра состоит в нахождении исходного значения, из которого можно получить данный конечный итог. В целом, эта функция похожа на Поиск решения (тщательно вы сможете с ней ознакомиться в нашей статье – “Поиск решения в Excel: пример использования функции”), но, при всем этом является наиболее обычной.

Использовать функцию можно только в одиночных формулах, и если будет нужно выполнить вычисления в остальных ячейках, в их придется все деяния выполнить поновой. Также функционал ограничен количеством обрабатываемых данных – лишь одно изначальное и конечное значения.

Внедрение функции

Давайте перейдем к практическому примеру, который дозволит лучшим образом осознать, как работает функция.

Итак, у нас есть таблица с списком спортивных продуктов. Мы знаем лишь сумму скидки (560 руб. для первой позиции) и ее размер, который для всех наименований однообразный. Предстоит узнать полную стоимость продукта. При всем этом принципиально, чтоб в ячейке, в которой в предстоящем отразится сумма скидки, была записана формула ее расчета (в нашем случае – умножение полной суммы на размер скидки).

Итак, метод действий последующий:

1. Перебегаем во вкладку “Данные”, в которой жмем на клавишу “Анализ “что если” в группе инструментов “Прогноз”. В раскрывшемся перечне избираем “Подбор параметра” (в ранешних версиях клавиша может находиться в группе “Работа с данными”).
2. На дисплее покажется окно для подбора параметра, которе необходимо заполнить:
   • в значении поля “Установить в ячейке” пишем адресок с финишными данными, которые нам известны, т.е. это ячейка с суммой скидки. Заместо ручного ввода координат можно просто щелкнуть по подходящей ячейке в самой таблице. При всем этом курсор должен быть в соответственном поле для ввода инфы.

Решение уравнений при помощи подбора параметра

Невзирая на то, что это не основное направление использования функции, в неких вариантах, когда идет речь про одну неведомую, она может посодействовать в решении уравнений.

К примеру, нам необходимо решить уравнение: 7x+17x-9x=75 .

1. Пишем выражение в вольной ячейке, заменив знак x на адресок ячейки, значение которой необходимо отыскать. В итоге формула смотрится так: =7*D2+17*D2-9*D2 .
2. Щелкаем Enter и получаем итог в виде числа 0, что полностью разумно, потому что нам лишь предстоит вычислить значение ячейки D2, которе и является “иксом” в нашем уравнении.

• В значении поля “Установить в ячейке” указываем координаты ячейки, в которой мы написали уравнение (т.е. B4).
• В значении, согласно уравнению, пишем число 75.
• В поле “Изменяя значения ячейки” указываем координаты ячейки, значение которой необходимо отыскать. В нашем случае – это D2.
• Когда все готово, жмем OK.

Заключение

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