Разное

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

Содержание

— Excel

MS Office 2007: Microsoft Excel

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

Одна из наиболее интересных (и наиболее мощных) возможностей Excel — допустимость использования массивов в формулах.

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

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

Задача 5.8. Формулы массивов для диапазонов ячеек

На рис. 95 показан простой рабочий лист, на котором вычисляются

объемы продаж некоторых товаров. Чтобы вычислить объем продаж каждого конкретного товара (значения в столбце D), обычно используется формула умножения количества проданного товара (столбец С) и цены товара (столбец В). Например, в ячейке D2 будет формула =В2*С2, которая затем копируется на все оставшиеся ячейки столбца D. В данном случае получим пять отдельных формул в столбце D.

Другой способ вычисления пяти значений в столбце D — использование одной формулы массива. Эта формула займет диапазон D2:D5 и вернет сразу все пять искомых значений.

Для создания формулы массива следует выполнить следующее.

  1. Выделить диапазон, в котором должен содержаться результат. В нашем примере это диапазон D2:D5.
  2. Ввести формулу =В2:В5*С2:С5. Поставьте равно и выделите диапазон В2:В5, поставьте значок умножить и выделите диа­пазон С2:С5.
  3. Нажмите комбинацию клавиш Ctrl+Shift+Enter, чтобы ввести формулу как формулу массива (обычно для завершения ввода формулы следует нажать клавишу Enter).

Формула будет введена сразу во все пять выделенных ячеек. Если вы посмотрите в строку формул, то там будет представлена формула {=В2:В5*С2:С5}, т.е. Excel произвел попарное умножение элементов массивов В2:В5 и С2:С5 и создал новый массив стоимостей D2:D5.

Обратите внимание на фигурные скобки, появившиеся в формуле — отличительный признак формулы массива. Вводить их вручную с кла­виатуры бесполезно — они автоматически появляются при нажатии Ctrl+Shift+Enter (при удержании нажатых клавиш Shift и Ctrl производят нажатие клавиши Enter). Если бы мы нажали Enter, то формула была бы введена только в активную ячейку блока (Проверьте!). Excel заключает формулу в фигурные скобки, показывая тем самым, что это формула массива. Эти скобки нельзя набирать вручную (формула будет воспринята как текст).

Задача 5.9. Формулы массивов для отдельных ячеек

Формулы массивов могут возвращать результат и в одну ячейку. На рис.96 в ячейку D7 введена формула массива

{=СУММ(В2:В5*С2:С5)}.

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

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

Формула, как и предыдущая, работает с двумя массивами, храня­щимися в диапазонах В2:В5 и С2:С7. Формула перемножает соответ­ствующие значения из этих массивов виртуальный массив, который существует только в памяти компьютера. Затем функция СУММ обраба­тывает этот новый массив и возвращает сумму его значений.

В данном случае вместо формулы массива можно воспользоваться функцией СУММПРОИЗВ, которая вернет тот же результат: =СУММПРОИЗВ(В2:В6;С2:С6).

Задача 5.10. Именованные массивы

Можно создать массив констант, присвоить ему имя и затем ис­пользовать именованный массив в формулах. Строго говоря, именованный массив представляет собой формулу, которой присвоено имя. Разберем на примере.

Пусть нам известна сумма прихода и сумма расхода. Необходимо вычислить доход как разность этих величин. Введите исходные данные в таблицу (рис.97).

Однако здесь фактически из вектор-столбца В2:В5 вычитается вектор-столбец С2:С5. Поэтому можно непосредственно вычесть из вектора вектор одной формулой, а не создавать отдельные формулы для компонент вектора.

Создание имен. Для наглядности дадим векторам имена. Выделите диапазон со вторым (В2:В5) и третьим (С2:С5) столбцами таблицы последовательно и присвойте имена во вкладке Формулы-присвоить Имя. Диапазону В2:В5 присвойте имя Приход, а диапазону С2:С5 — имя Расход.

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

Вводим в диапазон D2:D5 формулу массива.

  • Выделим блок D2: D5. В этом блоке активна ячейка D2.
  • Наберем знак равенства =.
  • Нажмем функциональную клавишу F3. Появится окно Вставка имени. Выберем имя Приход и щелкнем Ок. Формула примет вид: =Приход.
  • Наберем знак минус (-).
  • Вновь нажмем клавишу F3. В диалоговом окне Вставка имени выберем имя Расход и щелкнем Ок. Формула примет вид: =Приход-Расход.
  • Нажмем сочетание клавиш Shift+Ctrl+Enter. Во всех ячейках блока появится формула {=Приход-Расход}.

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

Ввод формулы массива. Разумеется, формулу массива можно вводить и без использования имен. Скопируйте блок А1:С5 в А8:С12 и повторите все шаги. Выделите блок D9:D12. В этом блоке активной ячейкой является D12. Наберите знак равенства =. Выделите блок В9:В12, наберите знак минус -, выделите блок С9: С12, нажмите сочетание клавиш Shift+Ctrl+Enter. Во всех ячейках блока появится формула {=В9:В12-С9:С12}. Мы получили две идентичные таблицы.

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

Изменение формулы массива. Попытайтесь очистить одну из ячеек, занятую формулой. Например, выделите ячейку D9 и нажмите клавишу Del. В этом случае появляется сообщение «Нельзя изменять часть массива», откуда следует, что удалить блок можно только целиком.

Чтобы отредактировать формулу массива, необходимо выделить все ячейки массива, активизировать строку формул и удалить фигурные скобки. По окончании редактирования формулы, следует нажать комбинацию клавиш Shift+Ctrl+Enter, чтобы внести изменения. Теперь содержимое всех ячеек массива изменится в соответствии с внесенными изменениями. (Попробуйте, например, ввести формулу {Приход-Расход-1} , потом отменить это.)

Расширение и сокращение диапазона, содержащего формулу массива.

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

  1. Выделить весь диапазон, содержащий формулу массива.
  2. Нажать клавишу F2, чтобы перейти в режим редактирования.
  3. Нажать комбинацию клавиш Ctrl+Enter. Это действие вводит отдельные формулы (не формулу массива) в каждую ячейку выделенного диапазона.
  4. Изменить выделение диапазона так, чтобы он включал новые ячейки (или исключал лишние).
  5. Нажать клавишу F2.
  6. Нажать комбинацию клавиш Shift+Ctrl+Enter.

Коррекция формулы при увеличении блока. Добавьте в обе таблицы на рабочем листе строку с данными: Год — 1996, приход — 240, расход 200. Необходимо посчитать прибыль за 1996. Раньше, когда формулы записывались в отдельные ячейки, поступили бы просто: скопировали бы формулу из ячейки D5 в D6. Проделаем это для первой таблицы.

Вместо ожидаемого 40 получим результат #ЗНАЧ!. Та же операция для второй таблицы даст правильный результат 40, но в строке формул мы увидим {=В13 : В16-С13 : С16} — образовался второй блок, что не является корректным решением нашей задачи. Отменим последнее действие.

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

  • Во вкладке Формулы-Диспетчер имен выделить Приход, и внизу окна в Диапазон изменить диапазон значений с В1:С5 на В1:С6. Для его изменения удалить имеющийся в окне диапазон и, перейдя на лист, выделить новый диапазон В1:С6. Точно также изменить диапазон для Расхода. После изменений диалоговое окно закрыть.
  • Выделить D2: D6, нажать клавишу F2 (редактирование) и, ничего не изменяя в формуле, нажать клавиши Shift+ Ctrl+Enter.

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

  • Выделить D8 :D13 и нажать клавишу F2 для редактирования фор­мулы.
  • Изменить в формуле ссылки на диапазоны ячеек с В8:В12 на В8:В13 и с С8: С12 на С8:С13. Нажать сочетание клавиш Shift+Ctrl+Enter.

Коррекция табличной формулы при уменьшении блока. Теперь необходимо удалить в каждой из таблиц строку для 1996 г. Для первой таблицы вновь изменить поименованные блоки (в результате в ячейке D6 результат отобразится как #Н/Д — недоступно). Выделить блок с формулой массива, нажать клавишу F2. Ввод закончить клавишами Ctrl+Enter.

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

Очистить последнюю строку таблицы. Выделить блок D2: D5, нажать клавишу F2, нажать клавиши Shift+Ctrl+Enter.

Аналогично поступить со второй таблицей:

  • Выделить диапазон с формулой массива.
  • Нажать комбинацию клавиш Ctrl+Enter, чтобы преобразовать формулу массива в обычную.
  • Удалить последнюю строчку таблицы.
  • Выделить диапазон для формулы массива, D9: D12.
  • Нажать F2 для ее редактирования. Используемые в формуле ссылки на диапазоны выделяются рамочками, закрашенными в разные цвета. Видно, что рамочки захватывают области удален­ной строки. Для корректировки ссылок в формуле можно умень­шить рамочки, схватив за любой нижний угол и подтянув к верху до последней используемой строки. Ссылки в формуле изменять­ся в соответствии с выделением. Но можно просто изменить в ссылках цифру 3 на 2 (В13 изменить на В12 и С13 на С12).
  • Закончить изменения клавишами Shift+Ctrl+Enter.

Задача 5.11. Создание массивов на основе значений ячеек диапазона.

На рис.98 показан рабочий лист, содержащий данные в диапазоне A1: С4.

Диапазон С8:F11 содержит массив, созданный на основе этих данных с помощью формулы {=А1:С4}

Массив в диапазоне С8 :F11 связан со значениями диапазона А1:С4. 

Если изменить какое-либо значение в последнем диапазоне, то автоматически изменится соответствующее значение в массиве.

Задача 5.12. Транспонирование массива.

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием и выполняется при помощи формулы массива и функции ТРАНСП.

Допустим, имеется двумерный массив ячеек (рис.99), который необходимо транспонировать.

Для размещения транспонированного массива следует выделить диапазон ячеек (рис. 100). Поскольку исходный массив ячеек состоял из 8 строк на 2 столбца, то необходимо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов. Далее в выделенном диапазоне ввести функцию транспонирования =ТРАНСП(, где в качестве аргумента функции является наш массив ячеек А1:В8. Заканчиваем ввод формулы комбинацией клавиш Ctrl+Shift+Enter и получаем «перевернутый массив» в качестве результата (рис. 101).

Задача 5.13. Таблица умножения.

Если вспомните детство, школу, свою тетрадку по математике… На обороте тетради на обложке была таблица умножения вот такого вида (рис. 102).

При помощи формул массива она вся делается в одно движение:

  • ввести два диапазона чисел от 1 до 10 в строке 1 и столбце А;
  • выделить диапазон В2:К11;
  • ввести формулу =А2:A11*В1:К1;
  • закончить ввод формулы комбинацией клавиш Ctrl+Shift+Enter, чтобы Excel воспринял ее как формулу массива. Результат пред­ставлен ниже (рис. 103).

Задача 5.14. Выборочное суммирование.

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

В данном случае формула массива синхронно пробегает по всем эле­ментам диапазонов СЗ:С21 и ВЗ:В21, проверяя, совпадают ли они с задан­ными значениями из ячеек G4 и G5.

Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом, суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

Можно применить и другой способ:

Нам необходимо просуммировать числа из диапазона D3:D21 (диапазон суммирования) при выполнении нескольких (двух) условий. Для каждой строки в случае одновременного равенства числа из диапазона СЗ:С21 числу G8, и числа из диапазона ВЗ:В21 числу G9, соответствующее число из диапазона суммирования будет суммироваться.

Для этого применим функцию СУММЕСЯИМН.

=СУММЕСЛИМН(D3:D21; СЗ:С21; G8; ВЗ:В21; G9)

На открывшейся форме СУММЕСЯИМН в окне Диапазон суммирования выбрать диапазон для суммирования (D3:D21). Для прописания условий в окне Диапазон_условия1 необходимо выбрать диапазон первого условия СЗ:С21, который равен критерию G8 — окно Условие1, в окне Диапазон_условия2 ввести диапазон второго условия ВЗ:В21, который должен равняться G9 — окно Условие2.

Попробуйте решить задачу двумя способами и сравните результат. 

Задача 5.15.

Вернемся к задаче обработки данных метеостанции (Задача 5.5). Для расчета количества засушливых месяцев, т.е. месяцев, когда выпало менее 10 мм осадков, очень удобно использовать функцию СЧЕТЕСЛИ. Однако с ее помощью нельзя получить количество месяцев, на протяжении которых количество осадков лежало бы в диапазоне от 20 до 80 (назовем такие месяцы нормальными). Для этого необходимо использовать дистрибутивные функции. Функции, которые можно применять к списку, называются дистрибутивными.

Скопируйте текст из ячейки А22 в ячейку А25 и откорректируйте его: «Количество нормальных месяцев». Сначала подсчитаем месяцы с нормальным количеством осадков. Будем использовать вспомогательный блок F3:Н14 тех же размеров, что и блок с исходными данными. В ячейку F3 вводится формула =ЕСЛИ(И(ВЗ>20; в3<80) ; 1; 0) и копируется в остальные ячейки блока F3:h24. В блоке выводятся нули и единицы.

Введенная формула является индикаторной функцией множества нормальных месяцев, т.е. 1 — выводится, когда количество осадков лежит в пределах между 20 и 80 мм и 0 — в противном случае. Для подсчета суммы нормальных месяцев вводим в ячейку F25 формулу =СУММ (F3:F14) и копируем ее в блок G25:Н25. Количество нормальных месяцев посчитано.

А теперь решим эту же задачу без использования вспомогательного блока. Введем в В25 табличную формулу {=СУММ (ЕСЛИ (ВЗ : В14>0;ЕСЛИ (ВЗ :В14<80; 1; 0) ; 0) ) } и скопируем ее в С25:D25. (Таким образом, компьютер, перебирая значения в указанном блоке, проверяет больше или меньше текущее число 20 и, если оно меньше, прибавляет к исходному число 0. В противном случае — проверяет выполнение второго условия (менее 80). Если и второе условие выполняется, то к исходному числу прибавляет 1. В противном случае — 0). Формула {=СУММ (ЕСЛИ (И(ВЗ>20;ВЗ<80) ;1;0) ) } не приведет к успеху, так как функция И не является дистрибутивной.

Этот пример показывает способ подсчёта в множестве количество элементов, удовлетворяющих определенному критерию.

Теперь вычислим суммарное количество осадков, выпавших и эти месяцы. Введите в А26 текст «Осадки в нормальные месяцы», в В26 — табличную формулу {=СУММ(ЕСЛИ(ВЗ:В14>20;ЕСЛИ(ВЗ:В14<80; ВЗ : В14; 0) ; 0) ) } и скопируйте ее в С26:D26. В Е25 и Е26 введите формулы для суммирования значений в строках (выделив блок В25:Е26, выберите значок Автосумма). Вы получите блок, показанный на рис. 105.

Задача 5.16.

В блоке A1:А10 записана числовая последовательность. Проверьте, является ли она возрастающей.

Решение. На новом листе запишите любую последовательность чисел в блок A1:А10.

Окончательное решение можно записать одной формулой, поместив его в ячейку А12.

{=ЕСЛИ(СУММ(ЕСЛИ(А2:А10-А1:А9>0;1;0))=СЧЕТ(A1:А10)-1; «возрастающая»; «не является возрастающей»)}

Разбор этой формулы:

  • А2:А10-А1:А9 (т.е., из А10 вычитается А9, из А9 вычитается А8 и т.д.) — образует блок, состоящий из первых разностей элементов исходного блока;
  • ЕСЛИ (А2 : А10-А1: А9>0; 1; 0) — составляет блок из индикаторов положительных первых разностей;
  • СУММ (ЕСЛИ (А2 :А10-А1 :А9>0; 1; 0) ) — считает количество ненулевых элементов в блоке индикаторов;
  • СЧЕТ (A1: А10)-1 — рассчитывает размер блока индикаторов, равный уменьшенному на 1 размеру исходною блока;
  • и, наконец, если количество ненулевых элементов в блоке инди­каторов равно размеру блока индикаторов, то последователь­ность возрастающая, иначе — нет.

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

Массивы Excel

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

Содержание статьи:

  • Что такое массив?
  • Измерения массива;
  • Массив констант;
  • Операции с массивами;
  • Резюме.

Определение массива

Чтобы понять, что такое массив, необходимо вспомнить, что такое переменная.

Переменная — область памяти, за которой закреплено определенной имя, например «x = 5» или «Имя = “Андрей”». Переменная всегда содержит только одно значение, т.е. не может быть разделена на более мелкие части. Данное определение больше подходит для переменных в языках программирования. Чтобы не отходить от темы Excel, переопределим его.

Каждый лист Excel является таблицей, за которой закреплена область в памяти компьютера. Таблица состоит из ячеек, которые имеют свой уникальный адрес. Сама ячейка не может быть разделена на более мелкие части, поэтому ее можно назвать переменной. Массив содержит набор переменных и имеет имя. То, что называют в Excel диапазоном, по своей сути является массивом: строка листа, столбец листа, количество ячеек >1, все это массивы данных. НО! Чтобы не вносить путаницу в определения функций и т.п. данные понятия необходимо разделять, т.к. приложение по-разному обрабатывает диапазоны и массивы.

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

Далее будет подробнее описана работа с массивами.

Измерения массива

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

  • Одномерным – 1 измерение;
  • Двумерным – 2 измерения.

Одномерный массив состоит из одного ряда значений. Это может быть строка или столбец.

Двумерный массив состоит из столбцов и из строк, т.е. представляется собой таблицу.

На рисунке ниже представлены оба вида массивов. Обратите внимание, что разные измерения имеют разные разделители («;» — для столбцов и «:» — строк).

Массив констант

Константа это та же переменная, только не меняющая значение. Если значение переменной можно поменять в любое время, то константа задается один раз и больше не меняется. Наверное, самая известная константа – число Пи.

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

  • {=A3:A7} – это обычный массив;
  • {1: 2: 3: 4: 5} – это массив констант.

Представьте, что Вам необходимо использовать в расчетах большой массив, состоящий из сотни констант, и использовать его нужно многократно. Набивать константы каждый раз для каждой формулы ручками – дело «неблагодарное». Поэтому, создайте синоним массива с помощью функции присвоения имен, расположенной на вкладке «Формулы» -> раздел «Определенные имена» -> кнопка «Диспетчер имен». В появившемся окне нажмите на кнопку «Создать», после чего появиться следующая форма:

  • Имя – имя диапазона;
  • Область – место, где данное имя будет доступно;
  • Примечание – комментарий. Текст, введенный здесь, будет высвечиваться при выборе имени массива из определенной для него области;
  • Диапазон – сам массив в виде ссылки на диапазон либо массив констант «={…}».

После заполнения формы, нажмите «OK».

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

О том, как их применять рассказывается дальше.

Операции с массивами

Перейдем, наконец, к примерам использования массивов.

Название автоЛитраж бака, лРасход, л на 100 км
Авто1506
Авто2607
Авто37010
Авто48012

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

При стандартных вычислениях формула составлялась бы так: =B2/C2*100. Затем ее необходимо протянуть. Мы получили готовый результат.

Повторим расчет, только с использованием массивов.

Сначала выделяем ячейки, в которых необходимо произвести расчет. Далее записываем в строку формул: =B2:B5/C2:C5*100, где “B2:B5” диапазон всех ячеек для литража, “C2:C5” диапазон всех ячеек для расхода. На данный момент это именно диапазоны, чтобы они превратились в массивы, нажмите одновременно клавиши Ctrl + Shift + Enter. Формула автоматически будет заключена в фигурные скобки, а расчет появиться во всех предварительно выделенных ячейках. Результат тот же, что и в первом варианте.

Следовательно, уже навязывается вопрос о смысле использования массивов.

В данном конкретном примере, что использовать – разницы нет, если только Вы не хотите защитить ячейки от случайного или намеренно изменения. Поясним. Когда к какому-то диапазону применяется массив, то затем никакой элемент этого массива не может быть отдельно изменен либо удален. Если попробовать произвести эти действия, приложение Excel выдаст ошибку. Чтобы избежать ее, выделите весь диапазон, к которому применен массив, а затем измените строку формул либо удалите ее полностью, после подтвердите изменения для всех элементов нажатием Ctrl + Shift + Enter.

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

Используя стандартные формулы, подсчет производиться двумя действиями:

  1. Рассчитать километраж для каждого авто;
  2. Просуммировать все имеющиеся результаты.

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

Выберите ячейку, в которую хотели бы записать результат. В нее впишите уже применяемую формулу, но в качестве аргумента функции СУММ. Подтвердите использование массива  нажатие Ctrl + Shift + Enter . Должно получиться следующее: {=СУММ(B2:B5/C2:C5*100)}.

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

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

Применим массив, который мы создавали в начале урока: Имя_диапазона.

Данный массив является одномерным и имеет размерность 5, т.к. содержит пять элементов. Перенесем значения массива в ячейки книги. Для этого выделим диапазон A1:E1, в строку формул введем имя массива и нажмем Ctrl + Shift + Enter. Получим результат:

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

Теперь поступим по-другому. Выделите вертикальный диапазон A1:A5 и введите в него массив. Во все ячейки диапазона загрузиться только первый элемент массива. Это произошло от того, что созданный нами массив является горизонтальным, а не вертикальным.

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

  1. Заполните числами несколько ячеек подряд по горизонтали и вертикали;
  2. Создайте функцию СУММ;
  3. Для первого аргумента функции укажите горизонтальный диапазон, для второго вертикальный;
  4. Нажмите на кнопку «вставить функцию» рядом со строкой формул;
  5. В окне аргументов функции посмотрите предварительные результаты для каждого аргумента. Те разделите, которые использует программа, необходимо использовать и Вам.

Рассмотрим еще один пример операций с массивами, а именно совместное использование горизонтальных, вертикальных и двумерных массивов.

Для начала создадим одномерный вертикальный массив ={1: 1: 1} и умножим его на 5, после чего узнаем сумму произведений его элементов.

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

Теперь умножим тот же диапазон на горизонтальный массив ={5; 5}.

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

Снова изменим условия и из вертикального массива сделаем двумерный массив ={1; 1: 1; 1: 1; 1}. Перемножим на ={5; 5}. И вот оно! Результат не поменялся. Почему? Потому что в случае использования двумерных массивов их элементы сопоставляются другим элементам массива по их порядковым номерам в равных измерениях.

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

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

Имейте в виду, что к массивам можно применять все формулы, которые применяются в стандартных ситуациях, включая логические. Например, в случае, описанном выше, когда перемножаются массивы с разными размерностями в одинаковых измерениях, чтобы избежать возврата ошибки, необходимо изменить формулу – {=СУММ(ЕСЛИОШИБКА(A1:C3*E1:F1;0))}.

Резюме

  1. Горизонтальный массив – это массив, ссылающийся на ячейки одной сроки либо содержащий константы, разделенные символом «;».
  2. Вертикальный массив – массив, ссылающий на ячейки одного столбца либо содержащий константы, разделенные символом «:».
  3. Размерность массива – количество элементов массива в одном измерении.
  4. Одномерный массив – либо горизонтальный либо вертикальный массив;
  5. Двумерный массив – вертикальный массив, содержащий в себе горизонтальные массивы одной размерности.
  6. Ввод массива ВСЕГДА необходимо подтверждать нажатием клавиш Ctrl + Shift + Enter.
  7. Нельзя изменить отдельный элемент массива. Изменению подлежать сразу все его элементы.
  8. В случае применения к массиву переменной, переменная будет сопоставлена каждому элементу массива.
  9. В случае применения к горизонтальному массиву вертикального массива, все их элементы будут сопоставлены друг другу.
  10. В случае применения массивов друг к другу, когда хоть один из них является двумерным, элементы массивов сопоставляются между собой по порядковым номера в равных измерениях (одинаковому расположению в массиве).
  11. При применении массивов друг к другу, в случае, когда хоть один из них является двумерным, размерности массивов должны совпадать.
  • < Назад
  • Вперёд >

Похожие статьи:

Новые статьи:

  • Критерий Манна-Уитни
  • Подключение MySQL в Excel
  • Подключение Excel к SQL Server

Если материалы office-menu. ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

Добавить комментарий

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

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

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

  • Подсчет ячеек, отвечающих определенным условиям
  • Формулы массива Excel с несколькими функциями
  • Формула массива для подсчета всех символов в диапазоне
  • Формула массива для подсчета определенных символов в диапазоне
  • Суммарные значения в каждой второй или N -й -й строке
  • Выполнять различные вычисления над числами в разных диапазонах
  • Пользовательские функции в формулах массива Excel
  • Формулы массива Excel — ограничения и альтернативы

Пример 1.

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

Введение функций СУММЕСЛИ и СЧЁТЕСЛИ, а также их множественных аналогов в Excel 2007 сделало использование формул массива излишним во многих сценариях. Однако есть задачи, где формула массива является наиболее эффективным, если не единственным решением.

Предположим, у вас есть 2 столбца чисел, столбец A (запланированный) и столбец B (фактический). И вы хотите подсчитать, во сколько раз столбец B больше или равен столбцу A, когда значение в столбце B больше 0.

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

Итак, вы выражаете условия как (B2:B10>=A2:A10) и (B2:B10>0), соединяете их с помощью звездочки (*), которая действует как оператор И в формулах массива, и включаете это выражение в аргументе функции СУММ:

=СУММ((B2:B10>=A2:A10) * (B2:B10>0))

Не забудьте нажать Ctrl + Shift + Enter, чтобы правильно ввести формулу массива Excel .

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

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

Итак, у нас есть два массива логических значений, где ИСТИНА равняется 1, а ЛОЖЬ равняется 0. Поскольку мы используем в формуле оператор массива И (*), СУММ будет суммировать только те строки, которые имеют TRUE (1) в обоих массивах, как показано на скриншоте ниже:

Примечание. Не все функции Excel, поддерживающие массивы, могут преобразовывать ИСТИНА и ЛОЖЬ в 1 и 0. В более сложных формулах массива вам может потребоваться использовать двойное тире (—), которое технически называется двойным унарным оператором, для преобразования нечисловых значений. Логические значения в числа.

Включение двойного тире в приведенную выше формулу также не принесет никакого вреда, оно просто обезопасит вас: =СУММ(--(B2:B10>=A2:A10) * (B2:B10> 0))

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

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

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

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

=MAX(IF(( salesmen_range =» name «) * ( products_range =» name «), sales_range ,»»))

Предположим, что имена продавцов находятся в столбце A, названия продуктов находятся в столбце B, а продажи — в столбце C, следующая формула возвращает наибольшую продажу Майк приготовил для яблок :

=MAX(IF(($A$2:$A$9="Майк") * ($B$2:$B$9="яблоки"), $C$2: $C$9,""))

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

На снимке экрана выше используются следующие формулы массива Excel (не забудьте нажать Ctrl + Shift + Enter, чтобы ввести их правильно):

Максимум: =МАКС(ЕСЛИ(($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,""))

Всего: =СУММ(ЕСЛИ(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,""))

Пример 3.

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

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

Формула проста:

=СУММ(ДЛСТР( диапазон ))

Вы используете функцию ДЛСТР, чтобы вернуть длину текстовой строки в каждой отдельной ячейке, а затем вы используете функцию СУММ, чтобы сложите эти числа.

Например, формула массива =СУММ(ДЛСТР(A1:A10)) вычисляет общее количество всех символов с пробелами в диапазоне A1:A10.

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

Если вы хотите узнать, сколько раз данный символ или группа символов встречается в указанном диапазоне ячеек, формула массива с функцией ДЛСТР может снова помочь. В этом случае формула немного сложнее:

=СУММ((ДЛСТР( диапазон ) — ДЛСТР(ПОДСТАВИТЬ( диапазон , символов , «»))) / ДЛСТР( символов ))

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

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

=СУММ((ДЛСТР(B2:B5) - ДЛСТР(ПОДСТАВИТЬ(B2:B5, E1, ""))) / ДЛСТР(E1))

В основе этой формулы функция ПОДСТАВИТЬ заменяет все вхождения указанного символа с пустой строкой («»).

Замещенная строка передается функции ДЛСТР для получения длины строки без интересующего символа (в данном примере «K»). А затем вы вычитаете длину замененной строки из длины исходной строки. Результатом этой операции является массив счетчиков символов, по одному на ячейку, который вы делите на длину подстроки. Операция деления не является строго необходимой, когда вы считаете один символ, как в этом примере. Но если вы подсчитываете количество вхождений определенной подстроки в диапазоне (например, заказы, начинающиеся с «KM»), вам нужно разделить на длину подстроки, иначе каждый символ в подстроке будет учитываться отдельно.

Наконец, вы используете SUM для сложения отдельных счетчиков.

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

-й -й строке

Если вы хотите суммировать каждую вторую или каждую N -ю -ю строку в таблице, вам понадобятся функции SUM и MOD, объединенные в формулу массива:

=СУММ((—(ОСТАТ(СТРОКА( диапазон ), n )=0)) * ( диапазон ))

Функция ОСТАТ возвращает остаток, округленный до ближайшего целого числа после того, как число разделить на делитель. Мы встраиваем в него функцию ROW, чтобы получить номер строки, а затем делим его на N -я строка (например, на 2 для суммирования каждой второй ячейки) и проверьте, равен ли остаток нулю. Если да, то ячейка суммируется.

Двойной унарный оператор (—) используется для преобразования нечисловых логических значений ИСТИНА и ЛОЖЬ, возвращаемых функцией ОСТАТ, в 1 и 0, чтобы функция СУММ могла складывать числа.

Например, для подсчета каждой второй ячейки в диапазоне B2:B10 используется одна из следующих формул:

Подсчет четных строк (2 nd , 4 th и т. д.):
=SUM((--(MOD(ROW($B2:B10), 2)=0))*(B2:B10))

Подсчет нечетных строк (1 st , 3 rd , и т.д. ):
=СУММ((--(MOD(СТРОКА($B2:B10), 2)=1))*(B2:B10))

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

=СУММ((--(MOD((СТРОКА($B$2:$ B$7)-СТРОКА($B$2)), E1)=E1-1))*($B$2:$B$7))

Где E1 — это каждая N строка, которую вы хотите суммировать.

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

Эту задачу можно легко решить, используя следующую вложенную формулу ЕСЛИ: ЕСЛИ( В8>=11, В3, ЕСЛИ(В8>=1, В2, «»)))))

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

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

=СУММ(B8*(B2:B6) * (--(B8>=ЗНАЧ(ЛЕВО(A2:A6,НАЙТИ(" ",A2:A6))))) * (--(B8<= ЗНАЧЕНИЕ(ПРАВО(A2:A6,ДЛСТР(A2:A6) - НАЙТИ(" до ",A2:A6)-ДЛСТР(" до" )))))

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

=СУММ(B8*{20;18;16;13;12}*{1;1;1;1;0}*{0;0;0;1;1})

Первые 5 -элементный массив — это не что иное, как числа цен в ячейках B2:B6. И последние 2 массива 0 и 1 определяют, какая цена будет использоваться в расчете. Итак, главный вопрос — откуда берутся эти два массива и что они означают?

Формула включает 2 функции ЗНАЧЕНИЕ: (B8>=ЗНАЧ())*(B8<=ЗНАЧ())

Функция 1 st проверяет, является ли значение в ячейке B8 больше или равно меньшему граница каждого диапазона "количество единиц" и 2 и проверяется, меньше ли B8 или равно верхней границе каждого диапазона (для извлечения значений верхней и нижней границ используются комбинации функций ВЛЕВО, ВПРАВО, НАЙТИ и ДЛСТР). В результате вы получите 0, если условие не выполнено, и 1, если условие выполнено.

Наконец, функция SUM умножает количество в B8 на каждый элемент массива цен (B2:B6) и на каждый элемент массивов 0 и 1. Поскольку умножение на 0 всегда дает 0, в окончательном расчете используется только одна цена — элемент, который имеет 1 в последних двух массивах.

В этом примере количество умножается на 13 долларов США, что соответствует диапазону сумм от 50 до 100. Это 4-й -й -й элемент массива цен (ячейка B5 в диапазоне B2:B6), и это единственный элемент, который имеет 1 в последних двух массивах.

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

  • Величины в A2:A6 должны составлять непрерывный диапазон, чтобы ни одно значение не было пропущено.
  • Все количества в A2:A6 должны быть введены в этом конкретном шаблоне "X to Y", потому что он жестко запрограммирован в формуле. Если ваши количества вводятся другим способом, скажем, «1 - 10», то замените «до» на «-» в формуле.

Если вы хотите отобразить сообщение « Вне диапазона », когда количество, введенное в ячейку B8, выходит за пределы допустимого диапазона, включите следующий оператор IF: ,НАЙТИ(" ",A2))), B8<=ЗНАЧ(ПРАВО(A6,ДЛСТР(A6) - НАЙТИ(" до ",A6)-ДЛСТР(" до" )))), СУММ(…))

