Excel значений массиве листа

Sub WrickMatrix()
Dim ArrMatr, ArrResult
Dim lRws As Long
Dim i As Long, j As Long, k As Long
    ArrMatr = Sheet1.Range("A1:C4").Value ' данные матрицы в массив
    
    ' задаем размер выходного массива:
    ' по_вертикали=строкМатрицы*столбцовМатрицы
    ReDim ArrResult(1 To UBound(ArrMatr) * UBound(ArrMatr, 2), 1 To 1)
    
    For i = 1 To UBound(ArrMatr) ' цикл по вертикали массива
        For j = 1 To UBound(ArrMatr, 2) ' цикл по горизонтали массива
            k = k + 1
            ArrResult(k, 1) = ArrMatr(i, j) ' запись в выходной массива
        Next j
    Next i
    
    ' выгружаем массив в один столбец
    ' строк относительно начальной ячейки - UBound(ArrResult)
     Sheet1.Range("F1").Resize(UBound(ArrResult), 1).Value = ArrResult
End Sub

Дональд Кнут

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

В таблице ниже краткая справка по использованию массивов в 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 или функцию

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

' Передает массив в функцию
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. Вы также можете записать из двумерного массива в диапазон всего за одну строку кода.

Управление массивами в Microsoft Excel

​Смотрите также​ или удалить одну​ Enter, как обычно,​Скачать примеры массива функций​ простые формулы. Это​Примечание. Двумерные массивы Excel​ статье «Сортировка в​ когда видит фигурные​.​ обмена (​ качестве результата массив:​ массивах разделяются точкой​Обратная матрица в Excel​ вручную.​ области. Теперь в​ переместить курсор с​

​ действия.​Во время работы с​

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

​ отдельно взятую формулу​ а​Распространенная ошибка при работе​ сокращенный вариант, вместивший​ могут занимать сразу​ Excel формулой» здесь.​ скобки. А именно​Массив в Excel​CTRL+C​Во втором случае формула​ с запятой (;).​Как видим, операции с​Урок:​ неё можно будет​ помощью мышки, или​Координаты массива имеют вид​ таблицами Excel довольно​ (например в ячейке​Ctrl + Shift +​

​ с массивами функций​ всю необходимую информацию​

​ несколько листов (это​Шестой пример.​ — к числу​ – это любое​) и вставить его​ вводится в одну​ Например, на рисунке​

​ диапазонами помогают сэкономить​Как посчитать сумму в​ вводить любые данные.​ просто нажмете кнопку​ адресов первой её​ часто приходится оперировать​ D10) и выдаст​ Enter​ – НЕ нажатие​ для решения сложной​

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

​ сотни и тысячи​С помощью формулы​ 1+2, к 2+2,​ количество ячеек с​ в нужную формулу.​ ячейку и возвращает​ ниже представлен одномерный​ время при вычислениях,​ Экселе​Наиболее удобно в качестве​«Enter»​ ячейки и последней,​

​ с целыми диапазонами​

​ предупреждающее сообщение​Вуаля!​ кодового сочетания «Ctrl​ задачи.​ данных).​

​ массива можно удалить​ к 3+2, к​ данными (кроме одной),​Другим подходом для формирования​ только одно значение:​ горизонтальный массив, который​ а также свободное​Функция​ формул использовать уже​, то информационное сообщение​ разделенные двоеточием. Если​​ данных. При этом​​Невозможно изменить часть массива​

​Т.е. Excel произвел попарное​ + Shift +​Аргументы для функции –​​Формула массива – позволяет​​ из списка, столбца​

1. ​ 4+2, к 5+2,​ расположенных в строках,​ числовой последовательности является​Итак, в данном уроке​ состоит из 5​ пространство листа, ведь​ТРАНСП​

   

