Yleinen kaava
=MATCH(1,EXACT(LEFT(A1,255),LEFT(rng,255))*EXACT(MID(A1,256,255),MID(rng,256,255)),0)
Yhteenveto
Jos haluat sovittaa yli 255 merkin pituisen tekstin MATCH-funktioon, voit käyttää VASEN, MID ja TARKKA funktioita jäsentää ja verrata tekstiä alla kuvatulla tavalla. Esitetyssä esimerkissä kaava G5: ssä on:
=MATCH(1,EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255)),0)
missä data on nimetty alue B5: B15.
Huomaa: tämä kaava suorittaa kirjainkoon huomioon ottavan vertailun.
Selitys
MATCH-toiminnolla on raja-arvo 255 merkkiä hakuarvolle. Jos yrität käyttää pidempää tekstiä, MATCH palauttaa #ARVO -virheen.
Voit kiertää tämän rajan käyttämällä loogista logiikkaa ja LEFT-, MID- ja EXACT-funktioita jäsentää ja verrata tekstiä.
Merkkijono, jota testaamme solussa E5, on 373 merkkiä seuraavasti:
Lorem ipsum dolor amet laittoi siihen linnun listicle trust -rahastoon, yksisarvisen vaporware-polkupyörän oikeuksiin, joita et todennäköisesti ole kuullut niistä viiksistä. Forage helvetica -kuoren semiootin perintö. Tumblr poutine yksisarvinen godard kovaa vauhtia ennen kuin ne myytiin loppuun narwhal-meditaatio-kitsch-liivi fixie twee kirjaimellisesti huppari retro. Lähettilaukku on karkean vihreän mehun käsityöläinen.
Ytimessä tämä on vain MATCH-kaava, joka on määritetty etsimään yhtä tarkassa vastaavuustilassa:
=MATCH(1,array,0)
Yllä olevan kaavan taulukko sisältää vain 1: n ja 0: n, ja 1: t edustavat vastaavaa tekstiä. Tämä taulukko on muodostettu seuraavalla lausekkeella:
EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255))
Tällä lausekkeella on kaksi osaa. Vasemmalla meillä on:
EXACT(LEFT(E5,255),LEFT(data,255)) // compare first 255 chars
Tässä VASEN-funktio poimii ensimmäiset 255 merkkiä E5: stä ja kaikista nimettyjen alueiden tietojen soluista (B5: B15). Koska tiedot sisältävät 11 tekstimerkkijonoa, VASEN tuottaa 11 tulosta.
EXACT-funktio vertaa sitten E5: n yksittäistä merkkijonoa kaikkiin VASEN palauttamiin 11 merkkijonoon. EXACT palauttaa 11 tulosta tällaisessa taulukossa:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
Oikealla meillä on toinen ilmaisu:
EXACT(MID(E5,256,255),MID(data,256,255) // compare next 255 chars
Tämä on täsmälleen sama lähestymistapa kuin LEFT: n kanssa, mutta tässä käytetään MID-toimintoa seuraavien 255 merkin tekstin purkamiseen. EXACT-toiminto palauttaa jälleen 11 tulosta:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
Kun yllä olevat kaksi taulukkoa kerrotaan keskenään, matemaattinen operaatio pakottaa TOSI EPÄTOSI -arvot 1: ksi ja 0: ksi. Boolen aritmeettisten sääntöjen mukaisesti tulos on tällainen taulukko:
(0;0;0;0;0;0;0;0;0;1;0)
joka palautetaan suoraan MATCH: iin hakutaulukkona. Kaava voidaan nyt ratkaista seuraavasti:
=MATCH(1,(0;0;0;0;0;0;0;0;0;1;0),0)
MATCH-funktio suorittaa tarkan haun ja palauttaa lopputuloksen 10, joka edustaa B5: B15: n kymmenes tekstimerkkijonoa.
Huomaa: Esimerkissä esitetty tekstin pituus lasketaan LEN-toiminnolla. Se ilmestyy vain viitteeksi.
Kirjainkoon erottamaton vaihtoehto
EXACT-funktio erottaa kirjainkoon, joten yllä oleva kaava ottaa huomioon kirjainkoon.
Voit tehdä isoilla ja pienillä kirjaimilla merkitsemättömän vastaavuuden käyttämällä ISNUMBER- ja SEARCH-toimintoja seuraavasti:
=MATCH(1,ISNUMBER(SEARCH(LEFT(E5,255),LEFT(data,255)))*ISNUMBER(SEARCH(MID(E5,256,255),MID(data,256,255))),0)
Tämän kaavan kokonaisrakenne on identtinen yllä olevan esimerkin kanssa, mutta HAKU-funktiota käytetään TARKAN sijasta tekstin vertailemiseen (selitetty yksityiskohtaisesti tässä).
Toisin kuin EXACT, SEARCH-toiminto tukee myös jokerimerkkejä.
XMATCH: n kanssa
XMATCH-toiminnolla ei ole samaa 255 merkkirajoitusta kuin MATCH. Voit suorittaa samanlaisen vastaavuuden pitkällä tekstillä XMATCH: lla käyttämällä alla olevaa paljon yksinkertaisempaa kaavaa:
=XMATCH(E5,data)
Huomaa: XMATCH tukee jokerimerkkejä, mutta se ei eroa isoja ja pieniä kirjaimia.
