ორი სიის შერწყმა დუბლიკატების გარეშე

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

მოდით, ტრადიციულად განვიხილოთ ასეთი საერთო პრობლემის გადაჭრის რამდენიმე გზა - პრიმიტიული „შუბლზე“ დაწყებული უფრო რთული, მაგრამ ელეგანტური.

მეთოდი 1: წაშალეთ დუბლიკატები

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

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

მეთოდი 1a. საყრდენი მაგიდა

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

მოგეხსენებათ, კრებსითი ცხრილი იგნორირებას უკეთებს გამეორებას, ასე რომ, გამოსავალზე მივიღებთ კომბინირებულ სიას დუბლიკატების გარეშე. დამხმარე სვეტი 1-ით საჭიროა მხოლოდ იმიტომ, რომ Excel-ს შეუძლია შექმნას შემაჯამებელი ცხრილები, რომლებიც შეიცავს მინიმუმ ორ სვეტს.

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

მეთოდი 2: მასივის ფორმულა

პრობლემის გადაჭრა შეგიძლიათ ფორმულებით. ამ შემთხვევაში, შედეგების ხელახალი გაანგარიშება და განახლება მოხდება ავტომატურად და მყისიერად, თავდაპირველ სიებში ცვლილებებისთანავე. მოხერხებულობისთვის და მოკლედ, მოდით მივცეთ ჩვენი სიების სახელები. სია 1 и სია 2გამოყენების სახელის მენეჯერი tab formula (ფორმულები — სახელების მენეჯერი — შექმნა):

დასახელების შემდეგ, ჩვენ გვჭირდება ფორმულა ასე გამოიყურება:

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

ლოგიკა აქ არის შემდეგი:

• ფორმულა INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) ირჩევს ყველა უნიკალურ ელემენტს პირველი სიიდან. როგორც კი ამოიწურება, ის იწყებს #N/A შეცდომის გაცემას:

  

• ფორმულა INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) ამოიღებს უნიკალურ ელემენტებს მეორე სიიდან იმავე გზით.
• ერთმანეთში ჩასმული ორი IFERROR ფუნქცია ახორციელებს გამომავალს ჯერ სიიდან-1-ის უნიკალურიდან, შემდეგ კი სიიდან-2-დან ერთმანეთის მიყოლებით.

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

Excel-ის ინგლისურ ვერსიაში ეს ფორმულა ასე გამოიყურება:

=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($E$1:E1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($E$1:E1, List2), 0)) ), "") 

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

მეთოდი 3. Power Query

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

მოქმედებების ალგორითმი შემდეგია:

1. გახსენით დაინსტალირებული დანამატის ცალკე ჩანართი დენის მოთხოვნა (თუ გაქვთ Excel 2010-2013) ან უბრალოდ გადადით ჩანართზე თარიღი (თუ გაქვთ Excel 2016).
2. აირჩიეთ პირველი სია და დააჭირეთ ღილაკს ცხრილიდან / დიაპაზონიდან (დიაპაზონიდან/ცხრილიდან). როდესაც ჰკითხეს ჩვენი სიიდან „ჭკვიანი ცხრილის“ შექმნის შესახებ, ჩვენ ვეთანხმებით:

   

3. იხსნება შეკითხვის რედაქტორის ფანჯარა, სადაც შეგიძლიათ იხილოთ ჩატვირთული მონაცემები და მოთხოვნის სახელი მაგიდის 1 (თუ გსურთ, შეგიძლიათ შეცვალოთ იგი საკუთარზე).
4. ორჯერ დააწკაპუნეთ ცხრილის სათაურზე (word სია 1) და დაარქვით სხვას (მაგალითად ხალხი). კონკრეტულად რა დავასახელოთ არ არის მნიშვნელოვანი, მაგრამ გამოგონილი სახელი უნდა გვახსოვდეს, რადგან. მეორე ცხრილის იმპორტისას მოგვიანებით კვლავ უნდა იქნას გამოყენებული. მომავალში ორი ცხრილის შერწყმა იმუშავებს მხოლოდ იმ შემთხვევაში, თუ მათი სვეტების სათაურები ემთხვევა.
5. გააფართოვეთ ჩამოსაშლელი სია ზედა მარცხენა კუთხეში დახურეთ და გადმოწერეთ და აირჩიე დახურვა და ჩატვირთვა… (დახურვა და ჩატვირთვა…):

   

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

   

7. ვიმეორებთ მთელ პროცედურას (ქულები 2-6) მეორე სიისთვის. სვეტის სათაურის სახელის გადარქმევისას მნიშვნელოვანია გამოიყენოთ იგივე სახელი (People), როგორც წინა მოთხოვნაში.
8. Excel-ის ფანჯარაში ჩანართზე თარიღი ან ჩანართზე დენის მოთხოვნა არჩევა მიიღეთ მონაცემები - შეაერთეთ მოთხოვნები - დაამატეთ (მიიღეთ მონაცემები - შერწყმის მოთხოვნები - დამატება):

   

9. დიალოგურ ფანჯარაში, რომელიც გამოჩნდება, აირჩიეთ ჩვენი მოთხოვნები ჩამოსაშლელი სიებიდან:

   

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

    

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

    

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

• როგორ შევაგროვოთ რამდენიმე ცხრილი სხვადასხვა ფაილიდან Power Query-ის გამოყენებით
• უნიკალური ნივთების ამოღება სიიდან
• როგორ შევადაროთ ორი სია ერთმანეთს დამთხვევებისა და განსხვავებების შესახებ