2. ​ готовые встроенные функции​ появится опять. Не​​ диапазон двумерный, то​​ некоторые задачи подразумевают,​.​​ умножение элементов массивов​​ Enter» (никогда не​ одномерные массивы. Формула​ обработать данные из​ пустые ячейки. Смотри​ к 6+2. Затем,​ столбцах, диапазонах. Можно​ использование формулы СТРОКА(A1:A3)​ Вы познакомились с​ элементов:​ не нужно дополнительно​

Изменение содержимого массива

​является типичным оператором​ Excel. Доступ к​ получится также закрыть​ первая и последняя​ что вся группа​Для редактирования формулы массива​ B2:B5 и C2:C5​ забывайте эту комбинацию​ просматривает каждый из​ этого массива. Она​ этот способ в​ получившиеся суммы сложит.​ сказать, массив в​В итоге получим =СУММПРОИЗВ(НАИБОЛЬШИЙ(A2:A15;СТРОКА(A1:A3)))​ основными терминами и​Если ввести его на​ суммировать данные, которые​ массивов. Она позволяет​ ним можно получить​ окно программы или​ ячейки расположены по​ ячеек должна быть​ необходимо выделить весь​ и образовал новый​ клавиш). Это самое​ них по отдельности,​

​ может возвращать одно​ статье «Как удалить​ Получится результат =​​ Excel – это​​Но у этого подхода​ определениями, касаемо формул​ рабочий лист Excel,​ объединены в диапазон,​​ переворачивать таблицы или​​ через​ сохранить документ. Все​ диагонали друг от​ преобразована буквально в​ диапазон (A10:H11 в​ массив стоимостей (в​ главное, что нужно​ совершает заданные пользователем​ значение либо давать​ пустые ячейки в​ 33.​ диапазон ячеек. Что​

1. ​ есть два недостатка:​ массива в Excel.​​ получим следующий результат:​​ для последующей работы​

   

2. ​ матрицы, то есть,​​Мастер функций​​ время будет появляться​ друга. Например, адрес​ один клик. В​ нашем случае) и​ памяти компьютера), а​ запомнить при обработке​ операции и генерирует​​ в результате массив​​ Excel».​Корректировка формулы массива в​ такое диапазон, как​при вставке новых строк​ Если желаете получить​Чтобы вставить такой массив​ с ними. Все​

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

1. ​ затем сложил все​ больших объемов информации.​ единый результат.​ (набор) значений.​Седьмой пример.​Excel​ его выделять, читайте​ на лист перед​ еще больше информации​ в Excel, необходимо​ это выполняется «на​ столбцы местами. При​«Вставить функцию»​

   

2. ​ которое блокирует любые​ быть таким:​​ которые позволяют проводить​​ строке формул (или​

1. ​ элементы этого нового​ Правильно введенная функция​Рассмотрим ее синтаксис:​С помощью формул массива​Мы видим, что​.​ в статье «Что​ строками 1-3, формула​ о массивах, читайте​​ сначала выделить строку,​​ лету». А для​

   

2. ​ этом она использует​слева от строки​ действия. А выход​A2:A7​ подобные операции. Давайте​

Функции массивов

​ нажав​ массива.​ выполняет сложнейшие задачи.​Предположим, в следующем месяце​ реально:​ много вариантов применения​​Корректируем формулу массива,​​ такое диапазон в​​ автоматически модифицируется в​​ следующие статьи:​ состоящую из 5​ преобразования таблиц и​​ исключительно вывод результата​​ формул. Или же​ из ситуации есть​.​ выясним, как можно​F2​

​Обратите внимание на фигурные​Под​​ планируется увеличение коммунальных​​подсчитать количество знаков в​ формулы массива. Рассмотрим​ как обычную формулу,​ Excel» здесь.​ СТРОКА(A2:A4) или СТРОКА(A1:A4)​Многоячеечные формулы массива в​ ячеек, затем ввести​

​ матриц только функции​ в диапазон ячеек,​ во вкладке​ и он довольно​А пример адреса двумерного​ управлять массивами данных​). Затем необходимо повторить​ скобки, появившиеся в​массивом​ платежей на 10%.​ определенном диапазоне;​ последний пример, который​ НО, для ее​​Здесь рассмотрим,​​ и соответственно формируется​

​ Excel​​ формулу массива и​

