Работа со сводными таблицами в Excel на примерах. Сводные таблицы в excel для экономистов


Сводные таблицы | Excel для финансиста

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

Скачайте файл svodnie-tablici. На листе данные этого файла находятся  двести записей о продажах товаров (на практике число анализируемых записей обычно на один-два порядка больше). Каждая запись представляет собой строчку в таблице и содержит информацию:

  • Дата совершения продажи;
  • Наименование товара;
  • Наименование покупателя товара;
  • Сумма сделки.

Относительно этих данных может возникнуть множество вопросов:

  • Какая общая сумма продаж?
  • Кто самый активный покупатель?
  • Какой самый популярный товар по общей сумме сделки?
  • Как распределены продажи в течение года, есть ли сезонность у товаров?
  • Растут или падают продажи в течение нескольких лет?

На все эти вопросы помогают ответить сводные таблицы.

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

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

Перед тем, как сделать сводную таблицу, нужно задать данные, которые будут в ней отражены. В нашем случае – вся таблица. Проще всего выделить таблицу, выбрав любую ячейку в ней и нажав Ctrl-A. Теперь в меню Вставка нажмите кнопку Сводная таблица, в открывшемся окне проверьте выбранный диапазон данных, выберите, что создание сводной таблицы произойдёт на новом листе, ОК.

1

Поля сводной таблицы

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

2Напомним, сводная таблица должна давать ответы на поставленные вопросы. Например, ответим на три первых вопроса: о сумме продаж, о самом активном покупателе и самом популярном товаре. Для этого нужно отметить в окне справа поля Наименование товара, Покупатель, Сумма. Программа разместит поле Сумма в окошко Суммарные значения (в самом низу справа), а остальные два поля – в окошко Названия строк. Перетащите одно из полей в окошко Названия столбцов. Получится примерно так:

3

Всего несколько кликов мышкой, и первая сводная таблица в Excel готова! Программа уже посчитала суммы продаж в двух разрезах: по покупателям и товарам, и вывела общий итог. Таким образом программа берёт и структурирует данные. Можно немного доработать сводную таблицу. Выделите финансовые данные таблицы (диапазон B5:E9), задайте этим ячейкам финансовый формат, суммы стали нагляднее. Выделите ячейку Е5 (общий итог – покупатель Автоматика), нажмите меню Параметры, в разделе Сортировка – большую кнопку Сортировка, в открывшемся окне – Параметры сортировки – По убыванию, ОК. Теперь и производители, и товары отсортированы по убыванию, ответы на первые три вопроса получены.

4

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

5

Одно окошко было пока обойдено вниманием: Фильтр отчёта. Перенесите туда поле Покупатель. В ячейках А1-А2 появился фильтр выбора значений этого поля, это полезно для более детального анализа. Добавив простую диаграмму-график на основе данных сводной таблицы, получаем хороший аналитический инструмент: выбирая покупателя, можно смотреть динамику продаж по каждому товару.

6

Скачать пример сводных таблиц Excel: svodnie-tablici

Практический пример со сводными таблицами: Экспорт данных из 1С в Excel на примере отчёта о движении денежных средств

Предыдущая глава: Глава 6. Основные функции Excel

Содержание учебника: Содержание

 

finexcel.ru

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

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

Например, вот простой список расходов семьи и сводная таблица, созданная на его основе:

Данные о расходах семьи

Соответствующая сводная таблица

Пример семейных расходов для создания сводной таблицы с данными по месяцам, категориям и суммам

Пример сводной таблицы: "Категории" в разделе "Строки" и "Месяцы" в разделе "Столбцы"

А вот сводная диаграмма:

Пример сводной диаграммы Excel

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

Перед началом работы

  • Данные должны быть представлены в виде таблицы, в которой нет пустых строк или столбцов. Рекомендуется использовать таблицу Excel, как в примере выше.

  • Таблицы — это отличный источник данных для сводных таблиц, так как строки, добавляемые в таблицу, автоматически включаются в сводную таблицу при обновлении данных, а все новые столбцы добавляются в список Поля сводной таблицы. В противном случае необходимо вручную обновлять диапазон исходных данных или использовать формулу динамического именованного диапазона.

  • Все данные в столбце должны иметь один и тот же тип. Например, не следует вводить даты и текст в одном столбце.

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

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

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

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

