Ніщо так не псує робочий процес, як вставлення нових даних і усвідомлення, що таблиця в Excel виявилася замалою. Раніше доводилося постійно розтягувати її межі — доки не було виявлено простий прийом, завдяки якому таблиці розширюються автоматично.
Динамічні масиви Excel — правильний спосіб створення таблиць, що самі розширюються
На відміну від формул, які повертають одне значення, динамічні масиви розподіляють результати на кілька клітинок без необхідності заздалегідь задавати точний діапазон. Така поведінка робить їх придатними для створення таблиць, що автоматично змінюють розмір.
Прикладами таких функцій є UNIQUE, SORT і FILTER. Вони повертають динамічні масиви, які збільшуються або зменшуються залежно від джерела даних. Коли додаються нові записи, результати оновлюються миттєво, без будь-яких ручних дій. Якщо формулу динамічного масиву введено в одну клітинку, Excel автоматично заповнює суміжні клітинки, необхідні для відображення всіх результатів. Про це свідчить синя рамка навколо діапазону. Спроба ввести щось у цю «область розливу» призведе до помилки #SPILL!, що є корисним обмеженням.
Використання динамічних масивів не лише зручне, а й надійне, оскільки ручне керування таблицями призводить до помилок, пропусків даних і зайвих труднощів.
Приклад створення списку унікальних елементів, що сам розширюється
Функція UNIQUE здатна зекономити багато часу. Замість ручного перегляду списків вона автоматично виділяє унікальні значення з даних.
Базовий синтаксис такий:
Аргумент array містить вихідні дані, наприклад стовпець із відділами співробітників або іменами клієнтів. Аргумент by_col (TRUE або FALSE) визначає, чи порівнювати за стовпцями чи за рядками, а exactly_once відбирає значення, що з’являються лише один раз.
Наприклад, для створення списку відділів у таблиці з даними співробітників можна ввести:
Тут стовпець R містить назви відділів з рядків 2 по 3004. Excel миттєво створює динамічний список унікальних відділів, що оновлюється автоматично при додаванні нових співробітників.
Цей метод перевершує традиційний спосіб видалення дублікатів, адже динамічні масиви залишаються пов’язаними з вихідними даними, тоді як ручне очищення створює статичні списки, які одразу застарівають після додавання нових записів.
У сценаріях, де потрібно знайти значення, що зустрічаються лише один раз, можна застосувати:
Ця формула допомагає виявити відділи з одним працівником або унікальні ролі в організації.
Автоматичне сортування динамічних списків
Функція SORT розширює можливості динамічних масивів. Наприклад, у даних про співробітників вона дозволяє автоматично впорядковувати імена чи зарплати при кожній зміні даних. Синтаксис виглядає так:
Аргумент array містить діапазон даних, sort_index задає стовпець для сортування, sort_order визначає порядок (1 — зростання, -1 — спадання), а by_col вказує, чи сортувати за стовпцями чи рядками.
Функції SORT та UNIQUE можна поєднати. Наприклад, формула:
повертає алфавітно впорядкований список унікальних відділів. Якщо HR додає нові відділи, вони автоматично з’являються в правильному алфавітному порядку.
Для аналізу зарплат можна використати:
Ця формула впорядковує дані співробітників за зарплатою у спадному порядку. Восьмий стовпець містить зарплати, а -1 визначає сортування від більшої до меншої.
Функція SORT чутлива до регістру та відрізняє числа, збережені як текст, від справжніх чисел. Для точного результату слід забезпечити узгодженість типів даних.
Функція FILTER для динамічних звітів
Функція FILTER створює один із найпотужніших динамічних звітів у Excel. Вона дозволяє автоматично відображати лише ті дані, які відповідають заданим умовам, без створення статичних копій, що швидко застарівають.
Синтаксис:
Тут array містить увесь набір даних, include задає критерії відбору, а if_empty відображає повідомлення, якщо немає результатів.
Наприклад, щоб показати всіх членів відділу продажів у базі співробітників, можна використати:
Якщо хтось переходить у відділ продажів, він автоматично з’являється у вибірці.
Для аналізу зарплат вище 50 000 доларів застосовується формула:
Коли працівник отримує підвищення, він одразу потрапляє до цього списку.
Можливе й комбінування умов:
У цьому випадку відображаються працівники відділу продажів із зарплатою понад 50 000 доларів. Символ * виступає оператором AND, вимагаючи виконання обох умов одночасно.
