Mundarija
Muammoni shakllantirish
Kirish ma'lumotlari sifatida bizda Excel fayli mavjud, unda varaqlardan birida quyidagi shakldagi savdo ma'lumotlari bilan bir nechta jadval mavjud:
Yozib oling:
- Har xil o'lchamdagi va har xil mahsulot to'plamlari va mintaqalar bilan hech qanday tartiblashsiz satr va ustunlardagi jadvallar.
- Jadvallar orasiga bo'sh chiziqlar qo'yilishi mumkin.
- Jadvallar soni har qanday bo'lishi mumkin.
Ikkita muhim taxmin. Taxmin qilinadiki:
- Har bir jadvalning tepasida, birinchi ustunda, jadvalda savdosi ko'rsatilgan menejerning ismi ko'rsatilgan (Ivanov, Petrov, Sidorov va boshqalar).
- Barcha jadvallardagi tovarlar va hududlarning nomlari bir xil tarzda yoziladi - ishlarning aniqligi bilan.
Yakuniy maqsad - barcha jadvallardan ma'lumotlarni keyingi tahlil qilish va xulosa tuzish uchun qulay bo'lgan bir tekis normallashtirilgan jadvalga to'plash, ya'ni bu jadvalda:
Qadam 1. Faylga ulaning
Keling, yangi bo'sh Excel faylini yaratamiz va uni yorliqda tanlang ma'lumotlar buyruq Ma'lumotlarni oling - Fayldan - Kitobdan (Ma'lumotlar - Fayldan - Ish kitobidan). Savdo ma'lumotlari bilan manba faylining joylashishini belgilang va keyin navigator oynasida bizga kerak bo'lgan varaqni tanlang va tugmani bosing. Ma'lumotlarni aylantirish (Ma'lumotlarni o'zgartirish):
Natijada, undagi barcha ma'lumotlar Power Query muharririga yuklanishi kerak:
2-qadam. Axlatni tozalang
Avtomatik yaratilgan qadamlarni o'chirish o'zgartirilgan turi (O'zgartirilgan tur) и Yuqori sarlavhalar (Targ'ib qilingan sarlavhalar) va filtr yordamida bo'sh chiziqlar va jami bo'lgan chiziqlardan xalos bo'ling null и JAMI birinchi ustun bo'yicha. Natijada biz quyidagi rasmni olamiz:
3-qadam. Menejerlarni qo'shish
Keyinchalik kimning sotuvi qaerda ekanligini tushunish uchun jadvalimizga ustun qo'shish kerak, bu erda har bir qatorda tegishli familiya bo'ladi. Buning uchun:
1. Buyruq yordamida qator raqamlari bilan yordamchi ustun qo'shamiz Ustun qo'shish - indeks ustuni - 0 dan (Ustun qo'shish - indeks ustuni - 0 dan).
2. Buyruq bilan formulali ustun qo'shing Ustun qo'shish - Maxsus ustun (Ustun qo'shish - Maxsus ustun) va u erda quyidagi qurilishni kiriting:
Ushbu formulaning mantig'i oddiy - agar birinchi ustundagi keyingi katakning qiymati "Mahsulot" bo'lsa, bu biz yangi jadvalning boshlanishiga qoqilganimizni anglatadi, shuning uchun biz oldingi katakning qiymatini ko'rsatamiz. menejerning ismi. Aks holda, biz hech narsani ko'rsatmaymiz, ya'ni null.
Ota-ona katakchasini familiya bilan olish uchun avvalo oldingi bosqichdagi jadvalga murojaat qilamiz #"Indeks qo'shildi", va keyin bizga kerak bo'lgan ustun nomini belgilang [1-ustun] kvadrat qavs ichida va bu ustundagi katak raqami jingalak qavs ichida. Hujayra raqami biz ustundan oladigan joriy raqamdan bitta kam bo'ladi indeksNavbati bilan.
3. Bo'sh kataklarni to'ldirish qoladi null buyrug'i bilan yuqori kataklardan nomlar O'zgartirish - to'ldirish - pastga (O'zgartirish - To'ldirish - Pastga) va indekslar bilan endi kerak bo'lmagan ustunni va birinchi ustundagi familiyalar bilan qatorlarni o'chiring. Natijada biz quyidagilarni olamiz:
Qadam 4. Menejerlar tomonidan alohida jadvallarga guruhlash
Keyingi qadam, har bir menejer uchun qatorlarni alohida jadvallarga guruhlashdir. Buning uchun “Transformatsiya” yorlig‘ida “Guruh bo‘yicha” buyrug‘idan foydalaning (Transform – Group By) va ochilgan oynada “Menejer” ustunini va “Barcha satrlar” (barcha qatorlar) operatsiyasini tanlang. ular (summa, o'rtacha va boshqalar). P.):
Natijada biz har bir menejer uchun alohida jadvallarni olamiz:
5-qadam: Ichki jadvallarni o'zgartiring
Endi biz hosil bo'lgan ustunning har bir katagida joylashgan jadvallarni beramiz Barcha ma'lumotlar munosib shaklda.
Birinchidan, har bir jadvalda endi kerak bo'lmagan ustunni o'chiring rahbar. Biz yana foydalanamiz Maxsus ustun tab O'tkazish (O'zgartirish - Maxsus ustun) va quyidagi formula:
Keyin, boshqa hisoblangan ustun bilan biz har bir jadvaldagi birinchi qatorni sarlavhalarga ko'taramiz:
Va nihoyat, biz asosiy transformatsiyani amalga oshiramiz - M-funktsiyasi yordamida har bir jadvalni ochamiz Jadval.UnpivotBoshqa ustunlar:
Sarlavhadagi hududlarning nomlari yangi ustunga o'tadi va biz torroq, ammo ayni paytda uzunroq normallashtirilgan jadvalni olamiz. bilan bo'sh hujayralar null e'tiborga olinmaydi.
Keraksiz oraliq ustunlardan xalos bo'lish uchun bizda:
6-qadam Ichki jadvallarni kengaytiring
Ustun sarlavhasidagi ikkita strelkali tugma yordamida barcha normallashtirilgan ichki jadvallarni bitta ro'yxatga kengaytirish qoladi:
... va nihoyat biz xohlagan narsaga erishamiz:
Olingan jadvalni buyruq yordamida Excelga qayta eksport qilishingiz mumkin Bosh sahifa — Yopish va yuklash — Yopish va yuklash… (Uy — Yopish va yuklash — Yopish va yuklash…).
- Bir nechta kitoblardan turli sarlavhalar bilan jadvallar tuzing
- Berilgan jilddagi barcha fayllardan ma'lumotlarni yig'ish
- Kitobning barcha varaqlaridan ma'lumotlarni bitta jadvalga to'plash