Формула массива что это
Работа с массивами функций в Excel
Массив функций Excel позволяет решать сложные задачи в автоматическом режиме одновременно. Те, которые выполнить посредством обычных функций невозможно.
Фактически это группа функций, которые одновременно обрабатывают группу данных и сразу выдают результат. Рассмотрим подробно работу с массивами функций в Excel.
Виды массивов функций Excel
Массив – данные, объединенные в группу. В данном случае группой является массив функций в Excel. Любую таблицу, которую мы составим и заполним в Excel, можно назвать массивом. Пример:
В зависимости от расположения элементов различают массивы:
Одномерные массивы бывают:
Примечание. Двумерные массивы Excel могут занимать сразу несколько листов (это сотни и тысячи данных).
Формула массива – позволяет обработать данные из этого массива. Она может возвращать одно значение либо давать в результате массив (набор) значений.
С помощью формул массива реально:
Когда мы используем формулы массива, Excel видит диапазон значений не как отдельные ячейки, а как единый блок данных.
Синтаксис формулы массива
Используем формулу массива с диапазоном ячеек и с отдельной ячейкой. В первом случае найдем промежуточные итоги для столбца «К оплате». Во втором – итоговую сумму коммунальных платежей.
Формула после нажатия Ctrl + Shift + Enter оказалась в фигурных скобках. Она подставилась автоматически в каждую ячейку выделенного диапазона.
Рассмотрим другие примеры использования функций массива Excel – рассчитаем итоговую сумму коммунальных платежей с помощью одной формулы.
Формула массива в данном случае заменила две простые формулы. Это сокращенный вариант, вместивший всю необходимую информацию для решения сложной задачи.
Аргументы для функции – одномерные массивы. Формула просматривает каждый из них по отдельности, совершает заданные пользователем операции и генерирует единый результат.
Рассмотрим ее синтаксис:
Функции работы с массивами Excel
Предположим, в следующем месяце планируется увеличение коммунальных платежей на 10%. Если мы введем обычную формулу для итога =СУММ((C3:C8*D3:D8)+10%), то вряд ли получим ожидаемый результат. Нам нужно, чтобы каждый аргумент увеличился на 10%. Чтобы программа поняла это, мы используем функцию как массив.
Распространенная ошибка при работе с массивами функций – НЕ нажатие кодового сочетания «Ctrl + Shift + Enter» (никогда не забывайте эту комбинацию клавиш). Это самое главное, что нужно запомнить при обработке больших объемов информации. Правильно введенная функция выполняет сложнейшие задачи.
5 основных функции для работы с массивами
Виды массивов функций Excel
Массив – данные, объединенные в группу. В данном случае группой является массив функций в Excel. Любую таблицу, которую мы составим и заполним в Excel, можно назвать массивом. Пример:
В зависимости от расположения элементов различают массивы:
Одномерные массивы бывают:
Примечание. Двумерные массивы Excel могут занимать сразу несколько листов (это сотни и тысячи данных).
Формула массива – позволяет обработать данные из этого массива. Она может возвращать одно значение либо давать в результате массив (набор) значений.
С помощью формул массива реально:
Когда мы используем формулы массива, Excel видит диапазон значений не как отдельные ячейки, а как единый блок данных.
Классика жанра – товарный чек
Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее:
Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.
Обратите внимание на фигурные скобки, появившиеся в формуле – отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно – они автоматически появляются при нажатии Ctrl + Shift + Enter.
Разрешите Вас… транспонировать?
При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.
Допустим, имеем двумерный массив ячеек, который хотим транспонировать.
жмем Ctrl + Shift + Enter и получаем “перевернутый массив” в качестве результата:
Функция СТРОКА
Определяет и возвращает номер строки указанной ссылкой ячейки.
Синтаксис: =СТРОКА([ссылка]), где аргумент «ссылка» не является обязательным. Если он опущен, но возвращается номер текущей строки.
=СТРОКА(D4) – результат 4.
=СТРОКА() – функция вернет номер строки, в которой она расположена.
Функция СТОЛБЕЦ
Возвращает номер столбца ячейки, указанной ссылкой.
Синтаксис: =СТОЛБЕЦ([ссылка]), где «ссылка» не обязательный аргумент. По умолчанию возвращается номер текущего столбца.
=СТОЛБЕЦ(C4) – формула вернет значение 3.
=СТОЛБЕЦ() – функция возвращает номер столбца, в котором расположена.
Функция АДРЕС
Возвращает текст, представляющий адрес ячейки, заданной номерами строки и столбца.
Синтаксис: =АДРЕС(строка; столбец; [тип_закрепления]; [стиль_ссылки]; [имя_листа]), где:
=АДРЕС(1;1)>=АДРЕС(1;1;4)>=АДРЕС(1;1;4;ЛОЖЬ)>=АДРЕС(1;1;4;ЛОЖЬ;»Лист1″)>
Функция ДВССЫЛ
Преобразует адрес ссылки, заданный текстовой строкой, в ссылку на данный адрес.
Синтаксис: =ДВССЫЛ(адрес_ссылки; [стиль_ссылки]), где
=ДВССЫЛ(“a3”) – возвращает ссылку на ячейку A3.
=ДВССЫЛ(“r3c3”) – вернет ошибку #ССЫЛКА!, так как текст для ссылки в формате R1C1, а второй аргумент имеет значение по умолчанию.
=ДВССЫЛ(“r3c3”; ЛОЖЬ) – возвращает ссылку на ячейку C3.
=ДВССЫЛ(АДРЕС(СТРОКА(C3);СТОЛБЕЦ(C3))) – функция вернет аналогичный предыдущему примеру результат.
Вложение функции ДВССЫЛ со ссылкой на диапазон:
Функция ВЫБОР (CHOOSE)
Позволит вам выбрать значение из общего списка по указанному номеру позиции:
Функция ИНДЕКС (INDEX)
Эта функция возвращает указанное значение из одно- или двумерного диапазона:
=ИНДЕКС(A1:C6;4;3)
Как видно с примера, полученное значение 37, в указанном диапазоне стоит на пересечении строки №4 и столбика №3 в диапазоне A1:C6 указанном в формуле. В более простом примере показано как в диапазоне С1:С6, на 2 месте находится значение 15:
=ИНДЕКС(С1:С6;2) 
Функция ПОИСКПОЗ (MATCH)
Эта функция вернет позицию значения, которое вы будете искать в указанном диапазоне:
=ПОИСКПОЗ(B3;B2:B5;0)
Редактирование формулы массива
Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.
Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.
Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)
Описание категории «Ссылки и массивы»
Категория функций «Ссылки и массивы», другое название категории «Функции просмотра» используются для поиска значений в списках или таблицах, манипулирования диапазоном данных, а также, для получения дополнительной информации, например, информации о количестве строк или столбцов диапазона.
Ярким представителем функций данной категории является самая популярная функция ВПР, которая позволяет найти значение из ячейки диапазона, выполнив поиск в первом столбце этого же диапазона
Принцип работы функции ВПР из категории «Ссылки и массивы»
Функция ВПР (VLOOKUP) ищет значение в крайнем левом столбце таблицы и возвращает значение ячейки, находящейся в указанном столбце той же строки.
Пример применения формулы массива
Наиболее простой задачей, которой можно проиллюстрировать мощь формулы массива, является простой товарный чек. То есть у нас есть наименование товаров, их количество и цена за единицу. На выходе мы должны получить общую сумму покупки.
Как бы мы решали данную задачу стандартным образом?
Во-первых, мы бы получили итоговую сумму по каждому товару, перемножив количество товара на его цену.
Ну а затем просуммировали бы получение суммы, для получения итоговой. То есть нам бы потребовался дополнительный столбец для промежуточных вычислений.
Ту же самую задачу можно решить с помощью простейшей формулы массива.
Так как мы в формуле использовали диапазоны (массивы) данных, то и формула должна быть формулой массива. Для этого нужно нажать сочетание клавиш Ctrl + Shift + Enter и получим результат.
Что произошло и как работает формула массива? Это важно понять, так как в дальнейшем можно будет применять формулы массива для решения намного более заковыристых задач…
Итак, при вычислении формулы массива Excel произвел попарное умножение значений диапазонов B2:B5 и C2:C5. В результате получился массив значений который был просуммирован соответствующей функцией. Мы получили только одно значение итоговой суммы без вспомогательных вычислений.
Обратите внимание на формулу в строке формул. Она заключена в фигурные скобки, которые указывают нам, что мы имеем дело с формулой массива.
Похожим образом работает и функция ГПР (HLOOKUP):
Функции для работы со ссылками и массивами
В Excel имеется ряд функций для обработки ссылок и массивов: вычисление номеров строки или столбца таблицы по имени ссылки, определение количества столбцов (строк) ссылки или массива, выбор значения по номеру индекса и т. д.
Таблица 4.10. Функции для работы со ссылками и массивами.
| АДРЕС | Создает адрес ячейки в виде текста, используя номер строки и номер столбца. |
| ВПР | Просматривает левый столбец массива в поисках определенного значения и возвращает значение из указанной ячейки. |
| ВЫБОР | Использует номер индекса, чтобы выбрать и вернуть значение из списка аргументов-значений. |
| ГИПЕРССЫЛКА | Создание ссылки, открывающей документ, находящийся на жестком диске, сервере сети или Internet. |
| ГПР | Просматривает верхнюю строку массива в поисках определенного значения и возвращает значение из указанной ячейки. |
| двссыл | Возвращает ссылку, заданную аргументом ссылка на ячейку. |
| ИНДЕКС | Выбирает по индексу значение из ссылки или массива. |
| ОБЛАСТИ | Возвращает количество областей в ссылке. Область – это интервал смежных ячеек или отдельная ячейка. |
| ПОИСКПОЗ | Возвращает относительную позицию элемента массива. |
| ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫ | Получение данных сводной таблицы |
| ПРОСМОТР | Ищет значения в векторе или массиве. |
| СМЕЩ | Возвращает ссылку заданной высоты и ширины, отстоящую от другой ссылки на заданное количество строки и столбцов. |
| СТОЛБЕЦ | Возвращает номер столбца по заданной ссылке. |
| СТРОКА | Возвращает номер строки, определяемой ссылкой. |
| ТРАНШ | Возвращает транспонированный массив. |
| ЧИСЛСТОЛБ | Возвращает количество столбцов в ссылке или массиве. |
| ЧСТРОК | Возвращает количество строк в ссылке или массиве. |
Функция ГПР
Горизонтальное первое равенство. Ищет совпадение по ключу в первой строке определенного диапазона и возвращает значение из указанной строки этого диапазона в совпавшем с ключом столбце.
Синтаксис: =ГПР(ключ; диапазон; номер_строки; [интервальный_просмотр]).
Так как функция аналогична функции ВПР, за исключением того, что использует горизонтальные таблицы вместо вертикальных, то описания и примеры использования подходят и для ГПР с учетом упомянутых различий.
Формулы массива в Excel
Терминология
Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов:
Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее:
Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.
Пример 2. Разрешите Вас. транспонировать?
При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.
Допустим, имеем двумерный массив ячеек, который хотим транспонировать.
жмем Ctrl + Shift + Enter и получаем «перевернутый массив» в качестве результата:
Редактирование формулы массива
Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.
Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.
Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)
Пример 3. Таблица умножения
Вспомните детство, школу, свою тетрадку по математике. На обороте тетради на обложке было что? Таблица умножения вот такого вида:
При помощи формул массива она вся делается в одно движение:
и получаем результат:
Пример 4. Выборочное суммирование
Посмотрите как при помощи одной формулы массива красиво и легко выбираются данные по определенному товару и заказчику:
В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.
Формулы массива в Excel
Терминология
Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов:
Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее:
Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.
Пример 2. Разрешите Вас. транспонировать?
При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.
Допустим, имеем двумерный массив ячеек, который хотим транспонировать.
жмем Ctrl + Shift + Enter и получаем «перевернутый массив» в качестве результата:
Редактирование формулы массива
Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.
Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.
Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)
Пример 3. Таблица умножения
Вспомните детство, школу, свою тетрадку по математике. На обороте тетради на обложке было что? Таблица умножения вот такого вида:
При помощи формул массива она вся делается в одно движение:
и получаем результат:
Пример 4. Выборочное суммирование
Посмотрите как при помощи одной формулы массива красиво и легко выбираются данные по определенному товару и заказчику:
В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.
Динамические формулы массива и поведение разлитого массива
Excel формулы, возвращающие набор значений, также известных как массив, возвращают эти значения в соседние ячейки. Это действие называется переносом.
Формулы, которые могут возвращать массивы переменного размера, называются формулами динамического массива. Формулы, которые возвращают массивы, которые успешно пролиты, могут называться формулами с пролитой массивом.
Ниже следующую заметку можно найти, чтобы помочь вам понять и использовать формулы такого типа.
Что означает «пролитие»?
Примечание: Более старые формулы массива, известные как формулы массива прежних поколений, всегда возвращают фиксированный размер— они всегда протекают в одно и то же количество ячеек. Поведение, описанное в этом разделе, не относится к устаревшим формулам массива.
Основные моменты
При нажатии ввода для подтверждения формулы Excel динамический размер диапазона вывода и поместите результаты в каждую ячейку в этом диапазоне.
Если вы пишете формулу динамического массива для работы со списком данных, может быть полезно разместить ее в таблице Excel, а затем использовать структурированные ссылки для ссылки на данные. Это происходит потому, что структурированные ссылки автоматически настраиваются при добавлении или удалении строк из таблицы.
Пролитые формулы массива не поддерживаются в Excel таблицах, поэтому их следует разместить в сетке за пределами таблицы. Таблицы лучше всего подходят для хранения строк и столбцов независимых данных.
После ввода формулы разлитого массива при выделении любой ячейки в области Excel вокруг диапазона будет выделена граница. При выборе ячейки за пределами области граница исчезнет.
Редактировать можно только первую ячейку в области пролития. Если выбрать другую ячейку в области пролития, формула будет видна в области формул, но текст будет замеен, и изменить его будет нельзя. Если вам нужно обновить формулу, вы можете выбрать левую верхнюю ячейку в диапазоне массива, при необходимости изменить ее, а Excel при нажатии нажатием ввод автоматически обновит оставшуюся часть области.
Перекрытие формул: формулы массива не могут вводиться, если диапазон выходных данных блокируется. в этом случае Excel возвращает ошибку #SPILL! о том, что она заблокирована. Если удалить помеху, формула будет перенесена должным образом. В приведенном ниже примере диапазон вывода формулы перекрывает другой диапазон с данными и отображается с перекрывающимися ячейками с пунктирной границей со значениями, указывающими на то, что их нельзя пролить. Удалите заблокированные данные или скопируйте их в другом месте, и формула будет пролита, как ожидалось.
Устаревшие формулы массива, вводиться с помощью CTRL+SHIFT+ВВОД (CSE), по-прежнему поддерживаются по соображениям совместимости, но использовать их больше не нужно. При этом можно преобразовать устаревшие формулы массива в динамические формулы массива, выйдя из первой ячейки в диапазоне массива, скопируйте текст формулы, удалите весь диапазон массива, а затем снова введите формулу в левую верхнюю ячейку. Перед обновлением устаревших формул массива до формул динамического массива следует помнить о некоторой разнице междуними в вычислениях.
Приложение Excel ограничило поддержку динамических массивов в операциях между книгами, и этот сценарий поддерживается, только если открыты обе книги. Если закрыть исходную книгу, все связанные формулы динамического массива вернут ошибку #ССЫЛКА! после обновления.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.