Эта сложная функция If делает очень простую вещь - проверяет, больше или равно значение в ячейке B8 нижней границе A2 и меньше или равно верхней границе A6. Другими словами, он проверяет это условие: И(B8>=1, B8<=200) .

Полная формула выглядит следующим образом:

=ЕСЛИ(И(B8>=ЗНАЧ(ЛЕВО(A2,НАЙТИ(" ",A2))), B8<=ЗНАЧ(ПРАВО(A6,ДЛСТР(A6)- НАЙТИ(" до ",A6)-ДЛСТР(" до" )))), СУММ(B8*(B2:B6)*(--(B8>=ЗНАЧ(ЛЕВО(A2:A6,НАЙТИ(" ",A2 :A6)))))*(--(B8<=ЗНАЧ(ПРАВО(A2:A6,ДЛСТР(A2:A6)-НАЙТИ(" до ",A2:A6)-ДЛСТР(" до" )))) )), "Вне диапазона")

Пример 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 , то есть Нил в этом примере.
  • GetCellColor($C$2:$C$10)=GetCellColor($E$2) - мы используем пользовательскую функцию, чтобы получить цвет ячеек от C2 до C10 и проверить, соответствует ли он цветовому образцу в E2, зеленый в данном случае.

Обратите внимание, что мы используем двойной унарный оператор с обоими приведенными выше выражениями для преобразования логических значений ИСТИНА и ЛОЖЬ, которые они возвращают, в 1 и 0, с которыми может работать функция СУММ. Если выполняются оба условия, т. е. возвращаются две единицы, SUM складывает сумму продаж из соответствующей ячейки в столбце C.

