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

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

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

Сначала составим список процентных ставок.

В ячейки G5 и Н5 введите формулы для определения коэффициента наращения и суммы выплат: G5: =(1+С8)^С6; Н5:=С10*С4. Данные формулы сходны с формулами из ячеек С10 и С13. В эти формулы в дальнейшем должны быть подставлены значения процентной ставки из списка. Формулы для вычислений должны находиться в первой строке таблицы, поскольку значения в списке могут быть считаны только в направлении возрастания номеров строк.

Построим таблицу подстановки с одной переменной, в качестве которой выберем процентную ставку. Это значение находится в ячейке С8, на которую ссылаются формулы в ячейках G5 и Н5.

Выделите диапазон ячеек со списком исходных значений и диапазон ячеек с формулами (F5:Н19). Дайте команду Данные ► Таблица подстановки. Установите курсор ввода в поле Подставлять значения по строкам в диалоговом окне Таблица подстановки и задайте местонахождение входной ячейки. Выделите ячейку С8, чтобы вставить ее адрес в поле ввода, и нажмите кнопку ОК. После этого начнется процесс создания таблицы подстановки.

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

7. Изучите получившуюся таблицу.

8. Сохраните рабочую книгу.

Контрольные вопросы:

1. Что такое целевая ячейка?

2. При подборе параметра что обязательно должна содержать целевая ячейка?

3. Можно ли при подборе параметра просматривать промежуточные результаты вычисления?

4. Для чего используется таблица подстановки?

5. Как задаются параметры в таблице подстановки?

Задание. Составить несколько вариантов штатного расписания,воспользовавшись функцией "Подбор параметра"

Постановка задачи

Допустим начальник Центра занятости населения должен составить штатное расписание, т.е определить сколько работников и на каких должностях и с какой зарплатой он должен принять на работу. Причем всего выделено 10000$ (общий фонд зарплаты).

Для нормальной работы нужно, например,

• уборщицы -2
• Секретарь -1
• Регистратор б/работных -2
• Инспектор по работе с безработными – 5
• Программист - 3
• Психолог -1
• Специалист по работе с безработными – 6
• Экономист -1
• Гл. бухгалтер -1
• Кассир -1
• Завхоз -1
• Нач. кадров - 1
• Начальник ЦЗН

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

Начальник ЦЗН принимает на себя следующую модель задачи. За основу берется оклад уборщицы, а все остальные вычисляются через него во столько- то раз или на сколько – то больше. Говоря математическим языком, каждый оклад является линейной функцией от оклада уборщицы: Х*U+Y,где

Г –оклад уборщицы, а Х и У коэффициенты, которые для каждой должности определяются решением совета трудового коллектива.

Допустим, что совет трудового коллектива решил:, что секретарь должен получать в 1,5 раза больше уборщицы, завхоз в 2 раза больше,инспектор в 3 раза больше, специалист в 4 раза больше + надбавка в 100$?, бухгалтер в 5 раза больше, экономист в 5 раз больше + надбавка 80$. Начальник отдела кадров в 4 раза больше. Заведующий в 6 раз больше. Тогда первый вариант штатного расписания будет рассчитываться:

С помощью функции «Подбор параметра составьте другие варианты.

Работа с массивами [1]