Selles artiklis õpime, kuidas Excelist nii kategooria- kui ka kaubakriteeriumidele vastavatest loenditest hinda hankida.
Stsenaarium:
Tabelis on lihtne leida ühe kriteeriumiga väärtus, selleks võiksime lihtsalt kasutada funktsiooni VLOOKUP. Kuid kui teie andmetes pole ühtegi veerukriteeriumi ja peate leidma mitme veeru kriteeriumid, mis vastavad väärtusele, ei aita VLOOKUP.
Üldvalem nii kategooriale kui ka üksusele
| = INDEX (otsinguvahemik, MATCH (1, INDEX ((kirje1 = üksuse_vahemik) * (kategooria2 = kategooriavahemik), 0,1), 0)) |
lookup_range: see on vahemik, millest soovite väärtust hankida.
Kriteeriumid 1, kriteeriumid 2, kriteeriumid N: need on kriteeriumid, mille soovite vahemikus 1, vahemikus2 ja vahemikus N sobitada. Teil võib olla kuni 270 kriteeriumi - vahemiku paarid.
Vahemik1, vahemik2, vahemik N: need on vahemikud, milles vastate oma vastavatele kriteeriumidele
Näide:
Kõiki neid võib segadusse ajada. Mõistame, kuidas funktsiooni kasutada näite abil. Siin on meil andmetabel. Soovin broneeringu kuupäeva, ehitaja ja piirkonna abil kasutada kliendi nime. Siin on mul kolm kriteeriumi ja üks otsinguvahemik.
Kirjutage see valem lahtrisse I4, vajutades sisestusklahvi.
| = INDEKS (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)) |
Me juba teame, kuidas INDEX ja MATCH funktsioon EXCELis töötavad, seega ei hakka seda siin selgitama. Me räägime trikkist, mida me siin kasutasime.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Põhiosa on see. Selle väite iga osa tagastab massiivi tõene vale.
Kui tõeväärtused korrutatakse, tagastavad nad massiivi 0 ja 1. Korrutamine toimib JA -operaatorina. Raske, kui kõik väärtused on tõesed, siis tagastab see 1 muu 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) See tuleb tagasi
Mis tõlgitakse
| {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): Funktsioon INDEX tagastab sama massiivi ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) funktsiooni MATCH otsimismassiivina.
MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): MATCH -funktsioon otsib massiivist {0; 0; 0 1; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. Ja tagastab massiivist leitud esimese 1 indeksi numbri. Siin on 8.
INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Lõpuks naaseb INDEX väärtus antud vahemikust (E2: E16) leitud indeksi (8) juures.
Kui saate sellest tulenevalt vajutada klahve CTRL + SHIFT + ENTER, saate sisemise INDEX -funktsiooni kõrvaldada. Lihtsalt kirjuta see valem ja vajuta CTRL + SHIFT + ENTER.
Valem
| = INDEKS (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0)) |
Üldine massiivivalem mitme kriteeriumi otsimiseks
| = INDEX (otsinguvahemik, MATCH (1, (kriteerium1 = vahemik1)*(kriteerium2 = vahemik2)*(kriteerium N = vahemikN), 0)) |
Siin on kõik vaatlusmärkused, kasutades Exceli valemit
Märkused:
- Kasutage Vlookupi, kui teil on üks kriteerium, mis sobib, see on tavaline tava.
- Otsingumassiivi saate lisada rohkem ridu ja veerge.
- Tekstiargumendid tuleb esitada jutumärkides ("").
- Tabelis VLOOKUP peab vasakus servas või esimeses veerus olema lookup_array.
- Col -indeks ei saa olla 1, kuna see on otsingumassiiv
- Täpse vaste väärtuse jaoks kasutatakse argumenti 0. Ligikaudse vaste väärtuse jaoks kasutage 1.
- Funktsioon tagastab vea #N/A, kui otsinguväärtust otsingumassiivist ei leita. Seega püüdke viga vajadusel kinni.
Loodan, et see artikkel selle kohta, kuidas Excelis nii kategooriate kui ka üksuste kriteeriumitele vastavatest hindadest hinnast alla laadida, on selgitav. Siit leiate rohkem artikleid väärtuste arvutamise ja sellega seotud Exceli valemite kohta. Kui teile meeldisid meie ajaveebid, jagage neid oma sõpradega Facebookis. Samuti saate meid jälgida Twitteris ja Facebookis. Tahaksime sinust kuulda, andke meile teada, kuidas saaksime oma tööd täiustada, täiendada või uuendada ning muuta see teie jaoks paremaks. Kirjuta meile meilisaidile.
Kuidas Excelis uusimat hinda hankida : Igas ettevõttes on tavaline hindu värskendada ja iga ostu või müügi puhul tuleb kasutada uusimaid hindu. Viimase hinna hankimiseks Exceli loendist kasutame funktsiooni LOOKUP. Funktsioon LOOKUP toob viimase hinna.
Funktsioon VLOOKUP Exceli hinde arvutamiseks : Klasside arvutamiseks ei ole IF ja IFS ainsad funktsioonid, mida saate kasutada. VLOOKUP on selliste tingimuslike arvutuste jaoks tõhusam ja dünaamilisem. Hinnete arvutamiseks VLOOKUPi abil saame kasutada seda valemit.
17 asja Exceli kohta VLOOKUP : VLOOKUP -i kasutatakse kõige sagedamini sobitatud väärtuste toomiseks, kuid VLOOKUP saab teha palju enamat. Siin on 17 asja VLOOKUPi kohta, mida peaksite tõhusaks kasutamiseks teadma.
Otsi üles esimene tekst loendist Excelis : Funktsioon VLOOKUP töötab metamärkidega hästi. Me saame seda kasutada Exceli antud loendist esimese tekstiväärtuse eraldamiseks. Siin on üldine valem.
OTSINGU kuupäev loendis viimase väärtusega : Viimast väärtust sisaldava kuupäeva toomiseks kasutame funktsiooni LOOKUP. See funktsioon otsib lahtrit, mis sisaldab vektori viimast väärtust, ja kasutab seejärel seda viidet kuupäeva tagastamiseks.
Populaarsed artiklid:
50 Exceli otseteed tootlikkuse suurendamiseks : Saate Excelis oma ülesannetega kiiremini hakkama. Need otseteed aitavad teil Excelis oma töö efektiivsust suurendada.
Funktsiooni VLOOKUP kasutamine Excelis : See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
Funktsiooni IF kasutamine Excelis : Exceli IF -lause kontrollib tingimust ja tagastab konkreetse väärtuse, kui tingimus on TRUE, või tagastab mõne muu väärtuse, kui FALSE.
Funktsiooni SUMIF kasutamine Excelis : See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.
Funktsiooni COUNTIF kasutamine Excelis : Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea oma andmeid teatud väärtuste loendamiseks filtreerima. Countif funktsioon on armatuurlaua ettevalmistamisel hädavajalik.