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

مقایسه دو ستون در اکسل و پیدا کردن داده های مشترک دو لیست در اکسل همیشه یکی از پر تکرارترین مسائل در اکسل بشمار می رود. به عنوان مثال دو لیست از مشتریان داریم که تعدادی از این مشتریان در هر دو لیست وجود دارند و می خواهیم این اشتراک ها را مشخص کنیم. در این آموزش از روش فرمول نویسی این مسئله را حل میکنید و در مقاله مقایسه دو لیست با استفاده از ابزار پاور کوئری راهی دیگر آموزش داده شده که پیشنهاد میکنیم هر دو مقاله رو بخونید. همچنین انجام این مقایسه در گوگل شیت هم در آموزش مقایسه لیست در گوگل شیت قرار داده شده.

مشخص کردن موارد مشترک حالت های مختلفی دارد:

۱- می توان موارد مشترک را از طریق فرمول نویسی بصورت جداگانه لیست کرد.
۲- می توان با فرمول نویسی سل روبروی هر مورد مشترک را متمایز کرد.
۳- می توان موارد مشترک در یک لیست را با تغییر رنگ خاصی متمایز کرد.

در این آموزش نحوه متمایز کردن موارد مشترک از طریق اعمال فرمت (رنگ سل) را شرح می دهم:

مطابق شکل ۱ لیستی از نام شهرها داریم (لیست۱). حالا میخواهیم شهرهایی از لیست ۱ که در لیست ۲ وجود دارند را رنگی کنیم.

شکل ۱- مقایسه دو ستون در اکسل – داده های موجود برای مقایسه

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

برای اینکه بتوانیم تشخیص دهیم کدام شهر از لیست ۱ در لیست ۲ وجود دارد، کافیست تعداد هر شهر از لیست ۱ را در لیست ۲ شمارش کنیم. مثلا باید فرمولی بنویسیم که تعداد اصفهان را در لیست ۲ شمارش کند. اگر بیش از صفر باشد، یعنی آن شهر در لیست ۲ تکرار شده است و اگر صفر باشد یعنی آن شهر در لیست ۲ وجود ندارد. پس با استفاده از تابع Countif این کار را انجام می دهیم:

=COUNTIF($D$2:$D$5,B2)

خروجی این تابع صفر است زیرا شهر اصفهان در لیست ۲ وجود ندارد.

حالا برای اینکه در صورت موجود بودن، فرمت مورد نظر بر روی سل اعمال شود، باید شرط را بصورت منطقی دربیاوریم. که اگر شرط برقرار بود فرمت اعمال شود. پس تابع Countif را با IF ترکیب می کنیم:

=if(COUNTIF($D$2:$D$5,B2)>0,1,0)

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

روی سل B2 قرار گرفته و از مسیر زیر و مطابق شکل ۲، فرمول بالا را وارد می کنیم و فرمت دلخواه را از قسمت Format تنظیم میکنیم و Ok را می زنیم:

Home> Conditional_Formatting> New Rule> Use a formula to determine which cells to format

شکل۲- مقایسه دو ستون در اکسل – فرمول نویسی در Conditional Formatting

حالا کافیست شرط مورد نظر را به محدوده لیست ۱ انتقال دهیم. برای این کار ۲ روش وجود دارد:

• Format Painter : بر روی سل B2 قرار می گیریم و از تب Home گزینه format painter را کلیک کرده، سپس محدوده مورد نظر برای اعمال فرمت را انتخاب میکنیم. (که در اینجا کل لیست۱ یا محدوده B2:B13 است)
• مطابق شکل ۳ و از مسیر زیر، محدوده اعمال فرمت شرطی مورد نظر را تنظیم می کنیم:

Home> Conditional_Formatting> Manage Rule> Applies to

شکل۳- مقایسه دو ستون در اکسل – تنظیم محدوده مورد نظر برای اعمال فرمت شرطی

با زدن Apply فرمت شرطی بر روی محدوده مورد اعمال می شود. یعنی مانند شکل ۴ شهر هایی که تعداد آنها در لیست ۲ بیش از صفر است رنگی می شود.

شکل۴- مشخص شدن مواردی از لیست ۱ که در لیست ۲ وجود دارند