Ссылка на ячейку сводной таблицы excel

Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Предположим, что из вот такой базы данных по продажам:

…вы создали небольшую, но симпатичную сводную таблицу:

Но ваш руководитель хочет не совсем ее, а что-то похожее на:

То есть имеем несколько ощутимых трудностей:

• Исходный внешний вид сводной таблицы не подходит — дизайн отчета должен соответствовать корпоративным стандартам (цвета, логотипы, спарклайны, стрелки и т.д.). «Дорабатывать напильником» дизайн сводной — долгий и мучительный процесс. И не факт, что красота не слетит после пересчета и обновления.
• Из всей сводной для отчета вам нужны не все данные, а только конкретные модели Ford по Питеру — придется руками фильтровать.
• Стандартные итоги в сводной нам не подходят, т.к. нужны суммы по выручке в зеленых ячейках, но среднее по месяцу в итогах — сводная так не умеет.
• Полученные в сводной результаты — еще не конец, нам необходимо произвести с ними какие-то дополнительные вычисления: пересчитать выручку в тысячах, добавить прогноз на апрель, сравнить этот год с прошлым. Многое из перечисленного в сводных или невозможно в принципе (особенно для сводных на основе OLAP-кубов) или делается, но «через одно место» с помощью вычисляемых полей и объектов.
• Нужно построить по результатам хитрую диаграмму (обычные сводные диаграммы имеют много ограничений).

Изящным решением всех этих проблем может стать функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA), которая умеет извлекать нужные нам данные из сводной, чтобы использовать их в других таблицах или расчетах.

Сделаем на отдельном от сводной листе заготовку отчета:

Выделите первую ячейку зеленого диапазона, введите знак «равно» и щелкните по ячейке в сводной, которая содержит нужные данные, т.е. по B8, где лежит выручка Fiesta за январь. Вместо привычной ссылки а-ля «морской бой» Excel вставит функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ:

Давайте разберем ее подробно:

• Первый ее аргумент («Выручка») — это имя извлекаемого поля.
• Второй (Лист1!$A$4) — это адрес первой ячейки сводной таблицы, откуда мы берем данные. Этот параметр нужен, т.к. на листе может быть несколько сводных и Excel должен понимать из какой именно нужно вытащить число.
• Все остальные аргументы начиная с третьего — это попарно название поля и его значение, т.е., в нашем случае, это имя модели (Наименование=»Fiesta») и временной период (Дата=1). Поскольку в сводной была применена группировка дат по месяцам, то в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ мы получили не имя месяца, а его номер. Если бы в исходной базе данных был столбец не с датой, а с названием месяца, то группировка была бы не нужна и вместо единички был бы просто «январь».

А теперь самое интересное.

Аккуратно замените в формуле «Fiesta» на $С7, а единичку на D$5 и допишите в конце формулы деление на 1000, т.к. нам нужно отобразить данные в тысячах. Затем нажмите на Enter и протяните формулу на оставшиеся зеленые ячейки.

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

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

Ссылки по теме

• Что такое сводные таблицы и как их строить
• Настройка вычислений в сводных таблицах
• Группировка дат, чисел и текста в сводных таблицах

Аналогом функции ВПР для сводных таблиц является функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, которая возвращает данные, хранящиеся в отчете сводной таблицы.

Чтобы получить быстрый доступ к функции, необходимо ввести знак равенства в ячейку (=) и выделить необходимую ячейку в сводной таблице. Excel сгенерирует функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ автоматически.

Отключение создания GetPivotData

Чтобы отключить автоматическую генерацию функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, выберите любую ячейку в сводной таблице, перейдите по вкладке Работа со сводными таблицами -> Параметры в группу Сводная таблица. Щелкните по стрелке вниз, находящейся рядом с вкладкой Параметры. В выпавшем меню, уберите галку с пункта Создать GetPivotData.

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

Использование ссылок на ячейки в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Вместо указания названия пунктов или полей в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, вы можете ссылаться на ячейки, находящиеся на листе. В примере ниже ячейка E3 содержит название продукта, а формула в ячейке E4 ссылается на нее. В результате будет возвращен суммарный объем по тортам.

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

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

В примере ячейка E3 содержит название поля данных «Количество», и было бы неплохо ссылаться на эту ячейку в функции, вместо того, чтобы иметь название поля в формуле ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.

Однако, если мы поменяем первый аргумент поле_данных на ссылку на ячейку E3, Excel вернет нам ошибку #ССЫЛКА!

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(E3;$A$3)

Проблему решит простое добавление пустой строки (“”) в начало или конец ссылки на ячейку.

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(E3&»»;$A$3)

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

Использование дат в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Если вы используете даты в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, у вас могут возникнуть проблемы, даже если дата отображается в сводной таблице. К примеру, аргументом формулы ниже  является дата “21/04/2013”, и сводная таблица содержит поле с датами продаж. Однако формула в ячейке E4 возвращает ошибку.

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Объем»;$A$3;»Дата»;»21/04/2013″)

Для предотвращения ошибок, связанных с датами, вы можете воспользоваться одним из следующих способов:

• Сравнять форматы дат в формуле и сводной таблице
• Использовать функцию ДАТАЗНАЧ
• Использовать функцию ДАТА
• Сослаться на ячейку с корректной датой

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

Для получения корректного результата, во время использования функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, убедитесь, что форматы дат аргумента формулы и сводной таблицы одинаковые.

В ячейке E4, в формуле использована дата формата “ДД.ММ.ГГГГ”, и в результате возвращена правильтая информация.

Использование функции ДАТАЗНАЧ

Вместо ручного ввода даты в формуле, можно добавить функцию ДАТАЗНАЧ для возврата даты.

В ячейке E4, дата введена с помощью функции ДАТАЗНАЧ, и Excel возвращает необходимую информацию.

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Объем»;$A$3;»Дата»;ДАТАЗНАЧ(«21.04.2013»))

Использование функции ДАТА

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

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Объем»;$A$3;»Дата»;ДАТА(2013;4;21))

Ссылка на ячейку с датой

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

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Объем»;$A$3;»Дата»;E3)

Если вы работаете со сводными таблицами, то, вероятно, заметили, что в формуле, которая ссылается на ячейку в сводной таблице» ссылка автоматически преобразуется в функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ с рядом аргументов.

Рассмотрим пример (рис. 170.1). Формула в ячейке 14 (которую я создал) является обычной ссылкой на ячейку G4 в сводной таблице. Но вместо того, чтобы отобразить обычную формулу Excel преобразует ее так:
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Оценка";$E$3;"Пол";"Женский";"Район";"Район1").

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

Вы также можете снять флажок Создать GetPivotData в группе Работа со сводными таблицами ► Параметры ► Сводная таблица ► Параметры. В результате Excel перестанет генерировать формулы ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Установите флажок, и Excel начнет генерировать эти формулы снова. Эта команда не действует на существующие формулы.

Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel предназначена для получения доступа к полям данных сводных таблиц и возвращает данные в соответствии с запросом (формируется на основе переданных в данную функцию аргументов).

Пример как использовать функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel

Пример 1. В таблице Excel содержатся данные о поступлениях бытовой техники различного типа и от разных производителей на склад интернет-магазина по номеру дня. Создать сводную таблицу на основе существующей, получить данные о количестве полученных ноутбуках фирмы Samsung и их общей стоимости с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.

Исходная таблица:

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

Для получения искомых данных можно вручную ввести следующую функцию:

Описание аргументов:

• «Количество» – поле сводной таблицы, данные из которого требуется получить;
• A4:C15 – диапазон ячеек, в которых находится сводная таблица;
• «Наименование»;»Ноутбук»;»Фирма»;»Samsung» – характеристика получаемых данных, на основании которой производится поиск требуемых данных.

Полученный результат:

Как видно, результат совпадает со значением, хранящимся в сводной таблице. Для получения значения поля «Сумма» воспользуемся более удобным способом получения данных, когда рассматриваемая функция генерируется автоматически. Для этого выделим ячейку G8, вставим символ «=» и выделим ячейку B7:

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

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



Выборка данных из сводной таблицы с помощью формулы Excel

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

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

Формула 1 для разницы количества:

Формула 2 для разницы общей суммы:

Полученный результат:

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

Формула для анализа данных полученных из сводной таблицы Excel

Пример 3. В таблицу Excel выгружены статистические данные сайта в виде таблицы с полями «День», «Логин», «Страна» и «Время активности». Определить среднее время активности на сайте за исследуемый период для пользователей из каждой страны, вычислить наибольшее среди полученных значений.

Исходная таблица:

Создадим сводную таблицу на новом листе и добавим поля для отображения среднего значения времени активности на сайте:

Произведем расчет для пользователя из GB (Великобритания):

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

Определим наибольшее значение с помощью соответствующей формулы:

=НАИБОЛЬШИЙ(I10:I24;1)

Полученный результат:

Как видно, на сайте в среднем больше времени проводили пользователи из UA (Украины).

Особенности использования функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel

Рассматриваемая функция имеет следующий синтаксис:

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(поле_данных; сводная_таблица; [поле1; элем1; поле2; элем2]; …)

Описание аргументов (первые два являются обязательными для заполнения):

• поле_данных – аргумент, характеризующий имя поля данных, в котором содержатся данные для извлечения. Должен быть представлен текстовой строкой, например «Покупки».
• сводная_таблица – аргумент, принимающий ссылку на всю сводную таблицу, либо на некоторый диапазон ячеек, содержащийся в ней. Необходим для определения сводной таблицы, данные из которой требуется получить.
• [поле1; элем1; поле2; элем2]; … — необязательные аргументы, характеризующие данные, которые необходимо получить. Функция принимает до 126 пар имен полей и элементов. Имена элементов необходимо заключать в кавычки (исключением являются данные числового типа и даты).

Примечания 1:

1. В качестве аргумента сводная_таблица может быть передан диапазон ячеек, который включает сразу несколько сводных таблиц. В этом случае функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ будет извлекать данные из созданной последней таблицы.
2. Если аргумент сводная_таблица принимает диапазон пустых ячеек, рассматриваемая функция вернет код ошибки #REF!.
3. Пара поле1; элем1, ссылающаяся на одну и ту же ячейку, вернут данные из данной ячейки, которые могут быть любого типа, включая код ошибки.
4. Код ошибки #ССЫЛКА! Будет возвращен в случае, если аргументы функции указывают на невидимое поле или содержат фильтр, не отображающий данные согласно установленным им условиям.
5. Для корректного отображения данных в формате Время и Дата необходимо выполнять прямое преобразование (использовать функции ДАТА, ДАТАЗНАЧ и ВРЕМЯ).

Примечания 2:

1. Сводные таблицы используются для создания удобочитаемого отчета на основе данных из имеющейся громоздкой таблицы с большим количеством полей данных.
2. В Excel реализован визуальный интерфейс создания сводных таблиц, который делает данный процесс простым и наглядным. Однако алгоритм форматирования таких таблиц не является достаточно гибким, поэтому зачастую не удается достичь ожидаемого результата.
3. Один из специалистов Microsoft предложил новый метод, согласно которому созданная сводная таблица является не окончательным действием, а лишь промежуточным этапом при создании отчетов. Требуется самостоятельно создать оболочку итогового отчета, которая затем будет заполнена данными из сводной таблицы с использованием рассматриваемой функции. При этом сводная таблица может иметь примитивный вид, не требует форматирования и может находиться на скрытом листе в качестве невидимой базы данных.
4. При необходимости можно отключить функционал автоматического генерирования рассматриваемой функции. Для этого в параметрах сводных таблиц необходимо снять флажок перед «Создать GetPivotData».
5. Синтаксис функции достаточно сложный, поэтому чтобы упростить работу с ней, можно выполнить следующие действия:

• выделить пустую ячейку и ввести символ «=»;
• выделить поле данных с требуемой информацией в сводной таблице;
• функция с требуемыми аргументами сгенерируется автоматически.

Excel: как делать ссылки на сводную таблицу

«Уважаемые сотрудники «Б & К»! Я часто пользуюсь сводными таблицами, и в связи с этим у меня вопрос: как правильно делать ссылки на ячейки сводного отчета? Дело в том, что при обычном способе создания ссылок Excel вместо адреса вставляет специальную функцию, а это иногда очень неудобно. Подскажите, есть ли простой способ решения этой проблемы? Я работаю с программой MS Excel 2010. Среди параметров программы подходящих настроек я не нашел. Надеюсь на вашу помощь. Спасибо.

Владимир Ярославцев, главный бухгалтер, г. Днепропетровск».

Отвечает Николай КАРПЕНКО, канд. техн. наук, доцент каф. Прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства

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

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

1. Становимся на свободную ячейку, пусть это будет «D3».

2. Набираем символ «=» (начало формулы).

3. Щелкаем левой кнопкой мыши на ячейке «B3» (я хочу сделать ссылку на сумму реализации по контрагенту «ТОВ «Топаз»»). В ячейке «D3» вместо ссылки мы увидим такой результат: «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Сумма»; $A$1;»Покупатель»;»ТОВ «»Топаз»»»)». При этом значение в ячейке «D3» будет равно «119,80», что соответствует объемам продаж по «ТОВ «Топаз»».

4. Копируем эту формулу вниз до ячейки «D8» (на всю высоту сводной таблицы). Результат во всех ячейках будет одинаковым — «119,80». То есть функция получения данных из сводного отчета сослалась на одну и ту же ячейку сводной таблицы.

Причина такого поведения лежит в параметрах функции «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()». Таких параметров у нее четыре. Первым идет название поля, по которому нужно выбрать итог. В нашем случае это поле «Сумма». Так это поле называлось в исходной базе, с этим именем оно и попало в сводный отчет. Вторым параметром стоит ссылка на ячейку с заголовком поля. В формуле эта ссылка выглядит как «$A$1». Кстати, абсолютная адресация в данном случае обязательна! Третий параметр — название поля, по которому Excel будет выбирать данные из сводного отчета. В формуле указано, что поиск конкретного числа в сводной таблице нужно делать по полю «Покупатель». Последний параметр — это строка для поиска конкретного значения среди покупателей. В нашей функции указано значение «ТОВ «Топаз»». Поэтому Excel выберет итог именно по этому контрагенту. Сразу бросается в глаза, что большинство параметров в функции «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()» указаны в виде текстовых строк. Именно поэтому не сработала корректировка адресов при копировании формулы в ячейки «D3:D8», и все функции вернули один и тот же результат.

Кстати, исправить такую ситуацию несложно: нужно вместо фиксированного элемента «»ТОВ «»Топаз»»»» поставить ссылку на ячейку «A3». То есть формула в ячейке «D3» должна выглядеть так: «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ («Сумма»;$A$1;»Покупатель» ;A3)» (изменения выделены полужирным начертанием). В этом варианте после копирования формулы вниз до ячейки «D8» мы получим правильные объемы реализации по каждому контрагенту.

Однако речь сейчас о другом. Использование функции «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()» имеет свои преимущества и недостатки. Среди преимуществ я бы указал, что независимо от порядка сортировки записей в сводной таблице ссылка через функцию обеспечит правильный результат. И это понятно — извлечение данных из сводного отчета функция делает по ключевому полю, а не по адресу рабочего листа! Если посмотреть на формулу в ячейке «D3», то ключевым полем для обращения к сводной таблице является название фирмы «ТОВ «Топаз»». И при этом не имеет никакого значения, где конкретно находится запись по этой фирме — на первой позиции отчета или в самом конце. Данные Excel подставит правильно.

Недостаток работы с функцией «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()» состоит в том, что нужно корректировать значение ключевого поля или заменять его ссылкой. Поэтому в некоторых случаях удобнее вместо встроенной функции использовать ссылки на ячейки сводной таблицы. Чтобы вставить такие ссылки автоматически (отказаться от использования функции «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()»), нужно знать одну тонкость.

Секрет Встроенную функцию «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()» Excel использует только при ссылках на поля в области данных сводного отчета. При организации ссылок на заголовки строк или колонок он вставляет обычные ссылки на ячейки рабочего листа.

Зная это правило, мы легко получим «нормальные» ссылки на ячейки сводной таблицы. Для этого делаем так:

1. Открываем документ, как на рис. 1.

2. Становимся на ячейку «D3».

3. Вводим символ «=» (начинаем запись формулы).

4. Щелкаем левой кнопкой мыши на ячейке «A3». Excel добавит в текущую ячейку ссылку «=A3», где записано название фирмы. В данном конкретном случае — это «ТОВ «Топаз»».

5. Нажимаем «Enter» (завершаем ввод формулы).

6. Копируем формулу в ячейку «E3».

Смотрим на содержимое ячеек «D3» и «E3». Как и следовало ожидать, там находятся обычные ссылки: «=A3» и «=B3». Одна указывает на ячейку с названием фирмы, вторая — на объем реализации. Теперь с этими ссылками можно делать все что угодно — переносить на другой лист, использовать в расчетах и т. д.

И последнее. Работа с обычными ссылками незаменима, когда нужно построить график по данным сводного отчета (!) в программе Excel 2003. При создании такого графика Excel 2003 формирует его на отдельном листе, а это не всегда удобно. Чтобы отказаться от такой возможности и построить диаграмму на текущем листе, нужно создать рабочую область со ссылками на данные сводной таблицы. А затем по этим ссылкам сформировать диаграмму. Для таблицы на рис. 1 процедура выглядит так:

1. Открываем документ, переходим на ячейку «D3».

2. Вводим в нее формулу «=A3».

3. Копируем формулу в ячейки «D3:E8». В результате мы получим копию данных из сводной таблицы в виде формул.

4. Строим график по данным «D3:E8».

5. Чтобы скрыть «рабочую область», форматируем значения в блоке «D3:D8» белым цветом или ставим график поверх ячеек «D3:D8», чтобы закрыть им вспомогательную информацию (рис. 2).

На сегодня все. Удачной работы! Жду ваши вопросы, замечания и предложения на bk@id.factor.ua , nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum .

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ)

​Смотрите также​​Следующий этап – «создать​ команды​ можно группировать данные​ Поэтому мы можем​ по «Поставщикам», добавим​) — это имя​ на сервере SQL​ДИСП​содержит формулу​ его за пределы​Щелкните сводную таблицу.​ в каждой категории.​ оперируют отдельными записями.​Доступность вычислений и параметров​ числовых.​ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Продажи»; $A$4; «Месяц»; «Март»)​Примечание:​ поля». «Далее».​Итоги по (Summarize Values​ в сводной таблице​ объединить данные, а​

​ «Дату».​ извлекаемого поля.​ Server.​​Оценка дисперсии генеральной совокупности,​​=Яблоки * 0,25​

Описание

​ сводной таблицы.​Отобразится вкладка «Работа со​Более новые версии​ Например, формула вычисляемого​ в отчете зависит​Среднее​Возвращает общий итог за​Мы стараемся как​

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

Синтаксис

​ сводными таблицами» с​ Office 2007 Online ​

​ объекта​ от того, получены​

• ​Среднее арифметическое.​​ март (30 337 ₽).​ можно оперативнее обеспечивать​ которым будем формировать​:​Допустим, у нас есть​

• ​ отчет.​​ – сгруппируем даты​Лист1!$A$4​мастере подключения к данным​ подмножеством всей генеральной​ то для июля,​ формула: к вычисляемому​ дополнительными вкладками​Важно:​=Молоко * 115%​

• ​ ли исходные данные​​Максимум​ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Продажи»; $A$4; «Месяц»; «Март»;​ вас актуальными справочными​ сводный отчет. Выделяем​… и​ построенная сводная таблица​В ячейке-мишени (там, куда​ по кварталам. Для​) — это адрес​установите соединение, выполнив​ совокупности.​ августа и сентября​ полю или вычисляемому​Анализ​ Создать формулу в сводной​умножает каждое отдельное​ из базы данных​Наибольшее значение.​ «Товар»; «Продукты»; «Продавец»;​

  ​ материалами на вашем​

Замечания

• ​ первый диапазон вместе​Дополнительные вычисления (Show Data​ с результатами анализа​ будет переноситься таблица)​

• ​ этого щелкнем правой​ первой ячейки сводной​ соответствующие действия.​ДИСПР​ ее можно заменить​ объекту.​и​

• ​ таблице, подключенной к​ значение продаж молочных​ OLAP.​Минимум​ «Батурин»)​ языке. Эта страница​ с шапкой –​ as)​

• ​ продаж по месяцам​ ставим курсор. Пишем​ кнопкой мыши по​ таблицы, откуда мы​На странице 1 укажите​Дисперсия генеральной совокупности, которая​ формулой​Вычисляемые поля отображаются в​Конструктор​ источнику данных OLAP,​ продуктов на 115 %,​Вычисления на основе исходных​Наименьшее значение.​Возвращает значение 10 201 ₽.​ переведена автоматически, поэтому​ «добавить». Второй диапазон​:​ для разных городов​ = — переходим​ любой ячейке с​

