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

25 ответов на вопрос “Сводная таблица в excel из нескольких таблиц как сделать?”

  1. heart sound Ответить

    Настройка исходных данных

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

    Поля страницы при консолидации данных

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

    Использование именованных диапазонов

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

    Другие способы консолидации данных

    В Excel также доступны другие способы консолидации данных, которые позволяют работать с данными в разных форматах и макетах. Например, вы можете создавать формулы с объемными ссылками или использовать команду Консолидация (доступную на вкладке Данные в группе Работа с данными).

    Консолидация нескольких диапазонов

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

    Консолидация данных без использования полей страницы

    Чтобы объединить данные всех диапазонов и создать консолидированный диапазон без полей страницы, сделайте следующее:
    Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
    Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.
    В списке Выбрать команды из выберите пункт Все команды.
    Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.
    В книге щелкните пустую ячейку, которая не является частью сводной таблицы.
    Щелкните значок мастера на панели быстрого доступа.
    На странице Шаг 1 мастера выберите параметр в нескольких диапазонах консолидации, а затем нажмите кнопку Далее.
    На странице Шаг 2а выберите параметр Создать поля страницы, а затем нажмите кнопку Далее.
    На странице Шаг 2б сделайте следующее:
    Перейдите в книгу и выделите диапазон ячеек, а затем вернитесь в мастер сводных таблиц и диаграмм и нажмите кнопку Добавить.

  2. Miralsa Ответить


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

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

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

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

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

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

    Затем нас попросят выбрать диапазон значений.

    Для этого достаточно выделить всю таблицу целиком.

    Сразу после этого адрес подставится автоматически. Здесь всё очень просто, поскольку рассчитано для чайников. Для завершения построения нажмите на кнопку «OK».

    Редактор Excel предложит нам всего один вариант, поскольку таблица очень простая (для примера больше и не нужно).

    Попробуйте снова нажать на иконку «Вставить временную шкалу» (она расположена на вкладке «Анализ»).

    На этот раз никаких ошибок не будет. Вам предложат выбрать поле для сортировки. Поставьте галочку и нажмите на кнопку «OK».

    Благодаря этому появится окошко, в котором можно будет выбирать нужную дату при помощи бегунка.

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

    Обновить

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

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

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

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

    Действия

    При помощи этого инструмента вы сможете:
    очистить таблицу;
    выделить;
    переместить её.

    Вычисления

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

    К ним относятся:
    вычисляемое поле;

    вычисляемый объект;

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

    вывести формулы (информации нет, так как нет добавленных формул).

    Сервис

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

    Показать

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

    Благодаря этому вы сможете:
    настроить отображение боковой панели со списком полей;

    включить или выключить кнопки «плюс/мину»с;

    настроить отображение заголовков полей.

    Конструктор

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

    Можно настроить:
    промежуточные итоги:
    не показывать;
    показывать все итоги в нижней части;
    показывать все итоги в заголовке.

  3. Талантливые Нюшата Ответить

    Типичная задача при обработке информации полученной из разных источников. Типовое решение – взять и свести все таблицы в одну. Но что делать, когда таблиц много (например, 20), или свести их в одну нет возможности, на листе просто не хватает строк (все таблицы в сумме дают больше 1 100 000 строк)?
    Однако решение существует! И оно не очень сложное.
    Для решения этой задачи нам понадобиться надстройка ЁXCEL.
    Итак, приступим к решению. Мы имеем на входе 3 таблицы, расположенные на разных листах рабочей книги:

    Важно:
    Количество столбцов во всех таблицах должно быть одинаково;
    Кроме таблиц на листах не должно быть никакой информации.
    Начинаем манипуляции. Переходим в главном меню во вкладку ЁXCEL и нажимаем кнопку “Таблицы”, в выпавшем меню выбираем команду “Объединить таблицы”:

    В открывшемся диалоговом окне выделяем листы с таблицами, которые необходимо объединить и нажимаем “ОК”:

    Программа сформирует запрос – объединит таблицы и выведет информационное сообщение:

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

    В открывшемся диалоговом окне выбираем “Подключения в этой книге” – “Запрос из Excel Files” и нажимаем “Открыть”:

    В открывшемся диалоговом окне устанавливаем переключатели в положения “Отчет сводной таблицы” и “Новый лист”, нажимаем “ОК”:

    Программа создаст в книге новый лист на который выведет макет сводной таблицы. Обратите внимание – программа создаст в сформированной таблице новый столбец с названиями листов из которых были получены данные:

    Достройте сводную таблицу до необходимого состояния, добавьте в любую из связанных таблиц новую строчку, обновите сводную таблицу – в ней появятся добавленные данные.
    Чтобы обновление сводной таблицы происходило автоматически вставьте в модуль каждого листа содержащего таблицы следующий код (Как вставлять макросы?):
    Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveWorkbook.RefreshAll
    End Sub
    Важно:
    Если вы переместили файл в другую папку или отправили файл коллеге по электронной почте – необходимо заново связать таблицы (в запросе прописывается абсолютный путь к файлу).
    Чтобы запрос работал не зависимо от того в какой папке лежит файл вставьте в модуль “ЭтаКнига” следующий код:
    Private Sub Workbook_Open()
    Dim q As String
    On Error Resume Next
    q = Application.ThisWorkbook.Path & “\” & Application.ThisWorkbook.Name ‘Определяем текущий путь к файлу
    With ActiveWorkbook.Connections(“Запрос из Excel Files”).ODBCConnection ‘Имя запроса
    .Connection = “ODBC;DSN=Excel Files;DBQ=” & q & _
    “;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;” ‘Меняем строку подключения
    End With
    End Sub
    Данный макрос при открытии книги будет определять текущий путь к файлу и менять путь к файлу в запросе.

  4. ANGEL_DO_SLES Ответить

    Это можно сделать вручную и автоматически.
    Вручную:
    Ставим курсор в любом месте сводной таблице. В результате становится видна вкладка «Работа со сводными таблицами».
    В меню «Данные» жмем на кнопку «Обновить» (или комбинацию клавиш ALT+F5).

    Если нужно обновить все отчеты в книге Excel, выбираем кнопку «Обновить все» (или комбинацию клавиш CTRL+ALT+F5).
    Настройка автоматического обновления при изменении данных:
    На вкладке «Работа со сводными таблицами» (необходимо щелкнуть по отчету) выбираем меню «Параметры».
    Открываем «Дополнительные параметры сводной таблицы». Открывается мастер.
    В разделе «Данные» устанавливаем галочку напротив пункта «Обновить при открытии файла».

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

    Некоторые секреты форматирования

    Когда мы сводим в отчет большой объем данных, для выводов и принятия каких-то решения может понадобиться группировка. Допустим, нам нужно увидеть итоги за месяц или квартал.
    Группировка по дате в сводной таблице Excel:
    Источник информации – отчет с данными.
    Так как нам нужна группировка по дате, выделяем любую ячейку с соответствующим значением. Щелкаем правой кнопкой мыши.

    Из выпавшего меню выбираем «Группировку». Откроется инструмент вида:

    В полях «Начиная с» и «По» Excel автоматически проставил начальную и конечную даты диапазона данных. Определяемся с шагом группировки. Для нашего примера – либо месяцы, либо кварталы. Остановимся на месяцах.

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

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

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

    Работа с итогами

    У нас есть сводный отчет такого вида:

    Видны итоги по месяцам (сделано «Группировкой») и по наименованиям товаров. Сделаем отчет более удобным для изучения.
    Как в сводной таблице сделать итоги сверху:
    «Работа со сводными таблицами» – «Конструктор».
    На вкладке «Макет» нажимаем «Промежуточные итоги». Выбираем «Показывать все промежуточные итоги в заголовке группы».

    Получается следующий вид отчета:

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

    Получим отчет без дополнительных сумм:

    Детализация информации

    Огромные сводные таблицы, которые составляются на основании «чужих» таблиц, периодически нуждаются в детализации. Мы не знаем, откуда взялась сумма в конкретной ячейке Excel. Но можно это выяснить, если разбить сводную таблицу на несколько листов.
    В марте продано двуспальных кроватей на сумму 23 780 у.е. Откуда взялась эта цифра. Выделяем ячейку с данной суммой и щелкаем правой кнопкой мыши и выбираем опцию:

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

    Мы можем переместить всю сводную таблицу на новый лист, выбрав на вкладке «Действия» кнопку «Переместить».

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

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

    Выбираем из выпадающего меню название поля. В нашем примере – это название товара или дата. Мы остановимся на названии.

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

    Жмем ОК – сводная таблица меняется.

  5. FAVIJAMO Ответить

    Начнем с требований к исходной таблице.
    каждый столбец должен иметь заголовок;
    в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата; столбец «Поставщик» – названия компаний только в текстовом формате или можно вводить Код поставщика в числовом формате);
    в таблице должны отсутствовать полностью незаполненные строки и столбцы;
    в ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, нельзя в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе Сводная таблица будет работать неэффективно (в случае, если Вам нужна информация, например, в разрезе города);
    избегайте таблиц с «неправильной» структурой (см. рисунок ниже).

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

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

    В таблице имеются столбцы:
    Товар – наименование партии товара, например, «Апельсины»;
    Группа – группа товара, например, «Апельсины» входят в группу «Фрукты»;
    Поставщик – компания-поставщик Товаров, Поставщик может поставлять несколько Групп Товаров;
    Дата поставки – Дата поставки Товара Поставщиком;
    Регион продажи – Регион, в котором была реализована партия Товара;
    Продажи – Стоимость, по которой удалось реализовать партию Товара;
    Сбыт – срок фактической реализации Товара в Регионе (в днях);
    Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.
    Через Диспетчер имен (Формулы/ Определенные имена/ Диспетчер имен) откорректируем Имя таблицы на «Исходная_таблица».

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

    Сводную таблицу будем создавать для решения следующей задачи: «Подсчитать суммарные объемы продаж по каждому Товару».
    Имея исходную таблицу в формате EXCEL 2007, для создания Сводной таблицы достаточно выделить любую ячейку исходной таблицы и в меню Работа с таблицами/ Конструктор/ Сервис выбрать пункт Сводная таблица.

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

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

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

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

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

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

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

    Если возникли вопросы о том, какие же данные из исходной таблицы были использованы для подсчета тех или иных значений Сводной таблицы, то достаточно двойного клика мышкой на конкретном значении в Сводной таблице, чтобы был создан отдельный лист с отобранными из исходной таблицей строками. Например, посмотрим какие записи были использованы для суммирования продаж Товара «Апельсины». Для этого дважды кликнем на значении 646720. Будет создан отдельный лист только со строками исходной таблицы относящихся к Товару «Апельсины».

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

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

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

    Удалить Сводную таблицу можно несколькими способами. Первый – просто удалить лист со Сводной таблицей (если на нем нет других полезных данных, например исходной таблицы). Второй способ – удалить только саму Сводную таблицу: выделите любую ячейку Сводной таблицы, нажмите CTRL+A (будет выделена вся Сводная таблица), нажмите клавишу Delete.

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

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

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

  6. батя не бей Ответить

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

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

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

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

    Приведение сводной таблицы к сжатой, структурной или табличной форме

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

    Изменение способа отображения подписей элементов в форме макета

    В сводной таблице выберите поле строки.
    На ленте откроется вкладка Работа со сводными таблицами.
    Также в форме структуры или табличной форме можно дважды щелкнуть поле строки и перейти к действию 3.
    На вкладке Анализ или Параметры в группе Активное поле нажмите кнопку Параметры поля.

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

    Изменение расположения полей в сводной таблице

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

    Добавление полей в сводную таблицу

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

    Копирование полей в сводной таблице

    В сводной таблице, основанной на данных с листа Excel или внешних данных из источника, не относящегося к OLAP, можно добавить в область Значения несколько копий одного поля, чтобы отображать разные вычисления с помощью возможности Дополнительные вычисления. Например, можно сравнить вычисления (такие как коэффициенты валовой и чистой прибыли, минимальные и максимальные показатели продаж или количество клиентов и процент от их общего числа) расположив их рядом. Дополнительные сведения см. в статье Отображение различных вычислений в полях значений сводной таблицы.
    Щелкните имя поля в разделе полей и, удерживая нажатой кнопку мыши, перетащите его в область Значения в разделе макета.
    Повторите действие 1 столько раз, сколько нужно копий поля.
    В каждом скопированном поле измените нужным образом функцию сведения или настраиваемое вычисление.

  7. Anararim Ответить

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Недостатки

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

  8. Dakus Ответить

    На разных листах одной книги находятся отчеты по продажам в нескольких магазинах:

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

    Переходим на новый лист. Вызываем «Мастера»:
    Первый шаг. Указываем, что будем создавать «сводную диаграмму со сводной таблицей» на основе данных, которые находятся «в нескольких диапазонах консолидации».

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

    Третий шаг. Формируем список диапазонов, на основании которых будет построена сводная диаграмма. Сначала переходим на лист с первой таблицей – выделяем ее – жмем добавить. Переходим на лист со второй таблицей – повторяем все те же процедуры. Указываем количество полей – 1. Становится активным окошко «Первое поле». Даем ему название – «2013». Повторяем эту же операцию для каждого диапазона – названия «2014», «2015».

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

    После нажатия кнопки «Готово» получаем сводную диаграмму и таблицу следующего вида:

    Пока на этом остановимся и рассмотрим другой метод создания сводной диаграммы.
    ?

  9. NiggaBoss Ответить

    Способ 1. С помощью формул 

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

    Необходимо объединить их все в одну общую таблицу, просуммировав совпадающие значения по кварталам и наименованиям.
    Самый простой способ решения задачи “в лоб” – ввести в ячейку чистого листа формулу вида
    =’2001 год’!B3+’2002 год’!B3+’2003 год’!B3
    которая просуммирует содержимое ячеек B2 с каждого из указанных листов, и затем скопировать ее на остальные ячейки вниз и вправо.
    Если листов очень много, то проще будет разложить их все подряд и использовать немного другую формулу:
    =СУММ(‘2001 год:2003 год’!B3)
    Фактически – это суммирование всех ячеек B3 на листах с 2001 по 2003, т.е. количество листов, по сути, может быть любым. Также в будущем возможно поместить между стартовым и финальным листами дополнительные листы с данными, которые также станут автоматически учитываться при суммировании.

    Способ 2. Если таблицы неодинаковые или в разных файлах

    Если исходные таблицы не абсолютно идентичны, т.е. имеют разное количество строк, столбцов или повторяющиеся данные или находятся в разных файлах, то суммирование при помощи обычных формул придется делать для каждой ячейки персонально, что ужасно трудоемко. Лучше воспользоваться принципиально другим инструментом.
    Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:

    Хорошо заметно, что таблицы не одинаковы – у них различные размеры и смысловая начинка. Тем не менее их можно собрать в единый отчет меньше, чем за минуту. Единственным условием успешного объединения (консолидации) таблиц в подобном случае является совпадение заголовков столбцов и строк. Именно по первой строке и левому столбцу каждой таблицы Excel будет искать совпадения и суммировать наши данные.
    Для того, чтобы выполнить такую консолидацию:
    Заранее откройте исходные файлы
    Создайте новую пустую книгу (Ctrl + N)
    Установите в нее активную ячейку и выберите на вкладке (в меню) Данные – Консолидация (Data – Consolidate). Откроется соответствующее окно:

    Установите курсор в строку Ссылка (Reference) и, переключившись в файл Иван.xlsx, выделите таблицу с данными (вместе с шапкой). Затем нажмите кнопку Добавить (Add) в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов.
    Повторите эти же действия для файлов Риты и Федора. В итоге в списке должны оказаться все три диапазона:

    Обратите внимание, что в данном случае Excel запоминает, фактически, положение файла на диске, прописывая для каждого из них полный путь (диск-папка-файл-лист-адреса ячеек). Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен (Use labels). Флаг Создавать связи с исходными данными (Create links to source data) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.
    После нажатия на ОК видим результат нашей работы:

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

    Ссылки по теме

    Макрос для автоматической сборки данных с разных листов в одну таблицу
    Макрос для сборки листов из нескольких файлов

  10. VideoAnswer Ответить

Добавить ответ

Ваш e-mail не будет опубликован. Обязательные поля помечены *