توابع کاربردی اکسل در حسابداری: ۱۲ تابعی که هر روز لازمشان دارید

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

این مطلب فهرست الفبایی توابع نیست. ۱۲ تابع را آورده‌ایم که در بستن ماه، تراز، مغایرت بانکی، سنی‌بندی مطالبات، استهلاک و اقساط وام واقعاً به کار می‌آیند. کنار هر کدام یک فرمول آماده و یک خطای رایج هم هست؛ همان خطایی که معمولاً آخر شب پیدایش می‌کنید.

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

 

 

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

نصف فرمول‌هایی که «کار نمی‌کنند» مشکل فرمول ندارند؛ مشکل داده دارند. خروجی نرم‌افزار حسابداری معمولاً سرستون تکراری، ردیف جمع وسط داده و کد حسابی دارد که به‌صورت متن ذخیره شده است.

1. ردیف‌های جمع و سرصفحه‌های تکراری را حذف کنید. هر ردیف باید فقط یک آرتیکل سند باشد.
2. کد حساب را یک‌دست کنید. اگر بعضی کدها عدد و بعضی متن‌اند، جست‌وجو نیمه‌کاره جواب می‌دهد.
3. محدوده را با Ctrl+T به Table تبدیل کنید و نامش را بگذارید، مثلاً Ledger. فرمول‌ها با اضافه شدن سند جدید خودشان بزرگ می‌شوند.
4. تاریخ‌ها را واقعاً تاریخ کنید. تاریخ شمسی متنی مثل ۱۴۰۵/۰۷/۲۸ را نمی‌شود مستقیم مقایسه کرد؛ یا ستون تاریخ میلادی کمکی بسازید یا تاریخ را به شکل عددی ۱۴۰۵۰۷۲۸ نگه دارید.

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

 

 

پرکاربردترین تابع اکسل در حسابداری: SUMIFS

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

فرض کنید جدول Ledger ستون‌های Account، CostCenter، DateNum، Debit و Credit دارد. در برگه تراز، کد معین در ستون A است. مانده هر حساب:

=SUMIFS(Ledger[Debit],Ledger[Account],A2) - SUMIFS(Ledger[Credit],Ledger[Account],A2)

گردش همان حساب فقط در مهرماه ۱۴۰۵، با تاریخ عددی:

=SUMIFS(Ledger[Debit],Ledger[Account],A2,Ledger[DateNum],">=14050701",Ledger[DateNum],"<=14050730")

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

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

 

 

XLOOKUP و VLOOKUP برای کدینگ حساب‌ها

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

=XLOOKUP(A2,Chart[Code],Chart[Name],"کد تعریف نشده")

XLOOKUP از هر دو طرف جست‌وجو می‌کند، پیش‌فرضش تطابق دقیق است و پیام خطای دلخواه هم می‌گیرد. فقط نسخه اکسل را چک کنید: طبق راهنمای مایکروسافت، XLOOKUP در اکسل ۲۰۱۶ و ۲۰۱۹ وجود ندارد و فایلی که با آن ساخته‌اید روی آن نسخه‌ها خطای ‎#NAME?‎ می‌دهد.

اگر همکارانتان هنوز اکسل ۲۰۱۶ دارند، INDEX و MATCH را بنویسید. روی همه نسخه‌ها کار می‌کند و مثل XLOOKUP به ترتیب ستون‌ها حساس نیست:

=INDEX(Chart[Name],MATCH(A2,Chart[Code],0))

به نظرم VLOOKUP را فقط برای فایل‌های قدیمی نگه دارید. خطرناک‌ترین اشتباهش آرگومان آخر است. اگر FALSE یا صفر را جا بیندازید، VLOOKUP تطابق تقریبی می‌کند و بدون هیچ خطایی نام حساب دیگری را برمی‌گرداند. حسابی که اشتباه برچسب بخورد، از خطای ‎#N/A‎ خیلی بدتر است.

 

 

IF و IFS در رده بندی براساس سن مطالبات

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

اول تعداد روز گذشته از سررسید را حساب کنید. اگر سررسید در ستون C به تاریخ میلادی است:

=MAX(0,$H$1-C2)

سلول H1 تاریخ پایان دوره است. از TODAY استفاده نکنید؛ گزارش پایان اسفند باید فردا و ماه بعد هم همان عدد را نشان بدهد.

بعد با IFS طبقه را بدهید:

=IFS(D2=0,"جاری",D2<=30,"۱ تا ۳۰ روز",D2<=90,"۳۱ تا ۹۰ روز",D2<=180,"۹۱ تا ۱۸۰ روز",TRUE,"بیش از ۱۸۰ روز")

ترتیب شرط‌ها مهم است. IFS اولین شرط درست را برمی‌دارد، پس از کوچک به بزرگ بنویسید. آخرین شرط TRUE هم همه باقی‌مانده‌ها را می‌گیرد تا خطای ‎#N/A‎ نبینید. درصد ذخیره هر طبقه سیاست شرکت است و باید از مدیر مالی یا حسابرس بگیرید، نه از اکسل.

 

 

EOMONTH و ROUND: بستن ماه بدون اختلاف ریالی

EOMONTH آخرین روز ماه را برمی‌گرداند. برای اسناد تعدیلی پایان ماه، سررسید چک‌ها و محاسبه روزهای سود سپرده به کار می‌آید. =EOMONTH(A2,0) آخر همان ماه را می‌دهد و =EOMONTH(A2,1) آخر ماه بعد را. این تابع با تقویم میلادی کار می‌کند؛ برای ماه شمسی، جدول کمکی تاریخ‌ها همچنان لازم است.

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

=ROUND(B2*C2,0)

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

 

 

توابع استهلاک اکسل: SLN، DB و DDB

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

استهلاک سال اول یک دستگاه ۶۰۰ میلیونی با سه تابع اکسل (میلیون تومان)

تابع	روش	فرمول	استهلاک سال اول
SLN	خط مستقیم	=SLN(600,60,5)	۱۰۸
DB	نزولی با نرخ ثابت	=DB(600,60,5,1)	۲۲۱٫۴
DDB	مانده نزولی مضاعف	=DDB(600,60,5,1)	۲۴۰

SLN ساده است: بهای تمام‌شده منهای اسقاط، تقسیم بر عمر. DB نرخ را خودش از اسقاط و عمر درمی‌آورد؛ مایکروسافت توضیح داده این نرخ تا سه رقم اعشار گرد می‌شود. در مثال ما نرخ ۰٫۳۶۹ است. اگر دارایی وسط سال خریده شده، آرگومان پنجم DB تعداد ماه‌های سال اول را می‌گیرد.

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

 

 

PMT، IPMT و PPMT: جدول اقساط وام در اکسل

شرکت ۵۰۰ میلیون تومان تسهیلات ۲۴ماهه با نرخ سالانه ۲۳ درصد گرفته است. قسط ثابت ماهانه:

=PMT(23%/12,24,-500000000)

نتیجه حدود ۲۶٬۱۸۶٬۶۵۳ تومان است. منفی گذاشتن مبلغ وام فقط برای این است که قسط مثبت نمایش داده شود؛ اکسل پول دریافتی و پرداختی را با علامت مخالف می‌بیند.

برای ثبت حسابداری، هر قسط باید به سود و اصل تفکیک شود. سهم سود قسط اول با =IPMT(23%/12,1,24,-500000000) حدود ۹٬۵۸۳٬۳۳۳ تومان است و بقیه قسط اصل وام را کم می‌کند. PPMT همان سهم اصل را مستقیم می‌دهد. شماره قسط را از ستون کناری بگیرید و فرمول را تا ردیف ۲۴ بکشید؛ جدول کامل اقساط آماده است.

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

 

 

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

صورتحساب بانک یک طرف است و دفتر بانک شرکت طرف دیگر. سؤال مغایرت‌گیری ساده است: کدام ردیف در یکی هست و در دیگری نیست؟ COUNTIFS همین را می‌شمارد.

کنار هر ردیف دفتر، بپرسید چند ردیف با همان مبلغ و همان شماره چک یا پیگیری در صورتحساب بانک هست:

=COUNTIFS(Bank[Amount],D2,Bank[Ref],E2)

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

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

 

 

جدول خلاصه توابع کاربردی اکسل در حسابداری

کدام تابع اکسل برای کدام کار حسابداری

کار حسابداری	تابع	خطای رایج
تراز آزمایشی و گردش حساب	SUMIFS	حساب جدید که در برگه تراز ردیف ندارد
نام حساب از روی کد	XLOOKUP، INDEX و MATCH	جا انداختن FALSE در VLOOKUP
سنی‌بندی مطالبات	IFS، MAX	استفاده از TODAY به‌جای تاریخ پایان دوره
تاریخ پایان ماه	EOMONTH	اعمال مستقیم روی تاریخ شمسی متنی
سرشکن هزینه	ROUND	اختلاف یک ریالی در جمع سند
استهلاک	SLN، DB، DDB	استفاده از DB برای نرخ‌های جدول مالیاتی
اقساط وام	PMT، IPMT، PPMT	علامت مبلغ وام و تفاوت با جدول بانک
مغایرت بانکی	COUNTIFS	تطبیق فقط با مبلغ

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

 

 

سوالات متداول درباره توابع کاربردی اکسل در حسابداری

مهم‌ترین تابع اکسل برای حسابداری کدام است؟

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

XLOOKUP بهتر است یا VLOOKUP؟

اگر اکسل ۲۰۲۱، ۲۰۲۴ یا مایکروسافت ۳۶۵ دارید، XLOOKUP. پیش‌فرضش تطابق دقیق است، از چپ و راست جست‌وجو می‌کند و پیام خطای دلخواه می‌گیرد. اما در اکسل ۲۰۱۶ و ۲۰۱۹ وجود ندارد. اگر فایل بین همکاران با نسخه‌های قدیمی دست‌به‌دست می‌شود، INDEX و MATCH امن‌ترین انتخاب است.

چرا جمع تراز آزمایشی من در اکسل صفر نمی‌شود؟

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

آیا اکسل با تاریخ شمسی کار می‌کند؟

توابع تاریخ اکسل مثل EOMONTH و DATEDIF با تقویم میلادی کار می‌کنند. ساده‌ترین راه برای گزارش‌های شمسی این است که تاریخ را به شکل عددی هشت‌رقمی مثل ۱۴۰۵۰۷۲۸ نگه دارید تا مقایسه بزرگ‌تر و کوچک‌تر درست جواب بدهد. برای محاسبه فاصله روز، یک ستون کمکی تاریخ میلادی بسازید.

برای استهلاک مالیاتی از تابع DB استفاده کنم؟

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

با اکسل می‌شود جای نرم‌افزار حسابداری را گرفت؟

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

 

 

از کجا شروع کنید

توابع کاربردی اکسل در حسابداری زیادند، ولی کار روزانه با همین چند تابع می‌چرخد. SUMIFS تراز و گردش حساب را می‌سازد و XLOOKUP یا INDEX و MATCH کدینگ را وصل می‌کند. IFS مطالبات را سنی‌بندی می‌کند و COUNTIFS مغایرت بانکی را بیرون می‌کشد. PMT و توابع استهلاک هم محاسبه‌های دوره‌ای را از ماشین‌حساب نجات می‌دهند.

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