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

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

Если необходимо включить новые данные в формулу массива, см. раздел Развертывание формулы массива. Вы также можете попробовать:

• Правила изменения формул массива (они могут быть привередливыми)
• Удаление формулы массива (здесь также можно нажать клавиши CTRL+SHIFT+ВВОД).
• Использование констант массива в формулах массива (они могут быть удобны)
• Присвойте константе массива имя (они могут упростить использование констант)

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

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

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

Скопируйте приведенную ниже таблицу и вставьте ее в Excel в ячейке A1. Выделите ячейки E2:E11, введите формулу =C2:C11*D2:D11, а затем нажмите клавиши CTRL+SHIFT+ВВОД, чтобы сделать ее формулой массива.

Для чего нужны массивы в Excel и как ими пользоваться: инструкции со скриншотами

Как с помощью массивов ускорить расчёты в таблицах с тысячами значений? Как поменять местами столбцы и строки? Разбираемся на примерах.

Иллюстрация: Meery Mary для Skillbox Media

Ксеня Шестак

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

Часто новичкам в Excel кажется, что массивы — это высший пилотаж в работе с таблицами. На деле всё гораздо проще.

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

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

В статье разберёмся:

• какие виды массивов есть в Excel;
• что такое формула массива и как она работает.

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

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

В конце расскажем, как создать формулу массива в «Google Таблицах».

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

Массивы в Excel бывают одномерными и двумерными.

В одномерных массивах все данные расположены в одной строке или в одном столбце. В зависимости от этого их делят на горизонтальные и вертикальные.

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

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

Формула массива — формула, где в качестве входящих параметров используют диапазоны значений, а не одиночные ячейки. Диапазоны значений обозначаются через двоеточие :. Например, A1:A10 или А1:В10.

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

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

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

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

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

Допустим, нужно рассчитать смету устройства фундаментов. У нас есть перечень необходимых работ, их объёмы и цена единиц измерения объёмов.

Определим стоимость каждой работы.

Можно пойти классическим путём — перемножить первые ячейки столбцов «Количество» и «Цена ед., руб.», а затем растянуть результат вниз на все остальные виды работ. Но если видов будет несколько сотен или тысяч, этот вариант может быть неудобен.

Формула массивов выведет результаты одновременно для всего диапазона — никаких дополнительных действий выполнять не потребуется. Рассмотрим, как это сделать.

Шаг 1. Выделяем столбец, в котором хотим получить результат расчёта, — в нашем случае это диапазон E2:E9. В строке ссылок вводим знак равенства.

Шаг 2. Выделяем первый массив, который участвует в расчётах, — все значения столбца «Количество». Одновременно с этим в строке ссылок появляется выбранный диапазон: B2:B9.

Шаг 3. Ставим знак умножения в строке ссылок и выбираем второй массив — все значения столбца «Цена ед., руб.».

Строка ссылок принимает вид: fx=B2:B9*D2:D9. Это значит, что значения первого массива должны умножиться на значения второго массива.

Шаг 4. Нажимаем Enter — в столбце «Стоимость, руб.» появляется результат расчёта. Так, в один клик, формула сработала сразу для всех строк.

Умножаем одно значение сразу на весь столбец

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

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

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

Шаг 1. Выделяем столбец для результата расчёта: С13:С16. В строке ссылок вводим знак равенства.

Шаг 2. Выделяем массив, который участвует в расчётах, — все значения столбца «Доля от стоимости работ». В формуле строки ссылок появляется выбранный диапазон: B13:B16. Добавляем к нему знак умножения и выбираем ячейку с общей стоимостью работ: E10.

Шаг 3. Нажимаем Enter. Во всём столбце «Стоимость, руб.» появляются результаты расчётов.

Выполняем два действия одной формулой

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

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

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

Шаг 1. Выделяем ячейку, в которой хотим получить результат расчёта. В строке ссылок вводим знак равенства и оператор СУММ и открываем скобку.

Шаг 2. По аналогии с алгоритмом из предыдущего раздела, выделяем первый массив — значения столбца «Количество» и второй массив — значения столбца «Цена ед., руб.». Ставим между ними знак умножения и закрываем скобку.

Строка ссылок принимает вид: fx=СУММ(B2:B9*D2:D9). Это значит, что значения первого массива должны перемножиться со значениями второго массива, а все полученные результаты — суммироваться.

Шаг 3. Нажимаем Enter. В выбранной ячейке появляется результат расчёта. Формула рассчитала одновременно два действия: перемножила значения ячеек двух массивов и суммировала полученные результаты.

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

