Cov txiaj ntsig ntawm Pivot los ntawm Cov Qauv Qauv

Thaum tsim ib lub rooj pivot hauv Excel, hauv lub thawv thawj zaug, uas peb raug hais kom teeb tsa thawj zaug thiab xaiv qhov chaw los ntxig lub rooj pivot, muaj qhov tsis pom tseeb tab sis tseem ceeb heev checkbox hauv qab no - Ntxiv cov ntaub ntawv no rau Cov Qauv Qauv (Ntxiv cov ntaub ntawv no rau Data Model) thiab, me ntsis siab dua, qhov hloov Siv phau ntawv no tus qauv cov ntaub ntawv (Siv cov qauv ntaub ntawv ntawm phau ntawv no):

Hmoov tsis zoo, ntau tus neeg siv uas tau paub txog cov lus pivot tau ntev thiab siv tau zoo hauv lawv txoj haujlwm qee zaum tsis nkag siab lub ntsiab lus ntawm cov kev xaiv no thiab tsis txhob siv lawv. Thiab nyob rau hauv vain. Tom qab tag nrho, tsim ib lub rooj pivot rau Cov Qauv Cov Ntaub Ntawv muab peb ntau qhov zoo heev piv rau cov lus Excel pivot classic.

Txawm li cas los xij, ua ntej xav txog cov "buns" ze ze, cia peb nkag siab qhov tseeb, qhov qauv no yog dab tsi?

Dab tsi yog Data Model

Cov Qauv Qauv (ua luv li MD lossis DM = Cov Qauv Qauv) yog thaj chaw tshwj xeeb hauv Excel cov ntaub ntawv uas koj tuaj yeem khaws cov ntaub ntawv tabular - ib lossis ntau lub rooj sib txuas, yog tias xav tau, rau ib leeg. Qhov tseeb, qhov no yog cov ntaub ntawv me me (OLAP lub voos xwmfab) embedded hauv Excel phau ntawv ua haujlwm. Piv nrog rau cov classic cia ntawm cov ntaub ntawv nyob rau hauv daim ntawv ntawm ib txwm (los yog ntse) cov ntxhuav ntawm nplooj ntawv ntawm Excel nws tus kheej, cov ntaub ntawv qauv muaj ntau yam zoo:

• Cov ntxhuav tuaj yeem mus txog 2 billion kab, thiab daim ntawv Excel tuaj yeem haum me ntsis ntau dua 1 lab.
• Txawm hais tias qhov loj me me, kev ua cov rooj zoo li no (filtering, sorting, xam ntawm lawv, cov ntsiab lus hauv tsev, thiab lwm yam) tau ua. ceev heev Ntau nrawm dua Excel nws tus kheej.
• Nrog cov ntaub ntawv hauv Tus Qauv, koj tuaj yeem ua ntxiv (yog tias xav tau, nyuaj heev) suav nrog built-in DAX lus.
  
• Tag nrho cov ntaub ntawv loaded rau hauv Data Model yog heev compressed heev siv qhov tshwj xeeb built-in archiver thiab theej me ntsis nce qhov loj ntawm cov ntaub ntawv Excel qub.
  

Tus Qauv yog tswj hwm thiab suav los ntawm qhov tshwj xeeb ntxiv-hauv ua rau hauv Microsoft Excel - Powerpivottxog qhov kuv tau sau lawm. Txhawm rau qhib nws, ntawm lub tab tsim tawm nias COM ntxiv-ins (Tus tsim tawm - COM Add-ins) thiab kos lub thawv tsim nyog:

Yog tabs tsim tawm (Tus tsim tawm)koj tsis tuaj yeem pom nws ntawm daim kab xev, koj tuaj yeem tig nws los ntawm Cov ntaub ntawv – Kev xaiv – Ribbon teeb (Cov ntaub ntawv - Kev xaiv - Customize Ribbon). Yog hais tias nyob rau hauv lub qhov rais qhia saum toj no nyob rau hauv cov npe ntawm COM add-ins koj tsis muaj Power Pivot, ces nws yuav tsis suav nrog koj version ntawm Microsoft Office 🙁

