Елементи на обобщената таблица. Какво ще правим с получения материал. Показване и скриване на подробни данни

Основните елементи на обобщените таблици. Преди да продължа с тази работа, мисля, че не би било излишно да се каже от какви елементи са направени обобщените таблици и за това разгледайте примера на фигура 2. Полето на страницата е поле в оригиналния списък или таблица, поставено в зона за ориентация на страницата обобщена таблица.

В този пример Scope е поле на страница, което може да се използва за обобщение по региони. Когато посочите друг елемент в полето на страницата, обобщената таблица се преизчислява, за да се покажат общите суми, свързани с този елемент. Стойност на полето на страницата - Елементите на полето на страницата комбинират записите или стойностите на поле или колона в оригиналния списък на таблицата. С други думи, това е чрез превключване на това поле, всъщност всеки път, когато има преход към нов източник на данни за нов регион.

Например можете да дефинирате форматиране за множество нива на данни, има няколко нива на междинни суми в обобщена таблица и т.н. Също така, точно като стилове на таблици, можете да създадете свои собствени стилове, които да отговарят на вашите специфични нужди, било то корпоративни насоки или индивидуални предпочитания. Осевите таблици обаче са по-сложни от таблиците, така че са налични повече елементи на таблици за дефиниране на форматирането.

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

Само за неизменяема обобщена таблица.
Само за редактируема обобщена таблица.
Ако броят на записите е надвишен, ще се появи съответното съобщение.

Можете да използвате отчет за обобщена таблица, за да обобщавате, анализирате, изследвате и представяте обобщени данни.

Редови полета са полета в оригиналния списък или таблица, които са поставени в областта на редовете на обобщената таблица. В този пример Products и Vendor са низови полета. Вътрешните полета на реда, например Salesperson, съвпадат точно с полетата за външни редове на областта за данни, например вътрешните групи на продукти. По този начин тук се проследява определено правило, всеки елемент от поле от по-високо ниво, като че ли, усъвършенства всички полета от по-ниско ниво, параметърът на продукта ограничава всички данни под него - продавачи на продукти, данни за продажбите на продукти от тези продавачи. Полето на колона е поле в списък с източници или таблица, което се поставя в областта на колоната.

Ако имате различна версия, външният ви вид може да е малко по-различен, но освен ако не е посочено друго, функционалността е същата. Осевата таблица е интерактивен начин за бързо обобщаване на големи количества данни. Можете да използвате обобщена таблица, за да анализирате подробно числовите данни и да отговорите на неочаквани въпроси относно вашите данни. Осевата маса е специално проектирана за.

Подтип и обобщени числови данни, обобщавайте данни по категория и подкатегория и създавайте персонализирани изчисления и формули. Разгънете и свийте слоевете данни, за да фокусирате резултатите си и да пробиете подробностите от обобщените данни по области на интерес. Преместете редове в колони или колони в редове, за да видите различни обобщения на изходните данни. Филтриране, сортиране, групиране и условно форматиране най-полезното и интересно подмножество от данни, което ви позволява да се съсредоточите само върху информацията, която искате. Представяне на кратки, ангажиращи и коментирани онлайн отчети или отпечатани отчети. Запитвайте големи количества данни по много удобни за потребителя начини. ... Например, тук е прост списък с разходите на домакинствата вляво и обобщена таблица, базирана на списъка вдясно.

В този пример Quarters е поле на колона, което включва два елемента на полето KB2 и KB3. Вътрешните полета на колоните съдържат елементи, съответните области с данни са външните полета на колоните, разположени над вътрешните полета. Примерът показва само едно поле на колона.

Областта с данни е частта от обобщената таблица, която съдържа обобщените данни. Клетките в областта с данни показват суми за елементите на полета в редове или колони. Стойностите във всяка клетка на областта с данни съответстват на първоначалните данни. В този пример клетка C6 обобщава всички записи на сурови данни, които съдържат едно и също име на продукта, дистрибутор и конкретна четвърт Meat, Myastorg LLP и KB2. Елементите на полето са подкатегории на поле с обобщена таблица. В този пример стойностите Месо и Морски дарове са полеви елементи в полето Продукти.

Начини за работа с обобщена таблица. След като създадете обобщена таблица, изберете нейния източник на данни, подредете полетата в списъка с полета на обобщената таблица и изберете първоначално оформление, можете да изпълнявате следните задачи, докато работите с отчет за обобщената таблица. Проверете данните, като следвате тези стъпки.

Разгънете и свийте данните и покажете основна информация, свързана със стойности.

Сортиране, филтриране и групиране на полета и елементи.
Модифицирайте обобщените функции и добавете персонализирани изчисления и формули.

Променете оформлението на формуляра и оформлението на полетата, като следвате тези стъпки.

