Обобщената таблица има няколко оформления, които предоставят предварително дефинирана структура за отчета, но не можете да персонализирате тези оформления. Ако имате нужда от по-голяма гъвкавост при проектирането на оформлението на отчет с обобщена таблица, можете да преобразувате клетките във формули на работен лист и след това да промените оформлението на тези клетки, като се възползвате напълно от всички функции, налични в работния лист. Можете или да преобразувате клетките във формули, които използват функциите за куб, или да използвате функцията GETPIVOTDATA. Преобразуването на клетки във формули значително опростява процеса на създаване, актуализиране и поддържане на тези персонализирани обобщени таблици.
Когато преобразувате клетки във формули, тези формули имат достъп до същите данни като обобщената таблица и могат да бъдат обновени, за да се видят актуалните резултати. С възможното изключение от филтрите за отчети обаче вече нямате достъп до интерактивните функции на обобщена таблица, като например нивата на филтриране, сортиране или разгъване и свиване.
Забележка: Когато конвертирате обобщена таблица за онлайн аналитична обработка (OLAP), можете да продължите да обновявате данните, за да получите актуални стойности на мярката, но не можете да актуализирате действителните членове, които се показват в отчета.
Научете за често срещаните сценарии за преобразуване на обобщени таблици във формули на работен лист
Следват типични примери за това, което можете да направите, след като конвертирате клетки на обобщена таблица във формули на работен лист, за да персонализирате оформлението на конвертираните клетки.
Пренареждане и изтриване на клетки
Да речем, че имате периодичен отчет, който трябва да създавате всеки месец за служителите си. Трябва ви само поднабор от информацията в отчета и предпочитате да оформите данните по персонализиран начин. Можете просто да премествате и подреждате клетки в оформление за проектиране, което искате, да изтриете клетките, които не са необходими за месечния отчет на служителите, и след това да форматирате клетките и работния лист, така че да отговарят на вашите предпочитания.
Вмъкване на редове и колони
Да речем, че искате да покажете информация за продажбите за предишните две години, разделена по регион и група продукти, и че искате да вмъкнете разширен коментар в допълнителни редове. Просто вмъкнете ред и въведете текста. Освен това искате да добавите колона, която показва продажбите по регион и група продукти, които не са в първоначалната обобщена таблица. Просто вмъкнете колона, добавете формула, за да получите желаните резултати, и след това попълнете колоната надолу, за да получите резултатите за всеки ред.
Използване на няколко източника на данни
Да речем, че искате да сравните резултатите между производствена и тестова база данни, за да се уверите, че тестовата база данни произвежда очаквани резултати. Можете лесно да копирате формулите на клетките и след това да промените аргумента на връзката, така че да сочи към тестовата база данни, за да сравните тези два резултата.
Използване на препратки към клетки за промяна на потребителското въвеждане
Да речем, че искате целият отчет да се променя въз основа на потребителското въвеждане. Можете да промените аргументите във формулите за куб на препратки към клетки в работния лист и след това да въведете различни стойности в тези клетки, за да извлечете различни резултати.
Създаване на неуформатно оформление на редове или колони (наричано още асиметрично отчитане)
Да речем, че трябва да създадете отчет, който съдържа колона от 2008, наречена "Действителни продажби", колона от 2009 г., наречена "Прожектиране на продажби", но не искате други колони. Можете да създадете отчет, който съдържа само тези колони, за разлика от обобщената таблица, която изисква симетрично отчитане.
Създаване на собствени формули за кубове и MDX изрази
Да речем, че искате да създадете отчет, който показва продажбите за конкретен продукт от трима конкретни продавачи за месец юли. Ако сте запознати с MDX изразите и OLAP заявките, можете да въведете формулите за куба сами. Въпреки че тези формули могат да станат доста сложни, можете да опростите създаването и да подобрите точността на тези формули с помощта на "Автодовършване на формули". За повече информация вижте Използване на "Автодовършване на формули".
Забележка: Можете да преобразувате обобщена таблица за онлайн аналитична обработка (OLAP) само с помощта на тази процедура.
-
За да запишете обобщената таблица за бъдеща употреба, ви препоръчваме да направите копие на работната книга, преди да конвертирате обобщената таблица, като щракнете върху Файл > Запиши като. За повече информация вижте Записване на файл.
-
Подгответе обобщената таблица, така че да можете да намалите пренареждането на клетките след конвертирането, като направите следното:
-
Сменете с оформление, което прилича най-много на оформлението, което искате.
-
Взаимодействайте с отчета, като например филтриране, сортиране и препроектиране на отчета, за да получите желаните резултати.
-
-
Щракнете върху обобщената таблица.
-
В раздела Опции , в групата Инструменти щракнете върху Инструменти за OLAP и след това щракнете върху Преобразуване във формули.
Ако няма филтри за отчети, операцията за конвертиране завършва. Ако има един или повече филтри за отчети, се показва диалоговият прозорец Преобразуване във формули .
-
Решете как искате да конвертирате обобщената таблица:
Преобразуване на цялата обобщена таблица
-
Поставете отметка в квадратчето Конвертирай филтрите на отчета .
Това преобразува всички клетки във формули на работен лист и изтрива цялата обобщена таблица.
Конвертирайте само етикетите на редовете, етикетите на колоните и областта на стойностите на обобщената таблица, но запазете филтрите за отчети
-
Уверете се, че квадратчето за отметка Преобразувай филтрите на отчета е изчистено. (Това е настройката по подразбиране.)
Това преобразува всички клетки с етикети на редове, етикети на колони и области на стойности във формули на работни листове и запазва оригиналната обобщена таблица, но само с филтрите на отчета, така че да можете да продължите да филтрирате с помощта на филтрите на отчета.
Забележка: Ако форматът на обобщената таблица е версия 2000-2003 или по-стара версия, можете да конвертирате само цялата обобщена таблица.
-
-
Щракнете върху Преобразуване.
Операцията за конвертиране първо обновява обобщената таблица, за да гарантира, че се използват актуални данни.
Докато се извършва конвертирането, се показва съобщение в лентата на състоянието. Ако операцията отнема много време и предпочитате да я конвертирате по друг път, натиснете ESC, за да отмените операцията.
Забележки:
-
Не можете да преобразувате клетки с филтри, приложени към нива, които са скрити.
-
Не можете да преобразувате клетки, в които полетата имат потребителско изчисление, създадено чрез раздела "Показвай стойностите като " на диалоговия прозорец "Настройки на поле за стойности ". (В раздела Опции , в групата Активно поле щракнете върху Активно поле и след това щракнете върху Настройки на поле за стойности.)
-
За клетки, които се конвертират, форматирането на клетки се запазва, но стиловете на обобщена таблица се премахват, защото тези стилове могат да се прилагат само към обобщени таблици.
-
Можете да използвате функцията GETPIVOTDATA във формула, за да преобразувате клетки на обобщена таблица във формули на работен лист, когато искате да работите с не-OLAP източници на данни, когато предпочитате да не надстроите веднага до новия формат на обобщена таблица версия 2007 или когато искате да избегнете сложността на използването на функциите за куб.
-
Уверете се, че командата Генериране на GETPIVOTDATA в групата Обобщена таблица в раздела Опции е включена.
Забележка: Командата Генериране на GETPIVOTDATA задава или изчиства опцията Използвай функциите GETPIVOTTABLE за препратки към обобщена таблица в категорията Формули на секцията Работа с формули в диалоговия прозорец Опции на Excel .
-
В обобщената таблица се уверете, че клетката, която искате да използвате във всяка формула, е видима.
-
В клетка на работен лист извън обобщената таблица въведете формулата, до която искате да включите данни от отчета.
-
Щракнете върху клетката в обобщената таблица, която искате да използвате във вашата формула в обобщената таблица. Към вашата формула се добавя функция за работен лист GETPIVOTDATA, която извлича данните от обобщената таблица. Тази функция продължава да извлича правилните данни, ако оформлението на отчета се промени или ако обновите данните.
-
Завършете въвеждането на формулата и натиснете КЛАВИША ENTER.
Забележка: Ако премахнете някоя от клетките, към които има препратка във формулата GETPIVOTDATA, от отчета, формулата връща #REF!.
