مغایرت گیری در اکسل جزء یکی از نیاز های مالی در شرکتها می باشد که در این پست به بررسی مقایسه و مغایرت گیری دو لیست با یکدیگر با یک مثال پرداخته شده است.

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

مقایسه و مغایرت گیری دو لیست با یکدیگر

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

=SUMPRODUCT(–(D2:D8=B2:B8))

که در صورت استفاده از این فرمول نتیجه برابر با عدد ۳ می باشد به معنی آن که تنها سه کد فاکتور در دو لیست در ردیف یکسان با هم قرار دارند.نکته حائز اهمیت در فرمول فوق استفاده از دو علامت منفی قبل از پرانتز می باشد که به معنی دوبار ضرب عدد ۱- در مقدار داخل پرانتز است (البته میتوانستیم به جای دو علامت منفی عدد ۱ را در پرانتض ضرب نماییم) به منظور تبدیل مقادیر TRUE یا FALSE حاصل از مقایسه دو لیست به اعداد ۰ یا ۱ می باشد، لذا در ردیف هایی که حاصل داخل پرانتز برابر با FALSE است با ضرب دو علامت منفی در آن به عدد صفر و در حالتی که حاصل داخل پرانتز TRUE است با ضرب دو علامت منفی در آن به عدد یک تبدیل می شود.

در واقع حاصل عبارت داخل پرانتز به صورت ذیل می باشد (به معنی آن که ردیف اول، سوم و چهارم هر دو لیس عینا یکسان می باشند)

{TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE}

که بعد از ضرب شدن در دو علامت منفی به حالت ذیل تغییر میابد:

{۱;۰;۱;۱;۰;۰;۰}

و در نهایت اعداد ۱ با یکدیگرجمع می شوند و حاصل برابر با ۳ به دست می آید.

در پاسخ به سوال دوم مبنی بر تعداد رکوردهای مشترک در دو لیست میتوان به صورت مشابه از فرمول ذیل استفاده نمود.

=SUMPRODUCT(COUNTIF(D2:D8,B2:B8))

که در صورت استفاده از این تابع نتیجه برابر با ۴ به دست می آید.

در این حالت D2 تا D8 به عنوان محدوده پایه در نظر گرفته می شود و تک تک سلولهای محدوده B2 تا B4 درون آن جست و جو می شوند و تعداد تکرار هر یک در محدوده D2 تا D8 به عنوان خروجی نمایش داده میشود.

لذا حاصل عبارت داخل پرانتز برابر با ذیل می باشد.

{۱;۰;۱;۱;۰;۰;۱}

به معنی آن که رکوردهای اول، سوم، چهارم و هفتم از محدوده B2 تا B7 عینا در محدوده D2 تا D7 تکرار شده اند.