• 104 فرمول کاربردی و مهم در اکسل + مثال

    در این پست تعداد 104 فرومل به همراه مثال مرتبط با آن را در اختیار شما کاربران محترم قرار میدهیم
    این پست شامل فایل دانلود می باشد مشاهده

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

      

    1   جمع مقادیر
      مثال =SUM(5,6,9)
      نتیجه 20
           
    2   جمع مقادیر در یک محدوده
      مثال =SUM(A1:A2)
      نتیجه 125
           
    3   جمع مقادیر در محدوده یک جدول
      مثال =SUM(staff[Salary])
      نتیجه $ 945,000
           
    4   جمع مقادیر با شرایط خاص در یک محدوده
      مثال =SUMIFS(staff[Salary], staff[Department],"Sales")
      نتیجه $ 279,000
           
    5   جمع مقادیر بزرگتر/کوچکتر/... در یک محدوده
      مثال =SUMIFS(A1:A6, A1:A6,">25")
      نتیجه 100
           
    6   مجموع اعدادی که در حالت فیلتر می باشند
      مثال =SUBTOTAL(109,staff[Salary])
      نتیجه $ 945,000
           
    7   تعداد اعدادی که در حالت فیلتر می باشند
      مثال =SUBTOTAL(103,staff[Name])
      نتیجه 13
           
    8   جمع مقادیر در یک ستون
      مثال =SUM($A$1:A1)
      نتیجه 10
           
    9   تعداد اعداد در محدوده
      مثال =COUNT(A1:A3)
      نتیجه 6
           
    10   شمارش تمام مقادیر (از جمله متن)
      مثال =COUNTA(staff[Name])
      نتیجه 13
           
    11   تعداد مقادیر خالی در ورودی (محدوده یا ستون جدول)
      مثال =COUNTBLANK(A1:A10)
      نتیجه 14
           
    12   شمارش تعداد مقادیر غیر خالی
      مثال =ROWS(A1:A10)-COUNTBLANK(A1:A10)
      نتیجه 6
           
    13   تعداد دفعات مشاهده چند مورد شرایط داده شده
      مثال =COUNTIFS(staff[Department],"IT")
      نتیجه 3
           
    14   تعداد دفعات مشاهده متن با شروع حروف خاص
      مثال =COUNTIFS(staff[Name],"J*")
      نتیجه 13
           
    15   تعداد دفعات مشاهده متن با خاتمه حروف خاص
      مثال =COUNTIFS(staff[Name],"*n")
      نتیجه 4
           
    16   تعداد دفعات مشاهده متن با شرط خاص در بین حروف
      مثال =COUNTIFS(staff[Name],"*an*")
      نتیجه 3
           
    17   میانگین اعداد داده شده
      مثال =AVERAGE(staff[Salary])
      نتیجه $ 72,692
           
    18   میانگین اعداد داده شده با شرایط خاص
      مثال =AVERAGEIFS(staff[Salary],staff[Department], "HR")
      نتیجه $ 77,333
           
    19   میانگین عدد مثبت
      مثال =AVERAGEIFS(A1:A10,A1:A10,">0")
      نتیجه 25.83
           
    20   میانگین اعداد بالا یا پایین به جزء 10 ٪ ارزش
      مثال =TRIMMEAN(staff[Salary],10%)
      نتیجه $ 72,692
           
    21   میانگین 7 روز هفته (این فرمول را در سلول اول تایپ کنید و میانگین متحرک را بکشید)
      مثال =AVERAGE(A1:A4)
      نتیجه 25.83
           
    22   میانگین وزنی اعداد
      مثال =SUMPRODUCT(A1:A6,B1:B6)
      نتیجه 2550
           
    23   میانگین طیف وسیعی از مقادیر
      مثال =MEDIAN(staff[Salary])
      نتیجه $ 76,000
           
    24   بیشترین تعداد مکرر در محدوده
      مثال =MODE.SNGL(1,2,3,3,2,1,1,5,6,7,3,4,8,9)
      نتیجه 1
           
    25   چارک آماري مقادير داده شده
      مثال =QUARTILE.EXC(staff[Salary],1)
      نتیجه $ 59,500
           
    26   90% (یا هر) درصد از مقادیر داده شده
      مثال =PERCENTILE.EXC(staff[Salary],0.9)
      نتیجه $ 89,000
           
    27   حداقل مقدار در میان یک لیست از اعداد
      مثال =MIN(A1:A3)
      نتیجه 10
           
    28   3 عدد کوچک (یا هر) در میان یک لیست
      مثال =SMALL(staff[Salary],3)
      نتیجه $ 59,000
           
    29   رتبه یک عدد در یک لیست از مقادیر
      مثال =RANK.AVG(76000, staff[Salary])
      نتیجه 7
           
    30   حداکثر مقدار از یک لیست از مقادیر
      مثال =MAX(staff[Salary])
      نتیجه $ 89,000
           
    31   دومین مقدار بزرگترین در یک محدوده
      مثال =LARGE(A2:A7,2)
      نتیجه 30
           
    32   باقی مانده پس از تقسیم دو عدد
      مثال =MOD(31,7)
      نتیجه 3
           
    33   گرد کردن یک عدد به نزدیکترین عدد یا کسر کل
      مثال =ROUND(PI(),4)
      نتیجه 3.1416
           
    34   گرد کردن یک عدد به نزدیکترین چند
      مثال =MROUND(27,4)
      نتیجه 28
           
    35   قسمت صحیح  یک عدد
      مثال =INT(19/7)
      نتیجه 2
           
    36   درصد تغییر (واریانس) از یک مقدار به دیگری (H4=35000, H5=38000)
      مثال =H5/H4-1
      نتیجه 8.57%
           
    37   قسمت اعشاری از عدد
      مثال =MOD(PI(),1)
      نتیجه 0.141592654
           
    38   ارزش مطلق عدد
      مثال =ABS(30-43)
      نتیجه 13
           
    39   محاسبه قدرت یک عدد به دیگری
      مثال =7^3
      نتیجه 343
           
    40   بررسی شرایط و خروجی یکی از دو مقدار ممکن
      مثال =IF(A9>20,"Too high", "Too low")
      نتیجه Too low
           
    41   بررسی کنید که شرایط چندگانه درست است (AND)
      مثال =AND(A9>5,B9<20)
      نتیجه FALSE
           
    42   بررسی کنید که شرط درست است (OR)
      مثال =OR(E10="Sales", F10>90000, D10=A9)
      نتیجه FALSE
           
    43   استفاده از توابع AND و OR و IF به صورت همزمان در یک دستور
      مثال =IF(OR(AND(A1=10,A1=20),AND(B1=15;C1=15));"True";"False")
      نتیجه FALSE
           
    44   بررسی کنید که آیا این یا آن [منحصراَ  OR] (درست است اگر هر سلول A1 یا B1 از 10 بزرگتر باشد)
      مثال =XOR(A9>10, B9>10)
      نتیجه FALSE
           
    45   یکی را در میان مقادیر متعدد انتخاب کنید
    یک مقدار از یک لیست از مقادیر را می گیرد ، در این صورت ، A2 را به عنوان مقدار 3rd قرار می دهد
      مثال =CHOOSE(3,A9,B10,A10,B11)
      نتیجه 0
           
    46   چند شرایط به عنوان IFS 
      مثال =ifs(A9>10, "This is too high", A9>5, "This is ok", A9>2,"Almost low", A9<=2,"Really low")
      نتیجه This is ok
           
    47   بررسی کنید که آیا یک مقدار در یک لیست وجود دارد
      مثال =IF(COUNTIFS(staff[Name],"Jan")>0,"Yes, Jan is in there","No, no such person")
      نتیجه Yes, Jan is in there
           
    48   بررسی شرایط متعدد به عنوان IF تو در تو
      مثال =IF(A9>10, "This is too high",IF( A9>5, "This is ok", IF(A9>2,"Almost low", "Really low")))
      نتیجه Really low
           
    49   بررسی کنید که آیا یک مقدار بین دو مقدار دیگر است
      مثال =IF(AND(A9>=10,A9<=20),"Between 10 and  20","Nope, not between 10 and 20")
      نتیجه Nope, not between 10 and 20
           
    50   آیا سلول خالی است؟
      مثال =ISBLANK(A9)
      نتیجه TRUE
           
    51   آیا سلول دارای ارزش است؟
      مثال =ISEVEN(7)
      نتیجه FALSE
           
    52   آیا سلول دارای ارزش نامتعارف است؟
      مثال =ISODD(7)
      نتیجه TRUE
           
    53   آیا یک سلول شامل عدد است ؟
      مثال =ISNUMBER("A")
      نتیجه FALSE
           
    54   آیا یک سلول شامل فرمول است ؟
      مثال =ISFORMULA(A1)
      نتیجه FALSE
           
    55   آیا در یک سلول (یا فرمول)  عملیات یا درخواست به پایان می رسد خطا دارد؟
      مثال =ISERROR(7)
      نتیجه TRUE
           
    56   تبدیل متن به حروف کوچک
      مثال =LOWER("hello")
      نتیجه hello
           
    57   تبدیل متن به حروف بزرگ
      مثال =UPPER("james")
      نتیجه JAMES
           
    58   تبدیل متن به مورد استاندارد (حرف اول کلمه با حروف بزرگ)
      مثال =PROPER("this is a long sentence")
      نتیجه This Is A Long Sentence
           
    59   ترکیب مقادیر متن متفاوت به یک متن
      مثال =CONCATENATE(A3, " and ", A4)
      نتیجه 30 and 25
           
    60   استخراج چند حرف اول از یک متن
      مثال =LEFT("India",3)
      نتیجه Ind
           
    61   استخراج چند حرف آخر از یک متن
      مثال =RIGHT("New Zealand",4)
      نتیجه land
           
    62   استخراج بخش وسط از متن داده شده
      مثال =MID("United States",4,5)
      نتیجه ted S
           
    63   استخراج طول متن
      مثال =LEN("Chandoo.org")
      نتیجه 11
           
    64   جایگزین یک کلمه با یکی دیگر
      مثال =SUBSTITUTE("Microsoft Excel","cel","cellent")
      نتیجه Microsoft Excellent
           
    65   جایگزینی برخی از حروف با  حروف دیگر
      مثال =REPLACE("abc@email.com",5,1,"g")
      نتیجه abc@gmail.com
           
    66   پیدا کردن یک عبارتدر متن
      مثال =FIND("soft","Microsoft Excel")
      نتیجه 6
           
    67   استخراج اسامی از یک نام (بطور مثال مخفف نام Bill Jelen)
      مثال =LEFT(H1,1)&MID(H1,FIND(" ",H1)+1,1)
      نتیجه BJ
           
    68   پیدا کردن چند کلمه در جمله
      مثال =LEN(H2)-LEN(SUBSTITUTE(H2," ",""))+1
      نتیجه 10
           
    69   فضاهای غیر ضروری را از یک سلول حذف کنید
      مثال =TRIM("chandoo.org")
      نتیجه chandoo. org
           
    70   حذف هر چیزی پس از یک نماد یا کلمه (حذف حروف بعد از @ در  someone@something.com)
      مثال =LEFT(H3,FIND("@",H3)-1)
      نتیجه someone
           
    71   تاریخ امروز چیست ؟
      مثال =TODAY()
      نتیجه 6/27/2018
           
    72   زمان و تاریخ فعلی چیست ؟
      مثال =NOW()
      نتیجه 6/27/2018 12:00
           
    73   یک مقدار تاریخ را از سال ، ماه و روز ایجاد کنید
      مثال =DATE(2018,10,20)
      نتیجه 10/20/2018
           
    74   یک مقدار زمان از ساعت ، دقیقه و ثانیه
      مثال =TIME(9,45,21)
      نتیجه 0/40625
           
    75   استخراج روز  از تاریخ داده شده
      مثال =DAY(TODAY())
      نتیجه 27
           
    76   استخراج ماه از تاریخ داده شده
      مثال =MONTH(DATEVALUE("12-July-1999"))
      نتیجه 7
           
    77   استخراج سال از تاریخ داده شده
      مثال =YEAR(TODAY())
      نتیجه 2018
           
    78   پیدا کردن روز هفته (شماره) از یک تاریخ
      مثال =WEEKDAY(TODAY())
      نتیجه 4
           
    79   پیدا کردن روز هفته (نام روز) از تاریخ
      مثال =TEXT(TODAY(), "DDDD")
      نتیجه Wednesday
           
    80   نام یک ماه از یک تاریخ چیست ؟
      مثال =TEXT(TODAY(), "MMMM")
      نتیجه June
           
    81   استخراج ساعت از زمان
      مثال =HOUR(NOW())
      نتیجه 12
           
    82   استخراج دقیقه از زمان
      مثال =MINUTE(NOW())
      نتیجه 0
           
    83   استخراج ثانیه از زمان
      مثال =SECOND(NOW())
      نتیجه 2
           
    84   چه تاریخی پس از یا قبل از ماه X است
      مثال =EDATE(TODAY(),3)
      نتیجه 9/27/2018
           
    85    تاریخ ماه چیست ؟
      مثال =EOMONTH(DATE(2018,8,1),0)
      نتیجه 8/31/2018
           
    86   تعداد روزهای بین دو تاریخ را محاسبه کنید
      مثال =DATE(2018,12,1)-DATEVALUE("1-july-2018")
      نتیجه 153
           
    87   تعداد سالهای بین دو تاریخ را محاسبه کنید
      مثال =YEARFRAC(DATE(2009,9,24),TODAY(),1)
      نتیجه 8.76
           
    88   تعداد هفته ها بین دو تاریخ
      مثال =INT((DATE(2018,12,1)-DATEVALUE("1-july-2018"))/7)
      نتیجه 21
           
    89   تاریخ بعد یا قبل از  روز کاری (به جز تعطیلات آخر هفته و غیره) چیست ؟
      مثال =WORKDAY.INTL(TODAY(),12)
      نتیجه 7/13/2018
           
    90   چند روز کاری بین دو تاریخ وجود دارد؟
      مثال =NETWORKDAYS.INTL(TODAY(),DATE(2018,12,31))
      نتیجه 134
           
    91   در یک جدول یک مقدار جستجو کنید و موارد مربوطه را پیدا کنید
      مثال =VLOOKUP("John", staff, 3, FALSE)
      نتیجه $ 77,000
           
    92   یک الگوی را در یک جدول جستجو کنید و موارد مربوطه را پیدا کنید 
      مثال =VLOOKUP("Jon*", staff,2,FALSE)
      نتیجه Production
           
    93   موقعیت یک مقدار در یک لیست چیست؟
      مثال =MATCH(76000,staff[Salary],0)
      نتیجه 10
           
    94   سلول را به عدد تبدیل کنید
      مثال =NUMBERVALUE("123.456,78",",",".")
      نتیجه 123,456.78
           
    95   مقدار را به تاریخ تبدیل کنید
      مثال =DATEVALUE("1-jul-2018")
      نتیجه 43107
           
    96   سلول را به عدد تبدیل کنید
      مثال ="12456.78"*1
      نتیجه 12,456.78
           
    97   اگر در یک فرمول خطا وجود داشته باشد میتوانیم مقدار دیگری را نمایش دهیم
      مثال =IFERROR(VLOOKUP("Sam",staff,3,FALSE),"Employee not found")
      نتیجه Employee not found
           
    98   اگر در یک فرمول دارای خطای NA وجود داشته باشد، مقدار دیگری را نشان دهید
      مثال =IFNA(7/0,"This will appear if the error is #N/A")
      نتیجه #DIV/0!
           
    99   آیا یک سلول (یا فرمول) به خطا می رسد؟
      مثال =ISERROR(7)
      نتیجه TRUE
           
    100   با خیال راحت یک عدد را با عدد دیگر تقسیم کنید
      مثال =IF(A2=0,"",A1/A2)
      نتیجه 0.5
           
    101   عدد تصادفی ایجاد کنید (در هر بار تغییر در صفحه، خروجی تغییر می کند)
      مثال =RAND()
      نتیجه 0.948708709
           
    102   شماره تلفنی تصادفی ایجاد کنید (در هر بار تغییر در صفحه، خروجی تغییر می کند)
      مثال =RANDBETWEEN(1000000000,9999999999)
      نتیجه 4658756694
           
    103   نامه ای تصادفی از الفبای تولید کنید
    در هر بار تغییر در صفحه، خروجی تغییر می کند.
      مثال =CHAR(RANDBETWEEN(CODE("A"),CODE("Z")))
      نتیجه Z
           
    104   از لیست مقادیر گزینه تصادفی ایجاد کنید
      مثال =INDEX(staff[Name], RANDBETWEEN(1,COUNTA(staff[Name])))
      نتیجه June
           

     



    برای دانلود فایل های پیوست، می بایست در سایت ثبت نام و وارد شوید

    ورود، ثبت نام


    نظرات ارسال شده ارسال نظر جدید
    برای تبادل نظر، می بایست در سایت وارد شوید

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