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

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

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

Чтобы ввести формулу массива, нажмите клавиши CTRL+SHIFT+ENTER. Microsoft Excel заключит формулы массива в фигурные скобки( { } ).

Например, суммирование двух столбцов можно выполнить двумя способами.

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

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

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

На листе определены имена для диапазонов $A$2:$C$6 и $A$8:$D$17, а также для всех столбцов второго диапазона.

В ячейке F6 находится формула, позволяющая найти сумму столбца КОЛВОИКЦК

{ =СУММ(КОЛВОИКЦК) }.

В ячейке F7 находится формула, позволяющая найти сумму произведений столбцов КОЛВОИКЦК и ЦЕНАИКЦК.

{ =СУММ(ЦЕНАИКЦК*КОЛВОИКЦК) }.

В ячейке F2 находится формула, позволяющая найти частичную сумму произведений столбцов КОЛВОИКЦК и ЦЕНАИКЦК. Эта же формула скопирована в ячейкиF3,F4.

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

В ячейках G2:H2 находится формула, позволяющая найти разность между частичной суммой произведений столбцов КОЛВОИКЦК и ЦЕНАИКЦК и частичной суммой произведений столбца КОЛВОИКЦК и третьего столбца диапазона кнс. . Эта же формула скопирована в ячейкиG3:H3,G4:H4. Запись этой формулы в две ячейки связана с особенностью электронной таблицы , видимо это связано с использованием в формуле массива функции ВПР.

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

Функции СУММЕСЛИ и СЧЁТЕСЛИ нельзя использовать в формулах массива.

Попытка ввести следующую формулу массива приведет сообщению об ошибке.

{=СУММЕСЛИ(ИМЯИКЦК;E2;ЦЕНАИКЦК*КОЛВОИКЦК)}

Более простая формула работает нормально, но не является формулой массива.

=СУММЕСЛИ(ИМЯИКЦК;E2; КОЛВОИКЦК)

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

Например, функцию КОРЕНЬ можно использовать, а функции И и ИЛИ нельзя.

Результаты в столбце В верны, а результаты в столбцах F,Gне соответствуют ожиданиям.

5. Таблица подстановок

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

Таблица подстановок всегда состоит из двух частей. Собственно таблички и рабочих ячеек, двух или одной.

Иногда в первом случае таблицу называют двухвходовой, а во втором одновходовой.

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

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

В 1-2-3 и Quattro для этого используется команда. Эту команду следует выполнять, если надо построить или пересчитать таблицу.

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

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

Рис.4.7. Использование таблицы подстановок.

Табличка занимает ячейки А2:Е8, формула для расчетов по таблице подстановок в А2, рабочие ячейки D1:E1.

6. Итоги

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

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

Рис.4.8. Результат команды Итоги.

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