Добавление формулы в таблицы подстановки данных

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

Ø В ячейку Е13 ввести формулу: =($D$13*Срок_кредита-Кредит).

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

Ø Нажать клавишу Enter. В эту ячейку Excel поместит результат вычислений по введенной формуле для значения процентной ставки 8,5 %.

Ø Выделить диапазон ячеек C13:E20 и выполнить команду Данные – Таблица подстановки.

Ø Щелкнуть в поле Подставлять значения по строкам в и выделить ячейку D8.

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

Рабочий лист должен выглядеть следующим образом:

Таблица подстановки с двумя переменными

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

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

Ø Начиная с ячейки С25, ввести в столбце процентные ставки. Указанные действия показаны на следующем рисунке:

 

Ø Выделить ячейку D13 и выполнить команду Правка – Копировать.

Ø Правой кнопкой мыши щелкнуть на ячейке С24, выбрать в контекстном меню команду Специальная вставка и вставить в ячейку только формулу.

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

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

Ø Выделить диапазон ячеек C24:I31.

Ø Выполнить команду Данные – Таблица подстановки.

Ø Щелкнуть в поле Подставлять значения по столбцам в и выделить ячейку D9.

Замечание. Так как данные подстановки, определяющие различные сроки кредита, располагаются в строке, то для перехода от одного из них к другому нужно двигаться по столбцам. Поэтому в данном случае заполняется поле Подставлять значения по столбцам в. Адресация к ячейке D9 объясняется тем, что именно эта ячейка носит имя Срок_кредита.

Ø Щелкнуть в поле Подставлять значения по строкам в и выделить ячейку D8.

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

Ø Щелкнуть по кнопке ОК.

Лист Размеры платежей будет иметь следующий вид:

 

Массивы в Excel

 

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

Расчет НДС, как пример умножения элементов массива на число

В качестве примера использования формулы массива приведем расчет цен группы товаров с учетом налога на добавленную стоимость (НДС). Пусть, например, в диапазоне ячеек В2:В4 даны цены группы товаров без учета НДС. Необходимо найти цену каждого товара с учетом НДС (который будем полагать равным 25%). Таким образом, необходимо умножить массив элементов в ячейках В2:В4 на 125%. Результат надо разместить в ячейках диапазона С2:С4 (рис.5.1). Необходимо выполнить следующие действия:

1. Выберите диапазон, например, С2:С4, в котором будет размещен результат умножения первоначального массива на число. От диапазона, в котором будет находиться результат, требуется, чтобы он имел тот же размер, что и исходный диапазон (рис. 6.1).

2. Введите формулу:

=В2:В4*125%

3. Завершите ввод формулы не нажатием клавиши <Enter>, а нажатием комбинации клавиш <Ctrl>+<Shift>+<Enter>. Таким образом, вы сообщите Excel, что необходимо выполнить операцию над массивом, т.е. создать формулу массива. В ответ Excel автоматически возьмет формулу в фигурные скобки (рис. 6.2): {=B2:B4*125%}

Рис. 6.1. Выделение диапазона для ввода результирующего массива

 

 

Рис. 6.2. Умножение элементов массива на число