Yuav ua li cas kom ntim ib tug parachute?
Tau txais txiaj ntsig. tsab 2, kho.
Cia peb hais tias peb muaj cov lus txib hauv qab no:
Peb yuav tsum paub, piv txwv li, dab tsi yog tus nqi ntawm Ivanov qhov kev txiav txim thib peb los yog thaum Petrov tua nws thib ob deal. Lub built-in VLOOKUP muaj nuj nqi tsuas yog tshawb nrhiav thawj qhov tshwm sim ntawm lub xeem lub npe hauv lub rooj thiab yuav tsis pab peb. Cov lus nug xws li "Leej twg yog tus thawj tswj hwm ntawm tus lej xaj 10256?" tseem yuav nyob tsis teb, tk. lub built-in VLOOKUP tsis muaj peev xwm rov qab tau qhov tseem ceeb ntawm txhua kab mus rau sab laug ntawm kev tshawb nrhiav ib qho.
Ob qho ntawm cov teeb meem no tau daws nyob rau hauv ib qho poob swoop - cia peb sau peb tus kheej txoj haujlwm uas yuav saib tsis yog rau thawj zaug, tab sis, feem ntau, rau qhov tshwm sim Nth. Ntxiv mus, nws yuav tuaj yeem tshawb nrhiav thiab tsim cov txiaj ntsig hauv txhua kab. Wb hu, hais, VLOOKUP2.
Qhib Visual Basic Editor los ntawm nias ALT + F11 los yog xaiv los ntawm cov ntawv qhia zaub mov Kev Pabcuam - Macro - Visual Basic Editor (Cov cuab yeej - Macro - Visual Basic Editor), ntxig ib qho tshiab module (menu Ntxig - Module) thiab luam cov ntawv ntawm cov haujlwm no muaj:
Function VLOOKUP2(Table As Variant, SearchColumnNum As Long, SearchValue As Variant, _ N As Long, ResultColumnNum As Long) Dim i As Long, iCount As Long Select Case TypeName(Table) Case "Range" For i = 1 To Table.Rows .Count If Table.Cells(i, SearchColumnNum) = SearchValue Then iCount = iCount + 1 End Yog Yog iCount = N Ces VLOOKUP2 = Table.Cells(i, ResultColumnNum) Exit For End Yog Next i Case "Variant()" Rau kuv = 1 To UBound(Table) If Table(i, SearchColumnNum) = SearchValue Then iCount = iCount + 1 If iCount = N Then VLOOKUP2 = Table(i, ResultColumnNum) Exit For End If Next i End Xaiv End Function
Kaw Visual Basic Editor thiab rov qab mus rau Excel.
Tam sim no dhau Ntxig - Function (Insert — Function) hauv qeb Neeg Siv Li (User Defined) koj tuaj yeem nrhiav peb VLOOKUP2 muaj nuj nqi thiab siv nws. Lub luag haujlwm syntax yog raws li nram no:
=VLOOKUP2(table; number_of_column_where_we look for; lookup_value; N; num_of_column_from_to_get_value)
Tam sim no cov kev txwv ntawm tus qauv kev ua haujlwm tsis yog qhov cuam tshuam rau peb:
PS Ua tsaug tshwj xeeb rau The_Prist rau kev txhim kho txoj haujlwm kom nws tuaj yeem tshawb nrhiav hauv phau ntawv kaw.
- Nrhiav thiab hloov cov ntaub ntawv los ntawm ib lub rooj mus rau lwm qhov siv VLOOKUP muaj nuj nqi
- "Sab laug VLOOKUP" siv lub INDEX thiab MATCH ua haujlwm