Ntawm Power Pivot tab uas tshwm, yuav muaj lub teeb ntsuab loj khawm Management (Txuas), nyem rau ntawm qhov uas yuav qhib lub Power Pivot qhov rais nyob rau sab saum toj ntawm Excel, qhov twg peb yuav pom cov ntsiab lus ntawm Cov Qauv Qauv ntawm phau ntawv tam sim no:

Ib qho tseem ceeb nyob rau hauv txoj kev: ib phau ntawv ua haujlwm Excel tsuas tuaj yeem muaj ib Cov Qauv Qauv.

Load tables rau hauv Data Model

Txhawm rau thauj cov ntaub ntawv mus rau hauv Tus Qauv, ua ntej peb tig lub rooj mus rau hauv lub dynamic "smart" keyboard shortcut Ctrl+T thiab muab nws lub npe phooj ywg rau ntawm tab constructor (Tsim). Qhov no yog ib kauj ruam yuav tsum tau.

Tom qab ntawd koj tuaj yeem siv ib qho ntawm peb txoj kev xaiv los ntawm:

• Nias lub pob Ntxiv rau Model (Ntxiv rau Data Model) tab Powerpivot tab Tsev (Tsev).
  
• Xaiv pab pawg Insert – PivotTable (Insert — Pivot Table) thiab qhib lub checkbox Ntxiv cov ntaub ntawv no rau Cov Qauv Qauv (Ntxiv cov ntaub ntawv no rau Data Model). Nyob rau hauv cov ntaub ntawv no, raws li cov ntaub ntawv loaded rau hauv tus qauv, ib tug pivot rooj kuj yog tam sim ntawd ua.
  
• Hauv Advanced tab Cov ntaub ntawv (Hnub) nyem rau ntawm lub pob Los ntawm Table/Range (Los ntawm Table/Range)thauj peb lub rooj rau hauv Power Query editor. Txoj hauv kev no yog qhov ntev tshaj plaws, tab sis, yog tias xav tau, ntawm no koj tuaj yeem ua cov ntaub ntawv ntxiv tu, kho thiab txhua yam kev hloov pauv, uas Power Query muaj zog heev.

  Tom qab ntawd cov ntaub ntawv combed yog uploaded rau Model los ntawm cov lus txib Tsev - Kaw thiab Load - Kaw thiab Load hauv… (Lub Tsev - Kaw & Load - Kaw & Load rau…). Hauv qhov rai uas qhib, xaiv qhov kev xaiv Tsuas yog tsim kev sib txuas (tsuas yog tsim kev sib txuas) thiab, qhov tseem ceeb tshaj, muab zuam Ntxiv cov ntaub ntawv no rau Cov Qauv Qauv (Ntxiv cov ntaub ntawv no rau Data Model).
  

Peb tsim cov ntsiab lus ntawm Cov Qauv Qauv

Txhawm rau tsim kom tau cov ntaub ntawv cov qauv, koj tuaj yeem siv ib qho ntawm peb txoj hauv kev:

• Nias lub khawm cov lus qhia (Pivot Table) hauv Power Pivot qhov rais.
• Xaiv cov lus txib hauv Excel Insert – PivotTable thiab hloov mus rau hom Siv phau ntawv no tus qauv cov ntaub ntawv (Insert — Pivot Table — Siv phau ntawv no cov ntaub ntawv qauv).
• Xaiv pab pawg Insert – PivotTable (Insert — Pivot Table) thiab qhib lub checkbox Ntxiv cov ntaub ntawv no rau Cov Qauv Qauv (Ntxiv cov ntaub ntawv no rau Data Model). Lub rooj "ntse" tam sim no yuav raug thauj mus rau hauv Tus Qauv thiab cov lus xaus yuav raug tsim rau tag nrho Cov Qauv.

Tam sim no peb tau xav txog yuav ua li cas thauj cov ntaub ntawv rau hauv Cov Qauv Qauv thiab tsim cov ntsiab lus ntawm nws, cia peb tshawb xyuas cov txiaj ntsig thiab qhov zoo uas qhov no muab rau peb.

Qhov txiaj ntsig 1: Kev sib raug zoo ntawm cov rooj tsis siv cov qauv

