Power Query ilə müxtəlif Excel fayllarından cədvəllərin yığılması

Problemin formalaşdırılması

Excel istifadəçilərinin əksəriyyətinin gec və ya tez qarşılaşdığı çox standart vəziyyətlərdən biri üçün gözəl bir həll yoluna baxaq: çox sayda fayldan məlumatları bir yekun cədvəldə tez və avtomatik toplamaq lazımdır. 

Tutaq ki, bizdə filial şəhərlərindən məlumatlar olan bir neçə fayldan ibarət aşağıdakı qovluq var:

Faylların sayının əhəmiyyəti yoxdur və gələcəkdə dəyişə bilər. Hər bir faylın adlı bir vərəqi var Satışməlumat cədvəlinin yerləşdiyi yer:

Cədvəllərdəki sıraların (sifarişlərin) sayı, əlbəttə ki, fərqlidir, lakin sütunlar dəsti hər yerdə standartdır.

Tapşırıq: şəhər fayllarını və ya cədvəllərdə sətirləri əlavə edərkən və ya silərkən sonradan avtomatik yenilənmə ilə bütün fayllardan məlumatları bir kitaba toplamaq. Yekun konsolidə edilmiş cədvələ əsasən, o zaman istənilən hesabatları, pivot cədvəlləri, məlumatları filtrlə çeşidləmək və s. qurmaq mümkün olacaq. Əsas odur ki, toplaya bilək.

Silahları seçirik

Həll üçün bizə Excel 2016-nın ən son versiyası (lazımi funksionallıq artıq standart olaraq quraşdırılmışdır) və ya pulsuz əlavə quraşdırılmış Excel 2010-2013-ün əvvəlki versiyalarına ehtiyacımız var. Güc sorğusu Microsoft-dan (buradan yükləyin). Power Query xarici dünyadan Excel-ə məlumat yükləmək, sonra onu çıxarmaq və emal etmək üçün super çevik və super güclü vasitədir. Power Query demək olar ki, bütün mövcud məlumat mənbələrini dəstəkləyir – mətn fayllarından SQL-ə və hətta Facebook-a qədər 🙂

Əgər sizdə Excel 2013 və ya 2016 yoxdursa, daha sonra oxuya bilməzsiniz (zarafat edir). Excel-in köhnə versiyalarında belə bir tapşırığı yalnız Visual Basic-də makro proqramlaşdırmaqla (bu, yeni başlayanlar üçün çox çətindir) və ya monoton əl ilə kopyalamaqla (uzun vaxt tələb edən və səhvlər yaradan) yerinə yetirilə bilər.

Addım 1. Bir faylı nümunə kimi idxal edin

Birincisi, nümunə olaraq bir iş kitabından məlumatları idxal edək ki, Excel "fikri qəbul etsin". Bunu etmək üçün yeni boş iş dəftəri yaradın və…

• Excel 2016 varsa, nişanı açın Tarix və sonra Sorğu yaradın - Fayldan - Kitabdan (Məlumat - Yeni Sorğu - Fayldan - Excel-dən)
• Əgər sizdə Power Query əlavəsi quraşdırılmış Excel 2010-2013 varsa, o zaman nişanı açın Güc sorğusu və üzərinə seçin Fayldan - Kitabdan (Fayldan — Excel-dən)

Sonra açılan pəncərədə hesabatların olduğu qovluğumuza keçin və şəhər fayllarından hər hansı birini seçin (hansısının fərqi yoxdur, çünki hamısı tipikdir). Bir neçə saniyədən sonra Naviqator pəncərəsi görünəcək, burada sol tərəfdə bizə lazım olan vərəqi (Satış) seçməlisiniz və onun məzmunu sağ tərəfdə göstəriləcək:

Bu pəncərənin aşağı sağ küncündəki düyməni sıxsanız Download (Yük), sonra cədvəl dərhal orijinal şəklində vərəqə idxal ediləcək. Tək bir fayl üçün bu yaxşıdır, lakin bir çox belə faylları yükləmək lazımdır, ona görə də bir az fərqli gedəcəyik və düyməni sıxacağıq. Islah (Edit). Bundan sonra Power Query sorğu redaktoru kitabdakı məlumatlarımızla ayrı bir pəncərədə göstərilməlidir:

Bu, cədvəli bizə lazım olan görünüşə "bitirməyə" imkan verən çox güclü bir vasitədir. Bütün funksiyalarının səthi təsviri belə təxminən yüz səhifə çəkəcək, lakin çox qısa olsa da, bu pəncərədən istifadə edərək aşağıdakıları edə bilərsiniz:

• lazımsız məlumatları, boş sətirləri, xətaları olan xətləri süzün
• məlumatları bir və ya bir neçə sütun üzrə çeşidləyin
• təkrardan qurtulun
• yapışqan mətni sütunlara bölün (ayırıcılara, simvolların sayına və s.)
• mətni sıraya qoyun (əlavə boşluqları çıxarın, hərfi düzəldin və s.)
• məlumat növlərini hər cür çevirin (mətn kimi nömrələri normal rəqəmlərə çevirin və əksinə)
• cədvəlləri köçürmək (döndürmək) və iki ölçülü çarpaz cədvəlləri düz olanlara genişləndirmək
• cədvələ əlavə sütunlar əlavə edin və Power Query-də quraşdırılmış M dilindən istifadə edərək onlarda düstur və funksiyalardan istifadə edin.
• ...

Məsələn, cədvəlimizə ayın mətn adı olan bir sütun əlavə edək ki, daha sonra pivot cədvəl hesabatlarını qurmaq daha asan olsun. Bunu etmək üçün sütun başlığına sağ klikləyin tarixvə əmri seçin Dublikat sütun (Dublikat Sütun), sonra görünən dublikat sütunun başlığına sağ klikləyin və Əmrlər seçin Transform - Ay - Ay adı:

Hər sətir üçün ayın mətn adları ilə yeni sütun formalaşdırılmalıdır. Sütun başlığına iki dəfə klikləməklə, onun adını dəyişə bilərsiniz Kopyalama tarixi daha rahat üçün ay, məsələn.

Əgər bəzi sütunlarda proqram məlumat növünü tam olaraq tanımayıbsa, onda hər bir sütunun sol tərəfindəki format işarəsini vuraraq ona kömək edə bilərsiniz:

Sadə bir filtrdən istifadə edərək səhvləri və ya boş sətirləri, həmçinin lazımsız menecerləri və ya müştəriləri xaric edə bilərsiniz:

Üstəlik, bütün həyata keçirilən çevrilmələr sağ paneldə sabitlənir, burada həmişə geriyə yuvarlana (çarpaz) və ya parametrlərini (dişli) dəyişdirə bilərsiniz:

Yüngül və zərif, elə deyilmi?

Addım 2. Sorğumuzu funksiyaya çevirək

Hər bir idxal edilmiş kitab üçün edilən bütün məlumat dəyişikliklərini sonradan təkrarlamaq üçün biz yaradılmış sorğumuzu funksiyaya çevirməliyik ki, bu da öz növbəsində bütün fayllarımıza tətbiq olunacaq. Bunu etmək əslində çox sadədir.

Sorğu redaktorunda Baxış sekmesine keçin və düyməni basın Qabaqcıl redaktor (Bax - Qabaqcıl Redaktor). Bütün əvvəlki hərəkətlərimizin M dilində kod şəklində yazılacağı bir pəncərə açılmalıdır. Nəzərə alın ki, misal üçün idxal etdiyimiz fayla gedən yol kodda sərt kodlaşdırılıb:

İndi bir neçə düzəliş edək:

Onların mənası sadədir: birinci sətir (fayl yolu)=> prosedurumuzu arqumentli funksiyaya çevirir fayl yolu, və aşağıda biz sabit yolu bu dəyişənin dəyərinə dəyişirik. 

Hamısı. Basın finiş və bunu görməlidir:

Məlumatların yoxa çıxmasından qorxmayın – əslində hər şey qaydasındadır, hər şey belə görünməlidir 🙂 Biz öz xüsusi funksiyamızı uğurla yaratdıq, burada məlumatların idxalı və emalı üçün bütün alqoritm konkret fayla bağlanmadan yadda saxlanılır. . Ona daha başa düşülən bir ad vermək qalır (məsələn əldə edin) sahədə sağdakı paneldə Ad və biçmək olar Əsas səhifə — Bağlayın və endirin (Ev - Bağla və Yüklə). Nəzərə alın ki, nümunə üçün idxal etdiyimiz fayla gedən yol kodda sərt kodlaşdırılıb. Siz Microsoft Excel-in əsas pəncərəsinə qayıdacaqsınız, lakin sağda bizim funksiyamızla yaradılmış əlaqəsi olan panel görünməlidir:

Addım 3. Bütün faylların toplanması

Ən çətin hissəsi geridə qaldı, xoş və asan hissəsi qaldı. Taba keçin Məlumat - Sorğu yarat - Fayldan - Qovluqdan (Məlumat - Yeni Sorğu - Fayldan - Qovluqdan) və ya Excel 2010-2013 varsa, tabdakı kimi Güc sorğusu. Görünən pəncərədə bütün mənbə şəhər fayllarımızın yerləşdiyi qovluğu göstərin və üzərinə klikləyin OK. Növbəti addım bu qovluqda (və onun alt qovluqlarında) olan bütün Excel fayllarının və onların hər biri üçün təfərrüatların siyahıya alınacağı bir pəncərə açmalıdır:

Basın Dəyişdirmək (Edit) və yenidən tanış sorğu redaktoru pəncərəsinə daxil oluruq.

İndi cədvəlimizə yaradılan funksiyamızla başqa bir sütun əlavə etməliyik ki, bu da hər bir fayldan məlumatları “çəkəcək”. Bunu etmək üçün nişana keçin Sütun əlavə et - Xüsusi Sütun (Sütun əlavə et - Fərdi Sütun əlavə et) və görünən pəncərədə funksiyamızı daxil edin əldə edin, bunun üçün hər bir faylın tam yolunu arqument kimi göstərərək:

Klikdən sonra OK yaradılmış sütun sağdakı cədvəlimizə əlavə edilməlidir.

İndi bütün lazımsız sütunları silək (Excel-də olduğu kimi, sağ siçan düyməsini istifadə edərək – Aradan qaldırılması), yalnız əlavə edilmiş sütunu və fayl adı olan sütunu tərk edin, çünki bu ad (daha doğrusu, şəhər) hər bir sıra üçün ümumi məlumatda olması faydalı olacaq.

İndi isə “vay an” – funksiyamızla əlavə edilmiş sütunun yuxarı sağ küncündə öz oxları olan ikona klikləyin:

… işarəni çıxarın Orijinal sütun adından prefiks kimi istifadə edin (Prefiks olaraq orijinal sütun adından istifadə edin)basın OK. Və bizim funksiyamız qeydə alınmış alqoritmə əməl edərək və hər şeyi ümumi cədvəldə toplayaraq hər bir fayldan məlumatları yükləyib emal edəcək:

Tam gözəllik üçün siz həmçinin fayl adları olan birinci sütundan .xlsx uzantılarını silə bilərsiniz – standart “heç nə” ilə əvəz etməklə (sütun başlığına sağ klikləyin – Əvəz) və bu sütunun adını dəyişdirin şəhər. Həm də tarixlə sütundakı məlumat formatını düzəldin.

Hamısı! Basın Ev - Bağlayın və Yükləyin (Ev - Bağla və Yüklə). Bütün şəhərlər üçün sorğu ilə toplanmış bütün məlumatlar “ağıllı masa” formatında cari Excel cədvəlinə yüklənəcək:

Yaradılmış əlaqəni və montaj funksiyamızı heç bir şəkildə ayrıca saxlamağa ehtiyac yoxdur - onlar adi şəkildə cari fayl ilə birlikdə saxlanılır.

Gələcəkdə qovluqda (şəhərlər əlavə etmək və ya silmək) və ya fayllarda hər hansı bir dəyişikliklə (sətirlərin sayını dəyişdirmək) birbaşa masanın və ya sağ paneldəki sorğunun üzərinə sağ klikləmək kifayətdir. əmr Yeniləyin və Saxlayın (Təzələmək) – Power Query bir neçə saniyə ərzində bütün məlumatları yenidən “yenidən quracaq”.

PS

Dəyişiklik. 2017-ci ilin yanvar yeniləmələrindən sonra Power Query Excel iş kitablarını necə toplamaq lazım olduğunu öyrəndi, yəni artıq ayrıca funksiya yaratmağa ehtiyac yoxdur – bu avtomatik olaraq baş verir. Beləliklə, bu məqalənin ikinci mərhələsinə ehtiyac yoxdur və bütün proses nəzərəçarpacaq dərəcədə sadələşir:

1. Seçmək Sorğu yarat - Fayldan - Qovluqdan - Qovluq seçin - OK
2. Faylların siyahısı göründükdən sonra basın Dəyişdirmək
3. Sorğu redaktoru pəncərəsində, ikili sütunu ikiqat ox ilə genişləndirin və hər bir fayldan götürüləcək vərəq adını seçin.

Və hamısı budur! Mahnı!

• Çarpaz cədvəli pivot cədvəlləri qurmaq üçün uyğun olan düz birinə çevirin
• Power View-də cizgi qabarcıq diaqramının qurulması
• Müxtəlif Excel fayllarından vərəqləri bir yerə yığmaq üçün makro