Министерство образования и науки Российской Федерации
Федеральное агентство по образованию
Государственное учреждение высшего профессионального образования
«Хабаровская государственная академия экономики и права»
Кафедра информационных технологий
Дрига Е.С.
Анализ данных в Microsoft Excel 2003
Практикум по выполнению индивидуальных
лабораторных заданий для студентов 1-го курса
всех специальностей всех форм обучения
Хабаровск 2008
ББК У.в6
Х12
Анализ данных в Microsoft Excel 2003 : практикум по выполнению индивидуальных лабораторных заданий для студентов 1-го курса всех специальностей всех форм обучения / сост. Е. С. Дрига. – Хабаровск : РИЦ ХГАЭП, 2008. – 48 с.
Рецензент Д. В. Тимошенко, кандидат технических наук, доцент кафедры ДВС ТОГУ
Утверждено издательско-библиотечным советом академии в качестве лабораторного практикума для студентов
Елена Сергеевна Дрига Анализ данных в Microsoft Excel 2003
практикум по выполнению индивидуальных лабораторных заданий
для студентов 1-го курса всех специальностей и форм обучения
Редактор Г.С. Одинцова
_______________________________________________________________
Подписано к печати Формат 60х84/16.
Бумага писчая. Офсетная печать. Усл.печ.л. 2,8. Уч.-изд.л. 2,0.
Тираж 300 экз. Заказ №___________________
_______________________________________________________________
680042, г.Хабаровск, ул.Тихоокеанская, 134, ХГАЭП, РИЦ
© Хабаровская государственная академия экономики и права, 2008
Введение
Microsoft Excel – одно из основных приложений, входящих в пакет Microsoft Office, знание которого и умение работать в котором является совершенно необходимым для специалистов, чья профессиональная деятельность в значительной степени связана с обработкой документов, представленными в табличной форме. Поэтому преподавание MS Excel осуществляется для студентов всех специальностей и всех форм обучения в курсе «Информатика».
На основе знаний, полученных при работе со списками, студенты осваивают приемы построения Сводных таблиц, позволяющих, в отличие от получения итогов в списках, представлять итоговые данные в наиболее удобном для последующего анализа виде. При изучении этого материала у студентов формируются навыки моделирования результатов обработки данных, инструментом для этого является Мастер сводных таблиц.
Также студенты изучают механизм консолидации электронных таблиц и их частей, позволяющий объединять таблицы или их части, расположенные как на одном, так и на разных листах одной или нескольких книг.
Кроме индивидуальных лабораторных заданий практикум содержит теоретическую часть и контрольные вопросы, способствующие активной самостоятельной работе студентов при выполнении заданий.
Библиографический список поможет студентам найти ответы на контрольные вопросы.
1. Создание сводных таблиц
Сводная таблица – средство обработки и представления данных в виде интерактивной таблицы на рабочем листе Excel. Сводные таблицы позволяют подытожить большие объемы данных, выбрав подходящий метод вычислений. Меняя местами заголовки строк и столбцов в сводной таблице можно изменять представление данных.
Сводные таблицы помогают анализировать информацию, размещенную в таблицах и списках Excel, а также извлекаемую из внешних источников данных.
Создание сводной таблицы производится с помощью мастера сводных таблиц, который вызывается командой Данные, Сводная таблица. На экране появится окно первого шага мастера сводных таблиц и диаграмм.
Шаг 1 из 3. На этом шаге следует выбрать источник данных из перечисленных в диалоговом окне. Возможные источники и их описания представлены в табл. 1.
Виды источников данных
Таблица 1
|
Название источника данных |
Описание |
|
Список или база данных Microsoft Excel |
Диапазон, расположенный на рабочем листе Excel |
|
Внешний источник данных |
Таблицы, созданные другими приложениями, например MS Access |
|
Несколько диапазонов консолидации |
Несколько диапазонов, расположенных на рабочих листах Excel. Диапазоны должны иметь одинаковую структуру |
|
Другая сводная таблица |
Существующая сводная таблица из активной рабочей книги Excel |
Перед каждым видом источника в диалоговом окне установлен переключатель. Первоначально активизирован наиболее распространенный вариант использования списков при построении сводной таблицы. Выбрать другой источник можно, сделав активным соответствующий переключатель. В зависимости от вида источника изменяются последующие этапы работы по созданию сводной таблицы.
Для перехода к следующему шагу нажать кнопку Далее.
Шаг 2 из 3. В появившемся диалоговом окне следует указать диапазон ячеек, содержащий данные.
В общем случае полное имя диапазона ячеек задается в виде:
[Имя_ книги] Имя_листа! Диапазон ячеек.
Если сводная таблица строится в той же книге, где находится исходная таблица, то имя книги указывать не обязательно.
Для указания диапазона ячеек из другой закрытой рабочей книги нажимается кнопка Обзор, далее, в открывшемся диалоговом окне, выбирается диск, папка и файл закрытой книги, вводятся имя рабочего листа и диапазон ячеек.
Если перед созданием сводной таблицы список был выделен, то в этом случае Excel автоматически предложит нужный диапазон. При нажатии кнопки Далее происходит переход к третьему шагу.
Шаг 3 из 3. Третий и последний шаг мастера сводных таблиц и диаграмм является ключевым. В самом диалоговом окне мастера определяется, где будет расположена сводная таблица. Как будет представлена информация, и какими свойствами будет обладать сводная таблица, определяется с помощью кнопок Макет и Параметры.
При нажатии на кнопку Макет появится диалоговое окно для определения структуры сводной таблицы. В центральной части окна расположены четыре области: Страница, Столбец, Строка и Данные. В правой части окна присутствуют названия полей, которые имеются в указанном источнике данных (рис.1). Конструирование сводной таблицы сводится к перетаскиванию мышью названий полей в различные области.
Рис.1. Диалоговое окно Мастер сводных таблиц и диаграмм – макет
Строка - поля этой области формируют заголовки строк сводной таблицы; если таких полей несколько, то они размещаются в макете сверху вниз, обеспечивая группирование данных сводной таблицы по иерархии полей, где для каждого элемента внешнего поля, элементы внутреннего поля повторяются.
Столбец - поля в этой области формируют заголовки столбцов сводной таблицы; если таких полей несколько, то они в макете размещаются слева направо, обеспечивая группирование данных сводной таблицы по иерархии полей.
Страница – область, которая предназначена для фильтрации данных. Выбор значения осуществляется с помощью раскрывающегося списка, содержащего все различные значения, которые принимает поле, помещенное в область Страница. По умолчанию выбирается значение Все, что означает отсутствие фильтрации.
В области Страница может быть размещено несколько полей, между которыми устанавливается иерархическая связь - сверху вниз.
Данные - обязательно определяемая область для размещения полей, по которым подводятся итоги, согласно выбранной итоговой функции; размещаемые здесь поля могут быть произвольных типов.
По умолчанию итоговой функцией для числовых данных является суммирование. Двойной щелчок мышью по полю в области Данные выводит на экран диалоговое окно Вычисление поля сводной таблицы, в котором можно переименовать поле, изменить формат представления числа, или в списке Операция выбрать функцию, для применения к данному полю (рис. 2). Помещение в область Данные нескольких названий означает выполнение итоговой функции над значениями каждого поля.
Рис.2. Диалоговое окно Вычисление поля сводной таблицы.
После того, как макет таблицы определен, можно указать ее параметры в диалоговом окне, которое появляется при нажатии кнопки Параметры на третьем шаге мастера сводных таблиц и диаграмм.
После нажатия кнопки Готово будет сформирована сводная таблица со стандартным именем.
Если после завершения построения сводной таблицы изменились исходные данные, то в этом случае для обновления данных сводной таблицы следует выполнить команду Данные, Обновить данные.
Для изменения структуры уже построенной сводной таблицы курсор устанавливается в область сводной таблицы, выполняется команда Данные, Сводная таблица, которая вызывает мастера сводных таблиц, шаг 3 из 3.
Редактирование и форматирование сводной таблицы удобно осуществлять с помощью панели инструментов Сводные таблицы.