Как ввести формулу массива в excel: Видео: формулы массива — Служба поддержки Майкрософт

Видео: формулы массива — Служба поддержки Майкрософт

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

Обучение работе с Excel 2013

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

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

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

• Формулы массива
  Видео
• Использование функций СУММ, СРЗНАЧ и МАКС
  Видео
• Изменение и удаление формул массива
  Видео

Далее: Создание раскрывающихся списков и управление ими

Вы можете создавать формулы массива, которые часто называют формулами CTRL-SHIFT-ВВОД или CSE-формулами, для выполнения вычислений, возвращающих один или несколько результатов.

Подробнее о них вы узнаете из этого видео.

Назначение формул массива

Формулы массива часто называют CSE-формулами, поскольку для их ввода используется сочетание клавиш CTRL+SHIFT+ВВОД, а не простое нажатие клавиши ВВОД.

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

• Подсчет числа знаков в диапазоне ячеек.

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

• Суммирование всех n-х значений в диапазоне значений.

Ввод формулы массива

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

2. Введите формулу.

3. Нажмите клавиши CTRL+SHIFT+ВВОД. Excel заполнит результатом каждую из выбранных ячеек.

Хотите узнать больше?

Использование формул массива: рекомендации и примеры

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

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

Excel работа с массивами данных

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

​Смотрите также​ A10:h21 в нашем​   ​ памяти компьютера), а​ возвращают одно значение​ массивами констант и​В этом примере функция​

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

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

1. ​ рисунке ниже. ​(MAX) и заменяем​Эта статья поможет разобраться,​
2. ​ случае)​​в качестве аргумента функции​​ затем сложил все​ и те, что​ их применением в​​ИНДЕКС​​ сразу несколько массивов.​

С формулой массива

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

1. ​Вспомните детство, школу, свою​ выделяем наш массив​ элементы этого нового​ дают на выходе​ Excel. Если желаете​
2. ​возвращает значение элемента​ Например, следующая формула​ (см. рисунок выше).​Другим подходом для формирования​ ​ вводимую непосредственно в​​ ее в новом​​ формула массива, которая​​ Excel существует, как​​ определенные трудности, особенно​​F9​​на​​ массива в Excel.​​ тетрадку по математике. ..​​ ячеек A1:B8​
3. ​ массива.​​ целый набор (массив)​​ получить еще больше​

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

​.​

• ​C2:C6​ Помещённая в одну​ На обороте тетради​жмем​Обратите внимание на фигурные​ значений. Рассмотрим их​
  ​ информации о массивах,​​ положение которого задано​
• ​ которое получится в​​ формировать с помощью​​ использование формулы СТРОКА(A1:A3)​​ можно сформировать вручную,​ формулах такой подход​ диапазонов. Из рисунка​​ сделать это:​​ Давайте рассмотрим несколько​

Клавиша F9

​Отлично! Элементы вертикального массива​, а​ ячейку формула массива​ на обложке было​

1. ​Ctrl + Shift +​​ скобки, появившиеся в​​ на простых примерах. ..​ читайте следующие статьи:​
2. ​ порядковым номером (оценкой).​​ результате сложения двух​​ следующей формулы: СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))​

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

​Задача: рассчитать общую сумму​Знакомство с формулами массива​
​Данная формула не является​
​ массивов констант:​

​Здесь функция ЧСТРОК() возвращает​

office-guru.ru>

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

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

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

​ в Excel​ формулой массива, хоть​=МАКС({1;2;3;4;5;6}+{7,8,9,10,11,12})​ количество значений в​ есть два недостатка:​ функций, например СТРОКА(). ​Если же необходимо изменить​ допустили небольшую ошибку,​​ мыши. Это самый​​ начать редактирование массива.​

​ англоязычной версии Excel)​B2:B6​Не прибегая к помощи​При помощи формул массива​ массив» в качестве​формулы массива​ классическим путем, то​Многоячеечные формулы массива в​ она и содержит​

