Как умножить массив на массив в excel

Массивы и формулы массива в Excel.

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

Массивом называют группу данных объединенных (сгруппированных) в одну структуру (группу)

В «Excel» массивы подразделяют на три типа в зависимости от структуры расположения данных в таблице:

Горизонтальный одномерный (линейный) массив – массив, в котором данные расположены горизонтально в одну строку.

Вертикальный одномерный (линейный) массив – массив, в котором данные расположены вертикально в один столбец.

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

Формулы (функции) массивов.

Для работы с массивами в «Эксель» предусмотрены специальные формулы – формулы массивов.

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

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

Рассмотрим в качестве примера смету состоящую из столбцов «Количество», «Трудозатраты на единицу», «Стоимость одного чел часа».

Смета на проведение работ в Excel.
Вид работ Количество Трудозатраты на единицу Стоимость одного чел часа
Прокладка кабеля, м 25 0,5 123
Укладка тротуарной плитки, м² 45 0,125 244
Покраска металлических поверхностей, м² 4 0,2 233
Монтаж дверного замка, шт. 12 0,8 40
Итого: 3480,4

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

Рассмотрим функцию массива ТРАНСП(). Эта функция полностью относится к функциям массива и производить транспонирование выделенного массива, то есть меняет местами столбцы и строки (переворачивает таблицу). Чтобы использовать данную функцию следует:

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

Формулы массива в EXCEL, возвращающие несколько значений

history 31 марта 2013 г.
    Группы статей

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

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

Начнем сразу с примеров.

СОВЕТ :Начальные знания о формулах массива можно прочитать в статье Формулы массива. Знакомство . О формулах массива, возвращающих одно значение можно прочитать в статье Формулы массива, возвращающие одно значение .

Пример1. Создание таблицы умножения

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

Для расчета всей таблицы умножения использована только одна формула массива =B4:K4*A5:A14

Чтобы создать таблицу умножения нужно:

  • создать строку (см. строка 4 на рисунке выше) и столбец (A) со значениями – множителями (1, 2, 3, …);
  • выделить диапазон B5:K14 , в котором будет размещены результаты произведения множителей;
  • в Строке формул ввести формулу массива =B4:K4*A5:A14 и нажать CTRL+SHIFT+ENTER ;
  • все выделенные ячейки автоматически заполнятся результатами перемножения соответствующих множителей из строки и столбца.

Теперь попробуем удалить любой компонент формулы массива. Например, щелкните ячейку E7 и нажмите клавишу DELETE . Появится сообщение: Нельзя изменять часть массива . Для того, чтобы удалить формулу массива, возвращающую множество значений, придется выделить весь диапазон ячеек B 5: K 14 и нажать клавишу DELETE .

Таким же образом можно изменить формулу: выделите весь диапазон ячеек, в Строке формул измените формулу. После этого нужно нажать сочетание клавиш CTRL+SHIFT+ ENTER , чтобы подтвердить изменение формулы.

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

Иногда возникает необходимость увеличить диапазон формулы массива. Увеличим размер таблицы умножения до 10х14:

  • выделите диапазон ячеек, содержащий текущую формулу массива ( B5:K14 ), а также пустые ячейки ( B15:K18 ), расположенные ниже.
  • в Строке формул измените формулу массива на =B4:K4*A5:A18 ;
  • нажмите сочетание клавиш CTRL+SHIFT+ENTER . Формула в ячейках B5:K14 будет обновлена, при этом экземпляры новой формулы (и результаты их вычисления) будут вставлены в новые ячейки B15:K18 .

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

Пример2. Три наибольших значения

Найдем 3 наибольших значения в списке и выведем их в 3-х ячейках.

Формула массива для решения этой задачи: =НАИБОЛЬШИЙ(A2:A11;<1:2:3>)

  • выделите 3 ячейки, которые будут содержать 3 наибольших значения.
  • в Строке формул введите вышеуказанную формулу массива;
  • нажмите сочетание клавиш CTRL+SHIFT+ENTER .

Выделенные ячейки будут заполнены значениями. Стоит обратить внимание на использованный в формуле массива массив констант <1:2:3>. Элементы разделены двоеточием, что заставляет интерпретировать EXCEL данный массив констант как столбец. Эту задачу можно конечно решить и без формул массива записав в 3-х ячейках 3 различные формулы: =НАИБОЛЬШИЙ(A2:A11;1) =НАИБОЛЬШИЙ(A2:A11;2) =НАИБОЛЬШИЙ(A2:A11;3) Здесь наглядно видно, что формулы массива представляют лишь сокращенную запись группы обычных формул.

Ссылка на основную публикацию