Ma'lumotlar modeli bo'yicha Pivotning afzalliklari

Excelda pivot jadvalini yaratishda, bizdan dastlabki diapazonni o'rnatish va pivot jadvalini qo'yish uchun joy tanlash so'raladigan birinchi dialog oynasida, quyida ko'zga ko'rinmas, ammo juda muhim katakcha mavjud - Ushbu ma'lumotlarni Ma'lumotlar modeliga qo'shing (Ushbu ma'lumotlarni qo'shing Ma'lumotlar modeliga) va biroz balandroq, kalit Ushbu kitobning ma'lumotlar modelidan foydalaning (Ushbu ish kitobining ma'lumotlar modelidan foydalaning):

Afsuski, pivot jadvallari bilan uzoq vaqtdan beri tanish bo'lgan va ularni o'z ishlarida muvaffaqiyatli ishlatadigan ko'plab foydalanuvchilar ba'zan bu variantlarning ma'nosini tushunmaydilar va ulardan hech qachon foydalanmaydilar. Va behuda. Axir, Ma'lumotlar modeli uchun pivot jadvalini yaratish bizga klassik Excel pivot jadvaliga nisbatan bir qancha muhim afzalliklarni beradi.

Biroq, ushbu "bulochkalar" ni yaqindan ko'rib chiqishdan oldin, avvalo, ushbu ma'lumotlar modeli nima ekanligini tushunib olaylik.

Ma'lumotlar modeli nima

Ma'lumotlar modeli (MD yoki DM sifatida qisqartirilgan = Ma'lumotlar modeli) Excel fayli ichidagi maxsus maydon bo'lib, unda jadval ma'lumotlarini saqlashingiz mumkin - agar kerak bo'lsa, bir yoki bir nechta jadvallar bir-biriga bog'langan. Aslida, bu Excel ish kitobiga kiritilgan kichik ma'lumotlar bazasi (OLAP kubi). Excel-ning o'zida oddiy (yoki aqlli) jadvallar ko'rinishidagi ma'lumotlarni klassik saqlash bilan solishtirganda, Ma'lumotlar modeli bir qator muhim afzalliklarga ega:

• Jadvallar gacha bo'lishi mumkin 2 milliard qator, va Excel varag'i 1 milliondan bir oz ko'proq sig'ishi mumkin.
• Katta hajmga qaramay, bunday jadvallarni qayta ishlash (filtrlash, saralash, ular bo'yicha hisob-kitoblar, qurilish xulosasi va boshqalar) amalga oshiriladi. juda tez Excelning o'zidan ancha tezroq.
• Modeldagi ma'lumotlar yordamida siz qo'shimcha (agar xohlasangiz, juda murakkab) hisob-kitoblarni amalga oshirishingiz mumkin o'rnatilgan DAX tili.
  
• Ma'lumotlar modeliga yuklangan barcha ma'lumotlar juda kuchli siqilgan maxsus o'rnatilgan arxivator yordamida va asl Excel faylining hajmini o'rtacha darajada oshiradi.
  

Model Microsoft Excel-ga o'rnatilgan maxsus plagin tomonidan boshqariladi va hisoblab chiqiladi - quvvat pivotibu haqda men allaqachon yozganman. Uni yoqish uchun yorliqda dasturchi bosish COM plaginlari (Ishlab chiquvchi - COM plaginlari) va tegishli katakchani belgilang:

Agar yorliqlar dasturchi (ishlab chiquvchi)siz uni lentada ko'ra olmaysiz, uni yoqishingiz mumkin Fayl - Variantlar - Tasmani o'rnatish (Fayl - Variantlar - Tasmani sozlash). Agar MAQOMOTI qo'shimchalari ro'yxatida yuqorida ko'rsatilgan oynada sizda Power Pivot bo'lmasa, u sizning Microsoft Office versiyangizga kiritilmagan 🙁

Ko'rsatilgan Power Pivot yorlig'ida katta och yashil tugma bo'ladi boshqaruv (Boshqarish), ustiga bosish Excelning tepasida Power Pivot oynasini ochadi, bu erda biz joriy kitobning ma'lumotlar modeli tarkibini ko'ramiz:

Yo'lda muhim eslatma: Excel ish kitobida faqat bitta ma'lumotlar modeli bo'lishi mumkin.

Ma'lumotlar modeliga jadvallarni yuklang

Modelga ma'lumotlarni yuklash uchun avval jadvalni dinamik "aqlli" klaviatura yorlig'iga aylantiramiz Ctrl+T va yorliqda unga do'stona nom bering konstruktor (Dizayn). Bu talab qilinadigan qadamdir.

Keyin siz uchta usuldan birini tanlashingiz mumkin:

• Tugmani bosing Modelga qo'shish (Ma'lumotlar modeliga qo'shish) tab quvvat pivoti tab Bosh sahifa (Uy).
  
• Jamoalarni tanlash Qo'shish - Pivot jadval (Qo'shish - umumiy jadval) va katakchani yoqing Ushbu ma'lumotlarni Ma'lumotlar modeliga qo'shing (Ushbu ma'lumotlarni Ma'lumotlar modeliga qo'shing). Bunday holda, Modelga yuklangan ma'lumotlarga ko'ra, pivot jadvali ham darhol quriladi.
  
• Kengaytirilgan yorlig'ida ma'lumotlar (Sana) tugmasini bosing Jadval/diapazondan (Jadval/diapazondan)jadvalimizni Power Query muharririga yuklash uchun. Bu yo'l eng uzun, ammo agar xohlasangiz, bu erda siz qo'shimcha ma'lumotlarni tozalash, tahrirlash va Power Query juda kuchli bo'lgan barcha turdagi o'zgarishlarni amalga oshirishingiz mumkin.

  Keyin taralgan ma'lumotlar buyruq orqali Modelga yuklanadi Bosh sahifa — Yopish va yuklash — Yopish va yuklash… (Uy — Yopish va yuklash — Yopish va yuklash…). Ochilgan oynada variantni tanlang Faqat ulanishni yarating (Faqat ulanish yaratish) va, eng muhimi, belgi qo'ying Ushbu ma'lumotlarni Ma'lumotlar modeliga qo'shing (Ushbu ma'lumotlarni Ma'lumotlar modeliga qo'shing).
  

Biz ma'lumotlar modelining qisqacha mazmunini yaratamiz

Xulosa ma'lumotlar modelini yaratish uchun siz uchta yondashuvdan birini qo'llashingiz mumkin:

• Tugmasini bosing xulosa jadvali (Pivot jadvali) Power Pivot oynasida.
• Excelda buyruqlarni tanlang Qo'shish - Pivot jadval va rejimga o'ting Ushbu kitobning ma'lumotlar modelidan foydalaning (Qo'shish - Umumiy jadval - Ushbu ish kitobining ma'lumotlar modelidan foydalaning).
• Jamoalarni tanlash Qo'shish - Pivot jadval (Qo'shish - umumiy jadval) va katakchani yoqing Ushbu ma'lumotlarni Ma'lumotlar modeliga qo'shing (Ushbu ma'lumotlarni Ma'lumotlar modeliga qo'shing). Joriy “aqlli” jadval Modelga yuklanadi va butun Model uchun umumlashtirilgan jadval tuziladi.

Endi biz Ma'lumotlar modeliga ma'lumotlarni qanday yuklash va u bo'yicha xulosa tuzishni aniqladik, keling, bu bizga beradigan afzallik va afzalliklarni ko'rib chiqaylik.

1-foyda: formulalardan foydalanmasdan jadvallar o'rtasidagi munosabatlar

Oddiy xulosa faqat bitta manba jadvalidagi ma'lumotlardan foydalangan holda tuzilishi mumkin. Agar sizda ulardan bir nechtasi bo'lsa, masalan, sotuvlar, narxlar ro'yxati, mijozlar katalogi, shartnomalar reestri va boshqalar, unda siz VLOOKUP kabi funktsiyalardan foydalanib, birinchi navbatda barcha jadvallardan ma'lumotlarni bir joyga to'plashingiz kerak bo'ladi. (KO'RISH), INDEKS (INDEKS), KO'PROQ TA'SHIRILGAN (MATCH), SUMMESLIMN (SUMIFS) va shunga o'xshashlar. Bu uzoq, zerikarli va Excelni katta hajmdagi ma'lumotlarga ega "fikr"ga aylantiradi.

Ma'lumotlar modelining qisqacha mazmuni bo'lsa, hamma narsa ancha sodda. Power Pivot oynasida jadvallar o'rtasidagi munosabatlarni bir marta o'rnatish kifoya - va u tugadi. Buning uchun yorliqda quvvat pivoti tugmasini bosing boshqaruv (Boshqarish) va keyin paydo bo'lgan oynada - tugma Diagramma ko'rinishi (Diagramma ko'rinishi). Havolalar yaratish uchun jadvallar orasidagi umumiy (kalit) ustun nomlarini (maydonlarini) sudrab borish qoladi:

Shundan so'ng, Ma'lumotlar modelining xulosasida siz jamlama maydoniga (satrlar, ustunlar, filtrlar, qiymatlar) har qanday tegishli jadvallardan istalgan maydonlarni kiritishingiz mumkin - hamma narsa avtomatik ravishda bog'lanadi va hisoblab chiqiladi:

2-foyda: noyob qiymatlarni hisoblang

Oddiy pivot jadval bizga bir nechta o'rnatilgan hisoblash funktsiyalaridan birini tanlash imkoniyatini beradi: yig'indi, o'rtacha, hisoblash, minimal, maksimal va hokazo. Ma'lumotlar modelining xulosasida ushbu standart ro'yxatga juda foydali funksiya qo'shilgan. noyob (takrorlanmaydigan qiymatlar) soni. Uning yordami bilan, masalan, biz har bir shaharda sotadigan noyob tovarlar (assortiment) sonini osongina hisoblashingiz mumkin.

Maydonni o'ng tugmasini bosing - buyruq Qiymat maydoni parametrlari va yorliqda operatsiya tanlang Turli elementlarning soni (Alohida hisob):

3-foyda: Maxsus DAX formulalari

Ba'zan pivot jadvallarda turli xil qo'shimcha hisob-kitoblarni bajarishingiz kerak bo'ladi. Muntazam xulosalarda, bu hisoblangan maydonlar va ob'ektlar yordamida amalga oshiriladi, ma'lumotlar modelining xulosasi esa maxsus DAX tilida o'lchovlardan foydalanadi (DAX = Data Analysis Expressions).

O'lchov yaratish uchun yorliqda tanlang quvvat pivoti buyruq Chora-tadbirlar - chora-tadbirlar yaratish (Tadbirlar - Yangi chora) yoki Pivot Fields ro'yxatidagi jadvalni o'ng tugmasini bosing va tanlang O'lchov qo'shing (O'lchov qo'shing) kontekst menyusida:

Ochilgan oynada quyidagilarni o'rnating:

• Jadval nomiyaratilgan o'lchov qaerda saqlanadi.
• O'lchov nomi - yangi maydon uchun tushunadigan har qanday nom.
• Tavsif - ixtiyoriy.
• formula - eng muhimi, chunki bu erda biz qo'lda kiritamiz yoki tugmani bosing fx va ro'yxatdan DAX funktsiyasini tanlang, biz o'lchovimizni Qiymatlar maydoniga tashlaganimizda natijani hisoblashimiz kerak.
• Oynaning pastki qismida siz darhol ro'yxatdagi o'lchov uchun raqam formatini o'rnatishingiz mumkin kategoriya.
  

DAX tilini tushunish har doim ham oson emas, chunki alohida qiymatlar bilan emas, balki butun ustunlar va jadvallar bilan ishlaydi, ya'ni klassik Excel formulalaridan keyin fikrlashni biroz qayta qurishni talab qiladi. Biroq, bunga arziydi, chunki katta hajmdagi ma'lumotlarni qayta ishlashda uning imkoniyatlarini ortiqcha baholash qiyin.

4-foyda: Maxsus maydon ierarxiyasi

Ko'pincha, standart hisobotlarni yaratishda siz bir xil maydonlar kombinatsiyasini ma'lum ketma-ketlikda pivot jadvallarga tashlashingiz kerak, masalan Yil-chorak-oy-kunyoki Turkum-mahsulotyoki Mamlakat-shahar-mijoz va hokazo. Ma'lumotlar modelining xulosasida bu muammoni o'zingiz yaratish orqali osongina hal qilinadi ierarxiya - moslashtirilgan maydonlar to'plamlari.

Power Pivot oynasida tugma yordamida diagramma rejimiga o'ting Diagramma ko'rinishi tab Bosh sahifa (Uy — Diagramma koʻrinishi), bilan tanlang Ctrl kerakli maydonlarni tanlang va ularni o'ng tugmasini bosing. Kontekst menyusi buyruqni o'z ichiga oladi Ierarxiya yaratish (Ierarxiya yaratish):

Yaratilgan ierarxiya nomini o'zgartirishi va sichqoncha yordamida kerakli maydonlarni sudrab olib borishi mumkin, shunda keyinchalik ular bir harakatda xulosaga kiritilishi mumkin:

Foyda 5: Maxsus trafaretlar

Oldingi paragrafning g'oyasini davom ettirib, Ma'lumotlar modelining xulosasida siz har bir maydon uchun o'zingizning elementlar to'plamini yaratishingiz mumkin. Masalan, barcha shaharlar ro'yxatidan siz o'zingizning mas'uliyat sohangizdagilar to'plamini osongina yaratishingiz mumkin. Yoki faqat mijozlaringizni, tovarlaringizni va hokazolarni maxsus to'plamga to'plang.

Buning uchun yorliqda Pivot jadval tahlili ochiladigan ro'yxatda Maydonlar, elementlar va to'plamlar tegishli buyruqlar mavjud (Tahlil qiling - Fields, Items & Sets — satr/ustun elementlari asosida toʻplam yaratish):

Ochilgan oynada siz tanlab olib tashlashingiz, istalgan elementlarning o'rnini qo'shishingiz yoki o'zgartirishingiz va natijada olingan to'plamni yangi nom bilan saqlashingiz mumkin:

Barcha yaratilgan to'plamlar PivotTable Fields panelida alohida papkada ko'rsatiladi, u erdan ularni har qanday yangi PivotTable satrlari va ustunlari joylariga erkin sudrab olib borish mumkin:

6-foyda: Jadvallar va ustunlarni tanlab yashirish

Bu kichik, ammo ba'zi hollarda juda yoqimli afzallik bo'lsa-da. Power Pivot oynasidagi maydon nomini yoki jadval yorlig'ini sichqonchaning o'ng tugmasi bilan bosish orqali siz buyruqni tanlashingiz mumkin. Client Toolkit'dan yashirish (Mijoz asboblaridan yashirish):

Yashirin ustun yoki jadval Pivot Table maydonlari ro'yxati panelidan yo'qoladi. Agar siz foydalanuvchidan ba'zi yordamchi ustunlarni (masalan, hisoblangan yoki aloqalarni yaratish uchun kalit qiymatlari bo'lgan ustunlar) yoki hatto butun jadvallarni yashirishingiz kerak bo'lsa, bu juda qulay.

Foyda 7. Kengaytirilgan matkap

Agar siz oddiy pivot jadvalidagi qiymatlar maydonidagi istalgan katakchani ikki marta bossangiz, Excel alohida varaqda ushbu katakchani hisoblashda ishtirok etgan manba ma'lumotlar qismining nusxasini ko'rsatadi. Bu juda qulay narsa, rasmiy ravishda Drill-down deb ataladi (ular odatda "muvaffaqiyatsiz" deyishadi).

Ma'lumotlar modelining xulosasida ushbu qulay vosita yanada nozik ishlaydi. Bizni qiziqtirgan natijaga ega bo'lgan istalgan katakda turib, uning yonida ochiladigan kattalashtiruvchi oynali belgini bosishingiz mumkin (u deyiladi. Ekspress trendlar) va keyin istalgan tegishli jadvalda sizni qiziqtirgan maydonni tanlang:

Shundan so'ng, joriy qiymat (Model = Explorer) filtr maydoniga kiradi va xulosa ofislar tomonidan tuziladi:

Albatta, bunday protsedura ko'p marta takrorlanishi mumkin, bu sizning ma'lumotlaringizni sizni qiziqtirgan yo'nalishda izchil ravishda o'rganishdir.

8-foyda: Pivotni kub funksiyalariga aylantiring

Agar siz Ma'lumotlar modeli uchun xulosada biron bir katakchani tanlasangiz va keyin yorliqda tanlang Pivot jadval tahlili buyruq OLAP asboblari - Formulalarga aylantirish (Tahlil qilish - OLAP asboblari - formulalarga aylantirish), keyin butun xulosa avtomatik ravishda formulalarga aylantiriladi. Endi satr-ustun maydonidagi maydon qiymatlari va qiymat sohasidagi natijalar maxsus kub funksiyalari yordamida Ma'lumotlar modelidan olinadi: CUBEVALUE va CUBEMEMBER:

Texnik jihatdan bu shuni anglatadiki, biz hozir xulosa bilan emas, balki formulalari bo'lgan bir nechta katakchalar bilan shug'ullanyapmiz, ya'ni xulosada mavjud bo'lmagan hisobotimiz bilan har qanday o'zgarishlarni osongina amalga oshirishimiz mumkin, masalan, o'rtaga yangi qatorlar yoki ustunlar qo'shing. hisobot, xulosa ichida qo'shimcha hisob-kitoblarni bajaring, ularni istalgan tarzda tartibga soling va hokazo.

Shu bilan birga, manba ma'lumotlari bilan aloqa, albatta, saqlanib qoladi va kelajakda bu formulalar manbalar o'zgarganda yangilanadi. Go'zallik!

• Power Pivot va Power Query bilan pivot jadvalda reja-fakt tahlili
• Ko'p qatorli sarlavhali pivot jadval
• Power Pivot yordamida Excelda ma'lumotlar bazasini yarating