Everything You Need to Know About Array Constants in Excel

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

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

Константы массива в Excel: определение

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

={10,20,30,40}

в ячейку A1 и нажатие Enter Возвращает динамический массив 10 в ячейке A1, 20 в ячейке B1, 30 в ячейке C1 и 40 в ячейке D1.

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

С другой стороны, использование полуколонов между значениями в константе массива возвращает вертикальный динамический массив (в рядах):

={10;20;30;40}

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

={10,20;30,40}

Константы массива работают иначе, чем раньше

Одноразовые версии Excel с 2021 года, Excel для Microsoft 365 и Excel для Интернета Все поддерживают динамические формулы массива. Это означает, что когда вы вводите константу массива в формуле, которая ожидает множественные значения и нажимает Enter, результат перемещается в соседние ячейки, а только верхняя левая ячейка, содержащая формула.

Однако в более старых версиях Excel, которые не поддерживают динамические формулы массива, вам необходимо выбрать все ячейки, в которых вы хотите отображать результаты, ввести формулу и нажать Ctrl+Shift+Enter, чтобы совершить его. На этом этапе та же формула появляется во всех выбранных вами ячеек. Это известно как формула массива CSE или Legacy.

ОС

Windows, MacOS, iPhone, iPad, Android

Бесплатная пробная версия

1 месяц

Microsoft 365 включает в себя доступ к офисным приложениям, таким как Word, Excel и PowerPoint на до пяти устройств, 1 ТБ хранилища OneDrive и многое другое.

Использование константы массива с функциями Excel

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

Sum и Countif: подсчет, сколько значений в массиве соответствует нескольким критериям

Представьте, что у вас есть это Excel Tableназван T_tasklogи вы хотите узнать количество задач, статус которого ПолныйВ В ожиданииили РазбитыйПолем

Чтобы сделать это без константов массива, вы можете ввести следующую формулу (разделенную на отдельные строки для облегчения чтения):

=COUNTIF(T_TaskLog[Status],"Complete")
+
COUNTIF(T_TaskLog[Status],"Pending")
+
COUNTIF(T_TaskLog[Status],"Pitched")

где результаты трех отдельных График Формулы складываются вместе с использованием оператора плюс (+).

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

=COUNTIF(T_TaskLog[Status],

Теперь, для критериев, вам необходимо ввести все три значения статуса в качестве постоянной массива, заключенной в кудрявые скобки. Затем добавьте закрытие скобок и нажмите Enter:

=COUNTIF(T_TaskLog[Status],{"Complete","Pending","Pitched"})

На этом этапе Excel возвращает три отдельных результата в виде динамического массива – 3 для Полный2 для В ожиданиии 2 для РазбитыйПолем Это потому, что вы еще не сказали Excel, чтобы добавить результаты вместе.

Чтобы исправить это, оберните всю формулу внутри функции SUM и нажмите Enter:

=SUM(COUNTIF(T_TaskLog[Status],{"Complete","Pending","Pitched"}))

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

Большой: найти вершину х Значения в диапазоне

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

Один из способов сделать это – использовать Большая функция в трех отдельных ячеек.

Ввод этой формулы в ячейку E2 возвращает наибольшее значение в столбце прибыли:

=LARGE(T_Profits[Profit],1)

Затем ввод этой формулы в ячейку E3 возвращает второе по величине значение:

=LARGE(T_Profits[Profit],2)

Наконец, ввод этой формулы в ячейку E4 возвращает третье по величине значение:

=LARGE(T_Profits[Profit],3)

Затем вы можете использовать Функция xlookup Чтобы получить идентификаторы магазинов, где были получены эти три самых высоких прибыли:

=XLOOKUP(E2:E4,T_Profits[Profit],T_Profits[Shop])

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

=LARGE(T_Profits[Profit],{1;2;3})

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

Точно так же, если вы хотите подвести итог самые большие три прибыли без использования констант массива, вам нужно будет ввести большую функцию три раза, разделенная знаком плюса:

=LARGE(T_Profits[Profit],1)+LARGE(T_Profits[Profit],2)+LARGE(T_Profits[Profit],3)

Однако использование константы массива с функцией суммы делает формулу намного более аккуратной:

=SUM(LARGE(T_Profits[Profit],{1,2,3}))

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

=SUM(LARGE(T_Profits[Profit],{1,2,3,4}))

Константы массива имен в Excel

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

Чтобы создать именованную константу массива, на вкладке «Формулы» на ленте нажмите «Менеджер по имени».

Затем в диалоговом окне «Менеджер имени» нажмите «Новый».

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

={

Теперь введите константу массива, вспоминая, что для создания горизонтального (строя) массив значения должны быть разделены запятыми, а для вертикальных (столбца) массивов вам необходимо разделить их с полуколонами. Кроме того, помните, что текстовые значения должны быть заключены в двойные кавычки. Когда вы закончите, закройте кудрявые брекеты:

={"Mon","Tue","Wed","Thu","Fri","Sat","Sun"}

Наконец, в поле «Имя» введите памятное имя для постоянной массива и нажмите «ОК».

Теперь вы можете использовать эту константу массива в своей рабочей книге, набрав равный знак, за которым следует только что назначили имени, и нажав Enter:

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

={5,10,15,20}

Таким образом, умножение значения в ячейке A1 на эту названную константу массива дает четыре отдельных результата:

=A1*Fives

Участки при использовании константы массива в Excel

Несмотря на множество преимуществ использования константы массива в Excel, есть некоторые правила и ограничения, о которых вы должны знать:

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

• Константы массива могут содержать только текст в двойных кавычках, простых числах (без символов валюты или процентных знаков), или Логические ценности (Истинно и ложь), разделенные запятыми и полуколонными делимитерами. Они не могут содержать ссылки на ячейки, другие массивы, формулы или подстановочные знаки.

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

• Если вы планируете поделиться своей рабочей книгой с людьми, менее опытными в Excel, остерегайтесь эффекта черного ящика, который создают константы массива. То есть, поскольку процесс расчета в константах массива менее прозрачен, чем в стандартных формулах, те, кто не знаком с логикой массива, может изо всех сил пытаться понять, как работает электронная таблица.

• В некоторых случаях использование функции динамического массива более эффективно, чем использование константы массива. Например, вместо того, чтобы вводить константу, такую ​​как {1,2,3}, вы можете использовать Функция последовательности Чтобы получить тот же результат.

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

Константы массива особенно удобны при использовании в качестве аргументов в функциях Excel, которые не возвращают динамические массивы по умолчанию. Тем не менее, динамические функции массива-доступны в одноразовых версиях Excel с 2021 года, Excel для Microsoft 365 и Excel для Интернета-предназначены специально для возврата нескольких значений в диапазон ячеек. Примеры этих специальных функций включают ФИЛЬТРВ Сорт и сортиВ УНИКАЛЬНЫЙи XlookupПолем