зміст
Вчора на марафоні 30 функцій Excel за 30 днів ми з'ясували деталі нашого операційного середовища за допомогою функції INFO (ІНФОРМ) і виявила, що вона більше не може допомогти нам із проблемами пам’яті. Ні нашої, ні ексельської пам'яті!
На п’ятий день марафону ми вивчатимемо функцію ВИБРАТИ (ВИБІР). Ця функція відноситься до категорії Посилання та масиви, він повертає значення зі списку можливих варіантів відповідно до числового індексу. Варто відзначити, що в більшості випадків краще вибрати іншу функцію, наприклад, ІНДЕКС (ІНДЕКС) і MATCH (БІЛЬШ ВІДКРИТИЙ) або ВЛООКУП (ВПР). Ми розглянемо ці функції пізніше в цьому марафоні.
Отже, звернемося до наявної інформації та прикладів по функції ВИБРАТИ (ВИБІР), давайте подивимося в дії, а також відзначимо слабкі сторони. Якщо у вас є інші поради та приклади щодо цієї функції, поділіться ними в коментарях.
Функція 05: ВИБРАТИ
функція ВИБРАТИ (SELECT) повертає значення зі списку, вибираючи його відповідно до числового індексу.
Як можна використовувати функцію ВИБРАТИ?
функція ВИБРАТИ (SELECT) може повернути елемент у списку під певним номером, наприклад:
- За номером місяця поверніть номер фінансового кварталу.
- За датою початку обчисліть дату наступного понеділка.
- За номером магазину покажіть суму продажів.
Синтаксис ВИБРАТИ
функція ВИБРАТИ (SELECT) має такий синтаксис:
CHOOSE(index_num,value1,value2,…)
ВЫБОР(номер_индекса;значение1;значение2;…)
- номер_індексу (index_number) має бути від 1 до 254 (або від 1 до 29 у Excel 2003 і попередніх версіях).
- номер_індексу (номер_індексу) можна ввести у функцію як число, формулу або посилання на іншу клітинку.
- номер_індексу (номер_індексу) буде округлено до найближчого цілого числа.
- аргументація значення (значення) можуть бути числами, посиланнями на клітинки, іменованими діапазонами, функціями або текстом.
Пастки ВИБИРАТИ (CHOICE)
В Excel 2003 і раніших версіях функція ВИБРАТИ (SELECT) підтримував лише 29 аргументів значення (значення).
Набагато зручніше шукати список на аркуші, ніж вводити всі елементи у формулу. З функціями ВЛООКУП (VLOOKUP) або MATCH (МАТЧ) Ви можете звернутися до списків значень, розташованих на робочих аркушах Excel.
Приклад 1: Фінансовий квартал за номером місяця
функція ВИБРАТИ (SELECT) добре працює з простими списками чисел як значень. Наприклад, якщо клітинка B2 містить номер місяця, функція ВИБРАТИ (SELECT) можна обчислити, до якого фінансового кварталу він належить. У наступному прикладі фінансовий рік починається в липні.
Формула містить 12 значень, що відповідають місяцям з 1 по 12. Фінансовий рік починається в липні, тому місяці 7, 8 і 9 припадають на перший квартал. У таблиці нижче ви можете побачити номер фінансового кварталу під кожним номером місяця.
У функції ВИБРАТИ (ВИБРАТИ) Номери кварталів необхідно вводити в тому порядку, в якому вони вказані в таблиці. Наприклад, у списку значень функції ВИБРАТИ (ВИБІР) у положеннях 7, 8 і 9 (липень, серпень і вересень) має бути під номером 1.
=CHOOSE(C2,3,3,3,4,4,4,1,1,1,2,2,2)
=ВЫБОР(C2;2;3;3;3;4;4;4;1;1;1;2;2;2)
Введіть номер місяця в клітинку C2 і функцію ВИБРАТИ (SELECT) обчислить номер фінансового кварталу в клітинці C3.
Приклад 2: обчисліть дату наступного понеділка
функція ВИБРАТИ (SELECT) може працювати в поєднанні з функцією ВИХОДНИЙ (DAYWEEK), щоб обчислити майбутні дати. Наприклад, якщо ви є членом клубу, який збирається кожного понеділка ввечері, то, знаючи сьогоднішню дату, ви можете розрахувати дату наступного понеділка.
На малюнку нижче показано порядкові номери кожного дня тижня. Стовпець H для кожного дня тижня містить кількість днів, які потрібно додати до поточної дати, щоб отримати наступний понеділок. Наприклад, до неділі потрібно додати лише один день. А якщо сьогодні понеділок, то до наступного понеділка ще сім днів.
Якщо поточна дата знаходиться в клітинці C2, то формула в клітинці C3 використовує функції ВИХОДНИЙ (ДЕНЬ) і ВИБРАТИ (ВИБРАТИ), щоб обчислити дату наступного понеділка.
=C2+CHOOSE(WEEKDAY(C2),1,7,6,5,4,3,2)
=C2+ВЫБОР(ДЕНЬНЕД(C2);1;7;6;5;4;3;2)
Приклад 3: Показати обсяг продажів для вибраного магазину
Ви можете скористатися функцією ВИБРАТИ (ВИБІР) у поєднанні з іншими функціями, наприклад SUM (СУМА). У цьому прикладі ми отримаємо загальні продажі для певного магазину, вказавши його номер у функції ВИБРАТИ (SELECT) як аргумент, а також список діапазонів даних для кожного магазину для обчислення підсумків.
У нашому прикладі номер магазину (101, 102 або 103) вводиться в клітинку C2. Щоб отримати значення індексу, наприклад 1, 2 або 3 замість 101, 102 або 103, скористайтеся формулою: =C2-100.
Дані про продажі для кожного магазину наведено в окремому стовпці, як показано нижче.
Всередині функції SUM Функція (SUM) буде виконана першою ВИБРАТИ (SELECT), який поверне бажаний діапазон підсумовування, що відповідає вибраному магазину.
=SUM(CHOOSE(C2-100,C7:C9,D7:D9,E7:E9))
=СУММ(ВЫБОР(C2-100;C7:C9;D7:D9;E7:E9))
Це приклад ситуації, коли набагато ефективніше використовувати інші функції, наприклад ІНДЕКС (ІНДЕКС) і MATCH (ПОШУК). Пізніше під час нашого марафону ми побачимо, як вони працюють.