Маалымат модели боюнча Pivot'тун артыкчылыктары

Excelде пивот таблицасын куруп жатканда, бизден баштапкы диапазонду коюуну жана пивот таблицасын киргизүү үчүн жерди тандоону талап кылган биринчи диалог кутусунда төмөндө көрүнбөгөн, бирок абдан маанилүү белги кутучасы бар - Бул маалыматты Маалымат моделине кошуңуз (Бул маалыматтарды кошуу маалымат моделине) жана, бир аз жогору, которуу Бул китептин маалымат моделин колдонуңуз (Бул иш китебинин маалымат моделин колдонуңуз):

Тилекке каршы, пивот таблицалары менен көптөн бери тааныш болгон жана аларды өз ишинде ийгиликтүү колдонгон көптөгөн колдонуучулар кээде бул варианттардын маанисин түшүнүшпөйт жана аларды эч качан колдонушпайт. Жана бекер. Анткени, маалымат модели үчүн пивот таблицасын түзүү бизге классикалык Excel пивот столуна салыштырмалуу бир нече маанилүү артыкчылыктарды берет.

Бирок, бул "булочкаларды" жакындан карап чыгуудан мурун, адегенде бул Data Model деген эмне экенин түшүнүп алалы?

Маалымат модели деген эмне

Маалымат модели (MD же DM катары кыскартылган = Маалымат модели) Excel файлынын ичиндеги атайын аймак, анда сиз таблицадагы маалыматтарды сактай аласыз - эгер кааласаңыз, бири-бири менен байланышкан бир же бир нече таблица. Чынында, бул Excel иш китебинин ичине камтылган кичинекей маалымат базасы (OLAP кубу). Excel барактарында кадимки (же акылдуу) таблицалар түрүндөгү маалыматтарды классикалык сактоого салыштырмалуу, Data Model бир нече олуттуу артыкчылыктарга ээ:

• Столдорго чейин болушу мүмкүн 2 миллиард сап, жана Excel барагы 1 миллиондон бир аз көбүрөөк туура келет.
• Гиганттык өлчөмүнө карабастан, мындай таблицаларды иштетүү (фильтрлөө, сорттоо, алар боюнча эсептөөлөр, курулуштун корутундусу ж.б.у.с.) жүргүзүлөт. абдан тез Excelдин өзүнөн алда канча ылдам.
• Моделдеги маалыматтар менен сиз кошумча (эгер кааласаңыз, өтө татаал) эсептөөлөрдү колдоно аласыз камтылган DAX тили.
  
• Маалымат моделине жүктөлгөн бардык маалымат абдан катуу кысылган атайын орнотулган архиватордун жардамы менен баштапкы Excel файлынын көлөмүн бир аз көбөйтөт.
  

Модель Microsoft Excelге орнотулган атайын кошумча тарабынан башкарылат жана эсептелинет - powerpivotбул жөнүндө мен буга чейин жазганмын. Аны иштетүү үчүн, өтмөктө иштеп чыкылдатуу COM кошумчалары (Иштеп чыгуучу — COM кошумчалары) жана тиешелүү кутучаны белгилеңиз:

Эгерде өтмөктөр иштеп (Иштеп чыгуучу)аны лентадан көрө албайсыз, аны күйгүзсөңүз болот Файл - Параметрлер - Тасма орнотуу (Файл — Параметрлер — Тасманы ыңгайлаштыруу). Эгерде жогоруда көрсөтүлгөн терезеде COM кошумчаларынын тизмесинде сизде Power Pivot жок болсо, анда ал сиздин Microsoft Office версияңызга кирбейт 🙁

Пайда болгон Power Pivot өтмөгүндө чоң ачык жашыл баскыч пайда болот жетекчилик (Башкаруу), аны чыкылдатуу менен Excelдин үстүндө Power Pivot терезеси ачылат, анда биз учурдагы китептин Маалымат моделинин мазмунун көрөбүз:

Жолдо маанилүү эскертүү: Excel иш китеби бир гана маалымат моделин камтышы мүмкүн.

Берилиштер моделине таблицаларды жүктөө

Моделге маалыматтарды жүктөө үчүн, адегенде биз таблицаны динамикалык "акылдуу" баскычтоптун жарлыгына айлантабыз Ctrl+T жана өтмөктө ага достук ат бериңиз Конструктор (Дизайн). Бул талап кылынган кадам.

Андан кийин сиз тандоо үчүн үч ыкманын каалаганын колдоно аласыз:

• баскычын басуу Моделге кошуу (Маалымат моделине кошуу) табулатура powerpivot табулатура Home (Үй).
  
• Командаларды тандоо Кыстаруу – Пивот таблицасы (Киргизүү — Пивот таблицасы) жана белги кутучасын күйгүзүңүз Бул маалыматты Маалымат моделине кошуңуз (Бул маалыматты Маалымат моделине кошуу). Бул учурда, Моделге жүктөлгөн маалыматтарга ылайык, пивот таблицасы да дароо курулат.
  
• Advanced өтмөгүндө маалыматтар (Дата) баскычын чыкылдатыңыз Таблицадан/Арападан (Таблицадан/Арападан)биздин таблицаны Power Query редакторуна жүктөө үчүн. Бул жол эң узун, бирок, эгер кааласаңыз, бул жерде сиз кошумча маалыматтарды тазалоону, түзөтүүнү жана Power Query абдан күчтүү болгон бардык түрдөгү трансформацияларды аткара аласыз.

  Андан кийин уяланган маалыматтар буйрук менен Модельге жүктөлөт Башкы бет — Жабуу жана жүктөө — Жабуу жана жүктөө… (Башкы бет — Жабуу&Жүктөө — Жабуу&Жүктөө...). Ачылган терезеде параметрди тандаңыз Жөн гана байланыш түзүңүз (Байланыш түзүү гана) жана, эн негизгиси, белги коюу Бул маалыматты Маалымат моделине кошуңуз (Бул маалыматты Маалымат моделине кошуу).
  

Биз Маалымат моделинин корутундусун түзөбүз

Кыскача маалымат моделин түзүү үчүн, сиз үч ыкманын каалаганын колдоно аласыз:

• Пресс-баскычы жыйынды таблица (Пивот таблицасы) Power Pivot терезесинде.
• Excelде буйруктарды тандаңыз Кыстаруу – Пивот таблицасы жана режимге өтүңүз Бул китептин маалымат моделин колдонуңуз (Кыстаруу — Пивот таблица — Бул иш китебинин маалымат моделин колдонуңуз).
• Командаларды тандоо Кыстаруу – Пивот таблицасы (Киргизүү — Пивот таблицасы) жана белги кутучасын күйгүзүңүз Бул маалыматты Маалымат моделине кошуңуз (Бул маалыматты Маалымат моделине кошуу). Учурдагы “акылдуу” таблица Моделге жүктөлөт жана бүтүндөй Модель үчүн жыйынды таблица түзүлөт.

Эми биз Маалымат моделине маалыматтарды кантип жүктөөнү жана анын жыйынтыгын чыгарууну түшүнгөнүбүздөн кийин, анын бизге берген артыкчылыктарын жана артыкчылыктарын изилдеп көрөлү.

1-пайда: Формулаларды колдонбостон, таблицалардын ортосундагы мамилелер

Кадимки корутундуну бир таблицадагы маалыматтарды колдонуу менен гана түзүүгө болот. Эгер сизде алардын бир нечеси бар болсо, мисалы, сатуулар, прейскурант, кардарлардын каталогу, контракттардын реестри ж. (КӨРҮҮ), ИНДЕКС (ИНДЕКС), КӨБҮРӨК ТҮШҮН (МАТЧ), SUMMESLIMN (SUMIFS) жана ушул сыяктуулар. Бул узак, тажатма жана Excel'иңизди көп сандагы маалыматтар менен "ойго" айдайт.

Берилиштер моделинин кыскача баяндамасында бардыгы алда канча жөнөкөй. Power Pivot терезесинде бир жолу таблицалардын ортосундагы мамилелерди орнотуу жетиштүү – жана ал бүттү. Бул үчүн, өтмөктө powerpivot кнопкасын басып, жетекчилик (Башкаруу) анан пайда болгон терезеде - баскыч Диаграмма көрүнүшү (Диаграмма көрүнүшү). Шилтемелерди түзүү үчүн таблицалардын ортосунда жалпы (ачкыч) тилке аттарын (талааларды) сүйрөө керек:

Андан кийин, Маалымат моделинин корутундусунда сиз жыйынтыктоочу аймакка (саптар, мамычалар, чыпкалар, баалуулуктар) каалаган тиешелүү таблицалардан каалаган талааларды ыргыта аласыз - баары автоматтык түрдө байланышып, эсептелинет:

2-пайда: уникалдуу баалуулуктарды санаңыз

Кадимки пивот таблица бизге бир нече орнотулган эсептөө функцияларынын бирин тандоо мүмкүнчүлүгүн берет: сумма, орточо, эсеп, минимум, максимум ж.б. уникалдуу (кайталанбаган маанилердин) саны. Анын жардамы менен, мисалы, биз ар бир шаарда сатуучу товарлардын (ассортименттин) уникалдуу элементтеринин санын оңой санай аласыз.

Талааны оң баскыч менен чыкылдатыңыз - буйрук Маани талаасынын параметрлери жана өтмөктө операция тандап алуу Ар кандай элементтердин саны (Өзгөчө эсеп):

3-пайда: Ыңгайлаштырылган DAX формулалары

Кээде пивот таблицаларында ар кандай кошумча эсептөөлөрдү жүргүзүүгө туура келет. Кадимки резюмелерде бул эсептелген талаалар жана объекттер аркылуу жасалат, ал эми маалымат моделинин корутундусу атайын DAX тилинде (DAX = Data Analysis Expressions) өлчөөлөрдү колдонот.

Өлчөмдү түзүү үчүн өтмөктөн тандаңыз powerpivot буйрук Чаралар – чара түзүү (Чаралар — Жаңы чара) же жөн гана Pivot Fields тизмесинен таблицаны оң баскыч менен чыкылдатып, тандаңыз Өлчөм кошуу (Өлчөм кошуу) контексттик менюда:

Ачылган терезеде төмөнкүнү коюңуз:

• Таблица атытүзүлгөн чара кайда сакталат.
• Аты-жөнү өлчөө – жаңы талаа үчүн сиз түшүнгөн каалаган ат.
• баяндоо – кошумча.
• формула – эң негизгиси, анткени бул жерде биз кол менен киргизебиз, же кнопканы басабыз fx жана тизмеден DAX функциясын тандаңыз, анда биз өлчөөбүздү баалуулуктар аймагына ыргытканда натыйжаны эсептеп чыгышыбыз керек.
• Терезенин ылдыйкы бөлүгүндө тизмедеги өлчөө үчүн сан форматын дароо орното аласыз Category.
  

DAX тилин түшүнүү дайыма эле оңой боло бербейт, анткени жеке баалуулуктар менен эмес, бүт мамычалар жана таблицалар менен иштейт, б.а. классикалык Excel формулаларынан кийин ой жүгүртүүнү бир аз реструктуризациялоону талап кылат. Бирок, бул арзырлык, анткени чоң көлөмдөгү маалыматтарды иштетүүдө анын мүмкүнчүлүктөрүнүн күчүн ашыкча баалоо кыйын.

4-пайда: Ыңгайлаштырылган талаа иерархиялары

Көбүнчө, стандарттуу отчетторду түзүүдө сиз ошол эле талаалардын айкалыштарын берилген ырааттуулукта пивот таблицаларына ыргытышыңыз керек, мисалы Жыл-чейрек-ай-күнже Категория-Продуктже Өлкө-Шаар-Кардар ж.б. Берилиштер моделинин корутундусунда бул көйгөйдү оңой эле өзүңүздүн түзүңүз иерархиялар — ыңгайлаштырылган талаа топтомдору.

Power Pivot терезесинде баскыч менен диаграмма режимине өтүңүз Диаграмма көрүнүшү табулатура Home (Үй — Диаграмма көрүнүшү), менен тандаңыз Ctrl каалаган талааларды жана аларды оң баскыч менен чыкылдатыңыз. Контексттик меню буйрукту камтыйт Иерархия түзүү (Иерархия түзүү):

Түзүлгөн иерархиянын атын өзгөртүүгө жана чычкандын жардамы менен ага керектүү талааларды сүйрөсө болот, ошондуктан кийинчерээк бир кыймылда алар кыскача мазмунга ыргытылат:

5-пайда: Ыңгайлаштырылган трафареттер

Мурунку абзацтын идеясын улантуу менен, Маалымат моделинин кыскача мазмунунда, сиз ар бир талаа үчүн өзүңүздүн элементтердин топтомун түзө аласыз. Мисалы, шаарлардын бардык тизмесинен сиз өзүңүздүн жооптуу чөйрөңүздөгүлөрдүн гана топтомун түзө аласыз. Же атайын топтомго кардарларыңызды, товарларыңызды ж.б.

Бул үчүн, өтмөктө Пивот таблицасын талдоо ачылуучу тизмеде Талаалар, элементтер жана топтомдор тиешелүү буйруктар бар (Талдоо — Fields, ИТемалар жана топтомдор — сап/мамыча элементтеринин негизинде топтом түзүү):

Ачылган терезеде сиз каалаган элементтердин ордун тандап алып, кошуп же өзгөртө аласыз жана алынган топтомду жаңы ат менен сактай аласыз:

Бардык түзүлгөн топтомдор өзүнчө папкадагы PivotTable Fields панелинде көрсөтүлөт, ал жерден аларды каалаган жаңы PivotTableдин саптарына жана мамычаларына эркин сүйрөсө болот:

6-пайда: Таблицаларды жана мамычаларды тандап жашыруу

Бул кичинекей, бирок кээ бир учурларда абдан жагымдуу артыкчылыгы болсо да. Power Pivot терезесиндеги талаанын атын же таблица өтмөгүн оң баскыч менен чыкылдатуу менен, сиз буйрукту тандай аласыз Client Toolkit'тен жашыруу (Кардар куралдарынан жашыруу):

Жашыруун мамыча же таблица Пивот таблица талаасынын тизмеси панелинен жок болот. Эгер сиз колдонуучудан кээ бир көмөкчү мамычаларды (мисалы, эсептелген же мамилелерди түзүү үчүн негизги маанилери бар мамычаларды) же бүтүндөй таблицаларды жашырышыңыз керек болсо, бул абдан ыңгайлуу.

Пайда 7. Өркүндөтүлгөн бургулоо

Кадимки пивот таблицасынын маанилик чөйрөсүндөгү каалаган уячаны эки жолу чыкылдатсаңыз, Excel өзүнчө баракта бул уячаны эсептөөгө катышкан баштапкы маалымат фрагментинин көчүрмөсүн көрсөтөт. Бул абдан ыңгайлуу нерсе, расмий түрдө Drill-down деп аталат (алар адатта "иштей албай калды" деп айтышат).

Берилиштер моделинин корутундусунда бул ыңгайлуу курал кыйла кылдат иштейт. Бизди кызыктырган натыйжасы бар каалаган уячада туруп, анын жанында пайда болгон лупа бар иконканы бассаңыз болот (ал деп аталат Экспресс тенденциялар) жана андан кийин тиешелүү таблицадан сизди кызыктырган талааны тандаңыз:

Андан кийин, учурдагы маани (Модель = Explorer) чыпка аймагына кирип, кыскача кеңселер тарабынан курулат:

Албетте, мындай жол-жобосу көп жолу кайталанышы мүмкүн, ырааттуу түрдө сизди кызыктырган багытта сиздин маалыматтарга.

8-пайда: Пивотту куб функцияларына айландырыңыз

Эгерде сиз маалымат модели үчүн кыскача уячаны тандап, андан кийин өтмөктөн тандасаңыз Пивот таблицасын талдоо буйрук OLAP куралдары - Формулаларга айландыруу (Талдоо — OLAP куралдары — формулаларга айландыруу), анда бүт корутунду автоматтык түрдө формулаларга айландырылат. Эми сап-мамыча аймагындагы талаа маанилери жана маани чөйрөсүндөгү натыйжалар атайын куб функцияларын колдонуу менен Маалымат моделинен чыгарылат: CUBEVALUE жана CUBEMEMBER:

Техникалык жактан алганда, бул азыр биз корутунду менен эмес, формулалары бар бир нече уячалар менен алектенип жатабыз дегенди билдирет, башкача айтканда, биз баяндамабыз менен корутундуда жок болгон каалаган трансформацияларды оңой жасай алабыз, мисалы, ортого жаңы саптарды же мамычаларды кыстаруу отчёттун, кыскача ичинде кандайдыр бир кошумча эсептөөлөрдү жүргүзүү, аларды каалагандай иретке келтирүү, ж.б.

Ошол эле учурда булак маалыматтары менен байланыш, албетте, сакталып турат жана келечекте бул формулалар булактар ​​өзгөргөндө жаңыланат. Сулуулук!

• Power Pivot жана Power Query менен пивот таблицада план-фактыларды талдоо
• Көп саптын аталышы бар пивот таблицасы
• Power Pivot аркылуу Excelде маалымат базасын түзүңүз