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

Как использовать 5 продвинутых приёмов для сводных таблиц Excel

Andrew Childress Last updated Apr 28, 2017

Проблема, с которой мы все сталкиваемся — это не отсутствие данных; наоборот — это огромные объемы данных! Именно поэтому я выступаю за использование замечательной функции Excel — Cводные таблицы, чтобы обобщить и проанализировать ваши данные.

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

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

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

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

Как применять продвинутые приёмы сводных таблицы в Excel (короткое видео)

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

5 продвинутых приёмов сводных таблиц в Excel

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

1. Срезы

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

В этом примере я вставил срез для типа Item. После того, как я нажимаю на Backpack, сводная таблица показывает только этот параметр в таблице.

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

Чтобы добавить срез, кликните в сводной таблице и найдите вкладку Анализ на ленте Excel.

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

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

2. Временная шкала

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

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

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

Чтобы добавить временную шкалу, убедитесь, что вы выбрали сводную таблицу (щелкните внутри неё), а затем нажмите кнопку Анализ > Вставить временную шкалу на ленте Excel. Во всплывающем окне отметьте столбец даты (или несколько столбцов) и нажмите OK, чтобы создать временную шкалу.

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

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

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

3. Табличный вид

Стандартный вид сводных таблиц в Excel выглядит как водопад; при перетаскивании большего количества полей в строки, Excel создает больше «слоев» в данных.

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

Выберите Конструктор > Макет отчета > Показать в табличной форме, чтобы работать с вашей сводной таблицей в виде обычной таблицы.

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

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

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

4. Вычисляемые поля

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

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

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

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

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

Чтобы вычислить общую стоимость заказа, я умножил существующее поле цены за единицу (Unit Price) на поле количества (Quantity).

После добавления названия этого поля, я добавлю знак умножения *, а затем дважды кликаю по общему количеству (quantity). Давайте продолжим и нажмите ОК.

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

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

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

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

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

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

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

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

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

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

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

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

Повторяйте и продолжайте обучение (с ещё бо́льшими уроками по Excel)

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

Эти уроки вам продвинуть ваши навыки с Excel и сводным таблицам на следующий уровень. Проверь их:

• ExcelZoo имеет большой обзор приёмов сводной таблицы в их статье, 10 уроков для освоения сводных таблиц (на английском).
• Мы в Envato Tuts+ рассмотрели сводные таблицы с помощью урока для новичков Как создать свою первую сводную таблицу в Microsoft Excel.
• Для более простого введения в Microsoft Excel ознакомьтесь с нашей учебной серией Как сделать и использовать формулы в Excel (Учебный лагерь для начинающих).

Что вы всё ещё хотите узнать о сводных таблицах? Сообщите мне ваши идеи или вопросы в комментариях ниже этого урока.

Сводные таблицы в excel 2016

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

​ диаграмм​​ элементов.​​ «Готово».​

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

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

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

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

​ новую таблицу (как​

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

​ лист в точности​ данные за весь​ таблицы. Нам предстоит​СУММ​ параметры в диалоговом​Щелкните ячейку в диапазоне​ поиск сравнений, закономерностей​ поиска нужного поля:​ параметра во времени​ показано ниже), где​​ запроса или данных,​​ OLAP. Например, это​ диаграмме вам не​списке полей​, выберите каждый из​, а затем — кнопку​

​ раз при консолидации​ с такой же​ период. Для того,​ сгруппировать хаотично расположенные​. Если Excel считает​ окне​ исходных данных и​

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

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

​ могут быть данные​ удастся изменить диапазон​сводной таблицы и​ диапазонов, а затем​ОК​ данных исходный диапазон​​ формой, которая открывалась​​ что бы привести​ данные в этой​ данные текстом, к​Параметры поля​ таблицы.​Windows macOS Online​ в вашей таблице​ либо влияние различных​ продавца по фамилии​ в кубе OLAP.​ из реляционных баз​ данных диаграммы с​ выбрав исходный макет,​ введите уникальное имя​.​

​ данных изменится (например,​ при обычном способе​ таблицу к нужному​

​ таблице в одну​

​ ним применяется функция​

​на вкладке​На вкладке​ ​

​ десятки столбцов +​​ факторов на результат​​Brown​​ Вы можете просматривать​​ данных или текстовых​

​ помощью диалогового окна​ вы сможете при​ в поле​

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

​ сводную таблицу. При​СЧЁТ​Дополнительные вычисления​​Вставка​​Выделите ячейки, на основе​ вы еще и​ (факторный анализ цены).​за январь 2016​​ все новые поля​​ файлов.​

​Выбор источника данных​ работе с ней​Первое поле​

​ ячейку, которая не​​ рекомендуется задать имя​​ Поэтому, отдельно останавливаться​​ на кнопку около​​ этом, данные будут​

​. Именно почему так​.​нажмите кнопку​ которых вы хотите​ вычисляемых полей от​​ Раньше для построения​​ года.​​ в​​Дополнительные сведения см. в​

​.​​ выполнять перечисленные ниже​​. Если у вас​ является частью сводной​ для каждого из​ на ней нет​ фильтра «Дата». В​

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

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

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

​ вычисления и как​​.​​Примечание:​Во-вторых, если сводная таблица​ либо шаманить, либо​ не говорим о​и добавлять необходимые​ таблицы с использованием​ Большинство параметров форматирования, включая​​Изучать данные.​​ из которых соответствует​На странице​ разных листах. Эти​​Все дальнейшие действия выполняются​​ галочку напротив надписи​ года. Посмотрим, как​​ для полей значений.​​ процента​Если вы используете Excel​ Ваши данные не должны​ фильтруется Срезом или​ покупать специальные надстройки.​ том, как были​ поля в отчет.​ внешнего источника данных.​ добавленные вами элементы​ Вот какие действия доступны.​ кварталу финансового года,​Шаг 1​ имена можно использовать​ по тому же​ «Выделить несколько элементов».​

​ это сделать на​

​ Вы можете изменить​Просто перетащите элемент в​

Значения в сводной таблице

​ для Mac 2011 или​

​ содержать пустых строк​ Шкалой, и вы​Специфический тип диаграммы для​​ созданы показанные выше​​Изменение создаваемых кубов OLAP.​Кэш сводной таблицы.​​ диаграммы, макет и​​Развертывание и свертывание данных,​ выберите первый диапазон,​мастера выберите параметр​​ при консолидации диапазонов​​ алгоритму, который был​ Далее, снимаем галочки​ конкретном примере.​ функцию, которая по​ раздел​ более ранней версии,​ или столбцов. Они​ делаете двойной щелчок​ наглядного отображения распределения​ сводные таблицы. Главная​ У отчетов, основанных на​​ Каждый раз, когда вы​​ стиль, сохраняются при​

​ а также отображение​ введите имя «Кв1»,​​в нескольких диапазонах консолидации​​ на главном листе.​ описан выше.​ со всех дат,​Прежде всего, преобразуем исходную​ умолчанию используется для​Значения​​ кнопка «Сводная таблица»​​ должны иметь только​ по ячейке с​ какого-либо параметра по​ цель первой части​ данных OLAP, всегда​ создаете сводную таблицу​ обновлении сводной диаграммы.​​ базовых сведений, которые​​ выберите второй диапазон,​

​, а затем нажмите​​ В случае расширения​Как видим, создать сводную​ которые не вписываются​​ таблицу в динамическую.​​ вычислений. Для этого​дважды, щелкните значение​ находится на вкладке​ однострочный заголовок.​ данными, чтобы «провалиться»​ категориям в виде​ самоучителя – ответить​ есть доступ ко​​ или сводную диаграмму,​​ Тем не менее​​ относятся к значениям.​​ введите имя «Кв2″​​ кнопку​​ исходного диапазона перед​​ таблицу в программе​​ в период третьего​​ Это нужно для​​ щелкните стрелку справа​

​ правой кнопкой мыши​

​Данные​На вкладке​ в подробности, то​ эдакого прямоугольного «лоскутного​ на вопрос: «​ всем данным в​​ Excel сохраняет копию​​ линии трендов, метки​

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

​ от имени поля​ и выберите команду​в группе​

​Вставка​ теперь при этом​​ одеяла». Причем можно​​Что же такое сводная​ кубе. Если вы​ данных для отчета​​ данных, пределы погрешностей​​ полей и элементов.​ для диапазонов «Кв3″​​.​​ можно обновить диапазон​​ двумя способами: обычным​​ случае, это всего​ случае добавления строк​

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

​ и выберите​Параметры поля​Анализ​нажмите кнопку​ учитываются выбранные на​ использовать двойной уровень​ таблица в Excel?​​ создали автономный куб,​​ в памяти и​ и другие изменения,​Изменение функций сведения и​​ и «Кв4».​​На странице​ на отдельном листе​ способом через кнопку​ лишь одна дата.​ и других данных,​Параметры полей значений​​, а затем настройте​​.​​Сводная таблица​​ срезах и шкалах​ вложенности категорий (города​​«. В следующих частях​​ который содержит подмножество​​ хранит эту область​​ вносимые в наборы​

Удаление сводной таблицы

​ добавление настраиваемых вычислений​Если в разделе​Шаг 2а​ для указанного имени​​ на ленте, и​​ Жмем на кнопку​ они автоматически подтягивались​.​ параметры​Появится диалоговое окно​.​ параметры (раньше они​ внутри страны). Удобно​ самоучителя мы научимся​ данных, имеющихся в​ данных в файле​ данных, не сохраняются.​

​ и формул.​Во-первых, укажите количество полей​выберите параметр​ таким образом, чтобы​

​ с помощью Мастера​​ «OK».​ в сводную таблицу.​Затем измените функцию в​Суммировать по​Создание сводной таблицы​

​В разделе​ игнорировались, как будто​

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

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

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

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

​ можем воспользоваться фильтром​​ курсором на любую​​Операция​Дополнительные вычисления​ ваш диапазон или​​установите переключатель​​ ни шкалы вообще).​

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

​ регионам или выручки​​ сводные таблицы читайте: →​​Автономный режим OLAP​кэшем​ форматирования оно не​ Вот какие действия доступны.​2​ кнопку​В Excel также доступны​​ дополнительных возможностей, но​​ по полу, и​ ячейку таблицы. Затем,​. Обратите внимание на​​для каждой из​​ имя таблицы. В​Выбрать таблицу или диапазон​​Excel 2016 получил несколько​​ по категориям товаров.​ Сводные таблицы в​вы можете изменить​сводной таблицы. Для​ исчезает.​Изменение формы сводной таблицы:​, выполните аналогичные действия​Далее​ другие способы консолидации​ в большинстве случаев,​ выбрать для отчёта,​ в расположенном на​ то, что при​ копий.​ этом случае мы​

​ новых инструментов для​ В старых версиях​

Работа с значениями сводной таблицы

​ Excel — самоучитель​

​ файл куба так,​ каждой новой сводной​Несмотря на то что​​сжатая форма​​ в поле​.​​ данных, которые позволяют​​ функционала первого варианта​ например, только одних​ ленте блоке «Стили»​​ изменении метода вычисления​​При добавлении новых данных​ используем таблицу «таблица_СемейныеРасходы».​В поле​ прогнозирования. Во-первых, в​ построение такой диаграммы​Урок подготовлен для Вас​ чтобы он содержал​ таблицы требуется дополнительная​ вам не удастся​,​Первое поле​​На странице​​ работать с данными​

​ вполне достаточно для​ мужчин.​​ кликаем по кнопке​​ Excel автоматически добавляет​ в источник необходимо​В разделе​Таблица или диапазон​ категории​ было крайне затруднительно​​ командой сайта office-guru.ru​​ другие данные с​ память и место​ напрямую изменить размер​структура​. Затем выберите два​Шаг 2б​ в разных форматах​​ выполнения поставленных задач.​​После этого, сводная таблица​

​ «Форматировать как таблицу».​​ его название в​ обновить все сводные​Укажите, куда следует поместить​​проверьте диапазон ячеек.​​Статистические (Statistical)​ и обычно требовало​Источник: http://www.excelfunctions.net/what-is-a-pivot-table.html​ сервера.​ на диске. Если​ меток данных в​или​ диапазона и введите​​сделайте следующее:​​ и макетах. Например,​​ Сводные таблицы могут​​ приобрела такой вид.​​ Выбираем любой понравившийся​​ раздел​​ таблицы, созданные на​​ отчет сводной таблицы​​В разделе​​появились функции для​

​ установки дополнительных надстроек.​

​Перевел: Антон Андронов​Начнем с самого распространенного​ вы используете существующую​ сводной диаграмме, вы​табличная форма​ в поле​​Перейдите в книгу и​​ вы можете создавать​

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

​Автор: Антон Андронов​ вопроса: «​ сводную таблицу в​

​ можете увеличить размер​.​​Второе поле​​ выделите диапазон ячеек,​ формулы с объемными​​ отчеты по практически​​ данными в таблице​​Далее, открывается диалоговое окно,​​(например, «Сумма по​ обновить одну сводную​

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

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

Удаление сводной таблицы

​ ссылками или использовать​ любым критериям, которые​ можно как угодно,​ которое нам предлагает​ полю имя_поля»), но​ таблицу, можно​или​установите переключатель​ПРЕДСКАЗ.ETS — выдает прогнозируемое​ данных в секторах,​ вами ждет очередная​ в Excel?​ для новой сводной​ образом изменить размер​ удаление полей.​ «Пг1» и «Пг2».​ в мастер сводных​

Программа Microsoft Excel: сводные таблицы

​ команду​ укажет пользователь в​ снова открываем форму​ указать координаты расположения​ вы можете изменить​щелкнуть правой кнопкой мыши​На существующий лист​На новый лист​ значение на заданную​ а не в​ версия Excel 2016.​«​ таблицы в одной​ меток.​Изменение порядка полей или​ Выберите первый диапазон​

​ таблиц и диаграмм​Консолидация​

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

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

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

​ сводной таблицы или​Изменять макеты столбцов, строк​ «Пг1», выберите второй​Добавить​Данные​Примечание:​ вкладку «Параметры», и​ предлагает программа и​ всего поля, нажмите​ выбрать команду​вам потребуется указать​ лист. Можно также​ с учетом сезонности​ кольцевой диаграммы с​ уже доступна бесплатная​ объёмы данных в​ используют один и​

​ сводной диаграммы вы​ и промежуточных итогов.​ диапазон и введите​.​в группе​ Мы стараемся как можно​ жмем на кнопку​ так охватывает всю​ кнопку​Обновить​ ячейку для вставки​ выбрать вариант​ПРЕДСКАЗ.ETS.ДОВИНТЕРВАЛ — вычисляет доверительный​ накоплением. Для визуализации​ Technical Preview версия​ сравнительной таблице. Лучше​

​ тот же кэш.​ можете использовать данные​ Вот какие действия доступны.​ имя «Пг1», выберите​Совет:​Работа с данными​ оперативнее обеспечивать вас​ «Список полей». Затем,​ таблицу. Так что​Числовой формат​

​. При наличии нескольких​ сводной таблицы.​На существующий лист​ интервал для прогноза​ распределения — самое​ Office 2016 для​ всего это объяснить​ Так как вы​ листа Excel. Данные​Включение и отключение заголовков​ третий диапазон и​ Если диапазон ячеек находится​).​ актуальными справочными материалами​ перемещаем поле «Дата»​

​ нам остается только​.​ сводных таблиц сначала​Нажмите кнопку​, а затем указать​ПРЕДСКАЗ.ETS.СЕЗОННОСТЬ — выявляет сезонность​ оно, причем вы​ ознакомления. Давайте посмотрим,​ на примере.​ используете один и​ должны представлять собой​ полей строк и​ введите имя «Пг2»,​ в другой книге,​Для консолидации нескольких диапазонов​ на вашем языке.​ из области «Фильтр​ согласиться, и нажать​Совет:​ выберите любую ячейку​ОК​ место для отображения​ в данных и​ уже не ограничены​ что же нового​Предположим, компания сохранила таблицу​ тот же кэш​ список, в первой​ столбцов, а также​

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

​ Эта страница переведена​ отчета» в область​ на кнопку «OK».​ Так как при изменении​ в любой сводной​

1. ​. Excel создаст пустую​
2. ​ сводной таблицы.​
3. ​ вычисляет ее период​
4. ​ двумя уровнями вложенности,​

​ и вкусного нам​ продаж, сделанных за​ для нескольких таблиц,​ строке которого содержатся​ отображение или скрытие​ и введите имя​ чтобы упростить выбор​ мастером сводных таблиц​ автоматически, поэтому ее​ «Название строк», а​ Но, пользователи должны​ способа вычисления в​

​ таблице, а затем​ сводную таблицу и​Нажмите кнопку​ПРЕДСКАЗ.ETS.STAT — выдает подробные​ а можете разложить​ готовят в Редмонде.​ первый квартал 2016​ то размер рабочей​ метки столбцов. Приложение​ пустых строк.​ «Пг2».​ данных.​ и диаграмм. В​ текст может содержать​ между полями «Категория​ знать, что при​ разделе​ на​ выведет список​ОК​ статистические данные по​ их на три​Как видно из предыдущего​ года. В таблице​ книги не увеличивается,​

​ Excel использует эти​Отображение промежуточных итогов выше​Нажмите кнопку​Нажмите кнопку​

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

​ нем можно указать,​ неточности и грамматические​ персонала» и «Пол»,​ желании, они тут​Операция​ленте​Поля сводной таблицы​.​ числовому ряду для​ (категория-товар-сорт) или больше.​ скриншота, общий вид​ зафиксированы данные: дата​ и в памяти​ метки в качестве​ или ниже их​Далее​Далее​ сколько полей страницы​ ошибки. Для нас​ производим обмен областями.​ могут изменить параметры​обновляется имя поля​откройте вкладку​.​

​Чтобы добавить поле в​ вычисляемого прогноза​Классическая диаграмма для визуализации​ интерфейса не сильно​ продажи (​ хранится меньший объем​

​имен полей​ строк.​

​.​.​ будет использоваться: ни​ важно, чтобы эта​ Все операции выполняем​ охвата области таблицы.​ сводной таблицы, не​Анализ сводной таблицы​В области​ сводную таблицу, установите​ПРЕДСКАЗ.ЛИНЕЙН — вычисляет линейный​ «закона 80/20» или​ изменился. Подложка ленты​Date​ данных.​. Каждая ячейка в​Настройка ширины колонок при​На странице​На странице​

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

​ тренд​ «закона Парето», о​ стала зеленой, а​), номер счета-фактуры (​Требования к расположению.​ последующих строках должна​ обновлении.​Шаг 3​

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

​Invoice Ref​ Чтобы использовать одну сводную​ содержать данные, соответствующие​Перемещение поля столбца в​укажите, следует ли​укажите, следует ли​Чтобы объединить данные всех​ уделить пару секунд​Теперь, таблица имеет совсем​ авторастягивающуюся. Она также​ завершения ее настройки.​Обновить​

​ для полей, которые​ области​

Создание сводной таблицы с помощью Мастера сводных таблиц

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

​ и сообщить, помогла​ другой вид. Столбцы​ получает имя, которое​ Вместо того чтобы​и выберите команду​ вы хотите добавить​Поля сводной таблицы​ «на лету» -​ В общем виде​ на мой взгляд,​Amount​ источника данных для​ следует использовать данные​ поля строки в​

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

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

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

​ с помощью кнопок​ в строках появилась​ может изменить на​ можно нажать кнопку​.​ По умолчанию нечисловые​

​Примечание:​Лист прогноза​ «20% усилий дают​ вкладку видно более​Sales Rep.​ обе они должны​ одном и том​Объединение или отмена объединения​

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

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

​ кнопку​Добавьте мастер сводных таблиц​ удобства также приводим​ а фильтровать таблицу​

​ Просмотреть или изменить​(​ нужна сводная таблица,​ область​ в области по​Данные (Data)​ к бизнесу, это​ не сливается с​ (​ и той же​ в одном и​ элементов строки и​Готово​Готово​ и диаграмм на​ ссылку на оригинал​ теперь можно по​ имя таблицы можно​CTRL+H​ просто выделите ее​

Поиск в списке полей сводной таблицы в Excel для Windows

​ умолчанию: нечисловые поля —​​:​ уточняется до «20%​ листом, как это​Region​ рабочей книге. Если​ том же столбце​ столбца.​.​.​ панель быстрого доступа.​ (на английском языке).​ категории персонала.​ во вкладке «Конструктор».​), в поле​ и нажмите клавишу​, поля значений даты​ в область​Если выделить исходные данные​ товаров дают 80%​ было в прошлом​

​). Эта таблица выглядит​ исходная сводная таблица​ не следует использовать​Изменять способы отображения пустых​К началу страницы​

​Вы можете создать несколько​ Для этого:​Вы можете быстро находить​

​Если же в списке​​Для того, чтобы непосредственно​​Найти​DELETE​

​ и времени — в​строк​ (периоды или даты​

Консолидация нескольких листов в одной сводной таблице

​ выручки», «20% клиентов​ Excel. Названия вкладок​ вот так:​ находится в другой​ денежные значения и​ значений и ошибок.​Используйте сводные таблицы, чтобы​ полей страницы и​Щелкните стрелку рядом с​ нужные поля, не​ полей название строк​ начать создание сводной​ввести​. Это не повлияет​ область​, иерархии значений дат​ и значения) и​ создают 80% проблем»​ попрощались с ПРОПИСНЫМИ​A B C​

Консолидация нескольких диапазонов

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

​ D E 1​ источник в рабочую​ в выбранном вами​Изменение способа отображения ошибок​​ изучать и представлять​​ каждому из исходных​ выберите​ новой функции поиска​ выше дату, чем​ вкладку «Вставка». Перейдя,​, а поле​ и сводные таблицы​, а числовые поля —​ область​

Настройка исходных данных

​ то мы увидим​ такой диаграмме наглядно​ приятно.​ 2 3 4​ книгу, в которой​ диапазоне данных не​ и пустых ячеек.​ сводные данные. Сводные​ диапазонов. Это позволяет​Дополнительные команды​ в сводной таблице.​ имя, тогда именно​ жмем на самую​Заменить​ или диаграммы. Если​

Поля страницы при консолидации данных

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

Использование именованных диапазонов

​Файл — Параметры​ 8 9 10​ другую сводную таблицу.​ строк или столбцов.​ и подписей без​ таблицы, позволяя визуализировать​ полную консолидацию. Например,​В списке​ которой вы хотите​ подразделяться на имена​ ленте, которая так​Дополнительные вычисления​ на отдельном листе,​. Вы также можете​ в область​ можно легко задать​ товару и, одновременно,​можно, как и​ 11​ Сводные таблицы и​Таблицы Excel.​ данных.​

Другие способы консолидации данных

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

Консолидация нескольких диапазонов

​Отображение и скрытие пустых​ данные. Кроме того,​ страницы могут консолидироваться​выберите пункт​В области​Также, можно отобразить числовые​ таблица». После этого,​ в процентах от​ нужных данных, вы​

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

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

​Invoice Ref​ в разных рабочих​ в виде списка​ строк.​

​ с помощью сводных​ данных отдела маркетинга​Все команды​​Поля сводной таблицы​​ значения таблицы в​

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

​ результат в графическом​​ Там, где линия​ выбор (как и​​Amount​​ книгах, — это отдельные​​ и отлично подходят​​Изменять формат.​​ диаграмм можно легко​

​ и отдела продаж​.​введите имя искомого​ виде гистограммы. Для​

​ котором следует выбрать,​ приведенном ниже примере​

​ этот лист. Так​​ элемент больше не​​ одной области в​​ представлении — очень​​ пересекает 80% (около​ ранее) почему-то совершенно​​Sales Rep.​​ объекты, для каждого​

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

​ мы изменили сумму​​ проще всего избавиться​​ нужен, просто перетащите​

​ другую, перетащите его​ удобно. Если нажать​ Ананаса) и можно​ убогий. Кроме зеленого​Region​ из которых имеется​​ данных для сводной​​Ручное и условное форматирование​

​ и тенденции. И​​ отдела, а в​Мастер сводных таблиц и​Это поле теперь будет​ с числовым значением​ создавать, таблицу или​

​ расходов на​​ от сводной таблицы.​ его за пределы​​ в целевую область.​​ кнопку​​ мысленно провести вертикальную​ и чисто белого​​01/01/2016​​ собственная копия данных​

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

Консолидация данных с использованием одного поля страницы

​ списка полей или​Подготовка​Создать​ линию, чтобы отделить​ варианта предлагается еще​2016-0001​ в памяти и​

​ сводной таблицы новые​Изменение стиля общего формата​ сводные диаграммы помогают​ данные всех трех​

​и нажмите кнопку​ части списка.​ во вкладку «Главная»,​​ кнопку «Сводная таблица».​​.​

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

​ темно-серый:​​$819​ в рабочих книгах.​​ и обновленные данные​​ сводной таблицы.​​ принимать взвешенные решения​​ отделов. Чтобы создать​​Добавить​

​Консолидация данных представляет собой​ жмем на кнопку​Открывается окно, в котором​Вы можете настроить такие​

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

​ из таблицы Excel​​Изменение числового формата для​​ на основании критически​​ консолидацию с использованием​​, а затем — кнопку​ удобный способ объединения​​ «Условное форматирование», переходим​​ нам опять нужно​

​ параметры в диалоговом​​ Online.​​ одна из особенностей​

​ в которой нет​ автоматически сформирована модель​ неважных (справа от​Небогато для программы, на​North​ сводные таблицы.​​ автоматически включаются в​​ полей.​

​ важных данных вашей​​ нескольких полей страницы,​ОК​ данных из нескольких​ в пункт «Гистограммы»,​ выбрать диапазон, или​

​ окне​​Важно:​​ сводной таблицы, благодаря​

​ пустых строк или​​ прогноза с формулами:​​ Ананаса). Мегаполезная диаграмма​ которую миллиард пользователей​01/01/2016​ Когда вы обновляете данные​ операцию обновления.​Включение форматирования сервера OLAP.​​ организации. Вы также​​ сделайте следующее:​

Консолидация данных с использованием нескольких полей страницы

​.​ источников в одном​ и выбираем понравившийся​ название таблицы. Как​Параметры поля значений​ Не рекомендуется создавать сводные​ которой можно быстро​ столбцов. Рекомендуется использовать​Приятная штука. Раньше мы,​ для ABC-анализа и​ по всему миру​2016-0002​ в новой сводной​Использование динамического именованного диапазона.​Дополнительные сведения см. в​ можете подключаться к​Добавьте мастер сводных таблиц​В книге щелкните пустую​ отчете. Например, если​

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

​ и просто изменять​ таблицу Excel, как​ например, на тренинге​​ подобных ему вещей.​​ пялятся по 5-10​

​$456​​ таблице, Excel также​​ Чтобы было проще обновлять​​ статье Разработка макета​​ внешним источникам данных,​

​ и диаграмм на​​ ячейку, которая не​ в каждом из​​Как видим, гистограмма появляется​​ сама подтянула имя​​Дополнительные вычисления​​ над ними в​​ ее вид.​

​ в примере выше.​ по прогнозированию делали​Другое название «диаграмма разброса»​ часов в сутки​

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

​ только в одной​​ нашей таблицы, так​​.​​ электронной таблице одновременно​​Список полей сводной таблицы​Таблицы — это отличный источник​​ такое вручную «от»​​ или Box-and-Whiskers Chart.​

​ иногда. По дизайну​​South​​ исходной сводной таблице​

​ можете создать динамический​ таблицы.​ SQL Server, кубам​ Для этого:​ таблицы.​ компании есть сводная​​ ячейке. Для того,​​ что тут ничего​

​диалоговое окно «Дополнительные​​ с другими пользователями.​Соответствующие поля в сводной​ данных для сводных​ и «до» -​ Весьма часто применяемый​

​ еще есть куда​​01/01/2016​ и наоборот. Когда​​ именованный диапазон и​Сводные диаграммы служат для​ служб SQL Server​

​Щелкните стрелку рядом с​​Щелкните значок мастера на​ таблица расходов, с​ чтобы применить правило​ больше делать не​ вычисления»» />​Выделите таблицу или диапазон​ таблице​​ таблиц, так как​ и занимало это​ в статистической оценке​ расти — это​2016-0003​

​ вы группируете элементы​

​ использовать его имя​​ графического представления данных,​ Analysis Services, Azure​​ панелью инструментов и​​ панели быстрого доступа.​​ помощью консолидации данных​ гистограммы для всех​ нужно. В нижней​Отображение значения как результата​​ в электронной таблице.​​Суммировать по​ строки, добавляемые в​ очень прилично времени.​ тип диаграммы, отображающий​ факт. (Прим. автора:​$538​ или отменяете их​ в качестве источника​ содержащихся в сопоставленных​ Marketplace, файлам подключения​ выберите​

​На странице​​ можно объединить такие​ ячеек таблицы, кликаем​​ части диалогового окна​​ вычисления и как​​На вкладке​По умолчанию поля сводной​​ таблицу, автоматически включаются​​Также в этой версии​ для набора данных​ я один устал​​Jones​​ группировку либо создаете​ данных для сводной​ с ними сводных​ к данным Office​Дополнительные команды​Шаг 1​ значения в корпоративный​ на кнопку, которая​ можно выбрать место,​ процента​Вставка​ таблицы, которые находятся​

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

​ от этого плоского​​South​​ вычисляемые поля или​ таблицы. Если вы​ таблицах. Как и​ (ODC-файлам), XML-файлам, базам​.​мастера выберите параметр​​ отчет о расходах.​​ появилась рядом с​

​ где будет создавать​

Обзор сводных таблиц и сводных диаграмм

​Просто перетащите элемент в​нажмите кнопку​ в области​ при обновлении данных,​ и статистических функций​среднее арифметическое — крестообразная​ безликого flat-design везде​01/01/2016​ вычисляемые элементы в​ расширите именованный диапазон,​ сводные таблицы, сводные​ данных Access и​В списке​в нескольких диапазонах консолидации​ Такой отчет может​ гистограммой, и в​ сводная таблица: на​ раздел​Сводная таблица​Значения​ а все новые​ переехало в категорию​ зарубка​ и кругом?)​2016-0004​ одной таблице, изменения​ чтобы включить в​ диаграммы — интерактивные объекты.​ текстовым файлам, чтобы​Выбрать команды из​, а затем нажмите​ содержать итоговые и​ открывшемся окне переводим​

​ новом листе (по​​Значения​.​, обобщаются с помощью​ столбцы добавляются в​Совместимость (Compatibility)​медиану (50% квантиль) -​В правом верхнем углу​$1,009​ будут внесены в​ него дополнительные данные,​

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

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

​ горизонтальная линия на​ экрана появилось поле​

​Barnes​ обе таблицы. Если​ просто обновите сводную​ диаграммы на экране​ или создавать на​Все команды​

​Далее​ продажам, текущим уровням​ «Ко всем ячейкам».​ на этом же.​ настройте параметры​ окно​

​СУММ​Поля сводной таблицы​ появились их более​ боксе​Помощника​North​

​ вам необходимо, чтобы​ таблицу, и в​ отображается область фильтра​ основе сводных таблиц​.​.​

​ запасов и наиболее​Теперь, наша сводная таблица​ Конечно, в большинстве​Операция​

​Создание сводной таблицы​. Если Excel считает​. В противном случае​ совершенные «потомки».​нижний (25%) и верхний​

​. Это, своего рода,​

​ одна сводная таблица​ нее будут включены​ сводной диаграммы. С​ другие таблицы.​

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

​Выберите в списке пункт​На странице​ популярным продуктам в​ приобрела презентабельный вид.​ случаев, намного удобнее​​и​​, в котором будет​ данные текстом, к​ необходимо вручную обновлять​Technical Preview — это​ (75%) квантили -​ реинкарнация знаменитой Скрепки​

​2016-0005​​ не зависела от​

​ эти новые данные.​ помощью этой области​Примечание:​Мастер сводных таблиц и​

​Шаг 2а​ рамках всей организации.​

​Создать сводную таблицу можно,​ использовать сводную таблицу​Дополнительные вычисления​

​ указан ваш диапазон​ ним применяется функция​​ диапазон исходных данных​

​ не релиз и,​​ нижняя и верхняя​​ — быстрая встроенная​​$486​​ другой, создайте сводную​​Включение итогов.​​ вы можете сортировать​

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

​выберите параметр​Для подведения итогов и​

​ применив Мастер сводных​ на отдельном листе.​​для каждой из​

​ или имя таблицы.​СЧЁТ​ или использовать формулу​ возможно, в финальной​ границы бокса​

​ поисковая система по​Jones​ таблицу на основе​

​ Excel автоматически создает промежуточные​ и фильтровать исходные​

​ снимки экрана созданы​и нажмите кнопку​Создать поля страницы​ обобщения результатов по​

​ таблиц. Но, для​ Но, это уже​ копий.​В разделе​

​. Именно поэтому так​ динамического именованного диапазона.​​ версии мы увидим​

​выбросы — в виде​ всем функциям и​

​South​ исходного источника данных,​ и общие итоги​

​ данные, используемые в​ в Excel 2016. Если​

​Добавить​​, а затем нажмите​

​ данным в отдельных​ этого сразу нужно​

​ индивидуальное дело каждого​При добавлении новых данных​

​Укажите, куда следует поместить​ важно не использовать​

​Все данные в столбце​

​ дополнительно еще какие-то​ отдельных точек​ инструментам Excel. В​01/02/2016​

Сведения о сводных диаграммах

​ а не копируйте​ в сводной таблице.​ сводной диаграмме. Изменения​ вы используете другую​, а затем — кнопку​ кнопку​ диапазонах можно консолидировать​ вывести данный инструмент​ пользователя, которое зависит​ в источник необходимо​ отчет сводной таблицы​ разные типы данных​ должны иметь один​ изменения и улучшения.​максимальное и минимальное значение​ это поле можно​2016-0006​ исходную сводную таблицу.​ Если источник данных​ макета и данных​ версию, то в​ОК​

​Далее​ их в сводной​ на Панель быстрого​ от его предпочтений,​ обновить все основанные​установите переключатель​ для полей значений.​ и тот же​ Но, судя по​ — в виде​ начать вводить название​$948​

​ Просто имейте в​ содержит автоматически вычисляемые​ в связанной сводной​

Различия между сводными и обычными диаграммами

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

​ виду, что если​​ промежуточные и общие​ таблице немедленно отражаются​ немного другой интерфейс,​В книге щелкните пустую​На странице​ листе. Диапазоны могут​ меню «Файл», и​​ Мы же просто​​ таблицы. Чтобы обновить​или​ функцию, которая по​ следует вводить даты​ ждать не стоит​

​Для указанного набора данных​​ и​North​ выполнять такие операции​ итоги, которые вы​ в макете и​

​ но если не​​ ячейку, которая не​Шаг 2б​ находиться в той​ жмем на кнопку​ жмем на кнопку​ сводную таблицу, можно​На существующий лист​ умолчанию используется для​ и текст в​ (кто-то скажет, что​ отображает количество элементов,​Помощник​​01/02/2016​​ слишком часто, это​

​ создали с помощью​​ данных в сводной​ указано иное, функции​ является частью сводной​сделайте следующее:​ же книге, что​ «Параметры».​ «OK».​ щелкнуть правой кнопкой​. При выборе варианта​ вычислений. Для этого​ одном столбце.​ это к лучшему,​ попадающих в заданные​оперативно выдаст список​2016-0007​

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

Создание сводной таблицы или сводной диаграммы на основе данных листа

​ мыши в любом​На существующий лист​ щелкните стрелку справа​Сводные таблицы применяются к​ возможно). Microsoft целенаправленно​ интервалы значений. Ширину​ подсказок, которыми можно​$740​ объеме свободной памяти.​Промежуточный итог​Как и в обычных​​Сводная таблица — это интерактивный​​На странице​ выделите диапазон ячеек,​ или в других​ переходим в раздел​ листе открывается форма​ месте ее диапазона​вам потребуется указать​ от имени поля​ моментальному снимку данных,​ и методично от​ интервалов либо их​ воспользоваться:​Barnes​Сводные диаграммы.​в группе​ диаграммах, в сводных​ способ быстрого обобщения​

​Шаг 1​​ а затем вернитесь​ книгах. Консолидированные данные​ «Панель быстрого доступа».​ создания сводной таблицы.​ и выбрать команду​ ячейку для вставки​ и выберите команду​ который называется кэшем,​ версии к версии​ количество можно задавать.​Само-собой, требует простых и​

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

​ правильных формулировок с​​01/03/2016​ таблицу или сводную​на вкладке​ данных, категории, маркеры​ Вы можете использовать​в нескольких диапазонах консолидации​ таблиц и диаграмм​ обобщать на регулярной​ команд на ленте.​​ части окна расположен​​Если вам больше не​​Примечание:​​.​​ не изменяются.​​ функции и потихонечку​ в частотном анализе,​ официальной терминологией («спарклайны»,​2016-0008​ диаграмму на основе​Данные​

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

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

​ а не «микродиаграммы»​$543​​ другой сводной таблицы,​, то с помощью​ Кроме того, вы​ анализа числовых данных​ кнопку​Добавить​ мере необходимости.​ ищем «Мастер сводных​ а ниже четыре​ просто выделите ее​ за пределами таблицы​

​ разделе​​ опыта работы со​Хорошо, что появились, наконец,​ подобных вещах. Раньше​ и т.д.), но​Smith​ но вам не​ этой же команды​ можете изменить тип​ и для получения​Далее​.​Итоговый консолидированный отчет сводной​ таблиц и диаграмм».​

​ области:​ и нажмите клавишу​​ или диапазона.​Суммировать по​ сводными таблицами или​ новые типы диаграмм,​ подобная задача решалась​ штука приятная. Начинающим​North​ удастся создать сводную​ удалите промежуточные и​ диаграммы и другие​

​ ответов на разнообразные​.​Совет:​ таблицы может содержать​

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

​ Выделяем его, жмем​​Названия строк;​ DELETE. Это не​Нажмите кнопку​. Обратите внимание на​ вы не знаете,​ которые все давно​ обычно группировкой по​ пользователям в ситуации​01/03/2016​ диаграмму непосредственно на​​ общие итоги, прежде​​ параметры, например названия,​ вопросы, связанные с​На странице​ Если диапазон ячеек находится​ следующие поля в​ на кнопку «Добавить»,​Названия столбцов;​ повлияет на другие​ОК​ то, что при​ с чего начать,​ ждали, но есть​ числовым интервалам в​ «помню, что есть​2016-0009​ основе другой сводной​ чем создавать сводную​ расположение условных обозначений,​ данными. Сводные таблицы​Шаг 2а​ в другой книге,​ области​ а потом на​

​Значения;​​ данные и сводные​. Excel создаст пустую​ изменении метода вычисления​ лучше воспользоваться​ еще куда расти​ сводных таблицах либо​ функция, но не​$820​ диаграммы. Изменения, вносимые​ таблицу.​ метки данных, расположение​ предназначены для выполнения​выберите параметр​ сначала откройте ее,​Список полей сводной таблицы​ кнопку «OK» в​Фильтр отчёта.​ таблицы или диаграммы.​ сводную таблицу и​ Excel автоматически добавляет​рекомендуемой сводной таблицей​ — за кадром​ использованием надстройки​ помню где» должно​

​Brown​ в сводную диаграмму,​​Вы можете получить данные​ диаграммы и т. д.​ следующих задач:​Создать поля страницы​ чтобы упростить выбор​, добавляемой в сводную​ правом нижнем углу​Просто, перетаскиваем мышкой нужные​ Если сводная таблица​ выведет список​ его название в​. При использовании этой​ остались проектные диаграммы​Пакет Анализа​ понравиться.​South​ влияют на связанную​ из внешнего источника​Вот сводная диаграмма, основанная​запрос больших объемов данных​, а затем нажмите​ данных.​ таблицу: «Строка», «Столбец»​ окна.​ нам поля таблицы​ находится на отдельном​Поля сводной таблицы​ раздел​

​ функции Excel определяет​​ (Gantt, Timeline), диаграммы-шкалы​.​Надеюсь, что в будущем​…​ сводную таблицу и​ данных, например базы​ на приведенном выше​ различными понятными способами;​ кнопку​В разделе​ и «Значение». Кроме​Как видим, после наших​ в соответствующие нашим​

Изменение исходных данных существующей сводной таблицы

​ листе, где больше​.​Пользовательское имя​ подходящий макет, сопоставляя​ («термометры») и т.д.​Надстройка для импорта данных​ эта штука будет​…​ наоборот.​ данных, куба OLAP​ примере сводной таблицы.​подведение промежуточных итогов и​Далее​Во-первых, укажите количество полей​ того, в отчет​ действий, на Панели​ потребностям области. Тут​ нет нужных данных,​В области​(например, «Сумма по​ данные с наиболее​ Про то, что​Power Query​ не просто искать​…​Изменение исходных данных может​

​ или текстового файла.​Дополнительные сведения см. в​​ вычисление числовых данных,​.​ страницы сводной таблицы​ можно включить до​ быстрого доступа появился​ не существует какого-либо​ вы можете просто​Поля сводной таблицы​ полю имя_поля»), но​ подходящими областями в​ спарклайнов давно можно​, ранее поставлявшаяся для​ в справке, а​Сводная таблица в Excel​ привести к тому,​ Например, у вас​ статье Создание сводной​ обобщение данных по​На странице​введите​ четырех полей фильтра,​​ новый значок. Кликаем​​ четкого установленного правила,​ удалить этот лист.​

​вверху установите флажки​​ вы можете изменить​ сводной таблице. Это​ было сделать не​ Excel 2013 отдельно,​ поддерживать голосовой ввод​ может резюмировать данные,​ что для анализа​ может быть база​ диаграммы.​ категориям и подкатегориям,​​Шаг 2б​​0​ которые называются «Страница1»,​ по нему.​ какие поля следует​ Так проще всего​

Что такое сводная таблица?

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

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

​сделайте следующее:​, а затем нажмите​ «Страница2», «Страница3» и​После этого, открывается мастер​ перемещать, ведь все​ избавиться от сводной​​ вы хотите добавить​​ изменить числовой формат​​ точку для дальнейших​​ существенно больше, как​​ На вкладке​​ морфологию — тогда​​ таблице, показать количество​​ данные. Например, вам​ о продажах, для​​ стандартными диаграммами, то​​ и формул;​Перейдите в книгу и​

​ кнопку​ «Страница4».​ сводных таблиц. Как​ зависит от таблицы-первоисточника,​ таблицы.​ в сводную таблицу.​

​ для всего поля,​	​ экспериментов. После создания​	​ в оригинале -​	​Данные (Data)​	​ можно будет просто​
​ записей или сумму​	​ может быть необходимо​	​ которых вы хотите​	​ работа со сводными​	​развертывание и свертывание уровней​
​ выделите диапазон ячеек,​	​Далее​	​Каждый из диапазонов данных​	​ видим, мы имеем​	​ и от конкретных​
​Сводные таблицы Excel предоставляют​	​ По умолчанию нечисловые​	​ нажмите кнопку​	​ рекомендуемой сводной таблицы​	​ тоже молчу.​
​она представлена в​	​ сказать Excel’ю что​	​ значений по любому​	​ удобно переключаться из​	​ создать сводку и​
​ диаграммами не будет​	​ представления данных для​	​ а затем вернитесь​	​.​	​ следует преобразовать в​
​ четыре варианта источника​	​ задач, которые могут​	​ возможность пользователям в​	​ поля добавляются в​	​Число. ​
​ вы можете изучить​	​Приятно, что в программу​	​ виде группы​	​ вы хотите сделать:​	​ столбцу. Например, вот​
​ тестовой базы данных​	​ которые вам необходимо​	​ для вас проблемой,​	​ получения точных сведений​	​ в мастер сводных​
​На странице​	​ формат перекрестной таблицы​	​ данных, откуда будет​	​ меняться.​	​ одном месте группировать​
​ область​	​.​	​ различные ориентации и​

​ по-умолчанию встраивают полезные​Скачать & Преобразовать​ «Сделай квартальный отчет​ такая сводная таблица​ в рабочую базу​ проанализировать.​ так как большинство​ о результатах, детализация​ таблиц и диаграмм​Шаг 3​ с совпадающими именами​ формироваться сводная таблица:​Итак, в данном конкретном​

​ значительные объемы информации,​строк​Совет:​ изменить порядок полей​ надстройки (Power Query,​

​:​ по регионам и​ показывает общую сумму​ данных. Переопределив исходные​Файлы подключения к данным​ действий для этих​ итоговых данных по​ и нажмите кнопку​укажите, следует ли​ строк и столбцов​​в списке или в​​ случае, мы переместили​ содержащейся в громоздких​​, поля значений даты​​ Так как при изменении​ для получения нужных​ Power Pivot), но​При помощи инструментов этой​ отправь его шефу!»​ продаж каждого из​​ данные, вы можете​​ Office.​

​ двух типов диаграмм​ интересующим вопросам;​Добавить​ добавлять сводную таблицу​ для элементов, которые​​ базе данных Microsoft​​ поля «Пол» и​ таблицах, а также​

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

​ вы хотите объединить.​ Excel;​ «Дата» в область​ составлять комплексные отчеты.​

​ область​ разделе​
​Вы также можете скачать​
​ тогда уже и​

​ в Excel таблицы​

Новые возможности Excel 2016

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

Общий вид

​столбцов​Суммировать по​ интерактивный учебник Создание​ на Fuzzy Lookup​ из практически всех​ в Excel аж​ года:​ используя данные, структура​ Office (ODC-файл) для​ и некоторые отличия.​ строки («сведение») для​ Если диапазон ячеек находится​ лист, и нажмите​ не следует включать​ (другой файл);​ «Категория персонала» в​ сводных таблиц обновляются​, а числовые поля —​

​обновляется имя поля​​ первой сводной таблицы.​​ с Power Map​ существующих основных форматов​ в 1997 году​Ниже представлена более сложная​ которых похожа на​ получения внешних данных​Ориентация строк и столбцов.​ просмотра различных сводок​ в другой книге,​

​ итоговые строки и​в нескольких диапазонах консолидации;​ область «Названия столбцов»,​ автоматически при изменении​ в область​ сводной таблицы, не​Рекомендуемые сводные таблицы​ расщедриться. К сожалению,​ баз данных, интернета​ — почти 20​ сводная таблица. В​ структуру информации, получаемой​ для сводной таблицы,​

Помощник

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

​ диаграммы в сводной​ данных;​ чтобы упростить выбор​.​ приведенном ниже примере​ или в сводной​ область «Название строк»,​ с ними таблицы.​. Вы также можете​ сводной таблицы до​Щелкните ячейку в диапазоне​

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

Новые типы диаграмм

​ Давайте выясним, как​ вручную перетаскивать элементы​ завершения ее настройки.​ исходных данных и​ что мы так​ можно средствами Power​ в этом вопросе​ разбиты по месяцам:​ эти данные значительно​ нее. Рекомендуется получать​ удастся изменить ориентацию​ условное форматирование наиболее​В разделе​ страницы, содержащее элемент​ в формате перекрестной​Внизу следует выбрать, что​ платы» в область​ создать сводную таблицу​ в любые поля​ Вместо того чтобы​ таблицы.​ и не увидим,​ Query еще и​ тронулся (не без​Еще одно преимущество сводных​

Водопад (Waterfall Chart)

​ отличаются, и в​ внешние данные для​ строк и столбцов​ важных и часто​Во-первых, укажите количество полей​ для каждого исходного​ таблицы.​ мы собираемся создавать,​ «Значения». Нужно отметить,​ в программе Microsoft​ сводной таблицы. Если​ вручную изменять имена,​На вкладке​ похоже, в новой​ обработать, «доводя до​ дружеских пенделей разработчикам​

Иерархическая (Treemap Chart)

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

Солнечные лучи (Sunburst Chart)

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

Парето (Pareto Chart)

​ MVP, скажу по​ их помощью можно​ дополнительных полей, возможно,​Источник данных OLAP.​Выбор источника данных​ к нужным сведениям;​ которые вы хотите​ всех диапазонов, сделайте​ содержащие элементы, которые​ и жмем на​ из другой таблицы​ Excel​ его за пределы​(​Рекомендуемые сводные таблицы​ для работы с​добавить вычисляемые столбцы или​ секрету). В Excel​ быстро извлечь данные​ будет проще создать​ Когда вы получаете исходные​. Вместо этого вы​представление кратких, наглядных отчетов​ использовать.​ следующее:​ представляют один или​ кнопку «Далее».​ возможны только в​Хотя, мы будем рассматривать​ списка полей или​в меню «Изменить»​.​

Ящик с Усами (BoxPlot Chart)

​ диапазонами (сравнение диапазонов,​ убрать ненужные​ 2016 появилось сразу​ из любой части​ другую сводную таблицу​ данные из базы​ можете изменить ориентацию​

• ​ с примечаниями на​В группе​
• ​Добавьте мастер сводных таблиц​ несколько исходных диапазонов.​После этого, появляется окно​
• ​ последней области. Как​ процесс создания сводной​ снимите его флажок.​), в поле​
• ​»Рекомендуемые сводные таблицы»​ например), ни улучшений​
• ​консолидировать данные из нескольких​ аж 6 принципиально​ таблицы. Например, если​

Частотная гистограмма (Histogram Chart)

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

Power Query

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

​ввести​ сводной таблицы» />​ Visual Basic (которую​ автоматически и т.д.​ большинство из которых​ продаж продавца по​ обновлении.​

​ возвращаются в Excel​ сводной таблице.​Например, слева показан простой​ метку элемента в​ Для этого:​

• ​ маркетинга, отдела продаж​
• ​ при желании можно​ проделывали данные манипуляции​
• ​ 2010, но данный​ сводной таблицы, благодаря​Сумма по полю​

​Excel проанализирует данные и​ с 1997 года​В общем и целом​ в старых версиях​ фамилии​ При обновлении сводной таблицы​ только в виде​

Сводные таблицы

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

​, а поле​ предоставит несколько вариантов,​ не меняли), ни​ — весьма полезное​ можно было построить​Brown​

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

Инструменты прогнозирования

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

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

​ диапазона​Дополнительные команды​ с данными по​ на кнопку «Далее».​​ таблица в левой​​За основу возьмем таблицу​​Список полей сводной таблицы​​Дополнительные вычисления​

​Выберите сводную таблицу, которая​Надеюсь дожить до момента,​ в Excel большие​ танцев с бубном.​Jan​ таблиц, основанных на​

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

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

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

Итоговые выводы

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

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

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

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

​ Сводные диаграммы основаны​ данных листа.​Пример​.​ таблица, в которой​ или на новом.​

Финансы в Excel

Главная Статьи Сводные таблицы Редактирование сводной таблицы

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

Содержание
Подключение макросов
Функциональность
Алгоритм работы
Программный код
События рабочей книги (ThisWorkbook):
Процедура поиска и вывода данных для редактирования:
Исправление ошибки работы с датами в Excel 2007:

Вложения:

edit_pivottable.xls

[Редактироание сводной таблицы]

70 kB

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

Подключение макросов

Для работы примера требуется подключение макросов VBA. В Excel 2002-2003 может потребоваться предварительно изменить безопасность макросов до среднего или низкого уровня (Сервис \ Макросы \ Безопасность). В Excel 2007 щелкните на строку сообщения под лентой, а затем подтвердите операцию.

Без подключенных макросов пример будет работать в стандартном режиме отображения деталей (drill-down) при двойном клике в области данных сводной таблицы.

Функциональность

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

В примере исходные данные размещены на листе «Data», а сводная таблица на листе «Отчет». В сводной таблице имеются 2 поля данных: «Объем», «Выручка» и вычисляемое поле «Средняя цена». Двойной клик доступен на обычных полях, при выборе вычисляемого поля никаких действий не производится, так как в этом случае, очевидно, нечего редактировать.

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

Обратите внимание, что в двух окнах находится одна и та же рабочая книга. Это видно по ярлыкам листов. Там же при первом открытии примера вы заметите появление нового листа «Filter». Этот лист используется для организации фильтра на листе исходных данных.

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

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

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

Алгоритм работы

1. Устанавливается свойство сводной таблицы, отменяющее стандартное поведение на двойной клик.
2. На уровне листа, на котором располагается сводная таблица, перехватывается событие двойного клика в области данных.
3. Проверяется, не является ли поле вычисляемым. Создается пустой лист для фильтрации исходных данных.
4. Формируется значения фильтра через проверку диапазонов областей строк, столбцов и страниц сводной таблицы. Эти значения записываются на служебный лист.
5. С помощью операции «Расширенный фильтр» фильтруется исходный диапазон данных.
6. Создается новое окно, в которое выводится отфильтрованный диапазон исходных данных.
7. Включается событие на активизацию окна Excel. При возврате в окно со сводной таблицей, второе окно с исходными данными закрывается.

Программный код

Небольшие комментарии к наиболее интересным частям программного кода VBA.

События рабочей книги (ThisWorkbook):

FlagCheckActiveWindow – публичное свойство для включения/отключения проверки активизации окна.