Šiame straipsnyje mes sužinosime, kaip „Excel“ ieškoti dviejų skaičių.
Scenarijus:
Lentelėje nesunku rasti vertę pagal vieną kriterijų. Mes galime tiesiog naudoti VLOOKUP. Bet ką galėtume padaryti, jei tie keli stulpelių kriterijai atitiktų jūsų duomenis ir kad juos reikia ieškoti keliuose stulpeliuose, kad jie atitiktų vertę. Sužinokime, kaip šią problemą galima išspręsti naudojant skirtingas „Excel“ paieškos versijas
LOOKUP reikšmė tarp dviejų skaičių
Naudojant VLOOKUP formulę
Jei VLOOKUP apytikslė atitiktis nerasta, paskutinė reikšmė, mažesnė už paieškos vertę, yra suderinama ir grąžinama iš nurodyto stulpelio indekso reikšmė.
Bendra formulė LOOKUP reikšmei tarp dviejų skaičių:
| = VLOOKUP (vertė, lentelė, lookup_col, 1) |
Dar vienas dalykas, susijęs su „Vlookup“, yra tai, kad jis ieško vertės stulpelyje ir jei neranda vertės stulpelių masyve, tada ji atitinka ir grąžina vertę, kuri yra mažesnė už tą reikšmę lentelės masyve.
Pastaba: VLOOKUP pagal numatytuosius nustatymus apytiksliai atitinka, jei praleidžiame diapazono paieškos kintamąjį. Apytikslė atitiktis yra naudinga, kai norite atlikti apytikslę atitiktį ir kai lentelės masyvas surūšiuotas didėjančia tvarka.
Naudojant formulę INDEX ir MATCH
INDEX ir MATCH formulė atlieka tą patį darbą, kaip aprašyta aukščiau, tačiau su skirtinga sintaksė. Bet jei turėtumėte pasirinkti tik tą, su kuriomis funkcijomis esate patenkintas
Bendroji formulė LOOKUP reikšmei tarp dviejų skaičių
| = INDEX (lookup_range, MATCH (1, INDEX ((kriterijus1 = diapazonas1)*(kriterijus2 = diapazonas2),0,1),0)) |
lookup_range: diapazonas, iš kurio norite gauti vertę.
1 kriterijus, 2 kriterijus, N kriterijus: tai yra kriterijai, kuriuos norite atitikti diapazone1, diapazone2 ir diapazone N. Galite turėti iki 270 kriterijų - diapazono porų.
Diapazonas1, diapazonas2, diapazonas N: tai yra diapazonai, kuriuose atitiksite atitinkamus kriterijus.
Pavyzdys :
Visa tai gali būti painu suprasti. Supraskime, kaip naudoti funkciją, naudodami pavyzdį. Čia mes turime studento ID ir atitinkamus pažymius teste. Dabar turėjome gauti kiekvieno mokinio pažymius peržiūrėję pažymių sistemos lentelę.
Norėdami tai padaryti, naudosime funkcijos VLOOKUP atributą, kuris yra, jei funkcija VLOOKUP neranda tikslios atitikties, kurios ji ieško apytiksliai tik lentelėje, ir tik tuo atveju, jei paskutinis funkcijos argumentas yra TIESA arba 1.
Žymų / balų lentelė turi būti didėjančia tvarka
Naudokite formulę:
| = VLOOKUP (B2, lentelė, 2, 1) |
Kaip tai veikia?
Funkcija „Vlookup“ ieško 40 reikšmių stulpelyje ir grąžina atitinkamą vertę, jei ji sutampa. Jei ji nesutampa, funkcija ieško mažesnės vertės nei paieškos vertė (t. Y. 40) ir grąžina rezultatą.
Čia table_array pavadintas diapazonas kaip lentelę formulėje ir B2 pateikiamas kaip langelio nuoroda.
Kaip matote, mes gavome pažymį pirmajam mokiniui. Dabar norėdami gauti visas kitas klases, naudosime nuorodą Ctrl + D arba naudokite „Excel“ langelio parinktį žemyn.
Čia yra visi klasės pažymiai, naudojantys funkciją VLOOKUP.
LOOKUP reikšmė tarp dviejų skaičių darbuotojų lentelėje
Turime lentelę, kurioje atskirame lape yra išsami informacija apie visus organizacijos darbuotojus. Pirmajame stulpelyje yra šių darbuotojų ID. Šią lentelę pavadinau emp_data.
Dabar mano paieškos lape reikia gauti darbuotojo informaciją, kurios ID įrašytas B3 langelyje. Aš pavadinau B3 kaip ID.
Kad būtų lengviau suprasti, visos stulpelio antraštės yra tiksliai tokia pat tvarka kaip ir lentelė emp_data.
Dabar C3 langelyje parašykite žemiau esančią formulę, kad gautumėte darbuotojo ID zoną, parašytą B3.
| = VLOOKUP (ID, Emp_Data, 2,0) |
Bus grąžinta darbuotojo ID 1-1830456593 zona, nes duomenų bazės 2 stulpelyje yra darbuotojų zona.
Nukopijuokite šią formulę į likusias langelius ir pakeiskite formulės stulpelio numerį, kad gautumėte visą darbuotojų informaciją.
Visą informaciją, susijusią su minėtu ID, galite pamatyti B3 langelyje. Nepriklausomai nuo to, kurį ID įrašysite B3 langelyje, visa informacija bus gauta nepakeičiant formulės.
Kaip tai veikia?
Nėra nieko sudėtingo. Mes tiesiog naudojame funkciją VLOOKUP, kad surastume ID ir tada gautume minėtą stulpelį. Praktikuokite VLOOKUP naudodami tokius duomenis, kad geriau suprastumėte VLOOKUP.
Gaukite darbuotojų duomenis naudodami antraštes
Anksčiau pateiktame pavyzdyje visi stulpeliai buvo sutvarkyti ta pačia tvarka, tačiau kartais turėsite duomenų bazę, kurioje bus šimtai stulpelių. Tokiais atvejais šis darbuotojų informacijos gavimo būdas nebus geras. Bus geriau, jei formulė galės pažvelgti į stulpelio antraštę ir iš stulpelio gauti duomenis iš darbuotojų lentelės.
Taigi, norėdami gauti vertę iš lentelės naudodami stulpelio antraštę, naudosime dviejų krypčių paieškos metodą arba pasakysime dinaminį stulpelį VLOOKUP.
Naudokite šią formulę C3 langelyje ir nukopijuokite į likusias ląsteles. Jums nieko nereikia keisti formulėje, viskas bus nuskaityta iš „thd emp_data“.
| = VLOOKUP (ID, Emp_Data, MATCH (C2, Emp_Data_Headers, 0), 0) |
Ši formulė tiesiog nuskaito visą informaciją iš suderintų stulpelių. Galite sumaišyti ataskaitos antraštes, tai neturės jokio skirtumo. Nepriklausomai nuo to, kuri antraštė parašyta aukščiau esančiame langelyje, yra atitinkami duomenys.
Kaip tai veikia?
Tai tiesiog dinamiškas VLOOKUP. Apie tai galite paskaityti čia. Jei čia paaiškinsiu, tai taps per dideliu straipsniu.
Gauti darbuotojo ID su daline atitiktimi
Gali atsitikti taip, kad neprisimenate viso darbuotojo asmens tapatybės dokumento, tačiau vis tiek norite gauti tam tikro asmens tapatybės dokumento informaciją. Tokiais atvejais dalinis atitikimas VLOOKUP yra geriausias sprendimas.
Pavyzdžiui, jei aš žinau, kad kai kuriame ID yra 2345, bet aš nežinau viso ID. Jei įvesiu šį skaičių ląstelėje C3, išvestis bus panaši.
Mes nieko negauname. Kadangi niekas neatitinka 2345 lentelėje. Pakeiskite aukščiau pateiktą formulę taip.
| =VLOOKUP("*"&ID&"*", Emp_Data,Rungtynės(C2, „Emp_Data_Headers“, 0), 0) |
Nukopijuokite tai visoje eilutėje. Ir dabar jūs turite pirmojo darbuotojo informaciją, kurioje yra šis numeris.
Atminkite, kad mes gausime pirmąjį ID, kuriame yra atitinkamas skaičius stulpelyje „Emp Id“. Jei bet kuriame kitame ID yra tas pats numeris, ši formulė neatgauna to darbuotojo informacijos.
Jei norite gauti visus darbuotojų ID, kuriuose yra tas pats skaičius, naudokite formulę, kurioje ieškomos visos suderintos vertės.
Naudojant formulę INDEX ir MATCH
Čia mes turime duomenų lentelę. Noriu ištrinti kliento vardą, naudojant užsakymo datą, statybininką ir vietovę. Taigi čia aš turiu tris kriterijus ir vieną paieškos diapazoną.
Parašykite šią formulę I4 langelyje paspauskite „Enter“.
| = INDEKSAS (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)) |
Kaip tai veikia:
Mes jau žinome, kaip EXCEL veikia INDEX ir MATCH funkcijos, todėl čia nesiruošiu to paaiškinti. Mes kalbėsime apie čia naudotą triuką.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Pagrindinė dalis yra tokia. Kiekviena šio teiginio dalis grąžina tikrą klaidingą masyvą.
Kai loginės vertės dauginamos, jos grąžina 0 ir 1 masyvą. Daugyba veikia kaip AND operatorius. Sunkus, kai visos vertės yra teisingos, tik tada grąžina 1 kitą 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Visa tai grįš
| {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} |
Kuris bus išverstas į
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Funkcija INDEX grąžins tą patį masyvą ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) į MATCH funkciją kaip paieškos masyvas.
MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): funkcija MATCH ieškos 1 masyve {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. Ir grąžins masyvo pirmojo indekso numerį. Čia yra 8.
INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): pagaliau INDEX grįš reikšmė iš nurodyto diapazono (E2: E16) esant nustatytam indeksui (8).
Čia yra visos stebėjimo pastabos, naudojant „Excel“ formulę
Pastabos :
- Žymų / balų lentelės stulpelis turi būti didėjančia tvarka, kitaip funkcija gali duoti neteisingą rezultatą.
- Funkcija VLOOKUP ieško vertės pirmoje „Table_array“ eilutėje ir ištraukia atitinkamas vertes tik iš lookup_range.
- Funkcijos VLOOKUP paskutinis funkcijos argumentas turi būti nustatytas į TRUE arba 1, kad būtų gautas apytikslis atitikimas.
- Funkcija VLOOKUP grąžina klaidą, jei darbaknygės adresas yra neteisingas arba neteisingas.
- Funkcija VLOOKUP grąžina klaidą, jei vertė nesutampa.
Tikimės, kad šis straipsnis apie tai, kaip rasti reikšmę tarp dviejų „Excel“ skaičių, yra aiškinamasis. Čia rasite daugiau straipsnių apie verčių skaičiavimą ir susijusias „Excel“ formules. Jei jums patiko mūsų tinklaraščiai, pasidalykite jais su draugais „Facebook“. Taip pat galite sekti mus „Twitter“ ir „Facebook“. Mes norėtume išgirsti jūsų nuomonę, praneškite mums, kaip galime patobulinti, papildyti ar naujovinti savo darbą ir padaryti jį geresnį. Rašykite mums el.
„Vlookup“ funkcijos naudojimo būdas duomenų tikrinime : Apribokite naudotojus, kad jie leistų reikšmes iš peržiūros lentelės, naudodami „Excel“ duomenų patvirtinimo formulės lauką. Duomenų patvirtinimo formulės langelis leidžia pasirinkti reikiamą apribojimo tipą.
Kaip gauti naujausią kainą „Excel“ : Įprasta atnaujinti kainas bet kuriame versle, o perkant ar parduodant būtina naudoti naujausias kainas. Norėdami gauti naujausią kainą iš „Excel“ sąrašo, naudojame funkciją LOOKUP. Funkcija LOOKUP pateikia naujausią kainą.
Funkcija VLOOKUP, skirta apskaičiuoti „Excel“ pažymį : Norėdami apskaičiuoti pažymius, IF ir IFS nėra vienintelės funkcijos, kurias galite naudoti. Tokiems sąlyginiams skaičiavimams VLOOKUP yra efektyvesnis ir dinamiškesnis. Norėdami apskaičiuoti pažymius naudodami VLOOKUP, galime naudoti šią formulę.
17 dalykų apie „Excel“ VLOOKUP : „VLOOKUP“ dažniausiai naudojamas norint gauti suderintas vertes, tačiau VLOOKUP gali nuveikti daug daugiau. Čia yra 17 dalykų, susijusių su VLOOKUP, kuriuos turėtumėte žinoti, kad galėtumėte efektyviai naudoti.
Ieškokite pirmojo teksto iš „Excel“ sąrašo : Funkcija VLOOKUP puikiai veikia su pakaitos simboliais. Tai galime naudoti norėdami išgauti pirmąją teksto vertę iš nurodyto „Excel“ sąrašo. Čia yra bendra formulė.
LOOKUP data su paskutine sąrašo verte : Norėdami gauti datą, kurioje yra paskutinė vertė, naudojame funkciją LOOKUP. Ši funkcija patikrina langelį, kuriame yra paskutinė vektoriaus reikšmė, ir tada naudoja šią nuorodą datai grąžinti.
Populiarūs straipsniai:
50 „Excel“ nuorodų, skirtų produktyvumui padidinti : Greičiau atlikite savo užduotis „Excel“. Šie spartieji klavišai padės padidinti darbo efektyvumą naudojant „Excel“.
Kaip naudotis „VLOOKUP“ funkcija „Excel“ : Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama norint ieškoti verčių iš įvairių diapazonų ir lapų.
Kaip naudoti „IF“ funkciją „Excel“ : „Excel“ IF sakinys patikrina sąlygą ir grąžina konkrečią reikšmę, jei sąlyga yra TRUE, arba grąžina kitą konkrečią reikšmę, jei FALSE.
Kaip naudotis „SUMIF“ funkcija „Excel“ : Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.
Kaip naudoti „COUNTIF“ funkciją „Excel“ : Skaičiuokite vertes su sąlygomis naudodami šią nuostabią funkciją. Norint suskaičiuoti konkrečias vertes, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.