Елементите на полето представляват записи в поле или колона в изходните данни. Елементите на полетата се показват като заглавия на редове или колони и в падащия списък за полета на страницата. Полето с данни е поле в списък с източници или таблица, което съдържа данни. В този пример полето Количество на поръчката е поле с данни, което обобщава първоначалните данни в полето или колоната Поръчка. Полето за данни обикновено обобщава група от числа като статистически данни или количества продажби, въпреки че текущите данни могат да бъдат и текстови.

Добавяне, промяна и премахване на полета.
Променете реда на полета или елементи.

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

Показване на междинни суми над или под редовете им.
Регулирайте ширините на колоните при опресняване.
Преместете полето на колоната в областта на реда или от полето на реда в областта на колоната.
Комбинирайте или разширете клетки за външни редове и колони.

Променете показването на интервали и грешки, като следвате тези стъпки.

По подразбиране обобщената таблица обобщава текстови данни, използвайки функцията за обобщаване на общите стойности, и числови данни, използвайки функцията за обобщаване на обобщението. Промяна на структурата на обобщена таблица Външен вид Можете да редактирате осевата таблица директно върху листа, като плъзнете имената на бутоните на полето или елементите на полето. Можете също така да промените реда на елементите в полето, за което трябва да изберете името на елемента и след това да зададете показалеца на границата на клетката.

Променете начина, по който се показват грешките и празните клетки.
Променете начина на показване на елементи и етикети без данни.

Променете формата, като следвате тези стъпки.

Форматирайте ръчно и условно клетки и диапазони.
Променете общия стил на формата на обобщената таблица.
Променете числовия формат за полетата.

Можете също да промените типа на диаграмата и други параметри като заглавия, разположение на легендата, етикети с данни, позиция на диаграмата и други. Има обаче някои разлики. Линиите на тенденции, етикетите с данни, лентите за грешки и други промени в наборите от данни не се запазват.

Когато показалеца се промени на стрелка, плъзнете клетката на полето на ново място. За да премахнете поле, плъзнете бутона за поле извън областта с подробности. Изтриването на поле ще скрие всички зависими стойности в обобщената таблица, но няма да повлияе на първоначалните данни. Ако трябва да използвате всички предоставени инструменти за структуриране на обобщената таблица или ако текущата таблица не е включвала преди това всички полета на изходните данни, трябва да използвате съветника за обобщена таблица.

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

Ако обобщената таблица съдържа голяма група полета на страница, те могат да бъдат поставени в редове или колони. Освен това трябва да се отбележи, че промяната на структурата на обобщената таблица не засяга първоначалните данни.

Край на работата -

Тази тема принадлежи към раздела:

Обработка на таблична информация с помощта на обобщени таблици с помощта на Microsoft Excel

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

Можете да изтегляте данни от външен източник на данни, като база данни, онлайн куб за анализ или текстов файл... Например можете да запишете база данни с записи за продажби, която искате да обобщите и анализирате. Вижте Преобразуване на елементи от обобщената таблица във формули на работни листове. Например данни от релационни бази данни или текстови файлове.

Използване на друга обобщена таблица като източник на данни

Всяка нова обобщена таблица изисква допълнителна памет и дисково пространство. Когато обаче използвате съществуваща обобщена таблица като източник за нова в същата работна книга, и двете използват един и същ кеш. Докато използвате повторно кеша, размерът на работната книга се намалява и в паметта се съхраняват по-малко данни. Изисквания за местоположение За да използвате една обобщена таблица като източник за друга, и двете трябва да са в една и съща работна книга. Ако оригиналната обобщена таблица е в друга работна книга, копирайте източника на мястото на работната книга, където искате да се появи новата.

Ако имате нужда от допълнителен материал по тази тема или не сте намерили това, което търсите, препоръчваме да използвате търсенето в нашата работна база:

Какво ще направим с получения материал:

Ако този материал се оказа полезен за вас, можете да го запазите на страницата си в социалните мрежи:

Всички теми в този раздел:

Създаване на обобщена таблица
Създайте обобщена таблица. Преди да създадете обобщена таблица, първо трябва да зададете данните за тази таблица, като изберете част от книгата, страниците и ако това е база данни или списък, след това n

Промените засягат обобщените таблици. Когато групирате или разгрупирате членове или създавате изчислени полета или изчислени членове в едно, и двете се засягат. Ако имате нужда от обобщена таблица, която е независима от друга, можете да създадете нова въз основа на оригиналния източник на данни, вместо да копирате оригиналната обобщена таблица.

Модифициране на изходните данни на съществуващ отчет на обобщената таблица

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

Подробности и действия на обобщената таблица
Подробностите за обобщената таблица и действията, извършени върху нея. Подробните данни са подкатегория на обобщена таблица. Тези елементи на обобщената таблица са уникални елементи на таблицата или с

Показване или скриване на подробни данни от обобщената таблица
Показване или скриване на подробни данни от обобщената таблица. За да покажете подробни данни, които преди това са били скрити, или да скриете съществуващи данни, трябва да изберете полевия елемент, подробни данни

Показва нови данни, причинени от актуализацията. Опресняването на обобщената таблица може също да промени данните, налични за показване. Можете да видите всички нови полета в списъка с полета и да добавите полета към отчета. Проблемът е, че началната информация не прави разлика между две години, а просто има поле с дата. Ще го решим по два начина.

Първо, ще използваме матрични формули, и второ, ще направим това, като създадем елемент, изчислен в обобщена таблица. Можете да проверите публикацията, за която говорихме. Започваме с малка база данни с две колони.

Общо 24 записа Количество.
Това е стойността на някаква икономическа стойност.
Това може да бъде например фактуриране от фирма.

Разделителна способност с матрични формули.

Показване или скриване на елементи на полета на обобщена таблица
Показване или скриване на елементи на полета на обобщена таблица. Ако трябва да покажете или скриете елемент на поле, за това първо трябва да изберете полето, в което искате да покажете или

Показване на максимален или минимален брой елементи в полето на обобщена таблица
Показва максималния или минималния брой елементи в полето на обобщена таблица. За да се покажат максималните или минималните елементи, е необходимо да изберете полето, в което елементите ще се показват в бъдеще.

В същия лист 1 ще решим нашия случай с помощта на матричната функция. За да разберете как да работите с такива функции, можете да се обърнете към следващата публикация. Формулата е в къдрави скоби, за да покаже, че е матрична формула. Той се копира и процентът на вариация се получава за всеки месец.

Добавихме колони за година и месец. Не е много ортодоксално да добавяте нови полета към базата данни, които се изчисляват с информация, която вече се съдържа в самата база данни. Тези формули, приложени към валидна дата, ни дават точно месеца и годината на тази дата.

Деактивирайте достъпа до подробности за обобщената таблица
Деактивирайте достъпа до подробности за обобщената таблица. Ако поставите показалеца върху клетка в областта с данни на обобщена таблица и щракнете двукратно, се показва обобщен списък с изходните данни

Групиране и разгрупиране на данни в обобщена таблица
Групиране и разгрупиране на данни в обобщена таблица. В обобщена таблица можете да групирате дати, часове, числа и избрани елементи. Например можете да комбинирате месеци, за да обобщите sq.

Поради тази причина се нуждаехме от графата Дата, за да бъде валидна дата и трябваше да вземем първия ден на всеки от въпросните месеци. Валидната дата трябва да посочва деня, месеца и годината, но след това във формата, който даваме, искаме само месеца и годината. Те са валидни, защото въпреки че не виждаме деня, това е форматът, но датата се въвежда като ден-месец-година.

Първо, ние създаваме обобщена таблица, както е показано по-долу. Поставяме етикети на месеца в етикети на редове, Година в колони на колони и Сумиране на данни в стойности. Сега можем да вмъкнем изчисления елемент. Ще се появи прозорец, в който можем да изградим нашия дизайнерски елемент.

Групирайте и разгрупирайте елементите в полето на обобщена таблица
Групирайте и разгрупирайте елементите в полето на обобщена таблица. За да групирате и разгрупирате елементи, трябва да изберете групираните елементи. След това щракнете върху бутона Група на панела

Групирайте и разгрупирайте числата в обобщена таблица
Групирайте и разгрупирайте числата в обобщена таблица. За да групирате и разгрупирате номера, изберете номера в полето, където искате да групирате елементите. След това натиснете бутона

Но не е нужно да пишем формула, като въвеждаме години, а избираме елементи с мишката. Новата колона е форматирана като процент и вече сме създали нашата обобщена таблица с изчисления елемент. Можем да създадем списък с всички формули, създадени в обобщената таблица както на изчислените елементи, така и на изчислените полета. За да направите това, поставете курсора си в обобщената таблица и изберете Инструменти за обобщена таблица, формули и Създаване на списък с формули.

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

Групирайте и разгрупирайте дати или часове в обобщена таблица
Групирайте и разгрупирайте дати или часове в обобщена таблица. За да групирате и разгрупирате дата или час, изберете датата или часа в полето, където искате да групирате елемента.

Групиране и разгрупиране на елементите на страницата на обобщената таблица
Групирайте и разгрупирайте елементите на поле на страница с обобщена таблица. Ако обобщената таблица е създадена от външен източник на данни, проверете дали полето на страницата извлича външни данни

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

Опресняване на данни в обобщена таблица
Опресняване на данни в обобщена таблица. За да актуализирате данните в обобщената таблица, трябва да извършите следните операции: Необходимо е да изберете клетката в обобщената таблица, чието съдържание се изисква

Деактивирайте опресняването на обобщената таблица при отваряне на файла на работната книга
Деактивирайте опресняването на обобщената таблица при отваряне на файл на работна книга. За да деактивирате опресняването на обобщената таблица при отваряне на файл, изберете клетка в обобщената таблица, за която няма да се извърши опресняване

Сортиране на данните от обобщената таблица
Сортиране на данните от обобщената таблица. Елементите на полетата с обобщена таблица автоматично се сортират във възходящ ред според името им. Сортирането на елементи по име ви позволява да възстановите оригинала

Обобщаване и обработка на данни от обобщената таблица
Обобщаване и обработка на данните на обобщената таблица. Полетата с данни на обобщената таблица могат да бъдат конфигурирани за изчисления, различни от подразбиращите се във функцията обобщение. За изпълнение на сложни

Използване на общи суми и междинни суми в обобщена таблица
Използване на общи суми и междинни суми в обобщена таблица. Обобщена таблица може да бъде конфигурирана да извършва изчисления с помощта на обобщаваща функция. Обобщаващата функция е видът на изчислението, извършен от

Поставете или премахнете междинни суми в обобщена таблица
Поставете или премахнете междинни суми в обобщена таблица. За да вмъкнете или изтриете междинни суми, трябва да зададете показалеца на бутона на полето, в което искате

Създайте диаграма за обобщена таблица
Създайте диаграма за обобщена таблица. За да създадете диаграма за обобщена таблица, в лентата с инструменти PivotTables изберете командата Select от менюто PivotTable. Провери това

Тази публикация представя колекция от прости и изящни инструменти за обобщени таблици в Excel. Какво се нарича съвети и трикове на английски. Отделете време да прочетете съветите тук. Кой знае, може би най-накрая ще намерите отговора на въпроса, който ви измъчва дълго време?

Съвет 1. Автоматично опресняване на обобщените таблици

Понякога искате обобщените таблици да се актуализират автоматично. Да приемем, че сте създали обобщена таблица за мениджър. Едва ли ще можете редовно да го актуализирате, освен ако мениджърът не ви остави до лаптопа си. Можете да активирате автоматична актуализация обобщена таблица, която да се изпълнява при всяко отваряне на работната книга:

Щракнете с десния бутон върху обобщената таблица и изберете Опции за обобщена таблица.
В диалоговия прозорец, който се появява Опции за обобщена таблица изберете раздел Данни.
Поставете отметка в квадратчето Опресняване при отваряне на файла.

Фигура: 1. Активирайте опцията Опресняване при отваряне на файла

Поле за отметка Опресняване при отваряне на файла трябва да се инсталира за всяка обобщена таблица поотделно.

Изтеглете бележката във формата или, примери във формата (файлът съдържа VBA кода).

Съвет 2: Едновременно актуализирайте всички обобщени таблици в книга

Ако работната книга съдържа множество обобщени таблици, актуализирането им едновременно може да бъде проблематично. Има няколко начина за преодоляване на тези трудности:

Метод 1. За всяка обобщена таблица, включена в работната книга, можете да изберете настройка, която да се опреснява автоматично при отваряне на работната книга (за повече подробности вижте Съвет 1).

Метод 3: Използвайте VBA код, за да актуализирате всички обобщени таблици в работната книга при поискване. Този подход използва метода RefreshAll на обекта Workbook. За да използвате тази техника, създайте нов модул и въведете следния код:

Sub Refresh_All ()

ThisWorkbook.RefreshAll

Съвет 3. Сортирайте артикулите в произволен ред

На фиг. Фигура 2 показва реда на дисплея по подразбиране за региони в обобщена таблица. Регионите са сортирани по азбучен ред: Запад, Север, Среден Запад и Юг. Ако вашите корпоративни правила изискват първо да покажете Западния регион, а след това Средния Запад, Северния и Южния регион, направете ръчно сортиране. Просто влезте в Средния Запад в клетка C4 и натиснете клавиша Въведете... Редът за сортиране на регионите ще се промени.

Съвет 4: Преобразувайте обобщената таблица в твърдо кодирани стойности

Целта на създаването на обобщена таблица е да обобщи и покаже данните в подходящ формат... Оригиналните данни за обобщената таблица се съхраняват отделно, което въвежда някои „режийни разходи“. Преобразуването на обобщената таблица в стойности ще ви позволи да използвате резултатите, получени в нея, без достъп до оригиналните данни или кеша на обобщената таблица. Начинът на трансформиране на обобщената таблица зависи от това дали е засегната цялата таблица или само част от нея.

За да конвертирате част от обобщена таблица, изпълнете следните стъпки:

