Массивы в excel: что такое, формула, как сделать / Skillbox Media

Как работать с массивами функций в Excel

Автор Амина С. На чтение 10 мин Опубликовано 28.07.2020

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

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

Содержание

1. Терминология
2. Виды массивов функций в Excel
3. Синтаксис формулы массива
4. Анализ данных с помощью формулы массива
5. Пример: товарный чек
6. Пример: меняем местами строки и столбцы
7. Редактирование формулы массива
8. Изменение содержимого массива
9. Функции массивов
10. Оператор СУММ
11. Оператор ТРАНСП
12. Оператор МОБР

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

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

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

1

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

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

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

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

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

2

В свою очередь, одномерные массивы можно разделить на горизонтальные и вертикальные. Первые включают ячейки одного ряда, а вторые – колонки.

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

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

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

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

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

Затем ставим курсор в строку формул, и там записываем =C3:C8*D3:D8.

3

Превращаем формулу в формат массива. Нужно нажать Ctrl + Shift + Enter.

После этого мы получаем таблицу с готовыми промежуточными итогами.

4

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

 

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

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

5

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

1. Выделяем ту ячейку, которая будет содержать результат вычисления по формуле массива.
2. Вводим туда формулу. В нашем случае мы будем суммировать значения из целевого диапазона, поэтому используем формулу СУММ(C3:C8*D3:D8).
3. С использованием комбинации Ctrl + Shift + Enter осуществляем трансформацию стандартной формулы в ту, которая работает с массивами данных.

По итогу, получаем следующий результат.

6

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

Давайте более подробно рассмотрим синтаксис этой формулы. 

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

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

1. Функция массива. Это описание той операции, которую Эксель должен выполнить. 
2. Массивы диапазона. Это непосредственно те диапазоны, которые будут обрабатываться формулой. 
3. Оператор массива – знак двоеточия. Обозначает расстояние в определенное количество ячеек между конкретными адресами. 

Чтобы было проще понять, вот небольшой рисунок.

7

Анализ данных с помощью формулы массива

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

17

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

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

18

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

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

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

19

В ней используется 3 множителя. Первый проверяет менеджеров. Второй – товары. А третий – непосредственно определяет сумму заказа.

Как работает эта формула? Да очень просто. Ее алгоритм следующий:

1. Сначала Excel проверяет всех менеджеров и товары, соответствуют ли они заданным критериям. Если да, функция возвращает значение 1, если нет – 0. 20
2. В случае несоответствия значений хотя бы одному из условий, один из множителей становится равным нулю. Соответственно, и итоговый результат будет аналогичным.
3. Если же оба условия выполняются, то функция осуществляет операцию умножения.
4. Наконец, все полученные значения суммируются, после чего ячейка выдает общую эффективность по обработанным заказам. 

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

Пример: товарный чек

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

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

8

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

9

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

10

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

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

11

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

12

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

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

13

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

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

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

14

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

После этого надо ввести формулу =ТРАНСП, введя в качестве аргумента функции A1:B8.

15

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

16

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

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

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

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

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

1. Найти кнопку «Отмена», расположенную слева от строки формул. Еще один вариант – нажатие клавиши Esc, которая выполняет ту же операцию. Во всех случаях блокировка будет снята и все операции можно выполнять заново. 
2. Снова ввести формулу массива с тем же диапазоном.

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

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

Рассмотрим наиболее часто используемые функции массивов.

Оператор

СУММ

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

Оператор

ТРАНСП

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

Оператор

МОБР

Эта функция дает возможность сделать обратную матрицу. x

=ТРАНСП() — для того чтобы вертикальный диапазон ячеек сделать горизонтальным и наоборот.

Из данной статьи вы узнаете, как в Excel ввести формулу массива.

Принцип ввода формулы массива расскажу на примере 2-х формул 

=ЛИНЕЙН() и =ТРАНСП().  

Для того, чтобы с помощью формулы =ЛИНЕЙН() рассчитать коэффициенты линейного тренда y=a+bx (a) и (b), необходимо:

1. Ввести в формулу данные

