Excel комбинация клавиш массив

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

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

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

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

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

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

   

6. чтобы 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.

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

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

В зависимости от расположения элементов различают массивы:

• одномерные (данные находятся в ОДНОЙ строке или в ОДНОМ столбце);
• двумерные (НЕСКОЛЬКО строк и столбцов, матрица).

Одномерные массивы бывают:

• горизонтальными (данные – в строке);
• вертикальными (данные – в столбце).

Примечание. Двумерные массивы Excel могут занимать сразу несколько листов (это сотни и тысячи данных).

Формула массива – позволяет обработать данные из этого массива. Она может возвращать одно значение либо давать в результате массив (набор) значений.

С помощью формул массива реально:

• подсчитать количество знаков в определенном диапазоне;
• суммировать только те числа, которые соответствуют заданному условию;
• суммировать все n-ные значения в определенном диапазоне.

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



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

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

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. Результат:

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

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

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

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

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

1. Посмотрим, как работает оператор «И» в функции массива. Нам нужно узнать, сколько мы платим за воду, горячую и холодную. Функция: . Итого – 346 руб.



2. Функция «Сортировки» в формуле массива. Отсортируем суммы к оплате в порядке возрастания. Для списка отсортированных данных создадим диапазон. Выделим его. В строке формул вводим . Жмем сочетание Ctrl + Shift + Enter.



3. Транспонированная матрица. Специальная функция Excel для работы с двумерными массивами. Функция «ТРАНСП» возвращает сразу несколько значений. Преобразует горизонтальную матрицу в вертикальную и наоборот. Выделяем диапазон ячеек, где количество строк = числу столбцов в таблице с исходными данными. А количество столбцов = числу строк в исходном массиве. Вводим формулу: . Получается «перевернутый» массив данных.



4. Поиск среднего значения без учета нулей. Если мы воспользуемся стандартной функцией «СРЗНАЧ», то получим в результате «0». И это будет правильно. Поэтому вставляем в формулу дополнительное условие: . Получаем:

Скачать примеры массива функций

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

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

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

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

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

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

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

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

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

=адрес_массива1*адрес_массива2

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

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

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

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



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

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

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

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

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



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

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

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



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

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



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

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

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

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

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

Урок: Мастер функций в Excel

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

Одной из наиболее востребованных функций в Экселе является СУММ. Её можно применять, как для суммирования содержимого отдельных ячеек, так и для нахождения суммы целых массивов. Синтаксис этого оператора для массивов выглядит следующим образом:

=СУММ(массив1;массив2;…)

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

Урок: Как посчитать сумму в Экселе

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

Функция ТРАНСП является типичным оператором массивов. Она позволяет переворачивать таблицы или матрицы, то есть, менять строки и столбцы местами. При этом она использует исключительно вывод результата в диапазон ячеек, поэтому после введения данного оператора обязательно нужно применять сочетание Ctrl+Shift+Enter. Также нужно отметить, что перед введением самого выражения нужно выделить на листе область, у которой количество ячеек в столбце будет равно числу ячеек в строке исходной таблицы (матрицы) и, наоборот, количество ячеек в строке должно равняться их числу в столбце исходника. Синтаксис оператора следующий:

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

Урок: Транспонирование матриц в Excel

Урок: Как перевернуть таблицу в Экселе

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

Функция МОБР позволяет производить вычисление обратной матрицы. Все правила ввода значений у этого оператора точно такие же, как и у предыдущего. Но важно знать, что вычисление обратной матрицы возможно исключительно в том случае, если она содержит равное количество строк и столбцов, и если её определитель не равен нулю. Если применять данную функцию к области с разным количеством строк и столбцов, то вместо корректного результата на выходе отобразится значение «#ЗНАЧ!». Синтаксис у этой формулы такой:

=МОБР(массив)

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

=МОПРЕД(массив)

Урок: Обратная матрица в Excel

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

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

​Смотрите также​ указан), принимающий числовые​ функции ИНДЕКС передана​ в мае с​ + Shift +​ сокращенный вариант, вместивший​ строке);​ в статье Формулы​;​ квадратов значений из​ в строке формул,​9​ массива, ознакомьтесь со​

Правила редактирования формул массива

​ Вручную вводе фигурных скобок​ Ctrl-Shift-Ввод» или «CSE»​ случаев будет проще​ простой, но в​В прошлых уроках мы​ значения, которые характеризуют​ ссылка на несколько​ использованием функции ИНДЕКС.​​ Enter» (никогда не​​ всю необходимую информацию​

​вертикальными (данные – в​ массива, возвращающие несколько​нажмем клавишу​ диапазона​ но изменить ее​2150​ статьей развернуть формулу​ вокруг формулы не​ формулы, так как​

1. ​ удалить старый массив​ ряде случаев абсолютно​ разобрали основные понятия​ номер строки в​ несмежных диапазонов. Для​
2. ​Вид исходной таблицы данных:​ забывайте эту комбинацию​ для решения сложной​ столбце).​
3. ​ значений.​F9​А2:A12​ не удается. Формулы​
4. ​Купе​ массива. Вы также​ преобразует его в​ необходимо нажать сочетание​
5. ​ и создать новый.​ непригодный способ.​ и сведения касаемо​ массиве или диапазоне,​​ поиска номеров строки​​Для нахождения стоимости молока​

​ клавиш). Это самое​ задачи.​Примечание. Двумерные массивы Excel​Преимущества и недостатки формул​, т.е. вычислим, выделенную​, просто записав в​ массива — особый случай,​5​ можете попробовать:​ формуле массива —​

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

​ клавиш Ctrl +​Прежде чем удалять старый​С помощью диалогового окна​ массивов в Excel.​ заданном аргументом массив,​ и столбца, на​ в мае используем​ главное, что нужно​Аргументы для функции –​

1. ​ могут занимать сразу​ массива рассмотрены в​ часть формулы;​ ячейке​ поэтому выполните одно​1950​
2. ​Правила изменения формул массива​​ необходимо нажать сочетание​​ Shift + Ввод,​ массив, скопируйте его​Выделить группу ячеек​​ В этом уроке​ либо номер строки​ пересечении которых находится​​ формулу:​​ запомнить при обработке​​ одномерные массивы. Формула​​ несколько листов (это​

   

   ​ одноименной статье Формулы​​получим {1:4:9:16:25:36:49:64:81:100:121} – массив​​B14​ из следующих действий.​Шашков​​ (это может быть​​ клавиш Ctrl +​

   

   ​ чтобы ввести их. ​

   

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

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

​Описание аргументов:​ больших объемов информации.​ просматривает каждый из​ сотни и тысячи​ массива. Преимущества и​ квадратов значений из​формулу =СУММ(A2:A12^2). Для сравнения:​Если вы уже введенные​​Седан​​ кропотливой работой)​

Как отредактировать формулу массива

​ Shift + Ввод,​ Формулы массива можно​ а затем используйте​ любую ячейку, которая​ формул массива, но​ заданном аргументом ссылка.​ значением, используются функции​B2:G8 – диапазон ячеек​ Правильно введенная функция​

​ них по отдельности,​ данных).​

1. ​ недостатки.​ столбца​ чтобы найти сумму​ формулы массива с​6​
2. ​Удаление формулы массива (можно​ чтобы создать формулу​ использовать для выполнения​ ее в новом​ принадлежит массиву:​​ с большим уклоном​​[номер_столбца] – необязательный аргумент,​ ПОИСКПОЗ. Функции ЕСЛИ​ таблицы с числовыми​
3. ​ выполняет сложнейшие задачи.​ совершает заданные пользователем​
4. ​Формула массива – позволяет​В файле примера также​​В​​ квадратов, используя обычные​ одной ячейкой, выделите​

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

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

​Функция ИНДЕКС в Excel​ операции и генерирует​ обработать данные из​ приведено решение данной​. Массив – это​ формулы, нам потребуется​ ячейку, нажмите клавишу​Купе​ клавиши Ctrl +​

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

​ порядкового номера диапазона​ выполняется поиск;​ возвращает данные из​ единый результат.​ этого массива. Она​

​ задачи функцией СУММПРОИЗВ(),​ просто набор неких​ дополнительный столбец для​ F2, внесите необходимые​8​ Shift + Ввод)​ фигурные скобки (​вычислять число символов в​ позволит сэкономить уйму​ списка​ же изменить уже​

• ​ столбца в массиве​ из ссылки, определяемого​
• ​ПОИСКПОЗ(A10;A2:A8;0) – функция, возвращающая​ таблицы или определенного​
• ​Рассмотрим ее синтаксис:​ может возвращать одно​
• ​ которая зачастую не​
• ​ элементов (значений).​ вычисления квадратов значений​
• ​ изменения и нажмите​2000​

​Использование констант массива в​{}​
​ диапазоне ячеек;​

​ времени.​

office-guru.ru

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

​Найти и выделить​​ существующую формулу массива​ или диапазоне, который​ номером четверти, которую​ номер строки из​ диапазона ячеек на​Предположим, в следующем месяце​ значение либо давать​ требует введения ее​Т.е. обычная функция СУММ()​ и одна ячейка​ клавиши Ctrl +​Создание формулы массива с​ формулах массива (они​) исчезнут из формулы​суммировать только те числа,​Если же необходимо изменить​выберите пункт​ в Excel?​ задан аргументом массив,​ можно выбрать из​ диапазона A2:A8, в​

​ основе известных строки​ планируется увеличение коммунальных​ в результате массив​ как формулы массива:​ в качестве аргумента​ для их суммирования​ Shift + Ввод.​ несколькими ячейками​ могут оказаться очень​ массива и внести​ которые отвечают определенным​ расположение массива на​Выделить группу ячеек​Когда формула массива помещена​ либо номер строки​ списка.​

• ​ которой находится значение,​ и столбца, значения​

• ​ платежей на 10%.​ (набор) значений.​=СУММПРОИЗВ($A$2:$A$12^2)​ получила некий массив​ (см. файл примера​Если вы уже введенные​

• ​В образце книги выберите​​ полезными)​​ изменения в формуле​ условиям, например наименьшие​

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

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

​ массива и добавлять​ значения в диапазоне​ меняя его размерность,​Откроется диалоговое окно​ то ее редактирование​

1. ​ переданном в качестве​ с фамилией Иванищев​ A10 («Молоко, 1л»);​

2. ​ качестве аргументов функции.​

   ​ обычную формулу для​ реально:​ СУММПРОИЗВ() нажимать​ на него).​B​ несколькими ячейками, выделите​ до E11. В​

   ​ (это может упростить​ фигурные скобки необходимо​ чисел, определенном верхней​ просто переместите его​Выделить группу ячеек​ в Excel обычно​

   

   ​ аргумента ссылка.​ за контрольную №2,​ПОИСКПОЗ(B10;B1:G1;0) – функция, возвращающая​Существует два варианта данной​ итога =СУММ((C3:C8*D3:D8)+10%), то​подсчитать количество знаков в​CTRL+SHIFT+ENTER​Теперь проведем тот​2:B13 на рисунке ниже).​ все ячейки, содержащие​ них будут помещены​

3. ​ констант для использования)​

   ​ нажать сочетание клавиш​ и нижней границами;​ как обычный диапазон.​​. Установите переключатель на​​ не представляет особой​

   ​[номер_области] – необязательный аргумент​​ написанную во второй​ номер столбца из​ функции, отличающиеся синтаксической​ вряд ли получим​ определенном диапазоне;​необязательно.​ же эксперимент с​В отличие от ввода​ ее, нажмите клавишу​ результаты.​Если вы хотите попрактиковаться​​ Ctrl + Shift​​суммировать все​Существует несколько подходов к​ пункт текущий массив​ сложности. Здесь главное​ функции ИНДЕКС ссылочной​ четверти:​ диапазона B1:G1, в​ записью:​ ожидаемый результат. Нам​

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

​суммировать только те числа,​Ссылки на статьи о​ формулой массива:​ обычных формул, после​ F2 и следуйте​Перед вводом формулы всегда​ с константами массива,​ + Ввод еще​n​

1. ​ редактированию размеров массива,​ и нажмите​ не забыть закончить​

2. ​ формы, принимающий числовое​

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

   ​ ввода формулы массива​ таким правилам:​ следует выбирать ячейки​ прежде чем применять​ раз.​-е значения в диапазоне​

   

3. ​ которые, возможно, Вам​

   ​ОК​ редактирование комбинацией клавиш​ значение, которое характеризует​​ синтаксической записи:​​ хранящееся в ячейке​

   ​ ИНДЕКС используется для​​ аргумент увеличился на​ условию;​ сайте Microsoft:​ массива =СУММ($A$2:$A$12^2);​ нужно нажать вместо​нельзя перемещать отдельные ячейки,​ для результатов.​ их к собственным​Для вычисления нескольких значений​ значений.​ пригодятся. Подходы приведены​​.​​Ctrl+Shift+Enter​ порядковый номер диапазона​= ИНДЕКС(массив; номер_строки; [номер_столбца])​ B10 («Май»).​ возврата одного или​ 10%. Чтобы программа​суммировать все n-ные значения​1.  ​в строке формул выделим​

​ENTER​ содержащие формулу, но​»Всегда» значит в 100​ данным, воспользуйтесь предоставленными​ с помощью формулы​В программе Excel существует​

• ​ в данном уроке.​Текущий массив будет выделен:​.​

• ​ из переданных в​= ИНДЕКС(ссылка; номер_строки; [номер_столбца];​Искомая ячейка находится в​ нескольких значений элементов​

• ​ поняла это, мы​ в определенном диапазоне.​2. из книги авторов​ аргумент функции СУММ(),​

• ​комбинацию клавиш​ вы можете перемещать​ процентах случаев.​

Попробуйте попрактиковаться

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

​ качестве аргумента ссылка,​ [номер_области])​ диапазоне B2:G8 на​ массива (константы массивов​ используем функцию как​Когда мы используем формулы​ Colin Wilcox и John​ т.е. $A$2:$A$12^2;​CTRL+SHIFT+ENTER​ их в виде​Введите следующую формулу. Для​

​В книге ниже приведены​ массив в диапазон​ массива: массива формулы​ выделять, удалять и​Ctrl+/​ т.е. возвращает массив,​ в котором будет​​Описание аргументов:​ пересечении строки и​​ в Excel, обозначаемые​ массив.​ массива, Excel видит​ Walkenbach​

​ несколько полезных правил​ и нажмите комбинацию.​

1. ​ у начинающих пользователей.​ столбца и строки.​ функции ИНДЕКС формы​ получим:​ Если в качестве​

​ массива. Нам нужно​ а как единый​При создании Именованных​

​ часть формулы;​ это первые буквы​

1. ​ с ними. Чтобы​ формула появится в​ книгу на компьютер,​ что и аргументы​ массива, вычисление нескольких​ по работе с​Самое простое, что Вы​ Давайте рассмотрим несколько​ Нумерация диапазонов начинается​

   ​ массива, принимающий ссылку​

2. ​Пример 2. Определить, сколько​

​ первого аргумента функции​ узнать, сколько мы​

1. ​ блок данных.​ формул и правил​

2. ​получим {1:4:9:16:25:36:49:64:81:100:121} – тот​ от названия клавиш,​ переместить все ячейки,​ последней выбранной ячейке.​

   ​ нажав значок Excel​

3. ​ массива.​

​ значений. Некоторые функции​ ними. Если желаете​ можете сделать с​ правил, которые необходимо​ с единицы (1).​

support.office.com

Правила изменения формул массива

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

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

​ функции ИНДЕКС все​ Диапазон ячеек или​ если бы купил​ быть выполнена как​ Функция: . Итого​ диапазоном ячеек и​ нажимая​ случае.​

​hift,​ выберите новое расположение​ формул:​ откройте версию приложения​ формулу массива.​ значений в качестве​ в Excel, читайте​

• ​ его. Для этого​Нельзя изменять содержимое одной​ несмежные диапазоны, переданные​ массив, указанные в​ 2 л молока​ формула массива (Ctrl+Shift+Enter).​ – 346 руб.​ с отдельной ячейкой.​CTRL+SHIFT+ENTER​Т.е. нажатие​E​ и нажмите сочетание​=C2:C11*D2:D11​ Excel для настольных​

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

• ​ и 4 кг​Ссылочная форма – функция​Функция «Сортировки» в формуле​ В первом случае​. Эти формулы вводятся​CTRL+SHIFT+ENTER​

• ​nter). После этого формула​ клавиш CTRL+V;​