Создание сводной таблицы вручную

  1. Щелкните ячейку в диапазоне исходных данных и таблицы.

  2. Выберите Вставка > Таблицы > Рекомендуемые сводные таблицы.

    Выберите "Вставка" > "Рекомендуемые сводные таблицы" для автоматического создания сводной таблицы

  3. Excel проанализирует данные и предоставит несколько вариантов, как в этом примере:

    Диалоговое окно "Рекомендуемые сводные таблицы" в Excel
  4. Выберите сводную таблицу, которая вам понравилась, и нажмите кнопку ОК. Excel создаст сводную таблицу на новом листе и выведет список Поля сводной таблицы.

  1. Щелкните ячейку в диапазоне исходных данных и таблицы.

  2. Выберите Вставка > Таблицы > Сводная таблица.

    Выберите "Вставка" > "Сводная таблица", чтобы вставить пустую сводную таблицу

    Если вы используете Excel для Mac 2011 или более ранней версии, кнопка "Сводная таблица" находится на вкладке Данные в группе Анализ.

    Вкладка "Данные", группа "Анализ"

  3. Появится диалоговое окно Создание сводной таблицы, в котором указан ваш диапазон или имя таблицы. В этом случае мы используем таблицу "таблица_СемейныеРасходы".

    Диалоговое окно "Создание сводной таблицы"
  4. В разделе Укажите, куда следует поместить отчет сводной таблицы выберите На новый лист или На существующий лист. При выборе варианта На существующий лист вам потребуется указать лист и ячейку.

  5. Если вы хотите включить несколько таблиц или источников данных в сводную таблицу, установите флажок Добавить эти сведения в модель данных.

  6. Нажмите кнопку ОК. Excel создаст пустую сводную таблицу и выведет список Поля сводной таблицы.

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

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

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

Соответствующие поля в сводной таблице

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

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

Значения в сводной таблице

  • Операция

    По умолчанию поля сводной таблицы, которые находятся в области Значения, обобщаются с помощью функции СУММ. Если Excel считает данные текстом, к ним применяется функция СЧЁТ. Именно почему так важно не использовать разные типы данных для полей значений. Вы можете изменить функцию, которая по умолчанию используется для вычислений. Для этого щелкните стрелку справа от имени поля и выберите Параметры полей значений.

    Диалоговое окно "Параметры поля значений"

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

    Диалоговое окно "Параметры поля значений" для параметров "Итоги по"

    Совет: Так как при изменении способа вычисления в разделе Операция обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно нажать кнопку Найти и заменить (CTRL+H), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.

  • Дополнительные вычисления

    Значения также можно выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.

    Пример сводной таблицы со значениями в процентах от общей суммы

    Вы можете настроить такие параметры в диалоговом окне Параметры поля значений на вкладке Дополнительные вычисления.

    "Параметры поля значений" > диалоговое окно "Дополнительные вычисления"
  • Отображение значения как результата вычисления и как процента

    Просто перетащите элемент в раздел Значения дважды, а затем настройте параметры Операция и Дополнительные вычисления для каждой из копий.

Обновление сводных таблиц

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

Обновление всех сводных таблиц: на ленте выберите "Работа со сводными таблицами" > "Анализ", в группе "Данные" щелкните стрелку под кнопкой "Обновить" и выберите команду "Обновить все".

Удаление сводной таблицы

Если вам больше не нужна сводная таблица, просто выделите ее и нажмите клавишу DELETE. Это не повлияет на другие данные и сводные таблицы или диаграммы. Если сводная таблица находится на отдельном листе, где больше нет нужных данных, вы можете просто удалить этот лист. Так проще всего избавиться от сводной таблицы.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также:

Создание сводной диаграммы

Использование срезов для фильтрации данных сводной таблицы

Создание временной шкалы сводной таблицы для фильтрации дат

Создание сводной таблицы с моделью данных для анализа данных в нескольких таблицах

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

Изменение исходных данных сводной таблицы

Вычисление значений в сводной таблице

Удаление сводной таблицы

support.office.com

Изменение исходных данных сводной таблицы

  1. Щелкните отчет сводной таблицы.

  2. На вкладке " Анализ " в группе данные нажмите кнопку Изменить источник данных и нажмите кнопку Изменить источник данных.

    Откроется диалоговое окно Изменение источника данных сводной таблицы.

  3. Выполните одно из действий, указанных ниже.

    • Чтобы изменить источник данных сводной таблицы на другую таблицу Excel или диапазон ячеек, нажмите кнопку выбрать таблицу или диапазон, затем введите первую ячейку в текстовом поле Таблица или диапазон и нажмите кнопку ОК

      Диалоговое окно ''Изменение источника данных сводной таблицы''

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

      1. Нажмите кнопку выберите использовать внешний источник данных и нажмите кнопку Выбрать подключение.

        Диалоговое окно ''Изменение источника данных сводной таблицы''

        Откроется диалоговое окно Существующие подключения.

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

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

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

        Дополнительные сведения см. в статье Создание, редактирование и управление подключениями к внешним данным.

      4. Нажмите кнопку ОК.

Что делать, если отсутствует подключение?

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

Диалоговое окно ''Выбор источника данных''

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

Вкладка ''Таблицы'' в диалоговом окне ''Существующие подключения''

  1. Выберите нужное подключение и нажмите кнопку Открыть.

  2. Выберите вариант Только создать подключение.

    Импорт данных с помощью варианта ''Только создать подключение''

  3. Щелкните пункт Свойства и выберите вкладку Определение.

    Свойства подключения

  4. Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.

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

  1. Щелкните отчет сводной таблицы.

  2. На вкладке Параметры в группе Данные щелкните пункт Изменить источник данных и затем выберите вариант Изменить источник данных.

    Отображается диалоговое окно Изменение данных в сводной таблице источника.

  3. Выполните одно из действий, указанных ниже.

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

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

  4. Чтобы выбрать другое сетевое подключение, выберите вариант Использовать внешний источник данных, а затем нажмите кнопку Выбрать подключение.

    Откроется диалоговое окно Существующие подключения.

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

  6. Выберите соединение в списке Выберите подключение и нажмите кнопку Открыть.

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

    Дополнительные сведения см. в статье Создание, редактирование и управление подключениями к внешним данным.

  7. Нажмите кнопку ОК.

Что делать, если отсутствует подключение?

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

Диалоговое окно ''Выбор источника данных''

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

Вкладка ''Таблицы'' в диалоговом окне ''Существующие подключения''

  1. Выберите нужное подключение и нажмите кнопку Открыть.

  2. Выберите вариант Только создать подключение.

    Импорт данных с помощью варианта ''Только создать подключение''

  3. Щелкните пункт Свойства и выберите вкладку Определение.

    Свойства подключения

  4. Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.

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

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

support.office.com

Работа со сводными таблицами в Excel на примерах

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

Исходный материал – таблица с несколькими десятками и сотнями строк, несколько таблиц в одной книге, несколько файлов. Напомним порядок создания: «Вставка» – «Таблицы» – «Сводная таблица».

А в данной статье мы рассмотрим, как работать со сводными таблицами в Excel.

Как сделать сводную таблицу из нескольких файлов

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

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

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

Гораздо сложнее сделать сводную таблицу на основе разных по структуре исходных таблиц. Например, таких:

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

Мастер сводных таблиц при таких исходных параметрах выдаст ошибку. Так как нарушено одно из главных условий консолидации – одинаковые названия столбцов.

Но два заголовка в этих таблицах идентичны. Поэтому мы можем объединить данные, а потом создать сводный отчет.

  1. В ячейке-мишени (там, куда будет переноситься таблица) ставим курсор. Пишем = - переходим на лист с переносимыми данными – выделяем первую ячейку столбца, который копируем. Ввод. «Размножаем» формулу, протягивая вниз за правый нижний угол ячейки.
  2. По такому же принципу переносим другие данные. В результате из двух таблиц получаем одну общую.
  3. Теперь создадим сводный отчет. Вставка – сводная таблица – указываем диапазон и место – ОК.

Открывается заготовка Сводного отчета со Списком полей, которые можно отобразить.

Покажем, к примеру, количество проданного товара.

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



Детализация информации в сводных таблицах

Из отчета (см.выше) мы видим, что продано ВСЕГО 30 видеокарт. Чтобы узнать, какие данные были использованы для получения этого значения, щелкаем два раза мышкой по цифре «30». Получаем детальный отчет:

Как обновить данные в сводной таблице Excel?

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

Обновление данных:

Курсор должен стоять в любой ячейке сводного отчета.

Либо:

Правая кнопка мыши – обновить.

Чтобы настроить автоматическое обновление сводной таблицы при изменении данных, делаем по инструкции:

  1. Курсор стоит в любом месте отчета. Работа со сводными таблицами – Параметры – Сводная таблица.
  2. Параметры.
  3. В открывшемся диалоге – Данные – Обновить при открытии файла – ОК.

Изменение структуры отчета

Добавим в сводную таблицу новые поля:

  1. На листе с исходными данными вставляем столбец «Продажи». Здесь мы отразим, какую выручку получит магазин от реализации товара. Воспользуемся формулой – цена за 1 * количество проданных единиц.
  2. Переходим на лист с отчетом. Работа со сводными таблицами – параметры – изменить источник данных. Расширяем диапазон информации, которая должна войти в сводную таблицу.

Если бы мы добавили столбцы внутри исходной таблицы, достаточно было обновить сводную таблицу.

После изменения диапазона в сводке появилось поле «Продажи».

Как добавить в сводную таблицу вычисляемое поле?

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

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

Инструкция по добавлению пользовательского поля:

  1. Определяемся, какие функции будет выполнять виртуальный столбец. На какие данные сводной таблицы вычисляемое поле должно ссылаться. Допустим, нам нужны остатки по группам товаров.
  2. Работа со сводными таблицами – Параметры – Формулы – Вычисляемое поле.
  3. В открывшемся меню вводим название поля. Ставим курсор в строку «Формула». Инструмент «Вычисляемое поле» не реагирует на диапазоны. Поэтому выделять ячейки в сводной таблице не имеет смысла. Из предполагаемого списка выбираем категории, которые нужны в расчете. Выбрали – «Добавить поле». Дописываем формулу нужными арифметическими действиями.
  4. Жмем ОК. Появились Остатки.

Группировка данных в сводном отчете

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

Щелкаем правой кнопкой мыши по любой дате. Выбираем команду «Группировать».

В открывшемся диалоге задаем параметры группировки. Начальная и конечная дата диапазона выводятся автоматически. Выбираем шаг – «Годы».

Получаем суммы заказов по годам.

Скачать пример работы

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

exceltable.com

Обучение сводным таблицам Excel на примерах

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

Работа со сводными таблицами в Excel

Создайте таблицу исходных данных о клиентах фирмы, так как показано на рисунке:

Теперь по исходной базе данных клиентов построим сводную таблицу, в которой можно легко отобразить средний возраст всех клиентов фирмы, которые не имеют дисконтную скидку c распределением на:

  • возраст;
  • образование;
  • пол.

Решение для построения сводного отчета в Excel:

  1. Перейдите на любую ячейку исходной клиентской базы и выберите инструмент: «Вставка»-«Таблицы»-«Сводная таблица».
  2. В появившемся диалоговом окне переключите на опцию «На существующий лист» и в поле «Диапазон» укажите значение h4:
  3. В окне настроек (справа) «Список полей сводной таблицы» переместите значение «Пол» из поля «Выберите поля для добавления в отчет» в поле «Фильтр отчета»:
  4. Таким же образом распределите остальные значения по полям так как указано выше на рисунке.
  5. Щелкните правой кнопкой мышки по любой ячейке внутри сводной таблицы. Из появившегося контекстного меню выберите опцию: «Итоги по»-«Среднее».
  6. Округлите все значения до десятых с помощью формата ячеек.

Обратите внимание! В ячейке I1 мы имеем возможность указать пол (мужской, женский или оба) для сегментирования отчета.

Полезный совет! Данная сводная таблица не имеет динамического подключения баз данных к исходной таблице клиентов. Поэтому любое изменение в исходной таблице не обновляются автоматически в сводной таблице. Поэтому после каждого изменения исходных данных следует щелкнуть правой кнопкой мышки по сводной таблице и выбрать опцию «Обновить». Тогда все данные выполнять пересчет и обновятся.



