Formula Excel: Potriviți textul lung -

Formula generică

=MATCH(1,EXACT(LEFT(A1,255),LEFT(rng,255))*EXACT(MID(A1,256,255),MID(rng,256,255)),0)

rezumat

Pentru a asocia textul mai lung de 255 de caractere cu funcția MATCH, puteți utiliza funcțiile LEFT, MID și EXACT pentru a analiza și compara textul, după cum se explică mai jos. În exemplul prezentat, formula din G5 este:

=MATCH(1,EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255)),0)

unde datele sunt intervalul numit B5: B15.

Notă: această formulă efectuează o comparație sensibilă la majuscule.

Explicaţie

Funcția MATCH are o limită de 255 de caractere pentru valoarea de căutare. Dacă încercați să utilizați text mai lung, MATCH va returna o eroare #VALUE.

Pentru a rezolva această limită, puteți utiliza logica booleană și funcțiile LEFT, MID și EXACT pentru a analiza și compara textul.

Șirul cu care testăm în celula E5 are 373 de caractere după cum urmează:

Lorem ipsum dolor amet a pus o pasăre pe el fond de încredere pentru articole, drepturi de biciclete unicorn vaporware probabil nu ai auzit de ele mustață. Semiotica crustă a furajelor helvetica este de fapt o moștenire. Tumblr poutine unicorn godard încearcă înainte să vândă meditația narvală kitsch vestă fixie twee literalmente cu glugă retro. Geantă de mesagerie iad de artizanat cu suc verde crust.

În esență, aceasta este doar o formulă MATCH, configurată pentru a căuta 1 în modul de potrivire exactă:

=MATCH(1,array,0)

Matricea din formula de mai sus conține doar 1s și 0s, iar 1s reprezintă textul potrivit. Această matrice este construită prin următoarea expresie:

EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255))

Această expresie în sine are două părți. În stânga avem:

EXACT(LEFT(E5,255),LEFT(data,255)) // compare first 255 chars

Aici, funcția STÂNGA extrage primele 255 de caractere din E5 și din toate celulele din intervalul de date numit (B5: B15). Deoarece datele conțin 11 șiruri de text, LEFT va genera 11 rezultate.

Funcția EXACT compară apoi șirul unic de la E5 cu toate cele 11 șiruri returnate de LEFT. EXACT returnează 11 rezultate într-o matrice ca aceasta:

(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)

În dreapta, avem o altă expresie:

EXACT(MID(E5,256,255),MID(data,256,255) // compare next 255 chars

Aceasta este exact aceeași abordare utilizată cu LEFT, dar aici folosim funcția MID pentru a extrage următoarele 255 de caractere de text. Funcția EXACT returnează din nou 11 rezultate:

(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)

Când cele două tablouri de mai sus sunt înmulțite una cu cealaltă, operația matematică constrânge valorile TRUE FALSE în 1s și 0s. Urmând regulile aritmeticii booleene, rezultatul este o matrice ca aceasta:

(0;0;0;0;0;0;0;0;0;1;0)

care este returnat direct la MATCH ca matrice de căutare. Formula poate fi rezolvată acum la:

=MATCH(1,(0;0;0;0;0;0;0;0;0;1;0),0)

Funcția MATCH efectuează o potrivire exactă și returnează un rezultat final de 10, care reprezintă al zecelea șir de text în B5: B15.

Notă: lungimea textului prezentată în exemplu este calculată cu funcția LEN. Apare doar pentru referință.

Opțiune care nu distinge majuscule

Funcția EXACT este sensibilă la majuscule și minuscule, astfel încât formula de mai sus va respecta majusculele și minusculele.

Pentru a efectua o potrivire fără majuscule și minuscule cu text lung, utilizați funcțiile ISNUMBER și SEARCH după cum urmează:

=MATCH(1,ISNUMBER(SEARCH(LEFT(E5,255),LEFT(data,255)))*ISNUMBER(SEARCH(MID(E5,256,255),MID(data,256,255))),0)

Structura generală a acestei formule este identică cu exemplul de mai sus, dar funcția SEARCH este utilizată în loc de EXACT pentru a compara textul (explicat în detaliu aici).

Spre deosebire de EXACT, funcția SEARCH acceptă și metacaracterele.

Cu XMATCH

Funcția XMATCH nu are aceeași limită de 255 de caractere ca MATCH. Pentru a efectua o potrivire similară pe textul lung cu XMATCH, puteți utiliza formula mult mai simplă de mai jos: