Mundarija
Muammoni shakllantirish
Keling, Excel foydalanuvchilarining ko'pchiligi ertami-kechmi duch keladigan juda standart vaziyatlardan biri uchun chiroyli yechimni ko'rib chiqaylik: siz tez va avtomatik ravishda ko'p sonli fayllardan ma'lumotlarni bitta yakuniy jadvalga to'plashingiz kerak.
Aytaylik, bizda filial shaharlari ma'lumotlari bilan bir nechta fayllarni o'z ichiga olgan quyidagi papka mavjud:
Fayllar soni muhim emas va kelajakda o'zgarishi mumkin. Har bir faylda nomli varaq bor savdoma'lumotlar jadvali qaerda joylashgan:
Jadvallardagi qatorlar (tartiblar) soni, albatta, har xil, ammo ustunlar to'plami hamma joyda standartdir.
Vazifa: shahar fayllari yoki jadvallardagi qatorlarni qo'shish yoki o'chirishda keyingi avtomatik yangilash bilan barcha fayllardan ma'lumotlarni bitta kitobga to'plash. Yakuniy konsolidatsiyalangan jadvalga ko'ra, u holda har qanday hisobotlarni, pivot jadvallarni, ma'lumotlarni filtrlash va boshqalarni qurish mumkin bo'ladi. Asosiysi, to'plash imkoniyatiga ega bo'lishdir.
Biz qurollarni tanlaymiz
Yechim uchun bizga Excel 2016 ning so'nggi versiyasi (kerakli funksionallik sukut bo'yicha u allaqachon o'rnatilgan) yoki bepul plagin o'rnatilgan Excel 2010-2013 ning oldingi versiyalari kerak. Quvvat so'rovi Microsoft-dan (uni bu yerdan yuklab oling). Power Query tashqi dunyodan Excelga ma'lumotlarni yuklash, so'ngra ularni olib tashlash va qayta ishlash uchun o'ta moslashuvchan va juda kuchli vositadir. Power Query deyarli barcha mavjud ma'lumotlar manbalarini qo'llab-quvvatlaydi - matnli fayllardan SQL va hatto Facebookgacha 🙂
Agar sizda Excel 2013 yoki 2016 bo'lmasa, unda siz boshqa o'qiy olmaysiz (shunchaki hazil). Excelning eski versiyalarida bunday vazifani faqat Visual Basic-da makrosni dasturlash (bu yangi boshlanuvchilar uchun juda qiyin) yoki monoton qo'lda nusxa ko'chirish (bu uzoq vaqt talab etadi va xatolarni keltirib chiqaradi) orqali amalga oshirilishi mumkin.
1-qadam. Bitta faylni namuna sifatida import qiling
Birinchidan, misol sifatida bitta ish kitobidan ma'lumotlarni import qilaylik, shunda Excel "g'oyani qabul qiladi". Buning uchun yangi bo'sh ish kitobini yarating va ...
- agar sizda Excel 2016 bo'lsa, yorliqni oching ma'lumotlar undan keyin So'rov yaratish - Fayldan - Kitobdan (Ma'lumotlar - Yangi so'rov - fayldan - Exceldan)
- agar sizda Power Query plaginiga ega Excel 2010-2013 bo'lsa, yorliqni oching Quvvat so'rovi va ustiga tanlang Fayldan - Kitobdan (Fayldan — Exceldan)
Keyin ochilgan oynada hisobotlar bilan papkamizga o'ting va shahar fayllaridan birini tanlang (qaysi biri muhim emas, chunki ularning barchasi odatiy). Bir necha soniyadan so'ng, Navigator oynasi paydo bo'ladi, u erda chap tomonda bizga kerak bo'lgan varaqni tanlashingiz kerak (Sotish) va uning mazmuni o'ng tomonda ko'rsatiladi:
Agar siz ushbu oynaning pastki o'ng burchagidagi tugmani bossangiz Download (Yuklash), keyin jadval darhol varaqqa asl shaklida import qilinadi. Bitta fayl uchun bu yaxshi, lekin biz bunday fayllarni ko'p yuklashimiz kerak, shuning uchun biz biroz boshqacha yo'l tutamiz va tugmani bosing tuzatish (Edit). Shundan so'ng, Power Query so'rovlar muharriri kitobdagi ma'lumotlarimiz bilan alohida oynada ko'rsatilishi kerak:
Bu jadvalni bizga kerakli ko'rinishga "tugatish" imkonini beruvchi juda kuchli vositadir. Hatto uning barcha funktsiyalarining yuzaki tavsifi ham taxminan yuz sahifani oladi, ammo agar juda qisqa bo'lsa, ushbu oynadan foydalanib siz:
- keraksiz ma'lumotlarni, bo'sh satrlarni, xatolar bilan chiziqlarni filtrlang
- ma'lumotlarni bir yoki bir nechta ustunlar bo'yicha saralash
- takrorlashdan xalos bo'ling
- yopishqoq matnni ustunlarga bo'ling (chegaralar, belgilar soni va boshqalar).
- matnni tartibga soling (qo'shimcha bo'shliqlarni olib tashlang, harflarni to'g'rilang va h.k.)
- ma'lumotlar turlarini har qanday usulda aylantirish (matn kabi raqamlarni oddiy raqamlarga aylantirish va aksincha)
- jadvallarni almashtirish (aylantirish) va ikki o'lchovli o'zaro faoliyat jadvallarni tekislarga kengaytirish
- jadvalga qo'shimcha ustunlar qo'shing va Power Query-ga o'rnatilgan M tilidan foydalanib, ulardagi formulalar va funktsiyalardan foydalaning.
- ...
Misol uchun, jadvalimizga oyning matn nomi bilan ustun qo'shamiz, shunda keyinchalik pivot jadval hisobotlarini tuzish osonroq bo'ladi. Buni amalga oshirish uchun ustun sarlavhasini o'ng tugmasini bosing sanava buyruqni tanlang Ikki nusxadagi ustun (Dublikat ustun)ni bosing va keyin paydo bo'ladigan takroriy ustunning sarlavhasini o'ng tugmasini bosing va Buyruqlar-ni tanlang. O'zgartirish - Oy - Oy nomi:
Har bir satr uchun oyning matn nomlari bilan yangi ustun hosil bo'lishi kerak. Ustun sarlavhasini ikki marta bosish orqali siz uning nomini o'zgartirishingiz mumkin Nusxa ko'chirish sanasi yanada qulayroq oy, masalan.
Agar ba'zi ustunlarda dastur ma'lumotlar turini to'g'ri tanimasa, har bir ustunning chap tomonidagi format belgisini bosish orqali yordam berishingiz mumkin:
Siz oddiy filtr yordamida xato yoki bo'sh satrlarni, shuningdek keraksiz menejerlar yoki mijozlarni chiqarib tashlashingiz mumkin:
Bundan tashqari, barcha amalga oshirilgan o'zgarishlar o'ng panelda o'rnatiladi, bu erda ularni har doim orqaga qaytarish (o'zaro) yoki parametrlarini (tishli) o'zgartirish mumkin:
Yengil va oqlangan, shunday emasmi?
2-qadam. So'rovimizni funksiyaga aylantiramiz
Har bir import qilingan kitob uchun qilingan barcha ma'lumotlarni o'zgartirishni keyinchalik takrorlash uchun biz yaratilgan so'rovni funksiyaga aylantirishimiz kerak, bu esa o'z navbatida barcha fayllarimizga qo'llaniladi. Buni qilish aslida juda oddiy.
So'rovlar muharririda Ko'rish yorlig'iga o'ting va tugmani bosing Murakkab muharrir (Koʻrish — Kengaytirilgan muharrir). Oldingi barcha harakatlarimiz M tilida kod shaklida yoziladigan oyna ochilishi kerak. E'tibor bering, biz misol uchun import qilgan faylga yo'l kodda qattiq kodlangan:
Endi bir nechta tuzatishlar kiritamiz:
Ularning ma'nosi oddiy: birinchi qator (fayl yo'li)=> protseduramizni argumentli funksiyaga aylantiradi fayl yo'li, va pastda biz ushbu o'zgaruvchining qiymatiga sobit yo'lni o'zgartiramiz.
Hammasi. ni bosing tugatmoq va buni ko'rish kerak:
Ma'lumotlar g'oyib bo'lganidan qo'rqmang - aslida hammasi joyida, hammasi shunday bo'lishi kerak 🙂 Biz o'z shaxsiy funksiyamizni muvaffaqiyatli yaratdik, bu erda ma'lumotlarni import qilish va qayta ishlash algoritmi ma'lum bir faylga bog'lanmasdan eslab qolinadi. . Unga tushunarliroq nom berish qoladi (masalan getData) maydonning o'ng tomonidagi panelda ism va siz o'rib olishingiz mumkin Bosh sahifa — Yoping va yuklab oling (Uy — Yopish va yuklash). E'tibor bering, biz misol uchun import qilgan faylga yo'l kodda qattiq kodlangan. Siz asosiy Microsoft Excel oynasiga qaytasiz, lekin o'ng tomonda bizning funktsiyamiz bilan yaratilgan ulanish paneli paydo bo'lishi kerak:
Qadam 3. Barcha fayllarni yig'ish
Barcha qiyin qismi ortda, yoqimli va oson qismi qoladi. Yorliqga o'ting Ma'lumotlar - So'rov yaratish - Fayldan - Jilddan (Ma'lumotlar - Yangi so'rov - Fayldan - Jilddan) yoki, agar sizda Excel 2010-2013 bo'lsa, yorliq kabi Quvvat so'rovi. Ko'rsatilgan oynada barcha manba shahar fayllarimiz joylashgan papkani belgilang va bosing OK. Keyingi bosqichda ushbu papkada (va uning pastki papkalarida) topilgan barcha Excel fayllari va ularning har biri uchun ma'lumotlar ro'yxati keltirilgan oynani ochish kerak:
bosing o'zgarish (Edit) va yana tanish so'rovlar muharriri oynasiga kiramiz.
Endi biz yaratilgan funktsiyamiz bilan jadvalimizga yana bir ustun qo'shishimiz kerak, bu esa har bir fayldan ma'lumotlarni "tortib oladi". Buni amalga oshirish uchun yorliqga o'ting Ustun qo'shish - Maxsus ustun (Ustun qo'shish - maxsus ustun qo'shish) va paydo bo'lgan oynada bizning funktsiyamizni kiriting getData, buning uchun argument sifatida har bir faylga to'liq yo'lni ko'rsating:
Tugmasini bosgandan so'ng OK yaratilgan ustun o'ngdagi jadvalimizga qo'shilishi kerak.
Keling, barcha keraksiz ustunlarni o'chirib tashlaymiz (Excelda bo'lgani kabi, sichqonchaning o'ng tugmasi yordamida - O'chiring), faqat qo'shilgan ustunni va fayl nomi bilan ustunni qoldiring, chunki bu nom (aniqrog'i, shahar) har bir satr uchun umumiy ma'lumotlarga ega bo'lish foydali bo'ladi.
Va endi "voy on" - bizning funktsiyamiz bilan qo'shilgan ustunning yuqori o'ng burchagidagi o'z strelkalari bo'lgan belgini bosing:
… belgini olib tashlang Prefiks sifatida asl ustun nomidan foydalaning (Prefiks sifatida asl ustun nomidan foydalaning)bosing OK. Va bizning funktsiyamiz har bir fayldan ma'lumotlarni yuklaydi va qayta ishlaydi, yozilgan algoritmga amal qiladi va hamma narsani umumiy jadvalda to'playdi:
To'liq go'zallik uchun siz .xlsx kengaytmalarini fayl nomlari bilan birinchi ustundan olib tashlashingiz mumkin - standart "hech narsa" bilan almashtirish orqali (ustun sarlavhasini o'ng tugmasini bosing - O'zgartirish) va ushbu ustun nomini o'zgartiring tuyg'ular. Shuningdek, ustundagi ma'lumotlar formatini sana bilan to'g'rilang.
Hammasi! ni bosing Bosh sahifa - Yopish va yuklash (Uy — Yopish va yuklash). Barcha shaharlar bo'yicha so'rov orqali to'plangan barcha ma'lumotlar joriy Excel varag'iga "aqlli jadval" formatida yuklanadi:
Yaratilgan ulanish va bizning yig'ish funksiyamiz hech qanday tarzda alohida saqlanishi shart emas - ular odatiy tarzda joriy fayl bilan birga saqlanadi.
Kelajakda papkada (shaharlarni qo'shish yoki o'chirish) yoki fayllardagi (satrlar sonini o'zgartirish) har qanday o'zgarishlar bilan to'g'ridan-to'g'ri jadval yoki o'ng paneldagi so'rovni sichqonchaning o'ng tugmasi bilan bosish kifoya qiladi. buyruq Yangilash va saqlash (yangilash) – Power Query bir necha soniya ichida barcha ma’lumotlarni qayta “qayta tiklaydi”.
PS
Tuzatish. 2017 yil yanvar oyidagi yangilanishlardan so'ng Power Query Excel ish kitoblarini o'z-o'zidan qanday yig'ishni o'rgandi, ya'ni endi alohida funksiya qilishning hojati yo'q - bu avtomatik ravishda sodir bo'ladi. Shunday qilib, ushbu maqoladagi ikkinchi qadam endi kerak emas va butun jarayon sezilarli darajada soddalashadi:
- tanlang So'rov yaratish - Fayldan - Jilddan - Jildni tanlang - OK
- Fayllar ro'yxati paydo bo'lgandan so'ng, tugmasini bosing o'zgarish
- So'rovlar muharriri oynasida Ikkilik ustunni ikki o'q bilan kengaytiring va har bir fayldan olinadigan varaq nomini tanlang.
Va bu hammasi! Qo'shiq!
- O'zaro faoliyat jadvalni aylantiruvchi jadvallarni yaratish uchun mos tekis qilib qayta loyihalash
- Power View-da jonlantirilgan pufakchali diagramma yaratish
- Turli xil Excel fayllaridan varaqlarni bittasiga yig'ish uchun so'l