25.04.2016      297      1
 

Как сделать сводную таблицу в Excel


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

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

Теперь о требованиях, которых необходимо придерживаться при ее создании в Эксель. Исходные данные должны быть представлены в виде таблицы, у которой должно быть название для столбцов, то есть шапка. Для этих целей отлично подойдет создание умной таблицы в Excel. Также она не должна содержать пустые строки, столбцы и ячейки. Не должно быть скрытых строк/столбцов и объединенных ячеек.

Как создать

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

Как сделать сводную таблицу в Excel

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

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

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

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

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

Как расставить поля

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

Фильтровать данные по выбранному диапазону мы будем по продавцам. То есть — выберем продавца, а в таблице отобразятся проданные им товары. Зажимаем левой кнопкой мыши поле «Продавец» и перетягиваем его в область «Фильтр отчета». Таблица изменилась, а добавленное поле теперь отмечено галочкой.

В качестве строк выберем «Товары». Аналогичным образом перетягиваем нужное поле в область «Названия строк».

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

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

В область «Названия столбцов» перетянем поле «Дата». Чтобы отобразить продажи не за каждый день, а, к примеру, по месяцам, кликните по любой дате правой кнопкой мыши и выберите из меню «Группировать».

Дальше выбираем, с какой по какую дату хотим выполнить группировку, и с каким шагом.

Таблица примет следующий вид.

Теперь в область «Значения» перетянем поле «Сумма».

Как видите, отобразились просто числа, хотя в исходном диапазоне был задан числовой формат ячеек для данного столбца, он также может быть денежный и финансовый. В столбце «Дата» формат ячеек был тоже соответствующий – дата.

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

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

Как работать с данными

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

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

В области «Значения» можно настроить параметры для поля. В примере выводится сумма значений: Рома в феврале продал рубашек на сумму 1 800.00. давайте посмотрим, сколько это штук. Кликаем левой кнопкой мышки по строке «Сумма по полю…» и выбираем из меню «Параметры полей значений».

В следующем окне из списка выбираем «Количество», выберите то, что подходит конкретно в вашем случае, и нажимаем «ОК».

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

Теперь сделаем, чтобы общий фильтр для таблицы был по месяцам. Меняем области: в «Фильтр отчета» перетаскиваем поле «Дата», в «Названия столбцов»«Продавец».

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

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

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

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


1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (1 оценок, среднее: 5,00 из 5)
Загрузка...
Об авторе: Олег Каминский

Вебмастер. Высшее образование по специальности "Защита информации". Создатель портала comp-profi.com. Автор большинства статей и уроков компьютерной грамотности

Обсуждение: есть 1 комментарий
  1. Максим:

    СПАСИБО!!!

    Ответить

Ваш комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

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

Последние комментарии