Bir neçə kitabdan fərqli başlıqlarla cədvəllər yaradın

Problemin formalaşdırılması

Bir qovluqda bir neçə faylımız var (nümunəmizdə – 4 ədəd, ümumi halda – istədiyiniz qədər). Hesabatlar:

İçəridə bu fayllar belə görünür:

Burada:

• Bizə lazım olan məlumat vərəqi həmişə çağırılır pics, lakin iş kitabının istənilən yerində ola bilər.
  
• Vərəqdən kənarda pics Hər bir kitabda başqa vərəqlər ola bilər.
• Məlumatları olan cədvəllər fərqli sayda sıraya malikdir və iş vərəqində fərqli cərgə ilə başlaya bilər.
• Fərqli cədvəllərdə eyni sütunların adları fərqli ola bilər (məsələn, Kəmiyyət = Kəmiyyət = Miqdar).
• Cədvəllərdəki sütunlar fərqli qaydada yerləşdirilə bilər.

Tapşırıq: vərəqdəki bütün fayllardan satış məlumatlarını toplayın pics sonradan onun üzərində xülasə və ya hər hansı digər analitika yaratmaq üçün ümumi cədvələ daxil edin.

Addım 1. Sütun adları kataloqunun hazırlanması

Ediləcək ilk şey, sütun adları və onların düzgün şərhi üçün bütün mümkün variantları olan bir arayış kitabı hazırlamaqdır:

Nişandakı Format kimi cədvəl düyməsini istifadə edərək bu siyahını dinamik “ağıllı” cədvələ çeviririk Əsas səhifə (Ev - Cədvəl kimi format) və ya klaviatura qısa yolu Ctrl+T əmri ilə Power Query-ə yükləyin Data – Cədvəldən/Aralıqdan (Məlumat - Cədvəldən/Aralıqdan). Excelin son versiyalarında onun adı dəyişdirilib Yarpaqları ilə (vərəqdən).

Power Query sorğu redaktoru pəncərəsində biz ənənəvi olaraq addımı silirik Dəyişdirilmiş Növ və düyməni basaraq onun yerinə yeni bir addım əlavə edin f_xdüstur çubuğunda (əgər görünmürsə, onu nişanda aktivləşdirə bilərsiniz baxış) və daxili Power Query dilində M formulunu daxil edin:

=Cədvəl.Satırlar(Mənbə)

Bu əmr əvvəlki addımda yüklənmişi çevirəcək mənbə arayış cədvəli hər biri öz növbəsində bir cüt dəyər olan iç içə siyahılardan (Siyahı) ibarət siyahıya oldu - oldu bir sətirdən:

Bu tip məlumatlara bir az sonra, bütün yüklənmiş cədvəllərdən başlıqların adının kütləvi şəkildə dəyişdirilməsi zamanı lazım olacaq.

Dönüşüm tamamlandıqdan sonra əmrləri seçin Əsas səhifə — Bağlayın və Yükləyin — Bağlayın və Yükləyin… və idxalın növü Sadəcə əlaqə yaradın (Ev - Bağla və Yüklə - Bağla və Yüklə... - Yalnız əlaqə yarat) və Excel-ə qayıdın.

Addım 2. Biz bütün fayllardan hər şeyi olduğu kimi yükləyirik

İndi bütün fayllarımızın məzmununu qovluqdan yükləyək – hələlik olduğu kimi. Komandaların seçilməsi Məlumat - Məlumat əldə et - Fayldan - Qovluqdan (Məlumat - Məlumat əldə et - Fayldan - Qovluqdan) və sonra mənbə kitablarımızın olduğu qovluq.

Önizləmə pəncərəsində vurun Çevirmək (çevir) or Dəyişdirmək (Edit):

Və sonra bütün yüklənmiş faylların məzmununu genişləndirin (İkili) sütun başlığında ikiqat oxları olan düymə məzmun:

Birinci faylın nümunəsində Power Query (Vostok.xlsx) bizdən hər bir iş kitabından götürmək istədiyimiz vərəqin adını soruşacaq – seçin pics və OK düyməsini basın:

Bundan sonra (əslində) istifadəçi üçün aydın olmayan bir neçə hadisə baş verəcək, nəticələri sol paneldə aydın görünür:

1. Power Query qovluqdan ilk faylı götürəcək (bizdə olacaq Vostok.xlsx — görmək Fayl nümunəsi) nümunə kimi və sorğu yaradaraq məzmununu idxal edir Nümunə faylını çevirin. Bu sorğu kimi bir neçə sadə addımdan ibarət olacaq mənbə (fayl girişi) naviqasiya (vərəq seçimi) və bəlkə də başlıqların artırılması. Bu sorğu yalnız bir xüsusi fayldan data yükləyə bilər Vostok.xlsx.
   
2. Bu sorğu əsasında onunla əlaqəli funksiya yaradılacaq Fayl çevirmək (xarakterik bir işarə ilə göstərilir f_x), burada mənbə faylı artıq sabit deyil, dəyişən dəyər – parametr olacaq. Beləliklə, bu funksiya arqument kimi daxil etdiyimiz hər hansı bir kitabdan məlumatları çıxara bilər.
   
3. Funksiya sütundan hər bir fayla (İkili) növbə ilə tətbiq olunacaq məzmun – addım buna cavabdehdir Fərdi funksiyaya zəng edin faylların siyahısına sütun əlavə edən sorğumuzda Fayl çevirmək hər bir iş kitabından idxal nəticələri ilə:

   

4. Əlavə sütunlar silinir.
5. İçəri daxil edilmiş cədvəllərin məzmunu genişləndirilir (addım Genişləndirilmiş cədvəl sütunu) – və biz bütün kitablardan məlumatların toplanmasının yekun nəticələrini görürük:

   
   

Addım 3. Zımpara

Əvvəlki ekran görüntüsü açıq şəkildə göstərir ki, birbaşa montaj "olduğu kimi" keyfiyyətsizdir:

• Sütunlar tərsinə çevrilir.
• Çoxlu əlavə xətlər (boş və yalnız deyil).
• Cədvəl başlıqları başlıq kimi qəbul edilmir və verilənlərlə qarışdırılır.
  

Bütün bu problemləri çox asanlıqla həll edə bilərsiniz – sadəcə Nümunə Faylı Çevir sorğusunu düzəldin. Bizim ona etdiyimiz bütün düzəlişlər avtomatik olaraq əlaqəli Faylı Çevir funksiyasına düşəcək, yəni hər bir fayldan verilənlərin idxalı zamanı daha sonra istifadə olunacaq.

Sorğu açmaqla Nümunə faylını çevirin, lazımsız sıraları filtrləmək üçün addımlar əlavə edin (məsələn, sütuna görə Column2) və düymə ilə başlıqları qaldırın Başlıq kimi ilk sətirdən istifadə edin (Birinci sıranı başlıq kimi istifadə edin). Cədvəl daha yaxşı görünəcək.

Fərqli fayllardakı sütunların daha sonra avtomatik olaraq bir-birinin altına sığması üçün onlar eyni adlandırılmalıdır. Siz M kodunun bir sətri ilə əvvəllər yaradılmış kataloqa uyğun olaraq belə bir kütləvi ad dəyişdirə bilərsiniz. Düyməni yenidən basaq f_x düstur çubuğunda və dəyişdirmək üçün funksiya əlavə edin:

= Cədvəl.Sütunların adını dəyişdirin(#”Yüksək Başlıqlar”, Başlıqlar, MissingField.İqnor)

Bu funksiya əvvəlki addımdan cədvəli götürür Yüksək başlıqlar və içindəki bütün sütunların adını iç içə axtarış siyahısına uyğun olaraq dəyişdirir Headlines. Üçüncü arqument MissingField.İqnor kataloqda olan, lakin cədvəldə olmayan başlıqlarda xəta baş verməməsi üçün lazımdır.

Əslində, hamısı budur.

Müraciətə qayıdırıq Hesabatlar tamamilə fərqli bir şəkil görəcəyik - əvvəlkindən daha gözəl:

• Power Query, Power Pivot, Power BI nədir və Excel istifadəçisinin onlara nə üçün ehtiyacı var
  
• 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ı