Mündəricat
Artıq bir neçə hüceyrədən mətni birinə necə tez yapışdıra biləcəyiniz və əksinə, uzun mətn sətirini komponentlərə necə təhlil edə biləcəyiniz haqqında yazdım. İndi yaxın, lakin bir az daha mürəkkəb bir işə baxaq - müəyyən bir şərt yerinə yetirildikdə bir neçə hüceyrədən mətni necə yapışdırmaq olar.
Tutaq ki, bizdə müştərilərin məlumat bazası var, burada bir şirkət adı onun əməkdaşlarının bir neçə müxtəlif elektron poçtuna uyğun gələ bilər. Bizim vəzifəmiz, məsələn, müştərilər üçün poçt siyahısı yaratmaq, yəni aşağıdakı kimi bir nəticə əldə etmək üçün bütün ünvanları şirkət adları ilə toplamaq və onları birləşdirməkdir (vergül və ya nöqtəli vergüllə ayrılır):
Başqa sözlə, mətni şərtə uyğun yapışdıracaq (bağlayacaq) alətə ehtiyacımız var - funksiyanın analoqu. SUMMESLI (SUMIF), lakin mətn üçün.
Metod 0. Düstur
Çox zərif deyil, amma ən asan yol. Növbəti sətirdəki şirkətin əvvəlkindən fərqli olub-olmadığını yoxlayacaq sadə bir düstur yaza bilərsiniz. Fərqli deyilsə, vergüllə ayrılmış növbəti ünvanı yapışdırın. Fərqlidirsə, yenidən başlayaraq yığılmışları "sıfırlayırıq":
Bu yanaşmanın mənfi cəhətləri göz qabağındadır: əldə edilən əlavə sütunun bütün hüceyrələrindən hər bir şirkət üçün yalnız sonunculara ehtiyacımız var (sarı). Siyahı böyükdürsə, onları tez seçmək üçün funksiyadan istifadə edərək başqa bir sütun əlavə etməli olacaqsınız DLSTR (LEN), yığılmış sətirlərin uzunluğunu yoxlamaq:
İndi siz olanları süzgəcdən keçirə və sonrakı istifadə üçün lazımi ünvanı yapışdıra bilərsiniz.
Metod 1. Bir şərtlə yapışdırmanın makrofunksiyası
Orijinal siyahı şirkət tərəfindən çeşidlənməyibsə, yuxarıdakı sadə düstur işləmir, ancaq VBA-da kiçik bir xüsusi funksiya ilə asanlıqla dolaşa bilərsiniz. Klaviatura qısayoluna basaraq Visual Basic Redaktorunu açın Alt + F11 və ya düyməni istifadə edərək Visual Basic nişanı geliştirici (İnkişaf etdirici). Açılan pəncərədə menyu vasitəsilə yeni boş modul daxil edin Daxil et - Modul və funksiyamızın mətnini oraya köçürün:
Funksiya MergeIf(TextRange As Range, SearchRange As String, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " yapışdırmalar bir-birinə bərabər deyilsə - səhvlə çıxırıq Əgər SearchRange.Count <> TextRange.Count Sonra MergeIf = CVErr(xlErrRef) Çıxış Funksiyasının Sonu 'Bütün xanaları keçsəniz, vəziyyəti yoxlayın və mətni OutText For i = 1 To SearchRange dəyişənində toplayın. Cells.Count Əgər SearchRange.Cells(i) Şərt Bəyənirsə, O zaman OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'sonuncu ayırıcı olmadan nəticələr göstərir MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End funksiyası
İndi Microsoft Excel-ə qayıdırsınızsa, onda funksiyalar siyahısında (düymə fx düstur çubuğunda və ya tabda Düsturlar - Daxil etmə funksiyası) funksiyamızı tapmaq mümkün olacaq MergeIf kateqoriyasında Müəyyən edilmiş istifadəçi (Müəyyən edilmiş istifadəçi). Funksiya üçün arqumentlər aşağıdakılardır:
Metod 2. Mətni qeyri-dəqiq şərtlə birləşdirin
Makronuzun 13-cü sətirində birinci simvolu əvəz etsək = təxmini uyğunluq operatoruna Kimi, onda ilkin məlumatların seçim meyarı ilə qeyri-dəqiq uyğunluğu ilə yapışdırmaq mümkün olacaq. Məsələn, şirkət adı müxtəlif variantlarda yazıla bilərsə, onda biz onların hamısını bir funksiya ilə yoxlayıb toplaya bilərik:
Standart joker işarələr dəstəklənir:
- ulduz (*) - hər hansı bir simvolun istənilən sayını göstərir (onların olmaması da daxil olmaqla)
- sual işarəsi (?) – hər hansı bir simvolu ifadə edir
- funt işarəsi (#) - hər hansı bir rəqəmi (0-9) ifadə edir
Varsayılan olaraq, Like operatoru hərflərə həssasdır, yəni, məsələn, “Orion” və “orion”u fərqli şirkətlər kimi başa düşür. İşə məhəl qoymamaq üçün Visual Basic redaktorunda modulun ən əvvəlinə sətir əlavə edə bilərsiniz Seçim Mətni Müqayisə et, hansı ki, Bəyənməyə keçəcək.
Beləliklə, şərtləri yoxlamaq üçün çox mürəkkəb maskalar tərtib edə bilərsiniz, məsələn:
- ?1##??777RUS – 777-dən başlayaraq 1 bölgəsinin bütün nömrələrinin seçimi
- MMC* – adı MMC ilə başlayan bütün şirkətlər
- ##7## – üçüncü rəqəmin 7 olduğu beş rəqəmli rəqəmsal kodu olan bütün məhsullar
- ?????? – beş hərfin bütün adları və s.
Metod 3. Mətni iki şərtlə yapışdırmaq üçün makro funksiyası
İşdə mətni birdən çox şərtlə əlaqələndirmək lazım olduqda problem yarana bilər. Məsələn, təsəvvür edək ki, əvvəlki cədvəlimizdə şəhərlə birlikdə daha bir sütun əlavə edilib və yapışdırma yalnız müəyyən bir şirkət üçün deyil, həm də müəyyən bir şəhər üçün aparılmalıdır. Bu halda, funksiyamız ona başqa diapazon yoxlanışı əlavə etməklə bir qədər modernləşdirilməli olacaq:
MergeIfs funksiyası(Mətn diapazonu diapazon kimi, Axtarış diapazonu1 diapazon kimi, şərt1 sətir kimi, axtarış aralığı2 diapazon kimi, şərt2 sətir kimi) Dim sərhədölçən sətir kimi, i uzunluq sərhədi kimi = ", " 'ayrıcı simvollar (boşluq və ya ; s. ilə əvəz edilə bilər) e.) 'əgər doğrulama və yapışdırma diapazonları bir-birinə bərabər deyilsə, xəta ilə çıxın Əgər SearchRange1.Count <> TextRange.Count Və ya SearchRange2.Count <> TextRange.Count Sonra MergeIfs = CVERr(xlErrRef) Çıxış Funksiyasını bitir 'bütün xanaları keçin, bütün şərtləri yoxlayın və mətni OutText dəyişəninə yığın For i = 1 SearchRange1.Cells.Count Əgər SearchRange1.Cells(i) = Condition1 Və SearchRange2.Cells(i) = Condition2 Sonra OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'sonuncu ayırıcı olmadan nəticələr göstərir MergeIfs = Sol(OutText, Len(OutText) - Len(Delimeter)) End Funksiyası
O, eyni şəkildə tətbiq olunacaq – indi yalnız arqumentlər daha çox göstərilməlidir:
Metod 4. Power Query-də qruplaşdırma və yapışdırma
Pulsuz Power Query əlavəsindən istifadə etsəniz, problemi VBA-da proqramlaşdırmadan həll edə bilərsiniz. Excel 2010-2013 üçün onu buradan yükləmək olar, Excel 2016-da isə artıq standart olaraq quraşdırılmışdır. Hərəkətlərin ardıcıllığı aşağıdakı kimi olacaq:
Power Query adi cədvəllərlə işləməyi bilmir, ona görə də ilk addım masamızı “ağıllı” masaya çevirməkdir. Bunu etmək üçün onu seçin və birləşməni basın Ctrl+T və ya tabdan seçin Ev - Cədvəl kimi formatlayın (Ev - Cədvəl kimi format). Sonra görünən tabda konstruktor (Dizayn) cədvəlin adını təyin edə bilərsiniz (standartdan ayrıldım Cədvəl 1):
İndi cədvəlimizi Power Query əlavəsinə yükləyək. Bunu etmək üçün, nişanda Tarix (Excel 2016 varsa) və ya Power Query sekmesinde (Excel 2010-2013 varsa) üzərinə klikləyin Masadan (Məlumatlar - Cədvəldən):
Açılan sorğu redaktoru pəncərəsində başlığın üzərinə klikləməklə sütunu seçin şirkət və yuxarıdakı düyməni basın qrup (Qrup üzrə). Qruplaşdırmada yeni sütunun adını və əməliyyat növünü daxil edin – Bütün xətlər (Bütün sıralar):
OK düyməsini basın və hər bir şirkət üçün qruplaşdırılmış dəyərlərin mini cədvəlini alırıq. Yaranan sütunda xanaların ağ fonunda (mətndə deyil!) siçanın sol düyməsini sıxsanız, cədvəllərin məzmunu aydın görünür:
İndi daha bir sütun əlavə edək, burada funksiyadan istifadə edərək, mini-cədvəllərin hər birindəki Ünvan sütunlarının məzmununu vergüllə ayıraraq yapışdırırıq. Bunu etmək üçün, nişanda Sütun əlavə edin basırıq Fərdi sütun (Sütun əlavə et - Fərdi sütun) və görünən pəncərədə Power Query-də quraşdırılmış M dilində yeni sütunun adını və birləşmə düsturunu daxil edin:
Qeyd edək ki, bütün M funksiyaları hərflərə həssasdır (Excel-dən fərqli olaraq). Tıkladıqdan sonra OK yapışdırılmış ünvanları olan yeni bir sütun alırıq:
Onsuz da lazımsız sütunu silmək qalır Cədvəl Ünvanları (başlığın üzərinə sağ klikləyin) Sütunu silin) və nişanı klikləməklə nəticələri vərəqə yükləyin Əsas səhifə — Bağlayın və endirin (Ev - Bağlayın və yükləyin):
Əhəmiyyətli nüans: Əvvəlki metodlardan (funksiyalardan) fərqli olaraq, Power Query-dən cədvəllər avtomatik olaraq yenilənmir. Gələcəkdə mənbə məlumatlarında hər hansı bir dəyişiklik olarsa, nəticələr cədvəlində hər hansı bir yeri sağ klikləməlisiniz və əmri seçməlisiniz. Yeniləyin və Saxlayın (Təzələmək).
- Uzun mətn sətirini hissələrə necə bölmək olar
- Müxtəlif hüceyrələrdən mətni birinə yapışdırmağın bir neçə yolu
- Mətni maska ilə yoxlamaq üçün Like operatorundan istifadə edin