Информатика. Технология обработки данных. Табличный процессор Microsoft Office Excel

128 Pages·2014·4.812 MB·Russian

by  Андреева Н.Б., Дятков В.С., Короткова Н.Н.

МИНОБРНАУКИ РОССИИ
Федеральное государственное бюджетное образовательное учреждение
высшего профессионального образования
«ПЕНЗЕНСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНОЛОГИЧЕСКИЙ УНИВЕРСИТЕТ»

СИСТЕМА
ОТКРЫТОГО
ОБРАЗОВАНИЯ

Информатика

Технология обработки данных
Табличный процессор Microsoft Office Excel

Учебное пособие

Рекомендовано федеральным государственным бюджетным
образовательным учреждением высшего профессионального образования
«Московский государственный технический университет имени Н.Э. Баумана»
в качестве учебного пособия для студентов высших учебных заведений,
обучающихся по направлению подготовки 230400
«Информационные системы и технологии»

Пенза
ПензГТУ
2014
УДК 681.3

Рекомендовано федеральным государственным бюджетным образова-
тельным учреждением высшего профессионального образования «Московский
государственный технический университет имени Н.Э. Баумана» в качестве
учебного пособия для студентов высших учебных заведений

Андреева, Н.Б.
Информатика. Технология обработки данных. Табличный
процессор Microsoft Office Excel : Учебное пособие / Н.Б. Анд-
реева, В.С. Дятков, Н.Н. Короткова; под ред. А.П. Ремонтова. –
Пенза : Изд-во ПензГТУ, 2014. – 128 с.

Данное учебное пособие является составной частью учебно-
методического комплекса по дисциплине «Информатика». В учебном по-
собии рассматриваются основные технологические приёмы обработки
данных в табличных процессорах на примере табличного процессора
Microsoft Office Excel. Приводятся справочные сведения с элементами
теории по изучаемой теме, даются методические указания по выполне-
нию лабораторных работ в формате рабочей тетради; рассматриваются
основные технологические приемы, необходимые для выполнения лабо-
раторных работ; приводятся индивидуальные контрольные задания, про-
верочные задания для оценки уровня освоения основных дидактических
единиц, а также типовые вопросы из комплекта контрольно-оценочных
материалов для подготовки к промежуточной аттестации в форме компь-
ютерного тестирования.
Данное учебное пособие подготовлено на кафедре «Информацион-
ные компьютерные технологии» Пензенского государственного техноло-
гического университета и предназначено для студентов высших учебных
заведений, обучающихся по направлению подготовки 230400 «Информа-
ционные системы и технологии», а также может быть использовано пре-
подавателями и студентами других направлений подготовки в образова-
тельных учреждениях высшего профессионального образования.
Учебное пособие допущено методическим советом факультета
«Информационные и образовательные технологии» Пензенского государ-
ственного технологического университета для использования в учебном
процессе для студентов очной, очно-заочной и заочной форм обучения.
Коллектив авторов выражает благодарность В.В. Волкову, про-
фессору кафедры «Технология машиностроения», за помощь в подготов-
ке материалов учебного пособия для получения рецензии уполномоченной
организации.

УДК 681.3

© Пензенский государственный технологический университет, 2014
© Н.Б. Андреева, В.С. Дятков, Н.Н. Короткова, 2014
2
Содержание

Предисловие…………………………………………………………... 4
1 Ввод и редактирование данных в Microsoft Office Excel………... 5
1.1 Обработка данных средствами электронных таблиц……….... 6
1.2 Лабораторная работа 1. Ввод и редактирование данных
в Microsoft Office Excel. Рабочая тетрадь………………………… 27
2 Использование формул и функций в табличном
процессоре Microsoft Office Excel…………………………………... 45
2.1 Использование формул и функций различных категорий
при выполнении расчётов в табличном процессоре
Microsoft Office Excel………………………………………………… 46
2.2 Лабораторная работа 2. Использование формул
и функций в табличном процессоре Microsoft Office
Excel. Рабочая тетрадь……………………………………………... 59
3 Построение диаграмм в табличном процессоре
Microsoft Office Excel………………………………………………… 90
3.1 Методика построения и редактирования диаграмм
в табличном процессоре Microsoft Office Excel……………………. 91
3.2 Лабораторная работа 3. Построение диаграмм в табличном
процессоре Microsoft Office Excel. Рабочая тетрадь…………….. 98
Библиографический список………………………………………….. 121
Приложение А. Рекомендации по оформлению отчета…………… 122
Приложение Б. Пример оформления отчета..……………………… 124

3
Предисловие

Дисциплина «Информатика» относится к дисциплинам базовой
части математического и естественно-научного цикла федерального
государственного образовательного стандарта высшего профессио-
нального образования (ФГОС ВПО) направления подготовки 230400
«Информационные системы и технологии». При изучении информа-
тики важная роль отводится освоению профессиональных навыков
пользователя персонального компьютера, владеющего базовыми про-
граммными средствами обработки информации.
Данное учебное пособие является составной частью учебно-
методического комплекса по дисциплине «Информатика». В учебном
пособии рассматриваются основные технологические приёмы обра-
ботки данных в табличных процессорах на примере табличного про-
цессора Microsoft Office Excel: проведение операций с ячейками,
строками и столбцами электронной таблицы; использование автома-
тизации в процессе ввода, редактирования, форматирования данных и
при оформлении таблиц; использование относительных и абсолют-
ных ссылок на ячейки; использование формул и функций различных
категорий при проведении расчетов; построение и редактирование
диаграмм. В учебном пособии приводятся:
справочные сведения с элементами теории по изучаемой теме;
даются методические указания по выполнению лабораторных
работ в формате рабочих тетрадей;
рассматриваются основные технологические приемы, необхо-
димые для выполнения лабораторных работ;
приводятся индивидуальные контрольные задания и провероч-
ные задания для оценки усвоения основных дидактических единиц;
приводятся типовые вопросы из комплекта контрольно-
оценочных материалов для подготовки к промежуточной аттестации
в форме компьютерного тестирования.
Комплект контрольно-оценочных материалов для проведения про-
межуточной аттестации в форме компьютерного тестирования по дис-
циплине «Информатика» апробирован, внедрён в учебный процесс и ис-
пользуется в информационно-образовательной среде вуза (модульная
объектно-ориентированная динамическая учебная среда Moodle).
Изучение данного раздела дисциплины «Информатика» способ-
ствует формированию компетенций, определяемых ФГОС ВПО дан-
ного направления подготовки, необходимых для развития у студентов
готовности решать учебные задачи, а в дальнейшем – профессио-
нальные задачи в области исследования, разработки, внедрения и со-
провождения информационных технологий и систем в условиях ин-
форматизации современного общества.
4
1 Ввод и редактирование данных в Microsoft Office Excel

Цель изучения темы – изучение основных возможностей обра-
ботки данных средствами табличных процессоров на примере таб-
личного процессора Microsoft Office Excel.
Вопросы темы:
1) обработка данных средствами табличных процессоров;
2) основные понятия, ввод и редактирование данных в Microsoft
Office Excel.
Изучив тему, студент должен…
– знать:
основные возможности и особенности обработки данных
средствами электронных таблиц и табличных процессоров;
основные технологические приёмы при проведении операций
с ячейками, строками и столбцами таблицы;
способы автоматизации ввода данных в ячейки;
особенности форматирования данных и оформления таблиц
Microsoft Office Excel;
– уметь:
вводить данные различных форматов;
редактировать и форматировать данные в ячейках рабочего листа;
– владеть:
приёмами обработки данных различных форматов в электрон-
ных документах;
навыками обработки данных в электронных таблицах для ре-
шения задач различных учебных дисциплин;
навыками использования базовых программных сред (элек-
тронных таблиц) для решения задач в различных областях профес-
сиональной деятельности.
При освоении темы необходимо:
изучить теоретический материал; обратить внимание на ос-
новные понятия, термины и определения; рассмотреть возможности и
особенности приложения Microsoft Office Excel;
выполнить лабораторную работу для получения умений по
изучаемой теме; освоить базовые средства и приемы работы с прило-
жением Microsoft Office Excel;
выполнить индивидуальные контрольные задания и провероч-
ные задания для оценки усвоения основных дидактических единиц;
рассмотреть типовые вопросы из комплекта контрольно-
оценочных материалов для подготовки к промежуточной аттестации
в форме компьютерного тестирования.
5
1.1 Обработка данных средствами электронных таблиц

Электронные таблицы и табличные процессоры. Электрон-
ная таблица – компьютерный эквивалент обычной таблицы, со-
стоящей из строк и столбцов, на пересечении которых располагаются
ячейки, содержащие числовую информацию, формулы или текст. На-
пример, в таблицах текстового редактора Microsoft Office Word име-
ются некоторые функции электронных таблиц. Формула задается как
выражение, в котором могут быть использованы:
ссылки на ячейки;
ключевые слова для ссылок на блок ячеек (LEFT – ячейки,
расположенные левее ячейки с формулой; RIGHT – ячейки, располо-
женные правее ячейки с формулой; ABOVE – ячейки, расположенные
выше ячейки с формулой; BELOW (ячейки, расположенные ниже
ячейки с формулой);
функции.
Табличный процессор – комплекс взаимосвязанных программ,
предназначенный для обработки электронных таблиц.
Табличные процессоры представляют собой удобное средство
для проведения целого ряда технических, бухгалтерских и статисти-
ческих расчетов. В каждом пакете имеются сотни встроенных мате-
матических, логических, статистических, финансовых, текстовых и
других функций и алгоритмов обработки данных. Кроме того, име-
ются мощные средства для связи таблиц между собой, создания и ре-
дактирования электронных баз данных. Специальные средства позво-
ляют автоматически получать и распечатывать настраиваемые отчеты
с использованием десятков различных типов таблиц, графиков, диа-
грамм, снабжать их комментариями и графическими иллюстрациями.
Многомерные таблицы позволяют быстро делать выборки в базе дан-
ных по любому критерию.
Основные возможности табличных процессоров: решение рас-
четных задач, проведение вычислений по формулам, заданным поль-
зователем (с использованием встроенных функций различных катего-
рий); решение оптимизационных задач; анализ и моделирование на
основе результатов вычислений; оформление таблиц, отчетов; по-
строение диаграмм требуемого вида; создание и ведение баз данных с
возможностью выбора записей по заданному критерию и сортировки
по любому параметру; перенесение (вставка) в таблицу информации
из документов, созданных в других программных средствах; печать
6
итоговых документов; коллективное использование данных, храня-
щихся в таблицах, распространение и просмотр электронных таблиц
всеми участниками рабочей группы.
Преимущества табличных процессоров:
1) решение задач с помощью электронных таблиц освобождает
от составления подробного алгоритма решения задачи и отладки со-
ответствующей программы с использованием знаний языков про-
граммирования (нужно только определенным образом записать в таб-
лицу исходные данные и математические соотношения, входящие в
модель решения задачи);
2) при использовании однотипных формул нет необходимости
вводить их многократно, можно скопировать формулу в нужные
ячейки (при этом произойдет автоматический пересчет адресов ячеек,
встречающихся в формуле);
3) изменение содержимого любой ячейки приводит к автомати-
ческому пересчету значений всех ячеек таблицы, в которых есть
ссылки на данную ячейку;
4) исходные данные и результаты расчетов можно представить
как в числовом виде, так и с помощью деловой графики, причем из-
менение данных, по которым строились графики, автоматически
обеспечивает изменение графического образа.
В настоящее время широко применяют редакторы электронных
таблиц Super Calc, MS Excel, Lotus, Quattro Pro, SDSS Spreadsheet,
Vista Calc, GS-Calc и др. Из них наибольшую известность приобрели
Lotus 1-2-3 фирмы Lotus Development, Super Calc фирмы Computer
Associates, Multiplan и Excel фирмы Microsoft.
Подробно приемы работы в Microsoft Office Excel рассмотрены
в лабораторном практикуме.
Общие сведения о Microsoft Office Excel. Класс программ,
предназначенных для создания и обработки табличных данных в
электронном виде, называется табличными процессорами или элек-
тронными таблицами.
Microsoft Office Excel (далее – Excel) – стандартная программа
из пакета MS Office, предназначенная для создания и форматирова-
ния электронных табличных документов любой сложности; обладает
мощными вычислительными возможностями (позволяет проводить
расчеты с помощью набора функций различных категорий, выпол-
нять анализ расчетов, проводить поиск и упорядочение данных); об-
ладает средствами деловой графики, имеет возможности ведения баз
7
данных, характеризуется повышенным уровнем безопасности, благо-
даря которому можно предоставлять сотрудникам, клиентам и биз-
нес-партнерам безопасный доступ к конфиденциальной информации.
Запуск Excel и завершение сеанса работы с Excel. Запуск и за-
крытие приложений из пакета MS Office имеют схожие действия.
Для запуска Excel необходимо последовательно выполнить сле-
дующие команды: Пуск Все программы Microsoft Office Mi-
crosoft Office Excel. Для завершения сеанса работы необходимо по-
следовательно выполнить команды на панели быстрого доступа окна
Excel:кнопки Office . .
Основные компоненты окна Excel представлены на рисунке 1.1.

Рисунок 1.1. – Общий вид окна Excel

Основной заменой меню и панелей инструментов служит «лен-
та» (рисунок 1.2). Лента состоит из объектов трех типов: вкладок,
групп и команд. Лента разработана для облегчения доступа к коман-
дам, которые собраны в логические группы, и состоит из вкладок
(Главная, Вставка, Разметка страницы, Формулы, Данные, Рецензи-
8
рование, Вид), ориентированных на выполнение задач, связанных с
определенными целями или объектами. Команды сгруппированы во-
круг основных задач, обычно выполняемых пользователем.
Каждая вкладка, в свою очередь, состоит из нескольких групп
взаимосвязанных элементов управления, разбивающих задачу на ее
составляющие. По сравнению с меню и панелями инструментов
«лента» вмещает значительно больше содержимого – кнопок, кол-
лекций, элементов диалоговых окон и т. д. Кнопки команд в каждой
группе служат для выполнения команд или отображения меню ко-
манд. Доступные дополнительные параметры для каждой группы вы-
зываются кнопкой вызова диалогового окна (кнопка со стрелкой в
правом нижнем углу группы на рисунке 1.3).
Кроме стандартного набора вкладок, которые отображаются на
«ленте» при запуске Excel, имеются вкладки еще двух типов, которые
отображаются в интерфейсе в зависимости от выполняемой задачи и
появляются, когда они нужны для работы: контекстные инстру-
менты и контекстные вкладки. Чтобы увеличить рабочую область,
некоторые вкладки выводятся на экран только по мере необходимо-
сти. Например, вкладка Работа с диаграммами отображается только
при выборе диаграммы.

Рисунок 1.2 – Пользовательский интерфейс «лента»:
1 – вкладка; 2 – группа; 3 – кнопка

9
Рисунок 1.3 – Использование кнопки вызова диалогового окна (1)
для настройки дополнительных параметров группы Шрифт (2)

Справочная система Excel. Excel содержит мощную справоч-
ную систему, которую можно активизировать способами, знакомыми
по работе в ОС Windows и с текстовым редактором Word: нажать
мышью пиктограмму (Справка: Microsoft Office Excel); нажать
клавишу F1 в любой момент сеанса работы с Excel. Всплывающие
подсказки дают мгновенную информацию о любом элементе графи-
ческого интерфейса.
Основные понятия Excel
Рабочая книга. Каждая электронная таблица Excel, хранимая в
файле с расширением .xls, называется рабочей книгой.
Примечание. В приложениях Word, Excel и PowerPoint выпуска
Office 2007 применяются открытые XML-форматы Microsoft Office.
Это означает, что используемые по умолчанию форматы файлов те-
перь основаны на XML (Extensible Markup Language). Такое измене-
ние имеет следующие преимущества: увеличение безопасности;
уменьшение вероятности повреждения файлов; уменьшение размеров
файлов; возможность совместного использования данных в ряде сис-
тем хранения и извлечения. Документы, листы и презентации теперь
по умолчанию сохраняются в новом формате, где последней буквой в
расширении файла становится буква «X», указывающая на XML
формат, например XLSX – в Excel.
10