اكسل والتحليل الاحصائي

اكسل والتحليل الاحصائيالإثنين فبراير 16, 2009 5:33 pm

يعتبر برنامج الاكسل من اقوى برامج
الجداول الالكترونية ، فعلاوة على امكانية ادارة وتنسيق الجدوال فيه وعمل
الرسومات البيانية وبانواع مختلفة وكثيرة واجراء الكثير من الدوال
الرياضية والاحصائية والمالية ، يمكن استخدام الاكسل للتحليل الاحصائي من
خلال استخدام اداة Analysis ToolPak وهي اداة يمكن اضافتها عند الحاجة الى
اجراء التحليلات الاحصائية ومنها الاتي:

1-ادوات تحليل التباين ANOVA

2-تحليل الارتباط Correlation

3-اداة التحليل Covarianes

4-الاحصاء الوصفي Descriptive Statistics

5-التمهيد الاسي Exponetial Smoothing

6-تحليل التباين باستخدام F-Test

7-ادوات T-Test

8-اداة التحليل Z-Test

9-اداة التحليل Histogram

10-اداة التحليل Moving Average

11-اداة التحليل Random Number Generation .

12-اداة التحليل Rank and Precentile

13-تحليل الانحدار Regression

14-اداة التحليل Sampling

وللمزيد يمكن الاستفادة من المنهج الاتي للاخ خالد محمد والخاص بالتحليل الاحصائي باستخدام الاكسل

[ندعوك للتسجيل في المنتدى أو التعريف بنفسك لمعاينة هذا الرابط]

رد: اكسل والتحليل الاحصائيالثلاثاء فبراير 17, 2009 3:34 pm

شكرا ليك على الموضوع بس كنت عاوز اعرف التحليل الحصائى باسستخدام التباين f-test
لانة مش موجود

رد: اكسل والتحليل الاحصائيالثلاثاء فبراير 17, 2009 7:50 pm

What is an F-Test ?

	This compares the spread of two data sets by comparing their variances.
	Remember that the Variance is a measure of how spread out, or scattered the values are.
	Also sometimes known as the Fisher test.
	The F-Test Two-Sample for Variances analysis tool performs a two-sample F-test to compare two population variances.
	The F-test is also known as the Variance ratio.
	This test involves calculating the variances for the two data sets, placing the larger value over the smaller value and then looking up the ratio in a table.

Simple Example

	The F-Test is used to compare the variances of two samples.
	Assumption that the largest mean is placed in the first column or row.

[ندعوك للتسجيل في المنتدى أو التعريف بنفسك لمعاينة هذه الصورة]

	Variable 1 Range - Enter the reference for the first column or row of data you want to analyze.
	Variable 2 Range - Enter the reference for the second column or row of data you want to analyse.
	Labels - Select if the first row or column of your input range contains labels. Clear this check box if your input range has no labels; Microsoft Excel generates appropriate data labels for the output table.
	Alpha - Enter the confidence level for the test. This value must be in the range 0...1. The alpha level is a significance level related to the probability of having a type I error (rejecting a true hypothesis).
	Output Range - Enter the reference for the upper-left cell of the output table. Excel automatically determines the size of the output area and displays a message if the output table will replace existing data.
	New Worksheet Ply - Click to insert a new worksheet in the current workbook and paste the results starting at cell A1 of the new worksheet. To name the new worksheet, type a name in the box.
	New Workbook - Click to create a new workbook and paste the results on a new worksheet in the new workbook.

[ندعوك للتسجيل في المنتدى أو التعريف بنفسك لمعاينة هذه الصورة]

	Mean - The mean of each of the samples.
	Variance - The variance of each of the samples
	Observations - The number of values in each of the samples.
	df - The Degress of Freedom for each of the samples.
	F - The F Statistic. An F statistic close to 1 provides evidence that the sample variances are equal. The higher the F statistic the less likely that the null hypothesis is true.
	P(F<=f) one tail - The probability that the
	F Critical one-tail - The critical value taken from the 0.05 F distribution.

رد: اكسل والتحليل الاحصائيالثلاثاء فبراير 17, 2009 7:55 pm