​Нажмите клавиши CTRL+SHIFT+ВВОД.​ систем.​В формулах массива используется​ читайте в статье​Знакомство с формулами массива​ массив и нажать​ массива. Но к​ ссылка, должны находиться​ могут содержать:​ яблок на основании​ ИНДЕКС используется для​ массива. Отсортируем суммы​

support.office.com

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

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

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

​ данных, содержащихся в​ возврата ссылки на​ к оплате в​​ для столбца «К​​ листа. Однако, если​ суммированием произвести промежуточные​​ фигурные скобки {​​ формуле массива (появится​ одной ячейкой​ и вставьте его​ Они все начинаются​ формул массива.​Многоячеечные формулы массива в​Delete​ применить свое форматирование.​Примечания:​ В этом случае​​ таблице к первому​​ определенную аргументами ячейку​

​ порядке возрастания. Для​ оплате». Во втором​ формуле массива присвоить​ вычисления с диапазоном​​ } (их не​​ ошибка «Нельзя изменять​​В образце книги выберите​​ в Excel в​ со знака равенства​Этот тип формулы массива​ Excel​.​Нельзя удалять ячейки, которые​​Аргументы номер_строки и [номер_столбца]​​ один из последующих​​ примеру.​​ или несколько ячеек​ списка отсортированных данных​ – итоговую сумму​ Имя, то EXCEL​ ячеек (с массивом​ вводят с клавиатуры,​ часть массива»), но​​ ячейку B13.​​ ячейке A1. Обязательно​ (=) и могут​ позволяет упростить модель​

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

​Для расчетов используем следующую​ нескольких диапазонов ячеек.​

• ​ его. В строке​​Выделяем диапазон Е3:Е8.​​ с ней нужно​ значений). Для самой​
• ​ после нажатия​ всю формулу и​ из методов из​​ E2: E11, введите​​ Excel.​
• ​ нескольких отдельных формул​​Массивы констант в Excel​​ складывает значения двух​ только весь массив.​
• ​ массива или диапазона,​ (строка или столбец​ формулу:​​ Если эти диапазоны​​ формул вводим .​В строку формул вводим​ делать. Например, если​

​ функции СУММ() ничего​CTRL+SHIFT+ENTER​ Начать сначала.​ шага 2 предыдущего​ формулу​
​В приведенном ниже примере​ одной формулой массива.​Применение формул массива в​

• ​ диапазонов. Из рисунка​Нельзя перемещать ячейки, которые​
• ​ переданных в качестве​ соответственно).​Функция СУММ возвращает сумму​
• ​ не являются смежными,​​ Жмем сочетание Ctrl​​ следующую формулу: =C3:C8*D3:D8.​ формуле =СУММ($A$2:$A$12^2) присвоить​
• ​ не изменилось –​). Это обрамление показано​Нельзя добавлять новые ячейки​ примера:​

​= C2: C11 *​​ формулы однотипных общие​​Щелкните ячейку, в которую​ Excel​ видно, что при​ входят в формулу​ аргументов массив или​Несколько строк и столбцов.​ значений в ячейках,​ можно явно указать​ + Shift +​Нажимаем одновременно клавиши: Ctrl​ имя Сумма_квадратов, а​ она получила тот​ на рисунке выше​ к блоку результатов​=СУММ(C2:C11*D2:D11)​ D2: D11​ файлы по цене​ нужно ввести формулу​Подходы к редактированию формул​ вводе формулы мы​ массива. Зато можно​

