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

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

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

Step by Step Training to Compare Two Columns in Excel

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

نحوه مقایسه ۲ ستون در اکسل ردیف به ردیف

این کار را می توانید با استفاده از تابع IF که در ادامه برای شما آورده ایم، انجام دهید:

مثال ۱-  دو ستون را برای تطبیق یا تفاوت در یک ردیف مقایسه کنید

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

فرمول matches (تطبیق ها)

برای یافتن سلول‌هایی در یک ردیف با محتوای یکسان، در این مثال A2 و B2 ، فرمول به شرح زیر می باشد:

=IF(A2=B2″Match”،””)

فرمول differences (تفاوت ها)

برای یافتن سلول‌های یک ردیف با مقادیر مختلف، به سادگی علامت مساوی را با علامت نا مساوی (<>) جایگزین کنید:

=IF(A2<>B2,”No match”,””)

Matches and differences (تطبیق ها و تفاوت ها)

حالا می توانید هم مطابقت ها و هم تفاوت ها را با یک فرمول واحد پیدا کنید:

=IF(A2=B2,”Match”,”No match”)

یا

=IF(A2<>B2,”No match”,”Match”)

نتیجه ممکن است شبیه به این به نظر برسد:

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

مثال ۲- دو لیست را برای مطابقت های حساس به حروف کوچک و بزرگ در یک ردیف مقایسه کنید

همانطور که احتمالاً متوجه شدید، فرمول های مثال قبلی هنگام مقایسه مقادیر متن، مانند ردیف ۱۰ در تصویر بالا، حروف بزرگ را نادیده می گیرد. اگر می خواهید مطابقت های حساس به حروف بزرگ بین ۲ ستون در هر سطر پیدا کنید، از تابع EXACT استفاده کنید:

=IF(EXACT(A2، B2)، “Match”، “”)

برای پیدا کردن تفاوت‌ های حساس به حروف کوچک و بزرگ در یک ردیف، متن مربوطه (“Unique” در این مثال) را در آرگومان سوم تابع IF وارد کنید، به عنوان مثال:

=IF(EXACT(A2، B2)، “Match”، “Unique”)

مقایسه چندین ستون را برای مطابقت در یک ردیف

در Worksheets های اکسل شما، چندین ستون را می توان بر اساس معیارهای زیر مقایسه کرد:

یافتن ردیف هایی با مقادیر یکسان در همه ستون ها (مثال ۱)

یافتن ردیف هایی با مقادیر یکسان در هر ۲ ستون (مثال ۲)

مثال ۱- موارد منطبق را در تمام سلول های یک ردیف پیدا کنید

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

=IF(AND(A2=B2, A2=C2), “Full match”, “”)

اگر جدول شما دارای تعداد زیادی ستون است، یک راه حل ساده تر استفاده از تابع COUNTIF است:

=IF(COUNTIF($A2:$E2, $A2)=5, “Full match”, “”)

مثال ۲- موارد منطبق را در هر دو سلول در یک ردیف پیدا کنید

اگر به دنبال راهی برای مقایسه ستون‌ها برای هر دو یا چند سلول با مقادیر مشابه در یک ردیف هستید، از فرمول IF با عبارت OR استفاده کنید:

=IF(OR(A2=B2, B2=C2, A2=C2), “Match”, “”)

در صورتی که تعداد ستون‌های زیادی برای مقایسه وجود داشته باشد، تابع OR شما ممکن است از نظر اندازه بسیار بزرگ شود. در این مورد، راه حل بهتر اضافه کردن چندین تابع COUNTIF است. COUNTIF اول شمارش می‌کند که چند ستون دارای همان مقدار ستون ۱ هستند، COUNTIF دوم شمارش می‌کند که چه تعداد از ستون‌های باقیمانده برابر با ستون ۲ هستند. اگر تعداد ۰ باشد، فرمول Match ، کلمه Unique را  برمی گرداند. مثلا:

=IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0,”Unique”,”Match”)

نحوه مقایسه دو ستون در اکسل برای مطابقت و تفاوت

فرض کنید ۲ لیست دیتا در اکسل دارید و می خواهید تمام مقادیر (اعداد، تاریخ یا رشته های متنی) را که در ستون A هستند اما در ستون B نیستند، پیدا کنید.

برای این کار، می‌توانید تابع COUNTIF($B:$B, $A2)=0 را در تست منطقی IF قرار دهید و بررسی کنید که آیا صفر (هیچ منطبقی یافت نمی‌شود) یا هر عدد دیگری (حداقل ۱ مطابق پیدا شده است) را برمی‌گرداند.

به عنوان مثال، فرمول IF/COUNTIF زیر در کل ستون B مقدار موجود در سلول A2 را جستجو می کند. اگر مطابقت پیدا نشد، فرمول (No match in B) را نمایش می دهد و در غیر این صورت یک رشته خالی نشان می دهد:

=IF(COUNTIF($B:$B, $A2)=0, “No match in B”, “”)

همین نتیجه را می توانید با استفاده از فرمول IF با توابع ISERROR و MATCH  به دست آورید:

=IF(ISERROR(MATCH($A2,$B$2:$B$10,0)),”No match in B”,””)

یا با استفاده از فرمول زیر (به یاد داشته باشید Ctrl + Shift + Enter را فشار دهید تا آن را به درستی وارد کنید):

=IF(SUM(–($B$2:$B$10=$A2))=0, ” No match in B”, “”)

اگر می‌خواهید یک فرمول واحد برای شناسایی مطابقت‌ها (تکراری‌ها) و تفاوت‌ها (مقادیر منحصربه‌فرد) بسازید، در هر یک از فرمول‌های بالا، مقداری متن برای مطابقت‌ها در دو گیومه خالی (“”) قرار دهید. مثلا:

=IF(COUNTIF($B:$B, $A2)=0, “No match in B”, “Match in B”)

نحوه مقایسه دو لیست در اکسل و پر کردن مطابقت ها

گاهی اوقات ممکن است شما نه تنها نیاز داشته باشید که دو ستون را در دو جدول مختلف مطابقت دهید، بلکه باید ورودی های منطبق را از جدول جستجو نیز بکشید. مایکروسافت اکسل یک تابع ویژه برای این ارائه می دهد: تابع VLOOKUP. به عنوان جایگزین، می توانید از فرمول INDEX MATCH قدرتمندتر و همه کاره تر استفاده کنید. کاربران اکسل ۲۰۲۱ و اکسل ۳۶۵ می توانند کار را با عملکرد XLOOKUP انجام دهند.

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

=VLOOKUP(D2، $A$2:$B$6، ۲، FALSE)

=INDEX($B$2:$B$6، MATCH($D2، $A$2:$A$6، ۰))

=XLOOKUP(D2، $A$2:$A$6، $B$2:$B$6)

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

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

مثال ۱- مطابقت ها و تفاوت ها را در هر ردیف برجسته کنید

برای مقایسه دو ستون و اکسل و برجسته کردن سلول‌های ستون A که ورودی‌های یکسانی در ستون B در همان ردیف دارند، موارد زیر را انجام دهید:

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

روی  Conditional formatting > New Rule برای استفاده از فرمولی که برای تعیین اینکه کدام سلول ها را قالب بندی کنید، کلیک کنید.

یک قانون با یک فرمول ساده مانند =$B2=$A2 ایجاد کنید (با فرض اینکه ردیف ۲ اولین سطر با داده است، بدون احتساب سرفصل ستون). لطفاً دوباره بررسی کنید که از یک مرجع ردیف نسبی (بدون علامت $) مانند فرمول بالا استفاده کنید.

برای برجسته کردن تفاوت بین ستون A و B، یک قانون با این فرمول ایجاد کنید:

=$B2<>$A2

مثال ۲- ورودی های منحصر به فرد در هر لیست را برجسته کنید

هر زمان که دو لیست را در اکسل مقایسه می کنید، ۳ مورد وجود دارد که می توانید آنها را برجسته کنید:

مواردی که فقط در لیست ۱ قرار دارند. (موارد خاص)

مواردی که فقط در لیست ۲ قرار دارند. (موارد خاص)

مواردی که در هر دو لیست هستند. (موارد تکراری)

این مثال نحوه رنگ آمیزی مواردی را که فقط در یک لیست هستند نشان می دهد.

فرض کنید لیست ۱ شما در ستون A (A2:A6) و لیست ۲ در ستون C (C2:C5) قرار دارد. قوانین قالب بندی شرطی را با فرمول های زیر ایجاد می کنید:

مقادیر منحصر به فرد را در لیست ۱ برجسته کنید (ستون A):

=COUNTIF($C$2:$C$5، $A2)=0

مقادیر منحصر به فرد را در لیست ۲ (ستون C) برجسته کنید:

=COUNTIF($A$2:$A$6، $C2)=0

و نتیجه زیر را بگیرید:

مثال ۳- موارد منطبق (تکراری) را بین ۲ ستون برجسته کنید

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

موارد منطبق در فهرست ۱ (ستون A):

=COUNTIF($C$2:$C$5، $A2)>0

موارد منطبق در فهرست ۲ (ستون C):

=COUNTIF($A$2:$A$6، $C2)>0

برجسته کردن تفاوت‌ها و مطابقت‌های ردیف را در چندین ستون

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

مثال ۱- ستون های متعدد را مقایسه کنید و مطابقت های ردیف را برجسته کنید

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

=AND($A2=$B2، $A2=$C2)

یا

=COUNTIF($A2:$C2، $A2)=3

جایی که A2، B2 و C2 در بالاترین سلول‌ها هستند و ۳ تا از تعداد ستون‌هایی که باید مقایسه شوند.

البته  فرمول AND و  COUNTIF محدود به مقایسه ۳ ستون نیستند، می توانید از فرمول های مشابه برای برجسته کردن ردیف هایی با مقادیر یکسان در ۴، ۵، ۶ یا بیشتر ستون استفاده کنید.

مثال ۲- چندین ستون را مقایسه کنید و تفاوت های ردیف را برجسته کنید

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

محدوده سلول هایی را که می خواهید مقایسه کنید، انتخاب کنید. در این مثال، سلول های A2 تا C8 را انتخاب کردیم.

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

برای تغییر ستون مقایسه، از کلید Tab برای پیمایش در سلول های مشخص از چپ به راست یا کلید Enter برای حرکت از بالا به پایین استفاده کنید.

در تب Home، به گروه Editing بروید و روی Find & Select > Go To Special کلیک کنید. سپس Row Differences را انتخاب کنید و روی دکمه OK کلیک کنید.

سلول هایی که مقادیر آنها با سلول مقایسه در هر ردیف متفاوت است، رنگی هستند. اگر می خواهید سلول های هایلایت را با رنگی سایه بزنید، به سادگی روی نماد Fill Color روی نوار کلیک کنید و رنگ مورد نظر خود را انتخاب کنید.

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

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

به عنوان مثال برای مقایسه سلول های A1 و C1 می توانید از فرمول های زیر استفاده کنید.

matches (تطبیق ها)

=IF(A1=C1، “Match”، “”)

differences (تفاوت ها)

=IF(A1<>C1, “Difference”, “”)

استفاده از افزونه Compare Two Tables

برای استفاده از افزونه Compare Two Tables شما باید این افزونه را دانلود کنید، سپس مراحل زیر را طی کنید.

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

برای مقایسه دو لیست، در اینجا مراحلی وجود دارد که باید دنبال کنید:

با کلیک کردن بر روی دکمه Compare Tables در تب Ablebits Data شروع کنید.

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

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

انتخاب کنید چه نوع دیتایی را جستجو می کنید:

مقادیر تکراری (تطابق) – مواردی که در هر دو لیست وجود دارد.

مقادیر منحصر به فرد (تفاوت ها) – مواردی که در لیست ۱ وجود دارد، اما در لیست ۲ وجود ندارد.

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

این مرحله کلیدی است که در آن ستون ها را برای روش های مقایسه دو ستون در اکسل انتخاب می کنید. در مورد ما، انتخاب واضح است زیرا ما فقط ۲ ستون را با هم مقایسه می کنیم، جمله: ۲۰۰۰ Winners against 2021 Winners. جداول بزرگتر، می توانید چندین جفت ستون را برای مقایسه انتخاب کنید.

در مرحله آخر نحوه برخورد با موارد پیدا شده را انتخاب کرده و Finish را کلیک کنید.

چند گزینه مختلف در اینجا موجود است. برای اهداف ما، این دو بسیار مفید هستند:

Highlight with color : سایه ها با رنگ مشخص مطابقت دارند یا تفاوت دارند (مانند قالب بندی شرطی اکسل).

Identify in the Status column : ستون وضعیت را با برچسب‌های “Duplicate” یا “Unique” درج کنید (مانند فرمول‌های IF).

برای این مثال، ما تصمیم گرفتیم موارد تکراری را با رنگ زیر برجسته کنیم:

و به نتیجه زیر رسیدیم:

با ستون وضعیت، نتیجه به صورت زیر خواهد بود:

سوالات متداول

آیا شناسایی تطابق و تفاوت در ستون یا ردیف، فرمول هایش فرق می کند؟

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

آیا می توانیم از چندین فرمول همزمان استفاده کرد؟

بله، شما می توانید از فرمول های مختلف در مسیر حل مسئله ای که می خواهید، استفاده کنید.

جمع بندی

ساده ترین راه برای روش های مقایسه دو ستون در اکسل استفاده از فرمول های IF و VLOOKUP و COUNTIF است. شما باید با توجه به نیاز خود از این فرمول ها استفاده کنید. همچنین می توانید برای سادگی بیشتر از افزونه های مختلف مثل Compare Two Tables استفاده کنید. این افزونه به شما کمک میکند مورد های متفاوت و یا مشابه را رنگی کنید و متمایز کنید تا شناسایی شود.

نظرات و پیشنهادات شما؟

در این مطلب از مجله خبری آی تی و دیجیتال ما به بررسی آموزش بهترین و سریع ترین روش های مقایسه دو ستون در اکسل پرداختیم. به نظر شما با بهترین روش مقایسه دو ستون در اکسل چیست؟ در بخش دیدگاه نظرات و سوالات خود را با ما در میان بگذارید.