Теоретические основы автоматизированной обработки информации и управления : решение прикладных задач в 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, совпадает со значением Процент. 
Далее он равен сумме значения кумулятивного процента для предыдущей ячейки и процента для соответствующей оценки. 
Затем следует скопировать каждую формулу в соответствующие 
ячейки. Чтобы при копировании формул ссылки на значения Итого 
и Размер выборки не менялись, соответствующие адреса должны 
быть абсолютными.