Как строить сводные таблицы в excel 2007

Сводные таблицы и анализ данных списков Excel

Учебная дисциплина
Информатика и компьютерная техника
Модуль 2. Офисные прикладные программы Microsoft Office 2007

2.2.6. Анализ деловых данных в Excel 2007

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

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

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

Рис. 1.

К данному диапазону будет применено форматирование, т.е. применен экспресс-стиль таблицы, заданный по умолчанию, при этом активизируются контекстные инструменты под общим названием "Работа с таблицами", которые входят в контекстную вкладку Конструктор. Для форматирования таблицы можно также применить средство "Форматировать как таблицу" на вкладке "Главная".

Рис. 2.

Для построения отчета по этой таблице целесообразно применить мощное средство "Сводная таблица". Для применения этого средства к спискам данных или к таблицам данных необходимо активизировать одну из ячеек таблицы данных, например ячейку таблицы "Остатки товаров на складе". Затем щелкнуть кнопку "Сводная таблица", которая находится на вкладке "Вставка" в группе "Таблица" (рисунок 3).

Рис. 3.

В открывшемся окне диалога "Создание сводной таблицы" требуется выбрать (выделить) таблицу или диапазон и указать, куда следует поместить отчет (желательно на новый лист), затем щелкнуть ОК. Откроется специальный мастер сводных таблиц (рисунок 4).

Рис. 4.

В левой части рабочего листа отображается изображение отчета (Сводная Таблица1), а в правой части листа расположены инструменты для создания сводной таблицы: четыре пустых областей и список полей. Для построения отчета надо в правой части перетащить требуемые поля в соответствующие области сводной таблицы: "Фильтр отчета", "Название столбцов", "Название строк" и "Значения".

Например, если выбрать поля: № склада, Наименование, Цена (грн.) и перетащить их в соответствующие области: "Название столбцов", "Название строк" и "Значения", то в правой части они будут отображаться в этих областях. При этом в левой части рабочего листа будет построена сводная таблица или отчет (рис. 5).

Рис. 5.

Следует отметить, что в области "Значения" выполняются какие-либо математические вычисления, например, суммирование (Сумма по полю Цена). Чтобы изменить тип вычислений, надо в области "Значения" щелкнуть левой кнопкой мыши по полю "Сумма по полю Цена" и в открывшемся меню выбрать команду "Параметры полей значений", затем в окне диалога "Параметры поля значений" выбрать требуемую функцию и щелкнуть на кнопке ОК.

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

Средства Excel для анализа данных и решения задач оптимизации

Мощными средствами анализа данных Excel 2007 являются:

  • анализ "что – если", к которым относятся: подбор параметров и диспетчер сценариев;
  • надстройка "Поиск решения" (надстройка Solver).

Средства анализ "что – если" помещены на вкладке "Данные" в группе "Работа с данными", а "Поиск решений" на вкладке "Данные" в группе "Analysis".

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

Программа "Поиск решений" предназначена для решения сложных систем уравнений, линейных и нелинейных задач оптимизации. В основе надстройка Solver лежат итерационные методы.

Средства анализа данных и решения задач оптимизации Excel 2007 аналогичны средствам Excel 2003. Работа со средствами Excel 2003 для анализа данных и решения задач оптимизации: подбор параметров, диспетчер сценариев и надстройка Solver подробно изложены на страничке: Решение задач оптимизации в Excel.

Copyright

© Обучение в интернет, .
Обратная связь

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

сводная таблица в Excel 2007Вернемся к таблице продаж, которую Вы уже сделали. Такие таблицы могут быть огромными, ведь каждый день могут совершаться десятки операций, и соответственно, каждый день в таблице будут добавляться десятки новых строк.

А теперь представьте себе, что Вам нужно сделать отчет о проделанной работе. Например, Вам нужно показать, сколько и каких товаров было продано в каждом месяце. Или кто из менеджеров сколько продал, и на какую сумму? Сколько было продано с каждого склада за определенные месяцы и так далее? Что делать в этом случае? Вручную считать долго и неудобно. На помощь приходит один из самых мощных инструментов Excel 2007сводная таблица.

Выделите хотя бы одну ячейку в таблице и нажмите Вставка — Сводная таблица . Будет показан диапазон данных для сводной таблицы (Ваша исходная таблица), и Вам предложат два варианта ее размещения: на новый лист или на уже существующий. Оставьте вариант на новый лист, и нажмите ОК .

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

Теперь достаточно ставить галочки напротив тех названий столбцов, которые Вам нужны. Например, Вы хотите составить отчет по сумме продаж каждого менеджера. Для этого ставите галочки напротив строк Продано и Менеджер . Эти названия попадут в области ниже: Менеджер в область Названия строк , а Продано — в область значений . А слева Вы увидите готовый отчет.

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

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

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

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

Рассмотрим теперь возможность фильтрации данных в сводной таблице Excel. Поставьте галочку напротив строки Склад . И перетащите эту строку из области Названия строк в область Фильтр отчета . Вверху в сводной таблице Excel появится строка Склад , в которой будет возможность выбора: все, склад 001 или склад 002.

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

Видео о том, как создавать сводные таблицы в Excel 2007

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

Также Вы можете подписаться на блог, и узнавать о всех новых статьях.
Это не займет много времени. Просто нажмите на ссылку ниже:
Подписаться на блог: Дорога к Бизнесу за Компьютером

Вам понравилась статья? Поделитесь, буду весьма признателен:

Также приглашаю добавиться в друзья в социальных сетях:

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

3 комментария »

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

Очень толковая статья! Большое спасибо автору. До этого пробовала почитать справку в Excel и разобраться в сводных таблицах, но толку было мало.

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

Ссылка на основную публикацию