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