შინაარსი
თუ ოდნავ მაინც იცნობთ რეგულარულ გამონათქვამებს, მაშინ არ გჭირდებათ მათი რეკლამირება. თუ არ ხართ მთლად საგანში, მაშინ რეგულარული გამონათქვამები (Regular Expressions = RegExp = „regexps“ = „Regular“) არის ენა, სადაც სპეციალური სიმბოლოებისა და წესების გამოყენებით, საჭირო ქვესტრიქონები იძებნება ტექსტში, მათი ამოღება. ან ჩანაცვლებულია სხვა ტექსტით. ეს არის ძალიან მძლავრი და ლამაზი ხელსაწყო, სიდიდის ბრძანება აღემატება ტექსტთან მუშაობის ყველა სხვა ხერხს.
მე უკვე აღვწერე დეტალურად და ცხოვრებიდან უამრავი მაგალითით, თუ როგორ შეგიძლიათ დაამატოთ რეგულარული გამოხატვის მხარდაჭერა Excel-ში მარტივი მაკროების გამოყენებით - თუ ეს სტატია არ წაგიკითხავთ, გირჩევთ წაიკითხოთ იგი გაგრძელებამდე. ბევრ ახალს აღმოაჩენთ, გარანტიას გაძლევთ 🙂
თუმცა, კითხვა ღია რჩება – როგორ დავამატოთ Power Query-ში რეგულარული გამონათქვამების გამოყენების შესაძლებლობა? Power Query, რა თქმა უნდა, თავისთავად კარგია და ბევრის გაკეთება შეუძლია ტექსტით (დაჭრა, წებოვნება, გაწმენდა და ა.შ.), მაგრამ თუ შეგეძლოთ მისი გადაკვეთა რეგულარული გამონათქვამების ძალით, ეს მხოლოდ ბომბი იქნებოდა.
სამწუხაროდ, არ არის ჩაშენებული ფუნქციები RegExps-თან მუშაობისთვის Power Query-ში და Microsoft-ის ოფიციალური დახმარება და ტექნიკური მხარდაჭერა ამ კითხვაზე უარყოფითად პასუხობს. თუმცა, ამ შეზღუდვის თავიდან აცილების გზა არსებობს 🙂
მეთოდის არსი
მთავარი იდეა მარტივია სამარცხვინო.
ჩაშენებული Power Query შესაძლებლობების სიაში არის ფუნქცია Ვებ გვერდი. ამ ფუნქციის აღწერა Microsoft-ის დახმარების ოფიციალურ საიტზე ძალიან მოკლეა:
თარგმნა, ეს იქნება: „აბრუნებს HTML დოკუმენტის შიგთავსს დაყოფილი მის კომპონენტურ სტრუქტურებად, ისევე როგორც სრული დოკუმენტისა და მისი სხეულის წარმოდგენას თეგების წაშლის შემდეგ“. ასე-ასე აღწერა, გულწრფელად.
ჩვეულებრივ, ეს ფუნქცია გამოიყენება ვებიდან მონაცემების იმპორტის დროს და ავტომატურად იცვლება, მაგალითად, ჩანართზე არჩევისას თარიღი Command ინტერნეტიდან (მონაცემები - ვებიდან). ფუნქციას არგუმენტად ვაძლევთ ვებ გვერდს და ის გვაბრუნებს მის შინაარსს ცხრილების სახით, მანამდე კი ყველა ტეგი გაასუფთავა.
რასაც დახმარება არ ამბობს არის ის, რომ გარდა HTML მარკირების ენისა ფუნქცია Ვებ გვერდი მხარს უჭერს JavaScript სკრიპტებს, რომელიც ახლა ყველგან არის გავრცელებული ინტერნეტის ვებსაიტებზე. და JavaScript, თავის მხრივ, ყოველთვის ახერხებდა რეგულარულ გამონათქვამებთან მუშაობას და აქვს ჩაშენებული ფუნქციები RegExps-ისთვის! ასე რომ, Power Query-ში რეგულარული გამონათქვამების განსახორციელებლად, ჩვენ უნდა მივცეთ Web.Page ფუნქციები, როგორც არგუმენტი მცირე JavaScript პროგრამისთვის, რომელიც შეასრულებს ყველა სამუშაოს Power Query-ისთვის.
როგორ გამოიყურება სუფთა JavaScript-ში
ინტერნეტში არსებობს უამრავი დეტალური გაკვეთილი JavaScript-ში რეგულარულ გამონათქვამებთან მუშაობის შესახებ (მაგალითად, ერთი, ორი).
მოკლედ და გამარტივებულად, JavaScript კოდი ასე გამოიყურება:
აქ:
- var str = 'გადაიხადე გადასახადები 123 და 789 სოსისისთვის'; - შექმენით ცვლადი ქ და მივცეთ მას წყარო ტექსტი, რომელსაც გავაანალიზებთ.
- var ნიმუში = /d+/gi; – შექმენით რეგულარული გამოხატულება და ჩასვით ცვლადში ნიმუში.
გამოთქმა იწყება ხაზით (/).
თავად გამოთქმა აქ, მაგალითად, არის d+ ნიშნავს ციფრების ნებისმიერ თანმიმდევრობას.
გამონათქვამის შემდგომი წილადის მეშვეობით არის დამატებითი საძიებო პარამეტრები (მოდიფიკატორები) – მათი დაზუსტება შესაძლებელია ნებისმიერი თანმიმდევრობით:
- g – ნიშნავს გლობალურ ძიებას, ანუ შესატყვისის პოვნის შემდეგ არ უნდა გაჩერდე, არამედ გააგრძელო ძებნა ტექსტის ბოლომდე. თუ ეს მოდიფიკატორი არ არის დაყენებული, მაშინ ჩვენი სკრიპტი დააბრუნებს მხოლოდ პირველ შესაბამისობას (123)
- i - ძიება ასოების შემთხვევის გარეშე
- m - მრავალ სტრიქონიანი ძებნა (გამოიყენება, როდესაც საწყისი ტექსტი იყოფა რამდენიმე სტრიქონად)
- var result = str.match(pattern).join(';'); - განახორციელეთ ძიება საწყის ტექსტში (ქ) მოცემული რეგულარული გამოსახულებით (ნიმუში) და ჩადეთ შედეგები ცვლადში შედეგად, დააკავშირეთ ისინი მძიმით ბრძანების გამოყენებით შეუერთდება
- დოკუმენტი.ჩაწერა (შედეგი); - შედეგის ცვლადის შიგთავსის ჩვენება
ასევე გაითვალისწინეთ, რომ JavaScript-ში ტექსტის სტრიქონები (რეგულარული გამონათქვამების გამოკლებით) ჩასმულია აპოსტროფებში და არა ციტატებში, როგორც Power Query-ში ან VBA-ში.
გამოსავალზე, ეს სკრიპტი მოგვცემს შედეგად ყველა ციფრს, რომელიც ნაპოვნია საწყის ტექსტში:
123, 789
JavaScript-ის მოკლე კურსი დასრულდა, მადლობა ყველას. იმედია მიხვდებით ლოგიკას 🙂
რჩება ამ კონსტრუქციის Power Query-ზე გადატანა.
ძიება და ამონაწერი ტექსტის ფუნქცია რეგულარული გამოხატვის მიხედვით Power Query-ში
ჩვენ ვაკეთებთ შემდეგს:
1. გახსენით Excel და შექმენით ახალი ცარიელი Power Query ჩანართში მონაცემები – მიიღეთ მონაცემები / შექმენით მოთხოვნა – სხვა წყაროებიდან – ცარიელი მოთხოვნა (მონაცემები — მიიღეთ მონაცემები / ახალი მოთხოვნა — სხვა წყაროებიდან — ცარიელი მოთხოვნა). თუ თქვენ გაქვთ Excel 2010-2013 და Power Query-ის ძველი ვერსია, არ გაქვთ ჩაშენებული, მაგრამ დაინსტალირებულია როგორც ცალკე დანამატი, მაშინ ეს ყველაფერი იქნება ჩანართზე დენის მოთხოვნაᲓა არა თარიღი.
2. შეკითხვის რედაქტორის ცარიელ ფანჯარაში, რომელიც იხსნება, მარჯვენა პანელში, დაუყოვნებლივ შეიყვანეთ ჩვენი მომავალი ფუნქციის სახელი (მაგალითად, fxRegExpExtract)
3. მოდით გადავიდეთ ჩანართზე ნახვა - გაფართოებული რედაქტორი (ნახვა - გაფართოებული რედაქტორი)ჩვენ ვშლით ცარიელი მოთხოვნის მთელ M-კოდს და ჩავსვით იქ ჩვენი სუპერფუნქციის კოდს:
უყურე ხელებს:
პირველ რიგში, ჩვენ ვამბობთ, რომ ჩვენს ფუნქციას ექნება სამი ტექსტური არგუმენტი: txt - გაანალიზებულია ორიგინალი ტექსტი, რეგესი - რეგულარული გამოხატვის ნიმუში, დელიმური — დელიმიტერული სიმბოლო შედეგების ჩვენებისთვის.
შემდეგ ჩვენ ვუწოდებთ ფუნქციას Ვებ გვერდითავის არგუმენტში ზემოთ აღწერილი JavaScript კოდის ფორმირება. ჩვენ ვამაგრებთ და ვცვლით ჩვენს ცვლადის არგუმენტებს კოდში.
ფრაგმენტი:
[მონაცემები]{0}[Children]{0}[Children]{1}[Text]{0}
… საჭიროა იმისათვის, რომ „ჩამოვარდეს“ ცხრილში ჩვენთვის საჭირო შედეგებით. საქმე იმაშია, რომ ფუნქცია Ვებ გვერდი შედეგად, ის აწარმოებს რამდენიმე ჩადგმულ ცხრილს, რომლებიც იმეორებენ ვებ გვერდის სტრუქტურას. M-კოდის ამ ნაწილის გარეშე, ჩვენი ფუნქცია გამოსცემს შემდეგს:
… და ჩვენ უნდა დავაწკაპუნოთ სიტყვაზე რამდენჯერმე მაგიდის, თანმიმდევრულად "ჩავარდნილი" სვეტებში ბავშვების ჩადგმულ ცხრილებში ბავშვები:
მთელი ამ ციტატის ნაცვლად, ჩვენ დაუყოვნებლივ მივუთითებთ ჩვენი ფუნქციის კოდში, რომელია ჩასმული ცხრილი და სვეტი (ტექსტი) გვჭირდება.
აქ, ფაქტობრივად, ყველა საიდუმლოებაა. რჩება ღილაკზე დაჭერა ფერი ფანჯარაში მოწინავე რედაქტორი, სადაც ჩავსვით ჩვენი კოდი და შეგიძლიათ გადახვიდეთ უგემრიელესზე – სცადეთ ჩვენი ფუნქცია სამსახურში.
აქ მოცემულია თესლის რამდენიმე მაგალითი.
მაგალითი 1. ანგარიშის ნომრის და თარიღის მიღება გადახდის აღწერილობიდან
ჩვენ გვაქვს საბანკო ამონაწერი გადახდების აღწერილობით (მიზნით), სადაც თქვენ უნდა ამოიღოთ გადახდილი ინვოისების ნომრები და თარიღები ცალკეულ სვეტებში:
ჩვენ ვტვირთავთ ცხრილს Power Query-ში სტანდარტული გზით მონაცემები – ცხრილიდან/დიაპაზონიდან (მონაცემები - თშეუძლია/რანგელოზი).
შემდეგ ჩვენ ვამატებთ გამოთვლილ სვეტს ჩვენი ფუნქციის მეშვეობით სვეტის დამატება – მორგებული ფუნქციის გამოძახება (სვეტის დამატება - მორგებული ფუნქციის გამოძახება) და შეიყვანეთ მისი არგუმენტები:
როგორც რეგულარული გამოხატულება (არგუმენტი რეგესი) შაბლონი ჩვენ ვიყენებთ:
(დ{3,5}|d{2}.d{2}.d{4})
… ითარგმნა ადამიანურ ენაზე, რაც ნიშნავს:
ნომრები 3-დან 5-ციფრამდე (ანგარიშის ნომრები)
or
ფორმის ფრაგმენტები "2-ბიტიანი ნომერი - წერტილი - 2-ბიტიანი ნომერი - წერტილი - 4-ბიტიანი ნომერი", ანუ DD.MM.YYYY ფორმის თარიღები.
როგორც განმსაზღვრელი პერსონაჟი (არგუმენტი დელიმური) შეიყვანეთ მძიმით.
დაწკაპვით OK ჩვენი ჯადოსნური ფუნქცია აანალიზებს ყველა საწყის მონაცემს ჩვენი რეგულარული გამოხატვის მიხედვით და ქმნის ჩვენთვის სვეტს ნაპოვნი ნომრებითა და ინვოისების თარიღებით:
რჩება მისი გამოყოფა მძიმით ბრძანების გამოყენებით მთავარი — გაყოფილი სვეტი — საზღვრის მიხედვით (მთავარი — სვეტის გაყოფა — დელიმიტერის მიხედვით) და ჩვენ მივიღებთ იმას, რაც გვინდოდა:
სილამაზე!
მაგალითი 2: ამოიღეთ ელფოსტის მისამართები ტექსტიდან
დავუშვათ, საწყის მონაცემად გვაქვს შემდეგი ცხრილი:
… საიდანაც ჩვენ უნდა ამოვიღოთ იქ ნაპოვნი ელ.ფოსტის მისამართები (სიცხადისთვის, მე მათ ტექსტში წითლად ავღნიშნე).
როგორც წინა მაგალითში, ჩვენ ვტვირთავთ ცხრილს Power Query-ში სტანდარტული გზით მონაცემები – ცხრილიდან/დიაპაზონიდან (მონაცემები - თშეუძლია/რანგელოზი).
შემდეგ ჩვენ ვამატებთ გამოთვლილ სვეტს ჩვენი ფუნქციის მეშვეობით სვეტის დამატება – მორგებული ფუნქციის გამოძახება (სვეტის დამატება - მორგებული ფუნქციის გამოძახება) და შეიყვანეთ მისი არგუმენტები:
ელ.ფოსტის მისამართების ანალიზი უფრო რთული ამოცანაა და მის გადასაჭრელად არსებობს სხვადასხვა ხარისხის კოშმარის რეგულარული გამონათქვამები. მე გამოვიყენე ერთ-ერთი მარტივი ვარიანტი - არა იდეალური, მაგრამ უმეტეს შემთხვევაში საკმაოდ მუშაობს:
[w|.|-]*@w*.[w|.]*
როგორც გამყოფი (დელიმური) შეგიძლიათ შეიყვანოთ მძიმით და ინტერვალით.
დაწკაპეთ OK და ვიღებთ სვეტს ელ.ფოსტის მისამართებით ამოღებული ორიგინალური ტექსტიდან „ფაფა“:
მაგია!
PS
როგორც ნათქვამია: "არ არსებობს ისეთი კარგი რამ, რაც არ შეიძლება უკეთესი გახდეს." Power Query თავისთავად მაგარია და რეგულარულ გამონათქვამებთან შერწყმისას ის გვაძლევს სრულიად არარეალურ ძალასა და მოქნილობას ნებისმიერი ტექსტური მონაცემების დამუშავებისას. ვიმედოვნებ, რომ Microsoft ოდესმე დაამატებს RegExp მხარდაჭერას Power Query-სა და Power BI-ის განახლებებში და ყველა ზემოთ ჩამოთვლილი ცეკვა ტამბურით წარსულს ჩაბარდება. კარგი, ჯერჯერობით, დიახ.
ასევე მინდა დავამატო, რომ მოსახერხებელია რეგულარული გამონათქვამებით თამაში საიტზე https://regexr.com/ – პირდაპირ ონლაინ რედაქტორში. იქ განყოფილებაში საზოგადოების ნიმუშები არსებობს უამრავი მზა რეგულარული სეზონი ყველა შემთხვევისთვის. ექსპერიმენტი – რეგულარული გამონათქვამების მთელი ძალა ახლა თქვენს სამსახურშია Power Query-ში!
- რა არის რეგულარული გამონათქვამები (RegExp) და როგორ გამოვიყენოთ ისინი Excel-ში
- ბუნდოვანი ტექსტის ძიება Power Query-ში
- ცხრილების აწყობა სხვადასხვა ფაილებიდან Power Query-ის გამოყენებით