Сводные таблицы в эксель

Применение умных таблиц

Давайте вернемся к исходной таблице с данными, встанем в любую ячейки таблицы и в панели вкладок перейдем на Главная -> Стили -> Форматировать как таблицу:

Среди стилей оформления выбираем любой понравившийся (внешний вид на функциональности не влияет), далее выбираем диапазон для таблицы (по умолчанию Excel автоматически его определяет) и нажимаем OK:

Полученная умная таблица обладает большим спектром удобных свойств, но в данном случае нам интересны 2:

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

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

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

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

Как создается сводная таблица в Excel

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

Если не обратить на это внимание, в дальнейшем могут возникнуть проблемы. Условия таковы:

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

Рассмотрим два метода создания табличного отчета.

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

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

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

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

1

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

2

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

  1. Необходимо открыть вкладку «Вставка», кликнуть по пункту «Сводная таблица» в левой части экрана.

3

  1. На экране появится окошко для составления сводной таблицы. Выбираем диапазон данных, из него будет создан отчет. Сначала в строке появится имя первой таблички – если есть необходимость, можно выбрать другие ячейки или указать имя другой таблицы из того же документа.
  2. Выберем место, где будет размещена сводная таблица. Ее можно поместить на тот же лист или на новый в одном документе с начальными данными.
  3. После заполнения всех полей нажимаем «ОК».

4

  1. Откроется окно для формирования таблички. В нем находится список полей и области настроек. В верхней части окна выбираем нужные поля. После этого перетаскиваем их в нужные области.

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

5

Когда таблица сформирована, нужно применить выбранные фильтры. Разберем этот этап по шагам. Стоит вспомнить условие: нужно определить продажи женских теннисных кроссовок.

  1. Открываем раздел «Пол» в таблице и выбираем «Женский», после этого кликаем «ОК».

6

  1. Применяем фильтр к виду спорта – согласно условию, необходимо поставить галочку в графе «Теннис» и кликнуть «ОК».

Результат: на листе представлены только запрошенные данные. Информация о сумме в нескольких строчках с одинаковым наименованием суммируется.

7

Использование Мастера сводных таблиц

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

  1. Открываем вкладку «Файл», ее раздел «Параметры». Находим пункт «Панель быстрого доступа», в появившемся списке выбираем пункт «Мастер сводных таблиц и диаграмм». Кнопка «Добавить» станет активной, по ней нужно кликнуть, а после – нажать «ОК».

8

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

9

  1. Второй шаг работы с Мастером – определение диапазона данных. Программа автоматически выбирает диапазон, поэтому нужно проверить правильный ли он. Если данные выбраны неправильно, выбираем ячейки вручную. После настройки диапазона нажимаем «Далее».

10

  1. Определяем, где собираемся разместить сводную табличку. Если выбираете «Существующий лист», укажите конкретный лист из документа.

11

  1. Заполняем форму сводной таблички по правилам классического метода и выставляем фильтры.

Использование Мастера сводных таблиц

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

  1. Открываем меню Файл, кликаем по строке “Параметры”, далее – “Панель быстрого доступа”. Выбрав “Команды не на ленте” в предлагаемом перечне нам нужен пункт “Мастер сводных таблиц и диаграмм”. Отмечаем его курсором, нажимаем “Добавить >>” и завершаем настройки кликом по кнопке OK.
  2. В самом верхнем левом углу окна программы появится значок, нажав на который, запускаем Мастер сводных таблиц.
  3. В открывшемся окне необходимо выбрать источник данных, и на выбор может предлагаться до четырех опций. В нашем случае останавливаемся на первом варианте, т.е. создаем таблицу из списка или базы данных Excel. В нижней части окна выбираем пункт “сводная таблица” и нажимаем “Далее”.
  4. Появится следующее окно, где нужно указать координаты исходной таблицы, из которой будет сформирована сводная таблица. Если мы согласны с диапазоном, присвоенным программой автоматически, кликаем по кнопке “Далее”, либо сначала выделяем нужную область и затем уже двигаемся дальше.
  5. Аналогично ранее рассмотренному примеру выбираем место для размещения сводной таблицы и кликаем “Готово”. На выбор предлагаются две опции.
    • на новом листе
    • на существующем листе (нужно выбрать конкретный лист).
  6. Будет создана уже знакомая нам форма для конструирования сводной таблицы. Далее приступаем к ее настройке согласно нашим пожеланиям и задачам.

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

  1. Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.
  2. Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .
Рассмотрим первый способ.

Шаг первый.

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

Для этого следует кликнуть правой кнопкой мыши по ленте (панели инструментов) и выбрать из выпадающего меню пункт «Настройка ленты»

Настройка ленты

либо войти во вкладку

«Файл» => «Параметры» => «Настройка ленты».

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

Из списка выбираем «Мастер сводных таблиц и диаграмм»

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

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

Когда группа создана, выделите ее курсором, выделите курсором «Мастер сводных таблиц и диаграмм» в левом окне и нажмите кнопку «Добавить >>».

После нажмите «Ок».

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

Мастер сводных на панели

Шаг второй. Построение сводной таблицы из нескольких источников данных.

  • Кликнуть по кнопке мастера построения сводных таблиц.
  • На первом окне поставить флажок, напротив «в нескольких диапазонах консолидации» и флажок напротив «сводная таблица»

  • Во втором окне выбрать «Создать одно поле страницы»

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

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

  • Нажать кнопку «Готово».

Данный способ заключается в использовании запроса надстройки Power Query.

О данной надстройке рассказывалось в статье: «Power Query» в «Excel» — что это?

Создание запроса Power Query для сведения нескольких страниц книги в одну таблицу.

Шаг первый.

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

Шаг два.

Для этого во вкладке Power Query нужно нажать кнопку «Из таблицы» и указать в появившемся окне диапазон – источник данных. После чего нажать «Ок».

Power Query из таблицы

Шаг три.

Когда создан второй запрос, нужно во вкладке Power Query кликнуть по кнопке «Слияние запросов» и настроит в появившемся окне вид получившейся общей таблицы.

Слияние запросов

Шаг четыре.

Когда вид настроен, нужно нажать кнопку «Закрыть и загрузить.»

Закрыть и загрузить

Надстройка Power Query соберет данные с двух листов и соединит их в одной таблице.

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

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

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

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

В итоге мы получим следующий пример сводной таблицы:

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

В динамике добавление полей выглядит так:

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

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

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

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

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

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

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

Появляется диалоговое окно Создание сводной таблицы.  

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

Далее выбираем, куда поместить непосредственно Сводную таблицу. На новый лист или На существующий лист. Как правило Сводную таблицу помещают на новый лист. Если ее поместить на существующий лист, то в этом диалоговом окне, в соответствующем поле Диапазон, можно указать место куда разместить Сводную таблицу.

Нажимаем ОК.

Открылся новый лист в правой части которого появился блок настройки Сводной таблицы — Поля сводной таблицы. 

Он содержит в себе следующие элементы:

  • Поля для добавления в отчет. Здесь можно выбрать элементы исходной таблицы (диапазона данных), которые будут отображаться в Сводной таблицы. Чтобы выбрать нужный элемент, напротив него необходимо поставить галочку.
  • Фильтры. Здесь находятся элементы, которые будут фильтровать данные, отображаемые в  Сводной таблицы.
  • Столбцы. Здесь находятся элементы, которые будут отображаться в Сводной таблице в качестве столбцов.
  • Строки. Здесь находятся элементы, которые будут отображаться в Сводной таблице в качестве строк.
  • Значения. Здесь находятся элементы, которые будут отображаться в Сводной таблице в качестве числовых данных.

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

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

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

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

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

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

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

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

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

Как сделать сводную таблицу в excel: пошаговая инструкция

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

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

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

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

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

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

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

Числовой формат мы выбираем для следующего окна и отмечаем «Разделитель групп разрядов». Подтверждаем кнопкой «ОК».

Сортировка значений

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

Для этого нужно сделать следующее.

  1. Кликните на треугольник около нужного поля.
  2. В результате этого вы увидите следующее меню. Здесь вы можете выбрать нужный вариант сортировки («от А до Я» или «от Я до А»).

Если стандартного варианта недостаточно, вы можете в этом же меню кликнуть на пункт «Дополнительные параметры сортировки».

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

Здесь всё настроено в автоматическом режиме. Если вы уберете эту галочку, то сможете указать необходимый вам ключ.

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

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

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

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

  1. Открываем вкладку «Анализ» (в более ранних версиях – «Параметры») и кликаем по кнопке «Источник данных».

19

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

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

Если отчет не переведен в динамический формат, все еще есть способы обновить информацию в нем после внесения изменений в источник.  Обновим сводную таблицу вручную. Для этого кликнем по любой ячейке отчета и по пункту «Обновить» в контекстном меню. Также можно воспользоваться кнопкой с тем же названием на вкладке «Параметры»/«Анализ».

20

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

  1. Выбираем любую ячейку отчета и открываем вкладку «Параметры»/«Анализ». Находим пункт «Сводная таблица» – в нем нужно выбрать пункт «Параметры».

21

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

22

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

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

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

  1. Перейдите на вкладку «Вставка». Затем нажмите на иконку «Таблица». В появившемся меню выберите второй пункт.
  1. Сразу после этого появится окно, в котором будут различные примеры для построения. Подобные варианты предлагаются на основе нескольких столбцов. От их количества напрямую зависит число шаблонов.
  1. При наведении на каждый пункт будет доступен предварительный просмотр результата. Так работать намного удобнее.
  1. Можно выбрать то, что нравится больше всего.
  1. Для вставки выбранного варианта достаточно нажать на кнопку «OK».
  1. В итоге вы получите следующий результат.

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

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

  1. Таблица составляется по практически любому объему данных.
  2. Редактировать вид отчета можно через меню форматов – встроенная библиотека содержит множество цветовых тем таблиц.
  3. Возможно объединение данных в более широкие группы, например несколько дат объединяются в кварталы.
  4. По результатам отчета можно провести расчеты с помощью инструментов Excel, это не повлияет на источники данных.
  5. Информация в сводной таблице может стать основой для составления графика или другого визуального отчета.

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

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

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

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

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

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

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

Произвольная группировка в сводной таблице

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

Опять немного видоизменим нашу сводную таблицу и в этот раз в поля добавим наименование в текстовом виде:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Теперь обо всем по порядку.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как обновить вручную.

  1. Нажмите в любом месте на свод.
  2. На вкладке «Анализ» нажмите кнопку «Обновить» или же нажмите клавиши ALT + F5.

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

Чтобы обновить все сводные таблицы в файле, нажмите стрелку кнопки «Обновить», а затем  —  «Обновить все».

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

После запуска обновления вы можете просмотреть статус или отменить его, если вы передумали. Просто нажмите на стрелку кнопки «Обновить», а затем — «Состояние обновления» или «Отменить обновление».

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

  1. Откройте вкладку параметров, как это мы только что делали.
  2. В диалоговом окне «Параметры … » перейдите на вкладку «Данные» и установите флажок «Обновить при открытии файла».

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

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

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

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

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

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

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

Рейтинг
( Пока оценок нет )
Понравилась статья? Поделиться с друзьями:
Вековой опыт
Добавить комментарий

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