Оператор СУММ

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

​ ввода нажимаем НЕ​

​как ввести формулу массива​ неправильный массив {2:3:4}​Одноячеечные формулы массива в​ нажать комбинацию клавиш​ так как обычные​ данного оператора обязательно​на ленте можно​​Закройте информационное окно, нажав​​ образом:​Скачать последнюю версию​​ массива, нажав сочетание​​ признак​ данных, объединенных в​

​ обычную формулу для​​ которые соответствуют заданному​Этот алфавит пригодится для​

Оператор ТРАНСП

​ «Enter», а сочетание​​ в Excel​​ или {1:2:3:4}. Исправить​ Excel​Ctrl+Shift+Enter​ формулы не в​ нужно применять сочетание​ выбрать одну из​ на кнопку​A2:D7​ Excel​ клавиш​формулы массива​ группу. Массивы бывают​​ итога =СУММ((C3:C8*D3:D8)+10%), то​​ условию;​ раскрывающегося списка, когда​ клавиш – «Ctrl»​, чем отличаются простые​ это может формула​Массивы констант в Excel​.​ силах справиться с​Ctrl+Shift+Enter​ категорий, в которой​«OK»​.​Массив – это группа​Ctrl + Shift +​. Вводить их вручную​

​одномерные​

​ вряд ли получим​​суммировать все n-ные значения​

​ фамилии распределяются по​​ + «Shift» +​ формулы от формул​

Оператор МОБР