Дополнительные примеры формул массива Excel

А вот еще несколько примеров формул массива Excel, которые могут оказаться полезными.

  • Поиск по нескольким критериям — мощная функция ИНДЕКС/ПОИСКПОЗ, которая может выполнять поиск по значениям в 2 разных столбцах.
  • Получить все повторяющиеся значения в диапазоне поиска — как получить все экземпляры значения поиска.
  • Поиск с учетом регистра для всех типов данных - говорит сам за себя 🙂
  • Массив ПРОСМОТР и СУММ - как искать в массиве и суммировать все совпадающие значения.
  • Функция СУММ Excel в формулах массива — несколько примеров, демонстрирующих использование функции СУММ в формулах массива.
  • Примеры формул 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 после загрузки, чтобы разрешить запуск макроса.

На сегодня все, спасибо за внимание!

Массивы VBA — автоматизация Excel

В этой статье

  • Краткий обзор массивов VBA
    • Массивы
  • Краткие примеры массивов VBA
  • Преимущества массивов? - Скорость!
  • Создать / объявить массив (DIM)
    • Статический массив
    • Динамический массив
      • Варианты массивы
      • Неиседенные динамические массивы
    • Redim vs. Redim7
    • Declifing Actrays Declified
  • STEVERVENTERAP
    • Получить значение массива
  • Присвоение диапазона на массив
    • Выходной массив на диапазон
  • 2D / Многомерные массивы
  • Многомерные примеры массива
    • Пример массива
    • Array. Размер
      • Функции UBound и LBound
      • Функция длины массива
    • Циклический массив
      • Для каждого массива Цикл
      • Циклический цикл 2D-массива
    • Другие задачи массива
      • Clear Array
      • Массив подсчета
      • Удалить дубликаты
      • Фильтр
      • Функция isarray
      • СОЕДИНЕННЫ
    • Использование массивов в Access VBA

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

    VBA Array Quick Sheet

    Массивы

    Создать

    Dim arr(1 To 3) As Variant
    arr(1) = «один»
    arr(2) = «два»
    arr(3) = «три»

    Создать из Excel

    Уменьшить размер ячейки (от 1 до 3) как вариант
    Затемнить ячейку как диапазон, i как целое число
    i = LBound(arr)
    Для каждой ячейки в диапазоне («A1:A3»)
    i = i + 1
    arr(i) = cell.value
    Следующая ячейка

    Читать все элементы

    Dim i as Long
    For i = LBound(arr) To UBound(arr)
    MsgBox arr(i)
    Next i

    Erase

    Erase arr

    Array to String

    Dim sName As String
    sName = Join(arr, “:”)

    Reserve aDrr Size

    3 100)

    Set Value

    arr(1) = 22

    Примеры массива VBA Quick

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

     Sub ArrayExample()
    Dim strNames (от 1 до 4) как строка

    strNames(1) = "Шелли"
    strNames(2) = "Стив"
    strNames(3) = "Нима"
    strNames(4) = "Хосе"

    msgbox strNames(3)
Конец суб

    Здесь мы создали одномерный строковый массив: strNames размера четыре (может содержать четыре значения) и присвоили четыре значения. Наконец, мы отображаем третье значение в окне сообщения.

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

     

    Однако давайте рассмотрим пример, который покажет истинную мощь массива:

     Sub ArrayExample2()
    Dim strNames (от 1 до 60000) как строка
    Дим и пока

    Для я = 1 до 60000
        strNames(i) = Cells(i, 1).Value
    Далее я
Конец суб

    Здесь мы создали массив, который может содержать 60 000 значений, и мы быстро заполнили массив из столбца A рабочего листа.

    Преимущества массива? - Скорость!

    Вы можете подумать о массивах, подобных листам Excel:

    • Каждая ячейка (или элемент в массиве) может содержать собственное значение
    • Доступ к каждой ячейке (или элементу массива) можно получить по ее положению в строке и столбце.
      • Рабочий лист Упр. ячейки (1,4). значение = «Строка 1, столбец 4»
      • Пример массива arrVar(1,4) = "Строка 1, Столбец 4"

    Так зачем возиться с массивами? Почему бы просто не читать и не записывать значения непосредственно в ячейки Excel? Одно слово: Скорость!

    Чтение/запись в ячейки Excel — медленный процесс. Работать с массивами намного быстрее!

    Создание/объявление массива (Dim)

    Примечание. Массивы могут иметь несколько «измерений». Для простоты мы начнем с работы только с одномерными массивами. Позже в этом руководстве мы познакомим вас с многомерными массивами.

    Статический массив

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

    Примечание. Если размер массива не изменится, используйте статический массив.

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

    Вы можете явно объявить начальную и конечную позиции массива:

     Sub StaticArray1()

    'Создает массив с позициями 1,2,3,4
    Dim arrDemo1 (от 1 до 4) как строка
    
    'Создает массив с позициями 4,5,6,7
    Dim arrDemo2 (от 4 до 7) до тех пор, пока
    
    'Создает массив с позициями 0,1,2,3
    Dim arrDemo3 (от 0 до 3) As Long

End Sub

    Или вы можете ввести только размер массива:

     Sub StaticArray2()

    'Создает массив с позициями 0,1,2,3
    Dim arrDemo1(3) как строка

Конец суб

    Важно! Обратите внимание, что по умолчанию массивы начинаются с позиции 0. Таким образом, Dim arrDemo1(3) создает массив с позициями 0,1,2,3.

    Вы можете объявить Option Base 1 в верхней части вашего модуля, чтобы вместо этого массив начинался с позиции 1:

     Option Base 1

Субстатический массив3()

    'Создает массив с позициями 1,2,3
    Dim arrDemo1(3) как строка

End Sub

    Однако я считаю, что гораздо проще (и менее запутанно) просто явно объявить начальную и конечную позиции массивов.

    Кодирование VBA стало проще

    Прекратите искать код VBA в Интернете. Узнайте больше об AutoMacro — конструкторе кода VBA, который позволяет новичкам создавать процедуры с нуля с минимальными знаниями в области программирования и множеством функций, позволяющих сэкономить время для всех пользователей!

    Узнать больше!!

    Динамический массив

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

    Существует два способа объявления динамического массива.

    Variant Arrays

    Первый способ объявить динамический массив — установить тип массива Variant .

     Dim arrVar() As Variant

    С Variant Array размер массива определять не нужно. Размер подстроится автоматически. Просто помните, что массив начинается с позиции 0 (если вы не добавите Option Base 1 в начало вашего модуля)

     Sub VariantArray()
    Dim arrVar() как вариант
    
    'Определить значения (размер = 0,1,2,3)
    arrVar = Массив (1, 2, 3, 4)
    
    'Изменить значения (размер = 0,1,2,3,4)
    arrVar = Массив("1а", "2а", "3а", "4а", "5а")

    'Выходная позиция 4 ("5a")
    MsgBox arrVar(4)

Конец суб
    Невариантные динамические массивы

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

     Sub DynamicArray1()
    Dim arrDemo1() как строка

    'Изменяет размеры массива с позициями 1,2,3,4
    ReDim arrDemo1 (от 1 до 4)
    
End Sub

    Сначала вы объявляете массив, аналогичный статическому массиву, за исключением того, что вы опускаете размер массива:

     Dim arrDemo1() As String

    Теперь, когда вы хотите установить размер массива, используйте команду ReDim для изменения размера массива:

     'Изменяет размеры массива с позициями 1,2,3,4
ReDim arrDemo1(1 To 4)

    ReDim изменяет размер массива. Читайте ниже о разнице между ReDim и ReDim Preserve.

    ReDim против ReDim Preserve

    При использовании команды ReDim вы удаляете все существующие значения из массива. Вместо этого вы можете использовать ReDim Preserve для сохранения значений массива:

     'Изменяет размеры массива с позициями 1,2,3,4 (сохранение существующих значений)
ReDim Preserve arrDemo1(1 To 4)

    Упрощенное объявление массивов

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

    Программирование на языке VBA | Генератор кода работает на вас!

    Установить значения массива

    Установить значения массива очень просто.

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

     Sub ArrayExample()
    Dim strNames (от 1 до 4) как строка

    strNames(1) = "Шелли"
    strNames(2) = "Стив"
    strNames(3) = "Нима"
    strNames(4) = "Хосе"
End Sub

    С Variant Array вы можете определить весь массив одной строкой (практично только для небольших массивов):

     Sub ArrayExample_1Line()
    Dim strNames() как вариант

    strNames = Array("Шелли", "Стив", "Нима", "Хосе")

End Sub

    Если вы попытаетесь определить значение для несуществующего расположения массива, вы получите сообщение об ошибке Subscript Out of Range:

     strNames(5) = "Shannon"

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

    Получить значение массива

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

     Range("A1").Value = strNames(1)
    Диапазон ("A2"). Значение = strNames (2)
    Диапазон ("A3"). Значение = strNames (3)
    Диапазон ("A4"). Значение = strNames (4)

    Назначить диапазон массиву

    Чтобы назначить диапазон массиву, вы можете использовать цикл:

     Sub RangeToArray()
    Dim strNames (от 1 до 60000) как строка
    Дим и пока

    Для я = 1 до 60000
        strNames(i) = Cells(i, 1).Value
    Далее я
End Sub

    Это будет перебирать ячейки A1: A60000, назначая значения ячеек массиву.

    Выходной массив в диапазон

    Или вы можете использовать цикл для присвоения массива диапазону:

     Для я = 1 до 60000
        Ячейки(i, 1).Value = strNames(i)
    Далее я

    Это сделает обратное: назначит значения массива ячейкам A1:A60000

    2D/многомерные массивы

    До сих пор мы работали исключительно с одномерными (1D) массивами. Однако массивы могут иметь до 32 измерений.

    Думайте о массиве 1D как об одной строке или столбце ячеек Excel, о массиве 2D, как о целом рабочем листе Excel с несколькими строками и столбцами, а о массиве 3D, как о целой книге, содержащей несколько листов, каждый из которых содержит несколько строк и столбцов. (Вы также можете думать о трехмерном массиве как о кубике Рубика).

    Автомакрос | Окончательная надстройка VBA | Нажмите для бесплатной пробной версии!

    Примеры многомерных массивов

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

    Пример одномерного массива

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

     ПодмассивEx_1d()
    Dim strNames (от 1 до 60000) как строка
    Дим и пока
 
    'Присвоить значения массиву
    Для я = 1 до 60000
        strNames(i) = Cells(i, 1). Value
    Далее я
    
    'Вывод значений массива в диапазон
    Для я = 1 до 60000
        Листы("Выход").Ячейки(i, 1).Value = strNames(i)
    Далее я
Конец суб

    Пример двумерного массива

    Эта процедура содержит пример двумерного массива:

     Sub ArrayEx_2d()
    Dim strNames (от 1 до 60000, от 1 до 10) как строка
    Dim i As Long, j As Long
 
    'Присвоить значения массиву
    Для я = 1 до 60000
        Для j = от 1 до 10
            strNames(i, j) = Cells(i, j).Value
        Следующий j
    Далее я
    
    'Вывод значений массива в диапазон
    Для я = 1 до 60000
        Для j = от 1 до 10
            Листы("Выход").Ячейки(i, j).Value = strNames(i, j)
        Следующий j
    Далее я
Конец суб

    Пример трехмерного массива

    Эта процедура содержит пример трехмерного массива для работы с несколькими листами:

     Sub ArrayEx_3d()
    Dim strNames (от 1 до 60000, от 1 до 10, от 1 до 3) в виде строки
    Dim i As Long, j As Long, k As Long
 
    'Присвоить значения массиву
    Для к = от 1 до 3
        Для я = 1 до 60000
            Для j = от 1 до 10
                strNames(i, j, k) = Sheets("Лист" & k). Cells(i, j).Value
            Следующий j
        Далее я
    Следующий k
    
    'Вывод значений массива в диапазон
    Для к = от 1 до 3
        Для я = 1 до 60000
            Для j = от 1 до 10
                Листы («Вывод» и k). Ячейки (i, j). Значение = strNames (i, j, k)
            Следующий j
        Далее я
    Следующий k
Конец суб

    Автомакрос | Окончательная надстройка VBA | Нажмите для бесплатной пробной версии!

    Длина/размер массива

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

    Функции UBound и LBound

    Первым шагом к получению длины/размера массива является использование функций UBound и LBound для получения верхней и нижней границ массива:

     Sub UBoundLBound()
    Dim strNames (от 1 до 4) как строка
    
    MsgBox UBound(strNames)
    MsgBox LBound(strNames)
End Sub

    Вычитание двух (и добавление 1) даст вам длину:

     GetArrLength = UBound(strNames) - LBound(strNames) + 1

    Функция длины массива

    Вот функция для получения одномерного длина массива:

     Публичная функция GetArrLength(a As Variant) As Long
   Если Пусто(а) Тогда
      GetArrLength = 0
   Еще
      GetArrLength = UBound(a) - LBound(a) + 1
   Конец, если
Завершить функцию

    Нужно рассчитать размер двумерного массива? Ознакомьтесь с нашим руководством: Расчет размера массива.

    Циклический обход массива

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

     Sub ArrayExample_Loop1()
    Dim strNames (от 1 до 4) как строка
    Дим и пока

    strNames(1) = "Шелли"
    strNames(2) = "Стив"
    strNames(3) = "Нима"
    strNames(4) = "Хосе"
    
    Для i = от 1 до 4
        MsgBox strNames(i)
    Далее я
