Завантажте зразок робочої книги
У цьому посібнику ви знайдете Вступ до формул динамічного масиву в Excel та Google Таблицях.
Вступ
У вересні 2022 року Microsoft представила в Excel формули динамічного масиву. Їх мета - полегшити написання складних формул і з меншою ймовірністю помилки.
Формули динамічного масиву мають врешті -решт замінити формули масивів, тобто розширені формули, які вимагають використання Ctrl + Shift + Enter (CSE).
Ось коротке порівняння між формулою масиву та формулою динамічного масиву, що використовується для вилучення списку унікальних відділів із нашого списку в діапазоні А2: А7.
Формула застарілого масиву (CSE):
У комірку вводиться наступна формула D2 і вводиться, натиснувши Ctrl + Shift + Enter і копіюючи його з D2 до D5.
| 1 | {= IFERROR (ІНДЕКС ($ A $ 2: $ A $ 7, MATCH (0, COUNTIF ($ D $ 1: D1, $ A $ 2: $ A $ 7), 0)), "")}} |
Формула динамічного масиву:
Наступна формула вводиться лише в клітинку D2 і введіть, натиснувши Enter. З першого погляду можна зрозуміти, наскільки легко і просто написати формулу динамічного масиву.
| 1 | = Унікальний (A2: A7) |
Наявність
Станом на серпень 2022 року формули динамічного масиву доступні лише для користувачів Office 365.
Діапазон розливів і розливів
Формули динамічного масиву працюють, повертаючи кілька результатів до діапазону клітинок на основі однієї формули, введеної в одну клітинку.
Така поведінка називається «Проливання» а діапазон клітинок, де розміщуються результати, називається "Діапазон розливів". Коли ви вибираєте будь -яку клітинку в межах витоку, Excel виділяє її тоненькою синьою облямівкою.
У наведеному нижче прикладі формула динамічного масиву СОРТУВАТИ знаходиться в камері D2 і результати були розкинуті в діапазоні D2: D7
| 1 | = СОРТУВАННЯ (A2: A7) |
Результати формули є динамічними, тобто, якщо відбувається зміна в діапазоні джерел, результати також змінюються, а діапазон розливу змінюється.
#ПОРОШКА!
Слід звернути увагу, що якщо діапазон витоку не повністю порожній, повертається помилка #SPILL.
Коли ви вибираєте помилку #SPILL, бажаний діапазон розливу формули підсвічується пунктирною синьою облямівкою. Переміщення або видалення даних у непустій клітинці усуває цю помилку, дозволяючи формулі розливатися.
Посилання на розлив
Для посилання на діапазон витоку формули ми розміщуємо # символ після посилання на клітинку першої комірки в розливі.
Ви також можете посилатися на розлив, вибравши всі осередки в діапазоні розливу, і посилання на розлив буде створено автоматично.
У наведеному нижче прикладі ми хотіли б порахувати кількість працівників нашої фірми за формулою КОНТА після їх впорядкування в алфавітному порядку за формулою динамічного масиву СОРТУВАТИ.
Ми входимо в СОРТУВАТИ формула в D2, щоб замовити працівників у нашому списку:
| 1 | = СОРТУВАННЯ (A2: A7) |
Потім ми входимо в КОНТА формула в G2 підрахувати кількість працівників:
| 1 | = COUNTA (D2#) |
Зверніть увагу на використання # у D2# для посилання на результати, розлиті SORT у діапазоні D2: D7.
Нові формули
Нижче наведено повний список нових формул динамічного масиву:
- Унікальний - Повертає список унікальних значень із діапазону
- СОРТУВАТИ - Сортує значення в діапазоні
- СОРТУВАТИ ЗА - Сортує значення на основі відповідного діапазону
- ФІЛЬТР - Фільтрує діапазон на основі наданих критеріїв
- RANDARRAY - Повертає масив випадкових чисел від 0 до 1
- ПОСЛІДОВНІСТЬ - Створює список послідовних чисел, таких як 1, 2, 3, 4, 5
Формула динамічного масивуу Таблицях Google
Усі наведені вище приклади працюють точно так само в Google Таблицях, як і в Excel.
