შინაარსი
წყაროს ტექსტში საკვანძო სიტყვების ძიება ერთ-ერთი ყველაზე გავრცელებული ამოცანაა მონაცემებთან მუშაობისას. მოდით შევხედოთ მის გადაწყვეტას რამდენიმე გზით შემდეგი მაგალითის გამოყენებით:
დავუშვათ, რომ მე და თქვენ გვაქვს საკვანძო სიტყვების სია – მანქანის ბრენდების სახელები – და დიდი ცხრილი ყველა სახის სათადარიგო ნაწილებისგან, სადაც აღწერილობები ზოგჯერ შეიძლება შეიცავდეს ერთ ან რამდენიმე ასეთ ბრენდს ერთდროულად, თუ სათადარიგო ნაწილი ერთზე მეტს ეტევა. მარკის მანქანა. ჩვენი ამოცანაა ვიპოვოთ და გამოვაჩინოთ ყველა აღმოჩენილი საკვანძო სიტყვა მეზობელ უჯრედებში მოცემული გამყოფი სიმბოლოს მეშვეობით (მაგალითად, მძიმით).
მეთოდი 1. Power Query
რა თქმა უნდა, პირველ რიგში, ჩვენ ვაქცევთ ჩვენს ცხრილებს დინამიურ ("ჭკვიან") კლავიატურის მალსახმობის გამოყენებით Ctrl+T ან ბრძანებებს მთავარი – ფორმატირება როგორც ცხრილი (მთავარი - ფორმატირება როგორც ცხრილი)დაასახელეთ მათ სახელები (მაგალითად მარკებიи სათადარიგო ნაწილები) და ჩატვირთეთ სათითაოდ Power Query რედაქტორში ჩანართზე არჩევით მონაცემები – ცხრილიდან/დიაპაზონიდან (მონაცემები - ცხრილიდან/დიაპაზონიდან). თუ თქვენ გაქვთ Excel 2010-2013 ძველი ვერსიები, სადაც Power Query დაინსტალირებულია ცალკე დანამატის სახით, მაშინ სასურველი ღილაკი იქნება ჩანართზე. დენის მოთხოვნა. თუ თქვენ გაქვთ Excel 365-ის ახალი ვერსია, მაშინ ღილაკი ცხრილიდან / დიაპაზონიდან დარეკა ახლა იქ ფოთლებით (ფურცლიდან).
Power Query-ში თითოეული ცხრილის ჩატვირთვის შემდეგ, ჩვენ ვუბრუნდებით Excel-ს ბრძანებით მთავარი — დახურვა და ჩატვირთვა — დახურვა და ჩატვირთვა… — მხოლოდ კავშირის შექმნა (მთავარი — დახურვა და ჩატვირთვა — დახურვა და ჩატვირთვა… — მხოლოდ კავშირის შექმნა).
ახლა მოდით შევქმნათ დუბლიკატი მოთხოვნა სათადარიგო ნაწილებიმასზე მაუსის მარჯვენა ღილაკით და არჩევით დუბლიკატი მოთხოვნა (დუბლიკატი შეკითხვა), შემდეგ დაარქვით მიღებული ასლის მოთხოვნას შედეგები და ჩვენ გავაგრძელებთ მასთან მუშაობას.
მოქმედებების ლოგიკა შემდეგია:
- Advanced ჩანართზე სვეტის დამატება აირჩიეთ გუნდი მორგებული სვეტი (სვეტის დამატება — მორგებული სვეტი) და შეიყვანეთ ფორმულა = ბრენდები. დაწკაპუნების შემდეგ OK ჩვენ მივიღებთ ახალ სვეტს, სადაც თითოეულ უჯრედში იქნება ჩადგმული ცხრილი ჩვენი საკვანძო სიტყვების სიით - ავტომწარმოებლის ბრენდები:
- გამოიყენეთ ღილაკი ორმაგი ისრებით დამატებული სვეტის სათაურში, რათა გააფართოვოთ ყველა ჩადგმული ცხრილი. ამავდროულად, სათადარიგო ნაწილების აღწერილობითი ხაზები გამრავლდება ბრენდების რაოდენობის ჯერადად და მივიღებთ "სათადარიგო ნაწილის ბრენდის" ყველა შესაძლო წყვილ-კომბინაციას:
- Advanced ჩანართზე სვეტის დამატება აირჩიეთ გუნდი პირობითი სვეტი (პირობითი სვეტი) და დააყენეთ პირობა საკვანძო სიტყვის (ბრენდის) არსებობის შესამოწმებლად საწყის ტექსტში (ნაწილის აღწერა):
- იმისთვის, რომ საძიებო ქეისი არასენსიტიური იყოს, ხელით დაამატეთ მესამე არგუმენტი ფორმულების ზოლში შედარება.OrdinalIgnoreCase შემთხვევის შემოწმების ფუნქციამდე ტექსტი.შეიცავს (თუ ფორმულის ზოლი არ ჩანს, მაშინ მისი ჩართვა შესაძლებელია ჩანართზე მიმოხილვა):
- ჩვენ ვფილტრავთ მიღებულ ცხრილს, ვტოვებთ მხოლოდ ერთს ბოლო სვეტში, ანუ ემთხვევა და ვხსნით არასაჭირო სვეტს მოვლენები.
- იდენტური აღწერილობების დაჯგუფება ბრძანებით Ით დაჯგუფება tab ტრანსფორმაციის (ტრანსფორმა - ჯგუფის მიხედვით). როგორც აგრეგაციის ოპერაცია, აირჩიეთ ყველა ხაზი (ყველა მწკრივი). გამოსავალზე ვიღებთ სვეტს ცხრილებით, რომელიც შეიცავს ყველა დეტალს თითოეული სათადარიგო ნაწილისთვის, მათ შორის ავტომწარმოებლების ბრენდებისთვის, რომლებიც გვჭირდება:
- თითოეული ნაწილისთვის ქულების ამოსაღებად, დაამატეთ კიდევ ერთი გამოთვლილი სვეტი ჩანართზე სვეტის დამატება – მორგებული სვეტი (სვეტის დამატება — მორგებული სვეტი) და გამოიყენეთ ფორმულა, რომელიც შედგება ცხრილისგან (ისინი განლაგებულია ჩვენს სვეტში დაწვრილებით) და ამოღებული სვეტის სახელი:
- ჩვენ ვაჭერთ ღილაკს ორმაგი ისრებით მიღებული სვეტის სათაურში და ვირჩევთ ბრძანებას ამოიღეთ მნიშვნელობები (ამოიღეთ მნიშვნელობები)მარკების გამოსატანად თქვენთვის სასურველი ნებისმიერი გამსაზღვრელი სიმბოლოთი:
- არასაჭირო სვეტის ამოღება დაწვრილებით.
- მიღებულ ცხრილს რომ დავამატოთ მისგან გაუჩინარებული ნაწილები, სადაც აღწერილობაში არ არის ნაპოვნი ბრენდები, ჩვენ ვასრულებთ მოთხოვნის კომბინირების პროცედურას. შედეგი ორიგინალური მოთხოვნით სათადარიგო ნაწილები ღილაკს შეუთავსეთ tab მთავარი (მთავარი - მოთხოვნების შერწყმა). Კავშირის ტიპი - გარე შეერთება მარჯვნივ (მარჯვენა გარე შეერთება):
- რჩება მხოლოდ ზედმეტი სვეტების ამოღება და დარჩენილი სვეტების გადარქმევა-გადატანა - და ჩვენი ამოცანა მოგვარებულია:
მეთოდი 2. ფორმულები
თუ თქვენ გაქვთ Excel 2016 ან უფრო ახალი ვერსია, მაშინ ჩვენი პრობლემა შეიძლება მოგვარდეს ძალიან კომპაქტური და ელეგანტური გზით ახალი ფუნქციის გამოყენებით COMBINE (TEXTJOIN):
ამ ფორმულის ლოგიკა მარტივია:
- ფუნქცია ძიება (იპოვე) რიგრიგობით ეძებს თითოეული ბრენდის შემთხვევას ნაწილის მიმდინარე აღწერაში და აბრუნებს ან სიმბოლოს სერიულ ნომერს, საიდანაც იპოვეს ბრენდი, ან შეცდომა #VALUE! თუ ბრენდი არ არის აღწერილობაში.
- შემდეგ ფუნქციის გამოყენებით IF (თუ) и ეოშიბკა (ISERROR) ჩვენ ვცვლით შეცდომებს ცარიელი ტექსტური სტრიქონით, ხოლო სიმბოლოების რიგითი ნომრები თავად ბრენდის სახელებით.
- ცარიელი უჯრედებისა და ნაპოვნი ბრენდების შედეგად მიღებული მასივი იკრიბება ერთ სტრიქონში მოცემული გამყოფი სიმბოლოს მეშვეობით ფუნქციის გამოყენებით COMBINE (TEXTJOIN).
შესრულების შედარება და Power Query შეკითხვის ბუფერირება დაჩქარებისთვის
შესრულების შესამოწმებლად, ავიღოთ 100 სათადარიგო ნაწილების აღწერილობის ცხრილი, როგორც საწყისი მონაცემები. მასზე ვიღებთ შემდეგ შედეგებს:
- გადაანგარიშების დრო ფორმულებით (მეთოდი 2) – 9 წმ. როდესაც პირველად დააკოპირებთ ფორმულას მთელ სვეტში და 2 წმ. განმეორებით (სავარაუდოდ, ბუფერული ეფექტი).
- Power Query-ის (მეთოდი 1) განახლების დრო გაცილებით უარესია - 110 წამი.
რა თქმა უნდა, ბევრი რამ არის დამოკიდებული კონკრეტული კომპიუტერის აპარატურაზე და Office-ის დაინსტალირებულ ვერსიაზე და განახლებებზე, მაგრამ საერთო სურათი, ვფიქრობ, ნათელია.
Power Query-ის მოთხოვნის დასაჩქარებლად, მოდით შევცვალოთ საძიებო ცხრილი მარკები, რადგან ის არ იცვლება მოთხოვნის შესრულების პროცესში და არ არის საჭირო მისი მუდმივი გადათვლა (როგორც ამას დე ფაქტო Power Query აკეთებს). ამისთვის ვიყენებთ ფუნქციას ცხრილი.ბუფერი ჩაშენებული Power Query ენიდან M.
ამისათვის გახსენით შეკითხვა შედეგები და ჩანართზე მიმოხილვა დააჭირეთ ღილაკს გაფართოებული რედაქტორი (ნახვა - გაფართოებული რედაქტორი). ფანჯარაში, რომელიც იხსნება, დაამატეთ ხაზი ახალი ცვლადით მარკი 2, რომელიც იქნება ჩვენი ავტომწარმოებლის დირექტორიას ბუფერული ვერსია და გამოიყენეთ ეს ახალი ცვლადი მოგვიანებით შემდეგი შეკითხვის ბრძანებაში:
ასეთი დახვეწის შემდეგ ჩვენი მოთხოვნის განახლების სიჩქარე იზრდება თითქმის 7-ჯერ – 15 წამამდე. სულ სხვა რამეა 🙂
- ბუნდოვანი ტექსტის ძიება Power Query-ში
- ნაყარი ტექსტის შეცვლა ფორმულებით
- სრული ტექსტის ჩანაცვლება Power Query-ში List.Acumulate ფუნქციით