ნაყარი ტექსტის შეცვლა ფორმულებით

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

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

ახლა წარმოიდგინეთ, რომ ასეთი ცრუ მონაცემები თქვენთან რეგულარულად მოდის, ანუ ეს არ არის ერთჯერადი ამბავი „ხელით გაასწორე, დაივიწყე“, არამედ პრობლემა რეგულარულად და უჯრედების დიდ რაოდენობაში.

Რა უნდა ვქნა? ხელით არ შეცვალოთ მრუდი ტექსტი 100500-ჯერ სწორი ტექსტით „ძებნა და ჩანაცვლება“ ველის მეშვეობით ან დაწკაპუნებით Ctrl+H?

პირველი, რაც მახსენდება ასეთ სიტუაციაში, არის მასობრივი ჩანაცვლება წინასწარ შედგენილი არასწორი და სწორი ვარიანტების შესატყვისი საცნობარო წიგნის მიხედვით - ასე:

სამწუხაროდ, ასეთი ამოცანის აშკარა გავრცელებით, Microsoft Excel-ს არ აქვს მისი გადაჭრის მარტივი ჩაშენებული მეთოდები. დასაწყისისთვის, მოდით გაერკვნენ, თუ როგორ უნდა გავაკეთოთ ეს ფორმულებით, „მძიმე არტილერიის“ ჩართვის გარეშე მაკროების სახით VBA-ში ან Power Query-ში.

შემთხვევა 1. ნაყარი სრული ჩანაცვლება

დავიწყოთ შედარებით მარტივი შემთხვევით – სიტუაციით, როდესაც თქვენ უნდა შეცვალოთ ძველი დამახინჯებული ტექსტი ახლით. სრულად.

ვთქვათ, გვაქვს ორი ცხრილი:

პირველში – კომპანიების ორიგინალური ჭრელი სახელები. მეორეში – მიმოწერის ცნობარი. თუ პირველ ცხრილში კომპანიის სახელში ვიპოვით რომელიმე სიტყვას სვეტიდან Პოვნა, მაშინ თქვენ უნდა მთლიანად შეცვალოთ ეს მრუდე სახელი სწორით - სვეტიდან შემცვლელი მეორე საძიებო ცხრილი.

მოხერხებულობისთვის:

• ორივე ცხრილი გარდაიქმნება დინამიურად ("ჭკვიან") კლავიატურის მალსახმობის გამოყენებით Ctrl+T ან გუნდი ჩასმა – ცხრილი (ჩასმა - ცხრილი).
  
• ჩანართზე, რომელიც გამოჩნდება კონსტრუქტორი (დიზაინი) პირველი ცხრილი დაასახელა თარიღიდა მეორე საცნობარო ცხრილი – Substitutions.

ფორმულის ლოგიკის ასახსნელად ცოტა შორიდან წავიდეთ.

მაგალითისთვის ავიღოთ პირველი კომპანია A2 უჯრედიდან და დროებით დავივიწყოთ დანარჩენი კომპანიები, შევეცადოთ განვსაზღვროთ რომელი ვარიანტი სვეტიდან Პოვნა იქ ხვდება. ამისათვის აირჩიეთ ნებისმიერი ცარიელი უჯრედი ფურცლის თავისუფალ ნაწილში და შეიყვანეთ ფუნქცია იქ ᲞᲝᲕᲜᲐ (იპოვე):

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

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

თუ თქვენ გაქვთ Excel-ის წინა ვერსიები, შემდეგ დააჭირეთ მასზე შეიყვანეთ ჩვენ ვნახავთ მხოლოდ პირველ მნიშვნელობას შედეგების მასივიდან, ანუ შეცდომა #VALUE! (#VALUE!).

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

შედეგად მიღებული მასივი ნიშნავს, რომ ორიგინალური ცრუ კომპანიის სახელია (GK Morozko OAO) ყველა მნიშვნელობის სვეტში Პოვნა იპოვა მხოლოდ მეორე (მოროზკო), და დაწყებული ზედიზედ მე-4 სიმბოლოდან.

ახლა მოდით დავამატოთ ფუნქცია ჩვენს ფორმულას VIEW(ᲛᲝᲫᲔᲑᲜᲐ, ᲐᲘᲮᲔᲓᲔ ᲖᲔᲛᲝᲗ):

ამ ფუნქციას აქვს სამი არგუმენტი:

1. სასურველი ღირებულება - შეგიძლიათ გამოიყენოთ ნებისმიერი საკმარისად დიდი რიცხვი (მთავარია, რომ ის აღემატებოდეს ნებისმიერი ტექსტის სიგრძეს წყაროს მონაცემებში)
   
2. ნანახი_ვექტორი – დიაპაზონი ან მასივი, სადაც ჩვენ ვეძებთ სასურველ მნიშვნელობას. აქ არის ადრე დანერგილი ფუნქცია ᲞᲝᲕᲜᲐ, რომელიც აბრუნებს მასივს {#VALUE!:4:#VALUE!}
   
3. ვექტორი_შედეგი – დიაპაზონი, საიდანაც გვინდა დავაბრუნოთ მნიშვნელობა, თუ სასურველი მნიშვნელობა ნაპოვნია შესაბამის უჯრედში. აქ არის სწორი სახელები სვეტიდან შემცვლელი ჩვენი საცნობარო ცხრილი.
   

აქ მთავარი და არააშკარა ფუნქცია არის ის, რომ ფუნქცია VIEW თუ ზუსტი დამთხვევა არ არის, ყოველთვის ეძებს უახლოეს უმცირეს (წინა) მნიშვნელობას. ამიტომ, ნებისმიერი სოლიდური რიცხვის (მაგალითად, 9999) სასურველ მნიშვნელობად მითითებით, ჩვენ ვაიძულებთ VIEW იპოვეთ უჯრედი უახლოესი უმცირესი რიცხვით (4) მასივში {#VALUE!:4:#VALUE!} და დააბრუნეთ შესაბამისი მნიშვნელობა შედეგის ვექტორიდან, ანუ სწორი კომპანიის სახელი სვეტიდან. შემცვლელი.

მეორე ნიუანსი არის ის, რომ ტექნიკურად ჩვენი ფორმულა არის მასივის ფორმულა, რადგან ფუნქცია ᲞᲝᲕᲜᲐ შედეგებად აბრუნებს არა ერთს, არამედ სამი მნიშვნელობის მასივს. მაგრამ რადგან ფუნქცია VIEW მხარს უჭერს მასივებს გარეთ, მაშინ ჩვენ არ მოგვიწევს შევიტანოთ ეს ფორმულა, როგორც კლასიკური მასივის ფორმულა - კლავიატურის მალსახმობის გამოყენებით Ctrl+გადაიტანოს+შეიყვანეთ. მარტივი იქნება საკმარისი შეიყვანეთ.

Სულ ეს არის. იმედია მიხვდები ლოგიკას.

რჩება მზა ფორმულის გადატანა სვეტის პირველ უჯრედში B2 ფიქსირებული - და ჩვენი ამოცანა მოგვარებულია!

რა თქმა უნდა, ჩვეულებრივი (არა ჭკვიანი) მაგიდებით, ეს ფორმულა ასევე მშვენივრად მუშაობს (უბრალოდ არ დაივიწყოთ გასაღები F4 და შესაბამისი ლინკების დაფიქსირება):

საქმე 2. ნაყარი ნაწილობრივი ჩანაცვლება

ეს საქმე ცოტა უფრო რთულია. ისევ გვაქვს ორი "ჭკვიანი" ცხრილი:

პირველი ცხრილი არასწორად დაწერილი მისამართებით, რომელიც უნდა გამოსწორდეს (დავარქვი მონაცემები 2). მეორე ცხრილი არის საცნობარო წიგნი, რომლის მიხედვითაც თქვენ უნდა განახორციელოთ ქვესტრიქონის ნაწილობრივი ჩანაცვლება მისამართის შიგნით (მე დავარქვი ამ ცხრილს ჩანაცვლებები 2).

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

დასრულებული ფორმულა ასე გამოიყურება (აღქმის გასაადვილებლად, მე დავყავი ის რამდენ ხაზად გამოიყენება Alt+შეიყვანეთ):

აქ ძირითადი სამუშაო შესრულებულია სტანდარტული Excel ტექსტური ფუნქციით შემცვლელი (შემცვლელი), რომელსაც აქვს 3 არგუმენტი:

1. საწყის ტექსტი – პირველი მრუდე მისამართი მისამართი სვეტიდან
2. რასაც ჩვენ ვეძებთ - აქ ვიყენებთ ხრიკს ფუნქციით VIEW (ᲛᲝᲫᲔᲑᲜᲐ, ᲐᲘᲮᲔᲓᲔ ᲖᲔᲛᲝᲗ)სვეტიდან მნიშვნელობის ამოსაღებად წინა გზიდან Პოვნა, რომელიც ჩართულია ფრაგმენტის სახით მოსახვევ მისამართზე.
3. რითი შევცვალოთ - ანალოგიურად ვპოულობთ მის შესაბამის მნიშვნელობას სვეტიდან შემცვლელი.

შეიყვანეთ ეს ფორმულა Ctrl+გადაიტანოს+შეიყვანეთ აქაც არ არის საჭირო, თუმცა, ფაქტობრივად, მასივის ფორმულაა.

და აშკარად ჩანს (იხილეთ # N/A შეცდომები წინა სურათზე) რომ ასეთ ფორმულას, მთელი თავისი ელეგანტურობის მიუხედავად, აქვს რამდენიმე ნაკლი:

• ფუნქცია SUBSTITUTE არის რეგისტრის მგრძნობიარეასე რომ, "Spb" ბოლო სტრიქონში არ მოიძებნა ჩანაცვლების ცხრილში. ამ პრობლემის გადასაჭრელად, შეგიძლიათ გამოიყენოთ ფუნქცია ზამენიტი (ჩანაცვლება), ან წინასწარ მიიტანეთ ორივე ცხრილი იმავე რეესტრში.
• თუ ტექსტი თავდაპირველად სწორია ან მასში შესაცვლელი ფრაგმენტი არ არის (ბოლო ხაზი), შემდეგ ჩვენი ფორმულა უშვებს შეცდომას. ამ მომენტის განეიტრალება შესაძლებელია ფუნქციის გამოყენებით შეცდომების ჩარევით და ჩანაცვლებით Iferror (IFERROR):

  

• თუ ორიგინალი ტექსტი შეიცავს რამდენიმე ფრაგმენტი დირექტორიადან ერთდროულად, მაშინ ჩვენი ფორმულა ცვლის მხოლოდ ბოლოს (მე-8 სტრიქონში, ლიგოვსკი «გამზირზე« შეიცვალა "pr-t", მაგრამ "S-Pb" on „წმ. პეტერბურგი" აღარ, იმიტომ „S-Pb” უფრო მაღალია დირექტორიაში). ამ პრობლემის მოგვარება შესაძლებელია ჩვენი ფორმულის ხელახლა გაშვებით, მაგრამ უკვე სვეტის გასწვრივ ფიქსირებული:

  
  

ადგილებზე არ არის სრულყოფილი და შრომატევადი, მაგრამ ბევრად უკეთესია, ვიდრე იგივე ხელით გამოცვლა, არა? 🙂

PS

შემდეგ სტატიაში ჩვენ გავარკვევთ, თუ როგორ უნდა განვახორციელოთ ასეთი ნაყარი ჩანაცვლება მაკროებისა და Power Query-ის გამოყენებით.

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