Сводная таблица в excel – как сделать

Автор: · Опубликовано 10.03.2018 · Обновлено 08.02.2019

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

Начало работы

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

Например, рассмотрим одни и те же финансовые расходы в разных месяцах.

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

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

  1. Для начала ее необходимо полностью выделить.
  1. Затем перейдите на вкладку «Вставка». Нажмите на иконку «Таблица». В появившемся меню выберите пункт «Сводная таблица».
  1. В результате этого появится окно, в котором вам нужно указать несколько основных параметров для построения сводной таблицы. Первым делом необходимо выбрать область данных, на основе которых будет проводиться анализ. Если вы предварительно выделили таблицу, то ссылка на нее подставится автоматически. В ином случае ее нужно будет выделить.
  1. Затем вас попросят указать, где именно будет происходить построение. Лучше выбрать пункт «На существующий лист», поскольку будет неудобно проводить анализ информации, когда всё разбросано на несколько листов. Затем необходимо указать диапазон. Для этого нужно кликнуть на иконку около поля для ввода.
  1. Сразу после этого мастер создания сводных таблиц свернется до маленького размера. Помимо этого, изменится и внешний вид курсора. Вам нужно будет сделать левый клик мыши в любое удобное для вас место.
  1. В результате этого ссылка на указанную ячейку подставится автоматически. Затем нужно нажать на иконку в правой части окна, чтобы восстановить его до исходного размера.
  1. Для завершения настроек нужно нажать на кнопку «OK».
  1. В результате этого вы увидите пустой шаблон, для работы со сводными таблицами.
  1. На этом этапе необходимо указать, какое поле будет:
    1. столбцом;
    2. строкой;
    3. значением для анализа.

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

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

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

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

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

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

Обратите внимание: таблица создалась на новом листе. Это будет происходить каждый раз при использовании конструктора.

Анализ

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

Рассмотрим каждую из них более детально.

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

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

  • изменить имя;
  • вызвать окно настроек.

В окне параметров вы увидите много чего интересного.

Активное поле

При помощи этого инструмента можно сделать следующее:

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

Здесь вы сможете указать, в каком именно виде нужно выводить результат анализа информации.

Группировать

Благодаря этому инструменту вы можете настроить группировку по выделенным значениям.

Вставить срез

Редактор Microsoft Excel позволяет создавать интерактивные сводные таблицы. При этом ничего сложного делать не нужно.

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

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

Вставить временную шкалу

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

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

Затем нужно будет построить сводную таблицу.

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

  1. Затем нас попросят выбрать диапазон значений.
  1. Для этого достаточно выделить всю таблицу целиком.
  1. Сразу после этого адрес подставится автоматически. Здесь всё очень просто, поскольку рассчитано для чайников. Для завершения построения нажмите на кнопку «OK».
  1. Редактор Excel предложит нам всего один вариант, поскольку таблица очень простая (для примера больше и не нужно).
  1. Попробуйте снова нажать на иконку «Вставить временную шкалу» (она расположена на вкладке «Анализ»).
  1. На этот раз никаких ошибок не будет. Вам предложат выбрать поле для сортировки. Поставьте галочку и нажмите на кнопку «OK».
  1. Благодаря этому появится окошко, в котором можно будет выбирать нужную дату при помощи бегунка.
  1. Выбираем другой месяц и данных нет, поскольку все расходы в таблице указаны только за март.

Обновить

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

Источник данных

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

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

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

Действия

При помощи этого инструмента вы сможете:

  • очистить таблицу;
  • выделить;
  • переместить её.

Вычисления

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

К ним относятся:

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

Сервис

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

Показать

При помощи этого инструмента можно настроить внешний вид рабочего пространства редактора.

Благодаря этому вы сможете:

  • настроить отображение боковой панели со списком полей;
  • включить или выключить кнопки «плюс/мину»с;
  • настроить отображение заголовков полей.

Конструктор

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

