Liaison tèks pa kondisyon

Mwen deja ekri sou fason ou ka byen vit kole tèks ki soti nan plizyè selil nan yon sèl epi, Kontrèman, analize yon fisèl tèks long nan eleman. Koulye a, ann gade nan yon travay fèmen, men yon ti kras pi konplèks - ki jan yo kole tèks ki soti nan plizyè selil lè yon sèten kondisyon espesifye satisfè. 

Ann di ke nou gen yon baz done nan kliyan, kote yon non konpayi ka koresponn ak plizyè imel diferan nan anplwaye li yo. Travay nou an se kolekte tout adrès yo pa non konpayi yo ak konkatenasyon yo (separe pa vigil oswa pwent virgule) yo nan lòd yo fè, pou egzanp, yon lis adrès pou kliyan, sa vle di jwenn pwodiksyon yon bagay tankou:

Nan lòt mo, nou bezwen yon zouti ki pral kole (lyen) tèks la selon kondisyon an - yon analogue nan fonksyon an. SUMMESLI (SUMIF), men pou tèks.

Metòd 0. Fòmil

Pa trè elegant, men fason ki pi fasil. Ou ka ekri yon fòmil senp ki pral tcheke si konpayi an nan pwochen ranje a diferan de youn anvan an. Si li pa diferan, Lè sa a, kole pwochen adrès la separe pa yon vigil. Si li diferan, Lè sa a, nou "reset" akimile a, kòmanse ankò:

Dezavantaj yo nan apwòch sa a se evidan: soti nan tout selil yo nan kolòn adisyonèl jwenn, nou bezwen sèlman dènye yo pou chak konpayi (jòn). Si lis la gwo, Lè sa a, yo nan lòd yo byen vit chwazi yo, ou pral gen ajoute yon lòt kolòn lè l sèvi avèk fonksyon an DLSTR (LEN), tcheke longè fisèl yo akimile:

Koulye a, ou ka filtre soti sa yo epi kopye adrès ki nesesè yo kole pou plis itilize.

Metòd 1. Macrofonction nan kolan pa yon sèl kondisyon

Si lis orijinal la pa klase pa konpayi, Lè sa a, fòmil ki pi wo a senp pa travay, men ou ka fasilman jwenn alantou ak yon ti fonksyon koutim nan VBA. Louvri Editè Visual Basic la lè w peze yon chemen kout klavye Alt + F11 oswa itilize bouton an Vizyèl Debaz tab pwomotè (Devlopè). Nan fenèt ki ouvè a, mete yon nouvo modil vid nan meni an Mete – Modil epi kopye tèks fonksyon nou an la:

Fonksyon MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " kolaj yo pa egal youn ak lòt - nou sòti ak yon erè Si SearchRange.Count <> TextRange.Count Lè sa a, MergeIf = CVErr(xlErrRef) Sòti Fonksyon Fen Si 'ale nan tout selil yo, tcheke kondisyon an epi kolekte tèks la nan varyab OutText Pou i = 1 Pou SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Lè sa a, OutText = OutText & TextRange.Cells (i) & Delimeter Next mwen montre rezilta san dènye delimiter MergeIf = Left (OutText, Len (OutText) - Len (Delimeter)) Fen fonksyon  

Si ou kounye a retounen nan Microsoft Excel, Lè sa a, nan lis la nan fonksyon (bouton fx nan ba fòmil la oswa tab la Fòmil - Mete Fonksyon) li pral posib jwenn fonksyon nou an FizyoneSi nan kategori Itilizatè defini (Itilizatè a defini). Agiman yo nan fonksyon an se jan sa a:

Metòd 2. Konkatenasyon tèks pa kondisyon ki pa egzak

Si nou ranplase premye karaktè nan liy 13yèm makro nou an = a operatè match apwoksimatif la Klike sou 'jèm' , Lè sa a, li pral posib pote soti nan kolan pa yon matche ak egzak nan done yo inisyal ak kritè seleksyon an. Pou egzanp, si non konpayi an ka ekri nan diferan varyant, Lè sa a, nou ka tcheke ak kolekte yo tout ak yon sèl fonksyon:

Yo sipòte kokenn estanda:

• asterisk (*) - vle di nenpòt ki kantite nenpòt karaktè (ki gen ladan absans yo)
• mak kesyon (?) – vle di nenpòt karaktè sèl
• siy liv (#) - vle di nenpòt chif (0-9)

Pa default, operatè a Like se ka sansib, sa vle di konprann, pou egzanp, "Orion" ak "orion" kòm konpayi diferan. Pou inyore ka, ou ka ajoute liy lan nan kòmansman modil la nan editè Visual Basic Opsyon Konpare tèks, ki pral chanje Like yo dwe ka ensansib.

Nan fason sa a, ou ka konpoze mask trè konplèks pou tcheke kondisyon, pou egzanp:

• ?1##??777RUS – seleksyon tout plak machin nan rejyon 777 la, kòmanse ak 1.
• LLC* – tout konpayi ki gen non kòmanse ak LLC
• ##7## - tout pwodwi ki gen yon kòd dijital senk chif, kote twazyèm chif la se 7
• ?????? – tout non senk lèt, elatriye.
  

Metòd 3. Fonksyon makro pou kole tèks anba de kondisyon

Nan travay la ka gen yon pwoblèm lè ou bezwen lyen tèks la plis pase yon kondisyon. Pou egzanp, an n imajine ke nan tablo anvan nou an, yo te ajoute yon lòt kolòn ak vil la, ak kolan yo ta dwe fèt pa sèlman pou yon konpayi bay yo, men tou pou yon vil yo bay yo. Nan ka sa a, fonksyon nou an ap gen yon ti kras modènize lè nou ajoute yon lòt chèk ranje nan li:

Fonksyon MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'karaktè delimiter (yo ka ranplase ak espas oswa ; elatriye) e.) 'si seri validasyon ak kolan yo pa egal youn ak lòt, sòti ak yon erè Si SearchRange1.Count <> TextRange.Count Oswa SearchRange2.Count <> TextRange.Count Lè sa a, MergeIfs = CVErr(xlErrRef) Fonksyon Sòti Fen Si 'ale nan tout selil, tcheke tout kondisyon yo epi kolekte tèks la nan varyab OutText Pou i = 1 Pou SearchRange1.Cells.Count Si SearchRange1.Cells (i) = Condition1 Ak SearchRange2.Cells (i) = Condition2 Lè sa a, OutText = OutText & TextRange.Cells(i) & Delimeter End If Next mwen 'montre rezilta san dènye delimiter MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) Fonksyon Fen  

Li pral aplike nan egzakteman menm jan an - se sèlman agiman kounye a bezwen espesifye plis:

Metòd 4. Gwoupman ak kole nan Power Query

Ou ka rezoud pwoblèm nan san yo pa pwograme nan VBA, si ou itilize gratis Power Query ajoute nan. Pou Excel 2010-2013 li ka telechaje isit la, ak nan Excel 2016 li deja bati nan pa default. Sekans aksyon yo pral jan sa a:

Power Query pa konnen ki jan yo travay ak tab regilye, kidonk premye etap la se vire tab nou an nan yon "entelijan". Pou fè sa, chwazi li epi peze konbinezon an Ctrl+T oswa chwazi nan tab la Kay - Fòma kòm yon tab (Kay — Fòma kòm Tablo). Sou tab la ki Lè sa a, parèt Constructor (Design) ou ka mete non tab la (mwen kite estanda a Table 1):

Koulye a, kite a chaje tab nou an nan Power Query ajoute nan. Pou fè sa, sou tab la Done (si ou gen Excel 2016) oswa sou tab la Power Query (si ou gen Excel 2010-2013) klike sou Soti nan tab la (Done - Soti nan Tablo):

Nan fenèt editè rechèch la ki ouvè, chwazi kolòn nan klike sou tèt la Konpayi epi peze bouton anlè a Gwoup (Gwoup pa). Antre non nouvo kolòn nan ak kalite operasyon an nan gwoupman an - Tout liy yo (Tout Ranje):

Klike sou OK epi nou jwenn yon mini-tab ki gen valè gwoupe pou chak konpayi. Sa ki nan tablo yo vizib klèman si ou klike sou bò gòch la sou background blan selil yo (pa sou tèks la!) nan kolòn ki lakòz la:

Koulye a, ann ajoute yon lòt kolòn, kote, lè l sèvi avèk fonksyon an, nou kole sa ki nan kolòn yo Adrès nan chak nan mini-tab yo, separe pa vigil. Pou fè sa, sou tab la Ajoute kolòn nou peze Kolòn Custom (Ajoute kolòn - kolòn Custom) epi nan fenèt ki parèt la, antre non nouvo kolòn nan ak fòmil kouple a nan lang M ki bati nan Power Query:

Remake byen ke tout M-fonksyon yo sansib (kontrèman ak Excel). Apre klike sou OK nou jwenn yon nouvo kolòn ak adrès kole:

Li rete pou retire kolòn ki deja nesesè TableAddresses (klike dwa sou tit) Efase kolòn) epi telechaje rezilta yo nan fèy la lè w klike sou tab la Kay — Fèmen epi telechaje (Kay — Fèmen epi chaje):

Nuans enpòtan: Kontrèman ak metòd anvan yo (fonksyon), tablo ki soti nan Power Query yo pa mete ajou otomatikman. Si nan lavni an pral gen nenpòt chanjman nan done sous yo, Lè sa a, w ap bezwen klike sou dwa nenpòt kote nan tablo rezilta a epi chwazi lòd la. Mete ajou & Sove (Rafrechi).

• Ki jan yo divize yon fisèl tèks long an pati
• Plizyè fason pou kole tèks ki soti nan selil diferan nan yon sèl
• Sèvi ak operatè a Like pou teste tèks kont yon mask