• ​ берем данные. Этот​ имя сервера базы​ содержит все сводимые​=Яблоки * 0,5​ списке полей сводной​

• ​.​ невозможно.​ после чего полученные​ данных OLAP.​Произведение​ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Продажи»; $A$4; «Регион»; «Южный»)​ ее текст может​

Пример

​ вместе с названием​Также в версии Excel​ (если необходимо, то​ на лист с​ датой. В выпадающем​ параметр нужен, т.к.​ данных и выберите​ данные.​

​.​ таблицы. Вычисляемые объекты​На вкладке​Прежде всего определитесь, что​ величины суммируются в​    При создании сводных таблиц​Произведение значений.​Возвращает значение ошибки #ССЫЛКА!,​ содержать неточности и​ столбцов – снова​ 2010 к этому​ почитайте эту статью,​ переносимыми данными –​

​На странице 2 укажите​

support.office.com

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

​ деловые решения. Но​Чтобы изменить формулу для​ полях.​Вычисления​ или вычисляемый объект.​ в именах.​ вычисляются на сервере​ аналогично функции СЧЁТ.​ отображены.​ эта статья была​ первый диапазон. Ставим​% от суммы по​ или освежить память):​ Ввод. «Размножаем» формулу,​ таблица приобретает следующий​ понимать из какой​ базу данных, таблицу​ иногда непонятно, с​ нескольких ячеек, нажмите​Выполните одно из указанных​выберите команду​ Если вы хотите​    В имени, которое содержит​

Дополнительные сведения о вычислении значений в сводной таблице

​ OLAP еще до​Стандартное отклонение​ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Продажи»; $A$4; «Товар»; «Напитки»;​ вам полезна. Просим​ птичку у единицы.​ родительской строке (столбцу)​Нам хочется слегка изменить​ протягивая вниз за​ вид:​ именно нужно вытащить​ или запрос с​

​ чего начинать, особенно​

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

• ​ два или несколько​ отображения результатов в​​Оценка стандартного отклонения генеральной​ «Продавец»; «Ерёменко»)​ вас уделить пару​ Это первое поле​- позволяет посчитать​ ее внешний вид,​ правый нижний угол​

  

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

• ​Возвращает значение ошибки #ССЫЛКА!,​ секунд и сообщить,​​ сводного отчета. Даем​​ долю относительно промежуточного​​ чтобы она отображала​​ ячейки.​​ по значению столбца​​Все остальные аргументы начиная​​На странице 3 укажите​​ количества данных, которые​​ остальные ячейки.​​ поле, щелкните в​​Вывести формулы​​ поля, создайте вычисляемое​​ не имеет значения.​​ таблице невозможно изменить​​ является подмножеством всей​​ так как для​ помогла ли она​​ ему имя –​​ итога по строке​​ нужные вам данные​​По такому же принципу​ «Стоимость». Кликнем правой​ с третьего -​ файл подключения, который​​ хранятся за пределами​​В строке формул внесите​​ любом месте сводной​​.​​ поле. Если нужно​​ В примере выше​​ способ вычисления этих​​ генеральной совокупности.​

• ​ фамилии «Ерёменко» нет​​ вам, с помощью​​ «Магазин 1». Выделяем​ или столбцу:​ более наглядно, а​​ переносим другие данные.​​ кнопкой мыши по​​ это попарно название​​ требуется создать.​​ Excel (например, в​​ изменения в формулу.​​ таблицы.​​Перед тем как редактировать​ использовать в формуле​​ ячейки​​ значений. Например, вы​Несмещенное отклонение​ итоговых данных по​​ кнопок внизу страницы.​​ второй диапазон данных​​В прошлых версиях​​ не просто вываливала​​ В результате из​​ любой ячейке или​ поля и его​Чтобы создать новое подключение​​ базе данных Microsoft​​Если есть несколько вычисляемых​​Если нужно удалить вычисляемый​​ формулу, определите, к​ данные из одного​C6:D6​ не сможете выбрать​Стандартное отклонение генеральной совокупности,​ продажам напитков.​ Для удобства также​ – ставим птичку​ можно было вычислять​ кучу чисел на​ двух таблиц получаем​​ названию столбца. Выбираем​​ значение, т.е., в​​ к базе данных​​ Access или Microsoft​

• ​ объектов или формул,​ объект, в сводной​ чему она относится:​ или нескольких конкретных​могут называться​ другую функцию сведения​ которая содержит все​

• ​Наименование=»Fiesta»​​ в виде таблицы​ OLAP). В этом​ действия.​Отобразится вкладка «Работа со​ Если формула находится​ объект.​’Север Апрель’​​ итогов и добавить​​Оценка дисперсии генеральной совокупности,​ в полях значений​​В этой статье описаны​​ «Далее».​ итогом​ мыши по расчетному​​ таблица – указываем​​ поменяются в соответствии​

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

• ​ЯблокиОбласть​​Итоги.​ их в списке​ данные.​ создать собственные формулы​ получить.данные.сводной.таблицы можно использовать​ смещений. У нас​ например, процент выполнения​или в версии​ проданного товара.​ на этом остановиться.​ ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ мы получили​.​Ниже описано, как создать​, а затем — пункт​Анализ​ ячеек одного объекта.​содержит формулу​

​    Формулы не могут ссылаться​ полей сводной таблицы.​

​Настраиваемые вычисления.​ в вычисляемых полях​ для извлечения итоговых​ получилось так:​ плана или исполнения​ Excel 2007 –​

• ​Можно выводить для анализа​ Но мы пойдем​​ не имя месяца,​В диалоговом окне​ сводную таблицу с​Порядок вычислений​в группе​ Например, если вычисляемый​=Яблоки * 0,25​ на итоговые значения​ Вы также увидите​    Служат для отображения значений​ и вычисляемых объектах.​ данных из сводной​Как видите всего в​ бюджета:​Параметры полей значений​ разные параметры, перемещать​ по другому пути.​

  ​ а его номер.​Выбор источника данных​ использованием существующего подключения​.​Вычисления​ объект с именем​для всех месяцев,​ (в примере выше —​ все вычисляемые поля​ на основе других​ Например, можно добавить​ таблицы, при условии,​ несколько кликов можно​Сортировка от минимального к​(Value Field Settings)​ поля. Но на​ Нажмем «Дополнительные параметры​ Если бы в​найдите базу, к​ к внешним данным.​Щелкните одну из формул​

  ​выберите команду​ЯблокиРасчет​ то для июля,​ это​ и вычисляемые объекты,​ элементов или ячеек​ вычисляемый объект с​ что сводные данные​

• ​ создавать сложные отчеты​ максимальному и наоборот​, то откроется очень​​ этом работа со​ сортировки». Откроется окно​ исходной базе данных​ которой требуется подключиться,​Щелкните любую ячейку на​ и нажмите кнопку​Поля, элементы и наборы​содержит формулу​ августа и сентября​Сумма за март​ созданные с помощью​ в области данных.​ формулой расчета комиссионных​ отображаются в отчете.​ из нескольких листов​- немного странное​ полезное окно, используя​ сводными таблицами в​ вида:​ был столбец не​ и нажмите кнопку​ листе.​Вверх​, а затем — пункт​=Яблоки * 0,25​ ее можно заменить​

​,​ макросов, которые написаны​

​ Например, можно отобразить​ за продажу, которые​Примечание:​ или таблиц разного​ название для функции​ которое можно задать​ Excel не заканчивается:​Установим параметры сортировки: «Дата​ с датой, а​Открыть​Выберите​или​Вычисляемое поле​для всех месяцев,​

• ​ формулой​​Сумма за апрель​ на языке Visual​ значения в поле​ могут быть разными​ Можно быстро ввести простую​ объема информации.​ ранжирования (РАНГ), вычисляющей​ кучу интересных настроек:​ возможности инструмента многообразны.​ по убыванию». Кликнем​ с названием месяца,​.​Вставка​Вниз​или​ то для июля,​=Яблоки * 0,5​и​ Basic для приложений​ данных​ в различных регионах.​

• ​ формулу получить.данные.сводной.таблицы, введя​​Начнем с простейшего: добавления​ порядковый номер (позицию)​В частности, можно легко​​ по кнопке «Дополнительно».​ то группировка была​В диалоговом окне​​>​​.​​Вычисляемый объект​​ августа и сентября​.​​Общий итог​​ (VBA) и хранятся​Сумма продаж​ Эти комиссионные будут​ знак равенства (=)​ и удаления столбцов.​​ элемента в общем​​ изменить функцию расчета​

  

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

• ​ списке значений. Например,​ поля на среднее,​ видим, что продано​​ «Автоматической сортировки при​ и вместо единички​выберите нужную таблицу​.​ других формул, чтобы​​В поле​​ формулой​ объектов или формул,​Имена полей в ссылках​ не сможете их​ продаж за месяц​ промежуточные и общие​ хотите вернуть значение​ сводную табличку продаж​

  ​ с ее помощью​ минимум, максимум и​ ВСЕГО 30 видеокарт.​ каждом обновлении отчета».​​ был бы просто​​ и нажмите кнопку​В диалоговом окне​ расположить их в​Имя​=Яблоки * 0,5​ можно настроить порядок​

• ​ на элементы.​ изменить. Если вам​​Март​ итоги в сводной​ и затем щелкнув​ по разным отделам​ удобно ранжировать менеджеров​ т.д. Например, если​​ Чтобы узнать, какие​​Теперь при появлении в​​ «январь».​​ОК​​Создание сводной таблицы​​ нужном порядке.​выберите поле или​.​ вычислений.​    Вы можете включить имя​ нужны дополнительные типы​

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

• ​Совет:​ элемент, который нужно​​Щелкните сводную таблицу.​Щелкните сводную таблицу.​ поля в ссылку​ вычислений, обратитесь к​ итог по элементам​В сводных таблицах можно​​ таблицу, содержащую данные,​​Справа от сводной таблицы​ выручке, определяя кто​ сводной таблице сумму​ для получения этого​ дат программа Excel​Аккуратно замените в формуле​Если есть несколько таблиц,​Выберите данные для анализа​ Если вы не хотите​ удалить.​Отобразится вкладка «Работа со​Отобразится вкладка «Работа со​ на элемент. Имя​ администратору базы данных​ в поле​ рассчитывать данные разными​​ которые нужно вернуть.​​ у нас была​​ на каком месте​​ на количество, то​

• ​ значения, щелкаем два​ будет сортировать их​​ «Fiesta» на $С7,​ установите флажок​щелкните​ удалять формулу безвозвратно,​Нажмите кнопку​ сводными таблицами» с​ сводными таблицами» с​ элемента должно быть​​ OLAP.​​Месяц​​ способами. Вы узнаете​​ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(поле_данных; сводная_таблица; [поле1; элем1;​​ область задач, где​​ в общем зачете:​​ мы увидим не​​ раза мышкой по​ по убыванию (от​ а единичку на​Разрешить выбор нескольких таблиц​Использовать внешний источник данных​ то можете просто​Удалить​ дополнительными вкладками​ дополнительными вкладками​ заключено в квадратные​

  ​Если исходные данные получены​.​ о доступных методах​ поле2; элем2]; …)​ мы выбирали столбцы​Сводная таблица применяется для​​ суммарную выручку, а​​ цифре «30». Получаем​​ новых к старым):​​ D$5 и допишите​​, затем — флажки соответствующих​​.​ скрыть поле или​​.​​Анализ​​Анализ​​ скобки, например:​​ из базы данных​​Для настраиваемых вычислений в​ вычислений, о влиянии​Аргументы функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ описаны​​ в списке полей.​​ быстрого анализа большого​ количество сделок по​ детальный отчет:​Сначала составим сводный отчет,​ в конце формулы​ таблиц и нажмите​

​Нажмите кнопку​ элемент. Чтобы скрыть​

​Чтобы отобразить список всех​и​и​Регион[Север]​ OLAP, то при​ полях значений доступны​ типа исходных данных​ ниже.​ Если она исчезла,​ объема данных. Она​ каждому товару:​

​Если мы изменим какой-либо​ где итоги будут​ деление на 1000,​ кнопку​Выбрать подключение​

​ поле, перетащите его​ формул, которые используются​Конструктор​Конструктор​. Используйте этот формат,​ вычислении промежуточных и​ перечисленные ниже функции.​​ на вычисления и​​Поле_данных​

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

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

​ о том, как​    — обязательный аргумент. Заключенное​

​ по табличке.​ из разных таблиц​ данных Excel всегда​ таблице либо добавим​ суммой. Начнем работу​ отобразить данные в​.​На вкладке​

​Определите, к чему относится​ таблице, выполните следующие​

Создание формул в сводной таблице

​На вкладке​​На вкладке​ #ИМЯ?, которые возникают,​ включить или исключить​Результат​

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

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

​ в двух разных​ элементов.​Значение, введенное в данное​ на сводных диаграммах.​

Добавление вычисляемого поля

1. ​ данные, которые необходимо​

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

2. ​Показать​​ объекту.​​На вкладке​​Вычисления​​Вычисления​​ полях отчета имеют​​Вычисления на основе исходных​​ поле.​​Доступные методы вычислений​

   

3. ​ извлечь.​​ этого установим галочку​​ существенно расширяет возможности​ из тысячи ячеек​

4. ​ Такое положение вещей​​ в сводной таблице​​и протяните формулу​ виде данные должны​

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

5. ​ добавить столбец.​​ на оставшиеся зеленые​​ отображаться в книге​

Добавление вычисляемого объекта в поле

1. ​Все подключения​

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

2. ​ сводной таблице можно​    Обязательно. Ссылка на любую​​ напротив «Товара»). Отчет​​Можно сформировать новые итоги​​ хотя бы одна​​Обновление данных:​​Добавим в отчет заголовок​​ ячейки.​

   

3. ​ и куда их​или укажите категорию​ таблицы. Вычисляемые объекты​

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

   

5. ​ по исходным параметрам,​​ пустая или с​​Курсор должен стоять в​ «Поставщик». Заголовок «Стоимость»​

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

   ​выберите команду​Вывести формулы​Вычисляемое поле​ с именем «Мясо»​​ на внешних данных​​ значений или точек​​ описанных ниже методов.​​ или именованный диапазон​ появляется динамика продаж​ поменяв строки и​ текстом или с​

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

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

1. ​ нажмите кнопку​ данных, к которому​

   ​ элементов в других​Формулы​.​.​ в полях «Тип»​

2. ​ других типов или​ данных в отчете.​

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

1. ​Функции сведения в полях​

   ​ ячеек в сводной​ по дням в​ столбцы местами. Можно​​ числом в текстовом​​ отчета.​​ в поле «Значения»​​ работать как с​

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

   

3. ​ значений.​ таблице. Эта информация​​ каждом отделе.​​ произвести фильтрацию данных,​​ формате) – функцию​​Либо:​

4. ​ — в сводную​ обычными формулами, а​.​Чтобы повторно использовать существующее​

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

​Выполните одно из указанных​Вывести формулы​ которую нужно изменить.​Имя​

1. ​ избежать появления ошибок​

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

2. ​Правая кнопка мыши –​​ таблицу добавятся три​​ не как со​​Таблицы будут автоматически добавлены​​ подключение или воспользоваться​​ ниже действий.​​.​​ Она может находиться​​введите имя для​

   

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

​ #ИМЯ?, ссылаясь на​ полей значений, содержащих​Все значения в каждом​ области значений отображаются​ сводной таблицы с​ по месяцам. Для​ А также наглядно​ (Count).​ обновить.​ одинаковых столбца.​

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

Определение принадлежности формулы к вычисляемому полю или вычисляемому объекту

1. ​Для первого столбца оставим​

   ​ ее жесткими ограничениями,​ а база данных​ вариант​​    ​​ формула: к вычисляемому​​ поле» или «Вычисляемый​​В поле​

2. ​ образом:​​ функция «Сумма», а​​ в процентах от​​ на основе исходных​​ извлечь.​​ кнопкой мыши по​​Для примера используем таблицу​​ в одной сводной​​ сводной таблицы при​

   

3. ​ значение «Сумма» для​ т.е. дальше можно​ Access — в список​Подключения в этой книге​Щелкните сводную таблицу.​ полю или вычисляемому​
   ​ объект».​Формула​Тип[Мясо]​ для вычисления полей​ итогового значения по​ данных. Рассмотрим пример​Поле1, элем1, поле2, элем2​ полю «Дата». Нажимаем​ реализации товара в​ таблице сразу и​ изменении данных, делаем​ итогов. Для второго​ спокойно считать любые​ подключений книги.​.​На вкладке​

   ​ объекту. Если формула​Если для одного​введите формулу для​и​​ данных, содержащих текст, —​​ этому столбцу или​ с такими исходными​    Необязательно:. 1 до 126​​ «Группировать». Выбираем «по​​ разных торговых филиалах.​ среднее, и сумму,​ по инструкции:​ – «Среднее». Для​ итоги, динамику, прогнозы,​Создание сводной таблицы для​

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

Редактирование формулы вычисляемого поля

1. ​ вычисляемого объекта задано​

   ​ поля.​Категория[Мясо]​ функция «Количество». Для​​ ряду.​​ данными:​​ пар имен полей​​ месяцам». Получается сводная​

2. ​Из таблички видно, в​​ и количество, т.е.​​Курсор стоит в любом​​ третьего – «Количество».​​ строить любую диаграмму​​ анализа данных на​​ списка и нажмите​​в группе​​ объекте, также определите,​

   

3. ​ несколько формул, то​​Чтобы использовать в формуле​​.​ дальнейшего анализа и​% от суммы по​

4. ​Сводная таблица и сводная​​ и элементов, описывающие​​ таблица такого вида:​

5. ​ каком отделе, что,​​ несколько функций расчета​​ месте отчета. Работа​

Редактирование одной формулы для вычисляемого объекта

1. ​Поменяем местами значения столбцов​ и т.д.​

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

   

3. ​ диаграмма выглядят, как​​ данные, которые нужно​​Чтобы изменить параметры в​

4. ​ когда и на​​ для одного и​​ со сводными таблицами​

5. ​ и значения строк.​​Если исходные данные поменяются,​​Создание сводной таблицы для​

Редактирование отдельной формулы для конкретной ячейки вычисляемого объекта

1. ​Открыть​выберите команду​

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

2. ​Значение в каждой строке​ показано на рисунке​

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

Удаление формулы из сводной таблицы

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

1. ​ то смело забрасывайте​ Сводная таблица.​ названия столбцов. «Σ​ лишь обновить сводную​

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

2. ​ элемент, указав его​ например «Среднее», «Максимум»​

   • ​ процентах от итогового​ сводную диаграмму на​ порядке. Имена элементов,​ имеющихся полей строк​

   • ​ величину продаж по​ мышкой в область​Параметры.​ значения» — в​

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

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

   

4. ​ с помощью списка​​ отчет сводной таблицы:​​.​На вкладке​ его имя. Для​

5. ​Добавить поле​​ (с учетом того,​​ того, можно создавать​

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

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

1. ​ поле несколько раз​

2. ​ Данные – Обновить​​Сводный отчет стал более​​Обновить​​ полей​​, выберите место.​​В поле​​Параметры​​ остальных формул в​​. Например, чтобы вычислить​

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

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

   1. ​). Даже если завтра​

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

   3. ​ будут соответствовать вычислениям​ кавычки. Для сводных​ товаров, а не​ еще одну таблицу​ что-то похожее:​ – ОК.​

      ​Научимся прописывать формулы в​ изменится количество моделей​ для сводной таблицы​ на новый лист,​выберите поле, которое​Сервис​ не только имя​ 15 %, для каждого​ они отсортированы в​ отчета или другие​ значения​ в связанной сводной​ таблиц OLAP элементы​ по отделам.​ Excel, где посредством​…а потом задавайте разные​

      ​Добавим в сводную таблицу​ сводной таблице. Щелкаем​ или, скажем, Fiesta​Обновление данных в сводной​​ начиная с ячейки​​ нужно удалить.​выберите команду​ самого вычисляемого объекта,​​ значения в поле​​ настоящий момент).​ данные листа. Для​базового элемента​ таблице.​ могут содержать исходное​А вот что получится,​

2. ​ формул показать итоги.​ функции для каждого​

   ​ новые поля:​​ по любой ячейке​

   1. ​ будет уже не​

   2. ​ таблице​​ A1, выберите вариант​​Нажмите кнопку​​Формулы​​ но и имена​​ «Продажи», введите формулу​​Тип[1]​​ этого нужно создать​​в соответствующем​

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

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

   5. ​Удаление сводной таблицы​​На новый лист​​Удалить​

      ​, а затем — пункт​ элементов, на пересечении​​=Продажи * 15%​

   6. ​ — это​ вычисляемое поле или​

   7. ​базовом поле​​ столбца​​ также исходное имя​​ «дату» и добавим​​ информацию непродуктивно. Недолго​​ по очереди по​​ данными вставляем столбец​​ инструмент «Работа со​​ машиной в сводной,​

   8. ​Предположим, что из вот​​.​​.​

   9. ​Вывести формулы​​ которых используется формула.​​.​

   10. ​Молоко​​ вычисляемый объект в​​.​

       ​Месяц​ элемента. Пары полей​ «отдел»:​​ и ошибиться.​

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

   11. ​% от суммы по​содержит элементы​

       ​ и элементов сводной​А вот такой отчет​Самое рациональное решение –​ выбирая команду​ отразим, какую выручку​

   12. ​ вкладке «Параметры» выбираем​ равно ее найдет​

3. ​ по продажам:​ на активный лист,​    ​Найдите в списке формулу,​ по умолчанию для​

   1. ​Добавить​

   2. ​Тип[2]​​Использование формул в сводных​​ родительской строке​​Март​​ таблицы OLAP может​​ можно сделать, если​​ это создание сводной​​Параметры поля​​ получит магазин от​

   3. ​ «Формулы» — «Вычисляемое​ и правильно извлечет​​…вы создали небольшую, но​​ выберите вариант​​Щелкните поле, содержащее элемент,​​ которую нужно изменить.​

   4. ​ вычисляемого объекта с​.​ —​ таблицах​

Удаление формулы из сводной таблицы

​Рассчитывает значения следующим образом:​​и​ выглядеть следующим образом:​ перетащить поля между​ таблицы в Excel:​(Field settings)​ реализации товара. Воспользуемся​ поле».​

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

   ​ Она может находиться​ именем​Щелкните сводную таблицу.​Морепродукты​Формулы можно создавать только​(значение элемента) / (значение родительского​

2. ​Апрель​»[Продукт]»;»[Продукт].[Все продукты].[Продовольствие].[Выпечка]»​

   ​ разными областями:​​Выделяем ячейку А1, чтобы​

   1. ​, чтобы в итоге​

   2. ​ формулой – цена​​Жмем – открывается диалоговое​​ для нашего отчета.​​Но ваш руководитель хочет​​, а затем в​​На вкладке​​ в разделе «Вычисляемое​​МойЭлемент​​Отобразится вкладка «Работа со​

   3. ​. Когда позиции элементов​​ в отчетах, которые​​ элемента по строкам).​. Поле строки​

   4. ​Вычисляемые поля или элементы​​Чтобы название строки сделать​​ Excel знал, с​

      ​ получить желаемое:​​ за 1 *​

   5. ​ окно. Вводим имя​Сводная таблица – мощный​

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

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

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

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

1. ​ это название, щелкаем​ работать.​ окне​Переходим на лист с​​ формулу для нахождения​​ С ее помощью​

2. ​ на:​​укажите ячейку, в​​Сервис​​Если для одного вычисляемого​​ объекта с именем​Анализ​

3. ​ будут скрыты или​​ из источника данных​​Рассчитывает значения следующим образом:​

4. ​Север​ для функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.​​ по всплывающему меню.​​В меню «Вставка» выбираем​

​Параметры поля​​ отчетом. Работа со​ значений.​ пользователь анализирует большие​То есть имеем несколько​

support.office.com

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

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

​ не подходит​.​.​ является та, для​

1. ​ «Продажи» для строки​ сгруппированы, на вкладке​

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

   

3. ​Поле «Отдел» мы проставили​​ место. Так как​​Дополнительные вычисления​​ должна войти в​​ таблицами​​ только нужную в​​- дизайн отчета​

   

4. ​Excel добавит пустую сводную​​В поле​​ которой в столбце​

5. ​ «МойЭлемент» и столбца​​Анализ​​ этом индексе.​​ сводных таблицах нужно​​ элемента в выбранном​​Запад​​ создан последним в​ перед наименованиями товаров.​ мы установили курсор​(в Excel 2007-2010),​

​ сводную таблицу.​Экспериментируйте: инструменты сводной таблицы​ данный момент информацию.​ должен соответствовать корпоративным​​ таблицу и выведет​​Имя​

1. ​ B указано его​ «Январь».​в группе​​Для ссылки на элементы​​ учитывать описанные ниже​

2. ​базовом поле​​. Значение на пересечении​ диапазоне.​​ Воспользовавшись разделом меню​

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

   • ​ список полей, в​выберите элемент, который​ имя. Для остальных​​Чтобы внести изменения, воспользуйтесь​​Группировать​ можно использовать относительные​​ правила синтаксиса и​​).​ столбца​Если аргументы «поле» и​

3. ​ «переместить в начало».​​ данными, поле диапазона​​ выпадающий список​

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

   

4. ​ одним из описанных​выберите команду​ позиции. Они определяются​ поведения формул.​Отличие​Апрель​​ «элемент» описывают одну​​Покажем детали по конкретному​

   ​ заполнится автоматически. Если​Дополнительные вычисления​​ таблицы, достаточно было​​ получится, всегда можно​​ диапазон данных: итоги​​ т.д.). «Дорабатывать напильником»​ отображение нужных полей​Нажмите кнопку​​ B указывается не​​ ниже способов.​Разгруппировать​ относительно вычисляемого объекта,​

   ​Элементы формулы сводной таблицы.​​Значения в виде разности​и строки​ ячейку, возвращается значение,​ продукту. На примере​​ курсор стоит в​​(Show data as)​​ обновить сводную таблицу.​​ удалить неудачный вариант​​ финансовых операций, сведения​​ дизайн сводной -​​ и изменить их​​Удалить​ только имя самого​Щелкните сводную таблицу.​.​ содержащего формулу. Если​    В формулах, которые создаются​ по отношению к​Север​ содержащееся в этой​

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

Подключение к новому внешнему источнику данных

​ вычисляемого объекта, но​Отобразится вкладка «Работа со​Щелкните поле, в которое​ текущим регионом является​ для вычисляемых полей​ значению​ — это общая выручка​ ячейке, независимо от​ где отображены остатки​

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

1. ​ процесс. И не​

   • ​В разделе полей установите​​Для сведения данных в​​ и имена элементов,​ сводными таблицами» с​ нужно добавить вычисляемый​

   • ​Юг​​ и вычисляемых объектов,​​базового элемента​ от продаж, определенная​ его типа (строка,​ на складах. Выделяем​

2. ​ Сводную таблицу можно​​ можно выбрать варианты​​ «Продажи».​ для анализа, суммирования​

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

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

   • ​в соответствующем​ по исходным данным,​ число, ошибка и​

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

1. ​ Excel Online можно​​ используется формула.​​Анализ​​На вкладке​​Регион[-1]​

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

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

​Предположим, что есть формула​и​​Анализ​​ — это​ и в других​.​​Месяц​​Если аргумент «элемент» содержит​

1. ​ «развернуть».​​ на другом. Если​​(% of row)​ таблице. Менять исходную​ инструмент Excel позволяет​Создадим сводную таблицу: «Вставка»​Из всей сводной для​ области по умолчанию​​ как СУММ, СЧЁТ​​ по умолчанию для​

​Конструктор​в группе​Север​ формулах на листе.​Приведенное отличие​

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

• ​содержит значение​ дату, необходимо представить​В открывшемся меню выбираем​

• ​ мы хотим, чтобы​,​ информацию не имеет​

• ​ произвести фильтрацию и​ — «Сводная таблица».​ отчета вам​

• ​ в разделе областей​ и СРЗНАЧ. По​

• ​ вычисляемого объекта с​.​

• ​Вычисления​

support.office.com

Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

​. Если текущим регионом​ Также можно использовать​Значения в виде разности​

​Апрель​ это значение как​

​ поле с данными,​ сводные данные были​Доля от суммы по​ смысла. В таких​

​ группировку информации, изобразить​ Поместим ее на​

• ​нужны не все данные​​списка полей​ умолчанию для чисел​​ именем​На вкладке​выберите команду​ является​ константы и ссылаться​ в процентах по​, а столбец​ порядковый номер или​ которые необходимо показать.​ на существующей странице,​ столбцу​
• ​ ситуациях лучше добавить​ ее в различных​​ новый лист.​​, а только конкретные​.​ в полях значений​МойЭлемент​
• ​Анализ​​Поля, элементы и наборы​Север​​ на данные из​ отношению к значению​Регион​ воспользоваться функцией ДАТА,​Когда нажимаем на сводную​ не забывайте указывать​(% of column)​
• ​ вычисляемое (пользовательское) поле.​ разрезах (подготовить отчет).​Мы добавили в сводный​ модели Ford по​Как правило, нечисловые поля​​ используется функция СУММ.​​, а также другая​в группе​, а затем — пункт​, то​ отчета, но не​базового элемента​ — значение​ чтобы это значение​ таблицу, становится доступной​ для них место.​или​Это виртуальный столбец, создаваемый​Исходный материал – таблица​ отчет данные по​
• ​ Питеру — придется​ добавляются в область​​ Ниже описано, как​​ формула для этого​Вычисления​

​Вычисляемый объект​Регион[+1]​ допускается использование ссылок​​в соответствующем​​Север​ не изменилось при​ закладка с параметрами​ На странице появляется​Доля от общей суммы​ в результате вычислений.​

​ с несколькими десятками​ поставщикам, количеству и​ руками фильтровать.​

​строк​ выбрать другие функции​ объекта с именем​выберите команду​.​ — это​ на ячейки и​базовом поле​.​ открытии листа в​ отчета. С ее​ следующая форма:​

​(% of total)​

• ​ В нем могут​​ и сотнями строк,​​ стоимости.​Стандартные​
• ​, числовые — в область​​ сведения данных.​​МойЭлемент Январь Продажи​Поля, элементы и наборы​В поле​Юг​ определенных имен. Невозможно​.​На сводной диаграмме поле​ системе с другими​ помощью можно менять​Сформируем табличку, которая покажет​, чтобы автоматически подсчитать​
• ​ отображаться средние значения,​ несколько таблиц в​Напомним, как выглядит диалоговое​итоги в сводной нам​значений​Щелкните правой кнопкой мыши​. В сводной таблице​​, а затем — пункт​​Имя​. Например, для вычисляемого​​ использовать функции листа,​​С нарастающим итогом в​Регион​ языковыми настройками. Например,​ заголовки, источники данных,​ сумму продаж по​ проценты для каждого​ проценты, расхождения. То​ одной книге, несколько​ окно сводного отчета:​ не подходят​, а поля даты​ в любом месте​ вы увидите эту​Вычисляемое поле​введите имя вычисляемого​ объекта можно использовать​ для которых нужны​

​ поле​

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

​ формулу​ аргументы в виде​Значение в виде нарастающего​ поле категорий, в​ дату 5 марта​С помощью сводных таблиц​ полей сводной таблицы​ Вот так, например,​ формул. Данные вычисляемого​ создания: «Вставка» –​ программе инструкции для​ по выручке в​

​ область​ выберите команду​ «Продажи» для строки​В списке​В поле​​=Регион[-1] * 3%​​ ссылок на ячейки​ итога для последовательных​ котором элементы​ 1999 г., можно​ Excel легко проверить,​ выбираем названия столбцов,​ будет выглядеть наша​ поля взаимодействуют с​ «Таблицы» – «Сводная​ формирования сводного отчета.​ зеленых ячейках, но​

planetaexcel.ru

Управление сводными таблицами в Excel

​столбцов​Показать список полей​ «МойЭлемент» и столбца​Имя​Формула​. Если позиция, которую​ или определенных имен,​ элементов в​Север​ ввести двумя способами:​

Фильтр в сводной таблице Excel

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

​. Поле можно перенести​.​

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

​ а также формулы​базовом поле​,​ 36 224 или​

​ организации начисляют квартплату.​ Получаем итоги по​

​ включенной функцией​Инструкция по добавлению пользовательского​А в данной статье​ ошибку, из нижней​ в итогах -​ в любую другую​В списке​Выполните одно из указанных​

​ для которого нужно​ этого объекта.​ перед первым или​ массива.​.​Юг​ ДАТА(1999;3;5). Время можно​ Другой положительный момент​ каждому отделу.​Доля от суммы по​ поля:​ мы рассмотрим, как​ области можно удалить​

​ сводная так не​ область.​

​Поля сводной таблицы​ ниже действий.​ изменить формулу.​Чтобы использовать в формуле​ после последнего элемента​Имена полей и элементов.​% от суммы с​

​,​ задать в виде​

1. ​ – экономия. Если​Просто, быстро и качественно.​ столбцу​Определяемся, какие функции будет​
2. ​ работать со сводными​ заголовок, заменить его​
3. ​ умеет.​Совет:​в группе​

​Редактирование формулы вычисляемого поля​В поле​ данные из имеющегося​ в поле, формула​    В Excel имена полей​ нарастающим итогом в​Восток​

​ десятичных значений или​

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

​ другим.​Полученные в сводной результаты​  Также можно щелкнуть​

​Значения​

Сортировка в сводной таблице Excel

​    ​Формула​ элемента, щелкните его​ возвращает ошибку #ССЫЛКА!.​

​ и элементов используются​ поле​и​ с помощью функции​ контролировать, сколько расходуется​Первая строка заданного для​Если в выпадающем списке​ На какие данные​Первый этап – выгрузить​

​По данным, которые помещены​ — еще не​ имя поля правой​

​щелкните стрелку рядом​Щелкните сводную таблицу.​измените формулу.​ в списке​Использование формул в сводных​ для идентификации этих​Значение в виде нарастающего​Запад​

​ ВРЕМЯ.​ света, газа, то​ сведения данных диапазона​

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

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

​(Show data as)​ поле должно ссылаться.​ Excel и привести​ подводятся итоги. В​ произвести с ними​ выбрать команду​

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

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

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

​ столбец должен иметь​Отличие​

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

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

​Для начала предлагаем составить​ свой заголовок –​(Difference)​

​ товаров.​ Если наши данные​

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

exceltable.com

Работа со сводными таблицами в Excel на примерах

​ в том же​ связанной сводной таблице.​C3:C9​.​ полем рядов, в​ #REF!.​ сводную таблицу тарифов​ проще настроить сводный​, а в нижних​

​Работа со сводными таблицами​ находятся в Worde,​ и т.д. Если​ тысячах, добавить прогноз​,​ данных и нажмите​Формулы​На вкладке​ поле, что и​

​ Там вы увидите​используется имя поля​Сортировка от минимального к​ котором элементы​

Как сделать сводную таблицу из нескольких файлов

​Если аргументы не описывают​ по всем коммунальным​ отчет.​ окнах​ – Параметры –​ мы переносим их​ сделать это нужно​ на апрель, сравнить​Добавить в названия строк​ОК​, а затем — пункт​Анализ​ вычисляемый объект).​ отдельные значения, из​

​Молоко​ максимальному​Март​ видимое поле или​ платежам. Для разных​В Excel в качестве​Поле​ Формулы – Вычисляемое​ в Excel и​ для значений всего​

​ этот год с​или​.​Вычисляемое поле​

​в группе​Нажмите кнопку​ которых состоят данные,​. Для вычисляемого объекта​Ранг выбранных значений в​

​,​ содержат фильтр отчета,​ городов данные будут​ источника информации можно​(Base field)​ поле.​ делаем таблицу по​ поля, то щелкаем​ прошлым. Многое из​Добавить в значения​

​Примечание:​.​Вычисления​Добавить​ а затем сможете​ в поле​ определенном поле с​

​Апрель​ в котором не​ свои.​ использовать таблицы Access,​и​В открывшемся меню вводим​

1. ​ всем правилам Excel​ по названию столбца​ перечисленного в сводных​. В результате это​ Функции сведения недоступны в​В списке​выберите команду​.​ посмотреть на результаты​Тип​ учетом того, что​и​
2. ​ отображаются отфильтрованные данные,​Для примера мы сделали​ SQL Server и​Элемент​ название поля. Ставим​
3. ​ (даем заголовки столбцам,​ и меняем способ​ или невозможно в​ поле будет перенесено​ сводных таблицах на​

​Имя​Поля, элементы и наборы​Щелкните ячейку, для которой​

​ в графическом представлении​, оценивающего объем продаж​

​ наименьшему из них​Май​ функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ возвращает​ сводную табличку тарифов​ др.​(Base item)​ курсор в строку​

​ убираем пустые строки​

Детализация информации в сводных таблицах

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

Как обновить данные в сводной таблице Excel?

​отображаются как ряды,​ значение ошибки #ССЫЛКА!.​ для Москвы:​​выбрать​ «Формула». Инструмент «Вычисляемое​ и т.п.).​Например, среднее количество заказов​

​ сводных на основе​

​ в разделе областей.​ OLAP.​ для которого нужно​

​Вычисляемый объект​

​Чтобы изменить формулу для​Например, на этой сводной​

​ основе данных о​ а остальным — значения​ представленные в легенде.​В практических целях приведенные​

1. ​Для учебных целей возьмем​Часто требуется создавать сводные​Месяц​ поле» не реагирует​Дальнейшая работа по созданию​
2. ​ по каждому поставщику:​
3. ​ OLAP-кубов) или делается,​ Кроме того, поле​Функция сведения данных​ изменить формулу.​

Изменение структуры отчета

​.​ нескольких ячеек, нажмите​

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

​ о продажах для​ можно использовать формулу​ соответственно.​значений​

​ содержат не фактические​ человек, которые проживают​ таблиц. Есть пара​

Как добавить в сводную таблицу вычисляемое поле?

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

​Формула​Имя​ удерживая ее, выделите​ каждого продавца по​=Молоко * 115%​Сортировка от максимального к​с именем​ данные сводной таблицы,​ в квартире 60​

​ табличек с информацией.​(в родной англоязычной​

1. ​ сводной таблице не​ зависеть от типа​ а только в​ вычисляемых полей и​ нужную область в​Сумма значений. Используется по​измените формулу.​выберите вычисляемый объект.​
2. ​ остальные ячейки.​ регионам:​.​ минимальному​
3. ​Сумма продаж​ а только снимок​ кв. м. Чтобы​ Нужно объединить их​ версии вместо этого​ имеет смысла. Из​ данных. Если информация​ отдельной ячейке. Тогда​ объектов.​ соответствующем разделе.​ умолчанию для полей​Нажмите кнопку​В поле​В строке формул внесите​Чтобы посмотреть, как будут​
4. ​Примечание:​

Группировка данных в сводном отчете

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

​Формула​ изменения в формулу.​ выглядеть объемы продаж,​

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

​ более понятное​ категории, которые нужны​

​ но заголовки одинаковые),​

​ мыши именно по​ хитрую​позволяет настроить расположение​СЧЁТ​

exceltable.com

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

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

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

Другие функции расчета вместо банальной суммы

​Отобразится вкладка «Работа со​ 10 %, можно создать​ списке полей сводной​ наибольшему значению в​ каждом регионе за​Скопируйте образец данных из​​ каждый месяц.​ ​ в двух магазинах.​​т.е. предыдущий):​ – «Добавить поле».​​ таблиц – в​ ​Установим фильтр в сводном​​(обычные сводные диаграммы​ таблице. Для этого​ сведения данных СЧЁТ​ вычисляемого объекта​

​Изменить​ сводными таблицами» с​ вычисляемое поле в​ таблицы, а имена​ поле присваивается значение​ каждый месяц. Например,​ следующей таблицы и​Первый столбец = первому​Порядок создания сводной таблицы​…то получим сводную таблицу,​ Дописываем формулу нужными​ помощь.​

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

​ в которой показаны​ арифметическими действиями.​Мы просто создаем сводный​В перечне полей для​Изящным решением всех​ в разделе областей​ как СЧЁТЗ. СЧЁТ​Щелкните поле, содержащее вычисляемый​Щелкните ячейку, для которой​Анализ​ и воспользоваться формулой​ в каждом раскрывающемся​ меньшему значению — более​ может представлять (своим​

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

Долевые проценты

​ объект.​ нужно изменить формулу.​​и​​=Продажи * 110%​​ списке полей. Не​​ высокий ранг.​ положением на вертикальной​​ листа Excel. Чтобы​​ формула для расчета​Создадим отчет с помощью​ следующего месяца от​​Для примера посчитаем расходы​ ​ данных в нескольких​​ ставим галочку напротив​

​ стать функция​ область или просто​​ для пустых полей​На вкладке​ ​Чтобы изменить формулу для​​Конструктор​​.​ следует путать эти​ ​Индекс​​ оси, т. е. оси​​ отобразить результаты формул,​ ​ вида:​​ мастера сводных таблиц:​ предыдущего, т.е. –​ на товар в​ диапазонах консолидации.​ заголовка «Склад».​ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA)​ перетащить его.​​ и полей с​Параметры​​ нескольких ячеек, нажмите​

Динамика продаж

​.​​Результат сразу отображается на​ ​ имена с теми,​​Рассчитывает значения следующим образом:​​ значений) сумму продаж​ ​ выделите их и​​= тариф * количество​Вызываем меню «Мастер сводных​​ динамика продаж:​ ​ разные годы. Сколько​​Гораздо сложнее сделать сводную​​Перетащим это поле в​ ​, которая умеет извлекать​​Чтобы создать новое подключение​​ нечисловыми значениями.​​в группе​​ клавишу CTRL и,​​На вкладке​ сводной диаграмме, как​ которые отображаются в​((значение в ячейке) x (общий​​ за месяц​​ нажмите клавишу F2,​

​ человек / показания​ таблиц и диаграмм».​А если заменить​ было затрачено средств​ таблицу на основе​ область «Фильтр отчета».​

​ нужные нам данные​​ к внешним данным​ ​Среднее​​Сервис​​ удерживая ее, выделите​ ​Анализ​​ показано на этом​​ подсказках к диаграммам​​ итог)) / ((итог строки) x​Апрель​ а затем — клавишу​ счетчика / площадь​ Для этого щелкаем​Отличие​ в 2012, 2013,​

P.S.

​ разных по структуре​Таблица стала трехмерной –​ из сводной, чтобы​ на сервере SQL​Среднее арифметическое.​выберите команду​ остальные ячейки.​в группе​ рисунке:​​ и соответствуют именам​ (итог столбца)).​​в регионе​

​ ВВОД. При необходимости​​Для удобства рекомендуем сделать​ кнопку настройки панели​​(Difference)​

​ 2014 и 2015.​ исходных таблиц. Например,​ признак «Склад» оказался​ использовать их в​

planetaexcel.ru

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

​МИН​Вычисляемый объект​Совет:​Поля, элементы и наборы​ в регионе «Север»​Формулы работают с итоговыми​ настраиваемые вычисления не​Ниже перечислены функции сведения,​ данные.​ показания по счетчикам​

​ Здесь на вкладке​(% of difference)​ Excel выполняется следующим​ товара. Вторая –​ значения в отчете​Сделаем на отдельном от​ или сводной таблицы,​Наименьшее значение.​

Сводная таблица в Excel

​.​ Если есть несколько вычисляемых​, а затем — пункт​

​ за вычетом транспортных​ суммами, а не​ дают желаемых результатов,​ с помощью которых​Формула​ (переменная составляющая).​ «Настройка» находим «Мастер​и добавить​ образом. Для примера​ количество проданных единиц​ по номеру склада.​ сводной листе заготовку​ выполните указанные ниже​ПРОИЗВЕД​В поле​

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

1. ​ с отдельными записями.​ вы можете создать​ можно вычислять поля​Результат​
2. ​Наши формулы ссылаются на​ сводных таблиц». Добавляем​
3. ​условное форматирование​ сделаем простую сводную​ в разных магазинах.​ Нажимаем на стрелочку​ отчета:​ действия.​Произведение значений.​Имя​ можно настроить порядок​.​ 8 %, можно создать​    Формула для вычисляемого поля​ собственные формулы в​ значений. Эти функции​ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Продажи»; $A$4)​ лист, где расположена​ инструмент в панель​для выделения отрицательных​ по дате поставки​ Нам нужно свести​ в правом углу​Выделите первую ячейку зеленого​
4. ​На вкладке​Количество чисел​выберите вычисляемый объект.​ вычислений. Дополнительные сведения​Щелкните одну из формул​ в поле «Регион»​ оперирует суммой исходных​ вычисляемых полях и​

​ доступны для всех​

​Возвращает общий итог для​

• ​ сводная таблица с​ быстрого доступа. После​ значений красным цветом​
• ​ и сумме.​ эти две таблицы​ ячейки и выбираем​ диапазона, введите знак​Данные​
• ​Количество значений, содержащих числа​В поле​ см. в разделе​ и нажмите кнопку​ вычисляемый объект с​

​ данных для каждого​

Как сделать сводную таблицу из нескольких таблиц

​ вычисляемых объектах. Например,​ типов исходных данных,​ поля «Продажи» (49 325 ₽).​ тарифами.​ добавления:​ — то получим​Щелкаем правой кнопкой мыши​ в один отчет,​ интересующие нас позиции:​

​ «равно» и щелкните​нажмите кнопку​ (отличается от функции​

​Формула​ Изменение порядка вычислений​

1. ​Вверх​ такой формулой:​ используемого поля. Например,​ можно добавить вычисляемый​ кроме OLAP.​ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Сумма продаж»; $A$4)​Скачать все примеры сводной​Ставим курсор на первую​ то же самое,​ по любой дате.​ чтобы проиллюстрировать остатки,​Например, «1»:​
2. ​ по ячейке в​Из других источников​ СЧЁТ, в которой​измените формулу.​ для нескольких вычисляемых​или​=Север – (Север * 8%)​ формула вычисляемого поля​ объект с формулой​Функция​Также возвращает общий итог​ таблицы​
3. ​ табличку и нажимаем​ но не в​
4. ​ Выбираем команду «Группировать».​ продажи по магазинам,​В отчете отображается информация​ сводной, которая содержит​.​ учитываются заполненные поля).​Нажмите кнопку​ объектов или формул.​Вниз​
5. ​.​=Продажи * 1,2​ расчета комиссионных за​Сведение данных​ для поля «Продажи»​Если при расчете коммунальных​ инструмент «Мастера». В​ рублях, а в​В открывшемся диалоге задаем​ выручку и т.п.​ только по первому​ нужные данные, т.е.​Выберите нужное подключение.​
6. ​Смещенное отклонение​Изменить​Примечание:​.​Диаграмма будет выглядеть следующим​умножает сумму продаж​ продажу, которые могут​Сумма​

​ (49 325 ₽; можно задать​ платежей применяются льготы,​ открывшемся окне отмечаем,​ процентах:​ параметры группировки. Начальная​Мастер сводных таблиц при​

Как работать со сводными таблицами в Excel

​ складу. Вверху видим​ по B8, где​Щелкните​Оценка стандартного отклонения генеральной​.​ Если удалить формулу из​

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

​В Microsoft Excel 2010​ и конечная дата​ таких исходных параметрах​ значение и значок​ лежит выручка Fiesta​С сервера SQL Server​ совокупности, где выборка​Редактирование отдельных формул для​ сводной таблицы, восстановить​ других формул, чтобы​

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

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

​ в поле «Продавец»,​ 1,2, а не​ комиссионные будут автоматически​ данных.​

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

​ Выбираем шаг –​ как нарушено одно​Отфильтровать отчет можно также​ привычной ссылки а-ля​ к таблице на​ генеральной совокупности.​ объекта​ Если вы не​

​ нужном порядке.​ будет отображаться как​ умножает каждое отдельное​ включены в промежуточные​

​Количество​ выглядит на листе,​ требуйте в бухгалтерии​ есть нам нужно​ еще проще -​ «Годы».​ из главных условий​ по значениям в​

​ «морской бой» Excel​ сервере SQL Server.​СТАНДОТКЛОНП​

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

Проверка правильности выставленных коммунальных счетов

​ или ввести только​ своей обслуживающей организации.​ объединить несколько мест​ щелкнув правой кнопкой​Получаем суммы заказов по​ консолидации – одинаковые​ первом столбце.​ вставит функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ:​Щелкните​Стандартное отклонение генеральной совокупности,​Например, если вычисляемый объект​ безвозвратно, то можете​

​ всех формул, которые​ легенде, и появится​ 1,2 с последующим​ в отчете.​ функции СЧЁТЗ. Функция​ корень этого имени​

​ Когда меняются тарифы​ с информацией. Вид​ мыши по любому​

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

​ на диаграмме в​ суммированием полученных величин.​Влияние типа источника данных​ по умолчанию для​ (без слов «Сумма»,​

​ – просто измените​ отчета – «сводная​ полю и выбрав​

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

​ сводимые данные.​ЯблокиРасчет​ или элемент, перетащив​ сводной таблице.​

​ виде точки данных​Формулы для вычисляемых объектов​

​ на вычисления​ данных, отличных от​ «Счет» и т. д.).​ данные в ячейках.​ таблица». «Далее».​ в контекстном меню​По такой же схеме​ этих таблицах идентичны.​ отчет: уберем значение​»Выручка»​

exceltable.com

​ к кубу аналитики​

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Аналогом функции ВПР для сводных таблиц является функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, которая возвращает данные, хранящиеся в отчете сводной таблицы.

Чтобы получить быстрый доступ к функции, необходимо ввести знак равенства в ячейку (=) и выделить необходимую ячейку в сводной таблице. Excel сгенерирует функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ автоматически.

Отключение создания GetPivotData

Чтобы отключить автоматическую генерацию функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, выберите любую ячейку в сводной таблице, перейдите по вкладке Работа со сводными таблицами -> Параметры в группу Сводная таблица. Щелкните по стрелке вниз, находящейся рядом с вкладкой Параметры. В выпавшем меню, уберите галку с пункта Создать GetPivotData.

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

Использование ссылок на ячейки в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Вместо указания названия пунктов или полей в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, вы можете ссылаться на ячейки, находящиеся на листе. В примере ниже ячейка E3 содержит название продукта, а формула в ячейке E4 ссылается на нее. В результате будет возвращен суммарный объем по тортам.

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

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

В примере ячейка E3 содержит название поля данных «Количество», и было бы неплохо ссылаться на эту ячейку в функции, вместо того, чтобы иметь название поля в формуле ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.

Однако, если мы поменяем первый аргумент поле_данных на ссылку на ячейку E3, Excel вернет нам ошибку #ССЫЛКА!

Проблему решит простое добавление пустой строки (“”) в начало или конец ссылки на ячейку.

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

Использование дат в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Если вы используете даты в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, у вас могут возникнуть проблемы, даже если дата отображается в сводной таблице. К примеру, аргументом формулы ниже является дата “21/04/2013”, и сводная таблица содержит поле с датами продаж. Однако формула в ячейке E4 возвращает ошибку.

Для предотвращения ошибок, связанных с датами, вы можете воспользоваться одним из следующих способов:

• Сравнять форматы дат в формуле и сводной таблице
• Использовать функцию ДАТАЗНАЧ
• Использовать функцию ДАТА
• Сослаться на ячейку с корректной датой

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

Для получения корректного результата, во время использования функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, убедитесь, что форматы дат аргумента формулы и сводной таблицы одинаковые.

В ячейке E4, в формуле использована дата формата “ДД.ММ.ГГГГ”, и в результате возвращена правильтая информация.

Использование функции ДАТАЗНАЧ

Вместо ручного ввода даты в формуле, можно добавить функцию ДАТАЗНАЧ для возврата даты.

В ячейке E4, дата введена с помощью функции ДАТАЗНАЧ, и Excel возвращает необходимую информацию.

Использование функции ДАТА

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

Ссылка на ячейку с датой

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

Как получить данные со сводной таблицы

Здравствуйте друзья!

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

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

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

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

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

• прямые линки на сводную таблицу;
• использование расширенного фильтра и т.д;
• использование функцииПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.

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

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

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

Ставим курсор на первую ячейку куда нам необходимо получить данные и вводим функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, которая и извлечёт со сводной нужные данные для использования в наших расчётах. И выбираем в сводной таблице нужные данные, щелкая сначала на ячейку, где лежит выручка за январь. В результате мы получаем не привычного вида формулу, а автоматически вставленную функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, которую мы рассмотрим более подробно:

• Первым ее аргументом стало поле «Сумма», это является именем извлекаемого поля;
• Вторым аргументом у нас идёт (Свод!$C$1), это координаты ячейки, откуда мы вытягиваем данные, нужно водить обязательно, поскольку может быть много таблиц и данных, а программа обязана знать откуда взять данные.
• Третьим аргументом, как и следующими, являются названия полей и их значение. В нашем случае это стал вид товара и период времени.

Теперь с полученными, таким образом, данными, возможно работать как с обыкновенными формулами и на ним не распространяются те жёсткие ограничения которые существуют для сводных таблиц, а значит можно легко строить графики, подводить итоги и проводить анализ. Если ваши исходные данные будут исправлены, дополнены или заменены, вам нужно лишь обновить сводную таблицу (поставьте курсор мыши на сводную таблицу и вызвав контекстное меню, выбрать пункт «Обновить»). И даже если исходные данные кардинально изменятся, вы всё равно получите правильные данные, обновятся и итоги, и графики, и анализ.

Без экономической свободы никакой другой свободы быть не может.
Маргарет Тэтчер

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

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

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

Для решения этой задачи выполните следующие действия.

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

Рис. 3.42. В этой сводной таблице выполняются всевозможные вычисления

Рис. 3.43. Для создания формулы введите знак равенства и щелкните на нужной ячейке сводной таблицы

Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, созданная на шаге 5, жестко запрограммирована на возвращение значений из одной заданной ячейки сводной таблицы. Эта функция знакома многим пользователям, но далеко не все нашли время, чтобы детально разобраться в ее синтаксисе.

• =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, «Доход» — получение значения поля Доход.
• CaseSCudyPivot$A$3 — идентификация сводной таблицы путем выбора любой ячейки. Обратите внимание: по умолчанию Excel выбирает левую верхнюю ячейку сводной таблицы.
• «Год», 2011 — последняя пара аргументов представляет собой имя поля и возвращаемое значение. Второй аргумент из этой пары жестко закодирован. Ключ к успешному применению функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ — параметризация аргументов, которая позволит изменять их в дальнейшем.

Учтите, что использование функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ может привести к появлению определенных проблем. Если кто-либо изменил название категории Промышленное оборудование в отчете сводной таблицы, придется соответствующим образом изменить подпись в ячейке В4. Если этого не сделать, будет невозможно выбрать данные из промежуточной сводной таблицы. Функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ невозможно использовать для выборки данных из сводных таблиц OLAP и PowerPivot. В подобных случаях воспользуйтесь функциями кубов данных для создания совместимой сводной таблицы.

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA)

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

Примеры использования

GETPIVOTDATA(«SUM of number of units», «Pivot table»!А1)

GETPIVOTDATA(«AVERAGE of price per unit», A1, «division», «east»)

GETPIVOTDATA(«price per unit», B2, «division», «east», «subdivision», 2)

GETPIVOTDATA(A1, «Pivot table»!A1, «division», A2)

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(название_значения; любая_ячейка_сводной_таблицы; [исходный_столбец, . ]; [элемент_сводной_таблицы, . ])

• название_значения – название поля в сводной таблице, из которого необходимо извлечь данные.
  • название_значения должно быть заключено в кавычки или являться ссылкой на любую ячейку c соответствующим текстом.
  • Если имеется более одного значения, необходимо использовать точное название из сводной таблицы (например, «SUM of Sales»).
• любая_ячейка_сводной_таблицы – ссылка на ячейку в целевой сводной таблице. Мы рекомендуем указывать ячейку, расположенную в одном из верхних углов.
• исходный_столбец (необязательно) – название столбца в исходном наборе данных (не в сводной таблице).
• элемент_сводной_таблицы (необязательно) – название строки или столбца в сводной таблице, соответствующее исходному столбцу.

Примечания

• Регистр аргументов не имеет значения, за исключением случаев, когда они ссылаются на названия полей. Можно использовать любое сочетание заглавных и строчных букв.
• Если вы используете собственный заголовок для поля в сводной таблице, функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ будет работать только с вашим заголовком вместо названия столбца в исходных данных.
• В качестве аргумента любая_ячейка_сводной_таблицы может быть использована любая ячейка. Однако мы рекомендуем выбирать ту, которая расположена в верхнем углу таблицы. В противном случае, если сводная таблица уменьшится в размере из-за изменения данных и перестанет включать выбранную ячейку, функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ вернет ошибку.
• Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ может возвращать только те значения, которые содержатся в сводной таблице.

Похожие функции

QUERY . Выполняет запросы на базе языка запросов API визуализации Google.

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

ДАТА . Преобразует год, месяц и день в значение даты.

ВРЕМЯ . Преобразует часы, минуты и секунды в значение времени.

Статьи по теме

Набор данных для примеров, приведенных ниже.

Сводная таблица данных, указанных выше.

Извлечение атрибутов из сводной таблицы с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ .

Примеры работы функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel

Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel предназначена для получения доступа к полям данных сводных таблиц и возвращает данные в соответствии с запросом (формируется на основе переданных в данную функцию аргументов).

Пример как использовать функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel

Пример 1. В таблице Excel содержатся данные о поступлениях бытовой техники различного типа и от разных производителей на склад интернет-магазина по номеру дня. Создать сводную таблицу на основе существующей, получить данные о количестве полученных ноутбуках фирмы Samsung и их общей стоимости с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.

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

Для получения искомых данных можно вручную ввести следующую функцию:

• «Количество» – поле сводной таблицы, данные из которого требуется получить;
• A4:C15 – диапазон ячеек, в которых находится сводная таблица;
• «Наименование»;»Ноутбук»;»Фирма»;»Samsung» – характеристика получаемых данных, на основании которой производится поиск требуемых данных.

Как видно, результат совпадает со значением, хранящимся в сводной таблице. Для получения значения поля «Сумма» воспользуемся более удобным способом получения данных, когда рассматриваемая функция генерируется автоматически. Для этого выделим ячейку G8, вставим символ «=» и выделим ячейку B7:

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

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

Выборка данных из сводной таблицы с помощью формулы Excel

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

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

Формула 1 для разницы количества:

Формула 2 для разницы общей суммы:

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

Формула для анализа данных полученных из сводной таблицы Excel

Пример 3. В таблицу Excel выгружены статистические данные сайта в виде таблицы с полями «День», «Логин», «Страна» и «Время активности». Определить среднее время активности на сайте за исследуемый период для пользователей из каждой страны, вычислить наибольшее среди полученных значений.

Создадим сводную таблицу на новом листе и добавим поля для отображения среднего значения времени активности на сайте:

Произведем расчет для пользователя из GB (Великобритания):

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

Определим наибольшее значение с помощью соответствующей формулы:

Как видно, на сайте в среднем больше времени проводили пользователи из UA (Украины).

Особенности использования функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel

Рассматриваемая функция имеет следующий синтаксис:

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ( поле_данных; сводная_таблица ; [поле1; элем1; поле2; элем2]; …)

Описание аргументов (первые два являются обязательными для заполнения):

• поле_данных – аргумент, характеризующий имя поля данных, в котором содержатся данные для извлечения. Должен быть представлен текстовой строкой, например «Покупки».
• сводная_таблица – аргумент, принимающий ссылку на всю сводную таблицу, либо на некоторый диапазон ячеек, содержащийся в ней. Необходим для определения сводной таблицы, данные из которой требуется получить.
• [поле1; элем1; поле2; элем2]; … — необязательные аргументы, характеризующие данные, которые необходимо получить. Функция принимает до 126 пар имен полей и элементов. Имена элементов необходимо заключать в кавычки (исключением являются данные числового типа и даты).

1. В качестве аргумента сводная_таблица может быть передан диапазон ячеек, который включает сразу несколько сводных таблиц. В этом случае функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ будет извлекать данные из созданной последней таблицы.
2. Если аргумент сводная_таблица принимает диапазон пустых ячеек, рассматриваемая функция вернет код ошибки #REF!.
3. Пара поле1; элем1, ссылающаяся на одну и ту же ячейку, вернут данные из данной ячейки, которые могут быть любого типа, включая код ошибки.
4. Код ошибки #ССЫЛКА! Будет возвращен в случае, если аргументы функции указывают на невидимое поле или содержат фильтр, не отображающий данные согласно установленным им условиям.
5. Для корректного отображения данных в формате Время и Дата необходимо выполнять прямое преобразование (использовать функции ДАТА, ДАТАЗНАЧ и ВРЕМЯ).

1. Сводные таблицы используются для создания удобочитаемого отчета на основе данных из имеющейся громоздкой таблицы с большим количеством полей данных.
2. В Excel реализован визуальный интерфейс создания сводных таблиц, который делает данный процесс простым и наглядным. Однако алгоритм форматирования таких таблиц не является достаточно гибким, поэтому зачастую не удается достичь ожидаемого результата.
3. Один из специалистов Microsoft предложил новый метод, согласно которому созданная сводная таблица является не окончательным действием, а лишь промежуточным этапом при создании отчетов. Требуется самостоятельно создать оболочку итогового отчета, которая затем будет заполнена данными из сводной таблицы с использованием рассматриваемой функции. При этом сводная таблица может иметь примитивный вид, не требует форматирования и может находиться на скрытом листе в качестве невидимой базы данных.
4. При необходимости можно отключить функционал автоматического генерирования рассматриваемой функции. Для этого в параметрах сводных таблиц необходимо снять флажок перед «Создать GetPivotData».
5. Синтаксис функции достаточно сложный, поэтому чтобы упростить работу с ней, можно выполнить следующие действия:

• выделить пустую ячейку и ввести символ «=»;
• выделить поле данных с требуемой информацией в сводной таблице;
• функция с требуемыми аргументами сгенерируется автоматически.