Okuqukethwe
Sengike ngabhala mayelana nokuthi ungashesha ukunamathisela kanjani umbhalo osuka kumaseli amaningana uye kwelinye futhi, ngokuphambene, uhlukanise intambo yombhalo omude ibe izingxenye. Manje ake sibheke ukuvala, kodwa umsebenzi oyinkimbinkimbi kakhulu - indlela yokunamathisela umbhalo ovela kumaseli amaningana lapho kuhlangatshezwana nesimo esithile esicacisiwe.
Ake sithi sinesizindalwazi samakhasimende, lapho igama lenkampani eyodwa lingahambisana nama-imeyili amaningana ahlukene wabasebenzi bayo. Umsebenzi wethu uwukuqoqa wonke amakheli ngamagama ezinkampani futhi siwahlanganise (ahlukaniswe ngokhefana noma ama-semicolon) ukuze senze, isibonelo, uhlu lwamakheli lwamakhasimende, okungukuthi, sithole okukhiphayo okufana nokuthi:
Ngamanye amazwi, sidinga ithuluzi elizonamathisela (isixhumanisi) umbhalo ngokwesimo - i-analogue yomsebenzi. I-SUMMESLI (SUMIF), kodwa okombhalo.
Indlela 0. Ifomula
Hhayi nenhle kakhulu, kodwa indlela elula. Ungabhala ifomula elula ezohlola ukuthi inkampani emgqeni olandelayo ihlukile kwedlule. Uma ingahlukani, namathisela ikheli elilandelayo elihlukaniswe ngekhoma. Uma ihluka, khona-ke "setha kabusha" okuqoqiwe, siqale futhi:
Ukungalungi kwale ndlela kusobala: kuwo wonke amaseli ekholomu eyengeziwe etholiwe, sidinga kuphela okugcina enkampanini ngayinye (ophuzi). Uma uhlu lukhulu, khona-ke ukuze uzikhethe ngokushesha, kuzodingeka wengeze enye ikholomu usebenzisa umsebenzi I-DLSTR (LEN), ihlola ubude beyunithi yezinhlamvu eziqoqiwe:
Manje usungakwazi ukuhlunga lawo futhi ukopishe i-gluing yekheli elidingekayo ukuze usebenzise futhi.
Indlela 1. I-Macrofunction ye-gluing ngesimo esisodwa
Uma uhlu lwangempela lungahlelwanga inkampani, khona-ke ifomula elula engenhla ayisebenzi, kodwa ungakwazi ukuhamba kalula nomsebenzi omncane wangokwezifiso ku-VBA. Vula i-Visual Basic Editor ngokucindezela isinqamuleli sekhibhodi I-Alt + F11 noma ngokusebenzisa inkinobho Visual Basic ithebhu Unjiniyela (Unjiniyela). Ewindini elivulayo, faka imojuli entsha engenalutho ngemenyu Faka – Imojula bese ukopisha umbhalo womsebenzi wethu lapho:
I-Function MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Fiphalisa Idelimitha Njengeyunithi yezinhlamvu, i As Long Delimeter = ", " ama-gluings awalingani - siphuma ngephutha Uma UseshoRange.Count <> TextRange.Count Bese u-MergeIf = CVERr(xlErrRef) Phuma Ekuqedeni Komsebenzi Uma 'udlula kuwo wonke amaseli, hlola isimo bese uqoqa umbhalo kokuguquguqukayo kwe-OutText For i = 1 To SearchRange. Amaseli.Bala Uma UseshoRange.Amaseli(i) Njengombandela Bese I-OutText = I-OutText & TextRange.Amaseli(i) & I-Delimeter Okulandelayo 'ngibonisa imiphumela ngaphandle kwe-delimiter yokugcina MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End umsebenzi
Uma manje ubuyela ku-Microsoft Excel, bese ohlwini lwemisebenzi (inkinobho fx kubha yefomula noma ithebhu Amafomula – Faka Umsebenzi) kuzokwazi ukuthola umsebenzi wethu HlanganisaIf esigabeni Ukuchazwa Komsebenzisi (Umsebenzisi Uchaziwe). Izimpikiswano zomsebenzi zimi kanje:
Indlela 2. Hlanganisa umbhalo ngesimo esingaqondile
Uma sishintsha umlingiswa wokuqala kulayini we-13 we-macro yethu = ku-opharetha wokufanisa Like, khona-ke kuzokwazi ukwenza i-gluing ngokufanisa okungaqondile kwedatha yokuqala ngombandela wokukhetha. Isibonelo, uma igama lenkampani lingabhalwa ngezinhlobonhlobo ezahlukene, singase sihlole futhi siwaqoqe wonke ngomsebenzi owodwa:
Amakhadi asendle ajwayelekile ayasekelwa:
- inkanyezi (*) - isho noma iyiphi inombolo yanoma yiziphi izinhlamvu (kuhlanganise nokungabikho kwazo)
- uphawu lombuzo (?) – limele noma yimuphi uhlamvu olulodwa
- uphawu lwephawundi (#) - lumelela noma iyiphi idijithi eyodwa (0-9)
Ngokuzenzakalelayo, i-Like opharetha iyazwela, okungukuthi iqonda, isibonelo, i-“Orion” ne-“orion” njengezinkampani ezahlukene. Ukuze uzibe icala, ungakwazi ukwengeza umugqa ekuqaleni kwemojuli kusihleli esibonakalayo esiyisisekelo Inketho Qhathanisa Umbhalo, ezoshintsha u-Like ingabi nazwelo.
Ngale ndlela, ungaqamba imaski eyinkimbinkimbi kakhulu yokuhlola izimo, isibonelo:
- ?1##??777RUS – ukukhethwa kwawo wonke amapuleti elayisensi esifunda 777, kuqala ngo-1
- I-LLC* - zonke izinkampani amagama azo aqala nge-LLC
- ##7## – yonke imikhiqizo enekhodi yedijithali enamadijithi amahlanu, lapho idijithi yesithathu ingu-7
- ????? – wonke amagama ezinhlamvu ezinhlanu, njll.
Indlela 3. Umsebenzi weMacro wombhalo wokunamathisela ngaphansi kwezimo ezimbili
Emsebenzini kungase kube nenkinga lapho udinga ukuxhumanisa umbhalo ngaphezu kwesimo esisodwa. Isibonelo, ake sicabange ukuthi etafuleni lethu langaphambilini, ikholomu eyodwa ngaphezulu nedolobha yanezelwa, futhi i-gluing akufanele yenziwe kuphela inkampani enikeziwe, kodwa futhi nedolobha elinikeziwe. Kulokhu, umsebenzi wethu kuzodingeka ulungiswe kancane ngokwengeza okunye ukuhlola kobubanzi kuwo:
Function MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 Njengo Range, Condition2 As String) Fiphalisa Idelimitha NjengeString, i As Long Delimeter = ", " 'izinhlamvu zedelimiter (ingathathelwa indawo isikhala noma; njll.) e.) 'uma ukuqinisekiswa nobubanzi bokunamathisela kungalingani, phuma ngephutha Uma UseshoRange1.Count <> TextRange.Count Noma SearchRange2.Count <> TextRange.Count Then MergeIfs = CVERr(xlErrRef) Phuma Umsebenzi End Uma 'hamba kuwo wonke amaseli, hlola zonke izimo bese uqoqa umbhalo kokuguquguqukayo Okuphumayo For i = 1 To SearchRange1.Amaseli.Bala Uma UseshoRange1.Amaseli(i) = Condition1 And SearchRange2.Amaseli(i) = Condition2 Bese I-OutText = OutText & I-TextRange.Amaseli(i) & Delimeter End Uma Okulandelayo 'ngibonisa imiphumela ngaphandle kwedelimiter yokugcina MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Izosetshenziswa ngendlela efanayo - izimpikiswano kuphela manje ezidinga ukucaciswa okwengeziwe:
Indlela 4. Ukuhlanganisa nokunamathisela embuzweni wamandla
Ungakwazi ukuxazulula inkinga ngaphandle kokuhlela ku-VBA, uma usebenzisa isengezo samahhala Sombuzo Wamandla. Ku-Excel 2010-2013 ingalandwa lapha, futhi ku-Excel 2016 isivele yakhelwe ngaphakathi ngokuzenzakalelayo. Ukulandelana kwezenzo kuzoba kanje:
Umbuzo Wamandla awukwazi ukusebenza ngamathebula avamile, ngakho-ke isinyathelo sokuqala ukuguqula ithebula lethu libe "elihlakaniphile". Ukuze wenze lokhu, yikhethe bese ucindezela inhlanganisela Ctrl+T noma khetha kuthebhu Ikhaya – Fometha njengetafula (Ekhaya — Fometha Njengethebula). Kuthebhu evelayo umakhi (Umklamo) ungasetha igama letafula (ngishiye okujwayelekile Ithebula 1):
Manje ake silayishe ithebula lethu kusengezo Sombuzo Wamandla. Ukuze wenze lokhu, kuthebhu Idatha (uma une-Excel 2016) noma kuthebhu ethi Umbuzo Wamandla (uma une-Excel 2010-2013) chofoza Kusukela etafuleni (Idatha - Kusuka Kuthebula):
Ewindini lomhleli wombuzo elivulekayo, khetha ikholomu ngokuchofoza unhlokweni Company bese ucindezela inkinobho engenhla Group (Iqembu Nge). Faka igama lekholomu entsha kanye nohlobo lokusebenza eqenjini - Yonke imigqa (Yonke Imigqa):
Chofoza okuthi KULUNGILE futhi sithola ithebula elincane lamanani aqoqwe enkampanini ngayinye. Okuqukethwe kwamathebula kubonakala kahle uma uchofoza kwesokunxele ingemuva elimhlophe lamaseli (hhayi embhalweni!) kukholomu ewumphumela:
Manje ake sengeze ikholomu eyodwa, lapho, sisebenzisa umsebenzi, sinamathisela okuqukethwe kwamakholomu ekheli etafuleni ngalinye elincane, elihlukaniswe ngamakhoma. Ukuze wenze lokhu, kuthebhu Faka ikholamu siyacindezela Ikholomu yangokwezifiso (Engeza ikholomu — Ikholomu yangokwezifiso) futhi efasiteleni elivelayo, faka igama lekholomu entsha kanye nefomula yokuhlanganisa ngolimi luka-M olwakhelwe kumbuzo wamandla:
Qaphela ukuthi yonke imisebenzi ye-M ibucayi kakhulu (ngokungafani ne-Excel). Ngemva kokuchofoza OK sithola ikholomu entsha enamakheli anamathiselwe:
Kusele ukususa ikholomu evele ingadingekile Amakheli ethebula (chofoza kwesokudla esihlokweni) Susa ikholomu) bese ulayisha imiphumela eshidini ngokuchofoza ithebhu Ikhaya — Vala bese ulanda (Ekhaya - Vala futhi ulayishe):
I-nuance ebalulekile: Ngokungafani nezindlela zangaphambilini (imisebenzi), amathebula avela kumbuzo wamandla awabuyekezwa ngokuzenzakalelayo. Uma ngokuzayo kuzoba nanoma yiziphi izinguquko kudatha yomthombo, uzodinga ukuchofoza kwesokudla noma yikuphi kuthebula lemiphumela bese ukhetha umyalo. Buyekeza & Londoloza (Vuselela).
- Indlela yokuhlukanisa iyunithi yezinhlamvu ezinde zibe izingxenye
- Izindlela ezimbalwa zokunamathisela umbhalo osuka kumaseli ahlukene uye kwelinye
- Ukusebenzisa i-Like opharetha ukuhlola umbhalo ngemaski