Vba работа с массивами excel: Все про массивы в VBA читать в пошаговом руководстве по Excel

Фильтрация двумерного массива на VBA

Function ArrAutofilterNew(ByRef arr, ParamArray args() As Variant) As Variant
    ' Новая версия функции ArrAutofilter, от января 2021 года.  © ExcelVBA.ru
    ' Получает по ссылке массив ARR для фильтрации
    ' и список критериев фильтрации в формате "3=маска текста" (номер столбца, "=", искомое значение)
    ' Возвращает двумерный массив с подходящими строками
    
    On Error Resume Next
    ArrAutofilterNew = False ' возвращаемое значение в случае ошибки
    If UBound(args) = -1 Then Debug. Print "Array filtering error: filters required": Exit Function
    ReDim Filters(1 To UBound(args) + 1, 1 To 2)
 
    Dim i&, ColumnToCheck&, FiltersCount&, j&, ro&, RowsCount&:  Err.Clear: i& = UBound(arr, 2)
    If Err.Number > 0 Then Debug.Print "Array filtering error: two dimensional array required": Exit Function
 
    For i& = LBound(args) To UBound(args)    ' перебираем все параметры фильтрации
        If Not IsMissing(args(i&)) Then
            If args(i&) Like "#*=*" Then ' распознаем параметры фильтрации
                FiltersCount& = FiltersCount& + 1
                Filters(FiltersCount&, 1) = Val(Split(args(i&), "=")(0)) ' столбец массива
                Filters(FiltersCount&, 2) = Split(args(i&), "=", 2)(1) ' маска для значения
            Else ' неверно заданный фильтр
                Debug.Print "ArrAutofilterNew error: invalid filter «" & args(i&) & "»"
            End If
        End If
    Next i&
    If FiltersCount& = 0 Then Debug. Print "Array filtering error: all filters are empty": Exit Function
 
    ReDim arrCheck(LBound(arr, 1) To UBound(arr, 1)) As Boolean ' для результатов проверки
    For i = LBound(arr, 1) To UBound(arr, 1)    ' перебираем все строки массива, и проверяем их
        arrCheck(i) = True
        For j& = 1 To FiltersCount&    ' перебираем все параметры фильтрации
            If Not (arr(i, Filters(j&, 1)) Like Filters(j&, 2)) Then arrCheck(i) = False: Exit For
        Next j&
        RowsCount& = RowsCount& - arrCheck(i) ' увеличиваем счётчик подходящих строк на 1
    Next i
    If RowsCount& = 0 Then Exit Function ' выход, если нет ни одной подходящей строки в массиве
    
    ReDim newarr(1 To RowsCount&, LBound(arr, 2) To UBound(arr, 2)) ' формируем новый массив
    For i = LBound(arr, 1) To UBound(arr, 1)    ' снова перебираем все строки массива
        If arrCheck(i) Then ' если строка ранее помечена как подходящая
            ro& = ro& + 1 ' вычисляем номер строки в новом массиве
            For j = LBound(arr, 2) To UBound(arr, 2)
                newarr(ro&, j) = arr(i, j) ' заполняем массив значениями из исходного
            Next j
        End If
    Next i
    ArrAutofilterNew = newarr ' возвращаем результат
    Erase arrCheck
End Function

Sub FilterExample()
    On Error Resume Next
    Dim arr As Variant
 
    ' отбираем только нужные строки из диапазона a2:t200,
    ' где текст в третьем столбце начинается с "asy"
    arr = ArrAutofilterNew(Range("a2:t200"). Value, "3=asy*")
 
    ' создаем лист, вставляем на него результат
    Worksheets.Add.Range("a1").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End Sub

Лабораторная работа №5 (алгоритмы обработки массивов и их реализация)

Содержание

Цель работы

Освоить алгоритмы обработки массивов в среде VBA.

Постановка задания (задача №1)

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

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

Варианты заданий

Образец выполнения (вариант №1)

Условие задания

В одномерном массиве из $10$ целых чисел найти минимальный и максимальный элементы массива и заменить первый элемент массива суммой минимального и максимального элементов.

Решение задачи с использованием макросов на VBA

Закодируем $3$ процедуры, каждую из которых можно будет вызвать с помощью макроса:

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

Макрос на VBA in Excel (статический массив)

Visual Basic

Макрос на VBA in Excel (динамический массив)

Visual Basic

Макрос на VBA in Excel (очистка результатов)

Visual Basic

