İçerik
Metni birkaç hücreden bir hücreye nasıl hızlı bir şekilde yapıştırabileceğinizi ve bunun tersine uzun bir metin dizesini bileşenlere nasıl ayrıştırabileceğinizi zaten yazdım. Şimdi yakın ama biraz daha karmaşık bir göreve bakalım - belirli bir koşul karşılandığında birkaç hücreden metnin nasıl yapıştırılacağı.
Diyelim ki, bir şirket adının çalışanlarının birkaç farklı e-postasına karşılık gelebileceği bir müşteri veritabanımız var. Görevimiz, örneğin müşteriler için bir posta listesi yapmak, yani şöyle bir çıktı almak için tüm adresleri şirket adlarına göre toplamak ve bunları (virgül veya noktalı virgülle ayırarak) birleştirmek.
Başka bir deyişle, metni duruma göre yapıştıracak (bağlayacak) bir araca ihtiyacımız var - işlevin bir analogu SÜMMESLİ (TOPLA), ancak metin için.
Yöntem 0. Formül
Çok zarif değil, ama en kolay yol. Bir sonraki satırdaki şirketin bir öncekinden farklı olup olmadığını kontrol edecek basit bir formül yazabilirsiniz. Farklı değilse, sonraki adresi virgülle ayırarak yapıştırın. Farklıysa, tekrar başlayarak birikenleri “sıfırlarız”:
Bu yaklaşımın dezavantajları açıktır: elde edilen ek sütunun tüm hücrelerinden, her şirket için yalnızca sonunculara ihtiyacımız var (sarı). Liste büyükse, bunları hızlı bir şekilde seçmek için işlevi kullanarak başka bir sütun eklemeniz gerekir. DLSTR (UZUNLUK), birikmiş dizelerin uzunluğunu kontrol etmek:
Artık bunları filtreleyebilir ve daha fazla kullanım için gerekli adres yapıştırmayı kopyalayabilirsiniz.
Yöntem 1. Bir koşulla yapıştırmanın makro işlevi
Orijinal liste şirkete göre sıralanmazsa, yukarıdaki basit formül çalışmaz, ancak VBA'da küçük bir özel işlevle kolayca dolaşabilirsiniz. Bir klavye kısayoluna basarak Visual Basic Düzenleyicisini açın Alt + F11 veya düğmesini kullanarak Visual Basic çıkıntı geliştirici (Geliştirici). Açılan pencerede menüden yeni bir boş modül ekleyin Ekle – Modül ve fonksiyonumuzun metnini buraya kopyalayın:
MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " yapıştırmalar birbirine eşit değil - hata ile çıkıyoruz If SearchRange.Count <> TextRange.Count Sonra MergeIf = CVErr(xlErrRef) Exit Function End If 'tüm hücreleri gözden geçirin, koşulu kontrol edin ve i = 1 To SearchRange için OutText değişkenindeki metni toplayın. Cells.Count If SearchRange.Cells(i) Koşulu Seviyor Sonra OutText = OutText & TextRange.Cells(i) & Delimeter Sonraki i 'son sınırlayıcı olmadan sonuçları göster MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End işlev
Şimdi Microsoft Excel'e dönerseniz, işlevler listesinde (düğme fx formül çubuğunda veya sekmesinde Formüller – İşlev Ekle) fonksiyonumuzu bulmak mümkün olacak Birleştir kategoride Kullanıcı Tanımlı (Kullanıcı tanımlı). İşlevin argümanları aşağıdaki gibidir:
Yöntem 2. Metni kesin olmayan koşula göre birleştir
Makromuzun 13. satırındaki ilk karakteri değiştirirsek = yaklaşık eşleşme operatörüne Facebok sayfasını beğenin :, o zaman ilk verilerin seçim kriteri ile tam olmayan eşleşmesi ile yapıştırma yapmak mümkün olacaktır. Örneğin, şirket ismi farklı varyantlarda yazılabilirse, hepsini tek bir fonksiyonla kontrol edip toplayabiliriz:
Standart joker karakterler desteklenir:
- yıldız işareti (*) - herhangi bir sayıda karakteri belirtir (yoklukları dahil)
- soru işareti (?) – herhangi bir tek karakter anlamına gelir
- pound işareti (#) – herhangi bir rakam (0-9) anlamına gelir
Varsayılan olarak, Like operatörü büyük/küçük harfe duyarlıdır, yani örneğin "Orion" ve "orion" kelimelerini farklı şirketler olarak anlar. Durumu yok saymak için, Visual Basic düzenleyicisinde modülün en başına satırı ekleyebilirsiniz. Seçenek Metni Karşılaştır, bu, Beğeni'yi büyük/küçük harfe duyarlı olmayacak şekilde değiştirecektir.
Bu şekilde, koşulları kontrol etmek için çok karmaşık maskeler oluşturabilirsiniz, örneğin:
- ?1##??777RUS – 777 ile başlayan 1 bölgesinin tüm plakalarının seçimi
- LLC* – adı LLC ile başlayan tüm şirketler
- ##7## – üçüncü hanenin 7 olduğu beş haneli dijital koda sahip tüm ürünler
- ????? – beş harfli tüm isimler vb.
Yöntem 3. Metni iki koşulda yapıştırmak için makro işlevi
Çalışmada, metni birden fazla koşula bağlamanız gerektiğinde bir sorun olabilir. Örneğin, bir önceki tablomuzda şehir ile bir sütun daha eklendiğini ve yapıştırma işleminin sadece belirli bir şirket için değil, belirli bir şehir için de yapılması gerektiğini düşünelim. Bu durumda, fonksiyonumuza başka bir aralık kontrolü ekleyerek biraz modernize edilmesi gerekecektir:
MergeIfs(Aralık Olarak Metin Aralığı, Aralık Olarak Arama Aralığı1, Dizge Olarak Koşul1, Aralık Olarak Arama Aralığı2, Dizge Olarak Koşul2) Sınırlayıcıyı Dizge Olarak Dim, i Uzun Sınırlayıcı Olarak = ", " ' sınırlayıcı karakterler (boşluk veya ; vb. ile değiştirilebilir) e.) 'doğrulama ve yapıştırma aralıkları birbirine eşit değilse, bir hatayla çıkın If SearchRange1.Count <> TextRange.Count Veya SearchRange2.Count <> TextRange.Count Sonra MergeIfs = CVErr(xlErrRef) Exit Function End If 'tüm hücreleri gözden geçirin, tüm koşulları kontrol edin ve metni OutText değişkeninde toplayın For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 Ve SearchRange2.Cells(i) = Condition2 Ardından OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'son sınırlayıcı olmadan sonuçları görüntüle MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Tam olarak aynı şekilde uygulanacaktır – artık sadece argümanların daha fazla belirtilmesi gerekiyor:
Yöntem 4. Power Query'de gruplama ve yapıştırma
Ücretsiz Power Query eklentisini kullanırsanız, sorunu VBA'da programlama yapmadan çözebilirsiniz. Excel 2010-2013 için buradan indirilebilir ve Excel 2016'da zaten varsayılan olarak yerleşiktir. Eylemlerin sırası aşağıdaki gibi olacaktır:
Power Query, normal tablolarla nasıl çalışacağını bilmez, bu nedenle ilk adım, tablomuzu "akıllı" bir tabloya dönüştürmektir. Bunu yapmak için seçin ve kombinasyona basın Ctrl+T veya sekmeden seçin Ana Sayfa – Tablo olarak biçimlendir (Ana Sayfa — Tablo Olarak Biçimlendir). Daha sonra görünen sekmede Inşaatçı (Tasarım) tablo adını ayarlayabilirsiniz (standarttan ayrıldım Tablo 1):
Şimdi tablomuzu Power Query eklentisine yükleyelim. Bunu yapmak için sekmede Veri (Excel 2016'ya sahipseniz) veya Power Query sekmesinde (Excel 2010-2013'e sahipseniz) Masadan (Veriler — Tablodan):
Açılan sorgu düzenleyici penceresinde, başlığa tıklayarak sütunu seçin. Firmamız ve yukarıdaki düğmeye basın grup (Gruplama Ölçütü). Gruplamada yeni sütunun adını ve işlem türünü girin – Tüm satırlar (Tüm Satırlar):
Tamam'a tıklayın ve her şirket için gruplandırılmış bir değer tablosu elde ederiz. Sonuç sütunundaki hücrelerin beyaz arka planına (metnin üzerine değil!) sol tıklarsanız tabloların içeriği açıkça görülebilir:
Şimdi bir sütun daha ekleyelim, burada işlevi kullanarak her mini tablodaki Adres sütunlarının içeriğini virgülle ayırarak yapıştırıyoruz. Bunu yapmak için sekmede Sütun ekle basarız Özel sütun (Sütun ekle — Özel sütun) ve görünen pencerede, yeni sütunun adını ve Power Query'de yerleşik M dilindeki birleştirme formülünü girin:
Tüm M işlevlerinin büyük/küçük harfe duyarlı olduğunu unutmayın (Excel'den farklı olarak). tıkladıktan sonra OK yapıştırılmış adresleri olan yeni bir sütun alıyoruz:
Zaten gereksiz sütunu kaldırmak için kalır TabloAdresleri (başlığa sağ tıklayın) Sütunu sil) ve sonuçları sekmeye tıklayarak sayfaya yükleyin Ana Sayfa — Kapatın ve indirin (Ana Sayfa — Kapat ve yükle):
Önemli nüans: Önceki yöntemlerin (işlevlerin) aksine, Power Query'deki tablolar otomatik olarak güncellenmez. Gelecekte kaynak verilerde herhangi bir değişiklik olacaksa, sonuçlar tablosunda herhangi bir yere sağ tıklayıp komutu seçmeniz gerekecektir. Güncelle ve Kaydet (Yenile).
- Uzun bir metin dizesi nasıl parçalara bölünür
- Farklı hücrelerdeki metni tek bir hücreye yapıştırmanın birkaç yolu
- Metni bir maskeye karşı test etmek için Beğen operatörünü kullanma