سؤالی که بسیاری از اوقات با آن مواجه میشویم این است – «چگونه دو ستون را در اکسل مقایسه کنیم؟»
این کار میتواند به روشهای مختلفی انجام شود و روش مورد استفاده به ساختار داده و آنچه کاربر از آن میخواهد بستگی دارد.
به عنوان مثال، ممکن است بخواهید دو ستون را با یکدیگر مقایسه کنید و تمام نقاط مطابقت داده (که در هر دو ستون است) را پیدا کنید یا هایلایت کنید، یا فقط تفاوتها را (جایی که یک دادهی مشخص در یک ستون است و در دیگری نیست) و غیره.
از آنجایی که در مورد این موضوع بسیار سؤال شده، تصمیم گرفتیم این آموزش حجیم را با هدف نگارش بیشترین سناریوهای ممکن (اگر نه همه) بنویسیم.
اگر این مطلب برایتان مفید بود، آنرا با سایر کاربران اکسل به اشتراک بگذارید.
توجه داشته باشید که تکنیکهای مقایسه ستونها، که در این آموزش نشان داده شدهاند تنها محدود به این موارد نیستند.
بر اساس مجموعه دادهتان، ممکن است شما نیاز به تغییر یا اصلاح این روشها داشته باشید. با این حال، اصول اساسی همان است.
اگر فکر میکنید چیزی وجود دارد که میتواند به این آموزش اضافه شود، در بخش نظرات به ما اطلاع دهید.
مقایسه دو ستون بە صورت سطر بە سطر
این، سادهترین شکل مقایسه است. در این حالت، مقایسه به صورت سطر به سطر انجام شده و شناسایی میکنید کدام سطر دادههای یکسانی دارند و کدام یک از آنها ندارد.
مثال: مقایسه دو ستون در همان سطر
در زیر، مجموعهای از دادهها وجود دارد که باید بررسی کنم آیا نام ستون A با ستون B یکسان است یا خیر.
اگر مطابقت وجود داشته باشد، به عنوان نتیجه “TRUE” و اگر مطابقت وجود نداشته باشد، “FALSE” نمایش داده میشود.
فرمول زیر این کار را انجام میدهد:
=A2=B2
مثال: مقایسه دو ستون در همان سطر (با استفاده از فرمول IF)
اگر میخواهید نتیجهی توصیفیتری بدست آورید، میتوانید از یک فرمول سادهی IF برای برگرداندن “Match” (مطابقت) در هنگام مطابقت نامها و “Mismatch” (عدم مطابقت) هنگام عدم مطابقت نامها استفاده کنید.
=IF(A2=B2,"Match","Mismatch")
توجه: در صورتی که میخواهید مقایسه به بزرگی و کوچکی حروف حساس باشد، از فرمول IF زیر استفاده کنید:
=IF(EXACT(A2,B2),"Match","Mismatch")
با فرمول فوق، “IBM” و “ibm” دو نام مختلف در نظر گرفته میشوند و فرمول فوق “Mismatch” (عدم تطابق) برمیگرداند.
مثال:مقایسه سلولها و هایلایت کردن مطابقتها و مغایرتها
اگر میخواهید سطرهایی را که دادههای همسان دارند هایلایت کنید (به جای اینکه نتیجه را در یک ستون جداگانه به دست آورید)، میتوانید این کار را با استفاده از کاندیشنال فرمتینگ (Condition Formatting) انجام دهید.
در اینجا مراحل انجام این کار آورده شدهاست:
- کل مجموعه داده را انتخاب کنید.
- روی زبانهی “Home” کلیک کنید.
- در بخش Styles، بر روی گزینهی “Conditional Formatting” کلیک کنید.
- از منوی کشویی، “New Rule” را انتخاب کنید.
- در پنجرهی “New Formatting Rule”، بر روی “Use a formula to determine which cells to format” کلیک کنید.
- در فیلد formula، این فرمول را وارد کنید: =$A1=$B1
- بر روی دکمهی Format کلیک کنید و قالبی را که میخواهید برای سلولهایی که مطابقت دارند اعمال شود، تعیین کنید.
- OK را بزنید.
با این کار تمام سلولهایی که در هر سطر نامهایشان یکسان هستند، هایلایت میشود.
توجه: برای هایلایت کردن مغایرتها تنها کافی است در مرحلهی 6 فرمول را عوض کرده و این فرمول را وارد کنید: =$A1<>$B1
در ادامه میتوانید قالببندی مورد نظرتان را اعمال کنید.
مثال:مقایسه دو (یا چند) ستون و انتخاب (و هایلایت) سلولهای متفاوت
برای هایلایت کردن سریع سلولهای دارای مقادیر متفاوت در هر سطر جداگانه، میتوانید از ویژگی Go To Special اکسل استفاده کنید.
- محدودهی سلولهایی را که میخواهید مقایسه کنید، انتخاب کنید. در این مثال سلولهای A۲ تا C۸ را انتخاب کردهایم.به طور پیش فرض بالاترین سلول در محدودهی انتخابشده، سلول فعال است و سلولهای سایر ستونهای انتخابشده در همان ردیف، با آن سلول مقایسه میشوند. همانطور که در تصویر بالا مشاهده میکنید، سلول فعال سفید است در حالی که تمام سلولهای دیگر محدودهی انتخابشده هایلایت هستند. در این مثال سلول فعال A۲ است، بنابراین ستون مقایسه ستون A است.برای تغییر ستون مقایسه، از کلید Tab برای حرکت به چپ به راست میان سلولهای انتخابشده استفاده کنید یا از کلید Enter برای حرکت از بالا به پایین.نکته: برای انتخاب ستونهای غیر مجاور، ستون اول را انتخاب کنید، Ctrl را فشار داده و نگه دارید، و سپس ستونهای دیگر را انتخاب کنید. سلول فعال در آخرین ستون (یا در آخرین بلوک ستونهای مجاور) قرار خواهد گرفت. برای تغییر ستون مقایسه، از کلید Tab یا Enter مطابق توضیحات فوق استفاده کنید.در زبانهی Home، به بخش Editing بروید و گزینهی Find & Select سپس Go To Special… را کلیک کنید. حال Row differences را انتخاب کنید و بر روی دکمهی OK کلیک کنید.
- سلولهایی که مقادیر آنها با سلول مقایسهشده در هر سطر متفاوت است، انتخاب میشوند. اگر میخواهید سلولهای انتخابشده را رنگی کنید، کافیست روی نماد Fill Color روی روبین کلیک کنید و رنگ مورد نظر خود را انتخاب کنید.
مقایسه دو ستون و هایلایت کردن مطابقتها و مغایرتها
اگر میخواهید دو ستون را مقایسه کرده و دادههای همسان را هایلایت کنید، میتوانید از قابلیت تکراری (duplicate) در کاندیشنال فرمتینگ استفاده کنید.
توجه داشته باشید که این متفاوت از چیزی است که هنگام مقایسه هر ردیف دیدیم. در این حالت، ما مقایسه سطر به سطر را انجام نخواهیم داد.
مثال: مقایسه دو ستون و هایلایت کردن دادههای همسان
غالباً، مجموعه دادهای با دادههای همسان خواهید داشت، اما ممکن است آنها در یک ردیف قرار نگیرند.
مانند چیزی که در زیر نشان داده شدهاست:
توجه داشته باشید که لیست ستون A از لیست B بزرگتر است. همچنین برخی از نامها در هر دو لیست وجود دارند، اما در یک ردیف قرار ندارند (مانند IBM، Adobe، Walmart).
اگر میخواهید تمام نام شرکتهای همسان را هایلایت کنید، میتوانید با استفاده از کاندیشنال فرمتینگ این کار را انجام دهید.
در اینجا مراحل انجام این کار آورده شدهاست:
- کل مجموعه دادهها را انتخاب کنید.
- بر روی زبانهی Home کلیک کنید.
- در بخش Styles، بر روی گزینه “Condition Formatting” کلیک کنید.
- نشانگر را روی گزینهی Highlight Cell Rules قرار دهید.
- بر روی Duplicate Values کلیک کنید.
- در پنجرهی Duplicate Values، مطمئن شوید “Duplicate” انتخاب شدهاست.
- قالببندی را تعیین کنید
- OK را بزنید.
مراحل فوق نتیجهای مطابق شکل زیر را میدهد.
توجه: کاندیشنال فرکتینگ حساس به کوچکی و بزرگی حروف نیست؛ بنابراین “Apple” و “apple” یکسان در نظر گرفته میشوند و به عنوان تکراری هایلایت میشوند.
مثال: مقایسه دو ستون و هایلایت کردن دادههای غیرهمسان
در صورتی که میخواهید نامهایی را که در یک لیست وجود دارند و در دیگری نیست هایلایت کنید، میتوانید از کاندیشنال فرمتینگ نیز برای این کار استفاده کنید.
- کل مجموعه دادهها را انتخاب کنید.
- روی زبانهی Home کلیک کنید.
- در بخش Styles، بر روی گزینه “Condition Formatting” کلیک کنید.
- مکان نما را روی گزینهی Highlight Cell Rules قرار دهید.
- بر روی Duplicate Values کلیک کنید.
- در پنجرهی Duplicate Values، مطمئن شوید “Unique” انتخاب شدهاست.
- قالببندی را تعیین کنید.
- OK را بزنید.
این کار نتیجهای مطابق شکل زیر به شما میدهد. تمام سلولهای دارای نامی را که در لیست دیگر وجود ندارد، هایلایت میکند.
مقایسه دو ستون و یافتن دادههای ناموجود
اگر میخواهید تعیین کنید که آیا یک دادهی مشخص از یک لیست در لیست دیگر وجود دارد، میتوانید از فرمولهای جستوجو استفاده کنید.
با استفاده از فرمولهای جستوجو
فرض کنید شما یک مجموعه داده مانند تصویر زیر دارید و میخواهید شرکتهایی را که در ستون A حضور دارند اما در ستون B حضور ندارند، شناسایی کنید.
برای انجام این کار، میتوانیم از فرمول VLOOKUP زیر استفاده کنیم.
=ISERROR(VLOOKUP(A2,$B$2:$B$10,1,0))
این فرمول از تابع VLOOKUP برای بررسی اینکه آیا یک شرکت در A در ستون B موجود است یا خیر استفاده میکند. اگر حضور داشته باشد، آن نام را از ستون B برمیگرداند، در غیر این صورت یک خطای #N/A برمیگرداند.
نامهایی که خطای #N/A را برمیگردانند، نامهایی هستند که در ستون B وجود ندارند.
تابع ISERROR اگر نتیجهی VLOOKUP خطا باشد مقدار TRUE و اگر خطایی نباشد مقدار FALSE را برمیگرداند.
اگر لیستی از تمام نامهایی که مطابقت ندارد میخواهید، میتوانید ستون نتیجه را فیلتر کنید تا تمام سلولهای دارای TRUE را به دست آورید.
همچنین میتوانید برای انجام همین کار از تابع MATCH نیز استفاده کنید؛
=NOT(ISNUMBER(MATCH(A2,$B$2:$B$10,0)))
با استفاده از فرمول IF و COUNTIF
اگر میخواهید نتیجهی توصیفیتری بدست آورید، میتوانید از یک فرمول سادهی IF و COUNTIF برای برگرداندن “Match” (مطابقت) در هنگام مطابقت نامها و “Mismatch” (مغایرت) هنگام عدم مطابقت نامها استفاده کنید.
برای انجام این کار، میتوانیم از فرمول زیر استفاده کنیم.
=IF(COUNTIF($B$2:$B$10, A2)=0, "Mismatch", "Match")
این فرمول از تابع COUNTIF برای بررسی اینکه آیا یک شرکت در A در ستون B موجود است یا خیر استفاده میکند. اگر وجود نداشته باشد، تابع مقدار صفر را برمیگرداند ولی اگر وجود نداشته باشد تابع مقدار غیر صفر را برمیگرداند.
اگر وجود نداشته باشد شرط موجود در تابع IF درست خواهد بود پس تابع مقدار Mismatch (مغایرت) را برمیگرداند، در غیر این صورت تابع مقدار Match (مطابقت) را برمیگرداند.
مقایسه دو ستون و استخراج دادههای همسان
اگر دو مجموعه داده دارید و میخواهید موارد یک لیست را با لیست دیگر مقایسه کنید و دادههای همسان را استخراج کنید، باید از فرمولهای جستوجو استفاده کنید.
مثال: استخراج دادههای همسان (دقیق)
به عنوان مثال، در لیست زیر میخواهم مقدار ارزیابی بازار (market value) را برای ستون ۲ به دست آوریم. برای انجام این کار، باید آن را در ستون ۱ جستوجو کنیم و سپس مقدار ارزیابی بازار (market value) مربوطه را استخراج کنیم.
فرمول زیر این کار را انجام خواهد داد:
=VLOOKUP(D2,$A$2:$B$14,2,0)
یا
=INDEX($A$2:$B$14,MATCH(D2,$A$2:$A$14,0),2)
مثال: استخراج دادههای همسان (تقریبی)
در صورتی که یک مجموعه داده دارید که در دو ستون آن اختلاف جزئی در نامها وجود داشته باشد، استفاده از فرمولهای جستوجوی نشاندادهشدهی بالا کار شما را پیش نخواهد برد.
این فرمولهای جستوجو برای رسیدن به نتیجهی مناسب به یک مطابقت دقیق نیاز دارند. در تابع VLOOKUP یا MATCH گزینهای برای مطابقت تقریبی وجود دارد، اما در اینجا نمیتوان از آن استفاده کرد.
فرض کنید مجموعه دادهای مطابق شکل زیر داشته باشید. توجه داشته باشید که نامهایی وجود دارد که در ستون ۲ کامل نیست (مانند JPMorgan به جای JPMorgan Chase و Exxon به جای ExxonMobil).
در چنین حالتی می توانید با استفاده از کاراکترهای Wildcard جستوجوی تقریبی را انجام دهید.
فرمول زیر نتیجهی مناسب را برای این حالت به ما خواهد داد:
=VLOOKUP("*"&D2&"*",$A$2:$B$14,2,0)
یا
=INDEX($A$2:$B$14,MATCH("*"&D2&"*",$A$2:$A$14,0),2)
در مثال بالا، ستاره (*) یک کاراکتر Wildcard است که میتواند بیانگر هر تعداد از کاراکترها باشد. وقتی این کاراکتر در هر دو طرف مقدار مورد <جستوجو قرار گیرد، هر مقدار در ستون ۱ که حاوی مقدار جستجوی ستون ۲ است، به عنوان یک مطابقت در نظر گرفته میشود.
به عنوان مثال، *Exxon* برای ExxonMobil یک مطابقت خواهد بود (زیرا * میتواند نشاندهندهی هر تعداد از کاراکترها باشد).
مشاهدهی ویدئو
در ویدئوی زیر با دو روش فرمولنویسی و کاندیشنال فرمتینگ، مقایسهی دو ستون و یافتن دادههای موجود آموزش داده شدهاست.
عالی بود. ممنون
بسیار سپاسگزارم از محبت و دانشی که از شما آموختم انشااله موفق باشید.
سپاس و هزاران تشکر از آموزش روان و زیبا
عالی
مطلب بسیار خوبی بود.ممنون
عالی بود…………………..دمت گرم
درود
بسیار خوب و جامع
سپاس
خيلي خيلي عالي بود – فكر نمي كردم اكسل اينهمه قابليت داشته باشه ، شما هم سنگ تموم گذاشتيد – قدردان زحماتتون هستم
ممنونم از محبتتون خیلی زیبا و با حوصله
بسیار عالی و مفید و کاربردی
تشکر از وبسایت شما
عالی
خیلی لطف کردید
آموزشتون بسیار تمییز و دقیق بود
عالی
کامل جامعه مفید ، دستتون درد نکنه
خیلی جامع و عالی بود.
واقعا ممنون. خیلی کامل و جامع بود.
مقایسه دو ستون در دو شیت هم لطفا توضیح بدید. ممنون 🙏
واقعا آفرین
آفرین به کامل و دقیق بودن . آفرین به روش تدریس و روشن بودن مطلب.
ای کاش شایستگی شما در این هیاهوی اطلاعات ناقص وب راحت تر سر راه جویندگان قرار گیرد که مثل من وقتشان در مکتب آموزندگان پر انگیزه ولی کم مایه هدر نشود.
آفرین.
با سپاس از آموزش ارزشمند شما
کار من را راه انداخت.
بسیار عالی . ممنون
خیلی کاربردی بود.
واقعا عالی و مفید دستتون درد نکنه من کیف کردم دمتون گرم، خدا قوت موفق باشین
سلام
پس ازمدت ها توضیح کامل وجامعی یافتم.ممنون ازشما
بسیار سپاس عالی بود و راهگشا
بسیار عالی ،خییلی ممنون
سلام خسته نباشید میگم
خیلی خیلی ممنونم.من خیلی وقت بود دنبال همچین فرمولی میگشتم
عالی بود زنده باشید.
چقدر عالی بود . واقعا ممنونم . همونی بود که دنبالش بودم و بعد از انجامش چقدر هیجان زده ام!
متشکرم، کار من راه افتاد
سلام سایت خوبی دارید ممنون کارمو راه انداخت
سلام دوستم خیلی ممنون عالی بود
عالي و مفيد
ممنون
سپاسگزارم
بسیار مفید و عالی بود
سپاس فراوان به خاطر مطالب کاربردی.
خیلی خیلی خوب و مفید دعای خیر همه بدرقه راهتان …
خیلی عالی بود. واقعا ممنونم
خیلی عالی بود🌹
بسیار عالی-سپاس بیکران از بیان شیواتون
سلام دوست عزیز
مطالبتون واقعا عالی و کاربردی بود
دست گلتون درد نکنه و خسته نباشین و تشکر از مطالب مفیدتون
عالی ممنونم
thanks
تشکر