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

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

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

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

 

 
 
 

 

 

 

 
 

 

№ 3069 

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

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

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

Специальные функции MS Excel 

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

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

Москва 2018 

УДК 004.6 
 
Б24 

Р е ц е н з е н т  
канд. техн. наук, доц. А.О. Аристов 

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

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

УДК 004.6 

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

СОДЕРЖАНИЕ 

Лабораторная работа 1. Работа с формулами ...................................... 4 
Лабораторная работа 2. Работа с автоматическими фильтрами ........ 10 
Лабораторная работа 3. Работа с расширенными фильтрами ............ 15 
Лабораторная работа 4. Консолидация таблиц .................................... 21 
Лабораторная работа 5. Исследование данных с помощью 
сводных таблиц ....................................................................................... 24 
Лабораторная работа 6. Исследование данных с помощью 
расширенного инструментария сводных таблиц ................................. 29 
Лабораторная работа 7. Использование инструментария 
условного форматирования ................................................................... 39 
Лабораторная работа 8. Промежуточные итоги .................................. 50 
Лабораторная работа 9. Форматирование диаграмм ........................... 54 
Контрольные вопросы ............................................................................ 59 
Библиографический список ................................................................... 60 
 

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

РАБОТА С ФОРМУЛАМИ 

Формулы – это выражение, начинающееся со знака равенства и 
состоящее из числовых величин, адресов ячеек, функций, имен, которые соединены знаками арифметических операций. К знакам 
арифметических операций, которые используются в Excel, относятся: 
сложение, вычитание, умножение, деление, возведение в степень. 
Результатом выполнения формулы является значение, которое выводится в ячейке, а сама формула отображается в строке формул. Если 
значения в ячейках, на которые есть ссылки в формулах, изменяются, 
то результат изменится автоматически. 
После того как формула введена в ячейку, ее можно перенести, 
скопировать или распространить на блок ячеек. При копировании 
формул можно управлять изменением адресов ячеек или ссылок. Если перед всеми атрибутами адреса ячейки поставить символ “$” (например, $A$1), то это будет абсолютная ссылка, которая при копировании формулы не изменится. Изменятся только те атрибуты адреса 
ячейки, перед которыми не стоит символ “$”, т.е. относительные 
ссылки. Для быстрой установки символов “$” в ссылке ее необходимо выделить в формуле и нажать клавишу F4. 
Для перемещения формулы подведите указатель мыши к тому 
месту границы ячейки, где изображение указателя мыши изменяется 
с белого крестика на белую стрелку. Затем нажмите левую кнопку 
мыши и, удерживая ее, перемещайте ячейку в нужное место таблицы. Завершив перемещение, отпустите кнопку мыши.  
Для копирования формулы подведите указатель мыши к тому 
месту границы ячейки или блока, где изображение указателя изменяется с белого крестика на белую стрелку. Затем нажмите клавишу 
Ctrl и левую кнопку мыши и перемещайте ячейку в нужное место 
таблицы. Для завершения копирования отпустите кнопку мыши и 
клавишу Ctrl. Если в записи формулы есть относительные адреса 
ячеек, при копировании формулы они изменятся. 
Функции Excel – это специальные, заранее созданные формулы 
для сложных вычислений, в которые пользователь должен ввести 
только аргументы. 
Функции состоят из двух частей: имени функции и одного или нескольких аргументов. Имя функции описывает операцию, которую 
эта функция выполняет, например СУММ. 

Аргументы функции Excel задают значения или ячейки, используемые функцией, они всегда заключены в круглые скобки. Открывающая скобка ставится без пробела сразу после имени функции. 
Например, в формуле =СУММ(A2;A9), СУММ – это имя функции, а 
A2 и A9 - ее аргументы. 
Эта формула суммирует числа в ячейках A2 и A9. Даже если 
функция не имеет аргументов, она все равно должна содержать круглые скобки, например функция ПИ(). При использовании в функции 
нескольких аргументов они отделяются один от другого точкой с запятой. В функции можно использовать до 30 аргументов. 

Ввод функций в рабочем листе 
Можно вводить функции в рабочем листе прямо с клавиатуры или с 
помощью команды Функция меню Вставка. Если вы выделите ячейку и 
выберете команду Вставка/Функция, Excel выведет окно диалога Мастер функций – шаг 1 из 2. Открыть это окно можно также с помощью 
кнопки Вставка функции на строке ввода формул (рис. 1.1). 

 

Рис. 1.1. Мастер функций 

Пример 1. Рассчитать результаты тестирования студентов 
Открыть файл Exсel Счет1, Лист Задание1. 

Для выполнения задания необходимо вставить формулы для вычисления значений полей Сумма баллов и Оценка. 
Вводим формулу для расчета суммы баллов в ячейку I9: 

 
Сумма баллов = Тест1*Вес1+Тест2*Вес2+Тест3*Вес3. 

Для расчета используем абсолютные ссылки $Столбец$Строка 
на весовые коэффициенты для ячеек В4, В5, В6. 

 
Конечная формула =F9*$B$4+G9*$B$5+H9*$B$6. 

Применяем формулу для диапазона ячеек I9:I30. Для этого нажимаем на правый нижний угол ячейки I9 и растягиваем на нужный 
диапазон значений I9:I30 (рис. 1.2). 

 

Рис. 1.2. Применение формулы 

Вводим формулу для расчета графы Оценка в ячейку J9:  

 
Оценка=целая часть от (5*(Сумма баллов/Максимальное 
 
значение сумм баллов)). 

В формуле используем функцию ЦЕЛОЕ(число), округляющую 
число до ближайшего меньшего целого. 
Также используем функцию МАКС (число1; [число2]; …), которая 
возвращает наибольшее значение из набора значений: 

 
Конечная формула = ЦЕЛОЕ (5*($I9/(МАКС($I$9:$I$30)))). 

Применяем формулу для диапазона ячеек J9:J30. Для этого нажимаем на правый нижний угол ячейки J9 и растягиваем на нужный 
диапазон значений J9:J30 (рис. 1.3). 

Рис. 1.3. Применение формулы для диапозона ячеек J9:J30 

Пример 2. Рассчитать накопления на банковских вкладах по 
формуле сложных процентов 
Для выполнения задания необходимо ввести в строку формул формулу для вычисления значений полей Длительности хранения в днях, 
используя функцию ТДАТА(), которая возвращает текущую дату. 
При вычислении формулы используем числовой формат: 

 
Конечная формула = ТДАТА() - $C6. 

Применяем формулу для диапазона ячеек E6:E13. Для этого нажимаем на правый нижний угол ячейки E6 и растягиваем на нужный 
диапазон значений E6:E13 (рис. 1.4). 

 

Рис. 1.4. Применение формулы для диапазона ячеек E6:E13 

Вычисляем графу Текущий размер счета, который рассчитывается по формуле сложных процентов:  

 
Сумма вклада*(1+процент) длительность хранения в годах.