Меняем местами столбцы и строки

Иногда при работе в Excel нужно поменять положение столбцов или строк — транспортировать их. Например, перевести шапку таблицы из горизонтального положения в вертикальное. Делать это вручную долго — особенно, когда ячеек очень много. Ускорить процесс помогут массивы и оператор ТРАНСП:

Шаг 1. Выделяем ячейку, в которой хотим получить результат операции. В строке ссылок вводим знак равенства и оператор ТРАНСП и открываем скобку.

Шаг 2. Выделяем шапку таблицы и закрываем скобку. Строка ссылок принимает вид: fx=ТРАНСП(A1:E1).

Шаг 3. Нажимаем Enter — функция меняет положение шапки таблицы на вертикальное.

Как создать формулу массива в «Google Таблицах»? Всё точно так же, как в Excel, но нужно добавить оператор ARRAYFORMULA. Его ставят перед всей формулой массива в строке ссылок. Например, если вы хотите перемножить данные в двух столбцах, формула в готовом виде будет выглядеть так:
fx=ARRAYFORMULA(B2:B9*D2:D9).

Другие материалы Skillbox Media по Excel

• Как сделать сводные таблицы в Excel — детальная инструкция со скриншотами
• Руководство: как сделать ВПР в Excel и перенести данные из одной таблицы в другую
• Руководство по макросам для новичков — для чего нужны и как их сделать
• Инструкция: как закреплять строки и столбцы в Excel
• Руководство по созданию выпадающих списков в Excel — как упростить заполнение таблицы повторяющимися данными

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

������� ���������� ��������� MS Office 2007: Microsoft Excel

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

Одна из наиболее интересных (и наиболее мощных) возможностей Excel — допустимость использования массивов в формулах.

Массив — это набор элементов, которые могут обрабатываться как единая группа или каждый в отдельности. В Excel массивы могут быть одно- или двухмерными. Измерения массивов непосредственно соответствуют строкам и столбцам. Например, одномерный массив может быть группой ячеек, которые размещены в одной строке (горизонтальный массив) или в одном столбце (вертикальный массив). Двухмерный массив размещается в нескольких строках и столбцах (рис.94). Трехмерных массивов Excel не поддерживает.

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

Задача 5.8. Формулы массивов для диапазонов ячеек

На рис.95 показан простой рабочий лист, на котором вычисляются

объемы продаж некоторых товаров. Чтобы вычислить объем продаж каждого конкретного товара (значения в столбце D ), обычно используется формула умножения количества проданного товара (столбец С ) и цены товара (столбец В ). Например, в ячейке D2 будет формула = В2*С2 , которая затем копируется на все оставшиеся ячейки столбца D . В данном случае получим пять отдельных формул в столбце D .

Другой способ вычисления пяти значений в столбце D — использование одной формулы массива. Эта формула займет диапазон D2:D5 и вернет сразу все пять искомых значений.

Для создания формулы массива следует выполнить следующее.

1. Выделить диапазон, в котором должен содержаться результат. В нашем примере это диапазон D2:D5 .
2. Ввести формулу =В2:В5*С2:С5 . Поставьте равно и выделите диапазон В2:В5 , поставьте значок умножить и выделите диа­пазон С2:С5 .
3. Нажмите комбинацию клавиш Ctrl+Shift+Enter , чтобы ввести формулу как формулу массива (обычно для завершения ввода формулы следует нажать клавишу Enter ).

Формула будет введена сразу во все пять выделенных ячеек. Если вы посмотрите в строку формул, то там будет представлена формула , т.е. Excel произвел попарное умножение элементов массивов В2:В5 и С2:С5 и создал новый массив стоимостей D2:D5 .

Обратите внимание на фигурные скобки, появившиеся в формуле — отличительный признак формулы массива. Вводить их вручную с кла­виатуры бесполезно — они автоматически появляются при нажатии Ctrl+Shift+Enter (при удержании нажатых клавиш Shift и Ctrl производят нажатие клавиши Enter ). Если бы мы нажали Enter , то формула была бы введена только в активную ячейку блока ( Проверьте!). Excel заключает формулу в фигурные скобки, показывая тем самым, что это формула массива. Эти скобки нельзя набирать вручную (формула будет воспринята как текст) .

Задача 5.9 . Формулы массивов для отдельных ячеек

Формулы массивов могут возвращать результат и в одну ячейку. На рис.96 в ячейку D7 введена формула массива

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

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

