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

یکی از پرتکرارترین سوال های کاربران اکسل این است که «چگونه دو ستون را در اکسل مقایسه کنیم؟»

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

به عنوان مثال، ممکن است بخواهیم دو ستون را با یکدیگر مقایسه کنیم و تمام مقادیر مشترک را پیدا کنیم، یا تفاوت‌ها را (جایی که یک مقدار مشخص در یک ستون وجود دارد و در دیگری نیست) را بیابیم و هایلایت کنیم.

مقایسه سطر بە سطر دو ستون

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

این نوع مقایسه با دستور A1=B1= انجام می پذیرد و نیاز به ستون سوم دارد تا نتایج را به ما نشان دهد. همانطور که می دانید A1 و B1 براساس آدرس داده های شما تغییر می کند.

به عنوان مثال در داده های زیر مطابقت دو سطر هر ستون را با استفاده از دستور فوق سنجیدیم.

مشاهده می کنید که اگر مطابقت وجود داشته باشد، “TRUE” و اگر مطابقت وجود نداشته باشد، “FALSE” به عنوان نتیجه نمایش داده می شود.

مقایسه سطر به سطر دو ستون با استفاده از قابلیت Conditional Formatting

در ادامه بررسی مقایسه دو ستون در اکسل به نوع دوم آن می رسیم. اگر می‌خواهید سطرهایی را که داده های همسان دارند هایلایت کنید (به جای این که نتیجه را در یک ستون جداگانه به دست آورید)، می توانید این کار را با استفاده از فرمت دهی شرطی (Condition Formatting) انجام دهید.

برای انجام این کار، کل مجموعه داده را انتخاب کنید. سپس در تب Home، بر روی Conditional Formatting، کلیک کنید. بعد از آن از منوی کشویی، “New Rule” را انتخاب کنید.

بعد از باز شدن پنجره‌ی “New Formatting Rule”، بر روی گزینه “Use a formula to determine which cells to format” کلیک کنید. سپس در فیلد formula، این فرمول را وارد کنید:

=$A2=$B2

توجه داشته باشید که علامت دلار قبل از A و B، این دو ستون را مطلق کرده است.

بر روی گزینه Format کلیک کنید و فرمتی را که می خواهید برای سلول هایی که مطابقت دارند اعمال شود، تعیین کنید.

با Ok کردن تنظیمات انجام شده، نتیجه را به صورت زیر مشاهده می کنیم.

توجه: برای هایلایت کردن مغایرت ها تنها کافی است در در فیلد formula این فرمول را وارد کنید:

=$A2<>$B2

مقایسه دو ستون و مشخص کردن مطابقت ها و مغایرت ها

اکنون می خواهیم آیتم های مشترک ستون A و ستون B را صرف نظر از اینکه در یک سطر قرار گرفته اند یا نه جستجو کنیم. روش های مختلفی برای انجام این کار وجود دارد که در نمایش نتیجه نهایی متفاوت هستند.

مقایسه دو ستون با استفاده از فرمول نویسی

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

فرض کنید در مجموعه داده مثال قبل، می خواهیم داده های همسان را استخراج کنیم. برای انجام این کار تابع Vlookup را انتخاب می کنیم.

فرمول Vlookup را بصورت زیر می نویسیم:

=VLOOKUP(A2;$B$2:$B$11;1;0)

این فرمول از تابع Vlookup استفاده می شود تا بررسی کند ‌که آیا کاربرهای ستون A در ستون B هم هستند یا خیر. اگر حضور داشته باشد، آن شناسه را از ستون B برمی گرداند، در غیر این صورت خطای ‎#N/A را برمی گرداند.

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

مقایسه دو ستون با استفاده از قابلیت Conditional Formatting

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

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

برای درک بهتر مجموعه داده زیر را در نظر بگیرید که تعداد سطرهای هر ستون یکسان نیست و ما باید روشی غیر از مقایسه سطر به سطر را برگزینیم.

برای هایلات کردن داده های مشترک در این ستون، کل مجموعه داده را انتخاب می کنیم و بعد از کلیک کردن Conditional Formatting، نشانگر را روی گزینه Highlight Cell Rules قرار می دهیم. سپس روی Duplicate Values کلیک می کنیم.

با انجام مراحل بالا، پنجره Duplicate Values باز می شود. در این پنجره مطمئن شوید “Duplicate” انتخاب شده است. سپس قالب بندی مورد نظر را انتخاب می کنیم.

نتیجه به صورت زیر نمایش داده می شود.

برای نمایش داده های ناهمسان، مراحل فوق را تکرار می کنیم با این تفاوت که در پنجره Duplicate Values به جای “Duplicate”، گزینه “Unique” را انتخاب می کنیم.

در تصویر زیر داده های همسان با رنگ آبی و داده های ناهمسان با رنگ زرد مشخص شده اند.

مقایسه دو ستون با استفاده از فرمول نویسی و قابلیت Conditional Formatting

یکی دیگر از روش های مقایسه دو ستون در اکسل، استفاده از تابع Countif است. در این روش می خواهیم این تابع را در Conditional Formatting اجرا کنیم تا نتیجه را به صورت رنگی نمایش دهد.

فرض کنید می خواهیم ببینیم کدام یک از آیتم های ستون A در ستون B تکرار شده اند. پس برای رسیدن به جواب این سوال ابتدا ستون A را انتخاب می کنیم.

سپس در Conditional Formatting گزینه “New Rule” را انتخاب می کنیم. بعد از باز شدن پنجره‌ی “New Formatting Rule”، بر روی گزینه “Use a formula to determine which cells to format” کلیک می کنیم. در فیلد formula، فرمول زیر را وارد می کنیم:

=countif($B$2:$B$11;$A2)>0

این تابع ستون A را در ستون B می شمارد. پس سطرهای ستون A را مطلق نکردیم تا تابع بتواند یکی یکی سطرهای ستون A را با ستون B مقایسه کند. برای اینکه به تابع بگوییم حاصل شمارش مثبت باشد پس ۰< را در انتهای تابع اضافه کردیم.