Məlumat Modeli ilə Pivotun Faydaları

Excel-də pivot cədvəli qurarkən, bizdən ilkin diapazonu təyin etmək və pivot cədvəlini daxil etmək üçün yer seçmək tələb olunan ilk dialoq qutusunda aşağıda görünməz, lakin çox vacib bir onay qutusu var - Bu məlumatları Data Modelinə əlavə edin (Bu məlumatları əlavə edin Məlumat Modelinə) və bir az yuxarıda, keçid Bu kitabın məlumat modelindən istifadə edin (Bu iş kitabının Data Modelindən istifadə edin):

Təəssüf ki, pivot cədvəlləri ilə uzun müddətdir tanış olan və onlardan öz işlərində uğurla istifadə edən bir çox istifadəçi bəzən bu variantların mənasını həqiqətən başa düşmür və heç vaxt istifadə etmir. Və boş yerə. Axı, Data Modeli üçün pivot cədvəlinin yaradılması bizə klassik Excel pivot cədvəli ilə müqayisədə bir neçə çox mühüm üstünlük verir.

Bununla belə, bu “çörəkləri” yaxından nəzərdən keçirməzdən əvvəl gəlin əvvəlcə anlayaq ki, əslində bu Data Model nədir?

Məlumat modeli nədir

Məlumat modeli (MD və ya DM = Məlumat Modeli kimi qısaldılmışdır) Excel faylının içərisində cədvəl məlumatlarını saxlaya biləcəyiniz xüsusi sahədir – arzu olunarsa, bir-biri ilə əlaqəli bir və ya daha çox cədvəl. Əslində, bu, Excel iş kitabına daxil edilmiş kiçik verilənlər bazasıdır (OLAP kubu). Excel-in özünün vərəqlərində adi (və ya ağıllı) cədvəllər şəklində məlumatların klassik saxlanması ilə müqayisədə Məlumat Modeli bir sıra əhəmiyyətli üstünlüklərə malikdir:

• Cədvəllər qədər ola bilər 2 milyard xətt, və Excel vərəqi 1 milyondan bir qədər çox ola bilər.
• Nəhəng ölçülərə baxmayaraq, belə cədvəllərin emalı (süzgəcdən keçirmə, çeşidləmə, onlar üzrə hesablamalar, tikinti xülasəsi və s.) həyata keçirilir. çox sürətli Excel-in özündən daha sürətli.
• Modeldəki məlumatlarla, istifadə edərək əlavə (istəsəniz, çox mürəkkəb) hesablamalar apara bilərsiniz daxili DAX dili.
  
• Data Modelinə yüklənən bütün məlumatlar çox güclü sıxılmışdır xüsusi daxili arxivatordan istifadə edərək və orijinal Excel faylının ölçüsünü kifayət qədər orta dərəcədə artırır.
  

Model Microsoft Excel-də quraşdırılmış xüsusi əlavə ilə idarə olunur və hesablanır - powerpivothaqqında artıq yazmışam. Onu aktivləşdirmək üçün tabda geliştirici basın COM əlavələri (Tərtibatçı — COM Əlavələri) və müvafiq qutuyu yoxlayın:

Nişanlar varsa geliştirici (İnkişaf etdirici)lentdə onu görə bilməzsiniz, onu aça bilərsiniz Fayl - Seçimlər - Lent Quraşdırma (Fayl — Seçimlər — Lenti Fərdiləşdirin). COM əlavələri siyahısında yuxarıda göstərilən pəncərədə Power Pivot yoxdursa, o, Microsoft Office versiyanıza daxil edilməyib 🙁

Görünən Power Pivot sekmesinde böyük açıq yaşıl düymə olacaq idarə (İdarə et), üzərinə klikləməklə Excel-in yuxarı hissəsində Power Pivot pəncərəsi açılacaq, burada cari kitabın Məlumat Modelinin məzmununu görəcəyik:

Yol boyu vacib bir qeyd: Excel iş kitabında yalnız bir Məlumat Modeli ola bilər.

Cədvəlləri Data Modelinə yükləyin

Məlumatları Modelə yükləmək üçün əvvəlcə cədvəli dinamik “ağıllı” klaviatura qısayoluna çeviririk Ctrl+T və nişanda ona dost ad verin konstruktor (Dizayn). Bu tələb olunan addımdır.

