Как изменить вид сводной таблицы в excel
Использование стандартных фильтров в Excel не всегда удобно и ограничено одной таблицей. Для увеличения продуктивности в Excel существуют удобные инструменты, как Временна шкала и Срез . Они позволяют быстро выбирать данные по заданным параметрам, в том числе для нескольких таблиц сразу.
Срезы можно использовать для "Умных" и Сводных таблиц, Временную шкалу только для Сводных.
Сводная таблица является по сути конструктором используя который можно быстро собрать нужные показатели по заданным критериям. Такие таблицы удобно применять для построения различных отчетов. Подробнее о Сводных таблицах смотрите статью: Сводные таблицы в Excel: как создать?
Срез представляет собой фильтр, вынесенный в отдельный графический элемент. Добавить один или несколько Срезов, для "Умной" или Сводной таблицы, можно на вкладке Анализ ► Вставить срез :
Добавление Среза на вкладке Анализ. Для появления вкладки щелкните в любом месте "Умной" или Сводной таблицы, для которой хотите создать срез. Добавление Среза на вкладке Анализ. Для появления вкладки щелкните в любом месте "Умной" или Сводной таблицы, для которой хотите создать срез.В появившемся окне выберите столбец или несколько столбцов по которым будем построен фильтр:
Либо, на закладке Вставка ► Срез .
Временная шкала
Временная шкала параметр динамического фильтра, позволяющий легко фильтровать данные по периоду времени на одном из четырех уровней годы , кварталы , месяцы или дни .
Чтобы добавить шкалу на лист, выберите на вкладке Анализ ► Вставить временную шкалу:
Добавление Временной шкалы на вкладке Анализ. Исходные данные должны содержать столбец с датами. Добавление Временной шкалы на вкладке Анализ. Исходные данные должны содержать столбец с датами.Так же можно добавить через вкладку Вставка ► Временная шкала .
В открывшемся окне установите галку на против Дата ► ОK:
Чтобы производить фильтрацию Временной шкалой:
- Нажмите на стрелку рядом с отображаемым временным уровнем и выберите нужный вариант ( год , квартал , месяц или день ):
- Перетащите полосу прокрутки временной шкалы к периоду времени, который вы хотите выбрать;
- В элементе управления отрезком времени нажмите левой кнопкой на плитку периода времени и зажав перетащите ее, будет выбран период.
Чтобы очистить Временную шкалу или Срез, нажмите на шкале в правом верхнем углу кнопку Очистить фильтр .
Как и Cрез для фильтрации данных, Временную шкалу можно добавить один раз и затем использовать в любой момент для изменения диапазона времени Сводной таблицы.
Настройка вида Временной шкалы и Среза
Временную шкалу или Срез можно переместить расположив в более удобном месте, изменить их размер или стиль.
- Чтобы переместить фигуру, просто перетащите ее в нужное место на листе.
- Чтобы изменить размер, нажмите на фигуру, затем выберите нужный размер, перетаскивая маркеры размера или задав его на вкладке Параметры .
- Чтобы изменить стиль, нажмите на фигуру, отобразится меню Инструменты временной шкалы , выберите нужный стиль на вкладке Параметры .
Ис пользование Временной шкалы и Срезов для нескольких Сводных таблиц
Если ваши Сводные таблицы имеют один источник данных, вы можете использовать одну Временную шкалу и несколько Срезов для фильтрации для нескольких таблица.
Щелкните на Временную шкалу или Срез, а затем выберите на вкладке Параметры ► Подключения к отчетам . В открывшемся окне выберите Сводные таблицы, которые вы хотите добавить:
Для использования Временной шкалы для "Умной" таблицы преобразуйте её в Сводную, на вкладке Конструктор ► Сводная таблица или Вставка ► Сводная таблица . Это очень удобно и даёт больше вариантов для маневра.
Подробнее о Временной шкале и Срезах смотрите в видео ⬇⬇⬇
Очень часто при формировании отчётов (аналитики) основанных на больших объемах данных приходиться сводить в одну таблицу информацию с нескольких листов книги «Excel» .
В идеале для сбора и аналитики информации с разных листов «Эксель» желательно сделать сводную таблицу, которая будет брать данные из нескольких источников (листов книги).
Такую таблицу можно выполнить двумя разными способами.
- Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.
- Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .
Рассмотрим первый способ.
Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.
Шаг первый.
Необходимо добавить в ленту инструментов кнопку мастера создания сводных таблиц и диаграмм.
Для этого следует кликнуть правой кнопкой мыши по ленте (панели инструментов) и выбрать из выпадающего меню пункт «Настройка ленты»
Настройка ленты
либо войти во вкладку
«Файл» => «Параметры» => «Настройка ленты».
Далее в настройках ленты из выпадающего списка под надписью «Выбрать команды» выбрать пункт «Все команды». В окне ниже отобразятся все возможные команды, которые можно разместить на панели, в алфавитном порядке.
Из списка выбираем «Мастер сводных таблиц и диаграмм»
Добавление мастера сводных таблиц
В правом окне при помощи кнопки «Создать группу» создаем новую группу инструментов. Для группы можно выбрать удобное для Вас наименование. Например, «Своя группа». Можно выбрать на какой вкладке будет создана группа. В своем примере я выбрал вкладку «Главная».
Когда группа создана, выделите ее курсором, выделите курсором «Мастер сводных таблиц и диаграмм» в левом окне и нажмите кнопку «Добавить >>».
После нажмите «Ок».
Теперь на главной вкладке панели инструментов находится инструмент «Мастер сводных таблиц и диаграмм».
Мастер сводных на панели
Шаг второй. Построение сводной таблицы из нескольких источников данных.
- Кликнуть по кнопке мастера построения сводных таблиц.
- На первом окне поставить флажок, напротив «в нескольких диапазонах консолидации» и флажок напротив «сводная таблица»
Второй способ.
Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .
Данный способ заключается в использовании запроса надстройки Power Query.
О данной надстройке рассказывалось в статье: «Power Query» в «Excel» — что это?
Создание запроса Power Query для сведения нескольких страниц книги в одну таблицу.
Шаг первый.
Нужно создать два запроса, каждый из которых будет брать информацию из отдельной таблицы.
Шаг два.
Для этого во вкладке Power Query нужно нажать кнопку «Из таблицы» и указать в появившемся окне диапазон – источник данных. После чего нажать «Ок».
Power Query из таблицы
Шаг три.
Когда создан второй запрос, нужно во вкладке Power Query кликнуть по кнопке «Слияние запросов» и настроит в появившемся окне вид получившейся общей таблицы.
Слияние запросов
Шаг четыре.
Когда вид настроен, нужно нажать кнопку «Закрыть и загрузить.»
Надстройка Power Query соберет данные с двух листов и соединит их в одной таблице.
Всем привет, друзья! Как и обещал, продолжаю рассказывать про работу со сводными таблицами. Если вы пропустили начало, почитать можно здесь - Что такое сводная таблица и почему вам это нужно? . Сегодня будет теория.
Прежде чем мы перейдем к анализу данных с помощью нашей таблицы, давайте разберемся, какие гайки и болты составляют сводную таблицу Excel. Напомню, что в первой части мы создали вот такой образ.
Для эффективного использования сводной таблицы важно знать компоненты, которые создают сводную таблицу. В этой статье вы узнаете что такое:
- Список полей
- Сводный кеш
- Область значений
- Область строк
- Область столбцов
- Область фильтров
Список полей
Как именно будет выглядеть ваша сводная таблица, зависит только от вас. Как вы ее настроите так и будет. А настроить ее можно в области под названием - Список полей.
В этой области полностью настраивается видопредставление таблицы: в каком порядке будут показываться строки или столбцы, какие значения будут представлены в фильтре, а также каким образом будут выводиться результаты, т.е. суммироваться или вычисляться соотношения.
Сводный кеш
Как только вы создаете сводную таблицу с использованием данных, что-то происходит в верхней части. Excel делает снимок данных и сохраняет их в своей памяти. Этот снимок называется сводным кешем . Т.е. когда вы создаете различные представления с помощью сводной таблицы, Excel не возвращается к источнику данных, а использует сводный кэш для быстрого анализа данных и предоставления сводки / результатов.
Причина создания сводного кеша - оптимизация работы сводной таблицы. Даже когда у вас есть тысячи строк данных, сводная таблица очень быстро суммирует данные. Вы можете перетаскивать элементы в поля строк / столбцов / значений / фильтров, и результаты будут мгновенно обновлены.
Примечание. Одним из недостатков сводного кеша является то, что он увеличивает размер вашей книги. Поскольку это копия исходных данных, и когда вы создаете сводную таблицу, копия этих данных сохраняется в сводном кэше.
Область значений.
Область значений - это то, что содержит вычисления / значения. На основе набора данных, показанного в первой части, если вы хотите быстро рассчитать общий объем продаж по регионам за каждый год, вы можете получить сводную таблицу. Область, выделенная оранжевым цветом, - это и есть область значений.
Читайте также: