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

Содержание

Формулы массива в Excel — voxt.ru

Формулы массива — это очень полезные и мощные формулы, которые используются для выполнения некоторых из очень сложных вычислений в Excel, формула массива также известна как формулы CSE, поскольку для выполнения формул массива нам нужно одновременно нажимать CTRL + SHIFT + ENTER, а не просто нажимать enter, есть два типа формул массива: один дает нам один результат, а другой дает нам несколько результатов.

Формулы массива в Excel

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

Перво-наперво, что такое массив? Массив — это набор значений или переменных в наборе данных, например {a, b, c, d} — это массив, который имеет значения от «a» до «d». Точно так же в массиве excel — это диапазон ячеек значений,

На приведенном выше снимке экрана ячейки от B2 до ячейки G2 представляют собой массив или диапазон ячеек.

Они также известны как «формулы CSE» или «Control Shift Enter Formulas», поскольку нам нужно нажимать Ctrl + Shift + Enter для создания формулы массива в Excel.

В Excel у нас есть два типа формул массива:

Тот, который дает нам единственный результат.
Другой, который дает нам несколько результатов.

Мы изучим оба типа формул массива по этой теме.

Объяснение формул массива в Excel

Как объяснялось, формулы массива в Excel — это мощные формулы, которые помогают нам выполнять очень сложные вычисления.

Из приведенных выше примеров мы узнали, что эти формулы в Excel действительно упрощают сложные и длительные вычисления. Однако нужно помнить одну вещь в примере 2, где мы вернули несколько значений, используя эти формулы в Excel. Мы не можем изменить или сократить значение ячейки, так как это часть массива.

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

Например, введите случайное число в ячейку G6 и нажмите клавишу ВВОД, это приведет к следующей ошибке:

При вводе случайного значения 56 в ячейку G6 выдает ошибку, что мы не можем изменить часть массива.

Если нам нужно отредактировать формулу массива, перейдите на панель функций и отредактируйте значения или предоставленный диапазон, или если мы хотим удалить формулу массива, удалите весь массив, то есть диапазон ячеек от B6 до G6.

Как использовать формулы массива в Excel?

Давайте изучим формулу массива на нескольких примерах:

Вы можете скачать этот шаблон Excel с формулой массива здесь — Шаблон Excel с формулой массива

Формулы массива могут использоваться двух типов:

Если мы хотим вернуть одно значение, используйте эти формулы в одной ячейке, как в примере 1.
Если мы хотим вернуть более одного значения, используйте эти формулы в Excel, выбрав диапазон ячеек, как в примере 2.
Нажмите CTRL + Shift + Enter, чтобы создать формулу массива.

Пример # 1

В данных о продажах ресторана указаны продукты, цена каждого продукта и количество проданных продуктов.

Владелец хочет подсчитать общий объем продаж этих продуктов.

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

И он получит общий объем продаж,

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

Мы создадим нашу первую формулу массива в ячейке B7.

# 1 — В ячейке B7 введите = sum и нажмите кнопку табуляции на клавиатуре. Это открывает формулу суммы.

# 2 — Выберите диапазон ячеек от B2 до G2.

# 3 — Теперь поставьте после этого знак звездочки «*» для умножения.

# 4 — Выберите диапазон ячеек от B3 до G3.

# 5 — Вместо нажатия клавиши ввода нажмите CTRL + SHIFT + ВВОД.

Excel вычислил общий объем продаж, умножив количество продуктов на цену в каждом столбце и просуммировав их. В выделенном разделе мы видим, что Excel создал массив для диапазонов ячеек от B2 до G2 и от B3 до G3.

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

Пример # 2

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

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

Уравнение в ячейке B5, он должен повторить его для ячейки c4, d4 и так далее.

Опять же, если бы это были большие данные, это было бы утомительной и утомительной задачей.

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

# 1 — Выберите ячейки, в которых мы хотим получить промежуточные итоги, т. Е. По продажам каждого продукта. В данном случае это диапазон ячеек от B8 до G8.

# 2 — Введите «равно», чтобы подписать «=»

# 3 — Выберите диапазон ячеек от B2 до G2.

# 4 — После этого введите звездочку «*».

# 5 — Теперь выберите диапазон ячеек от B3 до G3.

# 6 — Что касается формулы массива, не нажимайте ввод, нажмите CTRL + SHIFT + ВВОД.

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

Ячейки от B6 до G6 представляют собой массив.

Пример # 3

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

Теперь владелец обычно может вычесть значение продаж с февраля месяца до января в ячейке C2 и так далее, а затем вычислить среднее значение.

Опять же, если бы это были большие данные, это было бы очень утомительной задачей. Сделаем это с массивом в формулах Excel.

# 1 — В ячейке D8 введите = среднее и нажмите кнопку TAB.

# 2 — Чтобы рассчитать рост, нам нужно вычесть значения одного месяца из предыдущего месяца, чтобы выбрать диапазон ячеек от B3 до B7.

# 3 — Поставьте после этого знак вычитания (-),

# 4 — Теперь выберите ячейки от B2 до B6.

# 5 — Что касается массива в excel, не нажимайте ввод, нажмите CTRL + SHIFT + ВВОД.

Формулы массивов в Excel легко рассчитали средний рост продаж без каких-либо проблем.

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

То, что нужно запомнить

Они также известны как формулы CSE или Control Shift для ввода формул Excel.
Не заключайте скобки для массива; Сам excel это делает, он вернет ошибку или неверное значение.
Если ввести скобку «{» вручную, Excel будет рассматривать ее как текст.
Не нажимайте ввод; вместо этого нажмите CTRL + SHIFT + Enter, чтобы использовать формулу массива.
Мы не можем изменить ячейку массива, поэтому, чтобы изменить формулу массива, либо измените формулу на панели функций, либо удалите формулу и измените ее в желаемом формате.

УЗНАТЬ БОЛЬШЕ >>

Post Views: 693

Формула массива в Excel | Блог Александра Воробьева

Опубликовано 24 мая 2015
Рубрика: Справочник Excel | 8 комментариев

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

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

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

Массив в Excel – это однородные данные, размещенные в нескольких ячейках столбца или строки или области. Данные, размещенные в ячейках одной строки или одного столбца – это одномерный массив. Данные, размещенные в нескольких строках и столбцах, то есть в таблице – это двумерный массив.

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

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

Формула массива вводится в ячейку после знака =, как и обычная формула, но если окончание ввода простой формулы подтверждается нажатием на клавишу Enter, то для окончания ввода формулы массива необходимо последовательно нажать на три клавиши – Ctrl+Shift+Enter. (Клавишу + нажимать не нужно, следует удерживать нажатыми предыдущие клавиши до момента нажатия на последнюю кнопку!)

Формула массива примет вид: {=ФОРМУЛА}.

Не стоит пробовать ввести фигурные скобки вручную напрямую с помощью клавиатуры!!! Для того чтобы программа Excel «поняла», что вы хотите ввести формулу массива нужно нажать на три вышеперечисленные клавиши!

Аргументами функций ФОРМУЛЫ могут быть как одиночные ячейки, так и диапазоны ячеек – все, как и в обычных формулах.

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

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

Ряд важных функций в Excel работают только с массивами и выводят результаты в массив. Это функции ЛИНЕЙН() ЛГРФПРИБЛ(), ТЕНДЕНЦИЯ() и другие.

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

Пример использования формул массивов для выборочного суммирования.

Требуется выборочно быстро найти и просуммировать некоторые значения из большой таблицы MS Excel.

Например, из обширной базы поставок изделий необходимо извлечь информацию о количестве и стоимости поставленных компании ООО «Оберон» изделий «Блок-СМ15».

Для решения задачи следует выполнить ниже перечисленную последовательность действий.

1. Скопировать из таблицы «База поставок изделий» название фирмы-заказчика и вставить, например,

в ячейку B2: ООО «Оберон»

2. Скопировать название изделия и вставить

в ячейку B3: Блок-СМ15

3. Ввести формулу массива для подсчета количества изделий

в ячейку B4: {=СУММ((A9:A27=B2)*(B9:B27=B3)*C9:C27)}=12

4. Ввести формулу массива для подсчета общей стоимости поставленных изделий

в ячейку B5: {=СУММ((A9:A27=B2)*(B9:B27=B3)*E9:E27)}=420 000,00

Как работают формулы?

Формула в ячейке B4 суммирует построчно произведения: Σ(a_i*b_i*c_i).

Формула в ячейке B5 суммирует построчно произведения: Σ(a_i*b_i*e_i).

Если в строке диапазона A9:A27 Excel находит значение равное значению в ячейке B2, то a_i=1. Если находит значение не равное значению в ячейке B2, то a_i=0. Аналогично присваиваются значения b_i. Значения c_i и e_i просто берутся построчно из ячеек указанных диапазонов.

Поставленную задачу можно решить и другим способом: при помощи объявления таблицы «База поставок изделий» списком, последовательного применения автофильтров к столбцам A и B и добавлением строки итогов.

Спектр задач, решаемых при помощи формул массивов.

Ниже приведен внушительный перечень задач, подробно рассмотренных в главе Применение формул массивов книги «Формулы в Excel 2013» Джона Уокенбаха. Изучив эти примеры и начав их использование на практике, можно серьезно повысить эффективность своей работы в Excel.

1. Суммирование в диапазоне, содержащем ошибки.

2. Подсчет количества ошибок в диапазоне.

3. Суммирование n наибольших значений в диапазоне.

4. Вычисление среднего без учета нулевых значений.

5. Поиск значения в диапазоне.

6. Подсчет отличающихся значений в двух диапазонах.

7. Местоположение максимального значения диапазона.

8. Поиск номера строки, в которой находится n-е значение, совпадающее с заданным.

9. Получение самого длинного текста в диапазоне.

10. Определение допустимых значений диапазона.

11. Вычисление суммы цифр числа.

12. Суммирование округленных значений.

13. Суммирование каждого n-го значения в массиве.

14. Удаление нечисловых символов из текстовой строки.

15. Поиск ближайшего значения в диапазоне.

16. Получение последнего значения в столбце.

17. Получение последнего значения в строке.

18. Извлечение положительных значений из диапазона.

19. Извлечение непустых ячеек из диапазона.

20. Изменение порядка следования элементов в диапазоне на противоположный.

21. Динамическая сортировка значений диапазона.

22. Возвращение списка уникальных значений диапазона.

23. Отображение календаря в диапазоне.

Другие статьи автора блога

На главную

Статьи с близкой тематикой

Отзывы

Как суммировать данные электронных таблиц с помощью формул массива Excel

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

Например, менеджерам может быть интересно узнать, что в прошлом месяце объем продаж увеличился на 10%. Но они были бы очарованы, узнав, что продажи виджетов Пэта Смита в Северо-Западном регионе удвоились в прошлом месяце, в то время как продажи всех остальных людей резко упали.

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

Знакомство с данными

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

Я назвал каждый столбец данных меткой, показанной в заголовке таблицы. Для этого я выбрал всю таблицу, за исключением строки «Итого», затем в группе Formulas, Defined Name s я выбрал Create From Selection ; убедился Верхний ряд проверен; а затем выбрал OK .

Назначая обычные имена диапазонов таблицам Excel, мы обычно получаем немного больше возможностей и делаем наши формулы немного менее загроможденными. И если Таблица сжимается или увеличивается, наши имена подстраиваются под Таблицу.

Знакомство с формулами массива, пример 1

Давайте начнем изучение этих данных с суммирования продаж Джилл и Джо. Ячейки J3 и J4 выполняют эту сводку, используя формулы массива.

Вот формула для показанной ячейки:

J3 : {=SUM(IF(Seller=$I3,Total,0))}

Обратите внимание на фигурные скобки, окружающие эту формулу. Вы НЕ вводите эти символы при вводе формулы. Вместо этого вы вводите формулу массивом.

То есть вы вводите формулу без фигурных скобок. Но когда вы закончите печатать, вы не нажмете Enter. Вместо этого вы удерживаете клавиши Ctrl и Shift , затем нажимаете Enter . После этого Excel отобразит формулу в строке формул с фигурными скобками, как показано выше.

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

Для иллюстрации часть формулы «Продавец=$I3» создает временный массив, подобный следующему: {FALSE; ПРАВДА; ЛОЖНЫЙ; ЛОЖНЫЙ; ЛОЖНЫЙ; ПРАВДА; ЛОЖНЫЙ; ПРАВДА; ЛОЖНЫЙ; ПРАВДА; ЛОЖНЫЙ; ПРАВДА; ЛОЖНЫЙ; FALSE} Здесь второй элемент массива равен TRUE. То есть вторая ячейка диапазона данных Seller — ячейка A3 — содержит текст «Джо».

Формула говорит, что везде, где появляется TRUE, возвращается соответствующее значение из столбца Total. Следовательно, инструкция…
=IF( Seller=$I3, Total, 0 )
…возвращает временный массив: {0; 12600; 0; 0; 0; 5060; 0; 1980 г.; 0; 1540; 0; 4500; 0; 0}

Наконец, функция СУММ возвращает сумму этого временного массива: 25 680.

Вторая формула аналогична:

J3 : {=СУММ(ЕСЛИ(Продавец=$I4,Итого,0))}

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

Другие примеры формул массива

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

Пример 2

В этой таблице представлены данные о продажах по продуктам в разбивке по продавцам. Общий итог, как вы можете видеть, совпадает с общим итогом примера 1.

Эта формула массива немного длиннее:

J9 : {=СУММ(ЕСЛИ((Продавец=$I9)*(Товар=J$8 ),Всего,0))}

Здесь мы умножаем первый массив (Seller=$19) на второй массив (Prod=J8) . Это возвращает массив, содержащий значение TRUE, только если обе соответствующие ячейки массива имеют значение TRUE.

То есть, когда вы работаете с формулами массива, вы умножаете, когда вам нужна связь AND.

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

Когда вы копируете формулу массива в оставшуюся часть этого дисплея, вы должны копировать в два этапа, а не в один. С обычными формулами вы можете скопировать ячейку J9 в диапазон J9:K10. Но когда вы пытаетесь применить тот же подход к формуле массива, Excel жалуется. То есть Excel возражает, когда вы пытаетесь скопировать формулу массива в диапазон из нескольких ячеек, который включает саму себя.

Поэтому сначала скопируйте ячейку J9 в ячейку J10, а затем скопируйте диапазон J9:J10 в диапазон K9:K10.

Формулы в строке 11 и в столбце L являются просто формулами СУММ для столбцов и строк соответственно, как показано здесь:

J11 : =СУММ(J9:J10)
L9 : =СУММ(J9:K9)

Для удобства в следующих примерах я буду называть диапазон вычислений выше и слева от СУММ рабочий диапазон .

Пример 3

Мы можем углубиться в данные, добавив дополнительный аргумент. Здесь мы вводим метку «Шляпы» в ячейку M14, чтобы сообщать только о продажах головных уборов.

Формулы массива для этого примера немного длиннее, но следуют той же схеме, что и раньше:

J16 : {=СУММ(ЕСЛИ((Продавец=$I16)*(Регион=J$15)*(Продукт=$M$14), Итого,0))}

Скопируйте эту формулу в рабочий диапазон, J16:L17.

Пример 4

Формулы массива работают с большим количеством функций, чем просто функция СУММ. И вы можете выполнять дополнительные вычисления в рамках формулы.

Например, эта формула возвращает самую большую цену за шляпы, проданные Джо:

J22 : {=MAX(IF((Продавец=$I22)*(Продукт=J$21),Итого/Кол-во,0) )}

Скопируйте эту формулу в рабочий диапазон.

Пример 5

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

Формула:

J28 : {=СУММ(ЕСЛИ((Продавец=$I28)*(Товар=J$27),Количество,0))}

Как и прежде, скопируйте эту формулу в рабочий диапазон .

Пример 6

Мы можем рассчитать среднюю цену, которую Джо назначил за шляпы.

Вот один из способов сделать это:

P4 : {=СРЗНАЧ( ЕСЛИ( (Продавец=$O4)*(Товар=P$3), Итого/Количество, «») )}

Обратите внимание на нулевую строку в последнем аргументе этой формулы. Если бы мы использовали ноль, функция СРЗНАЧ включила бы все эти нули в расчет своего среднего значения, чего мы не хотим. Вместо этого, используя нулевую строку, мы заставляем функцию СРЗНАЧ игнорировать ячейки, которые не удовлетворяют нашим критериям. В качестве альтернативы мы могли бы использовать FALSE, например:

P4 : {=СРЗНАЧ(ЕСЛИ((Продавец=$O4)*(Продукт=P$3),Итого/Количество,ЛОЖЬ))}

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

Пример 7

Эта версия вычисляет средневзвешенную цену продавца по продукту. Когда мы умножаем каждое значение на эквивалентное количество единиц, проданных из примера 5, и суммируем результаты, мы получаем общие суммы, показанные в примерах 1 и 2.

Ключевая формула:

P10 : {=СУММ( ЕСЛИ((Продавец=010$)*(Товар=9$), Итого, «»)) )
/ СУММ( ЕСЛИ((Продавец=010$) )*(Prod=P$9), Qty, «») )}

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

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

Как обычно, скопируйте эту формулу в рабочий диапазон.

Пример 8

Этот дисплей проверяет наши результаты. Первая формула выглядит просто:

P16 : =J28*P10

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

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

Пример 9

В примере 2 я сказал, что умножение двух тестов создает связь И. То есть результат ИСТИНА, только если все значения, которые вы перемножаете, также ИСТИНА.

Аналогично, суммирование двух результатов создает отношение ИЛИ.

Для иллюстрации предположим, что менеджер по продажам думает об объединении Северного и Западного регионов. Поэтому ему нужен отчет, в котором эти регионы рассматриваются как один регион.

Вот первая формула:

P24 : {=СУММ( ЕСЛИ( (Продавец=$O24)*((Регион=P$22)+(Регион=P$23)), Итого, 0) )}

Здесь, если Продавцом является Джо и если Регион — это либо Север, либо Запад, массив возвращает Итого; в противном случае возвращается 0. Как обычно, функция СУММ возвращает сумму массива.

Обратите внимание на эту часть формулы: (Регион=P$22)+(Регион=P$23)

Это наша замена функции ИЛИ. Поскольку Регион никогда не может быть ОБОИМ Севером и Западом, мы получаем ИСТИНА, если Регион равен любому из них; в противном случае мы получаем ложь.

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

