Преобразуване на клетки на обобщена таблица във формули на работен лист

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

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

Научете за често срещаните сценарии за преобразуване на обобщени таблици във формули на работен лист

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

Пренареждане и изтриване на клетки 

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

Вмъкване на редове и колони 

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

Използване на няколко източника на данни 

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

Използване на препратки към клетки за промяна на потребителското въвеждане 

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

Създаване на нееднакво оформление на редовете или колоните (наричано също асиметрично отчитане) 

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

Създаване на собствени формули за куб и MDX изрази 

Да речем, че искате да създадете отчет, който показва продажбите за конкретен продукт от трима конкретни продавачи за месец юли. Ако сте запознати с MDX изразите и OLAP заявките, можете да въведете формулите за куб сами. Макар че тези формули може да станат доста сложни, можете да опростите създаването и подобрите точността им с помощта на "Автодовършване на формули". За повече информация вижте "Използване на автодовършване на формули".

Преобразуване на клетки във формули, които използват функции за куб

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

2. Подгответе обобщената таблица, за да можете да намалите пренареждането на клетките след конвертирането, като направите следното:

   • Сменете с оформление, което най-много прилича на желаното от вас оформление.
   • Извършете работа с отчета, като например филтриране, сортиране и преработване на отчета, за да получите желаните резултати.

3. Щракнете върху обобщената таблица.

4. В раздела "Опции ", в групата "Инструменти " щракнете върху "OLAP инструменти" и след това щракнете върху "Преобразуване във формули".
   Ако няма филтри за отчети, операцията за конвертиране завършва. Ако има един или повече филтри за отчети, се показва диалоговият прозорец "Преобразуване във формули ".

5. Решете как искате да преобразувате обобщената таблица:
   Конвертиране на цялата обобщена таблица 

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

   • Уверете се, че квадратчето за отметка "Преобразуване на филтри за отчети " не е отметнато. (Това е настройката по подразбиране.)
     Това преобразува всички клетки за етикети на редове, етикети на колони и област за стойности във формули на работен лист и запазва оригиналната обобщена таблица, но само с филтрите за отчети, така че можете да продължите да филтрирате, като използвате филтрите за отчети.

     Забележка

     Ако форматът на обобщена таблица е версия 2000-2003 или по-стара версия, можете да конвертирате само цялата обобщена таблица.

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

   Забележка

   • Не можете да конвертирате клетки с филтри, приложени към нива, които са скрити.
   • Не можете да преобразувате клетки, в които полетата имат потребителско изчисление, създадено чрез раздела "Показвай стойностите като " на диалоговия прозорец "Настройки на полетата за стойности ". (В раздела "Опции ", в групата "Активно поле " щракнете върху "Активно поле" и след това върху "Настройки на полета със стойности".)
   • За клетки, които се конвертират, форматирането на клетките се запазва, но стиловете на обобщена таблица се премахват, тъй като тези стилове могат да се прилагат само към обобщени таблици.

Преобразуване на клетки с помощта на функцията GETPIVOTDATA

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

1. Уверете се, че командата "Генериране на GETPIVOTDATA " в групата "Обобщена таблица " на раздела "Опции " е включена.

   Забележка

   Командата "Генериране на GETPIVOTDATA" задава или изчиства опцията "Използване на функции за обобщена таблица за препратки към обобщени таблици" в категорията "Формули" на секцията "Работа с формули" в диалоговия прозорец "Опции на Excel".

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

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

4. Щракнете в обобщената таблица върху клетката, която искате да използвате във вашата формула, в обобщената таблица. Към вашата формула се добавя функция за работен лист GETPIVOTDATA, която извлича данните от обобщената таблица. Тази функция продължава да извлича правилните данни, ако оформлението на отчета се промени или ако обновите данните.

5. Завършете въвеждането на формулата и натиснете клавиша ENTER.

Проблем: Клетките на обобщената таблица не могат да се конвертират във формули на работен лист