ტექსტის გაანალიზება რეგულარული გამონათქვამებით (RegExp) Excel-ში

Excel-ში ტექსტთან მუშაობისას ერთ-ერთი ყველაზე შრომატევადი და იმედგაცრუებული ამოცანაა გარჩევა – ალფანუმერული „ფაფის“ კომპონენტებად დაყოფა და მისგან საჭირო ფრაგმენტების ამოღება. Მაგალითად:

• საფოსტო კოდის ამოღება მისამართიდან (კარგია, თუ საფოსტო კოდი ყოველთვის დასაწყისშია, მაგრამ რა მოხდება, თუ არა?)
• საბანკო ამონაწერში გადახდის აღწერიდან ინვოისის ნომრისა და თარიღის მოძიება
• TIN-ის მოპოვება კონტრაგენტების სიაში შემავალი კომპანიების ჭრელი აღწერებიდან
• მოძებნეთ მანქანის ნომერი ან სტატიის ნომერი აღწერაში და ა.შ.
  

ჩვეულებრივ, ასეთ შემთხვევებში, ტექსტის ხელით მოსაწყენი არჩევის ნახევარი საათის შემდეგ, აზრები იბადება ამ პროცესის ავტომატიზაციისთვის (განსაკუთრებით, თუ ბევრი მონაცემია). არსებობს რამდენიმე გამოსავალი და სირთულის-ეფექტურობის სხვადასხვა ხარისხით:

• გამოყენება ჩაშენებული Excel ტექსტური ფუნქციები ტექსტის ძიება-დაჭრა-წებვა: ლევსიმვ (მარცხნივ), RIGHT (მარჯვნივ), PSTR (შუაში), STsEPIT (შეერთება) და მისი ანალოგები, COMBINE (JOINTEXT), EXACT (ზუსტი) და ა.შ. ეს მეთოდი კარგია, თუ ტექსტში არის მკაფიო ლოგიკა (მაგალითად, ინდექსი ყოველთვის არის მისამართის დასაწყისში). წინააღმდეგ შემთხვევაში, ფორმულები ბევრად უფრო რთული ხდება და ზოგჯერ საქმე მასივის ფორმულებამდეც კი ხდება, რაც დიდ მაგიდებზე მნიშვნელოვნად ანელებს.
• გამოყენება ტექსტის მსგავსების ოპერატორის მსგავსად Visual Basic-დან მორგებული მაკრო ფუნქციით. ეს საშუალებას გაძლევთ განახორციელოთ უფრო მოქნილი ძიება ველური სიმბოლოების გამოყენებით (*, #,? და ა.შ.) სამწუხაროდ, ამ ხელსაწყოს არ შეუძლია ტექსტიდან სასურველი ქვესტრიქონის ამოღება - მხოლოდ შეამოწმეთ არის თუ არა იგი მასში.

გარდა ზემოაღნიშნულისა, არსებობს კიდევ ერთი მიდგომა, რომელიც კარგად არის ცნობილი პროფესიონალი პროგრამისტების, ვებ დეველოპერების და სხვა ტექნიკოსების ვიწრო წრეებში – ეს არის რეგულარული გამონათქვამები (Regular Expressions = RegExp = "regexps" = "Regulars"). მარტივად რომ ვთქვათ, RegExp არის ენა, სადაც სპეციალური სიმბოლოები და წესები გამოიყენება ტექსტში საჭირო ქვესტრიქონების მოსაძებნად, მათ ამოსაღებად ან სხვა ტექსტით ჩანაცვლებისთვის.. რეგულარული გამონათქვამები არის ძალიან მძლავრი და ლამაზი ინსტრუმენტი, რომელიც აღემატება ტექსტთან მუშაობის ყველა სხვა ხერხს სიდიდის რიგითობით. მრავალი პროგრამირების ენა (C#, PHP, Perl, JavaScript…) და ტექსტის რედაქტორები (Word, Notepad++…) მხარს უჭერს რეგულარულ გამონათქვამებს.

Microsoft Excel-ს, სამწუხაროდ, არ აქვს RegExp მხარდაჭერა, მაგრამ ეს მარტივად შეიძლება დაფიქსირდეს VBA-ით. გახსენით Visual Basic რედაქტორი ჩანართიდან დეველოპერი (დეველოპერი) ან კლავიატურის მალსახმობი Alt+F11. შემდეგ ჩადეთ ახალი მოდული მენიუში ჩასმა – მოდული და დააკოპირეთ შემდეგი მაკრო ფუნქციის ტექსტი იქ:

საჯარო ფუნქცია RegExpExtract(ტექსტი როგორც სტრიქონი, ნიმუში როგორც სტრიქონი, არჩევითი ელემენტი როგორც მთელი რიცხვი = 1) როგორც სტრიქონი შეცდომის სახით გადადით ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global =Treest regex. (ტექსტი) შემდეგ დააყენეთ მატჩები = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) გასვლა ფუნქციის დასასრული თუ ErrHandl: RegExpExtract = CVERr(xlErrValue) დასრულების ფუნქცია  

ახლა შეგვიძლია დავხუროთ Visual Basic რედაქტორი და დავბრუნდეთ Excel-ში ჩვენი ახალი ფუნქციის გამოსაცდელად. მისი სინტაქსი შემდეგია:

=RegExpExtract(Txt; Pattern; Item)

სადაც

• txt – უჯრედი ტექსტით, რომელსაც ჩვენ ვამოწმებთ და საიდანაც გვინდა გამოვყოთ ჩვენთვის საჭირო ქვესტრიქონი
• ნიმუში – ნიღაბი (ნიმუში) ქვესტრიქონების საძიებლად
• Item – ამოსაღები ქვესტრიქონის მიმდევრობის ნომერი, თუ რამდენიმე მათგანია (თუ არ არის მითითებული, მაშინ გამოჩნდება პირველი შემთხვევა)

ყველაზე საინტერესო აქ, რა თქმა უნდა, არის Pattern – RegExp-ის სპეციალური სიმბოლოების შაბლონური სტრიქონი, რომელიც განსაზღვრავს კონკრეტულად და სად გვინდა ვიპოვოთ. აქ არის ყველაზე ძირითადი პირობა დასაწყებად:

თუ ჩვენ ვეძებთ სიმბოლოების გარკვეულ რაოდენობას, მაგალითად, ექვსნიშნა საფოსტო კოდს ან ყველა სამასოიანი პროდუქტის კოდს, მაშინ მოვდივართ სამაშველოში. მრიცხველები or მრიცხველები არის სპეციალური გამონათქვამები, რომლებიც განსაზღვრავენ საძიებელი სიმბოლოების რაოდენობას. რაოდენობები გამოიყენება სიმბოლოზე, რომელიც მის წინ მოდის:

ახლა გადავიდეთ ყველაზე საინტერესო ნაწილზე – შექმნილი ფუნქციის გამოყენების ანალიზზე და რა ვისწავლეთ ცხოვრებიდან პრაქტიკულ მაგალითებზე ნიმუშების შესახებ.

ნომრების ამოღება ტექსტიდან

დასაწყისისთვის, მოდით გავაანალიზოთ მარტივი შემთხვევა - თქვენ უნდა ამოიღოთ პირველი რიცხვი ალფანუმერული ფაფიდან, მაგალითად, უწყვეტი კვების წყაროების სიმძლავრე ფასების სიიდან:

რეგულარული გამოხატვის ლოგიკა მარტივია: d ნიშნავს ნებისმიერ ციფრს და კვანტიფიკატორს + ამბობს, რომ მათი რაოდენობა უნდა იყოს ერთი ან მეტი. ორმაგი მინუსი ფუნქციის წინ არის საჭირო იმისათვის, რომ ამოღებული სიმბოლოები გადაიყვანოთ სრულ რიცხვად რიცხვი-როგორც ტექსტიდან.

Postcode

ერთი შეხედვით, აქ ყველაფერი მარტივია - ჩვენ ვეძებთ ზუსტად ექვს ციფრს ზედიზედ. ჩვენ ვიყენებთ სპეციალურ სიმბოლოს d ციფრისთვის და რაოდენობებისთვის 6 {} სიმბოლოების რაოდენობისთვის:

თუმცა, შესაძლებელია სიტუაცია, როდესაც, სტრიქონში ინდექსის მარცხნივ, ზედიზედ არის ნომრების კიდევ ერთი დიდი ნაკრები (ტელეფონის ნომერი, TIN, საბანკო ანგარიში და ა.შ.) მაშინ ჩვენი რეგულარული სეზონი ამოიღებს პირველ 6-ს. ციფრები მისგან, ანუ არ იმუშავებს სწორად:

ამის თავიდან ასაცილებლად, ჩვენ უნდა დავამატოთ მოდიფიკატორი ჩვენი რეგულარული გამოხატვის კიდეებზე b ნიშნავს სიტყვის დასასრულს. ეს ცხადყოფს Excel-ს, რომ ჩვენთვის საჭირო ფრაგმენტი (ინდექსი) ცალკე სიტყვა უნდა იყოს და არა სხვა ფრაგმენტის ნაწილი (ტელეფონის ნომერი):

ტელეფონი

ტექსტში ტელეფონის ნომრის პოვნის პრობლემა ის არის, რომ ნომრების ჩაწერის უამრავი ვარიანტია – დეფისით და მის გარეშე, ინტერვალით, რეგიონის კოდით ან მის გარეშე ფრჩხილებში და ა.შ. ამიტომ, ჩემი აზრით, უფრო ადვილია ჯერ ამოიღეთ ყველა ეს სიმბოლო საწყისი ტექსტიდან რამდენიმე ჩადგმული ფუნქციის გამოყენებით შემცვლელი (შემცვლელი)ისე, რომ იგი ერთ მთლიან მთლიანობაში ეკვრის, შემდეგ კი პრიმიტიულ რეგულარულს დ{11} ამოიღეთ ზედიზედ 11 ციფრი:

ITN

აქ ცოტა უფრო რთულია, რადგან TIN (ჩვენს ქვეყანაში) შეიძლება იყოს 10-ნიშნა (იურიდიული პირებისთვის) ან 12-ნიშნა (ფიზიკური პირებისთვის). თუ განსაკუთრებით ხარვეზს ვერ პოულობთ, მაშინ სავსებით შესაძლებელია დაკმაყოფილდეთ რეგულარულით დ{10,12}, მაგრამ, მკაცრად რომ ვთქვათ, ის ამოიღებს ყველა რიცხვს 10-დან 12 სიმბოლომდე, ანუ შეცდომით შეყვანილი 11 ციფრი. უფრო სწორი იქნება ორი შაბლონის გამოყენება, რომლებიც დაკავშირებულია ლოგიკური OR ოპერატორით | (ვერტიკალური ზოლი):

გთხოვთ გაითვალისწინოთ, რომ მოთხოვნაში ჯერ ვეძებთ 12-ბიტიან და მხოლოდ ამის შემდეგ 10-ბიტიან რიცხვებს. თუ ჩვენ დავწერთ ჩვენს რეგულარულ გამონათქვამს პირიქით, მაშინ ის ყველასთვის ამოიღებს, თუნდაც გრძელ 12-ბიტიან TIN-ებს, მხოლოდ პირველ 10 სიმბოლოს. ანუ, პირველი პირობის გააქტიურების შემდეგ, შემდგომი გადამოწმება აღარ ხორციელდება:

ეს არის ფუნდამენტური განსხვავება ოპერატორს შორის | სტანდარტული Excel-ის ლოგიკური ფუნქციიდან OR (OR), სადაც არგუმენტების გადაწყობა შედეგს არ ცვლის.

პროდუქტის SKU

ბევრ კომპანიაში უნიკალური იდენტიფიკატორები ენიჭება საქონელსა და მომსახურებას - სტატიებს, SAP კოდების, SKU-ს და ა.შ. თუ მათ აღნიშვნაში ლოგიკაა, მაშინ მათი ადვილად ამოღება შესაძლებელია ნებისმიერი ტექსტიდან რეგულარული გამონათქვამების გამოყენებით. მაგალითად, თუ ვიცით, რომ ჩვენი სტატიები ყოველთვის შედგება სამი დიდი ინგლისური ასოსგან, დეფისისა და შემდეგი სამნიშნა რიცხვისგან, მაშინ:

შაბლონის უკან ლოგიკა მარტივია. [AZ] - ნიშნავს ლათინური ანბანის ნებისმიერ დიდ ასოს. შემდეგი რაოდენობრივი მაჩვენებელი 3 {} ამბობს, რომ ჩვენთვის მნიშვნელოვანია ზუსტად სამი ასეთი ასო იყოს. დეფისის შემდეგ ველოდებით სამ ციფრს, ამიტომ ბოლოს ვამატებთ დ{3}

ფულადი თანხები

წინა პუნქტის მსგავსად, თქვენ ასევე შეგიძლიათ ამოიღოთ ფასები (ხარჯები, დღგ…) საქონლის აღწერილობიდან. თუ ფულადი თანხები, მაგალითად, მითითებულია დეფისით, მაშინ:

Pattern d კვანტიფიკატორით + ეძებს ნებისმიერ რიცხვს დეფისამდე და დ{2} დაეძებს პენიებს (ორი ციფრი) შემდეგ.

თუ თქვენ გჭირდებათ არა ფასების, არამედ დღგ-ს ამოღება, მაშინ შეგიძლიათ გამოიყენოთ ჩვენი RegExpExtract ფუნქციის მესამე არჩევითი არგუმენტი, რომელიც განსაზღვრავს ამოსაღები ელემენტის რიგით ნომერს. და, რა თქმა უნდა, შეგიძლიათ შეცვალოთ ფუნქცია შემცვლელი (შემცვლელი) შედეგებში ჩაწერეთ სტანდარტული ათობითი გამყოფი და დაამატეთ ორმაგი მინუსი დასაწყისში, რათა Excel-მა აღმოაჩინოს დღგ-ს ნორმალურ რიცხვად:

მანქანის ნომრები

თუ არ მიიღებთ სპეციალურ სატრანსპორტო საშუალებებს, მისაბმელებს და სხვა მოტოციკლებს, მაშინ მანქანის სტანდარტული ნომერი გაანალიზებულია პრინციპით "ასო - სამი ციფრი - ორი ასო - რეგიონის კოდი". უფრო მეტიც, რეგიონის კოდი შეიძლება იყოს 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"-ის შემდეგ:

ფაილის სახელი სრული ბილიკიდან

კიდევ ერთი ძალიან გავრცელებული სიტუაციაა ფაილის სახელის ამოღება სრული ბილიკიდან. ფორმის მარტივი რეგულარული გამოხატვა დაგეხმარებათ აქ:

ხრიკი აქ არის ის, რომ ძიება, ფაქტობრივად, ხდება საპირისპირო მიმართულებით - ბოლოდან დასაწყისამდე, რადგან ჩვენი შაბლონის ბოლოს არის $, და ჩვენ ვეძებთ ყველაფერს მანამდე, მარჯვნიდან პირველ უკუხაზამდე. წინა მაგალითში არსებული წერტილის მსგავსად, უკუღმა ხაზგასმულია.

PS

„ბოლოსკენ“ მინდა განვმარტო, რომ ყოველივე ზემოთქმული არის ყველა იმ შესაძლებლობის მცირე ნაწილი, რასაც რეგულარული გამონათქვამები იძლევა. არსებობს უამრავი სპეციალური სიმბოლო და წესი მათი გამოყენებისთვის და ამ თემაზე მთელი წიგნებია დაწერილი (დასაწყისად ამას მაინც გირჩევთ). გარკვეულწილად, რეგულარული გამონათქვამების წერა თითქმის ხელოვნებაა. თითქმის ყოველთვის, გამოგონილი რეგულარული გამოხატულება შეიძლება გაუმჯობესდეს ან დამატებული იყოს, რაც მას უფრო ელეგანტურს გახდის ან უფრო ფართო დიაპაზონთან შეყვანის მონაცემებთან მუშაობას შეძლებს.

სხვა ადამიანების რეგულარული გამონათქვამების გასაანალიზებლად და გასაანალიზებლად ან საკუთარი გამართვისთვის, არსებობს რამდენიმე მოსახერხებელი ონლაინ სერვისი: RegEx101, RegExr და მეტი

სამწუხაროდ, კლასიკური რეგულარული გამონათქვამების ყველა მახასიათებელი არ არის მხარდაჭერილი VBA-ში (მაგალითად, საპირისპირო ძიება ან POSIX კლასები) და შეუძლია მუშაობა კირილიცაზე, მაგრამ მე ვფიქრობ, რომ ის, რაც არსებობს, საკმარისია პირველად რომ მოგაწონოთ.

თუ ახალი არ ხართ ამ თემაზე და გაქვთ რაიმე გასაზიარებელი, დატოვეთ ნორმალური გამონათქვამები სასარგებლო Excel-ში მუშაობისას ქვემოთ მოცემულ კომენტარებში. ერთი გონება კარგია, მაგრამ ორი ჩექმა წყვილია!

• ტექსტის ჩანაცვლება და გასუფთავება SUBSTITUTE ფუნქციით
• ტექსტში ლათინური სიმბოლოების ძიება და ხაზგასმა
• მოძებნეთ უახლოესი მსგავსი ტექსტი (ივანოვი = ივონოვი = ივანოფი და ა.შ.)