¿Cómo normalizar datos en Excel?

El término normalización en sí mismo es una palabra de moda que es popular entre las personas que provienen de diferentes campos, como el aprendizaje automático, la ciencia de datos, las estadísticas, etc. La normalización es un término general que significa reducir los valores dentro de un cierto rango. El origen de que la palabra normalización sea una palabra de moda proviene del hecho de que a menudo la gente la malinterpreta y se usa indistintamente con otro término estadístico, estandarización. En este artículo, vamos a desmitificar ambos términos y luego leeremos cómo podemos implementar estas técnicas en un conjunto de datos de muestra en Excel.

Datos de normalización (o escalado mínimo-máximo) en excel

Es el proceso de escalar datos de tal manera que todos los puntos de datos se encuentran en un rango de 0 a 1. Por lo tanto, esta técnica hace posible llevar todos los puntos de datos a una escala común. La fórmula matemática para la normalización se da como:

 , where X is the data point, X_max and X_min are the maximum and minimum value in the group of records respectively. The process of normalization is generally used when the distribution of data does not follow the Gaussian distribution.

Echemos un vistazo a un ejemplo para ver cómo podemos realizar la normalización en un conjunto de datos de muestra. Supongamos que tenemos un registro de la altura de 10 estudiantes dentro de una clase como se muestra a continuación:

Altura (en cm)
152
155
168
175
153
162
173
166
158
156

Paso 1: Calcular el valor mínimo en la distribución. Se puede calcular usando la función MIN(). El valor mínimo resulta ser 152 que se almacena en la celda B14.

Paso 2: Calcular el valor máximo en la distribución. Se puede calcular usando la función MAX(). El valor máximo resulta ser 175 que se almacena en la celda B15.

Paso 3: Encuentra la diferencia entre los valores máximo y mínimo. Su diferencia resulta ser 175 – 152 = 23 que se almacena en la celda B16.

Paso 4: Para los primeros datos almacenados en la celda A2, calcularemos el valor normalizado como se muestra en el siguiente video.

Paso 5: podemos calcular manualmente todos los valores uno por uno para cada registro de datos o podemos obtener valores directamente para todas las demás celdas usando la función de autocompletar de Excel. Para esto, vaya a la esquina derecha de la celda B2 hasta que aparezca un símbolo (+), y luego arrastre el cursor hacia abajo para completar automáticamente los valores dentro de todas las celdas.

Nota: Al calcular el primer valor normalizado en la celda B2, debe asegurarse de que la dirección de referencia para las celdas B14 y B16 se bloquee con el botón Fn + F4; de lo contrario, se generará un error.

Si observamos de cerca los resultados, podemos notar que todos los valores se encuentran en el rango de 0 a 1.

Estandarización (o normalización de puntuación Z) 

La estandarización es un proceso en el que queremos escalar nuestros datos de tal manera que la distribución de nuestros datos tenga una media de 0 y una desviación estándar de 1. La fórmula matemática para la estandarización es la siguiente:

, where where X is the data point, X_mean is the mean of the distribution and σ_x is the standard deviation of the distribution.

El proceso de estandarización generalmente se usa cuando sabemos que la distribución de datos sigue la distribución gaussiana.

Método 1: calcular manualmente la normalización del puntaje z 

Paso 1: Calcular la media/promedio de la distribución. Se puede hacer usando la función PROMEDIO(). El valor medio resulta ser 161,8 y se almacena en la celda B14.

Paso 2: Calcular la desviación estándar de la distribución que se puede hacer usando la función STDEV(). La desviación estándar resulta ser 8,323994767, que se almacena en la celda B15.

Paso 3:  Para los primeros datos almacenados en la celda A2, calcularemos el valor estandarizado como se muestra en la imagen que se muestra a continuación.

Paso 4: después de calcular manualmente el primer valor, simplemente podemos usar la función de autocompletar de Excel para completar los valores estandarizados para todos los demás registros.

Nota: Al calcular el primer valor estandarizado en la celda B2, debe asegurarse de que la dirección de referencia para las celdas B14 y B15 se bloquee con el botón Fn+F4; de lo contrario, se generará un error.

Método 2: Cálculo de la normalización de la puntuación Z mediante la función STANDARDIZE()

Incluso podemos usar la función STANDARDIZE() incorporada para encontrar el valor estandarizado de un elemento. La sintaxis para la función STANDARDIZE() es la siguiente:

=ESTANDARIZAR(x,media,desviación_estándar)

Donde x es el elemento específico/rango de celdas, mean es el promedio/media aritmética de todos los elementos del registro y std_dev es la desviación estándar de todos los elementos del registro

Paso 1: Calcular la media/promedio de la distribución. Se puede hacer usando la función PROMEDIO(). El valor medio resulta ser 161,8 y se almacena en la celda B14.

Paso 2: Calcular la desviación estándar de la distribución que se puede hacer usando la función STDEV(). La desviación estándar resulta ser 8,323994767, que se almacena en la celda B15.

Paso 3: Para los primeros datos almacenados en la celda A2, calcularemos el valor estandarizado como se muestra en la imagen a continuación.

Paso 4: después de calcular manualmente el primer valor, simplemente podemos usar la función de autocompletar de Excel para completar los valores estandarizados para todos los demás registros.