Šiame straipsnyje mes sužinosime, kaip gauti kainą iš sąrašo, atitinkančio „Excel“ kategorijos ir elemento kriterijus.
Scenarijus:
Lentelėje nesunku rasti vertę pagal vieną kriterijų, todėl galėtume tiesiog naudoti VLOOKUP. Bet kai jūsų duomenyse nėra vieno stulpelio kriterijų ir jums reikia rasti kelis stulpelius, kad atitiktų vertę, VLOOKUP nepadeda.
Bendra formulė tiek kategorijai, tiek elementui
| = INDEKSAS (paieškos_diapazonas, MATCH (1, INDEKSAS ((elementas1 = elemento_diapazonas) * (2 kategorija = kategorijos_diapazija), 0,1), 0)) |
lookup_range: tai 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 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)) |
Mes jau žinome, kaip EXCEL veikia INDEX ir MATCH funkcijos, todėl čia to nepaaiškinsiu. 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įš
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 1 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).
Jei galite paspausti CTRL + SHIFT + ENTER, galite pašalinti vidinę INDEX funkciją. Tiesiog parašykite šią formulę ir paspauskite CTRL + SHIFT + ENTER.
Formulė
| = INDEKSAS (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0)) |
Bendra masyvo formulė kelių kriterijų paieškai
| = INDEX (lookup_range, MATCH (1, (kriterijus1 = diapazonas1)*(kriterijus2 = diapazonas2)*(kriterijusN = diapazonasN), 0)) |
Čia yra visos stebėjimo pastabos, naudojant „Excel“ formulę
Pastabos :
- Jei turite vieną kriterijų, naudokite „Vlookup“, tai yra įprasta praktika.
- Peržiūros masyve galite pridėti daugiau eilučių ir stulpelių.
- Teksto argumentai turi būti pateikti kabutėse („“).
- Lentelėje VLOOKUP kairėje arba pirmajame stulpelyje turi būti lookup_array.
- Kolonų indeksas negali būti 1, nes tai yra paieškos masyvas
- Tikslios atitikties reikšmei naudojamas 0 argumentas. Apytiksliai atitikties vertei naudokite 1.
- Funkcija grąžina klaidą #N/A, jei peržvalgos masyve nerasta paieškos vertės. Taigi, jei reikia, suraskite klaidą.
Tikimės, kad šis straipsnis apie tai, kaip gauti kainą iš sąrašo, atitinkančio „Excel“ kategorijos ir elemento kriterijus, 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.
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 vertei iš įvairių diapazonų ir lapų ieškoti.
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į.