В экселе массивы: Работа с массивами функций в Excel

Содержание

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

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

ВПР;
ГПР;
СТРОКА;
СТОЛБЕЦ;
АДРЕС;
ДВССЫЛ;
ЧСТРОК;
ЧИСЛСТОЛБ;
СМЕЩ;
ПОИСКПОЗ.

Функция ВПР

Вертикальное первое равенство. Ищет совпадение по ключу в первом столбце определенного диапазона и возвращает значение из указанного столбца этого диапазона в совпавшей с ключом строке.

Синтаксис: =ВПР(ключ; диапазон; номер_столбца; [интервальный_просмотр]), где

ключ – обязательный аргумент. Искомое значение, для которого необходимо вернуть значение.
диапазон – обязательный аргумент. Таблица, в которой необходимо найти значение по ключу. Первый столбец таблицы (диапазона) должен содержать значение совпадающее с ключом, иначе будет возвращена ошибка #Н/Д.
номер_столбца – обязательный аргумент. Порядковый номер столбца в указанном диапазоне из которого необходимо возвратить значение в случае совпадения ключа.

интервальный_просмотр – необязательный аргумент. Логическое значение указывающее тип просмотра:
- ЛОЖЬ – функция ищет точное совпадение по первому столбцу таблицы. Если возможно несколько совпадений, то возвращено будет самое первое. Если совпадение не найдено, то функция возвращает ошибку #Н/Д.
- ИСТИНА – функция ищет приблизительное совпадение. Является значением по умолчанию. Приблизительное совпадение означает, если не было найдено ни одного совпадения, то функция вернет значение предыдущего ключа. При этом предыдущим будет считаться тот ключ, который идет перед искомым согласно сортировке от меньшего к большему либо от А до Я. Поэтому, перед применением функции с данным интервальным просмотром, предварительно отсортируйте первый столбец таблицы по возрастанию, так как, если это не сделать, функция может вернуть неправильный результат. Когда найдено несколько совпадений, возвращается последнее из них.

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

Пример использования:

На изображении приведено 3 таблицы. Первая и вторая таблицы располагают исходными данными. Третья таблица собрана из первых двух.
В первой таблице приведены категории товара и расположение каждой категории.
Во второй категории имеется список всех товаров с указанием цен.
Третья таблица содержать часть товаров для которых необходимо определить цену и расположение.

Для цены необходимо использовать функцию ВПР с точным совпадением (интервальный просмотр ЛОЖЬ), так как данный параметр определен для всех товаров и не предусматривает использование цены другого товара, если вдруг она по случайности еще не определена.

Для определения расположения товара используется ВПР с приблизительным совпадением (интервальный просмотр ИСТИНА), так как распределение товара осуществляется по категориям. Из-за того, что наименование товара состоит из названия категории плюс дополнительный текст, по условиям сортировки от А до Я наименования товаров будут идти сразу после наименования категории, поэтому когда в таблице не обнаруживается совпадений с ключом подтягивается первое значение сверху.

В принципе, данный подход устраивал бы, если для товаров, для которых отсутствует категория, не подтягивалось расположение. Обратите внимание на товар «Лук Подмосковье». Для него определено расположение «Стелаж №2», хотя в первой таблице нет категории «Лук». Это происходит все по тем же причинам, когда функцией не обнаруживается равенств, то она определяет для значения значение меньшего самого близкого по сортировке ключа, а для «Лук Подмосковье» это категория «Картофель».

Он подобного эффекта можно избавиться путем определения категории из наименования товара используя текстовые функции ЛЕВСИМВ(C11;ПОИСК(» «;C11)-1), которые вернут все символы до первого пробела, а также изменить интервальный просмотр на точный.

Помимо всего описанного, функция ВПР позволяет применять для текстовых значений подстановочные символы – * (звездочка – любое количество любых символов) и ? (один любой символ). Например, для искомого значения «*» & «иван» & «*» могут подойти строки Иван, Иванов, диван и т.д.

Также данная функция может искать значения в массивах – =ВПР(1;{2;»Два»:1;»Один»};2;ЛОЖЬ) – результат выполнения строка «Два».

Функция ГПР

Горизонтальное первое равенство. Ищет совпадение по ключу в первой строке определенного диапазона и возвращает значение из указанной строки этого диапазона в совпавшем с ключом столбце.

Синтаксис: =ГПР(ключ; диапазон; номер_строки; [интервальный_просмотр]).

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

Функция СТРОКА

Определяет и возвращает номер строки указанной ссылкой ячейки.

Синтаксис: =СТРОКА([ссылка]), где аргумент «ссылка» не является обязательным. Если он опущен, но возвращается номер текущей строки.

Пример использования:

=СТРОКА(D4) – результат 4.
=СТРОКА() – функция вернет номер строки, в которой она расположена.

Функция СТОЛБЕЦ

Возвращает номер столбца ячейки, указанной ссылкой.

Синтаксис: =СТОЛБЕЦ([ссылка]), где «ссылка» не обязательный аргумент. По умолчанию возвращается номер текущего столбца.

Пример использования:

=СТОЛБЕЦ(C4) – формула вернет значение 3.
=СТОЛБЕЦ() – функция возвращает номер столбца, в котором расположена.

Функция АДРЕС

Возвращает текст, представляющий адрес ячейки, заданной номерами строки и столбца.

Синтаксис: =АДРЕС(строка; столбец; [тип_закрепления]; [стиль_ссылки]; [имя_листа]), где:

Строка – обязательный аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
- 1 – значение по умолчанию, когда закреплены все индексы;
- 2 – закрепление индекса строки;
- 3 – закрепление индекса столбца;
- 4 – адрес без закреплений.
стиль_ссылки – необязательный аргумент. Логическое значение:
- ИСТИНА – формат ссылок «A1»;
- ЛОЖЬ – формат ссылок «R1C1».
имя_листа – необязательный аргумент. Строка, представляющая имя листа, который необходимо добавить к тексту адреса ячейки.

Пример использования:

=АДРЕС

(1;1) – возвращает $A$1.
=АДРЕС(1;1;4) – возвращает A1.
=АДРЕС(1;1;4;ЛОЖЬ) – результат R[1]C[1].
=АДРЕС(1;1;4;ЛОЖЬ;»Лист1″) – результат выполнения функции Лист1!R[1]C[1].

Функция ДВССЫЛ

Преобразует адрес ссылки, заданный текстовой строкой, в ссылку на данный адрес.

Синтаксис: =ДВССЫЛ(адрес_ссылки; [стиль_ссылки]), где

адрес_ссылки – обязательный аргумент. Строка, представляющая адрес ссылки на ячейку или диапазон. Например, «C3», «R3C3» или «D8:D9».
стиль_ссылки – необязательный аргумент. Логическое значение, определяющее стиль ссылки:
- ИСТИНА – стиль A1. Является значением по умолчанию;
- ЛОЖЬ – стиль R1C1.

Пример использования:

=ДВССЫЛ(«a3») – возвращает ссылк

Знакомство с формулами массива в Excel

Формула массива – это один из наиболее мощных инструментов Excel, но также и один из самых сложных. Массивом в Excel называют набор элементов, который может обрабатываться как одно целое, так и как набор, т.е. каждый элемент по отдельности. В этом уроке мы познакомимся с базовыми сведениями о массивах.

Одномерные и двухмерные массивы

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

…или в одном столбце (вертикальный массив).

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

В отличие от языка программирования VBA, Excel не поддерживает трехмерные массивы.

Размерность массива

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

Примеры приведены для русифицированной версии Excel.

Элементы в одномерных горизонтальных массивах разделяются точкой с запятой (;). Например, на рисунке ниже представлен одномерный горизонтальный массив, который состоит из 5 элементов:Если ввести его на рабочий лист Excel, получим следующий результат:

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

Элементы в одномерном вертикальном массиве разделяются двоеточием (:). Например, на рисунке ниже представлен одномерный вертикальный массив, который состоит из 4 элементов.Если ввести его на рабочий лист, получим следующий результат:
В двухмерных массивах для разделения элементов в строке используется точка с запятой, а для разделения строк – двоеточие. На рисунке ниже представлен массив, который состоит из 3 строк и 5 столбцов.Если ввести данный массив в Excel, результат будет таким:

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

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

В качестве заполнителя отсутствующего элемента в массиве можно использовать значение #Н/Д (нет данных). К примеру, следующая запись будет синтаксически верной:

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

Одноячеечные и многоячеечные формулы массива

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

Во втором случае формула вводится в одну ячейку и возвращает только одно значение:

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

Оцените качество статьи. Нам важно ваше мнение:

Редактирование формул массива в Excel

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

Правила редактирования формул массива

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

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

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

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

Выделение массива в Excel

Если необходимо изменить формулу массива, то первое, что нужно сделать – это выделить диапазон, в котором содержится массив. В Excel существует, как минимум, 3 способа сделать это:

Выделить диапазон массива вручную, т.е. с помощью мыши. Это самый простой, но в ряде случаев абсолютно непригодный способ.
С помощью диалогового окна Выделить группу ячеек. Для этого выделите любую ячейку, которая принадлежит массиву:А затем на вкладке Главная из раскрывающегося списка Найти и выделить выберите пункт Выделить группу ячеек.
Откроется диалоговое окно Выделить группу ячеек. Установите переключатель на пункт текущий массив и нажмите ОК.
Текущий массив будет выделен:
При помощи комбинации клавиш Ctrl+/. Для этого выберите любую ячейку массива и нажмите комбинацию.

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

Самое простое, что Вы можете сделать с массивом в Excel – это удалить его. Для этого достаточно выделить нужный массив и нажать клавишу Delete.

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

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

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

Выделите диапазон массива любым из известных Вам способов. В нашем случае это диапазон C1:C12.
Перейдите в режим редактирования формулы, для этого щелкните по строке формул или нажмите клавишу F2. Excel удалит фигурные скобки вокруг формулы массива.
Внесите необходимые корректировки в формулу:
А затем нажмите комбинацию клавиш Ctrl+Shift+Enter, чтобы сохранить изменения. Формула будет отредактирована.

Изменение размеров формулы массива

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

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

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

Существует несколько подходов к редактированию размеров массива, которые, возможно, Вам пригодятся. Подходы приведены в данном уроке.

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

Оцените качество статьи.2) . Для сравнения: чтобы найти сумму квадратов, используя обычные формулы, нам потребуется дополнительный столбец для вычисления квадратов значений и одна ячейка для их суммирования (см. файл примера или диапазон B 2 :B13 на рисунке ниже).

В отличие от ввода обычных формул, после ввода формулы массива нужно нажать вместо ENTER комбинацию клавиш CTRL+SHIFT+ENTER (поэтому, иногда, формулы массива также называются формулами CSE — это первые буквы от названия клавиш, используемых для ввода C trl, S hift, E nter). После этого формула будет обрамлена в фигурные скобки { } (их не вводят с клавиатуры, они автоматически появляются после нажатия CTRL+SHIFT+ENTER ). Это обрамление показано на рисунке выше (см. Строку формул ).

Если бы мы нажали просто ENTER , то получили бы сообщение об ошибке #ЗНАЧ!, возникающую при использовании неверного типа аргумента функции, т.к. функция СУММ() принимает в качестве аргумента только диапазон ячеек (или формулу, результатом вычисления которой является диапазон, или константы). В нашем случае мы в качестве аргумента ввели не диапазон, а некое выражение, которое еще нужно вычислить перед суммированием, поэтому и получили ошибку.

Чтобы глубже понять формулы массива проведем эксперимент:

выделим ячейку B13 , содержащую обычную формулу =СУММ($B$2:$B$12) ;
в C троке формул выделим аргумент функции СУММ() , т.е. $B$2:$B$12 ;
нажмем клавишу F9 , т.е. вычислим, выделенную часть формулы;
получим {1:4:9:16:25:36:49:64:81:100:121} – массив квадратов значений из столбца В .2 ;
нажмем клавишу F9 , т.е. вычислим, выделенную часть формулы;
получим {1:4:9:16:25:36:49:64:81:100:121} – тот же массив, что и в первом случае.

Т.е. нажатие CTRL+SHIFT+ENTER заставило EXCEL перед суммированием произвести промежуточные вычисления с диапазоном ячеек (с массивом содержащихся в нем значений). Для самой функции СУММ() ничего не изменилось – она получила тот же массив, только предварительно вычисленный, а не прямо из диапазона ячеек, как в случае с обычной формулой. Понятно, что вместо функции СУММ() в формуле массива может быть использована любая другая функция MS EXCEL: СРЗНАЧ() , МАКС() , НАИБОЛЬШИЙ() и т.п.

Вышеприведенный пример иллюстрирует использование функции массива возвращающей единственное значение, т.е. результат может быть выведен в одной ячейке. Это достигается использованием функций способных «свернуть» вычисленный массив до одного значения ( СУММ() , СРЗНАЧ() , МАКС() ).2) присвоить имя Сумма_квадратов, а затем в ячейке указать =Сумма_квадратов , то получим правильный результат.

Массив последовательных чисел в EXCEL. Примеры и описание

Для построения формул массива иногда используют числовую последовательность, например {1:2:3:4:5:6:7}, вводимую непосредственно в формулу. Эту последовательность можно сформировать вручную, введя константу массива , или с использованием функций, например СТРОКА() . Также с помощью формул можно динамически изменять длину числовой последовательности.

Создание массива последовательных чисел

Сформируем массив последовательных чисел для решения конкретного примера: подсчитаем сумму 3-х наибольших значений .

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

Сумму 3-х наибольших значений можно подсчитать с помощью формулы =СУММПРОИЗВ(НАИБОЛЬШИЙ(A2:A15;{1:2:3}))

Чтобы не вводить массив {1:2:3} вручную, можно использовать следующий подход. Введите в диапазон ячеек B1:B3 значения 1, 2 и 3 соответственно. Выделив любую пустую ячейку, в Строке формул введите =B1:B3, затем выделите B1:B3 и нажмите клавишу F9 . Формула B1:B3 будет заменена значениями, содержащимися в этих ячейках, т.е. {1:2:3} . Теперь достаточно скопировать этот массив в Буфер обмена ( CTRL+C ) и вставить его в нужную формулу.

Другим подходом для формирования числовой последовательности является использование формулы СТРОКА(A1:A3)

В итоге получим =СУММПРОИЗВ(НАИБОЛЬШИЙ(A2:A15;СТРОКА(A1:A3)))

Но у этого подхода есть два недостатка:

при вставке новых строк на лист перед строками 1-3, формула автоматически модифицируется в СТРОКА(A2:A4) или СТРОКА(A1:A4) и соответственно формируется неправильный массив {2:3:4} или {1:2:3:4} . Исправить это может формула СТРОКА(ДВССЫЛ(«A1:A3»))
формула возвращает статичный массив.

Массив последовательных чисел переменной длины можно сформировать с помощью формулы = СТРОКА(ДВССЫЛ(«A1:A»&G2)) , где в ячейке G2 пользователем задается последний элемент последовательности (т.е. сумму скольких наибольших требуется найти). В итоге получим =СУММПРОИЗВ(НАИБОЛЬШИЙ(Список;СТРОКА(ДВССЫЛ(«A1:A»&G2))))

Динамический массив последовательных чисел

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

Создадим Динамический диапазон Список ( =СМЕЩ(Лист1!$A$2;;;СЧЁТ(Лист1!$A$2:$A$15)) ), который будет содержать все значения исходного списка и автоматически расширяться при добавлении новых значений в столбец А . Введем 9 значений (см. рисунок выше).

Массив последовательных чисел будем формировать с помощью следующей формулы: СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))

Здесь функция ЧСТРОК() возвращает количество значений в исходном списке (9). После выполнения функции СТРОКА() получим массив последовательных чисел {1:2:3:4:5:6:7:8:9} , причем этот массив будет динамическим: в зависимости от длины диапазона Список будет формироваться массив последовательных чисел соответствующего размера.

Функции VBA для работы с массивами

Функции VBA для работы с массивами | Excel для всех Главная » Функции VBA » 28 Апрель 2011 Дмитрий 117733 просмотров
Array() — позволяет автоматически создать массив нужного размера и типа и сразу загрузить в него переданные значения:
'инициализируем переменную с типом Variant Dim avArr 'присваиваем переменной значение массива avArr = Array("Первый элемент", "Второй элемент", "3", 4, "Последний") 'показываем 3-ий по порядку элемент MsgBox avArr(2)
‘инициализируем переменную с типом Variant Dim avArr ‘присваиваем переменной значение массива avArr = Array(«Первый элемент», «Второй элемент», «3», 4, «Последний») ‘показываем 3-ий по порядку элемент MsgBox avArr(2)
Опечатки нет. avArr(2) действительно выдаст третий элемент, т.к. по умолчанию для массива нижняя граница равна нулю. И да, таким образом можно создать исключительно одномерный массив.
Filter() — позволяет на основе одного массива получить другой, отфильтровав в исходном массиве нужные нам элементы.
LBound() — возвращает информацию о нижней границе массива (то есть номере первого имеющегося в нем значения)
Dim avArr avArr = Array("Первый элемент", "Второй элемент", "3", 4, "Последний") 'показываем первый элемент MsgBox avArr(LBound(avArr))
Dim avArr avArr = Array(«Первый элемент», «Второй элемент», «3», 4, «Последний») ‘показываем первый элемент MsgBox avArr(LBound(avArr))
UBound() — возвращает информацию о верхней границе массива (номер последнего имеющегося значения)
Dim avArr avArr = Array("Первый элемент", "Второй элемент", "3", 4, "Последний") 'показываем последний элемент MsgBox avArr(UBound(avArr))
Dim avArr avArr = Array(«Первый элемент», «Второй элемент», «3», 4, «Последний») ‘показываем последний элемент MsgBox avArr(UBound(avArr))
Join() — возможность слить множество строк из массива строк в одну строковую переменную. В качестве разделителя по умолчанию используется пробел, можно указать свой разделитель.
Dim avArr avArr = Array("Первый элемент", "Второй элемент", "3", 4, "Последний") 'объединяем все элементы массива с разделителем "-" MsgBox Join(avArr, "-")
Dim avArr avArr = Array(«Первый элемент», «Второй элемент», «3», 4, «Последний») ‘объединяем все элементы массива с разделителем «-« MsgBox Join(avArr, «-«)
Split() — обратная функция, разбивающая строку на массив строк . В качестве разделителя по умолчанию используется пробел, можно указать свой разделитель.
'инициализируем переменную с типом Variant 'т.к. затем это будет массив Dim sStr 'разбиваем указанный текст массив. Разделитель - "-" sStr = Split("Первый элемент-Второй элемент-3-4-Последний", "-") 'показываем 3-ий по порядку элемент MsgBox sStr(2)
‘инициализируем переменную с типом Variant ‘т.к. затем это будет массив Dim sStr ‘разбиваем указанный текст массив. Разделитель — «-« sStr = Split(«Первый элемент-Второй элемент-3-4-Последний», «-«) ‘показываем 3-ий по порядку элемент MsgBox sStr(2)
Статья помогла? Сделай твит, поделись ссылкой с друзьями!
Юридическая информация
Так же с этой статьей читают:
Наши партнеры

Спасибо за сообщение
Ваше сообщение было получено и отправлено администратору.
Динамические массивы, функции и формулы Excel
Благодаря революционному обновлению механизма вычислений Excel 365 формулы массива стали очень простыми и понятными для всех, а не только для суперпользователей. В руководстве объясняется концепция новых динамических массивов Excel и показано, как они могут сделать ваши рабочие листы более эффективными и более простыми в настройке.
Формулы массива
Excel всегда считались прерогативой гуру и экспертов по формулам.Если кто-то скажет вам: «Это можно сделать с помощью формулы массива», многие пользователи сразу же ответят: «А нет ли другого пути?».
Введение динамических массивов — долгожданное и очень долгожданное изменение. Благодаря их способности работать с несколькими значениями простым способом, без каких-либо уловок и причуд, формулы динамических массивов — это то, что каждый пользователь Excel может понять и создать с удовольствием.
Динамические массивы Excel
Динамические массивы — это массивы с изменяемым размером, которые автоматически вычисляют и возвращают значения в несколько ячеек на основе формулы, введенной в одну ячейку.
За более чем 30-летнюю историю Microsoft Excel претерпел множество изменений, но одно осталось неизменным — одна формула, одна ячейка. Даже при использовании традиционных формул массива приходилось вводить формулу в каждую ячейку, в которой должен отображаться результат. В случае динамических массивов это правило больше не действует. Теперь любая формула, возвращающая массив значений, автоматически переносится на соседние ячейки, без необходимости нажимать Ctrl + Shift + Enter или выполнять какие-либо другие действия. Другими словами, управлять динамическими массивами становится так же просто, как работать с одной ячейкой.
Позвольте мне проиллюстрировать эту концепцию на очень простом примере. Предположим, вам нужно умножить две группы чисел, например, чтобы вычислить разные проценты.
В предварительных динамических версиях Excel приведенная ниже формула будет работать только для первой ячейки, если вы не введете ее в несколько ячеек и не нажмете Ctrl + Shift + Enter, чтобы явно сделать ее формулой массива:
= A3: A5 * B2: D2
Теперь посмотрим, что происходит, когда та же формула используется в Excel 365.Вы вводите его всего в одну ячейку (в нашем случае — B3), нажимаете клавишу Enter… и сразу вся ярость заполняется результатами:
Заполнение нескольких ячеек одной формулой называется разливом , а заполненный диапазон ячеек называется диапазоном разлива.
Важно отметить, что недавнее обновление — это не просто новый способ обработки массивов в Excel. Фактически, это революционное изменение всей вычислительной машины. Благодаря динамическим массивам в библиотеку функций Excel было добавлено множество новых функций, а существующие стали работать быстрее и эффективнее.Со временем предполагается, что новые динамические массивы полностью заменят устаревшие формулы массива, вводимые с помощью сочетания клавиш Ctrl + Shift + Enter.
Наличие динамических массивов Excel
Динамические массивы были представлены на конференции Microsoft Ignite Conference в 2018 году, выпущены для подписчиков Office 365 в январе 2020 года и доступны только с подписками на Microsoft 365 .
Динамические массивы поддерживаются в последних версиях:
Excel 365 для Windows
Excel 365 для Mac
Excel 365 для Apple
Excel 365 для Android
Excel 365 для Windows Mobile
Обратите внимание, что Excel Online не поддерживает динамические массивы.
Функции динамического массива Excel
В рамках новой функциональности в Excel 365 были введены 6 новых функций, которые изначально обрабатывают массивы и выводят данные в диапазон ячеек. Вывод всегда динамический — когда в исходных данных происходит какое-либо изменение, результаты обновляются автоматически. Отсюда и название группы — функций динамического массива .
Эти новые функции легко справляются с рядом задач, которые традиционно считаются твердыми орешками. Например, они могут удалять дубликаты, извлекать и подсчитывать уникальные значения, отфильтровывать пробелы, генерировать случайные целые и десятичные числа, сортировать в порядке возрастания или убывания и многое другое.
Ниже вы найдете краткое описание того, что делает каждая функция, а также ссылки на подробные руководства:
UNIQUE — извлекает уникальные предметы из диапазона ячеек.
ФИЛЬТР — фильтрует данные в соответствии с заданными вами критериями.
SORT — сортирует диапазон ячеек по указанному столбцу.
SORTBY — сортирует диапазон ячеек по другому диапазону или массиву.
RANDARRAY — генерирует массив случайных чисел.
SEQUENCE — формирует список последовательных номеров.
Кроме того, есть две современные замены популярных функций Excel, которые официально не входят в группу, но используют все преимущества динамических массивов:
XLOOKUP — это более мощный преемник VLOOKUP, HLOOKUP и LOOKUP, который может выполнять поиск как в столбцах, так и в строках и возвращать несколько значений.
XMATCH — это более универсальный преемник функции MATCH, которая может выполнять вертикальный и горизонтальный поиск и возвращать относительное положение указанного элемента.
Формулы динамического массива Excel
В последних версиях Excel 365 поведение динамического массива глубоко интегрировано и становится встроенным для всех функций , даже тех, которые изначально не были предназначены для работы с массивами. Проще говоря, для любой формулы, которая возвращает более одного значения, Excel автоматически создает диапазон изменяемого размера, в который выводятся результаты. Благодаря этой способности существующие функции теперь могут творить магию!
Приведенные ниже примеры демонстрируют новые формулы динамических массивов в действии, а также влияние динамических массивов на существующие функции.
Пример 1. Новая функция динамического массива
Этот пример демонстрирует, насколько быстрее и проще решение может быть достигнуто с помощью функций динамического массива Excel.
Для извлечения списка уникальных значений из столбца обычно используется сложная формула CSE, подобная этой. В Excel 365 все, что вам нужно, это УНИКАЛЬНАЯ формула в ее базовой форме:
.
= УНИКАЛЬНЫЙ (B2: B10)
Вы вводите формулу в любую пустую ячейку и нажимаете Enter. Excel немедленно извлекает все различные значения в списке и выводит их в диапазон ячеек, начиная с ячейки, в которую вы ввели формулу (в нашем случае D2).При изменении исходных данных результаты автоматически пересчитываются и обновляются.
Пример 2. Объединение нескольких функций динамического массива в одной формуле
Если нет возможности выполнить задачу с помощью одной функции, соедините несколько функций вместе! Например, чтобы отфильтровать данные по условию и расположить результаты в алфавитном порядке, оберните функцию СОРТИРОВАТЬ вокруг ФИЛЬТРА следующим образом:
= СОРТИРОВАТЬ (ФИЛЬТР (A2: C13, B2: B13 = F1, «Нет результатов»))
Где A2: C13 — исходные данные, B2: B13 — значения для проверки, а F1 — критерий.
Пример 3. Использование новых функций динамического массива вместе с существующими
Поскольку новый механизм вычислений, реализованный в Excel 365, может легко преобразовывать обычные формулы в массивы, нет ничего, что могло бы помешать вам комбинировать новые и старые функции вместе.
Например, чтобы подсчитать, сколько уникальных значений находится в определенном диапазоне, вложите функцию UNIQUE динамического массива в старый добрый COUNTA:
= СЧЁТ (УНИКАЛЬНЫЙ (B2: B10))
Пример 4.Существующие функции поддерживают динамические массивы
Если вы предоставите диапазон ячеек функции TRIM в более старой версии, такой как Excel 2016 или Excel 2019, она вернет один результат для первой ячейки:
= ОБРЕЗАТЬ (A2: A6)
В динамическом Excel одна и та же формула обрабатывает все ячейки и возвращает несколько результатов, как показано ниже:
Пример 5. Формула ВПР для возврата нескольких значений
Как всем известно, функция VLOOKUP предназначена для возврата одного значения на основе указанного вами индекса столбца.Однако в Excel 365 вы можете предоставить массив номеров столбцов для возврата совпадений из нескольких столбцов:
= ВПР (F1, A2: C6, {1,2,3}, ЛОЖЬ)
Пример 6. Легкая формула TRANSPOSE
В более ранних версиях Excel синтаксис функции TRANSPOSE не допускал ошибок. Чтобы повернуть данные на листе, вам нужно было подсчитать исходные столбцы и строки, выбрать такое же количество пустых ячеек, но изменить ориентацию (ошеломляющая операция на огромных листах!), Ввести формулу ТРАНСПОРТИРОВКИ в выбранном диапазоне и нажмите Ctrl + Shift + Enter, чтобы завершить его правильно.Фух!
В динамическом Excel вы просто вводите формулу в крайнюю левую ячейку диапазона вывода и нажимаете Enter:
= ТРАНСПОРТ (A1: B10)
Готово!
Диапазон разлива — одна формула, несколько ячеек
Диапазон разлива — это диапазон ячеек, содержащий результаты, возвращаемые формулой динамического массива.
При выборе любой ячейки в диапазоне разлива появляется синяя граница, чтобы показать, что все внутри нее рассчитывается по формуле в верхней левой ячейке.Если вы удалите формулу в первой ячейке, все результаты исчезнут.
Диапазон разлива — действительно замечательная вещь, которая значительно облегчает жизнь пользователям Excel. Раньше, с формулами массива CSE, нам приходилось угадывать, в сколько ячеек их копировать. Теперь вы просто вводите формулу в первую ячейку, а Excel позаботится обо всем остальном.
Хотя поведение диапазона разлива является полностью динамическим (при изменении исходных данных диапазон разлива соответственно расширяется или сужается), оно не обновляется, когда новые записи добавляются за пределы указанного диапазона.Например, наша формула СОРТИРОВКИ обработала данные в формате A2: A10. Если в A11 вводится новое значение, оно не будет включено в результаты, если вы не измените ссылку на диапазон в формуле. Если вы хотите, чтобы такие изменения отражались на лету, преобразуйте исходный диапазон в таблицу Excel и используйте структурированные ссылки в своих формулах. В отличие от диапазонов, таблицы Excel автоматически расширяются и включают новые добавленные строки. Такого же эффекта можно добиться с помощью динамического именованного диапазона.
Примечание. Если некоторые другие данные блокируют диапазон разлива, возникает ошибка #SPILL.Как только мешающие данные будут удалены, ошибка исчезнет.
Ссылка на диапазон разлива (символ #)
Для обозначения диапазона разлива поместите хэш-тег или символ решетки (#) после адреса верхней левой ячейки диапазона.
Например, чтобы сослаться на результаты формулы динамического массива в C2, введите:
= C2 #
Чтобы сослаться на тот же диапазон утечек с другого листа, включите название листа в ссылку:
= Лист1! C2 #
Это то же самое, что ссылка на весь диапазон (= C2: C8), но в отличие от обычной ссылки, ссылка на разлив автоматически отражает изменения в размере диапазона.Это поведение особенно полезно, когда вы «скармливаете» диапазон разлива другой функции, динамической или обычной. Вы даже можете использовать его для именованных диапазонов и проверки данных.
Например, чтобы узнать, сколько случайных чисел сгенерировано формулой СЛУЧАЙНОГО СЛУЧАЯ в C2, укажите ссылку на диапазон разлива в функцию СЧЁТ:
= СЧЕТ (C2 #)
Чтобы сложить значения в диапазоне разлива, используйте:
= СУММ (C2 #)
Наконечник. Чтобы быстро перейти к диапазону разлива, просто выделите все ячейки внутри синего поля с помощью мыши. Excel автоматически создаст для вас справку о разливе.
Неявное пересечение и символ @
В динамическом массиве Excel есть еще одно существенное изменение в языке формул — введение символа @, известного как оператор неявного пересечения .
В Microsoft Excel неявное пересечение — это поведение формулы, которое сводит множество значений к одному значению.В старом Excel ячейка могла содержать только одно значение, так что это было поведение по умолчанию, и для него не требовался специальный оператор.
В Excel 365 все формулы по умолчанию рассматриваются как формулы массива. Оператор неявного пересечения используется для предотвращения поведения массива, если оно не требуется в определенной формуле. Другими словами, если вы хотите, чтобы формула возвращала только одно значение, поставьте @ перед именем функции, и она будет вести себя как формула без массива в традиционном Excel.
Чтобы увидеть, как это работает на практике, взгляните на снимок экрана ниже.
В C2 есть формула динамического массива, которая выводит результаты во многие ячейки:
= УНИКАЛЬНЫЙ (A2: A9)
В E2 функция имеет префикс @, который вызывает неявное пересечение. В результате возвращается только первое уникальное значение:
= @ УНИКАЛЬНЫЙ (A2: A9)
При открытии листа, созданного в более старой версии Excel, вы можете заметить, что символ @ автоматически добавляется к формулам, которые потенциально могут возвращать несколько значений.Это сделано для того, чтобы формула вела себя так же, как и в исходной версии Excel. В зависимости от формулы удаление @ либо приведет к разложению результатов на несколько ячеек, либо вообще не даст заметного эффекта.
Примечание. Первоначально Microsoft представила функцию SINGLE для той же цели, но позже заменила ее оператором @.
Преимущества динамических массивов Excel
Несомненно, динамические массивы — одно из лучших усовершенствований Excel за последние годы.Как и у любой новой функции, у нее есть свои сильные и слабые стороны. К счастью для нас, сильные стороны новых формул динамического массива Excel ошеломляют!
Простой и мощный
Динамические массивы позволяют гораздо проще создавать более мощные формулы. Вот пара примеров:
Родной для всех формул
В динамическом Excel вам не нужно беспокоиться о том, какие функции поддерживают массивы, а какие нет. Если формула может возвращать несколько значений, она будет делать это по умолчанию.Это также относится к арифметическим операциям и устаревшим функциям, как показано в этом примере.
Вложенные функции динамического массива
Для разработки решений для более сложных задач вы можете комбинировать новые функции динамического массива Excel или использовать их вместе со старыми, как показано здесь и здесь.
Относительные и абсолютные ссылки менее важны
Благодаря подходу «одна формула, много значений» нет необходимости блокировать диапазоны с помощью знака $, поскольку технически формула находится всего в одной ячейке.Таким образом, по большей части не имеет значения, использовать ли абсолютные, относительные или смешанные ссылки на ячейки (что всегда было источником путаницы для неопытных пользователей) — формула динамического массива в любом случае даст правильные результаты!
Ограничения динамических массивов
Новые динамические массивы — это прекрасно, но, как и в случае с любой новой функцией, есть несколько предостережений и соображений, о которых вам следует знать.
Результаты не могут быть отсортированы обычным способом
Диапазон разлива, возвращаемый формулой динамического массива, нельзя отсортировать с помощью функции сортировки Excel.Любая такая попытка приведет к ошибке « Вы не можете изменить часть массива ». Чтобы упорядочить результаты от наименьшего к наибольшему или наоборот, заключите текущую формулу в функцию СОРТИРОВАТЬ. Например, так вы можете фильтровать и сортировать одновременно.
Невозможно удалить какое-либо значение в диапазоне разлива
Ни одно из значений в диапазоне разлива не может быть удалено по той же причине: вы не можете изменить часть массива. Такое поведение ожидаемо и логично. Традиционные формулы массива CSE также работают таким же образом.
Результаты нельзя занести в таблицу
И эта особенность (или баг?) Довольно неожиданная. Если вы попытаетесь преобразовать диапазон разлива в таблицу Excel, Excel это сделает. Но вместо результатов вы увидите только # РАЗЛИВ! ошибка. Надеюсь, они исправят это в одной из следующих сборок.
Не работают с Excel Power Query
Результаты формул динамического массива не могут быть загружены в Power Query. Скажем, если вы попытаетесь объединить два или более диапазонов разлива вместе с помощью Power Query, это не сработает.
Динамические массивы в сравнении с традиционными формулами массива CSE
С введением динамических массивов мы можем говорить о двух типах Excel:
Dynamic Excel , который полностью поддерживает динамические массивы, функции и формулы. В настоящее время это только Excel 365.
Устаревшая версия Excel , также известная как традиционный или предварительный динамический Excel, где поддерживаются только формулы массива Ctrl + Shift + Enter. Это Excel 2019, Excel 2016, Excel 2013 и более ранние версии.
Само собой разумеется, что динамические массивы превосходят формулы массивов CSE во всех отношениях. Хотя традиционные формулы массивов сохранены в Excel 365 по соображениям совместимости, с этого момента рекомендуется использовать новые.
Вот самые существенные отличия:
Формула динамического массива вводится в одну ячейку и завершается обычным нажатием клавиши Enter. Чтобы заполнить устаревшую формулу массива, вам нужно нажать Ctrl + Shift + Enter.
Новые формулы массива автоматически переносятся во многие ячейки. Чтобы получить несколько результатов, формулы CSE необходимо скопировать в диапазон ячеек.
Выходные данные формул динамического массива автоматически изменяют размер при изменении данных в исходном диапазоне. Формулы CSE обрезают вывод, если возвращаемая область слишком мала, и возвращают ошибки в дополнительных ячейках, если возвращаемая область слишком велика.
Формулу динамического массива можно легко редактировать в одной ячейке. Чтобы изменить формулу CSE, вам необходимо выбрать и отредактировать весь диапазон.
Невозможно удалять и вставлять строки в диапазоне формул CSE — сначала необходимо удалить все существующие формулы. С динамическими массивами вставка или удаление строк не проблема.
Обратная совместимость: динамические массивы в устаревшей версии Excel
Когда вы открываете книгу, содержащую формулу динамического массива в старой версии Excel, она автоматически преобразуется в обычную формулу массива, заключенную в {фигурные скобки}. Когда вы снова откроете лист в Excel 365, фигурные скобки будут удалены.
В устаревшей версии Excel к новым функциям динамического массива и ссылкам на диапазоны переноса добавляется префикс _xlfn, указывающий на то, что эта функция не поддерживается. Знак ссылки диапазона разлива (#) заменяется функцией ANCHORARRAY.
Например, вот как УНИКАЛЬНАЯ формула отображается в Excel 2013 :
Большинство формул динамических массивов (но не все!) Будут продолжать отображать свои результаты в устаревшей версии Excel, пока вы не внесете в них какие-либо изменения. При редактировании формулы она сразу же прерывается и отображается одно или несколько #NAME? значения ошибок.
Формулы динамического массива Excel не работают
В зависимости от функции могут возникать разные ошибки, если вы используете неправильный синтаксис или недопустимые аргументы. Ниже приведены 3 наиболее распространенные ошибки, с которыми вы можете столкнуться при использовании любой формулы динамического массива.
# РАЗЛИВ! ошибка
Когда динамический массив возвращает несколько результатов, но что-то блокирует диапазон утечки, #SPILL! возникает ошибка.
Чтобы исправить ошибку, вам просто нужно очистить или удалить все ячейки в диапазоне разлива, которые не являются полностью пустыми.Чтобы быстро обнаружить все ячейки, которые мешают, щелкните индикатор ошибки, а затем щелкните Выберите препятствующие ячейки .
#REF! ошибка
Из-за ограниченной поддержки внешних ссылок между книгами динамические массивы требуют, чтобы оба файла были открыты. Если исходная книга закрыта, #REF! отображается ошибка.
# ИМЯ? ошибка
A #NAME? ошибка возникает, если вы пытаетесь использовать функцию динамического массива в более старой версии Excel.Помните, что новые функции доступны только в Excel 365.
Если эта ошибка появляется в Excel 365, дважды проверьте имя функции в проблемной ячейке. Скорее всего, это опечатка 🙂
Вот как использовать динамические массивы в Excel. Надеюсь, вам понравится эта фантастическая новая функциональность! В любом случае, я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Вас также может заинтересовать
Примеры формул массива Excel для начинающих и опытных пользователей
Формулы массива — одна из самых запутанных функций Excel, но при этом одна из самых интригующих и захватывающих.Освоение формул массива Excel — долгий путь, и на прошлой неделе мы сделали первые шаги, изучив основы функций массива и формул в Excel.
Однако, как и в случае с любым другим навыком, путь к мастерству вымощен практикой. Итак, сегодня мы сосредоточимся на расширенных примерах формул массива Excel и постараемся сделать их содержательными и интересными.
Пример 1. Подсчет ячеек, удовлетворяющих определенным условиям
Введение функций СУММЕСЛИ и СЧЁТЕСЛИ, а также их множественных аналогов в Excel 2007 сделало использование формул массива излишним во многих сценариях.Однако есть задачи, в которых формула массива является наиболее эффективным, если не единственным решением.
Предположим, у вас есть 2 столбца чисел: столбец A (запланированный) и столбец B (фактический). И вы хотите подсчитать, сколько раз столбец B больше или равен столбцу A, если значение в столбце B больше 0.
Эта задача требует сравнения двух диапазонов, и именно для этого предназначены формулы массивов Excel.
Итак, вы выражаете условия как (B2: B10> = A2: A10) и (B2: B10> 0), соединяете их с помощью звездочки (*), которая действует как оператор AND в формулах массива, и включаете это выражение в аргумент функции СУММ:
= СУММ ((B2: B10> = A2: A10) * (B2: B10> 0))
Не забудьте нажать Ctrl + Shift + Enter, чтобы правильно ввести формулу массива Excel.
Следующая пара абзацев предназначена для тех, кто любит заглядывать под капот. Если вас не интересуют технические подробности, вы можете сразу перейти к следующему примеру формулы.
Для лучшего понимания этой формулы в частности и формул массивов Excel в целом, давайте выберем два выражения в скобках функции СУММ в строке формул и нажмем F9, чтобы просмотреть массивы за частями формулы. Если вам нужна дополнительная информация о том, как работает клавиша F9, ознакомьтесь со статьей Оценка частей формулы массива с помощью F9.
Итак, у нас есть два массива логических значений, где TRUE равно 1, а FALSE равно 0. Поскольку мы используем в формуле оператор массива AND (*), SUM будет складывать только те строки, которые имеют TRUE (1) в обоих массивах, как показано на скриншоте ниже:
Примечание. Не все функции Excel, поддерживающие массивы, могут преобразовывать ИСТИНА и ЛОЖЬ в 1 и 0. В более сложных формулах массивов может потребоваться использование двойного тире (-), которое технически называется двойным унарным оператором, для преобразования не- числовые логические значения в числа.
Включение двойного тире в приведенную выше формулу тоже не повредит, это просто убережет вас: = СУММ (- (B2: B10> = A2: A10) * (B2: B10> 0 ))
А вот более сложный пример формулы массива Excel, который абсолютно требует использования двойного унарного оператора.
Пример 2. Использование нескольких функций в формулах массива Excel
Формулы массива могут работать с несколькими функциями Excel одновременно и выполнять несколько вычислений в рамках одной формулы.
Например, если у вас есть таблица, в которой перечислены продажи нескольких продуктов несколькими продавцами, и вы хотите узнать максимальную сумму продаж, сделанных данным лицом для данного продукта, вы можете написать формулу массива на основе следующего шаблона:
= МАКС (ЕСЛИ (( диапазон_продаж = « имя «) * ( диапазон_продуктов = « имя «), диапазон_продаж , «»))
Предполагая, что имена продавцов находятся в столбце A, названия продуктов — в столбце B, а продажи — в столбце C, следующая формула возвращает самую большую продажу , которую Майк сделал для яблок :
= МАКС (ЕСЛИ (($ A $ 2: $ A $ 9 = "микрофон") * ($ B $ 2: $ B $ 9 = "яблоки"), $ C $ 2: $ C $ 9, ""))
Естественно, вы можете заменить имена в формуле ссылками на ячейки, чтобы пользователи могли просто вводить имена в определенные ячейки, не изменяя формулу массива:
На скриншоте выше используются следующие формулы массива Excel (не забудьте нажать Ctrl + Shift + Enter, чтобы ввести их правильно):
Максимум: = MAX (IF (($ A $ 2: $ A $ 9 = $ F $ 1) * ($ B $ 2: $ B $ 9 = $ F $ 2), $ C $ 2: $ C $ 9, ""))
Минимум: = MIN (IF (($ A $ 2: $ A $ 9 = $ F $ 1) * ($ B $ 2: $ B $ 9 = $ F $ 2), $ C $ 2: $ C $ 9, ""))
Среднее значение: = СРЕДНЕЕ (ЕСЛИ (($ A $ 2: $ A $ 9 = $ F $ 1) * ($ B $ 2: $ B $ 9 = $ F $ 2), $ C $ 2: $ C $ 9, ""))
Итого: = СУММ (ЕСЛИ (($ A $ 2: $ A $ 9 = $ F $ 1) * ($ B $ 2: $ B $ 9 = $ F $ 2), $ C $ 2: $ C $ 9, ""))
Пример 3.Формула массива для подсчета всех символов в диапазоне
Этот пример формулы массива показывает, как можно подсчитать количество символов, включая пробелы, в диапазоне ячеек.
Формула очень проста:
= СУММ (LEN ( диапазон ))
Вы используете функцию LEN для возврата длины текстовой строки в каждой отдельной ячейке, а затем вы используете функцию SUM для сложения этих чисел.
Например, формула массива = SUM (LEN (A1: A10)) вычисляет общее количество всех символов с пробелами в диапазоне A1: A10.
Пример 4. Формула массива для подсчета определенных символов в диапазоне
Если вы хотите узнать, сколько раз данный символ или группа символов появляется в указанном диапазоне ячеек, снова может помочь формула массива с функцией LEN. В этом случае формула немного сложнее:
= СУММ ((LEN ( диапазон ) — LEN (ПОДСТАВИТЬ ( диапазон , символов , «»))) / LEN ( символов ))
А вот и практический пример.Предположим, у вас есть список заказов, в котором одна ячейка может содержать несколько номеров заказов, разделенных запятыми или любым другим разделителем. Существует несколько типов заказов, каждый из которых имеет свой уникальный идентификатор — первый символ в номере заказа.
Предполагая, что заказы находятся в ячейках B2: B5 и уникальный идентификатор в E1, формула выглядит следующим образом:
= СУММ ((LEN (B2: B5) - LEN (ПОДСТАВИТЬ (B2: B5, E1, ""))) / LEN (E1))
В основе этой формулы функция ЗАМЕНА заменяет все вхождения указанного символа пустой строкой («»).
Подставленная строка передается в функцию LEN, чтобы получить длину строки без интересующего символа («K» в этом примере). А затем вы вычитаете длину замененной строки из длины исходной строки. Результатом этой операции является массив количества символов, по одному на ячейку, который вы делите на длину подстроки. При подсчете одного символа, как в этом примере, операция деления не является строго необходимой. Но если вы подсчитываете количество вхождений определенной подстроки в диапазоне (например,г. заказы, начинающиеся с «KM»), вам нужно разделить на длину подстроки, иначе каждый символ в подстроке будет учитываться отдельно.
Наконец, вы используете СУММ, чтобы сложить отдельные значения.
Пример 5. Сумма значений в каждой второй или N ^{-й строке}
Если вы хотите суммировать каждую вторую или каждую N ^{-ю строку} в таблице, вам понадобятся функции SUM и MOD, объединенные в формулу массива:
= СУММ ((- (МОД (СТРОКА ( диапазон ), n ) = 0)) * ( диапазон ))
Функция MOD возвращает остаток, округленный до ближайшего целого числа после деления числа на делитель.Мы встраиваем в него функцию СТРОКА, чтобы получить номер строки, а затем делим ее на N ^{-ю строку} (например, на 2 для суммирования каждой второй ячейки) и проверяем, равен ли остаток нулю. Если да, то ячейка суммируется.
Двойной унарный оператор (-) используется для преобразования нечисловых логических значений TRUE и FALSE, возвращаемых MOD, в 1 и 0, чтобы функция СУММ могла суммировать числа.
Например, чтобы подсчитать все остальные ячейки в диапазоне B2: B10, используйте одну из следующих формул:
Подсчитать четные строки (2 ^-й, 4 ^-й и т. Д.):
= СУММ ((- (MOD (СТРОКА ($ B2: B10), 2) = 0)) * (B2: B10))
Подсчитайте нечетные строки (1 ^ряд, 3 ^ряд и т. Д.):
= СУММ ((- (MOD (ROW ($ B2: B10), 2) = 1)) * (B2: B10) )
Чтобы получить универсальную формулу, которая может суммировать значения в любых указанных вами строках N ^и и правильно работать с любыми диапазонами независимо от их расположения на листе, формула должна быть немного улучшена:
= СУММ ((- (MOD ((СТРОКА ($ B $ 2: $ B $ 7) -ROW ($ B $ 2)), E1) = E1-1)) * ($ B $ 2: $ B $ 7))
Где E1 — каждая N строка, которую вы хотите суммировать.
Пример 6. Выполнение различных вычислений для чисел в разных диапазонах
Вот типичная ситуация для многих поставщиков — цена за единицу варьируется в зависимости от приобретенного количества, и ваша цель — написать формулу, которая вычисляет общую цену для любой суммы, введенной в конкретную ячейку.
Эту задачу легко решить, используя следующую вложенную формулу ЕСЛИ:
= B8 * ЕСЛИ (B8> = 101, B6, IF (B8> = 50, B5, IF (B8> = 20, B4, IF (B8> = 11, B3, IF (B8> = 1, B2, "")))))
Однако у этого подхода есть существенное ограничение.Поскольку формула ссылается на каждую цену в ячейках от B2 до B6 по отдельности, вам придется обновить формулу, как только ваши пользователи изменят любой из существующих диапазонов или добавят новый диапазон количества.
Чтобы сделать формулу более гибкой, работайте с массивами, а не с отдельными ячейками. В этом случае, независимо от того, сколько значений было изменено, добавлено или удалено, вам нужно будет обновить только одну ссылку на диапазон в формуле.
= СУММ (B8 * (B2: B6) * (- (B8> = VALUE (LEFT (A2: A6, FIND ("", A2: A6))))) * (- (B8 <= VALUE (ВПРАВО (A2: A6, LEN (A2: A6) - НАЙТИ («к», A2: A6) -LEN («к»))))))
Подробное описание этой формулы, вероятно, потребует отдельной статьи, поэтому я сделаю лишь краткий обзор логики.Если вы выберете отдельные части формулы в строке формул и нажмете F9, вы увидите, что все сводится к оценке следующих 3 массивов (при условии, что количество в B8 равно 100, как на скриншоте выше):
= СУММ (B8 * {20; 18; 16; 13; 12} * {1; 1; 1; 1; 0} * {0; 0; 0; 1; 1})
Первый массив из 5 элементов - это не что иное, как числа цен в ячейках B2: B6. И последние 2 массива нулей и единиц определяют, какая цена будет использоваться в расчетах. Итак, главный вопрос - откуда берутся эти два массива и что они означают?
Формула включает 2 функции VALUE: (B8> = VALUE ()) * (B8 <= VALUE ())
Функция 1 ^st проверяет, не превышает ли значение в B8 нижнюю границу каждого диапазона «единицы количества», а функция 2 ^и проверяет, меньше ли B8 или равно верхней границе каждый диапазон (комбинации функций LEFT, RIGHT, FIND и LEN используются для извлечения значений верхней и нижней границы).В результате вы получите 0, если условие не выполняется, и 1, если условие выполнено.
Наконец, функция СУММ умножает количество в B8 на каждый элемент массива цен (B2: B6) и на каждый элемент массивов 0 и 1. Поскольку умножение на 0 всегда дает 0, в окончательном вычислении используется только одна цена - элемент, который имеет 1 в последних двух массивах.
В этом примере количество умножается на 13 долларов, что соответствует диапазону суммы от «50 до 100». Это 4 ^th элемент массива цен (ячейка B5 в диапазоне B2: B6), и это единственный элемент, который имеет единицы в последних двух массивах.
Чтобы формула работала правильно, обязательно проверьте эти две вещи:
Величины в A2: A6 должны составлять непрерывный диапазон, чтобы не пропустить ни одного значения.
Все величины в A2: A6 должны быть введены в этот конкретный шаблон «от X до Y», потому что он жестко закодирован в формуле. Если ваши количества вводятся другим способом, скажем «1–10», замените «на» на «-» в формуле.
Если вы хотите отобразить сообщение « Out of range », когда количество, введенное в B8, выходит за пределы диапазона сумм, включите следующий оператор IF:
= ЕСЛИ (И (B8> = VALUE (LEFT (A2, FIND ("", A2))), B8 <= VALUE (RIGHT (A6, LEN (A6) - FIND ("to", A6) -LEN («к»)))), СУММ (…))
Эта сложная функция If делает очень простую вещь - проверяет, больше или равно ли значение в B8 нижней границе в A2 и меньше или равно верхней границе в A6.Другими словами, он проверяет это условие: AND (B8> = 1, B8 <= 200) .
Полная формула выглядит следующим образом:
= ЕСЛИ (И (B8> = VALUE (LEFT (A2, FIND ("", A2))), B8 <= VALUE (RIGHT (A6, LEN (A6) -FIND ("to", A6) -LEN ("к")))), СУММ (B8 * (B2: B6) * (- (B8> = VALUE (LEFT (A2: A6, FIND ("", A2: A6))))) * (- - (B8 <= VALUE (RIGHT (A2: A6, LEN (A2: A6) -FIND («to», A2: A6) -LEN («to»)))))), «Out of range»)
Пример 7. Пользовательские функции в формулах массива Excel
Этот пример предназначен для опытных пользователей, которые имеют некоторое представление о макросах Excel VBA и пользовательских функциях.
Вы можете использовать свою собственную определяемую пользователем функцию в формулах массивов Excel при условии, что данная функция поддерживает вычисления в массивах.
Например, один из наших гуру Excel написал функцию под названием GetCellColor , которая может получить цвет всех ячеек в диапазоне в точности, как следует из ее названия. Вы можете взять код функции из этой статьи - Как подсчитывать и суммировать ячейки по цвету в Excel.
А теперь давайте посмотрим, как можно использовать функцию GetCellColor в формуле массива.Предположим, у вас есть таблица со столбцом с цветовой кодировкой, и вы хотите суммировать значения, соответствующие нескольким условиям, включая цвет ячейки. В качестве примера, давайте найдем общее количество «зеленых» и «желтых» продаж, сделанных Нилом:
Как показано на изображении выше, мы используем следующую формулу массива Excel:
= СУММ (- ($ A $ 2: $ A $ 10 = $ F $ 1) * ($ C $ 2: $ C $ 10) * (- (GetCellColor ($ C $ 2: $ C $ 10) = GetCellColor ($ E 2 $))))
Где ячейка F1 - это имя продавца, а E2 - цветовой узор.
Формула суммирует ячейки в диапазоне C2: C10, если выполняются следующие 2 условия:
$ A $ 2: $ A $ 10 = $ F $ 1 - проверяет, совпадает ли ячейка в столбце A с именем продавца в F1, которым в данном примере является Neal .
GetCellColor ($ C $ 2: $ C $ 10) = GetCellColor ($ E $ 2) - мы используем настраиваемую пользовательскую функцию для получения цвета ячеек с C2 по C10 и проверяем, соответствует ли он цветному шаблону в E2, зеленый в данном случае.
Обратите внимание, что мы используем двойной унарный оператор с обоими вышеуказанными выражениями, чтобы преобразовать логические значения ИСТИНА и ЛОЖЬ, которые они возвращают, в единицы и нули, с которыми может работать функция СУММ.Если оба условия выполняются, т.е. возвращаются две единицы, СУММ складывает сумму продаж из соответствующей ячейки в столбце C.
Другие примеры формул массива Excel
А вот еще несколько примеров формул массива Excel, которые могут быть полезны.
Формулы массивов Excel - ограничения и альтернативы
Формулы массива - безусловно, одна из самых мощных функций Excel, но не всемогущая. Вот наиболее важные ограничения массивов в Excel.
1.Большие массивы могут замедлить работу Excel
Хотя Microsoft Excel не накладывает никаких ограничений на размер массивов, которые вы используете в своих таблицах, вы ограничены доступной на вашем компьютере памятью, поскольку пересчет формул с большими массивами занимает много времени. Итак, теоретически вы можете создавать огромные массивы, состоящие из сотен или тысяч элементов, на практике это не рекомендуется, потому что они могут значительно замедлить ваши рабочие книги.
2. Использование целых массивов столбцов не допускается
Вам не разрешается создавать массив, включающий целый столбец или несколько столбцов по очевидной причине, описанной выше.Формулы массива в Excel очень требовательны к ресурсам, и Microsoft принимает превентивные меры против зависания Excel.
3. Ограничение формулами массива, относящимися к другому листу
В Excel 2003 и более ранних версиях один рабочий лист мог содержать не более 65 472 формул массива, относящихся к другому листу. В современных версиях Excel 2013, 2010 и 2007 формулы массивов между листами ограничены только доступной памятью.
4. Отладка формул массива
Если формула массива возвращает неверный результат, убедитесь, что вы нажали Ctrl + Shift + Enter при его вводе.Если да, выберите части формулы и нажмите клавишу F9, чтобы оценить и отладить их.
5. Альтернативы формулам массивов.
Если вы находите формулы массива Excel слишком сложными и запутанными, вы можете использовать одну из функций Excel, которая может естественным образом обрабатывать массивы данных (без нажатия Ctrl + Shift + Enter). Хорошим примером является функция СУММПРОИЗВ, которая умножает значения в указанных массивах и возвращает сумму этих продуктов. Другим примером является функция Excel INDEX с пустым значением или 0 в аргументе row_num или col_num для возврата массива значений из всего столбца или строки соответственно.
Если вы хотите загрузить примеры формул массива Excel, обсуждаемые в этом руководстве, чтобы реконструировать их для лучшего понимания, вы можете загрузить примеры формул. Это файл .xlsm, так как пример 6 включает пользовательскую функцию VBA, поэтому вам нужно будет нажать кнопку Enable Content после загрузки, чтобы разрешить запуск макроса.
На сегодня все, спасибо, что прочитали!
Excel VBA Array - Полное руководство
В этой публикации подробно рассматривается массив VBA, который является очень важной частью языка программирования Excel VBA.Он охватывает все, что вам нужно знать о массиве VBA.
В первом разделе мы рассмотрим, что именно представляет собой массив VBA. Возможно, вы не понимаете часть кода в первом разделе. Не волнуйся. Я буду разбивать все это на простые термины в следующих разделах.
В первом разделе ниже приводится краткое справочное руководство по с использованием массива VBA . Возвращайтесь к нему в любое время, когда вам понадобится быстрое напоминание о том, как работают части массива VBA.
Остальная часть сообщения представляет собой наиболее полное руководство, которое вы найдете в массиве VBA.
Ссылки по теме для массива VBA
Циклы используются для чтения через массив VBA:
Для цикла
Для каждого цикла
Другие структуры данных в VBA:
Коллекция VBA - Хорошо, если вы хотите продолжать вставлять элементы при автоматическом изменении размера.
VBA ArrayList - имеет большую функциональность, чем Коллекция.
Словарь VBA - позволяет хранить пару ключ \ значение.Очень полезно во многих приложениях.
Руководство Microssoft для массивов VBA можно найти здесь.
Краткое руководство по массиву VBA
Задача Статический массив Динамический массив
Объявить Dim arr (от 0 до 5) As Long Dim arr () As Long
Dim arr As Variant
Установить размер См. Объявить выше ReDim arr (от 0 до 5) как вариант
Получить размер (количество элементов) См. Функцию ArraySize ниже. См. Функцию ArraySize ниже.
Увеличить размер (сохранить существующие данные) Только динамический ReDim Preserve arr (от 0 до 6)
Заданные значения arr (1) = 22 arr (1) = 22
Получение значений total = arr (1) total = arr (1)
Первая позиция LBound (arr) LBound (arr)
Последняя позиция Убунт (обр.) Убунт (обр.)
Прочитать все элементы (1D) Для i = LBound (arr) To UBound (arr)
Next i
or
For i = LBound (arr, 1) To UBound (arr, 1)
Next i Для i = LBound (arr) To UBound (arr)
Next i
or
For i = LBound (arr, 1) To UBound (arr, 1)
Next i
Прочитать все элементы (2D) Для i = LBound (arr, 1) To UBound (arr, 1)
For j = LBound (arr, 2) To UBound (arr, 2)
Next j
Next i Для i = LBound (arr, 1) To UBound (arr, 1)
Для j = LBound (arr, 2) To UBound (arr, 2)
Next j
Next i
Прочитать все элементы Уменьшить элемент как вариант
Для каждого элемента в arr
Следующий элемент Уменьшить элемент как вариант
Для каждого элемента в arr
Следующий элемент
Переход к Sub Sub MySub (ByRef arr () As String) Sub MySub (ByRef arr () As String)
Возврат из функции Функция GetArray () As Long ()
Dim arr (от 0 до 5) As Long
GetArray = arr
End Function Функция GetArray () As Long ()
Dim arr () As Long
GetArray = arr
Конечная функция
Получение от функции Только динамическое Dim arr () As Long
Arr = GetArray ()
Стереть массив Стереть arr
* Сбрасывает все значения по умолчанию Стереть arr
* Удаляет массив
Строка в массив Только динамический Dim arr As Variant
arr = Split ("Джеймс: Эрл: Джонс", ":")
Массив в строку Dim sName As String
sName = Join (arr, ":") Dim sName As String
sName = Join (arr, ":")
Заполнить значениями Только динамический Dim arr As Variant
arr = Array ("John", "Hazel", "Fred")
Диапазон до массива Только динамический Dim arr As Variant
arr = Range ("A1: D2")
Массив в диапазоне То же, что и динамический Dim arr As Variant
Range ("A5: D6") = arr
Загрузить исходный код и данные
Нажмите кнопку ниже, чтобы получить полностью документированный исходный код этой статьи.
Что такое массив VBA и зачем он вам нужен?
Массив VBA - это тип переменной. Он используется для хранения списков однотипных данных. Примером может служить сохранение списка стран или списка еженедельных итогов.
В VBA обычная переменная может хранить только одно значение за раз.
В следующем примере мы используем переменную для хранения оценок учащегося:
'Одновременно может храниться только 1 значение Тусклый студент1 пока Студент1 = 55
Если мы хотим сохранить оценки другого ученика, нам нужно создать вторую переменную.
В следующем примере у нас есть оценки пяти студентов:
Оценки учащихся
Мы собираемся прочитать эти отметки и записать их в Immediate Window.
Примечание: Функция Debug.Print записывает значения в непосредственное окно. Чтобы просмотреть это окно, выберите в меню View-> Immediate Window (сочетание клавиш Ctrl + G)
Как вы можете видеть в следующем примере, мы пишем один и тот же код пять раз - по одному для каждого студента:
'https: // excelmacromastery.com / Public Sub StudentMarks () 'Получите лист под названием «Отметки» Dim sh As Рабочий лист Установить sh = ThisWorkbook.Worksheets («Метки») 'Объявить переменную для каждого студента Тусклый студент1 пока Тусклый студент2 пока Тусклый студент3 пока Тусклый студент4 пока Тусклый студент5 пока 'Прочитать оценки учеников из ячейки Student1 = sh.Range ("C" & 3). Значение Student2 = sh.Range ("C" & 4).Значение Student3 = sh.Range ("C" & 5). Значение Student4 = sh.Range ("C" & 6). Значение Student5 = sh.Range ("C" & 7). Значение 'Печать оценок учащихся Debug.Print "Студенческие оценки" Debug.Print Student1 Debug.Print Student2 Отладка.Печать Student3 Отладка. Распечатать Student4 Debug.Print Student5 Конец подписки
Ниже приведен вывод из примера:
Выход
Проблема с использованием одной переменной для каждого учащегося заключается в том, что вам необходимо добавить код для каждого учащегося.Следовательно, если в приведенном выше примере у вас была тысяча студентов, вам потребовалось бы три тысячи строк кода!
К счастью, у нас есть массивы, облегчающие нашу жизнь. Массивы позволяют хранить список элементов данных в одной структуре.
В следующем коде показан приведенный выше пример учащегося с использованием массива:
'ExcelMacroMastery.com
Excel VBA Array – The Complete Guide
Автор: Пол Келли 'Описание: считывает метки в массив и записывает 'массив к непосредственному окну (Ctrl + G) 'ДЛЯ ЗАПУСКА: щелкните подменю и нажмите F5 Public Sub StudentMarksArr () 'Получите лист под названием «Отметки» Dim sh As Рабочий лист Установите sh = ThisWorkbook.Рабочие листы («Знаки») 'Объявить массив для выставления оценок для 5 студентов Уменьшить количество учащихся (от 1 до 5), пока 'Считать оценки учащихся из ячеек C3: C7 в массив 'Смещение подсчитывает строки из ячейки C2. например i = 1 - это C2 плюс 1 строка, которая равна C3 'i = 2 - это C2 плюс 2 строки, то есть C4 Тусклый я как долго Для i = от 1 до 5 Студенты (i) = sh.Range ("C2"). Offset (i) .Value Далее я 'Печатать оценки учеников из массива в окно немедленного доступа Отлаживать.Печать "Оценки учащихся" Для i = LBound (студенты) To UBound (студенты) Студенты Debug.Print (i) Далее я Конец подписки
Преимущество этого кода в том, что он будет работать для любого количества студентов. Если нам нужно изменить этот код для работы с 1000 студентами, нам нужно только изменить (от 1 до 5) на (от 1 до 1000) в объявлении. В предыдущем примере нам нужно было бы добавить примерно пять тысяч строк кода.
Давайте быстро сравним переменные и массивы. Сначала сравниваем декларацию:
'переменная Тусклый студент пока Тусклая страна как строка 'Массив Уменьшить количество учащихся (от 1 до 3) до Тусклые страны (от 1 до 3) в виде строки
Далее мы сравниваем присвоение значения:
'присвоить значение переменной Студент1 = .Cells (1, 1) 'присвоить значение первому элементу в массиве Студенты (1) =.Ячейки (1, 1)
Наконец, мы посмотрим на запись значений:
'Вывести значение переменной Debug.Print Student1 'Вывести значение первого студента в массиве Студенты Debug.Print (1)
Как видите, использование переменных и массивов очень похоже.
Важен тот факт, что массивы используют индекс (также называемый нижним индексом) для доступа к каждому элементу. Это означает, что мы можем легко получить доступ ко всем элементам в массиве с помощью цикла For Loop.
Теперь, когда у вас есть некоторое представление о том, почему массивы полезны, давайте рассмотрим их шаг за шагом.
Два типа массивов VBA
Есть два типа массивов VBA:
Статический - массив фиксированной длины.
Dynamic (не путать с динамическим массивом Excel) - массив, длина которого задается во время выполнения.
Разница между этими типами в основном заключается в том, как они созданы. Доступ к значениям в обоих типах массивов абсолютно одинаков.В следующих разделах мы рассмотрим оба этих типа.
Объявление массива VBA
Статический массив объявляется следующим образом:
'https://excelmacromastery.com/ Публичная подпрограмма DecArrayStatic () 'Создать массив с местоположениями 0,1,2,3 Dim arrMarks1 (от 0 до 3) до тех пор, пока 'По умолчанию от 0 до 3, т.е. местоположения 0,1,2,3 Dim arrMarks2 (3) Пока 'Создать массив с локациями 1,2,3,4,5 Dim arrMarks3 (от 1 до 5) по длине 'Создать массив с местоположениями 2,3,4' Это редко используется Dim arrMarks4 (от 2 до 4) по длине Конец подписки
Массив от 0 до 3
Как видите, длина указывается при объявлении статического массива.Проблема в том, что вы никогда не можете быть уверены заранее, какая длина вам нужна. Каждый раз, когда вы запускаете макрос, у вас могут быть разные требования к длине.
Если вы не используете все расположения массива, то ресурсы тратятся. Поэтому, если вам нужно больше мест, вы можете использовать ReDim , но это по сути создает новый статический массив.
У динамического массива таких проблем нет. Вы не указываете длину, когда объявляете ее. Следовательно, вы можете увеличивать и уменьшать размер по мере необходимости:
'https: // excelmacromastery.com / Публичная подпрограмма DecArrayDynamic () 'Объявить динамический массив Dim arrMarks () Пока 'Установите длину массива, когда будете готовы ReDim arrMarks (от 0 до 5) Конец подписки
Динамический массив не выделяется, пока вы не используете оператор ReDim. Преимущество состоит в том, что вы можете подождать, пока не узнаете количество элементов, прежде чем устанавливать длину массива. В случае статического массива вы должны заранее указать длину.
Приведу пример.Представьте, что вы читаете рабочие листы с оценками учащихся. С помощью динамического массива вы можете подсчитать количество студентов на листе и установить массив этой длины. В статическом массиве вы должны установить длину максимально возможного числа студентов.

Присвоение значений массиву VBA
Для присвоения значений массиву используется номер местоположения. Вы назначаете значение для обоих типов массивов одинаково:
'https://excelmacromastery.com/ Публичная подпрограмма AssignValue () 'Объявить массив с местоположениями 0,1,2,3 Dim arrMarks (от 0 до 3) до тех пор, пока 'Установить значение позиции 0 arrMarks (0) = 5 'Установите значение позиции 3 arrMarks (3) = 46 'Это ошибка, поскольку нет местоположения 4 arrMarks (4) = 99 Конец подписки
Массив с присвоенными значениями
Номер местоположения называется индексом или индексом.Последняя строка в примере выдаст ошибку «Индекс вне диапазона», поскольку в примере массива нет ячейки 4.
Получить длину массива VBA
Нет собственной функции для получения количества элементов в массиве. Я создал функцию ArrayLength ниже, чтобы возвращать количество элементов в любом массиве независимо от того, сколько измерений:
'https://excelmacromastery.com/ Функция ArrayLength (arr As Variant) Как долго При ошибке Goto eh 'Цикл используется для многомерных массивов.Цикл завершится, когда Возникает ошибка «Индекс вне диапазона», т. Е. Больше нет измерений. Dim i As Long, длина As Long длина = 1 'Цикл, пока больше не будет измерений Делай, пока правда я = я + 1 'Если в массиве нет элементов, эта строка выдаст ошибку Длина = Длина * (UBound (arr, i) - LBound (arr, i) + 1) 'Установите здесь ArrayLength, чтобы не возвращать 1 для пустого массива ArrayLength = Длина Петля Готово: Функция выхода а: Если Err.Number = 13 Then 'Ошибка несоответствия типа Err.Raise vbObjectError, "ArrayLength" _ , «Аргумент, переданный функции ArrayLength, не является массивом». Конец, если Конечная функция
Вы можете использовать это так:
'Имя: TEST_ArrayLength Автор: Пол Келли, ExcelMacroMastery.com 'Описание: тестирует функции ArrayLength и записывает 'результаты в Немедленное окно (Ctrl + G) Sub TEST_ArrayLength () '0 товаров Dim arr1 () Пока Отлаживать.Печать ArrayLength (arr1) '10 товаров Dim arr2 (от 0 до 9) до тех пор, пока Debug.Print ArrayLength (arr2) '18 товаров Dim arr3 (от 0 до 5, от 1 до 3) до тех пор, пока Debug.Print ArrayLength (arr3) 'Вариант базы 0: 144 шт. Вариант базы 1:50 шт. Dim arr4 (1, 5, 5, 0 до 1) до тех пор Debug.Print ArrayLength (arr4) Конец подписки
Использование функций массива и разделения
Вы можете использовать функцию Array для заполнения массива списком элементов.Вы должны объявить массив как вариант типа. В следующем коде показано, как использовать эту функцию.
Dim arr1 как вариант arr1 = Array («Апельсин», «Персик», «Груша») Dim arr2 как вариант arr2 = массив (5, 6, 7, 8, 12)
Содержимое arr1 после использования функции массива
Массив, созданный функцией массива, будет начинаться с нулевого индекса, если вы не используете Option Base 1 в верхней части модуля.Затем он начнется с первого индекса. В программировании, как правило, считается плохой практикой иметь ваши фактические данные в коде. Однако иногда это полезно, когда вам нужно быстро протестировать код.
Функция Split используется для разделения строки на массив на основе разделителя. Разделитель - это такой символ, как запятая или пробел, разделяющий элементы.
Следующий код разделит строку на массив из трех элементов:
Dim s As String s = "Красный, желтый, зеленый, синий" Dim arr () как строка arr = Разделить (s, ",")
Массив после использования Split
Функция разделения обычно используется при чтении из файла с разделителями-запятыми или из другого источника, который предоставляет список элементов, разделенных одним и тем же символом.
Использование циклов с массивом VBA
Использование цикла For Loop обеспечивает быстрый доступ ко всем элементам в массиве. Вот где становится очевидной сила использования массивов. Мы можем читать массивы с десятью значениями или десятью тысячами значений, используя те же несколько строк кода. В VBA есть две функции: LBound и UBound. Эти функции возвращают самый маленький и самый большой индекс в массиве. В массиве arrMarks (от 0 до 3) LBound вернет 0, а UBound вернет 3.
В следующем примере случайные числа назначаются массиву с помощью цикла.Затем он распечатывает эти числа, используя второй цикл.
'https://excelmacromastery.com/ Публичная подпрограмма ArrayLoops () 'Объявить массив Dim arrMarks (от 0 до 5) до тех пор, пока 'Заполняем массив случайными числами Тусклый я как долго Для i = LBound (arrMarks) To UBound (arrMarks) arrMarks (i) = 5 * Rnd Далее я 'Распечатать значения в массиве Debug.Print «Местоположение», «Значение» Для i = LBound (arrMarks) To UBound (arrMarks) Отлаживать.Выведите i, arrMarks (i) Далее я Конец подписки
Функции LBound и UBound очень полезны. Их использование означает, что наши циклы будут корректно работать с любой длиной массива. Реальное преимущество состоит в том, что при изменении длины массива нам не нужно изменять код для печати значений. Цикл будет работать для массива любой длины, пока вы используете эти функции.
Использование цикла For Each с массивом VBA
Вы можете использовать цикл For Each с массивами.Важно помнить, что это только для чтения. Это означает, что вы не можете изменить значение в массиве.
В следующем коде значение mark изменяется, но не меняет значение в массиве.
За каждую отметку в arrMarks 'Не изменит значение массива mark = 5 * Rnd Следующая отметка
Цикл For Each отлично подходит для чтения массива. Как мы увидим, лучше писать специально для двумерного массива.
Тусклая метка как вариант Для каждой отметки в arrMarks Отладка. Печать Следующая отметка
Использование стирания с массивом VBA
Функция Erase может использоваться с массивами, но работает по-разному в зависимости от типа массива.
Для статического массива функция Erase сбрасывает все значения на значения по умолчанию. Если массив состоит из длинных целых чисел (например, типа Long), тогда все значения устанавливаются на ноль. Если массив состоит из строк, тогда все строки устанавливаются в «» и так далее.
Для динамического массива функция стирания освобождает память. То есть он удаляет массив. Если вы хотите использовать его снова, вы должны использовать ReDim для выделения памяти.
Давайте посмотрим на пример статического массива. Этот пример аналогичен примеру ArrayLoops в последнем разделе с одним отличием - мы используем Erase после установки значений. Когда значение будет распечатано, все они будут нулевыми:
'https://excelmacromastery.com/ Публичная подпрограмма EraseStatic () 'Объявить массив Dim arrMarks (от 0 до 3) до тех пор, пока 'Заполняем массив случайными числами Тусклый я как долго Для i = LBound (arrMarks) To UBound (arrMarks) arrMarks (i) = 5 * Rnd Далее я 'ВСЕ ЗНАЧЕНИЯ ОБНУЛЕНЫ Стереть arrMarks 'Распечатайте значения - теперь все нулевые Отлаживать.Вывести «Местоположение», «Значение» Для i = LBound (arrMarks) To UBound (arrMarks) Debug.Print i, arrMarks (i) Далее я Конец подписки
Теперь мы попробуем тот же пример с динамиком. После того, как мы использовали Erase, все места в массиве были удалены. Нам нужно использовать ReDim, если мы хотим снова использовать массив.
Если мы попытаемся получить доступ к членам этого массива, мы получим «Индекс вне диапазона». ошибка:
.
'https: // excelmacromastery.com / Публичная подпрограмма EraseDynamic () 'Объявить массив Dim arrMarks () Пока ReDim arrMarks (от 0 до 3) 'Заполняем массив случайными числами Тусклый я как долго Для i = LBound (arrMarks) To UBound (arrMarks) arrMarks (i) = 5 * Rnd Далее я 'arrMarks теперь освобожден. Нет местоположений. Стереть arrMarks Конец подписки
Увеличение длины массива VBA
Если мы используем ReDim в существующем массиве, то массив и его содержимое будут удалены.
В следующем примере второй оператор ReDim создаст полностью новый массив. Исходный массив и его содержимое будут удалены.
'https://excelmacromastery.com/ Sub UsingRedim () Dim arr () как строка 'Установить массив в слоты от 0 до 2 ReDim arr (от 0 до 2) arr (0) = "Яблоко" 'Массив с яблоком теперь удален ReDim arr (от 0 до 3) Конец подписки
Если мы хотим увеличить длину массива без потери содержимого, мы можем использовать ключевое слово Preserve .
Когда мы используем Redim Preserve , новый массив должен начинаться с того же начального измерения, например.
Мы не можем сохранить от (0 до 2) до (от 1 до 3) или до (от 2 до 10), так как это разные начальные размеры.
В следующем коде мы создаем массив, используя ReDim , а затем заполняем массив типами фруктов.
Затем мы используем Preserve , чтобы увеличить длину массива, чтобы не потерять исходное содержимое:
'https: // excelmacromastery.com / Sub UsingRedimPreserve () Dim arr () как строка 'Установить массив в слоты от 0 до 1 ReDim arr (от 0 до 2) arr (0) = "Яблоко" arr (1) = "Апельсин" arr (2) = "Груша" 'Сбросить длину и сохранить исходное содержимое ReDim Preserve arr (от 0 до 5) Конец подписки
Из приведенных ниже снимков экрана видно, что исходное содержимое массива было «сохранено».
Перед ReDim Preserve
Сохранение после ReDim
Предупреждение: В большинстве случаев вам не нужно изменять размер массива, как мы делали в этом разделе.Если вы изменяете размер массива несколько раз, вы можете рассмотреть возможность использования Collection.
Использование сохранения с двумерными массивами
Preserve работает только с верхней границей массива.
Например, если у вас есть двумерный массив, вы можете сохранить только второе измерение, как показано в этом примере:
'https://excelmacromastery.com/ Sub Preserve2D () Dim arr () Пока 'Установить начальную длину ReDim arr (от 1 до 2, от 1 до 5) 'Измените длину верхнего измерения ReDim Preserve arr (от 1 до 2, от 1 до 10) Конец подписки
Если мы попытаемся использовать Preserve для нижней границы, мы получим ошибку «Нижний индекс вне допустимого диапазона».
В следующем коде мы используем Preserve для первого измерения. Запуск этого кода приведет к ошибке «Индекс вне допустимого диапазона»:
'https://excelmacromastery.com/ Sub Preserve2DError () Dim arr () Пока 'Установить начальную длину ReDim arr (от 1 до 2, от 1 до 5) Ошибка "Индекс вне допустимого диапазона" ReDim Preserve arr (от 1 до 5, от 1 до 5) Конец подписки
Когда мы читаем из диапазона в массив, он автоматически создает двумерный массив, даже если у нас есть только один столбец.
Применяются те же правила сохранения. Мы можем использовать Preserve только для верхней границы, как показывает этот пример:
'https://excelmacromastery.com/ Sub Preserve2DRange () Dim arr как вариант 'Присвойте диапазон массиву arr = Sheet1.Range ("A1: A5"). Значение 'Сохранить будет работать только с верхней границей ReDim Preserve arr (от 1 до 5, от 1 до 7) Конец подписки
Сортировка массива VBA
В VBA нет функции для сортировки массива.Мы можем отсортировать ячейки листа, но это может быть медленным, если данных много.
Приведенную ниже функцию QuickSort можно использовать для сортировки массива.
'https://excelmacromastery.com/ Sub QuickSort (arr As Variant, first as long, last as long) Dim vCentreVal как вариант, vTemp как вариант Dim lTempLow As Long Dim lTempHi As Long lTempLow = первый lTempHi = последний vCentreVal = arr ((первый + последний) \ 2) Делать пока lTempLow <= lTempHi Do While arr (lTempLow) сначала lTempHi = lTempHi - 1 Петля Если lTempLow <= lTempHi Тогда 'Обменять значения vTemp = arr (lTempLow) arr (lTempLow) = arr (lTempHi) arr (lTempHi) = vTemp 'Перейти к следующим позициям lTempLow = lTempLow + 1 lTempHi = lTempHi - 1 Конец, если Петля Если first
Эту функцию можно использовать так:
'https: // excelmacromastery.com / Sub TestSort () 'Создать временный массив Dim arr () как вариант arr = Array («Банан», «Дыня», «Персик», «Слива», «Яблоко») 'Сортировать массив QuickSort arr, LBound (прибл), UBound (приб) 'Распечатать arr в немедленное окно (Ctrl + G) Тусклый я как долго Для i = LBound (arr) To UBound (arr) Отладка.Печать arr (i) Далее я Конец подписки
Передача массива VBA в подчиненный
Иногда вам нужно передать массив в процедуру.Вы объявляете параметр, используя круглые скобки, аналогично тому, как вы объявляете динамический массив.
Переход к процедуре с использованием ByRef означает, что вы передаете ссылку на массив. Поэтому, если вы измените массив в процедуре, он будет изменен, когда вы вернетесь.
Примечание: Когда вы используете массив в качестве параметра, он не может использовать ByVal, он должен использовать ByRef. Вы можете передать массив с помощью ByVal, сделав параметр вариантом.
'https://excelmacromastery.com/ 'Передает массив функции Публичный Sub PassToProc () Dim arr (от 0 до 5) как строка 'Передаем массив в функцию UseArray arr Конец подписки Открытая функция UseArray (ByRef arr () As String) 'Использовать массив Отлаживать.Печать UBound (обр.) Конечная функция
Возврат массива VBA из функции
Важно помнить следующее. Если вы хотите изменить существующий массив в процедуре, вы должны передать его как параметр с помощью ByRef (см. Последний раздел). Вам не нужно возвращать массив из процедуры.
Основная причина возврата массива - это когда вы используете процедуру для создания нового. В этом случае вы назначаете возвращаемый массив массиву в вызывающей стороне.Этот массив уже нельзя выделить. Другими словами, вы должны использовать динамический массив, который не был выделен.
Следующие примеры показывают это
'https://excelmacromastery.com/ Public Sub TestArray () 'Объявить динамический массив - не выделен Dim arr () как строка 'Вернуть новый массив arr = GetArray Конец подписки Открытая функция GetArray () As String () 'Создать и выделить новый массив Dim arr (от 0 до 5) как строка 'Возвращаемый массив GetArray = arr Конечная функция
Использование двумерного массива VBA
Массивы, которые мы рассматривали до сих пор, были одномерными массивами.Это означает, что массивы представляют собой один список элементов.
Двумерный массив - это, по сути, список списков. Если вы думаете об одной строке таблицы как об одном измерении, тогда более одного столбца будут двухмерными. Фактически электронная таблица - это эквивалент двумерного массива. Он имеет два измерения - строки и столбцы.
Следует отметить один небольшой момент: Excel обрабатывает одномерный массив как строку, если вы записываете его в электронную таблицу. Другими словами, массив arr (от 1 до 5) эквивалентен arr (от 1 до 1, от 1 до 5) при записи значений в электронную таблицу.
На следующем изображении показаны две группы данных. Первый - одномерный, второй - двухмерный.
Для доступа к элементу в первом наборе данных (одномерном) все, что вам нужно сделать, это указать строку, например. 1,2, 3 или 4.
Для второго набора данных (двумерного) необходимо указать строку И столбец. Таким образом, вы можете думать об одномерном как о нескольких столбцах и одной строке, а о двумерном - как о нескольких строках и нескольких столбцах.
Примечание: В массиве может быть более двух измерений. Это редко требуется. Если вы решаете проблему с помощью трехмерного массива, возможно, есть лучший способ сделать это.
Вы объявляете двумерный массив следующим образом:
Dim ArrayMarks (от 0 до 2,0 до 3) по длине
В следующем примере создается случайное значение для каждого элемента в массиве, и эти значения выводятся в окно Immediate Window:
'https: // excelmacromastery.com / Public Sub TwoDimArray () 'Объявить двумерный массив Dim arrMarks (от 0 до 3, от 0 до 2) как строка 'Заполните массив текстом, состоящим из значений i и j Dim i до тех пор, j до тех пор Для i = LBound (arrMarks) To UBound (arrMarks) Для j = LBound (arrMarks, 2) To UBound (arrMarks, 2) arrMarks (i, j) = CStr (i) & ":" & CStr (j) Следующий j Далее я 'Распечатать значения в массиве в Immediate Window Отлаживать.Выведите «i», «j», «Value» Для i = LBound (arrMarks) To UBound (arrMarks) Для j = LBound (arrMarks, 2) To UBound (arrMarks, 2) Отладка. Печать i, j, arrMarks (i, j) Следующий j Далее я Конец подписки
Как видите, мы используем второй цикл For внутри первого цикла для доступа ко всем элементам.
Результат примера выглядит следующим образом:
Как работает этот макрос:
Входит в цикл и
i установлен на 0
входит j петля
j установлен на 0
j установлено на 1
j установлено на 2
Выход j петля
i установлен на 1
j установлен на 0
j установлено на 1
j установлено на 2
И так до i = 3 и j = 2
Вы можете заметить, что LBound и UBound имеют второй аргумент со значением 2 .Это указывает, что это верхняя или нижняя граница второго измерения. Это начальное и конечное местоположение для j . Значение по умолчанию 1, поэтому нам не нужно указывать его для цикла и .
Использование каждого цикла
Использование For Each удобнее использовать при чтении из массива.
Возьмем приведенный выше код, который записывает двумерный массив
'Для использования цикла For требуется два цикла Отлаживать.Выведите «i», «j», «Value» Для i = LBound (arrMarks) To UBound (arrMarks) Для j = LBound (arrMarks, 2) To UBound (arrMarks, 2) Отладка. Печать i, j, arrMarks (i, j) Следующий j Далее я
Теперь давайте перепишем его, используя цикл For each. Как видите, нам нужен только один цикл, поэтому писать намного проще:
'Для использования For Each требуется только один цикл Debug.Print «Значение» Тусклый знак как вариант Для каждой отметки в arrMarks Отлаживать.Отметка для печати Следующая отметка
Использование цикла For Each дает нам массив только в одном порядке - от LBound до UBound. В большинстве случаев это все, что вам нужно.
Чтение из диапазона в массив VBA
Если вы читали мой предыдущий пост о ячейках и диапазонах, то вы знаете, что VBA имеет чрезвычайно эффективный способ чтения из диапазона ячеек в массив и наоборот.
'https://excelmacromastery.com/ Публичная подпрограмма ReadToArray () 'Объявить динамический массив Dim StudentMarks как вариант 'Считать значения в массив из первой строки StudentMarks = Range ("A1: Z1").Значение 'Записываем значения обратно в третью строку Диапазон ("A3: Z3"). Значение = StudentMarks Конец подписки
Динамический массив, созданный в этом примере, будет двумерным массивом. Как видите, мы можем считывать данные из всего диапазона ячеек в массив всего за одну строку.
В следующем примере будут считываться данные об учениках ниже из C3: E6 Листа 1 и распечатываться в окне немедленного доступа:
'https://excelmacromastery.com/ Общедоступная подпрограмма ReadAndDisplay () 'Получить диапазон Dim rg As Range Установите rg = ThisWorkbook.Рабочие листы («Лист1»). Диапазон («C3: E6») 'Создать динамический массив Dim StudentMarks как вариант 'Считать значения в массив из листа1 StudentMarks = rg.Value 'Распечатать значения массива Отладка. Печать "i", "j", "Value" Dim i до тех пор, j до тех пор Для i = LBound (StudentMarks) To UBound (StudentMarks) Для j = LBound (StudentMarks, 2) To UBound (StudentMarks, 2) Отладка: печать i, j, StudentMarks (i, j) Следующий j Далее я Конец подписки
Образец данных студентов
Вывод данных выборки
Как вы можете видеть, первое измерение (доступное с помощью и ) массива - это строка, а второе - столбец.Чтобы продемонстрировать это, взгляните на значение 44 в E4 выборки данных. Это значение находится в строке 2 столбца 3 наших данных. Вы можете видеть, что 44 хранится в массиве по адресу StudentMarks (2,3) .

Подробнее об использовании массивов с диапазонами можно узнать из этого видео на YouTube.
Как заставить ваши макросы работать со сверхвысокой скоростью
Если ваши макросы работают очень медленно, этот раздел может оказаться для вас очень полезным. Особенно, если вы имеете дело с большими объемами данных.Следующее - очень хорошо хранимый секрет в VBA
.
Обновление значений в массивах происходит экспоненциально быстрее, чем обновление значений в ячейках.
В последнем разделе вы видели, как мы можем легко считывать данные из группы ячеек в массив и наоборот. Если мы обновляем много значений, мы можем сделать следующее:
1. Скопируйте данные из ячеек в массив.
2. Измените данные в массиве.
3. Скопируйте обновленные данные из массива обратно в ячейки.
Например, следующий код будет намного быстрее, чем код под ним:
'https://excelmacromastery.com/ Публичная подпрограмма ReadToArray () 'Считать значения в массив из первой строки Dim StudentMarks как вариант StudentMarks = Range ("A1: Z20000"). Значение Тусклый я как долго Для i = LBound (StudentMarks) To UBound (StudentMarks) 'Обновите отметки здесь StudentMarks (i, 1) = StudentMarks (i, 1) * 2 '... Далее я 'Запишите новые значения обратно в рабочий лист Диапазон ("A1: Z20000"). Значение = StudentMarks Конец подписки
'https://excelmacromastery.com/ Sub UsingCellsToUpdate () Dim c как вариант Для каждого c в диапазоне ("A1: Z20000") c.Value = 'Обновите значения здесь Следующий c Конец подписки
Назначение из одного набора ячеек другому также намного быстрее, чем при использовании копирования и вставки:
'Назначение - это быстрее Диапазон ("A1: A10").Значение = диапазон ("B1: B10"). Значение 'Копировать и вставить - это медленнее Диапазон ("B1: B1"). Место назначения копирования: = Диапазон ("A1: A10")
Следующие комментарии от двух читателей, которые использовали массивы для ускорения работы своих макросов
«Несколько моих проектов из почти невозможного и долгого превратились в слишком легкие, а время выполнения сократилось с 10: 1». - датчанин
«Один отчет, который я написал, занял почти 3 часа при прямом доступе к ячейкам - 5 минут с массивами» - Джим
Вы можете увидеть больше о скорости массивов по сравнению с другими методами в этом видео на YouTube.

Чтобы увидеть сравнение между Find, Match и Arrays, стоит проверить этот пост Чарльза Уильямса.
Заключение
Ниже приводится краткое изложение основных моментов этого сообщения
Массивы - это эффективный способ хранения списка элементов одного типа.
Вы можете получить доступ к элементу массива напрямую, используя номер местоположения, который известен как индекс или индекс .
Типичная ошибка « Нижний индекс вне диапазона » вызвана доступом к несуществующему местоположению.
Существует два типа массивов: Static и Dynamic .
Статический используется, когда длина массива всегда одинакова.
Динамические массивы позволяют определять длину массива во время выполнения.
LBound и UBound обеспечивают безопасный способ поиска наименьших и наибольших индексов массива.
Базовый массив одномерный . Вы также можете иметь многомерные массивы.
Вы можете передать массив только процедуре, используя ByRef . Вы делаете это так: ByRef arr () as long.
Вы можете вернуть массив из функции, но массив, которому он назначен, в настоящее время не должен выделяться.

Задача	Статический массив	Динамический массив
Объявить	Dim arr (от 0 до 5) As Long	Dim arr () As Long Dim arr As Variant
Установить размер	См. Объявить выше	ReDim arr (от 0 до 5) как вариант
Получить размер (количество элементов)	См. Функцию ArraySize ниже.	См. Функцию ArraySize ниже.
Увеличить размер (сохранить существующие данные)	Только динамический	ReDim Preserve arr (от 0 до 6)
Заданные значения	arr (1) = 22	arr (1) = 22
Получение значений	total = arr (1)	total = arr (1)
Первая позиция	LBound (arr)	LBound (arr)
Последняя позиция	Убунт (обр.)	Убунт (обр.)
Прочитать все элементы (1D)	Для i = LBound (arr) To UBound (arr) Next i or For i = LBound (arr, 1) To UBound (arr, 1) Next i	Для i = LBound (arr) To UBound (arr) Next i or For i = LBound (arr, 1) To UBound (arr, 1) Next i
Прочитать все элементы (2D)	Для i = LBound (arr, 1) To UBound (arr, 1) For j = LBound (arr, 2) To UBound (arr, 2) Next j Next i	Для i = LBound (arr, 1) To UBound (arr, 1) Для j = LBound (arr, 2) To UBound (arr, 2) Next j Next i
Прочитать все элементы	Уменьшить элемент как вариант Для каждого элемента в arr Следующий элемент	Уменьшить элемент как вариант Для каждого элемента в arr Следующий элемент
Переход к Sub	Sub MySub (ByRef arr () As String)	Sub MySub (ByRef arr () As String)
Возврат из функции	Функция GetArray () As Long () Dim arr (от 0 до 5) As Long GetArray = arr End Function	Функция GetArray () As Long () Dim arr () As Long GetArray = arr Конечная функция
Получение от функции	Только динамическое	Dim arr () As Long Arr = GetArray ()
Стереть массив	Стереть arr * Сбрасывает все значения по умолчанию	Стереть arr * Удаляет массив
Строка в массив	Только динамический	Dim arr As Variant arr = Split ("Джеймс: Эрл: Джонс", ":")
Массив в строку	Dim sName As String sName = Join (arr, ":")	Dim sName As String sName = Join (arr, ":")
Заполнить значениями	Только динамический	Dim arr As Variant arr = Array ("John", "Hazel", "Fred")
Диапазон до массива	Только динамический	Dim arr As Variant arr = Range ("A1: D2")
Массив в диапазоне	То же, что и динамический	Dim arr As Variant Range ("A5: D6") = arr

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

Функция ВПР

Функция ГПР

Функция СТРОКА

Функция СТОЛБЕЦ

Функция АДРЕС

Функция ДВССЫЛ

Знакомство с формулами массива в Excel

Одномерные и двухмерные массивы

Размерность массива

Одноячеечные и многоячеечные формулы массива

Редактирование формул массива в Excel

Правила редактирования формул массива

Выделение массива в Excel

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

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

Изменение размеров формулы массива

Массив последовательных чисел в EXCEL. Примеры и описание

Динамический массив последовательных чисел

Функции VBA для работы с массивами

Спасибо за сообщение

Динамические массивы, функции и формулы Excel

Динамические массивы Excel

Наличие динамических массивов Excel

Функции динамического массива Excel

Формулы динамического массива Excel

Пример 1. Новая функция динамического массива

Пример 2. Объединение нескольких функций динамического массива в одной формуле

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

Пример 4.Существующие функции поддерживают динамические массивы

Пример 5. Формула ВПР для возврата нескольких значений

Пример 6. Легкая формула TRANSPOSE

Диапазон разлива — одна формула, несколько ячеек

Ссылка на диапазон разлива (символ #)

Неявное пересечение и символ @

Преимущества динамических массивов Excel

Простой и мощный

Родной для всех формул

Вложенные функции динамического массива

Относительные и абсолютные ссылки менее важны

Ограничения динамических массивов

Результаты не могут быть отсортированы обычным способом

Невозможно удалить какое-либо значение в диапазоне разлива

Результаты нельзя занести в таблицу

Не работают с Excel Power Query

Динамические массивы в сравнении с традиционными формулами массива CSE

Обратная совместимость: динамические массивы в устаревшей версии Excel

Формулы динамического массива Excel не работают

# РАЗЛИВ! ошибка

#REF! ошибка

# ИМЯ? ошибка

Вас также может заинтересовать

Примеры формул массива Excel для начинающих и опытных пользователей

Пример 1. Подсчет ячеек, удовлетворяющих определенным условиям

Пример 2. Использование нескольких функций в формулах массива Excel

Пример 3.Формула массива для подсчета всех символов в диапазоне

Пример 4. Формула массива для подсчета определенных символов в диапазоне

Пример 5. Сумма значений в каждой второй или N -й строке

Пример 6. Выполнение различных вычислений для чисел в разных диапазонах

Пример 7. Пользовательские функции в формулах массива Excel

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

Формулы массивов Excel - ограничения и альтернативы

1.Большие массивы могут замедлить работу Excel

2. Использование целых массивов столбцов не допускается

3. Ограничение формулами массива, относящимися к другому листу

4. Отладка формул массива

5. Альтернативы формулам массивов.

Excel VBA Array - Полное руководство

Ссылки по теме для массива VBA

Краткое руководство по массиву VBA

Загрузить исходный код и данные

Что такое массив VBA и зачем он вам нужен?

Два типа массивов VBA

Объявление массива VBA

Присвоение значений массиву VBA

Получить длину массива VBA

Использование функций массива и разделения

Использование циклов с массивом VBA

Использование цикла For Each с массивом VBA

Использование стирания с массивом VBA

Пример 5. Сумма значений в каждой второй или N ^{-й строке}