Мразите ли функцията на GETPIVOTDATA на Excel? Защо се появява? Как можете да го предотвратите? Има ли добра употреба за GETPIVOTDATA?
Повечето хора се сблъскват за първи път с GETPIVOTDATA, когато се опитват да изградят формула извън обобщена таблица, която използва числа в обобщената таблица. Например този процент на отклонение няма да се копира до останалите месеци поради вмъкване на функции на GETPIVOTDATA в Excel.
Excel вмъква GETPIVOTDATA по всяко време, когато използвате мишката или клавишите със стрелки, за да насочите към клетка в обобщената таблица, докато изграждате формула извън обобщената таблица.
Между другото, ако не искате да се показва функцията GETPIVOTDATA, просто напишете формула като = D5 / C5-1, без да използвате мишката или клавишите със стрелки, за да сочите към клетки. Тази формула се копира без никакви проблеми.
Ето набор от данни, който съдържа един номер на план на месец за магазин. Има и действителни продажби на месец на магазин за месеците, които са завършени. Вашата цел е да създадете отчет, който да показва факти за завършените месеци и план за бъдещите месеци.
Изградете обобщена таблица с Store в ROWS. Поставете месец и въведете в КОЛОНИТЕ. Получавате отчета, показан по-долу, с действителен януари, план за януари и напълно безсмисления план за януари Actual +.
Ако изберете месечна клетка и отидете на Настройки на полето, можете да промените междинните суми на Няма.
Това премахва безполезния план Actual +. Но все пак трябва да се отървете от колоните на плана за януари до април. Няма добър начин да направите това вътре в осевата таблица.
И така, вашият месечен работен процес става:
- Добавете действителните данни за новия месец към набора от данни.
- Изградете нова обобщена таблица от нулата.
- Копирайте обобщената таблица и поставете като стойности, така че тя вече не е обобщена таблица.
- Изтрийте колоните, които не са ви необходими.
Има по-добър начин. Следващата много малка фигура показва нов работен лист на Excel, добавен към работната книга. Всичко това е само прав Excel, без обобщени таблици. Единствената магия е функцията IF в ред 4, която превключва от Actual към Plan въз основа на датата в клетка P1.
Първата клетка, която трябва да бъде попълнена, е януари, Actuals за Baybrook. Кликнете в тази клетка и въведете знак за равенство. С помощта на мишката се придвижете обратно до осевата таблица. Намерете клетката за актуални януари за Baybrook. Кликнете върху тази клетка и натиснете Enter. Както обикновено, Excel създава една от онези досадни GETPIVOTDATA функции, които не могат да бъдат копирани.
Но днес, нека изучим синтаксиса на GETPIVOTDATA.
Първият аргумент по-долу е числовото поле "Продажби". Вторият аргумент е клетката, където се намира обобщената таблица. Останалите двойки аргументи са име на поле и стойност. Виждате ли какво направи автоматично генерираната формула? Тя е кодирана с твърдо „Baybrook“ като името на магазина. Ето защо не можете да копирате тези автоматично генерирани формули GETPIVOTDATA. Те всъщност твърдо кодират имена във формули. Въпреки че не можете да копирате тези формули, можете да ги редактирате. В този случай би било по-добре, ако редактирате формулата, за да сочи към клетка $ D6.
Ето формулата, след като я редактирате. Изчезнаха "Baybrook", "Jan" и "Actual". Вместо това сочите към $ D6, E $ 3, E $ 4.
Копирайте тази формула и след това изберете Специално поставяне, Формули във всички останали цифрови клетки.
Сега ето вашият годишен работен процес:
- Изградете грозна въртяща таблица, която никой никога няма да види.
- Настройте работния лист на отчета.
Всеки месец трябва:
- Поставете нови фактически данни под данните.
- Опреснете грозната ос.
-
Променете клетка P1 на отчетен лист, за да отрази новия месец. Всички номера се актуализират.
Променете клетка P1
Трябва да признаете, че използването на обикновен отчет, който извлича числа от обобщена таблица, ви дава най-доброто от двата свята. Можете да форматирате отчета по начини, по които не можете да форматирате обобщена таблица. Празни редове са добре. Можете да имате символи за валута на първия и последния ред, но не и между тях. Получавате двойно подчертаване и под общите суми.
Благодаря на @iTrainerMX, че предложи тази функция.
Гледам видео
- GetPivotData се случва, когато формула сочи вътре в обобщена таблица
- Докато първоначалната формула е правилна, не можете да копирате формулата
- Повечето хора мразят getpivotdata и искат да ги предотвратят
- Метод 1: Изградете формула без мишката или клавишите със стрелки
- Метод 2: Изключете GetPivotData за постоянно, като използвате падащото меню до опциите
- Но има полза за GetPivotData
- Вашият мениджър иска отчет с актуални данни за последните месеци и бюджет за бъдещето
- Нормалният работен процес ще ви накара да създадете обобщена таблица, да конвертирате в стойности, да изтриете колони
- Премахване на междинни суми, за да се предотврати актуален план за януари с помощта на настройките на полето
- Вместо това създайте обобщена таблица с данни „твърде много“
- Използвайте добре форматиран работен лист на отчета
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- От първата клетка с данни на работния лист изградете формула с мишката
- Позволете GetPivotData да се случи
- Проучете синтаксиса на GetPivotData (поле за връщане, място на завъртане, двойки)
- Променете твърдо кодираната стойност, за да сочи към клетка
- Трикратно натискане на F4 заключва само колоната
- Натискането на F4 два пъти заключва само реда
- Поставете специални формули
- Работен процес следващия месец: Добавяне на данни, опресняване на обобщена таблица, промяна на датата
- Изключително внимателни, за да внимавате за нови магазини
Стенограма на видеото
Научете Excel от подкаст, епизод 2013 - GetPivotData може да не е напълно зъл!
Ще подкастирам цялата тази книга, щракнете върху „i“ в горния десен ъгъл, за да се абонирате.
Добре, още в епизод 1998 г. говорих накратко за този проблем с GetPivotData. Ако изчислим% дисперсия и сме извън Pivot таблицата, сочеща вътре, и аз използвам мишката или клавиша със стрелка, така че 2019 / 2018-1. Този отговор, който ще получим тук, е правилен за януари, но когато щракнем двукратно, за да копираме това, формулата не копира, получаваме януарския отговор докрай. И когато го разгледаме, получаваме GetPivotData, не въведох GetPivotData, просто посочих тези клетки и това започна да се случва в Excel 2002 без никакво предупреждение. И в този момент казах, че начинът да се избегне това е да напишете формулата C5 / B5-1 и ще получите формула, която можете да копирате. Или ако просто мразите GetPivotData, ако е „напълно зло“, отидете в раздела „Анализ“, не „t между другото отворете бутона Опции. Върнете се в таблицата Pivot, отидете в раздела Analyze, отворете падащото меню до Options, премахнете отметката от това квадратче, това е глобална настройка. След като го изключите, той ще бъде изключен завинаги, добре.
Повечето от въпросите, които получавам, са „Как да изключа GetPivotData?“ но от време на време ще намеря някой, който обича GetPivotData. И обядвах с Роб Коли, когато той все още беше в Microsoft, и той каза „Е, нашите вътрешни клиенти обичат GetPivotData.“ Казах: „Какво? Не, всички мразят GetPivotData! " Роб казва: „Прав си, извън Microsoft, абсолютно, те мразят GetPivotData.“ Говоря за счетоводителите в Microsoft и по-късно срещнах един, който сега работи за екипа на Excel, Карлос и Карлос беше един от счетоводителите, които използват този метод.
Добре, ето какво трябва да направим. Имаме наш отчет, набор от данни тук, че за всеки месец имаме план за всеки магазин, а след това в дъното натрупваме фактически данни. Добре, така че имаме актуални за януари до декември, но имаме действителни само за няколко месеца, месеците, които са минали. И това, което нашият мениджър иска да направим, е да създадем отчет с магазини отляво, само магазините в Тексас, разбира се, за да направят живота по-труден. И след това имаме месеци и ако имаме действително за този месец, ние показваме действителното, така че действително януари, действително февруари, действително март, действително април. Но след това за месеците, в които нямаме актуални факти, ние превключваме и показваме бюджета, така че бюджетът излиза през декември, а след това общо, всичко, добре. Е, когато се опитате да създадете тази обобщена таблица, да,не работи.
Така че поставете обобщена таблица, нов работен лист, поставяте Store отляво, красивата страна, поставяте месеци отгоре, поставяте Type отгоре, поставяте продажби тук, добре. И така, ето какво получаваме, с което трябва да започнем да работим, така че имаме действителен януарски план, януарски план и след това напълно безполезния действителен януарски плюс план. Никой никога няма да използва това, но мога да се отърва от тези сиви колони, това е достатъчно лесно, някои тук в тази клетка, отидете в Настройки на полето и променете междинните суми на Няма. Но няма абсолютно никакъв начин за мен да премахна плана за януари, който няма да премахне и плана за април май юни юли, добре, няма начин да се отърва от това. Така че на този етап всеки месец, аз остана да избирам цялата обобщена таблица, отивам на Копиране и след това Поставяне, Поставяне на стойности. Вече не е обобщена таблица,и след това започвам ръчно да изтривам колоните, които не се показват в отчета.
Добре, това е нормалният метод, но счетоводителите в Microsoft са добавили допълнителна стъпка през януари, отнема 15 минути и тази стъпка позволява тази обобщена таблица да живее вечно, нали? Наричам това най-грозната Pivot таблица в света и счетоводителите в Microsoft приемат, че това е най-грозната Pivot таблица в света, но никой никога няма да види този отчет, освен тях. Това, което правят, е да дойдат тук на нов лист и да съставят отчета, който техният мениджър иска. Добре, ето магазините отляво, дори ги групирах в Хюстън, Далас и други, това е добре форматиран отчет. Подчертах сумите, ще видите, че когато получим някои числа, на първия ред има валута, но не и тези следващи редове, празни редове. О, празни редове в обобщената таблица.И една малка част от логиката тук, където мога да поставя датата на преминаване в клетка P1 и след това имам формула тук, която анализира, че АКО месецът на датата е> тази колона, и след това поставям думата Actual, в противен случай сложи думата План, добре. Така че всичко, което трябва да направя, е да променя това през датата и след това думата Действително да се обърне към плана, добре.
Сега, ето какво правим, ще си позволим да бъдем GetPivotData'd, нали? Не съм сигурен, че това е глагол, но ще позволим на Microsoft да получи GetPivotData. Така че започвам да изграждам формула с =, хващам мишката и отивам да търся действителния януари Baybrook! Затова се връщам в най-грозната Pivot таблица в света, намирам Baybrook, намирам януари, намирам действителен и щраквам Enter и им позволявам да ми го правят, добре, ето, сега имаме формула GetPivotData. Спомням си деня, в който направих това, беше като, знаете ли, след като Роб ми обясни какво правят, а аз се върнах и го опитах. Сега изведнъж през целия си живот се отървах от GetPivotData, всъщност никога не съм прегръщал GetPivotData. И така, това, което е, е първият елемент е това, което търсим, там "в полето, наречено Продажби, тук започва Pivot таблицата и може да бъде всяка клетка в Pivot таблицата, те използват горната лява ръка.
Добре, това е име на поле "Store", а след това те са кодирали "Baybrook", това е име на поле "Month", те са кодирали твърдо "January", това е име на поле "Type" имам кодиран „Действително“. Ето защо не можете да го копирате, защото те са кодирали твърдо стойностите. Но счетоводителите в Microsoft, Карлос и неговите колеги осъзнават: „Уау, почакайте малко, тук имаме думата Baybrook, тук имаме януари, тук имаме Actual. Просто трябва да променим тази формула, за да сочи към действителните клетки в отчета, вместо да бъде кодиран твърдо. " Добре, така те наричат това параметризиране на GetPivotData.
Премахнете думата Baybrook, елате тук и кликнете върху клетка D6. Сега трябва да заключа това до колоната, добре, така че натискам клавиша F4 3 пъти, получавам един $ преди D, добре. За месец януари премахвам твърдо кодирания януари, щраквам върху клетката E3, ще натисна два пъти F4, за да я заключа до реда, E $ 3. Напишете Actual, премахнете думата Actual, щракнете върху E4, отново F4 два пъти, добре, и получавам формула, която сега извлича тези данни обратно. Ще копирам това и след това ще поставя Специално, ще избирам Формати, alt = "" ESF, вижте F е подчертано там, ESF Enter и след това, след като направих това, просто ще повторя с F4, F4 е повторно и F4. Добре, така че сега имаме симпатичен отчет, той има заготовки, има форматиране, има единично счетоводно подчертаване под всеки раздел,в самото дъно има двойно счетоводно подчертаване.
Да, никога не получавате тези неща в обобщена таблица, това е невъзможно, но този отчет се управлява от обобщената таблица. Тогава какво правим, когато получим актуалните данни от май, върнем се тук, поставете ги, опреснете най-грозната обобщена таблица в света и след това тук на отчета просто променете датата на промяна от 4/30 на 5/31. И това, което прави, е, че причинява тази формула да премине от думата Plan към Actual, която отива и изважда действителните от доклада, вместо от плана, добре. Сега, ето какво е … това е страхотно, нали? Виждам къде бих направил това много, ако все още, знаете ли, работех в счетоводството.
Нещото, за което трябва да бъдете много внимателни, е, че ако създадат нов магазин, трябва да знаете да го добавите ръчно, нали, данните ще се показват в обобщената таблица, но бихте ги добавили ръчно. Сега това е подмножество на всички магазини, ако отчиташе всички магазини, вероятно тук, извън обхвата на печат, ще има нещо, което извади общия сбор от таблицата Pivot. И тогава щях да знам, ако тази сума не съвпада с общата сума от обобщената таблица, че нещо не е наред, и имам функция IF тук долу Казвайки „Здравейте, знаете ли, добавени са нови данни, бъдете много внимателни. ” Те имат някакъв механизъм за откриване, че има нови данни. Но го разбирам, това е страхотно използване. Така че, докато през повечето време GetPivotData просто ни побърква, всъщност може да има полза от него. Добре,така че това е съвет № 21 от 40 в книгата, купете я веднага, поръчайте онлайн, щракнете върху „i“ в горния десен ъгъл.
Дълго, дълго обобщение днес, добре: GetPivotData се случва, когато формула сочи вътре в обобщена таблица, а формула извън обобщената таблица сочи вътре. Въпреки че първоначалната формула е правилна, тя няма да се копира. Повечето хора мразят GetPivotData и искат да го предотвратят. Така че можете да създадете формула без мишката или клавишите със стрелки, просто напишете формулата или изключете GetPivotData за постоянно, ах, но има полза, добре. Така че трябва да съставим отчет с актуални данни за изминалия месец, бюджет за бъдещ. Нормален работен поток, създаване на обобщена таблица, преобразуване в стойности, изтриване на колони. Има начин да премахнете междинните суми, като използвате Настройки на полето, като се отървете от действителния план плюс януари. Вместо това просто ще създадем най-грозната Pivot таблица в света с твърде много данни.
Изградете добре форматиран, просто стар работен лист с отчети с може би малко логика, за да промените думата Actual на Plan. И след това от първата клетка на отчета, първото място, където ще бъдат числата в този отчет, въведете =, отидете на посоката на обобщената таблица и позволете GetPivotData да се случи. Ние изследваме синтаксиса на GetPivotData, така че това е полето за връщане, Продажби, където живее обобщената таблица, и след това двойки критерии, името на полето и стойността. Ще премахнем твърдо кодираната стойност и ще посочим клетка, като натискаме F4 3 пъти заключва само колоната, натискането на F4 2 пъти заключва само реда, копираме тази формула, Поставяме специални формули. Там добавих допълнителен съвет, че F4 е повторение, така че трябваше само веднъж да отида в диалоговия прозорец Специално поставяне и след това за следващото специално поставяне на формули, току-що използвано F4. Следващия месец добавете данните,опреснете Pivot таблицата, променете крайната дата. Уверете се, че не са изградили нови магазини, знаете ли, имат някакъв механизъм, ръчен или формула за проверка, проверете го. Благодарение на iTrainerMX в Twitter, който предложи GetPivotData, също Carlos и Rob от Microsoft, Rob сега от Power Pivot Pro. Карлос, че използва това, и Роб, че ми каза, че Карлос го използва, срещнах Карлос по-късно и той потвърди да, той беше един от счетоводителите, които използваха това през цялото време в Microsoft, добре, ето ви.и Роб, че ми каза, че Карлос го използва, срещнах Карлос по-късно и той потвърди да, той беше един от счетоводителите, които използваха това през цялото време в Microsoft, добре, ето ви.и Роб, че ми каза, че Карлос го използва, срещнах Карлос по-късно и той потвърди да, той беше един от счетоводителите, които използваха това през цялото време в Microsoft, добре, ето ви.
Е, хей, искам да ви благодаря, че отбихте, ще се видим следващия път за поредното излъчване от!
Свали файл
Изтеглете примерния файл тук: Podcast2013.xlsx
