4 страница

Вопрос 2

9.2. Прогнозирование с помощью команды «Подбор параметра»

Для решения задачи методом подбора параметра надо выполнить сле­дующие действия:

1) Выделить ячейку, содержащую формулу, для которой нужно найти определенное решение и вкладка Данные - группа Работа с данными - кнопка Анализ «что - если...» - выбрать пункт Подбор параметра

2) В поле «Установить в ячейке» ввести ссылку на ячейку, содержащую формулу (по умолчанию в это поле вводится адрес текущей ячейки)

3) В поле «Значение» ввести значение, которое нужно получить по заданной формуле.

4) В поле «Изменяя значение ячейки» ввести ссылку на ячейку, содержащую значение изменяемого параметра. Технология применения инструмента Подбор параметра рассматрива­ется на практических занятиях.

Вопрос 3

9.3. Использование команды Поиск решения для решения оптимиза-
ционных задач управления

Процедура поиска решения запускается так: вкладка Данные - груп­па Анализ - Поиск решения

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

Целевая ячейка - это ячейка содержащая формулу, для которой нужно найти максимальное, минимальное или заданное значение.

Изменяемые ячейки - это ячейки, от которых зависит значение це­левой ячейки.

Ограничения - это условия, накладываемые на некоторые ячейки. Ограничения могут быть наложены на любые ячейки таблицы, включая целевую и изменяемые ячейки.

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

Создание, изменение и удаление имен:

вкладка Формулы, группа Определенные имена, кнопка Диспетчер имен, кнопка Создать (или пункт Присвоить имя, команда Присвоить имя), диалоговое окно «Создание имени».

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

команды Поиск решения

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

1) нормативный расход производственных мощностей на изготовле­ние продукции

2) имеющиеся в наличии объемы производственных мощностей на предприятии

3) нормативные затраты сырья на изготовление продукции

4) имеющиеся в наличии объемы сырья

5) объемы заключенных договоров и объемы по прогнозу сбыта

Изменяемые ячейки - это объемы продаж тумб и столов.

Целевая ячейка - это прибыль, вычисляемая по формуле:

=тумба*(В6^6)+стол*(В7-Б7)

При решении задачи необходимо учесть следующие ограничения:

1. изменяемые ячейки E6, E7- целое

2. Ограничения на выпуск: объем продаж каждого вида продук­ции (т.е. изменяемы ячейки), должен быть больше или равен объему по заключенным договорам и меньше или равен объему по прогнозу сбыта, т.е.

В11 <=Е6тумба<=С 11, В12<=Е7стол журнальный<=С 12

3. Ограничения на сырье и материалы: расход сырья и материа­лов не должен превышать объема, имеющегося в наличии

(тумба*В17+стол* C17)<=D17

4. Ограничения на производственные мощности: объем использу­емых производственных мощностей не должен превышать имеющегося в наличии фонда времени работы оборудования

(тумба*Е11+стол*Е12)<=Е11

Выполнение.

1 .Ввод исходных данных:

Для наглядности изменяемым ячейкам присвоить имена - «Тумба » для ячейки Е6, «Стол» для ячейки Е7.

Исходные данные для задачи Поиск решения (режим чисел)

2. Ввод целевой функции в ячейку В19: =тумба*(B6-D6)+стол*(B7- D7) (вычисляет прибыль)

3. Ввод формул для вычисления ограничений:

• ячейку В23 введите формулу для вычисления объ­ема используемых производственных мощностей: =(тумба*EП+стол*E12)<=FП

• ячейку В24 введите формулу для вычисления рас­хода сырья и материалов: =(тумба*В17+стол*С17)<=D17

4. Установите курсор в ячейку с целевой функцией В19 и вкладка Данные, группа Анализ, кнопка Поиск решения.

5. В поле “Установить целевую ячейку” указать ссылку на ячейку, вычисляющую прибыль $В$19.

6. В поле “Равной” установить флажок “максимальному значению”

Примечание: если требуется получить конкретное значение прибыли, то нужно установить флажок “значению” и ввести нужное число.

7. В поле “Изменяя ячейки” ввести ссылки на изменяемые ячейки. В нашем примере это ссылки $Е$6:$Е$7.

8.Чтобы заполнить область Ограничения нажмите кнопку “Доба­вить”. Появится диалоговое окно “Добавление ограничения”:

