Çoxlu məlumat diapazonunda pivot cədvəli

Problemin formalaşdırılması

Pivot cədvəlləri Excel-in ən heyrətamiz alətlərindən biridir. Ancaq indiyə qədər, təəssüf ki, Excel versiyalarının heç biri, məsələn, müxtəlif vərəqlərdə və ya müxtəlif cədvəllərdə yerləşən bir neçə ilkin məlumat diapazonu üçün xülasə yaratmaq kimi sadə və zəruri bir işi tez bir zamanda edə bilməz:

Başlamazdan əvvəl bir neçə məqama aydınlıq gətirək. A priori hesab edirəm ki, məlumatlarımızda aşağıdakı şərtlər yerinə yetirilir:

• Cədvəllərdə istənilən verilənlərlə istənilən sayda sıra ola bilər, lakin onların başlığı eyni olmalıdır.
• Mənbə cədvəlləri olan vərəqlərdə əlavə məlumat olmamalıdır. Bir vərəq - bir masa. Nəzarət etmək üçün sizə klaviatura qısa yolundan istifadə etməyi məsləhət görürəm Ctrl+son, bu sizi iş vərəqindəki son istifadə olunan xanaya köçürür. İdeal olaraq, bu məlumat cədvəlindəki son xana olmalıdır. Əgər üzərinə kliklədiyiniz zaman Ctrl+son Cədvəlin sağında və ya altındakı istənilən boş xana vurğulanır – cədvəldən sonra sağdakı bu boş sütunları və ya cədvəlin altındakı sətirləri silin və faylı yadda saxlayın.

Metod 1: Power Query istifadə edərək pivot üçün cədvəllər qurun

Excel üçün 2010-cu il versiyasından başlayaraq hər hansı bir məlumatı toplaya və çevirə və sonra onları pivot cədvəli yaratmaq üçün mənbə kimi verə bilən pulsuz Power Query əlavəsi mövcuddur. Problemimizi bu əlavənin köməyi ilə həll etmək heç də çətin deyil.

Əvvəlcə Excel-də yeni boş bir fayl yaradaq – orada montaj baş tutacaq və sonra orada pivot cədvəli yaradılacaq.

Sonra tabda Tarix (Excel 2016 və ya daha yeni versiyanız varsa) və ya tabda Güc sorğusu (Excel 2010-2013 varsa) əmri seçin Sorğu yaradın - Fayldan - Excel (Məlumat əldə etmək — Fayldan — Excel) və toplanacaq cədvəllərlə mənbə faylını təyin edin:

Görünən pəncərədə hər hansı vərəqi seçin (hansı olmasının fərqi yoxdur) və aşağıdakı düyməni basın Dəyişdirmək (Edit):

Power Query Query Redaktoru pəncərəsi Excel-in üstündə açılmalıdır. Paneldəki pəncərənin sağ tərəfində Sorğu Parametrləri birincidən başqa bütün avtomatik yaradılmış addımları silin – mənbə (Mənbə):

İndi bütün vərəqlərin ümumi siyahısını görürük. Əgər məlumat vərəqlərinə əlavə olaraq faylda bəzi digər yan vərəqlər varsa, bu addımda vəzifəmiz cədvəl başlığındaki filtrdən istifadə edən bütün digərləri istisna olmaqla, yalnız məlumatın yüklənməsi lazım olan vərəqləri seçməkdir:

Sütun istisna olmaqla, bütün sütunları silin Tarixsütun başlığını sağ klikləyərək seçin Digər sütunları silin (Silin digər sütunlar):

Daha sonra sütunun yuxarısındakı qoşa oxa klikləməklə toplanmış cədvəllərin məzmununu genişləndirə bilərsiniz (onay qutusu Orijinal sütun adından prefiks kimi istifadə edin onu söndürə bilərsiniz):

Hər şeyi düzgün etmisinizsə, bu anda bir-birinin altında toplanmış bütün cədvəllərin məzmununu görməlisiniz:

Düymə ilə birinci sıranı masa başlığına qaldırmaq qalır Başlıq kimi ilk sətirdən istifadə edin (Birinci sıranı başlıq kimi istifadə edin) nişanı Əsas səhifə (Ev) və bir filtrdən istifadə edərək dublikat cədvəl başlıqlarını datadan silin:

Komanda ilə edilən hər şeyi yadda saxlayın Bağlayın və yükləyin – Bağlayın və yükləyin… (Bağla və Yüklə — Bağla və Yüklə...) nişanı Əsas səhifə (Ev), və açılan pəncərədə seçimi seçin Yalnız əlaqə (Yalnız bağlantı):

Hər şey. Yalnız bir xülasə qurmaq qalır. Bunu etmək üçün nişana keçin Daxil et - Pivot Cədvəl (Daxil et - Pivot Cədvəl), seçimi seçin Xarici məlumat mənbəyindən istifadə edin (Xarici məlumat mənbəyindən istifadə edin)və sonra düyməni basaraq Bağlantı seçin, xahişimiz. Pivotun sonrakı yaradılması və konfiqurasiyası ehtiyac duyduğumuz sahələri sətirlərə, sütunlara və dəyərlər sahəsinə sürükləməklə tamamilə standart bir şəkildə baş verir:

Gələcəkdə mənbə məlumatları dəyişirsə və ya daha bir neçə mağaza vərəqi əlavə edilərsə, o zaman əmrdən istifadə edərək sorğunu və xülasəmizi yeniləmək kifayətdir. Hamısını yeniləyin nişanı Tarix (Məlumat - Hamısını Yeniləyin).

Metod 2. Makroda UNION SQL əmri ilə cədvəlləri birləşdiririk

Problemimizin başqa bir həlli əmrdən istifadə edərək pivot cədvəli üçün məlumat dəsti (keş) yaradan bu makro ilə təmsil olunur. Birlik SQL sorğu dili. Bu komanda massivdə göstərilən bütün cədvəlləri birləşdirir Vərəq Adları kitabın vərəqləri vahid məlumat cədvəlinə. Yəni fiziki olaraq müxtəlif vərəqlərdən birinə diapazonları kopyalayıb yapışdırmaq əvəzinə, biz kompüterin RAM yaddaşında eyni şeyi edirik. Sonra makro verilmiş adla yeni vərəq əlavə edir (dəyişən Nəticə VərəqininAdı) və toplanmış keş əsasında onun üzərində tam hüquqlu (!) xülasə yaradır.

Makrodan istifadə etmək üçün tabdakı Visual Basic düyməsini istifadə edin geliştirici (İnkişaf etdirici) və ya klaviatura qısa yolu Dayandırmaq+F11. Sonra menyu vasitəsilə yeni boş modul daxil edirik Daxil et - Modul və aşağıdakı kodu oraya kopyalayın:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() String As Dim objPivotCache As PivotCache kimi Dim objRS Obyekt kimi Dim Nəticə VərəqAdı Variant kimi Dim Dim VərəqlərAdları Variant kimi 'nəticədə pivotun göstəriləcəyi vərəq adı"Nəticə cədvəli" mənbə cədvəlləri olan adlar SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'biz ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ilə SheetsNames-dən vərəqlərdən cədvəllər üçün keş yaradırıq. ) i üçün = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Sonrakı i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Sonu "Nəticədə pivot cədvəlini göstərmək üçün vərəqi yenidən yaradın Xəta On Sonrakı Proqramı davam etdirin. DisplayAlerts = False Worksheets(ResultSheetName). Delete Set wsPivot = Worksheets. Add wsPivo t. Ad = ResultSheetName 'bu vərəqdə yaradılan keş xülasəsini göstərin objPivotCache Set = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Heç bir wsPivot ilə heç bir şey objPivotCachestination T.ACotable. objPivotCache = Nothing Range("A3").End with End Sub seçin    

Hazır makro daha sonra klaviatura qısayolu ilə işlədilə bilər Dayandırmaq+F8 və ya tabdakı Makrolar düyməsini basın geliştirici (Tərtibatçı — Makroslar).

Bu yanaşmanın mənfi cəhətləri:

• Keşin mənbə cədvəlləri ilə əlaqəsi olmadığı üçün məlumatlar yenilənmir. Mənbə məlumatını dəyişdirsəniz, makronu yenidən işə salmalı və xülasəni yenidən yaratmalısınız.
• Vərəqlərin sayını dəyişdirərkən makro kodunu redaktə etmək lazımdır (massiv Vərəq Adları).
  

Ancaq nəticədə fərqli vərəqlərdən bir neçə diapazonda qurulmuş əsl tam hüquqlu pivot masası alırıq:

Voilà!

Texniki qeyd: makronu işləyərkən “Provayder qeydiyyatdan keçməyib” kimi xəta ilə qarşılaşırsınızsa, çox güman ki, sizdə Excel-in 64-bit versiyası var və ya Office-in natamam versiyası quraşdırılıb (giriş yoxdur). Vəziyyəti düzəltmək üçün makro kodundakı fraqmenti dəyişdirin:

	 Provayder=Microsoft.Jet.OLEDB.4.0;  

üçün:

	Provayder=Microsoft.ACE.OLEDB.12.0;  

Və Microsoft veb saytından Access-dən pulsuz məlumat emalı mühərrikini endirin və quraşdırın – Microsoft Access Database Engine 2010 Yenidən Dağıtılabilir

Metod 3: Excel-in köhnə versiyalarından Pivot Cədvəl Sihirbazını birləşdirin

Bu üsul bir az köhnəlmişdir, lakin hələ də qeyd etməyə dəyər. Formal olaraq desək, 2003-cü ilə qədər olan bütün versiyalarda Pivot Cədvəl Sihirbazında “bir neçə konsolidasiya diapazonu üçün pivot yaratmaq” seçimi var idi. Bununla belə, bu şəkildə qurulmuş bir hesabat, təəssüf ki, əsl tam hüquqlu xülasənin yalnız acınacaqlı bir görünüşü olacaq və adi pivot cədvəllərinin bir çox "çiplərini" dəstəkləmir:

Belə bir pivotda sahə siyahısında sütun başlıqları yoxdur, çevik struktur parametrləri yoxdur, istifadə olunan funksiyalar dəsti məhduddur və ümumiyyətlə, bütün bunlar pivot cədvəlinə çox bənzəmir. Bəlkə də buna görə 2007-ci ildən başlayaraq Microsoft pivot cədvəl hesabatları yaratarkən bu funksiyanı standart dialoqdan çıxardı. İndi bu funksiya yalnız fərdi düymə vasitəsilə mövcuddur Pivot Cədvəl Sihirbazı(Pivot Cədvəl Sihirbazı), istəsəniz, vasitəsilə Tez Giriş Alətlər Panelinə əlavə edilə bilər Fayl - Seçimlər - Tez Giriş Alətlər Panelini Fərdiləşdirin - Bütün Əmrlər (Fayl — Seçimlər — Tez Giriş Alətlər Panelini Fərdiləşdirin — Bütün Əmrlər):

Əlavə edilmiş düyməni tıkladıqdan sonra sehrbazın ilk addımında müvafiq seçimi seçməlisiniz:

Sonra növbəti pəncərədə hər bir aralığı növbə ilə seçin və ümumi siyahıya əlavə edin:

Ancaq yenə də, bu tam hüquqlu bir xülasə deyil, ona görə də ondan çox şey gözləməyin. Bu seçimi yalnız çox sadə hallarda tövsiyə edə bilərəm.

• Pivot Cədvəllərlə Hesabatların yaradılması
• Pivot Cədvəllərdə hesablamaları qurun
• Makroslar nədir, onlardan necə istifadə etmək, VBA kodunu hara köçürmək və s.
• Çox vərəqdən birinə məlumat toplama (PLEX əlavəsi)