Formüllerle toplu metin değiştirme

Değişken derecelerde "doğruluk" ile ilk verilerin yazıldığı bir listeniz olduğunu varsayalım - örneğin, adresler veya şirket adları:

Aynı şehrin veya şirketin burada rengarenk varyantlarda bulunduğu açıkça görülüyor ve bu da gelecekte bu tablolarla çalışırken çok fazla sorun yaratacak. Ve biraz düşünürseniz, diğer alanlardan çok sayıda benzer görev örneği bulabilirsiniz.

Şimdi böyle çarpık verilerin size düzenli olarak geldiğini düşünün, yani bu bir kerelik bir “elle düzelt, unut gitsin” hikayesi değil, düzenli olarak ve çok sayıda hücrede yaşanan bir sorun.

Ne yapalım? "Bul ve Değiştir" kutusu aracılığıyla veya tıklayarak, çarpık metni 100500 kez doğru olanla manuel olarak değiştirmeyin. Ctrl+H?

Böyle bir durumda akla gelen ilk şey, yanlış ve doğru seçenekleri eşleştirmek için önceden derlenmiş bir referans kitabına göre toplu bir değiştirme yapmaktır - bunun gibi:

Ne yazık ki, böyle bir görevin bariz yaygınlığı ile Microsoft Excel, bunu çözmek için basit yerleşik yöntemlere sahip değildir. Başlamak için, VBA veya Power Query'de makrolar biçiminde “ağır topçu” içermeden bunu formüllerle nasıl yapacağımızı bulalım.

Durum 1. Toplu tam değiştirme

Nispeten basit bir durumla başlayalım – eski çarpık metni yenisiyle değiştirmeniz gereken bir durum. tamamen.

Diyelim ki iki tablomuz var:

İlk olarak, şirketlerin orijinal alacalı isimleri. İkincisinde - bir yazışma referans kitabı. İlk tabloda şirket adına sütundan herhangi bir kelime bulursak Bulmak, o zaman bu çarpık adı tamamen doğru olanla değiştirmeniz gerekir - sütundan Vekil ikinci arama tablosu.

Kolaylık sağlamak için:

• Her iki tablo da bir klavye kısayolu kullanılarak dinamiğe ("akıllı") dönüştürülür Ctrl+T veya takım Ekle - Tablo (Ekle — Tablo).
  
• Görünen sekmede Inşaatçı (Tasarım) adlı ilk tablo Veri, ve ikinci referans tablosu – Yerine Koyma.

Formülün mantığını anlatmak için biraz uzaktan gidelim.

Örnek olarak A2 hücresinden ilk şirketi alarak ve geri kalan şirketleri geçici olarak unutarak, sütundan hangi seçeneği belirlemeye çalışalım. Bulmak orada buluşur. Bunu yapmak için, sayfanın boş kısmındaki herhangi bir boş hücreyi seçin ve oradaki işlevi girin. BULMAK (BULMAK):

Bu işlev, verilen alt dizenin dahil edilip edilmediğini belirler (ilk argüman, sütundaki tüm değerlerdir. Bulmak) kaynak metne (veri tablosundaki ilk şirket) girmeli ve metnin bulunduğu karakterin sıra numarasını veya alt dize bulunamadıysa bir hata vermelidir.

Buradaki hile, ilk argüman olarak bir değil birkaç değer belirttiğimiz için, bu fonksiyon da sonuç olarak bir değer değil, 3 elemanlı bir dizi dönecektir. Dinamik dizileri destekleyen en son Office 365 sürümüne sahip değilseniz, bu formülü girdikten ve tıkladıktan sonra Keşfet bu diziyi sayfada göreceksiniz:

Excel'in önceki sürümlerine sahipseniz, üzerine tıkladıktan sonra Keşfet sonuç dizisinden yalnızca ilk değeri göreceğiz, yani #DEĞER hatası! (#DEĞER!).

Korkmamalısın 🙂 Aslında formülümüz çalışıyor ve formül çubuğunda girilen işlevi seçip tuşuna basarsanız yine de tüm sonuç dizisini görebilirsiniz. F9(basmayı unutmayın Escformüle geri dönmek için):

Ortaya çıkan sonuç dizisi, orijinal çarpık şirket adında (GK Morozko OAO) bir sütundaki tüm değerlerin Bulmak sadece ikinciyi buldum (Morozko), ve arka arkaya 4. karakterden başlayarak.

Şimdi formülümüze bir fonksiyon ekleyelim VIEW(YUKARI BAK):

Bu işlevin üç argümanı vardır:

1. İstenen değer – yeterince büyük herhangi bir sayı kullanabilirsiniz (asıl nokta, kaynak verilerdeki herhangi bir metnin uzunluğunu aşmasıdır)
   
