No Image

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

СОДЕРЖАНИЕ
278 просмотров
10 марта 2020

В этом курсе:

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

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

Выделите ячейки, на основе которых вы хотите создать сводную таблицу.

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

На вкладке Вставка нажмите кнопку Сводная таблица.

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

В поле Таблица или диапазон проверьте диапазон ячеек.

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

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

Настройка сводной таблицы

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

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

Чтобы переместить поле из одной области в другую, перетащите его в целевую область.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На вкладке Вставка нажмите кнопку Сводная таблица.

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

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

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

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

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

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

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

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

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

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

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

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

Отображение значения как результата вычисления и как процента

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

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

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

Теперь вы можете вставить сводную таблицу в электронную таблицу в Excel в Интернете.

Важно: Не рекомендуется создавать сводные таблицы и работать над ними в электронной таблице одновременно с другими пользователями.

Выделите таблицу или диапазон в электронной таблице.

На вкладке Вставка нажмите кнопку Сводная таблица.

В Excel появится диалоговое окно Создание сводной таблицы, в котором будет указан ваш диапазон или имя таблицы.

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

Примечание: Указанная ячейка должна находиться за пределами таблицы или диапазона.

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

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

Читайте также:  Чем помыть стекло зимой

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

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

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

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

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

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

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

диалоговое окно "Дополнительные вычисления"" />

Отображение значения как результата вычисления и как процента

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

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

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

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

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

См. также

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Get expert help now

Don’t have time to figure this out? Our expert partners at Excelchat can do it for you, 24/7.

Сводные таблицы необходимы для суммирования, анализа и представления данных, находящихся в «больших» исходных таблицах, в различных разрезах. Рассмотрим процесс создания несложных Сводных таблиц.

Сводные таблицы ( Вставка/ Таблицы/ Сводная таблица ) могут пригодиться, если одновременно выполняются следующие условия:

  • имеется исходная таблица с множеством строк (записей), речь идет о нескольких десятках и сотнях строк;
  • необходимо провести анализ данных, который требует выборки (фильтрации) данных, их группировки (суммирования, подсчета) и представления данных в различных разрезах (подготовки отчетов);
  • этот анализ затруднительно провести на основе исходной таблицы с использованием других стредств: фильтра (CTRL+SHIFT+L), формул, Расширенного фильтра;
  • исходная таблица удовлетворяет определенным требованиям (см. ниже).

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

Подготовка исходной таблицы

Начнем с требований к исходной таблице.

  • каждый столбец должен иметь заголовок;
  • в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата; столбец «Поставщик» – названия компаний только в текстовом формате или можно вводить Код поставщика в числовом формате);
  • в таблице должны отсутствовать полностью незаполненные строки и столбцы;
  • в ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, нельзя в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе Сводная таблица будет работать неэффективно (в случае, если Вам нужна информация, например, в разрезе города);
  • избегайте таблиц с «неправильной» структурой (см. рисунок ниже).

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

Более детальные советы по построению таблиц изложены в одноименной статье Советы по построению таблиц.

Несколько облегчит процесс построения Сводной таблицы, тот факт, если исходная таблица будет преобразована в формат EXCEL 2007 ( Вставка/ Таблицы/ Таблица ). Для этого сначала приведите исходную таблицу в соответствие с вышеуказанными требованиями, затем выделите любую ячейку таблицы и вызовите окно меню Вставка/ Таблицы/ Таблица . Все поля окна будут автоматически заполнены, нажмите ОК.

Создание таблицы в формате EXCEL 2007 добавляет новые возможности:

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

В качестве исходной будем использовать таблицу в формате EXCEL 2007 содержащую информацию о продажах партий продуктов. В строках таблицы приведены данные о поставке партии продукта и его сбыте.

В таблице имеются столбцы:

  • Товар – наименование партии товара, например, «Апельсины»;
  • Группа – группа товара, например, «Апельсины» входят в группу «Фрукты»;
  • Поставщик – компания-поставщик Товаров, Поставщик может поставлять несколько Групп Товаров;
  • Дата поставки – Дата поставки Товара Поставщиком;
  • Регион продажи – Регион, в котором была реализована партия Товара;
  • Продажи – Стоимость, по которой удалось реализовать партию Товара;
  • Сбыт – срок фактической реализации Товара в Регионе (в днях);
  • Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.

Через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен ) откорректируем Имя таблицы на «Исходная_таблица».

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

Сводную таблицу будем создавать для решения следующей задачи: «Подсчитать суммарные объемы продаж по каждому Товару».

Имея исходную таблицу в формате EXCEL 2007, для создания Сводной таблицы достаточно выделить любую ячейку исходной таблицы и в меню Работа с таблицами/ Конструктор/ Сервис выбрать пункт Сводная таблица.

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

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

Читайте также:  Как правильно установить виндовс с диска

Структура Сводной таблицы в общем виде может быть представлена так:

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

Т.к. ячейки столбца Товар имеют текстовый формат, то они автоматически попадут в область Названия строк Списка полей. Разумеется, поле Товар можно при необходимости переместить в другую область Списка полей. Заметьте, что названия Товаров будут автоматически отсортированы от А до Я (об изменении порядка сортировки читайте ниже).

Теперь поставим галочку в Списке полей у поля Продажи.

Т.к. ячейки столбца Продажи имеют числовой формат, то они автоматически попадут в раздел Списка полей Значения.

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

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

Если возникли вопросы о том, какие же данные из исходной таблицы были использованы для подсчета тех или иных значений Сводной таблицы, то достаточно двойного клика мышкой на конкретном значении в Сводной таблице, чтобы был создан отдельный лист с отобранными из исходной таблицей строками. Например, посмотрим какие записи были использованы для суммирования продаж Товара «Апельсины». Для этого дважды кликнем на значении 646720. Будет создан отдельный лист только со строками исходной таблицы относящихся к Товару «Апельсины».

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

Если после создания Сводной таблицы в исходную таблицу добавлялись новые записи (строки), то эти данные не будут автоматически учтены в Сводной таблице. Чтобы обновить Сводную таблицу выделите любую ее ячейку и выберите пункт меню: меню Работа со сводными таблицами/ Параметры/ Данные/ Обновить . Того же результата можно добиться через контекстное меню: выделите любую ячейку Сводной таблицы, вызовите правой клавишей мыши контекстное меню и выберите пункт Обновить.

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

Удалить Сводную таблицу можно несколькими способами. Первый – просто удалить лист со Сводной таблицей (если на нем нет других полезных данных, например исходной таблицы). Второй способ – удалить только саму Сводную таблицу: выделите любую ячейку Сводной таблицы, нажмите CTRL+A (будет выделена вся Сводная таблица), нажмите клавишу Delete.

Изменение функции итогов

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

Изменение порядка сортировки

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

Теперь предположим, что Товар Баранки – наиболее важный товар, поэтому его нужно выводить в первой строке. Для этого выделите ячейку со значением Баранки и установите курсор на границу ячейки (курсор должен принять вид креста со стрелками).

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

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

Изменение формата числовых значений

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

В появившемся окне выберите числовой формат и поставьте галочку флажка Разделитель групп разрядов.

Добавление новых полей

Предположим, что необходимо подготовить отчет о продажах Товаров, но с разбивкой по Регионам продажи. Для этого добавим поле Регион продажи, поставив соответствующую галочку в Списке полей. Поле Регион продажи будет добавлено в область Названия строк Списка полей (к полю Товар). Поменяв в области Названия строк Списка полей порядок следования полей Товар и Регион продажи, получим следующий результат.

Выделив любое название Товара и нажав пункт меню Работа со сводными таблицами/ Параметры/ Активное поле/ Свернуть все поле , можно свернуть Сводную таблицу, чтобы отобразить только продажи по Регионам.

Добавление столбцов

Добавление поля Регион продажи в область строк привело к тому, что Сводная таблица развернулась на 144 строки. Это не всегда удобно. Т.к. продажи осуществлялись только в 6 регионах, то поле Регион продажи имеет смысл разместить в области столбцов.

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

Меняем столбцы местами

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

Удаление полей

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

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

Добавление фильтра

Предположим, что необходимо подготовить отчет о продажах Групп Товаров, причем его нужно сделать в 2-х вариантах: один для партий Товаров принесших прибыль, другой – для убыточных. Для этого:

  • Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все ;
  • Ставим галочки в Списке полей у полей Группа, Продажи и Прибыль;
  • Переносим поле Прибыль из области Названия строк Списка полей в область Фильтр отчета;

Вид получившейся Сводной таблицы должен быть таким:

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

После нажатия кнопки ОК будут выведены значения Продаж только прибыльных Партий.

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

Очистить фильтр можно через меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить фильтры .

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

Несколько итогов для одного поля

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

  • Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все ;
  • Поставьте галочки напротив полей Товар и Продажи в верхней части Списка полей. Поле Продажи будет автоматически помещено в область Значения;
  • Перетащите мышкой еще одну копию поля Продажи в ту же область Значения. В Сводной таблице появится 2 столбца подсчитывающими суммы продаж;

  • в Сводной таблице выделите любое значение поля Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество . Задача решена.

Отключаем строки итогов

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

Группируем числа и Даты

Предположим, что требуется подготовить отчет о сроках сбыта. В результате нужно получить следующую информацию: сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д. Для этого:

  • Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все ;
  • Поставьте галочку напротив поля Сбыт (срок фактической реализации Товара) в верхней части Списка полей. Поле Сбыт будет автоматически помещено в область Значения;
  • выделите единственное значение поля Сбыт в Сводной таблице, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество .
  • Перетащите мышкой еще одну копию поля Сбыт в область Названия строк;
Читайте также:  6000 Мм перевести в метры

Теперь Сводная таблица показывает сколько партий Товара сбывалось за 5, 6, 7, … дней. Всего 66 строк. Сгруппируем значения с шагом 10. Для этого:

  • Выделите одно значение Сводной таблицы в столбце Названия строк;
  • В меню Работа со сводными таблицами/ Параметры/ Группировать выберите пункт Группировка по полю;
  • Появившееся окно заполните, как показано на рисунке ниже;

Теперь Сводная таблица показывает сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д.

Чтобы разгруппировать значения выберите пункт Разгруппировать в меню Работа со сводными таблицами/ Параметры/ Группировать .

Аналогичную группировку можно провести по полю Дата поставки. В этом случае окно Группировка по полю будет выглядеть так:

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

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

К ячейкам Сводной таблицы можно применить правила Условного форматирования как и к ячейкам обычного диапазона.
Выделим, например, ячейки с 10 наибольшими объемами продаж. Для этого:

  • Выделите все ячейки содержащие значения продаж;
  • Выберите пункт меню Главная/ Стили/ Условное форматирование/ Правила отбора первых и последних значений/ 10 первых элементов ;
  • Нажмите ОК.

Работать со сводными таблицами 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. Устанавливаем фильтр по значению. Исключим из отчета информацию по односпальным кроватям – уберем флажок напротив названия товара.

Комментировать
278 просмотров
Комментариев нет, будьте первым кто его оставит

Это интересно
Adblock
detector