طبعا انا حبيت اقدم حاجه علميه وصحيحه فحبيت اقدمه بالصوره دي افضل
لان طبعا ومن المعروف احسن حاجه دراسه الرياضه والاحصاء والحاسب باللغه الانجليزيه
لان طبعا كل شغلنا علي الكمبيوتر
واللغه الانجليزيه هي المحببه في قسمنا طبعا

واتمني اكون فدتكم بالقدر المطلوب

ولقيت كمان شرح اخر وجميل
وهنزله ايضا لكي نستفيد جميعا

رد: اكسل والتحليل الاحصائيالثلاثاء فبراير 17, 2009 8:12 pm

Independent Groups t-test

When the means of two groups are to be compared[b] (where each group consists of subjects that are not related) then the Excel two-sample t-test
procedure is used to perform these calculations.

(NOTE: If your observations are related across “group” as paired or
repeated measurements, this in an INCORRECT version of the t-test. For
that case, see the tutorial on the Paired t-test.)

Assumptions: Subjects are randomly assigned to one of two groups. The
distribution of the means by group is normal with equal variances. Sample
sizes between groups do not have to be equal.

Test: The hypotheses for the comparison of means from two
independent groups are:

[/b]

_{Ho: m1 = m2
(means of the two groups are equal)}

_{Ha:
m1 ¹
m2
(means are not equal)}

The test statistic is a student’s t-test with N‑2 degrees of freedom,
where N is the total number of subjects. A low p‑value indicates evidence
to reject the null hypothesis in favor of the alternative. In other words,
there is evidence that the means are not equal.

For example,
suppose we are interested in comparing SCORES across GROUPS, where there
are two groups. The purpose is to determine if the mean SCORE on a test is
different for the two groups tested (i.e., control and treatment
groups). The example data is shown here:

Group	Scores
1	20
1	23
1	32
1	24
1	25
1	28
1	27.5
2	25
2	46
2	56
2	45
2	46
2	51
2	34
2	47.5

In this
example, GROUP contains two values, 1 or 2, indicating which group each
subject was in. The t-test will be performed on the values in the variable
(column) named SCORE.

An
independent group t-test is done in two steps:

[b]Step 1:
Decide if the variances are equal in both groups, which determines the
type of t-test to perform (one that assumes equal variances or one that
doesn’t make that assumption.) A conservative approach suggested in some
texts is to always assume unequal variances. Another approach is to do a
statistical test to determine equality.

Step 2:
Depending on you decision about the equality of variances you either
perform the version of the t-test that assumes equality of variances or
other one that doesn’t make that assumption.

رد: اكسل والتحليل الاحصائيالثلاثاء فبراير 17, 2009 8:14 pm

Determine Equality of Variance

If you take the conservative approach, skip this test and proceed to the
version of the t-test that does not assume equality of variance.

To do a statistical test to determine equality of variance, follow these
instructions. (The test for equality of variances is an F-test.)

[/b]

1.
In Excel, select Tools/ Data Analysis / F-Test Two Sample for
Variance.

2.
In the F-Test Two Sample for Variance dialog box: For the Input
Range for Variable 1, highlight the seven values of Score in group 1
(values from 20 to 27.5). For the input range for Variable 2, highlight
the eight values of Score in group 2 (values from 25 to 47.5). Leave the
other items at their default selections. This dialog box is shown below.
Click OK.

[ندعوك للتسجيل في المنتدى أو التعريف بنفسك لمعاينة هذه الصورة]

3.
The following results are produced by Excel:

F-Test Two-Sample for Variances

	*Variable 1*	*Variable 2*
Mean	25.64285714	43.8125
Variance	15.22619048	96.42410714
Observations	7	8
df	6	7
F	0.157908545
P(F<=f) one-tail	0.019378053
F Critical one-tail	0.23771837

Notice the highlighted probability p=0.01937. This is a one-tail p-value
associated with the test for equality of variance. Generally, if this
value is less than 0.05 you assume that the variances are NOT equal.

a.
If the variances are assumed to NOT be equal, proceed with the
t-test that assumes non-equal variances.

b.
If the variances are assumed to be equal, proceed with the
t-test that assumes equal variances.

رد: اكسل والتحليل الاحصائيالثلاثاء فبراير 17, 2009 8:15 pm

Perform the t-test

The process of doing the t-test in Excel is similar for both the equal and
unequal variances case – the main difference is which version you select
from the menu. Suppose you select the unequal version of the two-sample
t-test – this is how you proceed:

1.
Select Tools/ Data Analysis/ t-Test: Two Sample assuming
Unequal Variances

2.
For the Input Range for Variable 1, highlight the seven values of
Score in group 1 (values from 20 to 27.5). For the input range for
Variable 2, highlight the eight values of Score in group 2 (values from
25 to 47.5). Leave the other items at their default selections. This
dialog box is shown below. Click OK.

[ندعوك للتسجيل في المنتدى أو التعريف بنفسك لمعاينة هذه الصورة]

3.
The following output is created:

t-Test: Two-Sample Assuming Unequal Variances

	Variable 1	Variable 2
Mean	25.64285714	43.8125
Variance	15.22619048	96.42410714
Observations	7	8
Hypothesized Mean Difference	0
Df	9
t Stat	-4.816944724
P(T<=t) one-tail	0.000475506
t Critical one-tail	1.833112923
P(T<=t) two-tail	0.000951012
t Critical two-tail	2.262157158

Notice that
the two sample mean values (variance) are 25.64(15.23) and 43.81(96.42).
The two tailed calculated t-statistic is 4.82 and the
highlighted p-value for this test
is p=0.001. (0.000951012) Since the p-value is less than 0.05, this
provides evidence to reject the null hypothesis of equal means.

As an example
of how this might be reported in a journal article:

Methods: A
preliminary test for the equality of variances indicates that the
variances of the two groups were significantly difference F=.157, p=.02.
Therefore, a two-sample t-test was performed that does not assume equal
variances.

Results: The mean score for group 1 (M=25.64 SD= 3.9021, N= 7) was
significantly smaller than the scores for group 2 (M=42.81, SD=9.82, N=
8.) using the two-sample t-test for unequal variances, t(9) = -4.82, p
<= 0.001. (Technically, the degrees of freedom for this unequal
variances t-test should be 9.4 instead of 9, but Excel unfortunately
rounds off the DF, so it is reported incorrectly. Years ago, it used to be
conventional to round down if you were consulting a table for a
probability level, but most statistical programs now calculate the correct
p-value using a fractional DF though interpolation.)

Notice that the standard deviation is reported rather than the variances
as shown in the Excel results table. You can calculate the standard
deviation using Tools/ Data Analysis / Descriptive statistics.

When the variances are assumed equal, the analysis is similar, select
Tools/ Data Analysis/ t-Test: Two Sample assuming Equal Variances

رد: اكسل والتحليل الاحصائيالأحد فبراير 12, 2012 4:12 pm

شكرا شكرا جزيلا

رد: اكسل والتحليل الاحصائيالثلاثاء فبراير 24, 2015 7:09 am

يسلمــــــــــــــو

اهلا بك يا زائر لديك 16777214 مساهمة

اكسل والتحليل الاحصائي

descriptionاكسل والتحليل الاحصائيالإثنين فبراير 16, 2009 5:33 pm

descriptionرد: اكسل والتحليل الاحصائيالثلاثاء فبراير 17, 2009 3:34 pm

descriptionرد: اكسل والتحليل الاحصائيالثلاثاء فبراير 17, 2009 7:50 pm

descriptionرد: اكسل والتحليل الاحصائيالثلاثاء فبراير 17, 2009 7:55 pm

descriptionرد: اكسل والتحليل الاحصائيالثلاثاء فبراير 17, 2009 8:12 pm

descriptionرد: اكسل والتحليل الاحصائيالثلاثاء فبراير 17, 2009 8:14 pm

descriptionرد: اكسل والتحليل الاحصائيالثلاثاء فبراير 17, 2009 8:15 pm

descriptionرد: اكسل والتحليل الاحصائيالأحد فبراير 12, 2012 4:12 pm

descriptionرد: اكسل والتحليل الاحصائيالثلاثاء فبراير 24, 2015 7:09 am