Excel formülü: Uzun metni eşleştir -

Genel formül

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

Özet

255 karakterden uzun metinleri MATCH işleviyle eşleştirmek için, aşağıda açıklandığı gibi metni ayrıştırmak ve karşılaştırmak için SOL, ORTA ve TAM işlevlerini kullanabilirsiniz. Gösterilen örnekte, G5'teki formül şöyledir:

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

burada veriler , B5: B15 olarak adlandırılan aralıktır.

Not: Bu formül, büyük / küçük harfe duyarlı bir karşılaştırma yapar.

Açıklama

KAÇINCI işlevi, arama değeri için 255 karakterlik bir sınıra sahiptir. Daha uzun bir metin kullanmayı denerseniz, KAÇINCI işlevi # DEĞER hatası verir.

Bu sınırı geçici olarak çözmek için, metni ayrıştırmak ve karşılaştırmak için boole mantığını ve SOL, ORTA ve TAM işlevlerini kullanabilirsiniz.

E5 hücresinde test ettiğimiz dize aşağıdaki gibi 373 karakterdir:

Lorem ipsum dolor amet, listicle güven fonu, tek boynuzlu at vaporware bisiklet hakları için bir kuş koydu, muhtemelen bıyıklarını duymadınız. Yem helvetica huysuz göstergebilim aslında yadigarıdır. Tumblr somurtkan tek boynuzlu at godard, deniz gergedanı meditasyon kitsch yelek fixie twee kelimenin tam anlamıyla hoodie retro satmadan önce çok deneyin. Postacı çantası huysuz yeşil meyve suyu ustası.

Özünde, bu sadece bir MATCH formülüdür, tam eşleme modunda 1'i aramak için ayarlanmış:

=MATCH(1,array,0)

Yukarıdaki formüldeki dizi yalnızca 1'ler ve 0'lar içerir ve 1'ler eşleşen metni temsil eder. Bu dizi aşağıdaki ifade ile oluşturulur:

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

Bu ifadenin kendisi iki bölümden oluşmaktadır. Sol tarafta:

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

Burada, LEFT işlevi, E5'ten ve adlandırılmış aralık verilerindeki (B5: B15) tüm hücrelerden ilk 255 karakteri çıkarır. Veriler 11 metin dizisi içerdiğinden, LEFT 11 sonuç üretecektir.

EXACT işlevi daha sonra E5'teki tek dizeyi LEFT tarafından döndürülen tüm 11 dizeyle karşılaştırır. EXACT 11 sonuç şöyle bir dizi verir:

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

Sağ tarafta başka bir ifademiz var:

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

Bu, SOL ile tamamen aynı yaklaşımdır, ancak burada, metnin sonraki 255 karakterini çıkarmak için ORTA işlevini kullanıyoruz. EXACT işlevi yine 11 sonuç döndürür:

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

Yukarıdaki iki dizi birbiriyle çarpıldığında, matematik işlemi DOĞRU YANLIŞ değerlerini 1'lere ve 0'lara zorlar. Boole aritmetiği kurallarına göre sonuç şuna benzer bir dizidir:

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

arama dizisi olarak doğrudan MATCH'a döndürülür. Formül artık şu şekilde çözülebilir:

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

KAÇINCI işlevi tam bir eşleşme gerçekleştirir ve B5: B15'teki onuncu metin dizesini temsil eden 10'un son sonucunu döndürür.

Not: Örnekte gösterilen metin uzunluğu UZUNLUK işlevi ile hesaplanır. Yalnızca referans amaçlı görünür.

Büyük / küçük harfe duyarlı olmayan seçenek

EXACT işlevi büyük / küçük harfe duyarlıdır, bu nedenle yukarıdaki formül büyük / küçük harf duyarlıdır.

Uzun metinle büyük / küçük harfe duyarlı olmayan bir eşleşme gerçekleştirmek için ISNUMBER ve SEARCH işlevlerini aşağıdaki gibi kullanırsınız:

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

Bu formülün genel yapısı yukarıdaki örnekle aynıdır, ancak metni karşılaştırmak için TAM yerine ARA işlevi kullanılır (burada ayrıntılı olarak açıklanmıştır).

KESİNTEN farklı olarak, ARAMA işlevi ayrıca joker karakterleri de destekler.

XMATCH ile

XMATCH işlevi, MATCH ile aynı 255 karakter sınırına sahip değildir. XMATCH ile uzun metin üzerinde benzer bir eşleşme gerçekleştirmek için aşağıdaki çok daha basit formülü kullanabilirsiniz: