Манфиатҳои Pivot аз рӯи модели маълумот

Ҳангоми сохтани ҷадвали ҷамъбастӣ дар Excel, дар қуттии муколамаи аввалин, ки дар он аз мо хоҳиш карда мешавад, ки диапазони аввалияро таъин кунем ва ҷойеро барои ворид кардани ҷадвали ҷамъоварӣ интихоб кунем, дар зер қуттии қайдкунии ноаён, вале хеле муҳим мавҷуд аст - Ин маълумотро ба модели маълумот илова кунед (Ин маълумотро илова кунед ба модели маълумот) ва, каме баландтар, гузариш Модели маълумоти ин китобро истифода баред (Истифодаи модели маълумотии ин китоби корӣ):

Мутаассифона, бисёре аз корбароне, ки бо ҷадвалҳои пивот муддати тӯлонӣ ошно ҳастанд ва онҳоро дар кори худ бомуваффақият истифода мебаранд, баъзан маънои ин вариантҳоро дарк намекунанд ва ҳеҷ гоҳ аз онҳо истифода намебаранд. Ва бехуда. Дар ниҳоят, эҷоди ҷадвали ҷамъоварӣ барои Модели маълумот ба мо дар муқоиса бо ҷадвали классикии Excel бартариҳои хеле муҳим медиҳад.

Аммо, пеш аз он ки ин "булочкаҳо"-ро аз наздик баррасӣ кунем, биёед аввал бифаҳмем, ки ин модели додаҳо дар асл чист?

Модели маълумот чист

Модели маълумот (Ихтисоршуда ҳамчун MD ё DM = Модели маълумот) як минтақаи махсус дар дохили файли Excel мебошад, ки дар он шумо метавонед маълумоти ҷадвалиро нигоҳ доред - як ё якчанд ҷадвалҳо, ки агар хоҳед, бо ҳамдигар алоқаманданд. Дар асл, ин махзани хурди (куб OLAP) дар дохили китоби кории Excel ҷойгир карда шудааст. Дар муқоиса бо нигоҳдории классикии маълумот дар шакли ҷадвалҳои муқаррарӣ (ё интеллектуалӣ) дар варақаҳои худи Excel, Модели маълумот дорои якчанд бартариҳои назаррас аст:

• Ҷадвалҳо метавонанд то 2 миллиард хат, ва варақи Excel метавонад каме бештар аз 1 миллионро дар бар гирад.
• Сарфи назар аз андозаи азим, коркарди чунин ҷадвалҳо (филтркунӣ, ҷудокунӣ, ҳисобҳо дар онҳо, хулосаи сохтмон ва ғайра) иҷро карда мешавад. хеле зуд Хеле тезтар аз худи Excel.
• Бо маълумоти Модел, шумо метавонед бо истифода аз ҳисобҳои иловагӣ (агар хоҳед, хеле мураккаб) иҷро кунед забони дарунсохташудаи DAX.
  
• Ҳама маълумоте, ки ба модели маълумот бор карда шудааст, хеле аст сахт фишурда шудааст бо истифода аз бойгонии махсуси дарунсохт ва ба андозаи мӯътадил андозаи файли аслии Excel меафзояд.
  

Модел тавассути як замимаи махсуси дар Microsoft Excel сохташуда идора ва ҳисоб карда мешавад - қувваи барқки дар бораи он аллакай навиштаам. Барои фаъол кардани он, дар ҷадвал таҳиякунанда ангуштзании Иловаҳои COM (Таҳиягар — Иловаҳои COM) ва қуттии мувофиқро санҷед:

Агар ҷадвалҳо таҳиякунанда (Таҳиягар)шумо онро дар лента намебинед, шумо метавонед онро даргиронед Файл - Имконот - Танзими лента (Файл - Интихоб - Тасмаи фармоишӣ). Агар дар равзанаи дар боло дар рӯйхати замимаҳои COM нишон додашуда шумо Power Pivot надошта бошед, он ба версияи Microsoft Office дохил карда нашудааст 🙁

Дар ҷадвали Power Pivot, ки пайдо мешавад, тугмаи калони сабзи равшан хоҳад буд идоракунии (идора кунед), клик кардани он равзанаи Power Pivot дар болои Excel кушода мешавад, ки дар он мо мундариҷаи Модели маълумоти китоби ҷорӣро мебинем:

Як ёддошти муҳим дар роҳ: китоби кории Excel метавонад танҳо як модели маълумотро дар бар гирад.

Ҷадвалҳоро ба Модели маълумот бор кунед

Барои ба Модел бор кардани маълумот, аввал мо ҷадвалро ба миёнабури динамикии клавиатураи "интеллектуалӣ" табдил медиҳем Ctrl+T ва ба он дар ҷадвал номи дӯстона диҳед Конструктор (Тарроҳӣ). Ин як қадами зарурист.

Пас шумо метавонед яке аз се усулро барои интихоб истифода баред:

• Тугмаро пахш кунед Ба модел илова кунед (Илова ба модели маълумот) ҷадвали қувваи барқ ҷадвали хона (Хона).
  
• Интихоби дастаҳо Ворид - Ҷадвали ҷамъбастӣ (Ворид кардан — Ҷадвали ҷамъбастӣ) ва қуттии қайдкуниро фаъол созед Ин маълумотро ба модели маълумот илова кунед (Ин маълумотро ба модели маълумот илова кунед). Дар ин ҳолат, мувофиқи маълумоте, ки ба Модели бор карда шудааст, ҷадвали пивот низ фавран сохта мешавад.
  
• Дар ҷадвали Advanced Маълумот (Сана) тугмаро пахш кунед Аз ҷадвал/диапазон (Аз ҷадвал/диапазон)то ҷадвали моро ба муҳаррири Power Query бор кунед. Ин роҳ дарозтарин аст, аммо агар хоҳед, дар ин ҷо шумо метавонед тозакунии иловагии додаҳо, таҳрир ва ҳама намуди тағиротро иҷро кунед, ки дар он Power Query хеле қавӣ аст.

  Сипас маълумоти шонашуда бо фармон ба Модел бор карда мешавад Хона — Пӯшед ва бор кунед — Пӯшед ва бор кунед… (Хона — Пӯшед ва бор кунед — Пӯшед ва бор кунед…). Дар равзанаи кушодашуда, интихобро интихоб кунед Танҳо пайвастшавӣ эҷод кунед (Танҳо пайваст эҷод кунед) ва аз хама му-химтараш, кайд кардан лозим аст Ин маълумотро ба модели маълумот илова кунед (Ин маълумотро ба модели маълумот илова кунед).
  

Мо мухтасари Модели маълумотро месозем

Барои сохтани модели ҷамъбастии маълумот, шумо метавонед яке аз се равишро истифода баред:

• Тугмаро пахш кунед ҷадвали ҷамъбастӣ (Ҷадвали ҷамъбастӣ) дар равзанаи Power Pivot.
• Фармонҳоро дар Excel интихоб кунед Ворид - Ҷадвали ҷамъбастӣ ва ба режим гузаред Модели маълумоти ин китобро истифода баред (Гузоштан — Ҷадвали ҷамъбастӣ — Модели маълумоти ин китоби корро истифода баред).
• Интихоби дастаҳо Ворид - Ҷадвали ҷамъбастӣ (Ворид кардан — Ҷадвали ҷамъбастӣ) ва қуттии қайдкуниро фаъол созед Ин маълумотро ба модели маълумот илова кунед (Ин маълумотро ба модели маълумот илова кунед). Ҷадвали ҳозираи "интеллектуалӣ" ба Модел бор карда мешавад ва ҷадвали ҷамъбастӣ барои тамоми модел сохта мешавад.

Акнун, ки мо фаҳмидем, ки чӣ гуна маълумотро ба модели додаҳо бор кардан мумкин аст ва дар бораи он ҷамъбаст созем, биёед манфиатҳо ва бартариҳоеро, ки ин ба мо медиҳад, биомӯзем.

Фоидаи 1: Муносибати байни ҷадвалҳо бидуни истифодаи формулаҳо

