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


 

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

31712. Двіжущіе сили та умови розвитку особистості 99 KB
  Двіжущіе сили та умови розвитку особистості Розвиток особистості слід розуміти як процес формування особистості як соціальної якості індивіда в результаті його соціалізації та виховання. Володіючи природними анатомофізіологічними передумовами до становлення особистості в процесі соціалізації дитина вступає у взаємодію з навколишнім світом опановуючи досягненнями людства. Створювані в ході цього процесу здібності і функції відтворюють в особистості історично сформувалися людські якості. Оволодіння дійсністю у дитини здійснюється в його...
31713. Соціалізація особистості 95.5 KB
  socilis суспільний характеристика це процес входження індивіда в суспільство соціалізації особистості активного засвоєння ним соціального досвіду соціальних ролей норм цінностей необхідних для успішної життєдіяльності в даному суспільстві. У процесі соціалізації в людини формуються соціальні якості знання вміння відповідні навички що дає їй змогу стати дієздатним учасником соціальних відносин. Отже основа соціальнопсихологічного розуміння соціалізації особистості грунтується на характеристиці соціальнопсихологічного типу...
31714. Загальні поняття про особистість 49 KB
  В особистості немовби концентруються особливості суспільства основні його риси. Тому зрозуміти життя особистості можна тільки розглядаючи її у конкретних суспільних умовах в діяльності та стосунках з іншими людьми аналізуючи її соціальний статус та місце в суспільних відносинах. Усі особистості – індивіди але не кожен індивід – особистість. Паригіна модель особистості котра повинна зайняти місце в системі психології припускає поєднання двох підходів: соціологічного й загальнопсихолоігчного.
31715. ХАРАКТЕРИСТИКА МЕТОДІВ СОЦІАЛЬНОЇ ПСИХОЛОГІЇ 105 KB
  Метод спостереження може використатися як один із центральних самостійних методів дослідження. Метод спостереження здійснюється також з метою збору первинного матеріалу дослідження а також для контролю отриманих емпіричних даних. Класифікація спостереження виконується на різних підставах.
31716. Соціалізація старшокласників у школі 89.5 KB
  Ціннісні орієнтації референтної групи істотною мірою визначають соціальнопсихологічне обличчя підлітка. Оскільки група єдина площина соціальнопсихологічного досвіду в якій може проявити себе підліток яку він може засвоїти й через яку пізнати сукупність суспільних відносин то саме група стає формівною силою в соціалізації підлітка. Тут надзвичайно суттєвим є питання про те що визначає референтну значущість тієї чи іншої групи в очах підлітка або навпаки сприяє її зниженню.
31717. Структура педагогічної психології 35.5 KB
  До кожної теми подано список літератури щоб читач міг поперше більш докладно вивчити певний аспект теми яка його зацікавила подруге мав уявлення та добре орієнтувався у працях авторів які займаються проблемами та дослідженнями в галузі психології навчання та виховання. Предмет і задачі педагогічної психології Предметом педагогічної психології є психологія навчання психологія виховання психологія вчителя та педагогічної діяльності. Основний зміст педагогічної психології складають психологічні закономірності процесів навчання та...
31718. Загальна характеристика процесу виховання 43.5 KB
  Соціальня ситуація розвитку особистості Становлення людини як індивіда та особистості за Л. Виготським передбачає діалектичну взаємодію двох відносно автономних однак нерозривно пов'язаних процесів розвитку природного і соціального. Кожному віку притаманна певна специфічна соціольна сипгуаціярозвитку тобто особливе співвідношення внутрішніх процесів розвитку і зовнішніх умов яке є типовим для кожного вікового етапу зумовлює динаміку психічного розвитку протягом відповідного вікового періоду і нові якісно своєрідні психологічні утворення...
31719. Формування моральної свідомості 39 KB
  Для періоду дитинства взагалі характерне засвоєння моральних норм і перетворення останніх на регулятори поведінки та діяльності дитини через наслідування відповідних дій дорослих. 3 погляду педагогіки це орієнтація на максимальне усвідомлення у межах вікових психологічних можливостей дитиною моральних вимог що їх постійно висуває перед нею життя орієнтація на природну творчість дитини. У моральній свідомості учнів розрізняють два взаємопов'язаних рівні: теоретичний система моральних знань того чи іншого рівня узагальненості та рівень...
31720. Соціально-психологічні аспекти виховання 37 KB
  Такі групи називають референтними. Референтні групи можуть бути як реальними так і уявними але особистість завжди орієнтується на їх цінності і стандарти як на еталонні зразки своєї поведінки завжди прагне до визнання з їх боку. Якщо індивід реально входить до складу референтної групи то це створює психологічно сприятливі умови для успішного розвитку особистості в певному напрямку останній зовсім не обов’язково має співпадати зі загальною стратегією виховання особливо коли референтній групі притаманна асоціальна спрямованість.