Можно настроить:

  • промежуточные итоги:
    • не показывать;
    • показывать все итоги в нижней части;
    • показывать все итоги в заголовке.
  • общие итоги:
    • отключить для строк и столбцов;
    • включить для строк и столбцов;
    • включить только для строк;
    • включить только для столбцов.
  • макет отчета:
    • показать в сжатой форме;
    • показать в форме структуры;
    • показать в табличной форме;
    • повторять все подписи элементов;
    • не повторять подписи элементов.
  • пустые строки:
    • вставить пустую строку после каждого элемента;
    • удалить пустую строку после каждого элемента.
  • параметры стилей сводной таблицы (здесь можно включить/выключить каждый пункт):
    • заголовки строк;
    • заголовки столбцов;
    • чередующиеся строки;
    • чередующиеся столбцы.
  • настроить стиль оформления элементов.

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

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

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

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

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

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

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

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

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

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

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

Описанные выше действия подходят для современных редакторов (2007, 2010, 2013 и 2016 года). В старой версии всё выглядит иначе. Возможностей, разумеется, там намного меньше.

Для того чтобы создать сводную таблицу в Экселе 2003 года, нужно сделать следующее.

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

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

Заключение

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

Если данного самоучителя вам недостаточно, дополнительную информацию можно найти в онлайн справке компании Microsoft.

Видеоинструкция

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

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

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

Кто не любит сводные таблицы? Есть такие? Срочно восполняем пробел в этих статьях 1 и 2. Сегодня постараюсь подробно рассказать, как сделать формулы в сводной таблице без дополнительных в соседних ячейках. Запись формулы в сводной таблице подойдет, если вам необходимо держать все расчеты в одном месте и для сохранения формул при добавлении новых параметров в анализ. Удобно, если вы ищете лучший вариант сводной таблицы или анализируете данные!

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

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

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

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

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

Выбираем Параметры — раздел Сервис — Формулы

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

Ок. Готово.

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

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

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

Если есть вопросы, пишите нам в комментарии!

Поделитесь нашей статьей в ваших соцсетях:

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

Уровень мастерства: Средний

Содержание

Развертывание и свертывание полей занимает много времени

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

Чтобы развернуть или свернуть поле «ВСЕ», мы обычно нажимаем маленькие кнопки «развернуть» (+) и свернуть (-) для каждого элемента в поле.

Для этого есть ярлыки в контекстном меню и на вкладке «Параметры / Анализ» на ленте.

image

Мы также можем нажать кнопки «Развернуть поле» и «Свернуть поле» на вкладке «Анализ / параметры» на ленте. Ячейка в области строк или столбцов сводной таблицы должна быть выбрана для того, чтобы эти кнопки работали.

Сочетания клавиш для операций Развернуть/Свернуть:

Развернуть все поле: 

Свернуть все поле:  

Примечание. Для работы сочетаний клавиш должна быть выбрана ячейка в области «Строки или столбцы».

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

Новые кнопки «Развернуть» в сводных диаграммах Excel 2016

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

Однако эта функция доступна ТОЛЬКО в Excel 2016 (Office 365).

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

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

Шаги для изменения сводной диаграммы:

  1. Удалите кнопки поля Значения и Ось на графике.
  2. Удалите метки оси, заголовок и легенду.

Измените размер диаграммы, чтобы отображались только кнопки «Развернуть / Свернуть».

График будет скрыт за кнопками. Таким образом, мы просто используем сводную диаграмму для кнопок развернуть / свернуть. Важно отметить, что это будет работать только для полей в области строк сводной таблицы и только в Excel 2016 (Office 365).

Развернуть/Свернуть — макросы

Этот набор макросов развернет или свернет СЛЕДУЮЩЕЕ поле в области строк или столбцов. Это позволяет детализировать поля в строке или области столбцов.

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

Если сводная таблица в настоящее время свернута по годам, макрос «Expand_Entire_RowField» развернет ВСЕ элементы года, чтобы отобразить кварталы для каждого года.

Запуск макроса «Collapse_Entire_RowField» приведет к сворачиванию элементов Year обратно.

Вы можете скачать файл, содержащий макросы ниже.

Expand Collapse Entire Fields Macro.xlsm (68.3 KB)

Назначить макросы фигурам или кнопкам

Мы можем назначить эти макросы для фигур или кнопок на листе.

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

Формы можно вставлять в сводную диаграмму, чтобы имитировать функциональность кнопок развертывания / свертывания в Excel 2016.

Макро-код VBA

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

Макрос Expand находит поле в наивысшей позиции, которое свернуто, проверяя свойство ShowDetail каждого PivotItem. Если он НЕ находит расширенный элемент, он расширяет все поле. Если все элементы развернуты, то он переходит к полю в следующей позиции вниз.

 Sub Expand_Entire_RowField() ' Разверните поле самой низкой позиции в области строк ' который в настоящее время расширен (показаны детали)  Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim iFieldCount As Long Dim iPosition As Long    ' Создать ссылку на 1-ю сводную таблицу на листе   ' Может быть изменен для ссылки на конкретный лист или сводную таблицу.   Set pt = ActiveSheet.PivotTables(1)    ' Количество полей в области строк минус 1 (последнее поле не может быть расширено)   iFieldCount = pt.RowFields.Count - 1      ' Цикл по положению поля   For iPosition = 1 To iFieldCount     ' Петлевые поля в области Rows     For Each pf In pt.RowFields       ' Если позиция соответствует первой переменной цикла, то       If pf.Position = iPosition Then         ' Зациклить каждый элемент поворота         For Each pi In pf.PivotItems           ' Если основной элемент свернут, то           If pi.ShowDetail = False Then             ' Развернуть все поле             pf.ShowDetail = True             ' Выйти из макроса             Exit Sub           End If         Next pi       End If     Next pf   ' Если линия Exit Sub не нажата, то   ' цикл продолжится до следующей позиции поля   Next iPosition    End Sub 

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

 Sub Collapse_Entire_RowField() ' Свернуть поле самой низкой позиции в области строк  ' который в настоящее время расширен (показывая детали)  Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim iFieldCount As Long Dim iPosition As Long    ' Создать ссылку на первую сводную таблицу на листе   ' Может быть изменен для ссылки на конкретный лист или сводную таблицу.   Set pt = ActiveSheet.PivotTables(1)    ' Количество полей в области строк минус 1 (последнее поле не может быть расширено)   iFieldCount = pt.RowFields.Count - 1      ' Цикл назад по положению поля (шаг -1)   For iPosition = iFieldCount To 1 Step -1     ' Цикл полей в области строк     For Each pf In pt.RowFields       ' Если позиция соответствует первой переменной цикла, то       If pf.Position = iPosition Then         ' Зациклить каждый элемент поворота         For Each pi In pf.PivotItems           ' Если элемент разворачивается, то           If pi.ShowDetail = True Then             ' Свернуть все поле             pf.ShowDetail = False             ' Выход из макроса             Exit Sub           End If         Next pi       End If     Next pf   ' Если линия Exit Sub не нажата, то   ' Цикл продолжится до следующей позиции поля   Next iPosition  End Sub 

Скачать файл

Вы можете скачать файл, содержащий макросы для областей строк и столбцов.

Expand Collapse Entire Fields Macro.xlsm (68.3 KB)

Что можно улучшить?

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

Пожалуйста, оставьте комментарий ниже с вопросами или предложениями о том, как мы можем улучшить это. Спасибо! 🙂

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

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

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

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

Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.

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

— Можно ли отчет сделать не по выручке, а по прибыли?

— Можно ли товары показать по строкам, а регионы по столбцам?

— Можно ли такие таблицы делать для каждого менеджера в отдельности?

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

Рассмотрим, как создать сводную таблицу в Excel.

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

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

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

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

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

Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.

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

Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.

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

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

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

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

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

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

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

Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».

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

На ее построение потребовалось буквально 5-10 секунд.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

или через команду во вкладке Данные – Обновить все.

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

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

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

Скачать файл с примером.

Поделиться в социальных сетях:

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

—>

—>Главная—> » —>Статьи—> » Excel » Сводные таблицы

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

Если вы ещё не знакомы со сводными таблицами, то начните с этой статьи.

Проблема

Бывает так, что анализируемые данные попадают к нам в виде отдельных таблиц, которые, тем не менее, нужно связать. Это легко может сделать MS Access, а в Excel для этого приходилось всегда использовать формулы типа ВПР (VLOOKUP). Однако, начиная с Excel 2013, у нас появилась возможность при построении сводной таблицы в качестве источника использовать несколько таблиц, связанных между собой по ключевым полям.

Пример

В нашем примере мы располагаем 4-мя таблицами: Заказы, Строки заказов, Товары, Клиенты.

Таблица заказов:

image

Таблица Строк заказов:

image

Таблица Товаров

image

Таблица Клиентов

image

Исходные таблицы оформлены в виде умных таблиц: Orders, OrderLines, Goods и Clients.

image

Вполне очевидно, что таблицы Orders и OrderLines могут быть связаны по полю ID_Заказа, таблицы Orders и Clients — по полю ID_клиента, таблицы OrderLines и Goods — по полю ID_товара.

Скачать пример

Создание модели данных

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

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

  2. В появившейся панеле Поля сводной таблицы вы видите надпись ДРУГИЕ ТАБЛИЦЫ…

  3. Нажмём её. Появится такой вопрос:

  4. Отвечаем Да и видим, что в список полей добавились все наши таблицы:

  5. Если вы начнёте выбирать поля, то через некоторое время в списке полей появится кнопка СОЗДАТЬ…

  6. Нажмём её и создадим связи между нашими таблицами. Так создаётся связь между таблицей Orders и OrderLines. Обратите внимание, что Excel умеет создавать связь типа «один к одному» или «один ко многим«. Причём первой надо указывать таблицу, где «много», в противном случае Excel ругается и предлагает поменять их местами.

  7. Аналогично создаём другие связи.

  8. В диалоговое окно Управление связями можно попасть через ленту АНАЛИЗ команда Отношения

  9. Чтобы видеть больше полей на панеле Поля сводной таблицы, можно через кнопку Сервис (в виде шестерёнки) выбрать это представление:

  10. Результат будет таким:

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

Просто и удобно!

Читайте также:

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

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

—>Категория—>: Сводные таблицы | —>Добавил—>: dsb75 (23.10.2014) |
—>Просмотров—>: 35179 | | |
—>Всего комментариев—>: 2
image2 dsb75   (19.08.2015 12:14) [Материал]
image1 dsb75   (19.08.2015 12:13) [Материал] Получил такое письмо: ——————————- Денис, здравствуйте, спасибо за вашу статью про сводные таблицы по нескольким диапазонам. http://perfect-excel.ru/publ….-1-0-67   не могли бы вы подсказать, как с помощью сводных таблиц узнать стоимость заказов из вашего примере.   например, построить такие отчеты.   — вид продукта — общая стоимость согласно заказам — клиент — общая сумма заказов — заказ № — стоимость заказа  и т.п.   именно через сводные таблицы, а не модификацией текущих четырех таблиц.  Заранее большое спасибо.

—>Добавлять комментарии могут только зарегистрированные пользователи.—> [ —>Регистрация—> | —>Вход—> ]—>

Оцените статью
Рейтинг автора
5
Материал подготовил
Илья Коршунов
Наш эксперт
Написано статей
134
А как считаете Вы?
Напишите в комментариях, что вы думаете – согласны
ли со статьей или есть что добавить?
Добавить комментарий