Как Обновить Поля Сводной Таблицы в Excel • Область строк
Использование срезов в сводной таблице Excel — руководство для начинающих
Версия сводной таблицы для Excel 2010 была потрясена появлением новой супер классной функции — срезов.
Срез сводной таблицы позволяет фильтровать данные, когда вы выбираете один или несколько параметров в поле среза (как показано ниже).
В приведенном выше примере слайсер — это оранжевое поле справа, и вы можете легко отфильтровать сводную таблицу, просто нажав кнопку области в слайсере.
Нажмите здесь, чтобы загрузить образцы данных и следуйте дальше.
Вставка слайсера в сводную таблицу Excel
Предположим, у вас есть набор данных, как показано ниже:
Это фиктивный набор данных (розничные продажи в США), охватывающий 1000 строк. Используя эти данные, мы создали сводную таблицу, в которой показаны общие продажи по четырем регионам.
Когда у вас есть сводная таблица, вы можете вставлять срезы.
Вам могут понадобиться срезы, если вам нужна не вся сводная таблица, а только ее часть. Например, если вы не хотите видеть продажи для всех регионов, а только для Юга или Юга и Запада, вы можете вставить срез и быстро выбрать желаемый регион (ы), для которого вы хотите получить данные о продажах.
Срезы — это более наглядный способ фильтрации данных сводной таблицы на основе выбора.
Вот шаги, чтобы вставить слайсер для этой сводной таблицы:
Обратите внимание, что срез автоматически определит все уникальные элементы выбранного измерения и перечислит их в поле среза.
После того, как вы вставили слайсер, вы можете отфильтровать данные, просто щелкнув элемент. Например, чтобы получить данные о продажах только для Южного региона, щелкните Юг. Вы заметите, что выбранный элемент приобретает другой оттенок цвета по сравнению с другими элементами в списке.
Вы также можете выбрать сразу несколько элементов. Для этого, удерживая клавишу Control, щелкните те, которые хотите выбрать.
Если вы хотите отменить выбор, щелкните значок фильтра (с красным крестиком) в правом верхнем углу.
Вставка нескольких срезов в сводную таблицу
Вы также можете вставить несколько срезов, выбрав более одного измерения в диалоговом окне «Вставить срезы».
Обратите внимание, что эти слайсеры связаны друг с другом. Например, если я выберу «Средний Запад» в фильтре «Регион» и «Многострочный» в фильтре «Тип розничного продавца», тогда будут показаны продажи для всех многострочных розничных продавцов только в регионе Среднего Запада.
Кроме того, если я выберу Mid West, обратите внимание, что опция Specialty во втором фильтре приобретает более светлый оттенок синего (как показано ниже). Это означает, что нет данных по розничным торговцам специализированной продукции в регионе Среднего Запада.
Слайсеры Vs. Фильтры отчетов
Слайсеры выглядят супер круто и просты в использовании. Сила сводной таблицы заключается в том, что для ее использования не требуется много навыков. Все, что вам нужно сделать, это перетащить и щелкнуть здесь и там, и вы получите отличный отчет в считанные секунды.
Вот некоторые ключевые различия между срезами и фильтрами отчетов:
- Срезы не занимают фиксированную ячейку на листе. Вы можете перемещать их, как любой другой объект или форму. Фильтры отчета привязаны к ячейке.
- Фильтры отчетов связаны с определенной сводной таблицей. С другой стороны, срезы можно связать с несколькими сводными таблицами (как мы увидим позже в этом руководстве).
- Поскольку фильтр отчета занимает фиксированную ячейку, его проще автоматизировать с помощью VBA. С другой стороны, слайсер — это объект, и ему потребуется более сложный код.
Форматирование слайсера
Когда дело доходит до форматирования, слайсер обладает большой гибкостью.
Изменение цветов слайсера
Если вам не нравятся цвета слайсера по умолчанию, вы можете легко изменить его.
- Выберите слайсер.
- Перейдите в Инструменты слайсера -> Параметры -> Стили слайсера. Здесь вы найдете несколько различных вариантов. Выберите тот, который вам нравится, и ваш слайсер мгновенно получит это форматирование.
Если вам не нравятся стили по умолчанию, вы можете создать свои собственные. Для этого выберите параметр «Новый стиль среза» и укажите собственное форматирование.
Получение нескольких столбцов в блоке слайсера
По умолчанию слайсер имеет один столбец, и в нем перечислены все элементы выбранного измерения. Если у вас много элементов, Slicer показывает полосу прокрутки, которую вы можете использовать для просмотра всех элементов.
Возможно, вы захотите, чтобы все элементы были видны без проблем с прокруткой. Вы можете сделать это, создав слайсер с несколькими столбцами.
Это мгновенно разделит элементы в слайсере на две колонки. Однако вы можете получить что-то ужасное, как показано ниже:
Это выглядит загроможденным, и полные имена не отображаются. Чтобы он выглядел лучше, вы меняете размер слайсера и даже кнопок внутри него.
- Выберите слайсер.
- Перейдите в Инструменты слайсера -> Параметры.
- Измените высоту и ширину кнопок и слайсера. (Обратите внимание, что вы также можете изменить размер слайсера, просто выбрав его и с помощью мыши отрегулируйте края. Однако, чтобы изменить размер кнопки, вам нужно внести изменения только в параметрах).
Изменение / удаление заголовка слайсера
По умолчанию слайсер выбирает имя поля из данных. Например, если я создаю срез для регионов, заголовок автоматически будет «Регион».
Вы можете изменить заголовок или полностью удалить его.
Если вы не хотите видеть заголовок, снимите флажок «Отображать заголовок» в диалоговом окне.
Сортировка элементов в слайсере
По умолчанию элементы в слайсере сортируются в возрастающем порядке в случае текста и от старых к более новым в случае чисел / дат.
Вы можете изменить настройку по умолчанию и даже использовать свои собственные критерии сортировки.
Скрытие элементов без данных из окна слайсера
Может случиться так, что некоторые элементы сводной таблицы не содержат данных. В таких случаях вы можете заставить слайсеры скрывать этот элемент.
В таких случаях вы можете вообще не отображать его.
Подключение слайсера к нескольким сводным таблицам
Слайсер можно подключить к нескольким сводным таблицам. После подключения вы можете использовать один слайсер для одновременной фильтрации всех подключенных сводных таблиц.
Помните, что для подключения разных сводных таблиц к слайсеру они должны использовать один и тот же сводный кеш. Это означает, что они либо созданы с использованием тех же данных, либо одна из сводных таблиц была скопирована и вставлена как отдельная сводная таблица.
Ниже приведен пример двух разных сводных таблиц. Обратите внимание, что слайсер в этом случае работает только для сводной таблицы слева (и не влияет на таблицу справа).
Чтобы подключить этот слайсер к обеим сводным таблицам:
Теперь ваш слайсер подключен к обеим сводным таблицам. Когда вы делаете выбор в слайсере, фильтрация будет происходить в обеих сводных таблицах (как показано ниже).
Создание динамических сводных диаграмм с помощью срезов
Так же, как вы используете слайсер со сводной таблицей, вы также можете использовать его со сводными диаграммами.
Вот как вы можете создать эту динамическую диаграмму:
Обратите внимание, что вы можете подключить несколько слайсеров к одной сводной диаграмме, а также подключить несколько диаграмм к одному слайсеру (так же, как мы подключили несколько сводных таблиц к одному слайсеру).
Щелкните здесь, чтобы загрузить образцы данных и попробовать сами.
Вам также могут понравиться следующие руководства по сводным таблицам:
Создание сводных таблиц в Excel | Exceltip
- Выберите слайсер.
- Перейдите в Инструменты слайсера -> Параметры -> Стили слайсера. Здесь вы найдете несколько различных вариантов. Выберите тот, который вам нравится, и ваш слайсер мгновенно получит это форматирование.
Примечание. Т. к. отчеты сводных таблиц в Microsoft Excel не могут отображать некоторые макеты и вычисления сводной таблицы, сводный отчет может выглядеть иначе, чем сводная таблица в обозревателе. Сведения о работе со сводными отчетами и добавлении в них полей см. в справке Microsoft Excel.
Как обновить сводную таблицу в Google Таблицах
Сводные таблицы — это мощные инструменты, которые позволяют получить очень полезную информацию о ваших данных. Часто задаваемый вопрос на многих форумах Google Таблиц — «Как обновить сводные таблицы в Google Таблицах?»
Как обновить сводную таблицу в Google Таблицах
Как правило, сводные таблицы не нужно обновлять вручную. Они автоматически обновляются при изменении данных, извлекаемых таблицей.
Однако могут возникнуть ситуации, когда вы обнаружите, что данные в сводной таблице не обновляются.
В этом руководстве мы рассмотрим некоторые из этих ситуаций и способы их исправления, чтобы изменения в ваших исходных данных также обновлялись в сводной таблице.
Почему не обновляется сводная таблица?
Если вы обнаружите, что ваши сводные таблицы не обновляются автоматически при внесении изменений в исходный набор данных, это может быть связано с одной из следующих причин:
- Возможно, вы добавляете новые данные в строки за пределами диапазона сводной таблицы.
- В вашей сводной таблице могут использоваться фильтры
- Ваш набор данных может содержать формулы, которые необходимо пересчитать, например RANDOM, TODAY и т. Д.
Давайте посмотрим на каждую из вышеперечисленных ситуаций, почему они случаются и как их решать по очереди.
Проблема 1: сводная таблица не обновляется при добавлении новой строки
Сводная таблица работает с данными из определенного диапазона ячеек исходного листа. Если новая строка, которую вы пытаетесь добавить, находится за пределами диапазона сводной таблицы, это не повлияет на сводную таблицу.
И это, очевидно, имеет смысл, поскольку новый диапазон не попадает в диапазон, из которого была создана сводная таблица для извлечения данных.
При первоначальном создании сводной таблицы вы можете немного предвидеть и добавить несколько дополнительных строк для данных, которые вы, вероятно, добавите в будущем.
Например, если ваши данные в настоящее время занимают более 20 строк, вы можете создать сводную таблицу с 1000 строками. Дополнительные 980 строк могут оставаться пустыми, и вы можете позже заполнить их данными по мере необходимости.
Теперь, пока ваши данные находятся в пределах 1000 строк, сводная таблица будет обновляться автоматически.
Примечание: у этого метода есть небольшой недостаток. Если в исходном наборе данных есть пустые строки, в сводной таблице также будет отображаться одна пустая строка, которая может выглядеть не очень хорошо. Чтобы избавиться от этого, вы можете добавить в таблицу фильтр, который отфильтровывает пустые строки и отображает только те строки, которые содержат значение.
Проблема: ваша сводная таблица использует фильтры
Если в сводной таблице используются фильтры, ваши данные не будут обновляться при изменении исходных значений данных. Это ограничение возможностей сводной таблицы Google Таблиц.
Если у вас есть фильтры в сводной таблице, единственное решение — удалить их, внести изменения в исходные данные, а затем снова добавить фильтры. Вот шаги, которые вам необходимо выполнить:
- Нажмите на крестик рядом со всеми полями в категории «Фильтры» в редакторе сводной таблицы.
- Внесите необходимые изменения в исходный набор данных.
- Теперь изменения должны быть отражены в сводной таблице.
- Когда вы закончите вносить изменения, добавьте фильтры обратно, используя кнопку «Добавить» в категории «Фильтры» (в редакторе сводной таблицы).
Проблема: сводная таблица не обновляется, если исходный набор данных содержит такие функции, как RANDOM, TODAY и т. Д.
Сводная таблица плохо работает с функциями, требующими обновления. Поэтому, если ваши исходные данные содержат такие функции, как RANDOM, TODAY и т. Д., То изменения исходных данных не будут обновлены в сводной таблице.
К сожалению, у этой проблемы нет другого решения, кроме как просто избегать использования таких функций.
Это было три способа, с помощью которых вы можете попытаться убедиться, что ваша сводная таблица обновляется с изменениями в ваших данных.
Функция принудительного обновления изначально была доступна в Google Таблицах, но позже была исключена из более новых версий, поскольку имело больше смысла неявное автоматическое обновление.
Однако проблемы, из-за которых сводные таблицы не обновляются по причинам, указанным в этой главе, являются довольно обычным явлением, и Google следует как можно скорее решить эту проблему.
Но прежде чем они это сделают, мы предлагаем вам следовать нашим рекомендациям, чтобы обеспечить автоматическое обновление сводных таблиц с минимальными усилиями.
Как использовать 5 продвинутых приёмов для сводных таблиц Excel
- Возможно, вы добавляете новые данные в строки за пределами диапазона сводной таблицы.
- В вашей сводной таблице могут использоваться фильтры
- Ваш набор данных может содержать формулы, которые необходимо пересчитать, например RANDOM, TODAY и т. Д.
Сводная таблица состоит из четырех областей: Фильтры, Колонны, Строки и Значения. В зависимости от того, куда вы разместите данные, внешний вид сводной таблицы будет меняться. Давайте рассмотрим функцию каждой из областей более подробно.
Как использовать 5 продвинутых приёмов для сводных таблиц Excel
Проблема, с которой мы все сталкиваемся — это не отсутствие данных; наоборот — это огромные объемы данных! Именно поэтому я выступаю за использование замечательной функции Excel — Cводные таблицы, чтобы обобщить и проанализировать ваши данные.
Будучи профессионалом в области финансов, я генетически склонен любить электронные таблицы. Но я также обнаружил, что я использую электронные таблицы для организации моей творческой и внештатной работы. Независимо от того, для чего вы используете электронные таблицы, сводная таблица может помочь вам найти больший смысл в данных.
Этот урок, мы построим по нашему начальному уроку по использованию сводных таблиц, для лучшей работы с данными. Я покажу вам пять моих любимых передовых приёмов сводной таблицы.
На протяжении всего этого урока я буду использовать пример данных, предоставленных Microsoft на этой странице. Используйте эти данные, чтобы воссоздать примеры или проверить функции, которые я демонстрирую.
Как применять продвинутые приёмы сводных таблицы в Excel (короткое видео)
Мне нравится преподавать с помощью скринкастов, которые дают вам возможность наблюдать за тем, как я использую функции шаг за шагом. Ознакомьтесь с коротким видео ниже, которое охватывает пять моих любимых расширенных возможностей сводных таблиц Excel:
5 продвинутых приёмов сводных таблиц в Excel
Продолжайте читать для ознакомления о том, как использовать каждую из этих пяти функций в приведенном ниже уроке, включая: Срезы, Временную шкалу, Табличный вид, Вычисляемые поля и Рекомендуемые Сводные таблицы. Давайте приступим.
Срезы
Срезы — инструмент указания и клика, чтобы уточнить данные, включенные в сводную таблицу Excel. Вставьте срез и вы сможете легко изменять данные, включенные в сводную таблицу.
По многу раз я разрабатываю отчеты сводных тсблац, которые будут использоваться другими. Добавление срезов может помочь конечному пользователю настроить отчет по его вкусу.
Чтобы добавить срез, кликните в сводной таблице и найдите вкладку Анализ на ленте Excel.
Удерживайте Ctrl на клавиатуре, чтобы выбрать множество элементов срезов, которые будут включать в себя несколько выборок из столбца, в качестве данных сводной таблицы.
Временная шкала
Временные шкалы это особый тип срезов, используемый для настройки дат, включенных как часть данных сводной таблицы. Если ваши данные содержат даты, вам действительно нужно попробовать Временную шкалу в качестве способа выбора данных за определенные периоды времени.
Совет: если эта функция у вас не работает, убедитесь, что в исходных данных есть дата, отформатированная в электронной таблице, как дата.
Чтобы добавить временную шкалу, убедитесь, что вы выбрали сводную таблицу (щелкните внутри неё), а затем нажмите кнопку Анализ > Вставить временную шкалу на ленте Excel. Во всплывающем окне отметьте столбец даты (или несколько столбцов) и нажмите OK, чтобы создать временную шкалу.
После того, как временная шкала вставлена, вы можете кликнуть и перемещать ручки на временной шкале, чтобы изменить то, что входит в сводную таблицу.
Вы можете изменить способ работы временной шкалы, нажав на раскрывающийся список в правом нижнем углу. Вы можете изменить временную шкалу, чтобы вместо конкретных дат отображались данные по кварталу или году, например.
Табличный вид
Стандартный вид сводных таблиц в Excel выглядит как водопад; при перетаскивании большего количества полей в строки, Excel создает больше «слоев» в данных.
Проблема в том, что в сводные таблицы в стандартном представлении трудно вписывать формулы. Если у вас есть данные в сводной таблице, но вы хотите просмотреть её в виде обычной электронной таблице, вы должны использовать табличное представление для сводной таблицы.
Почему вы должны использовать табличный вид? Приведение данных сводной таблицы в классический вид с табличным стилем, позволит вам легче вписывать формулы данных или вставлять их в отдельный отчет.
Большую часть времени, мне удобнее использовать в Excel табличный вид. Он больше похож на стандартный вид электронных таблиц и легче писать формулы и работать с данными в ней. Я мог бы также взять это представление и вставить его в новую вкладку.
Вычисляемые поля
Вычисляемые поля это способ добавить столбец в сводную таблицу, которая не находится в исходных данных. Вы можете использовать стандартные математические операции для создания совершенно новых полей для работы. Возьмите два существующих столбца и используйте математику для создания совершенно новых.
Предположим, что у нас в электронной таблице есть данные о продажах. У нас есть количество проданных товаров и цена продажи за каждый товар. Это идеальный момент, чтобы использовать вычисляемое поле для вычисления общей суммы заказа.
Чтобы начать работу с вычисленными полями, начните кликнув внутри сводной таблицы, а затем найдите на ленте Анализ. Нажмите меню Поля, элементы и наборы, а затем выберите Вычисляемое поле.
В новом всплывающем окне начните с присвоения вычисленному полю имени. в моем случае я назову его Total Order. Общая стоимость заказа это количество, умноженное на цену каждой единицы. Затем я дважды кликнул по первому полю названия (quantity) в списке полей в этом окне.
После добавления названия этого поля, я добавлю знак умножения *, а затем дважды кликаю по общему количеству (quantity). Давайте продолжим и нажмите ОК.
Теперь Excel обновил мою расширенную сводную таблицу и добавил новое вычисленное поле. Вы также увидите список сводных таблиц в списке полей, чтобы вы могли перетаскивать их в любое место отчета, когда вам это нужно.
Если вы не хотите использовать арифметику по двум столбцам, вы также можете ввести свои собственные арифметические значения в вычисляемом поле. Например, если бы я хотел просто добавить 5% налога с продаж для каждого заказа, я мог бы написать следующее в вычисленное поле:
В принципе, вычисленные поля могут содержать любые стандартные математические операторы, такие как сложение, вычитание, умножение и деление. Используйте эти вычисленные поля, если вы не хотите обновлять исходные данные.
Рекомендуемые сводные таблицы
Функция Рекомендуемые сводные таблицы настолько хороша, что это похоже на обман. Вместо того, чтобы тратить время на перетаскивание полей, я начинаю с одной из рекомендуемых конфигураций.
Эта функция настолько проста в использовании, что и говорить нечего. Вы можете использовать её для быстрого создания расширенных сводных таблиц в Excel. Просто выделите свои данные, перейдите на вкладку Вставка на ленте Excel и выберите Рекомендуемые сводные таблицы.
Всплывающее окно содержит множество вариантов создания сводной таблицы из исходных данных. Щелкните по эскизам в левой части этого окна, чтобы просмотреть рекомендуемые параметры сводной таблицы предоставленной Excel.
Несмотря на то, что это расширенная функция, о которой осведомлены мало пользователей, это также отличный инструмент для запуска сводных таблиц. Ничто не мешает вам изменить сводную таблицу, заменив поля самостоятельно, но это удобная отправная точка.
Также, мне нравится эта функция в качестве изучения данных. Если я не знаю, что я ищу, когда я начинаю исследовать данные, рекомендуемые сводные таблицы Excel часто более проницательны, чем я!
Повторяйте и продолжайте обучение (с ещё бо́льшими уроками по Excel)
Этот продвинутый урок по Excel помог вам глубже погрузиться в сводные таблицы, одну из моих любимых функций для анализа и просмотра электронной таблицы Excel. Я использую сводные таблицы, чтобы находить значение в больших наборах данных, благодаря чему, я могу принимать правильные решения и принимать меры.
Эти уроки вам продвинуть ваши навыки с Excel и сводным таблицам на следующий уровень. Проверь их:
- ExcelZoo имеет большой обзор приёмов сводной таблицы в их статье, 10 уроков для освоения сводных таблиц (на английском).
- Мы в Envato Tuts+ рассмотрели сводные таблицы с помощью урока для новичков Как создать свою первую сводную таблицу в Microsoft Excel.
- Для более простого введения в Microsoft Excel ознакомьтесь с нашей учебной серией Как сделать и использовать формулы в Excel (Учебный лагерь для начинающих).
Что вы всё ещё хотите узнать о сводных таблицах? Сообщите мне ваши идеи или вопросы в комментариях ниже этого урока.
Волшебные сводные таблицы Excel | Блог Александра Воробьева
- ExcelZoo имеет большой обзор приёмов сводной таблицы в их статье, 10 уроков для освоения сводных таблиц (на английском).
- Мы в Envato Tuts+ рассмотрели сводные таблицы с помощью урока для новичков Как создать свою первую сводную таблицу в Microsoft Excel.
- Для более простого введения в Microsoft Excel ознакомьтесь с нашей учебной серией Как сделать и использовать формулы в Excel (Учебный лагерь для начинающих).
Большую часть времени, мне удобнее использовать в Excel табличный вид. Он больше похож на стандартный вид электронных таблиц и легче писать формулы и работать с данными в ней. Я мог бы также взять это представление и вставить его в новую вкладку.
Публикуя свою персональную информацию в открытом доступе на нашем сайте вы, даете согласие на обработку персональных данных и самостоятельно несете ответственность за содержание высказываний, мнений и предоставляемых данных. Мы никак не используем, не продаем и не передаем ваши данные третьим лицам.