Nilalaman
Nagsulat na ako tungkol sa kung paano mo mabilis na maidikit ang teksto mula sa ilang mga cell sa isa at, sa kabaligtaran, i-parse ang isang mahabang string ng teksto sa mga bahagi. Ngayon tingnan natin ang isang malapit, ngunit bahagyang mas kumplikadong gawain - kung paano i-glue ang teksto mula sa ilang mga cell kapag ang isang tiyak na tinukoy na kundisyon ay natugunan.
Sabihin nating mayroon kaming database ng mga customer, kung saan maaaring tumugma ang isang pangalan ng kumpanya sa ilang iba't ibang email ng mga empleyado nito. Ang aming gawain ay kolektahin ang lahat ng mga address ayon sa mga pangalan ng kumpanya at pagsama-samahin ang mga ito (na pinaghihiwalay ng mga kuwit o semicolon) upang makagawa, halimbawa, isang mailing list para sa mga customer, ibig sabihin, makakuha ng output tulad ng:
Sa madaling salita, kailangan namin ng isang tool na magdidikit (mag-link) ng teksto ayon sa kondisyon - isang analogue ng function. SUMMESLI (SUMIF), ngunit para sa text.
Paraan 0. Formula
Hindi masyadong eleganteng, ngunit ang pinakamadaling paraan. Maaari kang magsulat ng isang simpleng formula na susuriin kung ang kumpanya sa susunod na hilera ay naiiba sa nauna. Kung hindi ito naiiba, pagkatapos ay idikit ang susunod na address na pinaghihiwalay ng kuwit. Kung ito ay naiiba, pagkatapos ay "i-reset" namin ang naipon, simula muli:
Ang mga disadvantages ng diskarte na ito ay halata: mula sa lahat ng mga cell ng karagdagang hanay na nakuha, kailangan lamang namin ang mga huling para sa bawat kumpanya (dilaw). Kung ang listahan ay malaki, pagkatapos ay upang mabilis na piliin ang mga ito, kakailanganin mong magdagdag ng isa pang column gamit ang function DLSTR (LEN), sinusuri ang haba ng naipon na mga string:
Ngayon ay maaari mong i-filter ang mga iyon at kopyahin ang kinakailangang gluing ng address para sa karagdagang paggamit.
Paraan 1. Macrofunction ng gluing sa pamamagitan ng isang kondisyon
Kung ang orihinal na listahan ay hindi pinagsunod-sunod ayon sa kumpanya, kung gayon ang simpleng formula sa itaas ay hindi gagana, ngunit madali kang makakalibot gamit ang isang maliit na custom na function sa VBA. Buksan ang Visual Basic Editor sa pamamagitan ng pagpindot sa isang keyboard shortcut Alt + F11 o gamit ang pindutan Visual Basic tab developer (Developer). Sa window na bubukas, magpasok ng isang bagong walang laman na module sa pamamagitan ng menu Ipasok – Module at kopyahin ang teksto ng aming function doon:
Function MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " gluings are not equal to each other - we exit with a error If SearchRange.Count <> TextRange.Count Then MergeIf = CVErr(xlErrRef If 'check out the variable's Function End Kung 'Labas sa kundisyon ng Function. Para sa i = 1 Upang SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'display results without last delimiter MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End function
Kung babalik ka na ngayon sa Microsoft Excel, pagkatapos ay sa listahan ng mga function (button fx sa formula bar o tab Mga Formula – Ipasok ang Function) magiging posible na mahanap ang aming function Pagsamahin Kung nasa kategorya Tinukoy ng Gumagamit (Tukoy ng User). Ang mga argumento sa function ay ang mga sumusunod:
Paraan 2. Pagdugtungin ang teksto sa pamamagitan ng hindi eksaktong kundisyon
Kung papalitan natin ang unang character sa ika-13 linya ng ating macro = sa tinatayang operator ng tugma katulad, pagkatapos ay posible na magsagawa ng gluing sa pamamagitan ng isang hindi eksaktong tugma ng paunang data sa pamantayan ng pagpili. Halimbawa, kung ang pangalan ng kumpanya ay maaaring isulat sa iba't ibang variant, maaari naming suriin at kolektahin ang lahat ng ito sa isang function:
Ang mga karaniwang wildcard ay sinusuportahan:
- asterisk (*) – nagsasaad ng anumang bilang ng anumang mga character (kabilang ang kanilang kawalan)
- tandang pananong (?) – nangangahulugang alinmang karakter
- pound sign (#) – nangangahulugang alinman sa isang digit (0-9)
Bilang default, ang operator ng Like ay case sensitive, ibig sabihin, nauunawaan, halimbawa, ang "Orion" at "orion" bilang magkaibang kumpanya. Upang huwag pansinin ang case, maaari mong idagdag ang linya sa pinakadulo simula ng module sa Visual Basic editor Pagpipilian Ihambing ang Teksto, na lilipat sa Like para maging case insensitive.
Sa ganitong paraan, maaari kang bumuo ng napaka-kumplikadong mga maskara para sa pagsuri ng mga kondisyon, halimbawa:
- ?1##??777RUS – pagpili ng lahat ng mga plaka ng lisensya ng 777 rehiyon, simula sa 1
- LLC* – lahat ng kumpanya na ang pangalan ay nagsisimula sa LLC
- ##7## – lahat ng produkto na may limang digit na digital code, kung saan ang ikatlong digit ay 7
- ???? – lahat ng pangalan ng limang letra, atbp.
Paraan 3. Macro function para sa gluing text sa ilalim ng dalawang kundisyon
Sa trabaho ay maaaring magkaroon ng problema kapag kailangan mong i-link ang teksto ng higit sa isang kundisyon. Halimbawa, isipin natin na sa aming nakaraang talahanayan, ang isa pang haligi na may lungsod ay idinagdag, at ang gluing ay dapat isagawa hindi lamang para sa isang naibigay na kumpanya, kundi pati na rin para sa isang naibigay na lungsod. Sa kasong ito, ang aming function ay kailangang bahagyang ma-moderno sa pamamagitan ng pagdaragdag ng isa pang range check dito:
Function MergeIfs(TextRange Bilang Saklaw, SearchRange1 Bilang Saklaw, Kundisyon1 Bilang String, SearchRange2 Bilang Saklaw, Kundisyon2 Bilang String) Dim Delimeter Bilang String, i Bilang Mahabang Delimeter = ", " 'mga delimiter na character (maaaring mapalitan ng espasyo o ; atbp.) e.) 'kung ang isa't isa ay hindi pantay-pantay sa saklaw ng Text. Range.Count O SearchRange1.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End Kung 'pupunta sa lahat ng mga cell, suriin ang lahat ng mga kondisyon at kolektahin ang text sa variable na OutText Para sa i = 2 Upang SearchRange1.Cells.Count Kung SearchRange1.Cells(i) = SearchRange1.Cells(i) = SearchRange1.Cells(i) = Condition. & TextRange.Cells(i) at Delimeter End If Next i 'display results without last delimiter MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Ito ay ilalapat sa eksaktong parehong paraan - ang mga argumento lamang ang kailangang tukuyin nang higit pa:
Paraan 4. Pagpapangkat at pagdikit sa Power Query
Maaari mong lutasin ang problema nang walang programming sa VBA, kung gagamitin mo ang libreng Power Query add-in. Para sa Excel 2010-2013 maaari itong ma-download dito, at sa Excel 2016 ito ay naka-built in na bilang default. Ang pagkakasunud-sunod ng mga aksyon ay ang mga sumusunod:
Hindi alam ng Power Query kung paano magtrabaho sa mga regular na talahanayan, kaya ang unang hakbang ay gawing "matalino" ang aming talahanayan. Upang gawin ito, piliin ito at pindutin ang kumbinasyon Ctrl+T o pumili mula sa tab Tahanan – I-format bilang isang talahanayan (Home — Format bilang Talahanayan). Sa tab na lilitaw pagkatapos Tagagawa (Disenyo) maaari mong itakda ang pangalan ng talahanayan (Iniwan ko ang pamantayan Table 1):
Ngayon, i-load natin ang aming talahanayan sa add-in ng Power Query. Upang gawin ito, sa tab data (kung mayroon kang Excel 2016) o sa tab na Power Query (kung mayroon kang Excel 2010-2013) i-click Mula sa mesa (Data — Mula sa Talahanayan):
Sa window ng editor ng query na bubukas, piliin ang column sa pamamagitan ng pag-click sa header kompanya at pindutin ang pindutan sa itaas grupo (Pangkatin Ni). Ipasok ang pangalan ng bagong column at ang uri ng operasyon sa pagpapangkat - Lahat ng linya (Lahat ng Row):
I-click ang OK at makakakuha kami ng mini-table ng mga nakagrupong halaga para sa bawat kumpanya. Ang mga nilalaman ng mga talahanayan ay malinaw na nakikita kung mag-left-click ka sa puting background ng mga cell (hindi sa teksto!) sa resultang column:
Ngayon magdagdag tayo ng isa pang column, kung saan, gamit ang function, idinidikit natin ang mga nilalaman ng mga column ng Address sa bawat mini-table, na pinaghihiwalay ng mga kuwit. Upang gawin ito, sa tab Magdagdag ng haligi pinindot namin Custom na column (Magdagdag ng column — Custom na column) at sa lalabas na window, ilagay ang pangalan ng bagong column at ang coupling formula sa M language na binuo sa Power Query:
Tandaan na ang lahat ng M-function ay case sensitive (hindi katulad ng Excel). Pagkatapos mag-click sa OK nakakakuha kami ng bagong column na may mga nakadikit na address:
Ito ay nananatiling alisin ang hindi na kinakailangang hanay Mga TableAddress (right click sa pamagat) Tanggalin ang column) at i-upload ang mga resulta sa sheet sa pamamagitan ng pag-click sa tab Home — Isara at i-download (Home — Isara at i-load):
Mahalagang nuance: Hindi tulad ng mga nakaraang pamamaraan (function), ang mga talahanayan mula sa Power Query ay hindi awtomatikong ina-update. Kung sa hinaharap ay magkakaroon ng anumang mga pagbabago sa pinagmulan ng data, pagkatapos ay kailangan mong i-right-click kahit saan sa talahanayan ng mga resulta at piliin ang command I-update at I-save (Refresh).
- Paano hatiin ang isang mahabang string ng teksto sa mga bahagi
- Maraming mga paraan upang idikit ang teksto mula sa iba't ibang mga cell sa isa
- Gamit ang Like operator para subukan ang text laban sa isang mask