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

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

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

برای ساخت یک فرمول VLOOKUP به شکل اولیه آن، مراحل زیر را انجام دهید:

• برای lookup_value (اول آرگومان)، از بالاترین سلول لیست 1 استفاده کنید.
• برای table_array (آگومان دوم)، کل لیست 2 را ارائه کنید.
• برای col_index_num (آگومان سوم)، از 1 استفاده کنید زیرا فقط یک ستون در آرایه وجود دارد.
• برای range_lookup (آگومان چهارم)، FALSE را تنظیم کنید – مطابقت دقیق.

فرض کنید نام شرکت کنندگان در ستون A (فهرست 1) و اسامی کسانی که از مرحله صلاحیت گذرانده‌اند در ستون B (فهرست 2) دارید. شما می خواهید این 2 لیست را با هم مقایسه کنید تا مشخص شود کدام شرکت کننده از گروه A به رویداد اصلی راه یافته است. برای این کار از فرمول زیر استفاده کنید.

VLOOKUP(A2, $C$2:$C$9, 1, FALSE)=

فرمول به سلول E2 می‌رود، و سپس آن را از طریق تعداد سلول‌های موجود در لیست 1 به پایین می‌کشید.

لطفاً توجه داشته باشید که table_array با ارجاعات مطلق قفل شده است ($C$2:$C$9) به طوری که وقتی فرمول را در سلول‌های زیر کپی می کنید ثابت می‌ماند.

همانطور که می‌بینید، نام ورزشکاران واجد شرایط در ستون E نمایش داده می‌شود. برای بقیه شرکت کنندگان، یک خطای #N/A ظاهر می‌شود که نشان می‌دهد نام آن‌ها در لیست 2 موجود نیست.

دو ستون را در صفحات مختلف اکسل با استفاده از VLOOKUP مقایسه کنید

ستون‌هایی که باید مقایسه کنید همیشه در یک صفحه نیستند. در یک مجموعه داده کوچک، می‌توانید با مشاهده دو برگه در کنار هم، تفاوت‌ها را به صورت دستی تشخیص دهید.

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

با فرض اینکه لیست 1 در ستون A در Sheet1 و لیست 2 در ستون A در Sheet2 باشد، می توانید دو ستون را با هم مقایسه کنید و مطابق با این فرمول پیدا کنید:

IFNA(VLOOKUP(A2, Sheet2!$A$2:$A$9, 1, FALSE), "")=

مقایسه دو ستون و برگرداندن مقادیر مشترک (تطابق)

در مثال‌های قبلی، یک فرمول VLOOKUP را در ساده ترین شکل آن مورد بحث قرار دادیم:

IFNA(VLOOKUP(A2, $C$2:$C$9, 1, FALSE), "")=

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

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

در اکسل برای مایکروسافت 365 و اکسل 2021 که از آرایه‌های پویا پشتیبانی می‌کنند، می‌توانید از تابع FILTER برای الک کردن پویایی جاهای خالی استفاده کنید . برای این کار، از فرمول IFNA VLOOKUP به عنوان معیار FILTER استفاده کنید:

FILTER(A2:A14, IFNA(VLOOKUP(A2:A14, C2:C9, 1, FALSE), "")<>"")=

لطفاً توجه داشته باشید که در این مورد ما کل لیست 1 (A2:A14) را به آرگومان lookup_value در VLOOKUP ارائه می‌کنیم. این تابع هر یک از مقادیر جستجو را با لیست 2 (C2:C9) مقایسه می‌کند و آرایه‌ای از مطابقت‌ها و خطاهای N/A# را نشان دهنده مقادیر از دست رفته برمی‌گرداند. تابع IFNA خطاها را با رشته‌های خالی جایگزین می‌کند و نتایج را به تابع FILTER ارائه می‌کند، که جاهای خالی (<>””) را فیلتر می‌کند و آرایه‌ای از منطبق‌ها را به عنوان نتیجه نهایی خروجی می‌دهد.

همچنین، می‌توانید از تابع ISNA برای بررسی نتیجه VLOOKUP استفاده کنید و موارد ارزیابی شده را به FALSE فیلتر کنید، یعنی مقادیری غیر از خطاهای N/A#:

FILTER(A2:A14, ISNA(VLOOKUP(A2:A14, C2:C9, 1, FALSE))=FALSE)=

همین نتیجه را می توان با تابع XLOOKUP بدست آورد که فرمول را حتی ساده‌تر می‌کند. با توجه به توانایی XLOOKUP برای رسیدگی به خطاهای N/A# به صورت داخلی (اختیاری آرگومان if_not_found )، می‌توانیم بدون پوشش IFNA یا ISNA این کار را انجام دهیم:

FILTER(A2:A14, XLOOKUP(A2:A14, C2:C9, C2:C9,"")<>"")=

دو ستون را مقایسه کنید و مقادیر گمشده (تفاوت‌ها) را پیدا کنید

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

1. فرمول اصلی را برای جستجوی اولین مقدار از لیست 1 (A2) در لیست 2 ($C$2:$C$9) بنویسید:  VLOOKUP(A2، $C$2:$C$9، 1، FALSE)
2. فرمول بالا را در تابع ISNA قرار دهید تا خروجی VLOOKUP را برای خطاهای N/A# بررسی کنید. در صورت بروز خطا، ISNA پیغام TRUE و در غیر این صورت FALSE می دهد:   ISNA(VLOOKUP(A2، $C$2:$C$9، 1، FALSE))
3. برای تست منطقی تابع IF از فرمول ISNA VLOOKUP استفاده کنید. اگر آزمون به TRUE (خطای N/A#) ارزیابی شود، مقداری را از لیست 1 در همان ردیف برگردانید. اگر آزمون به FALSE ارزیابی شود (مطابقی در لیست 2 پیدا شد)، یک رشته خالی برگردانید.

فرمول کامل به این شکل است:

IF(ISNA(VLOOKUP(A2, $C$2:$C$9, 1, FALSE)), A2, "")=

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

در اکسل 365 و اکسل 2021، می توانید لیست نتایج را به صورت پویا فیلتر کنید. برای این کار، به سادگی فرمول ISNA VLOOKUP را در آرگومان include تابع FILTER قرار دهید :

FILTER(A2:A14, ISNA(VLOOKUP(A2:A14, C2:C9, 1, FALSE)))=

راه دیگر استفاده از XLOOKUP برای معیارها است – تابع رشته های خالی (“”) را برای نقاط داده از دست رفته برمی‌گرداند، و شما مقادیری را در لیست 1 که XLOOKUP رشته های خالی (“”=) برگردانده است، فیلتر می کنید:

FILTER(A2:A14, XLOOKUP(A2:A14, C2:C9, C2:C9,"")="")=

فرمول VLOOKUP برای شناسایی تطابق و تفاوت بین دو ستون

اگر می‌خواهید برچسب‌های متنی را به لیست اول اضافه کنید که نشان می‌دهد کدام مقادیر در لیست دوم موجود است و کدام نیست، از فرمول VLOOKUP همراه با توابع IF و ISNA/ISERROR استفاده کنید.

به عنوان مثال، برای شناسایی نام‌هایی که در هر دو ستون A و D و نام‌هایی که فقط در ستون A هستند، فرمول به شرح زیر است:

IF(ISNA(VLOOKUP(A2, $D$2:$D$9, 1, FALSE)), "Not qualified", "Qualified")=

در اینجا تابع ISNA خطاهای N/A# تولید شده توسط VLOOKUP را می‌گیرد و آن نتیجه میانی را به تابع IF می‌دهد تا متن مشخص‌شده را برای خطاها و متن دیگری را برای جستجوهای موفق برگرداند.

در این مثال، از برچسب‌های “Notqualified”/”Qualified” استفاده کردیم که برای مجموعه داده نمونه ما مناسب است. می‌توانید آن‌ها را با «در فهرست ۲»/«در فهرست ۲»، «در دسترس نیست»/«در دسترس» یا هر برچسب دیگری که مناسب می‌دانید جایگزین کنید.

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

یک راه دیگر برای شناسایی مطابقت‌ها و تفاوت‌ها در 2 ستون استفاده از تابع MATCH است:

IF(ISNA(MATCH(A2, $D$2:$D$9, 0)), "Not in List 2", "In List 2")=

دو ستون را با هم مقایسه کنید و یک مقدار از ستون سوم را برگردانید

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

به عنوان مثال، برای مقایسه نام های ستون های A و D در دو جدول زیر و برگرداندن زمان از ستون E، از فرمول زیر استفاده کنید:

VLOOKUP(A3, $D$3:$E$10, 2, FALSE)=

برای پنهان کردن خطاهای N/A#، از راه حل اثبات شده استفاده کنید – تابع IFNA:

IFNA(VLOOKUP(A3, $D$3:$E$10, 2, FALSE), "")=

به جای جاهای خالی، می توانید هر متنی را که می‌خواهید برای نقاط داده از دست رفته برگردانید – فقط آن را در آخرین آرگومان تایپ کنید. برای مثال:

IFNA(VLOOKUP(A3, $D$3:$E$10, 2, FALSE), "Not available")=

علاوه بر VLOOKUP، کار را می توان با چند توابع جستجوی دیگر انجام داد.

من فرمول انعطاف پذیر INDEX MATCH را ترجیح می‌دهم:

IFNA(INDEX($E$3:$E$10, MATCH(A3, $D$3:$D$10, 0)), "")=

یا از جانشین مدرن VLOOKUP – تابع XLOOKUP ، موجود در Excel 365 و Excel 2021 استفاده کنید:

XLOOKUP(A3, $D$3:$D$10, $E$3:$E$10, "")=

برای دریافت نام شرکت‌کنندگان واجد شرایط از گروه A و نتایج آن‌ها، کافی است سلول‌های خالی ستون B را فیلتر کنید:

FILTER(A3:B15, B3:B15<>"")=