How do I use XLSTAT functions within an Excel sheet?
Conjunto de datos XLS58.5 KB
-
Pro Software estadístico básico
-
Configuración del sistema
- Windows:
- Versiones: 9x/Me/NT/2000/XP/Vista/Win 7
- Excel: 97 o superior
- Procesador: 32 o 64 bits
- Disco duro: 150 Mb
- Mac OS X:
- OS: OS X
- Excel: X, 2004 y 2011
- Disco duro: 150 Mb
- Windows:
Ventajas
-
Sencillo y dirigido a los usuarios
Sencillo y dirigido a los usuarios XLSTAT es un software que se integra de forma transparente con Microsoft Excel, que es la hoja de cálculo más difundida del mundo. Esta integración hace que sea una de las herramientas más sencillas para trabajar ya que utiliza la misma filosofía que Microsoft Excel. El programa está disponible en una ficha de XLSTAT. Los análisis se agrupan en menús funcionales. Los cuadros de diálogo están dirigidos a los usuarios, por lo que la preparación de los análisis es tarea sencilla.
-
Resultados y datos compartidos sin dificultad
Resultados y datos compartidos sin dificultad Una de las mayores ventajas de XLSTAT es la forma transparente con la que se pueden compartir los datos y los resultados. Los resultados se almacenan en Microsoft Excel de modo que cualquier usuario puede acceder a ellos. No es necesario que el destinatario tenga una licencia de XLSTAT o cualquier visor adicional, lo que facilita y rentabiliza el trabajo en equipo. Del mismo modo, es fácil integrar los resultados en otras aplicaciones de Microsoft Office, como PowerPoint, por lo que se pueden crear estupendas presentaciones en cuestión de minutos.
-
Modular
Modular XLSTAT es un producto modular. XLSTAT-Pro es un módulo principal de estadística de XLSTAT, que incluye todas las funcionalidades dominantes en el análisis estadístico y multivariado. Es posible añadir funciones más avanzadas por medio de módulos complementarios para aplicaciones específicas. De este modo es posible adaptar el software a sus necesidades, aumentando la rentabilidad.
-
Didáctico
Didáctico Los resultados de XLSTAT están organizados por análisis y es fácil desplazarse por ellos. La información útil se proporciona junto con los resultados para ayudarle en su interpretación.
-
Asequible
Asequible XLSTAT es una solución completa y modular que se puede ajustar a cualquier necesidad de análisis comercial. Tiene un precio muy razonable, por lo que el retorno de su inversión es casi inmediato. Todas las licencias de XLSTAT incluyen también un servicio de asistencia de la mayor calidad.
-
Accesible: disponible en muchos idiomas
Accesible: disponible en muchos idiomas Nos hemos asegurado de que XLSTAT sea accesible para todos traduciendo el programa a muchos idiomas, incluyendo chino, inglés, alemán, italiano, japonés, polaco, portugués y español.
-
Automatizable y personalizable
Automatizable y personalizable La mayoría de las funciones estadísticas disponibles en XLSTAT pueden llamarse directamente desde la ventana Visual Basic de Microsoft Excel. Pueden modificarse e integrarse en código fuente adicional para ajustarse a sus necesidades. Añadir tablas y trazados, así como modificar los resultados existentes se convierte en tarea sencilla. Además, XLSTAT incluye algunas herramientas especiales en los cuadros de diálogo para generar automáticamente el código fuente VBA para reproducir su análisis empleando el editor de VBA o simplemente cargar ajustes predeterminados. Esta automatización de análisis rutinarios sin esfuerzo le ahorrará gran cantidad de tiempo.
XLSTAT-Pro allows you to directly use some XLSTAT functions within your Excel sheet. The functions that are currently available solve numerical issues that have been found with the built-in Excel functions, or complement the set of Excel statistical functions:
| Function | Description | Parameters |
|---|---|---|
| XLSTAT_Stdev(Vector, Weights) | Computes the standard deviation of a sample (unbiased) |
Vector: a column or row that contains the data. Missing values are accepted. Weights: optional; a column or row that contains the weights of the observations. Missing values are considered as null weights. |
| XLSTAT_StdevP(Vector, Weights) | Computes the standard deviation of a population (biased) |
Vector: a column or row that contains the data. Missing values are accepted. Weights: optional; a column or row that contains the weights of the observations. Missing values are considered as null weights. |
| XLSTAT_Var(Vector, Weights) | Computes the variance of a sample (unbiased) |
Vector: a column or row that contains the data. Missing values are accepted. Weights: optional; a column or row that contains the weights of the observations. Missing values are considered as null weights. |
| XLSTAT_StdevP(Vector, Weights) |
Computes the variance of a population (biased) |
Vector: a column or row that contains the data. Missing values are accepted. Weights: optional; a column or row that contains the weights of the observations. Missing values are considered as null weights. |
| XLSTAT_Correl(Vector1, Vector2, Weights) |
Computes the Pearson correlation coefficient (classical correlation) between two samples. |
Vector1: a column or row that contains the data of the first sample. Missing values are accepted. Vector2: a column or row that contains the data of the second sample. Missing values are accepted. Weights: optional; a column or row that contains the weights of the observations. Missing values are considered as null weights. |
| XLSTAT_Pearson(Vector1, Vector2, Weights) | see XLSTAT_Correl | see XLSTAT_Correl |
| XLSTAT_Spearman(Vector1, Vector2, Weights) | Computes the Spearman correlation coefficient between two samples. |
Vector1: a column or row that contains the data of the first sample. Missing values are accepted. Vector2: a column or row that contains the data of the second sample. Missing values are accepted. Weights: optional; a column or row that contains the weights of the observations. Missing values are considered as null weights. |
| XLSTAT_Kendall(Vector1, Vector2, Weights) | Computes the Kendall correlation coefficient between two samples. |
Vector1: a column or row that contains the data of the first sample. Missing values are accepted. Vector2: a column or row that contains the data of the second sample. Missing values are accepted. Weights: optional; a column or row that contains the weights of the observations. Missing values are considered as null weights. |
In order to illustrate the use of an XLSTAT function within an Excel sheet, we suggest an example that shows a numerical error of Excel that has been solved in version 2003. This error concerns the computation of the standard deviation and the variance. The XLSTAT function solves this issue, whatever your Excel version. The Excel file where the data and the functions are available can be downloaded by clicking here. We first start by a reminder on how to use formulas and functions in Excel.
1. Reminder
Using formulas in Excel cells allows to link cells and to perform simple or complex operations (mathematical, logical, textual, ...). To make the D2 cell be equal to the value in C2 plus 1000000000, you only need to type in D2, =C2+1000000000. The use of more complex function is possible through functions. For example, if you want to compute in cell F2 the square root of cell D2, and if you don't remember how the function writes, you can go to the "Insert / Function" menu of Excel after you have selected the F2 cell. The following dialog box is being displayed:
You then need to select "Math & Trig" in the list on the left side (see above), and then "SQRT" function in the list on the right side. Once the function is selected and once you clicked on OK, Excel displays a new dialog box, in which you need to enter the parameters of the function, here D2 (use the mouse to select cell D2). The result is pre-computed and displayed in the dialog box. It is displayed in cell F2 once you click on "OK".
If you want to know which formula has been used in an Excel cell, or if you want to modify the formula, you only need to click in the formula bar of Excel (or press F2) as the formula is displayed in it:
2. Using the XLSTAT_Var function
The following example involves two series of data: the first is the series of integers from 1 to 9, and the second corresponds to the first augmented by 1e9 (one billion).
The mean is 5 for the first series (5 + 1e9 = 1000000005) for the second series. But the variance should be the same for the two series as it measures the average deviation from the mean and as it is not sensitive to a translation of all the data.
We first decide to compute the unbiased variance (the sum of the squared deviations from the mean divided by n-1 where n is the number of observations), using the VAR function of Excel. The result displayed in cell C13 for the first series is correct (7.5), but it is wrong in cell D13 for the second series (except with Excel 2003).
We then compute the variance for the two series using the XLSTAT function.
To use an XLSTAT function, you only need to type = followed by its name or you can use the "Insert / Function" menu of Excel, and then choose "XLSTAT" in the list on the left. Then select the XLSTAT function in the list on the right.
We are using here the XLSTAT_Var function. The parameters which are displayed in bold are compulsory, while the other ones are optional. We select with the mouse the cells C2 to C10 for the first series, and the cells D2 to D10 for the second series.
We obtain the same result for both series.
As a conclusion, it is possible to use within Excel sheets functions that are based on the XLSTAT algorithms. We remind you that XLSTAT uses Excel only for inputting data and displaying results. All computations are done in independent programs and are based on proven algorithms.