Информационные системы в экономике. Конспект лекций по дисциплине "Информационные системы в экономике" Размещён в электронном образовательном ресурсе (эор) по дисциплине "Информационные системы в экономике" (
 Скачать 0.56 Mb.
|
Вопрос58.5.Способы изменения структуры сводных таблиц 1.Изменение макета: Изменение формы отчета сводной таблицы: компактной, табличной или структурной ( вкладка Конструктор, кнопка Макет, пункт Макет отчета) Отображение итогов над или под строками данных (вкладка Конструктор, кнопка Макет, Промежуточные итоги) . Перемещение поля столбца в область строк или поля строки в область столбцов (контекстное меню, Показать список полей таблицы). Изменение отображения ошибок, пустых ячеек, а также элементов и подписей, не содержащих данные (контекстное меню, Параметры сводной таблицы ). Изменение порядка полей или элементов; добавление, изменение расположения и удаление полей (контекстное меню, Показать список полей таблицы) Задание ширины столбца при обновлении (контекстное меню, Параметры сводной таблицы.) Отключение или включение отображения заголовков полей столбцов и строк, а также отображение или скрытие пустых строк. (контекстное меню, Параметры сводной таблицы). 2.Изменение формата: Для этого используются следующие методы. Условное форматирование и форматирование вручную ячеек и диапазонов. Изменение общего стиля форматирования сводной таблицы. 3.Изменение параметров поля (наименования поля, формата представления данных, изменения метода обработки поля данных): 1. установить курсор на нужную ячейку в области «Значения» 2.контекстное меню, пункт «Параметры полей значений», 3. в диалоговом окне «Параметры поля значений» можно изменить наименование поля, в поле «Операция» выбрать другой метод обработки. формат представления данных, Удаление полей: Через контекстное меню, пунктУдалить. Технология создание в сводной таблице вычисляемых полей 1Установить курсор на сводную таблицу 2.Вкладка Параметры 3.Кнопка Сервис 4.Формулы, пункт Вычисляемое поле: в диалоговом окне «Вставка вычисляемого поля»: в поле «Имя» ввести имя нового вычисляемого поля, в поле «Формула»: в разделе «Поля» установить курсор на поле участвующее в расчете, нажать кнопку «Добавить поле», ввести знак арифметической операции, потом выбрать следующее поле или ввести константу и т.д. когда формула будет готова нажать кнопкуДобавить и ОК. Создание и редактирование сводной диаграммы. Примеры использования сводных таблиц и диаграмм Сводная диаграмма используется для визуализации данных сводной таблицы и упрощения процедуры сравнений, поиска закономерностей и тенденций. Сводная диаграмма позволяет принимать решения, основанные на полученной информации о важных данных в организации Если сводная таблица уже построена и нужно построить сводную диаграмму, то 1.установить курсор на своднуютаблицу 2.вкладка Параметры 3. кнопка Сервис, кнопка Сводная диаграмма Создание отчета сводной диаграммы без готовой сводной таблицы: Вкладка Вставка, кнопка СВОДНАЯ ТАБЛИЦА, пункт СВОДНАЯ диаграмма. Выбрать диапазон данных Переместить поля в нужные области С помощью вкладок Конструктор, Макет можно отредактировать диаграмму (ввести значения, легенду и т.д.). Обратить внимание: непосредственно под диаграммой в указанном месте автоматически создается связанный отчет сводной таблицы. При изменении макета любого из этих отчетов изменяется и другой макет. Вопросы для самоконтроля 1. Особенности подготовки данных при создания консолидированной таблицы. 2. Назначение консолидированной таблицы. 3. Методы и консолидации: консолидация по формулам,по расположению, по категориям. 4. Элементы структуры консолидированной таблицы. 5. Назначение и особенность сводной таблицы. 6. Структура сводной таблицы. 7. Технология создания сводной таблицы. 8. Особенность создания сводной таблицы, разбитой на страницы? 9. Технология построения сводной диаграммы. 10. Редактирование различных элементов сводной таблицы и сводной диаграммы. Задания для практики Задание по работе с консолидированными таблицами: 1. В новой рабочей книге MS Excel создать ведомости учета выработки рабочими-сдельщиками за январь, февраль и март 2011 года, таблица 8.1. Таблица 8.1 Ведомость учета выработки рабочими-сдельщиками за январь 2011 г.
Выполнение нормы выработки рассчитывается по формуле: Выполнение нормы выработки=Фактически отработанные часы / Нормо-часы Количество записей должно быть разным в каждой ведомости – 5 записей в январе, 10 – в феврале, 15 – в марте. 2. Выполнить консолидацию данных трех исходных ведомостей с помо-щью трехмерных ссылок с подсчетом нормо-часов и фактически отработанных часов и выполнения норм выработки за первый квартал 2011 г. в разрезе табельных номеров. 3. Выполнить консолидацию данных за первый квартал по категориям поля «Цех» с подсчетом среднего значения выполнения нормы выработки. 4. Выполнить консолидацию данных за первый квартал по категориям поля «Табельный номер» с подсчетом нормо-часов и фактически отработанных часов за первый квартал 2009 г. Технология выполнения заданий приведена в учебно-методическом пособии по курсу «Информатика» [3, с.77-83]. Задание по работе со сводными таблицами: Задания выполняются по данным ведомости за март 2011 г., созданной для выполнения задания по консолидации. 1. На основании данных ведомости за март 2011 г. построить сводную таблицу с подсчетом нормо-часов и фактически отработанных часов по табельным номерам за март 2011 г. Предусмотреть возможность выборки данных по цехам и табельным номерам. 2. Построить сводную таблицу на основании данных, расположенных в нескольких диапазонах консолидации – в ведомостях за январь, февраль, март. Вывести в сводную таблицу нормо-часы и фактически отработанные часы по табельным номерам за первый квартал 2011 г. Предусмотреть возможность выборки данных по месяцам и цехам. 3. На основании данных ведомостей за январь, февраль, март построить сводную диаграмму, отображающую нормо-часы и фактически отработанные часы по фамилиям работников за первый квартал 2011 г. Предусмотреть возможность выборки данных по месяцам и цехам. Технология выполнения заданий приведена в учебно-методическом пособии по курсу «Информатика» [3, с.83-87]. Использованные информационные ресурсы 1. Бизнес-аналитика средствами Excel: Уч.пос. / Я.Л.Гобарева, О.Ю.Городецкая, А.В.Золотарюк; Финансовый универ. при Правительстве РФ. - М.: Вуз.уч.:НИЦ ИНФРА-М, 2013 –с. 100-141. 2. Информатика и информационные технологии: учебное пособие / Под ред. Романовой Ю.Д. - М.: Изд-во Эксмо, 2009. – с. 338-341, 383-384. 3. Лавренов С.М. Excel: Сборник примеров и задач / Лавренов С.М. – М.: Финансы и статистика, 2006. – Глава 10. 4. Экономическая информатика: учебник / Под ред. В.П. Косарева и Л.В. Еремина – М.: Финансы и статистика, 2006. – с. 362-370. Тема 9 (2 часа): Информационные технологии создания систем поддержки принятия решения и методы прогнозирования План: 9.1. Способы прогнозирования в электронных таблицах MSExcel 9.2. Прогнозирование с помощью команды Подбор параметра. 9.3.Использование команды Поиск решения для решения оптимизационных задач управления. 9.4.Применение таблицы данных для решения задач прогнозирования. Виды таблиц данных. 9.5. Анализ данных с помощью Диспетчера сценариев. Вопрос 1 9.1.Способы прогнозирования в электронных таблицах MS Excel 1.Подбор параметра Подбор параметра является инструментом анализа "что-если". «Подбор параметра» решает задачи следующего вида: имеется формула, которая зависит от некоторого параметра. Необходимо определить такое значение этого параметра, которое позволяет получить нужный результат формулы. Математическая суть задачи состоит в решении уравнения f(x)=a, где функция f(x) описывается заданной формулой, х – искомый параметр, а – требуемый результат. Для использования “подбора параметра” требуется, чтобы содержимое ячейки, в которой нужно получить желаемый результат, являлось формулой, ссылающейся на другие ячейки листа. Такая ячейка, содержащая формулу, называется целевой ячейкой, а ячейка, на которую ссылается формула - изменяемая ячейка. 2. Диспетчер сценариев Диспетчер сценариев является инструментом анализа "что-если" и предназначен для подстановки в одни и те же ячейки рабочего листа различных числовых значений. Это дает возможность, не создавая дополнительные рабочие листы проанализировать результаты, полученные на основе различных наборов данных. 3. Таблица данных Таблица данных является инструментом анализа "что-если" и реализует задачи, обратные задачам подбора параметра. Суть решаемых этим методом задач в следующем: имеется формула, зависящая от некоторых переменных. Задача состоит в определении за одну операцию результатов формулы при различных значениях этих переменных. Таблица данных это способ быстрого вычисления нескольких версий в рамках одной операции, а также способ просмотра и сравнения результатов всех различных вариантов на одном листе. 4. Поиск решения Поиск решения позволяет найти оптимальное или заданное значение ячейки путем подбора значений нескольких ячеек, удовлетворив несколько граничных условий. При этом можно наложить ограничения на любые ячейки, в том числе и на изменяемые ячейки. Математическая суть метода состоит в нахождении оптимального решения уравнения с несколькими переменными и несколькими ограничениями. Вопрос 2 9.2.Прогнозирование с помощью команды «Подбор параметра» Для решения задачи методом подбора параметра надо выполнить следующие действия: Выделить ячейку, содержащую формулу, для которой нужно найти определенное решение и вкладка Данные – группа Работа с данными – кнопка Анализ «что – если…» - выбрать пункт Подбор параметра В поле «Установить в ячейке» ввести ссылку на ячейку, содержащую формулу (по умолчанию в это поле вводится адрес текущей ячейки) В поле «Значение» ввести значение, которое нужно получить по заданной формуле. В поле «Изменяя значение ячейки» ввести ссылку на ячейку, содержащую значение изменяемого параметра. Технология применения инструмента Подбор параметра рассматривается на практических занятиях. Вопрос 3 9.3.Использование команды Поиск решения для решения оптимизационных задач управления Процедура поиска решения запускается так: вкладка Данные – группа Анализ – Поиск решения Для использования Поиска решения нужно определить целевую ячейку и изменяемые ячейки и знать Ограничения. Целевая ячейка - это ячейка содержащая формулу, для которой нужно найти максимальное, минимальное или заданное значение. Изменяемые ячейки – это ячейки, от которых зависит значение целевой ячейки. Ограничения - это условия, накладываемые на некоторые ячейки. Ограничения могут быть наложены на любые ячейки таблицы, включая целевую и изменяемые ячейки. Чтобы сделать формулы более понятными рекомендуется присвоить имена ячейкам, которые используются в формулах, Создание, изменение и удаление имен: вкладка Формулы, группа Определенные имена, кнопка Диспетчер имен, кнопка Создать (или пункт Присвоить имя, команда Присвоить имя), диалоговое окно «Создание имени». Постановка экономической задачи и технология решения с помощью команды Поиск решения Задача. Предприятие выпускает два вида продукции: тумба и стол журнальный. Необходимо найти такой вариант производственной программы, чтобы прибыль от реализации продукции предприятия была максимальной. При этом известны: нормативный расход производственных мощностей на изготовление продукции имеющиеся в наличии объемы производственных мощностей на предприятии нормативные затраты сырья на изготовление продукции имеющиеся в наличии объемы сырья объемы заключенных договоров и объемы по прогнозу сбыта Изменяемые ячейки – это объемы продаж тумб и столов. Целевая ячейка - это прибыль, вычисляемая по формуле: =тумба*(B6-D6)+стол*(B7-D7) При решении задачи необходимо учесть следующие ограничения: изменяемые ячейки E6, E7- целое Ограничения на выпуск: объем продаж каждого вида продукции (т.е. изменяемы ячейки), должен быть больше или равен объему по заключенным договорам и меньше или равен объему по прогнозу сбыта, т.е. B11<=E6тумба<=С11, B12<=E7стол журнальный<=С12 Ограничения на сырье и материалы: расход сырья и материалов не должен превышать объема, имеющегося в наличии (тумба*B17+стол*C17)<=D17 Ограничения на производственные мощности: объем используемых производственных мощностей не должен превышать имеющегося в наличии фонда времени работы оборудования (тумба*E11+стол*E12)<=F11 Выполнение. 1.Ввод исходных данных: Для наглядности изменяемым ячейкам присвоить имена – «Тумба » для ячейки Е6, «Стол» для ячейки Е7. Исходные данные для задачи Поиск решения (режим чисел)  Исходные данные для задачи Поиск решения (режим формул)  2.Ввод целевой функции в ячейку В19: =тумба*(B6-D6)+стол*(B7-D7) (вычисляет прибыль) 3. Ввод формул для вычисления ограничений: ячейку В23 введите формулу для вычисления объема используемых производственных мощностей: =(тумба*E11+стол*E12)<=F11 ячейку В24 введите формулу для вычисления расхода сырья и материалов: =(тумба*В17+стол*С17)<=D17 4.Установите курсор в ячейку с целевой функцией В19 и вкладка Данные, группа Анализ, кнопка Поиск решения. Появится диалоговое окно «Поиск решения»:  5. В поле “Установить целевую ячейку” указать ссылку на ячейку, вычисляющую прибыль $В$19. 6. В поле “Равной” установить флажок “максимальному значению” Примечание: если требуется получить конкретное значение прибыли, то нужно установить флажок “значению” и ввести нужное число. 7. В поле “Изменяя ячейки” ввести ссылки на изменяемые ячейки. В нашем примере это ссылки $Е$6:$Е$7. 8.Чтобы заполнить область Ограничения нажмите кнопку “Добавить”. Появится диалоговое окно “Добавление ограничения”:  Введем следующие ограничения: В области Ссылка на ячейку ввести $E$6, затем из списка выбрать “цел”, в области Ограничения появится “ целое ”, нажать кнопку добавить. Аналогичным образом введите ограничение для ячейки E7. Затем введите ограничения: тумба>= $B$11 тумба<=$C$11 стол>=$B$12 стол<=$С$12 $B$23<=$F$11 на сырьевые ресурсы, $B$24<=$D$17 на производственные ресурсы Изменить или удалить ограничения можно с помощью кнопок «Изменить» и «Удалить». 9.После ввода всех ограничений для запуска Поиска решения нажмите кнопкуВыполнить. На экран будет выведено окно “Результаты поиска решения:  10. Установите переключатель «Сохранить найденное решение» и выберите «Тип отчета» - Результаты. Создание отчетов по результатам поиска решения Поиск решения позволяет создать три типа отчетов найденного решения - Отчет по результатам, Устойчивость, Пределы. Отчеты автоматически создаются на новых листах РК. Если нужно создать два или три отчета, то они выделяются с помощью клавиши CTRL. Вопрос 4 9.4.Применение таблицы данных для решения задач прогнозирования. Виды таблиц данных Таблица данных это инструмент анализа, позволяющий на одном листе просмотреть и сравнить результаты всех различных вариантов значений формулы. На листе Excel таблица данных - это диапазон ячеек, показывающий, как изменение определенных значений в формулах влияет на результаты этих формул. Таблица данных может использоваться в 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. Батаршина Р.Р. Информационные технологии создания систем под-держки принятия решений и методы прогнозирования: методическая разработ-ка / Батаршина Р.Р., Степанова Ю.В., Фатыхова Л.Г. – Казань: КГФЭИ, 2011. – 57 с. 3. Информатика и информационные технологии: учебное пособие / Под ред. Романовой Ю.Д. - М.: Изд-во Эксмо, 2009. – с. 329-335, 361-366. 4. Лавренов С.М. Excel: Сборник примеров и задач / Лавренов С.М. – М.: Финансы и статистика, 2006. – Глава 11. Тема 10 (2 часа). Технология использования баз данных для создания информационных систем 10.1. База данных – основа автоматизированных информационных систем Структурные элементы базы данных 10.2. Основных модели баз данных: иерархическая, сетевая, реляционная 10.3 Этапы проектирование баз данных. Логическое и физическое проектирование 10.4. Понятие СУБД Access. Объекты СУБД Access – таблицы, запросы, формы, отчеты, макросы, модули 10.5. Постановка экономической задачи для проектирования АИС средствами СУБД Access 10.1.База данных – основа автоматизированных информационных систем Структурные элементы базы данных Банк данных — это система специально образом организованных данных (баз данных), а также программных, технических, языковых, организационно-методических средств, предназначенных для обеспечения централизованного накопления и коллективного использования данных. Банк данных включает в себя два основных компонента: базу данных; систему управления базой данных. База данных – это совокупность взаимосвязанных, хранящихся вместе данных, при этом данные хранятся независимо от использующих их программ. Система управления СУБД обеспечивает организацию ввода обработки и хранения данных. Система управления базой данных (СУБД) – это программа, с помощью которой реализуется централизованное управление данными, хранящимися в базе данных, а также обеспечивается поиск, корректировка данных, формирование ответов на запросы. Структурные элементы банка данных: Словарь данных Вычислительная система Администратор базы данных Обслуживающий персонал 10.2. Основных модели баз данных: иерархическая, сетевая, реляционная Модель данных – это совокупность структур данных и операций их обработки. С помощью модели данных можно легко отобразить структуру объектов и связи, установленные между ними. Существует множество различных моделей данных. К числу важнейших относятся следующие три модели: иерархическая, сетевая, реляционная. Иерархическая модель данных представляет собой совокупность связанных элементов, образующих иерархическую (древовидную) структуру. Примером просто иерархического представления может служить организационная структура высшего учебного заведения: институт – факультет – курс – группа. Пример построения иерархической структуры приведен на рис. 10.1.
Рис. 10.1.Пример построения иерархической модели данных Сетевая модель данных. В основе этой модели лежат те же понятия, что и в основе иерархической модели данных – узел, уровень и связь. Однако существенным отличием сетевой модели данных является то, что узел может быть связан с любым другим узлом, в том числе лежащим на уровне, не связанном непосредственно с уровнем исходного узла. Простейшим примером сетевой модели данных может служить база данных, в которой хранится информация о студентах вуза, при этом элемент «Студент» может иметь не один, а два исходных элемента: «Студент» – «Номер группы» и «Студент» – «Комната в общежитии»». Взаимосвязь между объектами сетевой структуры приведена на рис.10.2.
Рис. 10.2.Пример построения сетевой модели данных Реляционная модель данных получила название от английского термина relation – отношение. Реляционная модель данных позволяет представить любой набор данных в виде двумерной таблицы, рис.10.3. Простейшая реляционная база данных может состоять из единственной таблицы, в которой будут храниться все необходимые данные. Однако на практике реляционная база данных обычно состоит из множества таблиц, связанных по определенным критериям.
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