Cov ntsiab lus tsis tu ncua tuaj yeem tsim tau siv cov ntaub ntawv los ntawm ib lub rooj. Yog tias koj muaj ob peb ntawm lawv, piv txwv li, kev muag khoom, daim ntawv teev npe, cov neeg siv khoom, npe ntawm cov ntawv cog lus, thiab lwm yam, ces koj yuav tsum xub sau cov ntaub ntawv los ntawm txhua lub rooj rau hauv ib qho uas siv cov haujlwm xws li VLOOKUP. (VLOOKUP), INDEX (INDEX), XOV XWM ntxiv (MATCH), SUMMESLIMN (SUMIFS) thiab zoo li. Qhov no yog ntev, tedious thiab tsav koj Excel rau hauv "xav" nrog ntau cov ntaub ntawv.

Nyob rau hauv rooj plaub ntawm cov ntsiab lus ntawm Cov Qauv Qauv, txhua yam yog yooj yim dua. Nws yog txaus los teeb tsa kev sib raug zoo ntawm cov ntxhuav ib zaug hauv Power Pivot qhov rais - thiab nws ua tiav. Txhawm rau ua qhov no, ntawm lub tab Powerpivot nias lub pob Management (Txuas) thiab tom qab ntawd hauv lub qhov rai uas tshwm - khawm Daim duab saib (Daim duab saib). Nws tseem yuav luag cov npe (qhov tseem ceeb) kab npe ( teb) ntawm cov rooj los tsim cov kev sib txuas:

Tom qab ntawd, hauv cov ntsiab lus rau Cov Qauv Cov Ntaub Ntawv, koj tuaj yeem pov rau hauv cov ntsiab lus hauv cheeb tsam (kab, kab, lim, qhov tseem ceeb) txhua qhov chaw los ntawm cov rooj sib tham - txhua yam yuav raug txuas thiab suav tau:

Qhov txiaj ntsig 2: suav cov txiaj ntsig tshwj xeeb

Ib lub rooj pivot tsis tu ncua muab sijhawm rau peb xaiv ib qho ntawm ntau qhov kev ua haujlwm suav nrog: suav, nruab nrab, suav, yam tsawg kawg nkaus, siab tshaj plaws, thiab lwm yam. Hauv Cov Qauv Txheej Txheem, muaj txiaj ntsig zoo ntxiv rau daim ntawv teev npe no suav nrog. tus naj npawb ntawm cov cim (tsis rov ua qhov tseem ceeb). Nrog nws cov kev pab, piv txwv li, koj tuaj yeem suav cov naj npawb ntawm cov khoom tshwj xeeb ntawm cov khoom (ntau) uas peb muag hauv txhua lub nroog.

Right-click rau ntawm daim teb - hais kom ua Tus nqi teb xaiv thiab ntawm lub tab lag luam xaiv Tus naj npawb ntawm cov khoom sib txawv (Suav suav):

Qhov txiaj ntsig 3: Custom DAX Formulas

Qee zaum koj yuav tsum ua ntau yam kev suav ntxiv hauv cov lus pivot. Hauv cov ntsiab lus tsis tu ncua, qhov no yog ua tiav los ntawm kev suav teb thiab cov khoom siv, thaum cov ntaub ntawv qauv cov ntsiab lus siv cov kev ntsuas hauv cov lus tshwj xeeb DAX (DAX = Data Analysis Expressions).

Txhawm rau tsim qhov ntsuas, xaiv ntawm lub tab Powerpivot Hais kom Measures – Tsim Ntsuas (Kev ntsuas - Kev ntsuas tshiab) lossis tsuas yog nyem rau ntawm lub rooj hauv Pivot Fields daim ntawv teev npe thiab xaiv Ntxiv ntsuas (Ntxiv qhov ntsuas) nyob rau hauv cov ntsiab lus teb zaub mov:

Hauv qhov rai uas qhib, teeb tsa:

• Lub npeqhov twg qhov tsim ntsuas yuav raug khaws cia.
• Ntsuas lub npe - Txhua lub npe koj nkag siab rau daim teb tshiab.
• Hauj lwm - yeem.
• Formula - qhov tseem ceeb tshaj plaws, vim hais tias ntawm no peb yog manually nkag, lossis nyem rau ntawm lub pob fx thiab xaiv ib qho DAX muaj nuj nqi los ntawm daim ntawv teev npe, uas yuav tsum xam qhov tshwm sim thaum peb mam li muab peb cov kev ntsuas mus rau qhov Values ​​​​ cheeb tsam.
• Nyob rau sab qis ntawm lub qhov rais, koj tuaj yeem teeb tsa tus lej tam sim rau kev ntsuas hauv daim ntawv Qeb.
  

Cov lus DAX tsis yog ib txwm yooj yim to taub vim hais tias ua haujlwm tsis nrog tus kheej qhov tseem ceeb, tab sis nrog tag nrho cov kab thiab cov ntxhuav, piv txwv li yuav tsum muaj qee qhov kev xav rov qab los ntawm cov qauv Excel classic. Txawm li cas los xij, nws tsim nyog, vim tias lub zog ntawm nws lub peev xwm hauv kev ua cov ntaub ntawv loj yog qhov nyuaj rau overestimate.

Qhov txiaj ntsig 4: Kev cai teb hierarchies

Feem ntau, thaum tsim cov ntawv tshaj tawm tus qauv, koj yuav tsum pov tib qhov kev sib txuas ntawm cov teb rau hauv cov lus pivot hauv ib ntus, piv txwv li. Xyoo-Qib-Lub Hlis-Hnub, los yog Qeb-Cov khoom, los yog Lub teb chaws-City-Client thiab lwm yam hauv cov ntaub ntawv qauv cov ntsiab lus, qhov teeb meem no yooj yim daws tau los ntawm kev tsim koj tus kheej hierarchies - custom field sets.

Hauv Power Pivot qhov rais, hloov mus rau daim ntawv hom nrog lub pob Daim duab saib tab Tsev (Lub Tsev - Daim Duab Saib), xaiv nrog Ctrl xav teb thiab right-click rau lawv. Cov ntawv qhia zaub mov ntsiab lus yuav muaj cov lus txib Tsim Hierarchy (Tsim hierarchy):

Cov tsim hierarchy tuaj yeem hloov npe thiab rub mus rau hauv nws nrog tus nas lub teb xav tau, yog li tom qab hauv ib qho kev txav lawv tuaj yeem raug pov rau hauv cov ntsiab lus:

Qhov txiaj ntsig 5: Kev cai stencils

Txuas ntxiv lub tswv yim ntawm kab lus dhau los, hauv cov ntsiab lus ntawm Cov Qauv Qauv, koj tuaj yeem tsim koj tus kheej cov ntsiab lus rau txhua qhov chaw. Piv txwv li, los ntawm tag nrho cov npe ntawm cov nroog, koj tuaj yeem yooj yim ua ib pawg ntawm cov uas nyob hauv koj cheeb tsam ntawm lub luag haujlwm. Los yog sau tsuas yog koj cov neeg siv khoom, koj cov khoom, thiab lwm yam rau hauv ib qho tshwj xeeb.

Txhawm rau ua qhov no, ntawm lub tab Pivot table analysis hauv daim ntawv teev npe Fields, Khoom, thiab Teeb muaj cov lus txib sib xws (Kev tsom xam — Fields, Items & Sets - Tsim teeb raws li kab / kab khoom):

Hauv qhov rai uas qhib, koj tuaj yeem xaiv tshem tawm, ntxiv lossis hloov txoj haujlwm ntawm txhua lub ntsiab lus thiab khaws cov txiaj ntsig tau los ntawm lub npe tshiab:

Tag nrho cov teeb tsa tsim yuav raug tso tawm hauv PivotTable Fields vaj huam sib luag nyob rau hauv ib lub nplaub tshev cais, los ntawm qhov chaw lawv tuaj yeem rub tawm dawb mus rau kab thiab kab ntawm txhua qhov PivotTable tshiab:

Qhov txiaj ntsig 6: Xaiv zais cov lus thiab kab

Txawm hais tias qhov no yog qhov me me, tab sis muaj txiaj ntsig zoo heev hauv qee kis. Los ntawm txoj cai-nias ntawm lub npe teb lossis ntawm lub rooj tab hauv Power Pivot qhov rais, koj tuaj yeem xaiv cov lus txib Nkaum ntawm Client Toolkit (Hide los ntawm Client Tools):

Cov kem zais lossis cov lus yuav ploj ntawm PivotTable Field List pane. Nws yog qhov yooj yim heev yog tias koj yuav tsum tau nkaum ntawm tus neeg siv qee cov kab ke (piv txwv li, suav lossis kab nrog cov txiaj ntsig tseem ceeb rau kev tsim kev sib raug zoo) lossis txawm tias tag nrho cov ntxhuav.

Cov txiaj ntsig 7. Advanced drill-down

Yog tias koj muab ob npaug rau-nias ntawm ib lub xovtooj ntawm tus nqi hauv cheeb tsam pivot cov lus tsis tu ncua, ces Excel qhia rau ntawm ib daim ntawv cais ib daim qauv ntawm cov ntaub ntawv qhov chaw uas tau koom nrog hauv kev suav ntawm lub xovtooj no. Qhov no yog ib qho yooj yim heev, officially hu ua Drill-down (nyob rau hauv lawv feem ntau hais tias "fail").

Nyob rau hauv cov ntaub ntawv qauv cov ntsiab lus, cov cuab yeej siv no ua haujlwm tau zoo dua. Los ntawm kev sawv ntawm txhua lub xov tooj nrog cov txiaj ntsig uas peb nyiam, koj tuaj yeem nyem rau ntawm lub cim nrog lub iav tsom iav uas tshwm rau ntawm ib sab (nws yog hu ua Kev nthuav qhia Trends) thiab tom qab ntawd xaiv cov teb uas koj xav tau hauv ib lub rooj sib tham:

Tom qab ntawd, tus nqi tam sim no (Model = Explorer) yuav mus rau hauv cheeb tsam lim, thiab cov ntsiab lus yuav raug tsim los ntawm chaw ua haujlwm:

Tau kawg, cov txheej txheem zoo li no tuaj yeem rov ua dua ntau zaus, tsis tu ncua delving rau hauv koj cov ntaub ntawv hauv cov kev taw qhia koj xav tau.

Qhov txiaj ntsig 8: Hloov Pivot rau Cube Functions

Yog tias koj xaiv ib lub xov tooj ntawm cov ntsiab lus rau Cov Qauv Cov Ntaub Ntawv thiab tom qab ntawd xaiv ntawm tab Pivot table analysis Hais kom OLAP Cov cuab yeej – Hloov mus rau Formula (Tsim - OLAP Cov Cuab Yeej - Hloov mus rau cov qauv), ces tag nrho cov ntsiab lus yuav raug hloov dua siab tshiab rau cov qauv. Tam sim no cov nuj nqis teb nyob rau hauv kab-kem cheeb tsam thiab cov txiaj ntsig hauv cheeb tsam tus nqi yuav raug muab rov qab los ntawm Cov Qauv Cov Ntaub Ntawv siv cov haujlwm tshwj xeeb lub voos xwmfab: ​​CUBEVALUE thiab CUBEMEMBER:

Technically, qhov no txhais tau hais tias tam sim no peb tsis cuam tshuam nrog cov ntsiab lus, tab sis nrog ntau lub hlwb nrog cov qauv, piv txwv li peb tuaj yeem yooj yim ua txhua yam kev hloov pauv nrog peb daim ntawv tshaj tawm uas tsis muaj nyob rau hauv cov ntsiab lus, piv txwv li, ntxig cov kab tshiab lossis kab rau hauv nruab nrab. ntawm daim ntawv tshaj tawm, ua ib qho kev suav ntxiv hauv cov ntsiab lus, npaj lawv hauv txhua txoj kev xav tau, thiab lwm yam.

Nyob rau tib lub sijhawm, kev sib txuas nrog cov ntaub ntawv qhov chaw, ntawm chav kawm, tseem nyob thiab yav tom ntej cov qauv no yuav raug hloov kho thaum cov peev txheej hloov. Kev zoo nkauj!

• Kev tsom xam qhov tseeb hauv lub rooj pivot nrog Lub Hwj Chim Pivot thiab Lub Zog Query
• Pivot rooj nrog multiline header
• Tsim ib lub database hauv Excel siv Power Pivot