فهرست
خواندن این مقاله حدود 10 دقیقه زمان می برد. در 5 دقیقه آینده می توانید به راحتی دو ستون را در اکسل مقایسه کنید و متوجه شوید که آیا موارد تکراری در آنها وجود دارد، آنها را حذف کنید یا رنگی آنها را برجسته کنید. بنابراین، زمان آن فرا رسیده است!
اکسل یک نرم افزار بسیار قدرتمند و واقعا جالب برای ایجاد و پردازش حجم زیادی از داده ها است. اگر چندین کتاب کار با داده (یا فقط یک جدول بزرگ) دارید، احتمالاً می خواهید 2 ستون را مقایسه کنید، مقادیر تکراری را پیدا کنید و سپس کاری با آنها انجام دهید، به عنوان مثال، محتویات را حذف کنید، برجسته یا پاک کنید. ستون‌ها می‌توانند در یک جدول باشند، مجاور باشند یا مجاور نباشند، ممکن است در ۲ صفحه مختلف یا حتی در کتاب‌های مختلف قرار بگیرند.
تصور کنید ما 2 ستون با نام افراد داریم – 5 نام در هر ستون A و 3 نام در یک ستون B. شما باید نام های این دو ستون را با هم مقایسه کنید و موارد تکراری را پیدا کنید. همانطور که می‌دانید، این داده‌های ساختگی است که صرفاً به عنوان مثال گرفته شده است. در جداول واقعی، ما با هزاران یا حتی ده ها هزار رکورد روبرو هستیم.
گزینه A: هر دو ستون در یک صفحه هستند. به عنوان مثال، یک ستون A و ستون B.
گزینه B: ستون ها در ورق های مختلف هستند. به عنوان مثال، یک ستون A روی ورق Sheet2 و ستون A روی ورق Sheet3.
اکسل 2013، 2010 و 2007 دارای یک ابزار داخلی هستند موارد تکراری را حذف کنید (حذف موارد تکراری) اما در این شرایط ناتوان است زیرا نمی تواند داده ها را در 2 ستون مقایسه کند. علاوه بر این، فقط می تواند موارد تکراری را حذف کند. هیچ گزینه دیگری مانند برجسته کردن یا تغییر رنگ وجود ندارد. و اشاره کنید!
در مرحله بعد، راه های ممکن برای مقایسه دو ستون در اکسل را به شما نشان می دهم که به شما امکان می دهد رکوردهای تکراری را پیدا و حذف کنید.
2 ستون را در اکسل مقایسه کنید و ورودی های تکراری را با استفاده از فرمول ها پیدا کنید
گزینه A: هر دو ستون در یک صفحه هستند
- در اولین سلول خالی (در مثال ما، این سلول 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")بجای "تنها"و"تکراری» می توانید برچسب های خود را بنویسید، به عنوان مثال، «یافت نشد"و"پیدا شده"، یا فقط ترک کنید"تکراری' و به جای مقدار دوم یک کاراکتر فاصله وارد کنید. در مورد دوم، سلول‌هایی که هیچ تکراری برای آنها یافت نشد خالی می‌مانند، و به اعتقاد من، این نمایش داده‌ها برای تحلیل بیشتر راحت‌تر است.
- حالا بیایید فرمول خود را در تمام سلول های ستون کپی کنیم C، تا سطر پایین که حاوی داده های ستون است A. برای انجام این کار، نشانگر ماوس را به گوشه سمت راست پایین سلول ببرید C1، نشانگر مانند تصویر زیر به شکل یک خط تیره مشکی خواهد بود:روی دکمه سمت چپ ماوس کلیک کنید و نگه دارید و مرز کادر را به پایین بکشید و تمام سلول هایی را که می خواهید فرمول را در آن وارد کنید برجسته کنید. هنگامی که تمام سلول های مورد نیاز انتخاب شدند، دکمه ماوس را رها کنید:
روی دکمه سمت چپ ماوس کلیک کنید و نگه دارید و مرز کادر را به پایین بکشید و تمام سلول هایی را که می خواهید فرمول را در آن وارد کنید برجسته کنید. هنگامی که تمام سلول های مورد نیاز انتخاب شدند، دکمه ماوس را رها کنید:
نکته: در جداول بزرگ، در صورت استفاده از میانبرهای صفحه کلید، کپی کردن فرمول سریعتر خواهد بود. یک سلول را برجسته کنید C1 و مطبوعات کلیدهای Ctrl + C (برای کپی کردن فرمول در کلیپ بورد)، سپس کلیک کنید Ctrl + Shift + End (برای انتخاب تمام سلول های غیر خالی در ستون C) و در نهایت فشار دهید Ctrl + V (برای درج فرمول در تمام سلول های انتخاب شده).
- عالی است، اکنون همه مقادیر تکراری به عنوان " علامت گذاری شده اندتکراری":
گزینه B: دو ستون در برگه های مختلف (در کتاب های کاری مختلف) قرار دارند.
- در اولین خانه از اولین ستون خالی در کاربرگ Sheet2 (در مورد ما ستون B است) فرمول زیر را وارد کنید:
=IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")اینجا Sheet3 نام برگه ای است که ستون 2 روی آن قرار دارد و 1 دلار استرالیا: 10000 دلار آمریکا آدرس های سلولی از اول تا آخر در این ستون دوم هستند.
- فرمول را در تمام سلول های یک ستون کپی کنید B (همان گزینه A).
- این نتیجه را می گیریم:
پردازش موارد تکراری یافت شده
عالی است، ما ورودی هایی را در ستون اول پیدا کرده ایم که در ستون دوم نیز وجود دارد. حالا باید کاری با آنها انجام دهیم. مرور تمام رکوردهای تکراری در یک جدول به صورت دستی کاملاً ناکارآمد است و زمان زیادی می برد. راه های بهتری هم هست
فقط ردیف های تکراری در ستون A نمایش داده شود
اگر ستون های شما هدر ندارند، باید آنها را اضافه کنید. برای انجام این کار، مکان نما را روی عددی که نشان دهنده خط اول است قرار دهید و مانند شکل زیر به یک فلش سیاه تبدیل می شود:
راست کلیک کرده و از منوی زمینه انتخاب کنید الحاق (درج):
به ستون ها نام بدهید، به عنوان مثال، "نام"و"تکراری؟» سپس تب را باز کنید داده ها (داده ها) و فشار دهید فیلتر (فیلتر):
پس از آن روی فلش خاکستری کوچک کنار « کلیک کنیدتکراری؟« برای باز کردن منوی فیلتر؛ علامت همه موارد در این لیست را بردارید به جز تکراری، و مطبوعات OK.
این همه است، اکنون فقط آن عناصر ستون را می بینید А، که در ستون کپی شده اند В. تنها دو سلول از این قبیل در جدول آموزشی ما وجود دارد، اما، همانطور که می دانید، در عمل تعداد بیشتری از آنها وجود خواهد داشت.
برای نمایش مجدد تمام ردیف های یک ستون А، روی نماد فیلتر در ستون کلیک کنید В، که اکنون شبیه یک قیف با یک فلش کوچک است و انتخاب کنید همه را انتخاب کنید (انتخاب همه). یا می توانید همین کار را از طریق Ribbon با کلیک کردن انجام دهید داده ها (داده ها) > انتخاب و فیلتر کنید (مرتب سازی و فیلتر) > واضح (پاک کردن) همانطور که در تصویر زیر نشان داده شده است:
رنگ را تغییر دهید یا موارد تکراری یافت شده را برجسته کنید
اگر یادداشت هاتکراری” برای اهداف شما کافی نیست و می خواهید سلول های تکراری را با رنگ فونت، رنگ پر یا روش دیگری علامت گذاری کنید…
در این حالت، همانند تصویر بالا، موارد تکراری را فیلتر کنید، تمام سلول های فیلتر شده را انتخاب کرده و کلیک کنید Ctrl + 1برای باز کردن گفتگو سلولها را قالب بندی کنید (فرمت سلولی). به عنوان مثال، اجازه دهید رنگ پر شدن سلول ها در ردیف های تکراری را به زرد روشن تغییر دهیم. البته می توانید رنگ پر را با ابزار تغییر دهید پر کردن برگه (پر کردن رنگ). صفحه اصلی (صفحه اصلی) اما مزیت جعبه محاوره ای سلولها را قالب بندی کنید (Cell Format) به این صورت که می توانید تمام گزینه های قالب بندی را همزمان پیکربندی کنید.
اکنون قطعا هیچ سلولی با موارد تکراری را از دست نخواهید داد:
حذف مقادیر تکراری از ستون اول
جدول را فیلتر کنید تا فقط سلول هایی با مقادیر تکراری نشان داده شوند و آن سلول ها را انتخاب کنید.
اگر 2 ستونی که مقایسه می کنید در برگه های مختلف هستندیعنی در جداول مختلف روی محدوده انتخاب شده راست کلیک کرده و انتخاب کنید ردیف را حذف کنید (حذف خط):
رسانه ها و مطبوعات OKهنگامی که اکسل از شما می خواهد تأیید کنید که واقعاً می خواهید کل ردیف برگه را حذف کنید و سپس فیلتر را پاک کنید. همانطور که می بینید، فقط ردیف هایی با مقادیر منحصر به فرد باقی می مانند:
اگر 2 ستون در یک صفحه باشد، نزدیک به یکدیگر (مجاور) یا نزدیک به یکدیگر (نه مجاور)، سپس روند حذف موارد تکراری کمی پیچیده تر خواهد شد. ما نمی‌توانیم کل ردیف را با مقادیر تکراری حذف کنیم، زیرا با این کار سلولها از ستون دوم نیز حذف می‌شوند. بنابراین برای ترک فقط ورودی های منحصر به فرد در یک ستون А، این کار را انجام دهید:
- جدول را فیلتر کنید تا فقط مقادیر تکراری نشان داده شود و آن سلول ها را انتخاب کنید. روی آنها کلیک راست کرده و از منوی زمینه انتخاب کنید محتویات را پاک کنید (مطالب را پاک کنید).
- فیلتر را تمیز کنید.
- تمام سلول های یک ستون را انتخاب کنید А، از سلول شروع می شود A1 تمام راه را تا پایین حاوی داده ها.
- با کلیک بر روی داده ها (داده ها) و فشار دهید مرتب سازی A تا Z (از A به Z مرتب کنید). در کادر محاوره ای که باز می شود، را انتخاب کنید با انتخاب فعلی ادامه دهید (در انتخاب مشخص شده مرتب کنید) و روی دکمه کلیک کنید سیاه پوست (مرتب سازی):
- ستون را با فرمول حذف کنید، دیگر به آن نیاز نخواهید داشت، از این به بعد فقط مقادیر منحصر به فرد دارید.
- همین، حالا ستون А فقط شامل داده های منحصر به فردی است که در ستون نیست В:
همانطور که می بینید، حذف موارد تکراری از دو ستون در اکسل با استفاده از فرمول ها چندان دشوار نیست.