Nivîsara bi vegotinên rêkûpêk (RegExp) di Excel de parsek bikin

Dema ku di Excel de bi nivîsê re dixebitin yek ji wan karên herî demdirêj û xemgîn e parsing - Parçekirina "porê" a alfanumerîkî di nav pêkhateyan de û derxistina perçeyên ku em jê hewce ne. Bo nimûne:

• derxistina koda zip ji navnîşanê (baş e ger koda zip her gav di destpêkê de be, lê heke ne wusa be çi?)
• dîtina hejmar û dîroka fatûreyê ji danasîna dravdanê di beyannameya bankê de
• derxistina TIN-ê ji danasînên cûrbecûr yên pargîdaniyên di navnîşa hevalbendan de
• di danasînê de li jimareyek gerîdeyê an jimara gotarê bigerin, hwd.
  

Bi gelemperî di rewşên weha de, piştî nîv saetê bijartina xedar di nivîsê de bi destan, raman dest pê dike ku bi rengekî otomatîkî vê pêvajoyê bike (nemaze heke gelek dane hebin). Gelek çareserî û bi dereceyên cihêreng ên tevlihevî-karîgeriyê hene:

• Bikaranîn fonksiyonên nivîsê yên Excel-ê çêkirî ji bo lêgerîna nivîsê-birrîn-zeliqandî: LEVSIMV (ÇEP), RAST (RAST), PSTR (navîn), STsEPIT (PÊKIRIN) û analogên wê, HEVGIRÊDAN (JOINTEXT), TAM (TAM) hwd.. Eger di nivîsê de mantiqeke zelal hebe (mînak, îndeks her tim li destpêka navnîşanê ye) ev rêbaz baş e. Wekî din, formulas pir tevlihevtir dibin û, carinan, ew digihîje formulên rêzê, ku li ser maseyên mezin pir hêdî hêdî dibe.
• bikaranîna mîna operatorê wekheviya nivîsê ji Visual Basic di fonksiyonek makro ya xwerû de pêça. Ev dihêle ku hûn bi karekterên hovane (*, #,?, hwd.) lêgerînek maqûltir pêk bînin. Mixabin, ev amûr nikare binerxeya xwestinê ji nivîsê derxîne - tenê kontrol bike ka ew tê de heye yan na.

Ji bilî ya jorîn, nêzîkatiyek din heye ku di derdorên teng ên bernamenûsên profesyonel, pêşdebirên malperê û teknolojiyên din de pir baş tê zanîn - ev e. îfadeyên birêkûpêk (Rêveberên Birêkûpêk = RegExp = "regexps" = "rêkûpêk"). Bi tenê, RegExp zimanek e ku karakter û qaîdeyên taybetî têne bikar anîn da ku li jêr rêzikên pêwîst di nivîsê de bigerin, wan derxînin an jî bi nivîsek din veguherînin.. Gotinên birêkûpêk amûrek pir bi hêz û xweşik e ku ji hemî awayên din ên xebata bi nivîsê re bi rêzek mezinahiyê derbas dike. Gelek zimanên bernamekirinê (C#, PHP, Perl, JavaScript…) û edîtorên nivîsê (Word, Notepad++…) piştgirî didin îfadeyên birêkûpêk.

Microsoft Excel mixabin piştgiriyek RegExp ji qutikê nîne, lê ev bi VBA re bi hêsanî dikare were rast kirin. Edîtorê Visual Basic ji tabê vekin pêşvebirinê (pêşdebir) an jî kurteya klavyeyê alt+F11. Dûv re modula nû di nav menuyê de têxe Têxe - Modul û nivîsa fonksiyona makro ya jêrîn li wir kopî bikin:

Fonksiyona Giştî RegExpExtract(Nivîs Wek Rêz, Şêweya Wek Rêz, Tişta Bijarte Wek Hêjmar = 1) Wek String Li Ser Xeletiyê GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Nimûneya regex.Global = Heke rast (Nivîs) Dûv re lihevhatinan saz bike = regex.Execute(Text) RegExpExtract = matches.Item(Têkil - 1) Fonksiyon Derkeve Dawî Heke ErrHandl: RegExpExtract = CVERr(xlErrValue) Fonksiyon Dawî  

Naha em dikarin Edîtorê Visual Basic bigirin û vegerin Excel da ku taybetmendiya xweya nû biceribînin. Hevoksaziya wê ev e:

=RegExpExtract(Txt; Pattern; Babet)

ko

• txt - şaneyek bi nivîsa ku em kontrol dikin û em dixwazin jêdera ku em hewce ne jê derxin
• mînak - mask (pattern) ji bo lêgerîna binerdê
• Şanî - jimareya rêza binerxeya ku tê derxistin, heke çend ji wan hebin (eger ne diyar be, wê hingê bûyera yekem tê xuyang kirin)

Tişta herî balkêş li vir, bê guman, Pattern e - şablonek tîpên taybetî yên "bi zimanî" ya RegExp, ku diyar dike ka bi rastî û li ku derê em dixwazin bibînin. Li vir yên herî bingehîn hene ku hûn dest pê bikin:

Ger em li hejmarek karakteran digerin, mînakî, kodek posta şeş-hejmar an hemî kodên hilberên sê tîpî, wê hingê em werin rizgariyê jimarvan or jimarvan biwêjên taybet in ku hejmara tîpên li ser lêgerînê diyar dikin. Pîvanker li ser karaktera ku berî wê tê têne sepandin:

Naha em werin ser beşa herî balkêş - analîzek li ser sepana fonksiyona hatî afirandin û tiştê ku em di derbarê nimûneyên li ser mînakên pratîkî yên jiyanê de fêr bûne.

Derxistina hejmaran ji nivîsê

Ji bo destpêkê, bila em dozek hêsan analîz bikin - hûn hewce ne ku hejmara yekem ji porê alfanumerîkî derxînin, mînakî, hêza dabînkirina hêzê ya bênavber ji navnîşa bihayê:

Mantiqa li pişt îfadeya birêkûpêk hêsan e: d tê wateya her reqem, û pîvan + dibêje ku divê hejmara wan yek an jî zêdetir be. Kêmasiya ducarî ya li ber fonksiyonê hewce ye ku "li ser firînê" tîpên ku hatine derxistin ji jimar-wek-tekst veguhezîne jimareyek tam.

Postcode

Di nihêrîna pêşîn de, her tişt li vir hêsan e - em li pey hev li şeş jimaran digerin. Em karekterek taybetî bikar tînin d ji bo reqem û jimarvan 6 {} ji bo hejmara tîpan:

Lêbelê, rewşek mimkun e dema ku, li milê çepê nîşana di rêzê de, hejmareke din a mezin a hejmar li dû hev hebe (hejmara têlefonê, TIN, hesabê bankê, hwd.) Wê hingê demsala meya birêkûpêk dê 6 yekem derxe. hejmarên wê, ango dê rast nexebitin:

Ji bo pêşîgirtina vê yekê, pêdivî ye ku em guhêrbarek li dora keviya vegotina xweya birêkûpêk zêde bikin b nîşana dawiya peyvê. Ev ê ji Excel re eşkere bike ku perçeya (indeksa) ku em hewce ne divê peyvek cihêreng be, û ne beşek perçeyek din (hejmara têlefonê):

Phone

Pirsgirêka dîtina jimareyek têlefonê di nivîsê de ev e ku ji bo nivîsandina jimareyan gelek vebijark hene - bi û bê daçek, bi valahiyan, bi an bê koda herêmê di nav kevanan de û hwd. Ji ber vê yekê, bi dîtina min, ew hêsantir e pêşî van hemî karakteran ji nivîsa çavkaniyê bi karanîna çend fonksiyonên hêlînkirî paqij bikin DIBERDASITIN (DIBERDASITIN)da ku ew bi hev re bi tevahî yekane, û paşê bi rêkûpêkek primitive d{11} 11 reqeman li pey hev derxin:

ITN

Li vir ew hinekî tevlihevtir e, ji ber ku TIN (li Welatê Me) dikare 10-hejmar (ji bo kesên qanûnî) an 12-hejmar (ji bo kesan) be. Heke hûn bi taybetî xeletiyê nabînin, wê hingê mimkun e ku hûn bi rêkûpêk razî bibin d{10,12}, lê, bi hişkî, ew ê hemî jimareyan ji 10 heta 12 tîpan derxe, ango û bi xeletî 11 reqeman têxe. Dê rasttir be ku meriv du qalibên ku ji hêla operatorek OR-ya mantiqî ve girêdayî ne bikar bînin | (barê vertîkal):

Ji kerema xwe bala xwe bidin ku di pirsnameyê de em pêşî li hejmarên 12-bit, û tenê dûv re li hejmarên 10-bit digerin. Ger em vegotina xweya birêkûpêk berevajî binivîsin, wê hingê ew ê ji bo her kesî, tewra TIN-ên dirêj ên 12-bit, tenê 10 tîpên pêşîn derxe. Ango, piştî ku şerta yekem hate destpêkirin, verastkirina din êdî nayê kirin:

Ev ferqa bingehîn di navbera operator e | ji fonksiyonek mantiqa excel a standard OR (AN), li cihê ku ji nû ve rêzkirina argumanan encamê naguherîne.

SKUyên Hilberê

Di gelek pargîdaniyan de, nasnameyên yekta ji mal û karûbaran re têne destnîşan kirin - gotar, kodên SAP, SKU, hwd. Heke di navnîşana wan de mantiq hebe, wê hingê ew dikarin bi hêsanî bi karanîna biwêjên birêkûpêk ji her nivîsê werin derxistin. Mînakî, heke em zanibin ku gotarên me her gav ji sê tîpên îngilîzî yên mezin, defek û jimareyek sê-hejmar a paşîn pêk tê, wê hingê:

Mantiqa li pişt şablonê hêsan e. [AZ] - tê wateya her tîpên mezin ên alfabeya latînî. Pîvana paşîn 3 {} dibêje ku ji bo me girîng e ku tam sê tîpên weha hebin. Piştî dafikê, em li benda sê reqeman in, ji ber vê yekê em li dawiyê lê zêde dikin d{3}

mîqdarên Cash

Bi heman awayê paragrafa berê, hûn dikarin ji danasîna tiştan bihayan (lêçûn, VAT…) jî derxînin. Ger mîqdarên diravî, wek nimûne, bi defekê têne destnîşan kirin, wê hingê:

pattern d bi quantifier + digere ji bo her hejmarê heta deftera, û d{2} dê li dû qurişan (du reqeman) bigerin.

Heke hûn hewce ne ku ne bihayên, lê VAT-ê derxînin, wê hingê hûn dikarin argumana vebijarkî ya sêyemîn a fonksiyona meya RegExpExtract bikar bînin, ku jimara rêzdar a hêmana ku were derxistin diyar dike. Û, bê guman, hûn dikarin fonksiyonê veguherînin DIBERDASITIN (DIBERDASITIN) Di encaman de, veqetînera dehiyê ya standard bixin û di destpêkê de kêmasiyek ducar lê zêde bikin da ku Excel VAT-ya hatî dîtin wekî jimareyek normal şîrove bike:

Hejmarên plakaya otomobîlê

If you do not take special vehicles, trailers and other motorcycles, then the standard car number is parsed according to the principle “letter – three numbers – two letters – region code”. Moreover, the region code can be 2- or 3-digit, and only those that are similar in appearance to the Latin alphabet are used as letters. Thus, the following regular expression will help us to extract numbers from the text:

Dem

Ji bo derxistina demê di forma HH:MM de, îfadeya birêkûpêk a jêrîn guncan e:

Piştî perçeya kolonê [0-5]d, wekî ku hêsan tê fêm kirin, her jimarek di rêza 00-59 de destnîşan dike. Berî kolona di nav parantezê de, du qalib dixebitin, bi OR (boriyek) mentiqî veqetandî:

• [0-1]d - her hejmar di rêza 00-19 de
• 2[0-3] - her hejmar di rêza 20-23 de

Ji bo encama ku hatî bidestxistin, hûn dikarin fonksiyona standard Excel-ê jî bicîh bikin DEM (KOM)ji bo veguheztina wê nav formatek demê ya ku ji bernameyê re tê fêm kirin û ji bo hesabên din guncan e.

Kontrolkirina şîfreyê

Bifikirin ku pêdivî ye ku em navnîşa şîfreyên ku ji hêla bikarhêneran ve hatine vedîtin ji bo rastbûnê kontrol bikin. Li gorî qaîdeyên me, şîfre tenê dikarin tîpên îngilîzî (biçûk an mezin) û jimaran hebin. Cih, binî û nîşaneyên xalbendî yên din nayên destûr kirin.

Kontrolkirin dikare bi karanîna bêjeya birêkûpêk a hêsan a jêrîn were organîze kirin:

Bi rastî, bi şêwazek wusa em hewce dikin ku di navbera destpêkê de (^) û dawiya ($) di nivîsa me de tenê tîpên ji setê di nav kevaneyên çargoşe de hatine dayîn hebûn. Heke hûn hewce ne ku dirêjahiya şîfreyê jî kontrol bikin (mînak, bi kêmî ve 6 tîpan), wê hingê pîvanê + dikare bi navbera "şeş an bêtir" di formê de were guheztin {6,}:

Bajar ji navnîşanê

Em bibêjin ku divê em bajêr ji barika navnîşan derxin. Bernameya birêkûpêk dê bibe alîkar, ku nivîsê ji "g" derxe. ji bo kommaya din:

Werin em ji nêz ve li vê nimûneyê binêrin.

Ger we nivîsa li jor xwendibe, wê hingê we berê fêm kiriye ku hin tîpên di bêjeyên birêkûpêk de (demok, stêrk, nîşaneyên dolar, hwd.) xwedî wateyek taybetî ne. Ger hewce be ku hûn bixwe li van karakteran bigerin, wê hingê ew bi paşvekêşek (carinan tê gotin mertal kirin). Ji ber vê yekê, dema ku li perçeya "g" digerin. divê em bi awayekî rêkûpêk binivîsin Mr. eger em lê digerin ji bo plus, paşê + hwd.

Du tîpên paşîn ên di şablonê me de, xal û stêrka pîvanê, nîşana her hejmarê her tîpan, ango navê bajarekî ne.

Di dawiya şablonê de qertafek heye, ji ber ku em li nivîsa "g" digerin. to a comma. Lê dibe ku di nivîsê de çend koman hebin, rast? Ne tenê piştî bajêr, li pey kolan, xanî û hwd jî. Daxwaza me dê li ser kîjan ji wan raweste? Nîşana pirsê ji bo vê yekê ye. Bêyî wê, vegotina meya birêkûpêk dê xêza herî dirêj a gengaz derxe:

Di warê bilêvkirinên birêkûpêk de, nimûneyek weha "xemgîn" e. Ji bo rastkirina rewşê, nîşanek pirsê lazim e - ew pîvanê dike ku li dû wê ew "hişk" radiweste - û pirsa me nivîsê tenê digihîje ber hevbera yekem a piştî "g.":

Navê pelê ji riya tevahî

Rewşek din a pir gelemperî derxistina navê pelê ji riya tevahî ye. Daxuyanek birêkûpêk a hêsan a formê dê li vir alîkariyê bike:

Xetereya li vir ev e ku lêgerîn, bi rastî, berevajî rêyek berevajî pêk tê - ji dawiyê heya destpêkê, ji ber ku di dawiya şablonê me de ye. $, û em li her tiştî digerin berî wê heya paşîna yekem ji rastê. Pîvana paşîn, mîna xala di mînaka berê de, xilas dibe.

PS

"Ber bi dawiyê ve" Ez dixwazim zelal bikim ku hemî jorîn beşek piçûk e ji hemî îmkanên ku biwêjên birêkûpêk peyda dikin. Ji bo karanîna wan gelek karakter û qaîdeyên taybetî hene, û tevahî pirtûk li ser vê mijarê hatine nivîsandin (ez bi kêmanî vê yekê ji bo destpêkê pêşniyar dikim). Bi awayek, nivîsandina vegotinên birêkûpêk hema hema hunerek e. Hema hema her gav, vegotinek birêkûpêk a dahênerî dikare were baştir kirin an were zêdekirin, ku wê xweşiktir bike an jî bikaribe bi cûrbecûr daneyên têketinê re bixebite.

Ji bo analîzkirin û parkirina bêjeyên birêkûpêk ên kesên din an jî ya xwe jêbirin, çend karûbarên serhêl ên hêsan hene: RegEx101, RegExr û hîn

Mixabin, ne hemî taybetmendiyên bêjeyên birêkûpêk ên klasîk di VBA-yê de têne piştgirî kirin (mînakî, lêgerîna berevajî an dersên POSIX) û dikarin bi Cyrillic re bixebitin, lê ez difikirim ku tiştê ku li wir heye ji bo yekem car têra we dike ku we xweş bike.

Ger hûn di mijarê de ne nû ne, û tiştek we heye ku hûn parve bikin, dema ku di Excel de dixebitin di şîroveyên jêrîn de bêjeyên birêkûpêk bikêr bihêlin. Hişek baş e, lê du pêlav cotek in!

• Li şûna û paqijkirina nivîsê bi fonksiyona SUBSTITUTE
• Search and highlighting of Latin characters in text
• Li nivîsa herî nêzîk a wekhev bigerin (Ivanov = Ivonov = Ivanof, hwd.)