В итоге мы создали следующие макросы:

Перечень доступных макросов

Результаты работы программы

ВНИМАНИЕ	Для получения всех необходимых процедур своего варианта пишите на наш электронный адрес [email protected]
💡 Время нашего ответа обычно составляет не более 10 минут.

Постановка задания (задача №2)

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

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

Варианты заданий

№	Условие задачи
1	Дана целочисленная прямоугольная матрица. Определить количество строк, не содержащих ни одного нулевого элемента.
2	Дана целочисленная прямоугольная матрица. Определить количество столбцов, не содержащих ни одного нулевого элемента.
3	Дана целочисленная прямоугольная матрица. Определить количество столбцов, содержащих хотя бы один нулевой элемент.
4	Дана целочисленная квадратная матрица. Определить произведение элементов в тех строках, которые не содержат отрицательных элементов.
5	Дана целочисленная квадратная матрица. Определить сумму элементов в тех столбцах, которые не содержат отрицательных элементов.
6	Дана целочисленная прямоугольная матрица. Определить сумму элементов в тех строках, которые содержат хотя бы один отрицательный элемент.
7	Дана целочисленная прямоугольная матрица Найти сумму элементов в тех строках, которые содержат хотя бы один отрицательный элемент.
8	Дана целочисленная прямоугольная матрица. Найти сумму элементов в тех столбцах, которые содержат хотя бы один отрицательный элемент.
9	Дана целочисленная прямоугольная матрица. Определить номер первого из столбцов, содержащих хотя бы один нулевой элемент.
10	Дана целочисленная прямоугольная матрица. Найти номер первого из столбцов, не содержащих ни одного отрицательного элемента.

Образец выполнения (вариант №1)

Условие задания

Решение задачи с использованием макросов на VBA

Так как в условии ничего не было сказано о способе решения и об ограничениях, то решим данную задачу с использованием статического двумерного массива размером $3 \ \times \ 5$.

В итоге нам потребуется закодировать $2$ процедуры:

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

➡ Процедура №2 автоматизирует процесс уборки прошлых результатов, что позволяет более быстро проводить тестирование макросов.

Макрос на VBA in Excel (статическая прямоугольная матрица)

Visual Basic

Макрос на VBA in Excel (очистка результатов)

Visual Basic

В итоге мы создали следующие макросы:

Перечень доступных макросов

Результаты работы программы

ВНИМАНИЕ	Для получения всех процедур своего варианта пишите на наш электронный адрес [email protected]
💡 Время нашего ответа обычно составляет не более 10 минут.

Примеры макросов Visual Basic для работы с массивами

Резюме

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

Дополнительная информация

Корпорация Майкрософт предоставляет примеры программирования только для иллюстрации, без явных или подразумеваемых гарантий. Это включает, но не ограничивается, подразумеваемые гарантии товарного состояния или пригодности для конкретной цели. В этой статье предполагается, что вы знакомы с демонстрируемым языком программирования и инструментами, которые используются для создания и отладки процедур. Инженеры службы поддержки Майкрософт могут помочь объяснить функциональность конкретной процедуры, но они не будут изменять эти примеры, чтобы обеспечить дополнительную функциональность или создавать процедуры в соответствии с вашими конкретными требованиями. ПРИМЕЧАНИЕ. В процедурах Visual Basic для приложений слова после апострофа (‘) являются комментариями.

Чтобы заполнить массив, а затем скопировать его на рабочий лист

1. Откройте новую книгу и вставьте лист модуля Visual Basic.

2. Введите следующий код на листе модуля.

   Sub Sheet_Fill_Array()
      Dim myarray как вариант
      myarray = Массив (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
      Range("a1:a10").Value = Application.Transpose(myarray)
   Конец сабвуфера
    

3. org/ListItem»>

   Выберите Лист1.

4. В меню «Инструменты» выберите «Макрос», а затем нажмите «Макросы».

5. В диалоговом окне «Макрос» выберите Sheet_Fill_Array, а затем нажмите кнопку «Выполнить».

Для получения значений из рабочего листа и заполнения массива

1. Введите значения на Лист1 в ячейки A1:A10.

2. На листе модуля Visual Basic введите следующий код:

   Подпрограмма from_sheet_make_array()
      Затемнить этот массив как вариант
      этот массив = Диапазон ("a1: a10").  Значение
   
      counter = 1 'структура цикла для просмотра массива
      Пока счетчик <= UBound (этот массив)
         MsgBox этот массив (счетчик, 1)
         счетчик = счетчик + 1
      Венд
   Конец сабвуфера
    

3. Выберите Лист1.

4. В меню «Инструменты» выберите «Макрос», а затем нажмите «Макросы».

5. В диалоговом окне "Макрос" выберите from_sheet_make_array и нажмите кнопку "Выполнить".

Для передачи и получения массива

1. org/ListItem">

   На листе модуля введите следующий код:

   Sub pass_array()
      Затемнить этот массив как вариант
      этотмассив = Выбор.Значение
      receive_array (этот массив)
   Конец сабвуфера
   
   Sub Receive_array (этот массив)
      счетчик = 1
      Пока счетчик <= UBound (этот массив)
         MsgBox этот массив (счетчик, 1)
         счетчик = счетчик + 1
      Венд
   Конец сабвуфера
    

2. Выберите Лист1 и выделите диапазон A1:A10.

3. В меню «Инструменты» выберите «Макрос», а затем нажмите «Макросы».

4. В диалоговом окне "Макрос" выберите pass_array, а затем нажмите кнопку "Выполнить".

Для сравнения двух массивов

1. Создайте два именованных диапазона на Листе1. Назовите один диапазон1 и другой диапазон2.

   Например, выделите диапазон ячеек A1:A10 и назовите его range1; выделите диапазон ячеек B1:B10 и назовите его range2.

2. Введите следующий код на листе модуля.

   Подпрограмма compare_two_array()
      Затемнить этот массив как вариант
      Dim thatarray как вариант
   
      этотмассив = Диапазон ("диапазон1"). Значение
      тотмассив = Диапазон ("диапазон2"). Значение
      счетчик = 1
      Пока счетчик <= UBound (этот массив)
         х = этот массив (счетчик, 1)
         y = тот массив (счетчик, 1)
         Если х = у Тогда
            MsgBox "да"
         Еще MsgBox "нет"
         Конец, если
         счетчик = счетчик + 1
      Венд
   Конец сабвуфера
    

3. org/ListItem">

   Выберите Лист2.

4. В меню «Инструменты» выберите «Макрос», а затем нажмите «Макрос».

5. В диалоговом окне «Макрос» нажмите compare_two_array и нажмите «Выполнить».

   Вы увидите одно окно сообщения для каждого сравнения.

Для заполнения динамического массива

1. На листе модуля введите следующий код:

   Sub fill_array ()
   
      Затемнить этот массив как вариант
      number_of_elements = 3 'количество элементов в массиве
   
      'необходимо исправить ниже, чтобы установить размер
      Изменить размер этого массива (от 1 до числа_элементов) как целое число
      'изменить этот размер массива
      счетчик = 1
      наполнить = 7
      Для counter = 1 To number_of_elements
         этот массив (счетчик) = fillmeup
      Следующий счетчик
   
      counter = 1 'этот цикл показывает, что было заполнено
      Пока счетчик <= UBound (этот массив)
         MsgBox этот массив (счетчик)
         счетчик = счетчик + 1
      Венд
   
   Конец сабвуфера
    

2. org/ListItem">

   В меню «Инструменты» выберите «Макрос», а затем нажмите «Макросы».

3. В диалоговом окне "Макрос" выберите fill_array, а затем нажмите кнопку "Выполнить".

ПРИМЕЧАНИЕ. Изменение переменной "number_of_elements" определит размер массива.

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

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

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

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

Что такое массив в Excel?

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

Например, если вы поместите список покупок на неделю в формат массива Excel, он будет выглядеть так:

{"Молоко", "Яйца", "Сливочное масло", "Кукурузные хлопья"}

Затем, если вы выберете ячейки с A1 по D1, введите вышеуказанный массив со знаком равенства (=) перед ним в строке формул и нажмите CTRL + SHIFT + ENTER, вы получите следующий результат:

Вы только что создали одномерный горизонтальный массив. Пока ничего страшного, верно?

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

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

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

Формулы массива доступны во всех версиях Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 и более ранних версиях.

А теперь, похоже, самое время создать свою первую формулу массива.

Простой пример формулы массива Excel

Предположим, у вас есть несколько товаров в столбце B, их цены в столбце C, и вы хотите рассчитать общую сумму всех продаж.

Конечно, ничто не мешает вам подсчитать промежуточные итоги в каждой строке сначала с помощью чего-то такого простого, как =B2*C2 , а затем суммируйте эти значения:

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

1. Выберите пустую ячейку и введите в нее следующую формулу:

   =СУММ(B2:B6*C2:C6)

2. Нажмите сочетание клавиш CTRL + SHIFT + ENTER, чтобы завершить формулу массива.

   После этого Microsoft Excel заключает формулу в {фигурные скобки}, что является визуальным признаком формулы массива.

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

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

Зачем использовать формулы массива в Excel?

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

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

Как ввести формулу массива в Excel (Ctrl + Shift + Enter)

Как вы уже знаете, комбинация 3 клавиш CTRL+SHIFT+ENTER — это волшебное прикосновение, которое превращает обычную формулу в формулу массива.

При вводе формулы массива в Excel необходимо помнить о 4 важных вещах:

1. Когда вы закончите вводить формулу и одновременно нажмете клавиши CTRL SHIFT ENTER, Excel автоматически заключит формулу в {фигурные скобки}. Когда вы выбираете такую ​​ячейку (ячейки), вы можете увидеть фигурные скобки в строке формул, которые дают вам понять, что там находится формула массива.
2. Ввод скобок вокруг формулы вручную не работает. Вы должны нажать сочетание клавиш Ctrl+Shift+Enter, чтобы завершить формулу массива.
3. Каждый раз, когда вы редактируете формулу массива, фигурные скобки исчезают, и вам нужно снова нажать Ctrl+Shift+Enter, чтобы сохранить изменения.
4. Если вы забудете нажать Ctrl+Shift+Enter, ваша формула будет вести себя как обычная формула и обрабатывать только первые значения в указанном(ых) массиве(ах).

Поскольку все формулы массива Excel требуют нажатия Ctrl + Shift + Enter, их иногда называют формулами CSE .

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

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

В приведенном выше примере, чтобы просмотреть промежуточные итоги по всем продуктам, выберите B2:B6*C2:C6, нажмите F9 и получите следующий результат.

Примечание. Обратите внимание, что вы должны выбрать часть формулы до нажатия F9, иначе клавиша F9 просто заменит вашу формулу вычисленными значениями.

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

Существует несколько функций массива Excel, предназначенных для возврата массивов с несколькими ячейками, например ТРАНСП, ТРЕНД, ЧАСТОТА, ЛИНЕЙН и т. д.

Другие функции, такие как СУММ, СРЗНАЧ, АГРЕГАТ, МАКС, МИН, могут вычислять выражения массива при вводе в одну ячейку с помощью Ctrl + Shift + Enter.

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

Пример 1. Формула массива из одной ячейки

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

Обычно вы добавляете дополнительный столбец, скажем, столбец D, в котором рассчитывается изменение продаж для каждого продукта по формуле вроде =C2-B2 , а затем находите максимальное значение в этом дополнительном столбце =MAX(D:D ) .

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

=MAX(C2:C6-B2:B6)

Пример 2. Формула массива с несколькими ячейками в Excel

В предыдущем примере SUM предположим, что вы должны платить 10 % налога с каждой продажи и хотите рассчитать сумму налога для каждого продукта с помощью одной формулы.

Выберите диапазон пустых ячеек, скажем, D2:D6, и введите следующую формулу в строке формул:

=B2:B6 * C2:C6 * 0,1

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

Пример 3. Использование функции массива Excel для возврата массива из нескольких ячеек

Как уже упоминалось, Microsoft Excel предоставляет несколько так называемых «функций массива», которые специально разработаны для работы с массивами из нескольких ячеек. TRANSPOSE — одна из таких функций, и мы собираемся использовать ее для транспонирования приведенной выше таблицы, то есть преобразования строк в столбцы.

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

В нашем примере формула:

=ТРАНСП($A$1:$D$6)

Результат будет выглядеть примерно так:

Вот как вы используете ТРАНСП в качестве формулы массива CSE в Excel 2019 и более ранних версиях. В Dynamic Array Excel это также работает как обычная формула. Чтобы узнать о других способах транспонирования в Excel, ознакомьтесь с этим руководством: Как переключать столбцы и строки в Excel.

Как работать с формулами массива с несколькими ячейками

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

1. Выберите диапазон ячеек, где вы хотите вывести результаты до ввода формулы.
2. Чтобы удалить формулу массива с несколькими ячейками, либо выделите все содержащие ее ячейки и нажмите DELETE, либо выделите всю формулу в строке формул, нажмите DELETE, а затем нажмите Ctrl + Shift + Enter.
3. Нельзя редактировать или перемещать содержимое отдельной ячейки в формуле массива, а также вставлять новые ячейки или удалять существующие ячейки из формулы массива с несколькими ячейками. Всякий раз, когда вы пытаетесь это сделать, Microsoft Excel выдает предупреждение «. Вы не можете изменить часть массива ».
4. Чтобы сжать формулу массива, т.е. применить ее к меньшему количеству ячеек, нужно сначала удалить существующую формулу, а затем ввести новую.
5. Чтобы расширить формулу массива, т.е. применить ее к большему количеству ячеек, выбрать все ячейки, содержащие текущую формулу, плюс пустые ячейки, где вы хотите ее иметь, нажать F2, чтобы переключиться в режим редактирования, настроить ссылки в формуле и нажать Ctrl + Shift + Enter, чтобы обновить его.
6. Нельзя использовать формулы массива с несколькими ячейками в таблицах Excel.
7. Формулу массива с несколькими ячейками следует ввести в диапазоне ячеек того же размера, что и результирующий массив, возвращаемый формулой. Если ваша формула массива Excel создает массив, превышающий выбранный диапазон, лишние значения не будут отображаться на листе. Если массив, возвращаемый формулой, меньше выбранного диапазона, в дополнительных ячейках появятся ошибки #Н/Д.

Если ваша формула может возвращать массив с переменным числом элементов, введите его в диапазоне, равном или превышающем максимальный массив, возвращаемый формулой, и заключите формулу в функцию ЕСЛИОШИБКА, как показано в этом примере.

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

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

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

Существует 3 типа констант массива:

1. Константа горизонтального массива

Константа горизонтального массива находится в строке. Чтобы создать константу массива строк, введите значения, разделенные запятыми, и заключите их в фигурные скобки, например {1,2,3,4}.

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

Чтобы ввести горизонтальный массив в электронную таблицу, выберите соответствующее количество пустых ячеек в строке, введите формулу = {1,2,3,4} в строке формул и нажмите Ctrl + Shift + Enter. Результат будет примерно таким:

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

2. Константа вертикального массива

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

={11; 22; 33; 44}

3. Константа двумерного массива

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

= {"а", "б", "в"; 1, 2, 3}

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

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

1. Элементы константы массива

   Константа массива может содержать числа, текстовые значения, логические значения (ИСТИНА и ЛОЖЬ) и значения ошибок, разделенные запятыми или точками с запятой.

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

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

2. Именование констант массива

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

   • Перейдите на вкладку Формулы > Определенные имена и щелкните Определить имя . Либо нажмите Ctrl + F3 и щелкните New .
   • Введите имя в поле Имя
   • В поле Относится к введите элементы массива констант, заключенные в фигурные скобки со знаком равенства (=) перед ним. Например:

     = {"Вс", "Пн", "Вт", "Ср", "Чт", "Пт", "Сб"}

   • Нажмите OK, чтобы сохранить именованный массив и закрыть окно.

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

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

3. Предотвращение ошибок

   Если ваша константа массива работает неправильно, проверьте наличие следующих проблем:

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

Использование констант массива в формулах Excel

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

Пример 1. Сумма N наибольших/наименьших чисел в диапазоне

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

Затем вы берете функцию НАИБОЛЬШИЙ или МАЛЕНЬКИЙ, указываете весь диапазон ячеек в первом параметре и включаете константу массива во второй. Наконец, вставьте его в функцию SUM, например:

.

Сумма трех самых больших чисел: =СУММ(НАИБОЛЬШИЙ(диапазон, {1,2,3}))

Сумма 3 наименьших чисел: =СУММ(МАЛЕНЬКИЙ(диапазон, {1,2,3}))

Не забудьте нажать Ctrl + Shift + Enter, так как вы вводите формулу массива, и вы получите следующий результат:

Аналогичным образом можно вычислить среднее значение N наименьших или наибольших значений в диапазоне:

Среднее из трех верхних чисел: =СРЗНАЧ(БОЛЬШОЙ(диапазон, {1,2,3}))

Среднее 3 последних чисел: =СРЗНАЧ(МАЛЕНЬКИЙ(диапазон, {1,2,3}))

Пример 2. Формула массива для подсчета ячеек с несколькими условиями

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

Проще всего использовать формулу СЧЁТЕСЛИМН с несколькими условиями. Однако, если вы хотите включить много продуктов, ваша формула СЧЁТЕСЛИМН может стать слишком большой. Чтобы сделать его более компактным, вы можете использовать СЧЁТЕСЛИМН вместе с СУММ и включить константу массива в один или несколько аргументов, например:

=СУММ(СЧЁТЕСЛИМН(диапазон1, "критерий1", диапазон2, {"критерий1", "критерий2"}))

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

=СУММ(СЧЁТЕСЛИМН(B2:B9, "салли", C2:C9, {"яблоки", "лимоны"}))

Наш образец массива состоит только из двух элементов, так как цель состоит в том, чтобы продемонстрировать подход. В ваши формулы реального массива вы можете включать столько элементов, сколько требует ваша бизнес-логика, при условии, что общая длина формулы не превышает 8 192 символов в Excel 2019 - 2007 (1024 символа в Excel 2003 и ниже) и ваш компьютер мощный достаточно для обработки больших массивов. Дополнительные сведения см. в ограничениях формул массива.

А вот пример расширенной формулы массива, которая находит сумму всех совпадающих значений в таблице: СУММ и ВПР с константой массива.

Операторы И и ИЛИ в формулах массива Excel

Оператор массива сообщает формуле, как вы хотите обрабатывать массивы — используя логику И или ИЛИ.

• Оператор И — это звездочка (*), которая является символом умножения. Он указывает Excel возвращать ИСТИНА, если ВСЕ условия оцениваются как ИСТИНА.
• Оператор ИЛИ — знак плюс (+). Он возвращает ИСТИНА, если ЛЮБОЕ из условий в данном выражении оценивается как ИСТИНА.

Формула массива с оператором И

В этом примере мы находим сумму продаж, где продавец Майк И продукт Яблоки :

=СУММ((A2:A9="Майк") * (B2:B9="Яблоки") * (C2:C9))

или

=СУММ(ЕСЛИ(((A2:A9="Майк") * (B2:B9="Яблоки")), (C2:C9)))

Технически эта формула умножает элементы трех массивов в одинаковых позициях. Первые два массива представлены значениями ИСТИНА и ЛОЖЬ, которые являются результатами сравнения A2:A9 с Майком и B2:B9 с "Яблоками". Третий массив содержит числа продаж из диапазона C2:C9. Как и любая математическая операция , умножение преобразует ИСТИНА и ЛОЖЬ в 1 и 0 соответственно. А поскольку умножение на 0 всегда дает ноль, результирующий массив имеет 0, когда одно или оба условия не выполняются. Если оба условия выполняются, соответствующий элемент из третьего массива получает в окончательный массив (например, 1 * 1 * C2 = 10). Итак, результатом умножения является этот массив: {10; 0; 0; 30; 0; 0; 0; 0}. Наконец, функция СУММ суммирует элементы массива и вернуть результат 40.

Формула массива Excel с оператором ИЛИ

Следующая формула массива с оператором ИЛИ (+) суммирует все продажи, в которых продавцом является Майк ИЛИ товар Яблоки:

=СУММ(ЕСЛИ(((A2:A9="Майк") + (B2:B9="Яблоки")), (C2:C9)))

В этой формуле вы складываете элементы первых двух массивов (это условия, которые вы хотите проверить) и получаете ИСТИНА (>0), если хотя бы одно условие оценивается как ИСТИНА; FALSE (0), когда все условия оцениваются как FALSE. Затем ЕСЛИ проверяет, больше ли результат сложения 0, и если да, то СУММ складывает соответствующий элемент третьего массива (C2:C9).

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

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

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

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

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

Следующая формула сработает:

=СУММ(--(МЕСЯЦ(A2:A10)=1))

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

Если вас интересует другой месяц, замените 1 на соответствующее число. Например, 2 означает февраль, 3 — март и так далее. Чтобы сделать формулу более гибкой, вы можете указать номер месяца в какой-либо ячейке, как показано на скриншоте:

А теперь давайте проанализируем, как работает эта формула массива. Функция МЕСЯЦ возвращает месяц каждой даты в ячейках с A2 по A10, представленный порядковым номером, который создает массив {2;1;4;2;12;1;2;12;1}.

После этого каждый элемент массива сравнивается со значением в ячейке D1, которая в данном примере равна 1. Результатом этого сравнения является массив логических значений ИСТИНА и ЛОЖЬ. Как вы помните, вы можете выбрать определенную часть формулы массива и нажать F9, чтобы увидеть, чему соответствует эта часть:

Наконец, вы должны преобразовать эти логические значения в 1 и 0, которые может понять функция СУММ.