Функція Excel CHOOSE. Формули та приклади

Підручник пояснює синтаксис і основні способи використання Excel функція ВИБРАТИ і надає кілька нетривіальних прикладів, що показують, як використовувати формулу CHOOSE. Це одна з тих функцій Excel, яка сама по собі може здатися некорисною, але в поєднанні з іншими функціями вона надає низку неймовірних переваг. На найпростішому рівні функція CHOOSE використовується для отримання значення зі списку шляхом вказівки позиції цього значення. Далі в цьому підручнику ви знайдете кілька розширених застосувань, які, безперечно, варто вивчити.

Функція Excel CHOOSE: базовий синтаксис і використання

Функція CHOOSE в Excel призначена для повернення значення зі списку на основі певної позиції. Функція доступна в Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 і Excel 2007.

Синтаксис функції CHOOSE такий:

ВИБРАТИ (номер_індексу, значення1, [значення2],…)

Dónde:

Номер_індексу (обов'язково): позиція значення, яке буде повернуто. Це може бути будь-яке число від 1 до 254, посилання на клітинку або інша формула.

Значення1, значення2,…: список до 254 значень на вибір. Значення1 обов'язкове, інші значення необов'язкові. Це можуть бути числа, текстові значення, посилання на клітинки, формули або визначені імена. Ось приклад формули CHOOSE у найпростішій формі:

=ВИБРАТИ(3, «Майк», «Саллі», «Емі», «Ніл»)

Формула повертає «Емі" тому що Номер_індексу дорівнює 3, а «Емі» — третє значення в списку:

Можливо, вам може бути цікаво: Як використовувати функції ліворуч і праворуч у Excel

Excel ВИБЕРІТЬ функцію: 3 речі, які слід пам’ятати!

ВИБРАТИ — це дуже проста функція, і ви навряд чи знайдете труднощі з її впровадженням у свої робочі аркуші. Якщо результат, отриманий вашою формулою CHOSEN, є неочікуваним або не таким, як ви шукали, це може бути спричинено такими причинами:

1. Кількість значень на вибір обмежена 254.
2. Помилка повертається.
3. Якщо аргумент Номер_індексу є дробом, його скорочують до найменшого цілого числа.

Як використовувати функцію ВИБРАТИ в Excel – приклади формул

У наведених нижче прикладах показано, як CHOOSE може розширити можливості інших функцій Excel і надати альтернативні рішення для деяких типових завдань, навіть тих, які багато хто вважає нездійсненними.

ВИБРАТИ в Excel замість вкладеного IF

Одним із найпоширеніших завдань у Excel є повернення різних значень на основі певної умови. У більшості випадків це можна зробити за допомогою класичного вкладеного оператора IF. Але функція ВИБРАТИ Excel може бути швидкою та легкою для розуміння альтернативою.

Приклад 1: повертає різні значення залежно від умови

Припустімо, у вас є стовпець оцінок студентів і ви хочете позначити оцінки на основі таких умов: результат і бал.

• погано: 0 - 50
• Задовільно: 51 - 100
• Добре: 101 - 150
• Відмінно: більше 151

Один із способів зробити це — вкласти деякі формули IF одна в одну:

=IF(B2>=151, "Відмінно", IF(B2>=101, "Добре", IF(B2>=51, "Задовільно", "Погано"))

Інший спосіб - вибрати мітку, яка відповідає умові:

=ВИБРАТИ((B2>0) + (B2>=51) + (B2>=101) + (B2>=151), «Погано», «Задовільно», «Добре», «Відмінно»)

Як працює ця формула:

В аргументі Номер_індексу, оцінює кожну умову та повертає ПРАВДА Якщо умова виконується, ФЕЙК інакше. Наприклад, значення в клітинці B2 відповідає першим трьом умовам, тому ми отримуємо такий проміжний результат:

=ВИБРАТИ(ІСТИНА + ІСТИНА + ІСТИНА + ЛОЖЬ, «Погано», «Задовільно», «Добре», «Відмінно»)

Оскільки в більшості формули Excel, TRUE дорівнює 1, а FALSE дорівнює 0, наша формула зазнає такого перетворення:

=ВИБРАТИ(1 + 1 + 1 + 0; "Погано", "Задовільно", "Добре", "Відмінно")

Після виконання операції додавання ми маємо:

=CHOOSE(3; "Погано", "Задовільно", "Добре", "Відмінно")

У результаті повертається третє значення в списку, яке є "добре".

Поради:

• Щоб зробити формулу більш гнучкою, ви можете використовувати посилання на клітинки замість жорстко закодованих міток, наприклад:

=ВИБРАТИ((B2>0) + (B2>=51) + (B2>=101) + (B2>=151), $E$1, $E$2, $E$3, $E$4)

• Якщо жодна з умов не є TRUE, аргумент Номер_індексу буде встановлено на 0, змушуючи вашу формулу повертати #VALUE! помилка. Щоб уникнути цього, просто оберніть CHOOSE у функцію IFERROR таким чином:

=IFERROR(CHOOSE((B2>0) + (B2>=51) + (B2>=101) + (B2>=151), «Погано», «Задовільно», «Добре», «Відмінно»), « »)

Приклад 2: Виконайте різні обчислення залежно від умови

Ви можете використовувати функцію ВИБРАТИ Excel, щоб виконати обчислення на основі серії можливих обчислень/формул без вкладення кількох операторів IF один в одного. Для прикладу розрахуємо комісію кожного продавця на основі його продажів:

• 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)

 Формула Choose для створення випадкових даних

Як ви, мабуть, знаєте, Microsoft Excel має спеціальну функцію для генерування випадкових цілих чисел між нижнім і верхнім числами, які ви вказуєте: функція РАНДБЕЖ. Використовуйте аргумент index_num для CHOOSE, і ваша формула генеруватиме майже будь-які випадкові дані, які ви хочете. Наприклад, ця формула може створити список випадкових результатів сканування:

=ВИБРАТИ(RANDBETWEEN(1,4;XNUMX), «Погано», «Задовільно», «Добре», «Відмінно»)

Логіка формули очевидна: РАНДБЕЖ генерує випадкові числа від 1 до 4, а CHOOSE повертає відповідне значення зі попередньо визначеного списку з чотирьох значень.

Примітка: RANDBETWEEN - це a летюча функція і перераховується з кожною зміною, яку ви вносите в робочий аркуш. В результаті ваш список випадкових значень також зміниться. Щоб цього не сталося, ви можете замінити формули своїми значеннями за допомогою функції Спеціальний клей.

Можливо, ви хочете знати: Як зв’язати розкривний список у Excel

Формула Choose для створення лівого Vlookup

Якщо ви коли-небудь виконували вертикальний пошук у Excel, ви знаєте, що функція VLOOKUP може шукати лише в крайньому лівому стовпці. У ситуаціях, коли вам потрібно повернути значення ліворуч від стовпця підстановки, ви можете використовувати об’єднання ІНДЕКС / МАТЧ або обдурити VLOOKUP вкладення в нього функції CHOOSE. Ось як:

Припустімо, у вас є список балів у стовпці A, імена студентів у стовпці B, і ви хочете отримати оцінку певного студента. Оскільки стовпець повернення знаходиться ліворуч від стовпця пошуку, a Vlookup звичайна повертає помилку #N/A:

Щоб виправити це, запустіть функцію CHOOSE, щоб поміняти місцями стовпці, повідомляючи Excel, що стовпець 1 — це B, а стовпець 2 — A:

=CHOOSE({1,2}, B2:B5, A2:A5)

Тому що ми надаємо масив {1,2} в аргументі index_num, функція CHOOSE приймає діапазони в аргументах значень (зазвичай не приймає). Тепер вбудуйте наведену вище формулу в аргумент table_array de VLOOKUP:

=VLOOKUP(E1,CHOOSE({1,2}, B2:B5, A2:A5),2,FALSE)

При цьому пошук ліворуч виконується без проблем!

Формула Виберіть для повернення наступного робочого дня

Якщо ви не впевнені, чи варто вам йти завтра на роботу, чи можете залишитися вдома та насолоджуватися заслуженими вихідними, функція ВИБРАТИ Excel може дізнатися, коли настане наступний робочий день. Якщо у вас робочі дні з понеділка по п’ятницю, то формула така:

=СЬОГОДНІ()+ВИБРАТИ(ДЕНЬ ТИЖНЯ(СЬОГОДНІ()),1,1,1,1,1,3,2)

На перший погляд складно, але при уважному розгляді логіку формули легко зрозуміти:

WEEKDAY(TODAY()) повертає порядковий номер, що відповідає сьогоднішній даті в діапазоні від 1 (неділя) до 7 (субота). Це число йде в аргумент Номер_індексу нашої формули ВИБРАТИ.

Значення1 – значення7 (1,1,1,1,1,3,2) визначає, скільки днів додати до поточної дати. Якщо сьогодні неділя – четвер (index_num 1 – 5), додайте 1, щоб повернутися наступного дня. Якщо сьогодні п’ятниця (index_num 6), додайте 3, щоб повернути наступний понеділок. Якщо сьогодні субота (index_num 7), додайте 2, щоб знову повернутися в наступний понеділок. Так, це так просто.

Виберіть формулу, щоб повернути настроювану назву дня/місяця від дати

У ситуаціях, коли потрібно отримати назву дня в стандартному форматі, наприклад, повну назву (понеділок, вівторок тощо) або коротку назву, можна скористатися функцією TEXT, як описано в цьому прикладі: Отримайте день тижня з дати в Excel.

Якщо ви хочете повернути назву дня тижня або місяця в спеціальному форматі, скористайтеся функцією ВИБРАТИ Excel, як описано нижче. Щоб отримати день тижня:

=ВИБРАТИ(ДЕНЬ ТИЖНЯ(A2),»Нд»,»Пн»,»Вт»,»Ср»,»Чт»,»Пт»,»Сб»)

Щоб отримати місяць:

=ВИБРАТИ(МІСЯЦЬ(A2), «Січень»,»Лютий»,»Бер»,»Квіт»,»Травень»,»Червень»,»Лип»,»Серп»,»Вер»,»Жовтень»,»Листопад »,»Гру»)

Де A2 — клітинка, що містить початкову дату.

Подивіться на: Панелі даних в Excel. Що це таке і як їх додати

Pensamientos finales

Сподіваємося, цей підручник дав вам ідеї щодо того, як можна використовувати функцію ВИБРАТИ Excel для вдосконалення моделей даних. Дякуємо, що прочитали, і сподіваємося побачити вас наступного тижня! Якщо вам сподобався підручник, ви можете повідомити нам свою думку в розділі коментарів. Пам’ятайте, що цю функцію можна використовувати для багатьох речей, вам просто потрібно адаптувати дані формули.