=ЛИНЕЙН(известные значения y (например, объём продаж по месяцам), известные значения x (номера периодов), константа (коэффициент (a) в формуле y=a+bx, для его расчета ставим «1»), статистика (вводим «0»)) (см. файл с примером).

2. Установить курсор в ячейку с формулой и выделить соседнюю справа, как на рисунке:

3. Для ввода формулы массива нажимаем клавишу F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.

Коэффициенты линейного тренда y=a+bx (a) и (b) рассчитаны.

 

2-й пример (см. вложенный файл), в нём мы рассмотрим, как перевернуть диапазон и сделать из горизонтального вертикальный. Для этого воспользуемся функцией =ТРАНСП().

Как она работает:

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

2. Выделяем вертикальный диапазон, равный по количеству ячеек выделенному горизонтальному, вверху диапазона должна быть введена формула =ТРАНСП();

3. Для ввода формулы массива нажимаем клавишу F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.

Горизонтальный диапазон стал вертикальным. Теперь, если мы внёсем изменения в горизонтальный диапазон, они тут же отобразятся в вертикальном диапазоне.

 

Для ввода формулы массива необходимо 

1. выделить массив — это диапазон ячеек, в которые Excel выведет данные, 
2. и нажать чудо комбинацию клавиш  — F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.

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

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

• Novo Forecast Lite — автоматический расчет прогноза в Excel.
• 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
• Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

• Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Зарегистрируйтесь и скачайте решения

Статья полезная? Поделитесь с друзьями

 

Добавить комментарий

Видео: Формулы массива

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

Обучение Эксель 2013.

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

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

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

• Формулы массива
  видео
• Используйте СУММ, СРЕДНЕЕ и МАКС.
  видео
• Редактировать и удалять формулы массива
  видео

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

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

Зачем использовать формулы массива?

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

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

• Подсчитать количество символов, содержащихся в диапазоне ячеек.

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

• Суммируйте каждое n-е значение в диапазоне значений.

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

org/ItemList»>

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

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

3. Нажмите Ctrl+Shift+Enter. Excel заполнит каждую из выбранных вами ячеек результатом.

Хотите больше?

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

Создать формулу массива

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

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

Вы можете ввести знак =, B2 (количество акций Contoso), звездочку для умножения, B3, цену акции, знак + для добавления, C2, звездочку, C3 и т. д.

Но с большой серией это заняло бы целую вечность, и было бы легко ошибиться и не заметить ее.

Проще? Используйте формулу массива. Массив — это последовательность данных в строке, столбце или комбинации строк и столбцов.

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

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

В формуле массива введите знак =, СУММА , открывая скобку, выберите ячейки в строке «Акции» (это первый массив в формуле массива), затем нажмите звездочку для умножения, выберите ячейки в строке «Цена». row (это второй массив в формуле массива).

И это ключевое отличие: когда вы вводите формулу массива, вы нажимаете Ctrl+Shift+Enter, а не просто Enter.

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

Я не набрал в формуле закрывающую скобку, нажатие Ctrl+Shift+Enter сделает это за вас.

Формула массива умножает каждую ячейку в строке «Акции» на соответствующую ячейку в строке «Цена», а затем суммирует или складывает их и возвращает общую сумму 14 421,87 долларов США.

В строке формул формула заключена в фигурные скобки { }, что означает, что это формула массива.

Это делается автоматически при нажатии Ctrl+Shift+Enter. Не вводите эти фигурные скобки. Если вы это сделаете, Excel будет интерпретировать вашу формулу как текст, а не как формулу.

Далее: Используйте СУММ, СРЗНАЧ и МАКС в формулах массива.

Формулы и функции массива в Excel

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

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

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

• Что такое массив в Excel?
• Что такое формула массива?
• Как ввести формулу массива (CTRL+SHIFT+ENTER)
• Как оценить части формулы массива (клавиша F9)
• Формулы массива с одной и несколькими ячейками в Excel
• Константы массива Excel
• Операторы И и ИЛИ в формулах массива
• Двойной унарный оператор в формулах массива

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

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

Например, если вы поместите список покупок на неделю в формат массива Excel, он будет выглядеть так:

{«Молоко», «Яйца», «Масло», «Кукурузные хлопья»}

