В списке или базе данных какого-либо листа Microsoft Excel

Электронные таблицы ЕХСЕL

Сводные таблицы

 

Методические рекомендации для выполнения лабораторной работы

для студентов всех специальностей, направлений подготовки и форм обучения

 

 

 

Ростов-на-Дону

Составители:

к.т.н., доцент О.В. Смирнова
к.т.н., доцент А.Ю. Полуян

УДК 681.3

 

Подготовлено на кафедре «Вычислительные системы и информационная безопасность»

 

Методические рекомендации для выполнения лабораторной работы по теме «Электронные таблицы ЕХСЕL. Сводные таблицы» / ДГТУ, Ростов-на-Дону, 2012, 16 с.

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

Печатается по решению методической комиссии факультета «Энергетика и системы коммуникаций».

 

Цель занятия

Изучение основ работы в среде MS Excel.

Время 4 часа

Учебные вопросы:

1. Загрузка, создание, открытие и сохранение таблиц;

2. Выделение, копирование и редактирование ячеек;

3. Установка связей между таблицами;

4. Использование встроенных функций.

Порядок выполнения:

Изучить теоретическую часть, на основе методических рекомендаций и контекстной помощи MS Excel выполнить и описать порядок выполнения следующих заданий.

1. Понятие о сводных таблицах

Для всестороннего и эффективного анализа данных больших таблиц в Ехсе1 используются сводные таблицы (СТ). Функции работы с СТ относятся к одному из самых мощных инструментов Ехсе1.

Главные достоинства СТ - это представление больших объемов информации в концентрированном и удобном для анализа виде, широкие возможности для группировки данных, а так же возможность получения промежуточных и общих итогов, которые помещаются в таблицу автоматически.

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

СТ являются «трехмерными», т.к. к привычным атрибутам таблиц: строка и столбец, добавляется атрибут – страница (на основе исходной таблицы с данными по 12 месяцам, можно создать СТ, содержащую, например, 13 страниц; «открывая», специальным образом, любую из 12 страниц, можно просмотреть данные какого-то одного месяца, а на 13 странице - суммарные данные за все месяцы сразу).

Мастер сводных таблиц

Для построения и модификации СТ используется Мастер сводных таблиц, вызываемый с помощью пункта меню Данные – Сводная таблица. Мастер предоставляет ряд окон диалога, в которых пользователь, выбирая те или иные предложенные варианты, шаг за шагом создает СТ. Построение СТ выполняется в четыре этапа (шага). Переход от этапа к этапу выполняется кнопкой Шаг в диалоговых окнах Мастера сводных таблиц.

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

В списке или базе данных какого-либо листа Microsoft Excel

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

Во внешнем источнике данных, которым может быть база данных, текстовый файл или любой другой источник, кроме книги MicrosoftExcel.

В нескольких диапазонах консолидации, т.е. в нескольких областях одного или более листов Excel. При этом списки и листы должны иметь одинаковые заголовки строк и столбцов.

В другой сводной таблице, которую можно использовать для создания на ее основе новой СТ. Новая СТ будет связана с теми же исходными данными. При обновлении данных СТ - источника, другая СТ также обновляется. Перед каждым видом источника в диалоговом окне установлен переключатель. Первоначально активизирован наиболее распространенный вариант использования списков при построении СТ. Выбрать другой источник можно, сделав активным соответствующий переключатель. В зависимости от вида источника изменяются последующие этапы работы по созданию СТ.

2.2.2 На втором шаге в диалоговом окне указывается интервал клеток списка или базы данных, которые должны быть сведены.

В общем случае полное имя интервала задается в виде:

имя книги – имя листа – интервал. Если СТ строится в той же книге, где находится исходная таблица, то имя книги указывать не обязательно. Для указания интервала из другой закрытой рабочей книги нажимается кнопка ПРОЛИСТАТЬ, далее, в открывшемся диалоговом окне, выбирается диск, каталог и файл закрытой книги, вводятся имя рабочего листа и интервал клеток.

2.2.3 На третьем шаге в макете таблицы в режиме создания (рис.1.) создается структура СТ и определяются ее функции. Макет представлен в центре окна и состоит из областей: строка, столбец, страница и данные. Справа от макета отображаются все имена полей (заголовки столбцов) в заданном интервале исходной таблицы.

Размещение полей в определенную область макета выполняется путем их «перетаскивания» при нажатой левой кнопки мыши. Чтобы удалить поле из области макета, его перетаскивают за пределы макета. Удаление поля приведет к скрытию в СТ всех зависимых от него величин, но не повлияет на исходные данные. Каждое поле в областях столбец, строка, страница может размещаться только один раз, а в области данные оно может и повториться с разными итоговыми функциями.

Каждая область макета, куда размещаются поля исходной таблицы, имеет свое назначение, определяющее внешний вид СТ и ее функции:

Строка - поля этой области формируют заголовки строк СТ; если таких полей несколько, то они размещаются в макете сверху вниз, обеспечивая группирование данных СТ по иерархии полей, где для каждого элемента внешнего поля, элементы внутреннего поля повторяются (см. пример 1);

Столбец - поля в этой области формируют заголовки столбцов СТ; если таких полей несколько, то они в макете размещаются слева направо, обеспечивая группирование данных СТ по иерархии полей;

Страница - поля в этой области выступают в качестве фильтров и позволяют просматривать «на отдельных страницах» данные СТ, соответствующие разным значениям поля, помещенного в эту область; поле страницы в режиме просмотра располагаются на две строки выше основной части СТ в крайнем левом столбце (рис. 3). В соседней справа клетке выводится (Все) и рядом раскрывающая стрелка для выбора другого элемента поля; в области страница может быть размещено несколько полей, между которыми устанавливается иерархическая связь – сверху вниз;

Данные - обязательно определяемая область для размещения полей, по которым подводятся итоги, согласно выбранной итоговой функции; размещаемые здесь поля могут быть произвольных типов.

Пример 1.

Задание: По исходной таблице «Учет количества деревьев» (Приложения, табл.1) построить СТ, сгруппировав данные по породе, № делянки и возрасту. Подсчитать количество деревьев в указанных группах.

Выполнение: В макете (рис.10.) в область строка размещены два поля: порода дерева и № п.п. делянки, в область столбец - поле Возраст. В области данные: Сумма по полю Количество. Готовая СТ (рис. 2.) выводится на листе рабочей книги Ехсе1 в режиме просмотра, где клетки, содержащие имена полей, выделены темным цветом. На пересечении строк с номерами делянок (которые повторяются для каждой породы деревьев) и столбцов с возрастом выводятся величины, обозначающие общее количество деревьев данного возраста и породы деревьев, произрастающих на данной делянке. По внешнему полю Порода выводятся промежуточные итоги (Дуб всего, Клен всего и т.д.) и общие итоги по строкам и столбцам.

Примечание: Excel автоматически вычисляет промежуточные итоги под каждым элементом внешнего поля строки или столбца при помощи той же функции, которая выбрана для поля данных.

Данная СТ станет более компактной и это позволит отдельно просматривать данные по каждой породе, если переместить поле Порода в область страница.

В полученной СТ (рис. 3.) данные сгруппированы по возрасту, по номеру делянки и просуммированы для всех пород деревьев. Для ввода данных по любой отдельной породе, нужно щелкнуть на раскрывающей стрелке и выбрать другой элемент (например, ель).

Рис.1. Возможный вид макета сводной таблицы

Сумма по полю Количество деревьев, шт	Возраст, лет
Порода	N п.п. делянки									Общий итог
дуб
дуб Всего
ель
ель Всего
клен
клен Всего
липа
липа всего
Общий итог

Рис. 2. Сводная таблица к примеру 1

 

 

Рис. 3. Фрагмент сводной таблицы с полем в области страница

 

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

Для настройки параметров полей используются диалоговые окна двух типов с общим названием «Поле сводной таблицы», элементы которых позволяют:

изменить исходное имя поля, формат числа и др.;

указать тип формируемых итогов по значению поля;

удалить поле из макета и др.

На рис. 4. представлен пример окна для настройки параметров поля данные, в котором кнопка ПАРАМЕТРЫ открывает дополнительное поле списка «Показать данные в виде» (рис.5.) для задания дополнительных вычислений.

Пример 2.

Задание: По данным таблицы “Баланс ресурсов и использования мяса и мясопродуктов” (Приложения, табл. 2) определить: как в процентном соотношении изменился общий объем ресурсов за 1994-1996 г.?

Выполнение: Так как, общий объем ресурсов по каждой области находится в столбце ИТОГ, нужно создать СТ с вычислением суммы по полю ИТОГ для каждого отдельного года и определить процентное отличие полученной суммы годов 1995 и 1996 от 1994. В макете поместить поле Области, в область строка: Годы, в область данные – Сумма по полю, в итог задать дополнительное вычисление: «% отличия от», по полю Годы базовый элемент: 1994 (рис. 5).

 

Рис. 4. Окно “Поле сводной таблицы” для настройки

Поля области данные

 

 

Рис. 5. Фрагмент окна «Поле сводной таблицы», в котором задается дополнительное вычисление «% отличия от»

 

На рис.6. представлена СТ отражающая суммарные значения по полю Итог за отдельно взятые годы. В таблице на рис.6. использовано дополнительное вычисление % отличияполученной суммы годов 1995 и 1996 от 1994 для поля Итог области данные. По данным этой СТ видно, что общий объем ресурсов по всем областям сократился в 1995 году на 6,2%, а в 1996 году на 14,37% по сравнению с 1994г. С помощью раскрывающей стрелки поля области можно просмотреть аналогичные данные по любой, входящей в исходную таблицу области.

2.2.4 На четвертом шаге указывается начальная клетка для вставки СТ и некоторые дополнительные параметры СТ. В поле НАЧАЛЬНАЯ ЯЧЕЙКА указывается координата левого верхнего угла СТ или на текущем листе, или на другом листе текущей рабочей книги или в другой ранее открытой рабочей книге. Если адрес начальной клетки не определен, СТ создается на новом рабочем листе текущей рабочей книги начиная с клетки А1. В этом же окне можно определить нужны ли в СТ общие итоги по столбцам и строкам, а также другие параметры, которые по умолчанию отмечены крестиком, т.е. включены.

 

 

Рис. 6. Сводные таблицы для примера 4

12	Поделиться:

Воспользуйтесь поиском по сайту: