Тема 3. Использование пакетов прикладных программ при выполнении индивидуальной и групповой работы сотрудников в организации РОССИЙСКАЯ АКАДЕМИЯ ГОСУДАРСТВЕННОЙ СЛУЖБЫ ПРИ ПРЕЗИДЕНТЕ РОССИЙСКОЙ ФЕДЕРАЦИИ Дисциплина: « Информационные технологии в государственном и муниципальном управлении » Доктор технических наук, профессор Павлов Алексей Николаевич

Виды задач, решаемые в организациях 1. Задачи ориентированные на предоставление информации внешним по отношению к организации пользователям инвесторам, налоговым службам и т. д. В данном случае для анализа используются показатели, получаемые на основе данных стандартной бухгалтерской и статистической отчетности, а также других источников информации. 2. Задачи анализа, предназначенные для выработки стратегических управленческих решений развития. В этом случае информационная база должна быть шире, но в рамках достаточно высоко агрегированных показателей, характеризующих основные тенденции развития предприятия или организации. 3. Задачи анализа ориентированные на выработку тактических решений. Его информационная база чрезвычайно широка и требует охвата большого количества частных высоко детализированных показателей, характеризующих различные стороны функционирования объекта управления. Эффективность принятия управленческих решений в условиях функционирования информационных технологий в организациях различного типа обусловлена использованием разнообразных инструментов анализа финансово-хозяйственной деятельности предприятий. Можно выделить четыре круга задач, решаемые организацией. 4. Задачи оперативного управления экономическим объектом в соответствии с функциональными подсистемами экономического объекта. Для решения этих задач используется текущая оперативная информация о состоянии экономического объекта и внешней среды.

Этапы анализа исходных данных для подготовки управленческого решения Сбор данных о процессе Использование модели для составления прогноза Разработка предложений Мониторинг объекта по определенным параметрам Изменение условий в модели, оценка результатов прогноза Поддержка функций анализа данных осуществляется с использованием технологии сбора данных от управляемых объектов по сети, их группировки и обработки на модели, с помощью которой разыгрываются сценарии развития ситуации в будущем для того, чтобы принять окончательное решение

Задача о планировании ресурсов в регионе Ситуация: В регионе рассматривается вопрос о создании запасов определенных ресурсов для обеспечения деятельности предприятий, которые занимаются выпуском продукции, необходимой для обеспечения нужд населения. Каждое предприятие заинтересовано получить как можно больше ресурсов, которые будут оплачены из бюджета. По этой причине предприятия дают заявки с завышенными требованиями. Руководство региона понимает, что приобретать необходимые ресурсы надо, но бюджет ограничен, и поэтому необходимо объективно подойти к запросам пред приятий. Фактически, руководству региона необходимо решить несколько задач, которые связаны между собой, а исходными данными являются нормы и показатели деятельности предприятий при выпуске определенной номенклатуры товаров. Необходимо определить и проанализировать: 1) суммарные расходы на конечную продукцию, объем выпуска которой задается производственной программой (планом); 2) каковы коэффициенты прямых затрат на единицу конечной продукции; 3) каков будет расход используемых компонент (сырья, материалов, топлива, трудовых ресурсов) по предприятиям; 4) стоимость затрат на выполнение производственной программы; 5) каковы производственные затраты на единицу конечной продукции. Решение

Стохастический анализа развития ситуации методом цепей Маркова A B C A B C t Цепи Маркова (марковский процесс) основаны на предположении, что вероятность очередного события зависит от результатов предшествующего события и совершенно не зависит от событий более ранних. Вводится предположение, что имеется замкнутая система, внутри которой происходят некоторые события с объектами В систему входят объекты, которые обладают одинаковыми свойствами, но параметры (мощности) у них различаются между собой. Каждый объект является центром обслуживания нескольких однотипных элементов. ПРИМЕР

Возможности табличного процессора Excel для решения аналитических задач Разработчики Excel предлагают несколько возможных способов решения и прогнозирования той или иной задачи, которые наиболее часто встречаются в жизни управленцев при проведении анализа данных. Методы, заложенные в решение задач на Excel получили название «Что – если?». При попытке ответить на такой вопрос, потребовалось инструменты анализа разделить на три группы: Подбор параметров – методы позволяют найти значение функции, которые позволяют задать формулу, описывающую зависимость связанных между собой величин, для получения нужного результата. Таблицы подстановок дают возможность вычисления, просмотра и сравнения результатов, полученных при различных вариантах значения аргумента. Поиск решения осуществляет вычисление оптимального значения целевой функции при заданных ограничениях на определенные параметры, которые используются для описания уравнений задачи.

Надстройка - Поиск решения Постановка задачи: Необходимо увеличить прибыль предприятия за счет оптимальной загрузки производственных мощностей. Этапы решения поставленной задачи: 1.Провести обследование производственных подразделений. 2.Осуществить анализ возможностей предприятия и выявить ограничения, которые влияют на решение задачи. 3.Рассмотреть альтернативные варианты решения проблемы. 4.Построить модель решения задачи. 5.Провести расчеты. 6.Предложить наиболее подходящий вариант решения проблемы. Решения, приводящие к получению результата, который по тем или иным показателям предпочтительнее других, называются оптимальными.

Схема технологического процесса Поставщик деталей для изделия И1 Детали для изделия И2 Цех подготовки заготовок Цех фрезеровки заготовок Цех сверления заготовок Цех сборки заготовок Участок отладки Производственные подразделения, участвующие в выпуске изделий И1 и И2 Изделие И2Изделие И1 Этап – обследование предприятия Одновременно можно выполнять работы по созданию изделия 1 и изделия 2

Формализация задачи ФСРОП Граф технологической схемы выпуска различных видов изделий И2И1 Предположим, что на рабочих местах можно одновременно вести обработку деталей для изделий И1 и И2. Требуется найти такое соотношение загрузки рабочих мест производством изделий И1 и И2, которое позволит получить максимальную прибыль при реализации продукции. Затраты на производство (Тыс.руб.) Доход от реализации (Тыс.руб.) Прибыль (Тыс.руб.) Изделие И1 0,62,01,4 Изделие И2 1,60,41,2 Х1Х1 Х2Х2

Анализ возможных вариантов решения задачи Цех подготовки заготовок (П) Цех фрезеровки деталей (Ф) Цех сверления деталей (С) Цех сборки изделий (Р) Участок отладки (О) Лимит рабочего времени на месяц по участкам (час) Изделие типа И Нормы времени на обработку (час/шт.) Изделие типа И2 24,540,750 Рассмотрим варианты, предположим, что можно изготавливать только один вид изделия либо И1, либо И2 Сколько можно выпустить Реальный выпуск и потери Нет -100% % % 55 -8,3% 55 0% Сколько можно выпустить Реальный выпуск и потери 65 0% 65 -7,1% ,5% ,3% Нет -100% Затраты на производство (Тыс.руб.) Доход от реализации (Тыс.руб.) Прибыль (Тыс.руб.) Изделие И1 0,62,01,4 Изделие И2 1,60,41,2 Вариант 1 – выпускаем только Изделие типа И1, прибыль 1,4*55=77 тыс.руб. Вариант 2 – выпускаем только Изделие типа И2, прибыль 1,2*65=78 тыс.руб.

Математическая постановка задачи Обозначим через Х 1, Х 2 количество единиц продукции И1 и И2, производство которых планируется. Целевая функция – прибыль, приносимая производством (Х 1 и Х 2 ), будет равна Z = 1,4*X 1 + 1,2*X 2, где С 1 = 1,4 С 2 = 1,2 – прибыль от реализации единицы продукции вида И1 и И2 Введем ограничения для решения задачи: 1. Количество единиц продукции, может быть только положительным числом. X 1 0, X Оборудование не должно работать свыше выделенного лимита времени 0*X 1 + 2*X *X 1 + 4,5*X *X 1 + 4*X *X 1 + 0,75*X *X 1 + 0*X Цех подготовки Цех фрезерования Цех сверления Цех сборки Участок отладки Таким образом, задача сводится к нахождению неотрицательных значениях переменных Х 1 и Х 2, чтобы они удовлетворяли ограничениям – неравенствам, максимизируя линейную функцию этих переменных Z = C i *X i max

Графическое отображение решения оптимизационной задачи X 1 =>0 X 2 => X 1 =>0, X 2 =>0 0*X 1 + 2*X 2

Выполнение работ в Excel при решении оптимизационных задач 1. Установить надстройку Excel – Поиск решения Меню Сервис Надстройки 2. Ввести исходные данные для решаемой задачи 3. Установить зависимые ячейки и ввести формулы для вычислений 4. Вызвать программу Поиск решения и ввести ограничения для модели

Надстройка - Подбор параметров Исследователь часто задает вопрос – Что надо сделать для того, чтобы получить нужный результат? Получить ответ позволяет средство Excel (программа, которая вызывается командой) Подбор параметров. Меню Сервис Подбор параметров Условиями применения данного средства являются: 1.Известен требуемый ответ; 2.Создана формула для вычисления этого ответа; 3.Существует одно входное значение для получения этого ответа. Применять средство Подбор параметров удобно для решения математических задач, например, для нахождения корней уравнений, решения систем уравнений, при решении финансовых задач, например, при оценке условий получения кредита, при решении экономических задач, например при вычислении оптимальных запасов на складе.

Решение экономической задачи на основе средства – Подбор параметров Задача – необходимо определить оптимальный размер партии деталей, которые запускаются в производство при условии, чтобы суммарные затраты на обработку деталей, хранения их на складе, были минимальными. Характерной особенностью задач такого рода является то, что величина денежных затрат на конечную цену изделия зависит от количества деталей, которые находятся в производстве и на складе. На экономические показатели влияют несколько факторов (затраты на хранение и затраты на производство). Склад хранит детали и обеспечивает производство, чем больше деталей на складе, тем выше затраты на их хранение Производство использует детали, которые целесообразно подавать партиями, чем больше партия деталей, тем меньше затрат на переналадку оборудования ? Как найти оптимальное соотношение.

Постановка задачи Обозначим переменной Х – количество деталей в партии, которая подается на производство. Затраты на хранение запасов деталей включает: налог на имущество, затраты на обслуживание склада, заработную плату сотрудников. Как правило, эта величина постоянная, рассчитывается на единицу хранения (деталь). Обозначим эту величину q [руб,шт.]. Следовательно, текущие затраты будут составлять: Zq = ½qx (1/2 – обозначает, что на складе должен быть страховой задел для запуска новой партии деталей в производство) Затраты на производство изделий с полученными деталями складывается из расходов на подготовку и переналадку оборудования – S, расходов на сборку изделия – r. Зная (задавшись) суммарным количеством потребности в деталях на весь цикл производства изделий величиной – D, можно составить уравнение для определения производственных затрат: Zp = DS/x +rD

Решение задачи математическим методом Общая величина затрат на хранение и производство равна: Поскольку нас интересует точка, где функция достигает минимума, то для нахождения решения, необходимо провести следующие действия: 1. Найдем первую производную функции Z по X 2. Приравняем нулю первую производную (в этой точке функция Z достигает минимума) 3. Вычислим значение X

Способы анализа данных, организованных в виде списочных структур Список – способ хранения данных в таблице, совокупность поименованных строк, содержащий связанные данные. Таблица объединяет несколько списков, поэтому таблица может иметь общий заголовок, который относится ко всем спискам, а также наименования строк. Если на листе рабочей книги имеются еще данные, то список должен отделяться от них не менее чем одним пустым столбцом. К числу основных способов анализа данных в списках можно отнести следующие: Фильтрация данных; Получение промежуточных итогов; Создание сводных таблиц; Консолидация данных. Общий заголовок Список Название списка Список без названия

Анализ данных в табличном процессоре Excel Консолидация данных – это способ получения итоговой информации, при котором данные, расположенные в нескольких различных областях, объединяются в соответствии с выбранной функцией обработки. Консолидация [лат. consolidatio, от con (cum) вместе, заодно и solido уплотняю, укрепляю, сращиваю], упрочение, укрепление чего-либо; объединение, сплочение отдельных лиц, групп, организаций для усиления борьбы за общие цели. Консолидация по расположениюКонсолидация по категориям Данные исходных областей расположены в одном и том же порядке и имеют одни и те же заголовки. Например, существует множество одинаковых по структуре таблиц, но с разным наполнением данными. Данные исходных областей не упорядочены, но имеют одни и те же заголовки, т.е. списки имеют разную структуру, но одинаковые заголовки.

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

Порядок проведения консолидации данных 1.Создать таблицу на новом листе или отметить начальную ячейку для построения консолидированной таблицы. 2.Выбрать команду: Меню-Данные-Консолидация. 3. Выбрать диапазоны консолидируемых данных (источники)

Консолидация данных по категориям Данные исходных областей в таблицах не упорядочены, хотя заголовки таблиц имеют одинаковые наименования. Задача 1: необходимо определить суммарную стоимость каждого из наименований всех товаров, поступивших в течение полугодия на оба склада от всех поставщиков (наименование товара). Задача 2: необходимо обосновать финансовый план на первое полугодие с учетом поставок товаров по месяцам (вид товара и реквизиты поставщика). Задача 3: необходимо оценить суммарную стоимость каждого наименования товара, поступившего на оба склада от разных поставщиков (поставщик). Напоминание: при проведении консолидации данных по категориям следует выделять заголовки столбцов, кроме того: 1.Столбец (список), по которому осуществляется объединение, всегда должен быть первым; 2.Все остальные столбцы (списки) должны находиться правее первого; 3.В диалоговом окне консолидация следует отметить подписи заголовков и строк.

Разработка макроса Макрос – это набор инструкций, задающих последовательность действий, которые Microsoft Excel (или другое приложение) выполняет вместо вас. Макросы являются компьютерной программой, которая выполняется только внутри книги. Задача: необходимо очищать содержимое на листе перед тем, как осуществлять консолидацию данных. Порядок действий пользователя: 1.Вызвать макрорекордер МЕНЮ-СЕРВИС- МАКРОС-НАЧАТЬ ЗАПИСЬ 2.Заполнить карточку «Запись макроса» 3.Выполнить последовательно Все необходимые действия. 4. По окончании нажать на Кнопку – остановить запись. Имя макроса можно задать другое Сочетание клавиш

Просмотр и редактирование макроса Макрос создается в виде модуля, который можно редактировать и дополнять новыми командами (инструкциями), в соответствии с правилами программирования на VBA (Visual Basic for Application). Кнопка для просмотра кода макроса на VBA Область на листе Адреса ячеек Команда выделить Команда - очистить

Совместная работа элементов управления и макросов Элемент управления - Кнопка Элемент управления - Поле Режим конструктора Элементы управления Элементы управления – набор программных модулей, которые позволяют изменять свойства графических элементов для визуальной работы с данными. Наиболее типичными элементами управления являются: командные кнопки, текстовые окна, списковые структуры, радио-кнопки и т.п. При разработке интерфейса пользователя в объектно-ориентированном программировании, пользователь располагает визуальный объект (элемент управления) на экранной форме, задает ему необходимые свойства, а затем пишет программу, состоящую из инструкций (операторов), которые выполняют определенные действия. В приложениях Microsoft Office базовым языком для программирования является Visual Basic for Application. Подпрограмма – щелкнуть по кнопке Команда – очистить текстовое окно Функция – запустить модуль «Очистка» Выход из подпрограммы