Глава 3. Математические операции с массивами

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава                         Оглавление                              Следующая глава

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

Рис. 3.1. Формула массива суммирует разности между двумя массивами

Скачать заметку в формате Word или pdf, примеры в формате Excel2013

Если бы вы могли наблюдать вычисления шаг за шагом, вы бы увидели сначала D3:D6-C3:C6, потом {762:757:763:768}-{759:765:756:762}, потом {759-762:765-757:756-763:762-768}, и наконец {3:-8:7:6}. Каждый элемент второго массива вычитается из соответствующего элемента первого массива. Но что, если у вас один массив значений и вы бы хотели вычесть из каждого элемента массива одно и то же число? Возможно ли это? Да! Давайте посмотрим, как это сделать.

На рис. 3.2 показан набор данных, где в диапазоне B2:B5 показана стоимость, а в ячейке В8 – коэффициент чистой стоимости. Вы хотите вычислить цены с учетом скидки, а затем найти их сумму. Один из способов решить задачу – создать вспомогательный столбец, в котором вычислить цены со скидкой по каждому товару (С2:С5), а затем сложить эти значения (В9). На рис. 3.2 показан именно этот метод.

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

Но нельзя ли обойтись без вспомогательного столбца и написать формулу расчета в одной ячейке? Да, можно, и рис. 3.2 дает вам подсказку, как это сделать. Обратите внимание, что ячейка B8 умножается последовательно на ячейки от B2 до B5. Это, как если бы вы весь столбец умножили на B8. Похоже, что можно написать формулу массива, использовав произведение B2:B5*B8. Рис. 3.3 иллюстрирует применение такой формулы массива. Поскольку СУММПРОИЗВ – особая формула, нажатия Ctrl+Shift+Enter для ее ввода в ячейку не требуется.

Рис. 3.3. С помощью формулы массива расчет выполнен в одной ячейке

Если бы вы могли наблюдать вычисления по этой формуле шаг за шагом, вы бы увидели сначала B2:B5*B8, потом {12:27:9:18}*0,85, потом {12*0,85:27*0,85:9*0,86:18*0,85}, и наконец {10,2:22,95:7,65:15,3}.

На рис. 3.4 показано, как можно еще немного сократить расчеты.

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

Вы только что узнали, как вычесть число из массива. А наоборот: можно ли вычесть массив из числа? Как вы, наверное, догадались, ответ – «да». Давайте посмотрим, как это будет работать. Пример на рис. 3.5 показывает вычисление стоимости продукта, в условиях, когда вам предоставляется ряд скидок. Первый способ – применить вспомогательный столбец. Вы сами можете отследить последовательность вычислений в ячейках В2:В5, В7 и В10. Проблема в том, что ваша цель просто получить цену со скидкой и вам не нужны промежуточные шаги. Это идеальная ситуация для формулы в одной ячейке. Создание формулы для достижения того же конечного результата, не должно быть слишком сложным. Обратите внимание, что массив со скидками действительно вычитается из единицы.

Рис. 3.5. Формула массива, использующая вычитание массива из числа

Создания формулы массива:

  1. Начните с итоговой формулы, которая использовалась в методе решения с вспомогательной колонкой =ОКРУГЛ(B9*B7;2).
  2. Обратите внимание, что в ячейке B7, содержится формула =ПРОИЗВЕД(B2:B5). Подставьте ее в итоговую формулу. Получится =ОКРУГЛ(B9*ПРОИЗВЕД(B2:B5);2).
  3. Поскольку в ячейках B2:B5 содержатся лишь дополнять расчеты, замените их на вычитание массива из единицы: 1-А2:А5.
  4. Подставьте массив из п. 3 в формулу из п. 2; получится =ОКРУГЛ(B9*ПРОИЗВЕД(1-А2:А5);2).

Примечание. Обратите внимание, что функция ПРОИЗВЕД размещена внутри функции ОКРУГЛ. Размещение функции внутри другой функции, называется вложением функций. В этой книге, вы увидите довольно много таких вложений. Они являются весьма полезными, поскольку позволяют объединять возможности двух или нескольких функций в одной формуле. При создании сложных формул вы можете располагать элементы формул (отдельные функции) последовательно в соседних ячейках, а затем, копируя эти элементы, и вставляя их вместо соответствующих ссылок, вложить функции друг в друга, получив формулу в одной ячейке.

Вы должны определить, необходимо ли использовать Ctrl+Shift+Enter с созданной формулой? Рис. 3.6 показывает, что массив целиком относится к аргументу число1 функции ПРОИЗВЕД. Эта функция не была изначально запрограммирована для обработки операций с массивами. Чтобы Excel понял, что вы вводите формулу массива, используйте Ctrl+Shift+Enter. После ввода формулы, с помощью клавиш Ctrl+Shift+Enter, вы увидите фигурные скобки в строке формул (см. рис. 3.5).

Рис. 3.6. Аргумент число1 функции ПРОИЗВЕД изначально не запрограммирован для обработки операций с массивами, поэтому для ввода формулы массива используйте Ctrl+Shift+Enter

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

  • Массив, оператор, массив
  • Массив, оператор, один элемент
  • Один элемент, оператор, массив

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

Ваш адрес email не будет опубликован.