Bir nechta ma'lumotlar diapazonida pivot jadval

Muammoni shakllantirish

Pivot jadvallari Excelning eng ajoyib vositalaridan biridir. Ammo hozirgacha, afsuski, Excel versiyalarining hech biri, masalan, turli varaqlarda yoki turli jadvallarda joylashgan bir nechta dastlabki ma'lumotlar diapazonlari uchun xulosa yaratish kabi oddiy va kerakli ishni tezda bajara olmaydi:

Boshlashdan oldin, keling, bir nechta fikrlarga aniqlik kiritaylik. Apriori, bizning ma'lumotlarimizda quyidagi shartlar bajarilganiga ishonaman:

• Jadvallar har qanday ma'lumotlarga ega bo'lgan qatorlar soniga ega bo'lishi mumkin, lekin ular bir xil sarlavhaga ega bo'lishi kerak.
• Manba jadvallari bo'lgan varaqlarda qo'shimcha ma'lumotlar bo'lmasligi kerak. Bitta varaq - bitta stol. Boshqarish uchun men sizga klaviatura yorlig'idan foydalanishni maslahat beraman Ctrl+Oxiri, bu sizni ishchi varaqdagi oxirgi foydalanilgan katakka o'tkazadi. Ideal holda, bu ma'lumotlar jadvalidagi oxirgi katak bo'lishi kerak. Agar bosganingizda Ctrl+Oxiri Jadvalning o'ng tomonidagi yoki pastidagi har qanday bo'sh katak ta'kidlangan - jadvaldan keyin o'ngdagi ushbu bo'sh ustunlarni yoki jadval ostidagi qatorlarni o'chiring va faylni saqlang.

1-usul: Power Query yordamida pivot uchun jadvallar yarating

Excel uchun 2010 yil versiyasidan boshlab har qanday ma'lumotlarni to'plash va o'zgartirish, so'ngra ularni pivot jadval yaratish uchun manba sifatida berishi mumkin bo'lgan bepul Power Query plaginlari mavjud. Ushbu plagin yordamida muammomizni hal qilish unchalik qiyin emas.

Birinchidan, Excelda yangi bo'sh fayl yarataylik - unda yig'ilish bo'lib o'tadi va keyin unda pivot jadvali yaratiladi.

Keyin yorliqda ma'lumotlar (agar sizda Excel 2016 yoki undan keyingi versiyasi bo'lsa) yoki yorliqda Quvvat so'rovi (agar sizda Excel 2010-2013 bo'lsa) buyruqni tanlang So'rov yaratish - Fayldan - Excel (Ma'lumotlarni olish - Fayldan - Excel) va yig'iladigan jadvallar bilan manba faylni belgilang:

Ko'rsatilgan oynada istalgan varaqni tanlang (qaysi biri muhim emas) va quyidagi tugmani bosing o'zgarish (Edit):

Excelning tepasida Power Query Query Editor oynasi ochilishi kerak. Paneldagi oynaning o'ng tomonida So'rov parametrlari Birinchisidan tashqari avtomatik ravishda yaratilgan barcha qadamlarni o'chirish - manba (Manba):

Endi biz barcha varaqlarning umumiy ro'yxatini ko'ramiz. Agar faylda ma'lumotlar varaqlariga qo'shimcha ravishda boshqa yon varaqlar mavjud bo'lsa, unda bu bosqichda bizning vazifamiz jadval sarlavhasidagi filtrdan foydalangan holda, faqat ma'lumot yuklanishi kerak bo'lgan varaqlarni tanlashdir:

Ustundan tashqari barcha ustunlarni o'chiring ma'lumotlarustun sarlavhasini o'ng tugmasini bosing va tanlang Boshqa ustunlarni o'chiring (O'chirish boshqa ustunlar):

Keyin ustunning yuqori qismidagi ikkita o'qni bosish orqali to'plangan jadvallarning mazmunini kengaytirishingiz mumkin (tasdiqlash qutisi). Prefiks sifatida asl ustun nomidan foydalaning uni o'chirib qo'yishingiz mumkin):

