مغایرت‌گیری بانکی در اکسل؛ آموزش مرحله‌به‌مرحله با مثال و فایل نمونه

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

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

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

مغایرت‌گیری بانکی چیست؟

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

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

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

در ادامه، تمرکز اصلی ما بر همین تطبیق تراکنش‌ها در اکسل است.

چرا بهتر است مغایرت‌گیری بانکی را در اکسل انجام دهیم؟

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

• واریزی‌های ثبت‌شده در بانک را با فهرست رسیدها مقایسه کنید.

• تراکنش‌هایی را که در یکی از دو فهرست وجود ندارند، پیدا کنید.

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

• مغایرت‌ها را دسته‌بندی کنید و وضعیت پیگیری هر مورد را ثبت کنید.

• در پایان، گزارشی قابل بررسی برای حسابدار یا مدیر مالی تهیه کنید.

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

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

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

جدول اول: گردش حساب بانک

اطلاعات گزارش بانک را با ستون‌های زیر وارد کنید:

اطلاعات جدول بالا فرضی است و صرفاً برای آموزش استفاده می‌شود.

جدول دوم: رسیدها یا سوابق داخلی

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

این مثال دو نکته مهم دارد: برخی شناسه‌ها در هر دو فهرست یکسان‌اند، اما در بعضی موارد شناسه روی رسید با شناسه ثبت‌شده در بانک تفاوت دارد. همچنین ممکن است برای بعضی تراکنش‌های بانکی هیچ رسیدی پیدا نشود.

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

مرحله اول: اطلاعات دو فایل را یکسان‌سازی کنید

قبل از نوشتن فرمول، مطمئن شوید اطلاعات دو جدول قابل مقایسه‌اند. بسیاری از مغایرت‌های ظاهری به دلیل تفاوت در قالب اطلاعات رخ می‌دهند.

واحد مبلغ: بررسی کنید یک فایل مبلغ را به تومان و فایل دیگر به ریال ثبت نکرده باشد. برای مثال، ۱,۵۰۰,۰۰۰ تومان برابر با ۱۵,۰۰۰,۰۰۰ ریال است.

تاریخ تراکنش: فرمت تاریخ‌ها باید سازگار باشد. ممکن است تاریخ در یک فایل به شکل تاریخ واقعی اکسل و در فایل دیگر به‌صورت متن ذخیره شده باشد. ظاهر یکسان سلول‌ها لزوماً به معنی یکسان بودن مقدار داخلی آن‌ها نیست.

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

تراکنش‌های تکراری: وجود دو واریزی به مبلغ یکسان در یک روز امکان‌پذیر است. بنابراین مبلغ یکسان، به‌تنهایی برای اثبات اینکه دو ردیف متعلق به یک تراکنش هستند کافی نیست.

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

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

فرض کنید شناسه بانک در سلول D2 قرار دارد و شناسه‌های شیت رسیدها در ستون D ثبت شده‌اند. فرمول زیر را در ستون نتیجه شیت گردش_بانک وارد کنید:

=IF(D2="","شناسه ندارد",IF(COUNTIF('رسیدها'!$D$2:$D$1000,D2)=0,"بدون تطبیق شناسه",IF(COUNTIF('رسیدها'!$D$2:$D$1000,D2)>1,"شناسه تکراری در رسیدها","یک شناسه متناظر پیدا شد")))

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

• اگر شناسه خالی باشد، بررسی با شناسه امکان‌پذیر نیست.

• اگر هیچ شناسه مشابهی در شیت رسیدها پیدا نشود، باید راه دیگری برای بررسی انتخاب کنید.

• اگر یک یا چند مورد مشابه پیدا شود، باید تعداد موارد را بررسی کنید؛ شناسه تکراری را نباید بدون کنترل، تطبیق قطعی در نظر گرفت.

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

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

مرحله سوم: تطبیق بر اساس مبلغ و تاریخ

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

فرض کنید در شیت گردش_بانک، ستون A تاریخ و ستون B مبلغ را نگه می‌دارد. در شیت رسیدها نیز همین ساختار وجود دارد.

فرمول زیر را در ستون نتیجه شیت گردش_بانک وارد کنید:

=IF(COUNTIFS('رسیدها'!$A$2:$A$1000,A2,'رسیدها'!$B$2:$B$1000,B2)=0,"بدون نامزد",IF(OR(COUNTIFS('رسیدها'!$A$2:$A$1000,A2,'رسیدها'!$B$2:$B$1000,B2)>1,COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,B2)>1),"چند مورد مشابه؛ بررسی دستی","یک نامزد؛ بررسی شناسه و شرح"))

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

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

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

مرحله چهارم: واریزی‌های بدون رسید را پیدا کنید

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

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

برای هر مورد، این اطلاعات را بررسی کنید:

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

2. شرح تراکنش و شناسه‌های موجود.

3. رسیدهای بایگانی‌شده یا سوابق داخلی.

4. احتمال اختلاف زمان ثبت یا ناقص بودن گزارش.

5. نتیجه نهایی بررسی و علت احتمالی مغایرت.

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

مرحله پنجم: رسیدهای بدون تراکنش متناظر را هم پیدا کنید

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

برای تکمیل مغایرت‌گیری، همین بررسی را در جهت عکس نیز انجام دهید: این بار هر رسید را با اطلاعات شیت گردش بانک مقایسه کنید.

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

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

مرحله ششم: مغایرت‌های بانکی را دسته‌بندی کنید

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

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

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

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

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

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

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

۱. تطبیق صرفاً بر اساس مبلغ: چند مشتری ممکن است در یک روز مبالغ یکسانی واریز کنند. برای تشخیص درست، از شناسه مشترک و اطلاعات تکمیلی استفاده کنید.

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

۳. یکسان نبودن واحد پول: تفاوت تومان و ریال می‌تواند باعث ایجاد مغایرت‌های بزرگ و ظاهری شود.

۴. ناهماهنگی تاریخ‌ها: تفاوت نوع داده یا زمان ثبت تراکنش ممکن است نتیجه مقایسه را تغییر دهد.

۵. بررسی فقط یک جهت: برای تکمیل بررسی، هم تراکنش‌های بدون رسید و هم رسیدهای بدون تراکنش متناظر را پیدا کنید.

۶. تأیید قطعی بر اساس فرمول: فرمول اکسل به کیفیت داده‌های ورودی وابسته است. موارد مبهم باید با گزارش بانک و اسناد اصلی بررسی شوند.

آیا می‌توان مغایرت‌گیری بانکی را به‌صورت خودکار انجام داد؟

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

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

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

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

جمع‌بندی

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

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

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

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

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

چگونه واریزی‌های بدون رسید را پیدا کنیم؟

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

آیا مبلغ و تاریخ برای تطبیق تراکنش کافی است؟

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

چرا یک تراکنش با وجود مبلغ یکسان تطبیق داده نمی‌شود؟

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

آیا صورت مغایرت بانکی با تطبیق رسیدها یکسان است؟

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