Массивы в экселе: Создание формулы массива — Служба поддержки Office

У меня есть файл XLS с несколькими листами с именами 1, 2, 3, 4 и т. д. Теперь мне нужна формула, которая приведет, например, к =’1′!M16, где имя листа, на который я ссылаюсь, основано на текущей…

У меня есть формула массива, которая ссылается на выпадающую ячейку ($AG$7), чтобы определить, какие ячейки следует оценивать. Это хорошо работает, однако мне нужно включить дополнительный элемент в…

Может ли существовать одна формула (возможно, формула массива), которая может указать, делится ли число в F1 на любое из чисел в ячейках (A1:D9)? С уважением.

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

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

У меня есть формула массива следующим образом: Public Function fafsa(inputRange As range, desks As range) As Integer Dim vArr() As Integer Dim i As Long ReDim vArr(1 To inputRange.Cells.Count, 1 To…

Я пытаюсь решить ошибку #DIV/0! , возникающую, когда формула дает результат 0 . Я использую Excel 2011 для Mac. Вот оригинальная формула: =SUM((AX11025*1)/AV11025) А вот модифицированная формула для…

Существует ли формула в Excel, которая будет выполнять поиск массива 2-D в отдельных листах внутри одной книги? У меня есть три таблицы, отформатированные одинаково с разными данными в таблицах….

Ранее я создал формулу массива Excel, такую как: X13: =SUM(IF($F13>h23:R13,1,0))+IF(F13>F9,1,0)+SUM(IF($F13>T13:U13,1,0))+IF(T13>U13,1,0) При применении этой формулы вниз по столбцу и…

есть ли формула в Excel, которая может преобразовать адрес MAC из формата 11:22:33:44:55:ab в 1122-3344-55ab ? Спасибо!

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

Автор Дмитрий Якушев На чтение 24 мин. Просмотров 53.7k.

Дональд Кнут

Список настолько же силен, как и его самое слабое звено

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

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

Введение

В этой статье подробно рассматриваются массивы на языке программирования Excel VBA. Она охватывает важные моменты, такие как:

• Зачем вам массивы
• Когда вы должны их использовать
• Два типа массивов
• Использование более одного измерения
• Объявление массивов
• Добавление значений
• Просмотр всех предметов
• Супер эффективный способ чтения Range в массив

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

Быстрые заметки

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

Массивы и циклы идут рука об руку. Наиболее распространенными циклами, которые вы используете с массивами, являются циклы For i и For Each.

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

Массив VBA — это тип переменной. Используется для хранения списков данных одного типа. Примером может быть сохранение списка стран или списка итогов за неделю.

В VBA обычная переменная может хранить только одно значение за раз.

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

' Может хранить только 1 значение за раз
Dim Student1 As Integer
Student1 = 55

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

В следующем примере у нас есть оценки пяти студентов

Мы собираемся прочитать эти отметки и записать их в Immediate Window.

Примечание. Функция Debug.Print записывает значения в Immediate Window. Для просмотра этого окна выберите View-> Immediate Window из меню (сочетание клавиш Ctrl + G).

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

Public Sub StudentMarks()

    With ThisWorkbook.Worksheets("Лист1")

        ' Объявите переменную для каждого студента
        Dim Student1 As Integer
        Dim Student2 As Integer
        Dim Student3 As Integer
        Dim Student4 As Integer
        Dim Student5 As Integer

        ' Читайте оценки студентов из ячейки
        Student1 = .Range("C2").Offset(1)
        Student2 = .Range("C2").Offset(2)
        Student3 = .Range("C2").Offset(3)
        Student4 = .Range("C2").Offset(4)
        Student5 = .Range("C2").Offset(5)

        ' Печать студенческих оценок
        Debug.Print "Оценки студентов"
        Debug.Print Student1
        Debug.Print Student2
        Debug.Print Student3
        Debug.Print Student4
        Debug.Print Student5

    End With

End Sub

Ниже приведен вывод из примера

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

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

Public Sub StudentMarksArr()

    With ThisWorkbook.Worksheets("Лист1")

        ' Объявите массив для хранения оценок для 5 студентов
        Dim Students(1 To 5) As Integer

        ' Читайте оценки учеников из ячеек C3: C7 в массив
        Dim i As Integer
        For i = 1 To 5
            Students(i) = .Range("C2").Offset(i)
        Next i

        ' Распечатывать оценки студентов из массива
        Debug.Print "Оценки студентов"
        For i = LBound(Students) To UBound(Students)
            Debug.Print Students(i)
        Next i

    End With

End Sub

Преимущество этого кода в том, что он будет работать для любого количества студентов. Если нам нужно изменить этот код для работы с 1000 студентами, нам нужно всего лишь изменить (от 1 до 5) на (от 1 до 1000) в декларации. В предыдущем примере нам нужно было добавить примерно пять тысяч строк кода.

Давайте проведем быстрое сравнение переменных и массивов. Сначала мы сравним процесс объявления.

 ' Объявляем переменные
        Dim Student As Integer
        Dim Country As String

  ' Объявляем массивы
        Dim Students(1 To 3) As Integer
        Dim Countries(1 To 3) As String

Далее мы сравниваем присвоение значения

    ' присвоить значение переменной
        Student1 = .Cells(1, 1) 

    ' присваивать значение первому элементу в массиве
        Students(1) = .Cells(1, 1)

Наконец, мы смотрим на запись значений

  ' Вывести значение переменной
        Debug.Print Student1

  ' Вывести значение первого студента в массиве
        Debug.Print Students(1)

Как видите, использование переменных и массивов очень похоже.

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

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

Типы массивов VBA

В VBA есть два типа массивов:

1. Статический — массив фиксированного размера.
2. Динамический — массив, в котором размер задается во время выполнения

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

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

Статический массив объявляется следующим образом

Public Sub DecArrayStatic()

    ' Создать массив с местоположениями 0,1,2,3
    Dim arrMarks1(0 To 3) As Long

    ' По умолчанию от 0 до 3, то есть местоположения 0,1,2,3
    Dim arrMarks2(3) As Long

    ' Создать массив с местоположениями 1,2,3,4,5
    Dim arrMarks1(1 To 5) As Long

    ' Создать массив с местоположениями 2,3,4 'Это редко используется
    Dim arrMarks3(2 To 4) As Long

End Sub

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

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

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

Public Sub DecArrayDynamic()

    ' Объявить динамический массив
    Dim arrMarks() As Long

    ' Установите размер массива, когда вы будете готовы
    ReDim arrMarks(0 To 5)

End Sub

Динамический массив не выделяется, пока вы не используете оператор ReDim. Преимущество в том, что вы можете подождать, пока не узнаете количество элементов, прежде чем устанавливать размер массива. Со статическим массивом вы должны указать размер заранее.

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

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

Public Sub AssignValue()

    ' Объявить массив с местоположениями 0,1,2,3
    Dim arrMarks(0 To 3) As Long

    ' Установите значение позиции 0
    arrMarks(0) = 5

    ' становите значение позиции 3
    arrMarks(3) = 46

    ' Это ошибка, так как нет местоположения 4
    arrMarks(4) = 99

End Sub

Номер места называется индексом. Последняя строка в примере выдаст ошибку «Индекс вне диапазона», так как в примере массива нет местоположения 4.

Использование функций Array и Split

Вы можете использовать функцию Array для заполнения массива списком элементов. Вы должны объявить массив как тип Variant. Следующий код показывает, как использовать эту функцию.

  Dim arr1 As Variant
    arr1 = Array("Апельсин", "Персик","Груша")

  Dim arr2 As Variant
    arr2 = Array(5, 6, 7, 8, 12)

Массив, созданный функцией Array, начнется с нулевого индекса, если вы не используете Option Base 1 в верхней части вашего модуля. Затем он начнется с первого индекса. В программировании, как правило, считается плохой практикой иметь ваши реальные данные в коде. Однако иногда это полезно, когда вам нужно быстро протестировать некоторый код. Функция Split используется для разделения строки на массив на основе разделителя. Разделитель — это символ, такой как запятая или пробел, который разделяет элементы.

Следующий код разделит строку на массив из трех элементов.

 Dim s As String
    s = "Красный,Желтый,Зеленый,Синий"

    Dim arr() As String
    arr = Split(s, ",")

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

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

Использование цикла For обеспечивает быстрый доступ ко всем элементам массива. Вот где сила использования массивов становится очевидной. Мы можем читать массивы с десятью значениями или десятью тысячами значений, используя те же несколько строк кода. В VBA есть две функции: LBound и UBound. Эти функции возвращают самый маленький и самый большой индекс в массиве. В массиве arrMarks (от 0 до 3) LBound вернет 0, а UBound вернет 3.

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

Public Sub ArrayLoops()

    ' Объявить массив
    Dim arrMarks(0 To 5) As Long

    ' Заполните массив случайными числами
    Dim i As Long
    For i = LBound(arrMarks) To UBound(arrMarks)
        arrMarks(i) = 5 * Rnd
    Next i

    ' Распечатайте значения в массиве
    Debug.Print "Место нахождения", "Значение"
    For i = LBound(arrMarks) To UBound(arrMarks)
        Debug.Print i, arrMarks(i)
    Next i

End Sub

Функции LBound и UBound очень полезны. Их использование означает, что наши циклы будут работать правильно с любым размером массива. Реальное преимущество заключается в том, что если размер массива изменяется, нам не нужно менять код для печати значений. Цикл будет работать для массива любого размера, пока вы используете эти функции.

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

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

В следующем коде значение метки изменяется, но оно не меняет значение в массиве.

 For Each mark In arrMarks
        ' Не изменит значение массива
        mark = 5 * Rnd
    Next mark

Цикл For Each отлично подходит для чтения массива. Как видите, лучше писать специально для двумерного массива.

  Dim mark As Variant
    For Each mark In arrMarks
        Debug.Print mark
    Next mark

Использование Erase

Функция Erase может использоваться для массивов, но она работает по-разному в зависимости от типа массива.

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

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

Давайте рассмотрим пример статического массива. Этот пример аналогичен примеру ArrayLoops в последнем разделе с одним отличием — мы используем Erase после установки значений. Когда значение будет распечатано, все они будут равны нулю.

Public Sub EraseStatic()

    ' Объявить массив
    Dim arrMarks(0 To 3) As Long

    ' Заполните массив случайными числами
    Dim i As Long
    For i = LBound(arrMarks) To UBound(arrMarks)
        arrMarks(i) = 5 * Rnd
    Next i

    ' ВСЕ ЗНАЧЕНИЯ УСТАНОВЛЕНЫ НА НОЛЬ
    Erase arrMarks

    ' Распечатайте значения - там все теперь ноль
    Debug.Print "Место нахождения", "Значение"
    For i = LBound(arrMarks) To UBound(arrMarks)
        Debug.Print i, arrMarks(i)
    Next i

End Sub

Теперь мы попробуем тот же пример с динамикой. После того, как мы используем Erase, все места в массиве были удалены. Нам нужно использовать ReDim, если мы хотим использовать массив снова.

Если мы попытаемся получить доступ к членам этого массива, мы получим ошибку «Индекс вне диапазона».

Public Sub EraseDynamic()

    ' Объявить массив
    Dim arrMarks() As Long
    ReDim arrMarks(0 To 3)

    ' Заполните массив случайными числами
    Dim i As Long
    For i = LBound(arrMarks) To UBound(arrMarks)
        arrMarks(i) = 5 * Rnd
    Next i

    ' arrMarks теперь освобожден. Места не существуют.
    Erase arrMarks

End Sub

ReDim с Preserve

Если мы используем ReDim для существующего массива, то массив и его содержимое будут удалены.

В следующем примере второй оператор ReDim создаст совершенно новый массив. Исходный массив и его содержимое будут удалены.

Sub UsingRedim()

    Dim arr() As String
    
    ' Установить массив в слоты от 0 до 2
    ReDim arr(0 To 2)
    arr(0) = "Яблоко"
    
    ' Массив с яблоком теперь удален
    ReDim arr(0 To 3)

End Sub

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

Когда мы используем Redim Preserve, новый массив должен начинаться с того же начального размера, например мы не можем сохранить от (0 до 2) до (от 1 до 3) или до (от 2 до 10), поскольку они являются различными начальными размерами.

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

Затем мы используем Preserve для увеличения размера массива, чтобы не потерять оригинальное содержимое.

Sub UsingRedimPreserve()

    Dim arr() As String
    
    ' Установить массив в слоты от 0 до 1
    ReDim arr(0 To 2)
    arr(0) = "Яблоко"
    arr(1) = "Апельсин"
    arr(2) = "Груша"
    
    ' Изменение размера и сохранение исходного содержимого
    ReDim Preserve arr(0 To 5)

End Sub

Из приведенных ниже снимков экрана видно, что исходное содержимое массива было «сохранено».

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

Использование Preserve с 2-мерными массивами

Preserve работает только с верхней границей массива.

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

Sub Preserve2D()

    Dim arr() As Long
    
    ' Установите начальный размер
    ReDim arr(1 To 2, 1 To 5)
    
    ' Изменить размер верхнего измерения
    ReDim Preserve arr(1 To 2, 1 To 10)

End Sub

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

В следующем коде мы используем Preserve для первого измерения. Запуск этого кода приведет к ошибке «Индекс вне диапазона»:

Sub Preserve2DError()

    Dim arr() As Long
    
    ' Установите начальный размер
    ReDim arr(1 To 2, 1 To 5)
    
    ' Ошибка «Вне диапазона»
    ReDim Preserve arr(1 To 5, 1 To 5)

End Sub

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

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

Sub Preserve2DRange()

    Dim arr As Variant
    
    ' Назначить диапазон массиву
    arr = Sheet1.Range("A1:A5").Value
    
    ' Preserve будет работать только на верхней границе
    ReDim Preserve arr(1 To 5, 1 To 7)

End Sub

Сортировка массива

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

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

Sub QuickSort(arr As Variant, first As Long, last As Long)
  
  Dim vCentreVal As Variant, vTemp As Variant
  
  Dim lTempLow As Long
  Dim lTempHi As Long
  lTempLow = first
  lTempHi = last
  
  vCentreVal = arr((first + last) / 2)
  Do While lTempLow <= lTempHi
  
    Do While arr(lTempLow) < vCentreVal And lTempLow < last
      lTempLow = lTempLow + 1
    Loop
    
    Do While vCentreVal < arr(lTempHi) And lTempHi > first
      lTempHi = lTempHi - 1
    Loop
    
    If lTempLow <= lTempHi Then
    
        ' Поменять значения
        vTemp = arr(lTempLow)

        arr(lTempLow) = arr(lTempHi)
        arr(lTempHi) = vTemp
      
        ' Перейти к следующим позициям
        lTempLow = lTempLow + 1
        lTempHi = lTempHi - 1
      
    End If
    
  Loop
  
  If first < lTempHi Then QuickSort arr, first, lTempHi
  If lTempLow < last Then QuickSort arr, lTempLow, last
  
End Sub

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

Sub TestSort()

    ' Создать временный массив
    Dim arr() As Variant
    arr = Array("Банан", "Дыня", "Персик", "Слива", "Яблоко")
  
    ' Сортировать массив
    QuickSort arr, LBound(arr), UBound(arr)

    ' Печать массива в Immediate Window(Ctrl + G)
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        Debug.Print arr(i)
    Next i

End Sub

Передача массива в Sub или функцию

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

Переход к процедуре с использованием ByRef означает, что вы передаете ссылку на массив. Таким образом, если вы измените массив в процедуре, он будет изменен, когда вы вернетесь.
 
Примечание. Когда вы используете массив в качестве параметра, он не может использовать ByVal, он должен использовать ByRef. Вы можете передать массив с помощью ByVal, сделав параметр вариантом.

' Передает массив в функцию
Public Sub PassToProc()
    Dim arr(0 To 5) As String
    ' Передать массив в функцию
    UseArray arr
End Sub

Public Function UseArray(ByRef arr() As String)
    ' Использовать массив
    Debug.Print UBound(arr)
End Function

Возвращение массива из функции

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

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

Следующие примеры показывают это:

Public Sub TestArray()

    ' Объявить динамический массив - не выделен
    Dim arr() As String
    ' Возврат нового массива
    arr = GetArray

End Sub

Public Function GetArray() As String()

    ' Создать и выделить новый массив
    Dim arr(0 To 5) As String
    ' Возвращаемый массив
    GetArray = arr

End Function

Двумерные массивы

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

Двумерный массив — это список списков. Если вы думаете об одной строке электронной таблицы как об одном измерении, то более одного столбца является двухмерным. На самом деле электронная таблица является эквивалентом двумерного массива. Он имеет два измерения — строки и столбцы.

Следует отметить одну маленькую вещь: Excel обрабатывает одномерный массив как строку, если вы записываете его в электронную таблицу. Другими словами, массив arr (от 1 до 5) эквивалентен arr (от 1 до 1, от 1 до 5) при записи значений в электронную таблицу.

На следующем рисунке показаны две группы данных. Первый — это одномерный массив, а второй — двухмерный.

Чтобы получить доступ к элементу в первом наборе данных (одномерном), все, что вам нужно сделать, это дать строку, например. 1,2, 3 или 4.

Для второго набора данных (двумерного) вам нужно указать строку И столбец. Таким образом, вы можете думать, что 1-мерное — это несколько столбцов, а одна строка и двухмерное — это несколько строк и несколько столбцов.

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

Вы объявляете двумерный массив следующим образом:

Dim ArrayMarks(0 To 2, 0 To 3) As Long

В следующем примере создается случайное значение для каждого элемента в массиве и печатается значение в Immediate Window.

Public Sub TwoDimArray()

    ' Объявить двумерный массив
    Dim arrMarks(0 To 3, 0 To 2) As String

    ' Заполните массив текстом, состоящим из значений i и j
    Dim i As Long, j As Long
    For i = LBound(arrMarks) To UBound(arrMarks)
        For j = LBound(arrMarks, 2) To UBound(arrMarks, 2)
            arrMarks(i, j) = CStr(i) & ":" & CStr(j)
        Next j
    Next i

    ' Вывести значения в массиве в Immediate Window
    Debug.Print "i", "j", "Знаечние"
    For i = LBound(arrMarks) To UBound(arrMarks)
        For j = LBound(arrMarks, 2) To UBound(arrMarks, 2)
            Debug.Print i, j, arrMarks(i, j)
        Next j
    Next i

End Sub

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

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

Этот макрос работает следующим образом:

• Входит в цикл i
• i установлен на 0
• цикл Enters j
• j установлен на 0
• j установлен в 1
• j установлен на 2
• Выход из цикла j
• i установлен в 1
• j установлен на 0
• j установлен в 1
• j установлен на 2
• И так до тех пор, пока i = 3 и j = 2

Заметьте, что LBound и UBound имеют второй аргумент 2. Это указывает, что это верхняя или нижняя граница второго измерения. Это начальное и конечное местоположение для j. Значение по умолчанию 1, поэтому нам не нужно указывать его для цикла i.

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

Использование For Each лучше использовать при чтении из массива.
Давайте возьмем код сверху, который выписывает двумерный массив.

 ' Для цикла For необходимо два цикла
    Debug.Print "i", "j", "Значение"
    For i = LBound(arrMarks) To UBound(arrMarks)
        For j = LBound(arrMarks, 2) To UBound(arrMarks, 2)
            Debug.Print i, j, arrMarks(i, j)
        Next j
    Next i

Теперь давайте перепишем его, используя цикл For Each. Как видите, нам нужен только один цикл, и поэтому гораздо проще написать:

 ' Использование For Each требует только одного цикла
    Debug.Print "Значение"
    Dim mark As Variant
    For Each mark In arrMarks
        Debug.Print mark
    Next mark

Использование цикла For Each дает нам массив только в одном порядке — от LBound до UBound. В большинстве случаев это все, что вам нужно.

Чтение из диапазона ячеек в массив

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

Public Sub ReadToArray()

    ' Объявить динамический массив
    Dim StudentMarks As Variant

    ' Считать значения в массив из первой строки
    StudentMarks = Range("A1:Z1").Value

    ' Запишите значения обратно в третий ряд
    Range("A3:Z3").Value = StudentMarks

End Sub

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

В следующем примере будут считаны примеры данных студента ниже из C3: E6 Лист1 и распечатаны в Immediate Window.

Public Sub ReadAndDisplay()

    ' Получить диапазон
    Dim rg As Range
    Set rg = ThisWorkbook.Worksheets("Лист1").Range("C3:E6")

    ' Создать динамический массив
    Dim StudentMarks As Variant

    ' Считать значения в массив из листа 1
    StudentMarks = rg.Value

    ' Вывести значения массива
    Debug.Print "i", "j", "Значение"
    Dim i As Long, j As Long
    For i = LBound(StudentMarks) To UBound(StudentMarks)
        For j = LBound(StudentMarks, 2) To UBound(StudentMarks, 2)
            Debug.Print i, j, StudentMarks(i, j)
        Next j
    Next i

End Sub

Как видите, первое измерение (доступное через i) массива — это строка, а второе — столбец. Чтобы продемонстрировать это, взглянем на значение 44 в Е4 данных образца. Это значение находится в строке 2 столбца 3 наших данных. Вы можете видеть, что 44 хранится в массиве в StudentMarks (2,3).

Как заставить ваши макросы работать на суперскорости

Если ваши макросы работают очень медленно, этот раздел будет очень полезным. Особенно, если вы имеете дело с большими объемами данных. В VBA это держится в секрете.

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

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

1. Скопируйте данные из ячеек в массив.
2. Измените данные в массиве.
3. Скопируйте обновленные данные из массива обратно в ячейки.

Например, следующий код будет намного быстрее, чем код ниже:

Public Sub ReadToArray()

    ' Считать значения в массив из первой строки
    Dim StudentMarks  As Variant
    StudentMarks = Range("A1:Z20000").Value

    Dim i As Long
    For i = LBound(StudentMarks) To UBound(StudentMarks)
        ' Обновление отметок здесь
        StudentMarks(i, 1) = StudentMarks(i, 1) * 2
        '...
    Next i

    ' Запишите новые значения обратно на лист
    Range("A1:Z20000").Value = StudentMarks

End Sub

Sub UsingCellsToUpdate()
    
    Dim c As Variant
    For Each c In Range("A1:Z20000")
        c.Value = ' Обновите значения здесь
    Next c
    
End Sub

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

' Назначение - быстрее
Range("A1:A10").Value = Range("B1:B10").Value

' Копировать Вставить - медленнее
Range("B1:B1").Copy Destination:=Range("A1:A10")

Заключение

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

1. Массивы — это эффективный способ хранения списка элементов одного типа.
2. Вы можете получить доступ к элементу массива напрямую, используя номер местоположения, который известен как индекс.
3. Распространенная ошибка «Индекс вне диапазона» вызвана доступом к несуществующему местоположению.
4. Существует два типа массивов: статический и динамический.
5. Статический используется, когда размер массива всегда одинаков.
6. Динамические массивы позволяют вам определять размер массива во время выполнения.
7. LBound и UBound обеспечивают безопасный способ поиска самых маленьких и самых больших подписок массива.
8. Основной массив является одномерным. Есть еще многомерные массивы.
9. Чтобы только передать массив в процедуру, используйте ByRef. Вы делаете это так: ByRef arr() as long.
10. Вы можете вернуть массив из функции, но массив, которому он назначен, не должен быть выделен в данный момент.
11. Рабочий лист с его строками и столбцами по сути является двумерным массивом.
12. Вы можете читать непосредственно из диапазона листа в двухмерный массив всего за одну строку кода.
13. Вы также можете записать из двумерного массива в диапазон всего за одну строку кода.

Тема 1. Операции с массивами и матрицами в MS Excel



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

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

Адрес_первой_ячейки : Адрес_последней_ячейки.

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

’Имя_первого_рабочего_листа : Имя_последнего_рабочего_листа’ ! Адрес_первой_ячейки : Адрес_последней_ячейки

   
например,            = ‘Лист1:Лист2’!$A$1:$B$4
Если массив содержит данные арифметического типа, то с таким
массивом  можно выполнять арифметические операции такие, как:
-операции, в которых в качестве операндов участвуют массив
и единственная  переменная, например умножение элементов массива
на число;
-операции, в которых в качестве операндов участвует двумерный
массив и одномерный массив, например, почленно — построчное умножение;
> -операции, в которых участвуют массивы одинаковой размерности.
Для умножения (деления, сложения или вычисления функции) каждого
элемента массива на число следует выполнить следующие действия:
1.Ввести значения элементов массива в ячейки рабочего листа.
2.Выделить область для размещения результата операции, ее
размерность должна быть такой же, как размерность исходного массива.
3.В строку формул ввести формулу, например:
                 

= A1:B2*3 или = sin(A1:B2)

 и т.п.

Ctrl+Shift+Enter.

Пример 1. Требуется умножить элементы двумерного массива размерности 2 х 2 на число 3.

Решение: Введите в ячейки диапазона значения элементов массива (см. рис.1).1. Выделите диапазон ячеек такой же размерности, в который будет помещаться результат операции.2. Введите в выделенный диапазон формулу в формате: = адрес_начальной_ячейки_диапазона: адрес_конечной_ячейки * адрес_второго_ операнда3. Нажмите комбинацию клавиш Ctrl + Shift + Enter. В ячейках выделенного диапазона появится результат.
Рисунок 1

Упражнение 1

Excel Формула массива | Exceljet

Формула массива выполняет операцию с несколькими значениями вместо одного значения. Конечным результатом формулы массива может быть либо один элемент, либо массив элементов, в зависимости от того, как построена формула. Для правильной работы многие формулы массива нужно вводить с помощью Ctrl + Shift + ввод . При вводе формулы таким образом вы увидите формулу, заключенную в фигурные скобки {} в строке формул. Не вводите фигурные скобки вручную, иначе формула не будет работать!

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

Массив — это коллекция из более чем одного элемента.Массивы в Excel заключаются в фигурные скобки. Например, {1; 2; 3} или {«красный», «синий», «зеленый»}. Причина, по которой массивы так распространены в Excel, заключается в том, что они напрямую сопоставляются с диапазонами ячеек. Вертикальные диапазоны представлены в виде массивов, в которых используются точки с запятой, например {100; 125; 150}. Горизонтальные диапазоны представлены в виде массивов с запятыми, например {«маленький», «средний», «большой»}. В двумерном диапазоне будут использоваться как точки с запятой, так и запятые.

Примеры

Формулы массива довольно трудны для понимания из-за сложной и сложной терминологии.Но сами формулы массива могут быть очень простыми. Например, эта формула массива проверяет диапазон A1: A5 на значение «a»:

Операция с массивом — это сравнение каждой ячейки в A1: A5 со строкой «a». Поскольку сравнение работает с несколькими значениями, функция ИЛИ возвращает несколько результатов:

 
 = ИЛИ ({ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ}) 

Если какой-либо элемент в результирующем массиве имеет значение ИСТИНА, функция ИЛИ возвращает ИСТИНА.

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

Функция LEN получает несколько значений в диапазоне B2: B11 и возвращает несколько результатов в массиве, подобном этому внутри SUM:

 
 = СУММ ({3; 3; 5; 4; 5; 4; 6; 5; 4; 4}) 

, где каждый элемент в массиве представляет длину одного значения ячейки. Затем функция СУММ суммирует все элементы и возвращает 43 в качестве окончательного результата.

Специальный синтаксис

Во всех версиях Excel, кроме Excel 365, для правильной работы многие формулы массива необходимо вводить особым образом.Вместо ввода с помощью клавиши «Enter» их нужно вводить с помощью Control + Shift + Enter . Иногда вы увидите Control + Shift + Enter, сокращенно «CSE», как в «формуле CSE». Введенная таким образом формула будет заключена в фигурные скобки с обеих сторон:

Эти фигурные скобки автоматически отображаются в Excel. Убедитесь, что вы не вводите фигурные скобки вручную!

Не все формулы массива нужно вводить с помощью Control + Shift + Enter.Некоторые функции, такие как SUMPRODUCT, изначально запрограммированы для обработки операций с массивами и обычно не требуют Control + Shift + Enter. Например, обе формулы ниже представляют собой формулы массива, которые возвращают один и тот же результат, но только для версии SUM требуется Control + Shift + Enter:

Excel 365

В Excel 365 формулы массива являются собственными и не требуют Ctrl + Shift + Enter. Общие сведения см. В разделе Формулы динамических массивов в Excel.

Формулы и функции массивов в 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 — это самый удобный инструмент для выполнения сложных вычислений и решения сложных задач. Одна формула массива может заменить буквально сотни обычных формул. Формулы массива очень хороши для таких задач, как:

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

Как вы уже знаете, сочетание трех клавиш 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 может возвращать результат в одной или нескольких ячейках. Формула массива, введенная в диапазон ячеек, называется формулой с несколькими ячейками . Формула массива, содержащаяся в одной ячейке, называется формулой для одной ячейки .

Существует несколько функций массива Excel, которые предназначены для возврата массивов с несколькими ячейками, например TRANSPOSE, TREND, FREQUENCY, LINEST и т. Д.

Другие функции, такие как SUM, AVERAGE, AGGREGATE, MAX, MIN, могут вычислять выражения массива при вводе в одну ячейку с помощью Ctrl + Shift + Enter.

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

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

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

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

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

= МАКС (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. Введите функцию массива = TRANSPOSE (массив) и нажмите Ctrl + Shift + Enter. В нашем примере формула = TRANSPOSE ($ A $ 1: $ D $ 6) .

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

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

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

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

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

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

Константы массива 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. Предотвращение ошибок

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

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

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

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

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

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

Затем вы берете функцию LARGE или SMALL, указываете весь диапазон ячеек в первом параметре и включаете константу массива во второй. Наконец, вставьте его в функцию SUM, например:

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

Суммируйте 3 наименьших числа: = СУММ (МАЛЫЙ (диапазон, {1,2,3}))

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Оператор AND — это звездочка (*), которая является символом умножения. Он указывает 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), если хотя бы одно условие оценивается как ИСТИНА; ЛОЖЬ (0), когда все условия оцениваются как ЛОЖЬ.Затем IF проверяет, больше ли результат сложения 0, и если да, SUM складывает соответствующий элемент третьего массива (C2: C9).

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

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

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

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

Следующая формула подойдет:

= СУММ (- (МЕСЯЦ (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 соответственно. Вторая унарная инвертирует значения, то есть меняет знак, превращая их в +1 и 0, что большинство функций Excel могут понимать и с которыми могут работать. Если вы удалите двойной унарный из приведенной выше формулы, это не сработает.

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

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

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

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

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

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

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

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

Заполнение формулы массива в Excel

Формулы массива также часто называют формулами CSE (Ctrl + Shift + Enter), потому что вместо простого нажатия Enter для получения результата вам нужно нажать Ctrl + Shift + Enter для завершения формула и произвести результат.

Почему используются формулы массива?

Excel можно использовать для выполнения различных сложных математических операций и вычислений с использованием простых формул. Хотя некоторые формулы можно применять к отдельным ячейкам, формулы массивов в Excel предлагают преимущество получения результатов, которые применяются к диапазону ячеек. Например: в организации сотрудников формулы массива могут использоваться для расчета общей заработной платы каждого сотрудника за год, которая предоставляется на ежемесячной основе (формула массива с несколькими ячейками), а также общей заработной платы всех сотрудников с использованием Функция СУММ (формула массива с одной ячейкой).

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

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

Формулы массива обладают следующими преимуществами:

1. Формулы массива обеспечивают согласованность, точность и точность вычисляемых формул и значений, применяемых к диапазону ячеек.
2. Формулы массива сокращают потери времени на непродуктивные операции за счет применения формулы непосредственно к диапазону ячеек, и их не нужно применять отдельно.
3. Формулы массива гарантируют отсутствие избыточности и безопасность для пользователей, поскольку компонент формулы массива с несколькими ячейками не может быть заменен каким-либо другим значением, а формула массива ячеек целиком должна быть выбрана для изменения или безвозвратного удаления. Кроме того, в качестве дополнительной меры предосторожности вы должны одновременно нажать CSE для изменения формулы.
4. Формулы массива уменьшают размер файлов за счет использования одной формулы, а не различных подобных промежуточных формул.
5. Формулы массива уменьшают вероятность получения ошибочных результатов при неправильном применении формул.
   Например, когда необходимо вычислить сумму рабочих часов для каждого квартала месяцев, к массиву можно применить одну формулу суммы (формула массива с несколькими ячейками) вместо применения 4 различных формул массива.

Синтаксис формулы массива

Синтаксис формулы Excel очень общий и основан на различных встроенных функциях.Как и другие формулы, формула массива начинается со знака равенства (=). Ключевое различие между общей арифметической формулой и арифметической формулой массива заключается в том, что при использовании формулы массива вы нажимаете Ctrl + Shift + Enter, чтобы получить результат введенной формулы, тогда как в первом случае для получения результата используется Enter. Когда это будет сделано, Excel автоматически заключит формулу массива в фигурные скобки. Ручной ввод фигурных скобок преобразует ее в текстовую строку, как в случае с другими компьютерными языками, такими как C, и просто не будет работать и выполнять функцию.

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

Например: Чтобы вычислить сумму рабочих часов разных сотрудников и наградить лучшего сотрудника с наибольшим количеством рабочих часов, выполняется следующим образом:
{= D3: D12 + E3: E12 + F3: F12 + G3: G12}
, что то же самое, что и
= СУММ (D3: G3), примененное к h4, = СУММ (D4: G4), примененное к h5, ……., = СУММ (D12: G12), примененное к h22.

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

Ввод и изменение формул массива

1. Формула массива всегда начинается со знака равенства.
2. После того, как формула массива введена и применена к диапазону ячеек, будь то одна ячейка или несколько ячеек, ее функциональность может быть применена только при одновременном вводе Ctrl + Shift + Enter для получения результата. Это используется, чтобы распознать его как формулу массива и сохранить в своей памяти, автоматически добавляя фигурные скобки к формулам. Если вручную ввести фигурные скобки, Excel будет рассматривать ее как текстовую строку, которая не будет выполнять необходимую функцию.
3. При работе с формулами с несколькими ячейками всегда применяйте результат, выбирая «диапазон» ячеек, к которым необходимо применить формулы массива.
4. Отдельное содержимое отдельной ячейки в формуле массива нельзя изменить или изменить. Если попытаться сделать это, Excel отобразит сообщение о том, что вы не можете изменить часть массива, так как при вводе формулы массива Excel обрабатывает ее как данные, связанные друг с другом и нуждающиеся в коллективном изменении.
5. Ссылаясь на вышеупомянутый пункт, отдельная ячейка не может быть удалена, и весь массив необходимо удалить, выбрав весь массив и нажав клавишу «Удалить». Чтобы удалить любую формулу массива, выберите всю формулу (например, = D3: D12 + E3: E12 + F3: F12 + G3: G12), нажмите Delete, а затем нажмите Ctrl + Shift + Enter.

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

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

Создание формулы массива одной ячейки

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

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

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

   Например:
   Чтобы рассчитать общее количество рабочих часов всех сотрудников вместе и получить результат в ячейке h23, используется формула массива:
   = СУММ (D3: D12, E3: E12, F3: F12, G3: G12)

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

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

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

Создание формулы массива с несколькими ячейками

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

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

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

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

   Например:
   Чтобы рассчитать рабочее время каждого сотрудника в течение года и получить результат в диапазоне ячеек от h4: h22, используется формула массива:
   {= D3: D12 + E3: E12 + F3: F12 + G3 : G12}

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

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

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

Редактирование формулы массива одиночных ячеек

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

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

Формула отредактирована.

Редактирование формулы массива с несколькими ячейками

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

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

Формула отредактирована.

Перемещение формулы массива

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

Для перемещения формулы массива необходимо последовательно выполнить следующие шаги:

1. Выберите диапазон ячеек, которые необходимо переместить.
2. Нажмите Ctrl + X на клавиатуре.
3. Выберите новое расположение ячеек.
4. Нажмите Ctrl + V на клавиатуре, чтобы переместить их в новое желаемое положение.

Удаление формулы массива

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

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

Полное удаление формулы массива (без сохранения результата формулы массива)

Для полного удаления формулы массива необходимо последовательно выполнить следующие шаги:

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

Желаемая функция полного удаления выполнена.

Удаление формулы массива (сохранение результата формулы массива)

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

1. Скопируйте формулу и затем вставьте ее в ту же ячейку, используя параметр «Вставить значения», полученный путем щелчка правой кнопкой мыши по нужной ячейке или на вкладке «Главная» в разделе «Буфер обмена», а затем нажмите «Удалить», чтобы удалить значения.

OR

1. Выберите ячейку или диапазон ячеек, содержащих формулу, которую необходимо удалить.
2. На вкладке «Главная» в разделе «Редактирование» нажмите «Найти и выбрать», а затем нажмите «Перейти».
3. Щелкните Текущий массив.
4. На вкладке «Главная» в группе «Буфер обмена» щелкните «Копировать».
5. На вкладке «Главная» в группе «Буфер обмена» щелкните стрелку под надписью «Вставить» и выберите «Вставить значения».
6. Выберите диапазон ячеек и нажмите удалить.

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

Константы массива — это значения, добавленные в фигурные скобки, чтобы упростить совместную работу со связанными значениями путем присвоения имени константе массива, а также их совместной обработки. Они коллективно используют константы, а также диапазон значений. При использовании формулы массива введите открывающую скобку, введите желаемые значения и, наконец, введите закрывающую скобку. Например: = PRODUCT (A1: E1 * {1,2,3,4,5})

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

Использование константы массива для ввода значений в столбец

Давайте решим следующую задачу на примере:
Если нам нужно ввести пять значений, а именно 10, 20, 30, 40 и 50, в один столбец A последовательно, следующие шаги, которые необходимо принять:

1. Выберите ячейки, в которые необходимо ввести значения.
2. Введите знак равенства и желаемые постоянные значения, которые должны содержаться в ячейках.Если значение является строкой, поместите текст в двойные кавычки («).
   Разделяйте значения констант точкой с запятой, а не запятыми.
3. Нажмите Ctrl + Shift + Enter на клавиатуре, чтобы сохранить значения в столбце.
   Например: = {10; 20; 30; 40; 50}

   Это также называется одномерным вертикальным постоянным массивом.

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

Давайте решим следующую задачу на примере:
Если нам нужно ввести пять значений, а именно 10, 20, 30, 40 и 50 в одну строку 1 последовательно, необходимо выполнить следующие шаги:

1. Выберите ячейки, в которые необходимо ввести значения.
2. Введите знак равенства и желаемые постоянные значения, которые должны содержаться в ячейках. Если значение является строкой, поместите текст в двойные кавычки («).
   Теперь значения констант разделяйте запятыми, а не точкой с запятой.
3. Нажмите Ctrl + Shift + Enter на клавиатуре, чтобы значения в строке содержались.
   Например: = {10; 20; 30; 40; 50}

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

Использование константы массива для ввода значений в строки и столбцы

Давайте решим следующую задачу на примере:
Если нам нужно ввести двадцать пять значений, а именно, 10, 20, 30, 40,50…..240,250 последовательно, в нескольких строках и столбцах, составляющих пять строк и столбцов, необходимо выполнить следующие шаги:

1. Выберите ячейки, в которые необходимо ввести значения.
2. Введите знак равенства и желаемые постоянные значения, которые должны содержаться в ячейках. Если значение является строкой, поместите текст в двойные кавычки («).
   Разделите значения констант, которые должны содержаться в строках, запятыми и точками с запятой, при этом значения констант должны содержаться в столбцах.
3. Нажмите Ctrl + Shift + Enter на клавиатуре, чтобы значения в строке содержались.
   Например: = {10,20,30,40,50; 60,70,80,90,100; 110,120,130,140,150; 160,170,180,190,200; 210,220,230,240,250}

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

Следует помнить

1. Знак равенства (=) необходимо добавлять в начало каждой формулы массива Excel.
2. Скобки нельзя установить вручную при работе с формулой массива.Их можно ввести только вручную при работе с константами массива.
3. При вводе констант массива можно использовать числа и текст. Однако при работе с константами массива, содержащими текст, вводите его в двойные кавычки («»).

3 шага к работе с формулами массива в Excel

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

Массив — это группа ячеек, которую можно рассматривать как один объект.Есть два типа массивов: одномерные (значения в строках ИЛИ столбцы) и двумерные (значения в строках И столбцах).

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

Некоторые вычисления намного проще выполнять с массивами или формулами массива. Например: суммирование «n» наибольших или наименьших чисел из диапазона.

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

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

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

1. Используйте Ctrl + Shift + Enter

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

1. Выберите ячейки, которые будут содержать формулу массива
2. Введите формулу, просто начав ввод, или через строку формул
3. После завершения ввода формулы нажмите Ctrl + Shift + Enter, чтобы ввести формулу в массив

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

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

3. Выберите массивы с помощью сочетания клавиш

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

Выделив любую ячейку в массиве, просто одновременно нажмите «Ctrl» и «/» (косая черта), и будет выбран весь массив. Затем просто нажмите клавишу F2 или отредактируйте формулу в строке формул. (Не забудьте нажать Ctrl + Shift + Enter, когда закончите!)

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

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

динамических массивов в Excel: восемь формул, которые необходимо знать

Этот учебник по динамическим массивам в Excel s подходит для пользователей Excel для Microsoft 365.

Цель

Изучите НОВЫЕ функции Microsoft Dynamic Array в Excel и эффективно используйте их для решения проблем.

Объяснение динамических массивов в Excel

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

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

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

В этом посте мы рассмотрим пример каждой из них.

Видеоурок

ФИЛЬТР

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

= ФИЛЬТР (массив; включить, [if_empty])

В приведенном ниже примере Excel использует функцию ФИЛЬТР для поиска в массиве B4: E23 диапазона ячеек « Block» и извлекает значение, расположенное в ячейке G4 (восток).Это наш первый критерий. Затем он просматривает диапазон ячеек « Exam» и извлекает значение, расположенное в G7 (на английском языке). Две кавычки указывают Excel ничего не отображать, если он не может найти совпадений.

Это результат:

RANDARRAY

Функция СЛУЧАЙНЫЙ СЛУЧАЙ генерирует случайные числа. Размер массива определяется аргументом строк и столбцов. Вы также можете выбрать диапазон, в который будет попадать число, и если вы хотите, чтобы они были десятичными или целыми числами (целыми числами).Это похоже на более мощную версию функции RANDBETWEEN.

= RANDARRAY ([строки], [столбцы], [min], [max], [integer])

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

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

В этом примере Excel использовал функцию RANDARRAY для вывода таблицы случайных чисел в шесть строк в высоту и шесть столбцов в ширину со значениями от 10 до 50.Аргумент ИСТИНА указывает Excel выводить целые (целые) числа, а не десятичные.

ПОСЛЕДОВАТЕЛЬНОСТЬ

Функция ПОСЛЕДОВАТЕЛЬНОСТЬ генерирует список последовательных чисел. Массив может быть одномерным или двумерным, и вы можете указать начальное значение и значение шага. Если определен только аргумент rows, Excel выведет список значений, начиная с единицы по умолчанию.

= ПОСЛЕДОВАТЕЛЬНОСТЬ (строки, [столбцы], [начало], [шаг])

В этом примере Excel использовал функцию ПОСЛЕДОВАТЕЛЬНОСТЬ только с аргументом строк для вывода списка, который начинается с единицы.

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

В этом примере Excel использовал функцию ПОСЛЕДОВАТЕЛЬНОСТЬ для вывода списка последовательных чисел в десять строк в высоту и восемь столбцов в ширину, начиная с десяти и увеличиваясь с шагом в пять.

СОРТ

Функция СОРТИРОВКА в Excel сортирует содержимое диапазона, используя один или несколько столбцов.

= СОРТИРОВАТЬ (массив, [sort_index], [sort_order], [by_col])

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

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

СОРТ ищет в массиве B4: E23 столбец 4 ^-й и сортирует результаты в порядке убывания.

Если аргумент [sort_order] не указан, сортировка по умолчанию выполняется в порядке возрастания.

СОРТБИ

Функция СОРТИРОВАТЬ в Excel сортирует содержимое диапазона на основе значений из другого диапазона. Вы можете выполнять несколько сортировок одних и тех же данных.

= СОРТИРОВАТЬ (массив, по_массиву, [сортировка_порядка], [массив / порядок],…)

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

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

УНИКАЛЬНЫЙ

Функция UNIQUE возвращает список уникальных значений из диапазона.

= УНИКАЛЬНЫЙ (массив; [by_col]; [точно_once])

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

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

XLOOKUP

Функция XLOOKUP является современной заменой таких функций, как VLOOKUP, INDEX и MATCH. Он может выполнять поиск в вертикальном и горизонтальном диапазонах и поддерживает приблизительные и точные совпадения.

= XLOOKUP (поиск, поиск_массив, возврат_массив, [not_found], [match_mode], [search_mode])

В приведенном ниже примере Excel использовал функцию XLOOKUP для поиска приложения, указанного в ячейке h5 в диапазоне ячеек приложения, и возврата соответствующего результата из столбца «Доход».

XMATCH

Функция XMATCH выполняет поиск и возвращает позицию в вертикальном или горизонтальном диапазонах. Это последняя версия функции ПОИСКПОЗ и поддерживает приблизительное и точное соответствие.

= XMATCH (lookup_value, lookup_array, [match_mode], [search_mode])

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

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

Дополнительные бесплатные руководства по Excel от Simon Sez IT. Взгляните на наш ресурсный центр Excel .

Другие классы Excel, которые могут вам понравиться:

Чтобы изучить Excel с Simon Sez IT. Взгляните на доступные курсы Excel .

Excel Формула массива МЕДИАНА ЕСЛИ

Формула массива МЕДИАНА ЕСЛИ в Excel определяет среднее количество значений, соответствующих определенным критериям.Формула массива выполняет операцию с несколькими значениями вместо одного значения. В этой формуле массива мы, по сути, фильтруем набор данных с помощью формулы, чтобы найти медиану только для значений, которые соответствуют нашим условиям. Эту формулу можно использовать для заполнения калькулятора чистой цены College Board.

Синтаксис и аргументы формулы MEDIAN IF следующие:

{= МЕДИАНА (ЕСЛИ (логический_тест, значение_если_ истинное значение, значение_если_ ложь)}

Цели каждой части формулы:

• Функция МЕДИАНА находит среднее значение из набора чисел.
• Функция ЕСЛИ позволяет нам устанавливать условия для значений, которые мы хотим исследовать.
• Формула массива позволяет функции ЕСЛИ проверять несколько условий в одной ячейке. Когда условие выполнено, формула массива определяет, какие данные будет проверять функция МЕДИАНА, чтобы найти среднюю сумму вознаграждения.

Чтобы заполнить формулу массива, введите формулу (без фигурных скобок), затем одновременно нажмите клавиши Control, Shift и Enter. Это завершит формулу массива в Excel и вставит фигурные скобки в начало и конец формулы. Не вводите в формулу фигурные скобки. Формулы массива, которые создаются путем ввода Control, Shift и Enter, также известны как формулы CSE.

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

В этом примере мы работаем с двумя листами в нашей книге Excel:

1. Набор данных — этот рабочий лист включает вымышленные данные, которые мы будем использовать для расчета наших средних сумм вознаграждения (см. Рисунок 1). Он включает в себя студенческий билет, статус проживания, жилищный статус, диапазон EFC и размер присужденных грантов / стипендий. Обратите внимание, что на рисунке 1 показана только часть набора данных, чтобы проиллюстрировать, какие элементы данных включены.
2. Сводка — этот рабочий лист включает сводную сетку, в которой можно извлекать информацию из рабочего листа набора данных (см. Рисунок 2).

Рисунок 1.Рабочий лист набора данных (отображается только часть набора данных)

Рис. 2. Сводная таблица — сетка калькулятора чистой цены

Для начала используйте формулу массива MEDIAN IF, чтобы найти среднюю сумму вознаграждения для студентов, которые проживают в округе, живут на территории кампуса и имеют EFC равное 0 долларов США (см. Красную рамку на рис. 2, это ячейка B4). Светло-синие, темно-синие и фиолетовые поля являются критериями формулы.

В ячейке B4 сводной таблицы введите следующую формулу:

Следующие ссылки на столбцы и ячейки представляют наши критерии:

Для студентов, для которых выполняются вышеуказанные критерии, среднее значение берется из следующего столбца:

• Dataset! $ E: $ E — студенческий грант / размер стипендии

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

Чтобы завершить формулу массива, одновременно нажмите клавиши Control, Shift и Enter (фигурные скобки будут автоматически вставлены, чтобы заключить формулу).

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

В описательной форме эта формула говорит: «Возвратите медианное значение суммы гранта / стипендии для студентов, чей статус проживания соответствует статусу проживания, выбранному в сводной сетке, И чей жилищный статус соответствует жилищному статусу, выбранному в сводной сетке, И чей EFC Диапазон соответствует диапазону EFC, выбранному в итоговой сетке.”

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

• Копируйте и вставляйте формулы массива в новые ячейки небольшими разделами за раз (например, если вам нужно скопировать формулу массива в общей сложности на 800 ячеек, разделите процесс на части, чтобы вы скопировали формулу в 200 ячеек за один раз). время).

• После вставки формулы массива в ячейки скопируйте результаты и вставьте special: values ​​в то же место.Это приведет к удалению формул, так что они больше не увеличивают размер файла и скорость обработки.

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

• {= МАКС (ЕСЛИ (логический_тест, значение_если_ истинно, значение_если_ ложь)}
• {= МИН (ЕСЛИ (логический_тест, значение_если_ истинное значение, значение_если_ ложь)}

Просмотреть следующий совет

Формулы массива Excel — обзор, единичный вывод, вывод массива

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

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

• Формулы массива позволяют использовать стандартные формулы Excel для нескольких ячеек одновременно. В отличие от стандартных формул, которые принимают отдельные ячейки в качестве входных данных, формулы массивов принимают целые массивы в качестве входных данных.
• Excel использует следующий формат для ссылки на массивы: Первая ячейка: Последняя ячейка.
• В отличие от стандартных формул ячеек, которые можно использовать простым нажатием клавиши Enter / Return, формулы массива в Excel используются одновременным нажатием клавиш Control / Command, Shift и Enter.

Типы формул массива

Формулы массива можно разделить на две группы: формул одного выходного массива и формул выходного массива .

1. Формулы для одного выходного массива

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

2. Формулы массива выходных массивов

Они принимают один массив данных или несколько массивов данных в качестве входных и выдают массив данных в качестве выходных данных.

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

Непрерывные коллекции ячеек в Microsoft Excel известны как массивы или массивы ячеек. Каждая ячейка в Microsoft Excel имеет уникальный буквенно-цифровой код ссылки, который зависит исключительно от положения ячейки на листе Excel.Excel использует буквы для измерения столбцов и чисел для измерения строк. Например, ячейка во втором столбце и пятой строке имеет ссылку на ячейку B5. Excel использует следующий формат для ссылки на массивы:

Первая ячейка: Последняя ячейка

Ссылка на ячейку желтой ячейки на изображении ниже — B5. Ссылки на ячейки зеленого массива и красного массива — это D1: D10 и A12: F14 соответственно.

Общие сведения о формулах массива в Microsoft Excel

В отличие от стандартных формул ячеек, которые можно использовать простым нажатием клавиши Enter / Return, формулы массива в Excel используются нажатием клавиш Control / Command, Shift и Enter клавиши одновременно.Если формула успешно распознается как формула массива, Excel автоматически помещает формулу в фигурные / закрученные скобки {} .

Формулы единого выходного массива — иллюстративные примеры

Расчет средних значений

Мы можем найти среднее значение массива, введя в Excel следующее «= среднее (первая ячейка: последняя ячейка)» и нажав клавишу Control / Command, Shift и Enter одновременно. В этом случае мы можем найти среднюю зарплату, получаемую работниками компании ABC.

Расчет условных средних

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

Формулы для выходных массивов — иллюстративный пример

Расчет сумм по массиву

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

2. После создания столбца Total Pay мы выбираем массив, в котором должны отображаться выходные данные.

3. Введите правильную формулу, пока выбран выходной массив .

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

5. Нам нужно одновременно нажать клавиши Control / Command, Shift и Enter.

Дополнительные ресурсы

CFI предлагает программу сертификации финансового моделирования и оценки (FMVA) ™ Стать сертифицированным аналитиком финансового моделирования и оценки (FMVA) ® для тех, кто хочет вывести свою карьеру на новый уровень. Чтобы продолжить изучение и развитие своей базы знаний, ознакомьтесь с дополнительными соответствующими ресурсами ниже:

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