Ця інструкція допоможе швидко отримувати списки унікальних значень, виконувати масові обчислення без протягування формул і керувати результатами, що «проливаються» (spill) у діапазон клітинок. Корисно для аналізу даних у Microsoft 365, Excel 2024, Excel 2021 та Excel для вебу (Excel for the web).
Отримати унікальні значення одним кроком (UNIQUE)
- Виділіть клітинку, де має з’явитися список унікальних значень.
- Введіть формулу: =UNIQUE(B2:B11) і натисніть Enter.
Excel поверне всі унікальні міста одним «проливом» вниз з клітинки з формулою. Редагувати потрібно тільки верхню клітинку; решта — це значення проливу. Коли вихідні дані зміняться, результат оновиться автоматично.
Масові обчислення без протягування формули
- Виділіть клітинку для результатів поруч зі стовпцем із кількістю.
- Введіть формулу: =D2:D11*25 і натисніть Enter.
Одна формула одразу множить кожне значення в D2:D11 на 25 і проливає всі результати вниз. Пролив підтримують не лише «нові» функції — так працюють і звичайні посилання та арифметика.
Керування всім проливом через «хеш» (#)
Увесь блок результатів називається діапазоном проливу. Його зручно використовувати в інших формулах: додайте # до адреси верхньої лівої (якірної) клітинки проливу.
- У клітинці A2 введіть формулу: =SEQUENCE(5) і натисніть Enter.
- Щоб підсумувати весь пролив, введіть у будь-якій клітинці: =SUM(A2#) і натисніть Enter.
Посилання A2# завжди охоплює весь поточний пролив — якщо значень стане 8 замість 5, формула автоматично врахує всі 8.
Як порахувати рядки з багатостовпчикового проливу
Посилання з # повертає увесь прямокутник проливу — усі рядки і стовпці. Через це лічильники на кшталт COUNTA можуть порахувати забагато клітинок. Використовуйте INDEX, щоб узяти лише один стовпець.
- Отримайте відбір рядків за містом (наприклад, Austin): введіть =FILTER(A2:D11,B2:B11=»Austin») і натисніть Enter.
- Щоб порахувати записи в проливі з клітинки F2, введіть: =COUNTA(INDEX(F2#,,1)) і натисніть Enter.
У кроці 2 INDEX(F2#,,1) повертає лише перший стовпець із усього проливу, а COUNTA рахує правильну кількість рядків.
Що таке помилка #SPILL! і як її усунути
- Якщо бачите #SPILL!, перевірте, чи всі клітинки, куди має пролитися формула, порожні.
- Очистіть або звільніть перекриті клітинки, або перемістіть формулу в інше місце.
Динамічній формулі потрібен вільний простір для проливу, інакше Excel повертає #SPILL!.
Поради та типові помилки
- Підтримка версій. Динамічні масиви працюють у Microsoft 365, Excel 2024, Excel 2021 і Excel для вебу (Excel for the web). У Excel 2019, Excel 2016 і старіших вони не працюють.
- Фіксований діапазон не «росте». Посилання на B2:B11 не підхопить нові рядки нижче. Щоб результати автоматично оновлювалися з новими даними, перетворіть джерело на таблицю Excel і використовуйте стовпець таблиці у формулах.
- Редагування лише в якірній клітинці. Значення в клітинках проливу не редагуються — змінюйте формулу у верхній клітинці.
- Посилання з # — на весь блок. Воно повертає всі рядки і стовпці проливу. Щоб звернутися до окремого стовпця, використовуйте INDEX(якір#,,номер_стовпця).
Фото: Trump Excel
