Теоретические основы автоматизированной обработки информации и управления : решение прикладных задач в MS Excel

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РФ 

ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ АВТОНОМНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ  
ВЫСШЕГО ОБРАЗОВАНИЯ  
«НАЦИОНАЛЬНЫЙ ИССЛЕДОВАТЕЛЬСКИЙ ТЕХНОЛОГИЧЕСКИЙ УНИВЕРСИТЕТ «МИСиС» 

ИНСТИТУТ ИНФОРМАЦИОННЫХ ТЕХНОЛОГИЙ И АВТОМАТИЗИРОВАННЫХ 
СИСТЕМ УПРАВЛЕНИЯ 

 

 
 
 

 

 

 

 
 

 

№ 3068 

Кафедра автоматизированных систем управления

И.В. Баранникова 
Е.С. Могирева 
О.Г. Харахан 

Теоретические основы
автоматизированной обработки 
информации и управления 

Решение прикладных задач в MS Excel 

Лабораторный практикум 

Рекомендовано редакционно-издательским 
советом университета 

Москва 2018 

УДК 004.6 
 
Б24 

Р е ц е н з е н т  
канд. техн. наук, доц. Д.В. Калитин 

Баранникова И.В. 
Б24  
Теоретические основы автоматизированной обработки информации и управления : решение прикладных задач в MS Excel: 
лаб. практикум / И.В. Баранникова, Е.С. Могирева, О.Г. Харахан. – М. : Изд. Дом НИТУ «МИСиС», 2018. – 58 с. 
 

Лабораторный практикум дополняет и обновляет существующие учебные 
методические материалы, определяющие базовый уровень подготовки бакалавров в соответствии с учебной программой дисциплины «Теоретические 
основы автоматизированной обработки информации и управления». 
В работе представлены примеры решения прикладных задач в MS Excel в 
виде инструкций выполнения заданий по лабораторным работам: анализ реакции потребителей на новый продукт, особенности распределения ресурсов, 
планирование численности персонала и затрат на рекламу. Лабораторные работы позволят научиться использовать диалоговое окно «Поиск решения» 
для выполнения задач оптимизации функционирования объектов управления. 
Предназначен для студентов, обучающихся в бакалавриате по направлению подготовки 09.03.01 «Информатика и вычислительная техника». 

УДК 004.6 

 
 И.В. Баранникова, 
Е.С. Могирева, 
О.Г. Харахан, 2018 
 
 НИТУ «МИСиС», 2018 

СОДЕРЖАНИЕ 

Лабораторная работа 1. Анализ реакции потребителей 
на новый продукт ................................................................................... 4 
Лабораторная работа 2. Решение задач бизнес-анализа 
средствами аппарата сводных таблиц, консолидации, 
группировки и формирования итогов .................................................. 10 
Лабораторная работа 3. Управление данными торговой фирмы ...... 19 
Лабораторная работа 4. Задача распределения ресурсов ................... 30 
Лабораторная работа 5. Планирование численности персонала ....... 41 
Лабораторная работа 6. Планирование затрат на рекламу ................ 44 
Лабораторная работа 7. Примеры задач для самостоятельной 
работы ..................................................................................................... 51 
Контрольные вопросы ........................................................................... 56 
Библиографический список .................................................................. 57 
 

Лабораторная работа 1 

АНАЛИЗ РЕАКЦИИ ПОТРЕБИТЕЛЕЙ 
НА НОВЫЙ ПРОДУКТ 

Постановка задачи 
Полученные от респондентов данные (рис. 1.1) должны быть внесены в компьютер в структурированной форме в виде матрицы данных и проанализированы методами статистического анализа. Выделяют пять основных видов статистического анализа: дескриптивный 
анализ, выводной анализ, анализ различий, анализ связей и предсказательный анализ. В основе дескриптивного анализа, используемого 
в решении задач, лежит применение таких статистических мер, как 
средняя величина, мода, стандартное отклонение, размах или амплитуда вариации. 

 

