VLOOKUP muaj nuj nqi ib txwm yog ib qho ntawm Excel cov haujlwm muaj zog tshaj plaws. Nws cia koj tshawb nrhiav qhov tseem ceeb hauv thawj kab ntawm lub rooj, thiab xa cov nqi rov qab los ntawm cov teb ntawm sab xis. Tab sis Excel kuj muaj lub luag haujlwm hu ua XLOOKUP, uas tso cai rau koj tshawb nrhiav tus nqi hauv txhua kab lossis kab, thiab xa cov ntaub ntawv rov qab los ntawm lwm kab.
XLOOKUP ua haujlwm li cas
XLOOKUP muaj nuj nqi yog qhov yooj yim dua rau kev siv dua li VLOOKUP muaj nuj nqi, vim tias tsis yog kev qhia tus nqi rau kab ntawv, koj tuaj yeem qhia tag nrho ntau yam.
Lub luag haujlwm tseem tso cai rau koj tshawb nrhiav ob sab thiab kab, nrhiav tus nqi ntawm qhov sib tshuam ntawm tes.
Qhov tsis muaj ntawm XLOOKUP muaj nuj nqi yog raws li hauv qab no:
=XLOOKUP (lookup_value, lookup_array, return_array, [match_mode], [search_mode])
- lookup_value: Tus nqi koj xav tshawb rau
- lookup_array: Cov array (kem) koj xav tshawb
- return_array: Qhov tshwm sim (kem) koj xav muab tus nqi los ntawm
- match_mode (optional): Xaiv qhov kev sib tw tiag tiag (0), qhov sib tw pes tsawg lossis tus nqi tsawg tshaj plaws (-1), lossis qhov sib tw wildcard (2).
- search_mode (optional): Xaiv seb puas yuav tshawb nrhiav pib nrog thawj yam khoom hauv kab (1), yam khoom kawg hauv kab (-1), binary search ascending (2) lossis binary tshawb nrhiav nqis (-2).
Cov hauv qab no yog ob peb qhov kev saib ntau tshaj plaws uas koj tuaj yeem ua nrog XLOOKUP muaj nuj nqi.
Yuav Ua Li Cas Nrhiav Ib Qhov Kev Pom Zoo Siv XLOOKUP
Txoj kev yooj yim tshaj plaws los siv XLOOKUP yog tshawb nrhiav ib qho txiaj ntsig siv cov ntaub ntawv taw qhia los ntawm ib kab.
-
Daim ntawv qhia piv txwv no yog cov npe ntawm kev txiav txim xa los ntawm cov neeg muag khoom, suav nrog cov khoom, tus lej, tus nqi, thiab muag tag nrho.
Image -
Yog tias koj xav nrhiav thawj qhov muag hauv cov npe xa los ntawm ib tus neeg muag khoom tshwj xeeb, koj tuaj yeem tsim ib qho XLOOKUP muaj nuj nqi uas tshawb rau Rep kab rau lub npe. Cov haujlwm yuav rov qab tau cov txiaj ntsig los ntawm Total kem. XLOOKUP muaj nuj nqi rau qhov no yog:
=XLOOKUP(I2, C2:C44, G2:G44, 0, 1)
- I2: Points to the Rep Name search cell
- C2:C44: Qhov no yog Rep kem, uas yog tus saib array
- G2:G33: This is the Total kem, which is the return array
- 0: Xaiv qhov sib tw tiag tiag
- 1: Xaiv thawj qhov sib tw hauv cov txiaj ntsig
-
Thaum koj nias Enter thiab ntaus lub npe ntawm tus neeg muag khoom, tag nrho cov txiaj ntsig ntawm tes yuav qhia koj thawj qhov tshwm sim hauv lub rooj rau tus neeg muag khoom ntawd.
Image -
Yog tias koj xav tshawb nrhiav qhov muag tsis ntev los no (txij li lub rooj tau xaj los ntawm hnub nyob rau hauv qhov kev txiav txim rov qab), hloov qhov kev sib cav XLOOKUP kawg rau - 1, uas yuav pib kev tshawb nrhiav los ntawm lub xov tooj kawg hauv qhov kev tshawb nrhiav array thiab muab cov txiaj ntsig ntawd rau koj.
Image -
Qhov piv txwv no qhia txog kev tshawb fawb zoo sib xws uas koj tuaj yeem ua nrog VLOOKUP muaj nuj nqi los ntawm kev siv kab lus Rep ua thawj kab ntawm lub rooj saib. Txawm li cas los xij, XLOOKUP cia koj tshawb rau txhua kab hauv ob qho tib si. Piv txwv li, yog tias koj xav nrhiav tus neeg muag khoom muag uas muag thawj Binder xaj ntawm lub xyoo, koj yuav siv qhov ua haujlwm XLOOKUP ua haujlwm:
=XLOOKUP(I2, D2:D44, C2:C44, 0, 1)
- D2: Cov ntsiab lus rau cov khoom tshawb nrhiav cell
- D2:D44: This is the Item column, which is the lookup array
- C2:C44: Qhov no yog kab lus Rep, uas yog tus xa rov qab mus rau sab laug ntawm qhov nrhiav array
- 0: Xaiv qhov sib tw tiag tiag
- 1: Xaiv thawj qhov sib tw hauv cov txiaj ntsig
-
Lub sijhawm no, qhov tshwm sim yuav yog lub npe ntawm tus muag khoom muag uas muag thawj daim ntawv xaj ntawm lub xyoo.
Image
Ua ntsug thiab kab rov tav Match nrog XLOOKUP
Lwm lub peev xwm ntawm XLOOKUP uas VLOOKUP tsis muaj peev xwm yog lub peev xwm los ua ob qho kev tshawb nrhiav ntsug thiab kab rov tav, txhais tau tias koj tuaj yeem tshawb nrhiav ib yam khoom hauv kab, thiab hla ib kab ib yam.
Qhov kev tshawb nrhiav dual no yog qhov hloov pauv zoo rau lwm cov haujlwm Excel xws li INDEX, MATCH, lossis HLOOKUP.
-
Nyob rau hauv daim ntawv piv txwv nram qab no, kev muag khoom rau txhua tus neeg muag khoom tau muab faib ua peb lub hlis twg. Yog tias koj xav pom lub quarter thib peb muag rau ib qho kev muag khoom tshwj xeeb, yam tsis muaj XLOOKUP muaj nuj nqi, qhov kev tshawb nrhiav no yuav nyuaj.
Image -
Nrog XLOOKUP muaj nuj nqi, qhov kev tshawb nrhiav no yooj yim. Siv cov nram qab no XLOOKUP muaj nuj nqi, koj tuaj yeem tshawb nrhiav kev muag khoom peb lub hlis twg rau ib tus neeg muag khoom tshwj xeeb:
=XLOOKUP(J2, B2:B42, XLOOKUP(K2, C1:H1, C2:H42))
- J2: Cov ntsiab lus rau Rep tshawb nrhiav cell
- B2:B42: This is the Item column, which is the column lookup array
- K2: Cov ntsiab lus rau lub quarter nrhiav cell
- C1:H1: This is the row lookup array
- C2:H42: Qhov no yog qhov nrhiav array rau cov nyiaj duas hauv txhua lub quarter
Qhov no nested XLOOKUP muaj nuj nqi ua ntej txheeb xyuas tus muag khoom, thiab XLOOKUP ua haujlwm tom ntej txheeb xyuas lub quarter xav tau. Tus nqi xa rov qab yuav yog lub xov tooj ntawm ob qhov cuam tshuam.
-
Qhov tshwm sim rau cov qauv no yog peb lub hlis twg cov nyiaj tau los rau tus neeg sawv cev nrog lub npe Thompson.
Image
Siv XLOOKUP Function
XLOOKUP muaj nuj nqi tsuas yog muaj rau Office Insider cov neeg siv khoom, tab sis yuav sai sai no yuav muab tso rau txhua tus neeg siv Microsoft 365.
Yog tias koj xav sim cov haujlwm ntawm koj tus kheej, koj tuaj yeem dhau los ua Office Insider. Xaiv File > Account, tom qab ntawd xaiv tus Office Insider poob qis rau subscribe.
Thaum koj koom nrog Office Insider program, koj qhov kev teeb tsa ntawm Excel yuav tau txais tag nrho cov kev hloov tshiab tshiab, thiab koj tuaj yeem pib siv XLOOKUP muaj nuj nqi.
