Yadda za a shirya parachute daidai?
Amfani. Bugu na 2, sake dubawa.
Bari mu ce muna da tebur na umarni masu zuwa:
Muna bukatar mu san, alal misali, menene adadin umarnin Ivanov na uku ko lokacin da Petrov ya aiwatar da yarjejeniyarsa ta biyu. Ayyukan VLOOKUP da aka gina a ciki zai iya nemo farkon abin da ya faru na sunan ƙarshe a cikin tebur kuma ba zai taimake mu ba. Tambayoyi kamar "Wane ne manajan lambar oda 10256?" kuma zai kasance ba a amsa ba, tk. ginannen VLOOKUP ba zai iya dawo da ƙima daga ginshiƙai zuwa hagu na binciken ɗaya ba.
Duk waɗannan matsalolin guda biyu ana warware su ne sau ɗaya - bari mu rubuta namu aikin da zai duba ba kawai na farko ba, amma, a cikin yanayin gabaɗaya, don faruwar Nth. Bugu da ƙari, zai iya bincika da samar da sakamako a cikin kowane ginshiƙai. Bari mu kira shi, a ce, VLOOKUP2.
Bude Editan Kayayyakin Kaya ta latsa ALT + F11 ko ta zaɓi daga menu Sabis – Macro – Kayayyakin Basic Edita (Kayan aiki - Macro - Editan Kayayyakin Kayayyakin gani), saka sabon module (menu Saka - Module) da kwafi rubutun wannan aikin a can:
Aiki VLOOKUP2(Table As Variant, SearchColumnNum As Dogon, SearchValue As Variant, _N Dogon, ResultColumnNum As Dogon) 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 Sai iCount = iCount + 1 End If If iCount = N Sai VLOOKUP2 = Table.Cells(i, ResultColumnNum) Exit For End if next i Case "Variant()" For i = 1 Zuwa UBound(Table) Idan Table(i, SearchColumnNum) = SearchValue Sai iCount = iCount + 1 Idan iCount = N Sai VLOOKUP2 = Tebur (i, ResultColumnNum) Fita Don Ƙarshe Idan Na gaba na Ƙare Zaɓi Ƙarshen Aiki
Rufe Kayayyakin Basic Editan kuma komawa Excel.
Yanzu ta hanyar Saka - Aiki (Saka - Aiki) a cikin category Ma'anar Mai amfani (An bayyana mai amfani) Kuna iya nemo aikin mu na VLOOKUP2 kuma kuyi amfani da shi. Haɗin aikin shine kamar haka:
= VLOOKUP2 (tebur; lambar_column_inda_ muke nema; neman_darajar; N; adadin_column_daga_samu_darajar)
Yanzu iyakokin daidaitaccen aikin ba su zama cikas a gare mu ba:
PS Godiya ta musamman ga The_Prist don inganta aikin ta yadda zai iya bincika cikin rufaffiyar littattafai.
- Nemo da musanya bayanai daga wannan tebur zuwa wani ta amfani da aikin VLOOKUP
- "Hagu VLOOKUP" ta amfani da ayyukan INDEX da MATCH