Формула выбор в excel примеры несколько условий

Формула выбор в 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 часа, .

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

Использование оператора ВЫБОР

Функция ВЫБОР относится к категории операторов «Ссылки и массивы». Её целью является выведение определенного значения в указанную ячейку, которому соответствует номер индекса в другом элементе на листе. Синтаксис этого оператора следующий:

Аргумент «Номер индекса» содержит ссылку на ячейку, где находится порядковый номер элемента, которому следующей группой операторов присваивается определенное значение. Этот порядковый номер может варьироваться от 1 до 254. Если задать индекс, превышающий данное число, то оператор выведет в ячейку ошибку. Если в качестве данного аргумента ввести дробное значение, то функция воспримет его, как ближайшее к данному числу меньшее целое значение. Если задать «Номер индекса», для которого нет соответствующего аргумента «Значение», то оператор будет возвращать в ячейку ошибку.

Следующая группа аргументов «Значение». Она может достигать количества 254 элементов. При этом обязательным является аргумент «Значение1». В данной группе аргументов указываются те значения, которым будет соответствовать номер индекса предыдущего аргумента. То есть, если в качестве аргумента «Номер индекса» выступает число «3», то ему будет соответствовать значение, которое внесено, как аргумент «Значение3».

В качестве значений могут выступать разнообразные виды данных:

  • Ссылки;
  • Числа;
  • Текст;
  • Формулы;
  • Функции и т. д.
Читайте также:  Значки в микроволновке своими руками

Теперь давайте рассмотрим конкретные примеры применения данного оператора.

Пример 1: последовательный порядок расположения элементов

Давайте посмотрим, как действует данная функция на простейшем примере. У нас имеется таблица с нумерацией от 1 до 12. Нужно согласно данным порядковым номерам с помощью функции ВЫБОР указать наименование соответствующего месяца во второй колонке таблицы.

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

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

После этого нам предстоит вручную вбить в группу полей «Значение» наименование месяцев. Причем каждому полю должен соответствовать отдельный месяц, то есть, в поле «Значение1» записываем «Январь», в поле «Значение2»«Февраль» и т. д.

После выполнения указанной задачи щелкаем по кнопке «OK» внизу окна.

Как видим, сразу в той ячейке, которую мы отметили ещё в первом действии, отобразился результат, а именно наименование «Январь», соответствующее первому номеру месяца в году.

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

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

    В предыдущем случае мы применили формулу ВЫБОР, когда все значения номеров индекса были расставлены по порядку. Но как работает данный оператор в случае, если указанные значения перемешаны и повторяются? Давайте рассмотрим это на примере таблицы с успеваемостью школьников. В первом столбце таблицы указана фамилия ученика, во втором оценка (от 1 до 5 баллов), а в третьем нам предстоит с помощью функции ВЫБОР дать данной оценке соответствующую характеристику («очень плохо», «плохо», «удовлетворительно», «хорошо», «отлично»).

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

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

    Группу полей «Значение» заполняем следующим образом:

    После того, как введение вышеуказанных данных произведено, щелкаем по кнопке «OK».

    Значение балла для первого элемента выведено в ячейку.

  • Для того, чтобы произвести подобную процедуру и для остальных элементов столбца, копируем данные в его ячейки при помощи маркера заполнения, как это производилось в Способе 1. Как видим, и на этот раз функция отработала корректно и вывела все результаты в соответствии с заданным алгоритмом.
  • Пример 3: использование в комбинации с другими операторами

    Но гораздо продуктивнее оператор ВЫБОР можно использовать в комбинации с другими функциями. Посмотрим, как это делается на примере применения операторов ВЫБОР и СУММ.

    Имеется таблица реализации продукции по торговым точкам. Она разбита на четыре столбца, каждый из которых соответствует определенной торговой точке. Выручка указана отдельно за определенную дату построчно. Наша задача — сделать так, чтобы после ввода номера торговой точки в определенную ячейку листа отображалась сумма выручки за все дни работы указанного магазина. Для этого мы и будем использовать комбинацию операторов СУММ и ВЫБОР.

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

    Активируется окошко Мастера функций. На этот раз перемещаемся в категорию «Математические». Находим и выделяем наименование «СУММ». После этого щелкаем по кнопке «OK».

    Происходит запуск окошка аргументов функции СУММ. Данный оператор используется для подсчета суммы чисел в ячейках листа. Его синтаксис довольно прост и понятен:

    Читайте также:  Усеченный конус задачи с решениями

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

    Устанавливаем курсор в поле «Число1». Затем кликаем по пиктограмме, которая изображена в виде перевернутого треугольника. Этот значок находится в том же горизонтальном ряду, где расположены кнопка «Вставить функцию» и строка формул, но слева от них. Открывается список недавно используемых функций. Так как формула ВЫБОР недавно применялась нами в предыдущем способе, то она имеется в этом списке. Поэтому достаточно кликнуть по данному наименованию, чтобы перейти в окно аргументов. Но более вероятно, что у вас в списке этого наименования не окажется. В этом случае нужно кликнуть по позиции «Другие функции…».

    Производится запуск Мастера функций, в котором в разделе «Ссылки и массивы» мы должны отыскать наименование «ВЫБОР» и выделить его. Щелкаем по кнопке «OK».

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

    В поле «Значение1» нужно вписать координаты столбца «1 торговая точка». Сделать это довольно просто. Устанавливаем курсор в указанное поле. Затем, зажав левую кнопку мыши, выделяем весь диапазон ячеек столбца «1 торговая точка». Адрес тут же отобразится в окне аргументов.

    Аналогичным образом в поле «Значение2» добавляем координаты столбца «2 торговая точка», в поле «Значение3»«3 торговая точка», а в поле «Значение4»«4 торговая точка».

    После выполнения данных действий жмем на кнопку «OK».

    Но, как видим, формула выводит ошибочное значение. Это связано с тем, что мы ещё не ввели номер торговой точки в соответствующую ячейку.

  • Вводим номер торговой точки в предназначенной для этих целей ячейке. Сумма выручки по соответствующей колонке тут же отобразится в элементе листа, в котором установлена формула.
  • Важно учесть, что вводить можно только числа от 1 до 4, которые будут соответствовать номеру торговой точки. Если вы введете любое другое число, то формула опять выдаст ошибку.

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

    Читайте также:  Программирование ардуино на андроиде

    Отблагодарите автора, поделитесь статьей в социальных сетях.

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

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

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

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

    Первый аргумент функции ВЫБОР называется «Номер индекса» и определяет, который из очередных аргументов должен быть возвращен через функцию. Данный аргумент может принимать числовые значения от 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