Selles artiklis õpime, kuidas otsida Excelist mitu väärtuse eksemplari.
Kas otsida väärtusi rippmenüüst?
Siin mõistame, kuidas saame INDEX -funktsioonimassiivi valemi abil erinevaid tulemusi otsida. Valige lihtsalt loendist väärtus ja vastav tulemus on olemas.
Üldine valem
| {= INDEX (massiiv, SMALL (IF (lookup_value = lookup_value_range, ROW (lookup_value_range) -ROW (lookup_value_range) esimene lahter) +1), ROW (1: 1))}} |
Massiiv: Vahemik, kust soovite andmeid hankida.
lookup_value: Teie otsinguväärtus, mida soovite filtreerida.
lookup_value_range: Vahemik, milles soovite otsinguväärtust filtreerida.
Otsinguväärtuste vahemiku esimene lahter: kui teie otsinguväärtuste vahemik on $ A $ 5: $ A $ 100, siis selle $ 5 $.
Tähtis: Kõik peaks olema absoluutselt viidatud. lookup_value võib olla vastavalt nõudele suhteline.
Sisestage see massiivivalemina. Pärast valemi kirjutamist kasutage massiivivalemi saamiseks klahve CTRL+SHIFT+ENTER.
Näide mitme tulemuse otsimisest
Need õpilaste andmed on vahemikus A2: E14. Lahtris G1 on mul piirkonna väärtuste rippmenüü nt. Kesk-, ida-, põhja-, lõuna- ja lääneosa. Nüüd tahan, et olenemata sellest, milline piirkond mul G1 -s on, tuleks veergu H kuvada kõigi selle piirkonna õpilaste loend.
Excelis mitme väärtuse otsimiseks tuvastame oma muutujad.
Massiiv: $ 2 $: $ 14 $
lookup_value: $ G $ 1
lookup_value_range: $ 2 $: $ 14 $
Otsinguväärtuste vahemiku esimene lahter: 2 A dollarit
Ülaltoodud andmete kohaselt on meie valem Excelis mitme väärtuse toomiseks järgmine:
| {= IFERROR (INDEX ($ C $ 2: $ C $ 14, SMALL (IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14)) -ROW ($ A $ 2) +1) , ROW (1: 1))), "Enam väärtust pole")} |
See on massiivivalem. Ärge kasutage pärast valemi sisestamist lihtsalt sisestusklahvi. Kasutage koos CTRL + SHIFT + ENTER.
Nüüd mõistame, kuidas see toimib.
Kuigi valem võib tunduda keeruline, on idee lihtne. Peame hankima iga väärtuse esinemise indeksinumbri ja seejärel väärtused alla laadima Exceli funktsiooni INDEX abil.
Seega on peamine väljakutse saada hulga indeksi numbreid lookup_value. Indeksinumbrite saamiseks kasutasime funktsioone IF ja ROW. Valem on tõepoolest keeruline, jagame selle.
Soovime saada väärtusi õpilaste veerust, seega on meie funktsiooni INDEX massiiv $ 2 $: $ 14 $.
Nüüd peame andma ridade numbrid $ 2 $: $ 14 $ (otsinguväärtus), kus G1 väärtus eksisteerib (ütleme praegu G1 sellel on keskpunkt).
IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14): Nüüd tagastab see osa rea numbri, kui lahtri loenduri väärtus G1 (keskne) vahemikus $ 2 $: $ 14 $ muidu tuleb tagasi VALE. Selles näites naaseb see tagasi
{2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE}.
Kuna ülaltoodud massiiv sisaldab rea numbreid 1. reast (1: 1) ja vajame ridu, mis algavad meie massiivist (A2: A14). Selleks kasutame IF -valemis -ROW ($ A $ 2) +1. See tagastab enne meie massiivi käivitamist rea ridu.
Selle näite puhul on see -1. Kui see algaks A3 -st, tagastab see -2 ja nii edasi. See arv lahutatakse igast IF poolt tagastatud massiivi numbrist. Nii et Lõpuks IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) see tõlgitakse {1; FALSE; FALSE; FALSE; FALSE;6;7; FALSE; FALSE;10; FALSE; FALSE; FALSE}.
Seejärel ümbritseb seda massiivi funktsioon SMALL. See funktsioon tagastab antud massiivi väikseima n -nda väärtuse. Nüüd on meil VÄIKE ({2; FALSE; FALSE; FALSE; FALSE;7;8; FALSE; FALSE;11; FALSE; FALSE; FALSE}, RIDA (1: 1)). ROW (1: 1) tagastab 1. Seega tagastab ülaltoodud funktsioon massiivi 1. väikseima väärtuse, mis on 2.
Kui kopeerite selle valemi järgmistesse lahtritesse, saab ROW (1: 1) ROW (2: 2) ja tagastab massiivi 2. väikseima väärtuse, mis on 7 jne. See võimaldab funktsioonil kõigepealt tagastada kõigepealt leitud väärtuse. Aga kui soovite kõigepealt saada viimati leitud väärtuse, siis kasutage funktsiooni SUUR, mitte funktsiooni VÄIKE.
Kasutades ülaltoodud väärtusi, tagastab funktsioon INDEX hõlpsalt iga sobiva väärtuse vahemikust.
Täpseid vasteid saate teha ka Exceli funktsiooni INDEX ja MATCH abil. Lugege lisateavet selle kohta, kuidas teha Exceli rakenduses INDEX & MATCH funktsiooni tõstutundlik otsing. Osalisi vasteid saate otsida ka Exceli metamärkide abil.
Loodetavasti on see artikkel selle kohta, kuidas Excelis mitu väärtust otsida, selgitav. Siit leiate rohkem artikleid väärtuste arvutamise ja sellega seotud Exceli valemite kohta. Kui teile meeldisid meie ajaveebid, jagage seda 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.
Kasutage kahe või enama otsingutabeli VLOOKUP -i | Mitmest tabelist otsimiseks saame kasutada IFERROR -lähenemist. Mitmest tabelist otsimiseks võtab viga järgmise tabeli lülitina. Teine meetod võib olla If -lähenemine.
Kuidas Excelis teha tõstutundlikku otsingut? | Exceli funktsioon VLOOKUP ei ole tõstutundlik ja tagastab loendist esimese sobitatud väärtuse. INDEX-MATCH pole erand, kuid seda saab muuta, et muuta see tõstutundlikuks. Vaatame, kuidas…
Otsige Excelis sageli esinevat teksti kriteeriumidega | Otsing ilmub kõige sagedamini tekstis vahemikus, mida kasutame funktsiooni INDEX-MATCH with MODE abil. Siin on meetod.
Populaarsed artiklid:
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 VLOOKUP kasutamine Excelis : See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
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.