Затем, если вы выберете ячейки от A1 до D1, введите указанный выше массив, перед которым ставится знак равенства (=) в строке формул, и нажмите CTRL + SHIFT + ENTER, вы получите следующий результат:

Вы только что создали одномерный горизонтальный массив. Пока ничего страшного, верно?

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

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

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

Формулы массива доступны во всех версиях Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 и более ранних версиях.

А теперь, кажется, самое время создать свою первую формулу массива.

Простой пример формулы массива Excel

Предположим, у вас есть несколько товаров в столбце B, их цены в столбце C, и вы хотите рассчитать общую сумму всех продаж.

Конечно, ничто не мешает вам сначала вычислить промежуточные итоги в каждой строке с помощью чего-то простого, например =B2*C2 , а затем суммировать эти значения:

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

1. Выберите пустую ячейку и введите в нее следующую формулу:

   =СУММ(В2:В6*С2:С6)

2. Нажмите сочетание клавиш CTRL + SHIFT + ENTER, чтобы завершить формулу массива.

   После этого Microsoft Excel заключает формулу в {фигурные скобки}, что является визуальным признаком формулы массива.

   Формула умножает значения в каждой отдельной строке указанного массива (ячейки с B2 по C6), складывает промежуточные итоги и выводит общий итог:
   

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

Зачем использовать формулы массива в Excel?

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

• Суммировать числа, удовлетворяющие определенным условиям, например, суммировать N наибольших или наименьших значений в диапазоне.
• Суммируйте каждую вторую строку или каждую N ^-ю строку или столбец, как показано в этом примере.
• Подсчитать количество всех или определенных символов в указанном диапазоне. Вот формула массива, которая подсчитывает все символы, и еще одна, которая подсчитывает любые заданные символы.

Как ввести формулу массива в Excel (Ctrl + Shift + Enter)

Как вы уже знаете, комбинация 3 клавиш CTRL + SHIFT + ENTER — это волшебное прикосновение, которое превращает обычную формулу в формулу массива.

При вводе формулы массива в Excel необходимо помнить о 4 важных вещах:

1. Когда вы закончите вводить формулу и одновременно нажмете клавиши CTRL SHIFT ENTER, Excel автоматически заключит формулу в {фигурные скобки}. Когда вы выбираете такую ​​ячейку (ячейки), вы можете увидеть фигурные скобки в строке формул, которые дают вам понять, что там находится формула массива.
2. Ввод скобок вокруг формулы вручную не работает. Вы должны нажать сочетание клавиш Ctrl+Shift+Enter, чтобы завершить формулу массива.
3. Каждый раз, когда вы редактируете формулу массива, фигурные скобки исчезают, и вы должны снова нажать Ctrl+Shift+Enter, чтобы сохранить изменения.
4. Если вы забудете нажать Ctrl+Shift+Enter, ваша формула будет вести себя как обычная формула и обрабатывать только первые значения в указанном(ых) массиве(ах).

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

Используйте клавишу F9 для оценки частей формулы массива

При работе с формулами массива в Excel вы можете наблюдать, как они вычисляют и сохраняют свои элементы (внутренние массивы), чтобы отобразить окончательный результат, который вы видите в ячейке. Для этого выберите один или несколько аргументов в скобках функции, а затем нажмите клавишу F9.ключ. Чтобы выйти из режима оценки формулы, нажмите клавишу Esc.

В приведенном выше примере, чтобы увидеть промежуточные итоги всех продуктов, выберите B2:B6*C2:C6, нажмите F9 и получите следующий результат.

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

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

Существует несколько функций массива Excel, предназначенных для возврата массивов с несколькими ячейками, например ТРАНСП, ТРЕНД, ЧАСТОТА, ЛИНЕЙН и т. д. выражения массива при вводе в одну ячейку с помощью Ctrl + Shift + Enter.

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

Пример 1. Формула массива с одной ячейкой

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

Обычно вы добавляете дополнительный столбец, скажем, столбец D, в котором рассчитывается изменение продаж для каждого продукта по формуле вроде =C2-B2 , а затем находите максимальное значение в этом дополнительном столбце =MAX(D: Г) .

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