При копировании формулы в ячейку Q24 формула возвращает общую сумму продаж Джо для регионов, которые содержат слово «Юг» или не заполнены.

Расширение формул массива

Формулы массива могут быть расширены неожиданными способами. В частности, функции Excel ВЫБОР и СМЕЩ предлагают некоторые параметры, о которых следует помнить.

Для иллюстрации рассмотрим следующую формулу:

D20: {=СУММ(ЕСЛИ(Продавец=$A$20,Всего,0))}

Предположим, вы хотите расширить эту формулу. Предположим, что иногда вы хотите вернуть Итого, когда Продавец равен метке в ячейке A20, а в других случаях вы хотите вернуть Итого, когда Регион равен метке в ячейке B20. Вы хотите указать используемый тест, введя значение 1 (для продавца) или 2 (для региона) в ячейку C20.

Чтобы настроить эту сводку, вы можете использовать функцию ВЫБОР двумя разными способами:

D20 : {=СУММ(ЕСЛИ(ВЫБРАТЬ($C20,Продавец=$A$20,Регион=$B20),Всего,0))}

E20 : {=ВЫБРАТЬ($C20, СУММ( IF(Seller=$A$20,Total,0) ),
SUM( IF(Region=$B$20,Total,0)) )}

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

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

Аналогичным образом, функция СМЕЩ позволяет вам выбирать разные строки или столбцы для проверки или суммирования. Например:

C21 : {=СУММ( ЕСЛИ(СМЕЩ(Продавец, 0, $A22) = $B22, Итого, 0) )}

Здесь, если значение в ячейке A22 равно 1, функция СМЕЩ вызывает формула для сравнения метки в ячейке B22 с первым столбцом справа от «Продавец» или со столбцом «Продукт» в примере. Если значение в ячейке A22 равно 2, функция OFFSET сравнивает метку со вторым столбцом, которым в примере является столбец «Регион».

Обновление

Когда я впервые написал эту статью, в Excel не существовало S-функций (СУММЕСЛИМН, СРЗНАЧСЛИМН и т. д.). Сегодня вы можете выполнять многие из этих задач с помощью этой функции. Поскольку СУММЕСЛИМН вычисляет быстрее, чем формулы массива, и использует меньше памяти, обязательно изучите и эту функцию.

Однако, когда СУММЕСЛИМН не может сделать то, что вы хотите, формулы массива часто могут вам помочь.

Excel VBA — Создание массива — 3 способа

Я покажу вам три различных способа создания массива в Excel VBA и макросах и как получить данные из этих массивов.

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

Первый способ создать массив самый простой, мы просто используем функцию Array() в VBA:

Sub CreateArrayTest()

'первый способ добавить массив

myarray = Массив ("красный", "зеленый", "синий")

мой второй массив = Массив (10, 20, 30)

'вывести значения из массивов

MsgBox myarray(0) & " " & mysecondarray(2)

Конечный переходник

Это самый простой способ создать переменную массива в VBA и макросах для Excel.

Переменные массива: myarray и мой второй массив . Эти массивы являются просто переменными, которые используют функцию Array() для преобразования в массивы. Обратите внимание, что переменные даже не объявлены в начале макроса (хотя это хорошая практика, здесь это не требуется).

Загрузите прилагаемую электронную таблицу Excel из бокового меню или под этим учебным пособием, чтобы протестировать этот и следующие макросы.

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

Вы также заметите строку вывода:

MsgBox myarray(0) & " " & mysecondarray(2)

Это показывает вам две важные вещи.

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

Второе, на что следует обратить внимание, это то, что по умолчанию индексы начинаются с 0 (ноль), а НЕ с цифры 1.

Таким образом, вывод функции MsgBox выше будет red 30 .

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

. Подпрограмма CreateArrayTest2()
 'второй способ добавления массива
  
 'используя приведенный ниже метод, мы должны объявить, что эти переменные будут массивами
 Dim myarray(2) Как вариант
 Dim mysecondarray(2) Как вариант
  
 мой массив (0) = "красный"
 myarray(1) = "зеленый"
 myarray(2) = "синий"
  
 мой второй массив (0) = 10
 мой второй массив (1) = 20
 мой второй массив (2) = 30
  
 'вывести значения из массивов
 MsgBox myarray(2) & " " & mysecondarray(0)
  
 Конечный переходник

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

Dim myarray(2) Как вариант

Dim mysecondarray(2) Как вариант

Это то же самое, что и объявление обычного типа переменной в макросе, за исключением того, что мы должны сообщить Excel, что это переменная массива. Это делается, но включает (2) после каждой переменной.

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

ПОМНИТЕ: индексы или слоты переменных массива начинаются с 0 (нуля), а НЕ с 1.

Вместо 2 можно поставить любую цифру; вы можете поставить 50, если хотите сохранить 51 значение, или 1, если хотите сохранить 2 значения. Это немного сбивает с толку, и я покажу вам, как решить эту проблему в следующем примере.

Но теперь, когда вы знаете, как создать переменную массива, давайте поместим в нее данные.

Это приводит нас к этим строкам макроса:

мой массив (0) = "красный"

myarray(1) = "зеленый"

myarray(2) = "синий"

мой второй массив (0) = 10

мой второй массив (1) = 20

мой второй массив (2) = 30

На этот раз мы не используем функцию Array(). Вместо этого мы отдельно размещаем каждый элемент внутри массива, используя желаемый номер индекса.

Мы хотим, чтобы текст зеленого цвета сохранялся в myarray по индексу 1 , поэтому мы используем эту строку: