Функции ссылок и массивов excel

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

Для начала следует понимать, что такое массив и какие массивы бывают.

Массивом называют группу данных объединенных (сгруппированных) в одну структуру (группу)

В «Excel» массивы подразделяют на три типа в зависимости от структуры расположения данных в таблице:

Горизонтальный одномерный (линейный) массив – массив, в котором данные расположены горизонтально в одну строку.

Вертикальный одномерный (линейный) массив – массив, в котором данные расположены вертикально в один столбец.

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

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

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

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

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

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

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

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

Во втором — в нескольких одновременно.

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

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

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

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

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

Координаты массива имеют вид адресов первой её ячейки и последней, разделенные двоеточием. Если диапазон двумерный, то первая и последняя ячейки расположены по диагонали друг от друга. Например, адрес одномерного массива может быть таким: A2:A7.

А пример адреса двумерного диапазона выглядит следующим образом: A2:D7.

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

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

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

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

Если вы закроете, это сообщение, нажав на кнопку «OK», а потом попытаетесь переместить курсор с помощью мышки, или просто нажмете кнопку «Enter», то информационное сообщение появится опять. Не получится также закрыть окно программы или сохранить документ. Все время будет появляться это назойливое сообщение, которое блокирует любые действия. А выход из ситуации есть и он довольно прост

1. Закройте информационное окно, нажав на кнопку «OK».

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

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

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

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

После того, как изменения внесены, набираем комбинацию Ctrl+Shift+Esc. Формула будет изменена.

1. Для удаления формулы массива нужно точно так же, как и в предыдущем случае, выделить курсором весь диапазон ячеек, в котором она находится. Затем нажать на кнопку Delete на клавиатуре.

После этого формула будет удалена со всей области. Теперь в неё можно будет вводить любые данные.

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

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

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

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

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

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

Как извлечь значения, игнорируя пустые ячейки

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

Формула массива для извлечения различных значений, исключая пустые ячейки:

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

Напоминаем, что в приведенных выше формулах A2: A13 – это исходный список, а B1 – ячейка прямо над первой позицией формируемого списка.

На этом скриншоте показан результат отбора:

Быть может, кому-то будет полезна еще одна формула –

Она работает с числами и текстом, игнорирует пустые ячейки.

Использование расширенного фильтра.

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

1. Выберите столбец данных, из которого вы хотите извлечь отдельные значения.
2. Перейдите на вкладку «Данные» > группа «Сортировка и фильтр» и нажмите кнопку «Дополнительно .
3. В диалоговом окне Расширенный фильтр выберите следующие параметры:
   • Установите флажок Копировать в другое место .
   • В поле Исходный диапазон убедитесь, что он указан правильно.
   • В параметре Поместить результат в…   укажите самую верхнюю ячейку целевого диапазона. Помните, что вы можете копировать отфильтрованные данные только на текущий лист.
   • Выберите пункт «Только уникальные записи».
4. Наконец, нажмите кнопку ОК и проверьте результат.

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

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

Теперь немного усложним задачу.

Если требуется искать записи не по одному, а по нескольким столбцам, то можно их предварительно «склеить» при помощи функции СЦЕПИТЬ.

Записываем это в столбец F и копируем вниз. Получаем вспомогательную колонку.

В качестве исходного диапазона мы по-прежнему выбираем данные, из которых извлекаем уникальные значения. Теперь это два столбца – A и B.

Но искать уникальные мы по-прежнему можем только в одном столбце. Вот для этого нам и пригодится вспомогательная колонка F с объединенными данными. Ее то мы и указываем в поле «Диапазон условий».

Все остальное – так же, как и в предыдущем примере.

В результате мы получили все имеющиеся в таблице комбинации «Заказчик — Товар» на основе данных во вспомогательном столбце F.

Думаю, вы понимаете, что аналогичные действия можно произвести и с тремя столбцами (например Фамилия – Имя – Отчество). Главное условие – исходный диапазон должен быть непрерывным, то есть все столбцы должны находиться рядом.

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

Функция СМЕЩ

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

Синтаксис: =СМЕЩ(ссылка; смещ_по_строкам; смещ_по_столбцам; ; ), где

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

Если в результате смещения диапазон стал выходить на пределы листа, то функция возвращает ошибку #ССЫЛКА!.

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

Для примера будем использовать вложение функции СМЕЩ в функцию СУММ.Изначально ссылаемся на диапазон из 10 строк и 1 столбца, где все ячейки имеют значение 2. Таким образом получает результат выполнения формулы – 20.

Теперь сместим диапазон на один столбец влево, т.е. на -1.

Снова изменим ссылку, а именно расширим до 4 столбцов. После этого возвращаемая ссылка станет A3:D12. Результат на изображении.

Извлечь уникальные значения из диапазона.

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

Используем формулу массива

Здесь A2:C9 обозначает диапазон, из которого вы хотите извлечь уникальные значения. E1 – это первая ячейка столбца, в который вы хотите поместить результат. $2:$9 указывает на строки, содержащие данные, которые вы хотите использовать. $A:$C указывает на столбцы, из которых вы берёте исходные данные. Пожалуйста, измените их на свои собственные.

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

Как видите, извлекаются все уникальные и первые вхождения дубликатов.

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

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

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

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

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

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

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

1. выделяем ячейку С7
2. вводим с клавиатуры =СУММ(
3. выделяем диапазон B2:B5
4. вводим знак умножения (звездочка)
5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ – в итоге должно получиться так:

чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter

Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

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

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

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

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

• Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
• вводим функцию транспонирования =ТРАНСП(
• в качестве аргумента функции выделяем наш массив ячеек A1:B8

жмем Ctrl + Shift + Enter и получаем “перевернутый массив” в качестве результата:

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

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

Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

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

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

Вспомните детство, школу, свою тетрадку по математике. На обороте тетради на обложке было что? Таблица умножения вот такого вида:

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

1. выделяем диапазон B2:K11
2. вводим формулу =A2:A11*B1:K1
3. жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

и получаем результат:

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

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

В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

Выделение массива в Excel

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

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

2. С помощью диалогового окна Выделить группу ячеек. Для этого выделите любую ячейку, которая принадлежит массиву:А затем на вкладке Главная из раскрывающегося списка Найти и выделить выберите пункт Выделить группу ячеек.

   Откроется диалоговое окно Выделить группу ячеек. Установите переключатель на пункт текущий массив и нажмите ОК.

   Текущий массив будет выделен:

3. При помощи комбинации клавиш Ctrl+/. Для этого выберите любую ячейку массива и нажмите комбинацию.

Функция СУММПРОИЗВ – подробное описание

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

Сами способы следующие:

1. Определение суммы произведений массива.
2. Проверка значения на предмет соответствия определенным критериям, получение их произведений и суммирования.
3. Использование в качестве замены формулы массива.

На самом деле, возможных вариаций методов использования функции СУММПРОИЗВ значительно больше. Опытный пользователь Excel с легкостью сможет комбинировать несколько функций для того, чтобы заставить электронную таблицу работать так, как надо именно ему. 

Синтаксис функции СУММПРОИЗВ

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

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

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

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

1

Но можно значительно упростить задачу, использовав функцию СУММПРОИЗВ, использовав два аргумента: диапазон всех значений длины и диапазон всех значений ширины. После этого программа сама выполнит все необходимые действия. 

Видим, что результат использования функции СУММПРОИЗВ получился идентичным. 

2

Функция СУММПРОИЗВ с условием

В чистом виде функция СУММПРОИЗВ используется довольно редко. Особенно в бухгалтерском учете. Тяжело представить ситуацию, что она оказывается непосредственно полезной каждый день для того, чтобы рассчитывать сумму произведений. Впрочем, такие ситуации тоже бывают. Например, если поставлена задача перемножить между собой курс и сумму в долларах, а потом получить общий финансовый показатель, основанный на стоимости каждой продукции в национальной валюте. Но такая задача все равно случается довольно редко. 

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

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

Чтобы выполнить эту задачу с помощью этой функции, необходимо ее использовать и записать два условия в самом начале. Каждое из условий заключается в скобках. Поскольку в нашем случае должно быть соответствие сразу двум критериям, то мы использовали символ звездочки (*). Синтаксис виден на этом скриншоте. Вам достаточно просто подставить соответствующие вашему случаю значения в необходимые места. И дело сделано!

3

Давайте рассмотрим аргументы более подробно:

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

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

4

Размер массива

Размер массива является произведением длин всех его измерений. Он представляет собой общее число элементов, в данный момент содержащихся в массиве. Например, в следующем примере объявляется двухмерный массив с четырьмя элементами в каждом измерении. Как видно из выходных данных в примере, размер массива равен 16 (или (3 + 1) * (3 + 1).

Примечание

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

Размер массива можно определить с помощью свойства Array.Length. Длину каждого измерения многомерного массива можно узнать с помощью Array.GetLength метода.

Можно изменить размер переменной массива, назначив ей новый объект массива или используя инструкцию инструкции . В следующем примере оператор используется для изменения массива 100-element на массив 51-element.

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

Split

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

СинтаксисПараметры и Описание

• Выражение — требуемый параметр. Строковое выражение, которое может содержать строки с разделителями.
• Разделитель — необязательный параметр. Параметр, который используется для преобразования в массивы на основе разделителя.
• Count — необязательный параметр. Количество подстрок, которые нужно вернуть, и если указано как -1, то возвращаются все подстроки.
• Compare — Необязательный параметр. Этот параметр указывает, какой метод сравнения следует использовать.
• = vbBinaryCompare — выполняет двоичное сравнение
• 1 = vbTextCompare — выполняет текстовое сравнение

пример

Добавьте кнопку и добавьте следующую функцию.

Private Sub Constant_demo_Click()
   ' Splitting based on delimiter comma '$'
   Dim a as Variant
   Dim b as Variant
   
   a = Split("Red $ Blue $ Yellow","$")
   b = ubound(a)
   
   For i = 0 to b
      msgbox("The value of array in " & i & " is :"  & a(i))
   Next
End Sub

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

Функция, которая возвращает массив, содержащий указанное количество значений. Разделить на разделитель.

Формулы массива в EXCEL. Знакомство

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

Без формул массива (array formulas ) можно обойтись, т.к. это просто сокращенная запись группы однотипных формул. Однако, у формул массива есть серьезное преимущество: одна такая формула может заменить один или несколько столбцов с обычными формулами.

Например, можно найти сумму квадратов значений из диапазона А2:A12 , просто записав в ячейке B14 формулу =СУММ(A2:A12^2) . Для сравнения: чтобы найти сумму квадратов, используя обычные формулы, нам потребуется дополнительный столбец для вычисления квадратов значений и одна ячейка для их суммирования (см. файл примера или диапазон B 2 :B13 на рисунке ниже).

В отличие от ввода обычных формул, после ввода формулы массива нужно нажать вместо ENTER комбинацию клавиш CTRL+SHIFT+ENTER (поэтому, иногда, формулы массива также называются формулами CSE — это первые буквы от названия клавиш, используемых для ввода C trl, S hift, E nter). После этого формула будет обрамлена в фигурные скобки (их не вводят с клавиатуры, они автоматически появляются после нажатия CTRL+SHIFT+ENTER ). Это обрамление показано на рисунке выше (см. Строку формул ).

Если бы мы нажали просто ENTER , то получили бы сообщение об ошибке #ЗНАЧ!, возникающую при использовании неверного типа аргумента функции, т.к. функция СУММ() принимает в качестве аргумента только диапазон ячеек (или формулу, результатом вычисления которой является диапазон, или константы). В нашем случае мы в качестве аргумента ввели не диапазон, а некое выражение, которое еще нужно вычислить перед суммированием, поэтому и получили ошибку.

Чтобы глубже понять формулы массива проведем эксперимент:

• выделим ячейку B13 , содержащую обычную формулу =СУММ($B$2:$B$12) ;
• в C троке формул выделим аргумент функции СУММ() , т.е. $B$2:$B$12 ;
• нажмем клавишу F9 , т.е. вычислим, выделенную часть формулы;
• получим – массив квадратов значений из столбца В . Массив – это просто набор неких элементов (значений).

Т.е. обычная функция СУММ() в качестве аргумента получила некий массив (или точнее ссылку на него).Теперь проведем тот же эксперимент с формулой массива:

• выделим ячейку, содержащую формулу массива =СУММ($A$2:$A$12^2) ;
• в строке формул выделим аргумент функции СУММ() , т.е. $A$2:$A$12^2 ;
• нажмем клавишу F9 , т.е. вычислим, выделенную часть формулы;
• получим – тот же массив, что и в первом случае.

Т.е. нажатие CTRL+SHIFT+ENTER заставило EXCEL перед суммированием произвести промежуточные вычисления с диапазоном ячеек (с массивом содержащихся в нем значений). Для самой функции СУММ() ничего не изменилось – она получила тот же массив, только предварительно вычисленный, а не прямо из диапазона ячеек, как в случае с обычной формулой. Понятно, что вместо функции СУММ() в формуле массива может быть использована любая другая функция MS EXCEL: СРЗНАЧ() , МАКС() , НАИБОЛЬШИЙ() и т.п.

Вышеприведенный пример иллюстрирует использование функции массива возвращающей единственное значение, т.е. результат может быть выведен в одной ячейке. Это достигается использованием функций способных «свернуть» вычисленный массив до одного значения ( СУММ() , СРЗНАЧ() , МАКС() ). Примеры таких функций массива приведены в статье Формулы массива, возвращающие одно значение .

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

Преимущества и недостатки формул массива рассмотрены в одноименной статье Формулы массива. Преимущества и недостатки .

В файле примера также приведено решение данной задачи функцией СУММПРОИЗВ() , которая зачастую не требует введения ее как формулы массива: =СУММПРОИЗВ($A$2:$A$12^2)

Здесь, при вводе формулы СУММПРОИЗВ() нажимать CTRL+SHIFT+ENTER необязательно.

Ссылки на статьи о формулах массива на сайте Microsoft:

2. Основы формул массива (на английском) из книги авторов Colin Wilcox и John Walkenbach

ПРИМЕЧАНИЕ При создании Именованных формул и правил Условного форматирования формулы массива нельзя ввести нажимая CTRL+SHIFT+ENTER . Эти формулы вводятся только в ячейки листа. Однако, если формуле массива присвоить Имя , то EXCEL «сообразит», что нужно с ней нужно делать. Например, если формуле =СУММ($A$2:$A$12^2) присвоить имя Сумма_квадратов, а затем в ячейке указать =Сумма_квадратов , то получим правильный результат.

Похожие записи:

Как открыть командную строку от имени администратора в windows 10 Как обновить windows 7 до windows 10 бесплатно Как на пк и ноуте с windows 10 настроить басы Как перевернуть текст в word Msvcp100 dll для windows 10 Звук не работает на компе с ос windows 7