​ ссылка, иначе функция​ Функция ИНДЕКС вернет​ определенных функциями ИНДЕКС,​ диапазон, в котором​ Enter.​ + Shift +​ затем в ячейке​ же массив, только​ (см. Строку формул).​ ячеек, но добавлением​Нажмите клавиши CTRL+SHIFT+ВВОД.​, и нажмите клавиши​ каждого столбца, формула​ массива.​

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

​Если бы мы нажали​ новых данных на​Формула умножает значения в​ Ctrl + Shift​ расположенная в выбранных​

​Введите необходимую формулу.​Урок подготовлен для Вас​ наша задача ее​Нельзя вставлять новые ячейки,​ #ССЫЛКА!.​ строкой или столбцом​
​ содержащиеся в ячейках​

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

​ не прямо из​ просто​ листе и затем​

​ диапазонах ячеек C2:C11​

​ + Ввод, чтобы​ ячейках строки 5.​В формулах массива используется​

​ командой сайта office-guru.ru​​ исправить.​
​ в том числе​Если в качестве аргументов​ массива (диапазона), переданного​ B12 и B13​ принимающие в качестве​​ с двумерными массивами.​​Формула после нажатия Ctrl​Массив функций Excel позволяет​ диапазона ячеек, как​ENTER​ разверните формулу.​ и D2:D11, а​ упростить формулы массива.​Нажмите клавиши CTRL+SHIFT+ВВОД.​ синтаксис обычных формул.​Автор: Антон Андронов​Чтобы отредактировать формулу массива,​ строки и столбцы,​ номер_строки и (или)​

excel2.ru

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

​ в качестве первого​ (2 л, 4​ аргументов не ссылочные​ Функция «ТРАНСП» возвращает​ + Shift +​ решать сложные задачи​

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

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

​Автор: Антон Андронов​ выполните следующие действия:​ в диапазон массива.​ [номер_столбца] указаны значения​ аргумента, если указан​ кг) соответственно.​ данные, будут интерпретировать​ сразу несколько значений.​ Enter оказалась в​

​ в автоматическом режиме​ обычной формулой. Понятно,​

• ​ сообщение об ошибке​ сочетание клавиш CTRL+SHIFT+ВВОД.​ получая общую сумму.​
• ​Тип автомобиля​ открывающей и закрывающей​

​ со знака равенства​

• ​Примечание:​Выделите диапазон массива любым​
• ​Нельзя использовать многоячеечные формулы​ 0 (нуль), результатом​

​ только номер его​Результат вычислений:​ возвращенный функцией ИНДЕКС​ Преобразует горизонтальную матрицу​ фигурных скобках. Она​

​ одновременно. Те, которые​ что вместо функции​ #ЗНАЧ!, возникающую при​Наконец, можно сэкономить время​Примечание:​Число проданных единиц​ фигурными скобками (​

​ (=) и могут​Мы стараемся как​

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

​ подставилась автоматически в​ выполнить посредством обычных​ СУММ() в формуле​ использовании неверного типа​ при использовании константы​Мы стараемся как​

​Цена за единицу​

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

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

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

​Итоги продаж​).​ Excel.​ вас актуальными справочными​ случае это диапазон​ команды​ из строки или​

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

​ вас актуальными справочными​Ильина​Примечание:​Например эта формула вычисляет​ материалами на вашем​ C1:C12.​

1. ​Таблица​ столбца либо всего​
2. ​ заполнения первый аргумент​
3. ​ суммарные затраты на​ ссылки на нее.​ строк = числу​

​Если попытаться изменить данные​ которые одновременно обрабатывают​ функция MS EXCEL: СРЗНАЧ(), МАКС(),​ в качестве аргумента​ ввода символов в​ материалами на вашем​Седан​

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

​ покупку двух любых​

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

​ Например, функция =ФАКТР(ИНДЕКС(A4:B7;6;2))​ столбцов в таблице​ в какой-либо ячейке​ группу данных и​ НАИБОЛЬШИЙ() и т.п.​ только диапазон ячеек​ строке формул. Но​ языке. Эта страница​5​ вокруг формулы не​ котировок акций и​ переведена автоматически, поэтому​ формулы, для этого​Как видите, все перечисленные​

1. ​ в качестве аргумента​ принимающий ссылку на​ продуктов в любых​ вернет факториал числа,​ с исходными данными.​ столбца «К оплате»​ сразу выдают результат.​Вышеприведенный пример иллюстрирует использование​
2. ​ (или формулу, результатом​ есть несколько редактирования​ переведена автоматически, поэтому​2200​ преобразует его в​ поместит результат в​ ее текст может​ щелкните по строке​ выше правила подчеркивают,​ массив. Это справедливо​ один или несколько​
3. ​ количествах за любой​ которое содержится в​ А количество столбцов​ — ничего не​ Рассмотрим подробно работу​ функции массива возвращающей​ вычисления которой является​ и правила использования.​ ее текст может​=C2:C11*D2:D11​ формуле массива —​ ячейке рядом с​ содержать неточности и​ формул или нажмите​ что массив –​ и для функции​ диапазонов ячеек. Ссылки​ из указанных месяцев.​
4. ​ ячейке B6. Если​ = числу строк​ выйдет. Формула в​ с массивами функций​ единственное значение, т.е.​ диапазон, или константы).​ Дополнительные сведения о​ содержать неточности и​Купе​ необходимо нажать сочетание​

​ кнопкой «Общее значение».​

​ грамматические ошибки. Для​ клавишу​ это одно целое.​ ИНДЕКС ссылочной формы,​ на два и​Пример 3. Организовать фильтрацию​ результат выполнения функции​ в исходном массиве.​ массиве защищает значения​ в Excel.​ результат может быть​ В нашем случае​ них: Использование констант​

exceltable.com

Примеры функции ИНДЕКС для создания массивов значений в Excel

​ грамматические ошибки. Для​4​ клавиш Ctrl +​Эта формула сначала умножает​ нас важно, чтобы​F2​ Если не выполнить,​ только вместо массива​

Примеры использования функции ИНДЕКС в Excel

​ более несмежных диапазона​ данных по трем​ ИНДЕКС используется в​

1. ​ Вводим формулу: .​ диапазона от изменений.​Массив – данные, объединенные​ выведен в одной​ мы в качестве​ массива в формулах​ нас важно, чтобы​1800​ Shift + Ввод,​ количество акций (ячейки​ эта статья была​. Excel удалит фигурные​ хотя бы одно​ значений будет возвращена​
2. ​ должны быть заключены​ критериям из нескольких​ качестве аргумента функции,​ Получается «перевернутый» массив​ На экране появляется​ в группу. В​ ячейке. Это достигается​ аргумента ввели не​ массива.​ эта статья была​Егоров​ чтобы создать формулу​ B2–I2) на их​ вам полезна. Просим​ скобки вокруг формулы​ из вышеперечисленных правил,​ ссылка не весь​ в скобки (например,​ таблиц.​ принимающей данные ссылочного​ данных.​ соответствующая запись:​ данном случае группой​ использованием функций способных​ диапазон, а некое​Вводная статья для тех,​ вам полезна. Просим​Седан​ массива. Любое время​ курс (ячейки B3–I3),​ вас уделить пару​ массива.​ Excel не даст​ столбец, всю строку​ функция с аргументами​Вид таблиц данных:​ типа, он будет​

​Поиск среднего значения без​

Динамическая выборка из массива значений ячеек

​Рассмотрим другие примеры использования​ является массив функций​ «свернуть» вычисленный массив​ выражение, которое еще​ кто никогда не​ вас уделить пару​6​ изменить формулу массива,​

​ а затем складывает​

​ секунд и сообщить,​Внесите необходимые корректировки в​ отредактировать массив и​

​ или весь диапазон​

• ​ ((A1:B4;D4:E9);2;2;1), принимающая ссылку​Для быстрого поиска оценки​ определен в качестве​ учета нулей. Если​
• ​ функций массива Excel​ в Excel. Любую​ до одного значения​ нужно вычислить перед​ использовал формулы массива.​ секунд и сообщить,​
• ​2300​ фигурные скобки (​ результаты, получая общую​ помогла ли она​ формулу:​ выдаст следующее предупреждение:​

​ (если оба аргумента​ на несмежные диапазоны​ у любого ученика​ ссылки на ячейку.​ мы воспользуемся стандартной​ – рассчитаем итоговую​

Стратегический анализ расходов в Excel

​ таблицу, которую мы​ (СУММ(), СРЗНАЧ(), МАКС()).​ суммированием, поэтому и​Без формул массива (array​ помогла ли она​Купе​{}​ сумму 53 184.​ вам, с помощью​А затем нажмите комбинацию​

​Если необходимо изменить формулу​

​ принимают значение 0).​ A1:B4 и D4:E9).​

​ за любую контрольную​ Например, функция =СТРОКА(ИНДЕКС(A4:B7;6;2))​ функцией «СРЗНАЧ», то​ сумму коммунальных платежей​ составим и заполним​ Примеры таких функций​ получили ошибку.​ formulas) можно обойтись,​

​ вам, с помощью​

​8​) исчезнут из формулы​ В этом примере​ кнопок внизу страницы.​ клавиш​ массива, то первое,​Если требуется, чтобы функция​ Один из последующих​

Пример формулы ИНДЕКС и ПОИСКПОЗ в Excel

​ в любой из​ вернет число 4,​ получим в результате​ с помощью одной​

​ в Excel, можно​

​ массива приведены в​Чтобы глубже понять формулы​ т.к. это просто​ кнопок внизу страницы.​1700​ массива и внести​ используется формула массива,​

1. ​ Для удобства также​
2. ​Ctrl+Shift+Enter​
3. ​ что нужно сделать​

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

​ то есть номер​ «0». И это​ формулы.​ назвать массивом. Пример:​ статье Формулы массива,​ массива проведем эксперимент:​ сокращенная запись группы​ Для удобства также​Еременко​ изменения в формуле​ расположенная только в​ приводим ссылку на​, чтобы сохранить изменения.​ – это выделить​ значения нескольких ячеек,​ для заполнения, если​

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

Особенности использования функции ИНДЕКС в Excel

​ возвращающие одно значение.​выделим ячейку​

​ однотипных формул. Однако,​

​ приводим ссылку на​Седан​

​ массива и добавлять​

• ​ одной ячейке.​ оригинал (на английском​ Формула будет отредактирована.​ диапазон, в котором​ а не одной​ в качестве данного​ содержащих:​ содержится ячейка B6.​ вставляем в формулу​ «Итого»).​

1. ​ элементов различают массивы:​Формулы массива также могут​B13​ у формул массива​ оригинал (на английском​3​ фигурные скобки необходимо​
2. ​Нажмите клавиши CTRL+SHIFT+ВВОД.​ языке) .​Очень часто возникает необходимость​ содержится массив. В​ ячейки, находящейся на​ аргумента передана ссылка​1 Фамилии учеников:​​ дополнительное условие: 0;A1:A8))’​Вводим формулу вида: =СУММ(C3:C8*D3:D8).​

• ​одномерные (данные находятся в​ возвращать сразу несколько​, содержащую обычную формулу​ есть серьезное преимущество:​ языке) .​2000​ нажать сочетание клавиш​Формула автоматически вставляется между​Формулы массива — мощные​ уменьшить или увеличить​ Excel существует, как​ пересечении указанных номеров​ на области, состоящие​2 Номера контрольных работ:​Пример 1. В таблице​ class=»formula»>. Получаем:​Нажимаем сочетание клавиш: Ctrl​ ОДНОЙ строке или​ значений. Понятно, что​ =СУММ($B$2:$B$12);​ одна такая формула​Предположим, вы пытаетесь изменить​
• ​Купе​ Ctrl + Shift​ открывающей и закрывающей​ формулы, которые позволяют​ количество ячеек в​ минимум, 3 способа​ строки и столбца,​ из одной строки​3 Номера четвертей:​ Excel указаны данные​Скачать примеры массива функций​
• ​ + Shift +​ в ОДНОМ столбце);​ для того чтобы​в Cтроке формул выделим​ может заменить один​ формулу массива, но​1​ + Ввод еще​ фигурными скобками (​ выполнять сложные расчеты​
• ​ формуле массива. Скажу​ сделать это:​ она должна быть​ или одного столбца.​В ячейку, в которой​ о средней стоимости​Распространенная ошибка при работе​ Enter. Результат:​двумерные (НЕСКОЛЬКО строк и​ отобразить такой результат​ аргумент функции СУММ(),​ или несколько столбцов​ при этом запутались.​1600​ раз.​{ }​ часто невозможно стандартных​ сразу, что дело​Выделить диапазон массива вручную,​

​ выполнена в качестве​

1. ​номер_строки – обязательный для​ будет выводится результат,​ некоторых продуктов в​ с массивами функций​Формула массива в данном​ столбцов, матрица).​ необходимо задействовать целый​ т.е.​ с обычными формулами.​
2. ​ Вы выделяете формулу​Климов​Если необходимо включить новые​).​ функций. Они также​ это не простое​ т.е. с помощью​ формулы массива (сочетание​ заполнения аргумент (если​ запишем следующую формулу:​ определенные месяцы года.​ – НЕ нажатие​ случае заменила две​Одномерные массивы бывают:​ диапазон ячеек. Примеры​$B$2:$B$12​Например, можно найти сумму​ в ячейке или​Седан​ данные в формуле​
3. ​Примечание:​ называются «Сочетание клавиш​ и в большинстве​ мыши. Это самый​ клавиш Ctrl+Shift+Enter).​ следующий явно не​В качестве первого аргумента​ Найти стоимость молока​ кодового сочетания «Ctrl​ простые формулы. Это​горизонтальными (данные – в​

exceltable.com

​ таких функций приведены​

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

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

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

Когда мы говорим о формулах массива, мы подразумеваем, что это нормальная формула Excel (СУММ, МАКС, СЧЁТЕСЛИ…), но немного измененная, чтобы принять в качестве входных данных массив или набор массивов. Это то, что лежит в основе формул массива и делает его столь мощным.

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

=АДРЕС(МИН(ЕСЛИ((A1:A9)=МИН(A1:A9);СТРОКА(A1:A9);»»));1)

Результатом в этом случае будет адрес ячейки с наименьшим значением в данном диапазоне. Как вы можете увидеть, при обновлении данных в диапазоне, результат тоже меняется. Подобного эффекта можно также добиться с помощью обычной формулы =АДРЕС(ПОИСКПОЗ(МИН(A1:A9);A1:A9;0);1). Однако ж мы только начали изучение, в дальнейшем вы обнаружите, что некоторые вещи можно делать только с помощью формулы массива, либо с большим количеством обычных формул.

Части формул массивов в Excel

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

(Прежде чем мы пойдем дальше, убедитесь, что при вводе формул массива, вы вводите Ctrl + Shift + Enter, а не обычный Enter, как при обычных формулах).

Массив констант в формулах массивов

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

Горизонтальный массив констант

Горизонтальный массив констант вводиться как последовательность чисел, разделенных точкой с запятой (;), заключенных в фигурные скобки. Например: {1;2;3;4;5}. Горизонтальные массивы могут быть использованы в качестве входных данных для формулы массива. Они также могут быть введены в таблицу, как показано ниже.

Вертикальный массив констант

В отличие от горизонтального, в вертикальном массиве констант значения разделяются двоеточием (:) и также заключаются в фигурные скобки. Например: {1:2:3:4:5}.

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

Оператор массива сообщает формуле, какую операцию необходимо совершить над массивами, предоставленными в качестве массива. К тому же, вы можете использовать операторы И (альтернативный вариант написания — *) и ИЛИ альтернативный вариант написания — +).

Оператор массива И

Оператор И возвращает значение ИСТИНА в случаях, когда все условия выражения возвращают значение ИСТИНА. Пример ниже показывает использование оператора массива И (*) между массивами:

Оператор массива ИЛИ

Оператор ИЛИ возвращает значение ИСТИНА, если хотя бы один из условий выражения возвращает значение ИСТИНА. Пример ниже показывает использование оператора массива ИЛИ (+) между массивами:

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

Диапазон массива вводиться точно также, как и обычная формула (например, A1:A10). Их не обязательно сразу же заключать в скобки (Например, =СУММ(ЕСЛИ((A1:A10)=10;10;»»)) ) или (=СУММ(ЕСЛИ((A1:A10=10);10;»»))). Но для упрощения отладки, я предпочитаю сразу установить скобки в формулах.

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

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

Сортировка с помощью формулы массива

Скажем, у вас есть набор данных в ячейках D2:D10 и вы хотите отсортировать их в порядке возрастания. Вы уже наверное догадались, что нам понадобиться функция НАИМЕНЬШИЙ(), которая возвращает n-ое наименьшее значение и заданного диапазона. Нам также понадобиться диапазон, где мы будем производить вычисления.

Обычная функция НАИМЕНЬШИЙ для одной ячейки выглядит следующим образом =НАИМЕНЬШИЙ(D2:D10;1). Такая формула вернет нам наименьшее значение диапазона D2:D10. Но нам необходимо скопировать эту функцию во все остальные ячейки и внести изменения во второй аргумент, чтобы получить отсортированный список. Для начала выделим диапазон, в котором мы хотим увидеть список, затем вводим формулу в первую ячейку и жмем Ctrl + Shift + Enter. Формула будет скопирована на весь диапазон, результатом станет отсортированный список.

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

Предположим, мы хотим выяснить имя менеджера с наибольшими продажами. Вот где обнаруживается истинная мощь формул массивов. Ели бы мы использовали обычные формулы, нам понадобилось бы столько же строк, сколько менеджеров, если не больше. Однако мы можем сделать тоже самое в одну формулу массива =СМЕЩ(A1;МАКС(ЕСЛИ(СУММЕСЛИ((A2:A10);(A2:A10);(D2:D10))=МАКС(СУММЕСЛИ((A2:A10);(A2:A10);(D2:D10)));СТРОКА(A2:A10);»»))-1;0). То, что мы делаем здесь – это сравниваем сумму продаж конкретного менеджера с суммой продаж максимального менеджера. Если условие истинно, возвращает номер строки. Функция ЕСЛИ возвращает массив номеров строк, относящихся к менеджеру с наибольшим показателем продаж, в противном случае возвращается пустота. С помощью функции МАКС мы находим строку, где происходит последнее вхождение имени, а затем с помощью функции СМЕЩ возвращаем имя из этой строки.

Консолидация данных по более чем одному условию

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

Еще один пример консолидации данных по условию

Теперь попробуйте сами. Дайте мне сумму продаж всех записей, в которых 1) менеджер — Фёдор Абрамов И продукт Книги 2) Продажи >= 500. Подумайте минутку.

Мы можем достичь этого с помощью формулы массива =СУММ(ЕСЛИ(((A2:A10=»Фёдор Абрамов»)*(B2:B10=»Книги»))+((D2:D10>=500));D2:D10;0)). Так что же мы здесь сделали? Мы проверили три условия – первые два были скомбинированы с помощью оператора И (*) и третье было добавлено с помощью оператора ИЛИ (+). В результате получилась структурированная формула массива, где были указаны все три условия в качестве аргумента функции ЕСЛИ. Функция ЕСЛИ в свою очередь генерирует массив со значениями из четвертого столбца, когда оно принимает значение ИСТИНА и 0, если ЛОЖЬ. Результат, конечно, представляет собой сумму продаж отвечающим всем трем, указанным выше, условиям.

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

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

Без формул массива (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 , т.е. вычислим, выделенную часть формулы;
• получим {1:4:9:16:25:36:49:64:81:100:121} – массив квадратов значений из столбца В . Массив – это просто набор неких элементов (значений).

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

• выделим ячейку, содержащую формулу массива =СУММ($A$2:$A$12^2) ;
• в строке формул выделим аргумент функции СУММ() , т.е. $A$2:$A$12^2 ;
• нажмем клавишу F9 , т.е. вычислим, выделенную часть формулы;
• получим {1:4:9:16:25:36:49:64:81:100:121} – тот же массив, что и в первом случае.

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

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

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

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

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

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

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