=MAX(C2:C6-B2:B6)

Пример 2. Формула массива с несколькими ячейками в Excel хотите рассчитать сумму налога для каждого продукта с одной формулой.

Выберите диапазон ячеек в каком-нибудь пустом столбце, скажем, D2:D6, и введите следующую формулу в строку формул:

=B2:B6 * C2:C6 * 0,1

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

Пример 3. Использование функции массива Excel для возврата массива из нескольких ячеек

Как уже упоминалось, Microsoft Excel предоставляет несколько так называемых «функций для работы с массивами», которые специально разработаны для работы с массивами из нескольких ячеек. TRANSPOSE — одна из таких функций, и мы собираемся использовать ее для транспонирования приведенной выше таблицы, то есть преобразования строк в столбцы.

1. Выберите пустой диапазон ячеек, в который вы хотите вывести транспонированную таблицу. Поскольку мы преобразовываем строки в столбцы, убедитесь, что выбрано такое же количество строк и столбцов, сколько столбцов и строк в вашей исходной таблице соответственно. В этом примере мы выбираем 6 столбцов и 4 строки.
2. Нажмите F2, чтобы войти в режим редактирования.
3. Введите функцию массива =ТРАНСП(массив) и нажмите Ctrl + Shift + Enter. В нашем примере используется формула =ТРАНСП($A$1:$D$6) .

   Результат будет выглядеть примерно так:
   

Вот как вы используете функцию массива в Excel. Чтобы изучить основы ТРАНСПОНИРОВАНИЯ, ознакомьтесь с этим руководством: Как транспонировать в Excel — преобразовать строки в столбцы.

Как работать с формулами массива с несколькими ячейками

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

1. Выберите диапазон ячеек, где вы хотите вывести результаты до , введя формулу.
2. Чтобы удалить формулу массива с несколькими ячейками, либо выделите все содержащие ее ячейки и нажмите DELETE, либо выделите всю формулу в строке формул, нажмите DELETE, а затем нажмите Ctrl + Shift + Enter.
3. Нельзя редактировать или перемещать содержимое отдельной ячейки в формуле массива, а также вставлять новые ячейки или удалять существующие ячейки из формулы массива с несколькими ячейками. Всякий раз, когда вы пытаетесь это сделать, Microsoft Excel выдает предупреждение « Нельзя изменить часть массива «.
4. Чтобы сжать формулу массива, т.е. применить ее к меньшему количеству ячеек, нужно сначала удалить существующую формулу, а затем ввести новую.
5. Чтобы расширить формулу массива, т. е. применить ее к большему количеству ячеек, выбрать все ячейки, содержащие текущую формулу, плюс пустые ячейки, где вы хотите ее иметь, нажать F2, чтобы переключиться в режим редактирования, настроить ссылки в формуле и нажать Ctrl + Shift + Enter, чтобы обновить его.
6. Нельзя использовать формулы массива с несколькими ячейками в таблицах Excel.
7. Вы должны ввести формулу массива с несколькими ячейками в диапазоне ячеек того же размера, что и результирующий массив, возвращаемый формулой. Если ваша формула массива Excel создает массив, превышающий выбранный диапазон, лишние значения не будут отображаться на листе. Если массив, возвращаемый формулой, меньше выбранного диапазона, в дополнительных ячейках появятся ошибки #Н/Д.

Если ваша формула может возвращать массив с переменным числом элементов, введите его в диапазоне, равном или превышающем максимальный массив, возвращаемый формулой, и заключите формулу в функцию ЕСЛИОШИБКА, как показано в этом примере.

Константы массива Excel

В Microsoft Excel константа массива — это просто набор статических значений. Эти значения никогда не изменяются при копировании формулы в другие ячейки или значения.

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

Существует 3 типа констант массива:

1. Константа горизонтального массива

Константа горизонтального массива находится в строке. Чтобы создать константу массива строк, введите значения, разделенные запятыми, и заключите их в фигурные скобки, например {1,2,3,4}.

Примечание. При создании константы массива открывающую и закрывающую фигурные скобки следует вводить вручную.

Чтобы ввести горизонтальный массив в электронную таблицу, выберите соответствующее количество пустых ячеек в строке, введите формулу = {1,2,3,4} в строке формул и нажмите Ctrl + Shift + Enter. Результат будет примерно таким:

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

2. Константа вертикального массива

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

={11; 22; 33; 44}

3. Константа двумерного массива

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

= {"а", "б", "в"; 1, 2, 3}

Работа с константами массива Excel

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

1. Элементы константы массива

   Константа массива может содержать числа, текстовые значения, логические значения (ИСТИНА и ЛОЖЬ) и значения ошибок, разделенные запятыми или точками с запятой.

   Вы можете ввести числовое значение как целое, десятичное или в экспоненциальном представлении. Если вы используете текстовые значения, они должны быть заключены в двойные кавычки («»), как и в любой формуле Excel.

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

2. Именование констант массива

   Чтобы упростить использование константы массива, дайте ей имя:

   • Перейдите на вкладку Формулы > группу Определенные имена и нажмите Определить имя . Либо нажмите Ctrl + F3 и щелкните New .
   • Введите имя в поле Имя
   • В Ссылается на поле , введите элементы константы вашего массива, заключенные в фигурные скобки, с предшествующим знаком равенства (=). Например:

     = {"Вс", "Пн", "Вт", "Ср", "Чт", "Пт", "Сб"}
     

   • Нажмите OK, чтобы сохранить именованный массив и закрыть окно.

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

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

3. Предотвращение ошибок

   Если ваша константа массива работает неправильно, проверьте наличие следующих проблем:

   • Разделите элементы вашей константы массива соответствующим символом — запятой в горизонтальных константах массива и точкой с запятой в вертикальных.
   • Выбран диапазон ячеек, который точно соответствует количеству элементов в константе вашего массива. Если вы выберете больше ячеек, каждая дополнительная ячейка будет иметь ошибку #Н/Д. Если вы выберете меньше ячеек, будет вставлена ​​только часть массива.

Использование констант массива в формулах Excel

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

Пример 1. Сумма N самых больших/наименьших чисел в диапазоне

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

Затем вы берете функцию НАИБОЛЬШИЙ или МАЛЕНЬКИЙ, указываете весь диапазон ячеек в первом параметре и включаете константу массива во второй. Наконец, вставьте его в функцию СУММ, например:

Суммируйте 3 самых больших числа: =СУММ(НАИБОЛЬШИЙ(диапазон, {1,2,3}))

Суммируйте 3 самых маленьких числа: =СУММ( МАЛЕНЬКИЙ(диапазон, {1,2,3}))

Не забудьте нажать Ctrl + Shift + Enter, так как вы вводите формулу массива, и вы получите следующий результат:

Аналогичным образом можно вычислить среднее N наименьших или наибольших значений в диапазоне:

Среднее трех верхних чисел: =СРЗНАЧ(БОЛЬШОЙ(диапазон, {1,2,3}))

Среднее 3 нижних чисел: =СРЗНАЧ(МАЛЕНЬКИЙ(диапазон, {1,2,3}))

Пример 2. Формула массива для подсчета ячеек с несколькими условиями

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

Проще всего использовать формулу СЧЁТЕСЛИМН с несколькими условиями. Однако, если вы хотите включить много продуктов, ваша формула СЧЁТЕСЛИМН может стать слишком большой. Чтобы сделать его более компактным, вы можете использовать СЧЁТЕСЛИМН вместе с СУММ и включить константу массива в один или несколько аргументов, например:

=СУММ(СЧЁТЕСЛИМН(диапазон1, "критерий1", диапазон2, {"критерий1", "критерий2 "}))

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

=СУММ(СЧЁТЕСЛИМН(B2:B9, "sally", C2:C9, {"яблоки", "лимоны"}))

Наш образец массива состоит всего из двух элементов, поскольку цель состоит в том, чтобы продемонстрировать подход. В ваши формулы реального массива вы можете включать столько элементов, сколько требует ваша бизнес-логика, при условии, что общая длина формулы не превышает 8 192 символов в Excel 2019 — 2007 (1024 символа в Excel 2003 и ниже) и ваш компьютер мощный достаточно для обработки больших массивов. Дополнительные сведения см. в ограничениях формул массива.

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

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

Оператор массива сообщает формуле, как вы хотите обрабатывать массивы — используя логику И или ИЛИ.

• Оператор И — это звездочка (*), которая является символом умножения. Он указывает Excel возвращать ИСТИНА, если ВСЕ условия оцениваются как ИСТИНА.
• Оператор ИЛИ — знак плюс (+). Он возвращает ИСТИНА, если ЛЮБОЕ из условий в данном выражении оценивается как ИСТИНА.

Формула массива с оператором И

В этом примере мы находим сумму продаж, где продавец — Майк И продукт — Яблоки :

=СУММ((A2:A9="Майк" ) * (B2:B9="Яблоки") * (C2:C9))

Или

=СУММ(ЕСЛИ(((A2:A9="Майк") * (B2:B9="Яблоки") ), (C2:C9)))

Технически эта формула умножает элементы трех массивов в одинаковых позициях. Первые два массива представлены значениями TRUE и FALSE, которые являются результатом сравнения A2:A9.Майку» и B2:B9 в «Яблоки». Третий массив содержит номера продаж из диапазона C2:C9. Как и любая математическая операция, умножение преобразует ИСТИНА и ЛОЖЬ в 1 и 0 соответственно. А поскольку умножение на 0 всегда дает ноль, результирующий массив имеет 0, когда одно или оба условия не выполняются.Если оба условия выполняются, соответствующий элемент из третьего массива попадает в итоговый массив (например, 1 * 1 * C2 = 10). Таким образом, результат умножение представляет собой этот массив: {10;0;0;30;0;0;0;0} Наконец, функция СУММ суммирует элементы массива и возвращает результат 40.

Формула массива Excel с оператором ИЛИ

Следующая формула массива с оператором ИЛИ (+) суммирует все продажи, где продавцом является Майк ИЛИ продуктом является Яблоки:

=СУММ(ЕСЛИ(((A2 :A9="Майк") + (B2:B9="Яблоки")), (C2:C9)))

В этой формуле вы складываете элементы первых двух массивов (которые являются условиями, которые вы хотите протестировать) и получите ИСТИНА (>0), если хотя бы одно условие оценивается как ИСТИНА; FALSE (0), когда все условия оцениваются как FALSE. Затем ЕСЛИ проверяет, больше ли результат сложения 0, и если да, то СУММ складывает соответствующий элемент третьего массива (C2:C9).

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

Двойной унарный оператор в формулах массивов Excel

Если вы когда-либо работали с формулами массивов в Excel , скорее всего, вы столкнулись с несколькими, содержащими двойное тире (—), и вам, возможно, было интересно, для чего он использовался.

Двойное тире, которое технически называется двойным унарным оператором , используется для преобразования нечисловых логических значений (ИСТИНА / ЛОЖЬ), возвращаемых некоторыми выражениями, в 1 и 0, которые может понять функция массива.

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

Следующая формула сработает:

=СУММ(--(МЕСЯЦ(A2:A10)=1))

Поскольку это формула массива Excel, не забудьте нажать Ctrl + Shift + Enter, чтобы завершить ее.

Если вас интересует другой месяц, замените 1 на соответствующий номер. Например, 2 означает февраль, 3 — март и так далее. Чтобы сделать формулу более гибкой, вы можете указать номер месяца в какой-нибудь ячейке, как показано на скриншоте:

А теперь давайте проанализируем, как работает эта формула массива. Функция МЕСЯЦ возвращает месяц каждой даты в ячейках с A2 по A10, представленный порядковым номером, который создает массив {2;1;4;2;12;1;2;12;1}.

После этого каждый элемент массива сравнивается со значением в ячейке D1, которая в данном примере равна 1. Результатом этого сравнения является массив логических значений ИСТИНА и ЛОЖЬ. Как вы помните, вы можете выбрать определенную часть формулы массива и нажать F9, чтобы увидеть, чему соответствует эта часть:

Наконец, вы должны преобразовать эти логические значения в 1 и 0, которые может понять функция СУММ.