Формула на Excel: Съвпадение на дълъг текст -

Обща формула

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

Обобщение

За да съчетаете текст по-дълъг от 255 знака с функцията MATCH, можете да използвате функциите LEFT, MID и EXACT, за да анализирате и сравните текста, както е обяснено по-долу. В показания пример формулата в G5 е:

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

където данните са наименования диапазон B5: B15.

Забележка: тази формула извършва сравнение с малки и големи букви.

Обяснение

Функцията MATCH има ограничение от 255 знака за справочната стойност. Ако се опитате да използвате по-дълъг текст, MATCH ще върне грешка #VALUE.

За да заобиколите това ограничение, можете да използвате логическа логика и функциите LEFT, MID и EXACT за анализиране и сравняване на текст.

Низът, с който тестваме в клетка E5, е 373 знака, както следва:

Lorem ipsum dolor amet постави птица в него, доверителен фонд за списъци, права за велосипеди с еднорог, които вероятно не сте чували за тях. Фураж helvetica crusty semiotics всъщност реликва. Tumblr poutine unicorn godard опитайте усилено, преди да разпродадете нарвал медитация кич жилетка fixie twee буквално качулка ретро. Пратеник чанта ад на хрупкав зелен сок занаятчия.

В основата си това е просто формула MATCH, настроена да търси 1 в режим на точно съвпадение:

=MATCH(1,array,0)

Масивът във формулата по-горе съдържа само 1s и 0s, а 1s представляват съвпадащ текст. Този масив е конструиран от следния израз:

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

Самият този израз има две части. Вляво имаме:

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

Тук функцията LEFT извлича първите 255 знака от E5 и от всички клетки в посочените данни за обхвата (B5: B15). Тъй като данните съдържат 11 текстови низа, LEFT ще генерира 11 резултата.

След това функцията EXACT сравнява единичния низ от E5 с всичките 11 низа, върнати от LEFT. EXACT връща 11 резултата в масив като този:

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

Вдясно имаме друг израз:

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

Това е точно същият подход, който се използва с LEFT, но тук използваме функцията MID, за да извлечем следващите 255 знака текст. Функцията EXACT отново връща 11 резултата:

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

Когато двата масива по-горе се умножат един по друг, математическата операция принуждава стойностите TRUE FALSE на 1s и 0s. Следвайки правилата на булевата аритметика, резултатът е масив като този:

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

който се връща директно на MATCH като справочен масив. Формулата вече може да бъде разрешена до:

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

Функцията MATCH изпълнява точно съвпадение и връща краен резултат от 10, който представлява десетия текстов низ в B5: B15.

Забележка: дължината на текста, показана в примера, се изчислява с функцията LEN. Появява се само за справка.

Опция без чувствителност към главни букви

Функцията EXACT е чувствителна към малки и големи букви, така че формулата по-горе ще зачита регистъра.

За да изпълните съвпадение без регистър с дълъг текст, използвате функциите ISNUMBER и SEARCH, както следва:

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

Цялостната структура на тази формула е идентична с примера по-горе, но функцията SEARCH се използва вместо EXACT за сравняване на текст (обяснен подробно тук).

За разлика от EXACT, функцията SEARCH поддържа и заместващи символи.

С XMATCH

Функцията XMATCH няма същото ограничение от 255 знака като MATCH. За да извършите подобно съвпадение на дълъг текст с XMATCH, можете да използвате много по-опростената формула по-долу: