Šajā rakstā mēs uzzināsim, kā MEKLĒT vērtību starp diviem cipariem programmā Excel.
Scenārijs:
Tabulā ir viegli meklēt vērtību ar vienu kritēriju. Mēs varam vienkārši izmantot VLOOKUP. Bet ko mēs varētu darīt, ja šie vairāku sleju kritēriji atbilst jūsu datiem un ir jāizmeklē vairākās slejās, lai tie atbilstu vērtībai. Uzzināsim, kā šo problēmu var atrisināt, izmantojot dažādas Excel versijas
LOOKUP vērtība starp diviem cipariem
Izmantojot VLOOKUP formulu
Ja VLOOKUP aptuvenā atbilstība netiek atrasta, tad tiek saskaņota pēdējā vērtība, kas ir mazāka par uzmeklēšanas vērtību, un tiek atgriezta vērtība no dotā slejas indeksa.
Vispārēja formula uz LOOKUP vērtību starp diviem cipariem:
| = VLOOKUP (vērtība, tabula, lookup_col, 1) |
Vēl viena lieta par Vlookup ir tā, ka tā meklē vērtību kolonnā un, ja tā neatrod vērtību kolonnas masīvā, tā atbilst un atgriež vērtību, kas ir mazāka par šo vērtību tabulas masīvā.
Piezīme. VLOOKUP pēc noklusējuma atbilst aptuvenai, ja mēs izlaižam diapazona uzmeklēšanas mainīgo. Aptuvenā atbilstība ir noderīga, ja vēlaties veikt aptuvenu atbilstību un kad tabulas masīvs ir sakārtots augošā secībā.
Izmantojot formulu INDEX un MATCH
Formula INDEX un MATCH darbojas tāpat kā iepriekš, bet ar atšķirīgu sintaksi. Bet, ja jums vajadzētu izvēlēties tikai to, ar kuru funkcijām esat apmierināts
Vispārīgā formula uz LOOKUP vērtību starp diviem cipariem
| = INDEKSS (lookup_range, MATCH (1, INDEX ((kritērijs1 = diapazons1)*(kritērijs2 = diapazons2),0,1),0)) |
lookup_range: diapazons, no kura vēlaties izgūt vērtību.
Kritēriji1, Kritēriji2, Kritēriji N: Šie ir kritēriji, kuriem vēlaties atbilst diapazonā1, diapazonā2 un diapazonā N. Jums var būt līdz 270 kritērijiem - diapazona pāri.
Diapazons1, diapazons2, diapazonsN: šie ir diapazoni, kuros jūs atbilstat saviem attiecīgajiem kritērijiem.
Piemērs :
To visu var būt mulsinoši saprast. Sapratīsim, kā izmantot funkciju, izmantojot piemēru. Šeit mums ir studenta ID un viņu attiecīgās atzīmes testā. Tagad mums bija jāiegūst atzīmes katram skolēnam, meklējot atzīmju sistēmas tabulā.
Lai to izdarītu, mēs izmantosim funkcijas VLOOKUP atribūtu, kas ir, ja funkcija VLOOKUP neatrod precīzu atbilstību, ko tā meklē tikai tabulā, un tikai tad, ja funkcijas pēdējais arguments ir vai nu PATIESA vai 1.
Zīmju / rezultātu tabulai jābūt augošā secībā
Izmantojiet formulu:
| = MEKLĒŠANA (B2, tabula, 2, 1) |
Kā tas darbojas?
Funkcija Vlookup atzīmju slejā atrod vērtību 40 un atgriež atbilstošo vērtību, ja tā atbilst. Ja tas nesakrīt, funkcija meklē mazāko vērtību nekā uzmeklēšanas vērtība (ti, 40) un atgriež rezultātu.
Šeit table_array ir nosaukts diapazons kā tabula formulā un B2 ir norādīts kā šūnu atsauce.
Kā redzat, mēs ieguvām atzīmi pirmajam skolēnam. Tagad, lai iegūtu visas pārējās pakāpes, mēs izmantosim saīsni Ctrl + D vai izmantojiet Excel velciet uz leju šūnu opciju.
Šeit ir visas klases pakāpes, kurās tiek izmantota funkcija VLOOKUP.
LOOKUP vērtība starp diviem skaitļiem tabulā Darbinieki
Mums ir tabula, kurā atsevišķā lapā ir informācija par visiem organizācijas darbiniekiem. Pirmajā slejā ir šo darbinieku ID. Šo tabulu esmu nosaucis par emp_data.
Tagad manai meklēšanas lapai ir jāiegūst darbinieka informācija, kuras ID ir ierakstīts šūnā B3. Es esmu nosaucis B3 kā ID.
Lai būtu vieglāk saprast, visu kolonnu virsraksti ir tādā pašā secībā kā tabula emp_data.
Tagad ierakstiet zemāk esošo formulu šūnā C3, lai izgūtu B3 ierakstīto darbinieka ID zonu.
| = MEKLĒŠANA (ID, Emp_Data, 2,0) |
Tādējādi tiks atgriezta darbinieka ID zona 1-1830456593, jo datu bāzes 2. slejā ir iekļauta darbinieku zona.
Kopējiet šo formulu pārējās šūnās un mainiet kolonnas numuru formulā, lai iegūtu visu informāciju par darbiniekiem.
Visu informāciju, kas saistīta ar minēto ID, var redzēt šūnā B3. Neatkarīgi no tā, kuru ID jūs ierakstāt šūnā B3, visa informācija tiks ielādēta, nemainot formulas.
Kā tas darbojas?
Nav nekas grūts. Mēs vienkārši izmantojam funkciju VLOOKUP, lai meklētu ID un pēc tam izgūtu minēto kolonnu. Praktizējiet VLOOKUP, izmantojot šādus datus, lai labāk izprastu VLOOKUP.
Izgūt darbinieku datus, izmantojot virsrakstus
Iepriekš minētajā piemērā visas kolonnas bija sakārtotas tādā pašā secībā, taču reizēm jums būs datubāze, kurā būs simtiem kolonnu. Šādos gadījumos šī darbinieku informācijas izguves metode nebūs laba. Būs labāk, ja formula varēs aplūkot kolonnas virsrakstu un iegūt datus no šīs slejas no darbinieku tabulas.
Tātad, lai iegūtu tabulas vērtību, izmantojot kolonnas virsrakstu, mēs izmantosim divvirzienu uzmeklēšanas metodi vai teiksim dinamisko kolonnu VLOOKUP.
Izmantojiet šo formulu šūnā C3 un kopējiet pārējās šūnās. Formulā nekas nav jāmaina, viss tiks izgūts no thd emp_data.
| = VLOOKUP (ID, Emp_Data, MATCH (C2, Emp_Data_Headers, 0), 0) |
Šī formula vienkārši izgūst visu informāciju no atbilstošajām kolonnām. Pārskatā varat sajaukt galvenes, tas neko nemainīs. Neatkarīgi no tā, kurš virsraksts ir ierakstīts iepriekšējā šūnā, ir ietverti attiecīgie dati.
Kā tas darbojas?
Tas ir vienkārši dinamisks VLOOKUP. Par to varat izlasīt šeit. Ja es to šeit paskaidrošu, tas kļūs par pārāk lielu rakstu.
Izgūstiet darbinieka ID ar daļēju atbilstību
Var gadīties, ka jūs neatceraties visu darbinieka ID, bet tomēr vēlaties iegūt kāda ID informāciju. Šādos gadījumos daļēja atbilstība VLOOKUP ir labākais risinājums.
Piemēram, ja es zinu, ka daži ID satur 2345, bet es nezinu visu ID. Ja es ievadīšu šo numuru šūnā C3, izvade būs līdzīga.
Mēs neko nesaņemam. Tā kā tabulā nekas neatbilst 2345. Mainiet iepriekš minēto formulu šādi.
| =MEKLĒŠANA("*"&ID&"*", Emp_Data,MATCH(C2, Emp_Data_Headers, 0), 0) |
Kopējiet to visā rindā. Un tagad jums ir pirmā darbinieka informācija, kurā ir šis numurs.
Lūdzu, ņemiet vērā, ka mēs iegūsim pirmo ID, kas slejā Emp Id satur atbilstošo numuru. Ja kāds cits ID satur to pašu numuru, šī formula neizgūst šī darbinieka informāciju.
Ja vēlaties iegūt visus darbinieku ID, kas satur vienu un to pašu numuru, izmantojiet formulu, kas uzmeklē visas atbilstošās vērtības.
Izmantojot formulu INDEX un MATCH
Šeit mums ir datu tabula. Es vēlos izvilkt klienta vārdu, izmantojot rezervācijas datumu, celtnieku un apgabalu. Tātad šeit man ir trīs kritēriji un viens meklēšanas diapazons.
Ierakstiet šo formulu šūnā I4, nospiediet taustiņu Enter.
| = INDEKSS (E2: E16, MATCH (1, INDEKSS ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)) |
Kā tas strādā:
Mēs jau zinām, kā EXCEL darbojas INDEX un MATCH funkcija, tāpēc es šeit to neskaidrošu. Mēs šeit runāsim par triku, ko izmantojām.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Galvenā daļa ir šī. Katra šī paziņojuma daļa atgriež patiesu nepatiesu masīvu.
Reizinot Būla vērtības, tās atgriež masīvu no 0 un 1. Reizināšana darbojas kā AND operators. Smagi, ja visas vērtības ir patiesas, tikai tad atgriež 1 citu 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Tas viss atgriezīsies
| {FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}*
{FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}* {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}] |
Kas tiks tulkots
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEKSS ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Funkcija INDEX atgriezīs to pašu masīvu ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) līdz MATCH funkcijai kā uzmeklēšanas masīvs.
MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): funkcija MATCH meklēs 1 masīvā {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. Un atgriezīs masīvā atrastā pirmā indeksa numuru. Šeit ir 8.
INDEKSS (E2: E16, MATCH (1, INDEKSS ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Visbeidzot, INDEX atgriezīsies vērtība no dotā diapazona (E2: E16) pie atrastā indeksa (8).
Šeit ir visas novērojumu piezīmes, izmantojot formulu programmā Excel
Piezīmes:
- Atzīmju / punktu tabulas slejai jābūt augošā secībā, pretējā gadījumā funkcija var dot nepareizu rezultātu.
- Funkcija VLOOKUP meklē vērtību tabulas masīva pirmajā rindā un iegūst atbilstošās vērtības tikai pa labi no uzmeklēšanas diapazona.
- Funkcijas VLOOKUP pēdējais funkcijas arguments ir jāiestata vai nu TRUE, vai 1, lai iegūtu aptuveno atbilstību.
- Funkcija VLOOKUP atgriež kļūdu, ja darbgrāmatas adrese ir nederīga vai nepareiza.
- Funkcija VLOOKUP atgriež kļūdu, ja vērtība nav saskaņota.
Ceru, ka šis raksts par to, kā meklēt vērtību starp diviem cipariem programmā Excel, ir paskaidrojošs. Vairāk rakstu par vērtību un saistīto Excel formulu aprēķināšanu meklējiet šeit. Ja jums patika mūsu emuāri, kopīgojiet tos ar saviem draugiem Facebook. Un arī jūs varat sekot mums Twitter un Facebook. Mēs labprāt uzklausītu jūsu viedokli, dariet mums zināmu, kā mēs varam uzlabot, papildināt vai ieviest jauninājumus mūsu darbā un uzlabot to jūsu labā. Rakstiet mums e -pasta vietnē.
Veids, kā izmantot Vlookup funkciju datu validācijā : Ierobežojiet lietotājus atļaut vērtības no uzmeklēšanas tabulas, izmantojot Excel datu formulas lodziņu. Datu validācijas formulas lodziņš ļauj izvēlēties nepieciešamo ierobežojuma veidu.
Kā iegūt jaunāko cenu programmā Excel : Parasti jebkurā biznesā tiek atjauninātas cenas, un pirkumiem vai pārdošanai obligāti jāizmanto jaunākās cenas. Lai iegūtu jaunāko cenu no Excel saraksta, mēs izmantojam funkciju LOOKUP. Funkcija LOOKUP iegūst jaunāko cenu.
Funkcija VLOOKUP, lai aprēķinātu atzīmi programmā Excel : Lai aprēķinātu pakāpes, IF un IFS nav vienīgās funkcijas, kuras varat izmantot. VLOOKUP ir efektīvāks un dinamisks šādiem nosacītiem aprēķiniem. Lai aprēķinātu pakāpes, izmantojot VLOOKUP, mēs varam izmantot šo formulu.
17 lietas par Excel VLOOKUP : VLOOKUP visbiežāk tiek izmantots, lai izgūtu atbilstošas vērtības, taču VLOOKUP var paveikt daudz vairāk. Šeit ir 17 lietas par VLOOKUP, kas jums jāzina, lai efektīvi izmantotu.
MEKLĒT pirmo tekstu no saraksta programmā Excel : Funkcija VLOOKUP lieliski darbojas ar aizstājējzīmēm. Mēs to varam izmantot, lai izvilktu pirmo teksta vērtību no dotā saraksta programmā Excel. Šeit ir vispārīgā formula.
MEKLĒŠANAS datums ar pēdējo vērtību sarakstā : Lai izgūtu datumu, kurā ir pēdējā vērtība, mēs izmantojam funkciju LOOKUP. Šī funkcija pārbauda šūnu, kurā ir pēdējā vektora vērtība, un pēc tam izmanto šo atsauci, lai atgrieztu datumu.
Populāri raksti:
50 Excel saīsnes, lai palielinātu produktivitāti : Ātrāk izpildiet savus uzdevumus programmā Excel. Šie īsceļi palīdzēs jums palielināt darba efektivitāti programmā Excel.
Kā lietot funkciju VLOOKUP programmā Excel : Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, ko izmanto, lai meklētu vērtības no dažādiem diapazoniem un lapām.
Kā lietot funkciju IF programmā Excel : IF priekšraksts programmā Excel pārbauda nosacījumu un atgriež noteiktu vērtību, ja nosacījums ir TRUE, vai atgriež citu noteiktu vērtību, ja FALSE.
Kā lietot funkciju SUMIF programmā Excel : Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.
Kā lietot funkciju COUNTIF programmā Excel : Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.