შინაარსი
ერთ-ერთი ყველაზე ნახული ვიდეო ჩემს YouTube არხზე არის ვიდეო Flash Fill-ის შესახებ Microsoft Excel-ში. ამ ხელსაწყოს არსი ის არის, რომ თუ თქვენ გჭირდებათ როგორმე გარდაქმნათ თქვენი წყაროს მონაცემები, მაშინ უბრალოდ უნდა დაიწყოთ შედეგის აკრეფა, რომლის მიღებაც გსურთ მიმდებარე სვეტში. რამდენიმე ხელით აკრეფილი უჯრედის შემდეგ (ჩვეულებრივ, 2-3 საკმარისია), Excel „გაიგებს“ თქვენთვის საჭირო ტრანსფორმაციების ლოგიკას და ავტომატურად გააგრძელებს თქვენს მიერ აკრეფილს და დაასრულებს თქვენთვის ერთფეროვან სამუშაოს:
ეფექტურობის კვინტესენცია. ჯადოსნური ღილაკი „გააკეთე სწორად“, რომელიც ყველას ძალიან გვიყვარს, არა?
სინამდვილეში, Power Query-ში არის ასეთი ხელსაწყოს ანალოგი - იქ მას ეძახიან სვეტი მაგალითებიდან (სვეტი მაგალითებიდან). სინამდვილეში, ეს არის Power Query-ში ჩაშენებული პატარა ხელოვნური ინტელექტი, რომელსაც შეუძლია სწრაფად ისწავლოს თქვენი მონაცემებიდან და შემდეგ გარდაქმნას ისინი. მოდით უფრო ახლოს მივხედოთ მის შესაძლებლობებს რამდენიმე პრაქტიკულ სცენარში, რათა გავიგოთ, სად შეიძლება იყოს ის ჩვენთვის სასარგებლო რეალურ ამოცანებში.
მაგალითი 1. ტექსტის წებოვნება/მოჭრა
ვთქვათ, გვაქვს ასეთი "ჭკვიანი" ცხრილი Excel-ში თანამშრომლების მონაცემებით:
ჩატვირთეთ იგი Power Query-ში სტანდარტული გზით - ღილაკით ცხრილიდან / დიაპაზონიდან tab თარიღი (მონაცემები - ცხრილიდან/დიაპაზონიდან).
დავუშვათ, ჩვენ უნდა დავამატოთ სვეტი გვარებით და ინიციალებით თითოეული თანამშრომლისთვის (Ivanov SV პირველი თანამშრომლისთვის და ა.შ.). ამ პრობლემის გადასაჭრელად შეგიძლიათ გამოიყენოთ ორიდან ერთი მეთოდი:
- დააწკაპუნეთ მარჯვენა ღილაკით სვეტის სათაურზე წყაროს მონაცემებით და აირჩიეთ ბრძანება დაამატეთ სვეტი მაგალითებიდან (დაამატე სვეტი მაგალითებიდან);
- აირჩიეთ ერთი ან მეტი სვეტი მონაცემებით და ჩანართზე სვეტის დამატება აირჩიეთ გუნდი სვეტი მაგალითებიდან. აქ, ჩამოსაშლელ სიაში, შეგიძლიათ მიუთითოთ, საჭიროა თუ არა ყველა ან მხოლოდ შერჩეული სვეტის ანალიზი.
შემდეგ ყველაფერი მარტივია - სვეტში, რომელიც ჩნდება მარჯვნივ, ვიწყებთ სასურველი შედეგების მაგალითების შეყვანას და Power Query-ში ჩაშენებული ხელოვნური ინტელექტი ცდილობს გაიგოს ჩვენი ტრანსფორმაციის ლოგიკა და განაგრძოს შემდგომი დამოუკიდებლად:
სხვათა შორის, ამ სვეტის ნებისმიერ უჯრედში შეგიძლიათ შეიყვანოთ სწორი ვარიანტები, ანუ სულაც არ იყოს ზემოდან ქვევით და ზედიზედ. ასევე, თქვენ შეგიძლიათ მარტივად დაამატოთ ან წაშალოთ სვეტები ანალიზიდან მოგვიანებით სათაურის ზოლში არსებული ველის გამოყენებით.
ყურადღება მიაქციეთ ფანჯრის ზედა ფორმულას - ეს არის ის, რასაც ჭკვიანი Power Query ქმნის, რათა მივიღოთ ჩვენთვის საჭირო შედეგები. სხვათა შორის, ეს არის ფუნდამენტური განსხვავება ამ ხელსაწყოსა და მყისიერი შევსება Excel-ში. მყისიერი შევსება მუშაობს როგორც „შავი ყუთი“ – ისინი არ გვაჩვენებენ ტრანსფორმაციების ლოგიკას, არამედ უბრალოდ მზა შედეგებს გვაძლევენ და ჩვენ მათ თავისთავად ვიღებთ. აქ ყველაფერი გამჭვირვალეა და ყოველთვის შეგიძლიათ აბსოლუტურად ნათლად გაიგოთ, რა ხდება ზუსტად მონაცემებთან.
თუ ხედავთ, რომ Power Query-მა „დაიჭირა იდეა“, მაშინ შეგიძლიათ უსაფრთხოდ დააჭიროთ ღილაკს OK ან კლავიატურის მალსახმობი Ctrl+შეიყვანეთ – შეიქმნება მორგებული სვეტი Power Query-ის მიერ გამოგონილი ფორმულით. სხვათა შორის, მოგვიანებით მისი ადვილად რედაქტირება შესაძლებელია, როგორც ჩვეულებრივი ხელით შექმნილი სვეტი (ბრძანებით სვეტის დამატება – მორგებული სვეტი) ნაბიჯის სახელის მარჯვნივ გადაცემათა კოლოფის ხატულაზე დაწკაპუნებით:
მაგალითი 2: შემთხვევა, როგორც წინადადებებში
თუ დააწკაპუნეთ მაუსის მარჯვენა ღილაკით სვეტის სათაურზე ტექსტით და აირჩიეთ ბრძანება ტრანსფორმაციის (ტრანსფორმა), შემდეგ შეგიძლიათ იხილოთ სამი ბრძანება, რომელიც პასუხისმგებელია რეესტრის შეცვლაზე:
მოსახერხებელი და მაგარია, მაგრამ ამ სიაში, მაგალითად, მე პირადად ყოველთვის მაკლდა კიდევ ერთი ვარიანტი - შემთხვევა, როგორც წინადადებებში, როდესაც კაპიტალი (მთავრული) ხდება არა პირველი ასო ყოველ სიტყვაში, არამედ მხოლოდ პირველი ასო უჯრედში და ტექსტის დანარჩენი ნაწილი, როდესაც ეს ნაჩვენებია პატარა (პატარა) ასოებით.
ეს გამოტოვებული ფუნქცია მარტივია ხელოვნური ინტელექტის გამოყენებით სვეტები მაგალითებიდან – უბრალოდ შეიყვანეთ Power Query-ის რამდენიმე ვარიანტი, რომ გააგრძელოთ იგივე სულისკვეთებით:
როგორც ფორმულა აქ, Power Query იყენებს უამრავ ფუნქციას ტექსტი.ზედა и ტექსტი.ქვედა, ტექსტის გარდაქმნა, შესაბამისად, დიდ და პატარა რეზერვებში და ფუნქციები ტექსტი.დაწყება и ტექსტი.შუაში – Excel-ის ფუნქციების LEFT და PSTR ანალოგები, რომლებსაც შეუძლიათ ტექსტიდან ქვესტრიქონის ამოღება მარცხნიდან და შუადან.
მაგალითი 3. სიტყვების პერმუტაცია
ზოგჯერ მიღებული მონაცემების დამუშავებისას საჭირო ხდება უჯრედებში სიტყვების მოცემული თანმიმდევრობით გადაწყობა. რა თქმა უნდა, შეგიძლიათ სვეტი გაყოთ ცალკეული სიტყვების სვეტებად გამყოფის საშუალებით და შემდეგ დააწებოთ მითითებული თანმიმდევრობით (არ დაგავიწყდეთ შორისების დამატება), მაგრამ ხელსაწყოს დახმარებით. სვეტი მაგალითებიდან ყველაფერი ბევრად უფრო ადვილი იქნება:
მაგალითი 4: მხოლოდ რიცხვები
კიდევ ერთი ძალიან მნიშვნელოვანი ამოცანაა უჯრედის შიგთავსიდან მხოლოდ რიცხვების (რიცხვების) ამოღება. როგორც ადრე, Power Query-ში მონაცემების ჩატვირთვის შემდეგ გადადით ჩანართზე სვეტის დამატება - სვეტი მაგალითებიდან და შეავსეთ რამდენიმე უჯრედი ხელით ისე, რომ პროგრამამ გაიგოს, რისი მიღება გვინდა:
Bingo!
კიდევ ერთხელ, ღირს ფანჯრის ზედა ნაწილის გადახედვა, რათა დარწმუნდეთ, რომ Query-მ სწორად შექმნა ფორმულა – ამ შემთხვევაში ის შეიცავს ფუნქციას ტექსტი. აირჩიეთ, რომელიც, როგორც თქვენ მიხვდით, ამოიღებს მოცემულ სიმბოლოებს საწყისი ტექსტიდან სიის მიხედვით. შემდგომში, ეს სია, რა თქმა უნდა, შეიძლება ადვილად რედაქტირდეს ფორმულების ზოლში საჭიროების შემთხვევაში.
მაგალითი 5: მხოლოდ ტექსტი
წინა მაგალითის მსგავსად, შეგიძლიათ ამოიღოთ და პირიქით – მხოლოდ ტექსტი, წაშალოთ ყველა რიცხვი, სასვენი ნიშნები და ა.შ.
ამ შემთხვევაში გამოიყენება ფუნქცია, რომელიც უკვე საპირისპიროა მნიშვნელობით – Text.Remove, რომელიც ამოიღებს სიმბოლოებს ორიგინალური სტრიქონიდან მოცემული სიის მიხედვით.
მაგალითი 6: მონაცემების ამოღება ალფანუმერული ფაფიდან
Power Query ასევე დაგეხმარებათ უფრო რთულ შემთხვევებში, როდესაც გჭირდებათ სასარგებლო ინფორმაციის ამოღება საკანში არსებული ალფანუმერული ფაფიდან, მაგალითად, მიიღეთ ანგარიშის ნომერი საბანკო ამონაწერში გადახდის მიზნის აღწერიდან:
გაითვალისწინეთ, რომ Power Query-ის გენერირებული კონვერტაციის ფორმულა შეიძლება საკმაოდ რთული იყოს:
წაკითხვისა და გაგების გასაადვილებლად, ის შეიძლება გადაკეთდეს ბევრად უფრო საღი ფორმად უფასო ონლაინ სერვისის გამოყენებით. Power Query Formatter:
ძალიან მოსახერხებელი რამ - პატივისცემა შემქმნელებს!
მაგალითი 7: თარიღების კონვერტაცია
Tool სვეტი მაგალითებიდან შეიძლება გამოყენებულ იქნას თარიღის ან თარიღის დროის სვეტებზეც. როდესაც შეიყვანთ თარიღის პირველ ციფრებს, Power Query გამოსადეგად აჩვენებს ყველა შესაძლო კონვერტაციის ვარიანტების ჩამონათვალს:
ასე რომ, თქვენ შეგიძლიათ მარტივად გადაიყვანოთ ორიგინალური თარიღი ნებისმიერ ეგზოტიკურ ფორმატში, როგორიცაა "წელი-თვე-დღე":
მაგალითი 8: კატეგორიზაცია
თუ იარაღს ვიყენებთ სვეტი მაგალითებიდან რიცხვითი მონაცემების მქონე სვეტში, ის სხვაგვარად მუშაობს. დავუშვათ, რომ ჩვენ გვაქვს თანამშრომლების ტესტის შედეგები ჩატვირთული Power Query-ში (პირობითი ქულები 0-100 დიაპაზონში) და ვიყენებთ შემდეგ პირობით გრადაციას:
- მასტერსი - ისინი, ვინც 90-ზე მეტი ქულა გაიტანა
- ექსპერტები - 70-დან 90-მდე ქულა
- მომხმარებლები - 30-დან 70-მდე
- დამწყები - ისინი, ვინც 30-ზე ნაკლები ქულა დააგროვეს
თუ ჩვენ დავამატებთ სვეტს მაგალითებიდან სიაში და დავიწყებთ ამ გრადაციების ხელით მოწყობას, მაშინ ძალიან მალე Power Query აიღებს ჩვენს იდეას და დაამატებს სვეტს ფორმულით, სადაც ოპერატორები ერთმანეთში ბუდობენ. if ლოგიკა განხორციელდება, ძალიან ჰგავს იმას, რაც ჩვენ გვჭირდება:
ისევ და ისევ, თქვენ არ შეგიძლიათ დააჭიროთ სიტუაციას ბოლომდე, მაგრამ დააწკაპუნეთ OK და შემდეგ შეასწორეთ ბარიერის მნიშვნელობები უკვე ფორმულაში - ეს უფრო სწრაფია შემდეგნაირად:
დასკვნები
რა თქმა უნდა, ინსტრუმენტი სვეტი მაგალითებიდან არ არის "ჯადოსნური აბი" და, ადრე თუ გვიან, იქნება არასტანდარტული სიტუაციები ან განსაკუთრებით უგულებელყოფილი შემთხვევები "კოლექტიური მეურნეობის" მონაცემებში, როდესაც Power Query ჩავარდება და ვერ შეძლებს იმის გარკვევას, რაც გვინდა. ჩვენთვის სწორად. თუმცა, როგორც დამხმარე ინსტრუმენტი, ძალიან კარგია. გარდა ამისა, მის მიერ გენერირებული ფორმულების შესწავლით, შეგიძლიათ გააფართოვოთ ცოდნა M ენის ფუნქციების შესახებ, რაც მომავალში ყოველთვის გამოგადგებათ.
- ტექსტის გაანალიზება რეგულარული გამოსახულებებით (RegExp) Power Query-ში
- ბუნდოვანი ტექსტის ძიება Power Query-ში
- Flash შეავსეთ Microsoft Excel