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

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

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

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

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

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

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

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 множителя. Первый проверяет менеджеров. Второй – товары. А третий – непосредственно определяет сумму заказа.

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

    Сначала Excel проверяет всех менеджеров и товары, соответствуют ли они заданным критериям. Если да, функция возвращает значение 1, если нет – 0.

20

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

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

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

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

    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. Снова ввести формулу массива с тем же диапазоном.

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

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

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

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

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

    Читайте также  Проверка данных в Excel, выпадающих список, ограничение символов и значений

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

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

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

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

    Источник:
    http://office-guru.ru/excel/formuly-funkcii/kak-rabotat-s-massivami-funkcij-v-excel.html

    Запрос на выборку данных (формулы) в EXCEL

    13 апреля 2013 г.

    Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению стандартного Фильтра ). Произведем отбор значений из исходной таблицы с помощью формул массива . В отличие от применения Фильтра ( CTRL+SHIFT+L или Данные/ Сортировка и фильтр/ Фильтр ) отобранные строки будут помещены в отдельную таблицу.

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

    1. Один числовой критерий (Выбрать те Товары, у которых цена выше минимальной)

    Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — число ).

    Необходимо отобразить в отдельной таблице только те записи (строки) из Исходной таблицы, у которых цена выше 25.

    Решить эту и последующие задачи можно легко с помощью стандартного фильтра . Для этого выделите заголовки Исходной таблицы и нажмите CTRL+SHIFT+L . Через выпадающий список у заголовка Цены выберите Числовые фильтры. , затем задайте необходимые условия фильтрации и нажмите ОК.

    Будут отображены записи удовлетворяющие условиям отбора.

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

    Критерий (минимальную цену) разместим в ячейке Е6 , таблицу для отфильтрованных данных — в диапазоне D10:E19 .

    Теперь выделим диапазон D11:D19 (столбец Товар) и в Строке формул введем формулу массива :

    Вместо ENTER нажмите сочетание клавиш CTRL+SHIFT+ENTER .

    Те же манипуляции произведем с диапазоном E11:E19 куда и введем аналогичную формулу массива :

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

    Чтобы показать динамизм полученного Отчета (Запроса на выборку) введем в Е6 значение 65. В новую таблицу будет добавлена еще одна запись из Исходной таблицы, удовлетворяющая новому критерию.

    Если в Исходную таблицу добавить новый товар с Ценой в диапазоне от 25 до 65, то в новую таблицу будет добавлена новая запись.

    В файле примера также содержатся формулы массива с обработкой ошибок, когда в столбце Цена содержится значение ошибки, например #ДЕЛ/0! (см. лист Обработка ошибок ).

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

    3. Один критерий Дата (Выбрать те Товары, у которых Дата поставки совпадает заданной)

    Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий — Дата ).

    Для отбора строк используются формулы массива, аналогичные Задаче1 (вместо критерия =$B$12:$B$20)*(СТРОКА($B$12:$B$20)-СТРОКА($B$11));$J$12-СТРОКА(A12)+СТРОКА($B$11)+1))

    Примечание : После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER. Это сочетание клавиш используется для ввода формул массива.

    Скопируйте формулу массива вниз на нужное количество ячеек. Формула вернет только те значения Товаров, которые были поставлены в диапазоне указанных дат. В остальных ячейках будут содержаться ошибки #ЧИСЛО! Ошибки в файле примера (Лист 4.Диапазон Дат) скрыты с помощью Условного форматирования .

    Аналогичную формулу нужно ввести и для дат в столбец E.

    В ячейке J12 вычислено количество строк исходной таблицы, удовлетворяющих критериям:

    Для ввода первой формулы выделите диапазон ячеек G12:G20 . После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER.

    Решение3 : Если столбец Дат СОРТИРОВАН, то можно не использовать формулы массива.

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

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

    5. Один критерий Дата (Выбрать те Товары, у которых Дата поставки не раньше/ не позже заданной)

    Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий — Дата (не позже) ).

    Для отбора строк, дата которых не раньше (включая саму дату), используется формула массива:

    = ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ($E$7 C15;И($B$7>=B15;$B$7 =$B$13:$B$21)*($B$13:$B$21>0);СТРОКА($B$13:$B$21);»»);СТРОКА($B$13:$B$21)-СТРОКА($B$12)) -СТРОКА($B$12))

    Условие $E$7=$A$13:$A$21 гарантирует, что будут отобраны товары только определенного типа. Условие $E$8>=$B$13:$B$21 гарантирует, что будут отобраны даты не позже заданной (включая). Условие $B$13:$B$21>0 необходимо, если в диапазоне дат имеются пустые ячейки. Знак * (умножение) используется для задания Условия И (все 3 критерия должны выполняться для строки одновременно).

    Примечание . Случай, когда список несортирован, рассмотрен в статье Поиск ДАТЫ (ЧИСЛА) ближайшей к заданной, с условием в MS EXCEL. Несортированный список .

    7. Один Текстовый критерий (Выбрать Товары определенного вида)

    Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — Текст ).

    Задача решается аналогично Задачам 1 и 3. Более подробное решение см. в статье Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск .

    8. Два Текстовых критерия (Выбрать Товары определенного вида, поставленные в заданный месяц)

    Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (И) ).

    Для отбора строк используется формула массива:

    Выражение ($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19) задает оба условия (Товар и Месяц).

    Выражение СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК($A$11:$A$19))) формирует массив последовательных чисел <1:2:3:4:5:6:7:8:9>, т.е. номера строк в таблице.

    9. Два Текстовых критерия (Выбрать Товары определенных видов)

    Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (ИЛИ) ).

    В отличие от Задачи 7 отберем строки с товарами 2-х видов ( Условие ИЛИ ).

    Для отбора строк используется формула массива:

    = ИНДЕКС(A$11:A$19; НАИБОЛЬШИЙ((($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19))*(СТРОКА($A$11:$A$19)-СТРОКА($A$10)); СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1))

    Условие ($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19) гарантирует, что будут отобраны товары только заданных видов из желтых ячеек (Товар2 и Товар3). Знак + (сложение) используется для задания Условие ИЛИ (должен быть выполнен хотя бы 1 критерий).

    Вышеуказанное выражение вернет массив <0:0:0:0:1:1:1:0:0>. Умножив его на выражение СТРОКА($A$11:$A$19)-СТРОКА($A$10) , т.е. на массив последовательных чисел <1:2:3:4:5:6:7:8:9>, получим массив позиций (номеров строк таблицы), удовлетворяющих критериям. В нашем случае это будет массив <0:0:0:0:5:6:7:0:0>.

    С помощью функции НАИБОЛЬШИЙ() выведем 3 значения из позиции 5 (строка 15 листа), 6 (16) и 7 (17), т.е. значения Товар2, Товар2 и Товар3. Для этого используем выражение СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1 , которое последовательно (начиная со строки 11) будет возвращать числа 3; 2; 1; 0; -1; -2; . Формула НАИБОЛЬШИЙ(. ;3) вернет число 5, НАИБОЛЬШИЙ(. ;2) вернет число 6, НАИБОЛЬШИЙ(. ;1) вернет число 7, а НАИБОЛЬШИЙ(. ;0) и далее вернет ошибку, которую мы скроем условным форматированием .

    И наконец, с помощью функции ИНДЕКС() последовательно выведем наши значения из соответствующих позиций: = ИНДЕКС(A$11:A$19;5) вернет Товар2, = ИНДЕКС(A$11:A$19;6) вернет Товар2, = ИНДЕКС(A$11:A$19;7) вернет Товар3.

    10. Отбор значений с учетом повторов

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

    Наиболее популярные статьи из этого раздела:

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

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

    Предположим, что нас интересует сколько и каких партий товаров поставлялось по цене от 1000р. до 2000р. (критерий 1). Причем, партий с одинаковой ценой должно быть минимум 3 (критерий 2).

    Решением является формула массива:

    Эта формула возвращает номера строк, которые удовлетворяют обоим критериям.

    Формула =СУММПРОИЗВ(($C$14:$C$27>=$B$7)*($C$14:$C$27 =$B$10)) подсчитывает количество строк, которые удовлетворяют критериям.

    В файле примера на листе «10.Критерий — колич-во повторов» настроено Условное форматирование , которое позволяет визуально определить строки удовлетворяющие критериям, а также скрыть ячейки, в которых формула массива возвращает ошибку #ЧИСЛО!

    11. Используем значение критерия (Любой) или (Все)

    В фильтре Сводных таблиц MS EXCEL используется значение (Все), чтобы вывести все значения столбца. Другими словами, в выпадающем списке значений критерия содержится особое значение, которое отменяет сам критерий (см. статью Отчеты в MS EXCEL , Отчет №3).

    В файле примера на листе «11. Критерий Любой или (Все)» реализован данный вариант критерия.

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

    Остальная часть формулы аналогична рассмотренным выше.

    12. Актуальная цена

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

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

    2) определяем последнюю (максимальную) дату для каждого товара с помощью формулы массива =МАКС((Таблица1[товар]=E8)*Таблица1[дата])

    3) наконец, выводим актуальную цену =СУММЕСЛИМН(Таблица1[цена];Таблица1[товар];E8;Таблица1[дата];F8)

    Для товара не должно быть повторов дат, иначе цены будут суммироваться (если повторяется последняя дата).

    В файле примера приведено решение на листе 12. Актуальная цена.

    Источник:
    http://excel2.ru/articles/zapros-na-vyborku-dannyh-formuly-v-ms-excel

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

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

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

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

    Читайте также  Как сделать ссылку на фотографию в excel?

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

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

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

  • чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter
  • Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

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

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

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

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

    • Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
    • вводим функцию транспонирования =ТРАНСП(
    • в качестве аргумента функции выделяем наш массив ячеек A1:B8

    жмем Ctrl + Shift + Enter и получаем «перевернутый массив» в качестве результата:

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

    Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

    Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

    Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)

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

    Вспомните детство, школу, свою тетрадку по математике. На обороте тетради на обложке было что? Таблица умножения вот такого вида:

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

    1. выделяем диапазон B2:K11
    2. вводим формулу =A2:A11*B1:K1
    3. жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

    и получаем результат:

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

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

    В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

    Источник:
    http://www.planetaexcel.ru/techniques/2/91/

    ВПР с несколькими условиями: 5 примеров.

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

    Мы предложим вам несколько вариантов решения проблемы поиска по нескольким условиям.

    ВПР по нескольким условиям с использованием дополнительного столбца.

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

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

    Сразу по трем столбцам функция ВПР искать не может. Поэтому нам нужно объединить их в один. И, поскольку поиск производится всегда в крайнем левом (первом) столбце, то нужно добавить его в нашу таблицу тоже слева.

    Вставляем перед таблицей с данными дополнительный столбец A. Затем при помощи оператора & объединяем в нем содержимое B,C и D. Записываем в А7

    и копируем в находящиеся ниже ячейки.

    Формула поиска в D4 будет выглядеть:

    В диапазон поиска включаем и наш дополнительный столбец. Критерий поиска – также объединение 3 значений. И извлекаем результат из 5 колонки.

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

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

    ВПР по двум условиям при помощи формулы массива.

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

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

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

    Обратите внимание на фигурные скобки, которые означают, что это формула массива. То есть наша функция ВПР работает не с отдельными значениями, а разу с массивами данных.

    Разберем процесс подробно.

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

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

    В результате получим вот такой виртуальный массив данных на основе нашей исходной таблицы:

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

    С такой работой функция ВПР вполне справится.

    Такой ход стал возможен путем применения формулы массива. Поэтому обратите особое внимание: круглые скобки в формуле писать руками не нужно! В ячейке B3 вы записываете формулу

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

    Таким образом, функция ВПР поиск по двум столбцам производит в 2 этапа: сначала мы очищаем диапазон данных от строк, не соответствующих одному из условий, при помощи функции ЕСЛИ и формулы массива. А затем уже в этой откорректированной информации производим обычный поиск по одному только второму критерию при помощи ВПР.

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

    Вот как это будет выглядеть.

    ВПР по нескольким критериям с применением массивов — способ 2.

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

    Условия возьмем те же, что и в предыдущем примере.

    Формулу в С4 введем такую:

    Естественно, не забываем нажать CTRL+Shift+Enter.

    Теперь давайте пошагово разберем, как это работает.

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

    Как и в предыдущем примере, мы ищем текст из объединенных в одно целое условий поиска.

    Далее определяем данные, среди которых будем искать.

    Конструкция вида A7:A20&B7:B20&C7:C20;D7:D20 создает 2 элемента. Первый – это объединение колонок A, B и C из исходных данных. Если помните, то же самое мы делали в нашем дополнительном столбце. Второй D7:D20 – это значения, одно из которых нужно в итоге выбрать.

    Функция ВЫБОР позволяет из этих элементов создать массив. <1,2>как раз и означает, что нужно взять сначала первый элемент, затем второй, и объединить их в виртуальную таблицу – массив.

    В первой колонке этой виртуальной таблицы мы будем искать, а из второй – извлекать результат.

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

    Двойной ВПР при помощи ИНДЕКС + ПОИСКПОЗ

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

    Существуют, пожалуй, даже более гибкие решения, нежели функция ВПР. Это комбинация функций ИНДЕКС + ПОИСКПОЗ.

    Область их применения очень велика, о чем бы также будем рассказывать на сайте mister-office.ru.

    А пока вернемся вновь к нашей задаче.

    Формула в С4 теперь выглядит так:

    И не забываем при вводе нажать CTRL+Shift+Enter! Это формула массива.

    Теперь давайте разбираться, как это работает.

    Функция ИНДЕКС в нашем случае позволяет извлечь элемент из списка по его порядковому номеру. Список – это диапазон D7:D20, где записаны суммы выручки. А вот порядковый номер, который нужно извлечь, мы определяем при помощи ПОИСКПОЗ.

    Синтаксис здесь следующий:

    ПОИСКПОЗ(что_ищем; где_ищем; тип_поиска)

    Тип поиска ставим 0, то есть точное совпадение. В нашем случае мы будем искать 1. Далее мы определим массив, в котором будем работать.

    Выражение (A7:A20=C1)*(B7:B20=C2)*(C7:C20=C3) позволит создать виртуальную таблицу примерно такого вида:

    Как видите, первоначально мы последовательно сравниваем каждое значение с нашим критерием отбора. В столбце А у нас записаны месяцы – сравниваем их с месяцем-критерием из ячейки C1. В случае совпадения получаем ИСТИНА, иначе – ЛОЖЬ. Аналогично последовательно проверяем год и название магазина. А затем просто перемножаем значения. Поскольку логические переменные для Excel – это либо 0, либо 1, то произведение их может быть равно 1 только в том случае, если мы имеем по каждой колонке ИСТИНА (то есть,1). Во всех остальных случаях получаем 0.

    Читайте также  Сложение и вычитание значений времени

    Убеждаемся, что цифра 1 встречается только единожды.

    При помощи ПОИСКПОЗ определяем, на какой позиции она находится. На какой позиции находится 1, на той же позиции находится в массиве и искомая сумма выручки. В нашем случае это 10-я.

    Далее при помощи ИНДЕКС извлекаем 10-ю по счету выручку.

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

    Достойная замена – функция СУММПРОИЗВ.

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

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

    Последовательно по каждой строке диапазона от 2-й до 27-й она проверяет совпадение каждого соответствующего значения с критерием поиска. Эти результаты перемножаются между собой и в итоге еще умножаются на сумму выручки. Если среди трех условий будет хотя бы одно несовпадение, то итогом будет 0. В случае совпадения сумма выручки трижды умножится на 1.

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

    В качестве бонуса можно продолжить этот пример и рассчитать общую сумму продаж менеджера в определенном регионе.

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

    Кстати, возможен и другой вариант расчета с этой же функцией:

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

    Источник:
    http://mister-office.ru/funktsii-excel/vlookup-conditions-5-examples.html

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

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

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

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

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

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

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

    Формула массива вводится в ячейку после знака =, как и обычная формула, но если окончание ввода простой формулы подтверждается нажатием на клавишу Enter, то для окончания ввода формулы массива необходимо последовательно нажать на три клавиши – Ctrl+Shift+Enter. (Клавишу + нажимать не нужно, следует удерживать нажатыми предыдущие клавиши до момента нажатия на последнюю кнопку!)

    Формула массива примет вид: .

    Не стоит пробовать ввести фигурные скобки вручную напрямую с помощью клавиатуры. Для того чтобы программа Excel «поняла», что вы хотите ввести формулу массива нужно нажать на три вышеперечисленные клавиши!

    Аргументами функций ФОРМУЛЫ могут быть как одиночные ячейки, так и диапазоны ячеек – все, как и в обычных формулах.

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

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

    Ряд важных функций в Excel работают только с массивами и выводят результаты в массив. Это функции ЛИНЕЙН() ЛГРФПРИБЛ(), ТЕНДЕНЦИЯ() и другие.

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

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

    Требуется выборочно быстро найти и просуммировать некоторые значения из большой таблицы MS Excel.

    Например, из обширной базы поставок изделий необходимо извлечь информацию о количестве и стоимости поставленных компании ООО «Оберон» изделий «Блок-СМ15».

    Для решения задачи следует выполнить ниже перечисленную последовательность действий.

    1. Скопировать из таблицы «База поставок изделий» название фирмы-заказчика и вставить, например,

    в ячейку B2: ООО «Оберон»

    2. Скопировать название изделия и вставить

    в ячейку B3: Блок-СМ15

    3. Ввести формулу массива для подсчета количества изделий

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

    в ячейку B5: <=СУММ((A9:A27=B2)*(B9:B27=B3)*E9:E27)>=420 000,00

    Как работают формулы?

    Формула в ячейке B4 суммирует построчно произведения: Σ( ai * bi * ci ).

    Формула в ячейке B5 суммирует построчно произведения: Σ( ai * bi * ei ).

    Если в строке диапазона A9:A27 Excel находит значение равное значению в ячейке B2, то ai =1. Если находит значение не равное значению в ячейке B2, то ai =0. Аналогично присваиваются значения bi . Значения ci и ei просто берутся построчно из ячеек указанных диапазонов.

    Поставленную задачу можно решить и другим способом: при помощи объявления таблицы «База поставок изделий» списком, последовательного применения автофильтров к столбцам A и B и добавлением строки итогов.

    Спектр задач, решаемых при помощи формул массивов.

    Ниже приведен внушительный перечень задач, подробно рассмотренных в главе Применение формул массивов книги «Формулы в Excel 2013» Джона Уокенбаха. Изучив эти примеры и начав их использование на практике, можно серьезно повысить эффективность своей работы в Excel.

    1. Суммирование в диапазоне, содержащем ошибки.

    2. Подсчет количества ошибок в диапазоне.

    3. Суммирование n наибольших значений в диапазоне.

    4. Вычисление среднего без учета нулевых значений.

    5. Поиск значения в диапазоне.

    6. Подсчет отличающихся значений в двух диапазонах.

    7. Местоположение максимального значения диапазона.

    8. Поиск номера строки, в которой находится n-е значение, совпадающее с заданным.

    9. Получение самого длинного текста в диапазоне.

    10. Определение допустимых значений диапазона.

    11. Вычисление суммы цифр числа.

    12. Суммирование округленных значений.

    13. Суммирование каждого n-го значения в массиве.

    14. Удаление нечисловых символов из текстовой строки.

    15. Поиск ближайшего значения в диапазоне.

    16. Получение последнего значения в столбце.

    17. Получение последнего значения в строке.

    18. Извлечение положительных значений из диапазона.

    19. Извлечение непустых ячеек из диапазона.

    20. Изменение порядка следования элементов в диапазоне на противоположный.

    21. Динамическая сортировка значений диапазона.

    22. Возвращение списка уникальных значений диапазона.

    23. Отображение календаря в диапазоне.

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

    Комментарии пишите пожалуйста в блоке внизу страницы.

    Источник:
    http://al-vo.ru/spravochnik-excel/formula-massiva-v-excel.html

    Массивы в Excel пример использования

    Массивы в Excel пример использования

    Добрый день, уважаемые подписчики и читатели блога! Сегодня чуть больше поговорим о массивах и практике их применения в Excel.

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

    Как обычно мы действуем? Вводим формулу — количество * цену, плюсуем доставку, enter, протягиваем за маркер автозаполнения вниз, получаем результат. Способ классический, рабочий.

    Давайте выполним те же самые действия, только умножать и прибавлять будем не ячейки, а целые диапазоны. Предварительно выделив диапазон от F3 до F11.

    Первый нюанс, когда работаем с формулой массива нельзя нажимать Enter! Нажимаем Ctrl+Shift+Enter! Что это даст в итоге.

    К обычной формуле добавятся фигурные скобки в самом начале и конце. Это говорит о том, что применён массив. То есть — если таблица большая, проще выделить весь столбец и использовать массив, чтобы не протягивать маркером до конца таблицы.

    Комбинации формул массива и обычных формул

    Усложняем задачу — нужно подвести итог (СУММ) по всему столбцу «ИТОГО», высчитать максимальную сумму заказа и узнать среднюю сумму заказа без копеек.

    Аналогично воспользуемся функцией СУММ, только передадим ей опять массив в виде вычислений. Не забываем про Ctrl+Shift+Enter!

    Взглянем в строчку формул.

    Посмотрим, что в этому случае делает программа Excel — пройдём по шагам вычислений. На вкладке «Формулы» нажмём кнопку «Вычислить формулу».

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

    Аналогично подсчитаем максимальную стоимость покупки. Используем формулу МАКС и передадим ей массив.

    Настала очередь средней суммы покупки. Для округления будем использовать ЦЕЛОЕ (чтобы результат был без копеек) и СРЗНАЧ.

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

    Как видно их примеров — у массивов есть свои плюсы и свои минусы. Не пробуйте удалять массив (если он содержится в диапазоне ячеек). Изменить только одну ячейку массива нельзя! Плюс — улучшаем безопасность, минус — приходится переделывать весь массив, если что-то пошло не так.

    Если остались вопросы — посмотрите новое видео. Ну и по традиции — вам может понравиться статья про функцию ПЛТ. Всем удачи!

    Источник:
    http://pcandlife.ru/massivi-v-excel-primer-ispolzovaniya/