Рис. 1.1. Результаты опроса респондентов 

Вычисление данных величин целесообразно выполнять с помощью встроенных функций MS Excel. 
В двух выборках, состоящих соответственно из 100 мужчин и 
100 женщин, был проведен опрос, с целью оценить качество нового 
вида продукта (например, нового сорта мыла). Оценки давались по 7балльной шкале (от 1 – «Совсем не нравится» до 7 – «Очень нравится»). Полученные оценки представлены на рис. 1.1. 
На основании данных постройте распределение частот для различных оценок качества и вычислите итоговые статистические показатели. Частотное распределение оценок представьте графически для 
обоих сегментов рынка. Перед использованием этих функций для 
выполнения дальнейших расчетов ознакомьтесь со справкой MS Excel по данной теме. 

Инструментарий 
Статистические функции, формула массива. 

Решение задачи 
Полученные результаты следует представить отдельно для мужчин и для женщин (рис. 1.2 и 1.3 соответственно). 

 

Рис. 1.2. Форма для заполнения результатами распределений 

Для выполнения задания необходимо сделать следующее. 
Заполните лист Мужчины, а затем, скопировав и исправив исходные данные, создайте лист Женщины. 

Рис. 1.3. Форма для итоговых статистических показателей 

Введите функцию для вычисления частоты как формулу массива. 
Для этого: 
– выделите вертикально смежные ячейки для результата в столбце 
Частота (всего 8 ячеек – на единицу больше, чем количество карманов значений оценок); 
– введите функцию: =ЧАСТОТА (массив_данных; массив_интервалов),  
 где массив_данных – это блок ячеек, содержащих результаты 
опроса (например, A4:E23); 
 массив_интервалов – это блок ячеек, содержащих оценки от 1 
до 7 (например, G5:G11); 
– в конце нажмите Ctrl + Shift + Enter. 
Далее составьте и введите формулы частотного распределения, %, 
учитывая, что: 

 
Итого = Сумма всех частот для оценок от 1 до 7; 
 
Процент = Частота/Размер выборки; 
 
Значащий процент = Частота/Итого. 

Кумулятивный процент показывает процент респондентов, давших ответ, равный или меньший указанного значения. Кумулятивный процент для оценки, равной 1, совпадает со значением Процент. 
Далее он равен сумме значения кумулятивного процента для предыдущей ячейки и процента для соответствующей оценки. 
Затем следует скопировать каждую формулу в соответствующие 
ячейки. Чтобы при копировании формул ссылки на значения Итого 
и Размер выборки не менялись, соответствующие адреса должны 
быть абсолютными. 

Расчет итоговых статистических показателей осуществляется по 
формулам 
 
Среднее = СРЗНАЧ(A4:E23); 
 
Максимум = МАКС(G5:G11); 
 
Минимум = МИН(G5:G11); 
 
Стандартное отклонение = СТАНДОТКЛОН(A4:E23); 
 
Действительных ответов = Итого; 
 
Мода = МОДА(A4:E23); 
 
Медиана = МЕДИАНА(A4:E23); 
 
Дисперсия = ДИСП(A4:E23); 
 
Недействительных ответов = Размер выборки – Итого. 

Функции вводятся с помощью Мастера функций. Категория – 
статистические. 

 

Рис. 1.4. Результаты расчетов для мужчин 

Основываясь на полученных данных, определите, какой из двух 
сегментов рынка (мужчины (рис. 1.4) или женщины (рис. 1.5)) является наиболее предпочтительным для внедрения нового вида продукта. 
На новом листе Сравнение создайте таблицу (рис. 1.6). 
В ячейке B4 задайте формулу =Мужчины!J5; в ячейке С4 задайте 
формулу =Женщины!J5. 

Рис. 1.5. Результаты расчетов для женщин 

 

Рис. 1.6. Сравнение оценок качества 

