فرمول ماندهگیری در اکسل چیست؟ آموزش محاسبه مانده بدهکار و بستانکار با مثال
فرمول مانده گیری در اکسل یکی از کاربردیترین ابزارها برای حسابداران، دانشجویان حسابداری و صاحبان کسبوکار است. با استفاده از این فرمول میتوانید مشخص کنید هر حساب، مشتری، فروشنده یا طرفحساب چه مقدار بدهکار یا بستانکار است.
برای مثال، ممکن است بخواهید بدانید یک مشتری چه مقدار از بدهی خود را پرداخت کرده است، مانده حساب یک تأمینکننده چقدر است یا مجموع بدهکار و بستانکار یک کد حساب در دفتر معین چه وضعیتی دارد. اکسل این کار را با چند فرمول ساده، بهویژه SUM و SUMIFS، سریع و دقیق انجام میدهد.
در این مقاله یاد میگیرید چگونه مانده بدهکار و بستانکار را در اکسل محاسبه کنید، وضعیت حساب را نمایش دهید و مانده هر حساب را بهصورت خودکار بهروزرسانی کنید.
مانده حساب در اکسل چیست؟
مانده حساب، تفاوت بین مجموع مبالغ بدهکار و مجموع مبالغ بستانکار یک حساب است. در حسابداری، مانده نهایی هر حساب میتواند بدهکار، بستانکار یا صفر باشد.
فرمول اصلی محاسبه مانده به شکل زیر است:
مانده حساب = مجموع بدهکار − مجموع بستانکار
نتیجه مثبت نشاندهنده مانده بدهکار است؛ در مقابل، نتیجه منفی مانده بستانکار را نشان میدهد و صفر بودن نتیجه به معنای تسویه حساب است.
ساخت جدول ماندهگیری در اکسل
برای شروع، یک جدول با ستونهای زیر ایجاد کنید:
| کد حساب | نام حساب | بدهکار | بستانکار | مانده | وضعیت |
|---|---|---|---|---|---|
| 101 | صندوق | 15,000,000 | 4,000,000 | ||
| 102 | بانک | 25,000,000 | 8,500,000 | ||
| 201 | حسابهای پرداختنی | 2,000,000 | 12,000,000 |
در این مثال، ستون C مبلغ بدهکار، ستون D مبلغ بستانکار، ستون E مانده و ستون F وضعیت حساب است.
سادهترین فرمول ماندهگیری در اکسل
اگر مبلغ بدهکار در سلول C2 و مبلغ بستانکار در سلول D2 قرار دارد، فرمول محاسبه مانده در سلول E2 به این صورت است:
=C2-D2
این فرمول مبلغ بستانکار را از مبلغ بدهکار کم میکند.
اگر نتیجه مثبت باشد، مانده حساب بدهکار است. اگر نتیجه منفی باشد، مانده حساب بستانکار خواهد بود. برای مثال، اگر بدهکار ۱۵,۰۰۰,۰۰۰ و بستانکار ۴,۰۰۰,۰۰۰ باشد، مانده برابر با ۱۱,۰۰۰,۰۰۰ است و حساب مانده بدهکار دارد.
این روش سادهترین فرمول مانده گیری در اکسل است و برای بررسی سریع مانده یک ردیف از اطلاعات حسابداری کاربرد دارد.
نمایش وضعیت بدهکار یا بستانکار با تابع IF
بعد از محاسبه مانده، میتوانید نوع مانده را نیز بهصورت خودکار نمایش دهید. اگر مانده در سلول E2 باشد، فرمول زیر را در ستون وضعیت وارد کنید:
=IF(E2>0,"بدهکار",IF(E2<0,"بستانکار","تسویه"))
این فرمول سه حالت را بررسی میکند. اگر مانده بیشتر از صفر باشد، عبارت بدهکار را نمایش میدهد. اگر مانده کمتر از صفر باشد، عبارت بستانکار را نشان میدهد. در صورتی که مانده صفر باشد، وضعیت حساب تسویه خواهد بود.
نمایش مبلغ مانده بدون عدد منفی
بعضی حسابداران ترجیح میدهند مبلغ مانده همیشه بهصورت مثبت نمایش داده شود و نوع آن در یک ستون جداگانه مشخص شود. در این حالت از تابع ABS استفاده کنید.
فرمول نمایش مبلغ مانده:
=ABS(C2-D2)
تابع ABS قدر مطلق عدد را نمایش میدهد. بنابراین حتی اگر حاصل تفریق منفی باشد، مبلغ مانده بهصورت مثبت دیده میشود.
سپس برای تعیین نوع مانده میتوانید از فرمول زیر استفاده کنید:
=IF(C2-D2>0,"بدهکار",IF(C2-D2<0,"بستانکار","تسویه"))
این روش برای تهیه گزارش مانده حساب مشتریان، فروشندگان و حسابهای معین بسیار مناسب است.
فرمول ماندهگیری چند حساب با SUMIFS
در بسیاری از فایلهای حسابداری، اطلاعات یک حساب در چند ردیف ثبت میشود. برای مثال، یک مشتری ممکن است چندین فاکتور خرید و چند پرداخت داشته باشد. در این شرایط، فرمول ساده تفریق برای هر ردیف کافی نیست و باید جمع بدهکار و بستانکار همان حساب محاسبه شود.
فرض کنید ستون A شامل کد حساب، ستون B شامل شرح حساب، ستون C شامل بدهکار و ستون D شامل بستانکار است. اگر کد حساب موردنظر در سلول G2 وارد شده باشد، فرمول محاسبه مجموع بدهکار آن حساب به شکل زیر است:
=SUMIFS($C$2:$C$100,$A$2:$A$100,G2)
برای محاسبه مجموع بستانکار همان حساب، از فرمول زیر استفاده کنید:
=SUMIFS($D$2:$D$100,$A$2:$A$100,G2)
حالا برای محاسبه مانده نهایی حساب میتوانید این دو فرمول را از هم کم کنید:
=SUMIFS($C$2:$C$100,$A$2:$A$100,G2)-SUMIFS($D$2:$D$100,$A$2:$A$100,G2)
در این فرمول، اکسل تمام مبالغ بدهکار مربوط به کد حساب موجود در G2 را جمع میکند و سپس مجموع بستانکار همان حساب را از آن کم میکند.
برای آشنایی بیشتر با ساختار و آرگومانهای این تابع، میتوانید راهنمای رسمی تابع SUMIFS در اکسل را مطالعه کنید.
مثال عملی محاسبه مانده مشتری در اکسل
فرض کنید اطلاعات زیر برای مشتریان ثبت شده است:
| کد مشتری | نام مشتری | بدهکار | بستانکار |
|---|---|---|---|
| 501 | شرکت الف | 10,000,000 | 0 |
| 501 | شرکت الف | 5,000,000 | 0 |
| 501 | شرکت الف | 0 | 8,000,000 |
| 502 | شرکت ب | 12,000,000 | 0 |
اگر بخواهید مانده مشتری با کد 501 را محاسبه کنید، نتیجه به شکل زیر خواهد بود:
۱۵,۰۰۰,۰۰۰ − ۸,۰۰۰,۰۰۰ = ۷,۰۰۰,۰۰۰
پس شرکت الف ۷,۰۰۰,۰۰۰ تومان مانده بدهکار دارد.
فرمول اکسل برای این محاسبه، در صورتی که کد مشتری در سلول G2 قرار داشته باشد، به این صورت است:
=SUMIFS($C$2:$C$100,$A$2:$A$100,G2)-SUMIFS($D$2:$D$100,$A$2:$A$100,G2)
محاسبه مانده جاری هر حساب در اکسل
گاهی فقط مانده نهایی مهم نیست و میخواهید پس از ثبت هر سند، مانده جاری حساب را هم مشاهده کنید. این روش برای دفتر معین، گزارش گردش حساب مشتریان و کنترل دریافتها و پرداختها کاربرد فراوانی دارد.
فرض کنید ستون D کد حساب، ستون F بدهکار و ستون G بستانکار باشد. در ستون H و در ردیف اول دادهها، فرمول زیر را وارد کنید:
=SUMIFS($F$2:F2,$D$2:D2,D2)-SUMIFS($G$2:G2,$D$2:D2,D2)
سپس فرمول را به ردیفهای پایین بکشید.
اکسل در هر ردیف، تمام مبالغ بدهکار ثبتشده تا همان ردیف را جمع میکند و مجموع مبالغ بستانکار تا همان ردیف را از آن کم میکند. به این ترتیب، مانده جاری هر کد حساب بهصورت خودکار نمایش داده میشود.
نکات مهم در استفاده از فرمول ماندهگیری
برای اینکه محاسبه مانده در اکسل درست انجام شود، باید چند نکته را رعایت کنید. اول اینکه مبالغ بدهکار و بستانکار را فقط بهصورت عدد ثبت کنید و از وارد کردن متن در میان اعداد خودداری کنید. دوم اینکه کد حساب یا نام حساب باید در تمام ردیفها یکسان ثبت شود؛ زیرا تفاوت کوچک در نام حساب میتواند باعث شود SUMIFS اطلاعات را جداگانه محاسبه کند.
برای انجام سریعتر و دقیقتر این محاسبات، آشنایی با توابع اکسل پرکاربرد در حسابداری، بهویژه SUM، IF، ABS و SUMIFS، ضروری است.
همچنین بهتر است دادههای خود را با میانبر Ctrl + T به Table تبدیل کنید. جدول اکسل باعث میشود هنگام اضافه کردن ردیفهای جدید، فرمولها و محدوده اطلاعات راحتتر گسترش پیدا کنند.
در فایلهای بزرگ، استفاده از کد حساب بهجای نام حساب، خطای محاسباتی را کمتر میکند. برای نمونه، بهتر است همه تراکنشهای یک مشتری با یک کد ثابت ثبت شوند، حتی اگر نام او در بخش شرح سند نوشته شده باشد.
تفاوت مانده بدهکار و بستانکار چیست؟
مانده بدهکار و بستانکار به ماهیت و گردش هر حساب بستگی دارد. معمولاً داراییها و هزینهها ماهیت بدهکار دارند؛ بنابراین افزایش آنها در ستون بدهکار ثبت میشود. در مقابل، بدهیها، سرمایه و درآمدها معمولاً ماهیت بستانکار دارند.
با این حال، هنگام تهیه گزارش ماندهگیری در اکسل، مهمترین نکته این است که مجموع بدهکار و بستانکار را درست ثبت کرده باشید. اکسل بر اساس اعداد ثبتشده، مانده را محاسبه میکند و نتیجه را بهصورت بدهکار یا بستانکار نشان میدهد.
جمعبندی
فرمول ماندهگیری در اکسل به شما کمک میکند وضعیت حسابها، مشتریان و فروشندگان را سریعتر بررسی کنید. برای یک ردیف ساده، از فرمول C2-D2 استفاده کنید. برای نمایش نوع مانده، تابع IF کاربرد دارد و برای محاسبه مانده حسابهایی که چندین گردش دارند، تابع SUMIFS بهترین انتخاب است.
با یادگیری فرمول مانده گیری در اکسل، کنترل گردش حسابها و تهیه گزارشهای مالی روزانه بسیار آسانتر میشود.
اگر گزارشهای حسابداری خود را در اکسل تهیه میکنید، یادگیری محاسبه مانده بدهکار و بستانکار باعث میشود کنترل حسابها دقیقتر، سریعتر و کمخطاتر انجام شود. همچنین میتوانید این روش را در گزارش وصول مطالبات، دفتر معین، گزارش مشتریان و کنترل حسابهای پرداختنی به کار ببرید.
