ВЫБЕРИТЕ функцию в Excel с примерами формул
В учебнике объясняется синтаксис и основные способы использования функции ВЫБОР, а также приводится несколько нетривиальных примеров, показывающих, как использовать формулу ВЫБОР в Excel.
ВЫБОР — одна из тех функций Excel, которые сами по себе могут показаться бесполезными, но в сочетании с другими функциями дают ряд замечательных преимуществ. На самом базовом уровне вы используете функцию ВЫБОР, чтобы получить значение из списка, указав положение этого значения. Далее в этом руководстве вы найдете несколько расширенных вариантов использования, которые, безусловно, стоит изучить.
Функция ВЫБОР в Excel — синтаксис и основные способы использования
Функция ВЫБОР в Excel предназначена для возврата значения из списка на основе указанной позиции.
Функция доступна в Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 и Excel 2007.
Синтаксис функции ВЫБОР следующий:
ВЫБЕРИТЕ(номер_индекса, значение1, [value2]…)
Где:
Index_num (обязательно) — позиция возвращаемого значения. Это может быть любое число от 1 до 254, ссылка на ячейку или другая формула.
Значение1, значение2, … — список до 254 значений, из которых можно выбирать. Требуется значение 1, другие значения необязательны. Это могут быть числа, текстовые значения, ссылки на ячейки, формулы или определенные имена.
Вот пример формулы ВЫБОР в самой простой форме:
=ВЫБОР(3, “Майк”, “Салли”, “Эми”, “Нил”)
Формула возвращает «Эми», потому что номер_индекса равно 3, а «Эми» — 3-е значение в списке:
Функция ВЫБОР в Excel — 3 вещи, о которых нужно помнить!
ВЫБОР — очень простая функция, и вы вряд ли столкнетесь с какими-либо трудностями при ее реализации на своих рабочих листах. Если результат, возвращаемый вашей формулой ВЫБОР, неожиданный или не тот результат, который вы искали, это может быть вызвано следующими причинами:
- Количество значений для выбора ограничено 254.
- Если номер_индекса меньше 1 или больше числа значений в списке, ошибка #VALUE! возвращается ошибка.
- Если номер_индекса аргумент представляет собой дробь, он усекается до наименьшего целого числа.
Как использовать функцию ВЫБОР в Excel – примеры формул
В следующих примерах показано, как ВЫБОР может расширить возможности других функций Excel и предоставить альтернативные решения для некоторых распространенных задач, даже для тех, которые многие считают невыполнимыми.
Excel ВЫБЕРИТЕ вместо вложенных ЕСЛИ
Одной из наиболее частых задач в Excel является возврат разных значений на основе заданного условия. В большинстве случаев это можно сделать с помощью классического вложенного оператора IF. Но функция ВЫБОР может быть быстрой и простой для понимания альтернативой.
Пример 1. Возврат разных значений в зависимости от условия
Предположим, у вас есть столбец с оценками учащихся, и вы хотите пометить оценки на основе следующих условий:
Результат Оценка Плохо 0–50 Удовлетворительно 51–100 Хорошо 101–150 Отлично свыше 151
Один из способов сделать это — вложить несколько формул ЕСЛИ друг в друга:
=ЕСЛИ(В2>=151, «Отлично», ЕСЛИ(В2>=101, «Хорошо», ЕСЛИ(В2>=51, «Удовлетворительно», «Плохо»)))
Другой способ — выбрать метку, соответствующую условию:
=ВЫБОР((B2>0) + (B2>=51) + (B2>=101) + (B2>=151), «Плохо», «Удовлетворительно», «Хорошо», «Отлично»)
Как работает эта формула:
в номер_индекса аргумент, вы оцениваете каждое условие и возвращаете TRUE, если условие выполнено, FALSE в противном случае. Например, значение в ячейке B2 соответствует первым трем условиям, поэтому мы получаем такой промежуточный результат:
=ВЫБОР(ИСТИНА + ИСТИНА + ИСТИНА + ЛОЖЬ, “Плохо”, “Удовлетворительно”, “Хорошо”, “Отлично”)
Учитывая, что в большинстве формул Excel ИСТИНА равняется 1, а ЛОЖЬ — 0, наша формула претерпевает следующее преобразование:
=ВЫБОР(1 + 1 + 1 + 0, “Плохо”, “Удовлетворительно”, “Хорошо”, “Отлично”)
После выполнения операции сложения имеем:
=ВЫБОР(3, “Плохо”, “Удовлетворительно”, “Хорошо”, “Отлично”)
В результате возвращается 3-е значение в списке, то есть «Хорошо».
Советы:
- Чтобы сделать формулу более гибкой, вы можете использовать ссылки на ячейки вместо жестко заданных меток, например:
=ВЫБОР((B2>0) + (B2>=51) + (B2>=101) + (B2>=151), $E$1, $E$2, $E$3, $E$4)
- Если ни одно из ваших условий не является ИСТИННЫМ, номер_индекса аргумент будет установлен на 0, заставляя вашу формулу возвращать #ЗНАЧ! ошибка. Чтобы избежать этого, просто оберните ВЫБОР в функцию ЕСЛИОШИБКА следующим образом:
=ЕСЛИОШИБКА(ВЫБЕРИТЕ((B2>0) + (B2>=51) + (B2>=101) + (B2>=151), “Плохо”, “Удовлетворительно”, “Хорошо”, “Отлично”), ” “)
Пример 2. Выполнение различных расчетов на основе условия
Аналогичным образом вы можете использовать функцию ВЫБОР Excel для выполнения одного вычисления в серии возможных вычислений/формул без вложения нескольких операторов ЕСЛИ друг в друга.
В качестве примера рассчитаем комиссию каждого продавца в зависимости от его продаж:
Комиссионные продажи 5% от $0 до $50 7% от $51 до $100 10% свыше $101
С суммой продаж в B2 формула принимает следующий вид:
=ВЫБОР((B2>0) + (B2>=51) + (B2>=101), B2*5%, B2*7%, B2*10%)
Вместо того, чтобы жестко задавать проценты в формуле, вы можете обратиться к соответствующей ячейке в своей справочной таблице, если таковая имеется. Только не забудьте исправить ссылки, используя знак $.
=ВЫБОР((B2>0) + (B2>=51) + (B2>=101), B2*$E$2, B2*$E$3, B2*$E$4)
Excel ВЫБЕРИТЕ формулу для генерации случайных данных
Как вы, наверное, знаете, в Microsoft Excel есть специальная функция для генерации случайных целых чисел между указанными вами нижними и верхними числами — функция СЛУЧМЕЖДУ. Вложите его в номер_индекса аргумент ВЫБОР, и ваша формула будет генерировать почти любые случайные данные, которые вы хотите.
Например, эта формула может вывести список случайных результатов экзамена:
=ВЫБОР(СЛУЧМЕЖДУ(1,4), “Плохо”, “Удовлетворительно”, “Хорошо”, “Отлично”)
Логика формулы очевидна: СЛУЧМЕЖДУ генерирует случайные числа от 1 до 4, а ВЫБОР возвращает соответствующее значение из предопределенного списка из четырех значений.
Примечание. СЛУЧМЕЖДУ — это изменчивая функция, которая пересчитывается при каждом изменении, которое вы вносите в рабочий лист. В результате ваш список случайных значений также изменится. Чтобы этого не произошло, вы можете заменить формулы их значениями с помощью Специальная вставка особенность.
ВЫБЕРИТЕ формулу, чтобы сделать левый Vlookup
Если вы когда-либо выполняли вертикальный поиск в Excel, вы знаете, что функция ВПР может выполнять поиск только в крайнем левом столбце. В ситуациях, когда вам нужно вернуть значение слева от столбца подстановки, вы можете либо использовать комбинацию ИНДЕКС/ПОИСКПОЗ, либо обмануть ВПР, вложив в нее функцию ВЫБОР. Вот как:
Предположим, у вас есть список оценок в столбце A, имена учащихся в столбце B, и вы хотите получить оценку конкретного ученика. Поскольку столбец возврата находится слева от столбца поиска, обычная формула ВПР возвращает ошибку #Н/Д:
Чтобы исправить это, заставьте функцию ВЫБОР поменять местами столбцы, сообщая Excel, что столбец 1 — это B, а столбец 2 — это A:
=ВЫБОР({1,2}, B2:B5, A2:A5)
Поскольку мы предоставляем массив {1,2} в номер_индекса аргумент, функция ВЫБОР принимает диапазоны в ценность аргументы (обычно это не так).
Теперь вставьте приведенную выше формулу в таблица_массив аргумент ВПР:
=ВПР(E1,ВЫБРАТЬ({1,2}, B2:B5, A2:A5),2,ЛОЖЬ)
И вуаля — поиск влево выполняется без сучка и задоринки!
ВЫБЕРИТЕ формулу для возврата на следующий рабочий день
Если вы не уверены, идти ли вам завтра на работу или можно остаться дома и насладиться заслуженными выходными, функция Excel ВЫБРАТЬ может узнать, когда следующий рабочий день.
Предполагая, что ваши рабочие дни с понедельника по пятницу, формула выглядит следующим образом:
=СЕГОДНЯ()+ВЫБРАТЬ(ДЕНЬНЕД(СЕГОДНЯ()),1,1,1,1,1,3,2)
Хитрая на первый взгляд, при ближайшем рассмотрении логика формулы проста:
WEEKDAY(TODAY()) возвращает порядковый номер, соответствующий сегодняшней дате, в диапазоне от 1 (воскресенье) до 7 (суббота). Этот номер идет на номер_индекса аргумент нашей формулы ВЫБОР.
Значение1 – значение7 (1,1,1,1,1,3,2) определяют, сколько дней добавить к текущей дате. Если сегодня воскресенье – четверг (номер_индекса от 1 до 5), вы добавляете 1, чтобы вернуться на следующий день. Если сегодня пятница (index_num 6), вы добавляете 3, чтобы вернуться в следующий понедельник. Если сегодня суббота (index_num 7), вы добавляете 2, чтобы снова вернуться в следующий понедельник. Ага, это так просто 🙂
ВЫБЕРИТЕ формулу, чтобы вернуть произвольное название дня/месяца из даты
В ситуациях, когда вы хотите получить название дня в стандартном формате, например полное название (понедельник, вторник и т. д.) или сокращенное название (понедельник, вторник и т. д.), вы можете использовать функцию ТЕКСТ, как описано в этом примере: Получить день недели по дате в Excel.
Если вы хотите вернуть день недели или название месяца в пользовательском формате, используйте функцию ВЫБОР следующим образом.
Чтобы получить день недели:
=ВЫБЕРИТЕ(ДЕНЬНЕД(A2),”Вс”,”Пн”,”Вт”,”Ср”,”Чт”,”Пт”,”Сб”)
Чтобы получить месяц:
=ВЫБЕРИТЕ(МЕСЯЦ(A2), “Янв”, “Фев”, “Мар”, “Апр”, “Май”, “Июнь”, “Июль”, “Август”, “Сент”, “Окт”, “Ноябрь” “, “декабрь”)
Где A2 — ячейка, содержащая исходную дату.
Я надеюсь, что это руководство дало вам некоторые идеи о том, как вы можете использовать функцию ВЫБОР в Excel для улучшения ваших моделей данных. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Скачать рабочую тетрадь
Примеры функции ВЫБЕРИТЕ в Excel
