EX
توابع اکسل ویژه حسابداری — مرجع جامع
مهمترین فرمولها و کاربرد آنها در حسابداری، انبار، گزارشگیری مالی و کنترل هزینه
جمع و خلاصهسازی
محاسبات تراز، جمع هزینهها و خلاصهسازی اعداد
SUMSUM(range)
جمع ساده یک بازه — پایهی اکثر گزارشهای مالی
مثال:
=SUM(B2:B120)SUBTOTALSUBTOTAL(func_code, range)
محاسبهٔ جمع/میانگین/... که ردیفهای فیلتر شده را نادیده میگیرد — مناسب برای لیستهای پویا
مثال:
=SUBTOTAL(9, C2:C500) /* 9 = SUM */SUMIF / SUMIFSSUMIF(range, criteria, [sum_range])
جمع شرطی — SUMIF برای یک شرط، SUMIFS برای چند شرط (مثلاً جمع فروش یک مشتری یا برای یک تاریخ خاص)
مثال:
=SUMIFS(D:D, A:A,"فروش", B:B,">=2025-01-01")COUNT / COUNTA / COUNTIFCOUNT(range)
شمارش سطرها: COUNT برای اعداد، COUNTA برای هر نوع داده، COUNTIF برای شرط
مثال:
=COUNTIF(E:E, "پرداختشده")AVERAGE / AVERAGEIFAVERAGE(range)
محاسبه میانگین — AVERAGEIF برای میانگین شرطی (مثلاً میانگین فروش یک محصول خاص)
مثال:
=AVERAGEIF(C:C, "محصول الف", D:D)MAX / MINMAX(range)
یافتن بیشترین یا کمترین مقدار در یک بازه — مفید برای تحلیل دادههای مالی
مثال:
=MAX(E2:E100)جستوجو و مرجع
برای وصل کردن جداول، ماتریسها و ساخت گزارشهای تلفیقی
VLOOKUPVLOOKUP(value, table, col_index, [range_lookup])
جستجوی عمودی؛ برای پیدا کردن اطلاعات مشتری، نام کالا و... (ترجیحاً از XLOOKUP استفاده کن)
مثال:
=VLOOKUP(G2, Products!A:C, 3, FALSE)XLOOKUPXLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])
نسخهٔ مدرنتر و انعطافپذیرتر از VLOOKUP/HLOOKUP (جستجوی به سمت چپ، مقدار پیشفرض و ...)
مثال:
=XLOOKUP(F2, Customers!A:A, Customers!B:B, "یافت نشد")INDEX + MATCHINDEX(range, MATCH(...))
ترکیب قدرتمند برای جستجوی قابلاطمینان و انعطافپذیر (بهجای VLOOKUP در جداول بزرگ)
مثال:
=INDEX(Products!C:C, MATCH(H2, Products!A:A, 0))HLOOKUPHLOOKUP(value, table, row_index, [range_lookup])
جستجوی افقی؛ برای دادههایی که در ردیفها سازماندهی شدهاند
مثال:
=HLOOKUP(A2, Prices!A1:Z10, 3, FALSE)OFFSETOFFSET(reference, rows, cols, [height], [width])
ایجاد یک مرجع به یک سلول یا محدوده که از یک سلول مشخص جابجا شده است
مثال:
=SUM(OFFSET(A1, 1, 2, 5, 1))توابع مالی
محاسبه نرخ، اقساط، ارزش فعلی و جریانهای نقدی
NPVNPV(rate, value1, [value2], ...)
محاسبه ارزش فعلی خالص جریانات نقدی — کاربرد در تحلیل سرمایهگذاریها
مثال:
=NPV(0.12, C2:C6) + C1 /* C1 = جریان اولیه */IRRIRR(values, [guess])
محاسبه نرخ بازده داخلی از یک سری جریان نقدی
مثال:
=IRR(C1:C6)PMTPMT(rate, nper, pv, [fv], [type])
محاسبه مبلغ قسط برای وام یا تسهیلات
مثال:
=PMT(0.08/12, 60, -1000000)PVPV(rate, nper, pmt, [fv], [type])
محاسبه ارزش فعلی یک سرمایهگذاری یا وام
مثال:
=PV(0.06/12, 60, -10000)FVFV(rate, nper, pmt, [pv], [type])
محاسبه ارزش آتی یک سرمایهگذاری با پرداختهای دورهای
مثال:
=FV(0.05/12, 120, -500, -10000)RATERATE(nper, pmt, pv, [fv], [type], [guess])
محاسبه نرخ بهره دورهای برای یک وام یا سرمایهگذاری
مثال:
=RATE(60, -20000, 1000000)*12NPERNPER(rate, pmt, pv, [fv], [type])
محاسبه تعداد دورههای لازم برای بازپرداخت وام یا رسیدن به هدف سرمایهگذاری
مثال:
=NPER(0.06/12, -500, -10000, 100000)SLNSLN(cost, salvage, life)
محاسبه استهلاک خط مستقیم برای یک دوره
مثال:
=SLN(100000, 10000, 10)DDBDDB(cost, salvage, life, period, [factor])
محاسبه استهلاک کاهشی دو برابری برای یک دوره خاص
مثال:
=DDB(100000, 10000, 10, 2)توابع تاریخ و زمان
مهم برای محاسبه سررسیدها، سن حسابها و دورهها
DATEDIFDATEDIF(start_date,end_date, "unit")
محاسبه اختلاف تاریخ بر حسب روز/ماه/سال — مفید برای حسابرسی دورهها
مثال:
=DATEDIF(A2, B2, "m") /* اختلاف به ماه */EDATEEDATE(start_date, months)
افزودن یا کم کردن ماه — محاسبه سررسید اقساط یا قراردادها
مثال:
=EDATE(A2, 3) /* 3 ماه بعد */TODAY / NOWTODAY() / NOW()
TODAY تاریخ امروز؛ NOW تاریخ و زمان کنونی — برای گزارشهای روزانه و لاگها
مثال:
=IF(D2 <= TODAY(),"سررسید","فعال")EOMONTHEOMONTH(start_date, months)
محاسبه تاریخ آخرین روز ماه — مفید برای گزارشهای ماهانه و سررسیدها
مثال:
=EOMONTH(TODAY(), 1) /* پایان ماه آینده */WORKDAYWORKDAY(start_date, days, [holidays])
محاسبه تاریخ پس از تعداد مشخصی روزهای کاری
مثال:
=WORKDAY(TODAY(), 10, Holidays!A2:A20)NETWORKDAYSNETWORKDAYS(start_date, end_date, [holidays])
محاسبه تعداد روزهای کاری بین دو تاریخ
مثال:
=NETWORKDAYS(A2, B2, Holidays!A2:A20)YEAR / MONTH / DAYYEAR(serial_number)
استخراج سال، ماه یا روز از یک تاریخ
مثال:
=MONTH(A2) /* استخراج ماه */توابع شرطی و منطقی
برای تحلیل شرطی، تخصیص حساب و اعتبارسنجی دادهها
IFIF(condition, value_if_true, value_if_false)
اساس چکهای منطقی و تصمیمگیری در سلولها
مثال:
=IF(E2>0, "بدهکار", "بستانکار")IFSIFS(cond1, val1, cond2, val2, ...)
جایگزین سلسلهای برای تو در توهای IF؛ خواناتر برای چند شرط
مثال:
=IFS(B2>=90,"عالی", B2>=75,"خوب", TRUE,"نیاز به بررسی")IFERRORIFERROR(value, value_if_error)
نمایش مقدار جایگزین بهجای خطا (مثلاً تقسیم بر صفر یا جستجوی ناموفق)
مثال:
=IFERROR(A2/B2, 0)AND / ORAND(condition1, condition2, ...)
برای ترکیب چند شرط در یک تابع IF یا سایر توابع شرطی
مثال:
=IF(AND(A2>100, B2="تایید شده"), "مجاز", "غیرمجاز")NOTNOT(logical)
برای معکوس کردن نتیجه یک شرط
مثال:
=IF(NOT(A2="لغو شده"), "فعال", "غیرفعال")SWITCHSWITCH(expression, value1, result1, [default])
ارزیابی یک عبارت در برابر لیستی از مقادیر و بازگرداندن نتیجه متناظر
مثال:
=SWITCH(A2, 1, "یک", 2, "دو", "عدد نامعتبر")توابع متنی و قالببندی
پاکسازی نام مشتری، جدا کردن کدها و آمادهسازی گزارش
TEXTTEXT(value, format_text)
قالبدهی عدد به متن (مناسب برای گزارشها و چاپ فاکتور)
مثال:
=TEXT(A2,"#,##0.00")CONCAT / &=A2 & " - " & B2
ترکیب فیلدها برای ساخت شناسه، توضیحات فاکتور و ...
مثال:
=A2 & " | " & TEXT(B2,"yyyy-mm-dd")TRIMTRIM(text)
حذف فاصلههای اضافی — مهم قبل از جستجو/مچکردن نامها
مثال:
=TRIM(C2)LEFT / RIGHT / MIDLEFT(text, num_chars)
استخراج کاراکترها از سمت چپ، راست یا وسط یک متن
مثال:
=LEFT(A2, 3) /* 3 کاراکتر اول */LENLEN(text)
محاسبه تعداد کاراکترهای یک متن
مثال:
=LEN(A2)FIND / SEARCHFIND(find_text, within_text, [start_num])
پیدا کردن موقعیت یک متن در متن دیگر (FIND حساس به بزرگی و کوچکی حروف است)
مثال:
=FIND("@", A2) /* پیدا کردن موقعیت @ */REPLACE / SUBSTITUTEREPLACE(old_text, start_num, num_chars, new_text)
جایگزینی بخشی از متن با متن جدید
مثال:
=SUBSTITUTE(A2, "قدیمی", "جدید")UPPER / LOWER / PROPERUPPER(text)
تبدیل متن به حروف بزرگ، کوچک یا حرف اول هر کلمه بزرگ
مثال:
=PROPER(A2) /* حرف اول هر کلمه بزرگ */گردکردن و محاسبات دقیق
مهم برای محاسبه مالیات، گزارش نهایی و تسویهها
ROUNDROUND(number, num_digits)
گرد کردن عدد به تعداد اعشار مشخص — استفاده در مالیات و مبلغ نهایی
مثال:
=ROUND(E2, 0) /* گرد به تومان */ROUNDUP / ROUNDDOWNROUNDUP(number, num_digits)
گرد به بالا یا پایین — مثلاً در محاسبه سررسید یا تخمینهای عملیاتی
مثال:
=ROUNDUP(F2, 0)MROUNDMROUND(number, multiple)
گرد کردن به نزدیکترین چندگان (مثلاً واحد پول یا بستهبندی)
مثال:
=MROUND(G2, 1000)INTINT(number)
گرد کردن عدد به سمت پایین به نزدیکترین عدد صحیح
مثال:
=INT(A2)MODMOD(number, divisor)
محاسبه باقیمانده تقسیم — مفید برای محاسبات دورهای
مثال:
=MOD(A2, 12) /* باقیمانده تقسیم بر 12 */تحلیل داده و گزارشگیری
توابع مفید برای تهیه داشبورد، جدول محوری (Pivot) و تحلیل هزینه
Pivot Table—
ابزار قدرتمند جمعبندی، گروهبندی و تهیه گزارشهای ترکیبی (از منوی Insert)
نکته: از Pivot بهعلاوه توابع GETPIVOTDATA برای استخراج مقادیر استفاده کن
SUMPRODUCTSUMPRODUCT(array1, [array2], ...)
مجموع حاصلضرب آرایهها — مناسب محاسبه هزینه کل از مقدار × قیمت با شرط
مثال:
=SUMPRODUCT((A2:A100="مواد")*(C2:C100)*(D2:D100))FILTER (نسخۀ جدید)FILTER(array, include, [if_empty])
فیلتر دینامیک دادهها بدون نیاز به جدول محوری — در نسخههای جدید Excel
مثال:
=FILTER(Table1, Table1[وضعیت]="باز")UNIQUE (نسخۀ جدید)UNIQUE(array, [by_col], [exactly_once])
استخراج مقادیر منحصر به فرد از یک محدوده
مثال:
=UNIQUE(A2:A100)SORT (نسخۀ جدید)SORT(array, [sort_index], [sort_order], [by_col])
مرتبسازی یک محدوده یا آرایه
مثال:
=SORT(A2:C100, 3, -1) /* مرتبسازی بر اساس ستون سوم به صورت نزولی */SORTBY (نسخۀ جدید)SORTBY(array, by_array1, [sort_order1], ...)
مرتبسازی یک محدوده بر اساس محدودههای دیگر
مثال:
=SORTBY(A2:A100, B2:B100, 1, C2:C100, -1)GETPIVOTDATAGETPIVOTDATA(data_field, pivot_table, [field1, item1], ...)
استخراج دادهها از یک جدول محوری
مثال:
=GETPIVOTDATA("فروش", $A$3, "محصول", "محصول الف")توابع اطلاعاتی و خطاها
برای بررسی نوع داده، سلولهای خالی و مدیریت خطاها
ISBLANKISBLANK(value)
بررسی خالی بودن یک سلول
مثال:
=IF(ISBLANK(A2), "خالی", "مقدار دارد")ISERRORISERROR(value)
بررسی خطا بودن یک مقدار
مثال:
=IF(ISERROR(A2/B2), "خطا", A2/B2)ISNUMBERISNUMBER(value)
بررسی عدد بودن یک مقدار
مثال:
=IF(ISNUMBER(A2), "عدد", "غیرعدد")ISTEXTISTEXT(value)
بررسی متن بودن یک مقدار
مثال:
=IF(ISTEXT(A2), "متن", "غیرمتن")NANA()
ایجاد خطای #N/A برای نشان دادن مقدار در دسترس نبودن
مثال:
=IF(A2="", NA(), A2)CELLCELL(info_type, [reference])
استخراج اطلاعات درباره قالب، موقعیت یا محتوای یک سلول
مثال:
=CELL("format", A2) /* فرمت سلول A2 */