Sonra seçmək üçün üç üsuldan hər hansı birini istifadə edə bilərsiniz:

• Düyməyə bas Modelə əlavə edin (Məlumat Modelinə əlavə edin) nişanı powerpivot nişanı Əsas səhifə (Ev).
  
• Komandaların seçilməsi Daxil et - Pivot Cədvəl (Daxil et - Pivot Cədvəl) və onay qutusunu yandırın Bu məlumatları Data Modelinə əlavə edin (Bu məlumatları Data Modelinə əlavə edin). Bu halda, Modelə yüklənmiş məlumatlara görə, pivot cədvəli də dərhal qurulur.
  
• Ətraflı sekmesinde Tarix (Tarix) düyməsini sıxın Cədvəldən/Aralıqdan (Cədvəldən/Aralıqdan)cədvəlimizi Power Query redaktoruna yükləmək üçün. Bu yol ən uzundur, lakin istəsəniz, burada Power Query-nin çox güclü olduğu əlavə məlumatların təmizlənməsi, redaktəsi və hər cür çevrilmələr edə bilərsiniz.

  Sonra daranmış məlumatlar komanda ilə Modelə yüklənir Əsas səhifə — Bağlayın və Yükləyin — Bağlayın və Yükləyin… (Ev — Bağla&Yüklə — Bağla&Yüklə...). Açılan pəncərədə seçimi seçin Sadəcə əlaqə yaradın (Yalnız əlaqə yaradın) və ən əsası, gənə qoyun Bu məlumatları Data Modelinə əlavə edin (Bu məlumatları Data Modelinə əlavə edin).
  

Məlumat Modelinin xülasəsini yaradırıq

Xülasə Məlumat Modeli yaratmaq üçün üç yanaşmadan hər hansı birini istifadə edə bilərsiniz:

• Düyməsini basın xülasə cədvəli (Pivot Cədvəli) Power Pivot pəncərəsində.
• Excel-də əmrləri seçin Daxil et - Pivot Cədvəl və rejimə keçin Bu kitabın məlumat modelindən istifadə edin (Daxil et - Pivot Cədvəl - Bu iş kitabının Məlumat Modelindən istifadə edin).
• Komandaların seçilməsi Daxil et - Pivot Cədvəl (Daxil et - Pivot Cədvəl) və onay qutusunu yandırın Bu məlumatları Data Modelinə əlavə edin (Bu məlumatları Data Modelinə əlavə edin). Cari “ağıllı” cədvəl Modelə yüklənəcək və bütün Model üçün xülasə cədvəli qurulacaq.

İndi biz Məlumat Modelinə məlumatları necə yükləməyi və onun üzərində xülasə qurmağı öyrəndik, gəlin bunun bizə verdiyi üstünlükləri və üstünlükləri araşdıraq.

Fayda 1: Düsturlardan istifadə etmədən cədvəllər arasında əlaqə

Müntəzəm xülasə yalnız bir mənbə cədvəlindəki məlumatlardan istifadə etməklə yaradıla bilər. Əgər sizdə onlardan bir neçəsi varsa, məsələn, satışlar, qiymət siyahısı, müştəri kataloqu, müqavilələrin reyestri və s., onda siz əvvəlcə VLOOKUP kimi funksiyalardan istifadə edərək bütün cədvəllərdən məlumat toplamalı olacaqsınız. (BAXIN), İNDEKS (İNDEKS), DAHA FAZLA (MAÇ), SUMMESLIMN (SUMIFS) və buna bənzər. Bu, uzun, yorucudur və Excel-i böyük miqdarda məlumatla "fikir" halına gətirir.

Məlumat Modelinin xülasəsi vəziyyətində hər şey daha sadədir. Power Pivot pəncərəsində cədvəllər arasında əlaqələri bir dəfə qurmaq kifayətdir – və bu, hazırdır. Bunu etmək üçün, nişanda powerpivot Düyməyə bas idarə (İdarə et) və sonra görünən pəncərədə – düymə Diaqram Görünüşü (Diaqram Görünüşü). Bağlantılar yaratmaq üçün ümumi (əsas) sütun adlarını (sahələrini) cədvəllər arasında sürükləmək qalır:

Bundan sonra, Məlumat Modeli üçün xülasədə, xülasə sahəsinə (sətirlər, sütunlar, filtrlər, dəyərlər) hər hansı əlaqəli cədvəllərdən istənilən sahələr ata bilərsiniz - hər şey avtomatik olaraq əlaqələndiriləcək və hesablanacaq:

Fayda 2: Unikal dəyərləri sayın

Adi pivot cədvəli bizə bir neçə daxili hesablama funksiyasından birini seçmək imkanı verir: cəm, orta, say, minimum, maksimum və s. Data Modelinin xülasəsində bu standart siyahıya çox faydalı funksiya əlavə olunur. unikal sayı (təkrar edilməyən dəyərlər). Onun köməyi ilə, məsələn, hər bir şəhərdə satdığımız unikal malların (aralıq) sayını asanlıqla saya bilərsiniz.

Sahəyə sağ vurun - əmr Dəyər sahəsi seçimləri və tabda Əməliyyat Seçmək Müxtəlif elementlərin sayı (Fərqli say):

Fayda 3: Fərdi DAX Formulaları

Bəzən pivot cədvəllərdə müxtəlif əlavə hesablamalar aparmalı olursunuz. Müntəzəm xülasələrdə bu, hesablanmış sahələr və obyektlərdən istifadə etməklə həyata keçirilir, məlumat modeli xülasəsi isə xüsusi DAX dilində ölçülərdən istifadə edir (DAX = Data Analizi İfadələri).

Ölçü yaratmaq üçün tabda seçin powerpivot Komanda Tədbirlər – Tədbir yaradın (Tədbirlər — Yeni tədbir) və ya sadəcə olaraq Pivot Fields siyahısında masanın üzərinə sağ klikləyin və seçin Ölçü əlavə edin (Ölçü əlavə et) kontekst menyusunda:

Açılan pəncərədə təyin edin:

• Cədvəlin adıyaradılmış tədbirin harada saxlanacağı.
• Ölçü adı – yeni sahə üçün anladığınız hər hansı ad.
• təsvir - istəyə görə.
• Formula – ən vacibi, çünki burada ya əl ilə daxil oluruq, ya da düyməni sıxırıq fx və sonra tədbirimizi Dəyərlər sahəsinə atdığımız zaman nəticəni hesablamalı olan siyahıdan DAX funksiyasını seçin.
• Pəncərənin aşağı hissəsində, siyahıdakı ölçü üçün rəqəm formatını dərhal təyin edə bilərsiniz kateqoriya.
  

DAX dilini başa düşmək həmişə asan olmur, çünki fərdi dəyərlərlə deyil, bütün sütunlar və cədvəllərlə işləyir, yəni klassik Excel düsturlarından sonra düşüncənin bir qədər yenidən qurulmasını tələb edir. Bununla belə, buna dəyər, çünki böyük miqdarda məlumatların emalında onun imkanlarının gücünü qiymətləndirmək çətindir.

Fayda 4: Fərdi sahə iyerarxiyaları

Çox vaxt, standart hesabatlar yaradarkən, məsələn, müəyyən bir ardıcıllıqla pivot cədvəllərinə eyni sahələrin birləşməsini atmalısınız. İl-Rüb-Ay-Günvə ya Kateqoriya-Məhsulvə ya Ölkə-Şəhər-Müştəri və s. Məlumat Modelinin xülasəsində bu problem özünüzü yaratmaqla asanlıqla həll olunur iyerarxiyalar - xüsusi sahə dəstləri.

Power Pivot pəncərəsində düymə ilə diaqram rejiminə keçin Diaqram Görünüşü nişanı Əsas səhifə (Ev — Diaqram Görünüşü), ilə seçin Ctrl istədiyiniz sahələri seçin və üzərinə sağ vurun. Kontekst menyusu əmrdən ibarət olacaq İyerarxiya yaradın (İyerarxiya yaradın):

Yaradılmış iyerarxiyanın adını dəyişdirmək və lazımi sahələri siçan ilə sürükləmək olar ki, sonradan bir hərəkətlə onlar xülasəyə atılsın:

Fayda 5: Xüsusi trafaretlər

Əvvəlki paraqrafın fikrini davam etdirərək, Məlumat Modelinin xülasəsində, həmçinin hər bir sahə üçün öz element dəstlərini yarada bilərsiniz. Məsələn, bütün şəhərlər siyahısından asanlıqla yalnız sizin məsuliyyət sahənizdə olanların dəstini yarada bilərsiniz. Və ya yalnız müştərilərinizi, mallarınızı və s.-ni xüsusi dəstdə toplayın.

Bunu etmək üçün, nişanda Pivot cədvəlinin təhlili açılan siyahıda Sahələr, elementlər və dəstlər müvafiq əmrlər var (Təhlil et - Fields, Items & Sets — Sətir/sütun elementləri əsasında dəst yaradın):

Açılan pəncərədə siz hər hansı elementi seçərək silə, əlavə edə və ya yerini dəyişə və nəticədə yaranan dəsti yeni adla saxlaya bilərsiniz:

Bütün yaradılmış dəstlər Pivot Cədvəl Sahələri panelində ayrı bir qovluqda göstəriləcək və oradan istənilən yeni Pivot Cədvəlin sətir və sütun sahələrinə sərbəst şəkildə sürüklənə bilər:

Fayda 6: Cədvəl və Sütunları Seçilmiş Gizlət

Baxmayaraq ki, bu, bəzi hallarda kiçik, lakin çox xoş bir üstünlükdür. Sahənin adına və ya Power Pivot pəncərəsindəki cədvəl nişanına sağ klikləməklə, əmri seçə bilərsiniz. Müştəri Alət dəstindən gizlədin (Müştəri Alətlərindən gizlət):

Gizli sütun və ya cədvəl Pivot Cədvəl Sahə Siyahısı panelindən yox olacaq. İstifadəçidən bəzi köməkçi sütunları (məsələn, hesablanmış və ya əlaqələr yaratmaq üçün əsas dəyərləri olan sütunlar) və ya hətta bütün cədvəlləri gizlətmək lazım olduqda çox rahatdır.

Fayda 7. Təkmil qazma

If you double-click on any cell in the value area in a regular pivot table, then Excel displays on a separate sheet a copy of the source data fragment that was involved in the calculation of this cell. This is a very handy thing, officially called Drill-down (in they usually say “fail”).

Məlumat Modelinin xülasəsində bu lazımlı alət daha incə işləyir. Bizi maraqlandıran nəticə ilə hər hansı bir xanada dayanaraq, yanında açılan böyüdücü şüşə ilə işarəni vura bilərsiniz (bu adlanır Ekspres Trendlər) və sonra hər hansı əlaqəli cədvəldə maraqlandığınız sahəni seçin:

Bundan sonra, cari dəyər (Model = Explorer) filtr sahəsinə daxil olacaq və xülasə ofislər tərəfindən qurulacaq:

Əlbəttə ki, belə bir prosedur dəfələrlə təkrarlana bilər, ardıcıl olaraq məlumatlarınızı maraqlandıran istiqamətdə araşdırın.

Fayda 8: Pivotu kub funksiyalarına çevirin

Məlumat Modeli üçün xülasədə hər hansı bir xana seçsəniz və sonra nişanı seçin Pivot cədvəlinin təhlili Komanda OLAP Alətləri - Formulalara çevirin (Təhlil et — OLAP Alətləri — Düsturlara çevir), sonra bütün xülasə avtomatik olaraq düsturlara çevriləcək. İndi sətir-sütun sahəsindəki sahə dəyərləri və dəyər sahəsindəki nəticələr xüsusi kub funksiyalarından istifadə edərək Data Modelindən alınacaq: KUBEVALU və KUBEMƏ:

Texniki olaraq, bu o deməkdir ki, biz indi xülasə ilə deyil, düsturlu bir neçə xana ilə məşğul oluruq, yəni xülasədə mövcud olmayan hesabatımızla asanlıqla istənilən transformasiya edə bilərik, məsələn, ortasına yeni sətir və ya sütunlar daxil edin hesabatın, xülasə daxilində hər hansı əlavə hesablamalar aparın, onları istədiyiniz şəkildə təşkil edin və s.

Eyni zamanda, mənbə məlumatları ilə əlaqə, əlbəttə ki, qalır və gələcəkdə bu düsturlar mənbələr dəyişdikdə yenilənəcəkdir. Gözəllik!

• Power Pivot və Power Query ilə pivot cədvəlində plan-fakt təhlili
• Çoxsətirli başlığı olan pivot cədvəli
• Power Pivot istifadə edərək Excel-də verilənlər bazası yaradın