Mundarija
Men bir nechta katakchalardan matnni tezda qanday qilib bittaga yopishtirishingiz va aksincha, uzun matn qatorini tarkibiy qismlarga ajratishingiz haqida allaqachon yozganman. Keling, yaqinroq, ammo biroz murakkabroq vazifani ko'rib chiqaylik - ma'lum bir shart bajarilganda bir nechta kataklardan matnni qanday yopishtirish kerak.
Aytaylik, bizda mijozlarning ma'lumotlar bazasi mavjud bo'lib, unda bitta kompaniya nomi uning xodimlarining bir nechta turli elektron pochtalariga mos kelishi mumkin. Bizning vazifamiz barcha manzillarni kompaniya nomlari bo'yicha yig'ish va ularni (vergul yoki nuqta-vergul bilan ajratilgan holda) birlashtirish, masalan, mijozlar uchun pochta ro'yxatini yaratish, ya'ni quyidagi kabi chiqishni olishdir:
Boshqacha qilib aytganda, bizga shartga ko'ra matnni yopishtiruvchi (bog'laydigan) vosita kerak - funktsiyaning analogi. SUMMESLI (SUMIF), lekin matn uchun.
0-usul. Formula
Juda oqlangan emas, lekin eng oson yo'li. Keyingi qatordagi kompaniya oldingisidan farq qiladimi yoki yo'qligini tekshiradigan oddiy formulani yozishingiz mumkin. Agar u farq qilmasa, vergul bilan ajratilgan keyingi manzilni yopishtiring. Agar u boshqacha bo'lsa, biz to'planganlarni qaytadan "qayta tiklaymiz":
Ushbu yondashuvning kamchiliklari aniq: olingan qo'shimcha ustunning barcha hujayralaridan har bir kompaniya uchun faqat oxirgi (sariq) kerak. Agar ro'yxat katta bo'lsa, ularni tezda tanlash uchun funktsiyadan foydalanib boshqa ustun qo'shishingiz kerak bo'ladi DLSTR (LEN), to'plangan satrlarning uzunligini tekshirish:
Endi siz ularni filtrlashingiz va keyingi foydalanish uchun kerakli manzilni yopishtirishingiz mumkin.
1-usul. Bir shart bilan yelimlashning makrofunksiyasi
Agar asl ro'yxat kompaniya bo'yicha tartiblanmagan bo'lsa, unda yuqoridagi oddiy formula ishlamaydi, lekin siz VBA-da kichik maxsus funksiya bilan osongina aylanib o'tishingiz mumkin. Klaviatura yorlig'ini bosish orqali Visual Basic muharririni oching Alt + F11 yoki tugma yordamida Visual Basic tab dasturchi (ishlab chiquvchi). Ochilgan oynada menyu orqali yangi bo'sh modulni joylashtiring Qo'shish - modul va u erda bizning funktsiyamiz matnini nusxalash:
Funktsiya MergeIf(TextRange As Range, SearchRange As String, Condition as String) Dim Delimeter As String, i As Long Delimeter = ", " yopishtirishlar bir-biriga teng emas - xato bilan chiqamiz If SearchRange.Count <> TextRange.Count Keyin MergeIf = CVErr(xlErrRef) Chiqish Funktsiya End Agar 'barcha katakchalarni ko'rib chiqsangiz, shartni tekshiring va matnni OutText For i = 1 To SearchRange uchun to'plang. Cells.Count Agar SearchRange.Cells(i) holat yoqsa, OutText = OutText & TextRange.Cells(i) & Delimeter Keyingi i 'natijalarni oxirgi ajratuvchisiz ko'rsatish MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End funktsiyasi
Agar siz hozir Microsoft Excel-ga qaytsangiz, u holda funktsiyalar ro'yxatida (tugma fx formulalar satrida yoki yorliqda Formulalar - Funktsiyani kiritish) funksiyamizni topish mumkin bo'ladi MergeIf toifasida Foydalanuvchi aniqlangan (Foydalanuvchi tomonidan belgilangan). Funktsiyaning argumentlari quyidagicha:
2-usul. Matnni noaniq shart bo‘yicha birlashtiring
Makromizning 13-qatoridagi birinchi belgini almashtirsak = taxminiy moslik operatoriga kabi, keyin dastlabki ma'lumotlarning tanlov mezoni bilan noto'g'ri mos kelishi bilan yopishtirishni amalga oshirish mumkin bo'ladi. Misol uchun, agar kompaniya nomi turli xil variantlarda yozilishi mumkin bo'lsa, biz ularni bitta funktsiya bilan tekshirishimiz va to'plashimiz mumkin:
Standart joker belgilar qo'llab-quvvatlanadi:
- yulduzcha (*) - har qanday belgilarning istalgan sonini (shu jumladan ularning yo'qligini) bildiradi.
- savol belgisi (?) - har qanday bitta belgini bildiradi
- funt belgisi (#) - har qanday bitta raqamni bildiradi (0-9)
Odatiy bo'lib, Like operatori katta-kichik harflarga sezgir, ya'ni, masalan, "Orion" va "orion" ni turli kompaniyalar sifatida tushunadi. Vaziyatni e'tiborsiz qoldirish uchun siz Visual Basic muharririda modulning eng boshida qatorni qo'shishingiz mumkin Variant Matnni solishtirish, bu esa "Like" ni o'zgartiradi, bu esa katta-kichik harflardan farq qiladi.
Shunday qilib, siz sharoitlarni tekshirish uchun juda murakkab niqoblar yaratishingiz mumkin, masalan:
- ?1##??777RUS – 777 hududining barcha davlat raqamlarini tanlash, 1dan boshlanadi
- MChJ* - nomi MChJ bilan boshlangan barcha kompaniyalar
- ##7## - besh xonali raqamli kodli barcha mahsulotlar, bu erda uchinchi raqam 7
- ?????? - besh harfdan iborat barcha nomlar va boshqalar.
3-usul. Ikki shartda matnni yopishtirish uchun makro funksiyasi
Ishda matnni bir nechta shartlarga bog'lash kerak bo'lganda muammo bo'lishi mumkin. Misol uchun, oldingi jadvalimizda shahar bilan yana bitta ustun qo'shilganligini tasavvur qilaylik va yopishtirish nafaqat ma'lum bir kompaniya uchun, balki ma'lum bir shahar uchun ham amalga oshirilishi kerak. Bunday holda, bizning funktsiyamiz unga boshqa diapazon tekshiruvini qo'shish orqali biroz modernizatsiya qilinishi kerak:
Funktsiya MergeIfs(TextRange as diapazon, SearchRange1 as diapazoni, Condition1 as diapazoni, Condition2 diapazon as, Condition2 string as) Dim delimeter as string, i as Long Delimeter = ", " 'cheklovchi belgilar (bo'shliq yoki ; va hokazo bilan almashtirilishi mumkin) e.) 'agar tekshirish va yopishtirish diapazonlari bir-biriga teng bo'lmasa, xato bilan chiqing SearchRange1.Count <> TextRange.Count Yoki SearchRange2.Count <> TextRange.Count Keyin MergeIfs = CVErr(xlErrRef) Chiqish Funktsiya tugasa, 'barcha katakchalardan o'ting, barcha shartlarni tekshiring va matnni OutText o'zgaruvchisiga to'plang For i = 1 SearchRange1.Cells.Count Agar SearchRange1.Cells(i) = Condition1 Va SearchRange2.Cells(i) = Condition2 Keyin OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'natijalarni oxirgi ajratuvchisiz ko'rsatish MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
U xuddi shu tarzda qo'llaniladi - endi faqat argumentlar ko'proq ko'rsatilishi kerak:
4-usul. Power Query dasturida guruhlash va yopishtirish
Agar siz bepul Power Query plaginidan foydalansangiz, muammoni VBA-da dasturlashsiz hal qilishingiz mumkin. Excel 2010-2013 uchun uni bu yerdan yuklab olish mumkin va Excel 2016 da u allaqachon sukut bo'yicha o'rnatilgan. Harakatlar ketma-ketligi quyidagicha bo'ladi:
Power Query oddiy jadvallar bilan qanday ishlashni bilmaydi, shuning uchun birinchi qadam stolimizni "aqlli" stolga aylantirishdir. Buning uchun uni tanlang va kombinatsiyani bosing Ctrl+T yoki yorliqdan tanlang Bosh sahifa - Jadval sifatida formatlash (Uy — Jadval sifatida formatlash). Keyin paydo bo'ladigan yorliqda konstruktor (Dizayn) jadval nomini o'rnatishingiz mumkin (men standartni qoldirdim 1 stol):
Endi jadvalimizni Power Query plaginiga yuklaymiz. Buning uchun yorliqda ma'lumotlar (agar sizda Excel 2016 bo'lsa) yoki Power Query yorlig'ida (agar sizda Excel 2010-2013 bo'lsa) tugmasini bosing. Stoldan (Ma'lumotlar - Jadvaldan):
Ochilgan so'rovlar muharriri oynasida sarlavhani bosish orqali ustunni tanlang kompaniya va yuqoridagi tugmani bosing guruh (Guruh bo'yicha). Yangi ustun nomini va guruhlashda operatsiya turini kiriting - Barcha qatorlar (Barcha qatorlar):
OK tugmasini bosing va biz har bir kompaniya uchun guruhlangan qiymatlarning mini-jadvalini olamiz. Olingan ustundagi katakchalarning oq fonida (matnga emas!) sichqonchaning chap tugmasi bosilsa, jadvallar mazmuni aniq ko‘rinadi:
Endi yana bitta ustun qo'shamiz, bu erda funktsiyadan foydalanib, biz har bir mini-jadvaldagi Manzil ustunlari tarkibini vergul bilan ajratilgan holda yopishtiramiz. Buning uchun yorliqda Ustun qo'shish bosamiz Maxsus ustun (Ustun qo'shish - Maxsus ustun) va paydo bo'lgan oynada Power Query-ga o'rnatilgan M tilida yangi ustun nomini va ulanish formulasini kiriting:
E'tibor bering, barcha M-funksiyalar katta-kichik harflarga sezgir (Exceldan farqli o'laroq). Bosgandan keyin OK biz yopishtirilgan manzillar bilan yangi ustunni olamiz:
Bu allaqachon keraksiz ustunni olib tashlash uchun qoladi Jadval manzillari (sarlavhani o'ng tugmasini bosing) Ustunni o'chirish) va yorliqni bosish orqali natijalarni varaqqa yuklang Bosh sahifa — Yoping va yuklab oling (Uy — Yopish va yuklash):
Muhim nuance: Oldingi usullardan (funktsiyalardan) farqli o'laroq, Power Query jadvallari avtomatik ravishda yangilanmaydi. Agar kelajakda manba ma'lumotlarida biron bir o'zgarish bo'lsa, natijalar jadvalining istalgan joyini sichqonchaning o'ng tugmasi bilan bosishingiz va buyruqni tanlashingiz kerak. Yangilash va saqlash (yangilash).
- Uzoq matn qatorini qismlarga qanday ajratish mumkin
- Turli hujayralardagi matnni bittaga yopishtirishning bir necha usullari
- Matnni niqob bilan tekshirish uchun Like operatoridan foydalanish