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

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

Подготовка

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

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

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

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

Создание

Создать сводную таблицу можно в программах различных версий (2010-2016). Рассматриваемая инструкция создана на основе Майкрософт Эксель 2013.

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

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

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

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

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

Обратите внимание! Данным способом невозможно консолидировать (объединить) несколько таблиц.

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

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

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

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

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

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

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

Обратите внимание! Если перед этим этапом произвести выделение таблицы, то все поля заполнятся автоматически.

Заполнение

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

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

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

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

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

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

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

  • В появившемся окне можно выбрать желаемый тип операции.

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

Сложности при работе

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

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

Обновление данных

  1. Если в ходе работы в объект с исходными данными были внесены изменения, необходимо нажать правой кнопкой мыши по колонке сводной таблицы и выбрать пункт «Обновить».
  2. Для того, чтобы получить обновление сразу всей конструкции, требуется зайти во вкладку «Анализ» и выбрать строку «Обновить все».

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

  • Чтобы добавить в объект большее количество строк или столбцов, их необходимо ввести в таблицу с исходными данными, после чего изменить адрес готовой конструкции, зайдя во вкладку «Анализ» и выбрав пункт «Источник данных».
  • После выполнения данных действий  на рабочем поле появится окошко для ввода новых данных, как правило они добавляются автоматически и остается только подтвердить свой выбор нажав кнопку «Ок».

Конструкция собранная с нескольких листов

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

  1. В открывшемся окне следует выбрать раздел «Панель быстрого доступа», затем строку «Все команды» и пункт» Мастер сводных таблиц и диаграмм».
  2. Желаемая функция отобразится рядом с треугольником вызывающим меню.

Примеры работы с данной функцией будут проиллюстрированы. Первым делом необходимо выбрать тип создания объекта. Требуется отдать предпочтение пункту «В нескольких диапазонах».

  • После рекомендуется выбрать пункт «Создать одно поле».

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

  1. Последний этап, выбор расположения нового объекта.
  2. После проделанной работы на новой странице появится сложная сводка.
Читайте также:  Как сделать таблицу в Excel список клиентов?

Удаление

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

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

Поделись с друзьями!

Источник: https://mir-tehnologiy.ru/svodnaya-tablitsa-v-excel-kak-sdelat/

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

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

Проблема

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

Это легко может сделать MS Access, а в Excel для этого приходилось всегда использовать формулы типа ВПР (VLOOKUP).

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

Пример

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: http://perfect-excel.ru/publ/excel/svodnye_tablicy/svodnaja_tablica_na_osnove_dvukh_i_bolee_svjazannykh_tablic/5-1-0-67

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

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

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

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

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

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

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

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

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

    Выберите любую ячейку исходной таблицы.

  2. Перейдите на вкладку ленты Вставка [Insert] и найдите группу Таблицы [Table].

  3. Выберите команду Рекомендуемые сводные таблицы [Recommended PivotTables].

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

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

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

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

    Выберите любую ячейку исходной таблицы.

  2. Перейдите на вкладку ленты Вставка [Insert] и найдите группу Таблицы [Table].

  3. Выберите команду Сводная таблица [PivotTable].

  4. В диалоговом окне Создание сводной таблицы [Create PivotTable] убедитесь в правильности диапазона данных, на основе которого будет строиться отчет. Если диапазон некорректный, его нужно поменять в поле Таблица и диапазон [Table/Range].

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

Читайте также:  Как сделать накладную в Word?

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

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

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

  1. После формирования сводной таблицы в выбранном месте появиться область с ее названием. По умолчанию отчет называется СводнаяТаблица1 [PivotTable 1].  Для начала работы необходимо щелкнуть левой клавшей мыши по данной области. В результате в правой части листа откроется макет сводной таблицы.

Макет сводной таблицы состоит из списка полей сводной таблицы, в котором перечислены все заголовки исходной таблицы и четырех областей: ФИЛЬТРЫ, КОЛОННЫ, СТРОКИ и ЗНАЧЕНИЯ.

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

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

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

Данную операцию можно сделать еще 2 способами:

  • отметить флажок напротив поля Группа;
  • щелкнуть по полу Группа правой кнопкой мыши и выбрать Добавить в названия строк [Add to Row Labels].

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

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

Теперь осталось добавить сумму по полю Рыночная стоимость. Для этого перенесем поле Рыночная стоимость в область значений. Желаемая таблица получена.

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

Скачать

Источник: https://micro-solution.ru/excel/pivot-tables/first-pivot-table

Новые возможности сводных таблиц в Excel 2013

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

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

На вкладке Вставка (Insert) появилась кнопка Рекомендуемые сводные таблицы (Recommended Pivot Tables), предлагающая несколько подходящих сводных таблиц разного типа на выбор в зависимости от вида исходных данных:

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

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

Фильтрация с помощью Временной Шкалы

К возможности фильтровать сводные таблицы срезами (slicers), появившейся в Excel 2010, добавили Временную шкалу (Timeline) — интерактивную графическую ось времени, которую можно вставить на вкладке Анализ (Analysis) . Выделив любой временной интервал на оси, мы получим в привязанной к ней сводной результаты именно за выбранный период:

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

Новый механизм расчета сводных таблиц (Data Object Model)

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

Теперь при построении сводной таблицы можно установить в нижней части диалога неприметную галочку Добавить эти данные в модель данных (Add to Data Object Model) и, тем самым, использовать для построения сводной «движок» Power Pivot, т.е. по-сути полноценную базу данных, встроенную в Excel:

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

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

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

На вкладке Данные (Data) появилась кнопка Отношения (Relationships), позволяющая связать нескольких таблиц по ключевым полям (столбцам) с помощью вот такого диалога:

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

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

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

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

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

Функция подсчета количества уникальных элементов

Если вы строите сводную с использованием Data Object Model, то в списке функций производимых над данными (правой кнопкой мыши по полю — Параметры поля) добавится еще одна новая — Число различных элементов (Distinct Count). Она вычислит не общее количество непустых элементов, как обычная функция Счет (Count), а количество неповторяющихся представителей.

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

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

Экспресс-просмотр

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

Читайте также:  Как сделать чтобы word 2003 открывал docx?

Логическим продолжением и развитием этой идеи стала новая функция Экспресс-просмотр (Quick Explore) в сводных таблицах Excel 2013.

При выделении в сводной любой ячейки с числовым результатом рядом появляется смарт-тег Экспресс-просмотра. Нажав на него, можно выбрать нужную нам таблицу (если сводная строится по нескольким таблицам) и интересующее нас поле для детализации. Так, например, если я выделю ячейку с результатами продаж менеджера Иванова и выберу поле Заказчик для детализации:

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

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

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

Недостатки

Из замеченного в процессе работы:

  • В сводных таблицах построенных на основе Объектной модели не работает группировка. Это хоть и не критично, но печально. Будем надеяться, что исправят.
  • Иногда названия столбцов в списке полей не соответствуют реальности, т.е. берутся не из ячеек шапки, а непонятно откуда. Помогает предварительное форматирование исходных диапазонов как «умных» таблиц.
  • Для выгрузки исходных данных из Объектной модели приходится использовать команду Данные — Подключения (Data — Connections).

Источник: https://www.planetaexcel.ru/techniques/8/158/

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

Моя знакомая, экономист на предприятии, владеет Экселем, иногда посматривает на форумы, но 5 лет не могла подступиться к самому, на мой взгляд, полезному инструменту Excel – сводным таблицам.

Что же это такое? Сводные таблицы – это результат выборки из правильно построенных данных.

Преимуществ у инструмента много, я описывать не буду, просто скажу, что без нее многим приходилось бы осваивать не только Excel, но и Microsoft Access.

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

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

Пример:

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

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

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

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

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

Значения – сюда ставим только цифровые значения.

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

Теперь на примере моих данных.

Какой вид может принять таблица:

  1. Можно посмотреть сколько каждая позиция была отгруженаСводная таблица в excel как сделать 2013
  2. Сколько было отгружено каждому магазинуСводная таблица в excel как сделать 2013
  3. Сколько было операций отгрузки в разбивке по месяцам по каждому магазину оплачено через банк.

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

Как видите, вариаций много.

По использованию СТ лично мои рекомендации:

Формат функции прост («Значения»; «Сводная таблица»; «Поле1»; «Элемент1»; «Поле2»; «Элемент2» и т.д.)

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

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

Эксель Практик

«Глаза боятся, а руки делают»

P.S.

Источник: http://excelpractic.ru/svodnye-tablicy-excel.html

Ссылка на основную публикацию
Adblock
detector