Формула, как и предыдущая, работает с двумя массивами, храня­щимися в диапазонах В2:В5 и С2:С7 . Формула перемножает соответ­ствующие значения из этих массивов виртуальный массив, который существует только в памяти компьютера. Затем функция СУММ обраба­тывает этот новый массив и возвращает сумму его значений.

В данном случае вместо формулы массива можно воспользоваться функцией СУММПРОИЗВ , которая вернет тот же результат: = СУММПРОИЗВ (В2:В6;С2:С6) .

Задача 5.10. Именованные массивы

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

Пусть нам известна сумма прихода и сумма расхода . Необходимо вычислить доход как разность этих величин. Введите исходные данные в таблицу (рис.97).

Однако здесь фактически из вектор-столбца В2:В5 вычитается вектор-столбец С2:С5 . Поэтому можно непосредственно вычесть из вектора вектор одной формулой, а не создавать отдельные формулы для компонент вектора.

Создание имен . Для наглядности дадим векторам имена. Выделите диапазон со вторым (В2:В5) и третьим (С2:С5) столбцами таблицы последовательно и присвойте имена во вкладке Формулы-присвоить Имя . Диапазону В2:В5 присвойте имя Приход , а диапазону С2:С5 — имя Расход .

Ввод табличной формулы с использованием имен диапазонов.

Вводим в диапазон D2:D5 формулу массива.

• Выделим блок D2: D5 . В этом блоке активна ячейка D2 .
• Наберем знак равенства =.
• Нажмем функциональную клавишу F3 . Появится окно Вставка имени . Выберем имя Приход и щелкнем Ок . Формула примет вид: = Приход .
• Наберем знак минус (-).
• Вновь нажмем клавишу F3 . В диалоговом окне Вставка имени выберем имя Расход и щелкнем Ок . Формула примет вид: = Приход-Расход.
• Нажмем сочетание клавиш Shift+Ctrl+Enter . Во всех ячейках блока появится формула .

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

Ввод формулы массива. Разумеется, формулу массива можно вводить и без использования имен. Скопируйте блок А1:С5 в А8:С12 и повторите все шаги. Выделите блок D9:D12 . В этом блоке активной ячейкой является D12 . Наберите знак равенства =. Выделите блок В9:В12 , наберите знак минус -, выделите блок С9: С12 , нажмите сочетание клавиш Shift+Ctrl+Enter . Во всех ячейках блока появится формула . Мы получили две идентичные таблицы.

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

Изменение формулы массива. Попытайтесь очистить одну из ячеек, занятую формулой. Например, выделите ячейку D9 и нажмите клавишу Del . В этом случае появляется сообщение « Нельзя изменять часть массива» , откуда следует, что удалить блок можно только целиком.

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

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

1. Выделить весь диапазон, содержащий формулу массива.
2. Нажать клавишу F2 , чтобы перейти в режим редактирования.
3. Нажать комбинацию клавиш Ctrl+Enter . Это действие вводит отдельные формулы (не формулу массива) в каждую ячейку выделенного диапазона.
4. Изменить выделение диапазона так, чтобы он включал новые ячейки (или исключал лишние).
5. Нажать клавишу F2 .
6. Нажать комбинацию клавиш Shift+Ctrl+Enter .

Коррекция формулы при увеличении блока. Добавьте в обе таблицы на рабочем листе строку с данными: Год — 1996, приход — 240, расход 200. Необходимо посчитать прибыль за 1996. Раньше, когда формулы записывались в отдельные ячейки, поступили бы просто: скопировали бы формулу из ячейки D5 в D6 . Проделаем это для первой таблицы. Вместо ожидаемого 40 получим результат #ЗНАЧ!. Та же операция для второй таблицы даст правильный результат 40, но в строке формул мы увидим — образовался второй блок, что не является корректным решением нашей задачи. Отменим последнее действие.

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

• Во вкладке Формулы-Диспетчер имен выделить Приход , и внизу окна в Диапазон изменить диапазон значений с В1:С5 на В1:С6 . Для его изменения удалить имеющийся в окне диапазон и, перейдя на лист, выделить новый диапазон В1:С6 . Точно также изменить диапазон для Расхода . После изменений диалоговое окно закрыть.
• Выделить D2: D6 , нажать клавишу F2 (редактирование) и, ничего не изменяя в формуле, нажать клавиши Shift+ Ctrl+Enter .

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

• Выделить D8 :D13 и нажать клавишу F2 для редактирования фор­мулы.
• Изменить в формуле ссылки на диапазоны ячеек с В8:В12 на В8:В13 и с С8: С12 на С8:С13 . Нажать сочетание клавиш Shift+Ctrl+Enter.

