مقایسه دو ستون در اکسل با ۷ روش کاربردی

هنگام کار با صفحات گسترده در نرم‌افزار مایکروسافت اکسل، مقایسه داده‌های موجود در دو ستون می‌تواند کاری زمان‌بر باشد. به جای تجزیه و تحلیل ستون‌ها و نوشتن عباراتی نظیر «مشابه» یا «غیرمشابه» در هر ستون جداگانه، می‌توانید از توابع اکسل برای ساده‌سازی فرآیند استفاده کنید. در این مقاله نگاهی به نحوه استفاده از توابع مختلف اکسل برای مقایسه دو ستون و شناسایی داده‌های مشابه یا غیر مشابه می‌اندازیم.

لازم به ذکر است که اگر تمایل دارید اکسل را بهتر و بیشتر یاد بگیرید می‌توانید مطالب آموزش اکسل و فرمول نویسی در اکسل مجله فرادرس را که یکی از بهترین مراجع رایگان جهت شروع به کار با این ابزار است مطالعه کنید، یا فیلم‌های آموزش اکسل فرادرس را ببینید.

1. نحوه برجسته کردن داده های تکراری در اکسل

اگر مایل هستید به مقایسه دو ستون در اکسل بپردازید، اما نمی‌خواهید ستون سومی به آن‌ها بیفزایید که نشان دهد داده‌ها در هر دو ستون وجود دارد یا خیر، می‌توانید از ویژگی «قالب‌بندی شرطی» یا «Conditional Formatting» استفاده کنید. برای انجام این کار مراحل را پایین را دنبال کنید:

1. سلول‌های داده‌ای را که می‌خواهید مقایسه کنید برگزینید.

2. به زبانه Home بروید.

3. از گروه Styles، منوی Conditional Formatting را باز کنید.

4. روی قسمت Highlight Cells Rules و بعد Duplicate Values بزنید.

5. در پنجره Duplicate Values، مطمئن شوید که گزینه Format cells that contain حاوی گزینه Duplicate باشد و نوع قالب‌بندی را در کنار بخش values with انتخاب کنید.

6. روی OK بزنید.

اکنون اکسل نام‌هایی را که در هر دو ستون وجود دارد برجسته می‌کند.

مطالب پیشنهادی برای مطالعه:

• ادغام دو ستون در اکسل — آموزش تصویری و ساده
• جمع ستون در اکسل — ۵ روش کاربردی به زبان ساده
• پسورد گذاشتن روی فایل اکسل — راهنمای تصویری کامل
• نوشتن فرمول جمع در اکسل — به زبان ساده + مثال
• جمع کردن اعداد در اکسل — آموزش سریع روش ها و فرمول ها

2. نحوه برجسته کردن داده های منحصر به فرد

اگر می‌خواهید داده‌هایی که بخشی از هر دو ستون نیستند را شناسایی کنید، می‌توانید مجددا از همان تابع بهره ببرید.

برای انجام این کار:

1. مجموعه داده را انتخاب کنید.

2.یک بار دیگر، به ترتیب به بخش‌های Duplicate Values < Highlight Cells Rules < Conditional Formatting < Home بروید.

3. برای سلول‌های حاوی قالب‌بندی، قسمت Unique را برگزینید.

4. نحوه برجسته شدن داده‌های ناهماهنگ را انتخاب و روی قسمت OK کلیک کنید.

اکنون اکسل نام‌هایی که فقط در یکی از دو ستون یافت می‌شوند را برجسته می‌کند.

اگرچه بهره‌مندی از این روش‌ها می‌تواند آسان به نظر برسد، اما ممکن است برای صفحات گسترده بزرگ‌تر کارآمد نباشند. بنابراین، راه‌حل‌های پیچیده‌تری را بررسی خواهیم کرد که به شما نشان می‌دهند کدام ردیف‌ها داده‌های مشابهی دارند یا از ستونی اضافی برای نمایش مقادیر بهره می‌برند که نمایان‌گر مطابقت داشتن یا نداشتن داده‌ها هستند.

فیلم آموزش اکسل Microsoft Office Excel 2016 در فرادرس

کلیک کنید

3. نحوه برجسته کردن ردیف‌های حاوی داده های یکسان

اگر به ترسیم تصویری بهتری از داده‌های یکسان نیاز دارید، می‌توانید اکسل را وادار کنید تا مقادیر منطبق را در دو ستون بیابد و سطرها را با داده‌های منطبق برجسته سازد.

همانطور که در روش قبلی توضیح دادیم، برای انجام این کار از ویژگی «Conditional Formatting» بهره می‌بریم، اما چند مرحله دیگر را به آن اضافه می‌کنیم. به این ترتیب، شما یک نشانگر بصری در اختیار دارید که به شما یاری می‌رساند تا داده‌های منطبق را سریع‌تر از خواندن از طریق ستونی جداگانه شناسایی کنید.

به منظور استفاده از قابلیت «Conditional Formatting» نرم‌افزار اکسل جهت مقایسه دو مجموعه داده، مراحل پایین را دنبال کنید:

1. داده‌های موردنظر برای مقایسه را برگزینید (سربرگ‌ها را شامل نشود) و زبانه Home را باز کنید.

2. روی قسمت Conditional Formatting بزنید و بخش New Rule را برگزینید.

3. از بخش Select a Rule Type، روی قسمت Use a formula بزنید تا مشخص شود کدام سلول‌ها را قالب‌بندی می‌کنید.

4. فرمول A2$=$B2= را در کادر Format values where this formula is true را وارد کنید. در اینجا، A و B مربوط به دو ستونی است که آن‌ها را مقایسه می‌کنیم.

5. برای سفارشی‌سازی نحوه برجسته یا هایلایت کردن ردیف‌ها توسط اکسل، روی قسمت Format کلیک کنید و در پنجره Format cells، زبانه Fill را برگزینید. می‌توانید رنگ پس‌زمینه، سبک الگو و رنگ الگو را نیز به دلخواه انتخاب کنید. در این بخش یک تصویر اولیه دریافت می‌کنید و بنابراین قادر خواهید بود پیش‌نمایش طرح را ببینید. پس از تکمیل فرآیند سفارشی سازی، روی قسمت OK بزنید.

6. در پنجره New Formatting Rule روی گزینه OK کلیک کنید، اکسل بلافاصله ردیف‌هایی را که داده‌های منطبق دارند برجسته می‌سازد.

هنگام مقایسه دو ستون در اکسل با استفاده از این روش، می‌توانید ردیف‌های دارای داده‌های مختلف را نیز برجسته کنید. مراحل بالا را طی کنید و در مرحله 5، فرمول زیر را در کادر Format values where this formula is true وارد کنید:

1=$A2<>$B2

4. نحوه شناسایی موارد منطبق با بهره‌مندی از قابلیت TRUE یا FALSE

هنگام مقایسه دو ستون در اکسل می‌توانید ستونی جدید نیز اضافه کنید. با بهره‌مندی از این روش، ستون سومی را میفزایید که در صورت مطابقت داده‌ها، پیام TRUE و اگر داده‌ها مطابقت نداشتند، پیام FALSE را نشان می‌دهد.

برای ستون سوم، از فرمول A2=B2= جهت مقایسه دو ستون اول استفاده کنید. اگر گمان می‌برید صفحه گسترده شما با ردیف‌های TRUE و FALSE خیلی شلوغ به نظر می‌رسد، می‌توانید فیلتری را در اکسل تنظیم کنید که در نتیجه فقط نمایان‌گر مقادیر TRUE باشد.

5. نحوه مقایسه دو ستون در اکسل با استفاده از تابع IF

یک روش متفاوت برای تجزیه و تحلیل داده‌های دو ستون، استفاده از تابع IF در اکسل است. این روش شبیه به روش قبل است، اما با این مزیت که قادر خواهید بود مقدار نمایش داده شده را سفارشی کنید.

به جای داشتن مقادیر TRUE یا FALSE، می‌توانید مقدار را برای داده‌های مطابق یکدیگر یا متفاوت از هم تنظیم کنید. برای این مثال، ما از مقادیر «تطابق داده‌ها» (Data matches)  و «عدم تطابق داده‌ها» (Data doesn’t match) بهره می‌بریم.

فرمولی که ما برای ستونی که نتایج را نشان می‌دهد استفاده می‌کنیم بدین شرح است:

1=IF(A2=B2,"Data Matches","Data Doesn’t Match")

6. نحوه مقایسه دو ستون با یک تابع VLOOKUP و یافتن داده‌های منطبق

راه دیگر برای یافتن موارد تکراری در اکسل در دو ستون، استفاده از تابع VLOOKUP است. اکسل هر سلول درون ستون دوم را با سلول‌های ستون اول مقایسه می‌کند.

از فرمول زیر برای ستون نمایش نتایج بهره ببرید. فقط مطمئن شوید که پیش‌تر محدوده داده را تنظیم کرده‌اید.

1=VLOOKUP(B2,$A$2:$A$14,1,0)

هنگام استفاده از این فرمول، نرم‌افزار اکسل داده‌های منطبق را نمایش می‌دهد یا از مقدار N/A# (به معنای غیر قابل‌دسترس) بهره می‌برد. با این حال، مقدار N/A# ممکن است گیج کننده باشد، به خصوص اگر صفحه گسترده را برای شخص دیگری بفرستید. اگر آن اشخاص در مورد اکسل باتجربه نباشند، می‌توانند باور کنند که اشتباهی وجود دارد.

بنابراین برای جلوگیری از هرگونه سردرگمی، تابع VLOOKUP را به یک تابع IFERROR ارتقا دهید. اگر مایل هستید داده‌هایی را بیابید که در ستون B و همچنین در ستون A قرار گرفته‌اند، از فرمول زیر استفاده کنید:

1=IFERROR(VLOOKUP(B2,$A$2:$A$14,1,0),"Data Doesn't Match")

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

7. نحوه مقایسه دو ستون در اکسل و استخراج داده‌ها

علاوه بر مقایسه دو ستون اکسل جهت مطابقت، می‌توانید از تابع VLOOKUP برای استخراج داده‌های منطبق نیز بهره ببرید. این کار باعث صرفه‌جویی در وقت شما می شود چرا که دیگر مجبور نیستید به صورت دستی از ستون اول عبور کرده و داده‌های مربوطه را جستجو کنید.

از این گذشته، اگر داده‌های ستون دوم از ستون اول خارج شوند، اکسل یک مقدار N/A# نمایش می‌دهد. برای این کار از فرمول زیر استفاده کنید:

1=VLOOKUP(D2,$A$2:$B$14,2,0)

توجه: اگر می‌خواهید از نتایج خود در برابر اشتباهات املایی محافظت کنید، از فرمول زیر بهره ببرید:

1=VLOOKUP("*"&D2&"*",$A$2:$B$14,2,0)

در اینجا، علامت ستاره (*) نقش یک وایلد کارت را دارد که جایگزین هر تعداد کاراکتری می‌شود.

مقایسه داده‌ها با سهولت

همانطور که پیش‌تر اشاره کردیم، راه‌ها و ترفندهای زیادی وجود دارد که می‌توانید از آن‌ها برای مقایسه دو ستون در اکسل و دریافت بهترین نتایج بهره‌مند شوید و اکسل ابزارهای متعددی دارد که می‌تواند به شما یاری برساندتا از انجام کارهای تکراری جلوگیری و در زمان صرفه جویی کنید.