Изберете копираните данни от обобщената таблица, щракнете с десния бутон и изберете копие (или въведете Ctrl + C на клавиатурата си).
Щракнете с десния бутон на мишката някъде в работния лист и изберете командата Поставете (или въведете Ctrl + V).

Ако трябва да конвертирате цялата обобщена таблица, изпълнете следните стъпки:

Изберете цялата обобщена таблица, щракнете с десния бутон и изберете копие... Ако обобщената таблица не съдържа областта ФИЛТРИ, можете да използвате клавишната комбинация Ctrl + Shift + *, за да изберете областта на обобщената таблица.
Щракнете с десния бутон върху произволно място на листа и изберете опция от контекстното меню Поставете специално.
Изберете опция Стойностите и щракнете Добре.

Добра идея е да изтриете междинните суми, преди да преобразувате обобщената таблица, тъй като те не са много полезни в офлайн набора от данни. За да изтриете всички междинни суми, отидете в менюто Дизайн -\u003e Междинни суми -\u003e Не показвайте междинни суми. За да изтриете конкретни междинни суми, щракнете с десния бутон върху клетката, в която се изчисляват сумите. Изберете елемента от контекстното меню Параметри на полето и в диалоговия прозорец Параметри на полето В глава Резултат изберете радио бутона Не... След като щракнете върху бутона Добре междинните суми ще бъдат изтрити.

Съвет 5. Попълване на празни клетки в полетата LINE

След преобразуване на обобщената таблица листът показва не само стойностите, но и цялата структура на данните на обобщената таблица. Например данните, показани на фиг. 3 са получени от обобщена таблица с таблично оформление.

Фигура: 3. Проблемно е да се използва тази преобразувана обобщена таблица, без да се попълват празни клетки от лявата страна

Имайте предвид, че полетата Регион и Пазар на продажбите поддържа същата структура на редове, както когато тези данни се намират в областта ROWS на обобщена таблица. Excel 2013 има бърз начин запълване на клетките в областта LINE със стойности. Щракнете в областта на обобщената таблица, след което преминете през менюто Конструктор -> Оформление на отчета -> (фиг. 4). След това можете да конвертирате обобщената таблица в стойности, което ще доведе до таблица с данни без интервали.

Фигура: 4. След прилагане на командата Повторете всички етикети на артикули всички празни клетки са запълнени

Съвет 6: Класиране на числови полета в обобщена таблица

Когато сортирате и класирате полета, съдържащи голям брой елементи от данни, не винаги е лесно да се определи численият ранг на елемента от данни, който се анализира. Освен това, ако общата таблица се преобразува в стойности, присвояването на всеки елемент от данни числов ранг, показан в поле с цяло число, значително ще улесни анализа на генерирания набор от данни. Отворете обобщена таблица като тази, показана на фиг. 5. Моля, обърнете внимание, че същият индикатор - Сума по поле Обем на продажбите - показва се два пъти. Щракнете с десния бутон върху втория екземпляр на показателя и изберете командата Допълнителни изчисления -> Сортиране от максимум към минимум (фиг. 6.)

След като създадете ранг, можете да персонализирате надписите на полетата и форматирането (Фигура 14.9). Резултатът ще бъде добър класиран доклад.

Съвет 7: Намалете размера на отчет за обобщена таблица

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

Изтрийте оригиналните данни.Ако работната книга съдържа оригинален набор от данни и обобщена таблица, размерът на файла й се удвоява. Следователно можете спокойно да изтриете оригиналните данни и това изобщо няма да засегне функционалността на вашата обобщена таблица. След изтриване на оригиналните данни не забравяйте да запазите компресираната версия на файла на работната книга. След изтриване на оригиналните данни можете да използвате обобщената таблица както обикновено. Единственият проблем е невъзможността да се актуализира пивотната таблица поради липсата на оригинални данни. Ако имате нужда от някои фонови данни, щракнете двукратно върху пресечната точка на реда и колоната в областта на общите суми (на Фигура 7, това е клетка B18). По този начин Excel зарежда съдържанието на кеша на обобщената таблица в нов работен лист.

Съвет 8: Създайте автоматично разширяващ се диапазон от данни

Със сигурност многократно сте се сблъсквали със ситуации, когато е трябвало да актуализирате отчетите на обобщената таблица ежедневно. Това е най-често необходимо, когато към източника на данни непрекъснато се добавят нови записи. В такива случаи ще трябва да предефинирате използвания преди това диапазон, преди новите записи да бъдат добавени към новата обобщена таблица. Предефинирането на първоначалния диапазон от данни за обобщената таблица е лесно, но когато го правите често, става доста досадно.

Решението на проблема е да преобразувате първоначалния диапазон от данни в таблица дори преди създаването на обобщената таблица. Благодарение на електронни таблици в Excel можете да създадете именен диапазон, който може автоматично да се разширява или свива в зависимост от количеството данни в него. Можете също така да свържете всеки компонент, диаграма, обобщена таблица или формула към диапазон, така че да можете да проследявате промените в набора от данни.

За да приложите описаната техника, изберете изходните данни и след това кликнете върху иконата на таблицата, намираща се в раздела Поставете (фиг. 8) или натиснете Ctrl + T (английски T). Щракнете Добре в прозореца, който се отваря. Обърнете внимание, че въпреки че не е необходимо да замествате обхвата на изходните данни в обобщената таблица, все пак трябва да щракнете върху бутона, когато добавяте изходни данни към обхвата в обобщената таблица. Обнови.

Съвет 9: Сравнете обикновените таблици с обобщена таблица

Ако сравнявате две различни таблици, е удобно да използвате обобщена таблица, което ще спести много време. Да предположим, че имате две таблици, които показват подробности за клиента за 2011 и 2012 г. (Фигура 9). Малките размери на тези таблици са дадени тук само като примери. На практика се използват таблици, които са много по-големи.

Сравнението създава единична таблица, от която се създава обобщена таблица. Уверете се, че имате начин да маркирате данните, свързани с тези таблици. В този пример това използва колоната Фискална година (фиг. 10). След като комбинирате двете таблици, използвайте получения комбиниран набор от данни, за да създадете нова обобщена таблица. Форматирайте обобщената таблица, така че областта на колоната на обобщената таблица да се използва като таг на таблицата (идентификатор, който показва произхода на таблицата). Както е показано на фиг. 11 години са в областта на колоната, а данните за клиентите са в областта на реда. Областта с данни съдържа обема на продажбите за всеки клиент.

Съвет 10: Автоматично филтриране на обобщена таблица

Както знаете, не можете да използвате AutoFilters в обобщените таблици. Има обаче трик за активиране на AutoFilters в обобщена таблица. Принципът зад тази техника е да поставите показалеца на мишката вдясно от последния заглавие на обобщената таблица (клетка D3 на фигура 12), след това отидете на лентата и изберете командата Данни -> Филтър... Отсега нататък във вашата обобщена таблица се появява автофилтър! Например можете да изберете всички клиенти с по-висока от средната скорост на транзакциите. AutoFilters добавят допълнителен слой анализ към обобщената таблица.

Съвет 11. Конвертиране на набори от данни, показани в обобщени таблици

Най-доброто оформление за оригиналните данни, преобразувани в обобщена таблица, е табличното оформление. Този тип оформление има следните характеристики: няма празни редове или колони, всяка колона има заглавка, всяко поле има стойност във всеки ред и колоните не съдържат повтарящи се групи данни. На практика често има набори от данни, които наподобяват този, показан на фиг. 13. Както можете да видите, имената на месеците се появяват на ред в горната част на таблицата, обслужвайки двойната функция на етикетите на колони и действителните данни. В обобщена таблица, създадена от такава таблица, това ще доведе до 12 полета за управление, всяко от които представлява отделен месец.

За да премахнете този проблем, можете да използвате обобщена таблица с няколко консолидирани диапазона като междинна стъпка (вижте подробности). За да конвертирате набор от данни, който има стил на матрица, в набор от данни, който е по-подходящ за създаване на обобщени таблици, следвайте тези стъпки.

Стъпка 1. Комбинирайте всички неколонни полета в една колона.За да създадете обобщени таблици с множество консолидирани диапазони, създайте колона с едно измерение. В този пример всичко, което не е свързано с полето месец, се разглежда като измерение. Следователно полетата Пазар на продажбите и Описание на услугата трябва да се обединят в една колона. За да комбинирате полета в една колона, просто въведете формула, която обединява двете полета, използвайки точка и запетая като разделител. Дайте име на новата колона. Въведената формула се показва в лентата с формули (фиг. 14).

Фигура: 14. Резултат от обединяване на колони Пазар на продажбите и Описание на услугата

След създаване на обединената колона, конвертирайте формулите в стойности. За да направите това, изберете новосъздадената колона, натиснете Ctrl + C и след това изпълнете командата Поставете -> Поставете специално -> Стойностите... Колоните вече могат да бъдат премахнати Пазар на продажбите и Описание на услугата (фиг. 15).

Фигура: 15. Изтрити колони Пазар на продажбите и Описание на услугата

Стъпка 2. Създайте обобщена таблица с множество диапазони на консолидация.Сега трябва да се обадите на съветника за обобщени таблици и диаграми, познат на много потребители от предишните версии на Excel. Натиснете Alt + D + P, за да извикате този съветник. За съжаление тази клавишна комбинация е за английската версия на Excel 2013. В руската версия тя съответства на клавишната комбинация Alt + D + N. Но не работи по неизвестни за мен причини. Можете обаче да пренесете добрия стар съветник на обобщената таблица в лентата с инструменти за бърз достъп, вижте. След като стартирате съветника, изберете радио бутона В множество диапазони на консолидация... Щракнете Освен това... Задайте ключа Създайте полета на страницата и щракнете Освен това... Определете работния обхват и кликнете Свършен (виж детайлите). Ще създадете обобщена таблица (Фигура 16).

Стъпка 3. Щракнете двукратно върху пресечната точка на ред и колона в общия общ ред.В този момент имате обобщена таблица (Фигура 16), която включва няколко диапазона на консолидация, което е почти безполезно. Изберете клетката, която е в пресечната точка на реда и общата колона, и щракнете двукратно върху нея (в нашия пример това е клетка N88). Ще получите нов лист, чиято структура наподобява структурата, показана на фиг. 17. Всъщност този лист е транспонирана версия на оригиналните данни.

Стъпка 4. Разделяне на колоната ред в отделни полета.Остава да се раздели колоната Линия в отделни полета (връщане към първоначалната структура). Добавете една празна колона веднага след колоната Линия... Маркирайте колона A и след това отидете на раздела лента Данни и щракнете върху бутона Текст на колона... На екрана се появява диалогов прозорец. Съветник за разпространение на текст... В първата стъпка изберете радио бутона Разграничени щракнете върху бутона Напред. В следващата стъпка изберете радио бутона точка и запетая и щракнете Свършен... Форматирайте текста, добавете заглавие и превърнете оригиналните данни в таблица, като натиснете Ctrl + T (Фигура 18).

Фигура: 18. Този набор от данни е идеален за създаване на обобщена таблица (сравнете с фиг. 13)

Съвет 12: Включете два числови формата в обобщена таблица

Сега помислете за ситуация, при която нормализиран набор от данни затруднява изграждането на лесна за анализ обобщена таблица. Пример е показан на фиг. 19 е таблица, която включва два различни показателя за всяка търговска зона. Забележете колона D, която идентифицира показателя.

Въпреки че тази таблица е пример за доста добро форматиране, не всичко е толкова добро. Моля, обърнете внимание, че някои показатели трябва да се показват в цифров формат, а други в процент. Но в базата данни източник, полето Стойност е от тип Double. Когато създавате обобщена таблица от набор от данни, не можете да присвоите два различни числови формата на едно и също поле Стойност... Тук има просто правило: едно поле съответства на един цифров формат. Опитът да присвоите числов формат на поле, на което е присвоен процентен процент, ще доведе до превръщането на процентните стойности в обикновени числа, които завършват със знак за процент (Фигура 20).

За да се реши този проблем, се използва персонализиран формат на числа, който форматира всяка стойност по-голяма от 1,5 като число. Ако стойността е по-малка от 1,5, тя се форматира като процент. В диалоговия прозорец Формат на клетка изберете раздел (всички формати) и на полето Тип въведете следния низ за форматиране (фиг. 21): [\u003e \u003d 1,5] $ # ## 0; [<1,5]0,0%

Фигура: 21. Приложете персонализиран формат на числа, където всички числа под 1,5 са форматирани като проценти

Резултатът е показан на фиг. 22. Както можете да видите, сега всеки индикатор е форматиран правилно. Разбира се, рецептата в този съвет не е универсална. По-скоро той посочва посоката, в която да се експериментира.

Съвет 13: Създайте честотно разпределение за осевата таблица

Ако някога сте генерирали честотни разпределения, използвайки функцията на Excel Честота, сигурно знаете, че това е много трудна задача. Освен това, след промяна на диапазоните от данни, всичко трябва да започне отначало. В този раздел ще научите как да създавате прости честотни разпределения с помощта на проста обобщена таблица. Първо създайте обобщена таблица с данните в областта на редовете. Обърнете внимание на фиг. 23, където полето е в областта на реда Сила на звука продажби.

Щракнете с десния бутон върху която и да е стойност в областта Редове и изберете опцията от контекстното меню Група... В диалоговия прозорец Групиране (Фиг. 24) определят стойностите на параметрите, които определят началото, края и стъпката на честотното разпределение. Щракнете върху OK.

Фигура: 24. В диалоговия прозорец Групиране коригирайте параметрите на честотното разпределение

Ако добавите поле към обобщената таблица Клиент (Фиг. 25), получаваме честотното разпределение на клиентските транзакции спрямо размера на поръчките (в долари).

Фигура: 25. Сега имате на разположение разпределението на клиентски транзакции според размерите на поръчките (в долари)

Предимството на тази техника е, че филтърът на отчетите на обобщената таблица може да се използва за интерактивно филтриране на данни въз основа на други колони, като напр. Регион и Пазар на продажбите... Потребителят също така има възможност за бързо регулиране на интервалите за разпределение на честотата, като щракнете с десния бутон върху произволен номер в областта на реда и след това изберете опция Група... За по-голяма яснота на представянето може да се добави обобщена диаграма (фиг. 26).

Съвет 14. Използване на обобщена таблица за разпределяне на набор от данни по листове на работни листове

Анализаторите често трябва да създават различни отчети за обобщената таблица за всеки регион, зона за продажби, мениджър и т.н. Тази задача обикновено включва дълъг процес на копиране на обобщената таблица на нов лист и след това модифициране на полето на филтъра, за да отрази съответния регион и мениджър. Този процес е ръчен и се повтаря за всеки анализ. Но всъщност можете да възложите създаването на отделни обобщени таблици на Excel на външни изпълнители. В резултат на прилагането на параметъра отделно обобщена таблица се създава автоматично за всеки елемент в областта на филтърните полета. За да използвате тази функция, просто създайте обобщена таблица, която включва поле за филтър (Фигура 27). Поставете курсора навсякъде в осевата таблица и в раздела Анализ в отборната група Осева маса кликнете върху падащото меню Параметри (фиг. 28). След това кликнете върху бутона Показване на страниците за филтриране на отчета.

Фигура: 28. Щракнете върху бутона Показване на страниците за филтриране на отчета

В диалоговия прозорец, който се появява (Фигура 29), можете да изберете поле за филтър, за което ще бъдат създадени отделни обобщени таблици. Изберете подходящото поле за филтър и щракнете Добре.

Фигура: 29. Диалогов прозорец Показване на страници с филтри за отчети

За всеки елемент от полето на филтъра ще бъде създадена обобщена таблица, поставена на отделен лист (фиг. 30). Имайте предвид, че разделите на листа се именуват по същия начин като елементите в полето за филтриране. Имайте предвид, че параметърът Показване на страници с филтри може да се приложи към филтриращите полета едно по едно.

Съвет 15: Използване на обобщена таблица за разпределяне на набор от данни в отделни работни книги

В съвет 14 използвахме специална опция за разделяне обобщени таблици от пазарите на продажби на различни листове от работната книга. Ако трябва да се разделите първоначални данни за различни пазари в отделни книги можете да използвате малко VBA код. Първо, поставете полето, върху което искате да филтрирате, в областта за филтриране на полета. Поставете полето Обем на продажбите в диапазона от стойности (фиг. 31). Предложеният VBA код избира на свой ред всеки елемент на ФИЛТЪР и извиква функцията Покажи детайличрез създаване на нов лист с данни. След това този лист се записва в нова работна книга

Код VBA.

Sub ExplodeTable ()

Затъмнете PvtItem като PivotItem

Затъмнете PvtTable като обобщена таблица

Dim strfield като PivotField

'Променете променливите според скрипта

ConststrFieldName \u003d "Пазар"<—Изменение имени поля

Const strTriggerRange \u003d "A4" '<—Изменение диапазона триггера

'Променете името на обобщената таблица (ако е необходимо)

SetPvtTable \u003d ActiveSheet.PivotTables ("PivotTable1") '<—Изменение названия сводной

‘Циклирайте през всеки елемент от избраното поле

За всеки PvtItem в PvtTable.PivotFields (strFieldName) .PivotItems

PvtTable.PivotFields (strFieldName) .CurrentPage \u003d PvtItem.Name

Обхват (strTriggerRange) .ShowDetail \u003d Вярно

„Присвояване на име на временен лист

ActiveSheet.Name \u003d "TempSheet"

„Копиране на данни в нова работна книга и изтриване на временен лист

ActiveSheet.Cells.Copy

ActiveSheet.Paste

Cells.EntireColumn.AutoFit

Application.DisplayAlerts \u003d Невярно

ActiveWorkbook.SaveAs _

Име на файла: \u003d ThisWorkbook.Path & "\\" & PvtItem.Name & ".xlsx"

ActiveWorkbook.Затваряне

Sheets ("Tempsheet"). Изтриване

Application.DisplayAlerts \u003d Вярно

Въведете този код в нов VBA модул. Проверете стойностите на следните константи и променливи и ги променете, ако е необходимо:

Const strFieldName. Името на полето, използвано за разделяне на данни. С други думи, това е поле, което се вписва в областта на филтъра / страниците на обобщената таблица.
Const strTriggerRange. Клетка за задействане, която съхранява единично число от областта с данни на обобщената таблица. В нашия случай задействащата клетка е A4 (вж. Фиг. 31).

В резултат на изпълнението на кода на VBA данните за всяка зона за продажби ще бъдат записани в отделна работна книга.

Бележка по книгата на Йелен, Александър. ... Глава 14.