Функция ВЫБОР в excel

history 6 апреля 2014 г.
    Группы статей
  • стандартные функции

Функция ВЫБОР() , английский вариант CHOOSE(), возвращает значение из заданного списка аргументов-значений в соответствии с заданном индексом. Например, формула =ВЫБОР(2;»ОДИН»;»ДВА»;»ТРИ») вернет значение ДВА. Здесь 2 — это значение индекса, а «ОДИН»;»ДВА»;»ТРИ» это первый, второй и третий аргумент соответственно .

Функция ВЫБОР() в Excel достаточно проста: Вы задаете массив значений, и порядковый номер (индекс) значения, которое нужно вывести из этого массива.

Синтаксис функции ВЫБОР()

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

  • Номер_индекса — номер выбираемого аргумента-значения. Номер_индекса должен быть числом от 1 до 254. Индекс можно ввести формулой или ссылкой на ячейку, содержащую число в диапазоне от 1 до 254;
  • Если номер_индекса равен 1, то функция ВЫБОР() возвращает значение1; если он равен 2, возвращается значение2 и так далее;
  • Если номер_индекса меньше 1 или больше, чем номер последнего значения в списке, то функция ВЫБОР возвращает значение ошибки #ЗНАЧ!
  • Если номер_индекса представляет собой дробь, то он усекается до меньшего целого;
  • сами значения — значение1 ;значение2; . могут быть числами, текстовыми строками и ссылками на диапазоны ячеек. Если в качестве значений введены конкретные значения, то функция возвращает одно из этих значений в зависимости от индекса, а если в качестве значений введены ссылки на ячейки, то функция возвращает соответственно ссылки.

Примеры

В диапазоне А8:А12 содержатся школьные оценки от 1 до 5. Необходимо вывести оценку прописью, т.е. «кол»;»неуд»;»удовлетворительно»;»хорошо»;»отлично».

Формула =ВЫБОР(A8;»кол»;»неуд»;»удовлетворительно»;»хорошо»;»отлично») решает эту задачу по выбору значений из списка в Excel. Альтернативное решение можно построить на основе формулы

Данный подход можно использовать для отображения дня недели прописью

=ВЫБОР(A8;»понедельник»;»вторник»;»среда»;»четверг»;»ПЯТНИЦА. «;»СУББОТА!!»;»ВОСКРЕСЕНЬЕ!») В этом случае значение в ячейке А8 может принимать значение от 1 до 7.

или времени года. Формула =ВЫБОР(2;»зима»;»весна»;»лето»;»осень») вернет весна .

Можно воспользоваться этой функцией для склонения слов, например, слова Час : 0 часов, 1 час, 2 часа, .

Ссылочная форма

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

В ячейку А33 пользователь вводит номер квартала (индекс для функции ВЫБОР() ). В качестве аргументов указаны 4 диапазона для каждого квартала. При выбор первого квартала будет подсчитана сумма продаж из диапазона А27:А29 , при выборе второго — B27:B29 и т.д.

В файле примера также имеются другие примеры, например, подсчет первых 2-х, 3-х и т.д. значений.


Функция ВЫБОР в Excel ее синтаксис и примеры использования

Функция ВЫБОР находит и возвращает значение из списка аргументов, используя номер индекса. Может обработать до 254 значений. Имеет простой синтаксис, но достаточно широкие возможности. Рассмотрим лучшие из них на конкретных практических примерах.

Аргументы и особенности синтаксиса

Синтаксис функции: =ВЫБОР( номер индекса; знач. 1; знач. 2; … ).

  1. Номер индекса – порядковый номер выбираемого аргумента из списка значений. Может быть числом от 1 до 254, ссылкой на ячейку с числом от 1 до 254, массивом или формулой.
  2. Знач. 1; знач. 2; … — список аргументов от 1 до 254, из которого выбирается значение или действие, соответствующее номеру индекса. Первое значение – обязательный аргумент. Последующие – нет. Список аргументов-значений – числа, ссылки на ячейки, имена, формулы, функции или текст.

Если указать номер индекса 1, то функция вернет первое значение их перечня. Если индекс равен 2 – второе значение. И так далее. Если список аргументов состоит из конкретных значений, то формула ВЫБОР возвращает одно из значений согласно индексу.

ВЫБОР.

Если аргументы – ссылки на ячейки, то функция вернет ссылки.

СУММ и иВЫБОР.

ВЫБОР возвращает ссылку на интервал В1:В7. А функция СУММ использует этот результат в качестве аргумента.

Аргументы-значения могут быть представлены отдельными значениями:

Аргументы.

Особенности использования функции:

  1. Если индекс представлен дробью, то функция возвращает меньшее целое значение. Меньшее целое значение.
  2. Если индекс – массив значений, то функция ВЫБОР вычисляет каждый аргумент.
  3. Если индекс не совпадает с номером аргумента в списке (меньше 1 или больше последнего значения), то функция выдает ошибку #ЗНАЧ!.



Функция ВЫБОР в Excel: примеры

Функция ВЫБОР решает задачи по представлению значений из списка в Excel. Например, диапазон А2:А8 содержит номера недели от 1 до 7. Необходимо отобразить день недели прописью, то есть «понедельник», «вторник», «среда», «четверг», «пятница», «суббота», «воскресенье».

Дни недели.

По такому же принципу можно выводить отметки, баллы, времена года прописью.

Теперь рассмотрим можно склонять слова с помощью Excel. Например, слово «рубль»: «0 рублей», «1 рубль», «2 рубля», «3 рубля», «4 рубля», «5 рублей» и т.д.

Склонять слова.

С помощью функции ВЫБОР можно вернуть ссылку на диапазон. Это позволяет делать вычисления над массивами данных по заданному пользователем критерию. Рассмотрим пример суммирования выручки в заданном пользователем магазине.

Имеются данные по выручке в нескольких торговых точках:

Выручка.

Формула рассчитывает выручку в магазине, заданном пользователем. В ячейке А8 можно изменить номер торговой точки –ВЫБОР вернет для функции СУММ ссылку на другой интервал. Если поставить в ячейке А8 цифру 2, формула подсчитает выручку для второго магазина (результат СУММ для диапазона В2:В5).

С помощью функции ВЫБОР можно задать аргумент для функции СУММ так, чтобы получить результат подсчета 2, 3, 4 и т.д. первых значений диапазона:

Пример.

Формула суммирует диапазон А1:А4. Вторая часть диапазона функции СУММ задана с помощью функции ВЫБОР.

Данная функция хорошо обрабатывает в качестве значений простые списки чисел. Поэтому с ее помощью можно вычислить по номеру месяца финансовый квартал.

Таблица с номерами месяцев и кварталов:

Таблица.

Так как финансовый год начался в апреле, месяцы 4, 5 и 6 попали в первый квартал. При введении аргументов функции, номера кварталов необходимо вводить в том порядке, в каком они находятся в таблице.

Фин.квартал.

В ячейку D8 пользователь вводит номер месяца. В ячейке D9 функция ВЫБОР вычисляет номер финансового квартала.

Можно так же вычислять грядущие даты. Эту задачу она решает в совокупности с функцией ДЕНЬНЕД. Например, пользователь делает небольшие отчеты о проделанной работе и сдает их начальнику каждый вторник. Можно рассчитать дату следующего вторника.

В первом столбце вспомогательной таблицы – номера дней недели. В третьем столбце – количество дней, которое нужно прибавить к текущей дате, чтобы получить следующий вторник. Например, к понедельнику необходимо добавить 1 день, ко вторнику – 7 дней (до следующего вторника).

Рассчитать дату.

В ячейку F2 запишем текущую дату (СЕГОДНЯ()). А в ячейку F3 – формулу для расчета даты следующего вторника:

СЕГОДНЯ и ДЕНЬНЕД.

Индекс определяется с помощью функции ДЕНЬНЕД, которая возвращает для заданной даты соответствующего дня недели.


Функция выбор в Excel

Функция «ВЫБОР» позволяет выполнить быстрый выбор из большого списка значений и данных и, опираясь на его индекс, указать определенное значение.

Найти оператор «ВЫБОР» можно в категории «Ссылки и массивы» и имеет следующий внешний вид: «=ВЫБОР(номер_индекса;значение1;значение2;. )».

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

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

В отличии от предыдущего аргумента, значениями могут быть ссылки, числа, тексты, формулы, различные функции.

Попробуем на практике применить оператор «ВЫБОР».

Введем исходные данные.

Функция выбор в Excel

В ячейке с наименованием первого месяца вызовем мастера функций, который позволит вызвать функцию «ВЫБОР» и приступим к заполнению аргументов.

Функция выбор в Excel

В диалоговом окне заполним все поля. «Номер индекса» — здесь мы укажем адрес первой ячейки. В «Значения» — введем названия месяцев и применим оператор.

Функция выбор в Excel

С помощью маркера авто заполнения заполним все ячейки таблицы и посмотрим на результат.

Функция выбор в Excel

Формула корректно скопировалась и заполненные ячейки имеют правильное наименование.

Функция выбор в Excel

Но что делать, порядок индекса различный, а нам нужно сделать так, чтобы все корректно сработало? Попробуем оператор «ВЫБОР» для произвольного порядка расположения значений.

Заполним исходные значения. В первый столбец введем, для примера, фамилии учащихся, во втором их успеваемость в оценках от 1 до 5, а в третьем будем использовать оператор «ВЫБОР» чтобы получить характеристику или описание оценки.

«Значениями» у нас будут «очень плохо», «плохо», «удовлетворительно», «хорошо», «отлично». Выберем ячейку и вызовем мастер функций. Заполним все поля соответствующими аргументами и применим операцию.

Функция выбор в Excel

С помощью маркера авто заполнения заполним весь столбец формулой, как это было сделано ранее в предыдущем способе.

Функция выбор в Excel

Оператор «ВЫБОР» можно также можно использовать в сочетании с другими различными операторами.

Функция выбор в Excel

К примеру, у нас есть таблица с данными по торговым точкам с их каждодневной выручкой. Оператор «ВЫБОР» поможет нам узнать сумму выручки за все дни для определенной точки.

Укажем ячейку, куда нам нужно будет выводить данные и вызовем мастера функций.
В появившемся окне мастера функций выберем математическую категорию и в ней оператор «СУММ», имеющий синтаксис: «=СУММ(число1;число2;. )».

Вместо первого аргумента укажем оператор «ВЫБОР» и заполним все его аргументы. Вместо значений укажем диапазоны для каждой торговой точки и применим операцию.

Функция выбор в Excel

Теперь можно попробовать посчитать сумму выручки для конкретной точки. Введем в поле с номером торговой точки «2» и получаем правильный результат – сумма выручки была корректно посчитана для всех дней второй торговой точки.


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


Формула ВЫБОР и примеры выполнения нескольких условий в Excel

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

Примеры выбора выполнения формулы по условию пользователя

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

Пример выбора выполнения формулы.

Синтаксис функции ВЫБОР:

аргументы ВЫБОР.

Первый аргумент функции ВЫБОР называется «Номер индекса» и определяет, который из очередных аргументов должен быть возвращен через функцию. Данный аргумент может принимать числовые значения от 1-го и до максимального количества последующих аргументов, то есть до 254. Очередные последующие 254 аргумента содержат значения, которое и будет возвращать функция. Если аргумент «Номер индекса» содержит число 1, функция будет возвращать содержимое второго аргумента. А если номер индекса = 2, будет возвращен третий аргумент и т.д.

Последующие аргументы после первого «Номер индекса» называются «Значение1»;«Значение2»… «Значение254». В выше приведенной формуле они определяют 3 способа агрегирования данных по продажам, которые может выбирать пользователь:

  1. Функция СУММ – суммирует числа в диапазоне ячеек.
  2. СЧЁТ – подсчитывает количество непустых ячеек с числами.
  3. СРЗНАЧ – выводит среднее арифметическое значение для диапазона чисел в ячейках.

Значение в первом аргументе функции ВЫБОР вычисляется функцией ПОИСКПОЗ, которая в данном примере возвращает число 1, 2 или 3 в зависимости от того какую позицию в выпадающем списке выберет пользователь в ячейке E6:

выпадающий список.

Сам выпадающий список заполняется значениями ссылаясь на диапазон ячеек E2:E4.

Если пользователь выберет из выпадающего списка опцию «Сумма», функция ПОИСКПОЗ вернет в результате вычисления число 1 для первого аргумента функции ВЫБОР. В итоге целая формула вернет результат вычисления функции СУММ, которая находится во втором аргументе. Если же пользователь выберет опцию «Количество», то ПОИСКПОЗ вернет число 2, а целая формула результат вычисления СЧЁТ из третьего аргумента.

Как показано выше на рисунке пользователь выбрал опцию «Среднее», поэтому функция ПОИСКПОЗ вернула число 3, а потом ВЫБОР выполнила функцию СРЗНАЧ из четвертого аргумента. В результате целая формула вернула ее среднее значение.

Пример использования формул ПРОМЕЖУТОЧНЫЕ.ИТОГИ и ВЫБОР в Excel

Альтернативным способом применения в формулах функций ВЫБОР и ПОИСКПОЗ является использования их вместе с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Данная функция очень универсальная и позволяет выполнять сразу несколько методов агрегации данных, которые можно применять к одному и тому же диапазону ячеек. Когда первый аргумент функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ содержит значение 1 – выводится среднее арифметическое чисел в ячейках того же диапазона:

ПРОМЕЖУТОЧНЫЕ.ИТОГИ и ВЫБОР.

Например, если первый аргумент функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ равен числу 9, значения диапазона ячеек будут просуммированы. Если же первый аргумент = 2, тогда подсчитывается количество ячеек этого же диапазона:

количество методов обработки диапазонов.

Внутри функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ можно использовать функции ВЫБОР и ПОИСКПОЗ, для определения метода агрегации данных. Данный способ составления формулы более рестрикционный, чем обычное размещение формул внутри функции ВЫБОР. Ведь количество методов обработки диапазонов данных функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ ограничено до 22-х.

Adblock
detector