Коррекция табличной формулы при уменьшении блока. Теперь необходимо удалить в каждой из таблиц строку для 1996 г. Для первой таблицы вновь изменить поименованные блоки (в результате в ячейке D6 результат отобразится как #Н/Д — недоступно). Выделить блок с формулой массива, нажать клавишу F2 . Ввод закончить клавишами Ctrl+Enter.

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

Очистить последнюю строку таблицы. Выделить блок D2: D5 , нажать клавишу F2 , нажать клавиши Shift+Ctrl+Enter .

Аналогично поступить со второй таблицей:

• Выделить диапазон с формулой массива.
• Нажать комбинацию клавиш Ctrl+Enter , чтобы преобразовать формулу массива в обычную.
• Удалить последнюю строчку таблицы.
• Выделить диапазон для формулы массива, D9: D12 .
• Нажать F2 для ее редактирования. Используемые в формуле ссылки на диапазоны выделяются рамочками, закрашенными в разные цвета. Видно, что рамочки захватывают области удален­ной строки. Для корректировки ссылок в формуле можно умень­шить рамочки, схватив за любой нижний угол и подтянув к верху до последней используемой строки. Ссылки в формуле изменять­ся в соответствии с выделением. Но можно просто изменить в ссылках цифру 3 на 2 ( В13 изменить на В12 и С13 на С12 ).
• Закончить изменения клавишами Shift+Ctrl+Enter .

Задача 5.11. Создание массивов на основе значений ячеек диапазона.

На рис.98 показан рабочий лист, содержащий данные в диапазоне A1: С4.

Диапазон С8:F11 содержит массив, созданный на основе этих данных с помощью формулы

Массив в диапазоне С8 :F11 связан со значениями диапазона А1:С4 .

Если изменить какое-либо значение в последнем диапазоне, то автоматически изменится соответствующее значение в массиве.

Задача 5.12 . Транспонирование массива.

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

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

Для размещения транспонированного массива следует выделить диапазон ячеек (рис. 100). Поскольку исходный массив ячеек состоял из 8 строк на 2 столбца, то необходимо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов. Далее в выделенном диапазоне ввести функцию транспонирования =ТРАНСП (, где в качестве аргумента функции является наш массив ячеек А1:В8. Заканчиваем ввод формулы комбинацией клавиш Ctrl+Shift+Enter и получаем «перевернутый массив» в качестве результата (рис. 101).

Задача 5.13. Таблица умножения.

Если вспомните детство, школу, свою тетрадку по математике. На обороте тетради на обложке была таблица умножения вот такого вида (рис. 102).

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

• ввести два диапазона чисел от 1 до 10 в строке 1 и столбце А;
• выделить диапазон В2:К11 ;
• ввести формулу =А2:A11*В1:К1 ;
• закончить ввод формулы комбинацией клавиш Ctrl+Shift+Enter , чтобы Excel воспринял ее как формулу массива. Результат пред­ставлен ниже (рис. 103).

Задача 5.14. Выборочное суммирование.

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

В данном случае формула массива синхронно пробегает по всем эле­ментам диапазонов СЗ:С21 и ВЗ:В21 , проверяя, совпадают ли они с задан­ными значениями из ячеек G4 и G5 .

Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом, суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

Можно применить и другой способ:

Нам необходимо просуммировать числа из диапазона D3:D21 (диапазон суммирования) при выполнении нескольких (двух) условий. Для каждой строки в случае одновременного равенства числа из диапазона СЗ:С21 числу G8, и числа из диапазона ВЗ:В21 числу G9 , соответствующее число из диапазона суммирования будет суммироваться.

Для этого применим функцию СУММЕСЯИМН.

=СУММЕСЛИМН (D3:D21; СЗ:С21; G8; ВЗ:В21; G9)

На открывшейся форме СУММЕСЯИМН в окне Диапазон суммирования выбрать диапазон для суммирования ( D3:D21 ). Для прописания условий в окне Диапазон_условия1 необходимо выбрать диапазон первого условия СЗ:С21 , который равен критерию G8 — окно Условие1 , в окне Диапазон_условия2 ввести диапазон второго условия ВЗ:В21 , который должен равняться G9 — окно Условие2 .

Попробуйте решить задачу двумя способами и сравните результат.

Вернемся к задаче обработки данных метеостанции ( Задача 5.5 ). Для расчета количества засушливых месяцев, т.е. месяцев, когда выпало менее 10 мм осадков, очень удобно использовать функцию СЧЕТЕСЛИ . Однако с ее помощью нельзя получить количество месяцев, на протяжении которых количество осадков лежало бы в диапазоне от 20 до 80 (назовем такие месяцы нормальными). Для этого необходимо использовать дистрибутивные функции. Функции, которые можно применять к списку, называются дистрибутивными.

Скопируйте текст из ячейки А22 в ячейку А25 и откорректируйте его: «Количество нормальных месяцев». Сначала подсчитаем месяцы с нормальным количеством осадков. Будем использовать вспомогательный блок F3:Н14 тех же размеров, что и блок с исходными данными. В ячейку F3 вводится формула = ЕСЛИ(И(ВЗ>20; в3<80) ; 1; 0) и копируется в остальные ячейки блока F3:H14 . В блоке выводятся нули и единицы.

Введенная формула является индикаторной функцией множества нормальных месяцев, т.е. 1 — выводится, когда количество осадков лежит в пределах между 20 и 80 мм и 0 — в противном случае. Для подсчета суммы нормальных месяцев вводим в ячейку F25 формулу =СУММ (F3:F14) и копируем ее в блок G25:Н25 . Количество нормальных месяцев посчитано.

А теперь решим эту же задачу без использования вспомогательного блока. Введем в В25 табличную формулу 0;ЕСЛИ (ВЗ :В14 и скопируем ее в С25:D25 . (Таким образом, компьютер, перебирая значения в указанном блоке, проверяет больше или меньше текущее число 20 и, если оно меньше, прибавляет к исходному число 0. В противном случае — проверяет выполнение второго условия (менее 80). Если и второе условие выполняется, то к исходному числу прибавляет 1. В противном случае — 0). Формула 20;ВЗ <80) ;1;0) ) >не приведет к успеху, так как функция И не является дистрибутивной.

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

