Массивы excel: что такое, формула, как сделать / Skillbox Media

Содержание

Программы и макросы Excel в категории Массивы

Универсальные функции VBA для обработки одномерных и двумерных массивов, сравнения массивов и поиска в них

Быстрый поиск в двумерном массиве
В данной статье показаны 2 способа быстрого поиска значений в двумерных массивах. Поскольку искомое значение может встретиться в нескольких строках обрабатываемого двумерного массива, оба способа получают на выходе отфильтрованный двумерный массив. Способы формирования отфильтрованных массивов — разные: первый способ использует функцию ArrAutofilterEx второй способ — функцию …
Сортировка двумерного массива на VB (VBA)
Сортировка двумерного массива по нулевому столбцу Public Function CoolSort(SourceArr As Variant) As Variant ‘ сортировка двумерного массива по нулевому столбцу Dim Check As Boolean, iCount As Integer, jCount As Integer, nCount As Integer ReDim tmpArr(UBound(SourceArr, 2)) As Variant Do Until Check Check = True For iCount = LBound(SourceArr, 1) To UBound(SourceArr,.
..
Сбор данных из множества текстовых файлов с разделителями
К примеру, есть у вас несколько десятков (или сотен) текстовых файлов с подобным содержимым: (количество файлов, и количество строк данных в каждом файле не ограничено) 1c04;1J0-698-151-G;1 комплект тормозных накладок;1J0698151G;1J0698151G;5;1 1c04;1H0698151A;Тормозные колодки;1H0698151A;1H0698151A;1;1 1c04;1K0-698-151-B;Тормозные колодки;1K0698151B;1K0698151B;2;1 А надо из всего этого…
Загрузка файла CSV на лист Excel
Надстройка предназначена для облегчения импорта данных в Excel из текстовых файлов с разделителями (например, из CSV) Пока во вложении — обычный файл Excel с нужными макросами, надстройку выложу позже В надстройке применена функция получения ссылки на заданную пользователем ячейку. Основой для надстройки послужила функция загрузки CSV файла в двумерный массив Использовать функции…
Надстройка для математического анализа в Excel
Надстройка с несколькими пользовательскими функциями вычислительной математики. Надстройка предназначена для тех пользователей, кто хочет: интерполировать табличные данные; находить корни уравнения; вычислять производные; численно интегрировать; аппроксимировать экспериментальные данные Внимание: 21 марта 2020 года выложена новая версия надстройки. Во вложении к статье — надстройка…
Чтение CSV файла в двумерный массив
Функция TextFile2Array предназначена для преобразования файла CSV в двумерный массив Очень часто при работе с текстовыми файлами (и, в частности, с файлами CSV) приходится их загружать на лист Excel, предварительно производя фильтрацию данных в этом файле. Чтобы упростить весь процесс — от выбора файла CSV в диалоговом окне, до разбиения загруженного из файла текста в двумерный массив, и была…
Преобразование массива в XML (экспорт таблицы в файл XML)
Функция Array2XML формирует из исходной таблицы объект типа DOMDocument, который можно выгрузить в XML-файл одной строкой кода (метод Save) Sub XMLExport() Dim Заголовок As Range, Данные As Range Set Заголовок = Range(«a1:f1») Set Данные = Range([A2], Range(«A» & Rows. Count).End(xlUp)).Resize(, Заголовок.Columns.Count) arrHeaders = Application….
Разбиение строк двумерного массива — по одной строке для каждого значения
Если у вас есть таблица Excel, в которой, в определённом столбце, через запятую перечислены значения (или диапазоны значений), а вы хотите получить аналогичную таблицу, но чтобы в каждой строке было только одно значение, — то вам на помощь придёт функция ExtendArray. (пример работы функции можно увидеть на прикреплённом изображении) В своей работе ExtendArray использует функцию…
Функция получения массива данных из видимых строк
Функция предназначена для получения двумерного массива данных из видимых строк листа Excel. Например, нас интересует массив, начиная со второй строки, шириной 5 столбцов. Если все строки на листе — видимые, то все делается просто, одной строкой кода: arr = Range(Range(«a2»), Range(«a» & Rows. Count).End(xlUp)).Resize(, 5).Value А если на листе включен…
Загрузка данных из закрытой книги Excel в двумерный массив
Sub ПримерИспользования() ‘ задаём полный путь к обрабатываемому файлу ПутьКФайлу$ = ThisWorkbook.Path & «\» & «Contract.XLS» Application.ScreenUpdating = False ‘ отключаем обновление экрана arr = LoadArrayFromWorkbook(ПутьКФайлу$, «a2», 30) ‘ загружаем данные ‘ выводим результаты в окно Immediate Debug.Print «…
Выборка случайных строк из двумерного массива
Функция RandomRowsFromArray предназначена для выборки из двумерного массива случайных строк. К примеру, исходный массив (таблица) имеет размер 1000*20 (1000 строк, и 20 столбцов) Нам требуется выбрать из этой таблицы, случайным образом, 50 строк (получив, таким образом, таблицу размерами 50*20) Кроме того, необходимо, чтобы при каждом новом запуске макроса, в выборку попадали новые строки. …
Разбиение двумерного массива на несколько массивов, группируя строки по заданному столбцу

Функция принимает в качестве параметра arr двумерный массив, и разбивает его на несколько массивов, группируя строки по значению столбца SplitColumn& Сколько есть уникальных значений в столбце SplitColumn&, удовлетворяющих маске Mask$, — столько двумерных массивов будет возвращено функцией в виде коллекции Например, если есть исходный массив размерами 100*5, в котором во втором столбце…
Обработка котировок (тиков) в формате CSV
Программа служит для преобразования тиковых значений котировок в свечи необходимого периода Исходный файл в формате .csv получается в результате выгрузки (экспорта) котировок с сайта www.finam.ru (Фьючерсы ФОРТС), и содержит большое количество строк (записей) — около 1 миллиона за 1 день. В исходном файле CSV имеется таблица со следующими столбцами: <TICKER> <PER> <DATE.

..
Расчёт доходности портфелей методом Монте-Карло
Программа предназначена для анализа ожидаемой доходности различных портфелей (рассматриваются всевозможные комбинации проектов) При помощи формул типа =СЛЧИС() на листах с характеристиками проектов генерируются различные комбинации исходных значений, на основании которых формулами рассчитывается доходность проекта. Методом Монте-Карло формируются массивы значений (по одному массиву для…
Формирование и рассылка случайного списка торговых точек
Программа предназначена для еженедельного формирования плана проверок торговых точек. Функции программы: выборка из базы данных заданного числа случайных торговых точек, в соответствии с настройками для каждой группы формирование таблицы (файл Excel) со списком выбранных объектов по шаблону рассылка созданного файла (в архиве ZIP) по заданному списку адресов электронной…
Сохранение двумерного массива в файл
Функция предназначена для сохранения двумерного массива в файл формата XLS Sub SaveArray(ByVal Arr, ByVal ColumnNames, ByVal DocName$) ‘ Получает двумерный массив Arr с данными, и массив заголовков столбцов ColumnNames. ‘ Создаёт новый файл в подпапке СФОРМИРОВАННЫЕ ДОКУМЕНТЫ с именем DocName$ On Error Resume Next ‘ создаём подпапку (там же, где текущий файл Excel)…

Выполнять несколько расчетов с помощью формул массива Excel

Формула массива Excel — это формула, которая выполняет вычисления для значений в одном или нескольких массивах, а не в одном значении данных. В программах для работы с электронными таблицами массив представляет собой диапазон или серию связанных значений данных , которые обычно находятся в соседних ячейках на рабочем листе.

Эти инструкции относятся к Excel 2019, 2016, 2013, 2010 и Excel для Office 365.

Что такое формулы массива?

Формулы массива похожи на обычные. Oни:

Начните со знака равенства ( = )
Используйте тот же синтаксис, что и обычные формулы
Используйте те же математические операторы
Следуйте тому же порядку работы

Однако формулы массивов заключены в фигурные скобки {}. И вы не можете просто ввести их; Вы должны добавить их, нажав Ctrl , Shift , , и введите ключи после ввода формулы в ячейку или ячейки. По этой причине формулу массива иногда называют формулой CSE в Excel.

Каждый раз, когда вы редактируете формулу массива, фигурные скобки исчезают. Чтобы вернуть их, снова нажмите клавиши Ctrl , Shift и Enter .

Существует два основных типа формул массива:

Формулы массива из одной ячейки, которые выполняют несколько вычислений в одной ячейке листа
Формулы из нескольких ячеек массива, которые находятся в нескольких ячейках листа

Как создать формулу массива

Введите формулу в ячейку.
Удерживайте клавиши Ctrl и Shift на клавиатуре.
Нажмите и отпустите клавишу Enter, чтобы создать формулу массива.
Отпустите клавиши Ctrl и Shift .
Если все сделано правильно, фигурные скобки будут окружать формулу.

Формулы для отдельных ячеек

Формула массива одной ячейки использует функцию, такую как SUM , AVERAGE или COUNT , для объединения вывода формулы массива из нескольких ячеек в одно значение в одной ячейке. Ниже приведен пример:

{= СУММ (А1: А2 * В1: В2)}

Приведенная выше формула складывает произведение A1 * B1 и A2 * B2 , а затем возвращает один результат в одной ячейке на листе. Другой способ представления этой формулы:

= (А1 * В1) + (А2 * В2)

Multi-Cell Array Formulas

Как следует из их названия, формулы массива из нескольких ячеек находятся в нескольких ячейках рабочего листа, и они возвращают массив в качестве ответа. Другими словами, одна и та же формула находится в двух или более ячейках и возвращает разные ответы в каждой ячейке.

Каждая копия или экземпляр формулы массива выполняет одинаковые вычисления в каждой ячейке, в которой она находится, но каждая использует разные данные. Поэтому каждый из них дает разные результаты. Пример формулы множества ячеек:

{= А1: А2 * В1: В2}

Если приведенная выше формула массива находится в ячейках C1 и C2 на листе, то результаты будут следующими:

Данные в A1 умножаются на данные в B1 , а результаты появляются в ячейке C1 .
Данные в A2 умножаются на данные в B2 , а результаты появляются в ячейке C2 .

Формулы массивов и функции Excel

Вы можете использовать многие встроенные функции Excel, такие как SUM , AVERAGE и COUNT , в формуле массива. Есть также несколько функций, таких как TRANSPOSE , которые всегда должны быть формулой массива, чтобы работать правильно. (Функция TRANSPOSE копирует данные из строки в столбец или наоборот.)

Вы также можете расширить полезность многих функций, таких как INDEX и MATCH или MAX и IF , используя их вместе в формуле массива.

Создать простую формулу массива из одной ячейки

Формулы массива с одной ячейкой обычно сначала выполняют вычисление с несколькими ячейками, а затем используют функцию, такую как AVERAGE или SUM, чтобы объединить выходные данные массива в один результат.

Игнорировать значения ошибок при поиске данных

Эта формула массива использует функции AVERAGE , IF и ISNUMBER, чтобы найти среднее значение для существующих данных , игнорируя значения ошибок, такие как # DIV / 0! и #NAME ?

Подсчет ячеек данных

Используйте SUM и ПЧ функции в формуле массива для подсчета клеток данных , которые соответствуют одному из нескольких условий; Этот метод отличается от использования функции COUNTIFS в Excel , которая требует, чтобы все установленные условия были выполнены до того, как он подсчитает ячейку.

Найти наибольшее положительное или отрицательное число

Этот пример сочетает в себе MAX функции и ПЧ функции в формуле массива , который будет найти наибольшее или максимальное значение для диапазона данных , когда она отвечает определенным критериям. Здесь наибольшее значение представляет самое медленное время.

Найдите наименьшее положительное или отрицательное число

Как и в примере выше, вы можете объединить функции MIN и IF в формуле массива, чтобы найти наименьшее или минимальное значение для диапазона данных, когда оно соответствует определенным критериям.

Найти среднее или среднее значение

Функция MEDIAN в Excel находит среднее значение для списка данных. Комбинируя его с функцией IF в формуле массива, вы можете найти среднее значение для разных групп связанных данных.

Создайте формулу поиска с несколькими критериями

Эта формула массива включает в себя вложение функций MATCH и INDEX для поиска конкретной информации в базе данных.

Сделать левую формулу поиска

ВПР функция обычно только поиск данных , расположенных в колонках справа, но, комбинируя его с ВЫБРАТЬ функции , вы можете создать левую формулу подстановки , которая будет искать столбцы данных слева от Lookup_Value аргумента.

При вводе массива в качестве аргумента для функции, которая обычно содержит только одно значение или ссылку на ячейку, вы можете вводить фигурные скобки напрямую, а не использовать комбинацию клавиш Ctrl + Shift + Enter , как в приведенном выше примере.

Преимущество массивов Excel

Перейти к основному содержанию

Илан Лотвин

руководитель отдела автоматизации маркетинга и данных | Бизнес-аналитик | Консультант по автоматизации | Python, SQL, Excel, VBA | Цена за конверсию (Иллинойс)

Опубликовано 16 августа 2021 г.

+ Подписаться

Одной из любимых тем в Excel являются формулы массива.

Немногие знакомы с возможностями массивов, и это позор, поскольку формулы массива — чрезвычайно мощный инструмент.

В прошлом правилом была одна формула в одной ячейке, даже с расширенными формулами мы должны были использовать одну и ту же формулу для каждой ячейки (занимает много памяти и, следовательно, больше времени для обработки вычислений)

Сегодня (из-за изменений в механизме вычислений Microsoft excel) Excel позволяет использовать одну формулу для заполнения нескольких ячеек — динамические массивы

Формула массива — это формула, которая может выполнять несколько вычислений для одного или нескольких элементов массива. Вы можете думать о массиве как о строке или столбце значений или комбинации строк и столбцов значений.

Если вы ежедневно используете таблицы Excel/Google, выполняете расчеты/анализируете различные столбцы, знание формул массива выведет вас на новый уровень!

Я поделюсь с вами самым простым примером, чтобы понять преимущества массивов:

Предположим, вы хотите рассчитать общий объем продаж для каждого автомобиля и общую сумму.

Большинство пользователей Excel создали формулу « =F10*G10 » в ячейке h20 и перетащили ее вниз.

Перетаскивание вниз означает:

Дополнительный объем памяти =

Размер файла больше

Склонен к ошибкам — Любой пользователь может изменить значения/формулы в любом месте столбца, и это будет очень сложно обнаружить.

Вместо этого мы можем использовать формулу массива для выполнения того же расчета, однако:

У нас есть только одна формула, расположенная в ячейке h20
указание на ошибку (например, #SPILL- см. пример)

Перетаскивая вниз, мы можем использовать формулу с несколькими вызовами для расчета набора промежуточных итогов или, альтернативно, использовать формулу с одной ячейкой для расчета общего итога.

Кроме того, можно рассчитать общее значение в одной ячейке с помощью формулы для одной ячейки. Это уменьшит как минимум два шага (умножение каждой строки, а затем суммирование)

Резюме вышеприведенных преимуществ формул динамического массива (среди многих других):

Непротиворечивость Если щелкнуть любую ячейку от h20 вниз, вы увидите ту же формулу. Эта согласованность может помочь обеспечить большую точность.
Безопасность Вы не можете перезаписать компонент формулы массива с несколькими ячейками. Например, щелкните ячейку h21 и нажмите клавишу DELETE. Excel не изменит вывод массива. Чтобы изменить его, вам нужно выбрать верхнюю левую ячейку в массиве или ячейку h20.
Файлы меньшего размера Часто можно использовать одну формулу массива вместо нескольких промежуточных формул. Например, в примере с продажами автомобилей для вычисления результатов в столбце E используется одна формула массива. Если бы вы использовали стандартные формулы, такие как =F10*G10, F11*G11, F12*G12 и т. д., вы использовали бы 11 различных формул. для расчета тех же результатов. Это не имеет большого значения, но что, если у вас есть тысячи строк? Тогда это может иметь большое значение.
Эффективность Функции массива могут быть эффективным способом построения сложных формул. Формула массива =СУММ(F10:F19*G10:G19) аналогично этому: =СУММ(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18, Ф19*Г19).
Переполнение Формулы динамического массива автоматически переносятся в выходной диапазон. Если ваши исходные данные находятся в таблице Excel, ваши формулы динамического массива будут автоматически изменять размер при добавлении или удалении данных.
#РАЗЛИВ! ошибка Динамические массивы представили #SPILL! ошибка, указывающая на то, что предполагаемый диапазон разлива по какой-то причине заблокирован. Когда вы устраните засор, формула автоматически прольется.

Дополнительный вариант использования динамических массивов (см. пример).

Предположим, вы хотите просуммировать общий объем продаж для каждого торгового представителя (столбцы A и B).

Раньше вы применяли формулу Sumif и перетаскивали ее вниз (столбец K).

Благодаря динамическому массиву, встроенному в функцию Sumif, вы можете избежать ошибок (формула находится только в одной ячейке) и, конечно же, уменьшить размер файла (ячейка E3)

На веб-сайте Microsoft есть отличная документация и пример рабочей тетради, которую можно скачать и попрактиковаться:

https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

Если у вас есть вопросы или комментарии.

Также хотелось бы узнать, как вы используете массивы в своей работе.

Удачи.

Регулярные выражения в Excel
30 окт.
2021 г.
Макросы Excel для бизнеса
27 окт. 2021 г.
Автоматизация электронной почты Outlook
11 окт. 2021 г.
Макросы Excel для бизнеса
6 окт. 2021 г.
Бизнес-решение — импорт имен файлов (макрос Excel)
31 августа 2021 г.

Другие также смотрели

Исследуйте темы

Работа с динамическими массивами Excel

Сообщество Excel было в восторге от динамических массивов с тех пор, как Microsoft выпустила общий выпуск в январе 2020 года.

Динамические массивы Excel изменили способ использования всех функций и формул в Excel в лучшую сторону. Для простоты мы будем использовать «Dynamic Excel» для обозначения новой среды и «Pre-Dynamic Excel» для описания предыдущего поведения Excel.

Что такое «динамический массив» в Excel?

Чтобы понять динамические массивы, нам нужно понять, как работают формулы в Pre-Dynamic Excel. Вообще говоря, одна формула возвращает вывод в одну ячейку. Когда формулы ссылались на диапазон, они по-прежнему были запрограммированы на возврат одного значения для каждой записи. Даже кажущееся исключение — формулы CSE или массива (см. ниже) — требовало дополнительных знаний о размере выходного диапазона.

В Dynamic Excel Excel автоматически выводит многозначный динамический диапазон, если это результат вашей формулы. Хорошим примером, иллюстрирующим разницу между Pre-Dynamic и Dynamic Excel, является функция ЧАСТОТА.

Загрузите бесплатный файл практики!

Используйте этот бесплатный файл Excel, чтобы практиковаться вместе с учебным пособием.

Введите адрес электронной почты

Control+Shift+Enter (CSE) и динамический массив

FREQUENCY — это функция массива . Для любой версии до Excel 365 это означает, что вам придется сначала выделить диапазон ячеек, в котором будет отображаться результирующий список. Затем введите формулу, используя синтаксис функции ЧАСТОТА.

Важно, чтобы вы не нажимали Enter. Нажмите CTRL+SHIFT+ВВОД. Это позволит Excel узнать, что вы вводите формулу массива. Excel вставляет фигурные скобки вокруг формулы. Это напомнит вам, что это было введено как формула массива.

Если у вас установлена текущая версия Microsoft 365, Excel распознает ЧАСТОТУ как функцию массива. Поскольку Excel 365 работает в среде динамического массива, вы можете просто ввести формулу в первую ячейку диапазона, который вы хотите использовать в качестве выходного диапазона. Затем нажмите клавишу ВВОД, как обычно, и результаты будут отображаться в нескольких ячейках.

Dynamic Excel также упрощает использование констант массива. Теперь, когда вы вводите константы массива в формулу, Excel понимает, как обработать операцию без какого-либо специального вмешательства.

  =B3:B6*{1.3,1.6}

Фигурные скобки вокруг констант массива вводятся вручную и интерпретируются Excel как одномерный вертикальный массив. Это не ново. Однако возможность слить результаты без ввода формулы с помощью Control+Shift+Enter есть. По общему признанию, вводить формулы CSE всегда было немного неудобно.

Из этих примеров видно, почему формулы CSE находятся на пути к полному отказу от использования.

Доступность в версиях Excel

При всей шумихе вокруг динамических массивов Excel вы, вероятно, захотите узнать, о чем идет речь. Вот несколько вещей, о которых следует помнить:

Динамические массивы недоступны в версиях до Excel 365, поэтому, к сожалению, они недоступны в Excel 2019.
Есть восемь новых встроенных функций динамического массива.
Функции и формулы динамических массивов также доступны в Excel Online.

Возможности

В Dynamic Excel есть три особенно заметных особенности: автоматическое поведение сброса, оператор диапазона сброса и оператор неявного пересечения.

1.

Поведение при сбросе

Когда формулы динамического массива автоматически возвращают значения в несколько ячеек в зависимости от требуемых выходных данных, такое поведение называется сбросом . Распределение имеет несколько преимуществ, в том числе:

Excel определяет и применяет требуемую область вывода без каких-либо изменений в способе ввода формулы.
Когда исходные данные изменяются, полученные результаты немедленно обновляются.
Уменьшена потребность в абсолютных и относительных ссылках.
Уменьшена потребность в копировании формул в наборе данных.
Возможность легко создавать списки проверки данных и именованные диапазоны, которые обновляются при расширении или сокращении списков.

Выходная область формулы динамического массива называется диапазоном разлива . Диапазон разлива обозначается прямоугольником вокруг выходной области, когда выбрана хотя бы одна из ячеек.

Как и в формулах CSE до Dynamic Excel, редактируется только первая ячейка в области разлива. Формулы в других ячейках отображаются серым цветом и не могут быть изменены. Любая попытка изменить «фантомные» ячейки в пределах диапазона разлива уничтожит диапазон и приведет к ошибке #SPILL! ошибка.

2. Оператор диапазона сброса

В Excel введен оператор диапазона сброса , #, для обозначения выходных данных массива сброса. Например, D2# относится к диапазону сброса, начинающемуся с ячейки D2, которая была заполнена с использованием формулы динамического массива UNIQUE(B2:B7). Формула в ячейке E2 ссылается на значения в ячейке D2:D5 с выражением D2# .

  =СЧЁТЕСЛИ(B2:B7,D2#)

3. Неявный оператор пересечения

Неявная логика пересечения не нова. Excel делает это в фоновом режиме с Pre-Dynamic Excel. В таблицах Excel неявный оператор пересечения @ долгое время использовался для обозначения операции, которая ссылается на значение в той же строке, что и формула. Неявное пересечение заставляет формулу возвращать одно значение, поскольку ячейка может содержать только одно значение.

В приведенном ниже примере в Pre-Dynamic Excel вводится следующая формула.

  =A4:A8/СУММ($A$4:$A$8)

Поскольку в этой среде одна формула предназначена для вывода в одну ячейку, Pre-Dynamic Excel не может перенести результаты в ячейки B4:B8 и должен решить, какое значение вернуть в ячейку, в которую была введена формула. Хотя символ @ не виден, Excel использует молчаливое неявное пересечение в фоновом режиме, чтобы вернуть значение, используя ячейку в той же строке или столбце, что и формула.

Благодаря возможности возвращать несколько значений в Dynamic Excel, эта же формула автоматически распространяет результаты в диапазоне B4:B8.

Если вы хотите, чтобы Excel возвращал один ввод, вручную введите оператор неявного пересечения @, и Excel будет использовать значение в соответствующей строке для выполнения вычислений.

Например, на изображении ниже массив h4:I6 использует функцию СОРТИРОВКИ для сортировки продаж в регионах от большего к меньшему, а не в алфавитном порядке.

  =СОРТИРОВКА(E3:F6,2,-1)

Однако, если бы нас интересовало только название региона с наибольшими продажами, мы могли бы поместить неявный оператор перед именем функции, заставив Excel возвращать одно ценить.

  =@SORT(E3:F6,2,-1)

По мере продолжения перехода к Dynamic Excel вы также можете увидеть неявный оператор пересечения для обратной совместимости. Когда формула, вызывающая неявное пересечение, создается в Pre-Dynamic Excel и затем открывается в динамической среде, оператор @ теперь может быть виден, чтобы указать, почему было возвращено только одно значение.

Функции и формулы динамических массивов

Существует восемь новых функций динамических массивов со встроенными характеристиками динамического массива, то есть эти функции созданы для возврата динамических диапазонов. Эти функции:

ФИЛЬТР — фильтровать записи по критериям.
RANDARRAY — генерирует случайный список или массив чисел.
ПОСЛЕДОВАТЕЛЬНОСТЬ — создать последовательный список или массив чисел.
SORT — сортировка списка или массива по столбцу или строке в этом массиве.
SORTBY — сортировать список или массив по другому списку или массиву.
UNIQUE — извлечь уникальные значения из списка.
XLOOKUP — гибкий поиск, возвращающий соответствующие значения в списке или массиве.
XMATCH — гибкий поиск, возвращающий относительное положение значений в списке или массиве.

Объединение функций динамического массива

Вышеуказанные функции сами по себе эффективны, но их объединение позволяет выполнять несколько задач в одной записи. Несколько примеров показаны ниже.

Пример 1 — СОРТИРОВКА и ФИЛЬТР

  SORT(массив, [sort_index], [sort_order], [by_col])

  ФИЛЬТР(массив, включить, [if_empty])

В этой комбинации функция СОРТИРОВКА оборачивает функцию ФИЛЬТР для сортировки результатов отфильтрованного массива.

  =СОРТИРОВКА(ФИЛЬТР(A2:D12,B2:B12="бутерброд"),4)

Формула в ячейке F2 фильтрует массив A2:D12 путем поиска записей с категорией «бутерброд». Функция SORT сортирует этот массив по цене, 4-й столбец.

Пример 2 — СОРТИРОВКА и УНИКАЛЬНОСТЬ

  SORT(массив, [индекс_сортировки], [порядок_сортировки], [по_столбцу])

  UNIQUE(массив, [по_столбцу], [exactly_once])

Используйте СОРТИРОВКУ вокруг функции UNIQUE для извлечения уникальных значений в массиве и последующей сортировки записей, соответствующих определенному критерию.

Приведенная выше функция UNIQUE возвращает уникальные значения в диапазоне B3:B8, а функция SORT использовалась для их сортировки в алфавитном порядке. Результаты были автоматически разлиты в диапазоне E3:E6.

Существуют и другие опции для функций UNIQUE и SORT.

Пример 3 — COUNTA и UNIQUE

  СЧЁТ(значение1, [значение2]…)

  UNIQUE(array, [by_col], [exactly_once])

Когда COUNTA вложен с UNIQUE, вы можете быстро подсчитать количество уникальных значений в массиве, используя одну запись.

  =COUNTA(UNIQUE(База данных[Имя группы]))

Пример 4 — ФИЛЬТР и ФИЛЬТР

  ФИЛЬТР(массив, включить, [if_empty])

Вы даже можете вложить функцию ФИЛЬТР в другой ФИЛЬТР, чтобы вернуть выбранные столбцы отфильтрованных записей!

  =ФИЛЬТР(ФИЛЬТР(Меню,Меню[Цена]<5),{1,0,1})

Использование константы массива {1,0,1} в качестве аргумента include внешней функции ФИЛЬТР Excel, чтобы исключить второй столбец (поскольку 1=ИСТИНА и 0=ЛОЖЬ).

Списки проверки данных динамического массива

Раньше создание динамического раскрывающегося списка для проверки данных было промежуточной задачей, поскольку в Excel не было встроенного метода для этой задачи. Нам нужно было бы вставить новый элемент где-нибудь в середине исходного списка или использовать функцию OFFSET в сочетании с COUNTA, чтобы иметь возможность добавлять новые элементы в конец исходного списка. Это было довольно сложное решение.

После того как исходный список создан с помощью динамической функции или формулы, создание динамического списка проверки данных из расширенного массива теперь так же просто, как использование индикатора диапазона переноса.

Шаг 1. Создайте список источников с помощью функции динамического массива или формулы

В приведенном ниже примере функция СОРТИРОВКИ использовалась для создания списка источников в алфавитном порядке.

  =СОРТИРОВКА(Меню[Название элемента])

Шаг 2. Используйте индикатор диапазона разлива, чтобы обратиться к динамическому выходу

В ячейке, где требуется раскрывающийся список, перейдите на вкладку «Данные» и выберите команду «Проверка данных». Выберите Список из раскрывающегося списка Разрешить .
См. диапазон разливов в поле Источник . Индикатор диапазона разлива необходимо ввести вручную после ссылки на первую ячейку диапазона разлива. Если проверка данных будет скопирована в другие ячейки, следует использовать абсолютную ссылку на ячейку.

  =$A$14#

Ошибки при использовании динамических массивов

При работе с формулами динамических массивов могут возникать новые сообщения об ошибках. Вместо того, чтобы ломать голову над ними, вот краткий список наиболее распространенных и возможных причин.

#РАЗЛИВ!

Диапазон разлива не пустой

Excel вернет #SPILL! сообщение об ошибке в первой ячейке диапазона разлива при наличии препятствия, то есть если какая-либо из ячеек в требуемой области вывода не пуста. Чтобы исправить это, очистите значение (я) в мешающей ячейке (ячейках) или переместите формулу в место, где достаточно пустых ячеек для размещения вывода.

Диапазон переполнения неизвестен

Эта ошибка может возникнуть, если Excel не может определить, сколько строк и столбцов потребуется для вывода. Например, когда изменчивые функции, возвращающие разные значения при каждом пересчете формул (например, СЛУЧАЙ, СЛУЧМЕЖДУ, СЛУЧАЙ), вложены в некоторые другие динамические функции. См. пример ниже.

Обратите внимание, что одна и та же формула может работать в одних итерациях и не работать в других просто из-за нестабильности функции. Вы можете попробовать еще раз или вычислить формулу шаг за шагом, т. е. сгенерировать случайные значения с помощью формулы, скопировать и вставить их как значения, а затем вручную поместить их в массив.

Диапазон сброса в таблице

Несколько удивительно, но таблицы Excel в настоящее время не поддерживают режим сброса. Это наиболее очевидно при попытке использовать одну из восьми новых функций, в которые встроена функциональность динамического массива. Чтобы использовать эти функции, преобразуйте таблицу Excel в диапазон. Для устаревших функций поведение таблиц в Dynamic и Pre-Dynamic Excel идентично.

#РАССЧ!

Пустые массивы не поддерживаются

Поскольку Dynamic Excel не поддерживает пустые массивы, любая формула, результатом которой является пустой массив, вернет #CALC! ошибка. Например, если ни одна запись в исходном массиве не соответствует критериям в включают аргумент функции ФИЛЬТР, Excel вернет #CALC! ошибка. В этом случае решением было бы включить необязательный аргумент if_empty для отображения альтернативного ответа.

Вы не можете изменить часть массива

Это сообщение появляется во всплывающем окне, если вы пытаетесь отсортировать элементы в диапазоне разброса с помощью команды «Сортировка» на ленте или в контекстном меню.

Это связано с известным принципом, согласно которому мы не можем изменить часть формулы массива (аналогично старым формулам CSE). Чтобы отсортировать диапазон сброса, используйте функцию СОРТИРОВКА по формуле, которая создала диапазон сброса. См. примеры 1 и 2 выше.

Путь вперед

Уже взволнованы? Dynamic Excel представляет собой значительное обновление способа обработки формул в Excel. Со временем Pre-Dynamic Excel уйдет в прошлое, а динамические массивы станут стандартом.

Чтобы узнать больше об Excel, попробуйте наш курс Excel — базовый и продвинутый сегодня.