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

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

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

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

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

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

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

آموزش تابع SUM؛ سریع‌ترین راه برای جمع اعداد

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

کاربرد تابع SUM:

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

  • محاسبه مجموع فروش روزانه یا ماهانه
  • جمع هزینه‌های شرکت
  • محاسبه مجموع حقوق کارکنان
  • جمع مبالغ فاکتورها
  • محاسبه مجموع دریافتی یا پرداختی حساب‌ها

ساختار تابع: =SUM(number1, number2, …)

یا برای یک محدوده از سلول‌ها: =SUM(A2:A20)

مثال:

 

فرض کنید مبالغ فروش در سلول‌های B2 تا B10 ثبت شده‌اند. برای محاسبه مجموع فروش کافی است فرمول زیر را وارد کنید:

=SUM(B2:B10)

 

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

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

آموزش تابع AVERAGE؛ محاسبه میانگین اعداد

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

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

کاربرد تابع AVERAGE

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

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

ساختار تابع: =AVERAGE(number1, number2, …)

یا برای یک محدوده از سلول‌ها: =AVERAGE(C2:C31)

مثال: فرض کنید میزان فروش روزانه یک فروشگاه در سلول‌های C2 تا C31 ثبت شده است. برای محاسبه میانگین فروش کافی است فرمول زیر را وارد کنید:

=AVERAGE(C2:C31)

 

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

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

۳. تابع IF؛ انجام محاسبات شرطی

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

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

کاربرد تابع IF:

برخی از کاربردهای این تابع عبارت‌اند از:

  • بررسی پرداخت یا عدم پرداخت فاکتورها
  • محاسبه پاداش کارکنان بر اساس میزان فروش
  • تعیین تخفیف برای مشتریان
  • بررسی عبور هزینه‌ها از بودجه تعیین‌شده
  • تعیین وضعیت قبولی یا مردودی در دوره‌های آموزشی

ساختار تابع: =IF(logical_test, value_if_true, value_if_false)

مثال: فرض کنید مبلغ فروش هر کارمند در ستون D ثبت شده است و تصمیم گرفته‌اید اگر فروش بیشتر از ۵۰ میلیون تومان باشد، عبارت «پاداش دارد» نمایش داده شود و در غیر این صورت عبارت «پاداش ندارد» نوشته شود.

فرمول به این شکل خواهد بود: =IF(D2>50000000,”پاداش دارد”,”پاداش ندارد”)

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

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

آموزش تابع SUMIF؛ جمع اعداد بر اساس یک شرط

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

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

کاربرد تابع SUMIF:

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

  • محاسبه مجموع فروش یک محصول مشخص
  • جمع هزینه‌های یک بخش یا پروژه
  • محاسبه مجموع پرداخت‌های انجام‌شده
  • جمع مبالغ مربوط به یک مشتری خاص
  • محاسبه مجموع درآمد یا هزینه در یک بازه مشخص

ساختار تابع: =SUMIF(range, criteria, [sum_range])

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

=SUMIF(A2:A20,”پرینتر”,B2:B20)

در این مثال، اکسل ابتدا سلول‌های ستون A را بررسی می‌کند و هر جا نام «پرینتر» وجود داشته باشد، مبلغ متناظر آن را از ستون B با سایر مبالغ مشابه جمع می‌زند.

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

آموزش تابع COUNTIF؛ شمارش اطلاعات بر اساس شرط

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

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

کاربرد تابع COUNTIF

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

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

ساختار تابع: =COUNTIF(range, criteria)

مثال: فرض کنید وضعیت پرداخت فاکتورها در ستون C ثبت شده و برای فاکتورهای تسویه‌شده عبارت «پرداخت شد» نوشته شده است. برای شمارش تعداد فاکتورهای پرداخت‌شده، از فرمول زیر استفاده کنید:

=COUNTIF(C2:C100,”پرداخت شد”)

 

اکسل تمام سلول‌هایی را که عبارت «پرداخت شد» در آن‌ها وجود دارد، شمارش کرده و تعداد آن‌ها را نمایش می‌دهد.

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

آموزش تابع XLOOKUP؛ جستجوی سریع اطلاعات در جدول

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

این تابع در نسخه‌های جدید Microsoft Excel معرفی شده و نسبت به VLOOKUP امکانات بیشتری دارد. برای مثال، می‌تواند اطلاعات را از هر جهت جستجو کند و در صورت پیدا نکردن مقدار موردنظر، پیام دلخواهی نمایش دهد.

کاربرد تابع XLOOKUP:

  • جستجوی اطلاعات مشتریان بر اساس کد یا نام
  • نمایش قیمت کالا از روی کد محصول
  • پیدا کردن اطلاعات کارکنان با استفاده از شماره پرسنلی
  • بازیابی اطلاعات فاکتورها
  • جستجوی موجودی کالا در انبار

ساختار تابع: =XLOOKUP(lookup_value, lookup_array, return_array)

مثال: فرض کنید کد کالا در ستون A و قیمت آن در ستون B قرار دارد. اگر بخواهید قیمت کالایی با کد موجود در سلول E2 را نمایش دهید، از فرمول زیر استفاده کنید:

=XLOOKUP(E2,A2:A100,B2:B100)

در این مثال، اکسل ابتدا کد کالا را در ستون A جستجو می‌کند و سپس قیمت مربوط به آن را از ستون B نمایش می‌دهد.

فرمول کاربردی اکسل

فرمول کاربردی اکسل

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

ساختار تابع ROUND؛ گرد کردن اعداد با دقت دلخواه

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

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

کاربرد تابع ROUND

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

ساختار تابع: =ROUND(number, num_digits)

مثال: فرض کنید نتیجه یک محاسبه در سلول C2 برابر با 125478.6789 است و می‌خواهید آن را با دو رقم اعشار نمایش دهید. کافی است فرمول زیر را وارد کنید:

=ROUND(C2,2)

 

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

آموزش تابع TODAY؛ نمایش خودکار تاریخ روز

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

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

کاربرد تابع TODAY:

  • ثبت تاریخ روز در گزارش‌های مالی
  • محاسبه تعداد روزهای باقی‌مانده تا سررسید پرداخت
  • بررسی زمان صدور یا دریافت فاکتور
  • تهیه گزارش‌های روزانه
  • محاسبه فاصله بین دو تاریخ

ساختار تابع: =TODAY()

مثال: اگر این فرمول را در یک سلول وارد کنید: =TODAY()

اکسل تاریخ روز سیستم را نمایش می‌دهد. همچنین می‌توانید این تابع را با سایر فرمول‌ها ترکیب کنید. برای مثال، اگر تاریخ سررسید یک فاکتور در سلول B2 ثبت شده باشد، با فرمول زیر می‌توان تعداد روزهای باقی‌مانده تا سررسید را محاسبه کرد: =B2-TODAY()

آموزش تابع IFERROR؛ مدیریت خطاها در فرمول‌ها

 

یکی از مشکلات رایج هنگام کار با اکسل، نمایش خطاهایی مانند #N/A، #DIV/0! یا #VALUE! است. این خطاها ممکن است ظاهر گزارش را نامرتب کنند یا باعث شوند کاربران تصور کنند فایل دچار مشکل شده است. تابع IFERROR به شما کمک می‌کند در صورت بروز خطا، به جای نمایش پیام‌های پیش‌فرض اکسل، نتیجه دلخواه خود را نمایش دهید.

کاربرد تابع IFERROR:

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

ساختار تابع:  =IFERROR(value, value_if_error)

مثال: فرض کنید از تابع XLOOKUP برای جستجوی قیمت کالا استفاده کرده‌اید، اما ممکن است کد کالا در جدول وجود نداشته باشد. در این صورت می‌توانید فرمول را به شکل زیر بنویسید:

=IFERROR(XLOOKUP(E2,A2:A100,B2:B100),”کالا یافت نشد”)

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

جمع‌بندی:

تسلط بر فرمول‌های اصلی اکسل، یکی از مهارت‌های ضروری برای هر حسابدار، کارمند مالی یا حتی مدیر کسب‌وکار است. بسیاری از محاسبات روزانه با استفاده از توابعی مانند SUM، AVERAGE، IF، SUMIF، COUNTIF، XLOOKUP، ROUND، TODAY، IFERROR و TEXT انجام می‌شوند و یادگیری آن‌ها می‌تواند سرعت انجام کارها را افزایش داده و احتمال بروز خطا را کاهش دهد.

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