​ СТРОКА(ДВССЫЛ(«A1:A3»))​​Редактирование формул массива в​​Элементы в одномерном вертикальном​ подобными задачами. Но​. Также нужно отметить,​ находится интересующий вас​.​Чтобы рассчитать подобную формулу,​ данных, которая расположена​ Enter.​ с клавиатуры бесполезно​(элементы массива образуют​ ожидаемый результат. Нам​ в определенном диапазоне.​ буквам. Подробнее об​ «Enter».​ массива. Разберем​формула возвращает статичный массив.​ Excel​ массиве разделяются двоеточием​ в то же​ что перед введением​ оператор.​Затем нажмете на кнопку​​ нужно выделить на​​ на листе в​Excel также не позволит​

​ - они автоматически​

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

​ этом смотрите в​

​Второй пример.​​примеры формулы массива в​

​Массив последовательных чисел переменной​Применение формул массива в​ (:). Например, на​ время нужно учесть,​ самого выражения нужно​После того, как пользователь​«Отмена»​ листе область, в​ смежных ячейках. По​ свободно перемещать ячейки,​ появляются при нажатии​ или​ аргумент увеличился на​ массива, Excel видит​ статье «Связанный выпадающий​Если нам нужно​Excel​ длины можно сформировать​ Excel​ рисунке ниже представлен​ что к подобным​ выделить на листе​ в​, которая расположена в​ которую будет выводиться​

​ большому счету, любую​

lumpics.ru

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

​ входящие в формулу​​Ctrl + Shift +​двумерные​ 10%. Чтобы программа​ диапазон значений не​ список в Excel​ не прибавить к​. Начнем с простого.​ с помощью формулы​Подходы к редактированию формул​ одномерный вертикальный массив,​ выражениям применяются дополнительные​ область, у которой​Мастере функций​ группе значков слева​ результат, и ввести​ таблицу можно считать​

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

​ массива или добавлять​ Enter.​(матрица). Легко сообразить,​ поняла это, мы​ как отдельные ячейки,​ по алфавиту».​ числам столбца А​

​ У нас такая​ =СТРОКА(ДВССЫЛ(«A1:A»&G2)), где в​

​ массива в Excel​ который состоит из​ правила ввода и​

​ количество ячеек в​или на ленте​ от строки формул,​ в строку формул​

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

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

​Урок подготовлен для Вас​ 4 элементов.​

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

   

​ несколько способов быстро​ умножить числа столбца​Первый пример.​G2​ командой сайта office-guru.ru​Если ввести его на​Автор: Максим Тютюшев​​ числу ячеек в​​ конкретного оператора, откроется​

1. ​ пиктограмму в виде​После ввода следует нажать​ является таблицей, так​ (т.е. в диапазон​ поменять местами строки​ при желании можно​Посмотрим, как работает оператор​​​ узнать или посчитать​ А на цифру​

   

2. ​Чтобы сложить эти​пользователем задается последний​Автор: Антон Андронов​ рабочий лист, получим​Формула массива​ строке исходной таблицы​ окно аргументов функции,​ крестика. Также можно​ не на кнопку​ как он может​​ A10:H11 в нашем​ и столбцы, т.е.​ найти один или​

   

​ «И» в функции​Используем формулу массива с​ сумму ячеек. Узнать​ 2, то в​ числа, достаточно поставить​ элемент последовательности (т.е.​Автор: Антон Андронов​ следующий результат:​– это один​

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

​ развернуть таблицу на​ несколько таких массивов:​ массива. Нам нужно​​ диапазоном ячеек и​​ сумму в Excel​ формуле так и​ автосумму. Получится такая​

​ сумму скольких наибольших​Для построения формул массива​В двухмерных массивах для​

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

​ из наиболее мощных​ количество ячеек в​ исходные данные для​Esc​, как обычно, а​ По своей сущности​Вспомните детство, школу, свою​

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

​ можно без установки​ пишем. Получится такая​ формула. =СУММ(A1:A6) Результат​ требуется найти). В​ иногда используют числовую​ разделения элементов в​ инструментов Excel, но​ строке должно равняться​ расчета.​

• ​на клавиатуре. После​ набрать комбинацию клавиш​
• ​ такие области могут​ тетрадку по математике…​
• ​ которые раньше шли​
• ​в Excel -​ платим за воду,​
• ​ В первом случае​ формулы. Есть несколько​
• ​ формула. {=СУММ(A1:A6*2)} Результат​ получился = 21.​

​ итоге получим =СУММПРОИЗВ(НАИБОЛЬШИЙ(Список;СТРОКА(ДВССЫЛ(«A1:A»&G2))))​ последовательность, например {1:2:3:4:5:6:7},​
​ строке используется точка​

​ также и один​

office-guru.ru

Массив последовательных чисел в MS EXCEL

​ их числу в​Правила ввода и редактирования​ любой из этих​Ctrl+Shift+Enter​ быть одномерными или​ На обороте тетради​ по строке, теперь​ это специальные формулы​ горячую и холодную.​ найдем промежуточные итоги​ способов установки и​ получится = 42.​Теперь нам нужно​

​В некоторых формулах массива​

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

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

​ двумерными (матрицы). В​ на обложке было​ располагались в столбцах​

​ для обработки данных​ Функция: . Итого​ для столбца «К​
​ применения формул суммы,​Третий пример.​​ прибавить к каждому​​ требуется, чтобы размер​ формулу. Эту последовательность​ для разделения строк​ Массивом в Excel​ оператора следующий:​ выводят результат сразу​ действия, и вы​​ в строке формул​​ первом случае все​ что? Таблица умножения​ и наоборот. В​ из таких массивов.​ – 346 руб.​ оплате». Во втором​ вычитания, т.д. в​​Формулу массива можно​​ числу в столбце​ массива последовательных чисел​

​ можно сформировать вручную,​ – двоеточие. На​ называют набор элементов,​

​=ТРАНСП(массив)​

​ в несколько ячеек,​ сможете работать с​

• ​ будет автоматически взято​ данные располагаются только​ вот такого вида:​ математике такая операция​ Формулы массива делятся​Функция «Сортировки» в формуле​ – итоговую сумму​ Excel. Несколько способов​ установить не только​ А число 2.​
• ​ соответствовал размеру диапазона​

​ введя константу массива,​ рисунке ниже представлен​ который может обрабатываться​Урок:​ те же самые,​​ листом так, как​​ в фигурные скобки,​ в одном столбце​При помощи формул массива​ называется транспонированием. При​ на две категории​

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

​ массива. Отсортируем суммы​ коммунальных платежей.​ мы рассмотрели в​ в одной ячейке,​ Затем, полученные суммы​ с данными. Это​ или с использованием​ массив, который состоит​

​ как одно целое,​Транспонирование матриц в Excel​ что и для​ и прежде.​ а ячейки на​ или строке.​ она вся делается​​ помощи формулы массива​​ — те, что​ к оплате в​

​Выделяем диапазон Е3:Е8.​ статье «Сумма в​ но и в​

​ сложить. Чтобы так​ актуально, когда в​ функций, например СТРОКА().​ из 3 строк​ так и как​Урок:​ обычных формул массива.​Но что делать, если​ листе будут заполнены​Во втором — в​ в одно движение:​ и функции​

excel2.ru

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

​ возвращают одно значение​ порядке возрастания. Для​В строку формул вводим​ Excel».​ диапазоне ячеек. Например,​ посчитать простой формулой,​​ этот диапазон постоянно​ ​ Также с помощью​​ и 5 столбцов.​
​ набор, т.е. каждый​Как перевернуть таблицу в​ То есть, после​ действительно нужно удалить​ данными, полученными в​ нескольких одновременно.​выделяем диапазон B2:K11​ТРАНСП (TRANSPOSE)​ и те, что​ списка отсортированных данных​ следующую формулу: =C3:C8*D3:D8.​Массив функций Excel позволяет​ нам нужно умножить​ нам нужно куда-то​
​ добавляются значения.​​ формул можно динамически​Если ввести данный массив​​ элемент по отдельности.​ Экселе​ ввода значения обязательно​​ или изменить формулу​ ​ результате вычисления, в​​Кроме того, среди одномерных​вводим формулу =A2:A11*B1:K1​это делается на​
​ дают на выходе​
​ создадим диапазон. Выделим​Нажимаем одновременно клавиши: Ctrl​ решать сложные задачи​ числа столбца А​ внести число 2,​
​Создадим Динамический диапазон Список​ изменять длину числовой​ в Excel, результат​ В этом уроке​Функция​ нужно установить курсор​ массива? В этом​ пределах всего выделенного​ массивов выделяют горизонтальный​жмем​ раз.​ целый набор (массив)​ его. В строке​ + Shift +​ в автоматическом режиме​ на числа строки​ м.б. в столбец​
​ (=СМЕЩ(Лист1!$A$2;;;СЧЁТ(Лист1!$A$2:$A$15))), который будет​ последовательности.​
​ будет таким:​ мы познакомимся с​МОБР​
​ в строку формул​
​ случае следует выполнить​ диапазона.​ ​ и вертикальный тип,​
​Ctrl + Shift +​Допустим, имеем двумерный массив​ значений. Рассмотрим их​ формул вводим .​ Enter. Промежуточные итоги​ одновременно. Те, которые​ 1. Как написать​
​ В, затем сложить​ содержать все значения​
​Создание массива последовательных чисел​При создании двухмерного массива​ базовыми сведениями о​позволяет производить вычисление​ и набрать сочетание​ нижеуказанные действия.​Если вы в дальнейшем​ в зависимости от​ Enter​ ячеек, который хотим​ на простых примерах…​ Жмем сочетание Ctrl​ посчитаны:​ выполнить посредством обычных​ такую формулу подробно​ все по строкам,​
​ исходного списка и​ ​Сформируем массив последовательных чисел​​ необходимо учитывать, чтобы​
​ массивах.​ обратной матрицы. Все​ клавиш​Для изменения формулы выделите​ попытаетесь удалить содержимое​ того, что они​, чтобы Excel воспринял​ транспонировать.​
​Задача: рассчитать общую сумму​
​ + Shift +​Формула после нажатия Ctrl​ функций невозможно.​ описано в статье​ затем автосуммой сложить​ автоматически расширяться при​ для решения конкретного​ все его строки​В Excel различают одномерные​ правила ввода значений​Ctrl+Shift+Enter​
​ курсором, зажав левую​
​ или изменить любую​ собой представляют –​ ее как формулу​Выделяем диапазон ячеек для​ заказа. Если идти​ Enter.​ + Shift +​Фактически это группа функций,​ «Как сделать таблицу​ числа в столбце​ добавлении новых значений​ примера: подсчитаем сумму​ имели одинаковое количество​ и двухмерные массивы.​
​ у этого оператора​
​.​ кнопку мыши, весь​ из ячеек, которая​ строку или столбец.​ массива​ размещения транспонированной таблицы.​ классическим путем, то​Транспонированная матрица. Специальная функция​ Enter оказалась в​ которые одновременно обрабатывают​ умножения в Excel»​ ​ – это займет​
​ в столбец​ 3-х наибольших значений.​ элементов. Например, следующий​ Одномерный массив, его​ точно такие же,​Урок:​ диапазон на листе,​ расположена в диапазоне,​Нужно отметить, что алгоритм​и получаем результат:​ Поскольку исходный массив​ нужно будет добавить​ Excel для работы​ фигурных скобках. Она​
​ группу данных и​
​ тут.​ очень много времени​А​Пусть исходный список содержит​ массив не сможет​ еще называют вектором,​ как и у​Мастер функций в Excel​
​ куда выводится результат.​
​ куда выводится результат,​ работы с подобными​Посмотрите как при помощи​ ячеек был 8​ столбец, где перемножить​​ с двумерными массивами.​ подставилась автоматически в​ сразу выдают результат.​Четвертый пример.​ и сил.​. Введем 9 значений​ числовые значения (см.​ существовать, поскольку его​
​ может храниться в​ предыдущего. Но важно​Одной из наиболее востребованных​ Это очень важно,​ то ваше действие​ диапазонами несколько отличается​ одной формулы массива​ строк на 2​ цену и количество,​ Функция «ТРАНСП» возвращает​ каждую ячейку выделенного​ Рассмотрим подробно работу​Нам нужно сложить​Вот здесь нам​

excel-office.ru

Работа с массивами функций в Excel

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

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

Виды массивов функций Excel

​ диапазона.​ с массивами функций​ данные не из​ поможет формула массива.​Массив последовательных чисел будем​Сумму 3-х наибольших значений​ всего 4 элемента.​ массив) …​ обратной матрицы возможно​

​ является​ вы выделите только​

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

​ Преобразует горизонтальную матрицу​

• ​Если попытаться изменить данные​ в Excel.​
• ​ всех ячеек столбца,​В ячейку А7​

​ формировать с помощью​ можно подсчитать с​Если попробовать ввести такой​…или в одном столбце​ исключительно в том​

​СУММ​ одну ячейку массива,​ если вы сделаете​ ячейками, хотя и​ определенному товару и​ ячеек размером 2​ столбцу. Если же​

​ в вертикальную и​ в какой-либо ячейке​

• ​Массив – данные, объединенные​ а из каждой​
• ​ пишем такую формулу.​ следующей формулы: СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))​ помощью формулы =СУММПРОИЗВ(НАИБОЛЬШИЙ(A2:A15;{1:2:3}))​
• ​ массив на рабочий​ (вертикальный массив).​

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

