Как Вставить Расчет в Сводную Таблицу Excel • Динамика продаж

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

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

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

Шаг 1. Включаем надстройку Power Pivot.

Надстройка Power Pivot входит в стандартный комплект Excel 2013, 2016 и Excel 365 для Windows. Она подключается одной галочкой в окне надстроек:

ФайлПараметрыНадстройкиНадстройки COMMicrosoft Power Pivot.

Шаг 2. Загружаем исходную таблицу Exсel в модель данных Power Pivot и создаем сводную таблицу с подключением к модели. В качестве примера возьмем таблицу (рис. 1), в которой ведутся остатки ассортимента в магазине обуви.

Как Вставить Расчет в Сводную Таблицу Excel • Динамика продаж

Исходную таблицу с информацией об остатках обуви загружаем в модель данных Power Pivot, как показано на рис. 2.

Как Вставить Расчет в Сводную Таблицу Excel • Динамика продаж

Выполнив загрузку, выходим из окна Power Pivot, создаем новый лист, через команду «Вставка» строим сводную таблицу.

Важный момент: в случае с Power Pivot для создания сводной таблицы выделять исходную таблицу не нужно.

Шаг 3. Добавим в область строк сводной таблицы следующие поля: Тип обуви, Полное наименование, Размеры, Цвет (рис. 3).

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

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

Чтобы создать меру, нужно на вкладке Power Pivot выбрать «Меры» и «Создать меру» (см. рис. 3).

Как Вставить Расчет в Сводную Таблицу Excel • Динамика продаж

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

Как Вставить Расчет в Сводную Таблицу Excel • Динамика продаж

Функция VALUES() создает таблицу из одного столбца с уникальными значениями (в нашем случае это «коричневый», «синий», «черный»).

Функция COUNTROWS() подсчитывает в таблице количество строк.

К мере применяются фильтры сводной таблицы (учитываются ее разрезы).

В группировке Ботильонов и следующего наименования обуви указано 3 — максимальное количество уникальных цветов. Все три цвета повторяются в первом по порядку размере 38, поэтому у данного размера тоже 3. В размере 39 только один цвет, поэтому стоит 1 (рис. 5).

Как Вставить Расчет в Сводную Таблицу Excel • Динамика продаж

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

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

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

Как Вставить Расчет в Сводную Таблицу Excel • Динамика продаж

Чтобы обойти данную ошибку, нужно определить, когда VALUES() возвращает одно или несколько значений. Для этого воспользуемся функцией HASONEVALUE(). Она возвращает 1 (ИСТИНА), если в ней столбец с одним значением, и 0 (ЛОЖЬ), если столбец с несколькими значениями.

Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 10, 2018.

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Вы, наверное, заметили, что при создании новой сводной таблицы Excel автоматически добавляет к названиям строк и столбцов кнопки фильтрации. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Чтобы завершить создание сводной таблицы, осталось назначить поля, перечисленные в списке, различным частям таблицы. Эта задача осуществляется путем перетаскивания имени поля из списка в соответствующую зону нижней части панели задач Поля сводной таблицы.

Как строить сводные таблицы для бизнеса в Excel | Бизнес-школа LABA (ЛАБА)

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

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

Для начала встанем в любую ячейку нашей таблицы с данными. Далее в панели вкладок перейдем Вставка -> Сводная таблица:

В данном меню мы можем настроить 2 основных момента — на основе каких данных построить таблицу и где ее разместить.

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

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

Нажимаем OK и перед нами появляется следующий конструктор:

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

В правой части мы видим следующие элементы (список полей и области):

  • Список полей;
    Список всех заголовков столбцов исходной таблицы с данными.
  • Фильтры;
    Добавление дополнительного среза для детализации данных.
  • Строки;
    Поля таблицы вынесенные в строки.
  • Столбцы;
    Поля таблицы вынесенные в столбцы;
  • Значения.
    Вычисляемые числовые данные по соответствующим полям из строк и столбцов (единственный вычисляемый элемент в таблице).

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

Microsoft Excel 2003
2. Если перетащить один элемент (или два, реже – три и более) «Списка полей сводной таблицы» в левую зону шаблона, где он станет «Полем строк», то мы получим из записей этого поля базы данных заголовки строк сводной таблицы.
специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Во-вторых, тогда, когда приходится через определенные промежутки времени повторять пересчитывать отчеты в связи с изменениями источника таблицы базы данных. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
В MS Excel 2007 по команде «Сводная таблица» выпадает диалоговое окно «Создание сводной таблицы», в котором достаточно указать источник данных для анализа и место расположения создаваемой сводной таблицы.

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

  • Умная таблица получает имя (в примере выше Таблица1, его можно легко поменять), которое можно использовать для определения диапазона в качестве источника для сводной таблицы;
  • Умная таблица автоматически изменяет размер при добавлении или удалении новых строк или столбцов.

Функция СРЗНАЧЕСЛИ была введена в Excel 2007. Если вам нужно, чтобы ваша книга была совместима с более ранними версиями, используйте эти формулы:
=СУММЕСЛИ(C2:C27;”Женский”;B2:B27)/СЧЕТЕСЛИ(C2:C27;”Женский”)
=СУММЕСЛИ(C2:C27;”Мужской”;B2:B27)/СЧЕТЕСЛИ(C2:C27;”Мужской”)

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

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

Как Вставить Расчет в Сводную Таблицу Excel • Динамика продаж

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

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

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

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

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

Закройте диалоговое окно Параметры сводной таблицы щелчком на кнопке ОК.

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

  • Промежуточные итоги в заголовке группы, если нужно представлять сначала итоги, а затем данные элемента поля.
  • Пустая строка после каждого элемента, если нужно, чтобы элементы поля данных отделялись друг от друга пустой строкой.
  • Разрыв страницы после каждого элемента, чтобы данные каждого элемента поля выводились на печать на отдельных листах.
специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
В окне Поля встаем на поле Прибыль и нажимаем кнопку Добавить поле так чтобы оно появилось в поле Формула , затем нажимаем на клавиатуре Знак деления, встаем на поле Сумма и снова нажимаем кнопку Добавить поле. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Допустим, у нас есть построенная сводная таблица с результатами анализа продаж по месяцам для разных городов (если необходимо, то почитайте эту статью, чтобы понять, как их вообще создавать или освежить память):
Как Вставить Расчет в Сводную Таблицу Excel • Динамика продаж

Волшебные сводные таблицы Excel | Блог Александра Воробьева

  1. Выведите на экран панель инструментов Сводные таблицы.
  2. Установите указатель мыши на любую ячейку сводной таблицы.
  3. Щелкните на кнопке Обновить данные панели инструментов Сводные таблицы.
  4. Чтобы установить обновление сводной таблицы при каждом открытии книги, откройте диалоговое окно Параметры сводной таблицы (см. рис.9.7). Для этого щелкните правой кнопкой мыши на любой ячейке уже созданной сводной таблицы и в появившемся контекстном меню выберите команду Параметры таблицы. Затем в группе Источник установите флажок обновить при открытии.

8. Нажмем на кнопку со стрелкой вниз в ячейке B1 и в выпавшем списке вместо записи «(Все)» выберем «3», соответствующую интересующему нас заказу №3 и закроем список, нажав кнопку «OK».

Форматирование сводной таблицы.

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

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

1. Продолжим работу с созданной таблицей. Активируем мышью ячейку B5.

2. На панели инструментов «Сводная таблица» нажимаем кнопку «Параметры поля» (выделена справа на снимке, расположенном ниже).

Как Вставить Расчет в Сводную Таблицу Excel • Динамика продаж

3. В выпавшем окне «Вычисление поля сводной таблицы» меняем имя и жмем кнопку «OK».

5. На панели «Сводная таблица» нажимаем кнопку «Формат отчета» (выделена слева на снимке, расположенном выше — над п.3).

6. В появившемся окне «Автоформат» выбираем из предложенных вариантов форматирования, например, «Отчет 6» и нажимаем «OK» .

Как Вставить Расчет в Сводную Таблицу Excel • Динамика продаж

Внешний вид нашей сводной таблицы существенно преобразился.

svodnaya-tablitsa-5-15s

Сводная таблица с текстовыми полями в области значений
Страница – поле, которому в отчете сводной таблице или сводной диаграммы назначена страничная ориентация, обеспечивает фильтрацию данных сводной таблицы по значениям выбранных полей, может содержать несколько полей списка; поле страницы
специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Среди стилей оформления выбираем любой понравившийся внешний вид на функциональности не влияет , далее выбираем диапазон для таблицы по умолчанию Excel автоматически его определяет и нажимаем OK. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Чтобы переместить поле данных, поместите указатель мыши на любую кнопку поля и перетащите ее в новую область. Таким образом, можно преобразовать поле строки в поле столбца и наоборот. Можно также создать поле страницы, перетащив кнопку соответствующего поля в область над макетом сводной таблицы.

Как добавить формулу в сводную таблицу excel — Все про Эксель

Откройте ниспадающий список страниц щелчком на кнопке со стрелкой и выберите нужный параметр фильтрации. Например, в таблице рисунка можно проводить анализ отдельно по каждой категории товаров, по каждой дате и (рис. 9.8) по каждому продавцу.

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

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

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

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

Нажав курсором на сводную таблицу, появится раздел Работа со сводными таблицами в панели инструментов

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

Ссылка на сводную таблицу

Если вам все-таки нужно сослаться на сводную таблицу, то ссылка в большинстве случае будет иметь вид = ПОЛУЧИТЬ.ДАННЫЕ(…

Чтобы записать обычную формулу, самый простой способ такой, как показано ниже

Сводные таблицы в Excel: Создание, Фильтрация, Форматирование
Как мы видим, Excel автоматически определил диапазон исходной таблицы с данными (для этого мы как раз и перешли в произвольную ячейку внутри таблицы). Но в целом мы также можем и самостоятельно задать диапазон.
специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Другими словами мы вначале определяем какой именно вид должна принять таблица, а потом исходя из этого распределить поля по областям. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
#3. Избегайте нечисловых значений в столбцах, на основании которых будут построены расчетные метрики таблицы. Если такие данные присутствуют, при агрегировании они будут приравниваться к нулю — и итоговое значение может быть искажено.

Сводные таблицы в Excel: Форматирование

  1. Выберите любую ячейку в столбце С.
  2. Щелкните правой кнопкой мыши и в контекстном меню выберите Сортировка ► Сортировка от А до Я.
  3. Выберите Данные ► Структура ► Промежуточный итог для открытия диалогового окна Промежуточные итоги.
  4. В окне Промежуточные итоги выберите в списке При каждом изменении в пункт Пол, в списке ОперацияСреднее, а в поле Добавить итоги по установите флажок Оценка.

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

Как должны выглядеть источники данных

Предположим, вам нужно построить помесячные суммарные затраты по подкатегориям затрат с такими параметрами:

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

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

Как Вставить Расчет в Сводную Таблицу Excel • Динамика продаж

На основании этих данных и будем строить сводный отчет.

Сначала убедитесь, что исходные данные правильно оформлены:

#1. Каждый столбец должен содержать единый тип данных — даты должны находиться в поле с датами и иметь формат дат. В поле «Наименование» не должно быть информации о городе покупки.

#2. Не желательно оставлять пустые строки. Сводная таблица будет построена, но визуально наличие строк «(пусто)» неприятно. Их можно убрать с помощью фильтров.

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

Как Вставить Расчет в Сводную Таблицу Excel • Динамика продаж

#4. В качестве источника используйте именованные динамические таблицы. Так вам не придется постоянно менять диапазон данных перед обновлением сводной таблицы. Простой и удобный инструмент, который это реализует — «Умные таблицы» Excel.

Как Вставить Расчет в Сводную Таблицу Excel • Динамика продаж

Выделите любую ячейку уже «умной» таблицы и дайте ей подходящее имя.

Как Вставить Расчет в Сводную Таблицу Excel • Динамика продаж

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Excel добавит на ленту набор контекстных вкладок Работа со сводными таблицами с собственными контекстными вкладками Анализ и Конструктор. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Вы, наверное, заметили, что при создании новой сводной таблицы Excel автоматически добавляет к названиям строк и столбцов кнопки фильтрации. Эти кнопки позволяют отобрать определенные записи и отсортировать их в нужном порядке.

Фильтрация с помощью временных шкал

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

Изменение итоговой функции сводной таблицы

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

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

Изменение итоговой функции сводной таблицы

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

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

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

Вот и всё! Теперь вы умеете создавать сводные таблицы в Excel, форматировать, сортировать и фильтровать данные.

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Excel делает форматирование новой сводной таблицы, добавляемой на рабочий лист, таким же легким и быстрым, как и форматирование любой таблицы или списка данных. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Сводные таблицы в Excel позволяют экспериментировать с итоговыми данными в динамическом режиме. Кроме того, можно воспользоваться графическим образом сводной таблицы — сводной диаграммой, которая позволяет увидеть результаты анализа данных.

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

  • Список полей. Служит для сокрытия и отображения списка полей на панели задач в правой части области рабочего листа.
  • +/- Кнопки. Используется для сокрытия и отображения кнопок сворачивания (-) и разворачивания (+) конкретных строк и столбцов, позволяющих временно удалять и отображать в сводной таблице конкретные значения.
  • Заголовки полей. Служит для сокрытия и отображения полей, назначаемых меткам строк и столбцов сводной таблицы.

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

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: