متن را با عبارات منظم (RegExp) در اکسل تجزیه کنید

یکی از وقت گیرترین و خسته کننده ترین کارها هنگام کار با متن در اکسل است تجزیه - تجزیه حروف عددی "فرنی" به اجزا و استخراج قطعات مورد نیاز از آن. مثلا:

• استخراج کد پستی از آدرس (خوب است اگر کد پستی همیشه در ابتدا باشد، اما اگر نباشد چه؟)
• یافتن شماره و تاریخ فاکتور از شرح پرداخت در صورت حساب بانکی
• استخراج TIN از توصیفات متنوع شرکت های موجود در لیست طرف مقابل
• شماره خودرو یا شماره مقاله را در توضیحات و غیره جستجو کنید.
  

معمولاً در چنین مواردی، پس از نیم ساعت انتخاب دلهره آور متن به صورت دستی، افکاری به ذهن خطور می کند که به نحوی این فرآیند را خودکار کند (مخصوصاً اگر داده های زیادی وجود داشته باشد). چندین راه حل و با درجات مختلف پیچیدگی و کارایی وجود دارد:

• استفاده کنید توابع متنی اکسل داخلی برای جستجو-برش-چسب کردن متن: LEVSIMV (ترک کرد), RIGHT (درست), PSTR (اواسط), STsEPIT (الحاق) و آنالوگ های آن, آمیختن (JOINTEXT), دقیق (دقیق) و غیره اگر منطق واضحی در متن وجود داشته باشد (مثلاً نمایه همیشه در ابتدای آدرس باشد) این روش خوب است. در غیر این صورت، فرمول‌ها بسیار پیچیده‌تر می‌شوند و گاهی اوقات حتی به فرمول‌های آرایه‌ای هم می‌رسد که در جدول‌های بزرگ به شدت کند می‌شود.
• با استفاده از مانند عملگر تشابه متن از ویژوال بیسیک که در یک تابع ماکرو سفارشی پیچیده شده است. این به شما امکان می دهد با استفاده از کاراکترهای عام (*، #،؟، و غیره) جستجوی انعطاف پذیرتری را پیاده سازی کنید.

علاوه بر موارد فوق، رویکرد دیگری وجود دارد که در محافل باریک برنامه نویسان حرفه ای، توسعه دهندگان وب و سایر فناوران بسیار شناخته شده است – این عبارات با قاعده (عبارات منظم = RegExp = "regexps" = "عادی"). به زبان ساده، RegExp زبانی است که در آن از کاراکترها و قوانین ویژه برای جستجوی زیررشته‌های ضروری در متن، استخراج یا جایگزینی آنها با متن دیگر استفاده می‌شود.. عبارات منظم ابزاری بسیار قدرتمند و زیبا هستند که از همه روش های دیگر کار با متن پیشی می گیرند. بسیاری از زبان های برنامه نویسی (C#، PHP، Perl، JavaScript…) و ویرایشگرهای متن (Word، Notepad++…) از عبارات منظم پشتیبانی می کنند.

مایکروسافت اکسل متأسفانه پشتیبانی از RegExp را ندارد، اما این مشکل را می توان به راحتی با VBA برطرف کرد. ویرایشگر ویژوال بیسیک را از تب باز کنید توسعه دهنده (توسعه دهنده) یا میانبر صفحه کلید دگرساز+F11. سپس ماژول جدید را از طریق منو وارد کنید درج - ماژول و متن تابع ماکرو زیر را در آنجا کپی کنید:

تابع عمومی RegExpExtract (متن به عنوان رشته، الگو به عنوان رشته، مورد اختیاری به عنوان عدد صحیح = 1) به عنوان رشته در خطا برو به ErrHandl تنظیم regex = CreateObject("VBScript.RegExp") regex.Pattern = الگوی regex.Global =Trueest اگر (متن) سپس مطابقت ها را تنظیم کنید = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) خروج از تابع پایان اگر ErrHandl: RegExpExtract = CVERr(xlErrValue) تابع پایان  

اکنون می‌توانیم ویرایشگر ویژوال بیسیک را ببندیم و به اکسل برگردیم تا ویژگی جدید خود را امتحان کنیم. نحو آن به صورت زیر است:

=RegExpExtract(Txt؛ الگو؛ آیتم)

جایی که

• کلیپ برد چند منظوره – یک سلول با متنی که در حال بررسی آن هستیم و می خواهیم زیررشته مورد نیاز خود را از آن استخراج کنیم
• الگو - ماسک (الگو) برای جستجوی زیر رشته
• مورد - شماره دنباله زیررشته ای که باید استخراج شود، اگر چندین مورد از آنها وجود داشته باشد (اگر مشخص نشده باشد، اولین رخداد نمایش داده می شود)

البته جالب‌ترین چیز در اینجا Pattern است - یک رشته الگو از کاراکترهای خاص "به زبان" RegExp، که مشخص می‌کند دقیقاً چه چیزی و کجا می‌خواهیم پیدا کنیم. در اینجا ابتدایی ترین موارد برای شروع شما آورده شده است:

اگر به دنبال تعداد مشخصی کاراکتر هستیم، مثلاً یک کد پستی شش رقمی یا همه کدهای سه حرفی محصول، به کمک می آییم. سنجها or سنجها عبارات خاصی هستند که تعداد کاراکترهای مورد جستجو را مشخص می کنند. کمیت‌کننده‌ها برای کاراکتر قبل از آن اعمال می‌شوند:

حالا بیایید به جالب ترین بخش برویم - تجزیه و تحلیل کاربرد تابع ایجاد شده و آنچه در مورد الگوهای نمونه های عملی از زندگی آموختیم.

استخراج اعداد از متن

برای شروع، بیایید یک مورد ساده را تجزیه و تحلیل کنیم - شما باید اولین عدد را از فرنی الفبایی استخراج کنید، به عنوان مثال، قدرت منابع تغذیه اضطراری را از لیست قیمت:

منطق پشت عبارت منظم ساده است: d به معنای هر رقم و کمیت است + می گوید که تعداد آنها باید یک یا چند باشد. منهای دو برابر جلوی تابع برای تبدیل نویسه‌های استخراج‌شده به یک عدد کامل از عدد به‌عنوان متن، «در حال پرواز» مورد نیاز است.

کد پستی

در نگاه اول، همه چیز در اینجا ساده است - ما دقیقاً به دنبال شش رقم در یک ردیف هستیم. ما از یک شخصیت خاص استفاده می کنیم d برای رقم و کمیت 6،XNUMX،XNUMX {} برای تعداد کاراکترها:

با این حال، موقعیتی ممکن است زمانی که در سمت چپ شاخص در خط، مجموعه بزرگ دیگری از اعداد در یک ردیف (شماره تلفن، TIN، حساب بانکی و غیره) وجود داشته باشد، سپس فصل عادی ما 6 عدد اول را بیرون خواهد کشید. ارقام از آن، یعنی به درستی کار نخواهد کرد:

برای جلوگیری از این اتفاق، باید یک اصلاح کننده در لبه های عبارت معمولی خود اضافه کنیم b نشان دهنده پایان یک کلمه این به اکسل روشن می کند که قطعه (شاخص) مورد نیاز ما باید یک کلمه جداگانه باشد و نه بخشی از قطعه دیگر (شماره تلفن):

تلفن

مشکل پیدا کردن شماره تلفن در متن این است که گزینه های بسیار زیادی برای نوشتن اعداد وجود دارد - با و بدون خط تیره، از طریق فاصله، با یا بدون کد منطقه در داخل پرانتز و غیره. بنابراین، به نظر من، راحت تر است ابتدا با استفاده از چندین تابع تو در تو، تمام این کاراکترها را از متن منبع پاک کنید جایگزین (جایگزین)به طوری که در یک کل واحد به هم می چسبد و سپس با یک منظم اولیه d{11} ۱۱ رقم را پشت سر هم بیرون بکشید:

ITN

اینجا کمی پیچیده‌تر است، زیرا TIN (در کشور ما) می‌تواند 10 رقمی (برای اشخاص حقوقی) یا 12 رقمی (برای اشخاص حقیقی) باشد. اگر به خصوص ایراد نگیرید، کاملاً ممکن است که از معمول راضی باشید d{10,12}، اما، به طور دقیق، تمام اعداد را از 10 تا 12 کاراکتر بیرون می کشد، یعنی و به اشتباه 11 رقم وارد کرده است. استفاده از دو الگو که توسط یک عملگر OR منطقی به هم متصل شده اند، صحیح تر است | (نوار عمودی):

لطفاً توجه داشته باشید که در پرس و جو ابتدا به دنبال اعداد 12 بیتی و تنها پس از آن برای اعداد 10 بیتی می گردیم. اگر عبارت معمولی خود را برعکس بنویسیم، برای همه، حتی TIN های بلند 12 بیتی، فقط 10 کاراکتر اول، بیرون می آید. یعنی پس از ایجاد شرط اول، تأیید بیشتر انجام نمی شود:

این تفاوت اساسی بین اپراتور است | از یک تابع منطقی استاندارد اکسل OR (یا)، جایی که مرتب کردن مجدد آرگومان ها نتیجه را تغییر نمی دهد.

SKU های محصول

در بسیاری از شرکت‌ها، شناسه‌های منحصربه‌فرد به کالاها و خدمات اختصاص داده می‌شوند - مقالات، کدهای SAP، SKU و غیره. اگر در نمادگذاری آنها منطق وجود داشته باشد، می‌توان آنها را به راحتی از هر متنی با استفاده از عبارات منظم بیرون کشید. به عنوان مثال، اگر بدانیم که مقالات ما همیشه از سه حرف بزرگ انگلیسی، یک خط فاصله و یک عدد سه رقمی بعدی تشکیل شده است، پس:

منطق پشت قالب ساده است. [AZ] - به معنای هر حروف بزرگ الفبای لاتین است. کمیت بعدی 3،XNUMX،XNUMX {} می گوید که برای ما مهم است که دقیقاً سه حرف از این دست وجود داشته باشد. بعد از خط فاصله منتظر سه رقم هستیم و در آخر اضافه می کنیم d{3}

مبالغ نقدی

به روشی مشابه پاراگراف قبل، می توانید قیمت ها (هزینه ها، مالیات بر ارزش افزوده ...) را نیز از توضیحات کالا خارج کنید. اگر مبالغ پولی، برای مثال، با خط فاصله نشان داده شود، آنگاه:

الگو d با کمیت + هر عددی را تا خط فاصله جستجو می کند و d{2} پس از آن به دنبال سکه (دو رقمی) خواهد بود.

اگر نیاز به استخراج نه قیمت‌ها، بلکه VAT دارید، می‌توانید از آرگومان اختیاری سوم تابع RegExpExtract ما استفاده کنید، که شماره ترتیبی عنصری را که باید استخراج شود را مشخص می‌کند. و البته، می توانید عملکرد را جایگزین کنید جایگزین (جایگزین) در نتایج، به جداکننده اعشاری استاندارد خط خطی بزنید و در ابتدا یک منهای دو برابر اضافه کنید تا اکسل VAT یافت شده را به عنوان یک عدد عادی تفسیر کند:

شماره پلاک خودرو

اگر وسایل نقلیه خاص، تریلرها و سایر موتورسیکلت ها را سوار نمی کنید، شماره استاندارد خودرو طبق اصل "حرف - سه عدد - دو حرف - کد منطقه" تجزیه می شود. علاوه بر این، کد منطقه می تواند 2 یا 3 رقمی باشد و فقط آنهایی که از نظر ظاهری شبیه الفبای لاتین هستند به عنوان حروف استفاده می شوند. بنابراین، عبارت منظم زیر به ما کمک می کند تا اعداد را از متن استخراج کنیم:

زمان

برای استخراج زمان در قالب HH:MM، عبارت منظم زیر مناسب است:

بعد از قطعه کولون [0-5] روزهمانطور که فهمیدن آن آسان است، هر عددی را در محدوده 00-59 تنظیم می کند. قبل از کولون داخل پرانتز، دو الگو کار می کنند که با یک OR (لوله) منطقی از هم جدا شده اند:

• [0-1] روز - هر عددی در محدوده 00-19
• 2 [0-3] - هر عددی در محدوده 20-23

برای نتیجه به دست آمده، می توانید تابع استاندارد Excel را نیز اعمال کنید TIME (تیم)برای تبدیل آن به یک فرمت زمانی که برای برنامه قابل درک باشد و برای محاسبات بعدی مناسب باشد.

بررسی رمز عبور

فرض کنید که باید لیست رمزهای عبور اختراع شده توسط کاربران را از نظر صحت بررسی کنیم. طبق قوانین ما، رمزهای عبور فقط می توانند شامل حروف انگلیسی (کوچک یا بزرگ) و اعداد باشند. فاصله، زیرخط و سایر علائم نگارشی مجاز نیست.

بررسی را می توان با استفاده از عبارت منظم ساده زیر سازماندهی کرد:

در واقع، با چنین الگویی نیاز داریم که بین آغاز (^) و پایان ($) در متن ما فقط کاراکترهایی از مجموعه داده شده در کروشه وجود داشت. اگر همچنین باید طول رمز عبور را بررسی کنید (به عنوان مثال، حداقل 6 کاراکتر)، سپس کمیت + می تواند با فاصله "شش یا بیشتر" در فرم جایگزین شود {6 ،}:

شهر از آدرس

فرض کنید باید شهر را از نوار آدرس بیرون بکشیم. برنامه معمولی با استخراج متن از "g" کمک خواهد کرد. به کاما بعدی:

بیایید نگاهی دقیق تر به این الگو بیندازیم.

اگر متن بالا را خوانده باشید، قبلاً متوجه شده اید که برخی از کاراکترها در عبارات منظم (نقطه، ستاره، علامت دلار و غیره) معنای خاصی دارند. اگر لازم است به دنبال خود این شخصیت‌ها بگردید، قبل از آنها یک بک اسلش (که گاهی اوقات نامیده می‌شود استحفاظ). بنابراین، هنگام جستجوی قطعه "g." باید به صورت منظم بنویسیم آقای. اگر ما به دنبال یک امتیاز مثبت هستیم، پس + و غیره.

دو کاراکتر بعدی در قالب ما، نقطه و ستاره کمیت، مخفف هر تعداد از هر کاراکتر، یعنی هر نام شهری هستند.

در انتهای الگو یک کاما وجود دارد، زیرا ما به دنبال متن "g" هستیم. به کاما اما ممکن است چندین کاما در متن وجود داشته باشد، درست است؟ نه تنها بعد از شهر، بلکه بعد از خیابان، خانه ها و ... درخواست ما روی کدام یک متوقف می شود؟ علامت سوال برای همین است. بدون آن، عبارت منظم ما طولانی ترین رشته ممکن را بیرون می کشد:

از نظر عبارات منظم، چنین الگویی "طمع" است. برای تصحیح وضعیت، یک علامت سوال لازم است - کمیت را می‌سازد که بعد از آن "خسیس" می‌ماند - و درخواست ما متن را فقط تا اولین کاما شمارنده بعد از "g" می‌برد:

نام فایل از مسیر کامل

یکی دیگر از موارد بسیار رایج استخراج نام فایل از مسیر کامل است. یک عبارت منظم ساده از فرم در اینجا کمک خواهد کرد:

ترفند در اینجا این است که جستجو، در واقع، در جهت مخالف رخ می دهد - از انتها به ابتدا، زیرا در انتهای الگوی ما $، و ما به دنبال همه چیز قبل از آن تا اولین اسلش از سمت راست هستیم. علامت بک اسلش مانند نقطه در مثال قبلی حذف شده است.

PS

«به سوی پایان» می‌خواهم توضیح دهم که همه موارد بالا بخش کوچکی از همه امکاناتی است که عبارات منظم ارائه می‌دهند. تعداد زیادی شخصیت و قوانین خاص برای استفاده از آنها وجود دارد و کتاب های کاملی در این زمینه نوشته شده است (برای شروع حداقل این یکی را توصیه می کنم). به نوعی نوشتن عبارات منظم تقریباً یک هنر است. تقریباً همیشه، یک عبارت منظم اختراع شده را می توان بهبود بخشید یا تکمیل کرد، و آن را زیباتر یا قادر به کار با طیف وسیع تری از داده های ورودی می کند.

برای تجزیه و تحلیل و تجزیه عبارات منظم دیگران یا اشکال زدایی عبارات خود، چندین سرویس آنلاین راحت وجود دارد: RegEx101, RegExr و بیشتر

متأسفانه، تمام ویژگی‌های عبارات منظم کلاسیک در VBA پشتیبانی نمی‌شوند (مثلاً جستجوی معکوس یا کلاس‌های POSIX) و می‌توانند با سیریلیک کار کنند، اما من فکر می‌کنم آنچه که وجود دارد برای اولین بار کافی است که شما را خوشحال کند.

اگر در این موضوع تازه کار نیستید و چیزی برای به اشتراک گذاشتن دارید، عبارات منظم مفید هنگام کار در اکسل را در نظرات زیر بگذارید. یک ذهن خوب است، اما دو چکمه یک جفت است!

• جایگزین کردن و پاک کردن متن با عملکرد SUBSTITUTE
• جستجو و برجسته کردن حروف لاتین در متن
• جستجوی نزدیکترین متن مشابه (ایوانف = ایونوف = ایوانوف و غیره)