Hevitra ato Anatiny
Efa nanoratra momba ny fomba ahafahanao mametaka haingana lahatsoratra avy amin'ny sela maromaro ho iray aho ary, mifanohitra amin'izany, mametaka tady lahatsoratra lava ho singa. Andeha hojerentsika ny asa akaiky, saingy somary sarotra kokoa - ny fomba hametahana lahatsoratra avy amin'ny sela maromaro rehefa misy fepetra voafaritra tsara.
Andeha atao hoe manana angon-drakitra momba ny mpanjifa izahay, izay ahafahan'ny anaran'ny orinasa iray mifanandrify amin'ny mailaka samihafa an'ny mpiasany. Ny andraikitray dia ny manangona ny adiresy rehetra amin'ny anaran'ny orinasa ary manambatra azy ireo (sarahan'ny faingo na semicolons) mba hanaovana, ohatra, ny lisitry ny mailaka ho an'ny mpanjifa, izany hoe mahazo vokatra toy ny:
Raha lazaina amin'ny teny hafa dia mila fitaovana izay hametahana (rohy) ny lahatsoratra araka ny fepetra - analogue ny asa SUMMESLI (SUMIF), fa ho an'ny lahatsoratra.
Fomba 0. Formula
Tsy tena kanto, fa ny fomba tsotra indrindra. Azonao atao ny manoratra raikipohy tsotra izay hanamarina raha tsy mitovy amin'ny teo aloha ny orinasa amin'ny laharana manaraka. Raha tsy mitovy dia apetaho ny adiresy manaraka sarahana faingo. Raha tsy mitovy izany, dia "avereno" ny voaangona, manomboka indray:
Ny tsy fahampian'ity fomba fiasa ity dia miharihary: avy amin'ny sela rehetra azo avy amin'ny tsanganana fanampiny, ny farany ihany no ilaina ho an'ny orinasa tsirairay (mavo). Raha lehibe ny lisitra, dia tsy maintsy ampidirinao tsanganana hafa amin'ny alàlan'ny fiasa izany mba hisafidianana haingana azy ireo DLSTR (LEN), manamarina ny halavan'ny tady voaangona:
Azonao atao izao ny manivana ireo ary mandika ny adiresy ilaina gluing ho ampiasaina bebe kokoa.
Fomba 1. Macrofunction ny gluing amin'ny fepetra iray
Raha toa ka tsy voalamina araka ny orinasa ny lisitra tany am-boalohany, dia tsy mandeha ny formula tsotra etsy ambony, fa afaka mandeha mora foana ianao miaraka amin'ny fiasa mahazatra kely ao amin'ny VBA. Sokafy ny Visual Basic Editor amin'ny fanindriana ny hitsin-dàlana Alt + F11 na mampiasa ny bokotra Visual Basic vakizoro Developer (mpamorona). Ao amin'ny varavarankely misokatra, ampidiro ny maody tsy misy na inona na inona amin'ny alàlan'ny menio Ampidiro - Module ary kopia ny lahatsoratry ny asantsika any:
Function MergeIf(TextRange as Range, SearchRange as Range, Condition as String) Dim Delimeter As String, i As Long Delimeter = ", " tsy mitovy ny gluings - mivoaka misy hadisoana isika Raha SearchRange.Count <> TextRange.Count Avy eo MergeIf = CVErr(xlErrRef) Exit Function End Raha 'mandeha amin'ny sela rehetra, jereo ny fepetra ary angony ny lahatsoratra ao amin'ny OutText variable For i = 1 To SearchRange. Cells.Miisa raha toa ny SearchRange.Cells(i) Toy ny toe-javatra avy eo OutText = OutText & TextRange.Cells(i) & Delimeter Manaraka aho 'mampiseho valiny tsy misy fetra farany MergeIf = Havia(OutText, Len(OutText) - Len(Delimeter)) End asa
Raha miverina amin'ny Microsoft Excel ianao izao, dia ao amin'ny lisitry ny fiasa (button fx ao amin'ny tabilao formula na tabilao Formulas - Ampidiro Function) dia ho azo atao ny mahita ny asany MergeIf amin'ny sokajy Voafaritra ny mpampiasa (voafaritr'ny mpampiasa). Ny arguments amin'ny asa dia toy izao manaraka izao:
Fomba 2. Ampifandraiso amin'ny toe-javatra tsy marina ny lahatsoratra
Raha solointsika ny tarehintsoratra voalohany amin'ny andalana faha-13 amin'ny macro = mankany amin'ny mpandraharaha lalao eo ho eo toy ny, dia azo atao ny manao gluing amin'ny alàlan'ny fifanandrinana tsy marina amin'ny angon-drakitra voalohany miaraka amin'ny mari-pankasitrahana. Ohatra, raha azo soratana amin'ny karazany samihafa ny anaran'ny orinasa, dia azontsika atao ny manamarina sy manangona azy rehetra amin'ny fiasa iray:
Tohanana ny wildcards mahazatra:
- asterisk (*) - manondro ny isan'ny tarehintsoratra rehetra (anisan'izany ny tsy fisian'izy ireo)
- marika fanontaniana (?) - mijoro ho an'ny toetra tokana
- marika pound (#) - mijoro ho an'ny isa tokana (0-9)
Amin'ny alàlan'ny default, ny operator Like dia saro-pady, izany hoe mahafantatra, ohatra, ny "Orion" sy ny "orion" ho orinasa samihafa. Mba tsy hiraharaha ny tranga, azonao atao ny manampy ny tsipika eo am-piandohan'ny maody ao amin'ny tonian-dahatsoratra Visual Basic Safidy Ampitahao lahatsoratra, izay hanova ny J'aime ho case insensitive.
Amin'izany fomba izany dia afaka mamorona saron-tava be pitsiny ianao mba hanamarinana ny fepetra, ohatra:
- ?1##??777RUS – fifantenana ny takelaka fahazoan-dàlana rehetra amin'ny faritra 777, manomboka amin'ny 1
- LLC * - orinasa rehetra izay manomboka amin'ny LLC ny anarany
- ##7## - ny vokatra rehetra misy kaody nomerika dimy isa, izay 7 ny isa fahatelo
- ????? – anarana rehetra misy litera dimy, sns.
Fomba 3. Fampiasa macro amin'ny fametahana lahatsoratra amin'ny fepetra roa
Amin'ny asa dia mety hisy olana rehefa mila mampifandray ny lahatsoratra mihoatra ny fepetra iray ianao. Ohatra, alao sary an-tsaina fa ao amin'ny latabatra teo aloha dia nampiana tsanganana iray hafa miaraka amin'ny tanàna, ary ny gluing dia tokony hatao tsy ho an'ny orinasa iray ihany, fa ho an'ny tanàna iray ihany koa. Amin'ity tranga ity, ny asa ataontsika dia tsy maintsy havaozina kely amin'ny alàlan'ny fanampim-panamarinana isan-karazany:
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 = ", " 'delimter characters (azo soloina espace na ; sns.) e.) 'raha tsy mitovy ny isan'ny fanamarinana sy ny fametahana dia mivoaha miaraka amin'ny hadisoana Raha SearchRange1.Count <> TextRange.Count na SearchRange2.Count <> TextRange.Count Avy eo MergeIfs = CVErr(xlErrRef) Exit Function End If 'Mandehana amin'ny sela rehetra, jereo ny fepetra rehetra ary manangona ny lahatsoratra ao amin'ny OutText variable For i = 1 mankany amin'ny SearchRange1.Cells.Count Raha SearchRange1.Cells(i) = Condition1 Sy SearchRange2.Cells(i) = Condition2 Avy eo OutText = OutText & TextRange.Cells(i) & Delimeter End Raha Manaraka i 'dia mampiseho valiny tsy misy fetra farany MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Hampihatra amin'ny fomba mitovy tanteraka izany - ny tohan-kevitra ihany izao no tokony hofaritana bebe kokoa:
Fomba 4. Fanakambanana sy fametahana ao amin'ny Power Query
Azonao atao ny mamaha ny olana tsy misy fandaharana ao amin'ny VBA, raha mampiasa ny fanampiny Power Query maimaim-poana ianao. Ho an'ny Excel 2010-2013 dia azo alaina eto izany, ary ao amin'ny Excel 2016 dia efa natsangana tamin'ny alàlan'ny default. Ny filaharan'ny hetsika dia ho toy izao manaraka izao:
Ny Power Query dia tsy mahay miara-miasa amin'ny latabatra mahazatra, ka ny dingana voalohany dia ny mamadika ny latabatra ho "smart". Mba hanaovana izany, safidio ary tsindrio ny fitambarana Ctrl+T na mifidy avy amin'ny tabilao Trano - Atao ho latabatra (Horonan-trano - endrika toy ny latabatra). Ao amin'ny tabilao izay miseho avy eo Mpanao (Volavola) azonao atao ny mametraka ny anaran'ny latabatra (nandao ny standard table 1):
Andao izao hampiditra ny latabatray ao amin'ny add-in Power Query. Mba hanaovana izany, eo amin'ny tabilao Data (raha manana Excel 2016 ianao) na eo amin'ny tabilao Power Query (raha manana Excel 2010-2013) tsindrio Avy amin'ny latabatra (Angona — Avy amin'ny tabilao):
Ao amin'ny varavarankely fanontana fanontaniana izay misokatra, safidio ny tsanganana amin'ny fipihana ny lohapejy Company ary tsindrio ny bokotra etsy ambony Group (Groupe By). Ampidiro ny anaran'ny tsanganana vaovao sy ny karazana asa ao amin'ny vondrona - Andalana rehetra (Lalana rehetra):
Tsindrio OK ary mahazo tabilao kely misy soatoavina voavondrona ho an'ny orinasa tsirairay. Hita mazava tsara ny ao anatin'ny tabilao raha manindry ankavia eo amin'ny fiaviana fotsy amin'ny sela ianao (fa tsy eo amin'ny lahatsoratra!)
Andeha isika hanampy tsanganana iray hafa, izay, amin'ny fampiasana ny asa, dia mametaka ny votoatin'ny tsanganana Adiresy amin'ny tsirairay amin'ireo tabilao kely, misaraka amin'ny faingo. Mba hanaovana izany, eo amin'ny tabilao Manampia tsanganana manindry izahay Tsanganana manokana (Ampio tsanganana - tsanganana manokana) ary ao amin'ny varavarankely izay miseho, ampidiro ny anaran'ny tsanganana vaovao sy ny raikipohy fampifandraisana amin'ny fiteny M natsangana ao amin'ny Power Query:
Mariho fa ny M-function rehetra dia saro-pady (tsy toy ny Excel). Rehefa avy manindry OK mahazo tsanganana vaovao misy adiresy glued izahay:
Dia mijanona ny manala ny tsanganana efa tsy ilaina TableAddresses (tsindrio havanana amin'ny lohateny) Fafao ny tsanganana) ary ampidiro ao amin'ny takelaka ny valiny amin'ny fipihana ny tabilao Home — Akatona ary alaivo (Trano - Akatona ary asio entana):
Nuance manan-danja: Tsy toy ny fomba teo aloha (asa), ny latabatra avy amin'ny Power Query dia tsy havaozina ho azy. Raha hisy fiovana amin'ny angon-drakitra loharano any aoriana, dia mila manindry havanana ianao na aiza na aiza ao amin'ny tabilao valiny ary safidio ny baiko. Fanavaozana & Tehirizo (Havaozina).
- Ahoana ny fizarana tady lahatsoratra lava ho ampahany
- Fomba maro hametahana lahatsoratra avy amin'ny sela samihafa ho iray
- Mampiasa ny operator Like hitsapana lahatsoratra amin'ny saron-tava