تهیه تراز آزمایشی چهارستونی در Excel با استفاده از توابع SUMIF و ابزارهای مرتبسازی و فیلتر
مقدمه
تراز آزمایشی یکی از مهمترین گزارشهای میانی در فرآیند حسابداری است که نقش آن کنترل توازن مانده حسابها قبل از تهیه صورتهای مالی است. در بسیاری از واحدهای اقتصادی، ثبت اسناد حسابداری در اکسل انجام میشود و نیاز است از همین اطلاعات خام، تراز آزمایشی چهارستونی (مانده بدهکار – مانده بستانکار – گردش بدهکار – گردش بستانکار) بهصورت پویا، تمیز و قابل بهروزرسانی تهیه شود.
در این مقاله، با استفاده از توابع SUMIF و قابلیتهای Sort و Filter، یک روش کاربردی و ساده برای تهیه تراز را توضیح میدهیم.
مرحله ۱ – آمادهسازی جدول اسناد حسابداری
در اکسل، فرض میکنیم جدولی با ساختار زیر داریم:
| تاریخ | شماره سند | کد حساب | نام حساب | شرح | بدهکار | بستانکار |
|---|
نکتههای مهم:
حتماً کد حساب و نام حساب استاندارد و یکسان وارد شود.
ستونها باید Data Type صحیح داشته باشند (اعداد عدد، متن متن).
از فاصله اضافی، نیمفاصله اشتباه و دوگانگی کاراکترهای فارسی جلوگیری شود (در صورت نیاز: Clean، Trim، Replace و …).
مرحله ۲ – استخراج و تهیه لیست یکتا از کد حسابها
برای اینکه تراز آزمایشی ستونبندی دقیقی داشته باشیم، ابتدا باید لیست یکتا (Unique) از کد حسابها تهیه کنیم.
در Excel 365:
=UNIQUE(C:C)
در نسخههای قدیمی، میتوان از Advanced Filter یا Remove Duplicates استفاده کرد.
مرحله ۳ – محاسبه گردش بدهکار و بستانکار با SUMIF
در کنار لیست یکتای حسابها، ستونهای زیر را ایجاد کنید:
| کد حساب | نام حساب | گردش بدهکار | گردش بستانکار | مانده بدهکار | مانده بستانکار |
فرمول گردش بدهکار:
=SUMIF($C:$C, A2, $F:$F)
فرمول گردش بستانکار:
=SUMIF($C:$C, A2, $G:$G)
در اینجا:
ستون C = کد حسابها
ستون F = بدهکار
ستون G = بستانکار
A2 = کد حساب مورد نظر
مرحله ۴ – محاسبه مانده حساب
مانده = گردش بدهکار – گردش بستانکار
اما چون در تراز آزمایشی مانده تنها در یکی از ستونها (بدهکار یا بستانکار) نمایش داده میشود، فرمول را به شکل زیر تنظیم میکنیم:
مانده بدهکار:
=IF((C2 - D2) > 0, C2 - D2, 0)
مانده بستانکار:
=IF((D2 - C2) > 0, D2 - C2, 0)
که:
C2 = گردش بدهکار
D2 = گردش بستانکار
مرحله ۵ – مرتبسازی و فیلتر
برای خوانایی بیشتر تراز:
از گزینه Sort by Code استفاده کنید.
از Filter برای تفکیک حسابهای دارای مانده استفاده کنید.
جمع ستون بدهکار = جمع ستون بستانکار را حتماً در انتها کنترل نمایید.
در صورتی که تراز تراز نباشد، یعنی:
سند ناقص
اشتباه در ثبت
یا اشتباه در گروهبندی حسابها وجود دارد.
مزیت این روش
| ویژگی | توضیح |
|---|---|
| قابل بهروزرسانی | با اضافه شدن سند جدید، فقط Refresh لازم است |
| بدون نیاز به ماکرو | با توابع پایه انجام میشود |
| قابل استفاده در هر سازمان | مبتنی بر استاندارد ثبت حسابداری |
| مناسب برای گزارشگیری سریع | مخصوصاً زمانی که نرمافزار حسابداری در دسترس نیست |
جمعبندی
در این روش با استفاده از UNIQUE برای استخراج لیست حسابها، SUMIF برای محاسبه گردش و IF برای تعیین مانده، یک تراز آزمایشی چهارستونی استاندارد و دقیق قابل تهیه است. این روش هم برای دانشجویان حسابداری و هم برای حسابداران حرفهای قابل اتکا و سریع است و در محیط اکسل بهراحتی بهروزرسانی میشود.
معادل انگلیسی اصطلاحات
| اصطلاح فارسی | معادل انگلیسی |
|---|---|
| تراز آزمایشی | Trial Balance |
| گردش بدهکار | Debit Turnover |
| گردش بستانکار | Credit Turnover |
| مانده بدهکار | Debit Balance |
| مانده بستانکار | Credit Balance |
| اسناد حسابداری | Accounting Entries |
| کد حساب | Account Code |
| نام حساب | Account Title |
