51427

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

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

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

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

Русский

2014-02-11

59.5 KB

8 чел.

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

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

Цель работы:

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


 

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

21711. Оценка вероятностей возможных последствий от нарушений электроснабжения потребителей 181.5 KB
  Оценка вероятностей возможных последствий от нарушений электроснабжения потребителей Для решения широкого класса задач эксплуатации и проектирования с учётом фактора надёжности необходимо определение вероятностей возникновения возможных последствий от нарушения электроснабжения потребителей которые сводятся к следующим: вероятность возникновения катастрофических и аварийных ситуаций исследование которых необходимо для нормирования надёжности электроснабжения; вероятность возникновения отдельных составляющих ущерба их величина и...
21712. ИСПЫТАНИЯ НА НАДЕЖНОСТЬ ЭМС. КОНТРОЛЬНЫЕ ИСПЫТАНИЯ 2.49 MB
  Показатели надежности экспериментальными методами могут быть получены по результатам либо испытаний – специальных или совмещенных либо наблюдением за функционированием объекта в условиях эксплуатации. Методы испытаний организуются специально с целью определения показателей надежности объем их обычно заранее планируется условия функционирования объектов устанавливаются исходя из требований оценки конкретных показателей. Показатели надежности таких объектов оцениваются в основном либо по результатам совмещенных испытаний при которых...
21713. СТАТИСТИЧЕСКИЕ МЕТОДЫ ОЦЕНКИ, АНАЛИЗА И КОНТРОЛЯ НАДЕЖНОСТИ 358.5 KB
  Сбор информации об отказе элементов технических систем В общем комплексе мероприятий по обеспечению надёжности любого изделия сбор статистической информации об отказах и оценка показателей надёжности в условиях эксплуатации являются последним заключительным этапом. При этом появляется возможность оценить реальные значения показателей надежности и следовательно оценить эффективность мероприятий по обеспечению надёжности на всех этапах – проектирование производство испытания монтаж эксплуатация. Поэтому особое значение приобретает вопрос...
21714. ИСПЫТАНИЯ НА НАДЕЖНОСТЬ ЭМС. ОПРЕДЕЛИТЕЛЬНЫЕ ИСПЫТАНИЯ 3.06 MB
  При определительных испытаниях могут оцениваться законы распределения отказов и их параметры. При определительных испытаниях могут оцениваться законы распределения отказов и их параметры. Однако существует универсальный план испытаний позволяющий по единой методике проводить статистическую оценку величины Р для изделий с любым законом распределения. Полученные данные по отказам изделий в результате испытаний или по данным эксплуатации подвергаются статистической обработке для получения следующих результатов: определения вида функции...
21715. Планирование эксперимента при ускоренных испытаниях электрических машин 102 KB
  ТЕМА № 2 Регрессионный анализ установившихся режимов электрической системы Для этой цели целесообразно использование регрессионного моделирования сложной системы. При этом с использованием имеющихся программ расчета установившегося режима на ЭВМ проводятся целенаправленные исследования в результате которых получаются регрессионные модели для анализа или управления. Такие модели могут быть получены при регрессионном анализе или методом планирования многофакторного эксперимента МПЭ. При этом для построения линейных моделей используется полный...
21716. Законы распределения отказов 2.99 MB
  Законы распределения отказов Случайной называется величина которая в результате испытаний может принять то или иное значение причем заранее неизвестно какое именно. Если задан ряд распределений вероятностей для значений случайной величины X то математическое ожидание определяется по формуле Показателями характеризующими степень рассеяния случайной величины около своего математического ожидания являются дисперсия и среднее квадратическое отклонение: Для более полного описания случайных величин вводятся понятия функции распределения...
21717. Экономико-организационные проблемы разгрузки предприятий при дефиците мощности и прохождении максимумов нагрузки в энергосистеме 113.5 KB
  Экономикоорганизационные проблемы разгрузки предприятий при дефиците мощности и прохождении максимумов нагрузки в энергосистеме До настоящего времени работы по созданию экономически обоснованных рекомендаций по управлению электропотреблением промышленных предприятий практически не имели ни методической базы ни руководящих указаний позволяющих обеспечивать минимум экономических потерь от изменения режимов функционирования. Выполнение отмеченных условий связано с трудностями изза неопределенности а в отдельных случаях элементарного незнания...
21718. Задачи надёжности электроснабжения 203.5 KB
  Чтобы качественно сравнивать между собой события по степени их возможности нужно с каждым событием связать определенное число которое тем больше чем более возможно событие его вероятность. Найти вероятность исправной работы РП. Если вероятность одного события не изменяется от того произошло или не произошло другое событие то такие события называются независимыми и наоборот. Вероятность суммы n несовместных событий равна сумме вероятностей этих событий: где .
21719. Показатели надежности ЭМС 141 KB
  Вероятность безотказной работы ВБР– это вероятность того что при определенных условиях эксплуатации в заданном интервале времени не произойдет ни одного отказа. Кривые вероятности безотказной работы и вероятности отказов Вероятность отказа Qt– это вероятность того что при определенных условиях эксплуатации в заданном интервале времени произойдет хотя бы один отказ. Отказ и безотказная работа – события противоположенные и несовместимые 2 Частота отказов at– есть отношение отказавших изделий в единицу времени к первоначальному числу...