لوگوی تهران حسابتهران حسابحسابداری، مالی و هوش مصنوعی
ورودثبت نام

مقاله

تطبیق صورت‌حساب بانکی با Power Query در اکسل؛ جدول کنترل قابل بازبینی

تهران حساب
تطبیق صورت‌حساب بانکی با Power Query در اکسل؛ جدول کنترل قابل بازبینی

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

مسئله واقعی در مغایرت بانکی چیست؟

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

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

طراحی داده پیش از شروع Power Query

پیش از باز کردن فایل‌ها، یک قرارداد داده کوتاه بنویسید. این قرارداد مانع آن می‌شود که هر ماه روش تطبیق عوض شود یا گزارش با تغییر جزئی قالب بانک از کار بیفتد. برای هر منبع، یک جدول مجزا نگه دارید: Bank_Raw برای فایل دریافتی بانک و Ledger_Raw برای ریز دفتر. داده خام را در همان شکل اصلی نگه دارید و پاک‌سازی را در پرس‌وجو انجام دهید؛ ویرایش دستی داخل فایل خام، ردگیری تغییرات را ضعیف می‌کند.

ستون‌های حداقلی پیشنهادی

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

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

برای مبلغ علامت‌دار، ابتدا قاعده را روشن کنید: آیا ستون بدهکار بانک معادل پرداخت از حساب است یا دریافت؟ همین قاعده باید در دفتر نیز هم‌راستا شود. مقدار ۲۵۰٬۰۰۰ بدون جهت، برای ادغام امن کافی نیست؛ +۲۵۰٬۰۰۰ و −۲۵۰٬۰۰۰ دو رویداد متفاوت‌اند.

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

تطبیق صورت‌حساب بانکی با Power Query، گام‌به‌گام

برای فایل CSV از مسیر Data > Get Data > From File > From Text/CSV وارد شوید و برای ریزدفتر اکسل از From Table/Range یا From Excel Workbook استفاده کنید. مایکروسافت توضیح می‌دهد که هنگام ورود CSV، تشخیص جداکننده، سرستون و نوع ستون به‌صورت خودکار انجام می‌شود؛ اما این تشخیص را نباید بدون کنترل بپذیرید، به‌خصوص برای تاریخ، صفرهای ابتدای شماره‌ها و مبلغ‌ها.

۱) پاک‌سازی را در دو پرس‌وجوی جدا انجام دهید

دو پرس‌وجو با نام‌های روشن، مثلاً q_Bank_Clean و q_Ledger_Clean بسازید. در هرکدام سرستون‌ها را تثبیت، ستون‌های غیرلازم را حذف، فاصله‌های اضافه شرح و شناسه را پاک و نام ستون‌ها را یکسان کنید. اگر صورت‌حساب هر ماه در پوشه‌ای با ساختار ثابت قرار می‌گیرد، اتصال پوشه می‌تواند فایل‌های مشابه را به یک جدول ضمیمه کند؛ با این حال، ابتدا روی چند نمونه کنترل کنید که سرستون، ترتیب ستون و ردیف‌های پایانی در همه فایل‌ها یکسان باشد.

در Power Query از View، ابزارهای کیفیت، توزیع و پروفایل ستون را فعال کنید. این ابزارها تعداد مقدارهای معتبر، خطادار و خالی و نیز توزیع مقادیر را نشان می‌دهند و برای کشف شناسه خالی، مبلغ متنی یا شرح غیرعادی مفیدند. نام هر مرحله را نیز معنادار کنید؛ مانند «تبدیل تاریخ بانک» یا «حذف سطرهای جمع». مستندات مایکروسافت توصیه می‌کند برای پرس‌وجوها و مرحله‌ها نام و توضیح ثبت شود تا نگهداری راه‌حل آسان‌تر شود.

۲) نوع داده و تنظیم منطقه‌ای را صریح کنترل کنید

تاریخ و مبلغ را فقط با نگاه به پیش‌نمایش تأیید نکنید. اگر فایل تاریخ 03/04/2026 دارد، ممکن است بسته به تنظیم منطقه‌ای سوم آوریل یا چهارم مارس خوانده شود. در منوی تغییر نوع داده، گزینه Using Locale را به‌کار ببرید و منطقه‌ای را انتخاب کنید که واقعاً با فایل منبع سازگار است. مستندات مایکروسافت تصریح می‌کند که تنظیم منطقه‌ای در تفسیر متن برای تبدیل به نوع داده مؤثر است و برای اطمینان از تاریخ تبدیل‌شده می‌توان ستون‌های روز، ماه و سال را جداگانه کنترل کرد.

مبالغ را به Decimal Number یا Fixed Decimal Number تبدیل کنید و پیش از آن، جداکننده هزارگان، علامت منفی، پرانتزهای نشان‌دهنده مبلغ منفی و نماد پول را بررسی کنید. شناسه‌های تراکنش را معمولاً Text نگه دارید تا صفرهای ابتدای آن از دست نرود. مبلغ خالی را خودسرانه صفر نکنید؛ آن را به‌عنوان خطای داده یا وضعیت نیازمند رسیدگی پرچم بزنید.

۳) تکراری‌ها را حذف نکنید؛ ابتدا آشکارشان کنید

دو سطر با کلید یکسان می‌تواند کپی ناخواسته باشد، اما می‌تواند دو تراکنش واقعی هم باشد. ابتدا بر اساس کلید تطبیق، Group By انجام دهید و تعداد سطر و جمع مبلغ را به خروجی کنترل اضافه کنید. اگر شمارش بیش از یک شد، وضعیت «تکراری یا چندتایی» بدهید. حذف تکراری فقط وقتی مجاز است که شواهدی مانند شناسه یکتای یکسان، زمان ورود یکسان و تأیید منبع وجود داشته باشد. در غیر این صورت، سطرها باید در گزارش استثناها باقی بمانند.

۴) ادغام را با کلیدهای هم‌نوع و محافظه‌کارانه انجام دهید

از Merge Queries as New استفاده کنید تا جدول کنترل از دو پرس‌وجوی پاک‌شده ساخته شود و منابع اولیه دست‌نخورده بمانند. پیش از ادغام، نوع داده ستون‌های کلید در دو طرف باید یکسان باشد؛ مستندات مایکروسافت هشدار می‌دهد که تفاوت نوع داده می‌تواند نتیجه ادغام را نادرست کند. ترتیب انتخاب چند ستون هم در هر دو طرف باید یکسان باشد.

برای گزارش کامل ماه، Full outer join مناسب است: همه سطرهای بانک و دفتر را نگه می‌دارد، سپس با بازکردن ستون جدول مقابل، وضعیت می‌سازید. در مستندات Power Query، Full outer به‌عنوان نگهدارنده همه سطرهای هر دو جدول تعریف شده است. برای تهیه فهرست جداگانه، از Left anti join روی بانک برای «بانک بدون دفتر» و روی دفتر برای «دفتر بدون بانک» استفاده کنید. تطبیق فازی شرح را فقط برای تولید فهرست بررسی به کار ببرید؛ چون شباهت متنی، مدرک حسابداری برای انطباق نیست.

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

فرض کنید در بانک سه رویداد دارید: پرداخت ۱۲٬۵۰۰٬۰۰۰ ریالی با مرجع ۸۸۱۲، دریافت ۳۰٬۰۰۰٬۰۰۰ ریالی با مرجع ۵۰۳۱ و کارمزد ۱۲۰٬۰۰۰ ریالی. در دفتر، پرداخت و دریافت با همان مرجع ثبت شده‌اند، اما کارمزد ثبت نشده است. پس از یکسان‌سازی علامت مبلغ و ساخت کلید «مرجع | مبلغ»، دو ردیف نخست وضعیت «منطبق» می‌گیرند و کارمزد در خروجی «بانک بدون دفتر» باقی می‌ماند.

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

جدول کنترل و کنترل مانده بانک

خروجی نهایی را به‌صورت یک جدول Excel بارگذاری کنید، نه صرفاً اتصال. ستون‌های پیشنهادی آن عبارت‌اند از: کلید تطبیق، تاریخ بانک، تاریخ دفتر، مبلغ بانک، مبلغ دفتر، مرجع بانک، شماره سند، وضعیت تطبیق، علت استثنا، اقدام بعدی، مسئول، تاریخ بررسی و تأییدکننده. از این جدول سه نما تهیه کنید: «منطبق»، «بانک بدون دفتر» و «دفتر بدون بانک». یک شمارش جداگانه هم برای کلیدهای چندتایی و خطاهای تبدیل داشته باشید.

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

چک‌لیست اجرای ماهانه و مرز اتوماسیون

  • فایل خام بانک و ریز دفتر دوره را با نام و تاریخ نگهداری کنید.
  • بازه تاریخ، حساب بانکی و واحد پول دو منبع را تأیید کنید.
  • پرس‌وجوها را Refresh کنید و خطاهای نوع داده، ستون‌های خالی و کلیدهای چندتایی را ببینید.
  • فهرست‌های منطبق، بانک بدون دفتر و دفتر بدون بانک را خروجی بگیرید.
  • برای هر استثنا علت، اقدام، مسئول و شواهد رسیدگی ثبت کنید.
  • کارمزد، سود، برگشت، چک در راه و تراکنش‌های پایان دوره را جداگانه مرور کنید.
  • جمع استثناها و مانده تعدیل‌شده را با مانده دفتر کنترل کنید.
  • بازبینی و تأیید نهایی را، متناسب با ساختار سازمان، از ثبت و پرداخت تفکیک کنید.

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

منابع و مطالعه بیشتر

مطالعه بیشتر

همه مقالات