Да ли мрзите Екцел-ову функцију ГЕТПИВОТДАТА? Зашто се појављује? Како то можете спречити? Да ли постоји добра употреба за ГЕТПИВОТДАТА?
Већина људи се први пут сусреће са ГЕТПИВОТДАТА када покушава да изгради формулу изван заокретне табеле која користи бројеве у заокретној табели. На пример, овај проценат варијансе се неће копирати на остале месеце због Екцел-овог уметања ГЕТПИВОТДАТА функција.
Екцел убацује ГЕТПИВОТДАТА сваки пут када помоћу миша или тастера са стрелицом покажете на ћелију унутар пивот табеле док градите формулу изван пивот табеле.
Успут, ако не желите да се појави функција ГЕТПИВОТДАТА, једноставно откуцајте формулу као што је = Д5 / Ц5-1 без употребе миша или тастера са стрелицом за усмеравање ка ћелијама. Та формула се без проблема копира.
Ево скупа података који садржи један број плана месечно по продавници. Постоје и стварне месечне продаје по продавници за месеце који су комплетни. Циљ вам је да направите извештај који приказује стварне податке за завршене месеце и план за будуће месеце.
Направите пивот табелу са Сторе у РОВС. Ставите месец и тип у колоне. Добићете извештај приказан у наставку са јануарским стварним, јануарским планом и потпуно бесмисленим јануарским стварним + планом.
Ако одаберете ћелију за месец и одете у Подешавања поља, међузбројеве можете променити у Ништа.
Ово уклања бескорисни план Ацтуал +. Али ипак морате да се решите колона плана за јануар до априла. Не постоји добар начин за то унутар пивот табеле.
Дакле, ваш месечни ток посла постаје:
- Додајте актуелне податке за нови месец у скуп података.
- Направите нову стожерну табелу од нуле.
- Копирајте пивот табелу и налепите као вредности тако да више није пивот табела.
- Избришите колоне које вам нису потребне.
Постоји бољи пут. Следећа врло мала слика приказује нови Екцел радни лист додат у радну свеску. Све је ово само равни Екцел, без пивот табела. Једина чаролија је функција ИФ у реду 4 која се пребацује са стварног на план на основу датума у ћелији П1.
Прва ћелија коју треба попунити је јануар, заправо за Баиброок. Кликните ту ћелију и унесите знак једнакости. Помоћу миша се вратите до стожера. Пронађите ћелију за јануарске стварности за Баиброок. Кликните ту ћелију и притисните Ентер. Као и обично, Екцел прави једну од оних досадних ГЕТПИВОТДАТА функција које се не могу копирати.
Али данас, проучимо синтаксу ГЕТПИВОТДАТА.
Први аргумент у наставку је нумеричко поље „Продаја“. Други аргумент је ћелија у којој се налази стожерна табела. Преостали парови аргумената су име поља и вредност. Видите ли шта је радила аутоматски генерисана формула? Било је тешко кодирано „Баиброок“ као назив продавнице. Због тога не можете копирати ове аутоматски генерисане ГЕТПИВОТДАТА формуле. Они заправо тврдо кодирају имена у формуле. Иако ове формуле не можете да копирате, можете их уредити. У овом случају, било би боље да уредите формулу тако да указује на ћелију $ Д6.
Ево формуле након што је уредите. Нестали су „Баиброок“, „Јан“ и „Ацтуал“. Уместо тога, показујете на $ Д6, Е $ 3, Е $ 4.
Копирајте ову формулу, а затим одаберите Пасте Специал, Формулас у свим осталим нумеричким ћелијама.
Сада је ваш годишњи ток рада:
- Направите ружну стожерну табелу коју нико никада неће видети.
- Поставите радни лист извештаја.
Сваког месеца морате:
- Залепите нове стварне податке испод података.
- Освежите ружну стожерну табелу.
-
Промените ћелију П1 на листу извештаја како би одражавао нови месец. Сви бројеви се ажурирају.
Промените ћелију П1
Морате признати да вам коришћење обичног извештаја који извлачи бројеве из заокретне табеле даје најбоље од оба света. Можете форматирати извештај на начин да не можете форматирати заокретну табелу. Празни редови су у реду. Симболе валута можете имати у првом и последњем реду, али не између. Добијате и двоструко подвлачење испод укупних износа.
Захваљујући @иТраинерМКС на предлагању ове функције.
Погледајте видео
- ГетПивотДата се дешава када формула усмери унутар изведене табеле
- Иако је почетна формула тачна, не можете копирати формулу
- Већина људи мрзи гетпивотдата и жели да их спречи
- 1. метод: Направите формулу без миша или тастера са стрелицама
- 2. метод: Трајно искључите ГетПивотДата користећи падајући мени поред опција
- Али постоји употреба ГетПивотДата
- Ваш менаџер жели извештај са актуелностима за протекле месеце и буџетом за будућност
- Уобичајени ток посла би вам омогућио да направите пивот табелу, претворите у вредности, избришете колоне
- Уклањање међузбројева како би се спречио стварни и план за јануар помоћу поставки поља
- Уместо тога, направите пивот табелу са „превише“ података
- Користите лепо обликован радни лист извештаја
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Из прве ћелије података на радном листу изградите формулу помоћу миша
- Дозволите да се ГетПивотДата догоди
- Испитајте синтаксу ГетПивотДата (поље за враћање, локација осовине, парови)
- Промените чврсто кодирану вредност тако да показује на ћелију
- Притиском на Ф4 три пута закључаћете само колону
- Притиском на Ф4 два пута закључаћете само ред
- Налепите посебне формуле
- Ток посла следећег месеца: Додајте податке, освежите пивот табелу, промените датум
- Изузетно пажљиво пазите на нове продавнице
Транскрипт видео записа
Научите Екцел из подцаста, епизода 2013 - ГетПивотДата можда неће бити потпуно зао!
Подставићу целу ову књигу, кликните на „и“ у горњем десном углу да бисте се претплатили.
Добро, још у епизоди 1998. кратко сам разговарао о овом проблему ГетПивотДата. Ако израчунамо% варијансе и налазимо се изван пивот табеле која показује према унутра, а ја користим миш или тастер са стрелицом, дакле 2019 / 2018-1. Овај одговор који ћемо овде добити је тачан за јануар, али када двапут кликнемо да бисмо то копирали, формула се не копира, добићемо јануарски одговор скроз доле. И када то погледамо, добијамо ГетПивотДата, нисам откуцао ГетПивотДата, само сам показао на те ћелије, а ово је почело да се дешава још у програму Екцел 2002 без икаквог упозорења. И у том тренутку сам рекао да је начин да се то избегне прекуцавањем формуле Ц5 / Б5-1 и добићете формулу коју можете копирати. Или ако само мрзите ГетПивотДата, ако је „потпуно зло“, идите на картицу Анализирај, нет успут отворите дугме Оптионс. Вратите се у пивот табелу, идите на картицу Анализе, отворите падајући мени поред Оптионс, опозовите избор у овом пољу, то је глобално подешавање. Једном када га искључите, заувек ће се искључити, у реду.
Већину питања која добијем су „Како да искључим ГетПивотДата?“ али повремено ћу добити некога ко воли ГетПивотДата. А ја сам ручао са Робом Цоллиејем док је још био у Мицрософту, и рекао је „Па, наши интерни купци воле ГетПивотДата.“ Рекао сам „Шта? Не, сви мрзе ГетПивотДата! “ Роб каже: „У праву сте, ван Мицрософта, апсолутно, они мрзе ГетПивотДата.“ Говорим о рачуновођама унутар Мицрософта, а касније сам упознао једног који сада ради за Екцел тим, Царлос, а Царлос је био један од рачуновођа који користе ову методу.
У реду, па ево шта морамо да урадимо. Имамо наш извештај, овде постављен податак да за сваки месец имамо план за сваку продавницу, а затим на дну сакупљамо стварне податке. У реду, тако да имамо актуелне податке од јануара до децембра, али стварне податке имамо само за неколико месеци, месеци који су прошли. А оно што наш менаџер жели да урадимо је да направимо извештај са продавницама са леве стране, само продавнице у Тексасу, наравно, да би нам живот учинили тежим. А онда прелазимо преко месеци, а ако имамо стварни за тај месец, приказујемо стварни, дакле стварни јануар, стварни фебруар, стварни март, стварни април. Али онда се за месеце када немамо стварности пребацујемо и приказујемо буџет, дакле буџет до децембра, а онда свеукупно, све у реду. Па, када покушате да направите ову пивот табелу, да,не ради.
Дакле, убаците заокретну табелу, нови радни лист, ставите Сторе са леве стране, лепа страна, ставите Месеце на врх, ставите Типе на врх, ставите Салес овде, у реду. Дакле, ево шта добијамо са чим морамо почети да радимо, тако да имамо стварни јануарски план, јануарски план, а затим и потпуно бескористан јануарски стварни плус план. Нико ово никада неће користити, али могу се решити ових сивих ступаца, то је довољно једноставно, неки овде до ове ћелије, идите у Подешавања поља и промените међузбројеве у Ништа. Али апсолутно не постоји начин да уклоним јануарски план који неће уклонити ни план април мај јуни јул, у реду, не постоји начин да се ово отарасим. Дакле, у овом тренутку сваког месеца заглавио сам при одабиру целе пивот табеле, преласку на Копирај, а затим налепи, налепи вредности. То више није пивот табела,а затим почињем ручно да бришем колоне које се не појављују у извештају.
У реду, то је уобичајена метода, али рачуновође у Мицрософту додали су додатни корак у јануару, потребно је 15 минута, а овај корак омогућава овој Пивот табели да живи вечно, зар не? Ово називам најружнијом пивот табелом на свету, а рачуновође у Мицрософту прихватају да је ово најружнија пивот табела на свету, али нико никада неће видети овај извештај осим њих. Оно што раде је да дођу овде на нови лист и саставе извештај који жели њихов менаџер. Па, ево продавница са леве стране, чак сам их и груписао у Хоустон, Даллас и Отхер, то је лепо обликован извештај. Истакао сам укупне износе, видећете да када унесемо неке бројеве, у првом реду је валута, али не и следећи редови, празни редови. Оох, празни редови у пивот табели.И један мали делић логике овде горе, где могу да ставим датум проласка у ћелију П1, а затим овде имам формулу која анализира да АКО је месец протеклог датума> ова колона, а затим стављам реч Стварно, у супротном стави реч План, у реду. Дакле, све што морам да урадим је да променим ово кроз датум, а затим реч Стварно пребацим на план, у реду.
Сада, ево шта радимо, дозволићемо себи да будемо ГетПивотДата'д, зар не? Нисам сигуран да је то глагол, али омогућићемо Мицрософту да добије ГетПивотДата. Па почињем да градим формулу са =, ухватим миша и идем да потражим стварни Баиброок из јануара! Тако се враћам у најружнију пивот табелу на свету, проналазим Баиброок-а, проналазим јануара, налазим стварног и кликћем Ентер и дозвољавам им да ми то ураде, у реду, ето, сад имамо ГетПивотДата формулу. Сјећам се дана када сам то учинио, било је то, знате, након што ми је Роб објаснио шта раде, а ја сам се вратио и покушао. Сад сам се одједном, целог свог живота, решавао ГетПивотДата, заправо никада нисам прихватио ГетПивотДата. Дакле, шта је, прва ставка је оно што тражимо, ето 'Са пољем Продаја, овде почиње пивот табела и то може бити било која ћелија у пивот табели, користе горњу леву руку.
У реду, ово је назив поља „Сторе“, а затим су чврсто кодирали „Баиброок“, ово је име поља „Монтх“, они су тврдо кодирали „Јануари“, ово је име поља „Типе“, и они су „ Веома сам кодиран „Стварно“. ЗАТО га не можете копирати, јер су вредности добро кодирали. Али рачуновође у Мицрософту, Царлос и његови сарадници схватају: „Јој, сачекајте мало, овде имамо реч Баиброок, овде имамо јануар, овде имамо Ацтуал. Морамо само да променимо ову формулу тако да указује на стварне ћелије у извештају, уместо да буду кодиране “. У реду, па ово називају параметризирањем ГетПивотДата.
Уклоните реч Баиброок, дођите овде и кликните на ћелију Д6. Сада морам да закључам ово на колону, у реду, па притиснем тастер Ф4 3 пута, добићу један $ пре Д, у реду. За месец јануар уклањам кодирани јануар, кликнем на ћелију Е3, двапут ћу притиснути Ф4 да је закључам на ред, 3 УСД. Укуцајте Стварно, уклоните реч Стварно, кликните Е4, поново двапут Ф4, у реду, и добићу формулу која сада те податке враћа назад. Копираћу то, а затим налепите Специал, изаберите Формати, алт = "" ЕСФ, видите да је Ф подвучен тамо, ЕСФ Ентер, а сада то, учинио сам, поновићу са Ф4 Ф4 је редо и Ф4. У реду, тако да сада имамо леп извештај, има празних места, има форматирање, има једну рачуноводствену подвлаку испод сваког одељка,на самом дну има двоструку рачуноводствену подвлаку.
Тачно, ове ствари никада не ставите у пивот табелу, то је немогуће, али овај извештај се покреће из пивот табеле. Па шта онда радимо када добијемо мајске актуелне ствари, вратимо се овде, залепимо их, освежимо најружнију пивот табелу на свету, а онда овде на извештају само променимо датум проласка са 4/30 на 5/31. А оно што чини је то што узрокује да се ова формула пребаци са речи План на Стварно, која иде и извлачи стварне податке из извештаја, уместо плана, у реду. Ето, ово је … ово је сјајно, зар не? Видим где бих ово много радио да сам и даље, знате, радио у рачуноводству.
Ствар на коју морате бити веома опрезни је да ако направе нову продавницу морате да је додате ручно, тачно, подаци ће се приказати у изведеној табели, али бисте је додали ручно. Сада је ово подскуп свих продавница, да извештава о свим продавницама, вероватно бих овде, ван опсега штампе, имао нешто што је извукло укупан износ из пивот табеле. И онда бих знао, ако се овај укупан износ не подудара са укупним збројем из пивот табеле, да нешто није у реду, и овде имам функцију ИФ који каже „Хеј, знате, додани су нови подаци, будите врло опрезни. ” Они имају некакав механизам за откривање нових података. Али схватам, то је супер употреба. Дакле, док нас већину времена ГетПивотДата само излуђује, од тога заправо може бити користи. У реду,тако да је то савет бр. 21 од 40 у књизи, купите књигу одмах, наручите путем Интернета, кликните на „и“ у горњем десном углу.
Дуго, дуго рекапулисање данас, у реду: ГетПивотДата се дешава када формула усмери унутар изведене табеле, а формула изван пивот табеле показује унутра. Иако је почетна формула тачна, неће се копирати. Већина људи мрзи ГетПивотДата и жели да их спречи. Дакле, можете да направите формулу без миша или тастера са стрелицама, само откуцајте формулу или трајно искључите ГетПивотДата, ах, али употреба је у реду. Зато морамо да направимо извештај са стварним подацима за прошли месец, буџетом за будућност. Уобичајени ток посла, креирајте пивот табелу, претворите у вредности, избришите колоне. Постоји начин да уклоните међузбројеве помоћу поставки поља, решавајући се стварног плус плана за јануар. Уместо тога, само ћемо створити најружнију пивот табелу на свету са превише података.
Направите лепо форматиран, обичан стари радни лист извештаја са можда мало логике да реч Актуелно промените у План. А затим из прве ћелије извештаја, првог места где ће бројеви бити у том извештају, откуцајте =, идите на пивот табелу и дозволите да се догоде ГетПивотДата. Испитујемо синтаксу ГетПивотДата, тако да је то поље за враћање, Продаја, где живи изведена табела, а затим парови критеријума, име поља и вредност. Уклонићемо кодирану вредност и указати на ћелију, притиском на Ф4 3 пута закључаћете само колону, притиском на Ф4 2 пута закључаћете само ред, копирајте ту формулу, Налепите посебне формуле. Тамо сам дао додатни савет да је Ф4 поновљени, тако да сам само једном морао да пређем на дијалог Пасте Специал, а затим за следећу Пасте Специал Формуле управо сам користио Ф4. Следећег месеца додајте податке,освежите пивот табелу, промените датум. Уверите се да нису направили нове продавнице, знате, имају ли некакав механизам, било ручни, било формулу за проверу, погледајте то. Захваљујући иТраинерМКС-у на Твиттер-у, који је предложио ГетПивотДата, такође Царлосу и Робу из Мицрософта, Роб-у сада из Повер Пивот Про-а. Царлос што је ово користио, а Роб што ми је рекао да га Царлос користи, касније сам га упознао, и он је потврдио да, био је један од књиговођа који су то све време користили у Мицрософту, у реду, ето.и Роба што ми је рекао да га Царлос користи, касније сам га упознао, и он је потврдио да, био је један од књиговођа који су ово све време користили у Мицрософту, у реду, ето.и Роба што ми је рекао да га Царлос користи, касније сам га упознао, и он је потврдио да, био је један од књиговођа који су ово све време користили у Мицрософту, у реду, ето.
Па, хеј, желим да вам се захвалим што сте навратили, видимо се следећи пут за још један пренос од!
Скини докуменат
Преузмите датотеку узорка овде: Подцаст2013.клск
