Obecný vzorec
=MATCH(1,EXACT(LEFT(A1,255),LEFT(rng,255))*EXACT(MID(A1,256,255),MID(rng,256,255)),0)
souhrn
Chcete-li porovnat text delší než 255 znaků s funkcí MATCH, můžete pomocí funkcí LEFT, MID a EXACT analyzovat a porovnat text, jak je vysvětleno níže. V zobrazeném příkladu je vzorec v G5:
=MATCH(1,EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255)),0)
kde data jsou pojmenovaný rozsah B5: B15.
Poznámka: tento vzorec provádí porovnání velkých a malých písmen.
Vysvětlení
Funkce MATCH má pro vyhledávací hodnotu limit 255 znaků. Pokud se pokusíte použít delší text, MATCH vrátí chybu #HODNOTA.
Chcete-li tento limit vyřešit, můžete k analýze a porovnání textu použít logickou logiku a funkce LEFT, MID a EXACT.
Řetězec, s nímž testujeme v buňce E5, má 373 znaků takto:
Lorem ipsum dolor amet do něj vložil ptáka listinkový svěřenecký fond, práva na jednorázová jízdní kola unicorn, o kterých jste pravděpodobně neslyšeli knír. Krmivo helvetica křupavá semiotika ve skutečnosti dědictví. Tumblr poutine jednorožec godard vyzkoušet, než vyprodali narvalní meditační kýč vestu fixie twee doslova mikina retro. Messenger taška pekelná křupavá zelená šťáva řemeslník.
V jádru se jedná pouze o MATCH vzorec, který je nastaven tak, aby hledal 1 v režimu přesné shody:
=MATCH(1,array,0)
Pole ve výše uvedeném vzorci obsahuje pouze 1 s a 0 s a 1 s představují odpovídající text. Toto pole je konstruováno následujícím výrazem:
EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255))
Samotný tento výraz má dvě části. Vlevo máme:
EXACT(LEFT(E5,255),LEFT(data,255)) // compare first 255 chars
Zde funkce LEFT extrahuje prvních 255 znaků z E5 a ze všech buněk v pojmenovaných datech rozsahu (B5: B15). Protože data obsahují 11 textových řetězců, LEFT vygeneruje 11 výsledků.
Funkce EXACT poté porovná jeden řetězec z E5 se všemi 11 řetězci vrácenými LEFT. PŘESNÉ vrátí 11 výsledků v poli, jako je tento:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
Vpravo máme další výraz:
EXACT(MID(E5,256,255),MID(data,256,255) // compare next 255 chars
Jedná se přesně o stejný přístup, jaký se používá u LEFT, ale zde použijeme funkci MID k extrahování dalších 255 znaků textu. Funkce EXACT opět vrací 11 výsledků:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
Když jsou dvě výše uvedená pole navzájem vynásobena, matematická operace vynutí hodnoty TRUE FALSE na 1 s a 0 s. Podle pravidel booleovské aritmetiky je výsledkem pole jako toto:
(0;0;0;0;0;0;0;0;0;1;0)
který je vrácen přímo do MATCH jako vyhledávací pole. Vzorec lze nyní vyřešit na:
=MATCH(1,(0;0;0;0;0;0;0;0;0;1;0),0)
Funkce MATCH provede přesnou shodu a vrátí konečný výsledek 10, což představuje desátý textový řetězec v B5: B15.
Poznámka: délka textu uvedená v příkladu se počítá pomocí funkce LEN. Zobrazuje se pouze pro informaci.
Možnost nerozlišuje velká a malá písmena
Funkce EXACT rozlišuje velká a malá písmena, takže výše uvedený vzorec bude respektovat velká a malá písmena.
Chcete-li provést shodu bez rozlišování malých a velkých písmen s dlouhým textem, použijte funkce ISNUMBER a SEARCH následujícím způsobem:
=MATCH(1,ISNUMBER(SEARCH(LEFT(E5,255),LEFT(data,255)))*ISNUMBER(SEARCH(MID(E5,256,255),MID(data,256,255))),0)
Celková struktura tohoto vzorce je stejná jako v předchozím příkladu, ale k porovnání textu se místo funkce EXACT použije funkce SEARCH (podrobně vysvětleno zde).
Na rozdíl od PŘESNÉ funkce SEARCH také podporuje zástupné znaky.
S XMATCH
Funkce XMATCH nemá stejný limit 255 znaků jako MATCH. Chcete-li provést podobnou shodu na dlouhém textu s XMATCH, můžete použít mnohem jednodušší vzorec níže:
=XMATCH(E5,data)
Poznámka: XMATCH podporuje zástupné znaky, ale nerozlišuje velká a malá písmena.
