Lataa esimerkkityökirja
Tämä opetusohjelma osoittaa, miten kirjainkoolla erottava VLOOKUP suoritetaan Excelissä kahdella eri menetelmällä ja Google Sheets yhdellä menetelmällä.
Kirjainkoon erottava VLOOKUP ja sarake
VLOOKUP -toiminto
VLOOKUP -toimintoa käytetään etsimään likimääräinen tai tarkka vastaavuus alueen vasemmanpuoleisen sarakkeen arvolle ja palautetaan vastaava arvo toisesta sarakkeesta. Oletuksena VLOOKUP toimii vain sellaisilla arvoilla, jotka eivät erota kirjaimista, kuten:
| 1 | = HAKU ($ E $ 2, $ B $ 2: $ C $ 4,2,0) |
Kirjainkoolla erottava VLOOKUP
Yhdistämällä VLOOKUP, EXACT, MAX ja ROW voimme luoda kirjainkokoherkän VLOOKUP-kaavan, joka palauttaa vastaavan arvon kirjainkoolla merkitsevälle VLOOKUP: lle. Käydään esimerkki läpi.
Meillä on luettelo tuotteista ja niitä vastaavista hinnoista (huomaa, että nimikkeen isot ja pienet kirjaimet erottavat toisistaan):
Olettaen, että meitä pyydetään saamaan tuotteen hinta käyttämällä sen tuotetunnusta seuraavasti:
Tämän saavuttamiseksi meidän on ensin luotava auttajasarake ROW:
| 1 | = RIVI () |
= ROW () napsauta ja vedä (tai kaksoisnapsauta) täyttääksesi kaikki alueen rivit
Yhdistä seuraavaksi VLOOKUP, MAX, EXACT ja ROW seuraavanlaiseen kaavaan:
| 12 | = VLOOKUP (MAX (EXACT (,)*(ROW ())),,, 0) |
| 1 | = VLOOKUP (MAX (EXACT (E2, $ B $ 2: $ B $ 7)*(ROW ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0) |
Miten kaava toimii?
- EXACT -funktio tarkistaa kohteen E2 (hakuarvo) tuotetunnuksen B2: B7 (hakualue) -arvojen kanssa ja palauttaa taulukon TOSI, jos taulukossa on {FLASE, FLASE, FLASE, FLASE, FLASE, TRUE}.
- Tämä taulukko kerrotaan sitten ROW -taulukolla {2, 3, 4, 5, 6, 7} (huomaa, että tämä vastaa auttajasaraketta).
- MAX -funktio palauttaa tuloksena olevan taulukon maksimiarvon {0,0,0,0,0,7}, joka on esimerkissämme 7.
- Sitten käytämme tulosta hakuarvona VLOOKUP -ohjelmassa ja valitsemme hakualueeksi auttajasarakkeen. Esimerkissämme kaava palauttaa vastaavan arvon 16,00 dollaria.
Menetelmä 2 - kirjainkoolla merkitsevä VLOOKUP, jossa on "virtuaalinen" auttajasarake
Tämä menetelmä käyttää samaa logiikkaa kuin ensimmäinen menetelmä, mutta eliminoi tarpeen avustajasarakkeen luomiseen ja luo sen sijaan VALINTA- ja RIVI -muodon "virtuaalisen" auttajasarakkeen luomiseksi seuraavasti:
| 12 | = VLOOKUP (MAX (EXACT (,)*(ROW ())),VALITSE ({1,2}, RIVI (),),, 0) |
| 1 | = VLOOKUP (MAX (EXACT (D2, $ B $ 2: $ B $ 7)*(ROW ($ B $ 2: $ B $ 7))), CHOOSE ({1,2}, ROW ($ B $ 2: $ B $ 7)) , $ C $ 2: $ C $ 7), 2,0) |
Miten kaava toimii?
- Kaavan ensimmäinen osa toimii samalla tavalla kuin ensimmäinen menetelmä.
- Yhdistämällä CHOOSE ja ROW palauttaa taulukon, jossa on kaksi saraketta, yksi rivinumeroa ja toinen hintaa varten. Matriisi on erotettu puolipisteellä seuraavan rivin ja pilkun kanssa seuraavalla sarakkeella: {2,45; 3,83; 4,23; 5,74; 6,4; 7,16}.
- Voimme sitten käyttää VLOOKUPin kaavan ensimmäisen osan tulosta löytääksesi vastaavan arvon CHOOSE- ja ROW -matriisistamme.
Kirjainkoolla erottava VLOOKUP Google Sheetsissä
Suorita kirjainkoolla erottava VLOOKUP Google Sheetsissä seuraavasti:
