Sisällys
Tämä pieni opetusohjelma selittää, kuinka funktio tehdään VPR (VLOOKUP) kirjainkoolla, näyttää useita muita kaavoja, joita Excel voi etsiä kirjainkoolla, ja osoittaa kunkin funktion vahvuudet ja heikkoudet.
Luulen, että jokainen Excel-käyttäjä tietää, mikä toiminto suorittaa pystyhaun. Aivan oikein, se on toiminto VPR. Kuitenkin harvat tietävät sen VPR isot ja pienet kirjaimet ovat samat.
Tässä on nopea esimerkki kyvyttömyydestä VPR tunnista rekisteri. Oletetaan solussa A1 sisältää arvon "lasku" ja solun A2 - "Lasku", kaava:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… lopettaa haun "laskulla", koska kyseinen arvo on luettelon ensimmäisenä, ja poimi arvon solusta B1.
Myöhemmin tässä artikkelissa näytän sinulle, miten se tehdään VPR kirjainkoon herkkä. Lisäksi opimme muutamia muita toimintoja, jotka voivat suorittaa kirjainkoolla huomioitavia hakuja Excelissä.
Aloitamme yksinkertaisimmasta - KATSO (HAKU) ja SUMPRODUCT (SUMPRODUCT), joilla on valitettavasti useita merkittäviä rajoituksia. Seuraavaksi tarkastelemme tarkemmin hieman monimutkaisempaa kaavaa INDEKSI+OTUMINEN (INDEX+MATCH), joka toimii moitteettomasti kaikissa tilanteissa ja minkä tahansa tietojoukon kanssa.
VLOOKUP-toiminto on isojen ja pienten kirjainten välinen ero
Kuten jo tiedät, tavallinen toiminto VPR kirjainkoolla ei ole merkitystä. On kuitenkin olemassa tapa tehdä siitä herkkä kirjainkoko. Tätä varten sinun on lisättävä taulukkoon lisäsarake seuraavan esimerkin mukaisesti.
Oletetaan sarakkeessa B on tuotetunnisteita (Tuote) ja haluat poimia sarakkeista tuotteen hinnan ja vastaavan kommentin C и D. Ongelmana on, että tunnisteet sisältävät sekä pieniä että isoja kirjaimia. Esimerkiksi solujen arvot B4 (001Tvci3u) ja B5 (001Tvci3U) eroavat vain viimeisen merkin kohdalla, u и U vastaavasti.
Kuten voit kuvitella, tavallinen hakukaava
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
palaa $ 90, koska arvo 001Tvci3u on hakualueella aikaisemmin kuin 001Tvci3U. Mutta sitä me emme tarvitse, eihän?
Haku funktiolla VPR Excelissä sinun on lisättävä apusarake ja täytettävä sen solut seuraavalla kaavalla (jossa B on hakusarake):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Tämä kaava jakaa halutun arvon erillisiin merkkeihin, korvaa jokaisen merkin koodillaan (esimerkiksi sen sijaan A sen sijaan 65-vuotiaana a koodi 97) ja yhdistää sitten nämä koodit ainutlaatuiseksi numerosarjaksi.
Sen jälkeen käytämme yksinkertaista toimintoa VPR kirjainkoon erotteluun:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Toiminnon oikea toiminta VPR kirjainkoolla on merkitystä kahdesta tekijästä:
- Apusarakkeen on oltava katseltavissa olevan alueen vasemmanpuoleisin sarake.
- Etsimäsi arvon tulee sisältää merkkikoodi todellisen arvon sijaan.
Kuinka käyttää CODE-toimintoa oikein
Apusarakkeen soluihin lisätty kaava olettaa, että kaikissa hakuarvoissasi on sama määrä merkkejä. Jos ei, sinun on tiedettävä pienimmät ja suurimmat luvut ja lisättävä mahdollisimman monta ominaisuutta IFERROR (IFERROR), kuinka monta merkkiä on lyhimmän ja pisimmän haetun arvon välinen ero.
Jos esimerkiksi lyhin hakuarvo on 3 merkkiä ja pisin 5 merkkiä, käytä tätä kaavaa:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
Toiminnan vuoksi PSTR (MID) Esität seuraavat argumentit:
- 1. argumentti - teksti (teksti) on teksti- tai soluviittaus, joka sisältää poimittavat merkit (tässä tapauksessa se on B2)
- 2. argumentti - aloitusnumero (aloituspaikka) on ensimmäisten poimittavien merkkien sijainti. tulet sisään 1 ensimmäisessä toiminnossa PSTR, 2 – toisessa toiminnossa PSTR ja niin edelleen
- 3. argumentti - merkkien_määrä (merkkien_määrä) – Määrittää tekstistä poimittavien merkkien määrän. Koska tarvitsemme vain yhden merkin koko ajan, kirjoitamme kaikissa funktioissa 1.
RAJOITUKSET: Toiminto VPR ei ole paras ratkaisu kirjainkoon erotteluun Excelissä. Ensinnäkin tarvitaan lisäkolonni. Toiseksi, kaava tekee hyvää työtä vain, jos tiedot ovat homogeenisiä tai haettujen arvojen tarkka merkkimäärä tiedetään. Jos tämä ei ole sinun tapauksesi, on parempi käyttää jotakin alla näytetyistä ratkaisuista.
HAKU-toiminto kirjainkoon erotteluun
Toiminto KATSO (HAKU) liittyvät VPR, mutta sen syntaksi mahdollistaa isot ja pienet kirjaimet haut ilman lisäsarakkeen lisäämistä. Käytä tätä varten KATSO yhdistettynä toimintoon TARKKA (TARKKA).
Jos otamme tiedot edellisestä esimerkistä (ilman apusaraketta), seuraava kaava selviää tehtävästä:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Kaava hakee alueella A2: A7 tarkka vastaavuus solun arvon kanssa F2 kirjainkoolla ja palauttaa arvon saman rivin sarakkeesta B.
Pitää VPRtoiminto KATSO toimii yhtä hyvin tekstin ja numeeristen arvojen kanssa, kuten alla olevasta kuvakaappauksesta näet:
Tärkeää! Toiminnon vuoksi KATSO toimi oikein, hakusarakkeen arvot tulee lajitella nousevaan järjestykseen eli pienimmästä suurimpaan.
Selitän lyhyesti, kuinka toiminto toimii TARKKA edellä esitetyssä kaavassa, koska tämä on keskeinen kohta.
Toiminto TARKKA vertaa kahta tekstiarvoa 1. ja 2. argumentissa ja palauttaa TRUE, jos ne ovat täsmälleen samat, tai EPÄTOSI, jos ne eivät ole. Meille on tärkeää, että toiminto TARKKA kirjainkoon herkkä.
Katsotaan kuinka kaavamme toimii NÄKYMÄ+TARKKA:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Toiminto TARKKA vertaa solun arvoa F2 kaikki elementit sarakkeessa A (A2:A7). Palauttaa TRUE, jos täsmällinen vastaavuus löytyy, muussa tapauksessa EPÄTOSI.
- Koska annat ensimmäisen funktion argumentin KATSO arvo TRUE, se poimii vastaavan arvon määritetystä sarakkeesta (tässä tapauksessa sarakkeesta B) vain, jos löytyy tarkka vastaavuus, isot ja pienet kirjaimet.
Toivottavasti tämä selitys oli selvä ja nyt ymmärrät pääidean. Jos näin on, sinulla ei ole vaikeuksia muiden toimintojen kanssa, joita analysoimme tarkemmin, koska. ne kaikki toimivat samalla periaatteella.
RAJOITUKSET: Hakusarakkeen tiedot on lajiteltava nousevaan järjestykseen.
SUMPRODUCT – etsii tekstiarvot, isot ja pienet kirjaimet, mutta palauttaa vain numerot
Kuten otsikosta jo ymmärsit, SUMPRODUCT (SUMPRODUCT) on toinen Excel-funktio, joka auttaa sinua tekemään haun kirjainkoolla, mutta palauttaa vain numeerisia arvoja. Jos tämä vaihtoehto ei sovi sinulle, voit siirtyä välittömästi nippuun INDEKSI+OTUMINEN, joka tarjoaa ratkaisun kaikkiin tapauksiin ja kaikkiin tietotyyppeihin.
Selitän ensin lyhyesti tämän funktion syntaksin. Tämä auttaa sinua ymmärtämään paremmin seuraavaa kirjainkoolla olevaa kaavaa.
Toiminto SUMPRODUCT kertoo annettujen taulukoiden alkiot ja palauttaa tulosten summan. Syntaksi näyttää tältä:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Koska tarvitsemme kirjainkoon erottelun, käytämme funktiota TARKKA (TARKKA) edellisestä esimerkistä yhtenä kertoimista:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Kuten muistat, TARKKA vertaa solun arvoa F2 kaikki elementit sarakkeessa A. Palauttaa TRUE, jos täsmällinen vastaavuus löytyy, muussa tapauksessa EPÄTOSI. Matemaattisissa operaatioissa Excel käyttää arvoa TRUE as 1, ja EPÄTOSI 0Edelleen SUMPRODUCT kertoo nämä luvut ja summaa tulokset.
Nollia ei lasketa, koska kerrottuna ne antavat aina 0. Katsotaanpa tarkemmin, mitä tapahtuu, kun sarakkeessa on tarkka haku A löytyi ja palautettiin 1… Toiminto SUMPRODUCT kertoo sarakkeen luvun B on 1 ja palauttaa tuloksen – täsmälleen sama numero! Tämä johtuu siitä, että muiden tuotteiden tulokset ovat nolla, eivätkä ne vaikuta tulokseen saatuun summaan.
Valitettavasti toiminto SUMPRODUCT ei voi toimia tekstiarvojen ja päivämäärien kanssa, koska niitä ei voi kertoa. Tässä tapauksessa saat virheilmoituksen #ARVO! (#ARVO!) kuten solussa F4 alla olevassa kuvassa:
RAJOITUKSET: Palauttaa vain numeeriset arvot.
INDEX + MATCH – kirjainkoon erottelu minkä tahansa tietotyypin haku
Lopuksi olemme lähellä rajatonta ja kirjainkoolla herkkää hakukaavaa, joka toimii minkä tahansa tietojoukon kanssa.
Tämä esimerkki tulee viimeisenä, ei siksi, että paras jää jälkiruoaksi, vaan koska aiemmista esimerkeistä saadut tiedot auttavat sinua ymmärtämään isot ja pienet kirjaimet paremmin ja nopeammin. INDEKSI+OTUMINEN (INDEKSI+VASTAA).
Kuten luultavasti arvasit, toimintojen yhdistelmä LISÄTIETOJA и INDEX käytetään Excelissä joustavampana ja tehokkaampana vaihtoehtona VPR. Artikkeli INDEXin ja MATCHin käyttäminen VLOOKUPin sijaan selittää täydellisesti, kuinka nämä funktiot toimivat yhdessä.
Kerron vain tärkeimmät kohdat:
- Toiminto LISÄTIETOJA (MATCH) etsii arvoa tietyltä alueelta ja palauttaa sen suhteellisen sijainnin, eli rivin ja/tai sarakkeen numeron;
- Seuraavaksi toiminto INDEX (INDEX) palauttaa arvon määritetystä sarakkeesta ja/tai rivistä.
Kaavaan INDEKSI+OTUMINEN voi etsiä isot ja pienet kirjaimet, sinun tarvitsee vain lisätä siihen yksi funktio. Ei ole vaikea arvata, mikä se taas on TARKKA (TARKKA):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
Tässä kaavassa TARKKA toimii samalla tavalla kuin toiminnon yhteydessä KATSOja antaa saman tuloksen:
Huomaa, että kaava INDEKSI+OTUMINEN aaltosulkeiden sisällä on taulukkokaava, ja sinun on täydennettävä se painamalla Ctrl + Vaihto + Enter.
Miksi INDEX+MATCH on paras ratkaisu kirjainkoon erotteluun?
Paketin tärkeimmät edut INDEX и LISÄTIETOJA:
- Ei vaadi lisäpylvään lisäämistä, toisin kuin VPR.
- Ei vaadi hakusarakkeen lajittelua, toisin kuin KATSO.
- Toimii kaikentyyppisten tietojen kanssa - numerot, teksti ja päivämäärät.
Tämä kaava näyttää täydelliseltä, eikö niin? Itse asiassa se ei ole. Ja siksi.
Oletetaan, että hakuarvoon liittyvän palautusarvon sarakkeen solu on tyhjä. Minkä tuloksen kaava palauttaa? Ei? Katsotaanpa, mitä kaava itse asiassa palauttaa:
Hups, kaava palauttaa nollan! Tämä ei ehkä ole suuri ongelma, jos työskentelet puhtaiden tekstiarvojen kanssa. Kuitenkin, jos taulukko sisältää numeroita, mukaan lukien "oikeita" nollia, siitä tulee ongelma.
Itse asiassa kaikki muut hakukaavat (VLOOKUP, LOOKUP ja SUMPRODUCT), joista keskustelimme aiemmin, toimivat samalla tavalla. Mutta haluat täydellisen kaavan, eikö niin?
Tehdäksesi kaavan kirjainkoon herkkyyden INDEKSI+OTUMINEN täydellinen, laita se funktioon IF (IF), joka testaa solun palautusarvolla ja palauttaa tyhjän tuloksen, jos se on tyhjä:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
Tässä kaavassa:
- B on sarake, jolla on palautusarvot
- 1+ on luku, joka muuttaa funktion palauttaman solun suhteellisen sijainnin LISÄTIETOJA, solun todelliseen osoitteeseen. Esimerkiksi toiminnassamme LISÄTIETOJA hakutaulukko annettu A2: A7, eli solun suhteellinen sijainti A2 tulee 1, koska se on taulukon ensimmäinen. Mutta solun todellinen sijainti A2 sarakkeessa on 2, joten lisäämme 1tehdä eron ja saada funktio EPÄSUORISTA (SPIRECT) haki arvon halutusta solusta.
Alla olevissa kuvissa näkyy korjattu kirjainkoolla käytettävä kaava INDEKSI+OTUMINEN Toiminnassa. Se palauttaa tyhjän tuloksen, jos palautettu solu on tyhjä.
Kirjoitin kaavan uudelleen sarakkeiksi B:Dsopimaan kaavapalkki kuvakaappaukseen.
Formula palaa 0jos palautetussa solussa on nolla.
Jos haluat linkin INDEX и LISÄTIETOJA näyttää jonkin viestin, kun palautusarvo on tyhjä, voit kirjoittaa sen kaavan viimeisiin lainausmerkkeihin (""), esimerkiksi näin:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
