ახლახან განვიხილეთ FILTER.XML ფუნქციის გამოყენება XML მონაცემების ინტერნეტიდან იმპორტისთვის - მთავარი ამოცანა, რომლისთვისაც ეს ფუნქცია, ფაქტობრივად, არის განკუთვნილი. თუმცა, გზაში გამოჩნდა ამ ფუნქციის კიდევ ერთი მოულოდნელი და ლამაზი გამოყენება - წებოვანი ტექსტის უჯრედებად სწრაფად დაყოფისთვის.
ვთქვათ, გვაქვს მონაცემთა სვეტი, როგორიცაა:
რა თქმა უნდა, მოხერხებულობისთვის, მსურს დავყო იგი ცალკეულ სვეტებად: კომპანიის სახელი, ქალაქი, ქუჩა, სახლი. ამის გაკეთება შეგიძლიათ სხვადასხვა გზით:
- გამოყენება ტექსტი სვეტების მიხედვით ჩანართიდან თარიღი (მონაცემები - ტექსტი სვეტებში) და გადადით სამი ნაბიჯით ტექსტის პარსერი. მაგრამ თუ მონაცემები ხვალ შეიცვლება, მთელი პროცესის გამეორება მოგიწევთ.
- ჩატვირთეთ ეს მონაცემები Power Query-ში და გაყავით იქ, შემდეგ ატვირთეთ ისევ ფურცელზე და შემდეგ განაახლეთ მოთხოვნა, როდესაც მონაცემები შეიცვლება (რაც უკვე ადვილია).
- თუ თქვენ გჭირდებათ განახლების პროცესი, მაშინ შეგიძლიათ დაწეროთ რამდენიმე ძალიან რთული ფორმულა, რომ იპოვოთ მძიმეები და ამოიღოთ ტექსტი მათ შორის.
და შეგიძლია უფრო ელეგანტურად გააკეთო და გამოიყენო FILTER.XML ფუნქცია, მაგრამ რა შუაშია ეს?
FILTER.XML ფუნქცია საწყის არგუმენტად იღებს XML კოდს - ტექსტს, რომელიც მონიშნულია სპეციალური ტეგებითა და ატრიბუტებით და შემდეგ ანაწილებს მას კომპონენტებად, ამოიღებს ჩვენთვის საჭირო მონაცემთა ფრაგმენტებს. XML კოდი ჩვეულებრივ ასე გამოიყურება:
XML-ში, მონაცემთა თითოეული ელემენტი უნდა იყოს ჩასმული ტეგებში. ტეგი არის გარკვეული ტექსტი (ზემოთ მოცემულ მაგალითში არის მენეჯერი, სახელი, მოგება) ჩასმული კუთხის ფრჩხილებში. ტეგები ყოველთვის წყვილებში მოდის - იხსნება და იხურება (დასაწყისზე დამატებული ხაზებით).
FILTER.XML ფუნქციას შეუძლია ადვილად ამოიღოს ჩვენთვის საჭირო ყველა ტეგის შიგთავსი, მაგალითად, ყველა მენეჯერის სახელები და (რაც მთავარია) ერთდროულად აჩვენოს ისინი ერთ სიაში. ასე რომ, ჩვენი ამოცანაა დავამატოთ ტეგები წყაროს ტექსტში, გადააქციოთ ის XML კოდად, რომელიც შესაფერისია შემდგომი ანალიზისთვის FILTER.XML ფუნქციით.
თუ მაგალითად ავიღებთ პირველ მისამართს ჩვენი სიიდან, მაშინ დაგვჭირდება მისი გადაქცევა ამ კონსტრუქციაში:
მე დავურეკე გლობალური გახსნისა და დახურვის ყველა ტექსტური ტეგი tდა თითოეული ელემენტის ჩარჩოში ჩასმული ტეგები არის s., მაგრამ შეგიძლიათ გამოიყენოთ ნებისმიერი სხვა აღნიშვნა - არ აქვს მნიშვნელობა.
თუ ამ კოდიდან ამოვიღებთ შეწევას და ხაზების წყვეტებს - სრულიად, სხვათა შორის, სურვილისამებრ და დამატებულია მხოლოდ სიცხადისთვის, მაშინ ეს ყველაფერი გადაიქცევა ხაზად:
და მისი უკვე შედარებით მარტივად მიღება შესაძლებელია წყაროს მისამართიდან მასში მძიმეებით რამდენიმე ტეგით ჩანაცვლებით ფუნქციის გამოყენებით შემცვლელი (შემცვლელი) და სიმბოლოთი წებო & გახსნისა და დახურვის ტეგების დასაწყისში და ბოლოს:
შედეგად მიღებული დიაპაზონის ჰორიზონტალურად გასაფართოებლად, ჩვენ ვიყენებთ სტანდარტულ ფუნქციას ტრანსპ (ტრანსპოზირება)ჩვენი ფორმულის შეფუთვა მასში:
მთელი ამ დიზაინის მნიშვნელოვანი მახასიათებელია ის, რომ Office 2021-ისა და Office 365-ის ახალ ვერსიაში, დინამიური მასივების მხარდაჭერით, არ არის საჭირო სპეციალური ჟესტები შეყვანისთვის - უბრალოდ შეიყვანეთ და დააწკაპუნეთ შეიყვანეთ – ფორმულა თავისთავად იკავებს მისთვის საჭირო უჯრედების რაოდენობას და ყველაფერი მუშაობს ხმაურით. წინა ვერსიებში, სადაც ჯერ არ იყო დინამიური მასივები, ჯერ უნდა აირჩიოთ საკმარისი რაოდენობის ცარიელი უჯრედები ფორმულაში შესვლამდე (შეგიძლიათ ზღვრით), ხოლო ფორმულის შექმნის შემდეგ დააჭიროთ კლავიატურის მალსახმობს. Ctrl+გადაიტანოს+შეიყვანეთმასივის ფორმულის სახით შეყვანა.
მსგავსი ხრიკი შეიძლება გამოყენებულ იქნას ერთ უჯრედში ჩარჩენილი ტექსტის გამოყოფისას ხაზის გაწყვეტის გზით:
ერთადერთი განსხვავება წინა მაგალითთან არის ის, რომ მძიმის ნაცვლად, აქ ჩვენ ვცვლით უხილავ Alt + Enter ხაზის გაწყვეტის სიმბოლოს, რომელიც შეიძლება მითითებული იყოს ფორმულაში CHAR ფუნქციის გამოყენებით 10 კოდით.
- ხაზების წყვეტებთან მუშაობის დახვეწილობა (Alt + Enter) Excel-ში
- Excel-ში ტექსტის დაყოფა სვეტებად
- ტექსტის ჩანაცვლება SUBSTITUTE-ით