GetPivotData в Excel - Съвети за Excel

Току-що сте изградили обобщена таблица в Excel. Щракнете извън обобщената таблица. Изграждате формула на Excel. Копирате тази формула до всички редове в обобщената таблица. Изчислението отчита грешен отговор за всички, освен за първия ред на обобщената таблица. Току-що сте ужилени от генерирането на "функция" на GetPivotData. Нека да разгледаме какво е това и как да го предотвратим.

Просто предотвратете проблема с GetPivotData - бързият метод

Най-бързият начин да попречите на Excel да генерира GetPivotData е да въведете формулата си, без да използвате мишката или клавишите със стрелки. Ако просто напишете =E5/D5, тогава Excel ще създаде "нормална" относителна формула, която може да бъде копирана до всички редове, съседни на обобщената таблица.

Предотвратяване на проблема GetPivotData - Постоянният метод

Има скрита настройка, която да спре Excel да генерира GetPivotData. Той е по-скрит в Excel 2003, отколкото в Excel 2007.

В Excel 2007 изпълнете следните стъпки:

• Създайте обобщена таблица в Excel 2007
• Уверете се, че активната клетка е вътре в осевата таблица
• Погледнете отляво на раздела „Опции на инструментите за обобщена таблица“ на лентата. Има бутон Опции. Не натискайте бутона Опции! В дясната страна на бутона Опции има стрелка за падащо меню. Кликнете върху стрелката за падащо меню. Премахнете отметката от Генериране на GetPivotData.
• Вече можете да въвеждате формули с помощта на мишката, клавишите със стрелки или въвеждането.

В Excel 2003 и по-стари изпълнете следните стъпки:

• Изберете Инструменти, Персонализиране
• В диалоговия прозорец Персонализиране има три раздела. Изберете раздела Команди в центъра.
• В лявото списъчно поле изберете 7-ми елемент, Данни
• В дясното списъчно поле превъртете почти до дъното. 8-мата икона в края на списъка е Генериране на GetPivotData. Плъзнете тази икона от списъчното поле и я пуснете в средата на която и да е лента с инструменти.
• Щракнете върху бутона Затвори, за да затворите диалоговия прозорец.
• Щракнете върху иконата, която току-що добавихте в лентата с инструменти на Excel. Това ще изключи функцията. Вече можете да въвеждате формули с помощта на мишката, без да генерирате функции GetPivotData.

Ето един бонус съвет: Презирах тази функция и просто исках да я изключва през цялото време. Добавих иконата към лентата с инструменти, изключих иконата, след което я премахнах от лентата с инструменти. Сега … леко се успокоих. Виждам, че иконата понякога има някои добри приложения. И така, изградих обобщена таблица в Excel 2003 и се уверих, че указателят на клетката е в обобщената таблица. След това използвах диалоговия прозорец за персонализиране, за да плъзгам иконата Генериране на GetPivotData в лентата с инструменти на Pivot Table. Сега - когато съм в обобщена таблица, мога да избера да включа или изключа функцията.

Защо Microsoft направи това? Има ли добра полза за GetPivotData?

Какво трябва да направи GetPivotData? Ясно е, че Microsoft харесва функцията, тъй като са я наложили на милиони нищо неподозиращи хора, които използват обобщени таблици.

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

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

=GETPIVOTDATAвинаги започва с два конкретни аргумента. След това по избор има допълнителни двойки аргументи.

Ето двата задължителни аргумента:

1. Името на поле за данни. Това може да бъде "Сума на приходите" или просто "Приходи".
2. Всяка клетка, която се намира "вътре" в обобщената таблица. Знаете ли, че вашата обобщена таблица има име? Вероятно не сте знаели това, защото не можете да откриете името в интерфейса на Excel. Трябва да отидете на VBA, за да научите името на обобщената таблица. И така - за Microsoft беше по-лесно да ви позволи да идентифицирате обобщената таблица, като посочите която и да е клетка в обобщената таблица. Въпреки че можете да изберете всяка клетка, най-безопасно е да изберете горната лява ъглова клетка. Има шанс вашата обобщена таблица да се свие за определена комбинация от полета на страницата. В този случай използването на горната лява ъглова клетка ще гарантира, че продължавате да сочите вътре в осевата таблица.

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

GetPivotData може да връща само стойности, видими в обобщената таблица

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

• В клетка A2 GETPIVOTDATA връща продажбите на ABC през януари 2004 г. в централния регион. Това е грабването на 80003 от клетка G19.
• Формулата в A6 обаче не успява. Той иска продажби на ABC във всички региони. Осевата таблица показва обща ABC за Централна, обща ABC за Изток, обща ABC за Запад, но никога не показва обща ABC за всички региони, така че резултатът е #REF! грешка.
• Ако завъртите полето на региона нагоре към областта на страницата, формулата в A6 ще започне да работи, но формулата в A2 и A4 ще започне да връща #REF !. Ако изберете Central от падащото меню Region, тогава и четирите формули ще работят.
• Изключването на общите суми в диалоговия прозорец Опции на обобщената таблица ЩЕ доведе до много функции на GetPivotData да започнат да връщат #REF! грешки.