مغایرت گیری با اکسل یکی از فعالیتهای عمده حسابداران و کارشناسان مالی است که به منظور شناسایی عدم تطابق ها در حسابهای ثبت شده در نرم افزار حسابداری خود و حسابهای بانکی یا تعیین عدم انطباق ها با حسابهای ثبت شده با فاکتورهای خرید یا فروش استفاده میشود.

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

به منظور شناسایی مغایرتها در حسابهای مختلف، روشهای متعددی وجود دارد که یکی از مهمترین روشها استفاده از دستور Conditional Formatting است که در این پست حالت پایه آن آموزش داده شده است.

مثال مغایرت گیری

فرض کنیم مطابق با شکل ذیل، بعد از حذف ستونهای اضافی اطلاعات حسابهای ثبت شده در نرم افزار در سه ستون A، B و C  آورده شده باشد و اطلاعات حساب بانکی در سه ستون F، G و H آورده شده باشد.

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

۱- دریافت و پرداخت از بانک با تاریخی متفاوت از تاریخ دریافت یا پرداخت (با فاصله زمانی چند روز) درون نرم افزار ثبت شود.

۲- دریافت و پرداختها به صورت تجمیعی درون نرم افزار وارد شود به گونه ای که سه پرداخت صورت گرفته در یک روز در یک سند درون نرم افزار ثبت  شود.

لذا حالات ذیل باعث سخت شدن مسئله مغایرت گیری میشود. در تصویر ذیل سندهایی که در تاریخ مشابه درون نرم افزار ثبت شده است با فلش سبز، سندهایی که با تاریخ متفاوت درون نرم افزار ثبت شده است با فلش آبی و سندهایی که به صورت تجمیعی درون نرم افزار ثبت شده است با فلش زرد رنگ نشان داده شده است. در نهایت سلول سندهای دارای مغایرت درون نرم افزار به رنگ قرمز نمایش داده شده است. و هدف از حل مسئله مغایرت گیری پیدا کردن سلولهای به رنگ قرمز است.

حل مسئله مغایرت گیری در اکسل

میخواهیم طی آموزشهای متوالی، به حل مسئله مغایرت گیری بپردازیم و رکوردهای بدون مغایرت را شناسایی کنیم و با استفاده از دستور Conditional Formatting آنها را به رنگ سبز در بیاوریم تا در نهایت رکوردهای باقی مانده را شناسایی و اصلاح نماییم.

در گام اول باید داده های مربوط به اطلاعات نرم افزار را انتخاب نموده و از تب Home دستور Conditional ّFormatting را برگزیده و از بین حالات، حالت New Rule را انتخاب کرده تا پنجره تعریف Rule باز شود، سپس از بین حالات موجود، حالت Use a formulas to determine را انتخاب کرده تا امکان ورود فرمول مطابق با تصویر ذیل فراهم شود.

سپس درون قسمت ثبت فرمول، فرمول ذیل را وارد نموده

=COUNTIFS($F:$F,$A4,$G:$G,$B4,$H:$H,$C4)>0

و با کلیک کردن بر روی گزینه Format از تب Fill از پنجره باز شده، رنگ مد نظر را انتخاب نموده تا نتیجه ذیل حاصل شود.

در این فرمول گفته شده است چه تعداد رکوردهای بانکی هستند که تاریخ آنها برابر با تاریخ ثبت شده در سلول A4، و میزان واریز آنها برابر با عدد ثبت شده درون سلول B4 و میزان برداشت آن برابر با عدد ثبت شده درون سلول C4 است، تعداد آنها شمرده شود و در صورت بزرگتر از صفر بودن، ردیف ۴ در محدوده اطلاعات نرم افزار رنگی شود. به عبارت دیگر با این فرمول هر ردیفی از نرم افزار که حداقل یک بار در محدوده بانکی عینا تکرار شده باشد، رنگی میشود.

با تکرار فرایند فوق و انتخاب محدوده اطلاعات بانک و وارد نمودن فرمول ذیل در قسمت ثبت فرمود در Conditional Formatting

=COUNTIFS($A:$A,$F4,$B:$B,$G4,$C:$C,$H4)>0

نتیجه حاصله به صورت ذیل خواهد بود.

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

=COUNTIFS($F:$F,$A4,$G:$G,$B4,$H:$H,$C4)=COUNTIFS($A:$A,$A4,$B:$B,$B4,$C:$C,$C4)

=COUNTIFS($A:$A,$F4,$B:$B,$G4,$C:$C,$H4)=COUNTIFS($F:$F,$F4,$G:$G,$G4,$H:$H,$H4)

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

همانطور که مشاهده میشود در این حالت تمامی رکوردهایی که در قسمت بانک و قسمت نرم افزار در یک تاریخ و با تعداد برابر ثبت شده است مشخص شده اند و سایر رکوردها به صورت مغایرت شناسایی شده اند در حالی که این طور نیست. این پست شامل پایه ترین آموزش مغایرت گیری می باشد و آموزشهایی پیشرفته تر برای شناسایی سایر رکوردهای فاقد مغایرت در پست های بعدی ارائه شده است.

دانلود فایل اکسل