Агуулга
Хэд хэдэн нүднүүдээс текстийг хэрхэн хурдан нааж, эсрэгээр нь урт текстийн мөрийг бүрэлдэхүүн хэсгүүдэд задлан шинжлэх талаар би аль хэдийн бичсэн. Одоо тодорхой заасан нөхцөл хангагдсан тохиолдолд хэд хэдэн нүднээс текстийг хэрхэн яаж нааж болох талаар нарийн, гэхдээ арай илүү төвөгтэй ажлыг авч үзье.
Нэг компанийн нэр нь ажилчдынхаа хэд хэдэн өөр имэйлтэй тохирч болох хэрэглэгчдийн мэдээллийн сантай гэж бодъё. Бидний даалгавар бол бүх хаягийг компанийн нэрээр цуглуулж, хооронд нь холбоно (таслал эсвэл цэг таслалаар тусгаарлаж), жишээлбэл, үйлчлүүлэгчдэд зориулсан захидлын жагсаалт гаргах, жишээлбэл:
Өөрөөр хэлбэл, бидэнд нөхцөлийн дагуу текстийг нааж (холбох) хэрэгсэл хэрэгтэй - функцийн аналог. СУММЕСЛИ (SUMIF), гэхдээ текстийн хувьд.
Арга 0. Томъёо
Маш дэгжин биш, гэхдээ хамгийн хялбар арга. Та дараагийн эгнээнд байгаа компани өмнөхөөсөө ялгаатай эсэхийг шалгах энгийн томъёог бичиж болно. Хэрэв энэ нь ялгаатай биш бол таслалаар тусгаарласан дараагийн хаягийг наа. Хэрэв энэ нь ялгаатай бол бид хуримтлагдсан зүйлийг дахин эхлүүлж "дахин тохируулна".
Энэ аргын сул талууд нь тодорхой байна: олж авсан нэмэлт баганын бүх нүднүүдээс бид зөвхөн компани тус бүрийн сүүлчийнх нь (шар өнгөтэй) хэрэгтэй. Хэрэв жагсаалт том бол тэдгээрийг хурдан сонгохын тулд функцийг ашиглан өөр багана нэмэх шаардлагатай болно DLSTR (ЛЕН), хуримтлагдсан мөрүүдийн уртыг шалгах:
Одоо та тэдгээрийг шүүж, цаашид ашиглахын тулд шаардлагатай хаягийг нааж хуулж болно.
Арга 1. Нэг нөхцөлөөр наах макрофункц
Хэрэв анхны жагсаалтыг компаниар эрэмбэлээгүй бол дээрх энгийн томъёо ажиллахгүй ч VBA дахь жижиг тохируулгын функцийг ашиглан хялбархан тойрч болно. Visual Basic Editor-ийг гарын товчлолыг дарж нээнэ үү Alt + F11 эсвэл товчлуурыг ашиглана уу Visual Basic таб Developer (Хөгжүүлэгч). Нээгдсэн цонхонд цэсээр дамжуулан шинэ хоосон модулийг оруулна уу Оруулах - Модуль мөн манай функцийн текстийг тэнд хуулна уу:
Функц MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " наалтууд хоорондоо тэнцүү биш - бид алдаа гаргана If SearchRange.Count <> TextRange.Count Дараа нь MergeIf = CVErr(xlErrRef) Exit Function End Хэрэв 'бүх нүднүүдийг дундуур нь орж үзвэл нөхцөлийг шалгаад OutText For i = 1 To SearchRange-д текстийг цуглуул. Cells.Count Хэрэв SearchRange.Cells(i) нь нөхцөлтэй адил бол OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'үр дүнг сүүлийн хязгаарлагчгүйгээр харуулах MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End функц
Хэрэв та одоо Microsoft Excel руу буцаж очвол функцүүдийн жагсаалтад (товч fx томьёоны мөр эсвэл таб дээр Томъёо - Оруулах функц) бидний функцийг олох боломжтой болно MergeIf ангилалд Хэрэглэгчийн тодорхойлсон (Хэрэглэгчийн тодорхойлсон). Функцийн аргументууд дараах байдалтай байна.
Арга 2. Текстийг тодорхой бус нөхцөлөөр холбоно
Хэрэв бид макроныхаа 13-р мөрөнд эхний тэмдэгтийг орлуулах юм бол = ойролцоогоор тохирох оператор руу шиг, дараа нь сонгон шалгаруулах шалгууртай анхны өгөгдлүүдтэй таарахгүй байх замаар наалт хийх боломжтой болно. Жишээлбэл, хэрэв компанийн нэрийг өөр өөр хувилбараар бичиж болох юм бол бид бүгдийг нэг функцээр шалгаж, цуглуулж болно.
Стандарт орлуулагч тэмдэгтүүдийг дэмждэг:
- од (*) - дурын тооны тэмдэгтийг (тэдгээрийн байхгүйг оруулаад) илэрхийлнэ.
- асуултын тэмдэг (?) – дурын ганц тэмдэгтийг илэрхийлнэ
- фунт тэмдэг (#) - дурын нэг цифрийг илэрхийлнэ (0-9)
Анхдагч байдлаар, Like оператор нь жижиг жижиг үсгийн мэдрэмжтэй байдаг, жишээлбэл, "Orion" болон "orion" хоёрыг өөр компани гэж ойлгодог. Том үсгийг үл тоомсорлохын тулд та Visual Basic засварлагчийн модулийн хамгийн эхэнд мөрийг нэмж болно Текстийг харьцуулах сонголт, энэ нь "Like"-г шилжүүлж, жижиг жижиг үсгийг ялгахгүй.
Ийм байдлаар та нөхцөл байдлыг шалгахын тулд маш нарийн төвөгтэй маск зохиож болно, жишээлбэл:
- ?1##??777RUS – 777-ээс эхлэн 1 бүсийн бүх улсын дугаарыг сонгох
- ХХК* – ХХК-аар эхэлсэн бүх компаниуд
- ##7## – гурав дахь орон нь 7 гэсэн таван оронтой тоон код бүхий бүх бүтээгдэхүүн
- ?????? – таван үсгийн бүх нэр гэх мэт.
Арга 3. Хоёр нөхцөлд текстийг наах макро функц
Ажлын явцад текстийг нэгээс олон нөхцөлтэй холбох шаардлагатай үед асуудал гарч болзошгүй. Жишээлбэл, бидний өмнөх хүснэгтэд хоттой хамт нэг багана нэмж, наалтыг зөвхөн тухайн компанид төдийгүй тухайн хотод хийх ёстой гэж төсөөлөөд үз дээ. Энэ тохиолдолд бидний функцийг өөр хүрээний чек нэмж бага зэрэг шинэчлэх шаардлагатай болно:
MergeIfs функц (TextRange-ийг муж болгон, SearchRange1-ийг муж болгон, Condition1-ийг муж болгон, SearchRange2-ийг муж болгон, нөхцөл2-ыг мөр болгон) Dim Delimeter-ыг мөр болгон, i Урт зааглагчаар = ", " 'хязгаарлагч тэмдэгтүүдийг (зай эсвэл ; гэх мэтээр сольж болно) д.) 'хэрэв баталгаажуулах болон наах мужууд хоорондоо тэнцүү биш бол алдаа гарна. Хэрэв SearchRange1.Count <> TextRange.Count эсвэл SearchRange2.Count <> TextRange.Count Дараа нь MergeIfs = CVErr(xlErrRef) Гарах функц дуусна. "Бүх нүднүүдийг үзэж, бүх нөхцлийг шалгаад текстийг OutText хувьсагч руу цуглуулна. For i = 1 SearchRange1.Cells.Count бол SearchRange1.Cells(i) = Condition1 ба SearchRange2.Cells(i) = Condition2 Дараа нь OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'үр дүнг сүүлийн хязгаарлагчгүйгээр харуулах MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Энэ нь яг адилхан хэрэглэгдэх болно - одоо зөвхөн аргументуудыг нэмж зааж өгөх шаардлагатай:
Арга 4. Power Query дээр бүлэглэх, наах
Хэрэв та үнэгүй Power Query нэмэлтийг ашиглавал VBA програмчлалгүйгээр асуудлыг шийдэж чадна. Excel 2010-2013 хувилбарын хувьд үүнийг эндээс татаж авах боломжтой бөгөөд Excel 2016 дээр анхдагчаар суулгасан байна. Үйлдлийн дараалал дараах байдалтай байна.
Power Query нь ердийн хүснэгтүүдтэй хэрхэн ажиллахаа мэддэггүй тул эхний алхам бол ширээгээ "ухаалаг" болгох явдал юм. Үүнийг хийхийн тулд үүнийг сонгоод хослолыг дарна уу Ctrl+T эсвэл таб дээрээс сонгоно уу Нүүр хуудас - Хүснэгт хэлбэрээр форматлах (Нүүр хуудас - Хүснэгт хэлбэрээр форматлах). Дараа нь гарч ирэх таб дээр байгуулагч (Дизайн) Та хүснэгтийн нэрийг тохируулж болно (би стандартыг орхисон Хүснэгт 1):
Одоо хүснэгтээ Power Query нэмэлт рүү ачаалъя. Үүнийг хийхийн тулд таб дээр Өгөгдөл (хэрэв танд Excel 2016 байгаа бол) эсвэл Power Query таб (хэрэв танд Excel 2010-2013 байгаа бол) дээр дарна уу. Ширээн дээрээс (Өгөгдөл - Хүснэгтээс):
Нээгдсэн асуулга засварлагч цонхноос толгой дээр дарж баганыг сонгоно уу Компани нь мөн дээрх товчийг дарна уу Group (Бүлэг). Бүлэглэлд шинэ баганын нэр болон үйлдлийн төрлийг оруулна уу - Бүх мөрүүд (Бүх мөр):
"OK" дээр дарж, бид компани бүрийн хувьд бүлэглэсэн утгуудын мини хүснэгтийг авна. Үүссэн баганын нүднүүдийн цагаан дэвсгэр дээр (текст дээр биш!) хулганы зүүн товчийг дарвал хүснэгтүүдийн агуулга тодорхой харагдана.
Одоо дахиад нэг багана нэмье, энд функцийг ашиглан Хаяг баганын агуулгыг таслалаар тусгаарласан мини хүснэгт бүрт наа. Үүнийг хийхийн тулд таб дээр Багана нэмэх бид дарна Тусгай багана (Багана нэмэх - Тусгай багана) гарч ирэх цонхонд Power Query-д суулгасан M хэл дээрх шинэ баганын нэр болон холболтын томъёог оруулна уу:
Бүх M-функцууд (Excel-ээс ялгаатай) жижиг жижиг үсгийг мэдэрдэг гэдгийг анхаарна уу. Дарсны дараа OK Бид наасан хаягтай шинэ багана авах болно:
Энэ нь аль хэдийн шаардлагагүй баганыг арилгах хэвээр байна Хүснэгтийн хаягууд (гарчиг дээр баруун товшино уу) Багана устгах) таб дээр дарж үр дүнг хүснэгтэд байршуулна уу Нүүр хуудас — Хаах ба татаж авах (Нүүр хуудас — Хаах, ачаалах):
Чухал нюанс: Өмнөх аргуудаас (функцуудаас) ялгаатай нь Power Query-ийн хүснэгтүүд автоматаар шинэчлэгддэггүй. Хэрэв ирээдүйд эх сурвалжийн өгөгдөлд ямар нэгэн өөрчлөлт гарвал үр дүнгийн хүснэгтийн аль ч хэсэгт хулганы баруун товчийг дараад командыг сонгох хэрэгтэй. Шинэчлэх & Хадгалах (Сэргээх).
- Урт текст мөрийг хэрхэн хэсэг болгон хуваах вэ
- Өөр өөр нүднүүдээс текстийг нэг болгон наах хэд хэдэн арга
- Текстийг масктай харьцуулахын тулд Like операторыг ашиглана уу