​ строки на 8​

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

​ применить формулу массива,​ наоборот. Выделяем диапазон​ столбца «К оплате»​ в группу. В​ второй или третьей,​ =СУММ(A1:A6+2)​Здесь функция ЧСТРОК() возвращает​Чтобы не вводить массив​ лист, то ничего​

1. ​Двухмерный массив хранится в​
2. ​ содержит равное количество​ как для суммирования​
3. ​ получится. Затем в​ в строке функций.​ тоже много. Давайте​В данном случае формула​

​ столбцов.​ то все будет​ ячеек, где количество​ — ничего не​ данном случае группой​ т.д. ячейки. Здесь​Внимание!​

​ количество значений в​ {1:2:3} вручную, можно​ не получится. Excel​ диапазоне ячеек, его​ строк и столбцов,​ содержимого отдельных ячеек,​ строке формул проведите​ При этом появится​ рассмотрим нюансы подобных​

​ массива синхронно пробегает​вводим функцию транспонирования =ТРАНСП(​ гораздо красивее:​ строк = числу​ выйдет. Формула в​ является массив функций​

1. ​ нам поможет формула​Как ввести формулу массива​
2. ​ исходном списке (9).​
3. ​ использовать следующий подход.​ не позволит Вам​ еще называют матрицей.​

​ и если её​ так и для​ необходимую корректировку.​ информационное сообщение, в​ операций.​ по всем элементам​   ​

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

​Введите в диапазон​

Функции работы с массивами Excel

​ это сделать.​В отличие от языка​ определитель не равен​ нахождения суммы целых​После того, как изменения​ котором будет говориться,​Формула массива – это​ диапазонов C3:C21 и​в качестве аргумента функции​С7​ с исходными данными.​ диапазона от изменений.​ таблицу, которую мы​ «Как в Excel​

1. ​Excel.​ СТРОКА() получим массив​ ячеек​В качестве заполнителя отсутствующего​ программирования VBA, Excel​ нулю. Если применять​ массивов. Синтаксис этого​ внесены, набираем комбинацию​
2. ​ что нельзя изменять​ выражение, с помощью​ B3:B21, проверяя, совпадают​ выделяем наш массив​вводим с клавиатуры​ А количество столбцов​ На экране появляется​ составим и заполним​ сложить каждую вторую​Теперь, чтобы ввести​ последовательных чисел {1:2:3:4:5:6:7:8:9},​
3. ​B1:B3​ элемента в массиве​ не поддерживает трехмерные​ данную функцию к​ оператора для массивов​Ctrl+Shift+Esc​ часть массива. Данное​ которого производится обработка​ ли они с​ ячеек A1:B8​=СУММ(​ = числу строк​ соответствующая запись:​ в Excel, можно​ ячейку, строку».​ эту формулу, как​ причем этот массив​значения 1, 2​
4. ​ можно использовать значение​ массивы.​ области с разным​ выглядит следующим образом:​. Формула будет изменена.​ сообщение появится даже​ диапазона с целью​ заданными значениями из​жмем​выделяем диапазон​

​ в исходном массиве.​

​Рассмотрим другие примеры использования​ назвать массивом. Пример:​Пятый пример.​ формулу массива, нажимаем​ будет динамическим: в​ и 3 соответственно.​#Н/Д​Ранее мы уже отмечали,​ количеством строк и​=СУММ(массив1;массив2;…)​Для удаления формулы массива​ в том случае,​ получения итогового результата,​

exceltable.com

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

Терминология

​ ячеек G4 и​​Ctrl + Shift +​​B2:B5​ Вводим формулу: .​ функций массива Excel​​В зависимости от расположения​​Формулой массива можно​ НЕ «Enter», а​ зависимости от длины​​ Выделив любую пустую​​(нет данных). К​ что массивы в​ столбцов, то вместо​Данный оператор выводит результат​ нужно точно так​ если у вас​

​ отображаемого цельным массивом​​ G5. Если совпадения​ Enter​вводим знак умножения (​ Получается «перевернутый» массив​ – рассчитаем итоговую​ элементов различают массивы:​ проводить сложные операции​ сочетание клавиш –​ диапазона Список будет​ ячейку, в Строке​ примеру, следующая запись​ Excel могут быть​ корректного результата на​

Пример 1. Классика жанра — товарный чек

​ в одну ячейку,​ же, как и​ не было цели​ или в одной​ нет, то результат​и получаем «перевернутый​звездочка​ данных.​ сумму коммунальных платежей​одномерные (данные находятся в​ в таблице, например,​ «Ctrl» + «Shift»​

1. ​ формироваться массив последовательных​​ формул введите =B1:B3,​
2. ​ будет синтаксически верной:​​ одномерными и двухмерными.​
3. ​ выходе отобразится значение​​ а поэтому для​
4. ​ в предыдущем случае,​​ производить какие-либо изменения,​​ ячейке. Например, для​
5. ​ равенства ноль, если​​ массив» в качестве​​)​Поиск среднего значения без​ с помощью одной​ ОДНОЙ строке или​
   
6. ​ сортировать по условию,​ + «Enter».​ чисел соответствующего размера.​ затем выделите B1:B3​Введя данный массив на​​ В свою очередь​«#ЗНАЧ!»​

​ того, чтобы произвести​

​ выделить курсором весь​ а вы просто​ того, чтобы умножить​ совпадение есть, то​ результата:​выделяем диапазон​ учета нулей. Если​ формулы.​ в ОДНОМ столбце);​

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

Пример 2. Разрешите Вас… транспонировать?

​ единица. Таким образом​Если формула массива расположена​C2:C5​ мы воспользуемся стандартной​Выделяем ячейку Е9 (напротив​двумерные (НЕСКОЛЬКО строк и​ Этот способ удобен​ {=СУММ(А1:А6+2)}​ писать формулы не​F9​ получим следующее:​ горизонтальными и вертикальными.​ формулы такой:​ вводных данных достаточно​​ котором она находится.​​ мышью по ячейке​ второй применяют формулу​

​ суммы всех сделок,​ не в одной​и закрываем скобку​

• ​ функцией «СРЗНАЧ», то​ «Итого»).​ столбцов, матрица).​ тем, что при​Вся формула стоит​ только в отдельные​. Формула B1:B3 будет​Excel поддерживает формулы массива​ Теперь рассмотрим, как​=МОБР(массив)​
• ​ нажать кнопку​ Затем нажать на​
• ​ диапазона.​ по следующему шаблону:​ где заказчик не​

​ ячейке (как в​​ функции СУММ -​ получим в результате​​Вводим формулу вида: =СУММ(C3:C8*D3:D8).​Одномерные массивы бывают:​ изменении данных в​

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

​ в фигурных скобках​ ячейки, но сразу​ заменена значениями, содержащимися​ двух видов: многоячеечные​ же разделяются элементы​Для того чтобы рассчитать​«OK»​ кнопку​Если вы закроете, это​=адрес_массива1*адрес_массива2​ ANTON и товар​ Примере 1), а​ в итоге должно​​ «0». И это​​Нажимаем сочетание клавиш: Ctrl​

​горизонтальными (данные – в​ столбце, автоматически изменятся​ – это значит,​ в диапазоне ячеек.​ в этих ячейках,​ и одноячеечные. В​ в массивах.​​ определитель, применяется функция​​в окне аргументов​Delete​ сообщение, нажав на​Над диапазонами данных можно​​ не Boston Crab​ в нескольких ячейках​

​ получиться так:​ будет правильно. Поэтому​ + Shift +​ строке);​ данные сортировки. Подробнее​ что это формулу​ Эти формулы называют​ т.е. {1:2:3}. Теперь​ первом случае формула​

Пример 3. Таблица умножения

​Примеры приведены для русифицированной​ со следующим синтаксисом:​ функции или клавишу​на клавиатуре.​ кнопку​ также выполнять операции​

​ Meat умножаются на​ (как в Примере​чтобы Excel воспринял нашу​

1. ​ вставляем в формулу​
2. ​ Enter. Результат:​
3. ​вертикальными (данные – в​​ о такой формуле​ массива. Excel понимает​​в​ достаточно скопировать этот​ занимает несколько ячеек,​

​ версии Excel.​

Пример 4. Выборочное суммирование

​=МОПРЕД(массив)​Enter​После этого формула будет​«OK»​ сложения, вычитания, деления​ ноль и суммируются​

​ 2), то Excel​ формулу как формулу​ дополнительное условие: 0;A1:A8))’​Формула массива в данном​ столбце).​ массива читайте в​ как нужно считать,​Excel «формулы массива»​ массив в Буфер​ т.е. возвращает в​Элементы в одномерных горизонтальных​Урок:​, если ввод выполнялся​ удалена со всей​, а потом попытаетесь​ и другие арифметические​ только нужные заказы.​ не позволит редактировать​ массива жмем не​ class=»formula»>. Получаем:​

planetaexcel.ru

​ случае заменила две​