محاسبه مانده حساب مشتریان در اکسل ; آموزش فرمول ها
محاسبه مانده حساب مشتریان در اکسل یکی از روشهای ساده و کاربردی برای مدیریت حساب مشتریان و صرفهجویی در زمان حسابداری است. هر کسبوکاری که به مشتریان خود کالا یا خدمات ارائه میدهد، باید بداند هر مشتری چه مبلغی خرید کرده، چه مقدار از بدهی خود را پرداخت کرده و در نهایت چه مانده حسابی دارد.
وقتی تعداد مشتریان و معاملات افزایش پیدا میکند، انجام این محاسبات بهصورت دستی زمان زیادی میگیرد و احتمال اشتباه نیز بیشتر میشود. اکسل این امکان را فراهم میکند که اطلاعات مربوط به فروش، دریافتها و مانده حساب مشتریان را در یک جدول منظم ثبت کنیم و با استفاده از فرمولهای ساده، وضعیت حساب هر مشتری را بهسرعت محاسبه کنیم.
در این مقاله از سایت دکتر میرزازاده، مرحلهبهمرحله بررسی میکنیم که چگونه مانده حساب مشتریان را در اکسل محاسبه کنیم و با استفاده از فرمولهایی مانند “SUMIF” و “SUMIFS“، گزارش دقیقتری از بدهی و دریافت مشتریان تهیه کنیم.
مانده حساب مشتری چیست و چگونه در اکسل محاسبه میشود؟
مانده حساب مشتری در سادهترین حالت، تفاوت بین مبلغی است که مشتری باید پرداخت کند و مبلغی که تا امروز پرداخت کرده است. بنابراین اگر مشتری بیشتر از مبلغ بدهی خود پرداخت کرده باشد، حساب او میتواند بستانکار شود و اگر هنوز بخشی از بدهی خود را پرداخت نکرده باشد، مانده بدهکار خواهد بود.
برای مثال فرض کنید یک مشتری در طول یک ماه از یک شرکت به مبلغ 50 میلیون تومان خرید کرده است. این مشتری 30 میلیون تومان از مبلغ خرید را پرداخت کرده است. بنابراین:
مانده حساب = مبلغ فروش یا بدهی – مبلغ دریافتشده
در این مثال:
50 – 30 = 20 میلیون تومان
پس مشتری 20 میلیون تومان مانده بدهکار دارد.
استفاده از اکسل برای این کار باعث میشود اطلاعات مشتریان منظمتر شود و بتوانیم با کمترین زمان، گزارش حساب آنها را تهیه کنیم.
چه اطلاعاتی برای محاسبه مانده حساب نیاز داریم؟
برای ساخت یک فایل ساده و کاربردی، بهتر است حداقل اطلاعات زیر را در اکسل ثبت کنید:
– نام مشتری
– تاریخ معامله
– شماره فاکتور
– مبلغ فروش یا بدهکاری
– مبلغ دریافتشده
– توضیحات
– مانده حساب
هرچه تعداد اطلاعات دقیقتر باشد، بررسی سوابق مشتری نیز راحتتر خواهد بود.
فرمول محاسبه مانده حساب مشتریان در اکسل
در یک سیستم ساده، میتوان مانده حساب را با این فرمول محاسبه کرد:
مانده = مجموع بدهکار – مجموع بستانکار
البته اینکه کدام مبلغ در ستون بدهکار و کدام مبلغ در ستون بستانکار قرار بگیرد، به ساختار حسابداری شما بستگی دارد. در فایلهای ساده فروش و دریافت، معمولاً مبلغ فروش را بهعنوان بدهی مشتری و مبلغ دریافتی را بهعنوان پرداخت مشتری در نظر میگیریم.
ساخت جدول محاسبه مانده حساب مشتریان در اکسل
برای شروع، یک فایل جدید در Excel باز کنید و اطلاعات را در ستونهای جداگانه قرار دهید. برای نمونه میتوانیم جدولی شبیه جدول زیر ایجاد کنیم:
نام مشتری| تاریخ| شرح| مبلغ فروش| مبلغ دریافت| مانده
علی رضایی| 1405/01/05| فروش کالا| 10,000,000| 4,000,000| 6,000,000
علی رضایی| 1405/01/12| فروش کالا| 8,000,000| 3,000,000| 11,000,000
مریم احمدی| 1405/01/08| فروش کالا| 12,000,000| 12,000,000| 0
در این ساختار، ستون «مبلغ فروش» نشاندهنده مبلغی است که مشتری باید پرداخت کند و ستون «مبلغ دریافت» مبلغی است که از او دریافت شده است.
محاسبه مانده هر ردیف با فرمول
فرض کنیم مبلغ فروش در ستون D و مبلغ دریافت در ستون E قرار دارد و ردیف اول اطلاعات از شماره 2 شروع شده است.
در سلول F2 میتوانیم فرمول زیر را وارد کنیم:
“=D2-E2”
با این فرمول، مبلغ دریافت از مبلغ فروش کم میشود و مانده همان معامله به دست میآید.
برای مثال اگر مبلغ فروش 10 میلیون تومان و مبلغ دریافت 4 میلیون تومان باشد، نتیجه فرمول 6 میلیون تومان خواهد بود.
برای اعمال فرمول روی ردیفهای بعدی هم کافی است گوشه سلول را بکشید تا فرمول برای سایر ردیفها نیز کپی شود.
محاسبه مانده تجمعی مشتری در اکسل
گاهی مشتری در چند مرحله خرید کرده و در چند مرحله نیز مبلغی پرداخت کرده است. در این حالت بهتر است مانده بهصورت تجمعی محاسبه شود.
فرض کنید در ستون D مبلغ بدهکاری و در ستون E مبلغ پرداختی ثبت شده است. در F2 میتوانیم بنویسیم:
“=D2-E2”
در F3 نیز میتوانیم از فرمول زیر استفاده کنیم:
“=F2+D3-E3”
در این حالت، مانده قبلی به اضافه بدهکاری جدید و منهای دریافت جدید میشود.
فرض کنید مشتری در معامله اول 10 میلیون تومان خرید کرده و 4 میلیون تومان پرداخت کرده است. مانده او 6 میلیون تومان میشود. در معامله دوم 8 میلیون تومان دیگر خرید میکند و 3 میلیون تومان پرداخت میکند.
مانده جدید:
6 + 8 – 3 = 11 میلیون تومان
به این ترتیب، اکسل میتواند مانده حساب مشتری را بهصورت لحظهای بهروزرسانی کند.
محاسبه مجموع بدهی و دریافت هر مشتری با SUMIF در اکسل
وقتی تعداد معاملات زیاد شود، محاسبه مانده هر مشتری از روی تکتک ردیفها سخت خواهد شد. در این شرایط یکی از بهترین توابع اکسل، تابع SUMIF است.
این تابع به ما اجازه میدهد مجموع مبلغهای مربوط به یک مشتری خاص را محاسبه کنیم.
فرض کنید نام مشتری در ستون A، مبلغ فروش در ستون D و مبلغ دریافت در ستون E قرار دارد.
برای محاسبه مجموع فروش مشتری «علی رضایی» میتوانیم از فرمول زیر استفاده کنیم:
“=SUMIF(A:A,”علی رضایی”,D:D)”
این فرمول تمام ردیفهایی را که نام مشتری در آنها «علی رضایی» است پیدا میکند و مبلغ فروش مربوط به آنها را با هم جمع میکند.
برای محاسبه مجموع دریافتهای همین مشتری نیز میتوانیم بنویسیم:
“=SUMIF(A:A,”علی رضایی”,E:E)”
در نهایت مانده حساب او از تفاضل این دو عدد به دست میآید.
محاسبه مانده نهایی هر مشتری در اکسل
فرض کنید در یک بخش از فایل، نام مشتری را در سلول H2 نوشتهایم.
برای محاسبه مجموع فروش مشتری میتوانیم بنویسیم:
“=SUMIF(A:A,H2,D:D)”
و برای محاسبه مجموع دریافت:
“=SUMIF(A:A,H2,E:E)”
حالا اگر مجموع فروش در I2 و مجموع دریافت در J2 قرار گرفته باشد، در K2 میتوانیم بنویسیم:
“=I2-J2”
به این ترتیب، مانده حساب مشتری بهصورت خودکار محاسبه میشود.
مزیت مهم این روش این است که اگر تعداد معاملات از 10 مورد به 100 یا حتی چند هزار مورد برسد، ساختار محاسبه همچنان قابل استفاده خواهد بود.
محاسبه مانده حساب با SUMIFS در اکسل
اگر بخواهیم اطلاعات را بر اساس چند شرط محاسبه کنیم، تابع SUMIFS گزینه مناسبتری است.
برای مثال ممکن است بخواهیم فروش یک مشتری را فقط در یک بازه زمانی مشخص محاسبه کنیم. در این حالت میتوانیم از SUMIFS استفاده کنیم و همزمان مشتری و تاریخ را بهعنوان شرط قرار دهیم.
این روش برای شرکتهایی که گزارشهای ماهانه، فصلی یا سالانه تهیه میکنند، کاربرد زیادی دارد.
ساخت گزارش مانده حساب مشتریان در اکسل
اگر تعداد مشتریان زیاد باشد، بهتر است بهجای اینکه هر بار اطلاعات را بهصورت دستی بررسی کنیم، یک گزارش خلاصه در اکسل بسازیم.
برای نمونه میتوانیم جدولی با ستونهای زیر داشته باشیم:
مشتری| مجموع فروش| مجموع دریافت| مانده
علی رضایی| 80,000,000| 55,000,000| 25,000,000
مریم احمدی| 45,000,000| 45,000,000| 0
رضا کریمی| 70,000,000| 40,000,000| 30,000,000
این جدول به مدیر یا حسابدار کمک میکند در چند ثانیه وضعیت حساب مشتریان را مشاهده کند.
پیدا کردن مشتری و بررسی حساب با Filter در اکسل
یکی از قابلیتهای بسیار کاربردی اکسل، Filter است. با فعال کردن فیلتر روی جدول میتوانیم فقط اطلاعات مربوط به یک مشتری خاص را نمایش دهیم.
برای مثال اگر 500 ردیف تراکنش داشته باشیم و بخواهیم فقط حساب «علی رضایی» را ببینیم، کافی است روی ستون نام مشتری فیلتر قرار دهیم.
با این کار تمام تراکنشهای مربوط به این مشتری نمایش داده میشود و بررسی حساب او بسیار سادهتر خواهد شد.
گزارش مانده حساب مشتریان با PivotTable در اکسل
وقتی حجم اطلاعات زیاد است، PivotTable یکی از بهترین ابزارهای اکسل برای تهیه گزارش حساب مشتریان است.
با PivotTable میتوانیم نام مشتری را در قسمت Rows، مبلغ فروش و مبلغ دریافت را در قسمت Values قرار دهیم. اکسل سپس مجموع هر مشتری را بهصورت خودکار نمایش میدهد.
این روش مخصوصاً برای شرکتهایی مناسب است که تعداد زیادی فاکتور و دریافت دارند و میخواهند بدون نوشتن فرمولهای متعدد، گزارش خلاصه تهیه کنند.
جلوگیری از اشتباه در محاسبه مانده حساب مشتریان
یکی از مهمترین نکات در ساخت فایل حساب مشتریان این است که اطلاعات باید منظم ثبت شوند. اگر نام یک مشتری یک بار «علی رضایی» و بار دیگر «علیرضایی» یا «علی رضایی » با فاصله اضافی ثبت شود، ممکن است اکسل آنها را دو مورد متفاوت در نظر بگیرد.
به همین دلیل بهتر است ورود اطلاعات تا حد امکان استاندارد باشد.
همچنین توصیه میشود مبلغها در تمام فایل با یک واحد ثابت ثبت شوند. برای مثال اگر مبالغ را به تومان وارد میکنید، تمام اطلاعات را به تومان ثبت کنید و از ترکیب تومان و ریال در یک جدول خودداری کنید.
جمعبندی: بهترین روش محاسبه مانده حساب مشتریان در اکسل
محاسبه مانده حساب مشتریان در اکسل کار پیچیدهای نیست و با یک جدول استاندارد و چند فرمول ساده میتوان این فرآیند را بهصورت دقیق مدیریت کرد.
در سادهترین حالت، باید مبلغ فروش یا بدهکاری مشتری را از مبلغ دریافتشده کم کنیم تا مانده حساب به دست بیاید. برای محاسبه مانده هر ردیف میتوان از فرمول “=D2-E2” استفاده کرد. زمانی که معاملات یک مشتری زیاد باشد، تابع SUMIF برای محاسبه مجموع فروش و دریافت بسیار کاربردی است و در شرایطی که چند شرط همزمان داریم، میتوان از SUMIFS استفاده کرد.
برای فایلهای بزرگتر نیز ابزارهایی مانند Filter و PivotTable میتوانند گزارشگیری را بسیار سریعتر کنند.
در نهایت، مهمترین نکته این است که اطلاعات فروش و دریافت را از همان ابتدا بهصورت منظم و یکپارچه در اکسل ثبت کنید. وقتی ساختار فایل درست باشد، محاسبه مانده حساب مشتریان، تهیه گزارشهای مدیریتی و بررسی بدهی مشتریان بسیار سادهتر خواهد شد.
اگر فایل اکسل شما برای حسابداری طراحی شده باشد، میتوان حتی گزارشهایی مانند مانده بدهکار مشتریان، مشتریان تسویهشده، مجموع فروش، مجموع دریافت و مانده حساب در یک بازه زمانی مشخص را نیز بهصورت خودکار تهیه کرد.
