როგორ შევადაროთ ორი სვეტი Excel-ში და წაშალოთ დუბლიკატები (ხაზგასმით, შეღებვით, გადაადგილებით)

ამ სტატიის წასაკითხად დაახლოებით 10 წუთი დაგჭირდებათ. მომდევნო 5 წუთში შეგიძლიათ მარტივად შეადაროთ ორი სვეტი Excel-ში და გაარკვიოთ არის თუ არა მათში დუბლიკატები, წაშალოთ ან მონიშნოთ ფერად. ასე რომ, დრო დადგა!

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

წარმოიდგინეთ, გვაქვს 2 სვეტი ხალხის სახელებით – 5 სახელი თითო სვეტში A და 3 სახელი სვეტში B. თქვენ უნდა შეადაროთ სახელები ამ ორ სვეტში და იპოვოთ დუბლიკატები. როგორც გესმით, ეს არის ფიქტიური მონაცემები, აღებული მხოლოდ მაგალითად. რეალურ ცხრილებში საქმე გვაქვს ათასობით ან თუნდაც ათიათასობით ჩანაწერთან.

ვარიანტი: ორივე სვეტი ერთსა და იმავე ფურცელზეა. მაგალითად, სვეტი A და სვეტი B.

ვარიანტი B: სვეტები სხვადასხვა ფურცლებზეა. მაგალითად, სვეტი A ფურცელზე Sheet2 და სვეტი A ფურცელზე Sheet3.

Excel 2013, 2010 და 2007 აქვს ჩაშენებული ინსტრუმენტი დუბლიკატების წაშლა (წაშალეთ დუბლიკატები), მაგრამ ის უძლურია ამ სიტუაციაში, რადგან არ შეუძლია მონაცემების შედარება 2 სვეტში. უფრო მეტიც, მას შეუძლია მხოლოდ დუბლიკატების წაშლა. არ არსებობს სხვა ვარიანტები, როგორიცაა ფერების ხაზგასმა ან შეცვლა. და მიუთითეთ!

შემდეგი, მე გაჩვენებთ Excel-ში ორი სვეტის შედარების შესაძლო გზებს, რაც საშუალებას მოგცემთ იპოვოთ და წაშალოთ დუბლიკატი ჩანაწერები.

• შეადარეთ 2 სვეტი და მოძებნეთ დუბლიკატები ფორმულების გამოყენებით
  
  • ვარიანტი A: ორივე სვეტი ერთსა და იმავე ფურცელზეა
  • ვარიანტი B: სვეტები განთავსებულია სხვადასხვა ფურცლებზე ან სხვადასხვა სამუშაო წიგნში
• ნაპოვნი დუბლიკატების დამუშავება
  
  • აჩვენეთ მხოლოდ დუბლიკატი რიგები A სვეტში
  • შეცვალეთ ფერი ან მონიშნეთ ნაპოვნი დუბლიკატები
  • წაშალეთ დუბლიკატები პირველი სვეტიდან

შეადარეთ 2 სვეტი Excel-ში და იპოვეთ დუბლიკატი ჩანაწერები ფორმულების გამოყენებით

ვარიანტი A: ორივე სვეტი ერთსა და იმავე ფურცელზეა

1. პირველ ცარიელ უჯრედში (ჩვენს მაგალითში ეს არის უჯრედი C1), ჩვენ ვწერთ შემდეგ ფორმულას:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   ჩვენს ფორმულაში A1 ეს არის პირველი სვეტის პირველი უჯრედი, რომლის შედარებასაც ვაპირებთ. 1$ B$ и 10000$ B$ ეს არის მეორე სვეტის პირველი და ბოლო უჯრედების მისამართები, რომლებთანაც შევადარებთ. ყურადღება მიაქციეთ აბსოლუტურ მითითებებს - სვეტის ასოებს და მწკრივების ნომრებს წინ უძღვის დოლარის ნიშანი ($). მე ვიყენებ აბსოლუტურ მითითებებს, რათა უჯრედების მისამართები იგივე დარჩეს ფორმულების კოპირებისას.

   თუ გსურთ იპოვოთ დუბლიკატები სვეტში Bშეცვალეთ მითითებები ისე, რომ ფორმულა ასე გამოიყურებოდეს:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

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

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

   

რჩევა: დიდ ცხრილებში ფორმულის კოპირება უფრო სწრაფი იქნება, თუ იყენებთ კლავიატურის მალსახმობებს. მონიშნეთ უჯრედი C1 და დააჭირეთ Ctrl + C (ფორმულის ბუფერში დასაკოპირებლად), შემდეგ დააწკაპუნეთ Ctrl + Shift + End (C სვეტის ყველა არა ცარიელი უჯრედის შესარჩევად) და ბოლოს დააჭირეთ Ctrl + V (ფორმულის ჩასმა ყველა არჩეულ უჯრედში).

3. შესანიშნავია, ახლა ყველა დუბლიკატი მნიშვნელობა აღინიშნება როგორც "დუბლიკატი"

ვარიანტი B: ორი სვეტი სხვადასხვა ფურცელზეა (სხვადასხვა სამუშაო წიგნებში)

1. სამუშაო ფურცლის პირველი ცარიელი სვეტის პირველ უჯრედში Sheet2 (ჩვენს შემთხვევაში ეს არის სვეტი B) შეიყვანეთ შემდეგი ფორმულა:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   აქ Sheet3 არის ფურცლის სახელი, რომელზეც მე-2 სვეტი მდებარეობს და $A$1:$A$10000 არის უჯრედის მისამართები 1-დან ბოლომდე ამ მე-2 სვეტში.

2. დააკოპირეთ ფორმულა სვეტის ყველა უჯრედში B (იგივე ვარიანტი A).
3. ჩვენ ვიღებთ ამ შედეგს:

ნაპოვნი დუბლიკატების დამუშავება

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

აჩვენეთ მხოლოდ დუბლიკატი რიგები A სვეტში

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

დააწკაპუნეთ მარჯვენა ღილაკით და აირჩიეთ კონტექსტური მენიუდან Insertion (ჩასმა):

მიეცით სახელები სვეტებს, მაგალითად, ”სახელი"და"დუბლიკატი?» შემდეგ გახსენით ჩანართი თარიღი (მონაცემები) და დააჭირეთ ფილტრი (ფილტრი):

ამის შემდეგ დააწკაპუნეთ პატარა ნაცრისფერ ისარზე "დუბლიკატი?« ფილტრის მენიუს გასახსნელად; მოხსენით ყველა ელემენტი ამ სიაში გარდა დუბლიკატიდა პრესა OK.

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

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

შეცვალეთ ფერი ან მონიშნეთ ნაპოვნი დუბლიკატები

თუ შენიშვნები "დუბლიკატი” არ არის საკმარისი თქვენი მიზნებისთვის და გსურთ მონიშნოთ დუბლიკატი უჯრედები შრიფტის სხვა ფერით, შევსების ფერით ან სხვა მეთოდით…

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

ახლა თქვენ ნამდვილად არ გამოტოვებთ დუბლიკატების მქონე უჯრედებს:

დუბლიკატი მნიშვნელობების ამოღება პირველი სვეტიდან

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

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

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

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

1. გაფილტრეთ ცხრილი მხოლოდ დუბლიკატი მნიშვნელობების საჩვენებლად და აირჩიეთ ეს უჯრედები. დააწკაპუნეთ მათზე მარჯვენა ღილაკით და აირჩიეთ კონტექსტური მენიუდან შინაარსის გასუფთავება (გარკვეული შინაარსი).
2. გაასუფთავეთ ფილტრი.
3. აირჩიეთ ყველა უჯრედი სვეტში А, დაწყებული საკნიდან A1 ყველა გზა ქვემოთ ბოლოში შეიცავს მონაცემებს.
4. დააჭირეთ თარიღი (მონაცემები) და დააჭირეთ დახარისხება A-დან Z-მდე (დახარისხება A-დან Z-მდე). დიალოგურ ფანჯარაში, რომელიც იხსნება, აირჩიეთ განაგრძეთ მიმდინარე არჩევანი (დაალაგეთ მითითებული არჩევანის ფარგლებში) და დააჭირეთ ღილაკს შავი (დახარისხება):
5. წაშალეთ სვეტი ფორმულით, ის აღარ დაგჭირდებათ, ამიერიდან მხოლოდ უნიკალური მნიშვნელობები გაქვთ.
6. ეს არის ის, ახლა სვეტი А შეიცავს მხოლოდ უნიკალურ მონაცემებს, რომლებიც არ არის სვეტში В:

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