Далее скопируйте эти формулы вниз с помощью мыши. На основании данных рис. 1.6 создайте на отдельном листе сравнительную 
диаграмму, выбрав команду Вставка – Вставить гистограмму или 
линейчатую диаграмму – Гистограмма и задав название оси Х – 

Оценка качества продукта. Отформатируйте полученную гистограмму, чтобы она приняла вид, представленный на рис. 1.7.: 

 

Рис. 1.7. Диаграмма оценок качества продукта 

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

Лабораторная работа 2 

РЕШЕНИЕ ЗАДАЧ БИЗНЕС-АНАЛИЗА 
СРЕДСТВАМИ АППАРАТА СВОДНЫХ ТАБЛИЦ, 
КОНСОЛИДАЦИИ, ГРУППИРОВКИ 
И ФОРМИРОВАНИЯ ИТОГОВ 

Постановка задачи 
Имеются данные о сбыте продукции, сведенные в таблицу текущих продаж (рис. 2.1). Создайте сводные и выборочные таблицы, 
позволяющие анализировать итоговую информацию о сбыте по видам продукции, регионам, продавцам, годам. 

 

Рис. 2.1. Таблица исходных данных 

Продолжите заполнение таблицы (см. рис. 2.1) на период 2017 г., 
скопировав все данные и изменив некоторые так, чтобы в ней было 
4 вида продукции, 4 продавца и 4 региона и, как минимум, два года, 
например, 2015 г. и 2016 г. Постройте диаграммы. Определите тенденцию сбыта продукции по ее видам и регионам. Постройте столбчатую диаграмму с линией тренда, которая показывает тенденцию 
изменения сбыта продукции. 

Инструментарий 
Сводные таблицы, сортировка данных в таблицах (пользовательский порядок сортировки), группировка данных в сводных таблицах, 
подведение итогов (Данные – Итоги), фильтрации данных (Автофильтр, расширенный фильтр), консолидация данных. Функция 
Тенденция. 

Решение задачи 
1. Составьте таблицу заданного вида. Дайте рабочему листу 
название Исходные данные. Сохраните файл под именем lab2.xls. 
2. Упорядочите данные исходной таблицы по годам, а в рамках 
каждого года – по месяцам, использовав пользовательский порядок 
сортировки. 
3. Создайте сводную таблицу (см. рис. 2.1), задав в ней в качестве 
поля строки – Год, поля столбца – Продукция, поля данных – Сбыт. 
Используйте для вычисления сбыта функцию Сумм, задаваемую по 
умолчанию (автоматически). Переименуйте созданный рабочий лист 
в Год – Продукция. 
Поле строки определяет название строк, поле столбца – название 
столбцов, поле данных (количественно) суммарные данные таблицы. 
В сводных таблицах может использоваться еще поле страницы, 
определяющее признак группировки, в разрезе которого на странице 
будут выводиться итоги. 
Каждую сводную таблицу начинайте делать, открыв рабочий лист 
Исходные данные с выделенной таблицей. Каждую таблицу создавайте на отдельном листе, переименовав ее в соответствии с содержанием. 
Для создания сводной таблицы используется Мастер. Для этого надо: 
– выделить исходную таблицу с названиями строк и столбцов; 
– на панели инструментов выбрать Вставка – Сводная таблица. 
Назначить диапазон и поместить отчет на новый лист, нажать ОK; 
– создать макет сводной таблицы, перетащив мышью поле Год в 
область строки, поле Продукция – в область столбца, поле Сбыт – в 
область данных. В области Данные для цифровых данных автоматически задается функция СУММ, для текстовых – СЧЕТ. Можно задать другие функции для вычисления цифровых данных. (Для выбора функций Макс и Мин сделайте двойной щелчок на поле Сумма 
по сбыту в области данных.) 
На экране появится отформатированная сводная таблица, составленная по годам и видам продукции и содержащая итоговые данные 
по сбыту, выводимые автоматически.