مغایرت گیری کامل و چند شرطی در اکسل

سلام دوست من

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

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

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

در این آموزش می‌خواهیم یک روش کاربردی برای مغایرت‌گیری چند شرطی در Excel را یاد بگیریم که با استفاده از تابع COUNTIFS و Conditional Formatting انجام می‌شود.

مغایرت گیری کامل و چند شرطی

چرا از COUNTIFS استفاده می‌کنیم؟

تابع COUNTIFS برای شمارش سلول‌ها یا ردیف‌هایی استفاده می‌شود که چند شرط را به‌صورت هم‌زمان داشته باشند.

ساختار کلی این تابع:

=COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,...)

برای مثال می‌توانیم از Excel بخواهیم بررسی کند چند ردیف وجود دارد که:

1. تاریخ آن برابر تاریخ تراکنش فعلی باشد.
2. مبلغ واریز آن برابر مبلغ واریز تراکنش فعلی باشد.
3. مبلغ برداشت آن برابر مبلغ برداشت تراکنش فعلی باشد.

اگر نتیجه COUNTIFS برابر صفر باشد، یعنی چنین تراکنشی در جدول مقابل پیدا نشده است.

یک مثال واقعی‌تر از مغایرت بانکی

فرض کنید در گردش حساب بانک این تراکنش را داریم:

در نرم‌افزار نیز همین تاریخ ثبت شده است، اما مبلغ به اشتباه 15,500,000 وارد شده است.

از آنجا که هر سه شرط باید هم‌زمان برقرار باشند:

تاریخ = برابر
واریز = متفاوت
برداشت = برابر

COUNTIFS نمی‌تواند این ردیف را به‌عنوان یک تراکنش مشابه پیدا کند.

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

این یکی از مزیت‌های مغایرت‌گیری چندشرطی نسبت به مقایسه ساده یک ستون است.

یک نکته بسیار مهم درباره تراکنش‌های تکراری

روش COUNTIFS که در این آموزش استفاده کردیم برای بسیاری از فایل‌های مغایرت‌گیری کاملاً کاربردی است، اما یک محدودیت مهم دارد.

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

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

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

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

این موضوع در فایل‌های حسابداری بزرگ اهمیت زیادی دارد.

اگر زمان تراکنش هم در اختیار داشته باشیم چه کنیم؟

گاهی تاریخ و مبلغ برای شناسایی تراکنش کافی نیستند.

برای مثال ممکن است در یک روز چندین بار مبلغ 5,000,000 واریز شده باشد.

در این حالت می‌توان یک شرط دیگر نیز به فرمول اضافه کرد؛ مثلاً:

تاریخ + ساعت + واریز + برداشت

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

=COUNTIFS(
$E$3:$E$10,$A3,
$F$3:$F$10,$B3,
$G$3:$G$10,$C3,
$H$3:$H$10,$D3
)=0

البته بسته به ساختار فایل شما، محدوده‌ها و ستون‌ها باید متناسب با اطلاعات واقعی تغییر کنند.

تغییر رنگ فقط سلول یا کل ردیف؟

در مثال بالا محدوده:

A3:C11

را برای Conditional Formatting انتخاب کردیم.

در نتیجه تمام سلول‌های همین محدوده رنگی می‌شوند.

اما اگر بخواهید اطلاعات بیشتری در همان ردیف داشته باشید، مثلاً:

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

مثلاً:

=COUNTIFS($E$3:$E$10,$A3,$F$3:$F$10,$B3,$G$3:$G$10,$C3)=0

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

استفاده از رنگ‌های متفاوت برای دو نوع مغایرت

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

مثلاً:

مغایرت نوع اول: تراکنش در بانک وجود دارد ولی در نرم‌افزار نیست → قرمز

مغایرت نوع دوم: تراکنش در نرم‌افزار وجود دارد ولی در بانک نیست → نارنجی

با این روش تنها با نگاه کردن به فایل متوجه می‌شوید هر مغایرت مربوط به کدام سمت است.

مزیت این روش چیست؟

این روش چند مزیت مهم دارد:

• نیازی به بررسی دستی تک‌تک تراکنش‌ها نیست.
• چند شرط به‌صورت هم‌زمان بررسی می‌شود.
• مغایرت‌ها به‌صورت رنگی مشخص می‌شوند.
• اطلاعات اصلی جدول تغییر نمی‌کند.
• با تغییر اطلاعات، نتیجه Conditional Formatting نیز به‌صورت خودکار به‌روزرسانی می‌شود.
• برای فایل‌های مالی و حسابداری کاربرد زیادی دارد.

جمع‌بندی

برای مغایرت‌گیری در اکسل روش‌های مختلفی وجود دارد، اما وقتی لازم است چند ویژگی یک تراکنش را هم‌زمان با جدول دیگری مقایسه کنیم، استفاده از COUNTIFS در کنار Conditional Formatting یکی از روش‌های ساده و کاربردی است.

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

تاریخ + مبلغ واریز + مبلغ برداشت

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

=COUNTIFS($E$3:$E$10,$A3,$F$3:$F$10,$B3,$G$3:$G$10,$C3)=0

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

=COUNTIFS($A$3:$A$11,$E3,$B$3:$B$11,$F3,$C$3:$C$11,$G3)=0

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

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

این تکنیک در کنار توابعی مانند COUNTIF، COUNTIFS، SUMIF و SUMIFS می‌تواند بخش زیادی از کارهای روزمره حسابداری و کنترل اطلاعات را در Excel ساده‌تر کند.