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

Как научить Excel ссылаться на ячейку в сводной таблице, как на обычную

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

Рис. 1. Исходные данные

Скачать заметку в формате Word или pdf, примеры в формате Excel2013

На основе исходных данных вы создаете сводную таблицу и группируете заказы и расходы по месяцам (рис. 2).

Рис. 2. Сводная таблица

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

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

Я уже несколько лет обходил эту проблему различными ухищрениями, а недавно знакомый показал изящный и предельно простой способ избежать этой напасти… 🙂 Оказывается, автоматическое создание формулы ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ можно отключить! Пройдите по меню Файл (у меня Excel 2013) → Параметры Excel, перейдите на вкладку Формулы и в разделе Работа с формулами отключите опцию Использовать функции GetPivotData для ссылок в сводной таблице

Рис. 4. Отключите опцию Использовать функции GetPivotData для ссылок в сводной таблице

Теперь при ссылке на ячейку сводной таблицы будет отображаться ее адрес, как и при выборе обычной ячейки (рис. 5).

Рис. 5. При ссылке на ячейку сводной таблицы отображается адрес ячейки, как и при выборе обычной ячейки

Источник

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

history 10 апреля 2013 г.

Сводные таблицы ( Вставка/ Таблицы/ Сводная таблица ) могут пригодиться, если одновременно выполняются следующие условия:

Подготовка исходной таблицы

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

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

Создание таблицы в формате EXCEL 2007 добавляет новые возможности:

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

В таблице имеются столбцы:

Через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен ) откорректируем Имя таблицы на « Исходная_таблица ».

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

Сводную таблицу будем создавать для решения следующей задачи: «Подсчитать суммарные объемы продаж по каждому Товару».

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

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

Структура Сводной таблицы в общем виде может быть представлена так:

Т.к. ячейки столбца Товар имеют текстовый формат, то они автоматически попадут в область Названия строк Списка полей. Разумеется, поле Товар можно при необходимости переместить в другую область Списка полей. Заметьте, что названия Товаров будут автоматически отсортированы от А до Я (об изменении порядка сортировки читайте ниже ).

Теперь поставим галочку в Списке полей у поля Продажи.

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

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

Детализация данных Сводной таблицы

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

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

Изменение функции итогов

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

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

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

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

Изменение формата числовых значений

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

Добавление новых полей

Предположим, что необходимо подготовить отчет о продажах Товаров, но с разбивкой по Регионам продажи. Для этого добавим поле Регион продажи, поставив соответствующую галочку в Списке полей. Поле Регион продажи будет добавлено в область Названия строк Списка полей (к полю Товар). Поменяв в области Названия строк Списка полей порядок следования полей Товар и Регион продажи, получим следующий результат.

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

Добавление поля Регион продажи в область строк привело к тому, что Сводная таблица развернулась на 144 строки. Это не всегда удобно. Т.к. продажи осуществлялись только в 6 регионах, то поле Регион продажи имеет смысл разместить в области столбцов.

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

Меняем столбцы местами

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

Удаление полей

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

Другой способ – снять галочку напротив удаляемого поля в верхней части Списка полей. Но, в этом случае поле будет удалено сразу из всех областей Списка полей (если оно использовалось в нескольких областях).

Добавление фильтра

Предположим, что необходимо подготовить отчет о продажах Групп Товаров, причем его нужно сделать в 2-х вариантах: один для партий Товаров принесших прибыль, другой – для убыточных. Для этого:

Вид получившейся Сводной таблицы должен быть таким:

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

После нажатия кнопки ОК будут выведены значения Продаж только прибыльных Партий.

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

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

Несколько итогов для одного поля

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

Отключаем строки итогов

Группируем числа и Даты

Предположим, что требуется подготовить отчет о сроках сбыта. В результате нужно получить следующую информацию: сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д. Для этого:

Теперь Сводная таблица показывает сколько партий Товара сбывалось за 5, 6, 7, … дней. Всего 66 строк. Сгруппируем значения с шагом 10. Для этого:

Теперь Сводная таблица показывает сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д.

Аналогичную группировку можно провести по полю Дата поставки. В этом случае окно Группировка по полю будет выглядеть так:

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

Условное форматирование ячеек Сводной таблицы

К ячейкам Сводной таблицы можно применить правила Условного форматирования как и к ячейкам обычного диапазона. Выделим, например, ячейки с 10 наибольшими объемами продаж. Для этого:

Источник

Определение источников данных, используемых в модели данных книги

Если вы работаете с данными, добавленными в модель Excel данных, иногда вы можете не отслеживать, какие таблицы и источники данных были добавлены в модель данных.

Примечание: Убедитесь, что вы включили надстройку Power Pivot. Дополнительные сведения см. в том, как запустить надстройку Power Pivot для Excel.

Чтобы точно определить, какие данные есть в модели, выполните следующие простые действия:

В Excel щелкните Power Pivot > Управление, чтобы открыть окно Power Pivot.

Просмотрите вкладки в окне Power Pivot.

Каждая вкладка содержит таблицу в вашей модели. Столбцы в каждой таблице отображаются в качестве полей в списке полей сводной таблицы. Любой серый столбец скрыт от клиентских приложений.

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

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

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

в Excel щелкните Данные > Подключения;

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

щелкните Свойства > Определение, чтобы просмотреть строку подключения.

Примечание: Модели данных были введены в Excel 2013. Вы можете использовать их для создания сводных таблиц, сводных диаграмм и отчетов Power View, визуализирующих данные из нескольких таблиц. Дополнительные сведения о моделях данных можно узнать в Excel.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

Вычисляемое поле в Сводных таблицах в MS Excel

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

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

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

1. Организуйте свои исходные данные

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

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

Полезные советы:

2. Создаем и размещаем макет

Откроется окно «Создание ….. ». Убедитесь, что в поле Диапазон указан правильный источник данных. Затем выберите местоположение для свода:

Нажатие ОК создает пустой макет без цифр в целевом местоположении, который будет выглядеть примерно так:

Полезные советы:

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

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

3. Как добавить поле

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

По умолчанию Microsoft Excel добавляет поля в раздел «Макет» следующим образом:

4. Как удалить поле из сводной таблицы?

Чтобы удалить любое поле, вы можете выполнить следующее:

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

5. Как упорядочить поля?

Вы можете изменить расположение показателей тремя способами:

Все внесенные вами изменения применяются немедленно.

Ну а ежели спохватились, что сделали что-то не так, не забывайте, что есть «волшебная» комбинация клавиш CTRL+Z, которая отменяет сделанные вами изменения (если вы не сохранили их, нажав соответствующую клавишу).

6. Выберите функцию для значений (необязательно)

По умолчанию Microsoft Excel использует функцию «Сумма» для числовых показателей, которые вы помещаете в область «Значения». Когда вы помещаете нечисловые (текст, дата или логическое значение) или пустые значения в эту область, к ним применяется функция «Количество».

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

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

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

В Excel 2010 и ниже опция «Суммировать значения по» также доступна на ленте – на вкладке «Параметры» в группе «Расчеты».

7. Используем различные вычисления в полях значения (необязательно)

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

Это называется «Дополнительные вычисления». Доступ к ним можно получить, открыв вкладку «Параметры …», как это описано чуть выше.

Подсказка. Функция «Дополнительные вычисления» может оказаться особенно полезной, когда вы добавляете одно и то же поле более одного раза и показываете, как в нашем примере, общий объем продаж и объем продаж в процентах от общего количества одновременно. Согласитесь, обычными формулами делать такую таблицу придется долго. А тут – пара минут работы!

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

Советы

Предупреждения

Работа со сводными таблицами в Excel

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

Заменим выручку на прибыль.

Товары и области меняются местами также перетягиванием мыши.

Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.

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

Источник данных сводной таблицы Excel

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

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

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

3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.

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

В целом требований немного, но их следует знать.

Вычисляемое поле. Алгоритм расчета

Для каждого месяца у нас есть только одно значение фактических продаж (столбец Продажи) и плана. Вычисляемое поле ПроцентВыполнения возвращает значение равное их отношению. Например, для января 2012 года – это 50,19% (продано было 36992,22, а план был 73697,76). 36992,22/73697,76=0,5019 (см. строку 10 на листе Исходная таблица).

Читайте также: Фарматекс что это такое

Теперь проверим итоги по месяцам. За январь итоговым значением является 93,00%. Как это значение получилось?

Сначала программа вычислила СУММУ продаж за январь по всем годам, затем, вычислила СУММУ всех плановых значений. Разделив одно на другое, было получено 93,00%. В этом можно убедиться проделав вычисления самостоятельно (см. строку 10 на листе Сводная таблица, столбцы H:J).

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

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

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

Чтобы обойти данное ограничение и вычислить, например, средний % выполнения плана для всех январских месяцев, придется отказаться от Вычисляемого поля. Создайте в исходной таблице новый столбец – отношение продажи к плану для каждого месяца (см. лист Исходная таблица2). Затем, создайте на ее основе другую сводную таблицу. В окне параметров полей значений установите Среднее.

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

Изменяем и удаляем Вычисляемое поле

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