txheem
Kuv twb tau sau txog yuav ua li cas koj tuaj yeem ceev cov ntawv nplaum los ntawm ntau lub hlwb rau hauv ib qho thiab, sib piv, cais cov kab ntawv ntev rau hauv cov khoom. Tam sim no cia saib ze, tab sis me ntsis txoj haujlwm nyuaj - yuav ua li cas rau cov ntawv nplaum los ntawm ob peb lub hlwb thaum muaj qee qhov xwm txheej tau ntsib.
Cia peb hais tias peb muaj cov ntaub ntawv ntawm cov neeg siv khoom, qhov twg ib lub tuam txhab npe tuaj yeem cuam tshuam rau ntau yam emails ntawm nws cov neeg ua haujlwm. Peb txoj hauj lwm yog los sau tag nrho cov chaw nyob los ntawm cov tuam txhab npe thiab sib txuas lawv (sib cais los ntawm commas lossis semicolons) txhawm rau ua, piv txwv li, daim ntawv xa ntawv rau cov neeg siv khoom, piv txwv li tau txais cov khoom lag luam xws li:
Hauv lwm lo lus, peb xav tau ib lub cuab yeej uas yuav nplaum (txuas) cov ntawv nyeem raws li qhov xwm txheej - ib qho analogue ntawm kev ua haujlwm SUMMESLI (SUMIF), tab sis rau ntawv.
Txoj kev 0. Formula
Tsis heev elegant, tab sis txoj kev yooj yim tshaj. Koj tuaj yeem sau cov qauv yooj yim uas yuav kuaj xyuas seb lub tuam txhab nyob rau kab tom ntej txawv ntawm qhov dhau los. Yog hais tias nws tsis txawv, ces muab lo rau qhov chaw nyob tom ntej sib cais los ntawm ib qho comma. Yog tias nws txawv, ces peb "rov pib dua" cov khoom sau, pib dua:
Qhov tsis zoo ntawm txoj hauv kev no yog qhov pom tseeb: los ntawm tag nrho cov hlwb ntawm kab ntxiv tau txais, peb xav tau tsuas yog cov kawg nkaus rau txhua lub tuam txhab (daj). Yog tias daim ntawv teev npe loj, ces txhawm rau xaiv lawv sai, koj yuav tsum tau ntxiv lwm kem siv cov haujlwm DLSTR (LEN), xyuas qhov ntev ntawm cov hlua sib sau:
Tam sim no koj tuaj yeem lim tawm cov sawv daws yuav thiab luam qhov chaw nyob tsim nyog gluing rau kev siv ntxiv.
Txoj kev 1. Macrofunction ntawm gluing los ntawm ib tug mob
Yog tias thawj daim ntawv teev npe tsis raug txheeb xyuas los ntawm lub tuam txhab, tom qab ntawd cov qauv yooj yim saum toj no tsis ua haujlwm, tab sis koj tuaj yeem yooj yim mus ncig nrog kev ua haujlwm me me hauv VBA. Qhib Visual Basic Editor los ntawm nias lub keyboard shortcut Alt + F11 los yog siv lub khawm Visual Basic tab tsim tawm (Tus tsim tawm). Nyob rau hauv lub qhov rais uas qhib, ntxig ib tug tshiab khoob module los ntawm cov ntawv qhia zaub mov Ntxig - Module thiab luam cov ntawv ntawm peb txoj haujlwm muaj:
Function MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = "," gluings tsis sib npaug - peb tawm nrog qhov yuam kev Yog SearchRange.Count <> TextRange.Count Ces MergeIf = CVErr(xlErrRef) Exit Function End Yog tias 'mus dhau tag nrho cov hlwb, tshawb xyuas qhov xwm txheej thiab sau cov ntawv hauv qhov sib txawv OutText Rau i = 1 Rau SearchRange. Cells.Count Yog SearchRange.Cells(i) Li Cas Ces OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'display results without last delimiter MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End muaj nuj nqi
Yog tias koj tam sim no rov qab mus rau Microsoft Excel, tom qab ntawd hauv cov npe ntawm cov haujlwm (khawm fx nyob rau hauv lub formula bar los yog tab Formulas – Insert Function) nws yuav tuaj yeem nrhiav tau peb txoj haujlwm Merge If hauv qeb Neeg Siv Li (User Defined). Cov lus sib cav rau lub luag haujlwm yog raws li hauv qab no:
Txoj Kev 2. Muab cov ntawv sau los ntawm qhov xwm txheej tsis raug
Yog tias peb hloov thawj tus cim hauv kab 13 ntawm peb macro = mus rau approximate match operator zoo li, ces nws yuav muaj peev xwm mus nqa tawm gluing los ntawm ib tug inexact match ntawm thawj cov ntaub ntawv nrog rau cov kev xaiv. Piv txwv li, yog tias lub tuam txhab lub npe tuaj yeem sau ua ntau yam sib txawv, ces peb tuaj yeem tshawb xyuas thiab sau lawv tag nrho nrog ib qho haujlwm:
Standard wildcards tau txais kev txhawb nqa:
- asterisk (*) - qhia txog ib tus lej ntawm cov cim (nrog rau lawv qhov tsis tuaj)
- lo lus nug cim (?) - sawv cev rau ib tus cwj pwm
- phaus kos npe (#) - sawv cev rau ib tus lej (0-9)
Los ntawm lub neej ntawd, Tus Neeg Ua Haujlwm Zoo li yog cov ntaub ntawv nkag siab, piv txwv li, "Orion" thiab "orion" raws li cov tuam txhab sib txawv. Txhawm rau tsis quav ntsej cov ntaub ntawv, koj tuaj yeem ntxiv kab ntawm qhov pib ntawm lub module hauv Visual Basic editor Option Sib Piv Cov Ntawv, uas yuav hloov Like los yog case insensitive.
Ua li no, koj tuaj yeem tsim cov npog ntsej muag nyuaj heev rau kev kuaj xyuas cov xwm txheej, piv txwv li:
- ?1##??777RUS - xaiv tag nrho daim ntawv tso cai daim ntawv tso cai ntawm cheeb tsam 777, pib nrog 1
- LLC * - txhua lub tuam txhab uas nws lub npe pib nrog LLC
- ##7## - tag nrho cov khoom nrog tsib tus lej digital code, qhov twg tus lej thib peb yog 7
- ????? - tag nrho cov npe ntawm tsib tsab ntawv, thiab lwm yam.
Txoj kev 3. Macro muaj nuj nqi rau gluing ntawv nyob rau hauv ob yam mob
Hauv kev ua haujlwm tej zaum yuav muaj teeb meem thaum koj xav tau txuas cov ntawv nyeem ntau dua ib qho xwm txheej. Piv txwv li, cia peb xav txog tias nyob rau hauv peb lub rooj dhau los, ib kab ntxiv nrog lub nroog tau ntxiv, thiab gluing yuav tsum tau nqa tawm tsis yog rau lub tuam txhab muab, tab sis kuj rau ib lub nroog. Hauv qhov no, peb txoj haujlwm yuav tsum tau hloov kho me ntsis los ntawm kev ntxiv lwm qhov kev kuaj xyuas rau nws:
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 cim (tuaj yeem hloov nrog qhov chaw lossis ; thiab lwm yam) e.) 'yog tias qhov validation thiab gluing ranges tsis sib npaug rau ib leeg, tawm nrog qhov yuam kev Yog SearchRange1.Count <> TextRange.Count lossis SearchRange2.Count <> TextRange.Count Ces MergeIfs = CVERr(xlErrRef) Exit Function End Yog ' mus dhau txhua lub hlwb, xyuas txhua yam xwm txheej thiab sau cov ntawv mus rau qhov sib txawv OutText Rau i = 1 Rau SearchRange1.Cells.Count Yog SearchRange1.Cells(i) = Condition1 Thiab SearchRange2.Cells(i) = Condition2 Ces OutText = OutText & TextRange.Cells(i) & Delimeter Xaus Yog Tom ntej no kuv 'tso tawm cov txiaj ntsig yam tsis muaj qhov kawg delimiter MergeIfs = Left(OutText, Len(Text) - Len(Delimeter)) End Function
Nws yuav raug siv raws nraim tib txoj kev - tsuas yog cov lus sib cav tam sim no yuav tsum tau teev ntau ntxiv:
Txoj Kev 4. Muab pab pawg thiab gluing hauv Power Query
Koj tuaj yeem daws qhov teeb meem yam tsis muaj kev ua haujlwm hauv VBA, yog tias koj siv lub zog lus nug dawb ntxiv-hauv. Rau Excel 2010-2013 nws tuaj yeem rub tawm ntawm no, thiab hauv Excel 2016 nws twb tau ua los ntawm lub neej ntawd. Cov txheej txheem ntawm kev ua yuav yog raws li nram no:
Power Query tsis paub yuav ua li cas ua haujlwm nrog cov ntxhuav tsis tu ncua, yog li thawj kauj ruam yog tig peb lub rooj mus rau "ntse" ib qho. Txhawm rau ua qhov no, xaiv nws thiab nias qhov sib xyaw ua ke Ctrl+T los yog xaiv los ntawm lub tab Tsev – Format ua ib lub rooj (Lub Tsev — Format as Table). Ntawm lub tab uas ces tshwm constructor (Tsim) koj tuaj yeem teem lub npe lub rooj (Kuv tawm ntawm tus qauv rooj 1):
Tam sim no cia peb thauj peb lub rooj rau hauv Power Query add-in. Txhawm rau ua qhov no, ntawm lub tab Cov ntaub ntawv (yog tias koj muaj Excel 2016) lossis nyob rau ntawm Power Query tab (yog tias koj muaj Excel 2010-2013) nyem Los ntawm lub rooj (Cov ntaub ntawv - Los ntawm Table):
Nyob rau hauv cov lus nug editor qhov rais uas qhib, xaiv kab los ntawm txhaj rau lub header lub tuam txhab thiab nias lub pob saum toj no Group (Group Los ntawm). Sau lub npe ntawm kab ntawv tshiab thiab hom kev ua haujlwm hauv pab pawg - Txhua kab (Txhua Kab):
Nyem OK thiab peb tau txais ib lub rooj me me ntawm pawg muaj txiaj ntsig rau txhua lub tuam txhab. Cov ntsiab lus ntawm cov ntxhuav tau pom meej meej yog tias koj sab laug-nias rau ntawm cov keeb kwm dawb ntawm cov hlwb (tsis yog ntawm cov ntawv nyeem!) hauv kab ntawv tshwm sim:
Tam sim no cia peb ntxiv ib kem ntxiv, qhov twg, siv lub luag haujlwm, peb muab cov ntsiab lus ntawm Chaw Nyob txhua qhov ntawm cov ntxhuav, sib cais los ntawm commas. Txhawm rau ua qhov no, ntawm lub tab Ntxiv kem peb nias Kev cai kem (Ntxiv kab - Custom column) thiab nyob rau hauv lub qhov rais uas tshwm sim, sau lub npe ntawm kab tshiab thiab cov qauv sib txuas hauv M hom lus ua rau Power Query:
Nco ntsoov tias txhua qhov M-functions yog cov ntaub ntawv nkag siab (tsis zoo li Excel). Tom qab nias rau OK peb tau txais ib kab tshiab nrog cov chaw nyob glued:
Nws tseem yuav tshem cov kab uas twb tsis tsim nyog lawm TableAddresses (right click on title) Rho tawm kab) thiab upload cov txiaj ntsig mus rau daim ntawv los ntawm nyem rau ntawm lub tab Tsev - Kaw thiab rub tawm (Lub Tsev - Kaw thiab thauj khoom):
Tseem ceeb nuance: Tsis zoo li cov txheej txheem dhau los (kev ua haujlwm), cov lus los ntawm Lub Hwj Chim Query tsis hloov kho tshiab. Yog tias yav tom ntej yuav muaj kev hloov pauv hauv cov ntaub ntawv hauv qhov chaw, tom qab ntawd koj yuav tsum tau nyem rau txhua qhov chaw hauv cov lus tau txais thiab xaiv cov lus txib Hloov tshiab & Txuag (Refresh).
- Yuav ua li cas faib ib txoj hlua ntev rau hauv qhov chaw
- Muaj ntau txoj hauv kev los nplaum cov ntawv los ntawm cov hlwb sib txawv rau hauv ib qho
- Siv tus neeg teb xov tooj Zoo li los ntsuas cov ntawv tawm tsam lub ntsej muag