Хулосаи муқаррариро танҳо бо истифода аз маълумот аз як ҷадвали манбаъ сохтан мумкин аст. Агар шумо якчандтои онҳоро дошта бошед, масалан, фурӯш, нархнома, феҳристи муштариён, феҳристи шартномаҳо ва ғайра, пас шумо аввал бояд маълумотро аз ҳамаи ҷадвалҳо бо истифода аз функсияҳо ба монанди VLOOKUP ҷамъ кунед. (ИНТИЗОР), ИНДЕКС (ИНДЕКС), БЕШТАР ФОШ ШУДААСТ (МАТЧ), SUMMESLIMN (SUMIFS) ва монанди инхо. Ин тӯлонӣ, дилгиркунанда аст ва Excel-и шуморо ба "фикр" бо миқдори зиёди маълумот мебарад.

Дар сурати мухтасари модели маълумот, ҳама чиз хеле соддатар аст. Як маротиба дар равзанаи Power Pivot танзим кардани муносибатҳо байни ҷадвалҳо кифоя аст ва ин кор анҷом ёфт. Барои ин, дар ҷадвал қувваи барқ кнопкаро пахш кунед идоракунии (идора кунед) ва баъд дар равзанаи пайдошуда – тугмача Намоиши диаграмма (Намоиши диаграмма). Барои эҷод кардани истинодҳо барои кашола кардани номҳои сутунҳои (майдонҳои) умумӣ (калидӣ) дар байни ҷадвалҳо боқӣ мемонад:

Пас аз он, дар хулосаи Модели маълумот, шумо метавонед дар майдони ҷамъбаст (сатрҳо, сутунҳо, филтрҳо, арзишҳо) ҳама гуна майдонҳоро аз ҷадвалҳои алоқаманд гузоред - ҳама чиз ба таври худкор пайваст ва ҳисоб карда мешавад:

Фоидаи 2: Арзишҳои беназирро ҳисоб кунед

Ҷадвали муҷаҳҳаз ба мо имкон медиҳад, ки яке аз якчанд функсияҳои ҳисобкунии дарунсохтро интихоб кунем: ҷамъ, миёна, ҳисоб, ҳадди ақал, максимум ва ғайра. Дар ҷамъбасти модели маълумот ба ин рӯйхати стандартӣ функсияи хеле муфид барои ҳисоб кардани шумораи беназир (қиматҳои такрорнашаванда). Бо ёрии он, масалан, шумо метавонед ба осонӣ шумораи ашёи нодири молро (ассортимент), ки мо дар ҳар як шаҳр мефурӯшем, ҳисоб кунед.

Дар майдони рост клик кунед - фармон Имконоти майдони арзиш ва дар лавҳа амалиёт интихоб кардан Шумораи унсурҳои гуногун (Ҳисоби мушаххас):

Манфиати 3: Формулаҳои фармоишии DAX

Баъзан шумо бояд дар ҷадвалҳои ҷамъоварӣ ҳисобҳои гуногуни иловагиро иҷро кунед. Дар ҷамъбасти муқаррарӣ, ин бо истифода аз майдонҳо ва объектҳои ҳисобшуда анҷом дода мешавад, дар ҳоле ки хулосаи модели додаҳо ченакҳоро бо забони махсуси DAX истифода мебарад (DAX = Ифодаи таҳлили маълумот).

Барои сохтани андоза, дар ҷадвал интихоб кунед қувваи барқ фармон Тадбирҳо - Эҷоди чораҳо (Тадбирҳо — Тадбирҳои нав) ё танҳо бо тугмаи рости муш дар ҷадвал дар рӯйхати Майдонҳои Пивот клик кунед ва интихоб кунед Илова кардани андоза (Илова кардани андоза) дар менюи контекстӣ:

Дар равзанаи кушодашуда, танзим кунед:

