Лабораторная работа № 6 Сводные таблицы Теоретический раздел

Понятие о сводных таблицах Для всестороннего и эффективного анализа данных больших таблиц в Excel

используются так называемые сводные таблицы. Функции работы со сводными

таблицами относятся к одному из самых мощных инструментов Excel.

Главные достоинства сводных таблиц - это представление больших объемов

информации в концентрированном и удобном для анализа виде, широкие возможности

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

итогов, которые помещаются в таблицу автоматически.

Применять инструмент сводные таблицы рекомендуется для больших таблиц, где

имеются различные повторения значений в столбцах и (или) строках.

Создание сводной таблицы Перед тем как создать сводную таблицу необходимо сначала задать данные для этой

таблицы, выделив часть книги, страницы, а в случае если это база данных или же список,

то необходимо выделить ячейку списка или базы данных.

Для случая, когда сводную страницу создают из одного документа (листа Microsoft

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

по кнопке Сводная таблица на вкладке Вставка.

После нажатия на эту кнопку пользователю откроется окно, в котором будет

предложен выбор диапазона и места для создания отчета сводной таблицы.

Диапазон сводной таблицы определен автоматически, т.к. курсор находился в одной

из ячеек таблицы. Выберите создание таблицы на отдельном листе и щелкните OK. Будет

создан новый лист, на котором появится область пустой сводной таблицы.

Поля, находящиеся в списке полей сводной таблице расположенном в правой части

экрана, перетаскиваются при нажатой левой кнопки мыши в область Названия строк,

Названия столбцов и Σ Значения, которые так же располагаются в окне Список полей

сводной таблицы в правой части экрана (см. рисунок выше).

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

свое назначение, определяющее внешний вид сводной таблицы и ее функции:

Названия строк - поля этой области формируют заголовки строк сводной таблицы;

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

группирование данных сводной таблицы в порядке размещения полей, где для каждого

элемента внешнего поля, элементы внутреннего поля повторяются;

Названия столбцов - поля в этой области формируют заголовки столбцов сводной

таблицы; если таких полей несколько, то они в таблице размещаются последовательно,

обеспечивая группирование данных сводной таблице в порядке размещения полей;

Фильтр отчета - поля в этой области выступают в качестве фильтров и позволяют

просматривать часть данных удовлетворяющих условию фильтра;

Данные - обязательно определяемая область для размещения полей, по которым

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

могут быть произвольных типов. В поле данных обычно подводятся итоги группы чисел,

хотя текущие данные могут быть и текстовыми. По умолчанию в сводной таблице

подведение итогов текстовых данных производится с помощью итоговой функции «Кол-

во значений», а числовых данных — с помощью итоговой функции «Сумма».

Практический блок Задание 1

Откройте файл Банк.xls.

На вкладке Вставка щелкните по кнопке Сводная таблица.

Перетащите в область строк поле Отделение банка, в область столбцов - поле

Вклад, а в область данных поле Сумма вкладов. В результате вы должны получить

таблицу представленную на рисунке.

Чтобы изменить сводную таблицу необходимо удалить название поля, перетащив

мышкой его название за пределы области сведения. Удаление поля приведет к скрытию в

сводной таблице всех зависимых от него величин, но не повлияет на исходные данные.

Задание 3

Измените поле, по которому происходит суммирование данных. Для этого надо:

поставить указатель мыши на элемент Сумма по полю Сумма вклада

при нажатой кнопке мыши утащить этот элемент за пределы таблицы, область

данных очистится

перетащить в область данных поле Проценты.

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

надо:

1. Щелкнуть по любой ячейке в области данных сводной таблицы.

2. Щелкнуть по кнопке Параметры поля на ленте инструментов (вкладка

Параметры) в разделе Активное поле.

3. В диалоговом окне выбрать операцию, выполняемую в области данных.

Задание 4

Измените операцию в поле данных на Максимум.

Детальные данные сводной таблицы и выполняемые над ними действия

Детальные данные являются подкатегорией сводной таблицы. Эти элементы

сводной таблицы являются уникальными элементами таблицы или списка. Например,

поле «Отделение банка» может содержать названия вкладов: «Юбилейный», «Обычный»

и т.д.

Отображение или скрытие детальных данных сводной таблицы Для того чтобы отобразить детальные данные, которые были скрыты ранее, или же

скрыть имеющиеся нужно выделить элемент поля, детальные данные которого

необходимо скрыть или показать.

Далее необходимо в группе инструментов Активное поле на вкладке Параметры

выбрать инструмент «Развернуть все поле» для отображения деталей или «Свернуть все

поле» для скрытия деталей. При появлении диалогового окна, выберите поле, для

которого необходимо показать детали.

Задание 5

Отобразите Вклады для каждого отделения банка. Для этого:

1. В сводной таблице установите курсор листа на любое название отделения банка,

которые расположены в колонке А.

2. В группе инструментов Активное поле на вкладке Параметры выберите

инструмент «Развернуть все поле» для отображения деталей.

3. В появившемся диалоговом окне со списком полей таблицы выберите значение

Вклад и нажмите кнопку ОК.

4. Результат представлен на рисунке ниже.

При желании вы можете воспользоваться инструментом Свернуть все поле и

скрыть дополнительные сведения.

Группировка и разгруппировка данных в сводной таблице В сводной таблице можно группировать даты, времена, числа и выделенные

элементы. Например, можно объединить месяцы, чтобы подвести итоги за квартал.

При выполнении операции группировки и разгруппировки данных сводной таблицы

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

Если элементы поля сгруппированы, добавление вычисляемых элементов в поле

невозможно.

Группировка чисел в сводной таблице Для осуществления группировки чисел

поместите в область строк или столбцов поле, по которому надо выполнять

группировку

выделите число в поле, в котором следует сгруппировать элементы

нажмите кнопку Группа по выделенному на вкладке Параметры

в диалоговом окне в поле "Начиная с" введите начальное значение, если это

необходимо, а в поле "по" введите конечное значение, если это необходимо. В поле с

шагом необходимо ввести значение периода, по которому будут создаваться группы

чисел.

Задание 6

Выполните группировку по полю Сумма вклада от 0 до 1000000 с шагом 100000 и

подсчитайте количество вкладов в каждой группе. Для этого:

1. перейдите на Лист 1 и по имеющимся данным создайте новую сводную таблицу

на новом листе;

2. в область строк перетащите поле Сумма вклада, а в область данных можно

перетащить поле Отделения банков;

3. установите курсор листа на любое значение суммы вклада в сводной таблице и на

вкладке Параметры нажмите на кнопку Группа по выделенному;

4. в появившемся диалоговом окне установите требуемые параметры и нажмите на

кнопку ОК.

5. полученный результат представлен на рисунке ниже.

Группировка дат в сводной таблице При группировка полей, содержащих даты можно выполнять группировку по

месяцам, кварталам и годам.

Задание 7

Вычислите суммы вкладов по кварталам. Для этого:

1. перейдите на Лист 1 и по имеющимся данным создайте новую сводную таблицу

на новом листе;

2. в область строк перетащите поле Дата вклада, а в область данных – поле Сумма

вклада;

3. установите курсор листа на любое значение даты вклада в сводной таблице и на

вкладке Параметры нажмите на кнопку Группа по выделенному;

4. в появившемся диалоговом окне установите значение Кварталы и нажмите на

кнопку ОК;

5. в результате вы получите следующую таблицу (см. рисунок ниже). Однако по

заданию необходимо вычислить суммы вкладов по кварталам, а мы пока нашли

количество вкладов;

6. установите курсор листа в область числовых значений и на вкладке Параметры

выберите инструмент Параметры поля. В диалоговом окне выберите соответствующую

операцию и нажмите на кнопку ОК.

Создание вычисляемого поля в сводной таблице Для создание вычисляемого поля в сводной таблице выделите ячейку в сводной

таблице.

После этого на вкладке Параметры найдите инструмент Поля,

элементы и наборы и в раскрывшемся списке выберите команду

Вычисляемое поле.

В случае малого разрешения экрана, либо окно программы развернуто

не на весь экран, инструмент Поля, элементы и наборы может быть свернут в кнопку

Вычисления.

В появившемся окне введите имя вычисляемого поля в поле "Имя", а так же

формулу в поле "Формула". Для использования имен полей таблицы в формуле

достаточно выбрать их двойным щелчком мышки из предложенного ниже списка.

Задание 8

Создайте вычисляемое поле Итого. Для этого:

1. перейдите на Лист 1 и по имеющимся данным создайте новую сводную таблицу

на новом листе;

2. в область строк перетащите поле Вклад;

3. установите курсор на название одного из вкладов;

4. на вкладке Параметры найдите инструмент Поля, элементы и наборы и в

раскрывшемся списке выберите команду Вычисляемое поле;

5. в диалоговом окне укажите имя поля – Итоги;

6. в строке формул после знака равно укажите формулу =Сумма вклада +

Проценты. Не набирайте имена полей вручную, а выбирайте их двойным щелчком

мышки из предложенного ниже списка полей.

7. после нажатия кнопки ОК вы должны получить результат представленный на

рисунке ниже;

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

правой части экрана и с ним можно работать, как и с другими полями.

Чтобы удалить это поле надо снова открыть диалоговое окно Вставка

вычисляемого поля, выбрать имя этого поля в верхней строчке и щелкнуть по кнопке

Удалить.

Создание диаграммы для сводной таблицы Для создания диаграммы для сводной таблицы необходимо на вкладке

Параметры, нажать на кнопку Сводная диаграмма. В диалоговом окне

указать тип диаграммы и нажать на кнопку ОК.

Задание 9

Постройте сводную диаграмму по вычисляемому полю Итого для сводной таблицы

из предыдущего задания.

Фильтры в сводной таблице Данные в уже созданной сводной таблице можно фильтровать с помощью области

Фильтр отчета в окне Список полей сводной таблицы.

Задание 10

Для всех отделений банка выведите суммы каждого вида вклада за первый квартал.

1. перейдите на Лист 1 и по имеющимся данным создайте новую сводную таблицу

на новом листе;

2. в область строк перетащите поле Отделение банка, в область столбцов – поле

Вклад, а в область значений – поле Сумма вклада;

3. в область Фильтр отчета перетащите поле Дата вклада;

4. в сводной таблице в области фильтра вы можете теперь выбрать необходимое

условие отбора – 1 квартал. (Ранее вы уже задавали способ группировки по датам,

поэтому в фильтре отображается уже заданный ранее параметр – по кварталам);

5. после нажатия кнопки ОК вы должны получить следующий результат:

Самостоятельное задание Откройте таблицу Сотрудники редакции.xlsx. Выполните следующие задания

(Каждое задание сохраняется в виде отдельной сводной таблицы на отдельном листе):

Создайте сводную таблицу с вычислением суммы окладов по отделам.

Создайте сводную таблицу, чтобы она выводила количество сотрудников по

отделам.

Создайте сводную таблицу, чтобы для каждого отдела выводился максимальный

оклад.

Создайте сводную таблицу, в которой выводились годы рождения сотрудников, и

указывалось, сколько сотрудников родилось в этот год.

Создайте сводную таблицу, в которой для отдела бухгалтерия отображались

должности и их количество.

Создайте сводную таблицу, в которой для каждой должности подсчитывались

суммарные отчисления (13% от оклада).

Построить диаграмму средний оклад по отделам.