Kaundan
Gisulat na nako kung giunsa nimo dali nga mapapilit ang teksto gikan sa daghang mga cell ngadto sa usa ug, sa kasukwahi, pag-parse sa usa ka taas nga string sa teksto sa mga sangkap. Karon atong tan-awon ang usa ka hapit, apan gamay nga labi ka komplikado nga buluhaton - kung giunsa ang pagpapilit sa teksto gikan sa daghang mga selyula kung ang usa ka piho nga kondisyon natuman.
Ingnon ta nga kami adunay database sa mga kustomer, diin ang usa ka ngalan sa kompanya mahimong katumbas sa daghang lainlaing mga email sa mga empleyado niini. Ang among tahas mao ang pagkolekta sa tanan nga mga adres sa mga ngalan sa kompanya ug pagdugtong niini (gibulag sa mga koma o semicolon) aron mahimo, pananglitan, usa ka lista sa pagpadala alang sa mga kostumer, i.e. makakuha og output sama sa:
Sa laing pagkasulti, kinahanglan namon ang usa ka himan nga mopilit (pagsumpay) sa teksto sumala sa kondisyon - usa ka analogue sa function. SUMMESLI (SUMIF), pero para sa text.
Pamaagi 0. Pormula
Dili kaayo elegante, apan ang pinakasayon nga paagi. Mahimo nimong isulat ang usa ka yano nga pormula nga magsusi kung ang kompanya sa sunod nga laray lahi sa nauna. Kung dili kini magkalainlain, dayon ipapilit ang sunod nga adres nga gibulag sa usa ka koma. Kung magkalainlain, nan "i-reset" namon ang natipon, magsugod pag-usab:
Ang mga disbentaha sa kini nga pamaagi klaro: gikan sa tanan nga mga selyula sa dugang nga kolum nga nakuha, kinahanglan ra naton ang katapusan alang sa matag kompanya (dilaw). Kung ang lista dako, nan aron dali nga mapili kini, kinahanglan nimo nga idugang ang lain nga kolum gamit ang function DLSTR (LEN), pagsusi sa gitas-on sa natipon nga mga kuwerdas:
Karon mahimo nimong i-filter ang mga ug kopyahon ang kinahanglan nga gluing nga adres alang sa dugang nga paggamit.
Pamaagi 1. Macrofunction sa gluing sa usa ka kondisyon
Kung ang orihinal nga lista wala gisunud sa kompanya, nan ang yano nga pormula sa ibabaw dili molihok, apan dali ka makalibot sa usa ka gamay nga naandan nga function sa VBA. Ablihi ang Visual Basic Editor pinaagi sa pagpindot sa keyboard shortcut Alt + F11 o gamit ang buton visual Basic tab developer (Developer). Sa bintana nga nagbukas, pagsulud usa ka bag-ong wala’y sulod nga module pinaagi sa menu Isulod – Module ug kopyaha ang teksto sa among function didto:
Function MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " gluings is not equal to each other - we exit with a error If SearchRange.Count <> TextRange.Count Dayon MergeIf = CVErr(xlErrRef) Exit Function End Kung 'moagi sa tanang mga cell, susiha ang kondisyon ug kolektaha ang teksto sa variable OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Sunod i 'display results without last delimiter MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End gimbuhaton
Kung mobalik ka karon sa Microsoft Excel, unya sa lista sa mga gimbuhaton (button fx sa formula bar o tab Mga pormula – Isulod ang Function) mahimong posible nga makit-an ang among function PaghiusaKon sa kategorya Gipunting nga Gipili sa Gumagamit (Gipasabot sa Gumagamit). Ang mga argumento sa function mao ang mosunod:
Pamaagi 2. Pagdugtong sa teksto pinaagi sa dili tukma nga kondisyon
Kung atong ilisan ang unang karakter sa ika-13 nga linya sa atong macro = ngadto sa gibana-bana nga match operator Sama sa, unya posible nga ipatuman ang gluing pinaagi sa dili tukma nga pagpares sa inisyal nga datos nga adunay kriterya sa pagpili. Pananglitan, kung ang ngalan sa kompanya mahimong isulat sa lainlaing mga variant, mahimo naton susihon ug kolektahon silang tanan sa usa ka function:
Gisuportahan ang standard wildcards:
- asterisk (*) – nagpaila sa bisan unsang gidaghanon sa bisan unsang mga karakter (lakip ang pagkawala niini)
- marka sa pangutana (?) – nagbarog alang sa bisan unsa nga karakter
- pound sign (#) – nagpasabot sa bisan unsa nga usa ka digit (0-9)
Sa kasagaran, ang Like operator kay case sensitive, i.e. nakasabut, pananglitan, "Orion" ug "orion" isip lain-laing mga kompanya. Aron ibaliwala ang kaso, mahimo nimong idugang ang linya sa sinugdanan sa module sa Visual Basic editor Opsyon Itandi ang Teksto, nga mobalhin sa Like nga mahimong case insensitive.
Niining paagiha, mahimo nimong isulat ang labi ka komplikado nga mga maskara alang sa pagsusi sa mga kondisyon, pananglitan:
- ?1##??777RUS – pagpili sa tanang plaka sa 777 nga rehiyon, sugod sa 1
- LLC * - tanan nga mga kompanya kansang ngalan nagsugod sa LLC
- ##7## – tanang produkto nga adunay lima ka digit nga digital code, diin ang ikatulo nga digit kay 7
- ???? – tanang ngalan sa lima ka letra, etc.
Pamaagi 3. Macro function para sa gluing text ubos sa duha ka kondisyon
Sa trabaho mahimong adunay problema kung kinahanglan nimo nga i-link ang teksto labaw sa usa ka kondisyon. Pananglitan, atong hunahunaon nga sa atong miaging lamesa, usa pa ka kolum uban sa siyudad ang gidugang, ug ang gluing kinahanglan nga ipatuman dili lamang alang sa usa ka kompanya, kondili alang usab sa usa ka siyudad. Sa kini nga kaso, ang atong function kinahanglan nga gamay nga moderno pinaagi sa pagdugang sa lain nga range check niini:
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 = "," 'delimiter characters (mahimong pulihan og space o ; etc.) e.) 'kon ang validation ug gluing ranges dili managsama sa usag usa, exit with a error If SearchRange1.Count <> TextRange.Count O SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'pag-agi sa tanang mga cell, susiha ang tanang kondisyon ug kolektaha ang teksto ngadto sa variable OutText Para sa i = 1 Sa SearchRange1.Cells.Count Kung SearchRange1.Cells(i) = Condition1 Ug SearchRange2.Cells(i) = Condition2 Unya OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'display results without last delimiter MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Ipadapat kini sa parehas nga paagi - ang mga argumento lamang karon ang kinahanglan nga ipiho pa:
Pamaagi 4. Pag-grupo ug pagpapilit sa Power Query
Mahimo nimong masulbad ang problema nga wala’y pagprograma sa VBA, kung mogamit ka nga libre nga Power Query add-in. Alang sa Excel 2010-2013 kini ma-download dinhi, ug sa Excel 2016 kini gitukod na pinaagi sa default. Ang han-ay sa mga aksyon mao ang mosunod:
Ang Power Query wala mahibal-an kung unsaon pagtrabaho sa mga regular nga lamesa, mao nga ang una nga lakang mao ang paghimo sa among lamesa nga usa ka "smart". Aron mahimo kini, pilia kini ug ipadayon ang kombinasyon Ctrl+T o pagpili gikan sa tab Panimalay - Pag-format ingon usa ka lamesa (Balay — Format isip Talaan). Sa tab nga makita dayon Magtutukod (Laraw) mahimo nimong itakda ang ngalan sa lamesa (gibiyaan nako ang sukaranan Table 1):
Karon atong ikarga ang atong lamesa sa Power Query add-in. Aron mahimo kini, sa tab Data (kung ikaw adunay Excel 2016) o sa Power Query tab (kung ikaw adunay Excel 2010-2013) i-klik Gikan sa lamesa (Data — Gikan sa Talaan):
Sa bintana sa editor sa pangutana nga nagbukas, pilia ang kolum pinaagi sa pag-klik sa ulohan Company ug pindota ang buton sa ibabaw Group (Grupo Ni). Pagsulod sa ngalan sa bag-ong kolum ug ang matang sa operasyon sa grupo - Tanang linya (Tanan nga Laray):
I-klik ang OK ug makakuha kami usa ka mini-table nga gigrupo nga mga kantidad alang sa matag kompanya. Ang mga sulod sa mga lamesa tin-aw nga makita kung imong left-click ang puti nga background sa mga cell (dili sa teksto!) sa resulta nga kolum:
Karon magdugang kita og usa pa ka kolum, diin, gamit ang function, atong ipapilit ang mga sulod sa mga column sa Address sa matag mini-table, nga gibulag sa mga koma. Aron mahimo kini, sa tab Pagdugang kolum atong gipugos Pasadya nga kolum (Idugang ang kolum — Custom nga kolum) ug sa bintana nga makita, isulod ang ngalan sa bag-ong kolum ug ang pormula sa pagdugtong sa M nga pinulongan nga gitukod sa Power Query:
Timan-i nga ang tanang M-function kay case sensitive (dili sama sa Excel). Human sa pag-klik sa OK nakakuha kami usa ka bag-ong kolum nga adunay mga adres nga gipapilit:
Nagpabilin nga tangtangon ang wala na kinahanglan nga kolum Mga Address sa Talaan (pag-right click sa titulo) Pagtangtang sa kolum) ug i-upload ang mga resulta sa sheet pinaagi sa pag-klik sa tab Panimalay — Isira ug i-download (Balay - Isira ug i-load):
Importante nga nuance: Dili sama sa nangaging mga pamaagi (function), ang mga lamesa gikan sa Power Query dili awtomatik nga gi-update. Kung sa umaabot adunay bisan unsang mga pagbag-o sa gigikanan nga datos, nan kinahanglan nimo nga i-right-click bisan asa sa lamesa sa mga resulta ug pilia ang mando I-update ug I-save (I-refresh).
- Giunsa pagbahin ang usa ka taas nga string sa teksto sa mga bahin
- Daghang mga paagi sa pagpapilit sa teksto gikan sa lainlaing mga cell ngadto sa usa
- Gigamit ang Like operator aron sulayan ang teksto batok sa usa ka maskara