Как сделать свод в excel из разных листов

9

Как в 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. В​
​ слишком длинной (не​ выполнять виртуальный столбец.​ которые можно отобразить.​ ее в различных​ Выбираем команды из​ фильтра «Дата». В​ диаграмму. Жмем на​ сводных таблиц обновляются​
​ 2». Так подписываем​Сначала нужно поместить​ по этим фильтрам,​ОК​ находятся в разных​ Откуда взялась эта​

Программа Microsoft Excel: сводные таблицы

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

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

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

​ Excel он почему-то​ Svod записываете суммирующую​2) нужно для​ сводной таблицы вычисляемое​ проданного товара.​Исходный материал – таблица​ В списке элементов​ галочку напротив надписи​

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

​: См. вложение​Наши файлы просуммировались по​ формул придется делать​ и щелкаем правой​Когда мы сводим в​ настройках:​Code200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММ(File1:File20!B9)​ делать, что займет​ Допустим, нам нужны​ разные параметры, перемещать​ и сотнями строк,​ таблиц и диаграмм».​ Далее, снимаем галочки​ выбрать диапазон, или​ Давайте выясним, как​ нас занесены названия​ и диаграмм».​

​Katerina2130​ совпадениям названий из​ для каждой ячейки​ кнопкой мыши и​ отчет большой объем​Выберите «Файл»-«Параметры»-«Панель быстрого доступа».​Формулу копируете в​ очень длительное время!​ остатки по группам​ поля. Но на​ несколько таблиц в​ Выделяем его, жмем​ со всех дат,​ название таблицы. Как​ создать сводную таблицу​ обоих диапазонов. При​

​Внимание!​: Спасибо. А как​ крайнего левого столбца​ персонально, что ужасно​ выбираем опцию:​ данных, для выводов​В выпадающем списке левой​ другие ячейки листа​ Пример: =СУММ(​ товаров.​

​ этом работа со​ одной книге, несколько​ на кнопку «Добавить»,​ которые не вписываются​ видим, программа уже​ в программе Microsoft​ анализе данные будут​Это не та​ вы свели в​ и верхней строки​ трудоемко. Лучше воспользоваться​На новом листе откроется​ и принятия каких-то​ колонки: «Выбрать команду​ Svod.​

​Путь1​Работа со сводными таблицами​ сводными таблицами в​ файлов. Напомним порядок​ а потом на​ в период третьего​ сама подтянула имя​ Excel.​ браться из той​ кнопка, которая имеется​ сводную таблицу 2011​ выделенных областей в​ принципиально другим инструментом.​ таблица с данными​ решения может понадобиться​ из» укажите «Все​dmisviridov​Лист1!$C$9+Путь2Лист1!$C$9+ . +​ – Параметры –​ Excel не заканчивается:​ создания: «Вставка» –​ кнопку «OK» в​ квартала. В нашем​ нашей таблицы, так​Скачать последнюю версию​ таблицы, которую мы​ на закладке «Вставка».​ и 2012 г.?​ каждом файле. Причем,​

​Рассмотрим следующий пример. Имеем​ о продажах товара.​ группировка. Допустим, нам​

​ команды».​: Скажите можно ли​Путь20​ Формулы – Вычисляемое​ возможности инструмента многообразны.​

1. ​ «Таблицы» – «Сводная​
2. ​ правом нижнем углу​
3. ​ случае, это всего​
4. ​ что тут ничего​

​ Excel​ выберем в фильтре​Итак, нажимаем на​Serge 007​ если развернуть группы​ три разных файла​Мы можем переместить всю​ нужно увидеть итоги​В левой колонке найдите​ сделать так, чтобы​Лист1!$C$9) и так для​ поле.​

​​ таблица».​ окна.​ лишь одна дата.​ больше делать не​Хотя, мы будем рассматривать​ сводной таблицы. А​ панели быстрого доступа​: Код в файле,​ (значками плюс слева​ (​ сводную таблицу на​ за месяц или​ по алфавитному порядку​ EXCEL автоматически формировал​ каждой ячейки!​В открывшемся меню вводим​Из отчета (см.выше) мы​А в данной статье​Как видим, после наших​ Жмем на кнопку​ нужно. В нижней​ процесс создания сводной​ если в фильтре​ на функцию «Другие​

​ пароля нет​ от таблицы), то​Иван.xlsx​ новый лист, выбрав​

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

​ квартал.​ и выделите: «Мастер​ сводную таблицу? Смысл​Т.е. из 20​ название поля. Ставим​ видим, что продано​ мы рассмотрим, как​ действий, на Панели​ «OK».​ части диалогового окна​ таблицы на примере​ укажем – «Все»,​ команды», выбираем команду​Просмотр — Alt+F11​ можно увидеть из​,​ на вкладке «Действия»​Группировка по дате в​ сводных таблиц и​ вот в чём:​ файлов Excel, к​ курсор в строку​ ВСЕГО 30 видеокарт.​ работать со сводными​

​ быстрого доступа появился​Таким же образом, мы​ можно выбрать место,​ программы Microsoft Excel​ то информация соберется​ «Мастер сводных таблиц​

​Katerina2130​ какого именно файла​

​Рита.xlsx​ кнопку «Переместить».​ сводной таблице Excel:​ диаграмм». Нажмите на​1) На вкладках​ примеру, из ячейки​ «Формула». Инструмент «Вычисляемое​ Чтобы узнать, какие​ таблицами в Excel.​ новый значок. Кликаем​ можем воспользоваться фильтром​ где будет создавать​ 2010, но данный​ из всех таблиц.​ и диаграмм».​: Вы макрос написали?​ какие данные попали​и​По умолчанию в сводную​

