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

Тема 5. Решение экономических задач в табличном процессоре Excel. Вопросы к экзамену: 1.Какие встречаются типовые задачи при анализе данных? 2.Какими средствами Excel можно воспользоваться, чтобы выбрать набор данных из нескольких таблиц? 3.Как поставить задачу, чтобы провести консолидацию данных в различных таблицах? 4.Какие способы анализа данных можно распространить на списочные структуры? 5.Как автоматизировать работу пользователя при работе со списками? 6.Как используют диаграммы при анализе данных?

Решение практических задач на основе встроенных функций (Частота встречаемости) Частота встречаемости – количество или доля элементов, выделенных по определённому признаку в общей совокупности элементов Задача – разделить игрушки по видам в отдельных хранилищах

Отображение частотного распределения игрушек по видам КуклыСобачки КотыМячи Задача – разделить игрушки по видам в отдельных хранилищах В рассматриваемом случае, необходимо создать шкалу, на которой отмечают наименования групп для включения в них, имеющихся элементов (различных видов игрушек).

ФамилияВозраст Соколова М.П.45 Погорелов П.Н.48 Орехов Н.С.27 Машина И.Я.46 Смирнова К.В.60 Аристов М.Ш.42 Коновалов П.П.22 Новикова С.О.36 Владимиров С.Е.39 Шахнозарова З.П.26 Статистическая обработка данных Предположим, имеется таблица со сведениями о кадровом потенциале предприятия. Необходимо повести анализ распределения сотрудников по неравномерным возрастным диапазонам. 1-й диапазон данных – все, кто младше 35 лет 2-ой диапазон данных - только те, кто младше 45 лет и старше 35 лет 3-й диапазон данных - только те, кто младше 50 лет и старше 45 лет 4-й диапазон данных – все, кто старше 50 лет

Порядок использование функции ЧАСТОТА 5. Отобразить результаты на листе Excel. Чтобы вывести массив данных на лист, не нажимайте на кнопку ОК. Необходимо нажать на кнопки: Ctrl+Shift+Enter

Анализ данных при выполнении логических операций Когда существует таблица с тестовыми и числовыми данными (несколько списков объединены общим названием), тогда появляется задача выбора записей (строк) по определенным условиям. Алгоритм поиска необходимых записей задается в виде формулы, в которую могут входить как арифметические операции, так и логические операции с данными. Для формирования логической операции используют условия типа: равно, больше, меньше или равно и т.п. Последовательность выполнения работ по отбору данных из списков: - Сформировать списки (данные объединяют в одну таблицу) - Разработать алгоритм отбора данных - Создать дополнительный столбец или несколько столбцов в исходной таблице - Ввести функцию для отбора данных Пример

Выполнение логических операций в таблицах Список жителей района ФамилияПол Год рождения Образование Категория работы Участие в работе общественны х организаций Стаж работы в выборных органах Виноградовм1973среднееРабочий Боеваж1969специальноеРабочий3 Авдееваж1967специальноеТорговляКПРФ Вавиловм1959среднееРабочий4 Климентьеваж1968высшееСлужащийНАШИ Смирноваж1956специальноеТорговляНАШИ4 Шлыковаж1963высшееСлужащий2 Федоровм1949среднееРабочийНАШИ1 Постановка задачи: необходимо в списке жителей района найти женщин, которые более 3-х лет являются членами общественной организации НАШИ. Решение задачи состоит из следующих действий: 1. Добавить к таблице еще один столбец с наименованием «Результаты выбора» (рис. 3). 2. Составить логическое выражение для отбора данных из исходной таблицы. 3. В ячейки I5:I12 ввести логическое выражение (функцию): =ЕСЛИ(И(G5="НАШИ";C5="ж"; H5>3);"ПОДХОДИТ";«») 4. Размножить, созданную функцию в ячейке I5 для ячеек, находящихся в столбце ниже неё, например, для ячеек I6:I12. РЕЗУЛЬТАТЫ ВЫБОРА I 5

Блок-схема алгоритма отбора претендентов Критерии отбора: Общ. Орг., Пол, Стаж Начало «НАШИ» «Ж» > 3 Конец Выход Отображение результатов ДАНЕТ ДА =ЕСЛИ(И(G5="НАШИ";C5="ж"; H5>3);"ПОДХОДИТ"; )

Задачи анализа данных с использованием встроенных функций процессора Excel Решение финансовых задач Ситуация: предположим, что предприниматель заключил договор с заказчиком на изготовление определённого вида продукции. Для изготовления продукции предпринимателю необходимо закупить 15 комплектов деталей на другом предприятии по заранее оговоренной цене. Необходимо оплатить закупку деталей, следовательно, придётся обратиться в банк за кредитом. Предположим, что заемщику известны условия, на которых он может получить кредит, тогда: Задача экономиста - оценить величину периодических платежей, которые предпринимателю придется выплачивать банку в погашении кредита. Решение можно получить, используя финансовую функцию ПЛТ - платежи.

Схема выполнения договоров Готовая продукция Комплектующие детали Заёмные средства Потребитель Приобретение комплектующих Договор на изготовление продукции

Представление решения задачи о выпуске продукции с позиции экономиста Срок погашения кредита Сумма кредита для приобретения комплекта деталей, согласно договора с заказчиком План погашения кредита в банке План выпуска продукции Средства, заработанные от реализации продукции

Реализация задачи в Excel 1.Построить таблицу с исходными данными 2.Выбрать стандартную функцию ПЛТ 3.Создать область на листе Excel для выполнения расчётов 4.Провести анализ результатов Пример

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

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

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

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

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

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

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

Методы статистической обработки данных 23,524,52417,515,5 23,551 2, ,20,15 0, Степень согласованности мнений всех экспертов оценивается с помощью коэффициента конкордации ω 1-место 2-место 3-место 4-место (3+4)/2=3,5