Выполнять несколько расчетов с помощью формул массива excel

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

Массив 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.

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

3.3. Пример макроса

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

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

Рис.42 Вычисление функции E₁(x)
итерационным способом

Один шаг итерации – это переход от значений в области
J2:J4 к значениям в области
L2:L4. Для того, чтобы сделать следующую
итерацию, нужно скопировать значения, получившиеся в области
L2:L4 и вставить их в область
J2:J4. При этом вставлять нужно только
величины, без формул. Величины в области H2:H4
дают исходные значения для начала итерации .Повторяя многократно
операцию Copy-Paste Special, можно получить в
ячейке L4 искомое значение. Однако
копирование – это скучное занятие и его было бы неплохо
автоматизировать. Для этого можно написать макрос.

Проще всего начать создание макроса через запись команд,
выполняемых на листе. Для этого идем в раздел меню Tools–Macro–Record
New Macro. Появляется окно (Рис. 43), в котором можно указать имя
макроса и где он будет расположен.  

Рис.43 Запись макро

После нажатия OK начинается
запись всех действий, выполняемых на листе. Когда все, что нужно
сохранено в макросе, запись надо остановить командой
Tools–Macro–Stop Recording. Результат можно увидеть, зайдя в
.   

 

Рис.44 Редактор Visual Basic

На Рис. 44 показан записанный макрос, который мы подвергли
небольшому редактированию – добавили цикл для повтора операции
Copy-Paste в числе nIter
раз. Величина nIter берется со
страницы из ячейки J6,
имеющей локальное имя n. Завершает
автоматизацию кнопка Repeat, к которой
привязан макрос Iteration.  

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

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

Предположим, в следующем месяце планируется увеличение коммунальных платежей на 10%. Если мы введем обычную формулу для итога =СУММ((C3:C8*D3:D8)+10%), то вряд ли получим ожидаемый результат. Нам нужно, чтобы каждый аргумент увеличился на 10%. Чтобы программа поняла это, мы используем функцию как массив.

1. Посмотрим, как работает оператор «И» в функции массива . Нам нужно узнать, сколько мы платим за воду, горячую и холодную. Функция: . Итого – 346 руб.
2. Функция «Сортировки» в формуле массива. Отсортируем суммы к оплате в порядке возрастания. Для списка отсортированных данных создадим диапазон. Выделим его. В строке формул вводим . Жмем сочетание Ctrl + Shift + Enter.
3. Транспонированная матрица. Специальная функция Excel для работы с двумерными массивами. Функция «ТРАНСП» возвращает сразу несколько значений. Преобразует горизонтальную матрицу в вертикальную и наоборот. Выделяем диапазон ячеек, где количество строк = числу столбцов в таблице с исходными данными. А количество столбцов = числу строк в исходном массиве. Вводим формулу: . Получается «перевернутый» массив данных.
4. Поиск среднего значения без учета нулей. Если мы воспользуемся стандартной функцией «СРЗНАЧ», то получим в результате «0». И это будет правильно. Поэтому вставляем в формулу дополнительное условие: 0;A1:A8))’ >. Получаем:

Распространенная ошибка при работе с массивами функций – НЕ нажатие кодового сочетания «Ctrl + Shift + Enter» (никогда не забывайте эту комбинацию клавиш). Это самое главное, что нужно запомнить при обработке больших объемов информации. Правильно введенная функция выполняет сложнейшие задачи.

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

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

Синтаксис: =АДРЕС(строка; столбец; ; ; ), где:

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

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

=АДРЕС>(1;1)>=АДРЕС>(1;1;4)>=АДРЕС>(1;1;4;ЛОЖЬ)>=АДРЕС>(1;1;4;ЛОЖЬ;»Лист1″)>

Простейшие примеры обмена значениями

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

Пример 1

Заполнение двумерного массива значениями и и их присвоение диапазону ячеек на рабочем листе Excel:

1 2 3 4 5 6 7 8 9 10 11 12 13

SubTest1() Dima(2,2)AsVariant a(,)=»телепузик» a(,1)=»журналист» a(,2)=»ящерица» a(1,)=»короед» a(1,1)=»утенок» a(1,2)=»шмель» a(2,)=200 a(2,1)=300 a(2,2)=400 Range(«A1:C3»).Value=a EndSub

В данном случае переменная массива не обязательно должна быть универсального типа (As Variant), например, если бы в нее записывались только текстовые данные, ее можно было бы объявить как строковую (As String), и все бы работало.

Пример 2

Объявление обычной переменной универсального типа, присвоение ей значений из диапазона ячеек «A1:C3», записанных кодом первого примера, и вставка этих значений из полученного двумерного массива в диапазон «D10:F12»:

1 2 3 4 5

SubTest2() DimaAsVariant a=Range(«A1:C3») Range(«D10:F12»)=a EndSub

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

Пример 3

Допустим, на рабочем листе «Лист1» в ячейках «A1:A5» записано количество какого-то товара, а в ячейках «B1:B5» — его цена. Необходимо к этой информации добавить сумму каждого товара, умножив количество на цену, и перенести данные на «Лист2».

1 2 3 4 5 6 7 8 9

SubTest3() DimaAsVariant,iAsLong a=Лист1.Range(«A1:C5») Fori=1To5 a(i,3)=a(i,1)_ *a(i,2) Next Лист2.Range(«A1:C5»)=a EndSub

Массив создан сразу с размерностью 5×3 с элементами под суммы. Даже если на первом листе в ячейках «C1:C5» есть какие-то значения, в массиве они будут перезаписаны результатами вычислений.

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

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

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

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

• Многоячеечные формулы массива в Excel
• Одноячеечные формулы массива в Excel
• Массивы констант в Excel
• Редактирование формул массива в Excel
• Применение формул массива в Excel
• Подходы к редактированию формул массива в Excel

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

Nested IF Array Formulas

A nested IF array formula can be very powerful and is probably one of the more common uses for array formulas in Excel. Although Excel provides the SUMIF and COUNTIF and AVERAGEIF functions, they don’t allow as much freedom as a nested IF array formula.

MAX-IF Array Formula

Older versions of Excel do not have the MAXIFS or MINIFS functions, so let’s create our own MAX-IF formula. When we use hyphens to name a formula, it usually means that we’re nesting the functions (IF within MAX in this case).

Let’s say that I have the following contact and sales log and I want a formula that will tell me when I last contacted Bob (cell H51).

Using MAX on the date range will give me that latest date (9/10/2017), but I only want to include the rows where the contact is Bob. So, I’ll use the MAX-IF array formula:

LARGE-IF Array Formula

The LARGE and SMALL functions come in handy when you want to find the value that is perhaps the 2nd largest or 2nd smallest.

The following function will return the second largest sale where the contact is Jim.

SMALL-IF Array Formula

This function returns the second smallest sale where the contact is Jim.

The LARGE and SMALL functions can be used for sorting arrays. More on that later. Hopefully, Excel will introduce a SORT function soon (Google Sheets has already done that).

The SMALL-IF formula can be used in combination with INDEX to do a .

SUM-IF Array Formula

Yes, there is already a SUMIF function that is generally better than using an array formula, but we’ll be getting into more advanced SUM-IF array formulas, so it’s useful to see the simple example:

More Reading: Chip Pearson provides some great examples of ways to use nested IF functions within the SUM and AVERAGE functions to ignore errors and zero values. See Chip Pearson’s article.

Using the Array and Split function

You can use the Array function to populate an array with a list of items. You must declare the array as a type Variant. The following code shows you how to use this function.

    Dim arr1 As Variant
    arr1 = Array("Orange", "Peach","Pear")

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

Contents of arr1 after using the Array function

The array created by the Array Function will start at index zero unless you use Option Base 1 at the top of your module. Then it will start at index one. In programming, it is generally considered poor practice to have your actual data in the code. However, sometimes it is useful when you need to test some code quickly.

The Split function is used to split a string into an array based on a delimiter. A delimiter is a character such as a comma or space that separates the items.

The following code will split the string into an array of four elements:

    Dim s As String
    s = "Red,Yellow,Green,Blue"

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

The array after using Split

The Split function is normally used when you read from a comma-separated file or another source that provides a list of items separated by the same character.

1.7. Некоторые важные функции

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

SUM/ СУММ

Суммирует все числа в
списке аргументов или в области.

Синтаксис:

SUM(number1
) 

Пример

Рис.12
Функция SUM

SUMSQ / СУММКВ

Возвращает сумму квадратов аргументов.

Синтаксис:

SUMSQ(number1
) 

Пример

Рис.13 Функция SUMSQ

SUMPRODUCT / СУММПРОИЗВ

Перемножает соответствующие элементы заданных массивов и
возвращает сумму произведений

Синтаксис:

SUMPRODUCT (array1,
array2, …)  

Пример

Рис.14 Функция SUMPRODUCT

AVERAGE / СРЗНАЧ

Возвращает
своих аргументов

Синтаксис:

AVERAGE(number1
) 

Пример

Рис.15 Функция AVERAGE

VAR / ДИСП 

Оценивает по выборке .

Синтаксис:

VAR(number1
,number2,

…) 

Пример
 

Рис.16 Функция VAR

STDEV / СТАНДОТКЛОН

Оценивает по выборке
.

Синтаксис:

STDEV(number1
,number2,

…) 

Пример

Рис.17 Функция
STDEV

CORREL / КОРРЕЛ

Возвращает
между интервалами
ячеек array1 и array2

Синтаксис:

CORREL(array1,
array2)

Пример

Рис.17 Функция
CORREL

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

Рис.19 Пример составной формулы

3.5. Надстройки

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

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

Стандартная версия Excel включает несколько надстроек,
среди которых для нас примечательны две: Solver Add-In и Analysis
Toolpak.

Надстройка

Solver Add-In предназначена для оптимизации значения в целевой
ячейке. Решение находится с помощью поиска величин в других ячейках,
функционально связанных с целевой.

Надстройка

Analysis Toolpak содержит набор статистических инструментов
для анализа данных

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

J-Walk Chart Tools Add-In – бесплатная утилита для
управление диаграммами: добавление подписей, ярлыков, и т.п. – все то,
что отсутствует в стандартной версии.

