خواندن این مقاله حدود 10 دقیقه طول می‌کشد و در 5 دقیقه آینده (یا حتی سریعتر اگر راه‌حل دوم توضیح داده شده در مقاله را انتخاب کنید) به راحتی دو ستون اکسل را برای موارد تکراری مقایسه می‌کنید و موارد تکراری را حذف یا هایلایت می‌کنید.
اکسل یک برنامه بسیار قدرتمند و واقعاً جالب برای ایجاد و پردازش آرایه های بزرگ داده است. اکنون که تعداد زیادی کتاب کار با مجموعه ای از داده ها، یا شاید فقط یک جدول بزرگ دارید، ممکن است بخواهید 2 ستون را برای موارد تکراری مقایسه کنید و سپس کاری را با ورودی های یافت شده انجام دهید، به عنوان مثال ردیف های تکراری را حذف کنید، رنگ های تقلبی را حذف کنید یا محتویات را پاک کنید. سلول های تکراری این دو ستون ممکن است به صورت پیوسته یا غیر پیوسته در یک جدول قرار گیرند و یا ممکن است در 2 کاربرگ یا حتی کتاب کار متفاوت قرار گیرند.
مثلاً 2 ستون با نام افراد دارید – 5 نام در ستون A و 3 نام در ستون B، و می خواهید داده ها را بین این دو ستون مقایسه کنید تا موارد تکراری پیدا کنید. همانطور که می دانید، این داده ها فقط برای یک مثال سریع، جعلی است. در کاربرگ های واقعی معمولاً هزاران و ده ها هزار ورودی دارید.
نوع A : هر دو ستون در یک صفحه، در یک جدول واحد قرار دارند: ستون A و ستون B
نوع B : دو ستون در صفحات مختلف قرار دارند: ستون A در Sheet2 و ستون A در Sheet3
ابزار داخلی Remove Duplicate موجود در Excel 2016، Excel 2013 و 2010 نمی تواند این سناریو را مدیریت کند زیرا نمی تواند داده ها را بین 2 ستون مقایسه کند. علاوه بر این، فقط می تواند فریب ها را حذف کند، هیچ گزینه دیگری مانند برجسته سازی یا رنگ آمیزی در دسترس نیست.
در ادامه، من قصد دارم 2 روش ممکن برای مقایسه دو ستون اکسل را توضیح دهم که به شما امکان می دهد ورودی های تکراری را پیدا و حذف کنید:
برای یافتن موارد تکراری با استفاده از فرمول های اکسل، 2 ستون را مقایسه کنید
نوع A: هر دو ستون در یک لیست قرار دارند
- در اولین سلول خالی، در مثال ما این سلول C1 است، فرمول زیر را بنویسید:
=IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")
- در فرمول ما، A1 اولین خانه از ستون اول است که می خواهیم برای مقایسه از آن استفاده کنیم. 1$ B$ و 10000$B$ آدرسهای اولین و آخرین سلول ستون 2 هستند که می خواهید با آنها مقایسه کنید. به مرجع سلول مطلق – علائم دلار ($) قبل از حروف ستون و اعداد ردیف توجه کنید. من عمداً از مرجع مطلق استفاده می کنم تا آدرس سلول ها در هنگام کپی کردن فرمول بدون تغییر باقی بمانند.اگر می‌خواهید موارد فریبنده را در ستون B پیدا کنید، نام ستون‌ها را عوض کنید تا فرمول به شکل زیر باشد:
=IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")به جای ” Unique “/” Duplicate ” می توانید برچسب های خود را بنویسید، به عنوان مثال ” یافت نشد “/” Found “، یا فقط ” Duplicate ” را بگذارید و به جای “Unique ” تایپ کنید. در مورد دوم، شما سلول های خالی در کنار سلول هایی خواهید داشت که موارد تکراری برای آنها یافت نشد، من معتقدم چنین ارائه ای برای تجزیه و تحلیل داده ها راحت تر است. - حالا بیایید فرمول را در تمام سلول‌های ستون C ، تا آخرین ردیفی که حاوی داده‌های ستون A است، کپی کنیم. برای این کار، مکان‌نما را در گوشه سمت راست پایین سلول C1 قرار دهید و مکان‌نما به یک ضربدر سیاه تغییر می‌کند.
- همانطور که در تصویر زیر نشان داده شده است:
- روی دکمه سمت چپ ماوس کلیک کنید و آن را پایین نگه دارید، حاشیه را به سمت پایین بکشید و تمام سلول هایی را که می خواهید فرمول را کپی کنید انتخاب کنید. هنگامی که تمام سلول های مورد نیاز انتخاب شدند، دکمه سمت چپ ماوس را رها کنید:
- نکته: در جداول بزرگ، کپی کردن فرمول با استفاده از میانبرها سریعتر است. برای انتخاب سلول C1 روی آن کلیک کرده و فشار دهیدCtrl + C(برای کپی فرمول در کلیپ بورد)، سپس فشار دهیدCtrl + Shift + End(برای انتخاب تمام سلول های غیر خالی در ستون C)، و در نهایت ضربه بزنیدCtrl + V(برای چسباندن فرمول در تمام سلول های انتخاب شده).
- عالی است، همه سلول های تکراری به عنوان “تکراری” پرچم گذاری شده اند:
نوع B: دو ستون در کاربرگ های مختلف (کتاب های کاری) قرار دارند.
- در سلول 1 از ستون خالی 1 در Sheet2 (ستون B در مورد ما)، فرمول را بنویسید:
=IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")جایی که Sheet3 نام برگه‌ای است که ستون دوم در آن قرار دارد و $1:$A$10000 آدرس خانه‌های اول و آخر آن ستون دوم است. - مشابه نوع A.
کار با موارد تکراری یافت شده
عالی، ما ورودی های ستون اول (ستون A) را پیدا کرده ایم که در ستون دوم (ستون B) نیز وجود دارد. حالا باید یه کاری باهاشون بکنیم 🙂
نگاهی به کل جدول و بررسی دستی ورودی های تکراری نسبتاً بی اثر خواهد بود و زمان زیادی می برد. راه های خیلی بهتری هم هست
فقط ردیف های تکراری در ستون A نمایش داده شود
اگر ستون های شما هدر ندارند، باید آنها را اضافه کنید. برای انجام این کار، مکان نما را روی عدد نشان دهنده ردیف اول قرار دهید و همانطور که در تصویر نشان داده شده است به یک فلش سیاه تبدیل می شود:
روی ردیف انتخاب شده کلیک راست کرده و از منوی زمینه ” Insert ” را انتخاب کنید:
نام هایی را به ستون های خود بدهید، به عنوان مثال ” نام ” و ” تکراری؟ “. سپس به تب Data بروید و روی Filter کلیک کنید :
پس از آن روی یک فلش خاکستری کوچک در کنار ” تکراری؟ ” کلیک کنید تا یک لیست کشویی باز شود، علامت همه موارد غیر از تکراری در آن لیست را بردارید و روی OK کلیک کنید :
تمام است، اکنون فقط آن سلول‌های ستون A را می‌بینید که مقادیر تکراری در ستون B دارند. فقط سه سلول از این قبیل در کاربرگ آزمایشی ما وجود دارد، همانطور که می‌دانید در برگه‌های واقعی به احتمال زیاد تعداد آنها بسیار زیاد است:
برای نمایش دوباره همه ردیف‌های ستون A، روی نماد فیلتر در ستون B که اکنون شبیه یک قیف با یک فلش کوچک است، کلیک کنید 
و «انتخاب همه» را علامت بزنید. همچنین می‌توانید همین کار را از طریق تب Data -> Select & Filter -> Clear انجام دهید ، همانطور که در تصویر نشان داده شده است:
موارد تکراری یافت شده را رنگ یا برجسته کنید
اگر پرچم ” تکراری ” برای اهداف شما کافی نیست و می خواهید سلول های تکراری را با رنگ فونت یا رنگ پر یا به روش دیگری علامت گذاری کنید…
سپس موارد تکراری را همانطور که در بالا توضیح داده شد فیلتر کنید، تمام سلول های فیلتر شده را انتخاب کرده و فشار دهیدCtrl + F1برای باز کردن کادر محاوره ای Format Cells . به عنوان مثال، اجازه دهید رنگ پس‌زمینه ردیف‌های تکراری را به زرد روشن تغییر دهیم. البته، می‌توانید رنگ پس‌زمینه سلول‌ها را با استفاده از گزینه Fill color در تب Home تغییر دهید ، اما مزیت کادر محاوره‌ای Format Cells این است که به شما امکان می‌دهد همه تغییرات قالب‌بندی را در یک زمان انجام دهید:
اکنون قطعاً یک سلول تکراری را از دست نخواهید داد:
موارد تکراری را از ستون اول حذف کنید
جدول خود را فیلتر کنید تا فقط سلول هایی با مقادیر تکراری نشان داده شوند و همه آن سلول ها را انتخاب کنید.
اگر 2 ستونی که با هم مقایسه می کنید در کاربرگ های مختلف قرار دارند ، یعنی در جداول جداگانه، روی محدوده انتخاب شده کلیک راست کرده و ” حذف ردیف ” را از منوی زمینه انتخاب کنید:
زمانی که اکسل از شما می‌خواهد تأیید کنید که می‌خواهید «حذف کل ردیف برگه» را انجام دهید، روی OK کلیک کنید و سپس فیلتر را پاک کنید. همانطور که می بینید، فقط ردیف هایی با مقادیر منحصر به فرد باقی می مانند:
اگر 2 ستون در یک کاربرگ ، در کنار یکدیگر (در مجاورت) قرار گیرند یا با یکدیگر تماس نداشته باشند (غیر مجاور)، حذف موارد تکراری کمی پیچیده تر است. ما نمی‌توانیم کل ردیف‌هایی را که حاوی مقادیر تکراری هستند حذف کنیم زیرا این کار سلول‌های مربوطه را در ستون دوم نیز حذف می‌کند. بنابراین، برای اینکه فقط ورودی های منحصر به فرد در ستون A باقی بمانید، موارد زیر را انجام دهید:
- جدول را فیلتر کنید تا فقط سلول های تکراری نمایش داده شوند و همه آن سلول ها را انتخاب کنید. بر روی انتخاب
- کلیک راست کرده و ” Clear contents ” را انتخاب کنید:
- فیلتر را پاک کنید.
- تمام سلول های ستون A را از سلول A1 تا آخرین سلولی که حاوی داده است، انتخاب کنید.
- به تب Data بروید و مرتب سازی A به Z را کلیک کنید . در پنجره گفتگوی باز شده، ” ادامه با انتخاب فعلی ” را انتخاب کنید و بر روی مرتب سازی کلیک کنید :
- ستون حاوی فرمول را حذف کنید زیرا دیگر به آن نیاز ندارید، فقط “Uniques” در آنجا باقی مانده است.
- این همه است، اکنون ستون A فقط حاوی داده های منحصر به فردی است که در ستون B وجود ندارد :
همانطور که می بینید، حذف موارد تکراری بین دو ستون اکسل با استفاده از فرمول ها چندان دشوار نیست. اگرچه نوشتن و کپی کردن فرمول فرآیندی بسیار زمان بر و خسته کننده است، اما هر بار که نیاز به مقایسه 2 ستون در کاربرگ خود دارید، فیلتر را اعمال و پاک کنید. راه حل دیگری که می خواهم به شما توجه کنم بسیار ساده تر است و تنها کسری از زمانی را که ما برای روش اول صرف کرده ایم نیاز دارد. من معتقدم چیزهای دلپذیرتری برای صرف وقت صرفه جویی شده پیدا خواهید کرد 😉
2 ستون اکسل را برای موارد تکراری با استفاده از یک جادوگر بصری مقایسه کنید
و اکنون اجازه دهید به شما نشان دهم که چگونه با استفاده از ابزار Dedupe برای اکسل، دو ستون را برای موارد تکراری مقایسه کنید .
- کاربرگ (یا کاربرگ‌ها) را باز کنید که در آن ستون‌هایی که می‌خواهید مقایسه کنید، قرار دارند.
- هر سلولی را در ستون 1 انتخاب کنید، به برگه Ablebits Data بروید و روی دکمه مقایسه جداول کلیک کنید :
- در مرحله 1 جادوگر، خواهید دید که ستون اول شما قبلاً انتخاب شده است، بنابراین به سادگی روی Next کلیک کنید .توجه داشته باشید. اگر می خواهید نه فقط 2 ستون، بلکه 2 جدول را با هم مقایسه کنید، باید کل جدول اول را در این مرحله انتخاب کنید.
- در مرحله 2 ویزارد، ستون دومی را که می خواهید با آن مقایسه کنید انتخاب کنید. ما Sheet2 را در همان Workbook انتخاب می کنیم. در بیشتر موارد، جادوگر هوشمند ستون 2 را به طور خودکار انتخاب می کند، اگر به دلایلی این اتفاق نیفتاد، ستون هدف را با استفاده از ماوس انتخاب کنید. اگر کل جداول را با هم مقایسه می کنید، کل جدول دوم را انتخاب کنید.
- برای یافتن مقادیر تکراری انتخاب کنید :
- جفت ستونی را که می خواهید مقایسه کنید انتخاب کنید:
- نکته. اگر جداول را با هم مقایسه می کنید، می توانید چندین جفت ستون را برای مقایسه انتخاب کنید، مثلاً نام و نام خانوادگی.
- و در نهایت، شما باید تصمیم بگیرید که با فریب های پیدا شده چه کاری می خواهید انجام دهید. شما می توانید انتخاب کنید که ورودی های تکراری را حذف کنید، آنها را به کاربرگ دیگری منتقل یا کپی کنید، یک ستون وضعیت اضافه کنید (نتیجه مشابه اولین راه حل ما با فرمول های اکسل خواهد بود)، موارد تکراری را برجسته کنید، یا فقط تمام سلول های دارای مقادیر تکراری را انتخاب کنید:
- نکته. حذف موارد تکراری را انتخاب نکنید، به خصوص اگر برای اولین بار از ابزار استفاده می کنید. درعوض، انتخاب کنید که dupes را به کاربرگ دیگری منتقل کنید . این کار موارد تکراری را از جدول اول حذف می‌کند، اما به شما فرصتی می‌دهد تا لیست ورودی‌های شناسایی شده به عنوان تکراری را مرور کنید. هنگام مقایسه با چندین ستون منطبق در جداول بزرگ، ممکن است اتفاق بیفتد که به طور تصادفی فراموش کرده اید یک ستون کلیدی با داده های منحصر به فرد را انتخاب کنید و جابجایی موارد تکراری از از دست رفتن غیرقابل برگشت داده ها جلوگیری می کند.
- روی Finish کلیک کنید و از نتیجه لذت ببرید. چیزی که اکنون داریم یک میز زیبا و تمیز بدون موارد تکراری است:
راه حل قبلی را به خاطر بسپارید و تفاوت را احساس کنید 🙂 در واقع سریع و آسان است که کاربرگ های خود را با مقایسه دو جدول حذف کنید . در واقع، زمان کمتری از زمانی که برای خواندن این مقاله صرف کرده اید، از شما خواهد گرفت.
