Formuła ogólna
=MATCH(1,EXACT(LEFT(A1,255),LEFT(rng,255))*EXACT(MID(A1,256,255),MID(rng,256,255)),0)
Podsumowanie
Aby dopasować tekst dłuższy niż 255 znaków za pomocą funkcji PODAJ.POZYCJĘ, możesz użyć funkcji LEWO, ŚRODEK i DOKŁADNE do analizowania i porównywania tekstu, jak wyjaśniono poniżej. W pokazanym przykładzie formuła w G5 to:
=MATCH(1,EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255)),0)
gdzie dane to nazwany zakres B5: B15.
Uwaga: ta formuła przeprowadza porównanie uwzględniające wielkość liter.
Wyjaśnienie
Funkcja PODAJ.POZYCJĘ ma ograniczenie do 255 znaków wartości wyszukiwania. Jeśli spróbujesz użyć dłuższego tekstu, PODAJ.POZYCJĘ zwróci błąd #ARG.
Aby obejść ten limit, możesz użyć logiki boolowskiej oraz funkcji LEFT, MID i EXACT do analizowania i porównywania tekstu.
Ciąg, który testujemy w komórce E5, ma 373 znaki w następujący sposób:
Lorem ipsum dolor amet umieścił na nim ptaka fundusz powierniczy listy, prawa rowerowe jednorożca do waporyzacji, o których prawdopodobnie nie słyszałeś. Forage helvetica crusty semiiotics właściwie dziedzictwo. Tumblr poutine unicorn godard try-hard, zanim sprzedali narwal medytacja kicz kamizelka fixie twee dosłownie bluza z kapturem retro. Listonoszka Hell of Crusty Green Juice Artisan.
Zasadniczo jest to po prostu formuła DOPASUJ, skonfigurowana do wyszukiwania 1 w trybie dopasowania ścisłego:
=MATCH(1,array,0)
Tablica w powyższym wzorze zawiera tylko jedynki i zera, a jedynki reprezentują pasujący tekst. Ta tablica jest tworzona za pomocą następującego wyrażenia:
EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255))
Samo wyrażenie składa się z dwóch części. Po lewej mamy:
EXACT(LEFT(E5,255),LEFT(data,255)) // compare first 255 chars
W tym przypadku funkcja LEFT wyodrębnia pierwsze 255 znaków z E5 i ze wszystkich komórek w nazwanym zakresie danych (B5: B15). Ponieważ dane zawierają 11 ciągów tekstowych, LEFT wygeneruje 11 wyników.
Następnie funkcja EXACT porównuje pojedynczy ciąg z E5 ze wszystkimi 11 ciągami zwracanymi przez LEFT. PORÓWNANIE zwraca 11 wyników w następującej tablicy:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
Po prawej mamy inne wyrażenie:
EXACT(MID(E5,256,255),MID(data,256,255) // compare next 255 chars
Jest to dokładnie to samo podejście, co w przypadku LEFT, ale tutaj używamy funkcji MID do wyodrębnienia kolejnych 255 znaków tekstu. Funkcja PORÓWNAJ ponownie zwraca 11 wyników:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
Kiedy dwie powyższe tablice są mnożone przez siebie, operacja matematyczna przekształca wartości TRUE FALSE na 1 i 0. Zgodnie z zasadami arytmetyki boolowskiej, wynikiem jest tablica taka:
(0;0;0;0;0;0;0;0;0;1;0)
która jest zwracana bezpośrednio do MATCH jako tablica wyszukiwania. Wzór można teraz przekształcić w:
=MATCH(1,(0;0;0;0;0;0;0;0;0;1;0),0)
Funkcja PODAJ.POZYCJĘ wykonuje dokładne dopasowanie i zwraca końcowy wynik równy 10, który reprezentuje dziesiąty ciąg tekstowy w B5: B15.
Uwaga: długość tekstu pokazana w przykładzie jest obliczana za pomocą funkcji DŁ. Wydaje się tylko w celach informacyjnych.
Opcja bez rozróżniania wielkości liter
Funkcja EXACT rozróżnia wielkość liter, więc powyższa formuła będzie uwzględniać wielkość liter.
Aby przeprowadzić dopasowanie bez rozróżniania wielkości liter w długim tekście, użyj funkcji ISNUMBER i SEARCH w następujący sposób:
=MATCH(1,ISNUMBER(SEARCH(LEFT(E5,255),LEFT(data,255)))*ISNUMBER(SEARCH(MID(E5,256,255),MID(data,256,255))),0)
Ogólna struktura tej formuły jest identyczna jak w powyższym przykładzie, ale funkcja SZUKAJ jest używana zamiast DOKŁADNE do porównywania tekstu (wyjaśniono szczegółowo tutaj).
W przeciwieństwie do EXACT funkcja SEARCH obsługuje również symbole wieloznaczne.
Dzięki XMATCH
Funkcja XMATCH nie ma takiego samego limitu 255 znaków jak PODAJ.POZYCJĘ. Aby wykonać podobne dopasowanie na długim tekście za pomocą XMATCH, możesz użyć znacznie prostszej formuły poniżej:
=XMATCH(E5,data)
Uwaga: XMATCH obsługuje symbole wieloznaczne, ale nie rozróżnia wielkości liter.
