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

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

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

1.   Назначение сводных таблиц.

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

2.   Пример.

Рассмотрим возможности сводных таблиц на конкретном примере  (рис. 1.).

Рис.1.Исходная таблица

Введите данные о выплатах. Отформатируйте таблицу:

-установите начертание шрифта заголовка полужирное;

-заголовки столбцов разместите в центре ячейки;

-для ячейки   D2  включите флажок «переносить по словам» на корешке «Выравнивание» окна «Форматирование ячейки»;

-прочертите рамки.

Теперь ваша таблица имеет вид как на рис. 1.

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

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

Ответ на этот вопрос получим  с помощью мастера сводных таблиц за четыре шага:

-выделите область исходных данных A2:F9;

-выполните команду ДАННЫЕ / СВОДНАЯ  ТАБЛИЦА;

-мастер сводных таблиц на 1 шаге запрашивает источник данных. В нашем случае должен быть установлен переключатель  «в списке или базе данных Microsoft Excel», нажмите  <Далее>;

-на втором шаге необходимо указать диапазон ячеек  электронной таблицы, содержащей исходные данные. Поскольку вы предварительно выделили область, содержащую исходные данные,  Excel автоматически указал этот диапазон. Нажмите  <Далее>;

-на третьем шаге необходимо заполнить пустой макет сводной таблицы, который имеет вид: (рис.2.).

Рис.2.Пустой макет сводной таблицы

В соответствии с вопросом необходимо отбуксировать кнопки полей исходной таблицы в следующем порядке:  кнопку «Дата»  на место столбца сводной таблицы, кнопку «Тема» на место строки сводной таблицы, а кнопку   «всего» на место данных.  Теперь макет имеет вид (рис. 3.);

Рис.3.Заполненный макет сводной таблицы

 -нажмите  <Далее>;

-на последнем шаге необходимо указать адрес ячейки левого верхнего угла диапазона, где будет помещена сводная таблица -А13, и название таблицы «Общие затраты». Выключите флажок  «Автоматически форматировать таблицу»,  иначе таблица «Выплаты по темам» изменит существенно свой вид. Нажмите  <Готово>. Сводная таблица имеет вид (рис.4.)

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

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

4.   Группирование.

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

-сделать активной ячейку В14;

-в контекстном меню выполнить команду   СТРУКТУУРА / СГРУППИРОВАТЬ или щелкнуть мышью на кнопке панели «Запрос и сводная таблица» «Сгруппировать»;

-в открывшемся окне  «Группировка» задать способ группирования данных «По годам» и снять группирование «По месяцам».

Теперь сводная таблица имеет вид (рис.5.).

Рис.5.Окончательный вид сводной таблицы

  Ответ на вопрос получен.

5.   Замена строк на столбцы.

    Изменим внешний вид таблицы.  Название тем используем в качестве столбцов, а название дат в качестве  строк. Двойной щелчок мышью на названии строки или столбца открывает диалоговое окно  «Вычисление поля сводной таблицы».  Откройте это окно для поля «Тема».  Установите переключатель  «По столбцам» из группы  «Ориентация».  Для поля  «Дата» установите ориентацию по строкам.  Таблица имеет вид (рис. 6.).

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

6.   Скрытие и добавление элементов поля.

   Пусть необходимо представить сведения о затратах на темы только за 1997 год по кварталам, для этого необходимо:

-открыть диалоговое окно «Вычисление поля для сводной таблицы»  для поля  «Дата»  и  скрыть элемент  «1996»;