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

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

Добрый день!

В этой статье мы рассмотрим еще одну функцию с раздела «Ссылки и массивы» и главным героем моей статьи станет функция ВЫБОР в Excel. Не могу порадовать вас ее большим функционалом, но, тем не менее, ее можно использовать, это уже зависит от ваших предпочтений и задач. Но, забегая на перед могу сказать, что во многих ситуациях наиболее эффективным будет использование функций ПОИСКПОЗ, ВПР и ИНДЕКС. Хотя может быть у вас и есть опыт ее использования, получая преимущества и удобство, если так, поделитесь в комментариях.

А что же именно делает функция ВЫБОР в Excel, и в каких случаях она используется. Эта функция позволяет согласно указанного числового индекса извлекать со списка нужное значение. Например, =ВЫБОР(2; «Зима»; «Весна»; «Лето»; «Осень»), согласно условиям получим значение «Весна».

Используется функция ВЫБОР в Excel во многих вариациях, это может быть:

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

Теперь давайте рассмотрим синтаксис который использует функция ВЫБОР в Excel в своей работе:

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

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

  • в случае, когда номер индекса, является дробью то производится его округление к меньшему целому;
  • в случае когда номер индекса меньше единицы или больше, нежели номер самого последнего значения в перечне, то функция ВЫБОР в Excel получает значение ошибки #ЗНАЧ!;
  • в случае, когда номер индекса равняется единице, рассматриваемая нами функция возвратит нам значение 1, при условии, что номер равен 2, то будет возвращено 2 и так далее.
  • значение №1; значение №2; . – обязательным является только «значение №1», а остальные значения, кстати их может быть от 1 до 254, не являются обязательными. Аргументы значений могут быть функциями, числами, текстом, ссылками на ячейки, формулами или определенными именами.
  • Ну а теперь стоит рассмотреть пример, как работает функция ВЫБОР в Excel, итак, у нас будет система оценивания от 1 до 5 и в зависимости от оценки мы будем определять успешность студента. Будем считать, что первые 2 оценки: 1 и 2 – это отчисление, 3 – это пересдача, 4 – хорошо, 5 – отлично.

    Формула для решения этой задачи будет следующая:

    =ВЫБОР(D2;”отчислен”;”отчислен”;”пересдача”;”хорошо”;”отлично”)

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

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

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

    До новых встреч!

    “Если вы хотите знать, что именно Бог думает о деньгах, посмотрите, кому он их дает?!

    М. Бэринг

    Источник: topexcel.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

    Читайте также:  Функция сегодня в excel примеры

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

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

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

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

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

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

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

    Источник: www.planetaexcel.ru

    Функция ВЫБОР() в Excel

    Функция ВЫБОР() , английский вариант 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-х и т.д. значений.

    Источник: excel2.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Источник: office-guru.ru

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

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

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

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

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

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

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

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

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

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

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

    

    Функция ВЫБОР в 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 – формулу для расчета даты следующего вторника:

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

    Источник: exceltable.com

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Источник: user-life.com