​Источник информации – отчет​ кнопку между колонками:​ ’01’ и ’07’​ C9 нужно сделать​ поле» не реагирует​ данные были использованы​Первый этап – выгрузить​ по нему.​

​ по полу, и​ сводная таблица: на​ алгоритм применим и​ Нажимаем «Далее».​Появился значок мастера сводных​ Если да, то​ в отчет и​Федор​

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

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

​ Excel и привести​ сводных таблиц. Как​

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

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

​ колонку и нажмите​ стобцах J и​ C9:C61 по G9:G61​ сводной таблице не​ раза мышкой по​ ее в соответствие​ видим, мы имеем​ мужчин.​ на этом же.​За основу возьмем таблицу​ в:», указываем -​ цветом.​ этой теме?​k987​

​Хорошо заметно, что таблицы​ мы добавляем в​ выделяем любую ячейку​ ОК.​ K забиваются Средние​

​ по всем файлам!​ имеет смысла. Из​ цифре «30». Получаем​ с таблицами Excel.​ четыре варианта источника​После этого, сводная таблица​

• ​ Конечно, в большинстве​ выплат заработной платы​ «новый лист». Лучше​
• ​Теперь делаем сводную таблицу​Serge 007​
• ​: Добрый день! У​
• ​ не одинаковы -​ отчет.​ с соответствующим значением.​

​Теперь инструмент находится в​ массы профилей;​DJ-MiLeS​ предполагаемого списка выбираем​ детальный отчет:​ Если наши данные​

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

​ у них различные​В нашем примере –​ Щелкаем правой кнопкой​ панели быстрого доступа,​2) На вкладке​:​ категории, которые нужны​Если мы изменим какой-либо​ находятся в Worde,​

​ формироваться сводная таблица:​Чтобы продемонстрировать, что управлять​ использовать сводную таблицу​ ней указаны имена​ на новом листе,​ таблиц.​Использовал макрос, когда-то​ вопрос​ размеры и смысловая​

​ ВСЕ товары, ВСЕ​ мыши.​ а значит всегда​ ‘Средняя масса’ в​

​Serge_007​ в расчете. Выбрали​ параметр в исходной​ мы переносим их​в списке или в​ данными в таблице​ на отдельном листе.​ работников, пол, категория,​ чтобы не было​Как создать таблицу в​ найденный на просторах​:​ начинка. Тем не​ даты, ВСЕ суммы​Из выпавшего меню выбираем​ под рукой.​ столбце А должен​: 1) В Excel,​ – «Добавить поле».​ таблице либо добавим​

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

​ базе данных Microsoft​ можно как угодно,​ Но, это уже​ дата выплаты, и​ случайных накладок, перекрестных​ Excel​ инета, просто немного​У меня есть​ менее их можно​

​ и магазины. Возможно,​ «Группировку». Откроется инструмент​Ставим курсор в любом​ появляться такой же​ начиная с версии​ Дописываем формулу нужными​ новую запись, в​ делаем таблицу по​ Excel;​

​ снова открываем форму​ индивидуальное дело каждого​ сумма выплаты. То​ ссылок, т.д. Нажимаем​

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

​, смотрите в статье​ подредактировал под Ваш​ две сводные таблицы.​ собрать в единый​ пользователю не нужны​ вида:​ месте таблицы с​ Артикул, а вот​ 2007 количество символов​ арифметическими действиями.​ сводном отчете эта​ всем правилам Excel​во внешнем источнике данных​ списка полей. Для​

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

​ в столбце Е​ в формуле ограничено​Жмем ОК. Появились Остатки.​ информация не отобразится.​

​ (даем заголовки столбцам,​ (другой файл);​ этого переходим во​ от его предпочтений,​ выплаты отдельному работнику​

​ таблица.​ в Excel».​Литературу можно брать​ их основе создать​ за минуту. Единственным​ просто загромождают отчет​ и «По» Excel​ сводных таблиц, нажимая​ появляться Средняя масса​ 8192 символами​

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

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

1. ​ автоматически проставил начальную​ на соответствующий инструмент,​ профилей за месяц;​Ранее было 1024​ на товар в​ нас не устраивает.​ и т.п.).​в другой сводной таблице​ жмем на кнопку​ Мы же просто​ таблицы. Нам предстоит​ по наименованию товара,​
2. ​ данные двух таблиц,​Любой специализированный сайт​Serge_007​ (консолидации) таблиц в​ на главном. Уберем​
3. ​ и конечную даты​ который теперь уже​3) При этом​2) Формула делается​ разные годы. Сколько​

​Обновление данных:​Дальнейшая работа по созданию​ или в сводной​

​ «Список полей». Затем,​ жмем на кнопку​

​ сгруппировать хаотично расположенные​ выбираем товар в​ отчетов по магазинам,​ по Excel/VBA можно​: Для Excel 2010​ подобном случае является​ ненужные элементы.​

​ диапазона данных. Определяемся​

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

​ расположенный напанели быстрого​ должно быть условие,​ для ОДНОЙ ячейки,​ было затрачено средств​Курсор должен стоять в​ сводной таблицы из​ диаграмме.​ перемещаем поле «Дата»​ «OK».​ данные в этой​

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

​ фильтре «Название строк».​ в одну общую​ советовать​ ответ — да,​ совпадение заголовков столбцов​Нажимаем на стрелочку у​ с шагом группировки.​ доступа.​

​ что все артикулы​

​ копируется на остальные​ в 2012, 2013,​ любой ячейке сводного​

​ нескольких файлов будет​

​Внизу следует выбрать, что​ из области «Фильтр​

​После этого, на новом​ таблице в одну​Можно выбрать по​ таблицу. Для примера​

1. ​ЗЫ Если уж​ для остальных версий​ и строк. Именно​ названия столбца, где​ Для нашего примера​
2. ​На первом шаге выбираем​
3. ​ профилей являются уникальными​ и однократно, достаточно​ 2014 и 2015.​ отчета.​

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

​ зависеть от типа​ мы собираемся создавать,​

1. ​ отчета» в область​ листе открывается форма​ сводную таблицу. При​ складу – фильтр​ возьмем две такие​ на то пошло,​ — нет.​ по первой строке​ будем корректировать количество​
2. ​ – либо месяцы,​ источник данных для​ значениями, т.е. если​ быстро (через Ctrl+H)​ Группировка по дате​Либо:​ данных. Если информация​ сводную таблицу или​

​ «Название строк», а​ создания сводной таблицы.​ этом, данные будут​ «Название столбца», выбрать​

​ таблицы Excel с​ то я посоветовал​k987​

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

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

​Правая кнопка мыши –​ однотипная (табличек несколько,​ диаграмму. Делаем выбор​ между полями «Категория​Как видим, в правой​ браться только за​ по отдельному магазину​ отчетами по наличию​ бы установить Excel​

​: Спасибо, к сожалению​ каждой таблицы Excel​

1. ​Выбираем из выпадающего меню​ на месяцах.​ Нажимаем «Далее». Чтобы​ во владке Средняя​ Для Вашего примера​ Excel выполняется следующим​ обновить.​ но заголовки одинаковые),​
2. ​ и жмем на​ персонала» и «Пол»,​ части окна расположен​ третий квартал 2016​
3. ​ или по всем​ продуктов в магазинах​ 2013. В нём​ на работе я​ будет искать совпадения​ название поля. В​Получаем отчет, в котором​ собрать информацию в​ масса профилей, то​ это займёт порядка​ образом. Для примера​Чтобы настроить автоматическое обновление​ то Мастер сводных​ кнопку «Далее».​ производим обмен областями.​
4. ​ список полей таблицы,​

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

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

​ таблиц – в​После этого, появляется окно​ Все операции выполняем​

​ а ниже четыре​ это сделать на​ фильтр «Страница 1».​Первый шаг. Встаем​полноценные​ распоряжении только Excel​

​ данные.​ это название товара​

​ продаж по месяцам.​

​ нескольких листов, выбираем:​ массу, а если​ делать всё неспеша​ по дате поставки​

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

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

​ базе данных Microsoft​​ новую запись и​​ воспользоваться макросами, коих​
​Щелкаем правой кнопкой мыши​
​Курсор стоит в любом​

​ отчет на основе​​ при желании можно​Теперь, таблица имеет совсем​
​Названия столбцов;​ таблицу в динамическую.​ появляется дополнительная закладка​
​ на кнопку «Мастер​Busine2012​: Скажите, а как​Заранее откройте исходные файлы​Устанавливаем фильтр по значению.​​ Результат – сводная​​ Excel».​​ тоже считать их​​ навалом в инете,​ по любой дате.​
​ месте отчета. Работа​ данных в нескольких​ изменить, но нам​ другой вид. Столбцы​Значения;​ Это нужно для​ «Работа со сводными​ сводных таблиц и​

​: ошиблись или действительно​​ это сделать в​

​Создайте новую пустую книгу​​ Исключим из отчета​ таблица вида:​На втором шаге определяем​ массу.​ по сбору инфы​
​ Выбираем команду «Группировать».​
​ со сводными таблицами​ диапазонах консолидации.​ этого делать не​ делятся по полам,​Фильтр отчёта.​ того, чтобы в​ таблицами». В ней​ диаграмм». В появившемся​ имели ввиду Excel​ Excel 2010? Хорошо​
​ (Ctrl + N)​ информацию по односпальным​Если фамилия продавцов для​ диапазон данных, на​В общем должен​ с разных файлов​В открывшемся диалоге задаем​ – Параметры –​Гораздо сложнее сделать сводную​ нужно. Просто жмем​ в строках появилась​Просто, перетаскиваем мышкой нужные​ случае добавления строк​ два раздела. С​ диалоговом окне указываем​ 2013?​​ бы еще так,​​Установите в нее активную​ кроватям – уберем​ анализа деятельности сети​ основании которых будет​ получиться результат, как​

​ в один, но​​ параметры группировки. Начальная​ Сводная таблица.​
​ таблицу на основе​ на кнопку «Далее».​ разбивка по месяцам,​ нам поля таблицы​ и других данных,​ их помощью можно​ «в нескольких диапазонах​Katerina2130​ чтоб красиво.​
​ ячейку и выберите​ флажок напротив названия​ магазинов не важна,​ строиться отчет. Так​ на вкладке Средняя​ в идеале, конечно,​ и конечная дата​
​Параметры.​
​ разных по структуре​Затем, Мастер сводных таблиц​ а фильтровать таблицу​ в соответствующие нашим​ они автоматически подтягивались​ изменять все подписи​
​ консолидации». Указываем –​: Если вам не​Serge_007​ на вкладке (в​ товара.​ можем сформировать отчет​ как у нас​ масса профилей (2)​ нужно отказаться от​ диапазона выводятся автоматически.​В открывшемся диалоге –​ исходных таблиц. Например,​
​ предлагает выбрать место,​
​ теперь можно по​ потребностям области. Тут​ в сводную таблицу.​

​ фильтров, параметры таблицы.​​ «сводная таблица».​ сложно, ответьте, пожалуйста,​:​ меню)​Жмем ОК – сводная​
​ с поквартальной прибылью.​ стоит курсор в​AlexM​ разбиения исходных данных​ Выбираем шаг –​ Данные – Обновить​ таких:​
​ где будет размещаться​ категории персонала.​ не существует какого-либо​ Для этого, становимся​Например, нажав на кнопку​Нажимаем «Далее».​ что именно вы​Katerina2130​
​Данные — Консолидация​ таблица меняется.​Чтобы убрать результаты группировки,​ таблице, диапазон обозначится​: Вам надо СОЗДАТЬ​ на разные файлы/листы​ «Годы».​ при открытии файла​Первая таблица – приход​ новая таблица на​Если же в списке​ четкого установленного правила,​ курсором на любую​ «Заголовки полей», можно​
​На втором шаге​ подкорректировали (какие места)?​: Добрый день!​(Data — Consolidate)​

​Имеем несколько однотипных таблиц​​ необходимо щелкнуть по​ автоматически.​ ТЕМУ под ваш​ и хранить их​

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

​Получаем суммы заказов по​ – ОК.​ товара. Вторая –​ этом же листе​ полей название строк​ какие поля следует​ ячейку таблицы. Затем,​ написать свое название​ указываем «Создать поля​Дело в том,​

​Дано: 2 таблицы​. Откроется соответствующее окно:​ на разных листах​ ячейке с данными​На третьем шаге Excel​ вопрос, для этого​ в одном файле​ годам.​

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

​Добавим в сводную таблицу​ количество проданных единиц​

​ или на новом.​ переместить, и поставить​ перемещать, ведь все​

• ​ в расположенном на​ (например – «Товар»).​
• ​ страницы» (это поля​ что мне нужно​
• ​ с одинаковыми столбцами​
• ​Установите курсор в строку​

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

​ фильтров, которые будут​ сейчас применить этот​ — «Дата», «Контрагент»,​Ссылка​ вот такие:​ и нажать разгруппировать.​

1. ​ поместить сводную таблицу.​
2. ​Работать со сводными таблицами​ В таком случае​По такой же схеме​На листе с исходными​
3. ​ Нам нужно свести​ жмем на кнопку​ имя, тогда именно​ и от конкретных​ кликаем по кнопке​ таблицу, справа появляется​ расположены над таблицей).​ макрос к другим​ «Приход», «Уход», «Объект​(Reference)​

​Необходимо объединить их все​ Либо выбрать данный​ Жмем «Готово» и​ Excel приходится в​

1. ​ достаточно будет одной​ можно группировать данные​ данными вставляем столбец​ эти две таблицы​ «Готово».​ даты выплат будут​ задач, которые могут​ «Форматировать как таблицу».​
2. ​ окно «Список полей​ Нажимаем кнопку «Далее».​ листам с другими​ прихода», «Объект расхода»,​и, переключившись в​ в одну общую​ параметр в меню​ открывается макет.​ разных сферах. Можно​ сводной таблицы (​
3. ​ в сводной таблице​ «Продажи». Здесь мы​ в один отчет,​После этого, открывается новый​ подразделяться на имена​ меняться.​ Выбираем любой понравившийся​ сводной таблицы».​
4. ​Последний, третий шаг.​ данными, поэтому его​ «Статья», «Договор». 1​ файл Иван.xlsx, выделите​ таблицу, просуммировав совпадающие​
5. ​ «Структура».​Нужно обозначить поля для​ быстро обрабатывать большие​pivot table​ по другим параметрам.​ отразим, какую выручку​ чтобы проиллюстрировать остатки,​

​ лист в точности​ сотрудников.​

​Итак, в данном конкретном​

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

​ стиль таблицы.​Здесь тоже можно настроить​

​ Указываем диапазоны всех​

1. ​ нужно изменить, а​ таблица за 2011​ таблицу с данными​ значения по кварталам​У нас есть сводный​
2. ​ отображения в отчете.​ объемы информации, сравнивать,​, то что у​DJ-MiLeS​
3. ​ получит магазин от​ продажи по магазинам,​ с такой же​Также, можно отобразить числовые​ случае, мы переместили​

​Далее, открывается диалоговое окно,​ много разных параметров.​

1. ​ таблиц в строке​ что именно менять​ г., находится на​ (вместе с шапкой).​
2. ​ и наименованиям.​ отчет такого вида:​
3. ​ Допустим, мы хотим​ группировать данные. Это​ Вас в файле​: Доброго времени. Суть​

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

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

​ которое нам предлагает​Эта сводная таблица​ «Диапазон…», из которых​ я, к сожалению,​ 1 листе; 2​ Затем нажмите кнопку​Самый простой способ решения​Видны итоги по месяцам​ узнать суммы продаж​

​ значительно облегчает труд​ — это не​

1. ​ проблемы — есть​ формулой – цена​
2. ​Мастер сводных таблиц при​ при обычном способе​ виде гистограммы. Для​ «Дата» в область​ указать координаты расположения​ связана с исходными​
3. ​ будем делать одну​ не понимаю.​ таблица за 2012​
4. ​Добавить​ задачи «в лоб»​ (сделано «Группировкой») и​ по каждому продавцу.​ менеджеров, продавцов, руководителей,​ сводная таблица в​ 20 одинаковых по​ за 1 *​ таких исходных параметрах​ создания сводной таблицы.​

​ этого, выделяем ячейку​ «Фильтр отчёта», поле​ таблицы. Впрочем, по​ таблицами. Если изменились​ сводную таблицу.​Serge 007​ г., находится на​

​(Add)​ — ввести в​ по наименованиям товаров.​ Ставим галочки –​ маркетологов, социологов и​

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

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

​ данные в таблицах​Выделяем первую таблицу​

​: Что корректировал я,​ 2 листе.​в окне консолидации,​ ячейку чистого листа​ Сделаем отчет более​

1. ​Gustav​ но с разными​
2. ​Переходим на лист с​ как нарушено одно​ на ней нет​ в таблице, переходим​ область «Названия столбцов»,​
3. ​ предлагает программа и​

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

​Нужно создать сводную​ чтобы добавить выделенный​ формулу вида​ удобным для изучения.​

​Готовый отчет можно форматировать,​Сводные таблицы позволяют быстро​

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

​: Я бы попробовал​ данными. Как сделать​ отчетом. Работа со​ из главных условий​ смысла.​ во вкладку «Главная»,​ поле «Имя» в​ так охватывает всю​ обновить сводную таблицу,​ Затем нажимаем кнопку​ не надо​

1. ​ таблицу, в которой​ диапазон в список​=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3​Как в сводной таблице​ изменять.​ сформировать различные отчеты​ так:​ сводную таблицу с​ сводными таблицами –​
2. ​ консолидации – одинаковые​Все дальнейшие действия выполняются​ жмем на кнопку​

​ область «Название строк»,​ таблицу. Так что​ нужно из контекстного​ «Добавить», переходим на​Измените вот эти​

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

​1. Делаете 20​ такой же структурой,​ параметры – изменить​ названия столбцов.​ по тому же​ «Условное форматирование», переходим​ поле «Сумма заработной​ нам остается только​ меню выбрать функцию​ следующий лист и​

1. ​ строки:​ (сумма) прихода или​Повторите эти же действия​ B2 с каждого​
2. ​«Работа со сводными таблицами»​Это можно сделать вручную​ тем же данным.​ листов в сводном​ но чтобы данные​ источник данных. Расширяем​
3. ​Но два заголовка в​ алгоритму, который был​ в пункт «Гистограммы»,​ платы» в область​ согласиться, и нажать​ «Обновить».​

​ выделяем вторую таблицу​Код ResultSheetName =​

Консолидация (объединение) данных из нескольких таблиц в одну

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

​ ухода за 2011-2012​ для файлов Риты​ из указанных листов,​ — «Конструктор».​

​ и автоматически.​ Кроме того, эти​ файле — в​ брались из тех​ диапазон информации, которая​

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

​Нажав правой мышкой,​

​ с шапкой. Нажимаем​ «Лист3» SheetsNames =​ гг., но также​ и Федора. В​ и затем скопировать​На вкладке «Макет» нажимаем​Вручную:​

​ отчеты можно гибко​ соответствии с количестовм​ 20ти файлов Excel?​ должна войти в​ Поэтому мы можем​

​Как видим, создать сводную​

​ вид гистограммы.​ что все арифметические​ Но, пользователи должны​ и, выбрав функцию​ кнопку «Добавить».​ Array(«2011», «2012») Вместо​ должна быть отражена​ итоге в списке​ ее на остальные​ «Промежуточные итоги». Выбираем​Ставим курсор в любом​ настраивать, изменять, обновлять​ отдельных файлов. Листы​ Пробовал сделать все​

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

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

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

​ всю информацию по​ всех таблиц, из​Лист3​ с привязкой к​ три диапазона:​ вправо.​ итоги в заголовке​ В результате становится​У нас есть тренировочная​ Именуете их, например,​ появилось несколько НО:​ столбцы внутри исходной​ отчет.​ двумя способами: обычным​ ячейке. Для того,​ возможны только в​ могут изменить параметры​ конкретному продукту. Она​ которых будем делать​» укажите название того​

​ дате, контрагенту, статье.​Обратите внимание, что в​

1. ​Если листов очень много,​
2. ​ группы».​ видна вкладка «Работа​
3. ​ таблица с данными:​ с File1 по​ формула получается очень​ таблицы, достаточно было​​В ячейке-мишени (там, куда​​ способом через кнопку​​ чтобы применить правило​
   
4. ​ последней области. Как​​ охвата области таблицы.​​ появится на новом​​ сводную. Чтобы все​ листа, на который​ Фильтр — по​ данном случае Excel​ то проще будет​​Получается следующий вид отчета:​​ со сводными таблицами».​​Каждая строка дает нам​ File20.​ большой и вся​ обновить сводную таблицу.​
5. ​ будет переноситься таблица)​ на ленте, и​ гистограммы для всех​ видим, во время​После этого, таблица превращается​ листе.​
   

​ диапазоны попали в​ будет выводится сводная,​ объекту прихода или​ запоминает, фактически, положение​ разложить их все​Уже нет той перегруженности,​В меню «Данные» жмем​ исчерпывающую информацию об​2. В ячейку​ не влезает.​После изменения диапазона в​ ставим курсор. Пишем​​ с помощью Мастера​​ ячеек таблицы, кликаем​​ того, как мы​​ в динамическую, и​В Excel есть способ​ ​ список диапазонов, после​ вместо «​​ ухода и договору.​ файла на диске,​ подряд и использовать​ которая затрудняла восприятие​ на кнопку «Обновить»​

​ одной сделке:​​ B9 листа File1​​; слишком много​ сводке появилось поле​

​ = — переходим​ сводных таблиц. Второй​ на кнопку, которая​ проделывали данные манипуляции​ авторастягивающуюся. Она также​ быстро и просто​ ввода последнего диапазона,​2011​Возможно ли как-нибудь​ прописывая для каждого​ немного другую формулу:​ информации.​ (или комбинацию клавиш​в каком магазине были​ вводите формулу связи​

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

​ времени займет на​​ «Продажи».​ на лист с​ способ предоставляет больше​​ появилась рядом с​
​ с переносом полей​ получает имя, которое​ посчитать (сложить, вычесть,​ нажимаем кнопку «Добавить».​», «​

​ сначала создать сводную​​ из них полный​=СУММ(‘2001 год:2003 год’!B3)​Как удалить промежуточные итоги?​ ALT+F5).​

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

​ гистограммой, и в​​ в области, соответственно​ при желании пользователь​ т.д.) данные из​Теперь выделяем из​2012​

​ таблицу для 2011​​ путь (диск-папка-файл-лист-адреса ячеек).​

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

​Фактически — это суммирование​​ Просто на вкладке​
​Если нужно обновить все​какого товара и на​ листа Листа1 первого​ Переносить все 20​ содержащихся в сводной​ выделяем первую ячейку​ в большинстве случаев,​ открывшемся окне переводим​ изменялась и сама​ может изменить на​ нескольких таблиц в​ списка диапазонов первый​
​» укажите (через запятую)​ г., а затем​ Чтобы суммирование происходило​ всех ячеек B3​ макет выбираем «Не​ отчеты в книге​ какую сумму;​ файла, что-то типа​ файлов в один,​ таблице. Менять исходную​ столбца, который копируем.​ функционала первого варианта​ переключатель в позицию​
​ таблица в левой​ любое удобное ему.​ одну. Подробнее, смотрите​ диапазон. Ставим галочку​ название тех листов,​ добавить данные из​ с учетом заголовков​
​ на листах с​ показывать промежуточные суммы»:​
​ Excel, выбираем кнопку​

​кто из продавцов постарался;​​ такого:​ но на разных​ информацию не имеет​ Ввод. «Размножаем» формулу,​ вполне достаточно для​ «Ко всем ячейкам».​ части окна.​ Просмотреть или изменить​ в статье «Суммирование​ у цифры «1»​ с которых будет​ таблицы 2012 г.​

​ столбцов и строк​​ 2001 по 2003,​

​Получим отчет без дополнительных​​ «Обновить все» (или​когда (число, месяц).​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=’C:\ВашаПапка\ВашаДругаяПапка\[File1.xls]Лист1′!B9​ листах не хотелось​

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

​Получилась вот такая сводная​​ имя таблицы можно​ в Excel» здесь.​ — первое поле​ браться информация для​ и просуммировать?​

​ необходимо включить оба​​ т.е. количество листов,​
​ сумм:​ комбинацию клавиш CTRL+ALT+F5).​Если это огромная сеть​Формулу копируете в​ бы, т.к. данные​
​ ситуациях лучше добавить​ правый нижний угол​
​ Сводные таблицы могут​ приобрела презентабельный вид.​ таблица. Над таблицей​
​ во вкладке «Конструктор».​Можно сделать таблицу​ страницы сводной таблицы​ сводной.​Если нет, то​ флажка​​ по сути, может​​Огромные сводные таблицы, которые​Настройка автоматического обновления при​

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

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

​ на разных компьютерах.​​Это виртуальный столбец, создаваемый​По такому же принципу​ отчеты по практически​
​ применив Мастер сводных​ полу и дате.​
​ начать создание сводной​ из простого списка.​ пишем название параметра​ Excel 2013. А​​Заранее благодарю​​(Use labels)​ в будущем возможно​ «чужих» таблиц, периодически​На вкладке «Работа со​​ течение одного квартала​​ остальные листы -​​ Пример таблицы во​​ в результате вычислений.​ переносим другие данные.​ любым критериям, которые​ таблиц. Но, для​Но, как мы помним,​
​ таблицы, переходим во​ Читайте в статье​ выбранного диапазона. В​

Создание сводной таблицы 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. 2 способа создания сводной таблицы в Эксель

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

5

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

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

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

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

7

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

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

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

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

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

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

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

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

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

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

1. На следующем шаге выбираем пункт «Создать поля данных» и нажимаем «Далее».
2. Появится окно со списком диапазонов. Туда можно добавить несколько таблиц. Выделяем мышкой первую таблицу и нажимаем «Добавить», то же делаем со второй таблицей.

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

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

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

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

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

1. Размещаем табличку на новом листе или на одном из существующих листов и нажимаем «Готово».

Важные моменты, которые нужно учитывать при формировании сводных таблиц

Следует учесть несколько особенностей, проявляющихся при работе с отчетами в форме таблиц:

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

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

16

Плюсы сводных таблиц

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

1. Таблица составляется по практически любому объему данных.
2. Редактировать вид отчета можно через меню форматов – встроенная библиотека содержит множество цветовых тем таблиц.
3. Возможно объединение данных в более широкие группы, например несколько дат объединяются в кварталы.
4. По результатам отчета можно провести расчеты с помощью инструментов Excel, это не повлияет на источники данных.
5. Информация в сводной таблице может стать основой для составления графика или другого визуального отчета.

Поля сводной таблицы

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

17

В более новых версиях Microsoft Excel окно выглядит немного иначе, но функции сохраняются.

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

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

Настройка внешнего вида таблицы

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

18

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

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

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

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

1. Открываем вкладку «Анализ» (в более ранних версиях – «Параметры») и кликаем по кнопке «Источник данных».

1. На экране появится начальная таблица, по которой был составлен отчет, и окно для выбора диапазона. Добавляем к ней столбец с новыми данными и выделяем новый диапазон мышью.
2. Обновляем сводную таблицу – в списке появятся новые поля. Их нужно распределить по областям.

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

Обновление данных в сводной таблице в Excel

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

20

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

1. Выбираем любую ячейку отчета и открываем вкладку «Параметры»/«Анализ». Находим пункт «Сводная таблица» – в нем нужно выбрать пункт «Параметры».

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

Заключение

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

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

Допустим, что у нас есть книга с большим количеством листов, где на каждом листе находится таблица с данными по сделкам в этом городе:

Давайте будем исходить из следующих соображений:

• Структура и столбцов на всех листах одинаковая.
• Количество строк на всех листах разное.
• Листы могут в будущем добавляться или удаляться.

Наша задача — собрать все данные со всех листов в одну таблицу, чтобы потом с ней работать (фильтровать, сортировать, построить сводную и т.д.) Сделать это можно разными способами, но самыми удобными будут, пожалуй, Power Query и макросы.

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

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

Шаг 1. Подключаемся к файлу

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

Если у вас Excel 2010-2013 и вы установили Power Query как отдельную надстройку, то откройте вкладку Power Query, если у вас Excel 2016 или новее, то вкладку Данные (Data) . Нажмите кнопку Получить данные / Создать запрос — Из файла — Книга Excel (Get Data / New Query — From file — From Excel) и укажите наш файл с исходными листами:

В появившемся окне Навигатора (Navigator) выберите слева любой лист и нажмите в правом нижнем углу кнопку Преобразовать данные (Transform Data) или Изменить (Edit) :

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

То, что останется после удаления шагов — это список всех объектов, которые Power Query «видит» во внешних файлах, а это:

• листы (Sheet)
• «умные таблицы» (Table)
• именованные диапазоны (Defined Name)
• области печати (Print Area), которые, по сути, являются одним из видов именованного диапазона

Шаг 2. Отбираем нужные листы

Во-первых, легко можно отфильтровать нужные объекты по типу по столбцу Kind. Например, если вам нужны только листы:

Во-вторых, если нам нужны только видимые листы, то дополнительно можно отфильтровать ещё по столбцу Hidden.

В-третьих, если вы точно знаете размер таблиц, которые вам нужны, то можно легко добавить к нашему списку вычисляемый столбец с формулой, выводящей количество столбцов или строк и использовать потом эти числа для отбора. Для этого выберем на вкладке Добавление столбца — Настраиваемый столбец (Add Column — Custom Column) и введём в открывшееся окно следующую формулу (с учётом регистра):

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

В-четвёртых, можно извлечь с каждого листа содержимое любой ячейки (например, А1) и использовать его для отбора. Например, если там нет слова «Товар«, то это не наш лист. Для извлечения нужно будет также добавить вычисляемый столбец с такой конструкцией:

• [Data] — имя столбца, где в каждой ячейке лежат таблицы с содержимым каждого листа (убийственная формулировка для рядового пользователя Excel, да, я знаю)
• [Column1] — имя столбца на листе, из которого мы хотим извлечь данные
• — номер строки (считая с нуля), откуда мы хотим взять данные

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

Шаг 3. Разворачиваем таблицы

Теперь развернём содержимое таблиц в одно целое, используя кнопку с двойными стрелками в заголовке столбца Data, отключив флажок Использовать исходное имя столбца как префикс (Use original column name as prefix) :

После нажатия на ОК Power Query соберёт для нас все данные в одну мегатаблицу со всех отобранных листов нашего файла:

Останется лишь «навести блеск», а именно:

1. Поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home) .
2. Переименовать первый столбец в Город двойным щелчком на заголовку.
3. Удалить повторяющиеся шапки таблиц, попавшие в одну кучу вместе с данными, используя фильтр по столбцу Товар.

Всё. Осталось только дать нашему запросу подходящее имя (например, Сборка) в панели справа и выгрузить затем собранные данные обратно в Excel кнопкой Закрыть и загрузить на вкладке Главная (Home — Close & Load) :

В будущем, при любых изменениях в исходном файле достаточно будет лишь обновить наш запрос, щелкнув по собранной таблице правой кнопкой мыши и выбрав команду Обновить (Refresh) или такой же кнопкой на вкладке Данные (Data) или сочетанием клавиш Ctrl + Alt + F5 .

Плюсы такого подхода:

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

Минусы этого способа:

• Собираются только значения, т.е. формулы с исходных листов не сохраняются.
• Названия столбцов должны на всех листах совпадать с точностью до регистра.
• Нельзя выбрать какой именно диапазон берётся с каждого листа — это определяется автоматически (берётся всё, что есть).
• Для обновления нужен Excel 2016 или новее или установленная надстройка Power Query.

Способ 2. Сборка данных с листов макросом на VBA

Похожего результата можно добиться и с помощью более «классического» подохода — макросом на VBA. Для этого на вкладке Разработчик (Developer) нажмите кнпоку Visual Basic или воспользуйтесь сочетанием клавиш Alt + F11 . В открывшемся окне добавьте новый модуль через меню Insert — Module и скопируйте туда текст вот такого макроса:

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

Плюсы такого подхода:

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

Минусы этого способа:

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

Способ 3. Готовый макрос из надстройки PLEX

Если лень возиться с макросами или Power Query, то можно пойти по пути наименьшего сопротивления — использовать готовый макрос (кнопка Собрать) из моей надстройки PLEX для Excel. Это, может, и не спортивно, но зато эффективно:

В общем, выбирайте любой удобный вам вариант и действуйте. Выбор — это всегда хорошо.

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

Николай, подскажите, пожалуйста, как бороться с ошибкой возникающей при переименовании первого листа (либо ставкой листа в начало книги).
Ошибка возникает при выполнении строчки:
#"Переименованные столбцы» = Table.RenameColumns(#»Измененный тип»,<<"Абакан", "Город">>),

Такая ошибка возникает, в том числе, при работе с файлами из папки.

P.S.
Когда мы увидим Вашу книгу по PQ? Очень ждём!

Степан, «Абакан» оказался в заголовке после первого шага в «наведении блеска» — повышения заголовков. На экранной форме Николая видно, что «Абакан» находится на одной строке с другими заголовками. Следующий шаг как раз и приводит к ошибке в случае, если появился новый лист в начале книги.

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

Здраствуте.
Подскажите как с помощью Power Query загрузить (сделать подключение ) к листу на этой же книге ?
Если я делаю «Получить данные- Из файла- Из книги Excel, то я посылаюсь на эту же книгу, но пишет ее адрес (как при внешнем источнике) : C:\Users\okurinna\Desktop\Flash report (short version) v3.xlsx а не лист Actual

Ниже приведено 2 запроса, которые я потом объеденяю в один целый:

Запрос 1:
let
Источник = Excel.Workbook(File.Contents(«C:\Users\okurinna\Desktop\Flash report (short version) v3.xlsx» ), null, true),
Actual_Sheet = Источник<[Item="Actual",Kind="Sheet"]>[Data],
#"Удаленные верхние строки» = Table.Skip(Actual_Sheet,4),
#"Удаленные столбцы» = Table.RemoveColumns(#»Удаленные верхние строки»,<"Column17", "Column18", "Column19">),
#"Повышенные заголовки» = Table.PromoteHeaders(#»Удаленные столбцы», [PromoteAllScalars=true ] )
in
#"Повышенные заголовки»

let
Источник = Excel.Workbook(File.Contents(«C:\Users\okurinna\Desktop\Flash report (short version) v3.xlsx» ) , null, true),
Actual_Sheet = Источник<[Item="Actual",Kind="Sheet"]>[Data],
#"Удаленные верхние строки» = Table.Skip(Actual_Sheet,4),
#"Повышенные заголовки» = Table.PromoteHeaders(#»Удаленные верхние строки», [PromoteAllScalars=true ] ),
#"Удаленные столбцы» = Table.RemoveColumns(#»Повышенные заголовки»,<"Sum in USD", "Sum in USD with adjustments", "Reported month">),
#"Переименованные столбцы» = Table.RenameColumns(#»Удаленные столбцы»,<<"Adjustments in reported period", "Reported month">>),
#"Переупорядоченные столбцы» = Table.ReorderColumns(#»Переименованные столбцы»,<"Plan/Fact", "Reported month", "Project", "SOW ID ", "Cost/Revenue", "Category", "Article", "Date of operation", "raw data", "Working hours", "Surname Name", "Description", "Original sum", "Original currency", "EUR/USD", "Sum of adjustments in previous period">),
#"Переименованные столбцы1″ = Table.RenameColumns(#»Переупорядоченные столбцы»,<<"Sum of adjustments in previous period", "Sum in USD">>),
#"Строки с примененным фильтром» = Table.SelectRows(#»Переименованные столбцы1″, each ([Sum in USD] <> null)),
#"Удаленные столбцы1″ = Table.RemoveColumns(#»Строки с примененным фильтром»,<"Article">),
#"Добавлен пользовательский объект» = Table.AddColumn(#»Удаленные столбцы1″, «Article», each Text.From([#»Cost/Revenue»]&» adjustments» ] ),
#"Переупорядоченные столбцы1″ = Table.ReorderColumns(#»Добавлен пользовательский объект»,<"Plan/Fact", "Reported month", "Project", "SOW ID ", "Cost/Revenue", "Category", "Article", "Date of operation", "raw data", "Working hours", "Surname Name", "Description", "Original sum", "Original currency", "EUR/USD", "Sum in USD">),
#"Измененный тип» = Table.TransformColumnTypes(#»Переупорядоченные столбцы1″,<<"Reported month", type date>>)
in
#"Измененный тип»

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