Power Query-də bir vərəqdən çoxformatlı cədvəllərin qurulması

Problemin formalaşdırılması

Giriş məlumatları olaraq, Excel faylımız var, burada vərəqlərdən birində aşağıdakı formada satış məlumatları olan bir neçə cədvəl var:

Qeyd:

• Müxtəlif ölçülü və müxtəlif məhsul dəstləri və bölgələr ilə sətir və sütunlarda heç bir çeşidləmə olmadan cədvəllər.
• Cədvəllər arasında boş xətlər qoyula bilər.
• Cədvəllərin sayı istənilən ola bilər.
  

İki mühüm fərziyyə. Güman edilir ki:

• Hər cədvəlin üstündə, birinci sütunda, cədvəldə satışını göstərən menecerin adı var (İvanov, Petrov, Sidorov və s.)
  
• Bütün cədvəllərdə malların və regionların adları eyni qaydada - hərf dəqiqliyi ilə yazılır.
  

Yekun məqsəd bütün cədvəllərdən məlumatları sonrakı təhlil və xülasə yaratmaq üçün əlverişli olan bir düz normallaşdırılmış cədvələ toplamaqdır, yəni bu cədvəldə:

Addım 1. Fayla qoşulun

Gəlin yeni boş Excel faylı yaradaq və nişanda onu seçək Tarix Komanda Məlumat əldə edin - Fayldan - Kitabdan (Məlumat - Fayldan - İş kitabından). Satış məlumatları ilə mənbə faylının yerini göstərin və sonra naviqator pəncərəsində bizə lazım olan vərəqi seçin və düyməni basın Data çevirmək (Məlumatların Transformasiyası):

Nəticədə, ondan bütün məlumatlar Power Query redaktoruna yüklənməlidir:

Addım 2. Zibil qutusunu təmizləyin

Avtomatik olaraq yaradılan addımları silin dəyişdirilmiş növü (Dəyişən Növ) и Yüksək başlıqlar (Təqdim edilmiş Başlıqlar) və bir filtrdən istifadə edərək boş sətirlərdən və cəmi olan xətlərdən xilas olun null и TOTAL birinci sütuna görə. Nəticədə aşağıdakı şəkli alırıq:

Addım 3. Menecerlərin əlavə edilməsi

Daha sonra kimin satışlarının harada olduğunu başa düşmək üçün cədvəlimizə hər sətirdə müvafiq soyadın olacağı bir sütun əlavə etmək lazımdır. Bunun üçün:

1. Komandadan istifadə edərək sətir nömrələri olan köməkçi sütun əlavə edək Sütun əlavə et - İndeks Sütunu - 0-dən (Sütun əlavə et - İndeks sütunu - 0-dən).

2. Komanda ilə düsturlu bir sütun əlavə edin Sütun əlavə etmək - Fərdi Sütun (Sütun əlavə et - Fərdi sütun) və orada aşağıdakı tikintini təqdim edin:

Bu düsturun məntiqi sadədir – əgər birinci sütundakı növbəti xananın dəyəri “Məhsul” olarsa, bu o deməkdir ki, biz yeni cədvəlin başlanğıcı ilə rastlaşdıq, ona görə də əvvəlki xananın dəyərini “Məhsul” ilə göstəririk. menecerin adı. Əks halda, biz heç nə göstərmirik, yəni null.

Soyadı olan ana xananı əldə etmək üçün əvvəlcə əvvəlki addımdakı cədvələ müraciət edirik #"İndeks əlavə edildi", və sonra bizə lazım olan sütunun adını göstərin [Sütun1] kvadrat mötərizədə və bu sütundakı xana nömrəsi qıvrımlı mötərizədə. Hüceyrə nömrəsi sütundan götürdüyümüz cari rəqəmdən bir az olacaq indeks, müvafiq olaraq.

3. Boş xanaları doldurmaq qalır null əmri ilə yuxarı xanalardan adlar Transform - Doldur - Aşağı (Çevr - Doldur - Aşağı) və indeksləri olan artıq lazım olmayan sütunu və birinci sütunda soyadları olan sətirləri silin. Nəticədə əldə edirik:

Addım 4. Menecerlər tərəfindən ayrı-ayrı cədvəllərdə qruplaşdırılması

Növbəti addım hər bir menecer üçün sətirləri ayrı-ayrı cədvəllərdə qruplaşdırmaqdır. Bunu etmək üçün Transformasiya sekmesinde Qrupla əmrindən istifadə edin (Transform – Group By) və açılan pəncərədə Menecer sütununu və əməliyyatı seçin Bütün sətirlər (Bütün sətirlər) onlar (cəm, orta və s.). P.):

Nəticədə hər bir menecer üçün ayrıca cədvəllər alırıq:

Addım 5: İçəri daxil edilmiş cədvəlləri çevirin

İndi ortaya çıxan sütunun hər bir xanasında yerləşən cədvəlləri veririk Bütün məlumatlar layiqli formada.

Əvvəlcə hər cədvəldə artıq lazım olmayan sütunu silin Manager. Yenidən istifadə edirik Fərdi sütun nişanı Transformasiya (Çevr - Fərdi sütun) və aşağıdakı düstur:

Sonra, başqa bir hesablanmış sütunla, hər bir cədvəldəki ilk sətri başlıqlara qaldırırıq:

Və nəhayət, biz əsas transformasiyanı həyata keçiririk - M-funksiyasından istifadə edərək hər bir cədvəli açırıq Cədvəl.Digər Sütunları Açın:

Başlıqdakı bölgələrin adları yeni sütuna daxil olacaq və biz daha dar, lakin eyni zamanda daha uzun normallaşdırılmış cədvəl alacağıq. ilə boş xanalar null nəzərə alınmır.

Lazımsız ara sütunlardan qurtulmaqla bizdə:

Addım 6 İç-içə Cədvəlləri genişləndirin

Sütun başlığında ikiqat oxları olan düyməni istifadə edərək, bütün normallaşdırılmış iç-içə cədvəlləri tək siyahıya genişləndirmək qalır:

... və nəhayət istədiyimizi əldə etdik:

Əmrdən istifadə edərək əldə edilən cədvəli yenidən Excelə ixrac edə bilərsiniz Ə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ə...).

• Bir neçə kitabdan fərqli başlıqlarla cədvəllər yaradın
• Verilmiş qovluqdakı bütün fayllardan məlumatların toplanması
• Kitabın bütün vərəqlərindən məlumatların bir cədvəldə toplanması