Подбор параметров
При обработке табличных данных часто возникает необходимость прогнозировать результат на основе известных исходных значений или наоборот определить какими должны быть исходные значения для получения указанного результата. Подобные задачи решаются с помощью программы Подбор параметров.
Задача на начисления сложных процентов: известен размер вклада, например 150000$, который1 будет помещен на срок 20 лет под заданный процент 5%.Требуется вычислить коэффициент наращивания(значение, показывающее во сколько раз увеличится вклад за указанный срок) и сумму выплат в конце периода(Рис.12).При изменении значения размера вклада, срока вклада или процентной ставки соответственно изменяется и сумма выплат.
Пользуясь этой моделью, можно установить как влияют исходные значения на конечный результат. Проанализировать таблицу, меняя размер вклада и процентную ставку. Вернутся к исходным данным.
Задача: определить значение исходных данных, исходя из заданной суммы выплат, например 500 000 $.Выделить ячейку С13 ,которая является целевой ячейкой, далее использовать Сервис/Подбор параметров ,указать в поле Значение целевое значение -500000.Оптимизируем по параметру размер вклада, поэтому в поле Изменяя ячейку необходимо указать ячейку С4 и ОК..
Ответ: Сумма вклада=188444,7$, чтобы при процентной ставке в 5%годовых через двадцать лет получить полмиллиона.
Рис.12 Таблица начисления сложных процентов
Упражнение 3
Задание 1
В окне Выбор источника данных следует установить БД MS ACCESS и ОК.
Откроется диалог:
Команда меню Данные/импорт внешних данных/Создать запрос.
МS Query открывает диалоговое окно Выбор источника данных. Выбрать существующий источник данных, базу Борей. Необходимо убедится ,что в диалоге Выбор источника данных установлен флажок Использовать мастер запросов и ОК. Откроется диалог Выбор базы данных , в окне Каталоги найти учебную базу, путь к ней С:\Program Files\Ms Office\Office10\Samples\Борей.mdb.
Откроется ДО мастера Создание запроса: Выбор столбцов.
В окне Имеющиеся таблицы и столбцы выделить таблицу Заказано и
щелчок на кнопке переноса данных (она помечена символом">").
При этом заголовки всех столбцов (полей) таблицы появятся рядом в поле Столбцы запроса. Чтобы включить в запрос избранные поля таблицы (таблиц) источника данных щелчок на знаке плюс(+)возле имени таблицы в поле Имеющиеся таблицы и столбцы. Знак (+)плюс сменится знаком минус (-),а заголовки полей таблицы появятся в списке. Выделить поля включаемые в запрос. И щелчок на кнопке переноса. Заголовки полей появятся в поле Имеющиеся таблицы и столбцы.
Выбрав всю таблицу Заказано ,щелчок на кнопке далее . Откроется диалоговое окно Создание запроса: отбор данных.Данное окно позволяет фильтровать данные столбца источника. Выделить поле Цена и задать условие отбора: больше 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.
В ДО укажите передачу данных на новый лист.