Agar siz hamma narsani to'g'ri bajargan bo'lsangiz, unda siz birin-ketin to'plangan barcha jadvallarning mazmunini ko'rishingiz kerak:

Birinchi qatorni tugma bilan jadval sarlavhasiga ko'tarish qoladi Sarlavha sifatida birinchi qatordan foydalaning (Birinchi qatordan sarlavha sifatida foydalaning) tab Bosh sahifa (Uy) va filtr yordamida ma'lumotlardan takroriy jadval sarlavhalarini olib tashlang:

Buyruq bilan bajarilgan hamma narsani saqlang Yopish va yuklash – Yopish va yuklash… (Yopish va yuklash — yopish va yuklash…) tab Bosh sahifa (Uy), va ochilgan oynada variantni tanlang Faqat ulanish (Faqat ulanish):

Hamma narsa. Xulosa yaratish uchungina qoladi. Buning uchun yorliqga o'ting Qo'shish - Pivot jadval (Qo'shish - umumiy jadval), variantni tanlang Tashqi ma'lumotlar manbasidan foydalaning (Tashqi ma'lumotlar manbasidan foydalaning)va keyin tugmani bosish orqali Ulanishni tanlang, bizning iltimosimiz. Pivotni keyingi yaratish va sozlash mutlaqo standart tarzda, bizga kerak bo'lgan maydonlarni satrlar, ustunlar va qiymatlar maydoniga sudrab borish orqali amalga oshiriladi:

Agar kelajakda manba ma'lumotlari o'zgarsa yoki yana bir nechta do'kon varaqlari qo'shilsa, buyruq yordamida so'rov va xulosani yangilash kifoya qiladi. Hammasini yangilang tab ma'lumotlar (Ma'lumotlar - Hammasini yangilash).

Usul 2. UNION SQL buyrug'i bilan jadvallarni makroda birlashtiramiz

Bizning muammomizning yana bir yechimi bu makros bilan ifodalanadi, u buyruq yordamida pivot jadval uchun ma'lumotlar to'plamini (keshini) yaratadi. BIRLIK SQL so'rovlar tili. Bu buyruq massivda ko'rsatilgan barcha jadvallarni birlashtiradi Varaq nomlari kitob varaqlarini yagona ma'lumotlar jadvaliga aylantiradi. Ya'ni, diapazonlarni turli varaqlardan bittasiga jismoniy nusxalash va joylashtirish o'rniga, biz kompyuterning operativ xotirasida xuddi shunday qilamiz. Keyin so'l berilgan nom bilan yangi varaq qo'shadi (o'zgaruvchi Natija varaqasi nomi) va to'plangan kesh asosida unga to'liq (!) Xulosa yaratadi.

Makrosdan foydalanish uchun yorliqdagi Visual Basic tugmasidan foydalaning dasturchi (ishlab chiquvchi) yoki klaviatura yorlig'i Alt+F11. Keyin menyu orqali yangi bo'sh modulni joylashtiramiz Qo'shish - modul va u erda quyidagi kodni nusxalash:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() String As Dim objPivotCache As PivotCache sifatida Dim objRS Object Dim Natija SheetName sifatida String Dim SheetsNames Variant sifatida 'varaq nomi, bu erda hosil bo'lgan pivot tasviri Natijalar varaqlari ko'rsatiladi. manba jadvallari bilan nomlar SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'biz SheetsNames sahifalaridan ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) bilan jadvallar uchun kesh hosil qilamiz. ) i uchun = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Keyingi 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 ) End "Natijadagi pivot jadvalini ko'rsatish uchun varaqni qayta yarating. Xatoda Keyingi dasturni davom ettiring. DisplayAlerts = False Worksheets(ResultSheetName). Delete Set wsPivot = Worksheets. Add wsPivo t. Name = ResultSheetName 'hosil qilingan kesh xulosasini ushbu varaqda ko'rsatish objPivotCache Set = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Hech narsa wsPivot bilan objPivotCachestination T.AR3) SetablePivotgeable. objPivotCache = Nothing Range("A3"). End with End Sub-ni tanlang.    

Tayyor makros keyin klaviatura yorlig'i bilan ishga tushirilishi mumkin Alt+F8 yoki yorliqdagi Makros tugmasi dasturchi (Dasturchi — Makroslar).

Ushbu yondashuvning kamchiliklari:

• Ma'lumotlar yangilanmagan, chunki kesh manba jadvallari bilan aloqasi yo'q. Agar siz manba ma'lumotlarini o'zgartirsangiz, makrosni qayta ishga tushirishingiz va yana xulosa yaratishingiz kerak.
• Varaqlar sonini o'zgartirganda, so'l kodni tahrirlash kerak (massiv Varaq nomlari).
  

Ammo oxir-oqibat biz turli xil varaqlardan bir nechta diapazonlarga qurilgan haqiqiy to'liq aylanish jadvalini olamiz:

Ana!

Texnik eslatma: agar siz makrosni ishga tushirishda "Provayder ro'yxatdan o'tmagan" kabi xatoga duch kelsangiz, sizda Excelning 64 bitli versiyasi yoki Officening to'liq bo'lmagan versiyasi o'rnatilgan (kirish yo'q). Vaziyatni tuzatish uchun so'l koddagi fragmentni almashtiring:

	 Provayder=Microsoft.Jet.OLEDB.4.0;  

uchun:

	Provayder=Microsoft.ACE.OLEDB.12.0;  

Va Microsoft veb-saytidan Access-dan bepul ma'lumotlarni qayta ishlash mexanizmini yuklab oling va o'rnating - Microsoft Access Database Engine 2010 Redistributable

3-usul: Excelning eski versiyalaridan umumiy jadval ustasini birlashtirish

Bu usul biroz eskirgan, ammo baribir eslatib o'tish kerak. Rasmiy ravishda aytganda, 2003 yilgacha va shu jumladan, barcha versiyalarda Pivot Table ustasida "bir nechta konsolidatsiya diapazonlari uchun pivot yaratish" opsiyasi mavjud edi. Biroq, shu tarzda tuzilgan hisobot, afsuski, haqiqiy to'liq xulosaning ayanchli ko'rinishi bo'ladi va an'anaviy pivot jadvallarning ko'pgina "chiplari" ni qo'llab-quvvatlamaydi:

Bunday pivotda maydonlar ro'yxatida ustun sarlavhalari mavjud emas, moslashuvchan tuzilma sozlamalari mavjud emas, ishlatiladigan funktsiyalar to'plami cheklangan va umuman olganda, bularning barchasi pivot jadvaliga unchalik o'xshamaydi. Ehtimol, shuning uchun 2007 yildan boshlab Microsoft pivot jadval hisobotlarini yaratishda ushbu funktsiyani standart dialog oynasidan olib tashladi. Endi bu xususiyat faqat maxsus tugma orqali mavjud Pivot jadval ustasi(Pivot jadval ustasi), agar xohlasangiz, orqali Tez kirish asboblar paneliga qo'shilishi mumkin Fayl - Variantlar - Tez kirish asboblar panelini sozlash - Barcha buyruqlar (Fayl - Variantlar - Tez kirish asboblar panelini sozlash - Barcha buyruqlar):

Qo'shilgan tugmani bosgandan so'ng, sehrgarning birinchi bosqichida tegishli variantni tanlashingiz kerak:

Va keyingi oynada navbatma-navbat har bir diapazonni tanlang va uni umumiy ro'yxatga qo'shing:

Ammo, yana bir bor, bu to'liq xulosa emas, shuning uchun undan ko'p narsa kutmang. Men bu variantni faqat juda oddiy holatlarda tavsiya qilishim mumkin.

• Pivot jadvallar yordamida hisobotlarni yaratish
• Pivot jadvallarida hisob-kitoblarni o'rnating
• Makroslar nima, ulardan qanday foydalanish, VBA kodini qayerdan nusxalash va h.k.
• Bir nechta varaqdan bittaga ma'lumotlarni yig'ish (PLEX qo'shimchasi)