Excel Veri Modeli (Power Pivot), birleştirici tabloları kullanarak büyük bir ayrıntılı veri kümesini üst düzey bir bütçeye bağlamanıza olanak tanır.
Bütçeler en üst düzeyde yapılır - aydan bölgeye, ürün grubuna göre gelir. Fiili değerler zaman içinde yavaşça birikir - faturaya göre fatura, satır öğesi satır öğesi. Küçük Bütçe dosyasını hacimli Gerçek Değerler verileriyle karşılaştırmak sonsuza dek bir sıkıntı oldu. PowerPivotPro.com olarak da bilinen Rob Collie'nin bu numarasına bayılıyorum.
Örneği oluşturmak için 54 satırlık bir bütçe tablonuz var: her ürün için bölge başına ayda bir satır.
Fatura dosyası detay seviyesinde: Bu yıl şu ana kadar 422 satır.
Bu iki veri kümesini eşleştirmenize izin verecek dünyada hiçbir DÜŞEYARA yoktur. Ancak, Power Pivot (diğer adıyla Excel 2013+ Veri Modeli) sayesinde bu kolaylaşır.
İki büyük veri kümesini birbirine bağlamak için "birleştiriciler" dediğim küçük küçük tablolar oluşturmanız gerekir. Benim durumumda, Ürün, Bölge ve Tarih iki tablo arasında ortaktır. Ürün tablosu küçük, dört hücreli bir tablodur. Bölge için Ditto. Tek bir tablodan veri kopyalayıp Yinelemeleri Kaldır seçeneğini kullanarak bunların her birini oluşturun.
Sağdaki takvim tablosunun oluşturulması aslında daha zordu. Bütçe verilerinin ayda bir satırı vardır ve her zaman ayın sonuna denk gelir. Fatura verileri, genellikle hafta içi olmak üzere günlük tarihleri gösterir. Bu nedenle, Tarih alanını her iki veri kümesinden de tek bir sütuna kopyalamam ve ardından tüm tarihlerin temsil edildiğinden emin olmak için kopyaları kaldırmam gerekiyordu. Daha sonra =TEXT(J4,"YYYY-MM")günlük tarihlerden bir Ay sütunu oluşturuyordum.
Tam Power Pivot eklentisine sahip değilseniz, Bütçe tablosundan bir pivot tablo oluşturmanız ve Bu Verileri Veri Modeline Ekle kutusunu işaretlemeniz gerekir.
Önceki ipucunda tartışıldığı gibi, pivot tabloya alanlar eklerken altı ilişki tanımlamanız gerekecektir. Bunu, İlişki Oluştur iletişim kutusunu altı ziyaretle yapabilirsiniz, ancak ben Power Pivot eklentimi başlattım ve altı ilişkiyi tanımlamak için diyagram görünümünü kullandım.
İşte tüm bunların işe yaraması için anahtar: Bütçe'den ve Gerçek'den sayısal alanları kullanmakta özgürsünüz. Ancak pivot tabloda Bölge, Ürün veya Ay'ı göstermek istiyorsanız, bunların birleştirici tablolarından gelmesi gerekir!
İşte beş tablodan gelen verileri içeren bir pivot tablo. Sütun A, Bölge birleştiricisinden geliyor. 2. satır, Takvim birleştiricisinden geliyor. Ürün dilimleyici, Ürün birleştiricidendir. Bütçe rakamları Bütçe tablosundan, Gerçek rakamlar ise Fatura tablosundan gelir.
Bu işe yarar çünkü birleştirici tabloları Bütçe ve Fiili tabloya filtreler uygular. Bu güzel bir tekniktir ve Power Pivot'un yalnızca büyük veriler için olmadığını gösterir.
Video izle
- Yukarıdan aşağıya küçük bir bütçe veri kümeniz var
- Aşağıdan yukarıya fiili veri kümesiyle karşılaştırmak istiyorsunuz
- Gerçekler bir fatura kaydından gelebilir
- Veri modeli, bu farklı boyutlu veri kümelerini karşılaştırmanıza olanak tanır.
- Her iki veri setini de bir Ctrl + T tablosuna dönüştür
- Raporlamak istediğiniz her metin alanı için bir marangoz tablosu oluşturun
- Değerleri kopyalayın ve kopyaları kaldırın
- Tarihler için, her iki tablodan tarihleri ekleyebilir ve ay sonuna dönüştürebilirsiniz
- Birleştiricileri Ctrl + T tabloları yapın
- İsteğe bağlı ancak beş tablonun tümünü adlandırmak yararlı
- Bütçe'den bir pivot tablo oluşturun ve Veri Modelini seçin
- Orijinal tablolardan Bütçe ve Gerçek'i kullanarak bir pivot tablo oluşturun
- Diğer tüm alanlar birleştirici tablolarından gelmelidir
- Ürüne göre dilimleyiciler ekleyin
- Bütçeden Marangozlara üç ilişki oluşturun
- Fiili'den Marangozlara üç ilişki oluşturun
- Yarın: Power Pivot ve DAX Formülleri ile ilişki kurmak ne kadar kolay
Video Metni
Excel'i podcast bölüm 2016'dan öğrenin - Yukarıdan Aşağıya Bütçe ve Aşağıdan Yukarı Gerçek Değerler!
Hey, tüm bu kitabın podcast'ini yapıyorum, sağ üst köşedeki "i" ye tıklayın ve çalma listesini takip edin.
Hey, bunu yarıda keseceğim, bu Bill Jelen 15 dakika sonra. Şimdi bunun inanılmaz derecede uzun bir podcast olduğunu anlıyorum ve sadece üzerine tıklamak istersiniz, ama size bu kadar kısa bir süre vereyim. Excel 2013'teyseniz ve şimdiye kadar küçük bir bütçe tablonuz ve çok büyük bir fiili tabloya sahipseniz ve bunları birlikte eşlemeniz gerekiyorsa, bu, Excel 2013'te sahip olduğumuz ve pek çok insanın açıklamadığı şaşırtıcı yeni bir yetenek ve muhtemelen bunu bilmiyorsunuz. Bu sizseniz, 2013'tesiniz ve bu iki veri setini haritalamanız, zaman ayırmanız, belki bugün, belki yarın, belki izleme listesine eklemeniz gerekiyor, buna değer, bu harika bir teknik.
Pekala, elimizde ne var, sol tarafta bir bütçemiz var, bu bütçe, her ürün grubu için en üst düzeyde, yukarıdan aşağıya, sağdan, her bölge için, her ay için bir bütçe var . Burada pek fazla kayıt yok, 55'e kadar, sağ tarafta bunu gerçeklerle karşılaştırmaya çalışıyoruz. Gerçekler bir fatura sicilinden geliyor, bu yüzden Bölgemiz, Ürünümüz ve Gelirimiz var, ancak bunlar ayrı faturalar, burada çok daha fazla veri, zaten yılın yarısına geldik ve şimdiden 423 kaydım var. Pekala, bu 55'i bu 423'e nasıl eşlersin? DÜŞEYARA ile yapmak zor olabilir, önce özetlemeniz gerekir, ancak neyse ki Excel 2013'te veri modeli bunu gerçekten çok kolay hale getirir. Bu kocaman masanın bu minik masayla iletişim kurmasına izin vermemiz gereken şey aracılar, ben onlara marangoz diyorum.Minik küçük tablolar, Ürün, Bölge ve Takvim, bütçeyi bu üç tabloya katacağız, gerçek olanı bu üç tabloyla birleştireceğiz ve mucizevi bir şekilde Pivot tablo çalışacak. Pekala, işte bunu nasıl yaptığımız.
Öncelikle birleştiricileri oluşturmam gerekiyor, bu yüzden bu Ürün alanını A sütunundan alıyorum ve F sütununa kopyalıyorum ve ardından Veri, Yinelemeleri Kaldır, Tamam'a tıklıyoruz ve küçük bir tabloyla kaldık, 1 3 satır başlık. Bölge için aynı şey, bölgeleri alın, Ctrl + C, sütun G'ye gidin, Yapıştır, Yinelenenleri Kaldır, Tamam'a tıklayın, 3 satır 1 başlık, tamam. Şimdi tarihler için, tarihler aynı değil, bunlar ay bitiş tarihleri, aslında ay bitiş tarihleri olarak saklanıyorlar ve bunlar hafta içi günler. Her iki listeyi de alacağım, Ctrl + C ikinci listeyi ve buraya yapıştıracağım, Ctrl + V, sonra daha kısa listeyi alacağım, kopyalayacağım ve aşağıya yapıştıracağım, pekala. Ve bu gerçekten sinir bozucu, bunlar tarih olarak saklansalar bile aylar olarak görünüyorlar ve Yinelenenleri Kaldır onları aynı şekilde görmez.Bu yüzden Yinelemeleri Kaldır'ı kullanmadan önce kısa bir tarihle değiştirmem gerekiyor. Bu verileri seçin, Verileri, Yinelemeleri Kaldır, Tamam'ı tıklayın ve ardından işe yaraması için burada biraz sıralayın.
Pekala, şimdi günlük tarihe göre rapor vermek istemiyorum, bu yüzden buraya bir sütun ekleyeceğim, Ay yazan bir arama sütunu ve bu tarih EOMONTH'a eşit olacak, 0, bu da bizi ayın sonu. Bunu kısa tarih olarak biçimlendirecek ve bunu bir yere kopyalayacaktır. Şimdi, bunların her birini bir Ctrl + T tablosuna dönüştürmemiz gerekiyor, bu yüzden buradan Ctrl + T, Masamın başlıkları var, güzel. Küçük olanlar, bunların yukarıda başlıklar olduğunun farkında değil, bu yüzden onu işaretlediğimizden emin olmalıyız ve Ctrl + T, tamam ve bu tablolara Tablo1, Tablo2, Tablo3 diyorlar, gerçekten sıkıcı isimler, değil mi? Bu yüzden bunları yeniden adlandıracağım ve ona BudTable, the ProdTable, RegTable, my CalTable ve ardından ActTable adını vereceğim.
İlk tablodan başlıyoruz ve bu arada bugün PowerPivot kullanmayacağımız için, tüm bunları veri modeliyle yapacağız. Yani, Excel 2013 veya daha yenisi, bu Ekleme, PivotTable'a sahipsiniz, "Bu verileri Veri Modeline ekle" kutusunu işaretleyeceğiz, Tamam'a tıklayacağız ve alan listemizi sihirli Tümü düğmesiyle alacağız. Çalışma kitabındaki Fiili, Bütçe, Takvim, Ürün, Bölge gibi beş tablonun tümünden seçim yapıyorum. Pekala, rakamlar Bütçe tablosundan gelecek, oraya bütçeyi koyacağım ve Gerçek tablodan da oraya gerçek olanı koyacağım, ama işte Pivot tablonun geri kalanı için. Satır alanına veya sütun alanına veya dilimleyici olarak koyacağımız diğer metin alanları birleştiricilerden gelmeli, tablolar arasındaki bu tablolardan gelmelidir.
Pekala, Takvim tablosundan o Ay alanını alacağız ve en üste koyacağız, şu anda diğer ilişkileri görmezden geleceğiz. İlişkileri ben yaratacağım ama hepsini bir kerede yaratmak istiyorum. Ve Bölge tablosu, bölgeleri yan tarafa koyun. Ürünleri yan tarafa koyabilirim, ama aslında Ürün tablosunu dilimleyici olarak kullanacağım, bu yüzden Analiz Et, Dilimleyici Ekle, Ürün tablosunu henüz kullanmadıysanız yine Tümü'ne gitmelisiniz. Öyleyse Tümüne gidin ve Ürünün, ürünlerden dilimleyici olarak oluşturulabileceğini göreceksiniz, bunun gibi. Pekala şimdi, bu noktada ilişki kurmadık, bu yüzden tüm bu sayılar yanlış. Ve yaratmamız gereken ilişkiler, bu küçük bütçe tablosundan 3 tablo oluşturmalıyız, biri ürünlere, bire bölgelere, bire takvime,bu 3 ilişki. Ve sonra, Gerçek tablodan Takvim'deki Ürün bölgesi ile ilişkiler oluşturmalıyız, yani toplam 6 tablo. Ve evet, PowerPivot'a sahip olsaydık bu kesinlikle daha kolay olurdu, ama yok ya da yok varsayalım.
Ve bu yüzden eski moda yöntemi kullanacağım, burada Oluştur diyalogu, solda Bütçe tablosunun olduğu yerde ve Bölge alanını kullanıp bunu Bölge tablosu, Bölge alanıyla ilişkilendireceğiz. . Pekala, 1/6 oluşturuldu. Tekrar Bütçe tablosundan Oluştur'u seçeceğim, Ürüne gidip bunu Ürün tablosuna, Ürüne bağlayıp Tamam'a tıklayın. Bütçe tablosundan Tarih alanına gidiyoruz, Takvim tablosuna gidiyoruz ve Kader alanında Tamam'a tıklayın, yolun yarısındayız, tamam. Gerçekleşenler tablosundan Bölge tablosuna, Bölge tablosuna Tamam'a tıklayın, Gerçek Değerler tablosundan Ürüne ve Gerçek Değerler tablosundan Takvime. Aslında Değerleri alacağım ve onu bir kenara bırakacağım, pekala. Tercih ettiğim bir görünümü elde etmek için Tasarım, Rapor Düzeni, Tablo Formunda Göster, Tüm Öğe Etiketlerini Tekrarla, pekala,bu kesinlikle harika! Şimdi bu minik tabloya sahibiz, bu tabloda yüzlerce kayıt içeren 50'den fazla kayıt var ve Veri Modeli sayesinde tek bir Pivot tablo oluşturduk. Bütçeyi görebildiğimiz her yer için Geliri görebiliriz, Bölgeye göre bölünmüştür, Ay'a göre bölünmüştür ve Ürüne göre dilimlenebilir.
Şimdi bu konsept bana Power Pivot Pro'yu çalıştıran Rob Collie'den geldi ve Rob orada pek çok kitap yarattı, sonuncusu “Power Pivot ve Power BI”. Sanırım bu aslında "Power Pivot Alchemy" kitabındaydı, bunu gördüğüm ve "Peki bu, Power Pivot aracılığıyla bildirmek için milyonlarca satırım olmasa da, uyumsuz boyutlarda iki veri kümesine sahip olduğum ve her ikisinden de rapor etmem gerektiği için hayatımda BÜYÜK bir fark yarattım. " Pekala, bu örnek ve diğerleri bu kitapta, eninde sonunda tüm kitap podcast'ini alacağım, iki buçuk ay alacak gibi görünüyor. Ama bugün kitabın tamamını aynı anda alabilirsin, oraya gidebilir, kitabı satın alabilirsin, e-kitap için 10 dolar, basılı kitap için 25 dolar ve tüm bu ipuçlarını aynı anda alabilirsin.
Pekala, burada gerçekten uzun bir bölüm var: Yukarıdan aşağıya küçük bir Bütçemiz var ve aşağıdan yukarıya Gerçek, farklı boyutlar, ancak Excel 2013'teki veri modelini kullanıyoruz … Bu arada, 2010'da iseniz teorik olarak bunu Power Pivot eklentisini alarak yapın ve tüm bu adımları 2010'da tekrar uygulayın. Her iki veri kümesini de bir Ctrl + T tablosunda yapın ve ardından raporlamak istediğiniz her şey için tablolarınızı satır etiketi veya sütun etiketi veya dilimleyiciler, bu nedenle bu değerleri kopyalayın ve tarihler için Yinelenenleri Kaldır. Aslında her iki tablodan da değerler aldım, çünkü her birinde bazı benzersiz değerler vardı ve sonra oraya çıkmak için EOMONTH'u kullandım, bu birleştirme tablolarını kontrollü tablolar haline getirdim. İsteğe bağlı, ancak 5 tablonun hepsini adlandırdım, çünkü bu ilişkileri kurarken Table1 olarak adlandırılmaktansa daha kolay,Tablo2, Tablo3.
Ve böylece, Bütçe tablosundan (Ekle, PivotTable) başlayın, Veri Modeli kutusunu işaretleyin ve ardından Bütçe ve Fiili kullanarak bir Pivot tablo oluşturun. Diğer her şey birleştirici tablolarından gelir, dolayısıyla satır ve sütun alanında Bölge ve Ay, Ürün tablosundan dilimleyiciler gelir. Ve sonra Bütçeden marangozlara 3 ilişki, Asıldan birleştiricilere 3 ilişki oluşturmak zorunda kaldık ve harika bir Pivot tablomuz var. Şimdi yarın Power Pivot sekmesini kullanmaya ve bazı ek hesaplamalar oluşturmaya bir göz atacağız. Yani tüm bunlar mümkün, hesaplanan bir alan eklemek istediğimizde, Office 365'in Pro Plus sürümünü edinmek için ayda fazladan 2 $ ödemeniz gerektiği zamandır.
Peki hey, bu ipucu için Power Pivot Pro'dan Rob Collie'ye teşekkürler ve uğradığınız için teşekkürler, bir dahaki sefere başka bir internet yayını için görüşürüz!
Dosyayı indir
Örnek dosyayı buradan indirin: Podcast2016.xlsx
