Перавагі Pivot па мадэлі даных

Пры стварэнні зводнай табліцы ў Excel, у самым першым дыялогавым акне, дзе нас просяць задаць пачатковы дыяпазон і выбраць месца для ўстаўкі зводнай табліцы, ёсць непрыкметны, але вельмі важны сцяжок ніжэй - Дадайце гэтыя даныя ў мадэль даных (Дадайце гэтыя дадзеныя да мадэлі дадзеных) і, крыху вышэй, выключальнік Выкарыстоўвайце мадэль даных гэтай кнігі (Выкарыстоўваць мадэль даных гэтай кнігі):

На жаль, многія карыстальнікі, якія даўно знаёмыя са зводнымі табліцамі і паспяхова выкарыстоўваюць іх у сваёй працы, часам не вельмі разумеюць сэнс гэтых опцый і ніколі імі не карыстаюцца. І дарэмна. У рэшце рэшт, стварэнне зводнай табліцы для мадэлі даных дае нам некалькі вельмі важных пераваг у параўнанні з класічнай зводнай табліцай Excel.

Аднак перш чым разглядаць гэтыя «плюшкі» зблізку, давайце спачатку разбярэмся, што ж уяўляе сабой гэтая мадэль дадзеных?

Што такое мадэль дадзеных

Мадэль дадзеных (скарочана MD або DM = мадэль даных) - гэта спецыяльная вобласць у файле Excel, дзе вы можаце захоўваць таблічныя даныя - адну або некалькі табліц, звязаных, пры жаданні, адна з адной. Фактычна, гэта невялікая база дадзеных (куб OLAP), убудаваная ў кнігу Excel. У параўнанні з класічным захоўваннем дадзеных у выглядзе звычайных (або разумных) табліц на аркушах самога Excel, мадэль дадзеных мае некалькі істотных пераваг:

• Табліцы могуць быць да 2 мільярды радкоў, а аркуш Excel можа змясціць крыху больш за 1 мільён.
• Нягледзячы на ​​гіганцкія памеры, апрацоўка такіх табліц (фільтраванне, сартаванне, разлікі па іх, пабудова зводкі і г.д.) выконваецца вельмі хутка Нашмат хутчэй, чым сам Excel.
• З дадзенымі ў Мадэлі можна выканаць дадатковыя (пры жаданні вельмі складаныя) вылічэнні з дапамогай убудаваная мова DAX.
  
• Уся інфармацыя, загружаная ў мадэль даных, вельмі моцна сціснуты з дапамогай спецыяльнага убудаванага архіватара і даволі умерана павялічвае памер зыходнага файла Excel.
  

Мадэль кіруецца і разлічваецца спецыяльнай надбудовай, убудаванай у Microsoft Excel - PowerPivotпра што я ўжо пісаў. Каб уключыць яго, на ўкладцы распрацоўшчык пстрычка Надбудовы COM (Распрацоўшчык — надбудовы COM) і адзначце адпаведны сцяжок:

Калі ўкладкі распрацоўшчык (Распрацоўшчык)вы не бачыце яго на стужцы, вы можаце ўключыць яго праз Файл – Параметры – Налада стужкі (Файл — Параметры — Наладзіць стужку). Калі ў паказаным вышэй акне ў спісе надбудоў COM у вас няма Power Pivot, значыць, ён не ўключаны ў вашу версію Microsoft Office 🙁

На ўкладцы Power Pivot, якая з'явіцца, будзе вялікая светла-зялёная кнопка кіраванне (Кіраваць), пстрычка па якой адкрые акно Power Pivot у верхняй частцы Excel, дзе мы ўбачым змесціва мадэлі даных бягучай кнігі:

Важная заўвага: рабочая кніга Excel можа ўтрымліваць толькі адну мадэль даных.

Загрузіце табліцы ў мадэль даных

Каб загрузіць дадзеныя ў Мадэль, спачатку мы ператворым табліцу ў дынамічнае «разумнае» спалучэнне клавіш Ctrl+T і дайце яму зручную назву на ўкладцы Канструктар (Дызайн). Гэта абавязковы крок.

Затым вы можаце выкарыстоўваць любы з трох спосабаў на выбар:

• Націсніце кнопку Дадаць у мадэль (Дадаць у мадэль даных) таб PowerPivot таб Галоўная (Дадому).
  
• Выбар каманд Устаўка – зводная табліца (Устаўка — зводная табліца) і ўключыце сцяжок Дадайце гэтыя даныя ў мадэль даных (Дадайце гэтыя даныя ў мадэль даных). У гэтым выпадку па дадзеных, загружаным у Мадэль, таксама адразу будуецца зводная табліца.
  
• На ўкладцы Дадаткова Дата (Дата) націсніце на кнопку З табліцы/дыяпазону (З табліцы/дыяпазону)каб загрузіць нашу табліцу ў рэдактар ​​Power Query. Гэты шлях самы доўгі, але, пры жаданні, тут можна вырабіць дадатковую ачыстку дадзеных, рэдагаванне і разнастайныя пераўтварэнні, у якіх Power Query вельмі моцны.

  Затым апрацаваныя даныя загружаюцца ў Мадэль камандай Галоўная — Зачыніць і загрузіць — Зачыніць і загрузіць у… (Галоўная — Зачыніць&Загрузіць — Зачыніць&Загрузіць у…). У якое адкрылася акне абярыце опцыю Проста стварыце злучэнне (Толькі стварыць злучэнне) і, галоўнае, пастаўце галачку Дадайце гэтыя даныя ў мадэль даных (Дадайце гэтыя даныя ў мадэль даных).
  

Мы ствараем зводку мадэлі даных

Каб пабудаваць зводную мадэль даных, вы можаце выкарыстоўваць любы з трох падыходаў:

• націсніце кнопку зводная табліца (Зводная табліца) у акне Power Pivot.
• Выберыце каманды ў Excel Устаўка – зводная табліца і перайсці ў рэжым Выкарыстоўвайце мадэль даных гэтай кнігі (Уставіць — Зводная табліца — Выкарыстоўваць мадэль даных гэтай кнігі).
• Выбар каманд Устаўка – зводная табліца (Устаўка — зводная табліца) і ўключыце сцяжок Дадайце гэтыя даныя ў мадэль даных (Дадайце гэтыя даныя ў мадэль даных). Бягучая «разумная» табліца будзе загружана ў мадэль, а зводная табліца будзе пабудавана для ўсёй мадэлі.

Цяпер, калі мы высветлілі, як загрузіць дадзеныя ў мадэль даных і пабудаваць на іх аснове зводку, давайце вывучым выгады і перавагі, якія гэта дае нам.

Перавага 1: адносіны паміж табліцамі без выкарыстання формул

Звычайная зводка можа быць пабудавана толькі з выкарыстаннем дадзеных з адной зыходнай табліцы. Калі ў вас іх некалькі, напрыклад, распродажы, прайс-ліст, каталог кліентаў, рэестр дагавораў і г.д., то вам спачатку трэба сабраць дадзеныя з усіх табліц у адну з дапамогай такіх функцый, як VLOOKUP. (ВПР), ІНДЭКС (ІНДЭКС), БОЛЬШ ВЫКЛЮЧАНЫЯ (МАТЧ), САММЕСЛІМН (СУМЫ) і таму падобнае. Гэта доўга, стомна і прымушае ваш Excel «задумацца» з вялікай колькасцю дадзеных.

У выпадку са зводкай мадэлі дадзеных усё значна прасцей. Дастаткова адзін раз наладзіць адносіны паміж табліцамі ў акне Power Pivot – і справа зроблена. Для гэтага на таб PowerPivot націсніце кнопку кіраванне (Кіраваць) а затым у якое з'явілася акне – кнопка Выгляд дыяграмы (Прагляд схемы). Засталося перацягнуць агульныя (ключавыя) назвы слупкоў (палёў) паміж табліцамі для стварэння спасылак:

Пасля гэтага ў зводцы для мадэлі даных вы можаце кідаць у вобласць зводкі (радкі, слупкі, фільтры, значэнні) любыя палі з любых звязаных табліц – усё будзе звязана і разлічана аўтаматычна:

Перавага 2: падлік унікальных значэнняў

Звычайная зводная табліца дае нам магчымасць выбраць адну з некалькіх убудаваных функцый вылічэння: сума, сярэдняе, падлік, мінімум, максімум і г. д. У зводцы мадэлі даных у гэты стандартны спіс дадаецца вельмі карысная функцыя для падліку колькасць унікальных (непаўтаральных значэнняў). З яго дапамогай, напрыклад, можна лёгка падлічыць колькасць унікальных адзінак тавараў (асартымент), якія мы прадаем у кожным горадзе.

Пстрыкніце поле правай кнопкай мышы – каманда Параметры палёў значэнняў і на ўкладцы аперацыя Выбіраць Колькасць розных элементаў (Розны лік):

Перавага 3: Карыстальніцкія формулы DAX

Часам даводзіцца выконваць розныя дадатковыя вылічэнні ў зводных табліцах. У звычайных зводках гэта робіцца з дапамогай вылічаных палёў і аб'ектаў, у той час як зводка мадэлі даных выкарыстоўвае меры на спецыяльнай мове DAX (DAX = Data Analysis Expressions).

Каб стварыць меру, абярыце на ўкладцы PowerPivot Каманда Меры - Стварыце меру (Меры — Новая мера) або проста пстрыкніце правай кнопкай мышы на табліцы ў спісе Pivot Fields і абярыце Дадайце меры (Дадаць меру) у кантэкстным меню:

У якое адкрылася акне задайце:

• Назва табліцыдзе будзе захоўвацца створаная мера.
• Назва меры – любое зразумелае вам імя для новага поля.
• Апісанне – неабавязковы.
• Формула – самае галоўнае, бо тут мы альбо ўручную ўводзім, альбо націскаем на кнопку fx і выберыце са спісу функцыю DAX, якая павінна вылічыць вынік, калі мы затым перакінем нашу меру ў вобласць значэнняў.
• У ніжняй частцы акна вы можаце адразу задаць фармат ліку для меры ў спісе катэгорыя.
  

Мова DAX не заўсёды простая для разумення, таму што працуе не з асобнымі значэннямі, а з цэлымі слупкамі і табліцамі, гэта значыць патрабуе некаторай перабудовы мыслення пасля класічных формул Excel. Аднак яно таго варта, бо магутнасць яго магчымасцяў па апрацоўцы вялікіх аб'ёмаў дадзеных цяжка пераацаніць.

Перавага 4: Карыстальніцкія іерархіі палёў

Часта пры стварэнні стандартных справаздач даводзіцца кідаць аднолькавыя камбінацыі палёў у зводныя табліцы ў зададзенай паслядоўнасці, напрыклад Год-Чвэрць-Месяц-Дзеньабо Катэгорыя-Прадуктабо Краіна-Горад-Кліент і г. д. У рэзюмэ мадэлі дадзеных гэтая праблема лёгка вырашаецца шляхам стварэння ўласнай іерархіі — карыстальніцкія наборы палёў.

У акне Power Pivot пераключыцеся ў рэжым дыяграмы кнопкай Выгляд дыяграмы таб Галоўная (Галоўная — Дыяграма), выберыце з дапамогай Ctrl патрэбныя палі і пстрыкніце іх правай кнопкай мышы. Кантэкстнае меню будзе ўтрымліваць каманду Стварыце іерархію (Стварыць іерархію):

Створаную іерархію можна перайменаваць і перацягнуць у яе мышкай неабходныя палі, каб потым адным рухам перакінуць іх у зводку:

Перавага 5: Індывідуальныя трафарэты

Працягваючы ідэю папярэдняга параграфа, у рэзюмэ мадэлі даных вы таксама можаце ствараць уласныя наборы элементаў для кожнага поля. Напрыклад, з усяго спісу гарадоў вы лёгка можаце скласці набор толькі тых, якія знаходзяцца ў зоне вашай адказнасці. Або збярыце ў спецыяльны набор толькі сваіх кліентаў, свае тавары і г.д.

Для гэтага на таб Аналіз зводнай табліцы у выпадальным спісе Палі, прадметы і наборы ёсць адпаведныя каманды (Аналіз - Філдс, Ітэмы і наборы — стварыць набор на аснове элементаў радка/слупка):

У якое адкрылася акне можна выбарачна выдаліць, дадаць або змяніць становішча любых элементаў і захаваць атрыманы набор пад новым імем:

Усе створаныя наборы будуць адлюстроўвацца на панэлі палёў зводнай табліцы ў асобнай тэчцы, адкуль іх можна свабодна перацягваць у вобласці радкоў і слупкоў любой новай зводнай табліцы:

Перавага 6: Выбарачна хаваць табліцы і слупкі

Хоць гэта і невялікае, але ў некаторых выпадках вельмі прыемнае перавага. Пстрыкнуўшы правай кнопкай мышы імя поля або ўкладку табліцы ў акне Power Pivot, вы можаце выбраць каманду Схаваць ад Client Toolkit (Схаваць ад кліенцкіх інструментаў):

Схаваны слупок або табліца знікне з панэлі спісу палёў зводнай табліцы. Гэта вельмі зручна, калі вам трэба схаваць ад карыстальніка некаторыя дапаможныя слупкі (напрыклад, вылічальныя або слупкі з ключавымі значэннямі для стварэння адносін) або нават цэлыя табліцы.

Перавага 7. Пашыраная дэталізацыя

Калі двойчы пстрыкнуць па любой вочку ў вобласці значэнняў у звычайнай зводнай табліцы, то Excel адлюстроўвае на асобным аркушы копію фрагмента зыходных дадзеных, які ўдзельнічаў у разліку гэтай ячэйкі. Гэта вельмі зручная рэч, якая афіцыйна называецца Drill-down (у нас звычайна кажуць «не атрымалася»).

У рэзюмэ мадэлі даных гэты зручны інструмент працуе больш тонка. Стаўшы на любую вочка з цікавіць нас вынікам, можна націснуць на якая ўсплывае побач абразок з павелічальным шклом (яна называецца Экспрэс-тэндэнцыі), а затым выберыце любое поле, якое вас цікавіць, у любой звязанай табліцы:

Пасля гэтага бягучае значэнне (Model = Explorer) пяройдзе ў вобласць фільтра, а зводка будзе пабудавана па офісах:

Зразумела, такую ​​працэдуру можна паўтараць шмат разоў, паслядоўна ўнікаючы ў свае дадзеныя ў цікавым для вас кірунку.

Перавага 8: Пераўтварэнне зводных функцый у кубічныя

Калі вы вылучыце любую ячэйку ў зводцы для мадэлі даных, а затым выберыце на ўкладцы Аналіз зводнай табліцы Каманда Інструменты OLAP – Пераўтварэнне ў формулы (Аналіз — Інструменты OLAP — Пераўтварэнне ў формулы), тады ўся зводка будзе аўтаматычна ператворана ў формулы. Цяпер значэнні палёў у вобласці радкоў-слупкоў і вынікі ў вобласці значэнняў будуць атрыманы з мадэлі даных з дапамогай спецыяльных функцый куба: CUBEVALUE і CUBEMEMBER:

Тэхнічна гэта азначае, што зараз мы маем справу не з зводкай, а з некалькімі вочкамі з формуламі, гэта значыць мы можам лёгка рабіць з нашым справаздачай любыя пераўтварэнні, якіх няма ў зводцы, напрыклад, устаўляць у сярэдзіну новыя радкі або слупкі. справаздачы, рабіць якія-небудзь дадатковыя разлікі ўнутры рэзюмэ, размяшчаць іх у любым выглядзе і г.д.

Пры гэтым сувязь з зыходнымі дадзенымі, вядома, захоўваецца і ў далейшым гэтыя формулы будуць абнаўляцца пры змене крыніц. Прыгажосць!

• Аналіз плана і фактаў у зводнай табліцы з дапамогай Power Pivot і Power Query
• Зводная табліца з шматрадковым загалоўкам
• Стварыце базу дадзеных у Excel з дапамогай Power Pivot