V tem članku se bomo naučili, kako pridobiti ceno s seznama, ki se ujema z merili kategorije in predmeta v Excelu.
Scenarij:
V tabeli je enostavno najti vrednost z enim merilom, za to lahko preprosto uporabimo VLOOKUP. Če pa v podatkih nimate enotnih meril za stolpce in morate poiskati več meril za stolpce, da se ujemajo z vrednostjo, VLOOKUP ne pomaga.
Splošna formula za kategorijo in izdelek
| = INDEX (iskalno območje, MATCH (1, INDEX ((item1 = item_range)) * (category2 = range_ range), 0,1), 0)) |
lookup_range: To je obseg, iz katerega želite pridobiti vrednost.
Merila1, Merila2, Merila N: To so merila, ki jih želite ujemati v razponu1, obsegu2 in obsegu N. Lahko imate do 270 meril - parov območij.
Razpon 1, obseg2, obsegN: To so obsegi, v katerih boste ustrezali vašim kriterijem
Primer:
Vse to je lahko zmedeno za razumevanje. Poglejmo, kako uporabiti funkcijo na primeru. Tukaj imamo podatkovno tabelo. Želim potegniti ime stranke z datumom rezervacije, graditeljem in površino. Tu imam torej tri kriterije in en obseg iskanja.
To formulo zapišite v celico I4, pritisnite Enter.
| = INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)) |
Že vemo, kako funkciji INDEX in MATCH delujeta v EXCEL -u, zato tega ne bomo razlagali tukaj. Govorili bomo o triku, ki smo ga uporabili tukaj.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Glavni del je to. Vsak del te izjave vrne polje true false.
Ko se logične vrednosti pomnožijo, vrnejo matriko 0 in 1. Množenje deluje kot operator AND. Torej, ko so vse vrednosti resnične, potem vrne 1 else 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) To se bo skupaj vrnilo
Kar se bo prevedlo v
| {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 vrne isto matriko ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) funkciji MATCH kot iskalna matrika.
MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): funkcija MATCH bo iskala 1 v matriki {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. In vrne številko indeksa prve 1, najdene v matriki. Kar je 8 tukaj.
INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Nazadnje se bo INDEX vrnil vrednost iz danega območja (E2: E16) pri najdenem indeksu (8).
Če lahko pritisnete CTRL + SHIFT + ENTER, lahko odstranite notranjo funkcijo INDEX. Samo napišite to formulo in pritisnite CTRL + SHIFT + ENTER.
Formula
| = INDEX (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0)) |
Splošna formula matrike za iskanje po več kriterijih
| = INDEX (iskalno območje, MATCH (1, (merila1 = obseg1)*(merila2 = obseg2)*(merilaN = obsegN), 0)) |
Tu so vse opombe, ki uporabljajo formulo v Excelu
Opombe:
- Če imate ena merila, uporabite Vlookup, to je običajna praksa.
- V iskalno polje lahko dodate več vrstic in stolpcev.
- Besedilni argumenti morajo biti navedeni v narekovajih ("").
- Tabela VLOOKUP mora imeti lookup_array v skrajnem levem ali prvem stolpcu.
- Indeks stolpca ne more biti 1, ker je iskalno polje
- Za vrednost natančnega ujemanja se uporablja argument 0. Za približno vrednost ujemanja uporabite 1.
- Funkcija vrne napako #N/A, če iskalne vrednosti ne najdete v matriki za iskanje. Zato po potrebi odpravite napako.
Upam, da je ta članek o tem, kako pridobiti ceno s seznama, ki se ujema z merili kategorije in predmeta v Excelu, razložljiv. Več člankov o izračunu vrednosti in sorodnih formulah Excel najdete tukaj. Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletnem mestu e -pošte.
Kako pridobiti zadnjo ceno v Excelu : Običajno je posodobiti cene v katerem koli poslu in uporaba najnovejših cen pri nakupu ali prodaji je nujna. Za pridobitev najnovejše cene s seznama v Excelu uporabimo funkcijo LOOKUP. Funkcija LOOKUP doseže najnovejšo ceno.
Funkcija VLOOKUP za izračun ocene v Excelu : Za izračun ocen IF in IFS nista edini funkciji, ki jo lahko uporabite. VLOOKUP je za take pogojne izračune učinkovitejši in dinamičnejši. Za izračun ocen z uporabo VLOOKUP -a lahko uporabimo to formulo.
17 stvari o Excelu VLOOKUP : VLOOKUP se najpogosteje uporablja za pridobivanje ujemajočih se vrednosti, vendar lahko VLOOKUP naredi veliko več kot to. Tukaj je 17 stvari o VLOOKUP -u, ki jih morate vedeti za učinkovito uporabo.
POGLEDAJTE prvo besedilo s seznama v Excelu : Funkcija VLOOKUP dobro deluje z nadomestnimi znaki. To lahko uporabimo za ekstrahiranje prve besedilne vrednosti z danega seznama v Excelu. Tu je splošna formula.
LOOKUP datum z zadnjo vrednostjo na seznamu : Za pridobitev datuma, ki vsebuje zadnjo vrednost, uporabimo funkcijo LOOKUP. Ta funkcija preveri celico, ki vsebuje zadnjo vrednost v vektorju, in nato uporabi to referenco za vrnitev datuma.
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost : Pospešite svoje naloge v Excelu. Te bližnjice vam bodo pomagale povečati učinkovitost dela v Excelu.
Kako uporabljati funkcijo VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo IF v Excelu : Stavek IF v Excelu preveri pogoj in vrne določeno vrednost, če je pogoj TRUE, ali vrne drugo posebno vrednost, če je FALSE.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.