در اینجا لیست بیش از یکصد فرمول اکسل برای موقعیت های مختلف آورده شده است.
اکثر فرمول مثالها که در اینجا نشان داده می شوند ، خود توضیف هستند (نیازی به توضیح اضافی ندارند).
در بعضی جاها از جدولی به نام 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
توجه: در مقاله “بیش از یکصد فرمول اکسل” توابع تاریخ و زمان بر اساس تاریخ میلادی عمل می کنند
سلام.خواستم بابت وبسایت خوبتون
ازتون تشکر کنم و امیدوارم باعث ایجاد
انگیزه براتون بشه
خیلی عالی بود. مرسی به خاطر مطالب مفیدی که میزارید
ممنون از سایت خوبتو ن
در زمینه ای که فعالیت میکنید جزو بهترین سایت ها هستید
بسیار عالی هستید به کارتون ادامه بدید
سپاس از سایت خوبتون
خوب بود اتفاقی از گوگل اومدم اینجا ولی خوب بود برای دوستم فرستادم اونم دنبالشه اونم بیاد ببینه
مطالبتون به جا و عالی بود
مطالبتون واقعا برای بنده گیرا و جذاب بود
سپاس از اشتراک گذاریتون
فکر نمیکردم اکسل این قدر فرمول داشته باشه
بسیار عالی و آموزنده
تشکر از شما
مطالب رو مطالعه کردم بسیار مطالب خوب و مفیدی بود با تشکر از دیدگاه های شما
مطالب رو مطالعه کردم بسیار مطالب خوب و مفیدی بود با تشکر از دیدگاه های شما .کولر گازی بوش
سلام
با نهایت تشکر از مطلب خوبی که به اشتراک گذاشتید
موفق و سربلند باشید
مممنون و سپاسگذارممممممم