Мазмұны
Мен бірнеше ұяшықтағы мәтінді бір ұяшыққа қалай тез жабыстыруға және, керісінше, ұзын мәтін жолын құрамдас бөліктерге талдауға болатынын жаздым. Енді жақынырақ, бірақ біршама күрделі тапсырманы қарастырайық – белгілі бір шарт орындалған кезде бірнеше ұяшықтан мәтінді қалай желімдеу керек.
Бізде тұтынушылардың деректер базасы бар делік, онда бір компания атауы оның қызметкерлерінің бірнеше түрлі электрондық пошталарына сәйкес келуі мүмкін. Біздің міндетіміз, мысалы, тұтынушыларға арналған жіберулер тізімін жасау, яғни келесідей нәтиже алу үшін барлық мекенжайларды компания атаулары бойынша жинау және оларды біріктіру (үтір немесе нүктелі үтір арқылы бөлу):
Басқаша айтқанда, бізге шартқа сәйкес мәтінді желімдейтін (байланыстыратын) құрал – функцияның аналогы қажет. СУММЕСЛИ (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 To SearchRange үшін айнымалы мәндегі мәтінді жинаңыз. Ұяшықтар.Санау Егер 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 операторы регистрді ескереді, яғни, мысалы, «Orion» және «orion» әртүрлі компаниялар ретінде түсінеді. Регистрді елемеу үшін жолды модульдің ең басына Visual Basic редакторында қосуға болады Мәтінді салыстыру опциясы, ол «Лайк» пернесін регистрді ескермейтін етіп ауыстырады.
Осылайша сіз жағдайларды тексеру үшін өте күрделі маскалар жасай аласыз, мысалы:
- ?1##??777RUS – 777-ден басталатын 1 аймағының барлық нөмірлерін таңдау
- LLC* – аты ЖШҚ деп басталатын барлық компаниялар
- ##7## – үшінші цифры 7 болатын бес таңбалы сандық коды бар барлық өнімдер
- ?????? – бес әріптен тұратын барлық атаулар және т.б.
3-әдіс. Екі жағдайда мәтінді желімдеуге арналған макрофункция
Жұмыста мәтінді бірнеше шартпен байланыстыру қажет болғанда мәселе туындауы мүмкін. Мысалы, біздің алдыңғы кестеде қаламен тағы бір баған қосылды деп елестетіп көрейік, ал желімдеу тек белгілі бір компания үшін ғана емес, сонымен қатар белгілі бір қала үшін де жүргізілуі керек. Бұл жағдайда біздің функциямыз оған басқа диапазонды тексеруді қосу арқылы сәл модернизациялануы керек:
MergeIfs функциясы(мәтін аралығы диапазон ретінде, SearchRange1 диапазон ретінде, шарт1 жол ретінде, SearchRange2 диапазон ретінде, шарт2 жол ретінде) күңгірттендіргіш жол ретінде, i ұзын шек ретінде = ", " 'бөлгіш таңбалары (бос орынмен немесе ; т.б. ауыстыруға болады) e.) 'егер тексеру және желімдеу ауқымдары бір-біріне тең болмаса, қатемен шығыңыз 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 функциясы
Ол дәл осылай қолданылады – енді тек аргументтер көбірек көрсетілуі керек:
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 операторын пайдалану