Clàr-innse
Is e aon de na gnìomhan as motha a tha a’ caitheamh ùine agus a tha duilich nuair a bhios tu ag obair le teacsa ann an Excel parsadh - a’ parsadh an “lite” alphanumeric gu co-phàirtean agus a’ toirt a-mach na criomagan a dh’ fheumas sinn bhuaithe. Mar eisimpleir:
- toirt a-mach an còd zip bhon t-seòladh (tha e math ma tha an còd-puist an-còmhnaidh aig an toiseach, ach dè mura h-eil?)
- lorg àireamh agus ceann-latha an fhàirdeal bhon tuairisgeul air a’ phàigheadh anns an aithris banca
- toirt a-mach TIN bho thuairisgeulan motley de chompanaidhean anns an liosta de cho-phàrtaidhean
- lorg àireamh càr no àireamh artaigil san tuairisgeul, msaa.
Mar as trice ann an leithid de chùisean, às deidh leth uair a thìde de bhith a ’togail an teacsa le làimh, bidh smuaintean a’ tòiseachadh a ’tighinn gu inntinn dòigh air choireigin gus am pròiseas seo a dhèanamh fèin-ghluasadach (gu sònraichte ma tha tòrr dàta ann). Tha grunn fhuasglaidhean ann agus le diofar ìrean de iom-fhillteachd-èifeachdas:
- cleachdadh Feartan teacsa Excel togte airson teacsa lorg-gearradh-glue: LEVSIMV (CLÌ), DEAS (DEAS), PSTR (meadhan), STsEPIT (CONCATENATE) agus a analogues, COMHRADH (JOINTEXT), MARBHRANN (EXACT) msaa Tha an dòigh seo math ma tha loidsig shoilleir anns an teacsa (mar eisimpleir, tha an clàr-amais an-còmhnaidh aig toiseach an t-seòlaidh). Rud eile, bidh na foirmlean a ’fàs tòrr nas iom-fhillte agus, uaireannan, thig e eadhon gu foirmlean rèite, a bhios gu mòr a’ slaodadh sìos air bùird mòra.
- cleachdadh mar ghnìomhaiche coltach ri teacsa bho Visual Basic air a phasgadh ann an gnìomh macro àbhaisteach. Leigidh seo leat sgrùdadh nas sùbailte a chuir an gnìomh a’ cleachdadh caractaran cairt-fiadhaich (*, #, ?, msaa.) Gu mì-fhortanach, chan urrainn don inneal seo am fo-sreang a tha thu ag iarraidh a thoirt a-mach às an teacsa - dìreach thoir sùil a bheil e ann.
A bharrachd air na tha gu h-àrd, tha dòigh-obrach eile ann a tha gu math ainmeil ann an cearcallan cumhang de phrògraman proifeasanta, luchd-leasachaidh lìn agus teicneòlaichean eile - is e seo abairtean cunbhalach (Sloinneadh cunbhalach = RegExp = "regexps" = "cunbhalach"). Gu sìmplidh, Is e cànan a th’ ann an RegExp far a bheilear a’ cleachdadh caractaran agus riaghailtean sònraichte gus na fo-thiotalan riatanach san teacsa a lorg, an toirt a-mach no teacsa eile a chuir nan àite.. Tha abairtean cunbhalach nan inneal fìor chumhachdach agus breagha a tha a’ dol thairis air a h-uile dòigh eile air obrachadh le teacsa a rèir meud. Bidh mòran de chànanan prògramaidh (C#, PHP, Perl, JavaScript…) agus luchd-deasachaidh teacsa (Word, Notepad ++…) a’ toirt taic do abairtean cunbhalach.
Gu mì-fhortanach chan eil taic RegExp aig Microsoft Excel a-mach às a 'bhogsa, ach faodar seo a shuidheachadh gu furasta le VBA. Fosgail an Deasaiche Visual Basic bhon taba -leasachaidh (Leasaiche) no ath-ghoirid meur-chlàr Alt+F11. An uairsin cuir a-steach am modal ùr tron chlàr Cuir a-steach - modal agus dèan lethbhreac de theacsa a’ ghnìomh macro a leanas an sin:
Gnìomh Poblach RegExpExtract (Teacsa mar String, Pàtran mar Shreang, Nì Roghainneil mar Shlànaighear = 1) Mar an t-sreang air Mearachd GoTo ErrHandl Set regex = CreateObject (“VBScript.RegExp”) regex.Pattern = Pàtran regex.Global = Fìor Ma tha regex.Test (Teacsa) An uairsin Suidhich maidsean = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Deireadh gnìomh fàgail ma tha ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function
Faodaidh sinn a-nis an Deasaiche Visual Basic a dhùnadh agus tilleadh gu Excel gus am feart ùr againn fheuchainn. Tha an co-chòrdadh aige mar a leanas:
=RegExpExtract(Txt; Pàtran; Nì)
far a bheil
- txt – cealla leis an teacsa a tha sinn a’ sgrùdadh agus às a bheil sinn airson an fho-thalamh a tha a dhìth oirnn a thoirt a-mach
- pàtran - masg (pàtran) airson sgrùdadh fo-thalamh
- Notaichean - àireamh sreath an fho-sreang a thèid a thoirt a-mach, ma tha grunn dhiubh ann (mura h-eil sin air a shònrachadh, tha a’ chiad tachartas air a thaisbeanadh)
Is e an rud as inntinniche an seo, gu dearbh, Pàtran - sreath teamplaid de charactaran sònraichte “ann an cànan” RegExp, a tha a ’sònrachadh dè dìreach agus càite a bheil sinn airson a lorg. Seo an fheadhainn as bunaitiche airson do thòiseachadh:
| Pattern | Tuairisgeul |
| . | Is e dot an rud as sìmplidh. Bidh e a’ maidseadh caractar sam bith sa phàtran aig an t-suidheachadh ainmichte. |
| s | Caractar sam bith a tha coltach ri àite (fànais, taba, no briseadh loidhne). |
| S | Anti-caochladh den phàtran a bh’ ann roimhe, ie caractar neo-gheala sam bith. |
| d | Àireamh sam bith |
| D | Anti-caochladh den fhear roimhe, ie NACH EIL digit |
| w | Caractar Laideann sam bith (AZ), figear, no fo-sgrìobhadh |
| W | Frith-chaochladh den fhear roimhe, ie chan e Laideann, chan e àireamh agus chan e fo-sgrìobhadh. |
| [caractaran] | Eadar camagan ceàrnagach, faodaidh tu aon charactar no barrachd a shònrachadh a tha ceadaichte aig an t-suidheachadh ainmichte san teacsa. Mar eisimpleir Gaileiridh a fhreagras air aon de na facail : Clàr or cathraiche. Chan urrainn dhut cuideachd caractaran àireamhachadh, ach an suidheachadh mar raon air a sgaradh le tàthan, ie an àite [ABDCDEF] sgrìobhadh [AF]. no an àite sin [4567] thoirt a-steach [-4 7]. Mar eisimpleir, gus a h-uile caractar Cyrillic ainmeachadh, faodaidh tu an teamplaid a chleachdadh [a-ya-YayoYo]. |
| [^caractaran] | Ma chuireas tu an samhla “còmhdaich” às deidh a’ bhreic cheàrnagach fosglaidh ^, an uairsin gheibh an seata a’ bhrìgh eile - aig an t-suidheachadh ainmichte san teacsa, bidh cead aig a h-uile caractar, ach a-mhàin an fheadhainn a tha air an liostadh. Tha, teamplaid [^ЖМ]ut a lorg Slighe or Substance or Forget, ach chan ann Sgeulach or misneach, me. |
| | | Gnìomhaiche Boole OR (NO) gus sgrùdadh a dhèanamh airson gin de na slatan-tomhais ainmichte. Mar eisimpleir (bhoThu|seadhon | fàirdeal) rannsaichidh e an teacsa airson gin de na faclan ainmichte. Gu h-àbhaisteach, tha seata de roghainnean dùinte ann am brathan. |
| ^ | Tòiseachadh na loidhne |
| $ | Deireadh loidhne |
| b | Deireadh an fhacail |
Ma tha sinn a’ coimhead airson àireamh sònraichte de charactaran, mar eisimpleir, còd-puist sia-dhigitach no a h-uile còd toraidh trì-litreach, thig sinn gu teasairginn luchd-tomhais or luchd-tomhais nan abairtean sònraichte a tha a’ sònrachadh an àireamh de charactaran a thèid a sgrùdadh. Bithear a’ cur tomhasan ris a’ charactar a tha air thoiseach air:
| Quantor | Tuairisgeul |
| ? | Zero no aon tachartas. Mar eisimpleir .? a’ ciallachadh aon charactar no a neo-làthaireachd. |
| + | Aon inntrigeadh no barrachd. Mar eisimpleir d+ a’ ciallachadh àireamh sam bith de dh’ àireamhan (ie àireamh sam bith eadar 0 agus Infinity). |
| * | Gun tachartas no barrachd, ie meud sam bith. Mar sin s* a 'ciallachadh àireamh sam bith de dh'àiteachan no àiteachan sam bith. |
| {àireamh} or {àireamh1,àireamh2} | Ma dh'fheumas tu àireamh shònraichte de thachartasan a shònrachadh, tha e air a shònrachadh ann am braces lùbach. Mar eisimpleir d{6} a 'ciallachadh gu cruaidh sia àireamhan, agus am pàtran s{2,5} - dhà gu còig àiteachan |
A-nis gluaisidh sinn air adhart chun a 'phàirt as inntinniche - mion-sgrùdadh air cleachdadh na gnìomh cruthaichte agus na dh'ionnsaich sinn mu phàtranan air eisimpleirean practaigeach bho bheatha.
Luchdaich a-nuas àireamhan bho teacsa
An toiseach, dèanamaid sgrùdadh air cùis shìmplidh - feumaidh tu a 'chiad àireamh a thoirt a-mach à brochan alphanumeric, mar eisimpleir, cumhachd solar cumhachd gun bhriseadh bhon liosta phrìsean:
Tha an loidsig air cùl an abairt àbhaisteach sìmplidh: d ciallachadh figear sam bith, agus an tomhas-tomhais + ag ràdh gum bu chòir an àireamh aca a bhith mar aon no barrachd. Tha feum air an minus dùbailte air beulaibh na gnìomh gus “air an iteig” na caractaran a chaidh a thoirt a-mach a thionndadh gu àireamh slàn bhon àireamh-mar-theacsa.
zip code
Aig a 'chiad sealladh, tha a h-uile dad sìmplidh an seo - tha sinn a' coimhead airson dìreach sia àireamhan ann an sreath. Bidh sinn a’ cleachdadh caractar sònraichte d airson figear agus àireamhair 6 {} airson an àireamh de charactaran:
Ach, tha suidheachadh comasach nuair a tha, air taobh clì a’ chlàr-amais san loidhne, seata mòr eile de àireamhan ann an sreath (àireamh fòn, TIN, cunntas banca, msaa.) An uairsin tarraingidh an seusan cunbhalach againn a-mach a’ chiad 6. àireamhan bhuaithe, ie chan obraich e gu ceart:
Gus casg a chuir air seo, feumaidh sinn inneal-atharrachaidh a chuir timcheall oirean ar faireachdainn àbhaisteach b a' ciallachadh deireadh focail. Nì seo e soilleir do Excel gum bu chòir don chriomag (clàr-amais) a tha a dhìth oirnn a bhith na fhacal air leth, agus chan ann mar phàirt de chriomag eile (àireamh fòn):
Phone
Is e an duilgheadas le bhith a’ lorg àireamh fòn anns an teacsa gu bheil uimhir de roghainnean ann airson àireamhan a sgrìobhadh – le agus às aonais tàthan, tro fhànais, le no às aonais còd sgìreil eadar camagan, msaa. Mar sin, nam bheachd-sa, tha e nas fhasa an toiseach glan a-mach na caractaran sin uile bhon teacsa tùsail a’ cleachdadh grunn ghnìomhan neadachaidh SUBSTITUIBH (SUBSTITUTE)gus am bi e a 'ceangal ri chèile ann an aon iomlan, agus an uair sin le prìomhadail riaghailt d{11} tarraing a-mach 11 àireamhan ann an sreath:
ITN
Tha e beagan nas iom-fhillte an seo, oir faodaidh TIN (nar Dùthaich) a bhith 10-digit (airson buidhnean laghail) no 12-fhigearach (airson daoine fa leth). Mura lorg thu coire gu sònraichte, tha e gu math comasach a bhith riaraichte leis an àbhaist d{10,12}, ach, gu cruaidh, tarraingidh e a-mach na h-àireamhan gu lèir bho 10 gu 12 caractaran, ie agus chaidh 11 àireamhan a-steach gu mearachdach. Bhiodh e na bu chòir dà phàtran a chleachdadh ceangailte le gnìomhaiche OR loidsigeach | (bàr dìreach):
Thoir an aire gum bi sinn sa cheist an toiseach a’ coimhead airson àireamhan 12-bit, agus dìreach an uairsin airson àireamhan 10-bit. Ma sgrìobhas sinn ar faireachdainn cunbhalach an taobh eile, tarraingidh e a-mach airson a h-uile duine, eadhon TINan fada 12-bit, dìreach a’ chiad 10 caractaran. Is e sin, às deidh a’ chiad staid a bhith air a phiobrachadh, chan eil tuilleadh dearbhaidh air a dhèanamh tuilleadh:
Is e seo an eadar-dhealachadh bunaiteach eadar an gnìomhaiche | bho ghnìomh loidsig excel àbhaisteach OR (NO), far nach atharraich ath-eagrachadh nan argamaidean an toradh.
SKUan toraidh
Ann am mòran chompanaidhean, tha aithnichearan sònraichte air an sònrachadh airson bathar agus seirbheisean - artaigilean, còdan SAP, SKUs, msaa. Mar eisimpleir, ma tha fios againn gu bheil na h-artaigilean againn an-còmhnaidh air an dèanamh suas de thrì prìomh litrichean Beurla, tàthan agus àireamh trì-fhigearach às deidh sin, an uairsin:
Tha an loidsig air cùl an teamplaid sìmplidh. [AZ] - a’ ciallachadh prìomh litrichean sam bith den aibideil Laideann. An ath tomhais 3 {} ag ràdh gu bheil e cudromach dhuinne gu bheil dìreach trì litrichean mar sin ann. Às deidh an tàthan, tha sinn a’ feitheamh trì àireamhan, agus mar sin cuiridh sinn ris aig an deireadh d{3}
Suimean airgid
San aon dòigh ris a’ pharagraf roimhe, faodaidh tu cuideachd prìsean (cosgaisean, VAT ...) a tharraing a-mach bhon tuairisgeul air bathar. Ma tha suimean airgid, mar eisimpleir, air an comharrachadh le tàthan, an uairsin:
Pattern d le tomhas-tomhais + lorg aireamh sam bith suas gu tàthan, agus d{2} coimheadaidh e airson sgillinnean (dà fhigear) às a dhèidh.
Ma dh’fheumas tu chan e prìsean a thoirt a-mach, ach VAT, faodaidh tu an treas argamaid roghnach den ghnìomh RegExpExtract againn a chleachdadh, a shònraicheas àireamh òrdail na h-eileamaid a thèid a thoirt a-mach. Agus, gu dearbh, faodaidh tu an gnìomh a chuir na àite SUBSTITUIBH (SUBSTITUTE) anns na co-dhùnaidhean, tàthan chun an dealaiche deicheach àbhaisteach agus cuir a-steach dùbailte minus aig an toiseach gus am bi Excel a’ mìneachadh a’ VAT a chaidh a lorg mar àireamh àbhaisteach:
Àireamhan clàr càr
Mura gabh thu carbadan sònraichte, luchd-tarraing agus baidhsagalan-motair eile, tha an àireamh càr àbhaisteach air a pharsadh a rèir a’ phrionnsapail “litir - trì àireamhan - dà litir - còd sgìre”. A bharrachd air an sin, faodaidh còd na sgìre a bhith 2- no 3-fhigearach, agus chan eil ach an fheadhainn a tha coltach ann an coltas ris an aibideil Laideann air an cleachdadh mar litrichean. Mar sin, cuidichidh an abairt àbhaisteach a leanas sinn gus àireamhan a tharraing às an teacsa:
Time
Gus an ùine a thoirt a-mach ann an cruth HH:MM, tha an abairt àbhaisteach a leanas freagarrach:
Às deidh criomag coloin [0-5]d, mar a tha e furasta obrachadh a-mach, a 'suidheachadh àireamh sam bith anns an raon 00-59. Ron choloin ann am brathan, bidh dà phàtran ag obair, air an sgaradh le OR loidsigeach (pìob):
- [0-1]d - àireamh sam bith anns an raon 00-19
- 2[0-3] - àireamh sam bith anns an raon 20-23
Gus an toradh a gheibhear, faodaidh tu cuideachd an gnìomh àbhaisteach Excel a chuir an sàs TIME (TEAM)gus a thionndadh gu cruth ùine a tha furasta a thuigsinn don phrògram agus a tha freagarrach airson tuilleadh àireamhachadh.
Sgrùdadh facal-faire
Seach gum feum sinn sgrùdadh a dhèanamh air an liosta de na faclan-faire a chruthaich luchd-cleachdaidh airson ceart. A rèir ar riaghailtean, chan fhaod ach litrichean Beurla (beagan no litrichean mòra) agus àireamhan a bhith ann am faclan-faire. Chan eil beàrnan, fo-loidhnichean agus comharran puingeachaidh eile ceadaichte.
Faodar sgrùdadh a chuir air dòigh leis an abairt àbhaisteach sìmplidh a leanas:
Gu dearbh, le leithid de phàtran feumaidh sinn sin eadar an toiseach (^) agus crìoch ($) anns an teacsa againn cha robh ann ach caractaran bhon t-seata a chaidh a thoirt seachad eadar camagan ceàrnach. Ma dh'fheumas tu cuideachd sùil a thoirt air fad am facal-faire (mar eisimpleir, co-dhiù 6 caractaran), an uairsin an tomhas-tomhais + faodar an eadar-ama “sia no barrachd” a chuir na àite san fhoirm {6,}:
Cathair bho sheòladh
Canaidh sinn gum feum sinn am baile-mòr a tharraing bhon bhàr seòlaidh. Cuidichidh am prògram àbhaisteach le bhith a’ toirt a-mach an teacsa bho “g.” chun an ath choma:
Bheir sinn sùil nas mionaidiche air a 'phàtran seo.
Ma tha thu air an teacsa gu h-àrd a leughadh, bha thu mar-thà a 'tuigsinn gu bheil brìgh sònraichte aig cuid de charactaran ann an abairtean cunbhalach (amannan, reultan, comharran dollar, msaa). Ma dh’ fheumas tu coimhead airson na caractaran sin iad fhèin, bidh cùl-taic romhpa (ris an canar uaireannan sgiath). Mar sin, nuair a thathar a’ lorg a’ chriomag “g.” feumaidh sinn sgrìobhadh ann an abairt àbhaisteach Mr. ma tha sinn a’ coimhead airson buannachd, ma-thà + msaa
Tha an ath dhà charactar anns an teamplaid againn, an dot agus an rionnag tomhais, a’ seasamh airson àireamh sam bith de charactaran, ie ainm baile sam bith.
Tha cromag aig deireadh an teamplaid, oir tha sinn a’ coimhead airson teacsa bho “g.” ri cromag. Ach faodaidh grunn chromagan a bhith san teacsa, ceart? Chan ann a-mhàin às deidh a’ bhaile, ach cuideachd às deidh an t-sràid, taighean, msaa. Co dhiubh a stadas ar n-iarrtas? Is ann airson sin a tha an comharra ceist. Às aonais, bhiodh an abairt àbhaisteach againn a’ tarraing a-mach an t-sreath as fhaide a ghabhas dèanamh:
A thaobh abairtean cunbhalach, tha pàtran mar sin “greedy”. Gus an suidheachadh a cheartachadh, tha feum air comharra ceist – bidh e a’ dèanamh an tomhas-tomhais às deidh sin tha e “stingy” - agus cha toir a’ cheist againn an teacsa ach suas chun chiad choma às deidh “g.”:
Ainm faidhle bhon t-slighe slàn
Is e suidheachadh gu math cumanta eile ainm an fhaidhle a thoirt a-mach às an t-slighe slàn. Cuidichidh abairt sìmplidh cunbhalach den fhoirm an seo:
Is e an cleas an seo gu bheil an sgrùdadh, gu dearbh, a’ tachairt an taobh eile - bho dheireadh gu toiseach, oir aig deireadh ar teamplaid tha $, agus tha sinn a 'coimhead airson a h-uile càil roimhe suas chun a' chiad cùl-taic bhon taobh dheas. Tha an cùl-taic air a theicheadh, mar an dot san eisimpleir roimhe.
PS
“A dh’ ionnsaigh an deireadh ”tha mi airson soilleireachadh gu bheil a h-uile rud gu h-àrd mar phàirt bheag de na cothroman a tha abairtean cunbhalach a’ toirt seachad. Tha tòrr charactaran sònraichte agus riaghailtean airson an cleachdadh, agus chaidh leabhraichean slàn a sgrìobhadh air a 'chuspair seo (tha mi a' moladh an tè seo co-dhiù airson toiseach tòiseachaidh). Ann an dòigh, tha sgrìobhadh abairtean cunbhalach cha mhòr na ealain. Cha mhòr an-còmhnaidh, faodar faireachdainn cunbhalach innleachdach a leasachadh no a leudachadh, ga dhèanamh nas eireachdail no comasach air obrachadh le raon nas fharsainge de dhàta cuir a-steach.
Gus mion-sgrùdadh agus sgrùdadh a dhèanamh air abairtean àbhaisteach dhaoine eile no do chuid fhèin a dheasbad, tha grunn sheirbheisean air-loidhne goireasach ann: Regex101, RegExr agus tuilleadh
Gu mì-fhortanach, chan eil a h-uile feart de abairtean cunbhalach clasaigeach a ’faighinn taic ann an VBA (mar eisimpleir, sgrùdadh cùil no clasaichean POSIX) agus faodaidh iad obrachadh le Cyrillic, ach tha mi a’ smaoineachadh gu bheil na tha ann gu leòr airson a ’chiad uair airson do thoileachadh.
Mura h-eil thu ùr don chuspair, agus gu bheil rudeigin agad ri roinn, fàg abairtean cunbhalach feumail nuair a bhios tu ag obair ann an Excel anns na beachdan gu h-ìosal. Tha aon inntinn math, ach tha dà bhròg nan paidhir!
- Cuir an gnìomh SUBSTITUTE an àite teacsa agus ga ghlanadh
- Lorg agus soilleireachadh charactaran Laideann ann an teacsa
- Lorg an teacsa coltach ris as fhaisge ort (Ivanov = Ivonov = Ivanof, msaa.)