Теперь вычислим суммарное количество осадков, выпавших и эти месяцы. Введите в А26 текст «Осадки в нормальные месяцы», в В26 — табличную формулу <=СУММ(ЕСЛИ(ВЗ:В14>20;ЕСЛИ(ВЗ:В14 и скопируйте ее в С26:D26 . В Е25 и Е26 введите формулы для суммирования значений в строках (выделив блок В25:Е26 , выберите значок Автосумма ). Вы получите блок, показанный на рис. 105.

В блоке A1:А10 записана числовая последовательность. Проверьте, является ли она возрастающей.

Решение . На новом листе запишите любую последовательность чисел в блок A1:А10 .

Окончательное решение можно записать одной формулой, поместив его в ячейку А12 .

<=ЕСЛИ(СУММ(ЕСЛИ(А2:А10-А1:А9>0;1;0))=СЧЕТ(A1:А10)-1; «возрастающая»; «не является возрастающей»)>

Разбор этой формулы:

• А2:А10-А1:А9 (т.е., из А10 вычитается А9 , из А9 вычитается А8 и т.д.) — образует блок, состоящий из первых разностей элементов исходного блока;
• ЕСЛИ (А2 : А10-А1: А9>0; 1; 0) — составляет блок из индикаторов положительных первых разностей;
• СУММ (ЕСЛИ (А2 :А10-А1 :А9>0; 1; 0) ) — считает количество ненулевых элементов в блоке индикаторов;
• СЧЕТ (A1: А10)-1 — рассчитывает размер блока индикаторов, равный уменьшенному на 1 размеру исходною блока;
• и, наконец, если количество ненулевых элементов в блоке инди­каторов равно размеру блока индикаторов, то последователь­ность возрастающая, иначе — нет.

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

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel для iPhone Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

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

Начиная с обновления для Microsoft 365 за сентябрь 2018 г., любая формула, которая может возвращать несколько результатов, автоматически переносит их вниз или в соседние ячейки. Это изменение поведения также сопровождается несколькими новыми функциями динамического массива. Формулы динамического массива, независимо от того, используют ли они существующие функции или функции динамического массива, необходимо вводить только в одну ячейку, а затем подтверждать, нажав ВВОД. Ранее в устаревших формулах массива сначала требовалось выбрать весь выходной диапазон, а затем подтвердить формулу с помощью CTRL+SHIFT+ВВОД. Их часто называют формулами CSE.

Формулы массива позволяют выполнять сложные задачи, например следующие:

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

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

Скачивание примеров

Массивы с несколькими ячейками и одной ячейкой

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

• Формула массива с несколькими ячейками
• Здесь мы вычисляем совокупный объем продаж купе и седанов для каждого продавца, вводя =F10:F19*G10:G19 в ячейку H10. При нажатии клавиши ВВОД вы увидите, как результаты распределяются по ячейкам H10:H19. Обратите внимание, что диапазон переноса выделяется границей при выделении любой ячейки в пределах диапазона. Вы также можете заметить, что формулы в ячейках H10:H19 неактивны. Они нужны только для справки, поэтому, если вы хотите изменить формулу, вам нужно выбрать ячейку H10, в которой находится основная формула.
• Формула массива с одной ячейкойВ ячейке H20 образца книги введите или скопируйте и вставьте =СУММ(F10:F19*G10:G19), а затем нажмите ВВОД. В этом случае будет выполнено перемножение значений массива (диапазон ячеек F10–G19), а затем — при помощи функции СУММ — сложение полученных результатов. Общий итог продаж составит 1 590 000 рублей. В данном примере демонстрируется, какие широкие возможности предоставляет данный тип формул. Например, предположим, что у вас есть 1000 строк с данными. Вы можете частично или полностью сложить их значения, создав формулу массива в отдельной ячейке, а не перетаскивать формулу на 1000 строк ниже. Кроме того, обратите внимание на то, что формула с одной ячейкой в ячейке H20 полностью независима от формулы с несколькими ячейками (формула в ячейках H10–H19). Это указывает на еще одно преимущество использования формул массива — их гибкость. Можно изменить формулы в столбце H, и это не повлияет на формулу в ячейке H20. Также может быть полезно иметь независимые итоги, как это, так как это помогает проверить точность результатов.
• Ниже перечислены дополнительные преимущества, которые обеспечивает использование формул динамического массива.
  • Согласованность Если щелкнуть несколько ячеек ниже ячейки H10, вы увидите, что все они содержат одну и ту же формулу. Такая согласованность помогает обеспечить более высокую точность результатов.
  • Безопасность Компонент формулы массива с несколькими ячейками нельзя переписать. Например, щелкните ячейку H11 и нажмите клавишу DELETE. Excel не будет изменять выходные данные массива. Чтобы их изменить, необходимо выбрать левую верхнюю ячейку массива или ячейку H10.
  • Меньший размер файлов Зачастую вместо нескольких промежуточных формул можно использовать одну формулу массива. Например, в примере с продажей автомобилей для вычисления результатов в столбце E используется одна формула массива. Если бы вы прибегли к обычным формулам (таким как =F10*G10, F11*G11, F12*G12 и т. д.), вам понадобилось бы 11 разных формул для получения таких же результатов. Это не очень важно, но что, если вам необходимо суммировать тысячи строк? Тогда это может иметь большое значение.
  • Эффективность Функции массива могут быть эффективным способом создания сложных формул. Формула массива =СУММ(F10:F19*G10:G19) та же: =СУММ(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).
  • Перенос Динамические формулы массива будут автоматически перенесены в диапазон вывода. Если исходные данные хранятся в таблице Excel, тогда формулы динамического массива будут автоматически изменять размер при добавлении и удалении данных.
  • Ошибка #ПЕРЕНОС! Динамические массивы ввели Ошибка #ПЕРЕНОС!, что означает, что предполагаемый диапазон переноса по какой-либо причине заблокирован. При устранении блока формула будет автоматически перенесены.

Создание одной и двухмерных констант массива

Константы массива являются компонентами формул массива. Константы массива создаются путем ввода списка элементов с последующей вставкой фигурных скобок (< >) вокруг списка, следующим образом:

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

В описанных ниже процедурах вы потренируетесь создавать горизонтальные, вертикальные и двумерные константы. Мы покажем примеры использования функции ПОСЛЕД для автоматического создания констант массива, а также для введенных вручную констант массива.

• Создание горизонтальной константы Воспользуйтесь книгой из предыдущих примеров или создайте новую книгу. Выберете любую пустую ячейку и введите =ПОСЛЕД(1,5). Функция ПОСЛЕД создает массив из 1 строки на 5 столбцов, как = . Будет отображен следующий результат: Вы также можете ввести: или=, но обратите внимание, где вы ставите точки с запятой вместо запятых. Как видите, параметр ПОСЛЕД имеет значительные преимущества по сравнению с вводом значений констант массива вручную. В основном это экономит время, но также помогает сократить количество ошибок, возникающих при вводе вручную. Его также проще читать, особенно потому, что точки с запятой трудно отличить от разделителей запятой.