Nrhiav tus lej ze tshaj plaws

txheem

Hauv kev xyaum, feem ntau muaj cov xwm txheej thaum koj thiab kuv yuav tsum nrhiav tus nqi ze tshaj plaws hauv ib lub teeb (table) nyob rau hauv kev sib raug zoo rau tus lej muab. Nws tuaj yeem yog, piv txwv li:

Kev suav cov luv nqi nyob ntawm qhov ntim.
Kev suav cov nyiaj tau txais txiaj ntsig nyob ntawm qhov kev ua tiav ntawm txoj kev npaj.
Kev xam cov nqi shipping nyob ntawm qhov kev ncua deb.
Kev xaiv cov thawv tsim nyog rau cov khoom, thiab lwm yam.

Ntxiv mus, kev sib tw yuav tsum tau nce thiab nqis, nyob ntawm qhov xwm txheej.

Muaj ntau txoj hauv kev - pom tseeb thiab tsis pom tseeb - los daws qhov teeb meem no. Cia peb saib lawv ua ntu zus.

Yuav pib nrog, cia peb xav txog tus neeg muag khoom uas muab cov luv nqi ntawm cov lag luam wholesale, thiab feem pua ntawm cov luv nqi nyob ntawm seb qhov ntau ntawm cov khoom yuav. Piv txwv li, thaum yuav ntau tshaj 5 daim, 2% luv nqi yog muab, thiab thaum yuav los ntawm 20 daim - twb 6%, thiab lwm yam.

Yuav ua li cas sai thiab zoo nkauj xam qhov feem pua ntawm cov luv nqi thaum nkag mus rau qhov ntau ntawm cov khoom yuav?

Txoj Kev 1: Nested IFs

Ib txoj hauv kev los ntawm koob "Dab tsi yog qhov xav tau - koj yuav tsum dhia!". Siv cov haujlwm nested IF (YOG) txhawm rau txheeb xyuas seb tus nqi ntawm tes poob rau hauv txhua lub sijhawm thiab tso saib cov luv nqi rau qhov sib thooj. Tab sis cov qauv hauv qhov no tuaj yeem dhau los ua cumbersome heev:

Nrhiav tus lej ze tshaj plaws

Kuv xav tias nws yog qhov tseeb tias debugging xws li "dab dab ntxwg nyoog" los yog sim ntxiv ob peb yam tshiab rau nws tom qab qee lub sijhawm lom zem.

Ntxiv rau, Microsoft Excel muaj qhov txwv ze rau IF muaj nuj nqi - 7 zaug hauv cov ntawv qub thiab 64 zaug hauv cov ntawv tshiab. Yuav ua li cas yog tias koj xav tau ntxiv?

Txoj Kev 2. VLOOKUP nrog lub sijhawm saib

Txoj kev no yog ntau compact. Txhawm rau xam qhov feem pua ntawm cov luv nqi, siv cov lus dab neeg ua haujlwm VPR (VLOOKUP) nyob rau hauv kwv yees nrhiav hom:

Nrhiav tus lej ze tshaj plaws

qhov twg

B4 - tus nqi ntawm qhov kom muaj nuj nqis ntawm cov khoom hauv thawj qhov kev lag luam uas peb tab tom nrhiav rau luv nqi
$G $4: $H $8 - qhov txuas mus rau lub rooj luv nqi - tsis muaj "header" thiab nrog cov chaw nyob ruaj nrog $ kos npe.
2 - tus naj npawb ntawm kab ke hauv cov lus luv nqi uas peb xav tau tus nqi luv nqi
TRUE - qhov no yog qhov "dub" faus. Yog hais tias raws li qhov kawg function sib cav VPR qhia meej dag (FALSE) los yog 0, ces qhov muaj nuj nqi yuav nrhiav nruj kev sib tw nyob rau hauv kab ntau ntau (thiab nyob rau hauv peb cov ntaub ntawv nws yuav muab ib tug #N / A yuam kev, vim tsis muaj nqi 49 nyob rau hauv lub luv nqi rooj). Tab sis yog tias hloov dag sau ntawv TRUE (TSIS YOG) los yog 1, ces qhov muaj nuj nqi yuav saib tsis yog rau qhov tseeb, tab sis ze tshaj qhov tsawg tshaj plaws tus nqi thiab yuav muab qhov feem pua ntawm cov luv nqi peb xav tau.

Lub downside ntawm txoj kev no yog qhov yuav tsum tau txheeb xyuas cov lus luv nqi hauv kev txiav txim siab los ntawm thawj kab. Yog tias tsis muaj qhov kev txheeb xyuas zoo li no (lossis nws ua tiav hauv qhov kev txiav txim rov qab), ces peb cov qauv yuav tsis ua haujlwm:

Nrhiav tus lej ze tshaj plaws

Raws li qhov no, txoj hauv kev no tsuas yog siv los nrhiav qhov ze tshaj plaws tus nqi tsawg tshaj plaws. Yog tias koj xav nrhiav qhov ze tshaj plaws, ces koj yuav tsum siv txoj hauv kev sib txawv.

Txoj Kev 3. Nrhiav qhov ze tshaj plaws uas siv INDEX thiab MATCH ua haujlwm

Tam sim no cia saib peb qhov teeb meem los ntawm lwm sab. Piv txwv li, peb muag ntau tus qauv ntawm cov twj tso kua mis muaj peev xwm. Lub rooj muag khoom ntawm sab laug qhia lub hwj chim xav tau los ntawm cov neeg siv khoom. Peb yuav tsum xaiv lub twj tso kua mis ntawm qhov ze tshaj plaws los yog sib npaug zog, tab sis tsis tsawg tshaj li qhov yuav tsum tau ua los ntawm qhov project.

VLOOKUP muaj nuj nqi yuav tsis pab ntawm no, yog li koj yuav tau siv nws cov analogue - ib pawg ntawm INDEX functions (INDEX) thiab EXPOSED ntau dua (MATCH):

Nrhiav tus lej ze tshaj plaws

Ntawm no, MATCH muaj nuj nqi nrog cov lus sib cav kawg -1 ua haujlwm nyob rau hauv hom kev nrhiav qhov ze tshaj plaws tus nqi, thiab INDEX muaj nuj nqi ces rho tawm cov qauv npe peb xav tau los ntawm kab uas nyob ib sab.

Txoj Kev 4. Tshiab muaj nuj nqi VIEW (XLOOKUP)

Yog hais tias koj muaj ib tug version ntawm Office 365 nrog tag nrho cov tshiab ntsia, ces es tsis txhob ntawm VLOOKUP (VLOOKUP) koj tuaj yeem siv nws cov analogue - VIEW muaj nuj nqi (XLOOKUP), uas kuv tau txheeb xyuas meej meej:

Nrhiav tus lej ze tshaj plaws

Ntawm no:

B4 - thawj tus nqi ntawm qhov ntau ntawm cov khoom uas peb tab tom nrhiav rau luv nqi
$G $4: $G $8 - qhov chaw uas peb tab tom nrhiav kev sib tw
$4: $8 - qhov ntau ntawm cov txiaj ntsig los ntawm qhov koj xav rov qab tus nqi luv
plaub sib cav (-1) suav nrog kev tshawb nrhiav tus lej tsawg tshaj plaws uas peb xav tau es tsis txhob muaj qhov sib tw.

Qhov zoo ntawm cov qauv no yog tias tsis tas yuav txheeb xyuas cov lus luv nqi thiab muaj peev xwm tshawb nrhiav, yog tias tsim nyog, tsis yog tsuas yog qhov ze tshaj plaws, tab sis kuj yog qhov ze tshaj plaws tus nqi. Qhov kev sib cav zaum kawg hauv qhov no yuav yog 1.

Tab sis, hmoov tsis, tsis yog txhua tus muaj qhov tshwj xeeb no - tsuas yog cov tswv zoo siab ntawm Office 365.

Txoj Kev 5. Lus nug

Yog tias koj tseem tsis tau paub txog qhov muaj zog thiab tsis muaj Power Query add-in rau Excel, ces koj nyob ntawm no. Yog tias koj twb paub lawm, cia peb sim siv nws los daws peb cov teeb meem.

Cia peb ua ib co kev npaj ua ntej:

Cia peb hloov peb cov ntxhuav los rau dynamic (ntse) siv cov keyboard shortcut Ctrl+T los yog pab neeg Tsev – Format ua ib lub rooj (Lub Tsev — Format as Table).
Kom paub meej, cia peb muab lawv lub npe. muag и Nyiaj cheb tab constructor (Tsim).
Load txhua lub rooj nyob rau hauv lem mus rau hauv Power Query siv lub pob Los ntawm Table/Range tab Cov ntaub ntawv (Cov ntaub ntawv - Los ntawm lub rooj / ntau). Nyob rau hauv tsis ntev los no versions ntawm Excel, lub pob no tau renamed rau Nrog nplooj (Los ntawm daim ntawv).
Yog tias cov ntxhuav muaj cov npe sib txawv nrog ntau, xws li hauv peb qhov piv txwv ("Qee yam ntawm cov khoom" thiab "Quantity from ..."), ces lawv yuav tsum tau renamed hauv Power Query thiab npe tib yam.
Tom qab ntawd, koj tuaj yeem rov qab mus rau Excel los ntawm kev xaiv cov lus txib hauv Power Query editor window Tsev - Kaw thiab Load - Kaw thiab Load hauv… (Lub Tsev - Kaw & Load - Kaw & Load rau…) thiab ces xaiv Tsuas yog tsim kev sib txuas (tsuas yog tsim kev sib txuas).
Ces qhov nthuav tshaj plaws pib. Yog tias koj muaj kev paub dhau los hauv Power Query, ces kuv xav tias txoj kab txuas ntxiv ntawm kev xav yuav tsum yog nyob rau hauv kev coj ntawm kev sib koom ua ke ob lub rooj nrog kev sib koom ua ke (merge) a la VLOOKUP, ib yam li cov ntaub ntawv hauv txoj kev dhau los. Qhov tseeb, peb yuav tsum tau sib koom ua ke hauv hom ntxiv, uas tsis yog txhua qhov pom tseeb ntawm thawj glance. Xaiv hauv Excel tab Cov ntaub ntawv - Tau cov ntaub ntawv - Muab cov lus thov - Ntxiv (Cov ntaub ntawv - Tau cov ntaub ntawv - Muab cov lus nug - Ntxiv) thiab tom qab ntawd peb cov rooj muag и Nyiaj cheb nyob rau hauv lub qhov rais uas tshwm:
Tom qab txhaj rau OK peb cov ntxhuav yuav muab khi rau hauv ib qho tag nrho - hauv qab ib leeg. Thov nco ntsoov tias cov kab nrog kom muaj nuj nqis ntawm cov khoom hauv cov rooj no poob rau ib leeg, vim. lawv muaj tib lub npe:
Yog tias thawj cov kab hauv kab hauv lub rooj muag khoom tseem ceeb rau koj, yog li ntawd tom qab tag nrho cov kev hloov pauv tom ntej koj tuaj yeem rov qab tau nws, ntxiv ib kab zauv rau peb lub rooj siv cov lus txib. Ntxiv ib Kem – Index Column (Ntxiv kab - Index kem). Yog tias cov kab sib lawv liag tsis muaj teeb meem rau koj, ces koj tuaj yeem hla cov kauj ruam no.
Tam sim no, siv cov npe nco-down hauv lub header ntawm lub rooj, txheeb nws los ntawm kab kom muaj nuj nqis Kev nce qib:
Thiab lub ntsiab ua kom yuam kev: right-click ntawm kab ntawv header luv nqi xaiv ib pab neeg Fill - Down (Fill - Down). Empty cells nrog null cia li sau nrog cov nqi luv nqi yav dhau los:
Nws tseem yuav rov qab kho qhov qub kab ke ntawm kab los ntawm kev txheeb los ntawm kab Index (koj tuaj yeem nyab xeeb rho tawm tom qab) thiab tshem tawm cov kab tsis tsim nyog nrog lub lim null los ntawm kab Kev lag luam code: