змест
Я ўжо пісаў пра тое, як можна хутка склеіць тэкст з некалькіх вочак у адну і, наадварот, разабраць доўгі тэкставы радок на складнікі. Зараз давайце паглядзім на блізкую, але крыху больш складаную задачу - як склеіць тэкст з некалькіх вочак пры выкананні вызначанага ўмовы.
Дапусцім, у нас ёсць база кліентаў, дзе адна назва кампаніі можа адпавядаць некалькім розным адрасам электроннай пошты яе супрацоўнікаў. Наша задача - сабраць усе адрасы па назвах кампаній і аб'яднаць іх (падзяляючы коскамі або кропкамі з коскі), каб скласці, напрыклад, спіс рассылкі для кліентаў, г.зн. атрымаць на выхадзе нешта накшталт:
Іншымі словамі, патрэбны інструмент, які будзе склейваць (звязваць) тэкст па ўмове – аналаг функцыі СУМЭСЛІ (SUMIF), але для тэксту.
Спосаб 0. Формула
Не вельмі элегантны, але самы просты спосаб. Можна напісаць простую формулу, якая будзе правяраць, ці адрозніваецца кампанія ў наступным радку ад папярэдняй. Калі ён не адрозніваецца, то склейце наступны адрас праз коску. Калі адрозніваецца, то «скідаем» назапашанае, пачынаючы спачатку:
Недахопы такога падыходу відавочныя: з усіх вочак атрыманага дадатковага слупка патрэбныя толькі апошнія для кожнай кампаніі (жоўтыя). Калі спіс вялікі, то для хуткага іх выбару прыйдзецца дадаць яшчэ адзін слупок з дапамогай функцыі DLSTR (LEN), правяраючы даўжыню назапашаных радкоў:
Цяпер вы можаце адфільтраваць іх і скапіяваць неабходныя склейкі адрасоў для далейшага выкарыстання.
Спосаб 1. Макрафункцыя склейвання па адной умове
Калі зыходны спіс не адсартаваны па кампаніях, то прыведзеная вышэй простая формула не працуе, але вы можаце лёгка абыйсціся з дапамогай невялікай карыстацкай функцыі ў VBA. Адкрыйце рэдактар Visual Basic, націснуўшы спалучэнне клавіш Alt + F11 або з дапамогай кнопкі Візуальны Бейсик таб распрацоўшчык (Распрацоўшчык). У якое адкрылася акне праз меню ўстаўце новы пусты модуль Устаўка – модуль і скапіруйце туды тэкст нашай функцыі:
Функцыя 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) Функцыя выхаду End If 'прайдзіце ўсе ячэйкі, праверце ўмову і збярыце тэкст у зменнай OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then 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 адчувальны да рэгістра, гэта значыць разумее, напрыклад, «Арыён» і «Арыён» як розныя кампаніі. Каб ігнараваць рэгістр, вы можаце дадаць радок у самым пачатку модуля ў рэдактары Visual Basic Параметр Параўнаць тэкст, які пераключыць Like на неадчувальны да рэгістра.
Такім чынам, вы можаце скласці вельмі складаныя маскі для праверкі ўмоў, напрыклад:
- ?1##??777RUS – выбар усіх нумарных знакаў рэгіёну 777, пачынаючы з 1
- LLC* – усе кампаніі, назва якіх пачынаецца з LLC
- ##7## – усе тавары з пяцізначным лічбавым кодам, дзе трэцяя лічба 7
- ????? – усе назвы з пяці літар і інш.
Спосаб 3. Макрафункцыя для склейвання тэксту пры двух умовах
У рабоце можа ўзнікнуць праблема, калі трэба звязаць тэкст не адной умовай. Да прыкладу, уявім, што ў нашай папярэдняй табліцы дадаўся яшчэ адзін слупок з горадам, і склейка павінна праводзіцца не толькі для дадзенай кампаніі, але і для дадзенага горада. У гэтым выпадку нашу функцыю прыйдзецца трохі мадэрнізаваць, дадаўшы да яе яшчэ адну праверку дыяпазону:
Function MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'сімвалы-раздзяляльнікі (можна замяніць прабелам або ; і г.д.) e.) 'калі дыяпазоны праверкі і склейвання не роўныя адзін аднаму, выйсці з памылкай If SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Выхад з функцыі End If 'прайдзіце па ўсіх ячэйках, праверце ўсе ўмовы і збярыце тэкст у зменную OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Then OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'адлюстраванне вынікаў без апошняга падзельніка MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Ён будзе прымяняцца сапраўды гэтак жа - толькі аргументаў цяпер трэба пазначыць больш:
Спосаб 4. Групоўка і склейванне ў Power Query
Вы можаце вырашыць праблему без праграмавання ў VBA, калі вы выкарыстоўваеце бясплатную надбудову Power Query. Для Excel 2010-2013 яго можна спампаваць тут, а ў Excel 2016 ён ужо ўбудаваны па змаўчанні. Паслядоўнасць дзеянняў будзе наступная:
Power Query не ўмее працаваць са звычайнымі табліцамі, таму першы крок - ператварыць нашу табліцу ў «разумную». Для гэтага выберыце яго і націсніце камбінацыю Ctrl+T або абярыце з укладкі Галоўная - фармат у выглядзе табліцы (Галоўная — фармат у табліцу). На ўкладцы, якая з'явіцца Канструктар (Дызайн) вы можаце ўсталяваць назву табліцы (я пакінуў стандарт Табліца 1):
Зараз давайце загрузім нашу табліцу ў надбудову Power Query. Для гэтага на таб Дата (калі ў вас Excel 2016) або на ўкладцы Power Query (калі ў вас Excel 2010-2013) націсніце Са стала (Дадзеныя — з табліцы):
У якое адкрылася акне рэдактара запытаў выберыце слупок, націснуўшы на загаловак кампанія і націсніце кнопку вышэй Група (Групаваць па). Увядзіце назву новага слупка і тып аперацыі ў групоўцы – Усе радкі (Усе радкі):
Націскаем ОК і атрымліваем міні-табліцу згрупаваных значэнняў для кожнай кампаніі. Змесціва табліц добра відаць, калі клікнуць левай кнопкай мышы па белым фоне вочак (а не па тэксце!) у атрыманай калонцы:
Зараз дадамо яшчэ адзін слупок, куды з дапамогай функцыі мы склейваем змесціва слупкоў Address у кожнай з міні-табліц, праз коскі. Для гэтага на таб Дадаць слупок націскаем Карыстальніцкі слупок (Дадаць слупок — карыстальніцкі слупок) і ў якое з'явілася акне ўвядзіце назву новага слупка і формулу спалучэння на мове M, убудаванай у Power Query:
Звярніце ўвагу, што ўсе M-функцыі адчувальныя да рэгістра (у адрозненне ад Excel). Пасля націску на OK мы атрымліваем новы слупок з прылепленымі адрасамі:
Засталося прыбраць ужо непатрэбную калонку Адрасы табліцы (правай кнопкай мышы на загалоўку) Выдаліць слупок) і загрузіце вынікі на аркуш, націснуўшы на ўкладку Галоўная — зачыніць і спампаваць (Галоўная — Зачыніць і загрузіць):
Важны нюанс: У адрозненне ад папярэдніх метадаў (функцый), табліцы з Power Query не абнаўляюцца аўтаматычна. Калі ў далейшым адбудуцца якія-небудзь змены ў зыходных дадзеных, то вам трэба будзе пстрыкнуць правай кнопкай мышы ў любым месцы табліцы вынікаў і выбраць каманду Абнавіць і захаваць (Абнавіць).
- Як разбіць доўгі тэкставы радок на часткі
- Некалькі спосабаў склеіць тэкст з розных вочак у адну
- Выкарыстанне аператара Like для праверкі тэксту на маску