1. ​Массивы констант могут содержать​ исходном списке (9).​при вставке новых строк​ Также с помощью​ расположение массива на​
2. ​ наша задача ее​ простой, но в​Нельзя изменять содержимое одной​ или двоеточием (в​
3. ​.​ формулы массива, нам​ она вся делается​ результата:​
4. ​. Вводить их вручную​ нужно будет добавить​ Excel​ массив. Поэтому при​
5. ​ числа, текст, логические​ После выполнения функции​ на лист перед​ формул можно динамически​​ рабочем листе, не​​ исправить.​

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

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

​ ее вводе достаточно​ значения и значения​ СТРОКА() получим массив​ строками 1-3, формула​ изменять длину числовой​ меняя его размерность,​Чтобы отредактировать формулу массива,​ непригодный способ.

​ массива. Но к​

1. ​ Элементы горизонтального массива​Ctrl+Shift+Enter​ последовательность действий, чтобы​выделяем диапазон B2:K11​ не в одной​ — они автоматически​
2. ​ цену и количество,​​ Excel​​ нажать клавишу​ ошибки​ последовательных чисел {1:2:3:4:5:6:7:8:9},​​ автоматически модифицируется в​ последовательности.​ просто переместите его​​ выполните следующие действия:​​С помощью диалогового окна​​ каждой ячейке можно​​ констант разделяются запятой​

   ​.​​ определить максимальное развитие​​вводим формулу =A2:A11*B1:K1​ ячейке (как в​ появляются при нажатии​​ а потом взять​​Редактирование формул массива в​

   ​Enter​

3. ​#Н/Д​​ причем этот массив​​ СТРОКА(A2:A4) или СТРОКА(A1:A4)​Создание массива последовательных чисел​ как обычный диапазон.​

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

​Выделите диапазон массива любым​Выделить группу ячеек​ применить свое форматирование. ​ (в англоязычной версии​Замечание:​ в столбце​жмем​ Примере 1), а​​Ctrl + Shift +​​ сумму по этому​

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

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

​Нельзя удалять ячейки, которые​ Excel) или точкой​

1. ​Формула массива в​Progress​Ctrl + Shift +​ в нескольких ячейках​ Enter.​
2. ​ столбцу. Если же​Применение формул массива в​Конечно же, мы в​={12;»Текст»;ИСТИНА;ЛОЖЬ;#Н/Д}​ зависимости от длины​​ неправильный массив {2:3:4}​​ для решения конкретного​ редактированию размеров массива,​ способов. В нашем​
3. ​ любую ячейку, которая​ входят в формулу​
4. ​ с запятой (в​ строке формул заключена​​.​​ Enter​ (как в Примере​

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

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

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

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

​ пригодятся. Подходы приведены​ C1:C12.​А затем на вкладке​ только весь массив.​Урок подготовлен для Вас​

​{}​ развитие для каждого​ ее как формулу​ не позволит редактировать​ поменять местами строки​ гораздо красивее:​ массива в Excel​ ячейки и получить​ нужен такой массив?​ чисел соответствующего размера.​ СТРОКА(ДВССЫЛ(«A1:A3»))​

• ​Пусть исходный список содержит​ в данном уроке.​
• ​Перейдите в режим редактирования​ Главная из раскрывающегося​
• ​Нельзя перемещать ячейки, которые​ командой сайта office-guru. ru​
• ​. Не пытайтесь ввести​
• ​ студента.​ массива​
• ​ или удалить одну​ и столбцы, т.е.​

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

​ Отвечу на него​

office-guru.ru>

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

​В Microsoft Excel можно​формула возвращает статичный массив.​ числовые значения (см.​Итак, сегодня Вы научились​ формулы, для этого​ списка​ входят в формулу​Источник: http://www.excel-easy.com/functions/array-formulas.html​ их вручную, скобки​Затем при помощи функции​и получаем результат:​ отдельно взятую формулу​ развернуть таблицу на​

​С7​

​ командой сайта office-guru.ru​Но грамотнее будет использовать​ в виде примера.​ создавать массивы, которые​

​Массив последовательных чисел переменной​ Файл примера).​ выделять, удалять и​

​ щелкните по строке​Найти и выделить​ массива. Зато можно​

​Перевел: Антон Андронов​ исчезнут при попытке​МАКС​
​Посмотрите как при помощи​ (например в ячейке​​ бок, чтобы данные,​​вводим с клавиатуры​Автор: Антон Андронов​ многоячеечную формулу массива. ​На рисунке ниже приведен​ не хранятся в​ длины можно сформировать​Сумму 3-х наибольших значений​​ редактировать формулы массива,​​ формул или нажмите​выберите пункт​ переместить весь массив.​Автор: Антон Андронов​ отредактировать формулу.​(MAX) мы найдём​ одной формулы массива​​ D10) и выдаст​​ которые раньше шли​=СУММ(​

​Автор: Антон Андронов​ Выглядеть она будет​ список студентов, которые​

​ диапазонах ячеек. Их​

​ с помощью формулы​ можно подсчитать с​

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

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

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

​Диапазон данных (массив констант)​ столбце​ выбираются данные по​Невозможно изменить часть массива​ располагались в столбцах​B2:B5​массивом​Мы можем пойти еще​

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

​Progress​ определенному товару и​.​

​ и наоборот. В​вводим знак умножения (​обычно понимают набор​ дальше и присвоить​ из числового вида​. В этом уроке​G2​ {1:2:3} вручную, можно​ ними. Если желаете​ скобки вокруг формулы​Выделить группу ячеек​ в диапазон массива.​

excel2.ru>

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

​ массивов в Excel.​ Excel, не в​.​ заказчику:​Для редактирования формулы массива​​ математике такая операция​​звездочка​ данных, объединенных в​ массиву констант имя.​ в ее словесное​ Вы узнаете, что​пользователем задается последний​

Коротко о массивах констант

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

​В данном случае формула​

​ необходимо выделить весь​ называется транспонированием. При​)​ группу. Массивы бывают​ Имя назначается точно​

​ описание и вывести​

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

​ мы продолжим изучение​

​ констант можно представить​ вспомогательный диапазон в​ массива синхронно пробегает​ диапазон (A10:h21 в​​ помощи формулы массива​​выделяем диапазон​

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

​ так же, как​ соответствующие значения в​ констант и как​ сумму скольких наибольших​ ячеек​

Пример применения массива констант в Excel

​ в Excel, читайте​ формулу:​ и нажмите​

​ созданных с помощью​ формул массива, но​ в таком виде:​ столбце​ по всем элементам​ нашем случае) и​ и функции​C2:C5​(элементы массива образуют​ и обычной константе,​ диапазоне C2:C7. В​ с ними работать​ требуется найти). В​

​B1:B3​

​ следующие статьи:​А затем нажмите комбинацию​ОК​ команды​ с большим уклоном​{19;33;63;48;13}​

​D​ диапазонов C3:C21 и​ изменить формулу в​

​ТРАНСП (TRANSPOSE)​​и закрываем скобку​​ строку или столбец)​ через диалоговое окно​ данном случае создавать​ в Excel. ​

​ итоге получим =СУММПРОИЗВ(НАИБОЛЬШИЙ(Список;СТРОКА(ДВССЫЛ(«A1:A»&G2))))​значения 1, 2​Знакомство с формулами массива​ клавиш​.​Таблица​​ на их практическое​​.​

​. Excel может хранить​ B3:B21, проверяя, совпадают​ строке формул (или​это делается на​ функции СУММ -​

​ или​Создание имени​ отдельную табличку для​Чтобы создать массив констант,​

​В некоторых формулах массива​ и 3 соответственно.​ в Excel​Ctrl+Shift+Enter​Текущий массив будет выделен:​.​ применение. Итак, как​​Функция​​ данные в своей​

​ ли они с​ нажав​​ раз.​​ в итоге должно​двумерные​:​

​ хранения текстового описания​ введите его элементы​

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

​При помощи комбинации клавиш​Как видите, все перечисленные​ же изменить уже​М​ памяти. Диапазон данных,​ заданными значениями из​F2​Допустим, имеем двумерный массив​

• ​ получиться так:​(матрица). Легко сообразить,​
• ​Не забывайте указывать знак​ оценок не имеет​
• ​ и заключите их​ массива последовательных чисел​
• ​ ячейку, в Строке​ Excel​
• ​ Формула будет отредактирована.​Ctrl+/​
• ​ выше правила подчеркивают,​ существующую формулу массива​

​АКС​ сохранённый в памяти​
​ ячеек G4 и​

​). Затем необходимо повторить​

office-guru.ru>

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

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

​ ячеек, который хотим​​чтобы Excel воспринял нашу​​ что почти в​ равенства в поле​ смысла, поэтому выгоднее​​ в фигурные скобки.​​ соответствовал размеру диапазона​ формул введите =B1:B3,​Одноячеечные формулы массива в​​Очень часто возникает необходимость​​. Для этого выберите​ что массив –​ в Excel?​(MAX) использует этот​ Excel, называется массивом​ G5. Если совпадения​

​ ввод измененной формулы​​ транспонировать. ​ формулу как формулу​ любой таблице Excel​Диапазон​ создать следующий массив​ Например, на рисунке​ с данными. Это​ затем выделите B1:B3​ Excel​ уменьшить или увеличить​ любую ячейку массива​ это одно целое.​Когда формула массива помещена​

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

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

1. ​Массивы констант в Excel​​ количество ячеек в​
2. ​ и нажмите комбинацию.​​ Если не выполнить,​
3. ​ в одну ячейку,​​ и возвращает результат​
4. ​Мы уже знаем, что​​ равенства ноль, если​​ клавиш​
5. ​ размещения транспонированной таблицы.​​ Enter, как обычно,​​ найти один или​ массив как текстовую​={«»;»Неудовл.»;»Удовл. «;»Хорошо»;»Отлино»}​ состоящий из 6​
   
   
6. ​ этот диапазон постоянно​F9​Применение формул массива в​ формуле массива. Скажу​Самое простое, что Вы​​ хотя бы одно​ то ее редактирование​

​63​

​ определить прогресс первого​ совпадение есть, то​Ctrl + Shift +​ Поскольку исходный массив​ а​ несколько таких массивов:​ строку.​В данном случае первый​ констант:​

​ добавляются значения.​. Формула B1:B3 будет​ Excel​ сразу, что дело​​ можете сделать с​​ из вышеперечисленных правил,​ в Excel обычно​.​ студента нам поможет​​ единица. Таким образом​ Enter.​

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

​ ячеек был 8​Ctrl + Shift +​Формулы массива​Теперь формула выглядит менее​ элемент массива содержит​={1;2;3;4;5;6}​Создадим Динамический диапазон Список​ заменена значениями, содержащимися​Подходы к редактированию формул​ это не простое​ массивом в Excel​ Excel не даст​ не представляет особой​При работе с формулами​​ формула, показанная на​​ суммы всех сделок,​Excel также не позволит​

​ строк на 2​ Enter​в Excel -​

• ​ пугающей:​ пустую строку, поскольку​Такой массив можно использовать​ (=СМЕЩ(Лист1!$A$2;;;СЧЁТ(Лист1!$A$2:$A$15))), который будет​ в этих ячейках,​ массива в Excel​ и в большинстве​ – это удалить​ отредактировать массив и​ сложности. Здесь главное​
• ​ массива можно использовать​ рисунке ниже.​
• ​ где заказчик не​ свободно перемещать ячейки,​ столбца, то надо​

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

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

​ т.е. {1:2:3}. Теперь​Урок подготовлен для Вас​ случаев будет проще​ его. Для этого​ выдаст следующее предупреждение:​ не забыть закончить​ режим просмотра содержимого​Чтобы из значений прогресса​ ANTON и товар​ входящие в формулу​ выделить диапазон пустых​Т.е. Excel произвел попарное​ для обработки данных​​ случаях массивы констант​​ 1 быть не​

​ Например, следующая формула​ исходного списка и​ достаточно скопировать этот​ командой сайта office-guru.ru​ удалить старый массив​ достаточно выделить нужный​Если необходимо изменить формулу​​ редактирование комбинацией клавиш​​ массива констант. ​ всех студентов найти​ не Boston Crab​ массива или добавлять​​ ячеек размером 2​ умножение элементов массивов​

​ из таких массивов.​ бывают даже очень​ может.​ суммирует значения этого​ автоматически расширяться при​ массив в Буфер​Автор: Антон Андронов​ и создать новый.​ массив и нажать​

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

​ массива, то первое,​Ctrl+Shift+Enter​В формуле выделите​ максимальное, мы добавляем​ Meat умножаются на​ новые строки-столбцы-ячейки в​

​ строки на 8​ B2:B5 и C2:C5​ Формулы массива делятся​

1. ​ полезны.​
2. ​Тогда формула, возвращающая нужный​
3. ​ массива:​​ добавлении новых значений​ обмена (​​Автор: Антон Андронов​Прежде чем удалять старый​ клавишу​

​ что нужно сделать​

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

​.​C2:C6-B2:B6​ сюда функцию​ ноль и суммируются​ диапазон формулы массива​ столбцов.​

​ и образовал новый​ на две категории​Итак, в данном уроке​ нам результат, будет​=СУММ({1;2;3;4;5;6})​ в столбец​CTRL+C​Для построения формул массива​ массив, скопируйте его​Delete​ – это выделить​Если же формула многоячеечная,​, как показано на​МАКС​ только нужные заказы. ​ (т.е. в диапазон​вводим функцию транспонирования =ТРАНСП(​ массив стоимостей (в​ — те, что​ Вы познакомились с​

planetaexcel.ru>

​ выглядеть следующим образом:​

• Диапазон данных для диаграммы excel
• Импорт данных в excel
• Проверка данных в excel
• Excel совместная работа с файлом excel
• Фильтр в excel не захватывает все данные
• Excel проверка данных
• Работа с массивами данных эксель
• Ввод данных в excel через форму
• Работа в excel с таблицами и формулами
• Для предотвращения потери данных excel не позволяет вставить строку
• Как сделать гистограмму в excel по данным таблицы
• Excel вставить данные из одной таблицы в другую в excel

Формула массива Excel • Мой центр онлайн-обучения

Знание формул массива Excel катапультирует вас в «Статус гуру» в вашем офисе, и я сделаю все возможное, чтобы сделать это безболезненно, но вам может понадобиться стимулятор мозга (a фруктовые или овощные закуски, которые дают 5-летним детям в школе моего сына в 10 утра каждое утро, когда они начинают терять концентрацию).

Формулы массива идеально подходят для суммирования или подсчета ячеек на основе нескольких критериев, немного похожи на СУММЕСЛИ и СУММЕСЛИМН, СЧЁТЕСЛИ и СЧЁТЕСЛИМН, но лучше, особенно если у вас только Excel 2003 и нет функций *ЕСЛИ.

В отличие от функций СУММЕСЛИМН и СЧЁТЕСЛИМН, которые позволяют указать только критерии И, в формулах массива можно также указать критерии ИЛИ.

Используя данные в таблице в качестве примера,
СУММЕСЛИМН ограничен следующим типом критериев:

Сумма продаж, если продавцом является Джон И регионом является Север

СУММЕСЛИМН Формула:

 =СУММЕСЛИМН( Sales,Sales_Person,"John", Region,"North") 

=142

Тогда как с формулой массива вы можете сделать это:

Сумма продаж, если продавец Джон ИЛИ Джим И регион — Север ИЛИ Юг

Формула массива:

 {=СУММА((")Продажи)*((+Sales_Person="John (Sales_Person="Jim"))*((Region="Север")+(Region="Юг")))} 

=306

Из приведенного выше примера формулы массива видно, что символ * указывает Excel на интерпретируйте критерии как И, а символ + — как ИЛИ.

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

Загрузите рабочую тетрадь и следуйте формулам или реконструируйте их.

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

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

Вы узнаете формулу массива в Excel, потому что она заключена в фигурные скобки { } см. пример выше . Эти фигурные скобки вставляются нажатием клавиш CTRL+SHIFT+ENTER при вводе формулы, в результате чего формулы массива иногда называют «формулами CSE».

Пример формулы массива

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

Формулы массива Excel проверяют результаты на ИСТИНА или ЛОЖЬ с числовым эквивалентом для ИСТИНА, равного 1, и для ЛОЖЬ, равным 0. Эти значения ИСТИНА или ЛОЖЬ известны как логические значения.

Используя приведенный выше пример, Excel определяет, являются ли значения в столбце продавца Джоном или Джимом, и присваивает ячейке значение 1 или 0 в зависимости от результата.

Таким образом, значения продавца равны {1,1,0,1,1,0}

Затем выполняется то же самое для региона:

{1,1,1,1,1,1}

А затем умножает эти результаты на столбец «Продажи» и суммирует результат для каждой строки:

{1x1x76}+{1x1x94}+{0x1x54}+{1x1x66}+{1x1x70}+{0x1x80} = 306

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

Как упоминалось ранее, формулы массива заключены в фигурные скобки. Вы не должны вводить эти фигурные скобки (если вы это сделаете, ваша формула не будет работать). Их нужно вводить, нажимая CTRL+SHIFT+ENTER при вводе формулы, а не просто нажимая ENTER.

Вы заметите, что фигурные скобки исчезнут при редактировании ячейки. Когда вы закончите редактирование, вам нужно снова нажать CTRL+SHIFT+ENTER, чтобы правильно ввести формулу.

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

Примеры формул массива

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

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

СЧЁТЕСЛИ Использование формулы массива и диапазона дат

Если у вас Excel 2007 или Excel 2010, вы можете сделать это с помощью функции СЧЁТЕСЛИМН.

Сценарий 1

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

Формула массива для мая в ячейке S6

 {=СУММ((Сотрудник=$R6)*
(Дата>=ДАТАЗНАЧ("05. 01.2011"))*(Дата
 
  Формула массива для июня в ячейке T6  
 
 {=СУММ((Сотрудник=$R6)*
(Дата>=ДАТАЗНАЧ("06.01.2011"))*(Дата
 
  Результат  
 
 
  Сценарий 2  
 
 Подсчитайте количество сверхурочных отработанных дней на человека в месяц, которые превышали 1 час 
 
  Формула массива для мая в ячейке S18  
 
 {=СУММ((Сотрудник=$R18)*(Дата=ДАТАЗНАЧ("31/05/2011")*(Сверхурочные>1)))} 
  Формула массива для июня в ячейке T18 
 {=СУММ((Сотрудник=$R18)*(Дата=ДАТАЗНАЧ("30.06.2011")*(Сверхурочные>1)))} 
  Результат  
 СРЕДНЕЕ Использование формул массива 
  Сценарий: 
 Среднее количество сверхурочных часов, отработанных на человека в месяц
  Если у вас Excel 2007 или Excel 2010, вы можете сделать это с помощью функции СРЗНАЧЕСЛИМН. 
  Формула массива в ячейке P30: 
 {=СРЗНАЧ(ЕСЛИ((Сотрудник=$O31)*(Дата>=ДАТАЗНАЧ("05. 01.2011"))*(Дата<=ДАТАЗНАЧ("31.05.2011")),Сверхурочные))} 
  Результат  
 Примечание. Когда функция ЕСЛИ используется в формуле массива, она вычисляется немного по-другому. Он создает промежуточный массив, используя ссылки на ячейки для TRUE и значения FALSE для FALSE.
 Например, промежуточный массив может выглядеть так:
 {ЛОЖЬ, ЛОЖЬ, ЛОЖЬ, ЛОЖЬ, N10, N11}
 Где N10 и N11 ИСТИНА, т.е. они соответствуют критериям. А поскольку функция среднего значения в Excel настроена на игнорирование логических значений, она будет усреднять только N10 и N11, игнорируя значения FALSE, создавая истинное СРЕДНЕЕ.
 Правила формулы массива 
 1.       Диапазоны, указанные в ваших формулах массива, должны быть одного размера, иначе вы получите ошибку. Например, в приведенной ниже формуле вы можете видеть, что, хотя формула может ссылаться на разные столбцы данных, длина столбца в каждом случае составляет от строки 6 до строки 11.
 {=СУММ((C6:C11)*((A6:A11="Джон")+(A6:A11="Джим"))*((B6:B11="Север")+(B6:B11=" Юг")))} 
 2.       Для ввода формулы массива необходимо нажать CTRL+SHIFT+ENTER, то есть зажать CTRL и SHIFT, а затем нажать ENTER, отпустив их все вместе. Если вы используете Mac, это COMMAND+RETURN.
 3.       Не вводите фигурные скобки самостоятельно, Excel делает это, когда вы нажимаете CTRL+SHIFT+ENTER.
 4.       Когда используется символ умножения *, это означает И, а когда используется символ плюс +, это означает ИЛИ.
 Введите ниже свой адрес электронной почты, чтобы загрузить образец книги.
 Отправляя свой адрес электронной почты, вы соглашаетесь с тем, что мы можем отправить вам наш информационный бюллетень Excel по электронной почте.
 
 

Формулы массива Excel для всех

Microsoft 365, Microsoft Excel, Microsoft Office для Mac, Office 2007, Office 2010, Office 2013, Office 2016, Office 2019, Office 2021 / Office LTSC, Office 365 /

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

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

Массивы требуют некоторого понимания, но оно того стоит. Они помогают сделать Excel быстрее и удобнее.

В настоящее время Excel поддерживает «формулы массива» только при специальной настройке. Но Excel 365 для Windows получает «формулы динамического массива», которые настраиваются автоматически.

Динамические или автоматические массивы, поступающие в Excel 365 для Windows

Текущий Excel требует, чтобы вы явно создали формулу массива (используя Ctrl + Shift + Enter, мы объясним ниже).

Microsoft называет новую функцию « динамических» массивов , но мы думаем о них как о автоматических массивах .

Если вы видите ошибку ячейки, например #SPILL. Они случаются, когда Excel думает, что вам нужен динамический массив, но вы просто используете Excel «по старинке». Это означает, что результат ячейки представляет собой массив, но недостаточно пустых ячеек (обычно внизу столбца), чтобы соответствовать результатам.

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

Любой, кто занимался программированием 101, может пропустить этот раздел .

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

Массивы могут быть более сложными. Это простой двумерный массив, он же сетка или таблица.

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

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

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

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

Расширенные формулы массива дают результат в виде массива. Мы вернемся к этому в следующей статье.

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

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

Мы сразу перешли от данных о продажах к сводкам МИН., МАКС., Среднее и СУММ без промежуточного шага.

Вот формула массива СУММ:

{=СУММ(C2:C6-B2:B6)}

Обратите внимание на фигурные скобки вокруг всей формулы? Это указывает на формулу массива.

Вот все формулы массива. Тот же массив с использованием разных функций Excel:

Под капотом

Со ссылкой на ячейку: C2:C6-B2:B6 Excel вычел каждую из пар ячеек.

Чтобы увидеть это, выберите часть формулы в виде массива и нажмите F9

=СУММ({13;12;26;16;1})

Это показывает массив чисел в ссылках на ячейки. В этом случае цифры такие же, как в старом столбце «Изменить» D на первом снимке экрана выше.

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

Отличие заключается в комбинации клавиш: Ctrl + Shift + Enter ( Command + Return на Mac). Это делает формулу массива вместо обычной формулы.

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

Важно:   Формула массива отображается с фигурными скобками, за исключением случаев редактирования. Скобки исчезают, когда вы редактируете формулу, что очень раздражает и является ловушкой.

Подробнее Важно: вы должны набирать Ctrl + Shift + Enter каждый раз, когда редактируете формулу массива.