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

Формирование сводной таблицы из нескольких файлов (таблиц) Excel

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

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

Запускаем мастер. Допустим, нам нужно объединить данные из двух листов, майского и за июнь. Главное условие – они должны совпадать по структуре. Если это так, то в первом окне мастера выбираем опции «Сводная таблица» и следующий параметр – «Несколько диапазонов консолидации».

На втором шаге указываем Мастеру, что нам нужно несколько полей. На третьем – выбираем диапазон ячеек для суммирования по первому листу, жмём «Добавить», и проделываем то же самое для второго листа Excel. Теперь нужно дать имена двум полям. Делаем это сначала для первого поля (жмём селектор «1», в графу «1 поле» вписываем «Май»), затем, по аналогии, для второго. При этом нужно будет указать в списке диапазонов соответствующую строку, первую или вторую.

После нажатия кнопки «Готово» откроется новое окно, в котором указываем «Новый лист» и снова жмём кнопку «Готово».

ВНИМАНИЕ. Получение многомерных таблиц неудобно тем, что в ней сложно ориентироваться при большом количестве полей, да и нужные расчёты приходится проводить, каждый раз указывая нужные значения из верхней области таблицы.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сортировку можно отсортировать по пользовательскому порядку с помощью настраиваемого списка.

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

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

Порядок сортировки следующий:

Перейдите на любую ячейку исходного диапазона A1:C10 и выберите инструмент: «ДАННЫЕ»-«Сортировка».
В появившемся диалоговом окне в первой секции «Столбец» из первого выпадающего списка «Сортировать по:» выбираем значение «Месяц», во втором выпадающем списке без изменений «Значение».

Как сделать Автосортировку в Excel?

Как осуществить сортировку по строкам в Excel:

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

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

Сортировка в столбцах сводной таблицы

Выделить любую ячейку в поле (столбце) по которому вы хотите выполнить сортировку.
Далее – нажимаем на кнопку «Сортировка» в группе «Работа со сводными таблицами» на вкладке «Параметры». …
В открывшемся окне — настраиваем параметры сортировки. …
Параметры сортировки – «По убыванию» и направление – «Сверху вниз».

Как можно провести группировку данных в сводной таблице?

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

Как разбить строки в сводной таблице?

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

Чтобы просмотреть сведения о текущем элементе, щелкните пункт Развернуть.
Чтобы скрыть сведения о текущем элементе, щелкните пункт Свернуть.
Чтобы скрыть сведения обо всех элементах в поле, щелкните пункт Свернуть все поле.

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

Для реализации нужно сделать следующие шаги:

Выберите ячейку и перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
Затем выберите третий вариант из «Применить правило к» и выберите «Форматировать все ячейки на основании их значений» из типа правила.

Как в Ексель сортировать по дате?

Сортировка по дате

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

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

Сортировка по цвету ячейки, цвету шрифта или значку

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

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

Добавление вычисляемого поля

Щелкните сводную таблицу. …
На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемое поле.
В поле Имя введите имя для поля.
В поле Формула введите формулу для поля. …
Нажмите кнопку Добавить.

Какие имеются возможности Excel для анализа экономической информации?

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

Как в Excel сделать промежуточные итоги?

Выбираем на ленте вкладку «Данные». Группа «Структура» — команда «Промежуточные итоги». Заполняем диалоговое окно «Промежуточные итоги». В поле «При каждом изменении в» выбираем условие для отбора данных (в примере – «Значение»).

Узнайте, как создать сводную таблицу из нескольких листов

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

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

Что такое сводная таблица в Excel?

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

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

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

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

В этом примере мы рассматриваем набор данных супермаркета. Эти данные разделены на четыре разновидности в зависимости от географических регионов: Центральный, Восточный, Западный и Южный. Те же данные будут показаны на четырех разных и отдельных листах ниже.

Центральный

Восток

Запад

Юг

Теперь воспользуемся сочетанием клавиш ALT + D. Это активирует клавиши доступа к офису, как показано ниже.

Теперь попробуйте нажать букву ‘P’ на клавиатуре. Это активирует мастер сводных таблиц, как показано ниже.

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

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

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

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

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

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

Измените его на SUM, и теперь вы сможете увидеть точные данные о продажах в зависимости от региона, как показано ниже.

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

На этом мы подошли к завершению этой статьи о создании сводной таблицы из нескольких листов в Excel.

Следующие шаги

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

Заинтересованы в бизнес-аналитике и поиске подходящего наставника? Бизнес-аналитика Simplilearn поможет вам. Этот сертификационный курс по бизнес-аналитике, предлагаемый Simplilearn, научит вас основным понятиям анализа данных и статистики, чтобы помочь в принятии решений на основе данных. Это обучение знакомит вас с Power BI и углубляется в статистические концепции, которые помогут вам извлекать ценные сведения из доступных данных для представления результатов с помощью панелей мониторинга на уровне руководителей.

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

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

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

Итоговый консолидированный отчет сводной таблицы может содержать следующие поля в области Список полей сводной таблицы, добавляемой в сводную таблицу: «Строка», «Столбец» и «Значение». Кроме того, в отчет можно включить до четырех полей фильтра, которые называются «Страница1», «Страница2», «Страница3» и «Страница4».

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

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

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

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

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

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

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

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

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

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

Щелкните любую ячейку на листе.

Выберите Вставка > Сводная таблица.

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

Выберите вариант Выбрать подключение.

На вкладке Таблицы в разделе Модель данных этой книги выберите Таблицы в модели данных книги.

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

Общие и промежуточные итоги

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

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

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

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

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

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

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

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

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