Введем следующие ограничения:

В области Ссылка на ячейку ввести $E$6, затем из списка выбрать “цел”, в области Ограничения появится “ целое ”, нажать кнопку доба­вить. Аналогичным образом введите ограничение для ячейки E7. Затем введите ограничения:

тумба>= $B$11 тумба<=$С$11 стол>=$В$12 стол<=$С$12

$B$23<=$F$11 на сырьевые ресурсы,

$B$24<=$D$17 на производственные ресурсы

Изменить или удалить ограничения можно с помощью кнопок «Из­менить» и «Удалить».

9.После ввода всех ограничений для запуска Поиска решения нажми­те кнопку Выполнить. На экран будет выведено окно “Результаты поиска решения:

10. Установите переключатель «Сохранить найденное решение» и выберите «Тип отчета» - Результаты.

Создание отчетов по результатам поиска решения

Поиск решения позволяет создать три типа отчетов найденного реше­ния - Отчет по результатам, Устойчивость, Пределы. Отчеты автоматиче­ски создаются на новых листах РК. Если нужно создать два или три отче­та, то они выделяются с помощью клавиши CTRL.

Вопрос 4

9.4. Применение таблицы данных для решения задач прогнозирова-
ния. Виды таблиц данных

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

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

Таблица данных может использоваться в 2-х вариантах:

1. Для анализа нескольких функций - если эти функции имеют одну переменную.

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

Подсчет таблицы данных выполняется так:

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

Технология создание таблицы данных подробно изложена в Методи­ческой разработке по дисциплине «Информационные системы в экономи­ке» для организации индивидуальной работы студентов, обучающихся по направлению 080100.62 «Экономика» по теме «Информационные техно­логии создания систем поддержки принятия решений и методы прогнози­рования» за 2012 год.

Вопрос 5

9.5. Анализ данных с помощью Диспетчера сценариев

Сценарии являются частью блока задач, который иногда называют инструментами анализа "что-если".

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

Сценарий - это множество входных значений, называемых изме­няемыми ячейками, которое можно сохранить под указанным именем, а затем применить к модели рабочего листа, чтобы проследить, как значе­ния изменяемых ячеек влияют на другие значения модели. Для каждого сценария можно определить до 32 изменяемых ячеек.

При создании сценариев для наглядности отчетов рекомендуется при­своить ИМЕНА ячейкам:

Работа со сценариями выполняется командой:

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

В диалоговом окне «Диспетчер сценариев» предусмотрены кнопки для изменения и выполнения сценариев:

Вывести - запуск выбранного сценария,

Добавить - создание нового сценария,

Удалить - удаление выбранного сценария,

Изменить - редактирование параметров сценария (т.е. выбрать дру­гие ячейки листа и другие числовые значения),

Объединить - копирование сценариев из других раб книг в текущий раб лист,

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

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

Вопросы для самоконтроля

1. Прогнозирование с помощью команды Подбор параметра.

2. Использование надстройки Поиск решения для решения оптимиза­ционных задач управления.

3. Математический смысл методов Подбор параметра и Поиск реше­ния.

4. Отличие методов Подбор параметра и Поиск решения.

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

6. Назначение команды Таблица данных. Возможности применения Таблицы данных для анализа данных экономического характера.

Задания для практики

Используя исходные данные, приведенные в методической разработ­ке «Технология и методы прогнозирования средствами электронной таб­лицы» [1, с.5-15], выполните следующие задания:

Задание 1. Предприятие выпускает один вид продукции. Используя метод Подбор параметра решить следующие задачи:

1) определить, при каком значении объема производства прибыль примет заданное значение.Сохранить найденное решение в виде сценария.

2) определить, при каком значении цены реализации прибыль примет заданное значение. Сохранить найденное решение в виде сценария.

Порядок выполнения задания приведен в методической разработке «Технология и методы прогнозирования средствами электронной табли­цы» [1, с.5-15].

Задание 2. Предприятие выпускает три вида продукции. Используя исходные данные, приведенные в методической разработке «Технология и методы прогнозирования средствами электронной таблицы» [1, с.58-59], и метод Поиск решения найти вариант производственной программы на те­кущий месяц, позволяющий получить максимальную прибыль в кратко­срочном периоде при условии полной загрузки мощностей предприятия и большого спроса на продукцию.

Создать отчет по результатам Поиска решения. Сохранить найденное решение в виде сценария.

Порядок выполнения задания приведен в методической разработке «Информационные технологии создания систем поддержки принятия решений и методы прогнозирования» [1, с.15-20].

Задание 3. Предприятие выпускает три вида продукции. Используя исходные данные, приведенные в методической разработке «Технология и методы прогнозирования средствами электронной таблицы» [1, с.58-59], и метод Поиск решения найти вариант производственной программы пред­приятия на текущий месяц, который бы позволил получить заданное зна­чение прибыли, в случае полной загрузки мощностей предприятия и большого спроса на продукцию.

Создать отчет по результатам Поиска решения. Сохранить найденное решение в виде сценария.

Порядок выполнения заданий приведен в методической разработке «Информационные технологии создания систем поддержки принятия ре­шений и методы прогнозирования» [1, с.15-20].

Задание 4.

1. Используя Таблицу данных с одной переменной, составить план по­гашения кредита размером 400000 рублей, выданного банком на срок 5 лет, при ставке 11% годовых.

2. Используя Таблицу данных с двумя переменными, рассчитать вы­платы по процентам в зависимости от размера кредита, выданного банком на срок 5 лет, при ставке 11% годовых.

Исходные данные для выполнения заданий и порядок выполнения приве-дены в методической разработке «Информационные технологии со­здания си-стем поддержки принятия решений и методы прогнозирования» [1, с.49-54].

Использованные информационные ресурсы

1. Бизнес-аналитика средствами Excel: Уч.пос. / Я.Л.Гобарева,

О.Ю.Городецкая, А.В.Золотарюк; Финансовый универ. при Правительстве РФ. - М.: Вуз.уч.:НИЦ ИНФРА-М, 2013 -с.141-153.

2. Информатика и информационные технологии: учебное пособие / Под ред. Романовой Ю.Д. - М.: Изд-во Эксмо, 2009. - с. 329-335, 361-366.

3. Лавренов С.М. Excel: Сборник примеров и задач / Лавренов С.М. - М.: Финансы и статистика, 2006. - Глава 11.

Лекция 10. Технология использования баз данных для создания информа-
ционных систем

10.1. База данных - основа автоматизированных информационных систем Структурные элементы базы данных

10.2. Основных модели баз данных: иерархическая, сетевая, реляционная

10.3 Этапы проектирование баз данных. Логическое и физическое проектиро­вание

10.4.Понятие СУБД Access. Объекты СУБД Access - таблицы, запросы, формы, отчеты, макросы, модули

10.5.Постановка экономической задачи для проектирования АИС средствами СУБД Access

10.1. База данных - основа автоматизированных информационных систем

Структурные элементы базы данных

Банк данных — это система специально образом организованных данных (баз данных), а также программных, технических, языковых, организационно­методических средств, предназначенных для обеспечения централизованного накопления и коллективного использования данных.

Банк данных включает в себя два основных компонента.

• базу данных;

• систему управления базой данных.

База данных - это совокупность взаимосвязанных, хранящихся вместе данных, при этом данные хранятся независимо от использующих их программ. Система управления СУБД обеспечивает организацию ввода обработки и хра­нения данных.

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

Структурные элементы банка данных:

1. Словарь данных

2. Вычислительная система

3. Администратор базы данных

10.2. Основных модели баз данных: иерархическая, сетевая, реляци­онная

Модель данных - это совокупность структур данных и операций их об­работки. С помощью модели данных можно легко отобразить структуру объек­тов и связи, установленные между ними.

Существует множество различных моделей данных. К числу важнейших относятся следующие три модели, иерархическая, сетевая, реляционная.

1. Иерархическая модель данных представляет собой совокупность связанных элементов, образующих иерархическую (древовидную) структуру.

Примером просто иерархического представления может служить органи­зационная структура высшего учебного заведения: институт - факультет - курс - группа. Пример построения иерархической структуры приведен на рис. 10.1.

Рис. 10.1. Пример построения иерархической модели данных

2. Сетевая модель данных. В основе этой модели лежат те же поня­тия, что и в основе иерархической модели данных - узел, уровень и связь. Од­нако существенным отличием сетевой модели данных является то, что узел может быть связан с любым другим узлом, в том числе лежащим на уровне, не связанном непосредственно с уровнем исходного узла.

Простейшим примером сетевой модели данных может служить база дан­ных, в которой хранится информация о студентах вуза, при этом элемент «Студент» может иметь не один, а два исходных элемента: «Студент» - «Но-

мер группы» и «Студент» - «Комната в общежитии»». Взаимосвязь между объектами сетевой структуры приведена на рис.10.2.

Рис. 10.2. Пример построения сетевой модели данных

3. Реляционная модель данных получила название от английского термина relation - отношение.

Реляционная модель данных позволяет представить любой набор дан­ных в виде двумерной таблицы, рис.10.3.

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

10.3 Этапы проектирование баз данных. Логическое и физическое

проектирование

Весь процесс проектирования базы данных можно разбить на ряд взаимосвязанных этапов, каждый из которых обладает своими особенно­стями и методами проектирования. На рис. 10.4. представлены этапы про­ектирования базы данных:

- первый этап - инфологическое проектирование;

- второй этап - датологическое проектирование.

Информационные
потребности пользователей

I

Проектирование инфологической модели
предметной области

Построение концептуальной модели данных

I

Построение физической модели данных

Рис. 10.4. Этапы проектирования базы данных

1. Инфологическое проектирование.

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

Но второй стадии инфологического проектирования разрабатывается инфологическая модель предметной области.

Она представляет собой описание структуры и динамики предметной об­ласти, характера информационных потребностей пользователей системы в тер­минах, понятных пользователю и не зависящих от реализации системы на ком­пьютере и выбранной СУБД.

2. Датологическое проектирование.

Датологическое проектирование подразделяется на логическое (постро­ение концептуальной модели данных) и физическое (построение физической модели) проектирование.

Главной задачей логического проектирования базы данных является представление выделенных на предыдущем этапе сведений в виде данных в форматах, поддерживаемых выбранной СУБД.

Задача физического проектирования - выбор способа хранения данных на физических носителях и методов доступа к ним с использованием возмож­ностей СУБД.

На этапе физического проектированиярешаются вопросы построения структуры хранимых данных, их размещение в памяти, выбора эффективных методов доступа к различным компонентам физической базы данных. Описы­вается также отображение логической структуры базы данных в структуру хра­нения.

10.4. Понятие СУБД Access. Объекты СУБД Access - таблицы, запросы,

формы, отчеты, макросы, модули

СУБД Access - это система управления реляционными базами данных.

СУБД Access позволяет выполнять следующие действия.

1) Проектирование базовых объектов информационных систем - двумерных таблиц с разными типами данных.

2) Установление связей между таблицами, с обеспечением механизма целост­ности данных, обновления и удаления записей.

3) Ввод, хранение, просмотр, сортировка, модификация и фильтрация данных из таблиц.

4) Создание, модификация и использование различных объектов (форм, запро­сов и отчетов).

СУБД Access работает со следующими объектами:

1. Таблица - основной объект базы данных, предназначенный для хранения данных.

2. Запрос - средство отбора данных из одной или нескольких таблиц на основании определенного условия.

ими.

4. Отчет - средство создания выходных документов и вывода их на печать.

5. Макрос - средство автоматизации выполнения конкретной опера­ции.

6. Модуль - это программы на языке VisualBasic, применяемые для настройки, оформления и расширения БД,

10.5. Постановка экономической задачи для проектирования АИС сред­ствами СУБД Access

1. Организационно-экономическая сущность задачи

Назначение задачи: создание автоматизированной информационной си­стемы на предприятии с дискретным характером производства и сдельной фор­мой оплаты труда на примере решения экономической задачи “Учет труда и за­работной платы на предприятии”.

Цель: созданная автоматизированная информационная система “Учет труда и заработной платы на предприятии” позволит своевременно выполнять начисление заработной платы для каждого рабочего в соответствии с произве­денной им продукцией.

Используемое программное обеспечение для решения задачи: система управления базами данных (СУБД) ACCESS.

2.Описание оперативной информации задачи Перечень оперативной информации: “Наряд на сдельную работу”, “До­платной лист”.

Вид представления оперативной информации: таблица 1, таблица 2.

Доплатной лист

Структура оперативных документов: таблица 1а, таблица 2а.

Структура документа “Наряд на сдельную работу”