В программе эксель выбор функции из списка выполняется с помощью

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

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

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

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

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

Примеры

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

Как в Excel выбрать элементы из списка с помощью функции ВЫБОР

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

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

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

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

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

Динамические выпадающие списки, ДВССЫЛ и Умные Таблицы — Функции Excel (7)

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

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

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

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

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

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

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

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

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

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

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

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

Ссылки по теме

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

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

Выпадающий список в Excel с помощью инструментов или макросов

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

Создание раскрывающегося списка

Путь: меню «Данные» — инструмент «Проверка данных» — вкладка «Параметры». Тип данных – «Список».

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

1. Вручную через «точку-с-запятой» в поле «Источник».
2. Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.
3. Назначить имя для диапазона значений и в поле источник вписать это имя.

Любой из вариантов даст такой результат.

Выпадающий список в Excel с подстановкой данных

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

1. Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».
2. Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона:
3. Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

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

1. Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.
2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).

Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Target.Address = «$C$2» Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range(«Деревья»), Target) = 0 Then lReply = MsgBox(«Добавить введенное имя » » в выпадающий список?», vbYesNo + vbQuestion) If lReply = vbYes Then Range(«Деревья»).Cells(Range(«Деревья»).Rows.Count + 1, 1) = Target End If End If End If End Sub

Сохраняем, установив тип файла «с поддержкой макросов».

Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Выпадающий список в Excel с данными с другого листа/файла

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

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

Как сделать зависимые выпадающие списки

Возьмем три именованных диапазона:

Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.

1. Создадим первый выпадающий список, куда войдут названия диапазонов.
2. Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
3. Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.

Выбор нескольких значений из выпадающего списка Excel

Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.

Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range(«Е2:Е9»)) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(0, 1)) = 0 Then Target.Offset(0, 1) = Target Else Target.End(xlToRight).Offset(0, 1) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range(«Н2:К2»)) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub

Private Sub Worksheet_Change( ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range( «C2:C5» )) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
newVal = Target
Application.Undo
oldval = Target
If Len(oldval) <> 0 And oldval <> newVal Then
Target = Target ,» https://exceltable.com/vba-macros/vypadayushchiy-spisok-v-excel» target=»_blank»]exceltable.com[/mask_link]