VLOOKUP on yksi Excelin käytetyimmistä toiminnoista. Se etsii arvoa alueelta ja palauttaa vastaavan arvon tietyssä sarakenumerossa.
Nyt törmäsin ongelmaan, jossa minun piti etsiä koko rivi ja palauttaa arvot kaikista kyseisen rivin sarakkeista (yksittäisen arvon palauttamisen sijaan).
Joten tässä minun piti tehdä. Alla olevassa tietojoukossa minulla oli myyntiedustajien nimet ja heidän tekemänsä myynti neljänneksellä vuonna 2012. Minulla oli pudotusvalikko, jossa oli heidän nimensä, ja halusin poimia kyseisen myyntiedustajan enimmäismyynnin näiden neljänneksen aikana.
Voisin keksiä kaksi eri tapaa tehdä tämä - käyttämällä INDEXiä tai VLOOKUPia.
Etsi koko rivi / sarake INDEX -kaavan avulla
Tässä on kaava, jonka olen luonut tekemään tämän käyttämällä indeksiä
= SUURI (INDEKSI ($ B $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0), 1)
Kuinka se toimii:
Tarkastellaan ensin INDEX -toimintoa, joka on kääritty LARGE -toiminnon sisään.
= INDEKSI ($ C $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0)
Analysoidaan tarkasti INDEX -funktion argumentit:
- Array - $ B $ 4: $ F $ 1
- Rivinumero - MATCH (H3, $ B $ 4: $ B $ 13,0)
- Sarakkeen numero - 0
Huomaa, että olen käyttänyt sarakkeen numeroa 0.
Temppu on tässä, että kun käytät sarakkeen numeroa 0, se palauttaa kaikkien sarakkeiden kaikki arvot. Jos valitsen avattavasta valikosta John, indeksikaava palauttaa kaikki Johnin 4 myyntiarvoa {91064,71690,67574,25427}.
Nyt voin käyttää suurta funktiota poimiaksesi suurimman arvon
Pro -vinkki - Palauta kaikki sarakkeiden/rivien arvot käyttämällä sarakkeen/rivin numeroa 0 -indeksikaavassa.
Etsi koko rivi / sarake VLOOKUP -kaavan avulla
Indeksikaava on siisti, puhdas ja vankka, mutta VLOOKUP -tapa on hieman monimutkainen. Se tekee myös toiminnasta haihtuvan. On kuitenkin hämmästyttävä temppu, jonka jaan tässä osiossa. Tässä on kaava:
= LARGE (VLOOKUP (H3, B4: F13, ROW (Epäsuora ("2:" & COUNTA ($ B $ 4: $ F $ 4))), FALSE), 1)
Kuinka se toimii
- RIVI (Epäsuora ("2:" & COUNTA ($ B $ 4: $ F $ 4))) - Tämä kaava palauttaa taulukon {2; 3; 4; 5}. Huomaa, että koska se käyttää epäsuoraa, tämä tekee tästä kaavasta haihtuvan.
- VLOOKUP (H3, B4: F13, ROW (Epäsuora (“2:” & COUNTA ($ B $ 4: $ F $ 4))), FALSE) - Tässä on paras osa. Kun yhdistät nämä, siitä tulee VLOOKUP (H3, B4: F13, {2; 3; 4; 5}, FALSE). Huomaa nyt, että yhden sarakenumeron sijasta olen antanut sille joukon sarakenumeroita. VLOOKUP etsii tottelevaisesti arvoja kaikista näistä sarakkeista ja palauttaa taulukon.
- Käytä nyt LARGE -toimintoa poimiaksesi suurimman arvon.
Muista käyttää kaavaa Control + Shift + Enter.
Ammattilaisvinkki - VLOOKUPissa yhden sarakenumeron käyttämisen sijaan, jos käytät sarakenumeroita, se palauttaa hakuarvojen matriisin.
