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

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

در بعضی جاها از جدولی به نام staff (کارمندان)  استفاده کرده ام.

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

بیش از یکصد فرمول اکسل

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

فرمول های مربوط به اعداد ، ارزش ها ، خلاصه ها و آمار

جمع چند مقدار

 

مثال         =SUM(5,6,9)

نتیجه       20

جمع مقادیر از یک محدوده

مثال         =SUM(A1:A5)

نتیجه       125

 

جمع کردن مقادیر از یک جدول مرجع

تذکر مهم: در هر فرمولی از مقاله “بیش از یکصد فرمول اکسل” از کلمه staff در فرمول ها استفاده شده باشد به تصویر بالای صفحه مراجعه نمایید

مثال         =SUM(staff[Salary])

نتیجه       $ 945,000

جمع کردن اعداد با توجه به شرایط خاص

مثال         =SUMIFS(staff[Salary], staff[Department],”Sales”)

نتیجه       $ 279,000

جمع اعداد بزرگتر از (کوچکتر از و …) ازیک مقدار

مثال         =SUMIFS(A1:A6, A1:A6,”>25″)

نتیجه       100

برای مطالعه بیشتر به مقاله “توابع جمع شرطی” مراجعه فرمایید

جمع اعدادی که هم اکنون فیلتر شده اند

مثال         =SUBTOTAL(109,staff[Salary])

نتیجه       $ 945,000

 

شمارش اعدادی که هم اکنون فیلتر شده اند

مثال         =SUBTOTAL(103,staff[Name])

نتیجه       13

بدست آوردن جمع کل یک ستون از شروع اولین سلول

این فرمول را در اولین سلول تایپ کرده و به سمت پایین کپی کنید تا همیشه جمع کل را داشته باشید

مثال         =SUM($A$1:A1)

نتیجه       10

شمارش اعداد یک محدوده

مثال         =COUNT(A1:A6)

نتیجه       6

شمارش تمام مقادیر (از جمله رشته های متنی)

مثال         =COUNTA(staff[Name])

نتیجه       13

شمارش سلول های خالی در یک جدول یا محدوده

مثال         =COUNTBLANK(A1:A20)

نتیجه       14

شمارش مقادیر در سلول های غیر تهی

فرمول  Rows  به شما می گوید که چه تعداد ردیف در محدوده قرار دارد. می توانید از تابع Columns هم استفاده کنید

مثال         =ROWS(A1:A20)-COUNTBLANK(A1:A20)

نتیجه       6

شمارش آیتم هایی که در یک شرط خاص صدق می کنند

مثال         =COUNTIFS(staff[Department],”IT”)

نتیجه       3

شمارش تعداد آیتم هایی که با یک متن خاص شروع می شوند

مثال         =COUNTIFS(staff[Name],”J*”)

نتیجه       13

شمارش تعداد آیتم هایی که با یک متن خاص تمام می شوند

مثال         =COUNTIFS(staff[Name],”*n”)

نتیجه       4

شمارش تمام آیتم هایی که شامل حرف داده شده باشند

مثال         =COUNTIFS(staff[Name],”*an*”)

نتیجه       3

میانگین اعداد مشخص شده

مثال         =AVERAGE(staff[Salary])

نتیجه       $ 72,692

میانگین اعدادی که در یک شرط خاص صدق می کنند

مثال         =AVERAGEIFS(staff[Salary],staff[Department], “HR”)

نتیجه       $ 77,333

میانگین اعداد مثبت

مثال         =AVERAGEIFS(A1:A10,A1:A10,”>0″)

نتیجه       25.83

میانگین اعداد بجز 10% بیشترین و کمترین مقادیر

مثال         =TRIMMEAN(staff[Salary],10%)

نتیجه       $ 72,692

میانگین روزانه داده های یک هفته

این  فرمول را در سلول اولین تایپ کرده و به سمت پایین کپی کنید تا میانگین بدست اید

مثال         =AVERAGE(A1:A7)

نتیجه       25.83

وزن متوسط اعداد

سلول های A1:A6 شامل مقدار و B1:B6 شامل وزن هستند (همزمان عمل ضرب و جمع انجام می شود)

مثال         =SUMPRODUCT(A1:A6,B1:B6)

نتیجه       2,550

برای مطالعه بیشتر به مقاله: “sumproduct چیست و چگونه از آن استفاده کنیم” مراجعه نمایید

میانه مقادیر در یک محدوده

مثال         =MEDIAN(staff[Salary])

نتیجه       $ 76,000

بیشترین تعداد تکرار در یک محدوده

مثال         =MODE.SNGL(1,2,3,3,2,1,1,5,6,7,3,4,8,9)

نتیجه       1

چارک های آماری مقادیر داده شده

در فرمول برای اولین چارک عدد 1 و برای دومین چارک عدد 2 و برای سومین چارک عدد  و3 ExC  به معنی نادیده گرفتن 0 و 1 برای محاسبه چارک ها می باشد.

مثال         =QUARTILE.EXC(staff[Salary],1)

نتیجه       $ 59,500

صدک 90 (یا هر نوع دیگر) از مقادیر داده شده

مثال         =PERCENTILE.EXC(staff[Salary],0.9)

نتیجه       $ 89,000

کمترین مقدار بین لیست اعداد

مثال         =MIN(A1:A6)

نتیجه       10

سومین مقدار(یا هر مقدار دیگری) کوچک در یک لیست

مثال         =SMALL(staff[Salary],3)

نتیجه       $ 59,000

رتبه یک عدد در لیستی از مقادیر

اگر دو عدد یک رتبه داشته باشند ، آنگاه رتبه به طور متوسط بدست می آید. از RANK.EQ برای دادن یک رتبه به هر دو شماره استفاده کنید

مثال         =RANK.AVG(76000, staff[Salary])

نتیجه       7

ماکزیمم مقدار یک لیست

مثال         =MAX(staff[Salary])

نتیجه       $ 89,000

دومین مقدار بزرگ لیست در یک محدوده

مثال         =LARGE(A2:A7,2)

نتیجه       30

فرمول انجام عملیات بر روی اعداد

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

مثال         =MOD(31,7)

نتیجه       3

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

مثال         =ROUND(PI(),4)

نتیجه       3.1416

گرد کردن یک عدد به نزدیکترین مضرب عدد داده شده

مثال         =MROUND(27,4)

نتیجه       28

قسمت صحیح یک عدد

مثال         =INT(19/7)

نتیجه       2

تغییر درصد (واریانس) از یک مقدار به ارزش دیگر

مقدار H5 3500 و مقدار H5 3800 می باشد

مثال         =H5/H4-1

نتیجه       %8.57

رقم اعشار یک عدد

مثال         =MOD(PI(),1)

نتیجه       0.141592654

مقدار مطلق یک عدد

مثال         =ABS(30-43)

نتیجه       13

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

مثال         =7^3

نتیجه       343

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

چک کردن یک شرط و برگرداندن یک مقدار

مثال         =IF(A9>20,”Too high”, “Too low”)

نتیجه       Too low

چک کردن اینکه هر دو شرط همزمان درست باشند (AND)

مثال         =AND(A9>5,B9<20)

نتیجه       FALSE

چک کردن اینکه حداقل یکی از چند شرط درست باشد (OR)

مثال         =OR(E10=”Sales”, F10>90000, D10=A9)

نتیجه       FALSE

چک کردن شرط منطقی (NOT)

مثال         =NOT(“Sam”=”Samuel”)

نتیجه       TRUE

بررسی کنید که آیا این یا آن (انحصاری OR)

جواب وقتی صحیح خواهد بود که یا  A9>10  یا B9>10  و نه هر دو

مثال         =XOR(A9>10, B9>10)

نتیجه       FALSE

انتخاب یک مقدار از بین چندین مقدار

مثال         =CHOOSE(3,A9,B10,A10,B11)

نتیجه       0

برای مطالعه بیشتر به مقاله “معرفی تابع Choose” مراجعه فرمایید

شرایط چندگانه به عنوان IFS

مثال         =ifs(A9>10, “This is too high”, A9>5, “This is ok”, A9>2,”Almost low”, A9<=2,”Really low”)

نتیجه       This is ok

بررسی کنید که آیا مقداری در لیست موجود است یا خیر

مثال         =IF(COUNTIFS(staff[Name],”Jan”)>0,”Yes, Jan is in there”,”No, no such person”)

نتیجه       Yes, Jan is in there

چک کردن چندین شرط با تابع IF  تو در تو

 

مثال         =IF(A9>10, “This is too high”,IF( A9>5, “This is ok”, IF(A9>2,”Almost low”, “Really low”)))

نتیجه       Really low

بررسی کنید که آیا مقداری بین دو مقدار دیگر وجود دارد یا خیر

مثال         =IF(AND(A9>=10,A9<=20),”Between 10 and  20″,”Nope, not between 10 and 20″)

نتیجه       Nope, not between 10 and 20

 

آیا سلول خالی است؟

مثال         =ISBLANK(A18)

نتیجه       TRUE

آیا مقدار زوج است؟

مثال         =ISEVEN(7)

نتیجه       FALSE

آیا مقدار فرد است؟

مثال         =ISODD(7)

نتیجه       TRUE

آیا سلول دارای عدد است؟

مثال         =ISNUMBER(A9)

نتیجه       FALSE

آیا سلول دارای فرمول است؟

مثال         =ISFORMULA(A9)

نتیجه       FALSE

آیا یک سلول (یا فرمول) به خطا می رسد؟

مثال         =ISERROR(7/0)

نتیجه       TRUE

فرمول های کار با مقادیر متن ، رشته ها ، کلمات و عبارات

تبدیل متن به حروف کوچک

مثال         =LOWER(“hello”)

نتیجه       hello

تبدیل متن به حروف بزرگ

مثال         =UPPER(D3)

نتیجه       JAMES

تبدیل متن به یک متن مناسب (حرف اول با کلمه بزرگ)

 

مثال         =PROPER(“this is a long sentence”)

نتیجه       This Is A Long Sentence

ترکیب دو متن جدا در یک متن

مثال         =CONCATENATE(A3, ” and “, A4)

نتیجه       30 and 25

ترکیب دو متن جدا در یک متن

مثال         =A3&” and “&A4

نتیجه       30 and 25

استخراج تعداد مشخصی کلمه از سمت چپ یک متن

مثال         =LEFT(“India”,3)

نتیجه       Ind

استخراج تعداد مشخصی کلمه از سمت راست یک متن

مثال         =RIGHT(“New Zealand”,4)

نتیجه       land

استخراج تعداد مشخصی کلمه از میان یک متن

مثال         =MID(“United States”,4,5)

نتیجه       ted S

طول رشته متنی داده شده چند حرف است؟

مثال         =LEN(“Chandoo.org”)

نتیجه       11

یک کلمه را با حرف دیگر جایگزین کنید

 

مثال         =SUBSTITUTE(“Microsoft Excel”,”cel”,”cellent”)

نتیجه       Microsoft Excellent

برخی حروف را با حروف دیگر جایگزین کنید

مثال         =REPLACE(“abc@email.com”,5,1,”g”)

نتیجه       abc@gmail.com

پیدا کردن شروع موقعیت یک متن در متن دیگر در صورت وجود

مثال         =FIND(“soft”,”Microsoft Excel”)

نتیجه       6

استخراج حروف اول نام و نام خانوادگی در یک رشته متنی

در سلول H1 نام Bill Jelen نوشته شده

مثال         =LEFT(H1,1)&MID(H1,FIND(” “,H1)+1,1)

نتیجه       BJ

پیدا کردن تعداد حروف یک متن

در سلول H2 عبارت ” “This is a very long sentence with lots of words  نوشته شده است

مثال         =LEN(H2)-LEN(SUBSTITUTE(H2,” “,””))+1

نتیجه       10

حذف فواصل خالی اضافی در یک سلول

مثال         =TRIM(”  chandoo.  org   “)

نتیجه       chandoo. org

هر چیزی را بعد از یک نماد یا کلمه حذف کنید

سلول H3 شامل  عبارت  someone@something.com می باشد

مثال         =LEFT(H3,FIND(“@”,H3)-1)

نتیجه       someone

فرمول های کار با تاریخ ، زمان و تقویم

تاریخ امروز کدام است؟

مثال         =TODAY()

نتیجه       6/27/2018

تاریخ و ساعت روز جاری کدام است؟

مثال         =NOW()

نتیجه       6/27/2018 12:00

تاریخ را از سال ، ماه و روز ایجاد کنید

مثال         =DATE(2018,10,20)

نتیجه       10/20/2018

مقدار ساعت را از ساعت ، دقیقه و ثانیه ایجاد کنید

مثال         =TIME(9,45,21)

نتیجه       9:45 AM

از تاریخ معین روز ماه دریافت کنید

مثال         =DAY(TODAY())

نتیجه       27

تاریخ داده شده چندمین ماه می باشد؟

مثال         =MONTH(DATEVALUE(“12-July-1999”))

نتیجه       7

استخراج سال از تاریخ

مثال         =YEAR(TODAY())

نتیجه       2018

پیدا کردن روز هفته از تاریخ

مثال         =WEEKDAY(TODAY())

نتیجه       4

روز هفته (نام روز) را از یک تاریخ پیدا کنید

مثال         =TEXT(TODAY(), “DDDD”)

نتیجه       Wednesday

نام ماه را از یک تاریخ پیدا کنید

مثال         =TEXT(TODAY(), “MMMM”)

نتیجه       June

استخراج ساعت از زمان

مثال         =HOUR(NOW())

نتیجه       12

استخراج دقیقه از زمان

مثال         =MINUTE(NOW())

نتیجه       0

استخراج ثانیه از زمان

مثال         =SECOND(NOW())

نتیجه       2

تاریخ بعد از / قبل از ماه X چیست؟

مثال         =EDATE(TODAY(),3)

نتیجه       9/27/2018

آخرین تاریخ یک ماه کدام است؟

مثال         =EOMONTH(DATE(2018,8,1),0)

نتیجه       8/31/2018

محاسبه تعداد روزهای بین دو تاریخ

 

مثال         =DATE(2018,12,1)-DATEVALUE(“1-july-2018”)

نتیجه       153

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

مثال         =YEARFRAC(DATE(2009,9,24),TODAY(),1)

نتیجه       8.76

تعداد هفته های بین دو تاریخ

مثال         =INT((DATE(2018,12,1)-DATEVALUE(“1-july-2018”))/7)

نتیجه       21

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

این با فرض شنبه و یکشنبه آخر هفته است. اگر الگوی سفر کاری دیگری دارید ، از پارامتر سوم NETWORKDAYS.INTL برای مشخص کردن آن استفاده کنید. به همین ترتیب ، می توانید لیستی از تعطیلات ویژه (روز سال نو ، دیوالی ، رمضان یا کریسمس و غیره) را نیز برای آنها مستثنی کنید.

مثال         =NETWORKDAYS.INTL(TODAY(),DATE(2018,12,31))

نتیجه       134

فرمول های جستجو

در یک جدول یک مقدار جستجو کنید و موارد مربوطه را پیدا کنید (مثال ، حقوق یک کارمند)

John را در ستون اول جدول کارمندان پیدا می کند و مقدار آن را از ستون 3 (Salary) باز می گرداند

مثال         =VLOOKUP(“John”, staff, 3, FALSE)

نتیجه       $ 77,000

یک الگو را در یک جدول جستجو کرده و موارد مربوطه را پیدا می کند (مثال ، حقوق یک کارمند)

مثال         =VLOOKUP(“Jon*”, staff,2,FALSE)

نتیجه       Production

پیدا کردن موقعیت یک مقدار در لیست

مثال         =MATCH(76000,staff[Salary],0)

نتیجه       10

فرمول هایی برای تبدیل یک داده به نوعی دیگر از داده ها

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

مثال         =NUMBERVALUE(“123.456,78″,”,”,”.”)

نتیجه       123,456.78

 

تبدیل یک مقدار به تاریخ

مثال         =DATEVALUE(“1-jul-2018”)

نتیجه       7/1/2018

تبدیل مقدار یک سلول به عدد (روش دیگر)

مثال         =”12456.78″*1

نتیجه       12,456.78

فرمول هایی برای بررسی و جلوگیری از خطاها

نمایش یک مقدار دلخواه در صورت وجود خطا در فرمول

مثال         =IFERROR(VLOOKUP(“Sam”,staff,3,FALSE),”Employee not found”)

نتیجه       Employee not found

نمایش یک مقدار دلخواه در صورت وجود خطای NA در فرمول

مثال         =IFNA(7/0,”This will appear if the error is #N/A”)

نتیجه       #DIV/0!

آیا یک سلول (یا فرمول) به خطا می رسد؟

مثال         =ISERROR(7/0)

نتیجه       TRUE

با خیال راحت یک عدد را با عدد دیگر تقسیم کنید

مثال         =IF(A2=0,””,A1/A2)

نتیجه       0.5

فرمول هایی برای تولید داده های تصادفی

تولید یک عدد تصادفی

با هر بار تغییر در کاربرگ، مقدار بدست آمده تغییر می کند

مثال         =RAND()

نتیجه       0.948708709

تولید یک شماره تلفن تصادفی

با هر بار تغییر در کاربرگ، مقدار بدست آمده تغییر می کند

مثال         =RANDBETWEEN(1000000000,9999999999)

نتیجه       (535) 050-1262

تولید یک حرف تصادفی از بین حروف الفبا

با هر بار تغییر در کاربرگ، مقدار بدست آمده تغییر می کند

مثال         =CHAR(RANDBETWEEN(CODE(“A”),CODE(“Z”)))

نتیجه       Z

ایجاد گزینه تصادفی از لیست مقادیر

با هر بار تغییر در کاربرگ، مقدار بدست آمده تغییر می کند

مثال         =INDEX(staff[Name], RANDBETWEEN(1,COUNTA(staff[Name])))

نتیجه       June

توجه: در مقاله “بیش از یکصد فرمول اکسل” توابع تاریخ و زمان بر اساس تاریخ  میلادی عمل می کنند

برای مطالعه بیشتر به اصل مقاله مراجعه فرمایید