Отображение элементов и данных в сводных таблицах
Отображение или скрытие детальных данных сводной таблицы
Для того чтобы отобразить детальные данные, которые были скрыты ранее, или же скрыть имеющиеся нужно выделить элемент поля, детальные данные которого необходимо скрыть или показать.
Далее необходимо нажать на соответствующую кнопку «Отобразить детали» или «Скрыть детали», которые расположены на панели инструментов Сводные таблицы, или же в меню «Данные» в пункте «группа и структура». При появлении диалогового окна, выберите поле, для которого необходимо показать детали.
Отображение или скрытие элементов поля сводной таблицы
Если же необходимо отобразить или скрыть элемент поля, то для этого необходимо, во-первых, выделить поле, в котором необходимо отобразить или скрыть элементы. Во-вторых, нужно нажать кнопку «Поле» сводной таблицы на панели инструментов «Сводные таблицы» (см. рисунок). Далее, в появившемся окне, нужно выбирать элементы, которые необходимо скрыть или отобразить, в списке «Скрыть элементы».
Чтобы отобразить или скрыть пустые элементы поля исходных данных, нужно в окне «вычисление поле сводной таблицы» установить или снять флажок «Отображать пустые элементы».
Основные и промежуточные итоги сводной таблицы будут пересчитаны автоматически, и, кроме того, скрытие данных сводной таблицы не повлияет на исходные данные.
Отображение максимальных или минимальных элементов в поле сводной таблицы. Для отображения максимальных или минимальных элементов необходимо выделить поле, в котором в дальнейшем будут показаны элементы.[8]
После этого нужно нажать кнопку «Поле сводной таблицы» на панели инструментов «Сводные таблицы». В появившемся окне в списке необходимо выбрать пункт «минимуму» или «максимум» после чего нажать кнопку «Далее».
После этого, в новом окне установить переключатель «Параметры отображения» в положение «вручную», а так же выберите варианты наибольших или наименьших в раскрывающемся списке отображать, а также число отображаемых элементов.
После чего выберите поле, содержащее данные, которое необходимо использовать в качестве критерия для определения максимальных или минимальных элементов, в раскрывающемся списке с помощью поля.[6]
При обновлении сводной таблицы, изменении ее структуры или отображении других элементов поля страницы происходит пересчет отображения максимальных или минимальных элементов.
Если задано отображение максимальных или минимальных элементов поля, остальные элементы поля скрываются. Чтобы отобразить все элементы поля, необходимо установить переключатель Параметры отображения в положение вручную.
Отключение доступа к детальным сведениям сводной таблицы
Если установить указатель на ячейку области данных сводной таблицы и дважды нажать кнопку мыши, отобразится итоговый список исходных данных для этой ячейки. Иногда бывает полезно ограничить доступ к подробным сведениям, и для выполнения этой процедуры необходимо проделать следующее: сводный таблица еxcel
Нужно выделить ячейку в сводной таблице, для которой будут введены ограничения.
Выберите команду «Параметры» находящуюся в меню «Сводная таблица», панель инструментов «Сводные таблицы».
Снимите флажок «развертывание разрешено» в группе «Данные».
Изучив параметры отображения элементов перейдем к следующему разделу, в котором рассмотрим как можно группировать элементы в сводной таблице
Как отображать используемые поля
Итог: узнаете, как создать развернутый лист «Показать детали» из сводной таблицы, которая содержит только поля (столбцы), используемые в сводной таблице.
Уровень мастерства: Средний
В листе «Показать подробности» обычно отображаются все поля
Если дважды щелкнуть ячейку в области значений сводной таблицы (или щелкнуть правой кнопкой мыши> Показать подробности), в рабочую книгу добавится новый лист. Новый лист содержит все строки исходных данных для ячейки, которую мы дважды щелкнули.
Это полезно, когда вы связываете числа и хотите увидеть все строки, которые составляют конкретное число.
Лист сведений также содержит ВСЕ столбцы из диапазона исходных данных.
Арис, член сообщества Excel Campus, задал отличный вопрос, можем ли мы создать информационный лист, включающий ТОЛЬКО поля, используемые в сводной таблице?
Это не возможно напрямую в Excel, но мы можем использовать макрос для решения этой проблемы. Давайте посмотрим, как мы можем использовать VBA, чтобы сохранить кучу времени! ��
Макрос — Показать детали используемых полей
Следующий макрос группирует и скрывает или удаляет столбцы на листе «Показать детали», которые НЕ используются в сводной таблице. Это означает, что у нас останется лист подробностей показа, который содержит только поля (столбцы), которые используются в сводной таблице. Это дает нам гораздо более тонкую таблицу, которую легче читать.
Преимущество группировки и скрытия столбцов заключается в том, что их можно легко сделать видимыми, нажав клавишу 2 в верхнем левом углу, чтобы развернуть группы.
Как работает макрос?
Вот что происходит при запуске макроса:
- Макрос создает лист ShowDetails для активной ячейки в сводной таблице.
- Затем он просматривает каждый столбец в таблице (объект списка) нового листа.
- Он проверяет, используется ли столбец (поле) в какой-либо области в сводной таблице.
- Если столбец НЕ используется, он группирует столбец (столбцы также могут быть скрыты или удалены).
- Шаги 3 и 4 повторяются для каждого столбца.
- Контур столбца свернут, поэтому остаются видимыми только использованные столбцы.
- Ширина столбцов таблицы автоматически подбирается, что позволяет сохранить еще один шаг с отображением подробных листов.
Вот макрос, который вы можете скопировать и вставить в свою личную книгу макросов или в любой проект VBA.
Как запустить макрос
Макрос можно добавить в вашу личную книгу макросов и запустить в любом файле Excel, который вы открыли. Вы можете добавить кнопку на ленту, которая будет запускать макрос в любое время. Ознакомьтесь с моей бесплатной серией видео в Персональной книге макросов, чтобы узнать, как начать работу и создавать кнопки на ленте.
Мы также могли бы добавить кнопку в контекстное меню правой таблицы, чтобы запустить макрос. Я поделился похожим кодом в моей статье об автоматическом форматировании чисел по умолчанию для сводных таблиц, но, пожалуйста, оставьте комментарий ниже, если вам интересен код для добавления кнопки в меню, вызываемое правой кнопкой мыши.
Другой вариант — добавить код в событие приложения для события BeforeDoubleClick. Тогда макрос может автоматически запускаться при двойном щелчке ячейки в сводной таблице.
Есть много способов реализовать этот макрос, в зависимости от того, как часто вы хотите его использовать. Пожалуйста, оставьте комментарий ниже с любыми вопросами или предложениями о том, как его улучшить. Спасибо! ��
Поля сводной таблицы как отобразить
Как скрыть / показать список полей сводной таблицы в Excel?
Когда вы вставляете сводную таблицу, в правом разделе рабочего листа появляется список полей сводной таблицы. Иногда этот список полей сводной таблицы может скрывать данные в правой части листа. Теперь я расскажу, как скрыть список полей сводной таблицы в Excel.
Скрыть / показать список полей сводной таблицы щелчком правой кнопкой мыши
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Чтобы скрыть список полей сводной таблицы, достаточно одного шага.
Щелкните правой кнопкой мыши любую ячейку сводной таблицы и выберите Скрыть список полей в контекстном меню.
Если вы хотите снова отобразить список полей, щелкните правой кнопкой мыши сводную таблицу и выберите Показать список полей.
Скрыть / отобразить список полей сводной таблицы с помощью VBA
Если вас интересует VBA, вы можете скрыть или показать список полей сводной таблицы, выполнив следующие действия:
1. Нажмите Alt + F11 ключи для открытия Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии скопируйте приведенный ниже код в окно.
VBA: скрыть список полей.
3. Нажмите Run или F5, то список полей сводной таблицы скрыт.
Функции: Для отображения списка полей с VBA вы можете использовать следующий код:
Как сделать сводную таблицу в Excel: пошаговая инструкция
Сводные таблицы – один из самых эффективных инструментов в MS Excel. С их помощью можно в считанные секунды преобразовать миллион строк данных в краткий отчет. Помимо быстрого подведения итогов, сводные таблицы позволяют буквально «на лету» изменять способ анализа путем перетаскивания полей из одной области отчета в другую.
Cводная таблица в Эксель – это также один из самых недооцененных инструментов. Большинство пользователей не подозревает, какие возможности находятся в их руках. Представим, что сводные таблицы еще не придумали. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте только 4 позиции. Продукцию регулярно покупает пара десятков клиентов, которые находятся в разных регионах. Каждая сделка заносится в базу данных и представляет отдельную строку.
Ваш директор дает указание сделать краткий отчет о продажах всех товаров по регионам (областям). Решить задачу можно следующим образом.
Вначале создадим макет таблицы, то есть шапку, состоящую из уникальных значений товаров и регионов. Сделаем копию столбца с товарами и удалим дубликаты. Затем с помощью специальной вставки транспонируем столбец в строку. Аналогично поступаем с областями, только без транспонирования. Получим шапку отчета.
Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.
Вы справились с заданием и показываете отчет директору. Посмотрев на таблицу, он генерирует сразу несколько замечательных идей.
— Можно ли отчет сделать не по выручке, а по прибыли?
— Можно ли товары показать по строкам, а регионы по столбцам?
— Можно ли такие таблицы делать для каждого менеджера в отдельности?
Даже если вы опытный пользователь Excel, на создание новых отчетов потребуется немало времени. Это уже не говоря о возможных ошибках. Однако если вы знаете, как сделать сводную таблицу в Эксель, то ответите: да, мне нужно 5 минут, возможно, меньше.
Рассмотрим, как создать сводную таблицу в Excel.
Создание сводной таблицы в Excel
Открываем исходные данные. Сводную таблицу можно строить по обычному диапазону, но правильнее будет преобразовать его в таблицу Excel. Это сразу решит вопрос с автоматическим захватом новых данных. Выделяем любую ячейку и переходим во вкладку Вставить. Слева на ленте находятся две кнопки: Сводная таблица и Рекомендуемые сводные таблицы.
Если Вы не знаете, каким образом организовать имеющиеся данные, то можно воспользоваться командой Рекомендуемые сводные таблицы. Эксель на основании ваших данных покажет миниатюры возможных макетов.
Кликаете на подходящий вариант и сводная таблица готова. Остается ее только довести до ума, так как вряд ли стандартная заготовка полностью совпадет с вашими желаниями. Если же нужно построить сводную таблицу с нуля, или у вас старая версия программы, то нажимаете кнопку Сводная таблица. Появится окно, где нужно указать исходный диапазон (если активировать любую ячейку Таблицы Excel, то он определится сам) и место расположения будущей сводной таблицы (по умолчанию будет выбран новый лист).
Обычно ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.
Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.
В верхней части панели находится перечень всех доступных полей, то есть столбцов в исходных данных. Если в макет нужно добавить новое поле, то можно поставить галку напротив – эксель сам определит, где должно быть размещено это поле. Однако угадывает далеко не всегда, поэтому лучше перетащить мышью в нужное место макета. Удаляют поля также: снимают флажок или перетаскивают назад.
Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.
Область значений – это центральная часть сводной таблицы со значениями, которые получаются путем агрегирования выбранным способом исходных данных.
В большинстве случае агрегация происходит путем Суммирования. Если все данные в выбранном поле имеют числовой формат, то Excel назначит суммирование по умолчанию. Если в исходных данных есть хотя бы одна текстовая или пустая ячейка, то вместо суммы будет подсчитываться Количество ячеек. В нашем примере каждая ячейка – это сумма всех соответствующих товаров в соответствующем регионе.
В ячейках сводной таблицы можно использовать и другие способы вычисления. Их около 20 видов (среднее, минимальное значение, доля и т.д.). Изменить способ расчета можно несколькими способами. Самый простой, это нажать правой кнопкой мыши по любой ячейке нужного поля в самой сводной таблице и выбрать другой способ агрегирования.
Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.
Область столбцов – аналогично строкам показывает уникальные значения выбранного поля, только по столбцам. Названия столбцов – это также обычно качественный признак. Например, годы и месяцы, группы товаров.
Область фильтра – используется, как ясно из названия, для фильтрации. Например, в самом отчете показаны продукты по регионам. Нужно ограничить сводную таблицу какой-то отраслью, определенным периодом или менеджером. Тогда в область фильтров помещают поле фильтрации и там уже в раскрывающемся списке выбирают нужное значение.
С помощью добавления и удаления полей в указанные области вы за считанные секунды сможете настроить любой срез ваших данных, какой пожелаете.
Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».
В результате мы получаем настоящую сводную таблицу.
На ее построение потребовалось буквально 5-10 секунд.
Работа со сводными таблицами в Excel
Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.
Заменим выручку на прибыль.
Товары и области меняются местами также перетягиванием мыши.
Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.
На все про все ушло несколько секунд. Вот, как работать со сводными таблицами. Конечно, не все задачи столь тривиальные. Бывают и такие, что необходимо использовать более замысловатый способ агрегации, добавлять вычисляемые поля, условное форматирование и т.д. Но об этом в другой раз.
Источник данных сводной таблицы Excel
Для успешной работы со сводными таблицами исходные данные должны отвечать ряду требований. Обязательным условием является наличие названий над каждым полем (столбцом), по которым эти поля будут идентифицироваться. Теперь полезные советы.
1. Лучший формат для данных – это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.
2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты по месяцам в отдельных столбцах.
3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.
4. Применяйте правильное форматирование к полям. Числа должны быть в числовом формате, даты должны быть датой. Иначе возникнут проблемы при группировке и математической обработке. Но здесь эксель вам поможет, т.к. сам неплохо определяет формат данных.
В целом требований немного, но их следует знать.
Обновление данных в сводной таблице Excel
Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши
или
через команду во вкладке Данные – Обновить все.
Так сделано специально из-за того, что сводная таблица занимает много места в оперативной памяти. Чтобы расходовать ресурсы компьютера более экономно, работа идет не напрямую с источником, а с кэшем, где находится моментальный снимок исходных данных.
Зная, как делать сводные таблицы в Excel даже на таком базовом уровне, вы сможете в разы увеличить скорость и качество обработки больших массивов данных.
Ниже находится видеоурок о том, как в Excel создать простую сводную таблицу.