95 подписчиков

101 Расширенные советы и рекомендации по составлению сводных таблиц, которые вам нужно знать

18 августа 202318 авг 2023

52 мин

Оглавление

Используйте таблицу для своих исходных данных
Изменение исходных данных
Отключите окно полей сводной таблицы

Оригинал статьи: https://www.howtoexcel.org/pivot-table-tips-and-tricks/

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

Вы узнаете все советы, которыми пользуются профессионалы, так что приготовьтесь к очень-очень длинному посту!

Ссылка на файлы: https://disk.yandex.ru/d/8jURCWGP2hipgA

Ссылка на шаблоны: https://disk.yandex.ru/d/egwe_nPON3kAJA

Загрузите файл примера с данными, используемыми в этом посте, чтобы следовать дальше.

Ваши исходные данные должны быть в табличном формате

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

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

Используйте таблицу для своих исходных данных

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

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

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

Выберите свои данные.
Перейдите на вкладку "Вставить" и нажмите кнопку "Таблица" в разделе "Таблицы" или используйте сочетание клавиш Ctrl + T.
Нажмите кнопку OK.
Выбрав активную ячейку внутри таблицы, перейдите на вкладку Инструменты оформления таблицы.
Измените название таблицы в разделе Свойства и нажмите Ввод.

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

Изменение исходных данных

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

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

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

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

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

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

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

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

Вы также можете закрыть окно, используя крестик в правом верхнем углу.

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

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

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

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

Раздел полей и области раздела сложены
Параллельный раздел полей и областей
Только раздел полей
Только раздел областей (2 на 2)
Только раздел областей (1 на 4)

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

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

Выберите в меню опцию Сортировать от А до Я. Теперь ваши поля будут отображаться в порядке убывания!

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

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

Переместить – это позволит вам отключить окно и перемещать его по электронной таблице.
Размер – это позволяет регулировать ширину и высоту (при отключении) окна.
Закрыть – это позволяет вам закрыть окно. Вы можете открыть его снова с помощью команды "Вкладка "Анализ" > "Список полей".

Сочетание клавиш мастера сводных таблиц и диаграмм

Используйте сочетание клавиш Alt + D + P, чтобы открыть мастер сводных таблиц и диаграмм. Здесь вы пройдете через шаги по настройке сводной таблицы или сводной диаграммы, выберете свои данные и местоположение для вашей новой сводной таблицы или диаграммы.

Создайте сводную таблицу с помощью сочетания клавиш

Используйте сочетание клавиш ribbon command Alt + N + V для быстрого создания сводной таблицы.

Показывать детали, стоящие за значением

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

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

Отключите отображение сведений, чтобы избежать случайного двойного щелчка

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

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

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

Замените пустые ячейки

Эта сводная таблица содержит пустые ячейки, поскольку наши исходные данные не содержат записей для этих комбинаций измерений. Например, нет данных для Arthur James и France, поэтому пересечение строки Arthur James и столбца France пустое. Мы можем изменить настройки, чтобы вместо пробела отображалось что-то вроде нуля или текста с надписью “N / A”.

Щелкните левой кнопкой мыши в любом месте сводной таблицы, затем выберите Параметры сводной таблицы.

В меню Параметры сводной таблицы

Перейдите на вкладку Макет и формат.
Установите флажок Показывать для пустых ячеек и введите значение, которое вы хотели бы отобразить для пробелов. В нашем примере мы заменим пустые ячейки на 0.
Нажмите кнопку OK.

Теперь ранее пустые ячейки были заменены нулями.

Показывать элементы без данных

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

Щелкните правой кнопкой мыши и выберите в меню Настройки поля.

Установите флажок Показывать элементы без данных и нажмите кнопку OK.

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

Удалите элементы из фильтра с помощью сочетания клавиш

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

Добавьте текущую выборку в фильтр

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

Выберите свой первый набор элементов для фильтрации вручную или с помощью окна поиска (тем же методом, что и на шагах 2 и 3).
Используйте поле Поиска для поиска, а затем выберите второй набор элементов для фильтрации.
Установите флажок с пометкой Добавить текущую выборку в фильтр, который появится при использовании окна поиска.
Нажмите кнопку OK.
Теперь, просмотрев фильтр, вы увидите, что в фильтр включены как ваши выборки из шага 1, так и выборки из шага 2.

Используйте переключатель "Выбрать все фильтры"

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

Отложить обновление макета

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

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

Добавляйте или удаляйте поля с помощью флажка

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

Фильтруйте поля в окне "Поля сводной таблицы"

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

Переименуйте любую метку

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

Переименуйте метку с конечным пробелом

Возможно, вы захотите изменить заголовок столбца, подобного нашему столбцу “Итого”, который отображается как “Сумма итога”, чтобы в сводной таблице отображалось просто “Итого”. К сожалению, это невозможно сделать, поскольку “Итого” уже существует в исходных данных. Если вы попытаетесь это сделать, вы получите всплывающее предупреждение “Название поля сводной таблицы уже существует“. Мы можем обойти это, добавив пробел в конец имени. Это будет считаться другим именем, но визуально оно будет выглядеть так же, как старое название поля.

Группируйте элементы в поле

Вы можете сгруппировать элементы в поле вместе для дальнейшего обобщения ваших данных. Выделите элементы, а затем щелкните правой кнопкой мыши и выберите Group из меню. Вы можете выбрать несколько несмежных элементов поля, удерживая клавишу Ctrl во время выбора. По умолчанию сгруппированным именем для набора элементов будет Group1, Group2, Group3 и т. Д… Но вы можете изменить их на что-то более значимое.

Вы также можете разгруппировать сгруппированное поле. Выберите его и щелкните правой кнопкой мыши, затем выберите Разгруппировать в меню.

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

Группируйте элементы в поле с помощью сочетания клавиш

Вы можете быстро сгруппировать элементы в поле, выделив элементы, которые хотите сгруппировать, а затем нажав клавишу со стрелкой Alt + Shift + Вправо.

Разгруппируйте сгруппированные элементы с помощью сочетания клавиш

Вы можете быстро разгруппировать сгруппированные элементы, выделив сгруппированный элемент и затем нажав клавишу со стрелкой Alt + Shift + Влево.

Групповые даты

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

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

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

Когда вы щелкаете правой кнопкой мыши по полю даты и выбираете в меню "Группировать", вам будут представлены различные варианты группировки.

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

Группируйте числа в диапазоны

Excel также может группировать числовые поля. Это может быть удобно, если вы хотите узнать что-то вроде “Какая часть моих продаж приходится на заказы стоимостью менее 50 долларов?“.

Если я помещу поле "Итог" как в область "Строки", так и в область "Значения", я не получу ничего полезного.

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

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

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

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

Придайте сводной таблице другой стиль

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

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

Изучите различные варианты стиля

Включайте или выключайте различные параметры стиля сводной таблицы. Перейдите на вкладку "Дизайн" на ленте и найдите раздел "Параметры стиля сводной таблицы". Параметры стиля

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

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

Обновите свои данные

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

Выберите ячейку в сводной таблице, чтобы активировать вкладки "Инструменты сводной таблицы"...........

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

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

Обновите с помощью сочетания клавиш

Обновите подключение к исходным данным активной сводной таблицы с помощью сочетания клавиш Alt + F5.

Обновите все с помощью сочетания клавиш

Обновите все подключения к данным для всех сводных таблиц в книге с помощью сочетания клавиш Ctrl + Alt + F5.

Автоматическое обновление данных при открытии рабочей книги

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

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

В меню "Параметры сводной таблицы" перейдите на вкладку "Данные" и установите флажок "Обновлять данные при открытии файла". Поле "Файл". Это обновит все сводные таблицы в книге, которые подключены к одному источнику данных.

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

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

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

Вы также можете выбрать только область Меток или значений отсюда.

Очистите все фильтры

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

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

Перейдите на вкладку Анализ.
Нажмите кнопку "Очистить" в разделе "Действия".
Выберите в меню Очистить фильтры.

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

Очистите всю сводную таблицу

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

Перейдите на вкладку Анализ.
Нажмите кнопку "Очистить" в разделе "Действия".
Выберите в меню Очистить все.

Теперь ваша сводная таблица будет в исходном пустом состоянии со всеми удаленными полями и фильтрами.

Очистите старые элементы полей

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

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

Теперь при обновлении старые фантомные элементы больше не будут отображаться.

Форматирование чисел

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

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

Теперь при обновлении старые фантомные элементы больше не будут отображаться.

Форматирование чисел

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

Разворачивать или сворачивать заголовки полей

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

Щелкните правой кнопкой мыши на поле, которое вы хотите развернуть или свернуть, и выберите Развернуть / Свернуть в меню.

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

Дважды щелкните, чтобы развернуть или свернуть заголовки полей

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

Добавление или удаление кнопок развертывания или свертывания

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

Чтобы добавить эти кнопки, выберите сводную таблицу, перейдите на вкладку "Анализ" и нажмите кнопку "+ /- Buttons" в разделе "Показать".

Автоматическое создание сводной таблицы для каждого элемента в фильтре

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

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

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

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

Разрешить несколько фильтров для каждого поля

В Excel доступны два типа фильтров для поля сводной таблицы: фильтр меток и фильтр значений. Допустим, вы хотели отфильтровать эту сводную таблицу по всем проданным товарам, которые начинаются с “P” (используя фильтр этикеток) и имеют общую стоимость более 20 долларов США (используя фильтр значений), при настройках по умолчанию невозможно использовать оба фильтра одновременно. Мы можем обновить настройки, чтобы разрешить это.

Выберите свою сводную таблицу и перейдите на вкладку "Анализ" на ленте и нажмите кнопку "Параметры" в разделе "Сводная таблица".

Включите несколько фильтров в диалоговом окне "Параметры сводной таблицы".

Перейдите на вкладку Итоги и фильтры.
Установите флажок Разрешить несколько фильтров для каждого поля.
Нажмите кнопку OK.

Теперь вы сможете использовать как фильтр меток, так и фильтр значений одновременно в одном поле.

Получите список уникальных значений из поля

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

Подсчитайте вхождение элемента в поле

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

Удалите исходные данные

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

Сортируйте элементы в алфавитном порядке по возрастанию или убыванию

Сортируйте элементы в алфавитном порядке по возрастанию или по убыванию. Щелкните левой кнопкой мыши на значке фильтра и выберите Сортировать от А до Я по возрастанию или Сортировать от Z до A по убыванию.

Сортировка элементов вручную

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

Щелкните левой кнопкой мыши и перетащите элемент в новое положение. Вы увидите большую зеленую полосу, указывающую, где будет размещен элемент.

Переместите элемент в его новое положение.

Сортируйте элементы в соответствии с соответствующим значением

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

В меню фильтр выберите Дополнительные параметры сортировки.

Выберите либо по возрастанию (от А до Я), либо по убыванию (от Z до A) затем выберите одно из полей значений в вашей сводной таблице, а затем нажмите кнопку OK.

Создайте пользовательский порядок сортировки

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

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

Выберите НОВЫЙ СПИСОК в поле Пользовательские списки.
Введите свой список элементов полей, отображаемых в том порядке, в котором вы хотите, чтобы они были отсортированы в вашей сводной таблице.
Нажмите кнопку Добавить, чтобы добавить свой список.
Нажмите кнопку Ok.

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

Вставьте пустую строку после каждого элемента

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

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

Дважды щелкните, чтобы открыть настройки поля значений

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

Подсчитывайте отдельные элементы

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

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

В настройках поля значения выберите Различное количество, чтобы суммировать поле значения, и нажмите кнопку OK.

Теперь в значениях будет отображаться различное количество. Обратите внимание, что общее количество теперь отражает, что у нас есть 7 различных имен клиентов в наших данных из 50 заказов.

Скрыть выбранные элементы

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

Выберите элементы, которые вы хотите скрыть с помощью фильтра. Вы можете использовать Ctrl для выбора несмежных элементов. Затем щелкните правой кнопкой по выбранным элементам.
Выберите в меню "Фильтр".
Выберите Скрыть выбранные элементы в подменю.

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

Сохраняйте выбранные элементы

Аналогично скрытию выбранных элементов, вы можете сохранить только выбранные элементы с фильтром.

Выберите элементы, которые вы хотите сохранить в своем фильтре. Вы можете использовать Ctrl для выбора несмежных элементов. Затем щелкните правой кнопкой по выбранным элементам.
Выберите в меню "Фильтр".
Выберите в меню Сохранить только выбранные элементы.

Измените макет

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

Отображать в компактной форме
Показать в схематичной форме
Отображать в табличной форме

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

Компактная форма будет содержать все поля строк в одном столбце в иерархической структуре.
Форма Outline по-прежнему будет иметь иерархическую структуру, но каждое поле строки будет находиться в отдельном столбце сводной таблицы.
Табличная форма не будет иметь иерархической структуры, и каждое поле строки будет находиться в отдельном столбце сводной таблицы.

Повторите все метки элементов

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

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

Включите или выключите общие итоги

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

Перейдите на вкладку "Дизайн" и выберите команду "Общие итоги" в разделе "Макет". Выберите один из четырех вариантов отображения общих итогов.

Отключено для строк и столбцов (общие итоги отображаться не будут)
Вкл. для строк и столбцов
Включено только для строк
Включено только для столбцов

Включение или выключение промежуточных итогов

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

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

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

Не показывать промежуточные итоги
Показать все промежуточные итоги внизу группы
Показать все промежуточные итоги в верхней части группы

Отключите GETPIVOTDATA

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

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

Добавьте второе поле в область значений

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

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

Каждый раз, когда вы добавляете поле в область "Значения", в конце ему добавляется порядковый номер, но помните, что вы можете изменить эти названия. Затем вы можете изменить тип суммирования, чтобы вместо суммы отображалось количество, среднее, Максимальное, минимальное значение, дисперсия или стандартное отклонение. Это позволит вам одновременно суммировать данные по полю множеством различных способов.

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

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

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

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

Добавьте цветовые шкалы

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

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

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

Добавление наборов значков

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

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

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

Правила добавления выделенных ячеек

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

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

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

Добавьте выделенное верхнее или нижнее форматирование N

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

10 лучших позиций.
Лучшие 10 процентов.
Нижние 10 пунктов.
Нижние 10 процентов.
Выше среднего.
Ниже среднего.

Хотя в этих параметрах указаны верхние и нижние 10, число может быть выбрано по желанию.

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

Форматируйте числа в виде невидимого текста

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

Щелкните правой кнопкой мыши в любом месте поля, которое вы хотите отформатировать, и выберите в меню Числовой формат. В диалоговом окне "Формат ячеек" выберите "Пользовательский" из "Категории", а затем введите три точки с запятой ;;; в область "Тип" и нажмите "ОК". Данные по-прежнему будут существовать в вашей сводной таблице, но они просто не будут видны!

Используйте настройки условного формата для удаления текста

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

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

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

Для наборов значков установите флажок Показывать только значок.

Запретить изменение ширины столбца при обновлении

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

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

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

Добавьте вычисляемое поле

Добавление вычисляемого поля в сводную таблицу эквивалентно добавлению нового столбца к исходным данным для выполнения вычисления на основе других данных. Например, наши данные содержат общую стоимость и общую сумму для каждого заказа. Если мы хотим рассчитать маржу прибыли по каждому заказу, мы могли бы добавить еще один столбец с расчетной маржей прибыли = 1 – (Общая стоимость / итого) или мы можем добавить вычисляемое поле.

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

Выберите свою сводную таблицу и перейдите на вкладку "Анализ" на ленте и нажмите кнопку "Поля, элементы и наборы", расположенную в разделе "Вычисления". Затем выберите Вычисляемое поле в меню.

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

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

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

Удаление вычисляемого поля

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

Удалите вычисляемое поле из диалогового окна Вставка вычисляемого поля.

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

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

Вставьте вычисляемое поле с помощью сочетания клавиш

Вы можете быстро открыть диалоговое окно Вставить вычисляемое поле, чтобы создать новое вычисляемое поле или отредактировать существующее вычисляемое поле, используя сочетание клавиш Ctrl + Shift + +.

Добавьте вычисляемый элемент

Если добавление вычисляемого поля похоже на добавление нового столбца к исходным данным, то добавление вычисляемого элемента похоже на добавление новой строки.

Допустим, у нас есть простая таблица, которая показывает проданный продукт вместе с общим объемом продаж. Наш столбец "Общее" в данных не включает никаких налогов, но существует 15%-ный налог на стулья, который мы должны включить в наш анализ. Нет проблем, мы можем добавить это с вычисляемым элементом!

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

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

Я назвал расчет налога на стул, и формула рассчитает 15% от суммируемого значения.

Теперь мы видим, что в нашем поле "Проданный товар" появилась новая строка под названием "Налог на стулья", значение которой составляет 15% от стоимости стула............................ Обратите внимание, что эта новая строка вносит свой вклад в общий итог.

Замените ошибки

Если вы создаете вычисляемое поле с помощью операции деления, подобной нашему расчету нормы прибыли, то, возможно, вы увидите некоторые #DIV / 0! ошибки (деление на ноль). Вы можете заменить их числом, например 0, или каким-либо текстом по вашему выбору, чтобы сделать таблицу более презентабельной. Вид этих ошибок не вселит уверенности в вашу аудиторию, поэтому лучше заменить их чем-то более убедительным.

Выберите свою сводную таблицу и перейдите на вкладку "Анализ" и выберите "Параметры" в разделе "Сводная таблица".

Включите опцию значений ошибок.

Перейдите на вкладку Макет и формат.
Установите флажок Показывать значения ошибок и введите значение или некоторый текст.
Нажмите кнопку OK.

Теперь ваша сводная таблица будет выглядеть намного презентабельнее.

Добавляйте связи между таблицами

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

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

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

Создайте сводную диаграмму

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

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

Вы можете превратить свои сводные таблицы во множество различных типов диаграмм.

Столбчатые диаграммы
Линейные диаграммы
Круговые диаграммы
Столбчатые диаграммы
Диаграммы областей
Радарные диаграммы

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

К ним также можно получить доступ на вкладке "Вставка" в разделе "Диаграммы" с помощью команды "Сводная диаграмма".

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

Вставьте сводную диаграмму с помощью сочетания клавиш

Выберите ячейку внутри сводной таблицы и нажмите Alt + F1, чтобы быстро добавить сводную диаграмму на тот же лист, что и ваша сводная таблица.

Вы можете использовать альтернативные сочетания клавиш командной строки ribbon: Alt + N + SZ

Добавьте срез

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

Перейти на анализ вкладку в ленту и выберите Вставить срез под фильтр разделе.

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

Теперь вы можете выполнять фильтрацию по любой комбинации элементов из вашего слайсера.

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

Добавьте временную шкалу

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

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

Перейти на анализ вкладку в ленту и выберите Вставить график под фильтр разделе.

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

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

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

Скрыть все кнопки полей на сводной диаграмме

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

Щелкните правой кнопкой мыши на любой из кнопок на диаграмме и выберите Скрыть кнопки всех полей на диаграмме.

Подключайте срезы или временные рамки к нескольким сводным таблицам

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

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

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

Измените количество столбцов в срезе

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

Щелкните правой кнопкой мыши на срезе, а затем выберите в меню Размер и свойства.

В окне "Срез формата" в разделе "Положение и макет" установите желаемое количество столбцов............

Теперь вы можете разместить то же количество элементов в меньшем пространстве вашего слайсера.

Фильтруйте по N основным элементам

Вы можете добавить фильтры, чтобы отобразить ваше верхнее или нижнее число в сводной таблице.

С помощью значка фильтра перейдите в раздел "Фильтры по значению" и выберите "Лучшие 10". Вы сможете выбирать из множества вариантов.

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

Добавьте фильтр значений для любого поля

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

Нажмите на фильтр значок справа от поля имя. Выберите значение фильтры из меню. Здесь вы можете выбрать любое количество вариантов.

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

Независимо от того, какой параметр фильтра значений вы выбрали, вы сможете настроить его в меню критерии фильтрации значений.

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

Увеличьте отступ метки строки в компактном формате макета

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

Выберите свою сводную таблицу и перейдите на вкладку "Анализ" и выберите "Параметры".

Перейдите на вкладку "Макет и формат", затем отрегулируйте количество символов для вашего отступа по желанию.

Добавьте несколько промежуточных итогов

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

Для этого вам понадобится сводная таблица с по крайней мере двумя полями в области строк сводной таблицы.

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

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

Это отличный способ показать больше сводной информации в ваших сводных таблицах.

Включайте новые элементы в ручные фильтры

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

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

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

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

В меню "Настройки полей" перейдите на вкладку "Промежуточные итоги и фильтры" и установите флажок "Включить новые элементы в фильтр вручную".

Используйте внешний источник подключения к данным

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

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

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

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

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

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

Обновляйте внешние подключения по расписанию

Вы можете настроить свои внешние подключения для обновления любых новых или обновленных данных по выбранному вами периодическому графику. Перейдите на вкладку "Данные" на ленте и выберите команду "Запросы и подключения".

Если вы сначала выберете сводную таблицу с помощью внешнего подключения, вы можете напрямую открыть меню "Свойства" на вкладке "Данные".

Щелкнитеправой кнопкой мыши на внешнем подключении в окне Запросы и подключения и выберите в меню "Свойства".

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

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

Включить фоновое обновление
Включите обновление данных при открытии файла
Включите обновление этого подключения при обновлении всех

Отображать значение как

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

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

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

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

Щелкните правой кнопкой мыши на любом значении, а затем выберите в меню "Показать значения как". В подменю вы сможете выбрать один из множества различных вариантов расчета. Вы также сможете вернуть полю значение Без вычислений отсюда.

Другой вариант - получить доступ к этому через меню "Настройки поля значения"...........

Перейдите на вкладку "Анализ" и нажмите кнопку "Настройки поля", расположенную под разделом "Активное поле". ............

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

Как только вы окажетесь в меню "Настройки поля значения", перейдите на вкладку "Показывать значения как"..........

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

Показывать значение в% от общего итога

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

Показывать значение в % от общего числа столбцов

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

Показывать значение в % от общего количества строк

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

Показывать значение в% от родительского столбца

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

Родительский столбец будет самым верхним полем в области Столбцов сводной таблицы.

Показывать значение в % от родительской строки

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

Родительская строка будет самым верхним полем в области Строк сводной таблицы.

Показывать значение в виде разницы

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

Показывать значение в % от разницы

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