فرمول های کاربردی برای حسابداری در اکسل میتوانند بخش بزرگی از کارهای روزمره حسابداران را سریعتر و دقیقتر کنند. از محاسبه حقوق و دستمزد و کسر بیمه گرفته تا محاسبه مالیات بر ارزش افزوده، استهلاک داراییها و تهیه گزارشهای مالی، اکسل ابزار قدرتمندی است که با تسلط بر آن میتوان در وقت و هزینه صرفهجویی کرد. اما وقتی حجم تراکنشها بالا میرود، یک نرمافزار حسابداری حرفهای دقت و سرعت بیشتری نسبت به اکسل ارائه میدهد.
اگر میخواهید تمام این فرمولها را با مثال عددی گام به گام یاد بگیرید، ادامه این مقاله دقیقاً همان چیزی است که نیاز دارید.
فرمول های کاربردی برای حسابداری در اکسل به چند دسته اصلی تقسیم میشوند:
در ادامه هر کدام را با مثال عددی واقعی توضیح میدهیم.
پایهایترین و پرکاربردترین فرمول حسابداری در اکسل است. برای جمع کردن درآمدها، هزینهها یا هر ستون عددی دیگری استفاده میشود.
| توضیح | مقدار | سلول |
| فروش هفته اول | ۵,۰۰۰,۰۰۰ | B2 |
| فروش هفته دوم | ۷,۵۰۰,۰۰۰ | B3 |
| فروش هفته سوم | ۶,۲۰۰,۰۰۰ | B4 |
| جمع کل فروش ماه | SUM(B2:B4)= | B5 |
نتیجه: ۱۸,۷۰۰,۰۰۰ تومان
برای محاسبه میانگین هزینهها، درآمدها یا هر داده مالی دیگری کاربرد دارد.
| توضیح | مقدار | سلول |
| فروش فروردین | ۱۲,۰۰۰,۰۰۰ | B2 |
| فروش اردیبهشت | ۱۵,۰۰۰,۰۰۰ | B3 |
| فروش خرداد | ۹,۰۰۰,۰۰۰ | B4 |
| میانگین فروش فصل | AVERAGE(B2:B4)= | B5 |
نتیجه: ۱۲,۰۰۰,۰۰۰ تومان
محاسبه حقوق و دستمزد یکی از روزمرهترین کارهای حسابداران است. فرمولهای زیر این فرآیند را دقیقتر و سریعتر میکنند.
وقتی کارمندی کمتر از یک ماه کامل کار کرده، باید حقوق را بر اساس روزهای کارکرد محاسبه کنید.
| توضیح | مقدار | سلول |
| حقوق پایه ماهانه | ۱۸,۰۰۰,۰۰۰ | B2 |
| روزهای کارکرد | ۲۳ | C2 |
| حقوق قابل پرداخت | B2/30*C2= | D2 |
نتیجه: ۱۳,۸۰۰,۰۰۰ تومان
مطالعه بیشتر: قوانین کسورات حقوق و دستمزد
اضافهکاری معمولاً ۴۰ درصد بیشتر از نرخ ساعتی عادی محاسبه میشود.
| توضیح | مقدار | سلول |
| حقوق پایه ماهانه | ۱۸,۰۰۰,۰۰۰ | B2 |
| ساعات کاری عادی ماه | ۱۷۶ | C2 |
| ساعات اضافهکاری | ۱۲ | D2 |
| مبلغ اضافهکاری | B2/C2*1.4*D2= | E2 |
نتیجه: ۱,۷۲۷,۲۷۲ تومان
مطالعه بیشتر: آخرین نرخ حق بیمه ۱۴۰۵
سهم کارگر از بیمه تأمین اجتماعی ۷ درصد از حقوق پایه است.
| توضیح | مقدار | سلول |
| حقوق پایه ماهانه | ۱۸,۰۰۰,۰۰۰ | B2 |
| سهم بیمه کارگر | B2*7%= | C2 |
نتیجه: ۱,۲۶۰,۰۰۰ تومان
سهم کارفرما از بیمه تأمین اجتماعی ۲۳ درصد از حقوق پایه است.
| توضیح | مقدار | سلول |
| حقوق پایه ماهانه | ۱۸,۰۰۰,۰۰۰ | B2 |
| سهم بیمه کارفرما | B2*23%= | C2 |
نتیجه: ۴,۱۴۰,۰۰۰ تومان
مطالعه بیشتر: بررسی شرایط و ضوابط دریافت بیمه کارفرما
تابع IF زمانی کاربرد دارد که بخواهید یک شرط را در محاسبات اعمال کنید. مثلاً اگر کارمندی بیش از ۲۲ روز کارکرد داشت، حق مسکن به حقوقش اضافه شود.
| توضیح | مقدار | سلول |
| حقوق پایه | ۱۸,۰۰۰,۰۰۰ | B2 |
| روزهای کارکرد | ۲۵ | C2 |
| حق مسکن | ۳,۰۰۰,۰۰۰ | D2 |
| حقوق نهایی | **IF(C2>22,B2+D2,B2)= | E2 |
نتیجه: چون کارکرد بیش از ۲۲ روز است → ۲۱,۰۰۰,۰۰۰ تومان
توضیح فرمول**: اگر روزهای کارکرد (C2) بیشتر از ۲۲ بود → حقوق پایه (B2) + حق مسکن (D2) را بده
در غیر این صورت → فقط حقوق پایه (B2) را بده
برای محاسبه مبلغ نهایی که به کارمند پرداخت میشود، کسورات را از حقوق ناخالص کم میکنیم.
| توضیح | مقدار | سلول |
| حقوق پایه | ۱۸,۰۰۰,۰۰۰ | B2 |
| روزهای کارکرد | ۱,۲۶۰,۰۰۰ | C2 |
| حق مسکن | ۹۰۰,۰۰۰ | D2 |
| حقوق نهایی | B2-C2-D2= | E2 |
نتیجه: ۱۵,۸۴۰,۰۰۰ تومان
محاسبه مالیات یکی از حساسترین بخشهای کار حسابداران است. فرمولهای زیر رایجترین محاسبات مالیاتی را پوشش میدهند.
بر اساس نرخهای مالیاتی سال ۱۴۰۴، درآمد سالانه تا ۱۴۴ میلیون تومان معاف از مالیات است. مازاد بر آن به صورت پلکانی مشمول مالیات میشود. تابع IF تودرتو این محاسبه پلکانی را در اکسل ممکن میکند.
| توضیح | مقدار | سلول |
| حقوق ماهانه | ۲۰,۰۰۰,۰۰۰ | B2 |
| درآمد سالانه | B2*12= | C2 |
| مالیات سالانه | =IF(C2<=144000000,0,IF(C2<=300000000,(C2-144000000)*10%,IF(C2<=600000000,(300000000-144000000)*10%+(C2-300000000)*15%,(300000000-144000000)*10%+(600000000-300000000)*15%+(C2-600000000)*20%))) | D2 |
| مالیات ماهانه | D2/12= | E2 |
نتیجه برای حقوق ۲۰ میلیون تومان در ماه:
توضیح فرمول:
ساختار کلی: فرمول سه سطح درآمد سالانه را بررسی میکند:
سطح اول:
IF(C2<=144000000, 0, …)
اگر درآمد سالانه کمتر یا مساوی ۱۴۴ میلیون تومان بود → مالیات صفر است (معافیت مالیاتی)
سطح دوم:
IF(C2<=300000000, (C2-144000000)*10%, …)
اگر درآمد سالانه بین ۱۴۴ تا ۳۰۰ میلیون تومان بود → مازاد بر ۱۴۴ میلیون با نرخ ۱۰٪ مشمول مالیات میشود
سطح سوم:
IF(C2<=600000000, (300000000-144000000)*10% + (C2-300000000)*15%, …)
اگر درآمد سالانه بین ۳۰۰ تا ۶۰۰ میلیون تومان بود:
سطح چهارم:
(300000000-144000000)*10% + (600000000-300000000)*15% + (C2-600000000)*20%
اگر درآمد سالانه بیشتر از ۶۰۰ میلیون تومان بود:
یک مثال عددی:
فرض کنید درآمد سالانه ۴۰۰ میلیون تومان است:
مطالعه بیشتر: روش های استعلام بدهی مالیاتی
به عنوان مثال نرخ مالیات بر ارزش افزوده در سال ۱۴۰۴ معادل ۱۰ درصد است.
| توضیح | مقدار | سلول |
| مبلغ فروش خالص | ۵۰,۰۰۰,۰۰۰ | B2 |
| مبلغ مالیات بر ارزش افزوده | B2*10%= | C2 |
| مبلغ کل فاکتور | B2+C2= | D2 |
نتیجه:
گاهی مبلغ کل فاکتور (شامل مالیات) در اختیار است و باید مالیات را از آن استخراج کنید.
| توضیح | مقدار | سلول |
| مبلغ کل فاکتور (شامل مالیات) | ۵۵,۰۰۰,۰۰۰ | B2 |
| مالیات بر ارزش افزوده | B2/1.1*10%= | C2 |
| مبلغ خالص بدون مالیات | B2/1.1= | D2 |
نتیجه:
وقتی با چندین پیمانکار کار میکنید، تابع SUMIF به شما امکان میدهد مالیات تکلیفی هر پیمانکار را جداگانه محاسبه کنید. مالیات تکلیفی پیمانکاران معمولاً ۵ درصد از مبلغ قرارداد است.
| توضیح | مقدار | سلول |
| ستون نام | نام پیمانکاران | A2:A6 |
| ستون مبلغ | مبالغ قراردادها | B2:B6 |
| پیمانکار مورد جستجو | شرکت الف | D2 |
| مالیات تکلیفی این پیمانکار | SUMIF(A2:A6,D2,B2:B6)*5%= | E2 |
نتیجه: اگر جمع قراردادهای شرکت الف ۲۰۰,۰۰۰,۰۰۰ تومان باشد → مالیات تکلیفی: ۱۰,۰۰۰,۰۰۰ تومان
نکته مهم: اعداد و نرخهای مالیاتی ذکرشده به عنوان مثال و بر اساس مصوبات سال ۱۴۰۴ هستند. قبل از استفاده عملی، آخرین بخشنامههای سازمان امور مالیاتی را بررسی کنید.
مطالعه بیشتر: دریافت دموی رایگان نرم افزار حسابداری پیمانکاری
محاسبه استهلاک یکی از مهمترین کارهای حسابداری است که اکسل چند تابع اختصاصی برای آن دارد. هر روش استهلاک فرمول جداگانهای دارد و انتخاب روش درست به نوع دارایی و سیاست مالی شرکت بستگی دارد.
در این روش مبلغ استهلاک هر سال یکسان است. سادهترین و رایجترین روش استهلاک در ایران است.
| توضیح | مقدار | سلول |
| ارزش اولیه دارایی | ۶۰۰,۰۰۰,۰۰۰ | B2 |
| ارزش اسقاط (ارزش باقیمانده) | ۶۰,۰۰۰,۰۰۰ | C2 |
| عمر مفید (سال) | ۵ | D2 |
| استهلاک سالانه | SLN(B2,C2,D2)= | E2 |
نتیجه: ۱۰۸,۰۰۰,۰۰۰ تومان در سال
یعنی هر سال ۱۰۸ میلیون تومان از ارزش دفتری این دارایی کسر میشود.
در این روش استهلاک سالهای اول بیشتر است و به تدریج کاهش مییابد. برای داراییهایی که در سالهای اول کارایی بیشتری دارند مناسبتر است.
| توضیحات | مقدار | سلول |
| ارزش اولیه دارایی | ۶۰۰,۰۰۰,۰۰۰ | B2 |
| ارزش اسقاط | ۶۰,۰۰۰,۰۰۰ | C2 |
| عمر مفید (سال) | ۵ | D2 |
| استهلاک سال اول | SYD(B2,C2,D2,1)= | E2 |
| استهلاک سال دوم | SYD(B2,C2,D2,2)= | E3 |
| استهلاک سال سوم | SYD(B2,C2,D2,3)= | E4 |
نتیجه:
در این روش درصد ثابتی از ارزش دفتری باقیمانده در هر دوره کسر میشود. مبلغ استهلاک هر سال کمتر از سال قبل است.
| توضیح | مقدار | سلول |
| ارزش اولیه دارایی | ۶۰۰,۰۰۰,۰۰۰ | B2 |
| ارزش اسقاط | ۶۰,۰۰۰,۰۰۰ | C2 |
| عمر مفید (سال) | ۵ | D2 |
| استهلاک سال اول | DB(B2,C2,D2,1,12)= | E2 |
| استهلاک سال دوم | DB(B2,C2,D2,2,12)= | E3 |
| استهلاک سال سوم | DB(B2,C2,D2,3,12)= | E4 |
نتیجه:
توضیح فرمول:
عدد اول (1، 2، 3) = شماره دوره (سال)
عدد دوم (12) = تعداد ماههای سال اول
| مناسب برای | مبلغ سال آخر | مبلغ سال اول | روش |
| داراییهای با کارایی یکنواخت | ۱۰۸,۰۰۰,۰۰۰ | ۱۰۸,۰۰۰,۰۰۰ | خط مستقیم (SLN) |
| داراییهای با کارایی بالا در ابتدا | ۳۶,۰۰۰,۰۰۰ | ۱۸۰,۰۰۰,۰۰۰ | مجموع سنوات (SYD) |
| ماشینآلات و تجهیزات | کمتر از SLN | ۱۶۳,۸۰۰,۰۰۰ | نزولی (DB) |
تهیه گزارشهای مالی دقیق و سریع یکی از مهمترین نیازهای حسابداران است. فرمولهای زیر این کار را به شکل قابل توجهی سادهتر میکنند.
وقتی میخواهید جمع یک دسته خاص از هزینهها یا درآمدها را محاسبه کنید، SUMIF بهترین گزینه است.
| توضیح | مقدار | سلول |
| ستون دستهبندی | نوع هزینه | A2:A10 |
| ستون مبالغ | مبالغ | B2:B10 |
| دسته مورد جستجو | اجاره | D2 |
| جمع کل هزینه اجاره | SUMIF(A2:A10,D2,B2:B10)= | E2 |
نتیجه: اگر سه ردیف هزینه اجاره با مبالغ ۵، ۵ و ۵ میلیون تومان داشته باشید → ۱۵,۰۰۰,۰۰۰ تومان
وقتی نیاز دارید هم نوع هزینه و هم بازه زمانی را همزمان فیلتر کنید، از SUMIFS استفاده کنید.
(محدوده جمع، محدوده شرط اول، شرط اول، محدوده شرط دوم، شرط دوم)=SUMIFS :ساختار فرمول
| توضیح | مقدار | سلول |
| ستون دستهبندی | نوع هزینه | A2:A10 |
| ستون تاریخ | تاریخ | B2:B10 |
| ستون مبالغ | مبالغ | C2:C10 |
| جمع هزینه اجاره از ابتدای ۱۴۰۴ | SUMIFS(C2:C10,A2:A10,”اجاره”,B2:B10,”>=”&DATE(1404,1,1))= | E2 |
نتیجه: جمع تمام هزینههای اجاره از ابتدای سال ۱۴۰۴ به بعد
برای شمارش تعداد فاکتورها، پرداختها یا هر نوع تراکنش دیگری کاربرد دارد.
(محدوده، شرط)=COUNTIF:ساختار فرمول
| توضیح | مقدار | سلول |
| ستون وضعیت | وضعیت فاکتور | A2:A20 |
| تعداد فاکتورهای تسویه شده | (“پرداخت شده”,A2:A20)COUNTIF= | C2 |
| تعداد فاکتورهای معوق | (“معوق”,A2:A20)COUNTIF= | C3 |
نتیجه: تعداد دقیق فاکتورهای پرداختشده و معوق به صورت جداگانه
وقتی میخواهید اطلاعات یک مشتری، کالا یا حساب را از یک جدول بزرگ استخراج کنید، VLOOKUP سریعترین راه است.
(مقدار جستجو، محدوده جدول، شماره ستون، نوع جستجو)=VLOOKUP :ساختار فرمول
| توضیح | مقدار | سلول |
| کد، نام، مانده حساب | جدول مشتریان | A2:C20 |
| کد مشتری مورد جستجو | ۱۰۰۵ | E2 |
| نام مشتری | VLOOKUP(E2,A2:C20,2,0)= | F2 |
| مانده حساب مشتری | VLOOKUP(E2,A2:C20,3,0)= | G2 |
نتیجه: با وارد کردن کد مشتری، نام و مانده حساب او به صورت خودکار نمایش داده میشود.
تفاوت SUBTOTAL با SUM این است که وقتی دادهها را فیلتر میکنید، فقط ردیفهای نمایشدادهشده را جمع میزند. این برای گزارشهای مالی که نیاز به فیلتر دارند بسیار کاربردی است.
(شماره تابع، محدوده) — برای جمع، شماره تابع ۹ است=SUBTOTAL :ساختار فرمول
| توضیح | مقدار | سلول |
| ستون دستهبندی | نوع هزینه | A2:A100 |
| ستون مبالغ | مبالغ | B2:B100 |
| جمع فقط ردیفهای فیلترشده | SUBTOTAL(9,B2:B100)= | B101 |
نتیجه: وقتی روی ستون نوع هزینه فیلتر میزنید، مبلغ B101 فقط هزینههای فیلترشده را نشان میدهد — نه کل جدول را.
XLOOKUP در نسخههای جدید اکسل (۲۰۱۹ به بعد) جایگزین VLOOKUP شده و محدودیتهای آن را ندارد. میتواند هم به راست و هم به چپ جستجو کند.
(مقدار جستجو، محدوده جستجو، محدوده برگشت، پیام خطا)=XLOOKUP :ساختار فرمول
| توضیح | مقدار | سلول |
| ستون جستجو | کد حساب | A2:A20 |
| ستون برگشت | مانده حساب | C2:C20 |
| کد حساب مورد جستجو | ۳۰۱ | E2 |
| مانده حساب ۳۰۱ | (“یافت نشد”,E2,A2:A20,C2:C20)XLOOKUP= | F2 |
نتیجه: مانده حساب ۳۰۱ نمایش داده میشود و اگر کد وجود نداشت، پیام «یافت نشد» نشان میدهد. برخلاف VLOOKUP که خطای #N/A میداد.
اکسل یک ابزار قدرتمند است و فرمولهایی که تا اینجا یاد گرفتید میتوانند بخش قابل توجهی از کارهای حسابداری را پوشش دهند. اما اکسل محدودیتهایی دارد که در یک نقطه مشخص، دیگر پاسخگوی نیازهای کسبوکار نیست.
| شاخص | اکسل | نرمافزار حسابداری |
| تعداد تراکنش روزانه | کمتر از ۲۰ تراکنش | بیشتر از ۲۰ تراکنش |
| صدور فاکتور رسمی | ندارد | دارد و به مودیان متصل است |
| چند کاربره | محدود | بله، با کنترل دسترسی |
| گزارشهای مالی استاندارد | دستی و زمانبر | خودکار و لحظهای |
| پشتیبانگیری خودکار | ندارد | دارد |
| خطای انسانی | زیاد | بسیار کم |
اگر کسبوکار شما به مرحلهای رسیده که اکسل دیگر پاسخگو نیست، فروشگاه کاوش طیف گستردهای از نرمافزارهای حسابداری را برای کسبوکارهای کوچک تا سازمانهای بزرگ ارائه میدهد. اگر مطمئن نیستید کدام نرمافزار با نیازهای شما تناسب دارد، کارشناسان کاوش میتوانند بهترین گزینه را به شما معرفی کنند
برای استفاده از فرمولهای پایه مثل SUM، AVERAGE و IF نیازی به دانش پیشرفته ندارید. اما برای فرمولهای تخصصیتر مثل SUMIFS، XLOOKUP و توابع استهلاک، آشنایی با مفاهیم پایه اکسل مثل آدرسدهی مطلق و نسبی، محدودههای نامگذاریشده و فرمولنویسی تودرتو ضروری است.
VLOOKUP فقط به سمت راست جستجو میکند و اگر ستون جستجو سمت چپ دادهها باشد، کار نمیکند. XLOOKUP این محدودیت را ندارد، پیام خطای سفارشی دارد و در نسخههای اکسل ۲۰۱۹ به بعد در دسترس است. اگر نسخه اکسل شما از XLOOKUP پشتیبانی میکند، همیشه آن را به VLOOKUP ترجیح دهید.
رایجترین دلیل اینه که اعداد شما به صورت متن (Text) ذخیره شدهاند نه عدد. در این حالت اکسل آنها را در جمع حساب نمیکند. برای تشخیص، به تراز اعداد در سلول نگاه کنید. اعداد واقعی راستچین و متن چپچین هستند. برای تبدیل، میتوانید از فرمول VALUE(A1)= استفاده کنید.
بله، اما زمانبر است و نیاز به دانش حسابداری و اکسل همزمان دارد. باید حسابهای بدهکار و بستانکار را به صورت دستی دستهبندی کنید و فرمولهای SUMIF را برای هر گروه از حسابها تنظیم کنید. برای کسبوکارهایی که تراکنشهای زیادی دارند، این کار عملاً مقرونبهصرفه نیست و نرمافزار حسابداری گزینه بهتری است.
SUM همیشه تمام سلولهای محدوده را جمع میزند، حتی ردیفهای پنهانشده یا فیلترشده. اما SUBTOTAL فقط ردیفهای نمایشدادهشده را حساب میکند. به همین دلیل در گزارشهای مالی که نیاز به فیلتر دارند، همیشه از SUBTOTAL به جای SUM استفاده کنید.
سه راه عملی وجود دارد. اول اینکه سلولهای حاوی فرمول را قفل کنید و از طریق Review → Protect Sheet از تغییر تصادفی آنها جلوگیری کنید. دوم اینکه نسخه پشتیبان روزانه از فایل تهیه کنید. سوم اینکه فایل را در فضای ابری مثل OneDrive ذخیره کنید تا تاریخچه تغییرات نگه داشته شود.
برای دریافت مقالات بیشتر، میتوانید ایمیل خود را وارد کنید.
آخرین مقالات
محصولات
