Excel. Обобщена таблица, базирана на няколко листа
Excel. Обобщена таблица, базирана на множество листа
Ако се сблъскате с необходимостта да създадете обобщена таблица въз основа на данни, поставени на няколко листа от една и съща книга (или различни книги), ще бъдете разочаровани. По стандартния начин Excel прави това "през едно място" ... Л
Да, Excel има такава опция в съветника за обобщена таблица, но както обикновено (както за обикновените пивоти) работи само за един набор от данни, например (вижте файла Pivot_sheets_one set. xlsx):
Имате списък с клиенти и техните продажби по тримесечия; данните за едно тримесечие са разположени на отделен лист. Нека създадем обобщена таблица, която консолидира данни за 4 тримесечия.
1. Назовете вашите оригинални масиви от данни; по принцип това не е необходимо, но от друга страна издига работата ви на ново ниво J , улеснява възприемането на информация от другите потребители и също така улеснява извършването на промени / допълнения към изходните данни в бъдеще (как да създадете динамично променящи се именувани масиви, вижте тук):
2. Стартирайте съветника за обобщена таблица (как да пренесете съветника в лентата с инструменти за бърз достъп, вижте тук); изберете опцията "в множество диапазони на консолидация", щракнете върху "следващ":
3. Оставете опцията по подразбиране „Създаване на поле за една страница“
4. Въведете името на първия диапазон, щракнете върху „Добавяне“:
5. Добавете и четирите диапазона, щракнете върху "Напред":
6. Оставете опцията по подразбиране "нов лист", щракнете върху "Край":
Създадена е обобщена таблица, базирана на данни, поставени на четири листа. Основната таблица (както обикновено) съдържа четири области. Но имената на полетата не съвпадат с това, което е в оригиналаданни:
Филтърът за отчет вместо имена на листове (q1, q2…) съдържа номера на обекти:
Имена на линии вместо "Клиент" - "Линия"; името на колоната "Продажби" е скрито в името "Колона":
В противен случай можете да работите с полученото резюме както обикновено. Например:
Можете да експериментирате - нищо няма да се промени, ако изходните данни (листове) са поставени в няколко файла.
Нека разгледаме как работи обобщена таблица, консолидираща данни от няколко листа, въпреки факта, че данните съдържат повече от един набор. Например номерът на поръчката за доставка, датата, артикулът и количеството и всички данни са поставени на три листа (вижте файла Summary_sheets_several sets.xlsx):
1. Наименувайте вашите оригинални масиви от данни.
2. Създайте обобщена таблица, както е описано по-горе
Ето какво се случи:
Това резюме ви позволява да определите броя на артикулите и броя на бройките за поръчки, но нито едно от полетата на колоната (нито артикулът, нито датата) не може да бъде „изтеглено“ в областта на реда, тоест не можете (както при работа с обикновено резюме) да определите броя на зарежданията / артикулите / бройките на ден; броя на пратките, които имат определен артикул и др.
Решението е предложено от Кирил Лапин (вижте тук)
Изтеглете zip файл (в два файла на Excel 2007 с поддръжка на макроси: от Кирил Лапин и с моя набор от данни) резюме. цип
1) Намерих начин да актуализирам данните не чрез макрос, а чрез контекстното меню на самата таблица (въпреки че всичко е малко на едно място)
2) Ако се опитате да създадете обобщена таблица с файл, свързан към себе си като към външни данни по стандартен начин, тогава Excel не ви позволява да направите това нито ръчно, нито с макрос
3) Може да бъде измаменExcel, ако файлът е преименуван или преместен в друга папка
1) създайте дублиран файл с базата данни Book1.xls в същата папка като основния файл
2) в основния файл създаваме обобщена таблица с връзка към външни данни Book1.xls
3) променете пътя и името на файла в низа за връзка на основния
Ето какво получих след прилагане на кода на Кирил:
Можете да работите с обобщената таблица както обикновено, като плъзнете и пуснете полета в желаната област. Една забележка: пуснах кода на два компютъра, на един, по някаква неизвестна за мен причина, не искаше да работи ...