Как в excel сделать сводную таблицу из нескольких файлов

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

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

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

Чтобы объединить несколько таблиц в списке полей сводной таблицы:

Можно импортировать их из реляционной базы данных, например, Microsoft SQL Server, Oracle или Microsoft Access. Вы можете импортировать несколько таблиц одновременно.

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

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

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

Щелкните Данные > Получение внешних данных > Из других источников > С сервера SQL Server.

В поле Имя сервера введите сетевое имя компьютера с запущенным сервером SQL Server.

В разделе Учетные данные входа в систему выберите команду Использовать проверку подлинности Windows, если вы подключаетесь с помощью своих учетных данных. В противном случае введите имя пользователя и пароль, предоставленные администратором базы данных.

Нажмите клавишу ВВОД и в разделе Выбор базы данных и таблицы выберите нужную базу данных, а затем щелкните Разрешить выбор нескольких таблиц.

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

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

В диалоговом окне Импорт данных выберите элемент Отчет сводной таблицы.

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

Обратите внимание: список полей содержит несколько таблиц. Это все таблицы, выбранные вами во время импорта. Каждую таблицу можно развернуть и свернуть для просмотра ее полей. Так как таблицы связаны, вы можете создать сводную таблицу, перетянув поля из любой таблицы в область ЗНАЧЕНИЯ, СТРОКИ или СТОЛБЦЫ.

Перетащите числовые поля в область ЗНАЧЕНИЯ. Например, если используется образец базы данных Adventure Works, вы можете перетащить поле “ОбъемПродаж” из таблицы “ФактПродажиЧерезИнтернет”.

Перетащите поля даты или территории в область СТРОКИ или СТОЛБЦЫ, чтобы проанализировать объем продаж по дате или территории сбыта.

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

Работа с другими типами баз данных

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

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

Импорт таблиц из других источников

Помимо SQL Server, вы можете импортировать таблицы из ряда других реляционных баз данных.

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

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

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

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

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

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

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

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

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

Источник: support.office.com

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

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

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

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

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

Шаг первый.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На существующий лист

  • Нажать кнопку «Готово».
  • Второй способ.

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

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

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

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

    Шаг первый.

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

    Шаг два.

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

    Power Query из таблицы

    Шаг три.

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

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

    Шаг четыре.

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

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

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

    Источник: ruexcel.ru

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

    Постановка задачи

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

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

    • Таблицы могут иметь любое количество строк с любыми данными, но обязательно – одинаковую шапку.
    • На листах с исходными таблицами не должно быть лишних данных. Один лист – одна таблица. Для контроля советую использовать сочетание клавиш Ctrl + End , которое перемещает вас на последнюю использованную ячейку листа. В идеале – это должна быть последняя ячейка таблицы с данными. Если при нажатии на Ctrl + End выделяется какая-либо пустая ячейка правее или ниже таблицы – удалите после таблицы эти пустые столбцы справа или строки снизу и сохраните файл.

    Способ 1. Сборка таблиц для сводной с помощью Power Query

    Начиная с 2010 версии для Excel существует бесплатная надстройка Power Query, которая умеет собирать и трансформировать любые данные и отдавать их потом как источник для построения сводной таблицы. Решить нашу задачу с помощью этой надстройки совсем несложно.

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

    Затем на вкладке Данные (если у вас Excel 2016 или новее) или на вкладке Power Query (если у вас Excel 2010-2013) выберем команду Создать запрос – Из файла – Excel (Get Data – From file – Excel) и укажем исходный файл с таблицами, которые надо собрать:

    В появившемся окне выберем любой лист (не принципиально какой именно) и внизу жмем кнопку Изменить (Edit) :

    Поверх Excel должно открыться окно редактора запросов Power Query. В правой части окна на панели Параметры запроса удалим все автоматически созданные шаги кроме первого – Источник (Source) :

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

    Удалим все столбцы, кроме колонки Data, щелкнув по заголовку столбца правой кнопкой мыши и выбрав команду Удалить другие столбцы (Remove other columns) :

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

    Если вы всё сделали правильно, то на этом моменте должны увидеть содержимое всех таблиц, собранных друг под другом:

    Осталось поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home) и удалить попавшие в данные повторяющиеся шапки таблиц с помощью фильтра:

    Сохраним всё проделанное с помощью команды Закрыть и загрузить – Закрыть и загрузить в. (Close & Load – Close & Load to. ) на вкладке Главная (Home) , а в открывшемся окне выберем опцию Только подключение (Connection Only) :

    Всё. Осталось только построить сводную. Для этого идём на вкладку Вставка – Сводная таблица (Insert – Pivot Table) , выбирыем опцию Использовать внешний источник данных (Use external data source) , а затем, нажав кнопку Выбрать подключение, наш запрос. Дальнейшее создание и настройка сводной происходит совершенно стандартным образом путем перетаскивания нужных нам полей в области строк, столбцов и значений:

    Если в будущем изменятся исходные данные или добавится еще несколько листов-магазинов, то достаточно будет обновить запрос и нашу сводную с помощью команды Обновить все на вкладке Данные (Data – Refresh All) .

    Способ 2. Объединяем таблицы SQL-командой UNION в макросе

    Еще одно решение нашей задачи представлено вот таким макросом, который создает набор данных (cache) для сводной таблицы, используя команду UNION языка запросов SQL. Эта команда объединяет таблицы со всех указанных в массиве SheetNames листов книги в единую таблицу данных. То есть вместо физического копирования-вставки диапазонов с разных листов на один мы делаем то же самое в оперативной памяти компьютера. Потом макрос добавляет новый лист с заданным именем (переменная ResultSheetName) и создает на нем полноценную(!) сводную на основе собранного кэша.

    Чтобы воспользоваться макросом используйте кнопку Visual Basic на вкладке Разработчик (Developer) или сочетание клавиш Alt + F11 . Затем вставляем новый пустой модуль через меню Insert – Module и копируем туда следующий код:

    Готовый макрос потом можно запустить сочетанием клавиш Alt + F8 или кнопкой Макросы на вкладке Разработчик (Developer – Macros) .

    Минусы такого подхода:

    • Данные не обновляются, т.к. кэш не имеет связи с исходными таблицами. При изменении исходных данных надо запустить макрос еще раз и построить сводную заново.
    • При изменении количества листов необходимо правки в код макроса (массив SheetNames).

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

    Техническое замечание: если при запуске макроса вы получаете сообщение об ошибке вида “Provider not registered”, то скорее всего у вас 64-битная версия Excel или установлена не полная версия Office (нет Access). Чтобы исправить ситуацию замените в коде макроса фрагмент:

    И скачайте и установите бесплатный движок обработки данных из Access с сайта Microsoft – Microsoft Access Database Engine 2010 Redistributable

    Способ 3. Мастер консолидации сводных таблиц из старых версий Excel

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

    В такой сводной нет заголовков столбцов в списке полей, нет гибкой настройки структуры, ограничен набор используемых функций и, в общем и целом, все это слабо похоже на сводную таблицу. Возможно именно поэтому начиная с 2007 года Microsoft эту функцию убрали из стандартного диалога при создании отчетов сводных таблиц. Теперь эта возможность доступна только через настраиваемую кнопку Мастер сводных таблиц (Pivot Table Wizard) , которую при желании можно добавить на панель быстрого доступа через Файл – Параметры – Настройка панели быстрого доступа – Все команды (File – Options – Customize Quick Access Toolbar – All Commands) :

    После нажатия на добавленную кнопку нужно выбрать на первом шаге мастера соответствующую опцию:

    А затем в следующем окне выделить по очереди каждый диапазон и добавить его в общий список:

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

    Источник: www.planetaexcel.ru

    Как в excel сделать сводную таблицу из нескольких файлов

    Типичная задача при обработке информации полученной из разных источников. Типовое решение – взять и свести все таблицы в одну. Но что делать, когда таблиц много (например, 20), или свести их в одну нет возможности, на листе просто не хватает строк (все таблицы в сумме дают больше 1 100 000 строк)?

    Однако решение существует! И оно не очень сложное.

    Для решения этой задачи нам понадобиться надстройка ЁXCEL .

    Итак, приступим к решению. Мы имеем на входе 3 таблицы, расположенные на разных листах рабочей книги:

    • Количество столбцов во всех таблицах должно быть одинаково;
    • Кроме таблиц на листах не должно быть никакой информации.

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

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

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

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

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

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

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

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

    Чтобы обновление сводной таблицы происходило автоматически вставьте в модуль каждого листа содержащего таблицы следующий код ( Как вставлять макросы? ):

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

    Чтобы запрос работал не зависимо от того в какой папке лежит файл вставьте в модуль “ЭтаКнига” следующий код:

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

    Файлы для скачивания:

    Файл Описание Размер файла: Скачивания
    Пример 27 Кб 1145

    Чтобы оценить всю прелесть – выньте файл из архива и при загрузке файла включите макросы.

    Возможные ошибки при использовании этого метода:

    • В таблицах одинаковое количество столбцов, но при попытке использовать запрос получаем сообщение: “В таблицах или запросах, выбранных в запросе на объединение, не совпадает число столбцов.” Причина: в одном или нескольких листах с таблицами есть пустые столбцы (в них раньше были данные) и MS Excel считает их столбцами таблиц. Решение: Выделить и удалить все пустые столбцы на листах с таблицами.
    • Все столбцы удалили, но ошибка осталась. Причина: файл сохранен на сетевом диске или открыт из почтовой программы. Решение: сохраните файл у себя на локальном компьютере, выполните запрос, вставьте в него макрос меняющий путь в запросе и выложите обратно в сеть (если нужно).
    • В полученном запросе в некоторых ячейках пропадают данные. Причина: в ваших таблицах встречаются столбцы, которые одновременно содержатся и числовые и текстовые значения. MS Excel считает, что эти столбцы должны содержать только числа и не выводит текст. Решение:преобразуйте все числовые значения в этих столбцах во всех таблицах в текстовые. Обновите запрос – текстовые данные появятся.

    Источник: e-xcel.ru

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

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

    Исходный материал – таблица с несколькими десятками и сотнями строк, несколько таблиц в одной книге, несколько файлов. Напомним порядок создания: «Вставка» – «Таблицы» – «Сводная таблица».

    А в данной статье мы рассмотрим, как работать со сводными таблицами в Excel.

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

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

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

    Мы просто создаем сводный отчет на основе данных в нескольких диапазонах консолидации.

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

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

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

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

    1. В ячейке-мишени (там, куда будет переноситься таблица) ставим курсор. Пишем = – переходим на лист с переносимыми данными – выделяем первую ячейку столбца, который копируем. Ввод. «Размножаем» формулу, протягивая вниз за правый нижний угол ячейки.
    2. По такому же принципу переносим другие данные. В результате из двух таблиц получаем одну общую.
    3. Теперь создадим сводный отчет. Вставка – сводная таблица – указываем диапазон и место – ОК.

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

    Покажем, к примеру, количество проданного товара.

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

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

    Из отчета (см.выше) мы видим, что продано ВСЕГО 30 видеокарт. Чтобы узнать, какие данные были использованы для получения этого значения, щелкаем два раза мышкой по цифре «30». Получаем детальный отчет:

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

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

    Курсор должен стоять в любой ячейке сводного отчета.

    Правая кнопка мыши – обновить.

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

    1. Курсор стоит в любом месте отчета. Работа со сводными таблицами – Параметры – Сводная таблица.
    2. Параметры.
    3. В открывшемся диалоге – Данные – Обновить при открытии файла – ОК.

    Изменение структуры отчета

    Добавим в сводную таблицу новые поля:

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

    Если бы мы добавили столбцы внутри исходной таблицы, достаточно было обновить сводную таблицу.

    После изменения диапазона в сводке появилось поле «Продажи».

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

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

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

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

    1. Определяемся, какие функции будет выполнять виртуальный столбец. На какие данные сводной таблицы вычисляемое поле должно ссылаться. Допустим, нам нужны остатки по группам товаров.
    2. Работа со сводными таблицами – Параметры – Формулы – Вычисляемое поле.
    3. В открывшемся меню вводим название поля. Ставим курсор в строку «Формула». Инструмент «Вычисляемое поле» не реагирует на диапазоны. Поэтому выделять ячейки в сводной таблице не имеет смысла. Из предполагаемого списка выбираем категории, которые нужны в расчете. Выбрали – «Добавить поле». Дописываем формулу нужными арифметическими действиями.
    4. Жмем ОК. Появились Остатки.

    Группировка данных в сводном отчете

    Для примера посчитаем расходы на товар в разные годы. Сколько было затрачено средств в 2012, 2013, 2014 и 2015. Группировка по дате в сводной таблице Excel выполняется следующим образом. Для примера сделаем простую сводную по дате поставки и сумме.

    Щелкаем правой кнопкой мыши по любой дате. Выбираем команду «Группировать».

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

    Получаем суммы заказов по годам.

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

    Источник: exceltable.com

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

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

    Если вы столкнулись с необходимостью создать сводную таблицу на основе данных, размещенных на нескольких листах одной книги (или разных книг), вас ждет разочарование. Стандартным образом Excel делает это «через одно место»… L

    Да в Excel есть такая опция в мастере сводных таблиц, но привычно (как и для обычных сводных) она работает только для одного набора данных, например (см. файл Сводная_листы_один набор. xlsx ):

    У вас есть список клиентов и объем продаж по ним по кварталам; данные за один квартал расположены на отдельном листе. Создадим сводную таблицу, консолидирующую данные за 4 квартала.

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

    2. Запустите мастер сводных таблиц (как вывести мастер на панель быстрого доступа см. здесь); выберите опцию «в нескольких диапазонах консолидации», нажмите «далее»:

    3. Оставьте предлагаемую по умолчанию опцию «Создать одно поле страницы»

    4. Введите имя первого диапазона, нажмите «Добавить»:

    5. Добавьте все четыре диапазона, нажмите «Далее»:

    6. Оставьте предлагаемую по умолчанию опцию «новый лист», нажмите «Готово»:

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

    Фильтр отчета вместо названий листов (кв1, кв2…) содержит номера объектов:

    Названия строк вместо «Клиент» – «Строка»; название столбца «Продажи» упрятано внутрь названия «Столбец»:

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

    Можете поэкспериментировать – ничего не изменится, если исходные данные (листы) разместить в нескольких файлах.

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

    1. Поименуйте ваши исходные массивы данных.

    2. Создайте сводную таблицу, как описано выше

    Вот, что получилось:

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

    Решение предложил Кирилл Лапин (см. здесь)

    Скачать файл в формате zip (внутри два файла Excel 2007 с поддержкой макросов: от Кирилла Лапина и с моим набором данных) сводные. zip

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

    2) Если пытаться создать сводную таблицу с подсоединением файла к самому себе как будто к внешним данным стандартным способом, то Excel не позволяет этого сделать ни вручную ни макросом

    3) Можно обмануть Excel если файл переименовывается или переносится в другую папку

    1) создаем файл дублер с базой данных Book1.xls в той же папке, что и главный файл

    2) в главном файле создаем сводную таблицу с подсоединением к внешним данным Book1.xls

    3) меняем путь и название файла в connection string на главный

    Вот, что получилось у меня, после применения кода Кирилла:

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

    Источник: pandia.ru