ტექსტის დაკავშირება პირობების მიხედვით

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

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

სხვა სიტყვებით რომ ვთქვათ, ჩვენ გვჭირდება ხელსაწყო, რომელიც დააწებება (დააკავშირებს) ტექსტს მდგომარეობის მიხედვით - ფუნქციის ანალოგი. SUMMESLI (SUMIF), მაგრამ ტექსტისთვის.

მეთოდი 0. ფორმულა

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

ამ მიდგომის ნაკლოვანებები აშკარაა: მიღებული დამატებითი სვეტის ყველა უჯრედიდან ჩვენ გვჭირდება მხოლოდ ბოლოები თითოეული კომპანიისთვის (ყვითელი). თუ სია დიდია, მაშინ მათი სწრაფად შესარჩევად, ფუნქციის გამოყენებით მოგიწევთ კიდევ ერთი სვეტის დამატება DLSTR (LEN), შეამოწმეთ დაგროვილი სტრიქონების სიგრძე:

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

მეთოდი 1. წებოვნების მაკროფუნქცია ერთი პირობით

თუ ორიგინალური სია არ არის დალაგებული კომპანიის მიხედვით, მაშინ ზემოთ მოყვანილი მარტივი ფორმულა არ მუშაობს, მაგრამ თქვენ შეგიძლიათ მარტივად გადახვიდეთ VBA-ში მცირე მორგებული ფუნქციით. გახსენით Visual Basic რედაქტორი კლავიატურის მალსახმობის დაჭერით Alt + F11 ან ღილაკის გამოყენებით Visual Basic tab დეველოპერი (დეველოპერი). ფანჯარაში, რომელიც იხსნება, მენიუში ჩადეთ ახალი ცარიელი მოდული ჩასმა – მოდული და დააკოპირეთ ჩვენი ფუნქციის ტექსტი იქ:

ფუნქცია MergeIf(TextRange როგორც დიაპაზონი, SearchRange როგორც დიაპაზონი, Condition As String) Dim Delimeter როგორც String, i As Long Delimeter = ", " gluings არ არის ერთმანეთის ტოლი - გამოვდივართ შეცდომით, თუ SearchRange.Count <> TextRange.Count შემდეგ MergeIf = CVERr(xlErrRef) გასვლის ფუნქცია დასრულება თუ 'გაიარე ყველა უჯრედი, შეამოწმე მდგომარეობა და შეაგროვე ტექსტი ცვლადში OutText For i = 1 To Search Range. Cells.Count If SearchRange.Cells(i) Like Condition then OutText = OutText & TextRange.Cells(i) & Delimeter შემდეგი მე 'აჩვენე შედეგები ბოლო დელიმიტერის გარეშე MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) დასასრული ფუნქცია  

თუ ახლა დაბრუნდებით Microsoft Excel-ში, მაშინ ფუნქციების სიაში (ღილაკი fx ფორმულების ზოლში ან ჩანართში ფორმულები – ჩასმა ფუნქცია) შესაძლებელი იქნება ჩვენი ფუნქციის პოვნა MergeIf კატეგორიაში მომხმარებლის განსაზღვრა (მომხმარებლის განსაზღვრული). ფუნქციის არგუმენტები შემდეგია:

მეთოდი 2. ტექსტის შეერთება არაზუსტი პირობით

თუ ჩვენ შევცვლით პირველ სიმბოლოს ჩვენი მაკროს მე-13 სტრიქონში = სავარაუდო მატჩის ოპერატორს Like, მაშინ შესაძლებელი იქნება შერჩევის კრიტერიუმთან საწყისი მონაცემების არაზუსტი შეხამებით წებოვნების განხორციელება. მაგალითად, თუ კომპანიის სახელი შეიძლება დაიწეროს სხვადასხვა ვარიანტში, მაშინ ჩვენ შეგვიძლია შევამოწმოთ და შევაგროვოთ ისინი ერთი ფუნქციით:

სტანდარტული ბუნების ბარათები მხარდაჭერილია:

• ვარსკვლავი (*) - აღნიშნავს ნებისმიერი სიმბოლოს ნებისმიერ რაოდენობას (მათ შორის არარსებობას)
• კითხვის ნიშანი (?) - ნიშნავს რომელიმე სიმბოლოს
• ფუნტის ნიშანი (#) - წარმოადგენს ნებისმიერ ერთ ციფრს (0-9)

ნაგულისხმევად, Like ოპერატორი არის რეგისტრის სენსიტიური, ანუ ესმის, მაგალითად, "Orion" და "orion", როგორც სხვადასხვა კომპანიები. ქეისის უგულებელყოფისთვის, შეგიძლიათ დაამატოთ ხაზი მოდულის დასაწყისში Visual Basic რედაქტორში ვარიანტი ტექსტის შედარება, რომელიც გადართავს Like-ს, რომ იყოს რეგისტრირებული.

ამ გზით, თქვენ შეგიძლიათ შეადგინოთ ძალიან რთული ნიღბები პირობების შესამოწმებლად, მაგალითად:

• ?1##??777RUS – 777 რეგიონის ყველა სანომრე ნიშნის შერჩევა 1-დან დაწყებული
• შპს* – ყველა კომპანია, რომლის სახელი იწყება შპს-ით
• ##7## – ყველა პროდუქტი ხუთნიშნა ციფრული კოდით, სადაც მესამე ციფრი არის 7
• ????? - ხუთი ასოს ყველა სახელი და ა.შ.
  

მეთოდი 3. მაკრო ფუნქცია ორი პირობით ტექსტის წებოვნებისთვის

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

ფუნქცია MergeIfs (TextRange როგორც დიაპაზონი, SearchRange1 როგორც დიაპაზონი, Condition1 როგორც სტრიქონი, Search Range2 როგორც დიაპაზონი, Condition2 როგორც სტრიქონი) Dim Delimeter როგორც string, i As Long Delimeter = ", " 'გამყოფი სიმბოლოები (შეიძლება შეიცვალოს ინტერვალით ან ; ა.შ.) ე.) 'თუ ვალიდაციისა და წებოვნების დიაპაზონი არ არის ერთმანეთის ტოლი, გამოდით შეცდომით, თუ SearchRange1.Count <> TextRange.Count ან SearchRange2.Count <> TextRange.Count შემდეგ MergeIfs = CVERr(xlErrRef) გასვლა ფუნქციიდან დასრულება თუ 'გაიარე ყველა უჯრედი, შეამოწმე ყველა პირობა და შეაგროვე ტექსტი ცვლადში OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 შემდეგ OutText = OutText & TextRange.Cells(i) & Delimeter End If Next მე 'აჩვენებს შედეგებს ბოლო გამიჯვნის გარეშე MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) ბოლო ფუნქცია  

იგი გამოყენებული იქნება ზუსტად იმავე გზით - ახლა მხოლოდ არგუმენტებია საჭირო მეტი დაზუსტება:

მეთოდი 4. დაჯგუფება და წებოვნება Power Query-ში

პრობლემის გადაჭრა შეგიძლიათ VBA-ში პროგრამირების გარეშე, თუ იყენებთ უფასო Power Query დანამატს. Excel 2010-2013-ისთვის მისი ჩამოტვირთვა შესაძლებელია აქ, ხოლო Excel 2016-ში ის უკვე ჩაშენებულია ნაგულისხმევად. მოქმედებების თანმიმდევრობა იქნება შემდეგი:

Power Query-მ არ იცის როგორ იმუშაოს ჩვეულებრივ ცხრილებთან, ამიტომ პირველი ნაბიჯი არის ჩვენი ცხრილის „ჭკვიანად“ გადაქცევა. ამისათვის აირჩიეთ და დააჭირეთ კომბინაციას Ctrl+T ან აირჩიეთ ჩანართიდან მთავარი – ფორმატირება როგორც ცხრილი (მთავარი - ფორმატირება როგორც ცხრილი). ჩანართზე, რომელიც შემდეგ გამოჩნდება კონსტრუქტორი (დიზაინი) შეგიძლიათ დააყენოთ ცხრილის სახელი (მე დავტოვე სტანდარტი მაგიდის 1):

ახლა მოდით ჩავტვირთოთ ჩვენი ცხრილი Power Query-ის დანამატში. ამისათვის, ჩანართზე თარიღი (თუ გაქვთ Excel 2016) ან Power Query ჩანართზე (თუ გაქვთ Excel 2010-2013) დააწკაპუნეთ მაგიდიდან (მონაცემები - ცხრილიდან):

შეკითხვის რედაქტორის ფანჯარაში, რომელიც იხსნება, აირჩიეთ სვეტი სათაურზე დაწკაპუნებით კომპანია და დააჭირეთ ზემოთ მოცემულ ღილაკს Group (Ით დაჯგუფება). შეიყვანეთ ახალი სვეტის სახელი და ოპერაციის ტიპი ჯგუფში – ყველა ხაზი (ყველა მწკრივი):

დააწკაპუნეთ OK და ჩვენ ვიღებთ დაჯგუფებული მნიშვნელობების მინი ცხრილს თითოეული კომპანიისთვის. ცხრილების შიგთავსი აშკარად ჩანს, თუ დააწკაპუნეთ მაუსის მარცხენა ღილაკით უჯრედების თეთრ ფონზე (არა ტექსტზე!) მიღებულ სვეტში:

ახლა დავამატოთ კიდევ ერთი სვეტი, სადაც ფუნქციის გამოყენებით ვაწებებთ Address სვეტების შიგთავსს თითოეულ მინი-ცხრილში მძიმეებით გამოყოფილი. ამისათვის, ჩანართზე სვეტის დამატება ვაჭერთ მორგებული სვეტი (სვეტის დამატება — მორგებული სვეტი) და ფანჯარაში, რომელიც გამოჩნდება, შეიყვანეთ ახალი სვეტის სახელი და დაწყვილების ფორმულა Power Query-ში ჩაშენებულ M ენაზე:

გაითვალისწინეთ, რომ ყველა M-ფუნქცია რეგისტრირებულია (განსხვავებით Excel-ისგან). დაწკაპუნების შემდეგ OK ჩვენ ვიღებთ ახალ სვეტს წებოვანი მისამართებით:

რჩება ისედაც არასაჭირო სვეტის ამოღება მაგიდის მისამართები (სათაურზე დააწკაპუნეთ მარჯვენა ღილაკით) სვეტის წაშლა) და ატვირთეთ შედეგები ფურცელზე ჩანართზე დაწკაპუნებით მთავარი — დახურეთ და გადმოწერეთ (მთავარი - დახურვა და ჩატვირთვა):

მნიშვნელოვანი ნიუანსი: წინა მეთოდებისგან (ფუნქციებისგან) განსხვავებით, Power Query-ის ცხრილები ავტომატურად არ განახლდება. თუ მომავალში იქნება რაიმე ცვლილება წყაროს მონაცემებში, მაშინ მოგიწევთ მარჯვენა ღილაკით დააწკაპუნოთ შედეგების ცხრილის ნებისმიერ წერტილში და აირჩიოთ ბრძანება განახლება და შენახვა (განახლება).

• როგორ დავყოთ გრძელი ტექსტის სტრიქონი ნაწილებად
• სხვადასხვა უჯრედებიდან ტექსტის ერთში წებოვნების რამდენიმე გზა
• Like ოპერატორის გამოყენება ნიღბის წინააღმდეგ ტექსტის შესამოწმებლად