Excel үшін !SEMTools
KZRU
Menu
Features of !SEMTools

Көбісі тұрақты өрнектердің не екенін естіді, бірақ олардың Microsoft Excel бағдарламасының «қақпағы астында» қолдау көрсетілетінін бәрі біле бермейді. Тұрақты өрнектер мәтінмен жұмысты бірнеше есе жылдамдатуға, ондағы ең күрделі үлгілерді табуға және ең күрделі зерттеу мәселелерін шешуге мүмкіндік береді. Жалғыз мәселе – оларды Excel бағдарламасында пайдалану үшін VBA білу керек.

Неліктен Майкрософт оларды парақ мүмкіндіктері ретінде қоспады және олар бір кездері болады ма, түсініксіз және белгісіз.

Бірақ !SEMTools қондырмасымен бұл білім қажет емес. Бірақ тұрақты өрнектердің синтаксисін минималды түсіну стандартты функцияларды қолдану арқылы шешу мүмкін емес дерлік есептерді шешуді жеңілдетеді немесе бұл үлкен ұзындықтағы формулаларды қажет етеді.

Excel бағдарламасындағы тұрақты өрнек функциялары

Тұрақты өрнектерді қолдау үшін !SEMTools қондырмасы қосылған кезде Excel бағдарламасында үш функция жұмыс істейді: REGEXMATCH, REGEXEXTRACT және REGEXREPLACE.

Олардың синтаксисі мен жұмыс принципі Google Spreadsheets бағдарламасына ұқсас. Сондықтан Excel бағдарламасында жазылған формулалар Google электрондық кестелерімен толық айна үйлесімді болады.

=REGEXMATCH("текст";"RegEx-паттерн для поиска")

REGEXMATCH мәтіннің үлгіге сәйкес келетін-келмейтініне байланысты ШЫН немесе ЖАЛҒАН (Excel бағдарламасының ағылшынша нұсқасында ШЫН немесе ЖАЛҒАН) қайтарады.

=REGEXEXTRACT("текст";"RegEx-паттерн для поиска")

REGEXEXTRACT үлгіге сәйкес мәтіннің бірінші бөлігін шығарып алады. Google Spreadsheets-тен шамалы айырмашылығы, егер ізделетін мәтінде мұндай фрагмент болмаса, Spreadsheets қатені қайтарады, ал қондырмада бос жол қайтарылады.

=REGEXREPLACE("текст";"RegEx-паттерн для поиска";"текст, которым заменяем найденное")

Тұрақты тіркестермен шығарылатын есептердің мысалдары

Мен сізге тұрақты тіркестердің барлық мүмкіндіктерін көрсету үшін мысалдармен сараң емеспін, өйткені олар өте үлкен. Бұл мақала өз күштерін белсендірек пайдалану үшін нұсқаулық және ынталандыру болады деп үміттенемін. Қарапайымнан күрделіге.

Тұрақты Excel пайдаланушыларына тұрақты өрнектерді барынша пайдалану мүмкіндігін беру үшін !SEMTools қондырмасына бірнеше жылдам процедуралар қосылды. Төмендегі барлық мысалдар оларды пайдалану арқылы көрсетіледі.

RegEx көмегімен ұяшықтардан деректерді шығару

Ұяшықтан бірінші санға дейінгі/соңғы мазмұнды шығарып алыңыз

.+\d
\d.+

Осындай қарапайым екі мысал. “+” қызмет кванфикатордың таңбасы болып табылды. Ол «әшкөз» моды қамтамасыз етті, онда өрнекті қанаттандыратын барлық таңбалар жол оны қанағаттандырмайынға сайкес келмейінше неме жолдың соны/басы келгенше алынады. Нүкте кез келген таңбаны білді. Осылайша, жолдың сонына дейін цифрдың алдында тұрған кез келген таңбалалар алынады.

«д» «цифтарды», айтпесе сандарды білдірген. Жоғарыдағы мысалдарда “\д”-ден кейін сандық көрсеткіш болмағандықтан, бір цифр. Егер бұл көрсеткішті нәтижелерден алып тастау қажет болса, мұны кейінірек жасауға болады. !SEMTTools

Сандар басқа турақты мысалпен де көрсетуге болады:

[0-9]

Ұяшықтардан сандарды «тарту»

Жолдан сандарды қалай шығаруға болады? Мұндай операцияға арналған тұрақты өрнек өте қарапайым болар еді:

\d

Шығару режиміне байланысты нәтиже ұяшықтағы бірінші немесе барлық сандар болады.

Егер оларды үздіксіз дәйектілік ретінде емес, таңбалар бір-бірінен кейінгі фрагменттерді сақтай отырып, бөлгіш арқылы шығару қажет болса, өрнек «ашкөз» кванфикатормен сәл өзгеше болады. Ал өндіру кезінде сепараторды пайдалану қажет болады.

\d+
[0-9]+

Бұл кез келген басқа таңбаларға қатысты, төмендегі сандары бар мысал:

izvlech-chisla-regex

N цифры бар ұяшықтан сандарды шығарып алу

Жоғарыдағы мысалда көріп отырғаныңыздай, жылдарды білдіретін сандардан басқа, басқа сандар шығарылды, мысалы, «1». Тек төрт саннан тұратын тізбектерді шығару үшін өрнекті өзгерту керек. Бірнеше нұсқа бар:

\d\d\d\d
[0-9][0-9][0-9][0-9]
\d{4}
[0-9]{4}

Соңғы екі опция кванторға арналған бұйра жақшаларды қамтиды. Ол үлгіні қанағаттандыратын таңба немесе жол фрагментінің қайталануларының ең аз санын көрсетеді. Квантордың алдындағы үлгі. Бұл жағдайда сандар болып табылатын кез келген төрт таңба қатарға тұруы керек.

izvlech-goda-regex
Біз барлық 4 таңбалы тізбектерді (жыл) бөлгіш арқылы шығарамыз

Extract latin characters with regular expression

[a-zA-Z] өрнегі барлық латын таңбаларын білдіреді. Осы және алдыңғы регистрдегі сызықша a мен z арасындағы және А мен Z арасындағы барлық таңбалар жалпы Юникод таңбалар кестесінде алынғанын білдіреді. Шаршы жақшалар «НЕМЕСЕ» сөзінің синонимі. Шаршы жақшаның ішіндегі элементтердің немесе жиындардың әрқайсысы тексеріледі және салыстырылатын жолда шаршы жақшаның ішінде ешқандай элемент болмаса ғана өрнек ештеңе таппайды.

izvlech-vsyu-latinitsu
Тұрақты тіркестерді пайдаланып латын әріптерін шығару

Шарт бойынша жолдардың соңындағы/басындағы таңбаларды шығарып алыңыз

Стандартты ОҢ және СОЛ формулалары ұяшықтан сәйкесінше соңғы және бірінші N таңбаны шығаруға мүмкіндік береді, бірақ олардың мүмкіндіктері осымен аяқталады.

Тұрақты өрнектердің көмегімен сіз мыналарды шығара аласыз:

  • Леп белгісімен аяқталатын ұяшықтағы соңғы бас әріптен кейінгі және қосатын таңбалар. Сондықтан ұяшықтардан барлық лепті сөйлемдерді шығарып аламыз. Бұл үшін өрнек келесідей: [A-Z][a-za-z0-9 ]+!$.
  • Егер ұяшық олардан басталса, бірінші N белгілі бір жиыннан таңбаларды таңдады.
  • Сол сияқты: ұяшық олармен аяқталса, соңғы N анықталған таңба.

Ұяшықтарды қалыпты өрнекке қарсы тексеріңіз

Деректерді шығарып алудың қажеті болмаса, бірақ оны кейінірек сүзгілеу үшін үлгіге сәйкес келетінін тексергіңіз келсе, REGEXMATCH формуласына баламалы процедураны пайдалану ыңғайлырақ.

Ұяшықтағы N цифрының санын табыңыз

N қажетті немесе жеткілікті шарт екеніне байланысты әртүрлі тұрақты өрнектер қажет. Басқаша айтқанда, N+1, N+2 және т.б. бастап тізбектерді санау керек пе. сандар орынды ма, жоқ па. Егер иә болса, өрнек жоғарыда айтылғандай болады:

\d\d\d\d
[0-9][0-9][0-9][0-9]
\d{4}
[0-9]{4}

Егер бізді N цифрларының тізбегі қатты қызықтырса, тапсырма екі итерацияда орындалуы керек:

Бірінші итерацияда сандарды сызық шекараларымен немесе одан кейінгі/бұрынғы цифрлық емес таңбалармен бірге шығарып алыңыз (бұл басқа цифрлардың жоқтығын тексерудің бір түрі болады).
Екіншіден, сандардың өзі.

Бірінші итерацияның өрнектері сәйкесінше:

(^|\D)\d\d\d\d($|\D)
(^|\D)[0-9][0-9][0-9][0-9]($|\D)
(^|\D)\d{4}($|\D)
(^|\D)[0-9]{4}($|\D)

Синтаксистің айырмашылығына мұқият қарасаңыз, ондағы таңбалардың нені білдіретінін түсінуге болады:

  • тік жолақ “|” «НЕМЕСЕ» дегенді білдіреді
  • «( )» жақшалары олардың ішіндегі аргументтерді санау және оларды өрнектің қалған бөлігінен «қоршау» үшін қажет,
  • карет «^» жолдың басын білдіреді,
  • доллар белгісі «$» – жолдың соңы,
  • \D – сандық емес таңбалар. Бас әріптің \d кері болатынын ескеріңіз. Бұл сәйкесінше латын және цифрларды және латын емес және цифрларды және \s және \S, бос орындардың әртүрлі түрлерін және бос орынсыз таңбаларды білдіретін \w және \W жұптарына да қатысты.

Сандардан басталатын ұяшықтарды табыңыз

Мұндай тексерудің өрнегі:

=REGEXMATCH(A1;"^\d.*")

Немесе формуланы енгізбей-ақ түпнұсқа диапазонның көшірмесінде тексеру процедурасын пайдалануға болады. Мысалдар қараңыз.

find-cells-starting-with-digit-1
Тұрақты өрнекті пайдаланып сандардан басталатын ұяшықтарды табыңыз

Ішкі жолдарды тұрақты өрнекпен ауыстыру

Біздің міндетіміз мәтіннен белгілі бір таңбаларды алып тастау болған кезде, мұндай ауыстырудың ең көп таралған жағдайы – бос орынды ауыстыру. Ең танымал:

  • мәтіннен сандарды алып тастау,
  • тыныс белгілерін жою,
  • әріптер мен сандардан басқа барлық таңбалар.

Бірақ нақты ауыстыру қажет болатын кездер болады, мысалы, әріптерді құйрықтармен/умлауттармен/акценттермен және еуропалық алфавиттердің басқа таңбаларымен олардың ағылшын тіліндегі әріптестерімен ауыстыру қажет болғанда. Тапсырма түпнұсқа семантика негізінде осы елдердің веб-сайттарының URL мекенжайларын қалыптастыратын SEO-мамандар арасында танымал. URL мекенжайын жасау кезінде RegEx көмегімен диакритикалық таңбаларды латын таңбаларымен ауыстыру үшін үлгі кестесінің басы осылай көрінеді:

image-5
Диакритика және олардың ағылшын тіліндегі баламалары

Емле ұяшығы

Ұяшық таңбасын таңба бойынша бөлу үшін бөлгіш арқылы барлық таңбаларды шығарып алу жеткілікті. Шығару өрнегі кәдімгі нүкте болады – ол кез келген таңбаны білдіреді.

split-by-symbol-excel

Ұяшықтағы әріптер мен сандарды бөлу

Егер сіз осы тапсырманың мәлімдемесін қатаң ұстанатын болсаңыз, оны орындау өте қиын. Бірақ екінші жағынан, тұрақты өрнектерді пайдалана отырып, таңбалардың сандық тізбегін сандық еместерден бөлуге болады. Өрнек келесідей болады:

([^\d.]+|[\d.]+)

Бұл процесс іс жүзінде осылай көрінеді:

split-numbers-non-numbers-regex
Тұрақты өрнектің көмегімен мәтінді сандарға және цифрлық емес таңбаларға (әріптер мен тыныс белгілері) бөлу

Бірінші сөзден кейін мәтінді енгізіңіз

Тұрақты өрнекпен ауыстырған кезде, !SEMTools барлығын емес, үлгіге сәйкес келетін бірінші табылған фрагментті ғана ауыстыру мүмкіндігіне ие. Бұл бірінші сөзден кейін таңбаларды енгізу мәселесін шешеді. Біз жай ғана бірінші бос орынды сәйкес процедураны пайдаланып қажетті таңбалармен ауыстырамыз (бұл тапсырманы SUBSTITUTE функциясы арқылы да шешуге болады, бірақ сіз тұрақты өрнекті ауыстыру функциясын да пайдалана аласыз). Қалыпты ауыстыру процедурасынан айырмашылығы, мұнда тек бірінші орын ауыстыруға болады. Бұл жағдайда бірінші бос орын. Көріп отырғаныңыздай, кеңістік әдеттегіден еш айырмашылығы жоқ:

replace-first-space
Бірінші бос орынды тұрақты өрнекті ауыстырумен ауыстырыңыз

Әр сөзден кейін немесе алдына таңба енгізіңіз

Қондырма бұл мәселені «Сөздерді өзгерту» мәзіріндегі дайын процедурамен екі рет басу арқылы шешеді, бірақ сіз қарапайым ауыстыру өрнегін де пайдалана аласыз:

($| )
(^| )

Өрнектер бірінші жағдайда бос орындар немесе жолдың соңы, ал екіншісінде бос орындар немесе жолдардың басы ауыстырылатынын білдіреді. Тік жолақ бірдей “НЕМЕСЕ”.

Және біз, сәйкесінше, сол немесе оң жағындағы таңбалы бос орынмен ауыстырамыз. Процедура ұяшыққа дейін немесе одан кейін қосымша бос орынды қосады, сондықтан одан құтылған жөн – қосымша бос орындарды алып тастаңыз немесе ұяшықтың басындағы/соңындағы таңбаларды жойыңыз.

symbol-after-every-word-1
Тұрақты тіркесті пайдаланып әр сөзден кейін таңбаны енгізу

!SEMTools ішінде нақты сөздерді іздеуге арналған тұрақты өрнектер

Тұрақты тіркес арқылы сөздерді табыңыз

Тұрақты сөз тіркесі арқылы сөздерді шығарып алу

Белгілі бір сөздерді шығаруға келгенде, тұрақты тіркестер керемет күрделі болады. Сондықтан, !SEMTools қондырмасы бөлек нысандар ретінде сөз деңгейінде RegEx үлгілерін қолдану тапсырмасын жеңілдетеді.

Құрамында латын және сандар бар сөздерді сөздер жиынынан алу тұрақты тіркесті пайдалану сияқты көрінеді:

([a-z]\d|\d[a-z])

Өрнектің әріптен кейінгі цифр немесе цифрдан кейінгі әріп тікелей жалғасуы керек екенін, арасында ешқандай таңбалар болмайтынын ескеріңіз. Егер басқа нәрселермен қатар, “asdf-13”, “1234-d” сияқты сөздерді шығару қажет болса, сіз келесілер арасындағы таңбалардың мүмкіндігін көрсетуіңіз керек:

([a-z].*\d|\d.*[a-z]).
extract-words-by-regex-1

Тұрақты сөз тіркесі арқылы сөздерді жою

Тұрақты өрнекке сәйкес келмейтін ұяшықтарды тазалау

Қолыңызда деректер массиві болса, онда қателер болуы мүмкін, олармен айналысуға уақыт жоқ және сонымен бірге тек жүз пайыз қолайлы деректерді шығарып алу қажет болса, маңызды емес деректерді тазалау үшін тұрақты өрнектерді пайдалануға болады. бір.

Мысалдар:

  • белгілі бір сөздер саны бар ұяшықтарды қалдырыңыз,
  • белгілі бір таңбалар саны бар ұяшықтарды қалдырыңыз,
  • тек сандардан тұратын ұяшықтарды қалдырыңыз,
  • тек әріптерден тұратын ұяшықтарды қалдырыңыз,
  • .com және .ru домен аймағында электрондық пошта мекенжайы бар ұяшықтарды қалдырыңыз.

«Ұяшықтарды тұрақты өрнек арқылы шығару» қолдану мысалдары.