No Image

Эксель выбор значения из диапазона

СОДЕРЖАНИЕ
0 просмотров
22 января 2020

В этой статье описаны синтаксис формулы и использование функции ВЫБОР в Microsoft Excel.

Описание

Использует номер_индекса, чтобы выбрать и вернуть значение из списка аргументов-значений. Функция ВЫБОР позволяет выбрать одно значение из списка, в котором может быть до 254 значений. Например, если первые семь значений — это дни недели, то функция ВЫБОР возвращает один из дней при использовании числа от 1 до 7 в качестве аргумента "номер_индекса".

Синтаксис

Аргументы функции ВЫБОР описаны ниже.

Номер_индекса — обязательный аргумент. Номер выбираемого аргумента-значения. Номер индекса должен быть числом от 1 до 254, формулой или ссылкой на ячейку, содержащую число в диапазоне от 1 до 254.

Если номер_индекса равен 1, то функция ВЫБОР возвращает значение1; если он равен 2, возвращается значение2 и так далее.

Если номер_индекса меньше 1 или больше, чем номер последнего значения в списке, то функция ВЫБОР возвращает значение ошибки #ЗНАЧ!.

Если номер_индекса представляет собой дробь, то он усекается до меньшего целого.

Значение1; значение2; . — аргумент "значение1" является обязательным, следующие за ним — нет. От 1 до 254 аргументов-значений, из которых функция ВЫБОР, используя номер индекса, выбирает значение или выполняемое действие. Аргументы могут быть числами, ссылками на ячейки, определенными именами, формулами, функциями или текстом.

Замечания

Если номер_индекса является массив, то при выполнении функции ВЫБОР вычисляется каждое значение.

Аргументы-значения функции ВЫБОР могут быть как ссылками на интервал, так и отдельными значениями.

которая возвращает значение, вычисленное на основе значений в интервале ячеек B1:B10.

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

Примеры

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

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

Читайте также:  Соль в бетонный раствор

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

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

  • Ссылки;
  • Числа;
  • Текст;
  • Формулы;
  • Функции и т. д.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Читайте также:  Тип жк матрицы подробно tn film

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

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

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

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

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

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

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

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

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

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

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

    Комментировать
    0 просмотров
    Комментариев нет, будьте первым кто его оставит

    Это интересно
    No Image Компьютеры
    0 комментариев
    No Image Компьютеры
    0 комментариев
    No Image Компьютеры
    0 комментариев
    No Image Компьютеры
    0 комментариев
    Adblock detector