توابع آماری Statistical functions در excel 2007

Statistical functions

Function	Description
AVEDEV	Returns the average of the absolute deviations of data points from their mean انحراف متوسط داده ها- در این تابع ابتدا قدرمطلق فاصله هر متغییر از میانگین سنجیده سپس حاصل جمع به تعداد تقسیم می گردد .
AVERAGE	Returns the average of its arguments میانگین داده ها در این تابع سلول های خالی و داده های متنی محاسبه نمی شوند.
AVERAGEA	Returns the average of its arguments, including numbers, text, and logical values میانگین کل :  انواع داده ها ی متنی و عددی در محاسبه منظور می گردند.   TRUE 1 و FALSE  0
AVERAGEIF	Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria محاسبه میانگین با احتساب یک شرط
AVERAGEIFS	Returns the average (arithmetic mean) of all cells that meet multiple criteria. میانگین محدوده با احتساب چند شرط
BETADIST	Returns the beta cumulative distribution function
BETAINV	Returns the inverse of the cumulative distribution function for a specified beta distribution
BINOMDIST	Returns the individual term binomial distribution probability
CHIDIST	Returns the one-tailed probability of the chi-squared distribution
CHIINV	Returns the inverse of the one-tailed probability of the chi-squared distribution
CHITEST	Returns the test for independence
CONFIDENCE	Returns the confidence interval for a population mean
CORREL	Returns the correlation coefficient between two data sets
COUNT	Counts how many numbers are in the list of arguments شمارش تعداد سلول های حاوی عدد
COUNTA	Counts how many values are in the list of arguments شمارش سلول های غیر خالی
COUNTBLANK	Counts the number of blank cells within a range شمارش سلول های خالی
COUNTIF	Counts the number of nonblank cells within a range that meet the given criteria شمارش تعداد سلول های حاوی شرط
COVAR	Returns covariance, the average of the products of paired deviations محاسبه کوواریانس
CRITBINOM	Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
DEVSQ	Returns the sum of squares of deviations
EXPONDIST	Returns the exponential distribution
FDIST	Returns the F probability distribution
FINV	Returns the inverse of the F probability distribution
FISHER	Returns the Fisher transformation
FISHERINV	Returns the inverse of the Fisher transformation
FORECAST	Returns a value along a linear trend
FREQUENCY	Returns a frequency distribution as a vertical array
FTEST	Returns the result of an F-test
GAMMADIST	Returns the gamma distribution
GAMMAINV	Returns the inverse of the gamma cumulative distribution
GAMMALN	Returns the natural logarithm of the gamma function, Γ(x)
GEOMEAN	Returns the geometric mean
GROWTH	Returns values along an exponential trend
HARMEAN	Returns the harmonic mean
HYPGEOMDIST	Returns the hypergeometric distribution
INTERCEPT	Returns the intercept of the linear regression line
KURT	Returns the kurtosis of a data set محاسبه کشیدگی توزیع
LARGE	Returns the k-th largest value in a data set کا امین عدد بزرگ توزیع - اگر کا عدد یک باشد همان ماکس را نمایش می دهد
LINEST	Returns the parameters of a linear trend
LOGEST	Returns the parameters of an exponential trend
LOGINV	Returns the inverse of the lognormal distribution
LOGNORMDIST	Returns the cumulative lognormal distribution
MAX	Returns the maximum value in a list of arguments بزرگترین عدد محدوده
MAXA	Returns the maximum value in a list of arguments, including numbers, text, and logical values
MEDIAN	Returns the median of the given numbers
MIN	Returns the minimum value in a list of arguments
MINA	Returns the smallest value in a list of arguments, including numbers, text, and logical values
MODE	Returns the most common value in a data set
NEGBINOMDIST	Returns the negative binomial distribution
NORMDIST	Returns the normal cumulative distribution رتبه درصدی یک داده را در یک توزیع نرمال ،با میانگین و انحراف استاندارد مشخص نمایش می دهد
NORMINV	Returns the inverse of the normal cumulative distribution یافتن نمره خام یا استاندارد معادل با رتبه درصدی در یک توزیع نرمال( رتبه درصدی می گیرد ، داده را نمایش می دهد )
NORMSDIST	Returns the standard normal cumulative distribution رتبه درصدی یک داده را در یک توزیع نرمال استاندارد، نمایش می دهد
NORMSINV	Returns the inverse of the standard normal cumulative distribution یافتن نمره خام یا استاندارد معادل با رتبه درصدی در یک توزیع نرمال استاندارد
PEARSON	Returns the Pearson product moment correlation coefficient ضریب همبستگی پیرسون را نمایش می دهد این شاخص که ضریب معتبری برای تعیین رابطه بین متغییر هاست تنها جهت و شدت رابطه بین دو متغییر را نمایش می دهد و نباید تفسیر آن بر حسب درصد باشد.به عنوان مثال ضریب 70% ،هفتاد درصد از رابطه بین دو متغییر را تبیین نمی کند .0
PERCENTILE	Returns the k-th percentile of values in a range نقاط درصدی همانند رتبه های درصدی اندازه های ترتیبی هستند و با استفاده از آنها می توان تعیین کرد چه عددی توزیع را به درصدهای مختلف تقسیم می کند
PERCENTRANK	Returns the percentage rank of a value in a data set محاسبه رتبه درصدی یک داده در یک توزیع- این رتبه به ما میگوید که چند درصد نمره ها در توزیع در زیر آن قرار گرفته اند
PERMUT	Returns the number of permutations for a given number of objects
POISSON	Returns the Poisson distribution
PROB	Returns the probability that values in a range are between two limits
QUARTILE	Returns the quartile of a data set محاسبه چارک های توزیع
RANK	Returns the rank of a number in a list of numbers رتبه بندی داده های توزیع
RSQ	Returns the square of the Pearson product moment correlation coefficient
SKEW	Returns the skewness of a distribution میزان کجی منحنی توزیع را نمایش می دهد
SLOPE	Returns the slope of the linear regression line
SMALL	Returns the k-th smallest value in a data set کا امین عدد کوچک مجموعه را نمایش می دهد .0
STANDARDIZE	Returns a normalized value محاسبه نمره زی یا زد یا استاندارد سازی نمره فرض کنید در یک آزمون که میانگین و انحراف استاندارد آن به ترتیب 75 و 10 می باشد نمره شما 95 شده است .هنگامی که این نمره را به زد تبدیل می کنید ، تابع عدد 2 را نمایش می دهد که بیانگر عملکرد شما در آزمون با توجه به توزیع نمره هاست.یعنی نمره شما دو انحراف استاندارد بالاتر از میانگین کلاس قرار دارد. 0
STDEV	Estimates standard deviation based on a sample انحراف معیار نمونه
STDEVA	Estimates standard deviation based on a sample, including numbers, text, and logical values انحراف معیار نمونه با احتساب متن و عدد
STDEVP	Calculates standard deviation based on the entire population انحراف معیار کل جمعیت
STDEVPA	Calculates standard deviation based on the entire population, including numbers, text, and logical values انحراف معیار کل جمعیت با احتساب متن و عدد
STEYX	Returns the standard error of the predicted y-value for each x in the regression
TDIST	Returns the Student's t-distribution این تابع مقدار عددی ، درجه آزادی و دامنه را دریافت کرده و احتمال شانسی بودن اختلاف بین میانگین نمونه و جامعه را نشان می دهد
TINV	Returns the inverse of the Student's t-distribution عدد موجود در جدول t  را بر اساس درجه آزادی و p  یا احتمال شانسی بودن اختلاف بین میانگین نمونه و جامعه  نمایش می دهد.در حقیقت این تابع بجای جدول t عمل می کند یعنی کاربر درجه آزادی وp  را وارد کرده و سپس تابع عدد مورد نظر را نمایش میدهد.اگر عدد بدست آمده از  t  محاسبه شده کوچکتر باشد فرض صفر رد می شود .
TREND	Returns values along a linear trend
TRIMMEAN	Returns the mean of the interior of a data set
TTEST	Returns the probability associated with a Student's t-test
VAR	Estimates variance based on a sample محاسبه واریانس نمونه ای از مجموعه
VARA	Estimates variance based on a sample, including numbers, text, and logical values واریانس گیری از تمام عناصر ( متن و عدد)0
VARP	Calculates variance based on the entire population محاسبه واریانس کل مجموعه
VARPA	Calculates variance based on the entire population, including numbers, text, and logical values
WEIBULL	Returns the Weibull distribution
ZTEST	Returns the one-tailed probability-value of a z-test

 

منابع

1. راهنمای اکسل ۲۰۰۷
2. کتاب - احتمالات و آمار کاربردی در روانشناسی و علوم تربیتی - دکتر علی دلاور
3. جزوه توابع آماری اکسل - بهرام صمدیان AMAR80.BLOGFA.COM      

 

قالب بندی شرطی در اکسل 2007

برای ایجاد یک قالب بندی همراه با شرط در اکسل ۲۰۰۷ ابتدا محدوده مورد نظر را انتخاب کرده سپس از مسیر    HOME > CONDITIONAL FORMATTING  را فعال می کنیم . این ابزار دو بخش مجزای تعیین شرط را طراحی کرده است ؛ که اولی همان گزینه CUSTOM  و دومی گزینه TOP 10  در نسخه 2003 می باشند . در حقیقت در اولی کاربران شرط خاصی را برای قالب بندی مشخص می کنند و در دومی درصد یا تعداد خاصی را مشاهده خواهند کرد .

1- HIGHLIGHT CELLS RULES

2-  TOP/BUTTON RULES

 

 

در بخش اول شرط های بزرگتر ،کوچکتر، مساوی ، بین و ... و در بخش دوم درصد یا تعدادی از بالا یا پایین لیست را رنگی می کند . یکی از گزینه های جدید above , below average است که داده های بالاتر وپایین تر از شاخص میانگین را رنگی می کند.

یکی دیگر از ویژگی های جدید اکسل ۲۰۰۷، یافتن سلول های تکراری و غیر تکراری است.گزینه duplicate value  این وظیفه را به خوبی انجام میدهد.جالب اینجاست که بدانید دیگر مانند اکسل ۲۰۰۳ تعداد شرط ها به ۳ محدود نمی شود و کاربران اکسل ۲۰۰۷ قادرند به صورت نامحدود ، محدوده خود را ، با قالب بندی های شرطی ، مشخص کنند .

حذف داده های تکراری

 

قابلیت جدید اکسل 2007 در حذف داده های تکراری یکی از مزایای انکارناپذیر این صفحه گسترده است.

مسیر اجرا : انتخاب محدوده مورد نظر      DATA>REMOVE DUPLICATES حال کلید SELECT ALL>OK

 

 

 

تغییرات اساسی اکسل 2007 و ویژگی های محاسباتی آن

 

Worksheet and workbook specifications and limits

ویژگی های محاسباتی اکسل 2007

تعداد کاربرگهای نامحدود یکی از ویپگی های خارق العاده اکسل 2007 محسوب می شود.

Feature	Maximum limit
Open workbooks	Limited by available memory and system resources
Worksheet size	1,048,576 rows by 16,384 columns
Column width	255 characters
Row height	409 points
Page breaks	1,026 horizontal and vertical تعداد کاراکترهای درون یک سلول
Total number of characters that a cell can contain	32,767 characters
Characters in a header or footer	255
Sheets in a workbook	Limited by available memory (default is 3 sheets)   اکسل 2007 از 16 میلیون رنگ برای نمودارهایش استفاده می کند
Colors in a workbook	16 million colors (32 bit with full access to 24 bit color spectrum)
Named views (view: A set of display and print settings that you can name and apply to a workbook. You can create more than one view of the same workbook without saving separate copies of the workbook.) in a workbook	Limited by available memory
Unique cell formats/cell styles	64,000
Fill styles	32
Line weight and styles	16
Unique font types	1,024 global fonts available for use; 512 per workbook
Number formats in a workbook	Between 200 and 250, depending on the language version of Excel that you have installed
Names in a workbook	Limited by available memory
Windows in a workbook	Limited by available memory
Panes in a window	4
Linked sheets	Limited by available memory
Scenarios (scenario: A named set of input values that you can substitute in a worksheet model.)	Limited by available memory; a summary report shows only the first 251 scenarios
Changing cells in a scenario	32
Adjustable cells in Solver	200
Custom functions	Limited by available memory
Zoom range	10 percent to 400 percent
Reports	Limited by available memory
Sort references	64 in a single sort; unlimited when using sequential sorts
Undo levels	100
Fields in a data form	32
Workbook parameters	255 parameters per workbook
Filter drop-down lists	10,000

 

Calculation specifications and limits

Feature	Maximum limit
Number precision	15 digits
Largest number allowed to be typed into a cell	9.99999999999999E+307
Largest allowed positive number	1.79769313486231E+308
Smallest allowed negative number	-2.2251E-308
Smallest allowed positive number	2.229E-308
Largest allowed negative number	-1E-307
Length of formula contents	8,192 characters
Internal length of formula	16,384 bytes
Iterations	32,767
Worksheet arrays	Limited by available memory
Selected ranges	2,048
Arguments in a function	255
Nested levels of functions	64
User defined function categories	255
Number of available worksheet functions	341
Size of the operand stack	1,024
Cross-worksheet dependency	64,000 worksheets that can refer to other sheets
Cross-worksheet array formula dependency	Limited by available memory
Area dependency	Limited by available memory
Area dependency per worksheet	Limited by available memory
Dependency on a single cell	4 billion formulas that can depend on a single cell
Linked cell content length from closed workbooks	32,767
Earliest date allowed for calculation	January 1, 1900 (January 1, 1904, if 1904 date system is used)
Latest date allowed for calculation	December 31, 9999
Largest amount of time that can be entered	9999:59:59

 

Charting specifications and limits

Feature	Maximum limit
Charts linked to a worksheet	Limited by available memory
Worksheets referred to by a chart	255
Data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) in one chart	255
Data points (data points: Individual values that are plotted in a chart. Related data points make up a data series. Data points are represented by bars, columns, lines, slices, dots, and other shapes. These shapes are called data markers.) in a data series for 2-D charts	32,000
Data points in a data series for 3-D charts	4,000
Data points for all data series in one chart	256,000

 

PivotTable and PivotChart report specifications and limits

Feature	Maximum limit
PivotTable reports (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) on a sheet	Limited by available memory
Unique items per field	1,048,576
Row (row field: A field that's assigned a row orientation in a PivotTable report. Items associated with a row field are displayed as row labels.) or column fields (column field: A field that's assigned a column orientation in a PivotTable report. Items associated with a column field are displayed as column labels.) in a PivotTable report	Limited by available memory
Report filters in a PivotTable report	256 (may be limited by available memory)
Value fields in a PivotTable report	256
Calculated item (calculated item: An item within a PivotTable field or PivotChart field that uses a formula you create. Calculated items can perform calculations by using the contents of other items within the same field of the PivotTable report or PivotChart report.) formulas in a PivotTable report	Limited by available memory
Report filters in a PivotChart report (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.)	256 (may be limited by available memory)
Value fields in a PivotChart report	256
Calculated item formulas in a PivotChart report	Limited by available memory
Length of the MDX name for a PivotTable item	32,767
Length for a relational PivotTable string	32,767

 

Shared workbook specifications and limits

Feature	Maximum limit
Users who can open and share a shared workbook (shared workbook: A workbook set up to allow multiple users on a network to view and make changes at the same time. Each user who saves the workbook sees the changes made by other users.) at the same time	256
Personal views (view: A set of display and print settings that you can name and apply to a workbook. You can create more than one view of the same workbook without saving separate copies of the workbook.) in a shared workbook	Limited by available memory
Days that change history (change history: In a shared workbook, information that is maintained about changes made in past editing sessions. The information includes the name of the person who made each change, when the change was made, and what data was changed.) is maintained	32,767 (default is 30 days)
Workbooks that can be merged at one time	Limited by available memory
Cells that can be highlighted in a shared workbook	32,767
Colors used to identify changes made by different users when change highlighting is turned on	32 (each user is identified by a separate color; changes made by the current user are highlighted with navy blue)

اکسل 2007 EXCEL 2007

What's new in Microsoft Office Excel 2007

یکی از شاهکار های جدید مایکروسافت را به جرات می توان اکسل ۲۰۰۷ از سری آفیس ۱۲ دانست. در این سری جدید تعداد سطرها از 65536 به 1048576 و تعداد ستون ها از 256 به 16384 تغییر یافته است.

این افزایش قابلیت های فراوانی را در اکسل ۲۰۰۷ برای کاربران مهیا می کند .محیط اکسل ۲۰۰۷ را در زیر مشاهده می کنید. 

 سه آیتم به محیط 2007 اضافه شده است که به ترتیب :

1- OFFICE BUTTON  که بخش فایل را پوشش می دهد :

 

کلید EXCEL OPTIONS  هم در این بخش قرار دارد:

 

 ۲- QAT که تصویر فلاپی در آن مشاهده می شود و عملیات ذخیره سازی و یا آندو و ردو را انجام می دهد. کاربران قادرند موارد مورد نیاز را با آن اضافه کنند یا از آن حذف نمایند .

 

 ۳- نوار ابزار ریبون RIBBON :

 این نوار ابزار ساختار، نتیجه محوری (RESULTS ORIENTED) داشته که بجای موضوع محوری در 2003 ،سرعت دسترسی به ابزارها را بیشتر می کند .

 

 

 

جزوه اکسل پیشرفته

دوستان برای گزارش گیری و تحلیل داده های لیست ها، در این جزوه چند روش ارائه شده است:

۱- استفاده از تابع SUBTOTAL  فیلتر ها

2- تابع SUMIF , COUNTIF

3- توابع پایگاه داده  DATABASE FUNCTIONS

DSUM DMAX DMIN ,...

4- استفاده از توابع آرایه ای  مثل FREQUENCY , TRANSPOSE,   SUM & IF

5- استفاده از جداول محوری PIVOT TABLE

استفاده از ابزار DATA VALIDATION , TEXT TO COLUMN,...

توابع روند سازی ROUND , ROUNDDOWN,ROUNDUP,ODD,EVEN,FLOOR,CEILING,TRUNC.,INT

 دانلود جزوه اکسل پیشرفته

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

علی توضیح اسفند ۸۶

 

ابزاری برای انتخاب ک محدوده از سلول ها VBA

برای انتخاب یک محدوده از سلول ها می توان از ابزاری بنام REFEDIT  استفاده کرد.

مثال : در یک فرم سه شیء TEXTBOX , REFEDIT, COMMANDBUTTON قرار دارند فرض کنید قصد داریم آدرس انتخاب شده توسط REFEDIT   را با فشار دادن کلید در TEXTBOX نمایش دهیم در بخش کد مربوط به کلید متن زیر را وارد می کنیم :

()Private Sub CommandButton1_Click

TextBox1 = RefEdit1.Value
End Sub

راهنمای اکسل

RefEdit Control

On a user form, this control displays the address of a range of cells that you've entered or selected on one or more worksheets. To select a range, click the button in the control to collapse the user form, select the range, and then click the button in the control again to expand the user form.

If the RefEdit control is embedded on a worksheet, you can link the contents of the control to a cell on any worksheet in that workbook.

Remarks

The default property for a RefEdit control is Value.

The default event for a RefEdit control is BeforeDragOver.

You cannot use a RefEdit control on a modeless user form. You can use the ShowModal property to set a user form to modal.

Example

This example sets the control tip text for the RefEdit1 control in UserForm1, and then it displays the user form.

UserForm1.RefEdit1.ControlTipText = _
    "The selected range of data in the worksheet"
UserForm1.Show
		

Later, the user clicks CommandButton1 in UserForm1. This example of an event-handling procedure displays the address of the selected worksheet range and then ends the program.

Private Sub CommandButton1_Click

    MsgBox RefEdit1.Value
    End
End Sub

 

تمرین جداول محوری - گزارش گیری و ساخت گزارش

با توجه به فایل زیر به سوالات زیر پاسخ دهید :

تمرین

کاربرگ دریافتی :

۱- جمع دریافتی های هر فرد چقدر است ؟

۲- جمع دریافتی های هر فرد در هر ماه چقدر است ؟

۳- میانگین دریافتی های هر فرد در هر ماه در هر بانک چقدر است ؟ 

کاربرگ انبار :

۱- از هر کالا به هر اندازه و جنس چقدر وارد انبار شده و چقدر خارج شده است؟

۲- هر وارد کننده از هر کالا چه میزانی وارد کرده است؟

 

کاربرگ لوازم التحریر

۱- هر فروشگاه از هر کالا چقدر فروخته است ؟

۲- هر فروشگاه در هر شهر از هر کالا چقدر فروخته است ؟