• Номи ҷадвалки дар он тадбири офаридашуда нигох дошта мешавад.
• Номи андозагирӣ - ҳар номе, ки шумо барои соҳаи нав мефаҳмед.
• Тавсифи - ихтиёрӣ.
• формула - чизи аз ҳама муҳим, зеро дар ин ҷо мо ё дастӣ ворид мекунем, ё тугмаро пахш мекунем fx ва аз рӯйхат функсияи DAX-ро интихоб кунед, ки он бояд натиҷаро ҳисоб кунад, вақте ки мо ченаки худро ба майдони Арзишҳо меандозем.
• Дар қисми поёнии равзана, шумо метавонед фавран формати рақамро барои ченак дар рӯйхат муқаррар кунед категория.
  

Фаҳмидани забони DAX на ҳамеша осон аст, зеро на бо арзишҳои инфиродӣ, балки бо тамоми сутунҳо ва ҷадвалҳо кор мекунад, яъне баъзе таҷдиди сохтори тафаккурро пас аз формулаҳои классикии Excel талаб мекунад. Бо вуҷуди ин, ин меарзад, зеро қудрати қобилиятҳои он дар коркарди миқдори зиёди маълумот аз ҳад зиёд арзёбӣ кардан душвор аст.

Бартарии 4: иерархияи майдони фармоишӣ

Аксар вақт, ҳангоми эҷоди ҳисоботи стандартӣ, шумо бояд ҳамон як комбинатсияи майдонҳоро дар пайдарпаии додашуда партоед, масалан, Сол-чорьяк-мох-руз, ё Категория - Маҳсулот, ё Кишвар-шаҳр-муштарӣ ва ғайра. Дар хулосаи модели маълумот, ин мушкилот тавассути эҷоди худ ба осонӣ ҳал карда мешавад иерархияҳо — маҷмӯи майдонҳои фармоишӣ.

Дар равзанаи Power Pivot бо тугма ба ҳолати диаграмма гузаред Намоиши диаграмма ҷадвали хона (Хона — Намоиши диаграмма), бо интихоб кунед Ctrl майдонҳои дилхоҳ ва тугмаи рости онҳоро пахш кунед. Менюи контекстӣ фармонро дар бар мегирад Эҷоди иерархия (Иерархия эҷод кунед):

Иерархияи сохташударо метавон тағир дод ва бо муш майдонҳои лозимиро ба он кашид, то баъдтар дар як ҳаракат онҳо ба хулоса андохта шаванд:

Бартарии 5: Шаблонҳои фармоишӣ

Идомаи идеяи параграфи қаблӣ, дар хулосаи Модели маълумот, шумо инчунин метавонед маҷмӯи унсурҳои худро барои ҳар як майдон эҷод кунед. Масалан, аз тамоми рӯйхати шаҳрҳо, шумо метавонед ба осонӣ маҷмӯи танҳо онҳоеро, ки дар минтақаи масъулияти шумо ҳастанд, созед. Ё танҳо муштариёни худ, молҳои худ ва ғайраро ба маҷмӯи махсус ҷамъ кунед.

Барои ин, дар ҷадвал Таҳлили ҷадвали пивот дар рӯйхати афтанда Майдонҳо, ҷузъҳо ва маҷмӯаҳо фармонҳои мувофиқ мавҷуданд (Таҳлил - Филдс, Иtems & Sets — Эҷоди маҷмӯа дар асоси ҷузъҳои сатр/сутун):

Дар равзанаи кушодашуда шумо метавонед ҷойгиркунии ҳама гуна элементҳоро ба таври интихобӣ хориҷ кунед, илова кунед ё тағир диҳед ва маҷмӯи натиҷаҳоро бо номи нав захира кунед:

Ҳама маҷмӯаҳои сохташуда дар панели Майдонҳои Ҷадвали Ҷадвали Ҷамъбастӣ дар ҷузвдони алоҳида намоиш дода мешаванд, ки аз он ҷо онҳоро ба сатрҳо ва сутунҳои ҳар як Ҷадвали нави нав кашола кардан мумкин аст:

Манфиати 6: Ҷадвалҳо ва сутунҳоро интихобан пинҳон кунед

Гарчанде ки ин дар баъзе мавридҳо як бартарии хурд, вале хеле гуворо аст. Бо пахш кардани тугмаи рости муш ба номи майдон ё ҷадвали ҷадвал дар равзанаи Power Pivot, шумо метавонед фармонро интихоб кунед. Пинҳон кардан аз Toolkit Client (Пинҳон аз абзорҳои муштарӣ):

Сутун ё ҷадвали пинҳоншуда аз панели Рӯйхати Майдони Ҷадвали Пивот нопадид мешавад. Ин хеле қулай аст, агар шумо бояд аз корбар якчанд сутунҳои ёрирасонро (масалан, ҳисобшуда ё сутунҳои дорои арзишҳои калидӣ барои эҷоди муносибатҳо) ё ҳатто тамоми ҷадвалҳоро пинҳон кунед.

Фоида 7. Пармакунии пешрафта

Агар шумо ягон чашмаки майдони арзишро дар ҷадвали муқаддамӣ ду маротиба клик кунед, Excel дар варақи алоҳида нусхаи порчаи маълумотҳои сарчашмаро, ки дар ҳисобкунии ин ячейка иштирок карда буд, намоиш медиҳад. Ин як чизи хеле қулай аст, ки расман Drill-down номида мешавад (дар онҳо одатан "нокомӣ" мегӯянд).

Дар хулосаи Модели маълумот, ин асбоби қулай ба таври дақиқтар кор мекунад. Бо истодан дар ҳама гуна ячейкае, ки натиҷае ба мо ҷолиб аст, шумо метавонед тасвири шишаи лупаеро, ки дар паҳлӯи он пайдо мешавад, клик кунед (он ном дорад) Тамоюлҳои экспресс) ва он гоҳ ягон майдони дар ҳама гуна ҷадвали алоқамандро интихоб кунед:

Пас аз он, арзиши ҷорӣ (Model = Explorer) ба минтақаи филтр дохил мешавад ва хулоса аз ҷониби идораҳо сохта мешавад:

Албатта, чунин тартиб метавонад борҳо такрор карда шавад, ки пайваста маълумотро дар самте, ки шумо ба он таваҷҷӯҳ доред, омӯзед.

Фоидаи 8: Табдил додани Pivot ба функсияҳои куб

Агар шумо ягон чашмаки ҷамъбасти Модели маълумотро интихоб кунед ва сипас дар ҷадвал интихоб кунед Таҳлили ҷадвали пивот фармон Асбобҳои OLAP - Табдил додан ба формулаҳо (Таҳлил - Tools OLAP - Табдил додан ба формулаҳо), пас тамоми хулоса ба таври худкор ба формулаҳо табдил дода мешавад. Акнун арзишҳои майдон дар майдони сатр-сутун ва натиҷаҳо дар минтақаи арзиш аз Модели маълумот бо истифода аз функсияҳои махсуси кубӣ гирифта мешаванд: CUBEVALUE ва CUBEMEMBER:

Аз ҷиҳати техникӣ, ин маънои онро дорад, ки ҳоло мо бо хулоса не, балки бо якчанд ҳуҷайраҳои дорои формулаҳо кор мекунем, яъне мо метавонем бо гузориши худ ҳар гуна тағиротро, ки дар хулоса мавҷуд нестанд, ба осонӣ анҷом диҳем, масалан, ба мобайн сатрҳо ё сутунҳои нав гузоред. ҳисобот, ҳама гуна ҳисобҳои иловагиро дар дохили хулоса анҷом диҳед, онҳоро ба тариқи дилхоҳ тартиб диҳед ва ғайра.

Дар баробари ин, алоќа бо маълумоти сарчашма, албатта, боќї мемонад ва дар оянда ин формулањо њангоми таѓйирёбии манбаъњо нав карда мешаванд. Зебоӣ!

• Таҳлили нақша-факт дар ҷадвали ҷамъоварда бо Power Pivot ва Power Query
• Ҷадвали ҷамъбастӣ бо сарлавҳаи бисёрсатр
• Бо истифода аз Power Pivot дар Excel пойгоҳи додаҳо эҷод кунед