فرمول نویسی در اکسل ۲۰۱۳
بسته آموزش مجازی فرمول نویسی Excel 2013
در نرم افزار راهنماي توابع و فرمول نويسي در اکسل به بررسي روش فرمول نويسي و استفاده از توابع در Excel 2013 ميپردازيم. در نرم افزار راهنماي توابع و فرمول نويسي در اکسل 2013 توابع زير بررسي مي شوند :
مقدمه
تابع چيست؟
توابع حذف کاراکترهاي اضافي
CLEAN : حذف تمام کاراکترهاي غير قابل چاپ.
TRIM : حذف فاصلههاي اضافي و فاصلهها در آغاز و پايان عبارت.
توابع تبديل حروف کوچک و بزرگ
LOWER : تمامي کاراکترها را به حروف کوچک تبديل ميکند.
PROPER : کاراکترهاي موجود در يک متن را به حالت استاندارد تبديل ميکند.
UPPER : تمامي کاراکترها را به حروف بزرگ تبديل ميکند.
تبديل حروف به اعداد به يکديگر
CHAR و UNICHAR : تبديل کد ASCII به حروف.
CODE و UNICODE : تبديل حروف به کد ASCII .
جايگزين کردن قسمتي از يک رشته متن
REPLACE : جاي کاراکتر مورد نظر کاراکتر ديگري را قرار ميدهد.
SUBSTITUTE : جاي عبارت درج شده در يک جمله عبارت ديگري را قرار ميدهد.
توابع دستيابي به قسمتي از يک رشته
CONCATENATE : به هم چسباندن دو يا چند متن.
LEFT : نمايش تعداد حروف سمت چپ سلول مورد نظر.
MID : نمايش بخشي از يک کلمه.
RIGHT : نمايش تعداد حروف سمت راست سلول مورد نظر
REPT : تکرار عبارتي خاص را به تعداد مشخص.
توابع اطلاعاتي
LEN : شمارش تعداد کاراکترهاي موجود در يک سلول.
FIND : پيدا کردن حروف مورد نظر خود در يک عبارت(حساس به کوچکي و بزرگي).
SEARCH : پيدا کردن حروف مورد نظر خود در يک عبارت.
EXACT : مقايسه عبارت موجود در دو سلول.
T : تشخيص متن بودن.
توابع تبديل انواع دادهها در اکسل
VALUE : پارامتر وروردي را به عدد تبديل ميکند.
TEXT : ورودي تابع به متن تبديل ميشود.
FIXED : عدد ورودي را گرد کرده و به متن تبديل ميکند.
توابع عملگر بولي
AND : گزارههاي مختلف را چک کرده و در صورت درست بودن تمامي آنها مقدار درست و در غير اين صورت مقدار نادرست را برميگرداند.
OR : گزارههاي مختلف را چک کرده و در صورت درست بودن يکي آنها مقدار درست و در غير اين صورت مقدار نادرست را برميگرداند.
NOT : اگر مقدارهاي وارد شده با هم برابر باشند مقدار نادرست و اگر نابرابر باشند مقدار درست را برميگرداند.
توابع شرطي
IF : تابع شرطي.
IFERROR : طرح يه معادله و درصورت اشتباه بودن پيغام مورد نظر به نمايش در بيايد.
IFNA : محتوای خروجی خطا را تغییر میدهد.
توابع بازگرداننده مقادير ثابت منطقي
TRUE : مقدار True را برميگرداند.
FALSE : مقدار False را برميگرداند.
توابع اطلاعات خطا
ISERROR : اگر مقدار داده شده بدون خطا باشد مقدار نادرست را بازميگرداند.
ERROR.TYPE : به جاي نمايش error يک عدد نمايش ميدهد.
توابع اطلاعاتي عددي
ISNUMBER : تشخيص عدد بودن يا متن بودن.
ISEVEN : تشخيص زوج بودن.
ISODD : تشخيص فرد بودن.
N : تبديل هر عبارتي به عدد.
توابع ديگر انواع داده
ISBLANK : تشخيص خالي بودن يک سلول.
ISLOGICAL : اگر جواب يک معادله درستي يا نادرستي بود عبارت true را نمايش ميدهد د ر غير اين صورت عبارت false را نشان ميدهد.
ISTEXT : شناسايي توابعي که داراي متن ميباشد.
ISNONTEXT : مخالف تابع ISTEXT ميباشد.
ISREF : مشخص ميکند که عبارت وارد شده يک محدوده از سلول ها است يا خير.
ISFORMULA : بررسی میکند داخل یک سلول تابع یا فرمول وجود دارد یا خیر.
TYPE : مشخص میکند در داخل هر سلول چه نوع کاراکتري وجود دارد.
تابع اطلاعات عمومي
CELL : هر نوع اطلاعاتي که در مورد سلول مورد نظر بخواهيم در اختيار ما قرار ميدهد.
SHEET : شیت مورد نظر چندمین شیت است
SHEETS : تعداد شیتهای موجود در فایل اکسل
INFO : نمايش مشخصات سيستم.
توابع بازگرداننده مقادير ثابت
NA : نمايش پيغام خطاي #N/A .
توابع تاريخ و زمان
DATE : تاريخ مورد نظر.
TIME : زمان مورد نظر.
توابع تاريخ و زمان فعلي
NOW : نمايش تاريخ و زمان جاري سيستم.
TODAY : نمايش تاريخ جاري سيستم.
توابع سازنده زمان
HOUR : از زمان فقط ساعت را نمايش ميدهد.
MINUTE : از زمان فقط دقيقه را نمايش ميدهد.
SECOND : از زمان فقط ثانيه را نمايش ميدهد.
توابع سازنده تاريخ
DAY : از تاريخ فقط روز را نمايش ميدهد.
MONTH : از تاريخ فقط ماه را نمايش ميدهد.
YEAR : از تاريخ فقط سال را نمايش ميدهد.
WEEKNUM : مشخص ميکند که در چندمين هفته از سال قرار داريم.
WEEKDAY : مشخص ميکند که در چندمين روز از هفته قرار داريم.
توابع محاسباتي تاريخ
EDATE : تاريخ را به عقب يا جلو ببريم.
EOMONTH : مشابه تابع EDATE ولي با اين تفاوت که وقتي تاريخ را به جلو ميبريم آخرين روز آن ماه را به نمايش در ميآورد.
WORKDAY : شمارش روزهاي کاري.
WORKDAY.INTL : کاري مشابه با تابع WORKDAY را انجام ميدهد، با اين تفاوت که روزهاي تعطيل را خودمان مشخص ميکنيم.
DAYS : تعداد روزهای بین دو تاریخ
DAYS360 : شمارش روزهاي بين دو تاريخ.
NETWORKDAYS : شمارش روزهاي کاري بين دو تاريخ.
NETWORKDAYS.INTL : شمارش روزهاي کاري بين دو تاريخ.(روزهاي تعطيل را خودمان مشخص ميکنيم.)
توابع آدرس دهي به سلول
ADDRESS : آدرس دهي در اکسل.
INDEX : براي ارجاع از يک يا چند سلول استفاده ميشود.
INDIRECT : با وارد کردن يک رشته آن را تبديل به يک مرجع سلول ميکند.
OFFSET : تعيين محدودهاي از سلول ها ميباشد.
اطلاعات سطر، ستون و محدوده
ROW : مشخص ميکند در چندمين رديف قرار داريم.
COLUMN : مشخص ميکند در چندمين ستون قرار داريم.
ROWS : همانند تابع ROW ميباشد، ولي با اين تفاوت که شما ميتوانيد مقدار چندين سلول را وارد نماييد.
COLUMNS : همانند تابع ROWS عمل ميکند ولي با اين تفاوت که مجموعه رديفها را شمارش ميکند.
AREAS : وظيفه شمارش محدودهها را دارد.
توابع جستجوي داده
HLOOKUP :به صورت افقي به دنبال عبارت مورد نظر ميگردد.
VLOOKUP : به صورت عمودي به دنبال عبارت مورد نظر ميگردد.
LOOKUP : در يک مجموعه از دادهها به دنبال يک مقدار ميگردد
CHOOSE : در يک ليست به صورت رشته شما با وارد کردن يک عدد رشته مورد نظر در ليست را به نمايش در ميآورد.
HYPERLINK : لينک دادن به يک سلول.
FORMULATEXT : فرمول موجود در یک سلول
MATCH : موقعيت نسبي موردي را در يک آرايه مشخص ميکند.
PIVOT TABLE : روش استفاده از جداول محوری
GETPIVOTDATA: استخراج داده از جدول
اطلاعات اوليه اعداد
ABS : محاسبه عمليات قدر مطلق .
SIGN : تشخيص مثبت يا منفي بودن عدد.
GCD : بزرگترين مقسوم عليه دو يا چند عدد را بدست ميآوريد
LCM : کوچکترين مضرب مشترک دو يا چند عدد را نمايش ميدهد.
عملگرهاي رياضي
SUM : محاسبه عمليات جمع.
POWER : عدد اول را به توان عدد دوم ميرساند.
SQRT : محاسبه جذر عدد مورد نظر.
QUOTIENT : استفاده از اين تابع ميتوان قسمت صحيح تقسيم دو عدد را بدست آورد.
MOD : محاسبه باقيمانده دو عدد.
AGGREGATE : مجموعه چندين تابع.
SUBTOTAL : مجموعه چندين برنامه کاربردي.
توابع گرد کننده
CEILING : عدد ورودي اول را به ضريب بزرگتر يا مساوي عدد ورودي دوم در مياورد.
CELING.MATH : همانند تابع فوق است با یک پارامتر بیشتر
EVEN : عدد را به اولين عدد بزرگ و زوج تبديل ميکند.
FLOOR : عدد ورودي اول را به ضريب کوچکتر يا مساوي عدد ورودي دوم در مياورد.
FLOOR.MATH :همانند تابع فوق است با یک پارامتر بیشتر
EVEN: عدد ورودی به اولین عدد بزرگتر از خودش و زوج تبدیل میکند.
INT : عدد مورد نظر را گرد ميکند و به صورت يک عدد صحيح نمايش ميدهد.
MROUND : عدد ورودي اول را به ضريب نزديکترين عدد ورودي دوم در مياورد.
ODD : تبديل عدد به اولين عدد فرد.
ROUND : گرد کردن اعداد.
ROUNDDOWN : وظيفه گرد کردن در يک محدوده از تعداد رقم اعشار يا صحيح به سمت پايين را دارد.
ROUNDUP : وظيفه گرد کردن در يک محدوده از تعداد رقم اعشار يا صحيح به سمت بالا را دارد.
TRUNC : مقدار اعشار يا عدد صحيحي که ميخواهيم نمايش بدهيم.
جمع شرطي
SUMIF : جمع اعداد با شرايط مورد نظر.
عملياتهاي پيشرفته رياضي
SUMPRODUCT : براي ضرب کردن اعداد استفاده ميشود.
SUMSQ : به توان دو رساندن اعداد.
SUMX2MY2 : اين تابع که علاوه بر اين که عددها را به توان دو ميرساند دو به دو از هم کم ميکند و نتيجه را با هم جمع ميکند.
SUMX2PY2 : اين تابع که علاوه بر اين که عددها را به توان دو ميرساند دو به دو با هم جمع ميکند و نتيجه را با هم جمع ميکند.
SUMXMY2 : اعداد مورد نظر را اول از هم کم ميکند و سپس به توان دو ميرساند.
SERIESSUM : مجموعه سري تواني.
توابع ماتریسي
MDETERM : محاسبه دترمينال ماتريس.
MINVERSE : معکوس يک ماتريس مربعي.
MMULT : محاسبه ضرب دو ماتريس.
MUNIT : تعریف یک ماتریس یکه یا واحد
محاسبه فاکتوريل
FACT : محاسبه فاکتوريل.
FACTDOUBLE : ضرب اعداد بين دو عدد مورد نظر( اگر عدد وارد شده فرد باشد فقط اعدا فرد را ضرب ميکند و بالعکس)
MULTINOMIAL : محاسبه فرمول
توابع متفرقه
ROMAN : عدد ورودي را به صورت لاتين نمايش ميدهد.
COMBIN : محاسبه تعداد جابجاييها بدون توجه به ترتيب قرارگيري.
اعداد تصادفي
RAND : يک عدد تصادفي بين صفر تا يک
RANDBETWEEN : يک عدد تصادفي بين دو عدد مورد نظر ما
توابع مثلثات
PI : نمايش عدد پي.
SQRTPI : عدد مورد نظر را در عدد ? ضرب ميکند و سپس جذر آن عدد را نمايش ميدهد.
DEGREES : تبديل زاويه برحسب راديان به درجه.
RADIANS : تبديل درجه را به راديان.
COS : محاسبه مقدار کسينوس يک عدد.
ACOS : محاسبه آرک کسينوس.
COSH : محاسبه کسينوس هيپربليک.
ACOSH : محاسبه آرک کسينوس هيپربليک.
SEC : محاسبه مقدار سکانت یک زاویه که بر حسب رادیان است
SECH : محاسبه مقدار سکانت هیپربلویک یک زاویه که بر حسب رادیان است
SIN : محاسبه سينوس يک عدد.
ASIN : محاسبه آرک سينوس.
SINH : محاسبه سينوس هيپربليک.
ASINH : محاسبه آرک سينوس هيپربليک.
TAN : محاسبه تانژانت.
ATAN : محاسبه آرک تانژانت.
TANH : محاسبه تانژانت هيپربليک.
ATANH : محاسبه آرک تانژانت هيپربليک.
توابع پيدا کردن بزرگترين و کوچکترين مقدار
MAX : پيدا کردن بزرگترين عدد بين چند سلول.
MAXA : همانند تابع MAX ميباشد با اين تفاوت که کلمات هم محاسبه ميکند.
MIN : پيدا کردن کوچکترين عدد بين چند سلول.
MINA : همانند تابع MIN ميباشد با اين تفاوت که کلمات هم محاسبه ميکند.
LARGE : پيدا کردن Nامين عدد بزرگ در يک ليست.
SMALL : پيدا کردن Nامين عدد کوچک در يک ليست.
توابع ديگر
PERMUT : محاسبه جايگشت.
PERMUTATIONA : محاسبه جایگشت که تکرار مجاز است
شمارش سلول
COUNT : مشخص کردن اعداد و تاريخ موجود در يک محدوده.
COUNTA : شمارش سلولهايي که پر هستند.
COUNTBLANK : تعداد سلول هاي خالي را بدست ميآورد.
COUNTIF : شمارش سلول با شرط مورد نظر
COUNTIFS : شمارش سلول با شرايط مورد نظر
ميانگين، فراواني و رتبه بندی
AVERAGE : به دست آوردن ميانگين.
AVERAGEA : به دست آوردن ميانگين. (کاراکترهاي متن هم شمارش ميکند.)
AVERAGEIF : ميانگين به صورت شرطي.
AVERAGEIFS : ميانگين به صورت دو يا چند شرطي.
MODE : شناسايي عدد تکرار شده.
MODE.SNGL : شناسايي عدد تکرار شده.
MODE.MULT : نمايش n عدد پر تکرار
GEOMEAN : محاسبه ميانگين هندسي.
HARMEAN : محاسبه ميانگين هارمونيک.
RANK : رتبهبندي اعداد موجود در يک ليست.
RANK.EQ : رتبهبندي اعداد موجود در يک ليست.
RANK.AVG : مشابه تابع RANK.EQ ميباشد، با اين تفاوت که اگر در ليست اعداد دو عدد تکراري وجود داشته باشد ميانگين دو عدد را نمايش ميدهد.
KURT : درجه اوج در يک نمودار اماري را نشان ميدهد.
PERCENTILE : محاسبه Kامين صدک.
PERCENTILE.INC : محاسبه Kامين صدک.
PERCENTILE.EXC : اين تابع کاملا شبيه تابع PERCENTILE.INC ميباشد، با اين تفاوت که تابع PERCENTILE.INC به صورت شامل يا inclusive ميباشد اما تابع PERCENTILE.EXC به صورت انحصاري يا exclusive است.
PERCENTRANK : براي محاسبه رتبه در مجموعه يا آرايه است.
PERCENTRANK.INC : براي محاسبه رتبه در مجموعه يا آرايه است.
PERCENTRANK.EXC : اين تابع کاملا شبيه تابع PERCENTRANK.INC ميباشد، با اين تفاوت که تابع PERCENTRANK.INC به صورت شامل يا inclusive ميباشد اما تابع PERCENTRANK.EXC به صورت انحصاري يا exclusive است.
QUARTILE : محاسبه چارك مجموعه اي از دادهها.
QUARTILE.INC : محاسبه چارك مجموعه اي از دادهها.
QUARTILE.EXC : اين تابع کاملا شبيه تابع QUARTILE.INC ميباشد، با اين تفاوت که تابع QUARTILE.INC به صورت شامل يا inclusive ميباشد اما تابع QUARTILE.EXC به صورت انحصاري يا exclusive است.
FREQUENCY : محاسبه فراوانی
انحراف و واريانس
AVEDEV : محاسبه متوسط انحراف معيار .
STDEV : محاسبه انحراف معيار نمونه.
STDEV.S : محاسبه انحراف معيار نمونه.
STDEVP : محاسبه انحراف معيار جامعه.
STDEV.P : محاسبه انحراف معيار جامعه.
VAR : واريانس يک مجموعه داده
VAR.S : واريانس يک مجموعه داده
VARP : محاسبه واريانس جامعه.
VAR.P : محاسبه واريانس جامعه.
COVAR : محاسبه كوواريانس است كه رابطه بين دو مجموعه را تعيين ميكند.
COVARIANCE.NORM : محاسبه كوواريانس است كه رابطه بين دو مجموعه را تعيين ميكند.
CONFIDENCE : محاسبه فاصله اطمينان ميانگين جامعه.
CONFIDENCE.NORM : محاسبه فاصله اطمينان ميانگين جامعه.
DEVSQ : محاسبه مجموع مربعات انحراف از معيارها.
توابع خط روند
INTERCEPT : محاسبه ضريب زاويه رگرسيون خطي.
SLOPE : محاسبه ضريب زاويه رگرسيون خطي.
GROWTH : محاسبه رشد جمعيت.
STEYX : محاسبه خطاي استاندارد Y پيشبيني شده.
FORECAST : محاسبه محل نقطه y.
TREND : Trend يا سير روند؛ نمايشي گرافيگي از روند سري دادههاست.
LOGEST : پارامترهاي يک روند نمايي براي عرضه مجموعه اي از x و y را برمي گرداند.
LINEST : بازگرداندن اطلاعات آماري توصيف روند خط بهترين برازش، از طريق يک سري عرضه شده از X-Y
BETADIST : محاسبه توزيع بتا
BETA.DIST : محاسبه توزيع بتا
BETAINV : محاسبه مقدار توزيع انباشته (تجمعي) تابع بتا ميپردازد. که اين بار احتمال را ميدهيم و مقدار x را بدست ميآوريم.
BETA.INV : محاسبه مقدار توزيع انباشته (تجمعي) تابع بتا ميپردازد. که اين بار احتمال را ميدهيم و مقدار x را بدست ميآوريم. (جديد در Excel 2010)
BINOMDIST : محاسبه توزيع بينم
BINOM.DIST : محاسبه توزيع بينم.
NEGBINOMDIST : محاسبه توزيع بينم منفي.
NEGBINOM.DIST : محاسبه توزيع بينم منفي.
CRITBINOM : مکمل تابع دو را انجام ميدهد.
BINOM.INV : کار مکمل تابع دو جملهاي را انجام ميدهد.
CHIDIST : احتمالات يک طرفه توزيع مجذور کاي (خي دو ) را بر ميگرداند.
CHISQ.DIST.RT : احتمالات يک طرفه توزيع مجذور کاي (خي دو ) را بر ميگرداند.
CHISQ.DIST : محاسبه توزيع مجذور و مربع( يا همان توزيع تجمعي و چگالي احتمال) استفاده ميکنيم
CHIINV : معکوس احتمال دم سمت راست توزيع مجذور کاي.
CHISQ.INV.RT : معکوس احتمال دم سمت راست توزيع مجذور کاي.
CHISQ.INV : معکوس احتمال دم سمت چپ توزيع مجذور کاي.
CHITEST : محاسبه آزمون چي يا Chi-Square
CHISQ.TEST : محاسبه آزمون چي يا Chi-Square.
CORREL : محاسبه ضريب همبستگي پيرسون.
EXPONDIST : محاسبه توزيع نمايي.
EXPON.DIST : محاسبه توزيع نمايي.
FDIST : محاسبه دم سمت راست توزيع احتمال F .
F.DIST.RT : محاسبه دم سمت راست توزيع احتمال F .
FINV : محاسبه معکوس دم راست توزيع احتمال F.
F.INV.RT : محاسبه معکوس دم راست توزيع احتمال F.
F.INV : محاسبه معکوس توزيع تجمعي F.
FISHER : تبديل فيشر.
FISHERINV : معکوس تبديل فيشر
FTEST : انجام آزمون F روي دو سري از دادهها.
F.TEST : انجام آزمون F روي دو سري از دادهها.
GAMMADIST : محاسبه مقدار تابع گاما را براي x معين.
GAMMA.DIST : محاسبه مقدار تابع گاما را براي x معين.
GAMMAINV : معکوس توز