Анализ данных: подбор параметра

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

Задание 1. Поиск заданного значения с помощью механизма подбора параметра.

Необходимо узнать количество сдаваемых в прокат видеомагнитофонов, чтобы прокатный бизнес не был убыточен. Другими словами, нужно узнать, сколько должно быть в прокате предметов, чтобы оплатить суммарные расходы и получить заданный чистый доход. Для этого можно использовать анализ «что-если» путем подбора параметра.

1. Откройте файл Упражнения.xlsx.

2. Вставьте в рабочую книгу новый лист с именем Упр.10.

3. Введите исходные данные, показанные на рис. 10.2.

4. Для расчетных полей используйте формулы:

5. Суммарный доход =В2*В3

6. Чистый доход =В4–В5

Рис. 10.2. Исходные данные

7. Выделите ячейку с формулой, которую нужно проанализировать (в данном примере это В6).

8. Перейдите на вкладку Данные и в разделе команд Работа с данными выберите команду Анализ «что-если» .

9. Выберите команду Подбор параметра (см. рис. 10.1).

10. В появившемся диалоговом окне введите 0 в поле Значение (т.е. задайте то значение, которое хотите получить), а в поле Изменяя значение ячейки – $В$3 (т.е. укажите ссылку на ячейку, которую хотите изменить) (рис. 10.3).

11. Нажмите на клавишу ОК.

Рис. 10.3. Диалоговое окно Подбор параметра

12. Excel подсчитает значение с помощью анализа «что-если» и покажет вам окно результата подбора параметра (рис. 10.4).

Рис. 10.4. Диалоговое окно Результат подбора параметра

13. Если хотите обновить данные на листе, нажмите на клавишу ОК.

Обратите внимание, что при открытом диалоговом окне Результат подбора параметра результат вычислений виден в ячейке В3 – это значение 100.

14. Нажмите на клавишу Отмена, чтобы оставитьна рабочем листе исходные данные.

15. Проверьте, что активной является, по-прежнему, ячейка В6, т.е. та, в которой есть формула рас чета чистого дохода.

16. Выполните команду Подбор параметра

17. Установите в поле Значение новое число – 50000р.

18. Установите в поле Изменяя значение ячейки – $В$2. В результате Excel рассчитает новое значение Платы за прокат – это 1000р.

19. Нажмите на клавишу Отмена, чтобы снова оставитьна рабочем листе исходные данные.

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

Задание 2. Создание таблицы подстановки для одной переменной с расположением исходных данных по строкам.

Необходимо узнать, как будет меняться Чистый доход при изменении Платы за прокат. Это можно сделать, используя первый способ – анализ «что-если». Однако в этом случае пришлось бы повторять всю процедуру для каждого значения Платы за прокат. Гораздо более быстрым и простым способом является использование таблицы подстановки данных с одной переменной, которая проверяет воздействие выбранной переменной на несколько формул.

1. В столбец С (Плата за прокат) введите значения, для которых необходимо найти альтернативные варианты подсчета Чистого дохода: 1000 р., 1250 р. и т.д. (используйте режим автозаполнения) рис. 10.5.

Рис. 10.5. Таблица подстановки с расположением

переменных по строкам

2. В ячейку D2 введите формулу, которая возвращает значение Чистого дохода (т.е. просто нужно сослаться на соответствующую ячейку, где эта формула уже имеется - это ячейка В6).

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

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

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

3. Выделите диапазон ячеек С2:D11 (таблица должна выделяться без заголовков).

4. Откройте вкладку Данные, в группе команд Работа с данными выполните команду Анализ «что-если», затем команду Таблица данных.

5. В появившемся диалоговом окне Таблица данных в поле Подставлять значения по строкам в введите ссылку на входную ячейку, для которой должны быть изменены исходные значения в таблице, т.е. $В$2 (рис. 10.6) и нажмите на клавишу ОК.

Рис. 10.6. Диалоговое око Таблица данных для построения таблицы подстановки с одной переменной

6. Результат расчета Чистого дохода появится в столбце D (см. рис. 10.5).

Задание 3. Создание таблицы подстановки для одной переменной с расположением исходных данных по столбцам.

1. В ячейки В14:J14 введите значения Количества предметов от 100 до 300 с помощью режима автозаполнения с шагом 25 (рис. 10.7).

2.

Рис. 10.7. Таблица подстановки с расположением

переменных по столбцам

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

формула для расчета должна находиться ниже на одну ячейку исходных данных;

формула для расчета должна находиться левее столбца c исходными данными.

3. В ячейку А15 введите формула для расчета Чистого дохода или ссылку на готовую формулу, например, на ячейку В6, как показано на рис. 10.7.

4. Выделите диапазон ячеек А14:I15.

5. Откройте диалоговое окно Таблица данных.

6. В поле Подставлять значения по столбцам в укажите ссылку на ячейку В3, чтобы подсчитать Чистый доход в зависимости от переменной Количество предметов (рис. 10.8).

Рис. 10.8. Диалоговое око Таблица данных для построения таблицы подстановки с одной переменной

7. Нажмите на клавишу ОК. В результате произойдет заполнение таблицы расчетными данными Чистого дохода (см. рис. 10.7).

Задание 4. Создание таблицы подстановки для двух переменных.

Необходимо узнать, как будет изменяться Чистый доход при изменении двух переменных: Платы за прокат и Количества предмето в, находящихся в прокате.

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

1. Создайте таблицу подстановки для двух переменных (рис. 10.9).

Рис. 10.9. Готовая таблица подстановки для двух переменных

2. В ячейки G3:O3 введите количество предметов от 100 до 300 с шагом 25, а в ячейки F4:F12 введите значения Платы за прокат от 1000р. до 3000р. с шагом 250.

3. На пересечении исходных данных в ячейку F3 введите ссылку на формулу, расположенную в ячейке В6 (см. рис. 10.9).

4. Правильно выделите диапазон ячеек для построения таблицы данных – F3:О12. Это очень важный этап, т.е. необходимо захватить формулу, оба диапазона переменных и пустые ячейки, куда будут внесены расчетные значения Чистого дохода.

5. Перейдите на вкладку Данные, в разделе Работа с данными выполните команду Анализ «Что, если…».

6. Введите в поле Подставлять значения по столбцам в адрес ячейки, в которой храниться значение, определяющее количество предметов – это ячейкаВ3 (рис. 10.10).

Рис. 10.10. Диалоговое око Таблица данных для построения таблицы подстановки с двумя переменными

7. Введите в поле Подставлять значения по строкам в адрес ячейки, в которой хранится значение, определяющее Плату за прокат – это ячейка В2.

8. Нажмите на клавишу ОК и проанализируйте заполненную таблицу.

Задание 5. Самостоятельно выполните примеры на создание таблиц с одной и двумя переменными.

Пример 1. Создание таблицы с одной переменной.

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

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

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

Если значения для таблицы подстановки с одной переменной расположены в строке, то введите формулу в ячейку, расположенную на один столбец левее и на одну строку ниже первого значения (рис. 10.11).

 

 

Рис. 10.11. Пример 1

Пример 2. Создание таблицы с двумя переменными.

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

 

 

Рис. 10.12. Пример 2