Загальна формула
=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 тві буквально балахон ретро. Сумка-сумочка пекло хрусткого зеленого соку ремісника.
По суті, це просто формула MATCH, налаштована на пошук 1 в режимі точного збігу:
=MATCH(1,array,0)
Масив у наведеній вище формулі містить лише 1 і 0, а 1 представляє відповідний текст. Цей масив побудований за таким виразом:
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)
Загальна структура цієї формули ідентична наведеному вище прикладу, але для порівняння тексту замість EXACT використовується функція SEARCH (докладно пояснена тут).
На відміну від EXACT, функція SEARCH також підтримує символи підстановки.
За допомогою XMATCH
Функція XMATCH не має такого ж обмеження в 255 символів, як MATCH. Щоб виконати подібний збіг із довгим текстом за допомогою XMATCH, можна скористатися набагато простішою формулою нижче:
=XMATCH(E5,data)
Примітка: XMATCH підтримує символи підстановки, але не враховує регістр.
