Veri Modeline Göre Pivot'un Faydaları

Excel'de bir pivot tablo oluştururken, ilk aralığı ayarlamamız ve pivot tabloyu eklemek için bir yer seçmemizin istendiği ilk iletişim kutusunda, aşağıda göze çarpmayan ama çok önemli bir onay kutusu vardır - Bu verileri Veri Modeline ekleyin (Bu verileri ekleyin Veri Modeline) ve biraz daha yüksek, anahtar Bu kitabın veri modelini kullanın (Bu çalışma kitabının Veri Modelini kullanın):

Ne yazık ki, pivot tablolara uzun süredir aşina olan ve bunları işlerinde başarıyla kullanan birçok kullanıcı, bazen bu seçeneklerin anlamını gerçekten anlamamakta ve hiç kullanmamaktadır. Ve boşuna. Sonuçta, Veri Modeli için bir pivot tablo oluşturmak, klasik Excel pivot tablosuna kıyasla bize çok önemli birkaç avantaj sağlıyor.

Ancak, bu “çörekleri” yakından incelemeden önce, bu Veri Modelinin aslında ne olduğunu anlayalım.

Veri Modeli Nedir?

Veri örneği (MD veya DM = Veri Modeli olarak kısaltılır), bir Excel dosyası içinde, istenirse birbirine bağlı bir veya daha fazla tablo olan tablo verilerini depolayabileceğiniz özel bir alandır. Aslında bu, bir Excel çalışma kitabının içine yerleştirilmiş küçük bir veritabanıdır (OLAP küpü). Verilerin Excel sayfalarında normal (veya akıllı) tablolar biçimindeki klasik depolanmasıyla karşılaştırıldığında, Veri Modelinin birkaç önemli avantajı vardır:

• tablolar kadar olabilir 2 milyar satırve bir Excel sayfası 1 milyondan biraz fazla sığabilir.
• Devasa boyutuna rağmen, bu tür tabloların işlenmesi (filtreleme, sıralama, üzerlerinde hesaplamalar, yapı özeti vb.) çok hızlı Excel'in kendisinden çok daha hızlı.
• Modeldeki veriler ile ek (isteniyorsa çok karmaşık) hesaplamalar yapabilirsiniz. yerleşik DAX dili.
  
• Veri Modeline yüklenen tüm bilgiler çok kuvvetle sıkıştırılmış özel bir yerleşik arşivleyici kullanarak ve orijinal Excel dosyasının boyutunu oldukça orta derecede artırır.
  

Model, Microsoft Excel'de yerleşik özel bir eklenti tarafından yönetilir ve hesaplanır. PowerPivothakkında zaten yazdım. Etkinleştirmek için sekmede geliştirici tıklayın COM eklentileri (Geliştirici — COM Eklentileri) ve uygun kutuyu işaretleyin:

Eğer sekmeler geliştirici (Geliştirici)şeritte göremezsin, açabilirsin Dosya – Seçenekler – Şerit Kurulumu (Dosya — Seçenekler — Şeridi Özelleştir). COM eklentileri listesinde yukarıda gösterilen pencerede Power Pivot'unuz yoksa, Microsoft Office sürümünüze dahil değildir 🙁

Görünen Power Pivot sekmesinde, büyük bir açık yeşil düğme olacaktır. Yönetim (Üstesinden gelmek), Excel'in üstündeki Power Pivot penceresini açacak olana tıklayarak, mevcut kitabın Veri Modelinin içeriğini göreceğiz:

Bu arada önemli bir not: Bir Excel çalışma kitabı yalnızca bir Veri Modeli içerebilir.

Tabloları Veri Modeline yükleyin

Modele veri yüklemek için önce tabloyu dinamik bir “akıllı” klavye kısayoluna dönüştürüyoruz. Ctrl+T ve sekmede ona kolay bir ad verin Inşaatçı (Tasarım). Bu gerekli bir adımdır.

Ardından, seçim yapmak için üç yöntemden herhangi birini kullanabilirsiniz:

• düğmesine basın Modele Ekle (Veri Modeline Ekle) çıkıntı PowerPivot çıkıntı Ana Sayfa (Ev).
  
• Takım seçimi Ekle – Özet Tablo (Ekle — Özet Tablo) ve onay kutusunu açın Bu verileri Veri Modeline ekleyin (Bu verileri Veri Modeline ekleyin). Bu durumda Modele yüklenen verilere göre hemen bir pivot tablo da oluşturulur.
  
• Gelişmiş sekmesinde Veri (Tarih) düğmeye tıklayın Tablodan/Aralıktan (Tablodan/Aralıktan)tablomuzu Power Query düzenleyicisine yüklemek için. Bu yol en uzundur, ancak istenirse burada Power Query'nin çok güçlü olduğu ek veri temizleme, düzenleme ve her türlü dönüştürme işlemini gerçekleştirebilirsiniz.

  Daha sonra taranan veriler komut ile Modele yüklenir. Ana Sayfa — Kapat ve Yükle — Kapat ve Yükle… (Ana Sayfa — Kapat&Yükle — Kapat&Yükle…). Açılan pencerede seçeneği seçin Sadece bir bağlantı oluşturun (Yalnızca bağlantı oluşturun) ve en önemlisi, bir onay işareti koyun Bu verileri Veri Modeline ekleyin (Bu verileri Veri Modeline ekleyin).
  

Veri Modelinin bir özetini oluşturuyoruz

Özet Veri Modeli oluşturmak için üç yaklaşımdan birini kullanabilirsiniz:

• butona basın özet tablo (Pivot tablo) Power Pivot penceresinde.
• Excel'de komutları seçin Ekle – Özet Tablo ve moda geç Bu kitabın veri modelini kullanın (Ekle — Özet Tablo — Bu çalışma kitabının Veri Modelini kullanın).
• Takım seçimi Ekle – Özet Tablo (Ekle — Özet Tablo) ve onay kutusunu açın Bu verileri Veri Modeline ekleyin (Bu verileri Veri Modeline ekleyin). Mevcut “akıllı” tablo Modele yüklenecek ve tüm Model için bir özet tablo oluşturulacaktır.

Artık Veri Modeline veri yüklemeyi ve bunun üzerine bir özet oluşturmayı öğrendiğimize göre, bunun bize sağladığı yararları ve avantajları inceleyelim.

Fayda 1: Formül kullanmadan tablolar arasındaki ilişkiler

Normal bir özet, yalnızca bir kaynak tablodaki veriler kullanılarak oluşturulabilir. Bunlardan birkaçına sahipseniz, örneğin, satışlar, fiyat listesi, müşteri dizini, sözleşmelerin kaydı, vb., o zaman önce DÜŞEYARA gibi işlevleri kullanarak tüm tablolardan verileri tek bir tabloda toplamanız gerekir. (DÜŞEYARA), İNDEKS (İNDEKS), DAHA FAZLA MARUZ KALMIŞ (KİBRİT), YAZSLIMN (TOPLA) ve benzerleri. Bu uzun, sıkıcı ve Excel'inizi büyük miktarda veri içeren bir "düşünceye" sürükler.

Veri Modelinin bir özeti durumunda, her şey çok daha basittir. Tablolar arasındaki ilişkileri Power Pivot penceresinde bir kez ayarlamak yeterlidir ve işlem tamamdır. Bunu yapmak için sekmede PowerPivot düğmesine basın Yönetim (Üstesinden gelmek) ve ardından beliren pencerede – düğmesi Grafik Görünümü (Diyagram Görünümü). Geriye, bağlantılar oluşturmak için tablolar arasında ortak (anahtar) sütun adlarını (alanları) sürüklemek kalır:

Bundan sonra, Veri Modeli özetinde, ilgili tablolardan herhangi bir alanı özet alanına (satırlar, sütunlar, filtreler, değerler) atabilirsiniz - her şey otomatik olarak bağlanacak ve hesaplanacaktır:

Avantaj 2: Benzersiz değerleri sayın

Normal bir pivot tablo bize birkaç yerleşik hesaplama işlevinden birini seçme fırsatı verir: toplam, ortalama, sayım, minimum, maksimum, vb. benzersiz sayısı (tekrarlanmayan değerler). Örneğin, onun yardımıyla, her şehirde sattığımız benzersiz ürünlerin (aralık) sayısını kolayca sayabilirsiniz.

Alana sağ tıklayın - komut Değer alanı seçenekleri ve sekmede Çalışma Klinik Farklı elemanların sayısı (Farklı sayı):

Avantaj 3: Özel DAX Formülleri

Bazen pivot tablolarda çeşitli ek hesaplamalar yapmanız gerekir. Normal özetlerde bu, hesaplanan alanlar ve nesneler kullanılarak yapılırken, veri modeli özeti özel bir DAX dilinde (DAX = Veri Analizi İfadeleri) ölçüler kullanır.

Bir ölçü oluşturmak için sekmede öğesini seçin. PowerPivot Komuta Ölçüler – Ölçü Oluştur (Önlemler — Yeni önlem) veya Özet Alanlar listesindeki tabloya sağ tıklayın ve ölçü ekle (Ölçü ekle) bağlam menüsünde:

Açılan pencerede şunları ayarlayın:

• Tablo ismioluşturulan ölçünün depolanacağı yer.
• Ölçü adı – yeni alan için anladığınız herhangi bir isim.
• Açıklama - isteğe bağlı.
• formül – en önemli şey, çünkü burada ya manuel olarak giriyoruz ya da butona tıklıyoruz fx ve listeden bir DAX işlevi seçin; bu, daha sonra ölçümümüzü Değerler alanına attığımızda sonucu hesaplamalıdır.
• Pencerenin alt kısmında, listedeki ölçü için sayı biçimini hemen ayarlayabilirsiniz. Kategoriler.
  

DAX dilini anlamak her zaman kolay değildir, çünkü tek tek değerlerle değil, tüm sütunlar ve tablolarla çalışır, yani klasik Excel formüllerinden sonra bir düşüncenin yeniden yapılandırılmasını gerektirir. Bununla birlikte, buna değer, çünkü büyük miktarda veriyi işleme yeteneklerinin gücünü abartmak zordur.

Avantaj 4: Özel alan hiyerarşileri

Genellikle standart raporlar oluştururken, aynı alan kombinasyonlarını belirli bir sırayla pivot tablolara atmanız gerekir, örneğin Yıl-Çeyrek-Ay-Günya da Kategori-Ürünya da Ülke-Şehir-Müşteri vb. Veri Modeli özetinde, bu sorun kendi modelinizi oluşturarak kolayca çözülür. hiyerarşileri - özel alan kümeleri.

Power Pivot penceresinde, düğmesiyle grafik moduna geçin Grafik Görünümü çıkıntı Ana Sayfa (Ana Sayfa — Diyagram Görünümü), ile seç Ctrl istenen alanlar ve üzerlerine sağ tıklayın. Bağlam menüsü komutu içerecektir Hiyerarşi Oluştur (Hiyerarşi oluşturun):

Oluşturulan hiyerarşi yeniden adlandırılabilir ve gerekli alanlar fare ile içine sürüklenebilir, böylece daha sonra bir harekette özete atılabilirler:

Avantaj 5: Özel şablonlar

Bir önceki paragrafın fikrine devam ederek, Veri Modelinin özetinde, her alan için kendi öğe kümelerinizi de oluşturabilirsiniz. Örneğin, tüm şehir listesinden, yalnızca sorumluluk alanınızdakileri kolayca bir dizi yapabilirsiniz. Veya sadece müşterilerinizi, mallarınızı vb. özel bir sette toplayın.

Bunu yapmak için sekmede Pivot tablo analizi açılır listede Alanlar, Öğeler ve Kümeler karşılık gelen komutlar var (Analiz et - Alanlar, Items & Sets — Satır/sütun öğelerine göre küme oluşturun):

Açılan pencerede, herhangi bir öğeyi seçerek kaldırabilir, ekleyebilir veya konumlarını değiştirebilir ve elde edilen kümeyi yeni bir adla kaydedebilirsiniz:

Oluşturulan tüm kümeler, PivotTable Alanları panelinde, herhangi bir yeni PivotTable'ın satır ve sütun alanlarına serbestçe sürüklenebilecekleri ayrı bir klasörde görüntülenecektir:

Avantaj 6: Tabloları ve Sütunları Seçerek Gizle

Bu, bazı durumlarda küçük ama çok hoş bir avantaj olmasına rağmen. Power Pivot penceresindeki alan adına veya tablo sekmesine sağ tıklayarak komutu seçebilirsiniz. İstemci Araç Setinden Gizle (İstemci Araçlarından Gizle):

Gizli sütun veya tablo, Özet Tablo Alan Listesi bölmesinden kaybolur. Kullanıcıdan bazı yardımcı sütunları (örneğin, ilişki oluşturmak için hesaplanmış veya anahtar değerlere sahip sütunlar) veya hatta tüm tabloları gizlemeniz gerekiyorsa çok uygundur.

Avantaj 7. Gelişmiş detaya inme

Normal bir pivot tablodaki değer alanındaki herhangi bir hücreye çift tıklarsanız, Excel, bu hücrenin hesaplanmasında yer alan kaynak veri parçasının bir kopyasını ayrı bir sayfada görüntüler. Bu, resmi olarak Detaya Gitme olarak adlandırılan çok kullanışlı bir şeydir (genellikle "başarısız" derler).

Veri Modeli özetinde, bu kullanışlı araç daha ustaca çalışır. Bizi ilgilendiren sonucun bulunduğu herhangi bir hücrenin üzerinde durarak, yanında açılan bir büyüteçle simgeye tıklayabilirsiniz (buna denir. Ekspres Trendler) ve ardından ilgili herhangi bir tabloda ilgilendiğiniz herhangi bir alanı seçin:

Bundan sonra mevcut değer (Model = Explorer) filtre alanına girecek ve özet ofisler tarafından oluşturulacaktır:

Tabii ki, böyle bir prosedür birçok kez tekrarlanabilir ve verilerinizi sürekli olarak ilgilendiğiniz yönde inceler.

Avantaj 8: Pivotu Küp İşlevlerine Dönüştür

Veri Modeli için özette herhangi bir hücreyi seçerseniz ve ardından sekmede Pivot tablo analizi Komuta OLAP Araçları – Formüllere Dönüştür (Analiz et — OLAP Araçları — Formüllere dönüştür), ardından tüm özet otomatik olarak formüllere dönüştürülür. Artık satır-sütun alanındaki alan değerleri ve değer alanındaki sonuçlar, özel küp işlevleri kullanılarak Veri Modelinden alınacaktır: KÜP DEĞERİ ve KÜPÜYE:

Teknik olarak bu, artık bir özetle değil, formüllü birkaç hücreyle uğraştığımız anlamına gelir, yani özette bulunmayan herhangi bir dönüşümü raporumuzla kolayca yapabiliriz, örneğin ortasına yeni satırlar veya sütunlar ekleyin raporun özetinin içinde herhangi bir ek hesaplama yapın, bunları istediğiniz şekilde düzenleyin, vb.

Aynı zamanda, kaynak verilerle olan bağlantı elbette devam eder ve gelecekte kaynaklar değiştiğinde bu formüller güncellenecektir. Güzellik!

• Power Pivot ve Power Query ile bir pivot tabloda plan-olgu analizi
• Çok satırlı başlık içeren pivot tablo
• Power Pivot kullanarak Excel'de bir veritabanı oluşturun