Excel'in GETPIVOTDATA işlevinden nefret mi ediyorsunuz? Neden ortaya çıkıyor? Nasıl önleyebilirsin? GETPIVOTDATA için iyi bir kullanım var mı?
Çoğu kişi, Pivot tablodaki sayıları kullanan bir pivot tablonun dışında bir formül oluşturmaya çalıştıklarında ilk olarak GETPIVOTDATA ile karşılaşır. Örneğin, Excel'in GETPIVOTDATA işlevlerini eklemesi nedeniyle bu fark yüzdesi diğer aylara kopyalanmaz.
Excel, pivot tablonun dışında bir formül oluştururken pivot tablonun içindeki bir hücreye işaret etmek için fare veya ok tuşlarını her kullandığınızda GETPIVOTDATA ekler.
Bu arada, GETPIVOTDATA işlevinin görünmesini istemiyorsanız, hücrelere işaret etmek için fareyi veya ok tuşlarını kullanmadan = D5 / C5-1 gibi bir formül yazmanız yeterlidir. Bu formül sorunsuz bir şekilde kopyalanır.
Mağaza başına ayda bir plan numarası içeren bir veri kümesi. Ayrıca tamamlanan aylar için mağaza başına aylık gerçek satışlar da vardır. Amacınız, tamamlanan aylar için gerçekleri gösteren ve gelecek aylar için plan yapan bir rapor oluşturmaktır.
ROWS'ta Mağaza ile bir pivot tablo oluşturun. SÜTUNLAR'a Ay koyun ve yazın. Ocak Fiili, Ocak Planı ve tamamen saçma olan Ocak Gerçek + Planı ile aşağıda gösterilen raporu alırsınız.
Bir ay hücresi seçer ve Alan Ayarları'na giderseniz, alt toplamları Yok olarak değiştirebilirsiniz.
Bu, gereksiz Gerçek + Planı kaldırır. Ancak yine de Ocak'tan Nisan'a kadar olan plan sütunlarından kurtulmanız gerekiyor. Bunu pivot tablonun içinde yapmanın iyi bir yolu yoktur.
Böylece, aylık iş akışınız şöyle olur:
- Yeni ayın fiili değerlerini veri kümesine ekleyin.
- Sıfırdan yeni bir pivot tablo oluşturun.
- Pivot tabloyu kopyalayın ve artık bir pivot tablo olmaması için değerler olarak yapıştırın.
- İhtiyaç duymadığınız sütunları silin.
Daha iyi bir yol var. Aşağıdaki çok küçük şekil, çalışma kitabına eklenen yeni bir Excel çalışma sayfasını gösterir. Bunların hepsi düz Excel, pivot tablolar yok. Tek sihir parçası, 4. satırda P1 hücresindeki tarihe göre Gerçek'den Plan'a geçiş yapan bir EĞER işlevidir.
Doldurulması gereken ilk hücre, Baybrook için Gerçekleşen Ocak. Bu hücreye tıklayın ve bir eşittir işareti yazın. Fareyi kullanarak pivot tabloya geri dönün. Baybrook için Ocak Gerçekleri hücresini bulun. Bu hücreye tıklayın ve Enter tuşuna basın. Her zamanki gibi, Excel kopyalanamayan can sıkıcı ÖZETVERİAL işlevlerinden birini oluşturur.
Ama bugün GETPIVOTDATA'nın sözdizimini inceleyelim.
Aşağıdaki ilk argüman "Satışlar" sayısal alanıdır. İkinci bağımsız değişken, pivot tablonun bulunduğu hücredir. Kalan bağımsız değişken çiftleri alan adı ve değeridir. Otomatik oluşturulan formülün ne yaptığını görüyor musunuz? Mağazanın adı olarak "Baybrook" kodlu. Bu nedenle, otomatik olarak oluşturulan bu GETPIVOTDATA formüllerini kopyalayamazsınız. Aslında isimleri formüllere kodlarlar. Bu formülleri kopyalayamasanız da düzenleyebilirsiniz. Bu durumda, formülü $ D6 hücresini gösterecek şekilde düzenlerseniz daha iyi olur.
Düzenledikten sonra formül burada. "Baybrook", "Jan" ve "Gerçek" gitti. Bunun yerine, $ D6, E $ 3, E $ 4'ü işaret ediyorsunuz.
Bu formülü kopyalayın ve ardından diğer tüm sayısal hücrelerde Özel Formülleri Yapıştır'ı seçin.
Şimdi işte yıllık iş akışınız:
- Kimsenin göremeyeceği çirkin bir pivot tablo oluşturun.
- Rapor çalışma sayfasını ayarlayın.
Her ay yapmanız gerekenler:
- Verilerin altına yeni gerçekleri yapıştırın.
- Çirkin pivot tabloyu yenileyin.
-
Rapor sayfasındaki P1 hücresini yeni ayı yansıtacak şekilde değiştirin. Tüm numaralar güncellenir.
Hücre P1'i Değiştir
Bir pivot tablodan sayıları alan sade bir rapor kullanmanın size her iki dünyanın da en iyisini verdiğini kabul etmelisiniz. Raporu, bir pivot tabloyu biçimlendiremeyeceğiniz şekillerde biçimlendirmekte özgürsünüz. Boş satırlar iyidir. Para birimi sembollerini ilk ve son satırlarda olabilir, ancak aralarında olamaz. Genel toplamların altında da çift alt çizgi alırsınız.
Bu özelliği önerdiği için @iTrainerMX'e teşekkürler.
Video izle
- GetPivotData, formül bir pivot tablonun içini işaret ettiğinde gerçekleşir
- İlk formül doğru olsa da, formülü kopyalayamazsınız
- Çoğu insan getpivotdata'dan nefret eder ve bunu önlemek ister
- Yöntem 1: Fare veya ok tuşları olmadan bir formül oluşturun
- Yöntem 2: Seçeneklerin yanındaki açılır menüyü kullanarak GetPivotData'yı kalıcı olarak kapatın
- Ancak GetPivotData için bir kullanım var
- Yöneticiniz, geçmiş aylar için Gerçek Değerler içeren bir rapor ve gelecek için bütçe istiyor
- Normal iş akışı, bir pivot tablo oluşturmanızı, değerlere dönüştürmenizi, sütunları silmenizi sağlar
- Alan Ayarlarını kullanarak Ocak Gerçek + Planını önlemek için Alt Toplamları Kaldırma
- Bunun yerine, "çok fazla" veri içeren bir pivot tablo oluşturun
- Güzel biçimlendirilmiş bir rapor çalışma sayfası kullanın
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Çalışma sayfasındaki ilk veri hücresinden fareyle bir formül oluşturun
- GetPivotData'nın gerçekleşmesine izin ver
- GetPivotData sözdizimini inceleyin (döndürülecek alan, özet konumu, çiftler)
- Sabit kodlanmış değeri bir hücreyi gösterecek şekilde değiştirin
- F4'e üç kez basmak yalnızca sütunu kilitler
- F4'e iki kez basmak yalnızca satırı kilitler
- Özel Formülleri Yapıştır
- Önümüzdeki ay iş akışı: Veri ekleyin, pivot tabloyu yenileyin, tarihe kadar değişiklik yapın
- Yeni mağazalara dikkat etmek için son derece dikkatli
Videonun Transkripti
Podcast 2013 bölümünden Excel öğrenin - GetPivotData Tamamen Kötü Olmayabilir!
Bu kitabın tamamını podcast yayınlayacağım, abone olmak için sağ üst köşedeki "i" ye tıklayın.
Pekala, 1998 bölümünde geri döndüğümde, bu GetPivotData sorunundan kısaca bahsetmiştim. Bir% varyans hesaplarsak ve Pivot tablonun dışındayken içeriyi işaret edersek ve fareyi veya ok tuşunu kullanırım, yani 2019 / 2018-1. Burada alacağımız bu cevap Ocak için doğrudur, ancak bunu kopyalamak için çift tıkladığımızda formül kopyalanmaz, Ocak cevabını tamamen aşağıya alırız. Ve ona baktığımızda GetPivotData'yı alıyoruz, GetPivotData yazmadım, sadece bu hücrelere işaret ettim ve bu, Excel 2002'de herhangi bir uyarı olmadan tekrar olmaya başladı. Ve o noktada, bundan kaçınmanın yolunun C5 / B5-1 formülünü yazmak olduğunu ve kopyalayabileceğiniz bir formül elde edeceğinizi söyledim. Ya da GetPivotData'dan nefret ediyorsanız, "tamamen kötü" ise, Analiz sekmesine gidin, don 't Bu arada Seçenekler düğmesini açın. Pivot tabloya geri dönün, Analiz sekmesine gidin, Seçenekler'in yanındaki açılır menüyü açın, bu kutunun işaretini kaldırın, bu genel bir ayardır. Bir kez kapattığınızda, sonsuza kadar kapanacak.
Aldığım sorular çoğu zaman "GetPivotData'yı nasıl kapatırım?" ama arada bir GetPivotData'yı seven birini alacağım. Ve o hala Microsoft'tayken Rob Collie ile öğle yemeği yiyordum ve "Şirket içi müşterilerimiz GetPivotData'yı seviyor" dedi. Ne dedim? Hayır, herkes GetPivotData'dan nefret eder! " Rob, "Haklısın, Microsoft dışında kesinlikle GetPivotData'dan nefret ediyorlar" diyor. Microsoft'un içindeki muhasebecilerden bahsediyorum ve daha sonra şimdi Excel ekibi için çalışan biriyle tanıştım, Carlos ve Carlos bu yöntemi kullanan muhasebecilerden biriydi.
Pekala, işte yapmamız gereken şey. Raporumuz var, burada her ay için her mağaza için planımız olan bir veri kümesi var ve daha sonra en altta gerçekleri biriktiriyoruz. Pekala, Ocak'tan Aralık'a kadar gerçeklerimiz var, ancak yalnızca birkaç aydır gerçekleri var, geçmiş aylar. Ve müdürümüzün yapmamızı istediği şey, hayatı daha da zorlaştırmak için sol tarafta mağazalar, elbette sadece Texas mağazaları olan bir rapor oluşturmak. Ve sonra aylara baktığımızda aylar var ve eğer o ay için bir gerçekimiz varsa, gerçek olanı gösteririz, yani Ocak gerçek, Şubat gerçek, Mart gerçek, Nisan gerçek. Ancak daha sonra, fiili değerlerimizin olmadığı aylar için, bütçeyi değiştiririz ve bütçeyi gösteririz, bu nedenle Aralık ayına kadar bütçe ayırın ve ardından her şeyi toplamı tamamlayın. Peki, bu Pivot tabloyu oluşturmaya çalıştığınızda, evet,çalışmıyor.
Öyleyse PivotTable'ı, Yeni Çalışma Sayfasını ekleyin, Store'u sol tarafa koyun, güzel olan, Ayları en üste koyun, Type'ı en üste koyun, Sales'i buraya koyun, tamam. İşte çalışmaya başlamamız gereken şey şu, yani Ocak ayı fiili, Ocak planı ve sonra tamamen yararsız olan Ocak ayı fiili artı planımız var. Bunu hiç kimse kullanmayacak, ancak bu gri sütunlardan kurtulabilirim, bu yeterince kolay, bazıları burada bu hücreye, Alan Ayarlarına gidin ve Alt Toplamları Yok olarak değiştirin. Ancak, Nisan-Mayıs-Haziran Temmuz planını da kaldırmayacak olan Ocak planını kaldırmamın kesinlikle bir yolu yok, tamam, bundan kurtulmanın bir yolu yok. Yani her ay bu noktada, Pivot tablonun tamamını seçip Kopyala ve ardından Yapıştır, Değerleri Yapıştır seçeneğine gidiyorum. Artık bir Pivot tablo değil,ve sonra raporda görünmeyen sütunları manuel olarak silmeye başlıyorum.
Pekala, bu normal yöntem, ancak Microsoft'taki muhasebeciler Ocak ayında fazladan bir adım ekledi, 15 dakika sürüyor ve bu adım, bu Pivot tablonun sonsuza kadar yaşamasına izin veriyor, değil mi? Buna dünyanın en çirkin Pivot tablosu diyorum ve Microsoft'taki muhasebeciler bunun dünyanın en çirkin Pivot tablosu olduğunu kabul ediyor, ancak bu raporu onlar dışında kimse görmeyecek. Yaptıkları şey, buraya yeni bir sayfaya gelip müdürünün istediği raporu oluşturmaktır. Pekala, işte sol taraftaki mağazalar, hatta onu Houston, Dallas ve Diğer olarak gruplandırdım, güzel biçimlendirilmiş bir rapor. Toplamları vurguladım, bazı sayılar aldığımızda, ilk satırda para birimi olduğunu göreceksiniz, ancak bu sonraki satırlar, boş satırlar değil. Ooh, Pivot tablodaki boş satırlar.Ve burada, P1 hücresine geçiş tarihini koyabileceğim küçük bir mantık parçası ve sonra burada, son tarihin> bu sütun EĞER ise bunu analiz eden bir formülüm var ve sonra Gerçek kelimesini koyuyorum Plan kelimesini koy, pekala. Yani tek yapmam gereken bunu tarihe kadar değiştirmek ve ardından Gerçek kelimesi plana geçmek, Pekala.
Şimdi, işte yaptığımız şey, kendimize GetPivotData'lı olmamıza izin vereceğiz, değil mi? Bunun bir fiil olduğundan emin değilim, ancak Microsoft'un GetPivotData'ya izin vereceğiz. Böylece bir = ile bir formül oluşturmaya başlıyorum, fareyi tutuyorum ve Ocak ayı gerçek Baybrook'u aramaya gidiyorum! Bu yüzden dünyanın en çirkin Pivot tablosuna geri dönüyorum, Baybrook'u buluyorum, Ocak'ı buluyorum, gerçek buluyorum ve Enter'a tıklıyorum ve bana yapmalarına izin ver, tamam, işte gidiyoruz, şimdi bir GetPivotData formülümüz var. Bunu yaptığım günü hatırlıyorum, Rob bana ne yaptıklarını anlattıktan sonra geri dönüp denedikten sonra gibiydi. Şimdi birdenbire, tüm hayatım boyunca, GetPivotData'dan kurtuldum, GetPivotData'yı asla benimsemedim. Öyleyse, ilk öğe aradığımız şey, orada 'Satış adlı bir alan, burası Pivot tablonun başladığı yerdir ve Pivot tablodaki herhangi bir hücre olabilir, sol üst eli kullanırlar.
Pekala, bu bir alan adı "Mağaza" ve sonra "Baybrook" kodunu gömdüler, bu bir alan adı "Ay", "Ocak" kodunu gömdüler, bu bir alan adı "Tür" ve onlar ve "Gerçek" kodlu. Bu yüzden kopyalayamazsınız çünkü değerleri kodlanmışlardır. Ancak Microsoft'taki muhasebeciler, Carlos ve meslektaşları şunu fark ettiler: "Vay be, bir saniye, burada Baybrook kelimesi var, burada Ocak ayı var, burada Gerçek var. Bu formülü, kodlanmış kod yerine rapordaki gerçek hücreleri gösterecek şekilde değiştirmemiz gerekiyor. " Pekala, bu yüzden bu GetPivotData parametresini çağırıyorlar.
Baybrook kelimesini kaldırın, buraya gelin ve D6 hücresine tıklayın. Şimdi, bunu sütuna kilitlemem gerekiyor, pekala, bu yüzden F4 tuşuna 3 kez basıyorum, D'den önce tek bir $ alıyorum, tamam. Ocak ayı için, kodlanmış Ocak ayını kaldırıyorum, E3 hücresine tıklıyorum, E $ 3 satırına kilitlemek için F4'e iki kez basacağım. Gerçek yazın, Gerçek kelimesini kaldırın, E4'e tıklayın, tekrar F4'e iki kez tıklayın, tamam, şimdi bu verileri geri çeken bir formül alıyorum. Bunu kopyalayacağım ve sonra Özel Yapıştır'ı seçin, Biçimleri seçin, alt = "" ESF, burada F'nin altı çizildiğine bakın, ESF Enter ve sonra şimdi bunu sadece F4 ile tekrarlayacağım F4 bir yinelemedir ve F4. Pekala, şimdi güzel görünen bir raporumuz var, boşlukları var, biçimlendirmesi var, her bölümün altında tek bir muhasebe alt çizgisi var,en altta çift muhasebe altı çizgisi var.
Doğru, bunları bir Pivot tabloda asla almazsınız, bu imkansız, ancak bu rapor Pivot tablodan alınmıştır. Öyleyse, Mayıs fiili değerlerini aldığımızda yaptığımız şey, buraya geri dönün, onları yapıştırın, gidip dünyanın en çirkin Pivot tablosunu yenileyin ve sonra burada, raporda sadece son tarihi 4 / 30'dan 5 / 31'e değiştirin. Ve bunun yaptığı şey, bu formülün Plan kelimesinden Gerçek'e geçmesine neden olur, bu da gerçekleri plan yerine rapordan alır ve çıkarır. Şimdi, işte şu - bu harika, değil mi? Hâlâ muhasebe alanında çalışsaydım, bunu nerede yapacağımı görebiliyorum.
Gerçekten dikkat etmeniz gereken şey, eğer yeni bir mağaza kurarlarsa, onu manuel olarak eklemeyi bilmeniz gerekir, doğru, veriler Pivot tabloda görünecek, ancak manuel olarak eklersiniz. Şimdi bu, tüm mağazaların bir alt kümesidir, eğer tüm mağazaları bildiriyor olsaydı, muhtemelen burada, baskı aralığının dışında, genel toplamı Pivot tablodan çeken bir şeye sahip olurdum. Ve sonra, eğer bu toplam Pivot tablosundaki genel toplamla uyuşmuyorsa, bir şeylerin yanlış olduğunu ve burada aşağıda bir EĞER işlevi olduğunu bilirim. "Hey, bilirsiniz, eklenen yeni veriler, çok dikkatli olun. " Yeni verilerin orada olduğunu tespit etmek için bir tür mekanizmaları var. Ama anlıyorum, harika bir kullanım. Dolayısıyla, GetPivotData çoğu zaman bizi çılgına çevirse de, aslında bunun bir kullanımı olabilir. Peki,işte bu, kitaptaki 40 üzerinden 21. ipucu, hemen kitabı satın alın, çevrimiçi sipariş verin, sağ üst köşedeki "i" yi tıklayın.
Bugün uzun, uzun bir özet, pekala: GetPivotData, bir formül bir Pivot tablonun içini işaret ettiğinde, Pivot tablonun dışındaki bir formül de içine işaret ettiğinde gerçekleşir. İlk formül doğru olsa da kopyalanmayacaktır. Çoğu insan GetPivotData'dan nefret eder ve bunu önlemek ister. Yani fare veya ok tuşları olmadan bir formül oluşturabilir, sadece formülü yazabilir veya GetPivotData'yı kalıcı olarak kapatabilirsiniz, ah, ama bir kullanım var, tamam. Bu nedenle, geçen ay için gerçekleri, gelecek için bütçeyi içeren bir rapor oluşturmalıyız. Normal iş akışı, bir Pivot tablo oluşturun, değerlere dönüştürün, Sütunları silin. Alan Ayarlarını kullanarak alt toplamları kaldırmanın ve Ocak ayı fiili artı planından kurtulmanın bir yolu var. Bunun yerine, çok fazla veriyle dünyanın en çirkin Pivot tablosunu oluşturacağız.
Gerçek kelimesini Plan olarak değiştirmek için belki biraz mantıkla güzel biçimlendirilmiş, sadece eski bir rapor çalışma sayfası oluşturun. Ve sonra, o raporda sayıların olacağı ilk yer olan ilk rapor hücresinden, bir = yazın, Pivot tabloya gidin ve GetPivotData'nın gerçekleşmesine izin verin. GetPivotData sözdizimini inceliyoruz, bu nedenle döndürülecek alandır, Satış, Pivot tablonun yaşadığı yer ve ardından ölçüt çiftleri, alan adı ve değer. Kodlanmış değeri kaldırıp bir hücreyi göstereceğiz, F4'e 3 kez basmak yalnızca sütunu kilitler, F4'e 2 kez basmak yalnızca satırı kilitler, bu formülü kopyalar, Özel Formülleri Yapıştır. Orada F4'ün bir yineleme olduğuna dair fazladan bir ipucu ekledim, bu yüzden Özel Yapıştır iletişim kutusuna yalnızca bir kez gitmem gerekti ve ardından bir sonraki Özel Yapıştır Formülleri için F4'ü kullandım. Önümüzdeki ay verileri ekleyin,Pivot tabloyu yenileyin, geçiş tarihini değiştirin. Yeni mağazalar inşa etmediklerinden emin olun, bilirsiniz, bir tür mekanizma, ya manuel ya da kontrol formülü, kontrol edin. GetPivotData'yı öneren Twitter'daki iTrainerMX, ayrıca Microsoft'tan Carlos ve Rob, şimdi de Power Pivot Pro'dan Rob. Bunu kullandığı için Carlos ve bana Carlos'un kullandığını söylediği için Rob, Carlos ile daha sonra tanıştım ve evet, bunu Microsoft'ta her zaman kullanan muhasebecilerden biri olduğunu doğruladı, tamam, işte buyrun.ve Rob Carlos'un onu kullandığını söylediği için, daha sonra Carlos'la tanıştım ve o evet, bunu Microsoft'ta her zaman kullanan muhasebecilerden biri olduğunu doğruladı.ve Rob Carlos'un onu kullandığını söylediği için, daha sonra Carlos'la tanıştım ve o evet, bunu Microsoft'ta her zaman kullanan muhasebecilerden biri olduğunu doğruladı.
Peki hey, uğradığın için teşekkür etmek istiyorum, bir dahaki sefere başka bir internet yayını için görüşürüz!
Dosyayı indir
Örnek dosyayı buradan indirin: Podcast2013.xlsx
