Contents
Ua kākau mua wau pehea e hiki ai iā ʻoe ke hoʻopili koke i nā kikokikona mai kekahi mau cell i hoʻokahi a, ʻokoʻa, hoʻokaʻawale i kahi kaula kikokikona lōʻihi i nā ʻāpana. E nānā kākou i kahi hana kokoke, akā ʻoi aku ka paʻakikī - pehea e hoʻopili ai i nā kikokikona mai kekahi mau cell ke loaʻa kahi kūlana i ʻōlelo ʻia.
E ʻōlelo kākou he waihona ʻikepili o nā mea kūʻai aku, kahi e hiki ai i kahi inoa hui ke pili i nā leka uila like ʻole o kāna mau limahana. ʻO kā mākou hana, ʻo ka hōʻiliʻili ʻana i nā helu inoa a pau e nā inoa ʻoihana a hoʻohui iā lākou (i hoʻokaʻawale ʻia e nā koma a i ʻole semicolons) i mea e hana ai, no ka laʻana, kahi papa inoa leka uila no nā mea kūʻai aku, ʻo ia hoʻi ka loaʻa ʻana o kahi mea like:
I nā huaʻōlelo ʻē aʻe, pono mākou i kahi mea hana e hoʻopili ai i ka kikokikona e like me ke kūlana - kahi analogue o ka hana. SUMMESLI (SUMIF), akā no ka kikokikona.
Keʻano 0. Kumu
ʻAʻole nani loa, akā ʻo ke ala maʻalahi. Hiki iā ʻoe ke kākau i kahi ʻano maʻalahi e nānā inā ʻokoʻa ka hui ma ka lālani aʻe mai ka mua. Inā ʻaʻole ʻokoʻa, e hoʻopili i ka ʻōlelo aʻe i hoʻokaʻawale ʻia e kahi koma. Inā ʻokoʻa, a laila "hoʻihoʻi" mākou i ka hōʻiliʻili, hoʻomaka hou:
ʻIke ʻia nā hemahema o kēia ala: mai nā ʻāpana āpau o ke kolamu hou i loaʻa, pono mākou i nā mea hope loa no kēlā me kēia hui (melemele). Inā nui ka papa inoa, a laila i mea e koho koke ai iā lākou, pono ʻoe e hoʻohui i kahi kolamu ʻē aʻe me ka hoʻohana ʻana i ka hana DLSTR (LEN), e nānā ana i ka lōʻihi o nā kaula i hōʻiliʻili ʻia:
I kēia manawa hiki iā ʻoe ke kānana i nā mea a kope i ka gluing address e pono ai no ka hoʻohana hou ʻana.
Kaʻina 1. Macrofunction o ka gluing e kekahi kūlana
Inā ʻaʻole i hoʻokaʻawale ʻia ka papa inoa mua e ka hui, a laila ʻaʻole hana ka ʻōlelo maʻalahi ma luna, akā hiki iā ʻoe ke hele maʻalahi me kahi hana maʻamau liʻiliʻi ma VBA. E wehe i ka Visual Basic Editor ma ke kaomi ʻana i kahi ʻaoʻao pōkole Alt + F11 a i ʻole ka hoʻohana ʻana i ke pihi Ke kākoʻo XIX. uku pila mea haku polokalamu (Ka mea hoʻomohala). Ma ka puka aniani e wehe ai, e hoʻokomo i kahi module kaʻawale hou ma o ka papa kuhikuhi Hoʻokomo - Module a kope i ka kikokikona o kā mākou hana ma laila:
Hana MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i Like Long Delimeter = "," ʻaʻole like nā gluings i kekahi i kekahi - ua puka mākou me ka hewa Inā SearchRange.Count <> TextRange.Count A laila MergeIf = CVErr(xlErrRef) Exit Function End Inā 'e hele i loko o nā pūnaewele a pau, e nānā i ke kūlana a hōʻiliʻili i ka kikokikona i loko o ka loli OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'hōʻike i nā hualoaʻa me ka hope ʻole MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) Hope hana
Inā hoʻi ʻoe i Microsoft Excel, a laila ma ka papa inoa o nā hana (button fx ma ka papa formula a i ʻole ka pā Nā ʻōkuhi – Hoʻokomo i ka hana) hiki ke ʻike i kā mākou hana MergeIf ma ka mahele Hoʻohālikelike ʻia ka mea hoʻohana (Ka mea hoʻohana i wehewehe). ʻO nā manaʻo no ka hana penei:
Ka Papa Hana 2. E hoʻohui i ke kikokikona ma ke kūlana pololei
Inā mākou e hoʻololi i ka hua mua ma ka laina 13 o kā mākou macro = i ka mea hoʻokani pili pili like me, a laila hiki ke hoʻokō i ka gluing e kahi hoʻokūkū kūpono ʻole o ka ʻikepili mua me ka koho koho. No ka laʻana, inā hiki ke kākau ʻia ka inoa ʻoihana i nā ʻano like ʻole, a laila hiki iā mākou ke nānā a hōʻiliʻili iā lākou āpau me hoʻokahi hana:
Kākoʻo ʻia nā kāleka kīʻaha maʻamau:
- asterisk (*) - hōʻike i kekahi helu o nā huaʻōlelo (me ka haʻalele ʻana)
- kaha nīnau (?) - kū no kēlā me kēia ʻano
- hōʻailona paona (#) – kū no kekahi huahelu (0-9)
ʻO ka mea paʻamau, ʻo ka mea hoʻohana Like ka mea maʻalahi, ʻo ia ka hoʻomaopopo ʻana, no ka laʻana, "Orion" a me "orion" ma ke ʻano he hui ʻokoʻa. No ka haʻalele ʻana i ka hihia, hiki iā ʻoe ke hoʻohui i ka laina ma ka hoʻomaka ʻana o ka module i ka hoʻoponopono Visual Basic Koho Hoʻohālikelike kikokikona, ka mea e hoʻololi Like i ka hihia ʻole.
Ma kēia ala, hiki iā ʻoe ke haku i nā masks paʻakikī loa no ka nānā ʻana i nā kūlana, no ka laʻana:
- ?1##??777RUS – koho i nā palapala laikini a pau o ka māhele 777, e hoʻomaka ana me 1
- LLC * - nā hui āpau e hoʻomaka ana ka inoa me LLC
- ##7## – nā huahana a pau me ka helu helu helu ʻelima, kahi o ka helu ʻekolu he 7
- ?????? - nā inoa āpau o nā leka ʻelima, etc.
Hana 3. Hana macro no ka hoʻopili ʻana i nā kikokikona ma lalo o ʻelua mau kūlana
Ma ka hana aia paha ka pilikia ke pono ʻoe e hoʻopili i ka kikokikona ma mua o hoʻokahi kūlana. No ka laʻana, e noʻonoʻo kākou i loko o kā mākou papa mua, ua hoʻohui ʻia kahi kolamu hou me ke kūlanakauhale, a pono e hana ʻia ka gluing ʻaʻole wale no kahi hui i hāʻawi ʻia, akā no kekahi kūlanakauhale i hāʻawi ʻia. I kēia hihia, pono e hoʻololi iki ʻia kā mākou hana ma ka hoʻohui ʻana i kahi hōʻoia ʻē aʻe iā ia:
Hana MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i Like Long Delimeter = "," 'mea hoʻokaʻawale (hiki ke pani ʻia me ka hakahaka a i ʻole ; etc.) e.) 'ina 'a'ole like ka hō'oia 'ana a me ka ho'opa'a 'ana i kekahi i kekahi, e ha'alele me ka hewa inā SearchRange. 'E hele i loko o nā pūnaewele a pau, e nānā i nā kūlana a pau a hō'ili'ili i ke kikokikona i loko o ka mea ho'ololi OutText No i = 1 To SearchRange2.Cells.Count If SearchRange1.Cells(i) = Condition1 A SearchRange1.Cells(i) = Condition1 Then OutText = OutText & TextRange.Cells(i) & Delimeter Hope Ina aʻe i 'hōʻike i nā hualoaʻa me ka wehe 'ole hope MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) Hope Function
E hoʻohana ʻia ma ke ʻano like - ʻo nā hoʻopaʻapaʻa wale nō i kēia manawa e pono e wehewehe hou ʻia:
Ka Papa Hana 4. Ka hui a me ka hoopili ana i ka Power Query
Hiki iā ʻoe ke hoʻoponopono i ka pilikia me ka ʻole o ka hoʻonohonoho ʻana ma VBA, inā ʻoe e hoʻohana i ka hoʻohui ʻana i ka Power Query manuahi. No Excel 2010-2013 hiki ke kiʻi ʻia ma aneʻi, a ma Excel 2016 ua kūkulu ʻia i loko o ka paʻamau. Penei ke kaʻina o nā hana:
ʻAʻole ʻike ʻo Power Query i ka hana me nā papa maʻamau, no laila ʻo ka hana mua e hoʻohuli i kā mākou papaʻaina i kahi "akamai". No ka hana ʻana i kēia, koho a kaomi i ka hui Ctrl+T a i ʻole koho mai ka pā Home - Hōʻano ma ke ʻano he papaʻaina (Home — Hōpili e like me ka Papa). Ma ka ʻaoʻao e hōʻike ʻia Kukahula (Hoʻolālā) hiki iā ʻoe ke hoʻonohonoho i ka inoa papa (haʻalele wau i ka standard 1 Pūnaewele):
I kēia manawa, e hoʻouka i kā mākou papaʻaina i loko o ka Power Query add-in. No ka hana ʻana i kēia, ma ka pā 'Ikepili (inā loaʻa iā ʻoe ka Excel 2016) a i ʻole ma ka Power Query tab (inā loaʻa iā ʻoe Excel 2010-2013) kaomi Mai ka papaʻaina (ʻIkepili - Mai ka Papa):
Ma ka puka aniani hoʻoponopono nīnau e wehe ana, koho i ke kolamu ma ke kaomi ʻana i ke poʻo Company a kaomi i ke pihi ma luna Group (Puʻulu Ma). E hoʻokomo i ka inoa o ke kolamu hou a me ke ʻano o ka hana i ka hui - Nā laina a pau (Lalani a pau):
Kaomi iā OK a loaʻa iā mākou kahi papa liʻiliʻi o nā waiwai i hui pū ʻia no kēlā me kēia hui. ʻIke maopopo ʻia nā mea i loko o nā papa inā ʻoe e kaomi hema ma ke kāʻei keʻokeʻo o nā cell (ʻaʻole ma ka kikokikona!) ma ke kolamu hopena:
I kēia manawa, e hoʻohui hou i hoʻokahi kolamu, kahi, me ka hoʻohana ʻana i ka hana, hoʻopili mākou i nā ʻike o nā kolamu Address i kēlā me kēia o nā papa liʻiliʻi, i hoʻokaʻawale ʻia e nā koma. No ka hana ʻana i kēia, ma ka pā Hoʻohui kolamu kaomi mākou kolamu maʻamau (Hoʻohui kolamu — kolamu maʻamau) a ma ka puka aniani e kū mai ana, e hoʻokomo i ka inoa o ke kolamu hou a me ke ʻano hoʻohui i ka ʻōlelo M i kūkulu ʻia i Power Query:
E hoʻomanaʻo, ʻo nā hana M a pau he hihia koʻikoʻi (ʻaʻole like me Excel). Ma hope o ke kaomi ʻana OK loaʻa iā mākou kahi kolamu hou me nā helu i hoʻopili ʻia:
Ke waiho nei ka wehe ʻana i ke kolamu pono ʻole Nā ʻŌlelo Aʻo (kaomi ʻākau ma ke poʻo inoa) Holoi kolamu) a hoʻouka i nā hopena i ka pepa ma ke kaomi ʻana i ka pā Home — Hoʻopili a hoʻoiho (Home — Pani a hoʻouka):
Nuance nui: ʻAʻole like me nā ʻano hana mua (nā hana), ʻaʻole hoʻonui ʻia nā papa mai Power Query. Inā i ka wā e hiki mai ana he mau loli i ka ʻikepili kumu, a laila pono ʻoe e kaomi ʻākau ma nā wahi āpau o ka papa hopena a koho i ke kauoha Hoʻohou a mālama (Hoʻohou).
- Pehea e hoʻokaʻawale ai i kahi kaula kikokikona lōʻihi i mau ʻāpana
- Nui nā ala e hoʻopili ai i nā kikokikona mai nā cell like ʻole i hoʻokahi
- Ke hoʻohana nei i ka mea hoʻohana Like e hoʻāʻo i ka kikokikona e kūʻē i kahi mask