51427

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

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

Информатика, кибернетика и программирование

Заголовок поля. Надпись описывающая содержимое поля. Элемент поля т. Его поля конкретные значения.

Русский

2014-02-11

59.5 KB

7 чел.

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

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

Цель работы:

1. Общие сведения

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

2. Терминология сводных таблиц

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

Источник данных. Список, на основе которого получена сводная таблица.

Поле. Категория информации. Эквивалент столбца в списке.

Заголовок поля. Надпись, описывающая содержимое поля.

Элемент. Элемент поля, т. е. Его (поля) конкретные значения.

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

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

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

3. Создание сводных таблиц

Чтобы приступить к созданию сводной таблицы, нужно ------ мастер сводных таблиц. Процесс создания производится за 4 этапа.

Шаг 1: задание типа источника данных.

Можно использовать один из четырех типов источников данных.

— список Excel;

— внешний источник данных, доступный через Microsoft Qnery;

— несколько диапазонов консолидации (отдельных списков Excel);

— другую сводную таблицу.

Шаг 2: указание местонахождения исходных данных

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

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

Шаг 3: задание макета таблицы

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

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

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

Шаг 4: указание места для размещения таблицы.

На этом шаге указывают, где будет располагаться сводная таблица. Возможны два варианта:

— на новом рабочем месте,

— на существующем месте. В этом случае нужно указать диапазон ячеек на месте, куда будет помещена таблица.

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

4. Реорганизация сводной таблицы

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

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

5. Размещение полей на оси страниц

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

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

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

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

6. Выделение элементов сводной таблицы

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

Чтобы включить или выключить структурное выделение, нужно выделить часть сводной таблицы. Затем выбрать команду Выделить из контекстного меню.

Вкл./выкл. структ. Выделение

Если кнопка возле команды Разрешить выделение нажать, то становятся доступными три верхние команды меню. Эти команды позволяют выделить только заголовки, Только данные, Заголовки и данные.

Чтобы выделить всю сводную таблицу, нужно выбрать команду Таблица целиком.

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

6. Форматирование сводной таблицы

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

Условное форматирование в сводной таблице запрещено. Не сохраняются форматы границ.

Можно выполнить Автоформат: Формат/автоформат

Чтобы применить числовой формат сразу по всем ячейкам области данных сводной таблицы, нужно выделить ячейку области данных, нажать кнопку Поле сводной таблицы (пункт Поле контекстного меню). В окне Вычисление поля сводной таблицы кнопка Формат позволит задать нужный числовой формат.

6.1. Отображение пустых ячеек в сводной таблице

  1.  Выделить ячейку.
  2.  Выбрать в контекстном меню команду Параметры.
  3.  Задать нужное значение для элемента управления Для пустых ячеек отображать.

Таким же образом можно задать отображение ошибочных значений.

7. Добавление и удаление полей

Вызвать 3-е окно мастера сводных таблиц.

Можно использовать несколько полей данных. Для этого в область данных макеты таблицы следует поместить второе поле. После завершения построения таблицы будет иметь новый заголовок поля – Данные. Промежуточные итоги будут выводиться для каждого поля.

8. Переименование полей

Чтобы переименовать поле или элемент, нужно его выделить и ввести новое значение.

9. Сортировка элементов

  1.  автосортировка: сортировка элементов пол с помощью значений данных:

1.1) выделить элемент поля или кнопку поля, которое нужно отсортировать;

1.2) выбрать команду Поле из контекстного меню;

  1.  нажать кнопку Далее. Появляется окно Дополн. параметры поля сводной таблицы.

Чтобы выключить автосортировку, нужно выбрать переключ. Вручную из группы Параметры сортировки.

Чтобы включить автосортировку, нужно установить переключатель По возрастанию или по Убыванию.

Автосортировка ---------- все изменение в иск. данных и корректирует отображение данных в сводной таблице в соответствии с параметрами автосортировки.

В списке с помощью поля нужно выбрать нужное поле (поле данных).

  1.  простая сортировка: сортировка элементов поля по заголовкам.

При отключенной автосортировке можно испытать обычные команды сортировки:

Данные/Сортировка

При включенной автосортировке нужно поступить одним из следую9их способов:

– выключить автосортировку, используя переключатель Вручную д. О. Дополн. Параметры св. Таблицы. А затем испытать обычные команды сортировки Excel;

– в д. о. Дополн. параметры св. табл. Установить переключатель По возрастанию или по убыванию, а затем в списке с помощью поля выбрать имя сортируемого поля (но не поля данных).

  1.  использование нестандартного порядка сортировки:
  2.  создать свой порядок на ----- Списки, д. о.  Параметры, вызываемого командой Сервис/Параметры. Затем выбрать команду Данные/сортировка. В появившемся д. о. Нажать кнопку Параметры и задать порядок (свой) сортировки в списке Сортировка по первому ключу;
  3.  можно использовать простую замену заголовков: на месте одного написать другое, и они таким образом поменяются местами.

10. Отображение и скрытие детальных данных

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

  1.  отображение и скрытие элементов полей:
  2.  выделить поле,
  3.  выбрать команду Поле из контекстного меню,
  4.  в списке Скрыть элементы выбрать элемента, которые не будут отображаться в св. Табл.
  5.  нажать ОК.

Для того, чтобы показать скрытые элементы, нужно эту операцию проделать еще раз.

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

  1.  Автоотображение. Можно отображать в поле можно несколько наибольших и наименьших элементов, основываясь на значениях в области данных:
  2.  выделить заголовок или элемент поля,
  3.  выбрать команду Поле,
  4.  выбрать кнопку Далее,
  5.  чтобы вкл./выкл. автоотображение нужно установить переключатель автоматическое/вручную, выбрать подходящие параметры отображения в раскрывающихся списках.
  6.  Отображение/скрытие элементов внутреннего поля

Скрыть/отобразить элементы внутреннего поля можно дважды щелкнув на заголовке внешнего поля,

  1.  Вывод детальной информации для значения поля данных.

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

11. Группировка данных

  1.  Создание группировка элементов
  2.  выделить элементы поля, которые нужно объединить в группу,
  3.  в контекстное меню выбрать команду Группа и структура/Группировать.

Excel создает новое поле, в котором сгруппирует выделенные элементы в новый элемент Группа 1. Можно этот элемент переименовать.

  1.  Группировка числовых элементов

Чтобы сгруппировать числовые элементы поля, нужно выделить элемент поля и нажать кнопку Группировать на панели инструментов Сводной таблицы

Появляется окно

12. Использование общих и промежуточных итогов

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

Общие итоги

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

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

Промежуточные итоги

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

  1.  дважды щелкнуть по заголовку поля,
  2.  в окне диалога выбрать одну или несколько функций и нажать ОК.

Удалить: установить переключатель Нет.

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

Применение нескольких итоговых функций к одному полю

Можно применить количество итоговых функций. Для этого нужно вызвать 3-е окно мастера сводных таблиц и перетащить в область данных диаграммы макета кнопку этого поля еще раз. Затем дважды щелкнуть на копии заголовка и сменить организацию.

Использование дополнительных вычислений

  1.  установить курсор на область данных и в контекстовом меню выбрать команду Поле или используя 3-е окно Мастера сводных таблиц, щелкнуть дважды по полю в области данных,
  2.  в диалоговом окне Вычисление поля сводной таблицы воспользоваться кнопкой Дополнительно.
  3.  группа Дополнительные вычисления позволяет задать специальные вычисления.

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

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

Приведенное отличие: значения ячеек области данных отображаются в виде разности с заданным элементов, нормированной к значению этого элемента. Проще Доля - 100 % .

С нарастающим итогом в поле: значения ячеек области данных отображаются в виде нарастающего итога для последовательных элементов. Сделать с нарастающим итогом: по год: 1  квартал 1996 = знач. 1996 знач.  По  квартал: в течение года поквартально добавляются данные.

Доля от суммы по строке: отображаются в процентах от итога по строке.

Доля от суммы по столбцу: отображаются в процентах от итога по столбцу.

Доля от общей суммы: отображаются в процентах от общей суммы.

Индекс:

Создание вычисляемого поля (в области данных)

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

Вычислить размер отчислений в 15% от поля Получено.

Создание вычисляемого элемента (к полям добавиться новое)

  1.  Выделение элементов сводной таблицы. Структурное выделение.

Выделение только заголовков или только данных. Выделение всей таблицы.

  1.  Форматирование таблиц. Отображение пустых ячеек. Правая кнопка Параметры.

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

Переименование полей.

Сортировка элементов

– простая (обычная) сортировка Данные/Сортировка;

– автосортировка, конт. Меню/Поле

 нестандартный порядок сортировки Данные/Сортировка

  1.  Отображение и открытие детальных данных

Группировка

  1.  сравнить подписку с розничной торговлей, контекстное меню, группировать,
  2.  группировка числовых элементов.
  3.  Общие и промежуточные итоги:

– изменение функции общих итогов

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

– точно также для самого внутр. Поля.

Важно при создании вычисляемого элемента: в формуле, по которой будет вычисляться новый элемент, должны использоваться только элементы того поля, для которого и создается этот элемент. Пусть нужно половину от суммы по всем каналам оставлять на развитие фирмы. Определить эту половину для каждого инв. № за каждый квартал.

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

Вычисляемый элемент – это новый элемент в существующем поле, полученный с помощью операций над другими элементами этого поля.

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


 

А также другие работы, которые могут Вас заинтересовать

21644. Конструкция антенна Двойной квадрат 202.5 KB
  Как все проволочные антенны она достаточно проста в изготовлении и не требует дорогостоящих материалов. Антенны типа Двойной квадрат обладают следующими характеристиками. Сравнение характеристик антенны GP 5 8 и описываемой антенны проводилось при малых углах излучения по отношению к горизонту что наиболее важно для проведения дальних связей поверхностной волной. Распорки антенны 8 шт.
21645. Государственно-частное партнерство в туристской сфере 176.5 KB
  Россия в сфере развития туризма на данный момент мало преуспела, но при правильном подходе и государственной поддержки развития туризма наша страна может со временем не просто догнать страны с развитой туристской сферой, но и опередить их...
21646. Изменения климата на планете Земля 298 KB
  Климатические изменения можно с некоторой долей условности разделить на долгопериодные, короткопериодные и быстрые, происходящие за весьма короткий срок по сравнению с характерным временем изменений в социально-экономической сфере. У каждого из них свои причины, относительно которых имеется ряд гипотез.
21647. Воспитание и дрессировка собаки породы ротвейлер 286.5 KB
  Щенков ротвейлера специалисты советуют кормить от 6 до 3 раз в день (от 2 месячного возраста до годовалого) постепенно переходя на двухразовое кормление. Первый раз щенка следует кормить рано утром до прогулки, а последний – незадолго перед сном.
21648. Удосконалення пільгового оподаткування в Україні 416 KB
  Вилучення (відрахування) – вид податкової пільги, при якій відбувається вилучення окремих складових частин із загальної бази оподаткування з метою її зменшення. Механізм надання такого виду пільг безпосередньо проектується на об’єкт оподаткування, який зменшується
21649. Вивчення кваркової моделі адронів 219.5 KB
  Поняття «елементарна частинка» у фізиці виникло у зв\'язку з ідеєю відшукання неподільних частинок, з яких складається вся матерія. Неподільність спочатку приписували атомам, потім - ядрам, потім - нуклонам.
21650. СОЕДИНЕНИЕ ОПТИЧЕСКИХ ВОЛОКОН 111 KB
  СОЕДИНЕНИЕ ОПТИЧЕСКИХ ВОЛОКОН Соединение оптических волокон является наиболее ответственной операцией при монтаже кабеля предопределяющей качество и дальность связи по ВОЛС. Соединение волокон и монтаж кабелей производятся как в процессе производства так и при строительстве и эксплуатации кабельных линий. Соединители оптических волокон как правило представляют собой арматуру предназначенную для юстировки и фиксации соединяемых волокон а также для механической защиты сростка. Потери вносимые соединением оптических волокон в тракт...
21651. ОСНОВНЫЕ ПРИНЦИПЫ ПРОЕКТИРОВАНИЯ И ЭКСПЛУАТАЦИОННО-ТЕХНИЧЕСКОГО ОБСЛУЖИВАНИЯ ВОЛС 83 KB
  ОСНОВНЫЕ ПРИНЦИПЫ ПРОЕКТИРОВАНИЯ И ЭКСПЛУАТАЦИОННОТЕХНИЧЕСКОГО ОБСЛУЖИВАНИЯ ВОЛС. Требования к ВОЛС. Учет совокупности всех перечисленных факторов делает процесс проектирования ВОЛС довольно сложным допускающим получение неоднозначного решения когда выбор окончательного варианта определится конкретными условиями применения. Разработке проекта строительства ВОЛС должны предшествовать изыскательские работы с выездом на место строительства зданий НРП и трассу прокладки кабеля.
21652. Метод коммутации каналов 124.5 KB
  Коммуникационные сети должны обеспечивать связь своих абонентов между собой. Абонентами могут выступать ЭВМ, сегменты локальных сетей, факс - аппараты или телефонные собеседники.