Загрузите образец книги
В этом руководстве вы познакомитесь с формулами динамических массивов в Excel и Google Таблицах.
Вступление
В сентябре 2022 года Microsoft представила в Excel формулы динамических массивов. Их цель - упростить написание сложных формул и снизить вероятность ошибки.
Формулы динамического массива предназначены для того, чтобы в конечном итоге заменить формулы массива, то есть расширенные формулы, требующие использования Ctrl + Shift + Enter (CSE).
Вот быстрое сравнение между формулой массива и формулой динамического массива, используемой для извлечения списка уникальных отделов из нашего списка в диапазоне. A2: A7.
Устаревшая формула массива (CSE):
В ячейку вводится следующая формула D2 и вводится нажатием Ctrl + Shift + Enter и копированием вниз из D2 к D5.
| 1 | {= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 7, ПОИСКПОЗ (0, СЧЁТЕСЛИ ($ 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 требуемый диапазон разлива формулы выделяется синей пунктирной рамкой. Перемещение или удаление данных в непустой ячейке устраняет эту ошибку, позволяя формуле разлиться.
Обозначение разлива
Чтобы сослаться на диапазон разлива формулы, мы помещаем # символ после ссылки на первую ячейку в разливе.
Вы также можете указать разлив, выбрав все ячейки в диапазоне разлива, и ссылка на разлив будет автоматически создана.
В приведенном ниже примере мы хотим подсчитать количество сотрудников в нашей фирме по формуле COUNTA после того, как они были упорядочены в алфавитном порядке с использованием формулы динамического массива СОРТИРОВАТЬ.
Мы входим в СОРТИРОВАТЬ формула в D2 для упорядочивания сотрудников в нашем списке:
| 1 | = СОРТИРОВАТЬ (A2: A7) |
Затем мы входим в COUNTA формула в G2 для подсчета количества сотрудников:
| 1 | = СЧЁТ (D2 #) |
Обратите внимание на использование # в D2 # для ссылки на результаты, выведенные функцией SORT в диапазоне D2: D7.
Новые формулы
Ниже приведен полный список новых формул динамического массива:
- УНИКАЛЬНЫЙ - Возвращает список уникальных значений из диапазона
- СОРТИРОВАТЬ - Сортировка значений в диапазоне
- СОРТИРОВАТЬ ПО - Сортировка значений по соответствующему диапазону
- ФИЛЬТР - Фильтрует диапазон на основе предоставленных критериев
- СЛУЧАЙНЫЙ - Возвращает массив случайных чисел от 0 до 1
- ПОСЛЕДОВАТЕЛЬНОСТЬ - Создает список последовательных номеров, таких как 1, 2, 3, 4, 5
Формула динамического массиваs в Google Таблицах
Все приведенные выше примеры работают в Google Таблицах точно так же, как и в Excel.
