Андрей Смирнов
Время чтения: ~21 мин.
Просмотров: 1

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

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

Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.

Заменим выручку на прибыль.

Товары и области меняются местами также перетягиванием мыши.

Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.

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

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

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

Обратите внимание, что у нас уже нет четких названий полей. Их можно вытащить, нажав на пункты в верхней области

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

Новые возможности сводных таблиц в Excel 2013

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

На вкладке Вставка (Insert) появилась кнопка Рекомендуемые сводные таблицы (Recommended Pivot Tables) , предлагающая несколько подходящих сводных таблиц разного типа на выбор в зависимости от вида исходных данных:

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

Фильтрация с помощью Временной Шкалы

К возможности фильтровать сводные таблицы срезами (slicers), появившейся в Excel 2010, добавили Временную шкалу (Timeline) — интерактивную графическую ось времени, которую можно вставить на вкладке Анализ (Analysis) . Выделив любой временной интервал на оси, мы получим в привязанной к ней сводной результаты именно за выбранный период:

Новый механизм расчета сводных таблиц (Data Object Model)

В Excel 2013 впервые реализована возможность строить сводные таблицы не на основе классического механизма кэша, как во всех прошлых версиях до этого. Теперь при построении сводной таблицы можно установить в нижней части диалога неприметную галочку Добавить эти данные в модель данных (Add to Data Object Model) и, тем самым, использовать для построения сводной «движок» Power Pivot, т.е. по-сути полноценную базу данных, встроенную в Excel:

Именно объектная модель позволяет сделать один из самых впечатляющих трюков этой версии — построить сводную сразу по нескольким диапазонам данных.

Связи и сводная по нескольким таблицам

На вкладке Данные (Data) появилась кнопка Отношения (Relationships) , позволяющая связать нескольких таблиц по ключевым полям (столбцам) с помощью вот такого диалога:

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

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

Функция подсчета количества уникальных элементов

Если вы строите сводную с использованием Data Object Model, то в списке функций производимых над данными (правой кнопкой мыши по полю — Параметры поля) добавится еще одна новая — Число различных элементов (Distinct Count) . Она вычислит не общее количество непустых элементов, как обычная функция Счет (Count) , а количество неповторяющихся представителей.

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

Экспресс-просмотр

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

Логическим продолжением и развитием этой идеи стала новая функция Экспресс-просмотр (Quick Explore) в сводных таблицах Excel 2013.

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

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

Недостатки

Из замеченного в процессе работы:

  • В сводных таблицах построенных на основе Объектной модели не работает группировка. Это хоть и не критично, но печально. Будем надеяться, что исправят.
  • Иногда названия столбцов в списке полей не соответствуют реальности, т.е. берутся не из ячеек шапки, а непонятно откуда. Помогает предварительное форматирование исходных диапазонов как «умных» таблиц.
  • Для выгрузки исходных данных из Объектной модели приходится использовать команду Данные — Подключения (Data — Connections) .

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

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

Итак, запускаем Excel… и загружаем такой список…

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

Выделите любую ячейку из этого списка:

Затем на вкладке Insert (Вставка) выберите команду PivotTable (Сводная таблица):

Появится диалоговое окно Create PivotTable (Создание сводной таблицы) с двумя вопросами для Вас:

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

Так как на предыдущем шаге мы уже выбрали одну из ячеек списка, то для создания сводной таблицы будет выделен весь список автоматически. Заметьте, что мы можем выбрать другой диапазон, другую таблицу и даже какой-нибудь внешний источник данных, например, таблицу базы данных Access или MS-SQL. К тому же нам необходимо выбрать, где разместить новую сводную таблицу: на новом листе или на одном из существующих. В данном примере мы выберем вариант – New Worksheet (На новый лист):

Excel создаст новый лист и разместит на нем пустую сводную таблицу:

Как только мы кликнем по любой ячейке в сводной таблице, появится ещё одно диалоговое окно: PivotTable Field List (Поля сводной таблицы).

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

Область Values (Значения), вероятно, самая важная из четырёх. То, какой заголовок помещён в эту область, определяет, по каким данным будут подводиться итоги (сумма, среднее, максимум, минимум и т.д.) Это, почти всегда, численные значения. Отличный кандидат на место в этой области – данные под заголовком Amount (Стоимость) нашей исходной таблицы. Перетащим этот заголовок в область Values (Значения):

Обратите внимание, что заголовок Amount теперь отмечен галочкой, а в области Values (Значения) появилась запись Sum of Amount (Сумма по полю Amount), указывающая на то, что столбец Amount просуммирован. Если мы посмотрим на саму сводную таблицу, то увидим сумму всех значений из столбца Amount исходной таблицы

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

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

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

Становится интересней! Наша сводная таблица начинает обретать форму…

Видите преимущества? За пару кликов мы создали таблицу, которую вручную пришлось бы создавать очень долго.

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

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

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

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

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

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

После того как будет определен источник данных и указано место расположения сводной таблицы, щелкните на кнопке ОК, и программа добавит пустую сетку для новой таблицы, а также откроет в правой части области рабочего листа панель Список полей сводной таблицы. Эта панель разделена на две части. Вверху находится список полей источника данных, которые можно добавить в сводную таблицу, а внизу — область, разделенная на четыре зоны: ФИЛЬТРЫ, СТРОКИ, СТОЛБЦЫ и ЗНАЧЕНИЯ.

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

  • ФИЛЬТРЫ. Здесь содержатся поля, позволяющие фильтровать данные таблицы. Так, например, если перетащить сюда поле года, то можно будет отображать в таблице сводные данные для каждого конкретного года, представленного в списке данных.
  • СТОЛБЦЫ. Здесь содержатся поля, определяющие данные, которые отображаются в столбцах сводной таблицы.
  • СТРОКИ. Здесь находятся поля, определяющие данные, которые отображаются в строках сводной таблицы.
  • ЗНАЧЕНИЯ. Здесь содержатся поля, определяющие, какие данные будут отображаться в ячейках таблицы, т.е. значения, консолидируемые в последнем столбце (по умолчанию суммируемые).

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

В этой сводной таблице в качестве фильтра из списка данных было выбрано поле Дата. Поле Категория я назначил меткам строк. В качестве значений были выбраны поля Расход и Доход.

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

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

  • Список полей. Служит для сокрытия и отображения списка полей на панели задач в правой части области рабочего листа.
  • +/- Кнопки. Используется для сокрытия и отображения кнопок сворачивания (-) и разворачивания (+) конкретных строк и столбцов, позволяющих временно удалять и отображать в сводной таблице конкретные значения.
  • Заголовки полей. Служит для сокрытия и отображения полей, назначаемых меткам строк и столбцов сводной таблицы.

Как работать с таблицей в Excel

С выходом новых версий программы работа в Эксель с таблицами стала интересней и динамичней. Когда на листе сформирована умная таблица, становится доступным инструмент «Работа с таблицами» — «Конструктор».

Здесь мы можем дать имя таблице, изменить размер.

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

Возможности динамических электронных таблиц MS Excel огромны. Начнем с элементарных навыков ввода данных и автозаполнения:

  1. Выделяем ячейку, щелкнув по ней левой кнопкой мыши. Вводим текстовое /числовое значение. Жмем ВВОД. Если необходимо изменить значение, снова ставим курсор в эту же ячейку и вводим новые данные.
  2. При введении повторяющихся значений Excel будет распознавать их. Достаточно набрать на клавиатуре несколько символов и нажать Enter.
  3. Чтобы применить в умной таблице формулу для всего столбца, достаточно ввести ее в одну первую ячейку этого столбца. Программа скопирует в остальные ячейки автоматически.
  4. Для подсчета итогов выделяем столбец со значениями плюс пустая ячейка для будущего итога и нажимаем кнопку «Сумма» (группа инструментов «Редактирование» на закладке «Главная» или нажмите комбинацию горячих клавиш ALT+»=»).

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

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

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

таблице продаж

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

Вставка — Сводная таблицаОК

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

ПроданоМенеджерМенеджерНазвания строкПроданообласть значений

Дополнительные параметры сортировкиСумма по полю проданоОК

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. Клиент – ID клиента, полное или короткое наименование.
  2. Товар – наименование товара или наименование группы товаров.
  3. Период – промежуток времени с единицами измерения (годы, кварталы, месяцы, дни), за который были собраны статистические показатели.
  4. Количество – количественные данные по продажам в соответствии с единицами измерения (шт., кг., л. и т.п.).
  5. Сумма – количество умножено на цены проданных товаров (данный показатель необходим для проведения сложных вычислений при планировании).

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

Пример таблицы с статистическими данными скопируйте из этого фрейма на пустой рабочий лист Excel:

На основе исходных данных построим сводную таблицу, в которую подтянем данные таблицы планирования. Если Ваши исходные данные находятся во внешней базе данных таких как SQL или Access, то можно сразу построить сводную таблицу подключившись непосредственно к внешним источникам данных. Но если данные уже экспортированы в таблицу Excel (так как в нашем случаи), тогда выделите диапазон ячеек A1:E61 и выберите инструмент: «ВСТАВКА»-«Сводная таблица». В появившемся окне «Создание сводной таблицы» просто Нажмите кнопку ОК не меня параметров, установленных по умолчанию.

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

Чтобы задать новое имя для новой сводной таблицы, сделайте ее активной и выберите инструмент: «РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ»-«АНАЛИЗ»-«Сводная таблица». И в поле «Имя:» введите текст «стАнализ». Потом просто нажмите клавишу Enter для подтверждения нового имени:

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

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

  • в полю СТРОКИ – значения Клиент и Год;
  • в полю КОЛОННЫ – значения Товар;
  • в полю ЗНАЧЕНИЯ – Количество.

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

Полезные советы по подбору источника данных

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

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

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

Важно, чтобы все числа были в числовом формате, а даты были действительно в формате даты. Иначе Excel будет трудно правильно сгруппировать и обработать данные

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

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

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

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

Откроется следующее диалоговое окно:

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

Будьте внимательны — первая строка указанного диапазона не должна быть пустой — в этом случае Excel сообщит об ошибке. Также мы советуем Вам обязательно создать заголовки для каждого столбца базовой таблицы — это сделает настройку сводной таблицы намного удобней.

Помимо выбора исходной таблицы Excel предоставляет возможность использовать в качестве источника данных базы данных и таблицы, созданные в других программах (Access, SQL Server и других).

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

Нажав кнопку Ок после настройки нужных нам условий, мы получаем следующий рабочий лист:

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

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

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

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

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

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

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

Настройка нашей таблицы должна выглядеть вот так:

Тогда наша сводная таблица будет иметь следующий вид:

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

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

Установите галочку, и Вы самостоятельно будете обновлять сводную таблицу, нажимая кнопку Обновить в нужный Вам момент.

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

Для этого достаточно в окне настройки отметить галочками два дополнительных поля — Марка телефона и Модель телефона, и перетянуть эти поля в область Фильтр отчета. Excel добавит в сводную таблицу два новых фильтра, которые помогут нам быстро узнать нужную информацию:

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

Форматирование таблицы в Excel

Для настройки формата таблицы в Экселе доступны предварительно настроенные стили. Все они находятся на вкладке “Конструктор” в разделе “Стили таблиц”:

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

Помимо цветовой гаммы, в меню “Конструктора” таблиц можно настроить:

  • Отображение строки заголовков – включает и отключает заголовки в таблице;
  • Строку итогов – включает и отключает строку с суммой значений в колонках;
  • Чередующиеся строки – подсвечивает цветом чередующиеся строки;
  • Первый столбец – выделяет “жирным” текст в первом столбце с данными;
  • Последний столбец – выделяет “жирным” текст в последнем столбце;
  • Чередующиеся столбцы – подсвечивает цветом чередующиеся столбцы;
  • Кнопка фильтра – добавляет и убирает кнопки фильтра в заголовках столбцов.
Рейтинг автора
5
Материал подготовил
Максим Иванов
Наш эксперт
Написано статей
129
Ссылка на основную публикацию
Похожие публикации