Bir nechta kitoblardan turli sarlavhalar bilan jadvallar tuzing

Muammoni shakllantirish

Bizda bitta papkada bir nechta fayllar mavjud (misolimizda - 4 dona, umumiy holatda - xohlaganingizcha). Hisobotlar:

Ichkarida bu fayllar quyidagicha ko'rinadi:

Bu erda:

• Bizga kerak bo'lgan ma'lumotlar varag'i har doim chaqiriladi Rasm, lekin ish kitobining istalgan joyida bo'lishi mumkin.
  
• Choyshabdan tashqari Rasm Har bir kitobda boshqa varaqlar bo'lishi mumkin.
• Ma'lumotlarga ega jadvallar turli qatorlar soniga ega va ish varag'idagi boshqa qatordan boshlanishi mumkin.
• Turli jadvallardagi bir xil ustunlarning nomlari farq qilishi mumkin (masalan, Miqdor = Miqdor = Miqdor).
• Jadvallardagi ustunlar boshqa tartibda joylashtirilishi mumkin.

Vazifa: varaqdagi barcha fayllardan savdo ma'lumotlarini to'plang Rasm keyinchalik xulosa yoki boshqa tahlillarni yaratish uchun bitta umumiy jadvalga kiriting.

Qadam 1. Ustun nomlari katalogini tayyorlash

Birinchi narsa, ustun nomlari va ularni to'g'ri talqin qilishning barcha mumkin bo'lgan variantlari bilan ma'lumotnoma tayyorlashdir:

Yorliqdagi Format as Jadval tugmasi yordamida biz ushbu ro'yxatni dinamik "aqlli" jadvalga aylantiramiz Bosh sahifa (Uy — Jadval sifatida formatlash) yoki klaviatura yorlig'i Ctrl+T va buyruq bilan Power Query-ga yuklang Ma'lumotlar - Jadvaldan/diapazondan (Ma'lumotlar - Jadvaldan/diapazondan). Excelning so'nggi versiyalarida uning nomi o'zgartirildi Barglari bilan (Varaqdan).

Power Query so'rovlar muharriri oynasida biz an'anaviy tarzda qadamni o'chirib tashlaymiz O'zgartirilgan tur va tugmani bosish orqali uning o'rniga yangi qadam qo'shing f_xformulalar panelida (agar u ko'rinmasa, uni yorliqda yoqishingiz mumkin Review) va o'rnatilgan Power Query M tilida formulani kiriting:

=Jadval.Qatorlar(Manba)

Ushbu buyruq oldingi bosqichda yuklangan buyruqni o'zgartiradi manba mos yozuvlar jadvalini ichki ro'yxatlardan (Ro'yxat) iborat ro'yxatga kiriting, ularning har biri o'z navbatida qiymatlar juftligidir. Bu bo'ldi bir qatordan:

Bizga bu turdagi ma'lumotlar birozdan keyin, barcha yuklangan jadvallar sarlavhalarini ommaviy qayta nomlashda kerak bo'ladi.

Konvertatsiyani tugatgandan so'ng, buyruqlarni tanlang Bosh sahifa — Yopish va yuklash — Yopish va yuklash… va import turi Faqat ulanishni yarating (Uy — Yopish va yuklash — Yopish va yuklash… — Faqat ulanish yaratish) va Excelga qayting.

2-qadam. Biz barcha fayllardan hamma narsani avvalgidek yuklaymiz

Keling, barcha fayllarimiz tarkibini papkadan yuklaylik - hozircha, xuddi shunday. Jamoalarni tanlash Ma'lumotlar - Ma'lumotlarni olish - Fayldan - Jilddan (Ma'lumotlar - Ma'lumot olish - Fayldan - Jilddan) va keyin bizning manba kitoblarimiz joylashgan papka.

Ko'rib chiqish oynasida ni bosing Aylantirish (O'zgartirish) or o'zgarish (Edit):

Va keyin barcha yuklab olingan fayllarning mazmunini kengaytiring (ikkilik) ustun sarlavhasida ikkita strelkali tugma Tarkib:

Birinchi fayl misolida Power Query (Vostok.xlsx) bizdan har bir ish kitobidan olmoqchi bo'lgan varaq nomini so'raydi - tanlang Rasm va OK tugmasini bosing:

Shundan so'ng (aslida), foydalanuvchi uchun tushunarsiz bo'lgan bir nechta voqealar sodir bo'ladi, ularning oqibatlari chap panelda aniq ko'rinadi:

1. Power Query papkadan birinchi faylni oladi (bizda bo'ladi Vostok.xlsx — ko'rish Fayl misol) misol sifatida va so'rov yaratish orqali uning mazmunini import qiladi Namuna faylini aylantirish. Ushbu so'rovda bir necha oddiy qadamlar bo'ladi manba (faylga kirish) navigatsiya (varaq tanlash) va, ehtimol, sarlavhalarni ko'tarish. Ushbu so'rov faqat bitta aniq fayldan ma'lumotlarni yuklashi mumkin Vostok.xlsx.
   
2. Ushbu so'rov asosida u bilan bog'liq funksiya yaratiladi Faylni aylantirish (xarakterli belgi bilan ko'rsatilgan f_x), bu erda manba fayli endi doimiy emas, balki o'zgaruvchan qiymat - parametr bo'ladi. Shunday qilib, bu funktsiya biz argument sifatida kiritilgan har qanday kitobdan ma'lumotlarni olishi mumkin.
   
3. Funktsiya ustundan har bir faylga (Ikkilik) navbat bilan qo'llaniladi Tarkib - qadam bu uchun javobgardir Maxsus funktsiyani chaqirish fayllar ro'yxatiga ustun qo'shadigan so'rovimizda Faylni aylantirish har bir ish kitobidan import natijalari bilan:

   

4. Qo'shimcha ustunlar olib tashlanadi.
5. Ichki jadvallarning mazmuni kengaytirildi (qadam Kengaytirilgan jadval ustuni) - va biz barcha kitoblardan ma'lumotlarni yig'ishning yakuniy natijalarini ko'ramiz:

   
   

3-qadam. Zımpara

Oldingi skrinshotda to'g'ridan-to'g'ri yig'ish "xuddi" sifatsiz bo'lib chiqqani aniq ko'rsatilgan:

• Ustunlar teskari.
• Ko'p qo'shimcha qatorlar (bo'sh va nafaqat).
• Jadval sarlavhalari sarlavha sifatida qabul qilinmaydi va ma'lumotlar bilan aralashtiriladi.
  

Siz ushbu muammolarni juda oson hal qilishingiz mumkin - shunchaki Namuna faylini aylantirish so'rovini o'zgartiring. Biz unga kiritgan barcha oʻzgarishlar avtomatik ravishda “Faylni aylantirish” funksiyasiga tushadi, yaʼni ular keyinchalik har bir fayldan maʼlumotlarni import qilishda foydalaniladi.

So'rovni ochish orqali Namuna faylini aylantirish, keraksiz qatorlarni filtrlash uchun qadamlar qo'shing (masalan, ustun bo'yicha Column2) va tugma bilan sarlavhalarni ko'taring Sarlavha sifatida birinchi qatordan foydalaning (Birinchi qatordan sarlavha sifatida foydalaning). Jadval ancha yaxshi ko'rinadi.

Turli fayllardagi ustunlar keyinchalik avtomatik ravishda bir-birining ostiga mos kelishi uchun ular bir xil nomlanishi kerak. Siz M-kodning bir qatori bilan oldindan yaratilgan katalog bo'yicha bunday ommaviy qayta nomlashni amalga oshirishingiz mumkin. Keling, tugmani yana bosamiz f_x formulalar qatorida o'zgartirish uchun funktsiyani qo'shing:

= Jadval.Ustunlarni qayta nomlash(#”Koʻtarilgan sarlavhalar”, Sarlavhalar, MissingField.Ignore)

Bu funksiya oldingi bosqichdagi jadvalni oladi Yuqori sarlavhalar va undagi barcha ustunlarni ichki qidiruv roʻyxatiga muvofiq qayta nomlaydi Yangiliklar. Uchinchi dalil MissingField.Ignore katalogdagi, lekin jadvalda bo'lmagan sarlavhalarda xatolik yuzaga kelmasligi uchun kerak.

Aslida, hammasi shu.

So'rovga qaytish Hisobotlar Biz butunlay boshqacha rasmni ko'ramiz - avvalgisidan ancha chiroyli:

• Power Query, Power Pivot, Power BI nima va ular Excel foydalanuvchisiga nima uchun kerak
  
• Berilgan jilddagi barcha fayllardan ma'lumotlarni yig'ish
• Kitobning barcha varaqlaridan ma'lumotlarni bitta jadvalga to'plash