Blog Archives

MENGAKTIFKAN DATA ANALYSIS TOOLS PADA EXCEL

 

Analisis statistik sebenarnya dapat dilakukan melalui Excel dengan bantuan tool “data analysis”.

Analysis ToolPak adalah Microsoft Office Excel dilakukan instalasi Microsoft Office or Excel. Untuk menggunakannya perlu dilakukan langkah2 berikut ini :

  1. Click the Microsoft Office Button , lalu click Excel Options.
  2.  Click Add-Ins, lalu pada  Manage box, pilih Excel Add-ins.
  3. Click Go.
  4. di dalam  Add-Ins available box, pilih Analysis ToolPak check box, lalu click OK.
  5. Setelah proses loading selesai maka “Data Analysis command” akan tersedia  Data tab

 

Add-Ins

Data Analysis Tools

Jenis-jenis analisis yang dapat digunakan antara lain :

The CORREL and PEARSON worksheet functions both calculate the correlation coefficient between two measurement variables when measurements on each variable are observed for each of N subjects. (Any missing observation for any subject causes that subject to be ignored in the analysis.) The Correlation analysis tool is particularly useful when there are more than two measurement variables for each of N subjects. It provides an output table, a correlation matrix, that shows the value of CORREL (or PEARSON) applied to each possible pair of measurement variables.

The correlation coefficient, like the covariance, is a measure of the extent to which two measurement variables “vary together.” Unlike the covariance, the correlation coefficient is scaled so that its value is independent of the units in which the two measurement variables are expressed. (For example, if the two measurement variables are weight and height, the value of the correlation coefficient is unchanged if weight is converted from pounds to kilograms.) The value of any correlation coefficient must be between -1 and +1 inclusive.

The Correlation and Covariance tools can both be used in the same setting, when you have N different measurement variables observed on a set of individuals. The Correlation and Covariance tools each give an output table, a matrix, that shows the correlation coefficient or covariance, respectively, between each pair of measurement variables. The difference is that correlation coefficients are scaled to lie between -1 and +1 inclusive. Corresponding covariances are not scaled. Both the correlation coefficient and the covariance are measures of the extent to which two variables “vary together.”

The Covariance tool computes the value of the worksheet function COVAR for each pair of measurement variables. (Direct use of COVAR rather than the Covariance tool is a reasonable alternative when there are only two measurement variables, that is, N=2.) The entry on the diagonal of the Covariance tool’s output table in row i, column i is the covariance of the i-th measurement variable with itself. This is just the population variance for that variable, as calculated by the worksheet function VARP.

The Descriptive Statistics analysis tool generates a report of univariate statistics for data in the input range, providing information about the central tendency and variability of your data.

The Exponential Smoothing analysis tool predicts a value that is based on the forecast for the prior period, adjusted for the error in that prior forecast. The tool uses the smoothing constant a, the magnitude of which determines how strongly the forecasts respond to errors in the prior forecast.

The Fourier Analysis tool solves problems in linear systems and analyzes periodic data by using the Fast Fourier Transform (FFT) method to transform data. This tool also supports inverse transformations, in which the inverse of transformed data returns the original data.or example, in a class of 20 students, you can determine the distribution of scores in letter-grade categories.

dst…silahkan baca sendiri di menu “help Excel”