Пример настройки сводных отчетов

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

  1. Если вы случайно закрыли окно «Список полей сводной таблицы», тогда щелкните левой кнопкой мышки по сводной таблице, чтобы сделать ее активной. Затем включите опцию: «Работа со сводными таблицами»-«Параметры»-«Показать»-«Список полей».
  2. Переместите значение «Пол» в поле «Название строк» под значение «Образование».

Структура отчета кардинально изменена:

Скачать пример обучения сводными таблицами

Появились группировки, которые управляются кнопками (+ и -). Эти вспомогательные кнопки и заголовки можно включить/отключить через меню: Работа со сводными таблицами»-«Параметры»-«Показать»-«Кнопки» или «Заголовки полей». Так же у нас больше нет фильтра для сегментирования отчета по полу в ячейке I1.

exceltable.com

Примеры работы со сводными таблицами в Excel

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

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

Создание отчета с помощью мастера сводных таблиц

У нас есть тренировочная таблица с данными:

Каждая строка дает нам исчерпывающую информацию об одной сделке:

  • в каком магазине были продажи;
  • какого товара и на какую сумму;
  • кто из продавцов постарался;
  • когда (число, месяц).

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

Создам отчет с помощью мастера сводных таблиц. В новых версиях Excel он почему-то спрятано глубоко в настройках:

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

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

  1. Ставим курсор в любом месте таблицы с данными. Вызываем мастер сводных таблиц, нажимая на соответствующий инструмент, который теперь уже расположенный напанели быстрого доступа.
  2. На первом шаге выбираем источник данных для формирования сводной таблицы. Нажимаем «Далее». Чтобы собрать информацию в сводный отчет из нескольких листов, выбираем: «в списке или базе данных Microsoft Excel».
  3. На втором шаге определяем диапазон данных, на основании которых будет строиться отчет. Так как у нас стоит курсор в таблице, диапазон обозначится автоматически.
  4. На третьем шаге Excel предлагает выбрать, куда поместить сводную таблицу. Жмем «Готово» и открывается макет.
  5. Нужно обозначить поля для отображения в отчете. Допустим, мы хотим узнать суммы продаж по каждому продавцу. Ставим галочки – получаем:

Готовый отчет можно форматировать, изменять.



Как обновить данные в сводной таблице Excel?

Это можно сделать вручную и автоматически.

Вручную:

  1. Ставим курсор в любом месте сводной таблице. В результате становится видна вкладка «Работа со сводными таблицами».
  2. В меню «Данные» жмем на кнопку «Обновить» (или комбинацию клавиш ALT+F5).
  3. Если нужно обновить все отчеты в книге Excel, выбираем кнопку «Обновить все» (или комбинацию клавиш CTRL+ALT+F5).

Настройка автоматического обновления при изменении данных:

  1. На вкладке «Работа со сводными таблицами» (необходимо щелкнуть по отчету) выбираем меню «Параметры».
  2. Открываем «Дополнительные параметры сводной таблицы». Открывается мастер.
  3. В разделе «Данные» устанавливаем галочку напротив пункта «Обновить при открытии файла».

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

Некоторые секреты форматирования

Когда мы сводим в отчет большой объем данных, для выводов и принятия каких-то решения может понадобиться группировка. Допустим, нам нужно увидеть итоги за месяц или квартал.

Группировка по дате в сводной таблице Excel:

  1. Источник информации – отчет с данными.
  2. Так как нам нужна группировка по дате, выделяем любую ячейку с соответствующим значением. Щелкаем правой кнопкой мыши.
  3. Из выпавшего меню выбираем «Группировку». Откроется инструмент вида:
  4. В полях «Начиная с» и «По» Excel автоматически проставил начальную и конечную даты диапазона данных. Определяемся с шагом группировки. Для нашего примера – либо месяцы, либо кварталы. Остановимся на месяцах.

Получаем отчет, в котором четко видны суммы продаж по месяцам. Поэкспериментируем и установим шаг – «Кварталы». Результат – сводная таблица вида:

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

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

Работа с итогами

У нас есть сводный отчет такого вида:

Видны итоги по месяцам (сделано «Группировкой») и по наименованиям товаров. Сделаем отчет более удобным для изучения.

Как в сводной таблице сделать итоги сверху:

  1. «Работа со сводными таблицами» - «Конструктор».
  2. На вкладке «Макет» нажимаем «Промежуточные итоги». Выбираем «Показывать все промежуточные итоги в заголовке группы».
  3. Получается следующий вид отчета:

Уже нет той перегруженности, которая затрудняла восприятие информации.

Как удалить промежуточные итоги? Просто на вкладке макет выбираем «Не показывать промежуточные суммы»:

Получим отчет без дополнительных сумм:

Детализация информации

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

  1. В марте продано двуспальных кроватей на сумму 23 780 у.е. Откуда взялась эта цифра. Выделяем ячейку с данной суммой и щелкаем правой кнопкой мыши и выбираем опцию:
  2. На новом листе откроется таблица с данными о продажах товара.

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

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

В нашем примере – ВСЕ товары, ВСЕ даты, ВСЕ суммы и магазины. Возможно, пользователю не нужны некоторые элементы. Они просто загромождают отчет и мешают сосредоточиться на главном. Уберем ненужные элементы.

  1. Нажимаем на стрелочку у названия столбца, где будем корректировать количество информации.
  2. Выбираем из выпадающего меню название поля. В нашем примере – это название товара или дата. Мы остановимся на названии.
  3. Устанавливаем фильтр по значению. Исключим из отчета информацию по односпальным кроватям – уберем флажок напротив названия товара.

Жмем ОК – сводная таблица меняется.

exceltable.com

Обновление данных в сводной таблице

Обновление вручную

  1. Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.

    Работа со сводными таблицами

  2. На вкладке Анализ выберите команду Обновить или нажмите ALT+F5.

    Кнопка "Обновить" на вкладке "Анализ"

    Совет: Чтобы обновить сразу все сводные таблицы в книге, нажмите кнопку Анализ > Обновить все.

Если таблица обновляется слишком долго, на вкладке Анализ нажмите стрелку рядом с кнопкой Обновить и выберите пункт Состояние обновления. Так вы сможете проверить состояние обновления.

Чтобы отменить обновление, нажмите Отменить обновление.

Блокировка изменения ширины столбцов и форматирования ячеек

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

  1. На вкладке Анализ нажмите кнопку Параметры.

    Кнопка "Параметры" на вкладке "Анализ"

  2. На вкладке Макет и формат установите флажки Автоматически изменять ширину столбцов при обновлении и Сохранять форматирование ячеек при обновлении.

Автоматическое обновление данных при открытии книги

  1. Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.

    Работа со сводными таблицами

  2. На вкладке Анализ нажмите кнопку Параметры.

    Кнопка "Параметры" на вкладке "Анализ"

  3. На вкладке " данные " установите флажок обновление данных при открытии файла.

Обновление вручную

  1. Щелкните в любом месте сводной таблицы.

    Откроется панель Работа со сводными таблицами, добавив Параметры и на вкладке Конструктор.

  2. На вкладке Параметры в группе " данные " выполните одно из указанных ниже действий.

  3. Чтобы обновить данные в соответствии с источником данных, нажмите кнопку Обновить или нажмите клавиши ALT + F5.

    Можно также щелкнуть правой кнопкой сводную таблицу и нажмите кнопку Обновить.

  4. Чтобы обновить все сводные таблицы в книге, щелкните стрелку Обновить и нажмите кнопку Обновить все.

    Изображение ленты Excel

    Если обновление занимает больше времени, чем ожидалось, нажмите кнопку Параметры > Обновить > Состояние обновления, чтобы проверить состояние обновления.

    Чтобы отменить обновление, нажмите Отменить обновление.

Блокировка изменения ширины столбцов и форматирования ячеек

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

  1. Нажмите кнопку Параметры > Параметры.

    Группа «Сводная таблица» на вкладке «Параметры» в разделе «Работа со сводными таблицами»
  2. На вкладке Макет и формат установите флажки Автоматически изменять ширину столбцов при обновлении и Сохранять форматирование ячеек при обновлении.

Автоматическое обновление данных сводной таблицы при открытии книги

  1. Щелкните в любом месте сводной таблицы.

  2. На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.

    Группа «Сводная таблица» на вкладке «Параметры» в разделе «Работа со сводными таблицами»

  3. В диалоговом окне Параметры сводной таблицы на вкладке " данные " установите флажок обновлять данные при открытии файла.

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

support.office.com