Подбор параметров

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

Задача на начисления сложных процентов: известен размер вклада, например 150000$, который1 будет помещен на срок 20 лет под заданный процент 5%.Требуется вычислить коэффициент наращивания (значение, показывающее во сколько раз увеличится вклад за указанный срок) и сумму выплат в конце периода(Рис.12).При изменении значения размера вклада, срока вклада или процентной ставки соответственно изменяется и сумма выплат.

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

Задача: определить значение исходных данных, исходя из заданной суммы выплат, например 500 000 $.Выделить ячейку С13,которая является целевой ячейкой, далее использовать Сервис/Подбор параметров,указать в поле Значение целевое значение - 500000. Оптимизируем по параметру размер вклада, поэтому в поле Изменяя ячейку необходимо указать ячейку С4 и ОК..

Ответ: Сумма вклада=188444,7$, чтобы при процентной ставке в 5%годовых через двадцать лет получить полмиллиона.

Рис.12 Таблица начисления сложных процентов

Упражнение 3

Задание 1

В окне Выбор источника данных следует установить БД MS ACCESS и ОК.

Откроется диалог:

1. Команда меню Данные/импорт внешних данных/Создать запрос.
2. МS Query открывает диалоговое окно Выбор источника данных. Выбрать существующий источник данных, базу Борей. Необходимо убедится,что в диалоге Выбор источника данных установлен флажок Использовать мастер запросов и ОК. Откроется диалог Выбор базы данных, в окне Каталоги найти учебнуюбазу, путь к ней С:\Program Files\Ms Office\Office10\Samples\Борей.mdb.
3. Откроется ДО мастера Создание запроса: Выбор столбцов.

В окне Имеющиеся таблицы и столбцы выделить таблицу Заказано и

щелчок на кнопке переноса данных (она помечена символом">").

При этом заголовки всех столбцов (полей) таблицы появятся рядом в поле Столбцы запроса. Чтобы включить в запрос избранные поля таблицы (таблиц) источника данных щелчок на знаке плюс(+)возле имени таблицы в поле Имеющиеся таблицы и столбцы. Знак (+)плюс сменится знаком минус (-),а заголовки полей таблицы появятся в списке. Выделить поля включаемые в запрос. И щелчок на кнопке переноса. Заголовки полей появятся в поле Имеющиеся таблицы и столбцы.

4. Выбрав всю таблицу Заказано,щелчок на кнопке далее. Откроется диалоговое окно Создание запроса: отбор данных. Данное окно позволяет фильтровать данные столбца источника. Выделить поле Цена и задать условие отбора: больше 272.00руб и меньше 625.00 руб. Выбрав столбец данных из таблицы источника, подлежащих фильтрации, указать мастеру запроса тип сортировки. В раскрывающемся списке в разделе Возвращать только записи удовлетворяющие условиям выбрать оператор фильтрации данных(равно, не равно, больше меньше, начинается с, похоже на и.т.д.) После выбора оператора указывается условие отбора рядом в раскрывающемся списке. Завершив построение фильтра данных, щелчок на кнопке Далее, чтобы продолжить работу. Открывается диалоговое окно Создание запроса: порядок сортировки, необходимо сортировать данные, по возрастанию или по убыванию. Отсортировать список по возрастанию.

.После выбора порядка сортировки щелчок на Далее,открывается последнее окно

три различных возможности:

●-Сохранить запрос, чтобы применить его позднее.

●-Вернуть данные в MS Excel для анализа.

●-Выполнить просмотр или изменение данных в MS Query, применяя средства

мастера запросов и при необходимости отредактировать запрос.

Задание 2

Сохранить запрос щелчок на кнопке Сохранить запрос, откроется диалог Сохранить как с открытой папкой Запросы. Дать имя запросу, расширение запрос получит- .dqy и ОК. Возвращаемся к последнемушагу мастера и выбираем переключатель

● -Выполнить просмотр или изменение данных в MS Query и Готово.

Открывается окно MS Query c таблицей Заказано, с заданными критериями и отфильтрованной информацией в нижней части окна. Далее в бланке запроса добавить дополнительные условия отбора по полю Скидка -больше 0.05 и меньше 0.2.Для чего рядом с полем Цена из раскрывающегося списка выбрать поле Скидка и в области Значение задать дополнительные условия отбора. Далее щелчок на ПИ на кнопке с восклицательным знаком - Выполнить запрос. Произойдет фильтрование данных по указанным условиям.

Добавьте еще одну таблицу в запрос, например Заказы и добавить поле Стоимость доставки. Добавить критерий больше 60.00руб и <300.00руб. Далее выполнить запрос и Сохранить.

Сохранить запрос Файл/Сохранить ивернуть данные в MS Excel Файл/Вернуть данные в MS Excel.

В ДО укажите передачу данных на новый лист.

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

1. Какие задачи решает программа Подбор параметров?
2. Чем отличается Поиск решения от Подбор параметров?
3. Что такое сценарий?
4. Что такое отчет на устойчивость?
5. Что такое запрос?
6. Как задать ограничения в ДО Поиск решения?
7. Что необходимо сделать, если нет в меню команды Поиск решения?

Литература

1. Лавренев С.М. «EXCEL.Сборник примеров и задач» - М., Финансы и статистика,
   2000. – 336 с.
2. Макарова Н.В. «Статистика в EXCEL»- М.,Финансы и статистика, 2003. – 366 с.
3. Гарнаев А. «EXCEL,VBA,INTERNET в экономике и финансах» - СПб.:БХВ - Петербург.»,2001.-816с.
4. Евдокимов В.В. «Экономическая информатика». Учебник для вузов. СПб.: Питер.1997.-598с.
5. С.В.Симонович. Информатика Базовый курс. СЛБ 2007 г.
6. Ф.Новиков, А. Яценко. Microsoft Office в целом.М.,2005 г.
7. Г.А.Новиков, П.А.Новиков, М.В.Орлова, А.М.Пылькин. Работа с текстовым процессором MS Word.-M,:Горячая линия- Телеком,2005.-198 с.

8.Безручко В.Т. Практикум по курсу «Информатика» Работа в WINDOS,WORD,EXCEL. М., Финансы и статистика,2004,271 c.

9. Информатика и информационные технологии / Уч. пособие. Под ред. Ю.Д. Романовой. M., 2010.

1. Колосков П.В, Кузнецова Н.А.. Самоучитель Windows Vista. Наука и техника, Санкт - Петербург, 2007.
2. Новиков Ф., Яценко А. Microsoft Office 2000 в целом. – СПб.: Санкт - Петербург, 1999.
3. Пасечник А. Переход на Windows 2000: для профессионалов. – СПб.: Питер, 2000.
4. Гаевский А.В. Самоучитель Word, Excel,Электронная почта. – Технолоджи - 3000: Москва, 2005.
5. Безручко В.Т.Практическая работаWord 2000. – СПб.: Питер, 2004.
6. Рудикова Л.В.Microsoft Excel для студентов. – СПб.: Санкт - Петербург, 2006.
7. Маликова Л.В., Пылькин А.Н., Жулева С.Ю. Практический курс по электронным таблицам MS Excel. – Телеком: Москва, 2006.
8. Калабухова Г.В., Титов В.М. Компьютерный практикум по информатике. М., 2008.
9. Лапунов А.В., Ульянов О.В., Прокди Р.Г. Интернет: Самоучитель. – М.: Наука и Техника, 2009.

19. Хожайнова Г.И. Курс лекций в электронном варианте.

Хожайнова Галина Ивановна

[1] Журнал издается в г.Москва

Дата добавления: 2014-10-31