Конец суб

    Однако, если вы не знаете размер массива (если массив динамический), вы можете использовать функции LBound и UBound из предыдущего раздела:

     Sub ArrayExample_Loop2()
    Dim strNames (от 1 до 4) как строка
    Дим и пока

    strNames(1) = "Шелли"
    strNames(2) = "Стив"
    strNames(3) = "Нима"
    strNames(4) = "Хосе"
    
    Для i = LBound(strNames) To UBound(strNames)
        MsgBox strNames(i)
    Далее я
Конец суб

    Автомакрос | Окончательная надстройка VBA | Нажмите для бесплатной пробной версии!

    Для каждого цикла массива

    Второй метод — с циклом для каждого. Это перебирает каждый элемент в массиве:

     Sub ArrayExample_Loop3()
    Dim strNames (от 1 до 4) как строка
    Тусклый элемент

    strNames(1) = "Шелли"
    strNames(2) = "Стив"
    strNames(3) = "Нима"
    strNames(4) = "Хосе"
    
    Для каждого элемента в strNames
        Элемент MsgBox
    Следующий элемент
Конец суб

    Цикл For Each Array Loop будет работать с многомерными массивами в дополнение к одномерным массивам.

    Цикл через двумерный массив

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

     Sub ArrayExample_Loop4()
    Dim strNames (от 1 до 4, от 1 до 2) как строка
    Dim i As Long, j As Long

    strNames(1, 1) = "Шелли"
    strNames(2, 1) = "Стив"
    strNames(3, 1) = "Нима"
    strNames(4, 1) = "Хосе"
    
    strNames(1, 2) = "Шелби"
    strNames(2, 2) = "Стивен"
    strNames(3, 2) = "Немо"
    strNames(4, 2) = "Джесси"
    
    Для j = LBound(strNames, 2) To UBound(strNames, 2)
        Для i = LBound(strNames, 1) To UBound(strNames, 1)
            MsgBox strNames(i, j)
        Далее я
    Следующий j
Конец суб

    Другие задачи массива

    Очистить массив

    Чтобы очистить весь массив, используйте оператор Erase:

     Erase strNames

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

     Sub ArrayExample()
    Dim strNames (от 1 до 4) как строка

    strNames(1) = "Шелли"
    strNames(2) = "Стив"
    strNames(3) = "Нима"
    strNames(4) = "Хосе"

    Стереть имена страниц
End Sub

    Кроме того, вы также можете изменить размер массива, чтобы изменить его размер, очистив часть массива:

     ReDim strNames(1 to 2)

    Это изменяет размер массива до размера 2, удаляя позиции 3 и 4.

    AutoMacro | Окончательная надстройка VBA | Нажмите для бесплатной пробной версии!

    Подсчет массива

    Вы можете подсчитать количество позиций в каждом измерении массива с помощью функций UBound и LBound (описанных выше).

    Вы также можете подсчитать количество введенных элементов (или элементов, соответствующих определенным критериям), прокручивая массив.

    Этот пример будет перебирать массив объектов и подсчитывать количество непустых строк, найденных в массиве:

     Sub ArrayLoopandCount()
    Dim strNames (от 1 до 4) как строка
    Dim i As Long, n As Long

    strNames(1) = "Шелли"
    strNames(2) = "Стив"
    
    Для i = LBound(strNames) To UBound(strNames)
        Если strNames(i) <> "" Тогда
            п = п + 1
        Конец, если
    Далее я
    
    MsgBox n & "обнаружены непустые значения."
End Sub

    Удалить дубликаты

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

    Фильтр

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

     Sub Filter_Match()
 
    'Определить массив
    Dim strNames как вариант
    strNames = Array("Стив Смит", "Шеннон Смит", "Райан Джонсон")
 
    'Массив фильтров
    Dim strSubNames как вариант
    strSubNames = Фильтр(strNames, "Кузнец")
    
    'Подсчет отфильтрованного массива
    MsgBox "Найдено " и UBound(strSubNames) - LBound(strSubNames) + 1 & " имена."
 
Конец суб

    Функция IsArray

    Вы можете проверить, является ли переменная массивом, используя функцию IsArray:

     Sub IsArrayEx()

    'Создает массив с позициями 1,2,3
    Dim arrDemo1(3) как строка
    
    'Создает обычную строковую переменную
    Dim str As String
    
    MsgBox IsArray(arrDemo1)
    MsgBox IsArray(строка)

End Sub

    AutoMacro | Окончательная надстройка VBA | Нажмите для бесплатной пробной версии!

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

    Вы можете быстро «объединить» весь массив с помощью функции присоединения:

     Sub Array_Join()
    Dim strNames (от 1 до 4) как строка
    Dim joinNames As String

    strNames(1) = "Шелли"
    strNames(2) = "Стив"
    strNames(3) = "Нима"
    strNames(4) = "Хосе"
    
    joinNames = Присоединиться (strNames, ",")
    MsgBox
End Sub

    Разделить строку на массив

    Функция разделения VBA разделит строку текста на массив, содержащий значения из исходной строки. Давайте рассмотрим пример:

     Sub Array_Split()
    Dim Names () как строка
    DimjoinNames As String
    
    joinNames = "Шелли, Стив, Нема, Хосе"
    Имена = Разделить (объединенные имена, ",")

    Имена MsgBox(1)
End Sub

    Здесь мы разбиваем эту строку текста «Шелли, Стив, Нема, Хосе» на массив (размер 4), используя разделитель-запятую (,).

    Const Массив

    Массив не может быть объявлен как константа в VBA. Однако вы можете обойти это, создав функцию для использования в качестве массива:

     ' Определить массив констант
Функция КонстантныйМассив()
    КонстантныйМассив = Массив(4, 12, 21, 100, 5)
Конечная функция

' Получить значение ConstantArray
Подпрограмма получения значений ()
    Массив констант MsgBox(3)
End Sub

    Копировать массив

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

     Sub CopyArray()

    Dim Arr1 (от 1 до 100) As Long
    Dim Arr2 (от 1 до 100) As Long
    Дим и пока
    
    'Создать массив1
    Для i = от 1 до 100
        Арр1(я) = я
    Далее я
    
    'КопироватьМассив1 в Массив2
    Для i = от 1 до 100
        Арр2(я) = Арр1(я)
    Далее я
    
    Сообщение Arr2(74)

Конец суб

    Автомакрос | Окончательная надстройка VBA | Нажмите для бесплатной пробной версии!

    Транспонирование

    Нет встроенной функции VBA, позволяющей транспонировать массив. Однако мы написали функцию для транспонирования 2D-массива. Прочтите статью, чтобы узнать больше.

    Массив возврата функции

    Разработчики VBA часто задают вопрос, как создать функцию, возвращающую массив. Я думаю, что большинство трудностей решается с помощью Variant Arrays. Мы написали статью на тему: Массив возврата функции VBA.

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

    Большинство приведенных выше примеров массивов работают точно так же в Access VBA, как и в Excel VBA.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *