چگونه دو ستون را در اکسل مقایسه کنیم؟ همه روش ها

منظور شما از مقایسه ستون‌ها در اکسل چیست؟

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

با توجه به مبانی مورد بحث، می‌توانید در ادامه قسمت عملی را .

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

در اینجا چندین روش موثر برای مقایسه دو ستون در اکسل وجود دارد:

• قالب بندی شرطی در اکسل – Conditional Formatting
• با استفاده از اپراتور Equals
• با استفاده از تابع VLookUp
• با استفاده از فرمول IF
• با استفاده از فرمول EXACT

قالب بندی شرطی در اکسل (Conditional Formatting)

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

مرحله 1

تمام سلول‌های صفحه گسترده را انتخاب کنید

گام 2

قالب‌بندی شرطی

به گزینه “Home” بروید و مقادیر تکراری را در نوار ابزار انتخاب کنید. بعد، به Conditional Formatting در گزینه Excel بروید.

یک پنجره جدید با گزینه هایی برای انتخاب مقادیر “Duplicate” و “Unique” روی صفحه ظاهر می‌شود. می‌توانید دو ستون را با مقادیر منطبق یا مقادیر منحصر به فرد مقایسه کنید.

مقادیر تکراری

مقادیر منحصر به فرد

با استفاده از اپراتور Equals

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

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

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

همانطور که در زیر نشان داده شده است، می‌توانید تغییرات جزئی در فرمول انجام دهید تا پیام‌های سفارشی‌شده را با استفاده از عبارت «IF» با پیام‌های سفارشی برای مقادیر «TRUE» و «FALSE» تحویل دهید.

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

در ادامه می‌توانید از تابع VLookUp در اکسل برای مقایسه ستون‌ها در اکسل استفاده کنید.

با استفاده از تابع VLOOKUp

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

برای استفاده از عملکرد VLookUp برای مقایسه سلول‌ها، مراحل دقیق ذکر شده در زیر را دنبال کنید.

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

می‌توانید آثار مقایسه‌های ناموفق را که به صورت خطا نمایش داده می‌شوند، ببینید. می‌توانید فرمول خود را با استفاده از تابع “IFERROR” تغییر دهید تا از چنین خطاهایی جلوگیری کنید، همانطور که در زیر نشان داده شده است.

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

برخلاف داده‌های نمونه که ارائه شده، سناریوهای دنیای واقعی ممکن است کمی متفاوت باشند. برای مثال، مقایسه بین دو سلول منفرد ممکن است به “FALSE” ختم شود، حتی اگر هر دو سلول داده‌های یکسانی را نشان دهند. حال با کمک تصویر زیر بهتر به آن نگاه کنید.

داده‌های ستون 1 و ستون 3 یکسان است. اما، یک پسوند اضافی برای چند سلول وجود دارد.

مثال: فورد هند در خانه اول ستون 1 و فورد در خانه اول ستون 3.

VLookUp استاندارد ممکن است در نتیجه “FASLE” را ارائه دهد. همانطور که در زیر نشان داده شده است، می‌توانید از حروف عام به عنوان یک تغییر جزئی در داده‌ها برای جلوگیری از این امر استفاده کنید.

اکنون سلول فرمول اصلاح شده را به تمام سلول‌ها بکشید و داده‌های حاصل به صورت زیر نشان داده می‌شود.

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

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

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

فرمول IF: =IF(A2=B2،”Match”،””)

اگر می‌خواهید نتیجه به صورت زیر باشد، مثال بالا را در نظر بگیرید:

• “مارک‌های مختلف خودرو” در صورتی که نام برندها مطابقت نداشته باشد
• اگر نام برندها مطابقت داشته باشد، “مارک‌های خودروی مشابه”.

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

ما از فرمول استفاده خواهیم کرد: “=IF(A2=B2، “مارک‌های مشابه خودرو، “مارک‌های مختلف خودرو”).”

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

نتیجه در ستون D در تصویر زیر نشان داده شده است.

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

برای استفاده از فرمول EXACT برای مقایسه دو ستون در اکسل، می‌توانیم از فرمول زیر برای مثال بالا استفاده کنیم:

“=EXACT(A2، B2)”

اگر مقدار یک ستون دقیقاً مشابه ستون دیگر باشد، نتیجه به صورت “درست” نمایش داده می‌شود و اگر مقادیر برابر نباشند، نتیجه “نادرست” خواهد بود.

باید در نظر داشت که فرمول EXACT به حروف کوچک و بزرگ حساس است.

اگر «هوندا» را در دو حالت مختلف بنویسیم و فرمول «=EXACT(A12, B12)» را اعمال کنیم، نتیجه «نادرست» به دست می‌آید، در حالی که اگر حروف در هر دو سلول یکسان باشد، نتیجه «نادرست» خواهد بود. درست است، واقعی.”

از کدام روش در هر سناریو استفاده شود

روش‌های مقایسه دو ستون در اکسل را یاد گرفته ایم. در ادامه توضیحاتی می‌دهیم که به شما در انتخاب روش برای سناریوهای خاص کمک می‌کند:

سناریوی 1: مقایسه دو ستون در اکسل ردیف به ردیف

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

• =IF(A2 = B2، «مطابقت»، «»)
• =IF(A2<>B2، “بدون مطابقت”، ” “)
• =IF(A2 = B2، “مطابقت”، “بدون مطابقت”)

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

• =IF(EXACT(A2، B2)، “Match”، ” “)
• =IF(EXACT(A2، B2)، “Match”، “بدون مطابقت”)

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

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

• =IF(AND(A2=B2، A2=C2)، “تطابق کامل”، ” “)
• =IF(COUNTIF($A2:$E2, $A2)=4، “تطابق کامل،”)،

که در آن 4 تعداد ستون هایی است که شما مقایسه می‌کنید

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

• =IF(OR(A2=B2، B2=C2، A2=C2)، “مطابقت”، “”)
• =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0″یکتا”،”مطابقت”)

سناریو 3: مقایسه دو ستون برای مطابقت و تفاوت

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

• =IF(COUNTIF($B:$B، $A2)=0، “در B موجود نیست”، “”)
• =IF(ISERROR(MATCH($A2,$B$2:$B$10,0))،”هیچ موجودی در B وجود ندارد”،””)

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

• =IF(COUNTIF($B:$B، $A2)=0، “عدم وجود در B”، “ارائه در B”)

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

برای مقایسه دو لیست و یافتن داده‌های منطبق، می‌توانید از تابع VLOOKUP استفاده کنید. همچنین می‌توانید از فرمول INDEX MATCH استفاده کنید. برای این سناریو می‌توانید از فرمول‌های زیر استفاده کنید:

• =VLOOKUP(D2، $A$2:$B$6، 2، FALSE)
• =INDEX($B$2:$B$6، MATCH($D2، $A$2:$A$6، 0))
• =XLOOKUP(D2، $A$2:$A$6، $B$2:$B$6)

A2، B2 و D2 اولین خانه‌های سه ستون هستند. 2 تعداد ستون‌های مقایسه شده است.

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

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

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

یا

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

3 تعداد ستون‌ها و A2، B2 و C2 بالاترین سلول‌ها برای مقایسه هستند.

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

1. ستون هایی را با مجموعه دادهای که می‌خواهید مقایسه کنید انتخاب کنید.

2. به بخش ویرایش گروه در تب Home بروید، روی منوی کشویی Find and Select کلیک کنید و گزینه Go To Special را انتخاب کنید. Row Differences را انتخاب کرده و روی OK کلیک کنید.

3. سلول‌هایی که مقادیر متفاوتی نسبت به سلول‌های مقایسه شده در هر ردیف دارند رنگ می‌شوند. برای تغییر رنگ روی نماد Fill Color کلیک کنید و رنگ مورد نظر خود را انتخاب کنید.

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

1. چگونه دو ستون را در اکسل مقایسه کنیم؟

یکی از روش‌های محبوب برای مقایسه دو ستون در اکسل این است که مراحل زیر را دنبال کنید: هر دو ستون داده را انتخاب کنید ← به تب Home بروید ← روی Find & Select کلیک کنید ← Go To Special ← انتخاب Row Differences ← Ok کلیک کنید.

2. آیا می‌توان دو ستون را در اکسل با استفاده از تابع Index-Match مقایسه کرد؟

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

3. چگونه چندین ستون را در اکسل مقایسه کنیم؟

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

4. چگونه دو لیست را در اکسل برای مطابقت با هم مقایسه می‌کنید؟

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

5. چگونه دو ستون را در اکسل مقایسه کنم و موارد تکراری را برجسته کنم؟

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

1. دو ستونی را که می‌خواهید مقایسه کنید انتخاب کنید.
2. به تب Home بروید و روی Conditional Formatting کلیک کنید.
3. “Highlight Cells Rules” را انتخاب کنید و “Duplicate Values” را از منوی کشویی انتخاب کنید.
4. در کادر محاورهای Duplicate Values، مطمئن شوید که “Duplicate” انتخاب شده است.
5. یک سبک قالب بندی را انتخاب کنید یا سبک پیش فرض را ترک کنید.
6. روی OK کلیک کنید.

سپس اکسل مقادیر تکراری را در ستون‌های انتخاب شده برجسته می‌کند و شناسایی آنها را آسان می‌کند.