Каталог расширений

Популярные теги

3gp       avi       fb2       jpg       mp3       pdf      

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


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

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

​Смотрите также​​Katerina2130​. Флаг​ поместить между стартовым​ нуждаются в детализации.​​ сводными таблицами» (необходимо​ размер таблицы станет​​ c File2 по​​ вложении. Заранее спасибо​ В нем могут​
​ В результате из​ укажет пользователь в​ этого сразу нужно​ в таблице должны​ вкладку «Вставка». Перейдя,​
​ "Создать таблицу Excel​
​ нашем примере, поставим​Чтобы​: Во вложении очень​
​Создавать связи с исходными​ и финальным листами​ Мы не знаем,​ щелкнуть по отчету)​ ужасающим. Проанализировать данные​ File20.​
​Serge_007​ отображаться средние значения,​ двух таблиц получаем​ настройках.​
​ вывести данный инструмент​ остаться данные только​ жмем на самую​
​ из списка".​ название таблицы «Магазин​​объединить таблицы в Excel,​ упрощенный вариант, в​ данными​
​ дополнительные листы с​ откуда взялась сумма​ выбираем меню «Параметры».​ в сотне строк​3. В сводном​:​ проценты, расхождения. То​ одну общую.​Автор: Максим Тютюшев​ на Панель быстрого​
​ за третий квартал.​ первую кнопку в​Сводные таблицы Excel предоставляют​ 1».​ расположенные на разных​ действительно это очень​(Create links to source​ данными, которые также​ в конкретной ячейке​Открываем «Дополнительные параметры сводной​
​ будет очень сложно.​
​ файле заводите лист​=СУММ(Путь[Название_книги1]Название_Листа!$C$9:$C$21)+​ есть результаты различных​Теперь создадим сводный отчет.​Пользователи создают сводные таблицы​ доступа.Переходим в пункт​
​ Пока же отображаются​ ленте, которая так​ возможность пользователям в​Затем выделяем из​ листахили в других​ большие таблицы, в​
​ data)​ станут автоматически учитываться​ Excel. Но можно​ таблицы». Открывается мастер.​ А на составление​ Svod. Этот лист​СУММ(Путь[Название_книги2]Название_Листа!$C$9:$C$21)+...+​ формул. Данные вычисляемого​
​ Вставка – сводная​ для анализа, суммирования​ меню «Файл», и​ данные за весь​ и называется «Сводная​ одном месте группировать​ списка диапазонов второй​ книгах Excel​
​ которых собрано всё.​позволит в будущем​ при суммировании.​ это выяснить, если​В разделе «Данные» устанавливаем​ отчета уйдет не​ не должен разорвать​СУММ(Путь[Название_книги20]Название_Листа!$C$9:$C$21)​ поля взаимодействуют с​ таблица – указываем​ и представления большого​ жмем на кнопку​
​ период. Для того,​ таблица». После этого,​ значительные объемы информации,​ диапазон, и в​, составить общую таблицу,​ В книге 3​ (при изменении данных​Если исходные таблицы не​ разбить сводную таблицу​
​ галочку напротив пункта​ один день. В​ монотонную последовательность листов​DJ-MiLeS​ данными сводной таблицы.​ диапазон и место​ объема данных. Такой​ «Параметры».​ что бы привести​ открывается меню, в​ содержащейся в громоздких​ этом же первом​ нужно сделать​ стоит сортировка по​
​ в исходных файлах)​ абсолютно идентичны, т.е.​ на несколько листов.​ «Обновить при открытии​ такой ситуации сводная​ File1..File20. Поэтому располагаете​: я так и​Инструкция по добавлению пользовательского​ – ОК.​ инструмент Excel позволяет​В открывшемся окне параметров,​
​ таблицу к нужному​ котором следует выбрать,​ таблицах, а также​ окне поля пишем​
​сводные таблицы Excel​ объекту "Москва" и​ производить пересчет консолидированного​ имеют разное количество​В марте продано двуспальных​ файла».​ таблица просто необходима.​
​ его либо до,​ делал, но:​ поля:​Открывается заготовка Сводного отчета​ произвести фильтрацию и​ переходим в раздел​ нам виду, кликаем​ что мы собираемся​ составлять комплексные отчеты.​​ название диапазона. Мы​. Делается это с​ по "хд". Вот​ отчета автоматически.​
​ строк, столбцов или​ кроватей на сумму​Теперь каждый раз при​Создам отчет с помощью​​ либо после последовательности​1) формула получается​
​Определяемся, какие функции будет​ со Списком полей,​ группировку информации, изобразить​ «Панель быстрого доступа».​ на кнопку около​ создавать, таблицу или​ При этом, значения​ напишем – «Магазин​ помощью специальной функции.​
​ отчет нужно составить​После нажатия на​ повторяющиеся данные или​ 23 780 у.е.​ открытии файла с​ мастера сводных таблиц.​ листов File1..File20. В​
​ слишком длинной (не​ выполнять виртуальный столбец.​ которые можно отобразить.​ ее в различных​ Выбираем команды из​ фильтра «Дата». В​ диаграмму. Жмем на​ сводных

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

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

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

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

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

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

Шаг первый.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Шаг первый.

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

    Шаг два.

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

    Power Query из таблицы

    Шаг три.

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

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

    Шаг четыре.

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

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

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

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

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

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

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

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

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

    Самое рациональное решение – это создание сводной таблицы в Excel:

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

    Просто, быстро и качественно.

    • Первая строка заданного для сведения данных диапазона должна быть заполнена.
    • В базовой табличке каждый столбец должен иметь свой заголовок – проще настроить сводный отчет.
    • В Excel в качестве источника информации можно использовать таблицы Access, SQL Server и др.

    

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

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

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

    Создадим отчет с помощью мастера сводных таблиц:

    1. Вызываем меню «Мастер сводных таблиц и диаграмм». Для этого щелкаем кнопку настройки панели быстрого доступа и нажимаем «Другие команды». Здесь на вкладке «Настройка» находим «Мастер сводных таблиц». Добавляем инструмент в панель быстрого доступа. После добавления:
    2. Ставим курсор на первую табличку и нажимаем инструмент «Мастера». В открывшемся окне отмечаем, что создать таблицу хотим в «нескольких диапазонах консолидации». То есть нам нужно объединить несколько мест с информацией. Вид отчета – «сводная таблица». «Далее».
    3. Следующий этап – «создать поля». «Далее».
    4. Прописываем диапазон данных, по которым будем формировать сводный отчет. Выделяем первый диапазон вместе с шапкой – «добавить». Второй диапазон вместе с названием столбцов – снова «добавить».
    5. Теперь в списке выбираем первый диапазон. Ставим птичку у единицы. Это первое поле сводного отчета. Даем ему имя – «Магазин 1». Выделяем второй диапазон данных – ставим птичку у цифры «2». Название поля – «Магазин 2». Нажимаем «Далее».
    6. Выбираем, где расположить сводную таблицу. На существующем листе или новом. Лучше выбрать новый лист, чтобы не было наложений и смещений. У нас получилось так:

    Как видите всего в несколько кликов можно создавать сложные отчеты из нескольких листов или таблиц разного объема информации.

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

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

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

    Добавим в сводную таблицу еще одно поле для отчета. Для этого установим галочку напротив «Даты» (или напротив «Товара»). Отчет сразу меняется – появляется динамика продаж по дням в каждом отделе.

    Сгруппируем данные в отчете по месяцам. Для этого щелкаем правой кнопкой мыши по полю «Дата». Нажимаем «Группировать». Выбираем «по месяцам». Получается сводная таблица такого вида:

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

    А вот что получится, если мы уберем «дату» и добавим «отдел»:

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

    Чтобы название строки сделать названием столбца, выбираем это название, щелкаем по всплывающему меню. Нажимаем «переместить в название столбцов». Таким способом мы переместили дату в столбцы.

    Поле «Отдел» мы проставили перед наименованиями товаров. Воспользовавшись разделом меню «переместить в начало».

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

    В открывшемся меню выбираем поле с данными, которые необходимо показать.

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

    Проверка правильности выставленных коммунальных счетов

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

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

    Для примера мы сделали сводную табличку тарифов для Москвы:

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

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

    = тариф * количество человек / показания счетчика / площадь

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

    Наши формулы ссылаются на лист, где расположена сводная таблица с тарифами.

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

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

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

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

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

    Можно импортировать их из реляционной базы данных, например, 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, вы можете импортировать таблицы из ряда других реляционных баз данных.

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

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

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

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

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

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

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

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

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

    Работа со сводными таблицами в 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 выполняется следующим образом. Для примера сделаем простую сводную по дате поставки и сумме.

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

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

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

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

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

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

    Сводные таблицы — один из самых замечательных инструментов в 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) :

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    8. Нажмите Готово.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Самое рациональное решение – это создание сводной таблицы в Excel:

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

    Просто, быстро и качественно.

    Важные нюансы:

    • Первая строка заданного для сведения данных диапазона должна быть заполнена.
    • В базовой табличке каждый столбец должен иметь свой заголовок – проще настроить сводный отчет.
    • В Excel в качестве источника информации можно использовать таблицы Access, SQL Server и др.
    

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

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

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

    Создадим отчет с помощью мастера сводных таблиц:

    1. Вызываем меню «Мастер сводных таблиц и диаграмм». Для этого щелкаем кнопку настройки панели быстрого доступа и нажимаем «Другие команды». Здесь на вкладке «Настройка» находим «Мастер сводных таблиц». Добавляем инструмент в панель быстрого доступа. После добавления:
    2. Ставим курсор на первую табличку и нажимаем инструмент «Мастера». В открывшемся окне отмечаем, что создать таблицу хотим в «нескольких диапазонах консолидации». То есть нам нужно объединить несколько мест с информацией. Вид отчета – «сводная таблица». «Далее».
    3. Следующий этап – «создать поля». «Далее».
    4. Прописываем диапазон данных, по которым будем формировать сводный отчет. Выделяем первый диапазон вместе с шапкой – «добавить». Второй диапазон вместе с названием столбцов – снова «добавить».
    5. Теперь в списке выбираем первый диапазон. Ставим птичку у единицы. Это первое поле сводного отчета. Даем ему имя – «Магазин 1». Выделяем второй диапазон данных – ставим птичку у цифры «2». Название поля – «Магазин 2». Нажимаем «Далее».
    6. Выбираем, где расположить сводную таблицу. На существующем листе или новом. Лучше выбрать новый лист, чтобы не было наложений и смещений. У нас получилось так:

    Как видите всего в несколько кликов можно создавать сложные отчеты из нескольких листов или таблиц разного объема информации.

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

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

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

    Добавим в сводную таблицу еще одно поле для отчета. Для этого установим галочку напротив «Даты» (или напротив «Товара»). Отчет сразу меняется – появляется динамика продаж по дням в каждом отделе.

    Сгруппируем данные в отчете по месяцам. Для этого щелкаем правой кнопкой мыши по полю «Дата». Нажимаем «Группировать». Выбираем «по месяцам». Получается сводная таблица такого вида:

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

    А вот что получится, если мы уберем «дату» и добавим «отдел»:

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

    Чтобы название строки сделать названием столбца, выбираем это название, щелкаем по всплывающему меню. Нажимаем «переместить в название столбцов». Таким способом мы переместили дату в столбцы.

    Поле «Отдел» мы проставили перед наименованиями товаров. Воспользовавшись разделом меню «переместить в начало».

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

    В открывшемся меню выбираем поле с данными, которые необходимо показать.

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

    Проверка правильности выставленных коммунальных счетов

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

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

    Для примера мы сделали сводную табличку тарифов для Москвы:

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

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

    = тариф * количество человек / показания счетчика / площадь

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

    Наши формулы ссылаются на лист, где расположена сводная таблица с тарифами.

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

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

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

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

    Сводные таблицы - один из самых замечательных инструментов в 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 и копируем туда следующий код:

    Sub New_Multi_Table_Pivot()
 Dim i As Long
 Dim arSQL() As String
 Dim objPivotCache As PivotCache
 Dim objRS As Object
 Dim ResultSheetName As String
 Dim SheetsNames As Variant
 
 'имя листа, куда будет выводиться результирующая сводная
 ResultSheetName = "Сводная"
 'массив имен листов с исходными таблицами
 SheetsNames = Array("Альфа", "Бета", "Гамма", "Дельта")
 
 'формируем кэш по таблицам с листов из SheetsNames
 With ActiveWorkbook
 ReDim arSQL(1 To (UBound(SheetsNames) + 1))
 For i = LBound(SheetsNames) To UBound(SheetsNames)
 arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]"
 Next i
 Set objRS = CreateObject("ADODB.Recordset")
 objRS.Open Join$(arSQL, " UNION ALL "), _
 Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
 .FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)
 End With
 
 'создаем заново лист для вывода результирующей сводной таблицы
 On Error Resume Next
 Application.DisplayAlerts = False
 Worksheets(ResultSheetName).Delete
 Set wsPivot = Worksheets.Add
 wsPivot.Name = ResultSheetName
 
 'выводим на этот лист сводную по сформированному кэшу
 Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal)
 Set objPivotCache.Recordset = objRS
 Set objRS = Nothing
 With wsPivot
 objPivotCache.CreatePivotTable TableDestination:=wsPivot.Range("A3")
 Set objPivotCache = Nothing
 Range("A3").Select
 End With
 
 End Sub
 
 

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

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

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

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

    Вуаля!

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

     Provider=Microsoft.Jet.OLEDB.4.0;
 

    на:

     Provider=Microsoft.ACE.OLEDB.12.0;
 

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

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

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

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

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

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


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

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

     

    Работа со сводными таблицами в 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 выполняется следующим образом. Для примера сделаем простую сводную по дате поставки и сумме.

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

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

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

    Скачать пример работы

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

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

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

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

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

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

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

    2. Щелкните Data > Получить внешние данные > из других источников > From SQL Server .

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

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

    5. Нажмите Enter и в поле Select Database and Table выберите нужную базу данных, затем нажмите Разрешить выбор нескольких таблиц .

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

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

    8. Нажмите Готово .

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

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

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

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

    • Перетащите поля даты или территории в область ROWS или COLUMNS для анализа продаж по дате или территории.

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

      Если вы работаете с другими типами баз данных:

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

      • Вы можете импортировать несколько таблиц из Access. Подробности см. В разделах «Учебник: импорт данных в Excel» и «Создание модели данных».

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

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

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

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

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

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

    2. Щелкните Вставить > Сводная таблица .

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

    4. Щелкните Выберите соединение .

    5. На вкладке Таблицы в Эта модель данных книги выберите Таблицы в модели данных книги .

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

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

    .

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

    Переполнение стека
    1. Около
    2. Продукты
    3. Для команд
    1. Переполнение стека Общественные вопросы и ответы
    2. Переполнение стека для команд Где разработчики и технологи делятся частными знаниями с коллегами
    3. Вакансии Программирование и связанные с ним технические возможности карьерного роста
    4. Талант Нанимайте технических специалистов и создавайте свой бренд работодателя
    5. Реклама Обратитесь к разработчикам и технологам со всего мира
    6. О компании
    .

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

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

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

    Взаимодействие с другими людьми

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

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

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

    Наиболее рациональным решением является создание сводной таблицы в Excel:

    1. Выделите ячейку A1, чтобы Excel знал, какую информацию ему следует использовать.
    2. В меню «ВСТАВИТЬ» выберите «Сводную таблицу».
    3. Открывается меню «Создать сводную таблицу», в котором мы выбираем диапазон и указываем расположение. Поле диапазона будет заполнено автоматически, поскольку мы установили курсор в ячейку данных. Если курсор находится в пустой ячейке, вам необходимо установить диапазон вручную.Сводную таблицу можно создать на том же листе или на другом. Не забудьте указать место для данных, если хотите, чтобы сводные данные были на существующей странице. На странице появится следующая форма:
    4. Создадим таблицу, в которой будет отображаться объем продаж по отделам. Выбираем нужные нам названия столбцов в списке полей сводной таблицы. Мы получаем результаты по каждому отделу.

    Просто, быстро и качественно.

    Важные нюансы:

    • Необходимо заполнить первую строку указанного диапазона.
    • У каждого столбца в основной таблице должен быть свой заголовок, потому что стало проще создавать сводный отчет.
    • Вы можете использовать таблицы Access, SQL Server и т. Д. В качестве источника информации в Excel.
    .

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

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

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

    включает таблицу с несколькими десятками и сотнями строк, несколько таблиц в одной книге, несколько файлов. Давайте пересмотрим порядок создания сводной таблицы: «ВСТАВИТЬ» - «Таблицы» - «Сводные таблицы».

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

    Взаимодействие с другими людьми

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

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

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

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

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

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

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

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

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

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

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

    Взаимодействие с другими людьми

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

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

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

    Если вы измените какой-либо параметр в исходной таблице или добавите новую запись, эта информация не будет отображаться в отчете сводной таблицы.Эта ситуация нас не устраивает. Выбираем «Сводная таблица» - «АНАЛИЗ» - «Обновить» (ALT + F5). Курсор должен находиться в любой ячейке главного отчета.

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

    Или щелкните правой кнопкой мыши - обновите.

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

    1. Курсор находится в любом месте отчета. «ИНСТРУМЕНТ Сводных таблиц» - «АНАЛИЗ» - «Сводная таблица» - «Параметры».
    2. В открывшемся диалоге - Данные - Обновлять данные при открытии файла - ОК.

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

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

    1. Вставить столбец «Продажи» на лист с исходными данными. Здесь мы отразим, какую выручку магазин получит от продажи товара. Воспользуемся формулой - цена (F2) * количество проданных единиц (I2).
    2. Перейти к листу с отчетом. «ИНСТРУМЕНТ Сводной таблицы» - «АНАЛИЗ» - «Изменить источник данных». Расширить круг информации, которая должна входить в сводную таблицу.

    Если мы добавили столбцы внутри исходной таблицы, этого было достаточно для обновления сводной таблицы.

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

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

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

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

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

    1. Определите, какие функции будет выполнять цифровая колонка. Определите данные в сводной таблице, на которые должно ссылаться вычисляемое поле. Допустим, вам нужны остатки по группам товаров.
    2. «ИНСТРУМЕНТ ПОВОРОТНОЙ ТАБЛИЦЫ» - «АНАЛИЗ» - «Поля, элементы и наборы» - «Вычисляемое поле».
    3. В открывшемся меню введите название поля. Установите курсор в строку «Формула». Инструмент «Расчетное поле» не реагирует на диапазоны. Поэтому выбирать ячейки в сводной таблице бессмысленно. Из предложенного списка выберите категории, которые необходимы для расчета. Затем нажмите «Вставить поле». Дополните формулу необходимыми арифметическими операциями.
    4. Нажмите ОК. Появились остатки.

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

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

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

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

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

    Скачать рабочий пример

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

    .

    Смотрите также