2. Görüntülenen_vektör – istenen değeri aradığımız aralık veya dizi. İşte daha önce tanıtılan fonksiyon BULMAK{#DEĞER!:4:#DEĞER!} dizisini döndüren ,
   
3. Vektör_Sonuçlar – ilgili hücrede istenen değer bulunursa, değeri döndürmek istediğimiz aralık. İşte sütundan doğru isimler Vekil referans tablomuz.
   

Buradaki ana ve belirgin olmayan özellik, fonksiyonun VIEW tam eşleşme yoksa, her zaman en yakın en küçük (önceki) değeri arar. Bu nedenle herhangi bir yüksek sayıyı (örneğin 9999) istenilen değer olarak belirleyerek zorlayacağız. VIEW {#DEĞER!:4:#DEĞER!} dizisindeki en küçük (4) numaralı hücreyi bulun ve sonuç vektöründen karşılık gelen değeri döndürün, yani sütundan doğru şirket adı Vekil.

İkinci nüans, teknik olarak formülümüzün bir dizi formülü olmasıdır, çünkü fonksiyon BULMAK sonuç olarak bir değil, üç değerden oluşan bir dizi döndürür. Ama işlevinden beri VIEW kutudan çıkan dizileri destekler, o zaman bu formülü klasik bir dizi formülü olarak girmemiz gerekmez – klavye kısayolu kullanarak Ctrl+vardiya+Keşfet. Basit bir tane yeterli olacaktır Keşfet.

Bu kadar. Umarım mantığı anlarsın.

Bitmiş formülü sütunun ilk B2 hücresine aktarmak için kalır. Sabit – ve görevimiz çözüldü!

Tabii ki, sıradan (akıllı değil) tablolarla, bu formül de harika çalışıyor (sadece anahtarı unutmayın) F4 ve ilgili bağlantıların sabitlenmesi):

Durum 2. Toplu kısmi değiştirme

Bu dava biraz daha zor. Yine iki “akıllı” tablomuz var:

Düzeltilmesi gereken çarpık yazılmış adreslerin bulunduğu ilk tablo (Ben aradım Data2). İkinci tablo, adres içindeki bir alt dizenin kısmi olarak değiştirilmesini gerektiren bir referans kitabıdır (bu tabloyu aradım Değişiklikler2).

Buradaki temel fark, orijinal verinin yalnızca bir parçasını değiştirmeniz gerektiğidir - örneğin, ilk adres yanlıştır. "St. Petersburg” sağda "St. Petersburg”, adresin geri kalanını (posta kodu, sokak, ev) olduğu gibi bırakarak.

Bitmiş formül şöyle görünecek (algılama kolaylığı için, onu kullanarak kaç satıra böldüm? Ara Toplam+Keşfet):

Buradaki ana çalışma, standart Excel metin işlevi tarafından yapılır. VEKİL (VEKİL)3 argümanı olan:

1. Kaynak metin – Adres sütunundaki ilk çarpık adres
2. Aradığımız şey - burada işlevle hileyi kullanıyoruz VIEW (YUKARI BAK)değeri sütundan çekmenin önceki yolundan Bulmak, eğri bir adreste bir parça olarak dahil edilir.
3. Ne ile değiştirilecek - aynı şekilde sütundan ona karşılık gelen doğru değeri buluyoruz Vekil.

Bu formülü şununla girin: Ctrl+vardiya+Keşfet aslında bir dizi formülü olmasına rağmen burada da gerekli değildir.

Ve açıkça görülüyor ki (önceki resimdeki #YOK hatalarına bakın), tüm zarafetine rağmen böyle bir formülün birkaç dezavantajı vardır:

• işlev SUBSTITUTE, büyük/küçük harfe duyarlıdır, bu nedenle sondan bir önceki satırdaki “Spb” değiştirme tablosunda bulunamadı. Bu sorunu çözmek için işlevi kullanabilirsiniz. ZAMENİT (YER DEĞİŞTİRMEK)veya ön olarak her iki tabloyu da aynı kayıt defterine getirin.
• Metin başlangıçta doğruysa veya içindeyse değiştirilecek parça yok (son satır), sonra formülümüz bir hata verir. Bu an, işlevi kullanarak hataları yakalayıp değiştirerek etkisiz hale getirilebilir. EĞERHATA (EĞERHATA):

  

• Orijinal metin içeriyorsa aynı anda dizinden birkaç parça, o zaman formülümüz yalnızca sonuncunun yerini alır (8. satırda, Ligovsky «Cadde« olarak değiştirildi "pr-t", Fakat "S-Pb" on "St. Petersburg” artık yok çünkü “S-Pb” dizinde daha yüksektir). Bu sorun, kendi formülümüzü yeniden çalıştırarak çözülebilir, ancak zaten sütun boyunca Sabit:

  
  

Yerlerde mükemmel ve hantal değil, ancak aynı manuel değiştirmeden çok daha iyi, değil mi? 🙂

PS

Bir sonraki makalede, makrolar ve Power Query kullanarak böyle bir toplu değiştirmenin nasıl uygulanacağını anlayacağız.

• DEĞİŞTİR işlevi metni değiştirmek için nasıl çalışır?
  
• EXACT İşlevini Kullanarak Tam Metin Eşleşmelerini Bulma
• Büyük/küçük harf duyarlı arama ve değiştirme (büyük/küçük harfe duyarlı DÜŞEYARA)