XLStat
– большой (и дорогой) пакет статистического анализа, включающий, в
частности, и PLS регрессию.

Multivariate Analysis Add-in – условно бесплатный пакет для
анализа многомерных данных, созданный в Бристольском университете

Fitter – надстройка для нелинейного регрессионного анализа.

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

Подробнее о надстройках можно прочитать

здесь. 

Array Size, Data Types And Memory Requirements

As I explain above, you can specify the data type of an array when you declare it. For general purposes, the explanations and comments about the topic that I provide in this tutorial are applicable.

As a consequence of this, whenever you don’t declare the data type for an array, Visual Basic for Applications uses the default data type. This default data type is Variant. However, you may want to declare arrays using a different data type (other than Variant).

The main reason for this is that there’s an inverse relationship between execution speed and the amount of bytes used by the relevant data. The more bytes your data uses, the slower the execution of your VBA application. In practice, this may not be a big issue, assuming that you’re working on a computer with enough available memory. However, if you work with very large (particularly multidimensional) arrays, you may notice a difference in performance.

An exception to this rule is if you want the array to hold different data types. In such a case, the array data type must be Variant. This is because, as explained by Richard Mansfield in Mastering VBA for Microsoft Office 2016:

When deciding how to proceed, remember that different data types have different nominal allocation requirements. The following table provides a basic idea of how many bytes of memory are usually required by several of the main VBA data types:

Data Type of Element	Bytes
Variant (numeric)	16
Variant (string)	22 + string requirement
Byte	1
Boolean	2
Currency	8
Date	8
Double	8
Integer	2
Long	4
Object	4
Single	4
String (variable-length)	10 + string requirement
String (fixed-length)	String requirement

The above values however, don’t provide all the information you need to understand how much memory a particular array needs. More precisely:

When calculating how many bytes an array uses, you must generally consider the following 2 factors:

• Factor #1: The data type of the array.
• Factor #2: The number of elements in the array.

You can get an idea of the array size is determined by multiplying (i) the amount of bytes required by the relevant data type and (ii) the number of array elements. In mathematical terms:

According to the Microsoft Dev Center (in the webpage I link to above), the maximum size of a VBA array depends on 2 main factors:

• Your operating system.
• Available memory.

As a general rule, execution is slower whenever you use an array whose size exceeds the RAM memory that’s available in the system you’re working with. This is because, as explained by Microsoft, “the data must be read from and written to disk”.

Now that you have a basic understanding of the relationship between array size, data types and memory requirements, let’s move on to the topic of…

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

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

1. Выделяем диапазон Е3:Е8.
2. В строку формул вводим следующую формулу: =C3:C8*D3:D8.
3. Нажимаем одновременно клавиши: Ctrl + Shift + Enter. Промежуточные итоги посчитаны:

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

Если попытаться изменить данные в какой-либо ячейке столбца «К оплате» — ничего не выйдет. Формула в массиве защищает значения диапазона от изменений. На экране появляется соответствующая запись:

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

1. Выделяем ячейку Е9 (напротив «Итого»).
2. Вводим формулу вида: =СУММ(C3:C8*D3:D8).
3. Нажимаем сочетание клавиш: Ctrl + Shift + Enter. Результат:

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

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

Рассмотрим ее синтаксис:

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

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

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

Воспользуемся функцией ИНДЕКС, которая возвращает значение из диапазона по номеру строки и столбца, но вот сам диапазон мы указывать не будем, а зададим массив констант вручную.

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

Для ввода формулы, в которой есть массив констант сочетание Ctrl+Shift+Enter нажимать не нужно, достаточно нажать Enter.

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

Кстати, любой диапазон значений, который используется в формуле, можно превратить в массив констант. Для примера возьмём простейшую формулу с функцией СУММ, которая возвращает сумму столбца А.

Выделяем диапазон А1:А9 в формуле и нажимаем клавишу F9 на клавиатуре. Теперь в формуле фигурируют уже не ссылки на ячейки листа, а массив констант, а значит формула не привязана к диапазону, на основе которого она создавалась и эти значения с листа можно удалить.

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

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

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

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

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

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

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

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

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

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

Conclusion

The following is a summary of the main points of this post

1. Arrays are an efficient way of storing a list of items of the same type.
2. You can access an array item directly using the number of the location which is known as the subscript or index.
3. The common error “Subscript out of Range” is caused by accessing a location that does not exist.
4. There are two types of arrays: Static and Dynamic.
5. Static is used when the length of the array is always the same.
6. Dynamic arrays allow you to determine the length of an array at run time.
7. LBound and UBound provide a safe way of find the smallest and largest subscripts of the array.
8. The basic array is one dimensional. You can also have multidimensional arrays.
9. You can only pass an array to a procedure using ByRef. You do this like this: ByRef arr() as long.
10. You can return an array from a function but the array, it is assigned to, must not be currently allocated.
11. A worksheet with its rows and columns is essentially a two-dimensional array.
12. You can read directly from a worksheet range into a two-dimensional array in just one line of code.
13. You can also write from a two-dimensional array to a range in just one line of code.