مقایسه دو ستون در اکسل (برای مطابقت و مغایرت) [با آموزش ویدئویی]

سؤالی که بسیاری از اوقات با آن مواجه می‌شویم این است – «چگونه دو ستون را در اکسل مقایسه کنیم؟»

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

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

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

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

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

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

اگر فکر می‌کنید چیزی وجود دارد که می‌تواند به این آموزش اضافه شود، در بخش نظرات به ما اطلاع دهید.

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

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

مثال: مقایسه دو ستون در همان سطر

در زیر، مجموعه‌ای از داده‌ها وجود دارد که باید بررسی کنم آیا نام ستون A با ستون B یکسان است یا خیر.

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

فرمول زیر این کار را انجام می‌دهد:

=A2=B2

مقایسه سلول‌ها در همان سطر با نمایش TRUE

مثال: مقایسه دو ستون در همان سطر (با استفاده از فرمول IF)

اگر می‌خواهید نتیجه‌ی توصیفی‌تری بدست آورید، می‌توانید از یک فرمول ساده‌ی IF برای برگرداندن “Match” (مطابقت) در هنگام مطابقت نام‌ها و “Mismatch” (عدم مطابقت) هنگام عدم مطابقت نام‌ها استفاده کنید.

=IF(A2=B2,"Match","Mismatch")

مقایسه سلول‌ها در همان سطر (با استفاده از فرمول IF)

توجه: در صورتی که می‌خواهید مقایسه به بزرگی و کوچکی حروف حساس باشد، از فرمول IF زیر استفاده کنید:

=IF(EXACT(A2,B2),"Match","Mismatch")

با فرمول فوق، “IBM” و “ibm” دو نام مختلف در نظر گرفته می‌شوند و فرمول فوق “Mismatch” (عدم تطابق) برمی‌گرداند.

مثال:مقایسه سلول‌ها و هایلایت کردن مطابقت‌ها و مغایرت‌ها

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

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

  1. کل مجموعه داده را انتخاب کنید.
  2. روی زبانه‌ی “Home” کلیک کنید.روی زبانه‌ی
  3. در بخش Styles، بر روی گزینه‌ی “Conditional Formatting” کلیک کنید.در بخش Styles، بر روی گزینه‌ی
  4. از منوی کشویی، “New Rule” را انتخاب کنید.از منوی کشویی،
  5. در پنجره‌ی “New Formatting Rule”، بر روی “Use a formula to determine which cells to format” کلیک کنید.در پنجره‌ی
  6. در فیلد formula، این فرمول را وارد کنید: =$A1=$B1فرمول را وارد کنید
  7. بر روی دکمه‌ی Format کلیک کنید و قالبی را که می‌خواهید برای سلول‌هایی که مطابقت دارند اعمال شود، تعیین کنید.بر روی دکمه‌ی Format کلیک کنید
  8. OK را بزنید.

با این کار تمام سلول‌هایی که در هر سطر نام‌هایشان یکسان هستند، هایلایت می‌شود.

تمام سلول‌هایی که در هر سطر نام‌هایشان یکسان هستند، هایلایت می‌شود

توجه: برای هایلایت کردن مغایرت‌ها تنها کافی است در مرحله‌ی 6 فرمول را عوض کرده و  این فرمول را وارد کنید:  =$A1<>$B1

در ادامه می‌توانید قالب‌بندی مورد نظرتان را اعمال کنید.

مثال:مقایسه دو (یا چند) ستون و انتخاب (و هایلایت) سلول‌های متفاوت

برای هایلایت کردن سریع سلول‌های دارای مقادیر متفاوت در هر سطر جداگانه، می‌توانید از ویژگی Go To Special اکسل  استفاده کنید.

  1. محدوده‌ی سلول‌هایی را که می‌خواهید مقایسه کنید، انتخاب کنید. در این مثال سلول‌های A۲ تا C۸ را انتخاب کرده‌ایم.انتخاب محدوده‌ی سلول‌هایبه طور پیش فرض بالاترین سلول در محدوده‌ی انتخاب‌شده، سلول فعال است و سلول‌های سایر ستون‌های انتخاب‌شده در همان ردیف، با آن سلول مقایسه می‌شوند. همان‌طور که در تصویر بالا مشاهده می‌کنید، سلول فعال سفید است در حالی که تمام سلول‌های دیگر محدوده‌ی انتخاب‌شده هایلایت هستند. در این مثال سلول فعال A۲ است، بنابراین ستون مقایسه ستون A است.برای تغییر ستون مقایسه، از کلید Tab برای حرکت به چپ به راست میان سلول‌های انتخاب‌شده استفاده کنید یا از کلید Enter برای حرکت از بالا به پایین.نکته: برای انتخاب ستون‌های غیر مجاور، ستون اول را انتخاب کنید، Ctrl را فشار داده و نگه دارید، و سپس ستون‌های دیگر را انتخاب کنید. سلول فعال در آخرین ستون (یا در آخرین بلوک ستون‌های مجاور) قرار خواهد گرفت. برای تغییر ستون مقایسه، از کلید Tab یا Enter مطابق توضیحات فوق استفاده کنید.در زبانه‌ی Home، به بخش Editing بروید و گزینه‌ی Find & Select سپس Go To Special…‎ را کلیک کنید. حال Row differences را انتخاب کنید و بر روی دکمه‌ی OK کلیک کنید.انتخاب Row differences از پنجره‌ی Go To Special
  2. سلول‌هایی که مقادیر آن‌ها با سلول مقایسه‌شده در هر سطر متفاوت است، انتخاب می‌شوند. اگر می‌خواهید سلول‌های انتخاب‌شده را رنگی کنید، کافیست روی نماد Fill Color روی روبین کلیک کنید و رنگ مورد نظر خود را انتخاب کنید.رنگ کردن سلول‌های انتخاب‌شده

مقایسه دو ستون و هایلایت کردن مطابقت‌ها و مغایرت‌ها

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

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

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

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

مانند چیزی که در زیر نشان داده شده‌است:

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

توجه داشته باشید که لیست ستون A از لیست B بزرگ‌تر است. هم‌چنین برخی از نام‌ها در هر دو لیست وجود دارند، اما در یک ردیف قرار ندارند (مانند IBM، Adobe، Walmart).

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

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

  1. کل مجموعه داده‌ها را انتخاب کنید.
  2. بر روی زبانه‌ی Home کلیک کنید.
  3. در بخش Styles، بر روی گزینه “Condition Formatting” کلیک کنید.در بخش Styles، بر روی گزینه
  4. نشانگر را روی گزینه‌ی Highlight Cell Rules قرار دهید.
  5. بر روی Duplicate Values کلیک کنید.بر روی Duplicate Values کلیک کنید.<
  6. در پنجره‌ی Duplicate Values، مطمئن شوید “Duplicate” انتخاب شده‌است.در پنجره‌ی Duplicate Values، مطمئن شوید
  7. قالب‌بندی را تعیین کنیدقالب‌بندی را تعیین کنید
  8. OK را بزنید.

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

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

توجه: کاندیشنال فرکتینگ حساس به کوچکی و بزرگی حروف نیست؛ بنابراین “Apple” و “apple” یکسان در نظر گرفته می‌شوند و به عنوان تکراری هایلایت می‌شوند.

مثال: مقایسه دو ستون و هایلایت کردن داده‌های غیرهمسان

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

  1. کل مجموعه داده‌ها را انتخاب کنید.
  2. روی زبانه‌ی Home کلیک کنید.
  3. در بخش Styles، بر روی گزینه “Condition Formatting” کلیک کنید.در بخش Styles، بر روی گزینه
  4. مکان نما را روی گزینه‌ی Highlight Cell Rules قرار دهید.
  5. بر روی Duplicate Values کلیک کنید.بر روی Duplicate Values کلیک کنید
  6. در پنجره‌ی Duplicate Values، مطمئن شوید “Unique” انتخاب شده‌است. Duplicate Values، مطمئن شوید
  7. قالب‌بندی را تعیین کنید.قالب‌بندی را تعیین کنید.
  8. 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 (مطابقت) را برمی‌گرداند.

تیجه مقایسه دو ستون و یافتن داده‌های ناموجود (if/countif)

مقایسه دو ستون و استخراج داده‌های همسان

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

مثال: استخراج داده‌های همسان (دقیق)

به عنوان مثال، در لیست زیر می‌خواهم مقدار ارزیابی بازار (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 یک مطابقت خواهد بود (زیرا * می‌تواند نشان‌دهنده‌ی هر تعداد از کاراکترها باشد).

مشاهده‌ی ویدئو

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

40 دیدگاه دربارهٔ «مقایسه دو ستون در اکسل (برای مطابقت و مغایرت) [با آموزش ویدئویی];

  1. بسیار سپاسگزارم از محبت و دانشی که از شما آموختم انشااله موفق باشید.

    پاسخ
  2. خيلي خيلي عالي بود – فكر نمي كردم اكسل اينهمه قابليت داشته باشه ، شما هم سنگ تموم گذاشتيد – قدردان زحماتتون هستم

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

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

    پاسخ
  5. سلام خسته نباشید میگم
    خیلی خیلی ممنونم.من خیلی وقت بود دنبال همچین فرمولی میگشتم
    عالی بود زنده باشید.

    پاسخ
  6. چقدر عالی بود . واقعا ممنونم . همونی بود که دنبالش بودم و بعد از انجامش چقدر هیجان زده ام!

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

    پاسخ

دیدگاهتان را بنویسید

Item added to cart.
0 items - تومان
ضبط پیام صوتی

زمان هر پیام صوتی 5 دقیقه است