С помощью формул массива мы можем моментально вычислять сложные итоги и эффективно анализировать данные.
В качестве переменных формулы массива мы задаем не одиночные значения, а целые диапазоны данных, таким образом, получая конечные результаты вычислений между заданными массивами. Для получения такого результата заканчивать редактирование формулы нужно не с помощью привычной нам клавиши Enter, а с помощью комбинации клавиш: Ctrl + Shift + Enter (например, жмите сначала Ctrl + Shift, а затем клавишу Enter).
Пример формулы массива
См. далее: примеры вычислений итогов с помощью формул массива: (1), (2), (3)
Пример вычислений с помощью формул массива (1)
Имеем итоговый отчет о проданных новогодних ёлках по артикулам:
➢Таблица (массив данных) по ценам проданных ёлок.
➢Таблица (массив данных) по проданным количествам.
Добавляем таблицу для расчета итогов проданных ёлок по артикулям в рублях:
Рассчитываем рублевые итоги по каждому артикулю по формуле: Цена умножить на Количество.
Оперировать будем тремя массивами:
Массив 1 – Цены.
Массив 2 – Количество.
Массив 3 = Массив 1 * Массив 2.
Выделяем диапазон массива, в котором вычисляем итоги (Массив 3).
Набираем формулу перемножения данных таблицы "Цены", указывая диапазон Массива 1 и таблицы "Количество",
указывая диапазон Массива 2:
Заканчиваем редактирование формулы комбинацией клавиш Ctrl + Shift + Enter.
Получаем на весь итоговый диапазон единую формулу массива, рассчитавшую наши итоги по каждому артиклю, относительно соответствующих цен и количеств:
Пример вычислений с помощью формул массива (2)
Имеем отчет о проданных новогодних ёлках по артикулям и по дням, а также таблицу с ценами:
Добавляем таблицу для расчета итогов проданных ёлок в рублях, по артикулям и по дням:
Вычисляем итоги по той же формуле: Цена умножить на Количество.
Оперировать будем тремя массивами:
Массив 1 – Цены.
Массив 2 – Количество.
Массив 3 = Массив 1 * Массив 2.
Выделяем диапазон итогового массива.
Набираем формулу перемножения данных таблицы "Цены", указывая диапазон Массива 1 и таблицы "Количество",
указывая диапазон Массива 2:
Завершаем редактирование формулы комбинацией клавиш Ctrl + Shift + Enter.
Пример вычислений с помощью формул массива (3)
Есть таблица продаж детских товаров:
Анализируем данные таблицы.
➢Найдем общую сумму продаж:
Завершаем редактирование формулы комбинацией клавиш Ctrl + Shift + Enter.
➢Найдем максимальную цену продаж:
Завершаем редактирование формулы комбинацией клавиш Ctrl + Shift + Enter.
➢Найдем наименование товара с максимальной ценой:
С помощью функции INDEX() найдем текстовое значение нашего массива по заданным номеру строки и номеру столбца.
Номер строки найдем с помощью функции MATCH() – искать будем максимальную цену продаж. Номер столбца известен – 1 (колонка Товар).
Итак, поиск номера строки, по которой расположен товар с максимальной ценой. Максимальная цена у нас:
Будем искать именно такое выражение в таком же массиве:
MATCH(lookup_value; lookup_array; match_type)
где:
lookup_value (искомое значение) – MAX(C2:C7+C2:C7*D2:D7) – MAX(цена_товара+цена_товара*наценка)
lookup_array (массив поиска) – C2:C7+C2:C7*D2:D7 – цена_товара+цена_товара*наценка
match_type (тип поиска) – 0 – поиск точного значения.
Завершаем редактирование формулы комбинацией клавиш Ctrl + Shift + Enter.
Найденное значение номера строки – 4:
Теперь для поиска значения с помощью функции INDEX() нам известны:
номер строки – 4;
номер столбца – 1;
массив поиска значения – A2:D7
Подставляем к нашей формуле поиск:
INDEX(array;row_number;column_number)
где:
array (массив поиска) – A2:A7
row_number (номер строки) – возвращенное значение функции MATCH(MAX(C2:C7+C2:C7*D2:D7);C2:C7+C2:C7*D2:D7;0)
column_number (номер столбца) – 1
Завершаем редактирование формулы комбинацией клавиш Ctrl + Shift + Enter.
Найденное наименование товара с максимальной ценой:
➢Аналогичным способом найдем максимальную сумму продажи и наименование этого товара; минимальные значения цены и суммы продажи и наименования этих товаров:
Скачать Excel файл с примерами: Формулы массивов