Kuchinja kuverenga muPivotTable ine zvicherwa

Slicers mumatafura epivot anogona kushandiswa kwete chete nenzira yechinyakare - kusefa iyo sosi data, asi zvakare kushandura pakati pemhando dzakasiyana dzekuverenga munzvimbo yakakosha:

Kuita izvi kuri nyore kwazvo - zvese zvaunoda ndeye akati wandei mafomula uye tafura yekubatsira. Zvakanaka, tichaita zvese izvi kwete muchidimbu chenguva dzose, asi muchidimbu chakavakwa maererano nePower Pivot Data Model.

Danho 1. Kubatanidza Power Pivot add-in

Kana iwo ma tabo eiyo Power Pivot yekuwedzera-mukati isingaonekwe muExcel yako, iwe unozofanirwa kutanga waigonesa. Pane nzira mbiri dzeizvi:

• Tab yokuvaka - bhatani COM yekuwedzera-in (Mugadziri - COM Yekuwedzera)
• Faira - Sarudzo - Wedzera-ins - COM Yekuwedzera - Enda (Faira - Sarudzo - Wedzera-ins - COM-Wedzero - Enda ku)

Kana izvi zvisingabatsire, edza kutangazve Microsoft Excel.

Nhanho yechipiri: Rodha Dhata muPower Pivot Data Model

Tichava nematafura maviri se data rekutanga:

Yokutanga itafura ine kutengesa, maererano nezvatichazovaka muchidimbu. Yechipiri itafura yekubatsira, apo mazita emabhatani emucheka weramangwana anopinda.

Ose ari maviri matafura aya anoda kushandurwa kuita "smart" (dynamic) ane keyboard pfupi Ctrl+T kana timu Kumba – Fomati setafura (Kumba - Fomati seTafura) uye zvinodikanwa kuvapa mazita ane njere patebhu Constructor (Dhizaini). Ngazvive, semuenzaniso, Sales и Services.

Mushure maizvozvi, tafura imwe neimwe inoda kuiswa muData Model - nokuda kweizvi tinoshandisa pane tab powerpivot bhatani Wedzera kune Data Model (Wedzera kune Data Model).

Nhanho 3. Gadzira chiyero kuti uone bhatani rakadzvanywa pachimedu

Iyo minda yakaverengerwa muPivotTable neData Model inonzi matanho. Ngatigadzire chiyero chinozoratidza zita rebhatani rakadzvanywa pane remangwana chidimbu. Kuti uite izvi, mune chero tafura yedu, sarudza chero sero isina chinhu mune yakaderera calculation pani uye pinda chinotevera chivakwa mubhari yeformula:

Pano, zita rechiyero rinouya kutanga (Akadzvanya bhatani), uyezve mushure mekoloni uye chiratidzo chakaenzana, fomula yekuiverenga uchishandisa basa ZVINHU DAX yakavakwa muSimba Pivot.

Kana iwe ukadzokorora izvi kwete muSimba Pivot, asi muSimba BI, saka colon haidiwe uye panzvimbo ZVINHU iwe unogona kushandisa imwe yemazuva ano mumwe wake - basa SELECTEDVALUE.

Isu haticherechedze zvikanganiso muchikamu chezasi chehwindo chinooneka mushure mekupinda muchimiro - ivo vanosimuka, nekuti isu hatisati tine pfupiso uye chidimbu mune icho chinhu chakadzvanywa.

Danho 4. Gadzira chiyero chekuverenga pane bhatani rakadzvanywa

Nhanho inotevera ndeyekugadzira chiyero chesarudzo dzakasiyana dzekuverenga zvichienderana nekukosha kwechiyero chakapfuura Akadzvanya bhatani. Heino fomula yacho yakanyanya kuoma:

Ngatizvitsemure chidimbu nechidimbu:

1. basa chinja - analogue ye nested IF - inotarisa kuzadzikiswa kwemamiriro akatsanangurwa uye inodzosera akasiyana siyana zvichienderana nekuzadzikiswa kwemamwe acho.
2. basa CHOKWADI() - inopa zvine musoro "chokwadi" kuitira kuti mamiriro akatariswa gare gare neiyo SWITCH basa anoshanda chete kana asangana, kureva chokwadi.
3. Zvadaro tinotarisa kukosha kwebhatani rakadzvanywa chiyero uye toverenga mhedzisiro yesarudzo nhatu dzakasiyana - sehuwandu hwemutengo, cheki yeavhareji uye nhamba yevashandisi vakasiyana. Kuti uverenge maitiro akasiyana, shandisa basa DISTINCTCOUNT, uye yekutenderera - ROUND.
4. Kana pasina chimwe chezvitatu zviri pamusoro chasangana, ipapo nharo yekupedzisira ye SWITCH basa inoratidzwa - tinoimisa se dummy tichishandisa basa. SHAKA().

Danho 5. Kuvaka muchidimbu uye kuwedzera chidimbu

Izvo zvinosara kudzoka kubva kuPower Pivot kuenda kuExcel uye kuvaka tafura yepivot ipapo kune yedu yese data uye zviyero. Kuti uite izvi, pane iyo Power Pivot hwindo huru tab sarudza command pfupiso tafura (Kumba - Pivot Tafura).

Zvadaro:

1. Tinokanda munda chigadzirwa kubva patafura Sales kunzvimbo Rows (Row).
2. Kukanda munda uko mugumisiro kubva patafura Services.
3. Kurudyi tinya pandima mugumisirouye sarudza timu Wedzera sechimedu (Wedzera seSlicer).
4. Kukanda chiyero chechipiri Kupedzisa kubva patafura Services kunzvimbo Values (Values).

Pano, chaizvoizvo, ndiwo maitiro ose. Iye zvino unogona kudzvanya pamabhatani ekucheka - uye iwo mahota ari patafura yepivot anochinja kune basa raunoda.

Runako 🙂

• Mabhenefiti ePivot neData Model
• Ronga-chokwadi ongororo mutafura yepivot paPower Pivot
• Gadzira dhatabhesi muExcel uchishandisa iyo Power Pivot yekuwedzera-mukati