Txoj kev 1. Cov qauv
Cia peb pib, ua kom sov, nrog rau qhov kev xaiv yooj yim tshaj plaws - cov qauv. Yog tias peb muaj ib lub rooj me me tau txheeb xyuas los ntawm hnub raws li cov tswv yim, tom qab ntawd txhawm rau suav cov kev khiav tag nrho hauv ib kem cais, peb xav tau cov qauv hauv qab:
Lub ntsiab feature ntawm no yog kev kho kom yooj yim ntawm ntau yam hauv SUM muaj nuj nqi - kev siv rau qhov pib ntawm qhov ntau yog ua tiav (nrog cov cim nyiaj daus las), thiab mus rau qhov kawg - txheeb ze (tsis muaj nyiaj). Raws li, thaum luam cov qauv mus rau tag nrho kem, peb tau txais ib qho kev nthuav dav, cov lej uas peb suav.
Qhov tsis zoo ntawm txoj kev no yog pom tseeb:
- Lub rooj yuav tsum tau txheeb raws hnub tim.
- Thaum ntxiv cov kab tshiab nrog cov ntaub ntawv, cov qauv yuav tsum tau txuas ntxiv manually.
Txoj kev 2. Pivot rooj
Txoj kev no yog me ntsis nyuaj, tab sis ntau qab ntxiag. Thiab kom exacerbate, cia peb xav txog qhov teeb meem loj dua - lub rooj ntawm 2000 kab ntawm cov ntaub ntawv, qhov twg tsis muaj kev txheeb xyuas los ntawm kab hnub, tab sis muaj kev rov ua dua (piv txwv li peb tuaj yeem muag ob peb zaug hauv tib hnub):
Peb hloov peb lub rooj qub rau hauv "ntse" (dynamic) keyboard shortcut Ctrl+T los yog pab neeg Tsev – Format ua ib lub rooj (Lub Tsev — Format as Table), thiab tom qab ntawd peb tsim lub rooj pivot ntawm nws nrog cov lus txib Insert – PivotTable (Insert — Pivot Table). Peb muab hnub tim rau hauv kab hauv cheeb tsam hauv cov ntsiab lus, thiab tus naj npawb ntawm cov khoom muag hauv qhov muaj nqis:
Thov nco ntsoov tias yog tias koj tsis muaj qhov qub version ntawm Excel, ces cov hnub tau txiav ua pawg los ntawm xyoo, quarters thiab hli. Yog tias koj xav tau ib pab pawg sib txawv (lossis tsis xav tau tag nrho), ces koj tuaj yeem kho nws los ntawm txoj cai-nias ntawm txhua hnub thiab xaiv cov lus txib Pab pawg / Ungroup (Pab pawg / Ungroup).
Yog tias koj xav pom ob qho tag nrho cov txiaj ntsig los ntawm lub sijhawm thiab kev khiav tag nrho hauv ib kem cais, ces nws ua rau kev txiav txim siab muab pov rau hauv thaj chaw tus nqi. Muag ib zaug ntxiv kom tau txais qhov sib npaug ntawm daim teb - hauv nws peb yuav qhib cov zaub ntawm kev khiav tag nrho. Ua li no, right-click rau ntawm daim teb thiab xaiv cov lus txib Kev suav ntxiv – Sau tag nrho (Show values as — Running Totals):
Nyob ntawd koj tuaj yeem xaiv qhov kev xaiv ntawm kev loj hlob tag nrho raws li qhov feem pua, thiab nyob rau hauv lub qhov rais tom ntej koj yuav tsum xaiv thaj chaw uas cov tsub zuj zuj mus - nyob rau hauv peb rooj plaub, qhov no yog hnub tim teb:
Qhov zoo ntawm txoj kev no:
- Ib tug loj npaum li cas ntawm cov ntaub ntawv yog sai nyeem.
- Tsis muaj cov qauv yuav tsum tau nkag mus manually.
- Thaum hloov pauv hauv cov ntaub ntawv qhov chaw, nws txaus los hloov kho cov ntsiab lus nrog txoj cai nas khawm lossis nrog cov ntaub ntawv - Refresh Tag nrho cov lus txib.
Qhov tsis zoo ua raws li qhov tseeb tias qhov no yog cov ntsiab lus, uas txhais tau hais tias koj tsis tuaj yeem ua txhua yam koj xav tau hauv nws (ntxig kab, sau cov qauv, tsim cov duab kos, thiab lwm yam) yuav tsis ua haujlwm.
Txoj Kev 3: Lub Hwj Chim Query
Cia peb thauj peb lub rooj "ntse" nrog cov ntaub ntawv hauv lub zog Query query editor siv cov lus txib Cov ntaub ntawv - Los ntawm Rooj / Ntau (Cov ntaub ntawv - Los ntawm Table / Range). Nyob rau hauv qhov tseeb versions ntawm Excel, los ntawm txoj kev, nws tau renamed - tam sim no nws yog hu ua Nrog nplooj (Los ntawm Sheet):
Tom qab ntawd peb yuav ua cov kauj ruam hauv qab no:
1. Soj lub rooj nyob rau hauv ascending kev txiav txim los ntawm kab hnub nrog cov lus txib Xaiv ascending nyob rau hauv lub lim drop-down daim ntawv teev nyob rau hauv lub rooj header.
2. Ib me ntsis tom qab, txhawm rau xam tag nrho cov kev khiav, peb xav tau ib pawg pab nrog tus lej kab ke. Cia peb ntxiv nws nrog cov lus txib Ntxiv Kem – Index Kem – Los ntawm 1 (Ntxiv kab - Index kem - Los ntawm 1).
3. Tsis tas li ntawd, txhawm rau xam qhov khiav tag nrho, peb xav tau ib qho kev siv rau kab Muag, qhov twg peb cov ntaub ntawv summarized lies. Hauv Power Query, txhua kab kuj tseem hu ua cov npe (cov npe) thiab kom tau txais qhov txuas rau nws, nyem rau ntawm kab ntawv kab ntawv thiab xaiv cov lus txib Nthuav dav (Show nthuav dav). Cov lus qhia peb xav tau yuav tshwm sim hauv cov qauv bar, suav nrog lub npe ntawm cov kauj ruam dhau los #"Index ntxiv", los ntawm qhov uas peb coj lub rooj thiab lub npe kab [Muag] los ntawm lub rooj no nyob rau hauv square brackets:
Luam cov lus qhia no rau hauv daim ntawv teev lus rau kev siv ntxiv.
4. Tshem tawm cov kauj ruam kawg ntxiv uas tsis tsim nyog Muag thiab ntxiv es tsis txhob suav ib kab rau xam cov khiav tag nrho nrog cov lus txib Ntxiv ib Kem – Custom Column (Ntxiv kab - Custom column). Cov mis peb xav tau yuav zoo li no:
Ntawm no muaj nuj nqi List.Range coj cov npe qub (kem [Kev muag khoom]) thiab rho tawm cov ntsiab lus los ntawm nws, pib los ntawm thawj (hauv cov qauv, qhov no yog 0, txij li tus lej hauv Power Query pib los ntawm xoom). Tus naj npawb ntawm cov khoom kom rov qab tau yog tus lej kab uas peb coj los ntawm kab [Index]. Yog li no muaj nuj nqi rau thawj kab tsuas yog rov qab ib lub xov tooj ntawm kab ntawv thawj zaug Muag. Rau kab thib ob - twb thawj ob lub hlwb, rau peb - thawj peb, thiab lwm yam.
Zoo, ces lub function List.Sum sums tus nqi rho tawm thiab peb tau txais nyob rau hauv txhua kab cov sum ntawm tag nrho cov yav dhau los hais, piv txwv li cumulative tag nrho:
Nws tseem yuav rho tawm Index kem uas peb tsis xav tau thiab xa cov txiaj ntsig rov qab mus rau Excel nrog Lub Tsev - Kaw & Load rau cov lus txib.
Qhov teeb meem yog daws.
Ceev thiab Furious
Hauv txoj cai, qhov no tuaj yeem raug tso tseg, tab sis muaj yoov me me hauv cov tshuaj pleev - qhov kev thov peb tsim ua haujlwm ntawm qhov nrawm ntawm tus vaub kib. Piv txwv li, ntawm kuv lub PC uas tsis muaj zog tshaj plaws, lub rooj tsuas yog 2000 kab ua tiav hauv 17 vib nas this. Yuav ua li cas yog tias muaj ntaub ntawv ntxiv?
Txhawm rau kom nrawm, koj tuaj yeem siv buffering siv qhov tshwj xeeb List.Buffer muaj nuj nqi, uas thauj cov npe (daim ntawv teev npe) muab rau nws raws li kev sib cav rau hauv RAM, uas zoo heev rau kev nkag mus rau nws yav tom ntej. Hauv peb qhov xwm txheej, nws ua rau muaj kev nkag siab tsis zoo rau #"Ntxiv qhov Performance index" [Sold] daim ntawv teev npe, uas Lub Hwj Chim Query yuav tsum tau nkag mus thaum xam tag nrho cov khiav hauv txhua kab ntawm peb 2000-kab lus.
Txhawm rau ua qhov no, hauv Power Query editor ntawm Main tab, nyem lub pob Advanced Editor (Lub Tsev - Advanced Editor) txhawm rau qhib qhov chaws ntawm peb cov lus nug hauv M hom lus ua rau Power Query:
Thiab tom qab ntawd ntxiv ib kab nrog qhov sib txawv ntawm qhov ntawd MyList, tus nqi uas yog xa rov qab los ntawm kev ua haujlwm buffering, thiab ntawm cov kauj ruam tom ntej peb hloov kev hu mus rau cov npe nrog qhov hloov pauv no:
Tom qab ua cov kev hloov pauv no, peb cov lus nug yuav nrawm dua thiab yuav tiv nrog 2000 kab lus hauv 0.3 vib nas this!
Lwm yam, txoj cai? 🙂
- Pareto daim ntawv (80/20) thiab yuav ua li cas los tsim nws hauv Excel
- Tshawb nrhiav lo lus tseem ceeb hauv cov ntawv nyeem thiab cov lus nug buffering hauv Power Query