بهترین فرمول‌های اکسل برای حسابداران؛ ۲۰ فرمول کاربردی با مثال

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

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

در این مطلب ۲۰ فرمول مهم اکسل را بررسی می‌کنیم و برای هرکدام یک مثال مرتبط با کارهای حسابداری می‌زنیم تا بتوانید همان فرمول‌ها را در فایل‌های واقعی خود استفاده کنید.

نکته: در مثال‌های این مقاله از , برای جدا کردن آرگومان‌های فرمول استفاده شده است. اگر Excel شما به‌جای کاما از ; استفاده می‌کند، کافی است کاماها را با ; جایگزین کنید.

۱. SUM؛ محاسبه مجموع اعداد

یکی از ساده‌ترین و در عین حال پرکاربردترین فرمول‌های اکسل، SUM است.

با این فرمول می‌توانید مجموع یک محدوده از اعداد را محاسبه کنید؛ مثلاً مجموع فروش، هزینه، دریافتی یا پرداختی‌های یک ماه.

فرض کنید مبالغ فروش در سلول‌های B2 تا B10 قرار دارند:

=SUM(B2:B10)

این فرمول مجموع تمام اعداد موجود در محدوده B2 تا B10 را محاسبه می‌کند.

مثال حسابداری

اگر مبالغ فروش روزانه یک فروشگاه در ستون B ثبت شده باشد، می‌توانید در انتهای جدول مجموع فروش را به دست بیاورید:

=SUM(B2:B31)

به این ترتیب بدون جمع کردن دستی ۳۰ عدد، مجموع فروش ماهانه مشخص می‌شود.

۲. SUMIF؛ جمع کردن اعداد بر اساس یک شرط

گاهی اوقات نمی‌خواهیم همه اعداد را با هم جمع کنیم.

مثلاً در یک فایل فروش، اطلاعات مربوط به چند محصول مختلف وجود دارد و می‌خواهیم فقط فروش یک محصول خاص را محاسبه کنیم.

برای این کار SUMIF بسیار کاربردی است.

ساختار کلی:

=SUMIF(range,criteria,sum_range)

فرض کنید نام محصول در ستون A و مبلغ فروش در ستون C قرار دارد.

برای محاسبه مجموع فروش محصول «لپ‌تاپ»:

=SUMIF(A2:A100,"لپ‌تاپ",C2:C100)

اکسل تمام ردیف‌هایی را که در ستون A عبارت «لپ‌تاپ» دارند پیدا می‌کند و مبلغ مربوط به آن‌ها را از ستون C جمع می‌زند.

کاربرد حسابداری

این فرمول برای تهیه گزارش فروش بر اساس محصول، مشتری، حساب یا دسته‌بندی هزینه بسیار مفید است.

۳. SUMIFS؛ جمع بر اساس چند شرط

SUMIFS نسخه پیشرفته‌تر SUMIF است و زمانی استفاده می‌شود که بخواهید هم‌زمان چند شرط داشته باشید.

فرض کنید یک فایل فروش دارید که ستون‌های آن شامل موارد زیر است:

• ستون A: تاریخ

• ستون B: فروشنده

• ستون C: محصول

• ستون D: مبلغ

می‌خواهیم فروش محصول «لپ‌تاپ» توسط فروشنده «احمدی» را محاسبه کنیم:

=SUMIFS(D2:D100,B2:B100,"احمدی",C2:C100,"لپ‌تاپ")

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

یک کاربرد واقعی

فرض کنید مدیر فروش از شما می‌خواهد بداند:

«فروش لپ‌تاپ توسط فروشنده احمدی در این ماه چقدر بوده؟»

با SUMIFS می‌توانید این گزارش را بدون فیلتر کردن دستی تمام اطلاعات به دست آورید.

۴. COUNT؛ شمارش سلول‌های دارای عدد

گاهی لازم است بدانید چند تراکنش، فاکتور یا مبلغ در یک محدوده ثبت شده است.

فرمول COUNT سلول‌هایی را که شامل عدد هستند شمارش می‌کند.

مثلاً:

=COUNT(B2:B100)

اگر در این محدوده ۷۵ مبلغ ثبت شده باشد، نتیجه فرمول ۷۵ خواهد بود.

کاربرد حسابداری

برای مثال می‌توانید تعداد فاکتورهای ثبت‌شده، تعداد مبالغ واردشده یا تعداد تراکنش‌های دارای مبلغ را محاسبه کنید.

۵. COUNTA؛ شمارش سلول‌های غیرخالی

COUNTA کمی با COUNT متفاوت است.

این فرمول تمام سلول‌های غیرخالی را می‌شمارد؛ فرقی نمی‌کند داخل سلول عدد باشد یا متن.

مثلاً:

=COUNTA(A2:A100)

اگر در ستون A نام ۸۰ مشتری ثبت شده باشد، نتیجه ۸۰ خواهد بود.

تفاوت COUNT و COUNTA

اگر هدف شما شمارش اعداد است، معمولاً COUNT مناسب است.

اگر می‌خواهید تعداد سلول‌های دارای اطلاعات را بشمارید، COUNTA کاربرد بیشتری دارد.

۶. COUNTIF؛ شمارش بر اساس یک شرط

با COUNTIF می‌توانید تعداد مواردی را که یک شرط مشخص دارند محاسبه کنید.

فرض کنید در ستون D وضعیت فاکتورهای مختلف ثبت شده است:

• پرداخت شده

• پرداخت نشده

• پرداخت شده

• پرداخت شده

برای شمارش فاکتورهای پرداخت‌نشده:

=COUNTIF(D2:D100,"پرداخت نشده")

کاربرد حسابداری

این فرمول برای بررسی تعداد فاکتورهای تسویه‌نشده، مشتریان بدهکار، تراکنش‌های ناقص یا وضعیت پرداخت‌ها بسیار کاربردی است.

۷. COUNTIFS؛ شمارش بر اساس چند شرط

COUNTIFS زمانی استفاده می‌شود که چند شرط هم‌زمان داشته باشیم.

فرض کنید در ستون B نام مشتری و در ستون C وضعیت پرداخت قرار دارد.

اگر بخواهیم تعداد فاکتورهای پرداخت‌نشده یک مشتری خاص را پیدا کنیم:

=COUNTIFS(B2:B100,"شرکت الف",C2:C100,"پرداخت نشده")

در این حالت اکسل فقط ردیف‌هایی را می‌شمارد که هر دو شرط را داشته باشند.

این روش برای تهیه گزارش‌های دقیق‌تر از وضعیت حساب مشتریان بسیار مفید است.

۸. IF؛ تصمیم‌گیری بر اساس یک شرط

فرمول IF یکی از مهم‌ترین فرمول‌های اکسل است.

ساختار آن:

=IF(condition,value_if_true,value_if_false)

مثلاً فرض کنید مبلغ فاکتور در سلول B2 قرار دارد و می‌خواهیم مشخص کنیم آیا مبلغ آن بیشتر از ۱۰ میلیون تومان است یا خیر:

=IF(B2>10000000,"بیش از ۱۰ میلیون","کمتر یا مساوی ۱۰ میلیون")

مثال حسابداری

می‌توانید برای بررسی وضعیت پرداخت نیز از آن استفاده کنید.

اگر مبلغ پرداخت‌شده در C2 و مبلغ کل فاکتور در B2 باشد:

=IF(C2>=B2,"تسویه شده","بدهکار")

این فرمول می‌تواند وضعیت هر فاکتور را به صورت خودکار مشخص کند.

۹. IFS؛ بررسی چند شرط مختلف

وقتی تعداد شرایط زیاد می‌شود، استفاده از چند IF تو در تو می‌تواند فایل را سخت و شلوغ کند.

در چنین شرایطی IFS می‌تواند گزینه مناسبی باشد.

مثلاً می‌خواهیم بر اساس مبلغ فروش، سطح فروش را مشخص کنیم:

=IFS(
B2>=100000000,"عالی",
B2>=50000000,"خوب",
B2>=20000000,"متوسط",
B2<20000000,"ضعیف"
)

در این مثال، مبلغ فروش بر اساس چند بازه بررسی می‌شود.

کاربرد حسابداری و مدیریتی

می‌توانید از این روش برای دسته‌بندی فروش، هزینه، سود، میزان وصول مطالبات یا عملکرد شعب استفاده کنید.

۱۰. IFERROR؛ جلوگیری از نمایش خطا

یکی از مشکلات رایج در فایل‌های اکسل، نمایش خطاهایی مثل #N/A یا #DIV/0! است.

فرمول IFERROR به شما اجازه می‌دهد مشخص کنید اگر یک فرمول با خطا مواجه شد، چه چیزی نمایش داده شود.

مثلاً:

=IFERROR(A2/B2,0)

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

یا می‌توانید متن نمایش دهید:

=IFERROR(A2/B2,"قابل محاسبه نیست")

این فرمول برای گزارش‌های مالی که قرار است مرتب و قابل ارائه باشند بسیار مفید است.

۱۱. VLOOKUP؛ پیدا کردن اطلاعات از یک جدول

VLOOKUP سال‌ها یکی از پرکاربردترین فرمول‌های جست‌وجو در اکسل بوده است.

فرض کنید یک جدول مشتریان دارید:

اگر کد مشتری در سلول A2 باشد و بخواهید نام مشتری را پیدا کنید:

=VLOOKUP(A2,$A$2:$C$100,2,FALSE)

اکسل کد موجود در A2 را در ستون اول جدول جست‌وجو می‌کند و نام مشتری را از ستون دوم برمی‌گرداند.

کاربرد حسابداری

برای اتصال کد مشتری به نام مشتری، پیدا کردن اطلاعات کالا، حساب‌ها یا اطلاعات ثبت‌شده در یک جدول مرجع بسیار کاربردی است.

۱۲. XLOOKUP؛ جست‌وجوی ساده‌تر و انعطاف‌پذیرتر

در نسخه‌های جدیدتر اکسل، XLOOKUP برای بسیاری از کارهای جست‌وجو بسیار راحت است.

مثلاً اگر کد مشتری در A2 باشد:

=XLOOKUP(A2,A2:A100,B2:B100,"پیدا نشد")

در این مثال، اکسل کد را در محدوده A2:A100 پیدا می‌کند و نتیجه متناظر را از B2:B100 برمی‌گرداند.

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

۱۳. INDEX؛ استخراج اطلاعات از یک موقعیت مشخص

فرمول INDEX زمانی کاربرد دارد که بخواهید مقدار موجود در یک موقعیت مشخص از یک محدوده را برگردانید.

مثلاً:

=INDEX(B2:B100,5)

این فرمول پنجمین مقدار موجود در محدوده B2:B100 را برمی‌گرداند.

INDEX زمانی کاربرد بیشتری پیدا می‌کند که همراه با MATCH استفاده شود.

مثلاً برای پیدا کردن مبلغ مربوط به یک کد خاص:

=INDEX(C2:C100,MATCH(A2,A2:A100,0))

این ترکیب در فایل‌های حسابداری قدیمی و حرفه‌ای بسیار کاربردی است.

۱۴. MATCH؛ پیدا کردن موقعیت یک مقدار

MATCH به‌جای خود مقدار، موقعیت آن را در یک محدوده پیدا می‌کند.

مثلاً:

=MATCH("شرکت الف",A2:A100,0)

اگر «شرکت الف» پنجمین مورد موجود در محدوده باشد، نتیجه ۵ خواهد بود.

ترکیب MATCH و INDEX یکی از روش‌های قدرتمند برای جست‌وجوی اطلاعات در فایل‌های اکسل است.

مثلاً:

=INDEX(C2:C100,MATCH(A2,A2:A100,0))

در این حالت ابتدا جایگاه مقدار A2 پیدا می‌شود و سپس مقدار متناظر از ستون C برگردانده می‌شود.

۱۵. ROUND؛ گرد کردن اعداد

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

برای این کار می‌توان از ROUND استفاده کرد.

مثلاً:

=ROUND(B2,0)

عدد موجود در B2 به نزدیک‌ترین عدد صحیح گرد می‌شود.

اگر بخواهید عدد را تا دو رقم اعشار گرد کنید:

=ROUND(B2,2)

مثال حسابداری

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

۱۶. ABS؛ محاسبه قدرمطلق

گاهی در محاسبات مالی فقط مقدار اختلاف مهم است و علامت مثبت یا منفی اهمیت ندارد.

برای مثال اگر مبلغ ثبت‌شده در سیستم ۱۵ میلیون تومان و مبلغ واقعی ۱۴.۸ میلیون تومان باشد، اختلاف می‌تواند مثبت یا منفی محاسبه شود.

فرمول:

=ABS(A2-B2)

اگر اختلاف بین دو عدد -200000 باشد، نتیجه این فرمول 200000 خواهد بود.

کاربرد حسابداری

برای محاسبه اختلاف بین مبلغ فاکتور و مبلغ پرداخت‌شده، اختلاف موجودی، مغایرت دو گزارش و بسیاری از کنترل‌های مالی می‌توان از ABS استفاده کرد.

۱۷. MAX؛ پیدا کردن بیشترین مقدار

با MAX می‌توانید بزرگ‌ترین عدد یک محدوده را پیدا کنید.

مثلاً:

=MAX(B2:B100)

اگر B2 تا B100 شامل مبلغ فاکتورهای مختلف باشد، این فرمول بیشترین مبلغ فاکتور را مشخص می‌کند.

مثال

فرض کنید مدیر یک شرکت می‌خواهد بداند:

«بیشترین فاکتور صادرشده در این ماه چقدر بوده؟»

با یک فرمول ساده می‌توانید پاسخ را پیدا کنید:

=MAX(B2:B100)

۱۸. MIN؛ پیدا کردن کمترین مقدار

MIN برخلاف MAX کوچک‌ترین عدد یک محدوده را پیدا می‌کند.

مثلاً:

=MIN(B2:B100)

اگر محدوده شامل مبالغ فاکتورها باشد، کمترین مبلغ را برمی‌گرداند.

این فرمول برای بررسی حداقل فروش، کمترین هزینه، پایین‌ترین مبلغ فاکتور و موارد مشابه کاربرد دارد.

۱۹. SUBTOTAL؛ محاسبه اطلاعات روی داده‌های فیلترشده

SUBTOTAL یکی از فرمول‌های بسیار مفید برای گزارش‌های حسابداری است، مخصوصاً زمانی که با جدول‌های بزرگ کار می‌کنید.

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

اگر از SUM استفاده کنید، معمولاً تمام ردیف‌های محدوده در محاسبه لحاظ می‌شوند.

اما SUBTOTAL می‌تواند برای محاسباتی که باید با فیلتر جدول هماهنگ باشند بسیار مفید باشد.

برای جمع یک محدوده:

=SUBTOTAL(9,B2:B100)

عدد 9 در اینجا به معنی عملیات جمع است.

اگر جدول را فیلتر کنید، نتیجه SUBTOTAL با داده‌های قابل مشاهده هماهنگ می‌شود.

کاربرد حسابداری

برای گزارش‌های فروش، هزینه، دریافت و پرداخت که مرتباً فیلتر می‌شوند، این فرمول می‌تواند کار را بسیار راحت‌تر کند.

۲۰. SUMPRODUCT؛ انجام محاسبات ترکیبی

SUMPRODUCT یکی از فرمول‌های قدرتمند اکسل است که در محاسبات مالی کاربرد زیادی دارد.

فرض کنید در یک جدول:

• ستون B = تعداد کالا

• ستون C = قیمت واحد

می‌خواهیم ارزش کل کالاها را محاسبه کنیم.

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

=SUMPRODUCT(B2:B100,C2:C100)

اکسل مقدار هر ردیف را در مقدار متناظر آن ضرب کرده و در نهایت همه نتایج را با هم جمع می‌کند.

مثال حسابداری

فرض کنید یک فاکتور شامل ۵۰ قلم کالا است. تعداد هر کالا در ستون B و قیمت واحد آن در ستون C قرار دارد.

با:

=SUMPRODUCT(B2:B51,C2:C51)

ارزش کل کالاهای فاکتور محاسبه می‌شود.

این فرمول برای محاسبه ارزش موجودی، مبلغ خرید، فروش و بسیاری از محاسبات ترکیبی بسیار کاربردی است.

یک مثال واقعی؛ ترکیب چند فرمول در یک فایل حسابداری

فرض کنید یک فایل فروش با ستون‌های زیر داریم:

حالا چند گزارش ساده می‌توانیم از همین اطلاعات استخراج کنیم.

مجموع فروش

=SUM(D2:D100)

مجموع فروش شرکت الف

=SUMIF(B2:B100,"شرکت الف",D2:D100)

تعداد فاکتورهای پرداخت‌نشده

=COUNTIF(E2:E100,"پرداخت نشده")

مجموع فروش لپ‌تاپ

=SUMIF(C2:C100,"لپ‌تاپ",D2:D100)

بیشترین مبلغ فاکتور

=MAX(D2:D100)

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

کدام فرمول‌ها را اول یاد بگیریم؟

اگر تازه می‌خواهید استفاده حرفه‌ای از اکسل را شروع کنید، لازم نیست هر ۲۰ فرمول را یک‌جا حفظ کنید.

برای کارهای روزمره حسابداری بهتر است ابتدا روی این فرمول‌ها مسلط شوید:

SUM برای جمع کردن اعداد

SUMIF و SUMIFS برای جمع‌های شرطی

COUNTIF و COUNTIFS برای شمارش اطلاعات

IF برای تعیین وضعیت

IFERROR برای کنترل خطاها

XLOOKUP یا VLOOKUP برای پیدا کردن اطلاعات

ROUND برای گرد کردن محاسبات

ABS برای محاسبه اختلاف

SUBTOTAL برای گزارش‌های فیلترشده

بعد از اینکه این موارد را یاد گرفتید، استفاده از فرمول‌هایی مانند INDEX، MATCH و SUMPRODUCT نیز بسیار راحت‌تر خواهد شد.

چند نکته برای ساخت فایل حسابداری بهتر در اکسل

فرمول‌ها تنها بخشی از یک فایل حسابداری خوب هستند. ساختار فایل هم اهمیت زیادی دارد.

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

همچنین بهتر است برای هر ستون فقط یک نوع اطلاعات ثبت شود. مثلاً در ستون مبلغ فقط عدد قرار دهید و واحد پول را داخل سلول ننویسید.

به‌جای این:

25,000,000 تومان

بهتر است مقدار عددی را وارد کنید:

25000000

و سپس از قالب‌بندی سلول برای نمایش مناسب عدد استفاده کنید.

این کار باعث می‌شود فرمول‌های اکسل بتوانند به‌درستی روی داده‌ها محاسبه انجام دهند.

اشتباه رایج در استفاده از فرمول‌های اکسل

یکی از مشکلات رایج این است که کاربران فرمول‌های بسیار پیچیده می‌سازند، در حالی که همان نتیجه را می‌توان با چند فرمول ساده‌تر به دست آورد.

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

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

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

جمع‌بندی

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

فرمول‌هایی مانند SUM، SUMIF، SUMIFS، COUNTIF و IF برای کارهای روزمره بسیار مهم هستند و فرمول‌هایی مانند XLOOKUP، INDEX، MATCH و SUMPRODUCT می‌توانند برای فایل‌های حرفه‌ای‌تر امکانات بیشتری در اختیار شما قرار دهند.

مهم‌تر از حفظ کردن فرمول‌ها، این است که بدانید هر فرمول چه مشکلی را حل می‌کند و چه زمانی باید از آن استفاده کنید.

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

سوالات متداول

آیا یادگیری اکسل برای حسابداران ضروری است؟

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

مهم‌ترین فرمول اکسل برای حسابداران چیست؟

یک فرمول واحد را نمی‌توان برای همه کارها مهم‌ترین دانست. SUM، SUMIF، SUMIFS، IF، COUNTIF و فرمول‌های جست‌وجو از جمله مواردی هستند که در بسیاری از فایل‌های مالی کاربرد دارند.

تفاوت SUMIF و SUMIFS چیست؟

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

آیا VLOOKUP هنوز کاربرد دارد؟

بله. VLOOKUP همچنان در بسیاری از فایل‌های اکسل استفاده می‌شود، هرچند در نسخه‌های جدیدتر اکسل، XLOOKUP برای بسیاری از جست‌وجوها گزینه ساده‌تر و انعطاف‌پذیرتری است.

چرا فرمول اکسل خطا نشان می‌دهد؟

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

آیا می‌توان فرمول‌های اکسل را در گزارش‌های مالی استفاده کرد؟

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