Пераключэнне вылічэнняў у зводнай табліцы са слайсерамі

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

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

Крок 1. Падключэнне надбудовы Power Pivot

Калі ўкладкі надбудовы Power Pivot не бачныя ў вашым Excel, вам спачатку трэба ўключыць яе. Для гэтага ёсць два варыянты:

  • Таб распрацоўшчык - кнопка Надбудовы COM (Распрацоўшчык — надбудовы COM)
  • Файл – Параметры – Надбудовы – Надбудовы COM – Go (Файл — Параметры — Надбудовы — Надбудовы COM — Перайсці да)

Калі гэта не дапамагае, паспрабуйце перазапусціць Microsoft Excel.

Крок 2. Загрузіце даныя ў мадэль даных Power Pivot

У якасці зыходных дадзеных у нас будуць дзве табліцы:

Пераключэнне вылічэнняў у зводнай табліцы са слайсерамі

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

Абедзве гэтыя табліцы неабходна пераўтварыць у «разумныя» (дынамічныя) з дапамогай спалучэння клавіш Ctrl+T або каманда Галоўная - фармат у выглядзе табліцы (Галоўная — фармат у табліцу) і пажадана даваць ім разумныя назвы на табл Канструктар (Дызайн). Няхай, напрыклад, продажаў и Паслугі.

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

Крок 3. Стварыце меру для вызначэння кнопкі, націснутай на зрэзе

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

Пераключэнне вылічэнняў у зводнай табліцы са слайсерамі

Тут назва меры стаіць на першым месцы (Націснуў кнопку), а потым пасля двукроп'я і знака роўнасці формула для вылічэння з дапамогай функцыі Значэнне DAX убудаваны ў Power Pivot.

Калі вы паўтарыце гэта не ў Power Pivot, а ў Power BI, то двукроп'е не спатрэбіцца і замест яго Значэнне вы можаце выкарыстоўваць яго больш сучасны аналаг - функцыю ВЫБРАНАЕ ЗНАЧЭННЕ.

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

Крок 4. Стварыце меру для разліку па націснутай кнопцы

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

Пераключэнне вылічэнняў у зводнай табліцы са слайсерамі

Давайце разбярэм па частках:

  1. функцыя Выключальнікі – аналаг укладзенага IF – правярае выкананне зададзеных умоў і вяртае розныя значэнні ў залежнасці ад выканання некаторых з іх.
  2. функцыя ПРАЎДА() – дае лагічнае «ісціна», так што ўмовы, якія пазней правяраюцца функцыяй SWITCH, працуюць толькі ў тым выпадку, калі яны выкананы, г.зн. ісціна.
  3. Затым мы правяраем значэнне меры націскання кнопкі і разлічваем канчатковы вынік для трох розных варыянтаў - у выглядзе сумы кошту, сярэдняга чэка і колькасці унікальных карыстальнікаў. Для падліку унікальных значэнняў выкарыстоўвайце функцыю DISTINCTCOUNT, а для акруглення – КРУГЛЫ.
  4. Калі ніводная з вышэйпералічаных трох умоў не выканана, то адлюстроўваецца апошні аргумент функцыі SWITCH - мы ўсталёўваем яго як фіктыўны з дапамогай функцыі ПУСТЫ().

Крок 5. Пабудова зводкі і даданне зрэзу

Застаецца вярнуцца з Power Pivot у Excel і пабудаваць там зводную табліцу для ўсіх нашых даных і паказчыкаў. Для гэтага ў акне Power Pivot на Галоўная каманда выбару ўкладкі зводная табліца (Галоўная — зводная табліца).

Тады:

  1. Кідаем поле прадукт З табліцы продажаў да вобласці Радкі (Рады).
  2. Кінуўшы там поле Вынік З табліцы Паслугі.
  3. Пстрыкніце поле правай кнопкай мышы Вынікі выбраць каманду Дадаць як кавалачак (Дадаць як слайсер).
  4. Кіданне другой такты Заключэнне З табліцы Паслугі да вобласці каштоўнасці (Значэнні).

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

Прыгажосць 🙂

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

 

Пакінуць каментар