Как стандартизировать данные в excel

Содержание

• Функция СТАНДАРТИЗАЦИЯ
• Какова стандартизированная ценность распределения?
• Назначение функции STANDARDIZE
• Уравнение Z-Score
• Синтаксис функции STANDARDIZE
• Вставка функции STANDARDIZE
• Шаги по добавлению функции стандартизации
• Функциональные аргументы
• Примеры синтаксиса функции STANDARDIZE
• Рекомендации
• Статьи по Теме
• Библия Excel 2019

Джошуа — аспирант USF. Он интересуется бизнес-технологиями, аналитикой, финансами и бережливым производством шести сигм.

Функция СТАНДАРТИЗАЦИЯ

Здесь мы рассмотрим все, что вам нужно знать, чтобы лучше понять функцию СТАНДАРТИЗАЦИЯ, начиная с понимания, что такое стандартизованное значение и как мы можем его использовать.

Какова стандартизированная ценность распределения?

Когда кто-то стандартизирует (нормализует) значение из распределения, цель состоит в том, чтобы скорректировать значение распределения вероятностей для согласования. Когда результаты тестов стандартизированы, цель состоит в том, чтобы выровнять распределение оценок учащихся в классе с нормальным распределением.

Стандартизированные оценки полезны, потому что они позволяют определить вероятность получения оценки и возможность сравнить две оценки из разных нормальных распределений.

Назначение функции STANDARDIZE

Функция стандартизации используется для возврата нормализованного значения из распределения, как указано ранее. Возвращаемое нормализованное значение также называется Z-оценкой. Z-оценка — это, по сути, количество стандартных отклонений от среднего значения для точки данных.

Формула на иллюстрации ниже показывает, как функция СТАНДАРТИЗАЦИЯ вычисляет z-оценку. Среднее значение вычитается из числа, подлежащего стандартизации. Затем это значение делится на стандартное отклонение набора данных.

Уравнение Z-Score

Синтаксис функции STANDARDIZE

Функцию СТАНДАРТИЗАЦИЯ необходимо вводить как формулу в Excel. Чтобы вручную добавить эту формулу в ячейку, необходимо выполнить следующие действия:

1. Необходимо щелкнуть ячейку

2. «= СТАНДАРТИЗИРОВАТЬ (» необходимо ввести в ячейку.

3. Необходимо добавить значение x, за которым следует запятая.

4. Среднее значение добавляется с запятой.

5. Стандартное стандартное отклонение добавляется с запятой.

6. Наконец, в конце используется закрывающая скобка, и можно вводить функцию.

Синтаксис показан ниже, а затем подробно описаны все аргументы, составляющие функцию СТАНДАРТИЗАЦИЯ.

= СТАНДАРТИЗАЦИЯ (x; среднее; стандартное_откл.)

X — это требуется, а также значение, которое необходимо нормализовать.

Среднее — требуется среднее значение, которое лучше описывать как среднее арифметическое для набора данных.

Standard_dev — обязательно. Стандартное отклонение набора данных.

Вставка функции STANDARDIZE

Функцию СТАНДАРТИЗАЦИЯ также можно вставить в электронную таблицу Excel. Этот метод предназначен для тех, кто не знаком с синтаксисом функции. Первые шаги к вставке начинаются ниже.

Чтобы использовать этот метод, выбирается ячейка, в которой должен отображаться стандартизованный результат.Затем выбирается вкладка с формулами и необходимо выбрать кнопку «Дополнительные функции» на ленте Excel. Затем выбирается статистическая выборка из списка, за которой следует выбор СТАНДАРТИЗАЦИЯ в раскрывающемся меню.

Шаги по добавлению функции стандартизации

После появления окна аргументов функций в эти поля можно добавить значения или ссылки для каждого аргумента.

Стрелки сбоку от поля можно использовать для выбора ссылки на ячейку. После выбора ссылки или ввода значений можно нажать кнопку ОК.

Функциональные аргументы

Примеры синтаксиса функции STANDARDIZE

Ниже приведены несколько примеров с пояснениями типов синтаксиса, которые можно использовать в двух примерах.

= СТАНДАРТИЗИРОВАТЬ (A2; A3; A4)

Используется со ссылками на ячейки — Здесь можно найти стандартизированную форму со ссылками на ячейки. Каждая ссылка представляет данные, рассчитанные из того же распределения, где A2 представляет x или стандартизируемое значение, A3 представляет среднее значение, а A4 представляет собой стандартное отклонение.

= СТАНДАРТИЗИРОВАТЬ (15,14,2,1,7)

Используется с числами — Здесь можно найти стандартизированную форму непосредственно с числами, добавленными к формуле стандартизации. Первое число 15 — это x, 14,2 — это среднее значение, а 1,7 — это стандартное отклонение.

Рекомендации

Microsoft. (нет данных). СТАНДАРТИЗИРОВАТЬ функцию. Получено 5 января 2020 г. с https://support.office.com/en-us/article/standardize-function-81d66554-2d54-40ec-ba83-6437108ee775.

Википедия. (2019, 5 декабря). Нормализация (статистика). Получено 24 января 2020 г. с https://en.wikipedia.org/wiki/Normalization_(statistics).

Статьи по Теме

Как использовать функцию COUNT в Excel

Как использовать функцию СРЕДНЕЕ в Excel

Как использовать функцию ABS в Excel

Как удалить ошибки в Excel с помощью функции ЕСЛИОШИБКА

Как использовать функцию ВПР в Excel

Преобразование единиц измерения: использование функции CONVERT

Чтобы узнать больше об использовании функций в Excel, я рекомендую следующую книгу. Я использую Библию Excel в течение многих лет, чтобы лучше понять все аспекты этого продукта Microsoft.

Библия Excel 2019

The term normalization in itself is a buzzword that is popular amongst people who come from different fields such as Machine Learning, Data Science, Statistics, etc. Normalization is a general term that means to scale down values inside a certain range. The origin of the word normalization being a buzzword comes from the fact that it is often misunderstood by people and is interchangeably used with another statistical term standardization. In this article, we are going to demystify both of these terms and later we will read how we can implement these techniques on a sample dataset in Excel.

Normalization (Or Min-Max scaling) data in excel

It is the process of scaling data in such a way that all data points lie in a range of 0 to 1. Thus, this technique, makes it possible to bring all data points to a common scale. The mathematical formula for normalization is given as:

 , 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.

Let’s have a look at one example to see how can we perform normalization on a sample dataset. Suppose, we have a record of the height of 10 students inside a class as shown below:

Step 1: Calculate the minimum value in the distribution. It can be calculated using the MIN() function. The minimum value comes out to be 152 which is stored in the B14 cell.

Step 2: Calculate the maximum value in the distribution. It can be calculated using the MAX() function. The maximum value comes out to be 175 which is stored in the B15 cell.

Step 3: Find the difference between the maximum and minimum values. Their difference comes out to be 175 – 152 = 23 which is stored in the B16 cell.

Step 4: For the first data stored in the A2 cell, we will calculate the normalized value as shown in the below video.

Step 5: We can manually calculate all values one by one for each data record or we can directly get values for all the other cells using the auto-fill feature of Excel. For this, go to the right corner of the B2 cell until a (+) symbol appears, and then drag the cursor to the bottom to auto-populate values inside all the cells.

Note: While calculating the first normalized value in the B2 cell, it should be made sure that the reference address for the B14 and B16 cells should be locked using Fn + F4 button otherwise an error will be thrown.

If we have a close look at the results, we can notice all the values lies in the range 0 to 1.

Standardization (Or Z-score normalization) 

Standardization is a process in which we want to scale our data in such a way that the distribution of our data has its mean as 0 and standard deviation as 1. The mathematical formula for standardization is given as:

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

The process of standardization is generally used when we know the distribution of data follows the gaussian distribution.

Method 1: Calculating z-score normalization manually 

Step 1: Calculate the mean/average of the distribution. It can be done using the AVERAGE() function. The mean value comes out to be 161.8 and is stored in the B14 cell.

Step 2: Calculate the standard deviation of the distribution which can be done using the STDEV() function. The standard deviation comes out to be 8.323994767 which is stored in the B15 cell.

Step 3:  For the first data stored in the A2 cell, we will calculate the standardized value as shown in the image given below.

Step 4: After manually calculating the first value, we can simply use the auto-fill feature of Excel to populate the standardized values for all other records.

Note: While calculating the first standardized value in the B2 cell, it should be made sure that the reference address for the B14 and B15 cells should be locked using  Fn+F4 button otherwise an error will be thrown.

Method 2: Calculating Z-score normalization using the STANDARDIZE() function

We can even use the built-in STANDARDIZE() function to find the standardized value of an element. The syntax for STANDARDIZE() function is given as:

=STANDARDIZE(x,mean,std_dev)

Where x is the specific element/range of cells, mean is the average/arithmetic mean of all the elements in the record, and std_dev is the standard deviation of all the elements in the record

Step 1: Calculate the mean/average of the distribution. It can be done using the AVERAGE() function. The mean value comes out to be 161.8 and is stored in the B14 cell.

Step 2: Calculate the standard deviation of the distribution which can be done using the STDEV() function. The standard deviation comes out to be 8.323994767 which is stored in the B15 cell.

Step 3: For the first data stored in the A2 cell, we will calculate the standardized value as shown in the below image.

Step 4: After manually calculating the first value, we can simply use the auto-fill feature of Excel to populate the standardized values for all other records.

The term normalization in itself is a buzzword that is popular amongst people who come from different fields such as Machine Learning, Data Science, Statistics, etc. Normalization is a general term that means to scale down values inside a certain range. The origin of the word normalization being a buzzword comes from the fact that it is often misunderstood by people and is interchangeably used with another statistical term standardization. In this article, we are going to demystify both of these terms and later we will read how we can implement these techniques on a sample dataset in Excel.

Normalization (Or Min-Max scaling) data in excel

It is the process of scaling data in such a way that all data points lie in a range of 0 to 1. Thus, this technique, makes it possible to bring all data points to a common scale. The mathematical formula for normalization is given as:

 , 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.

Let’s have a look at one example to see how can we perform normalization on a sample dataset. Suppose, we have a record of the height of 10 students inside a class as shown below:

Step 1: Calculate the minimum value in the distribution. It can be calculated using the MIN() function. The minimum value comes out to be 152 which is stored in the B14 cell.

Step 2: Calculate the maximum value in the distribution. It can be calculated using the MAX() function. The maximum value comes out to be 175 which is stored in the B15 cell.

Step 3: Find the difference between the maximum and minimum values. Their difference comes out to be 175 – 152 = 23 which is stored in the B16 cell.

Step 4: For the first data stored in the A2 cell, we will calculate the normalized value as shown in the below video.

Step 5: We can manually calculate all values one by one for each data record or we can directly get values for all the other cells using the auto-fill feature of Excel. For this, go to the right corner of the B2 cell until a (+) symbol appears, and then drag the cursor to the bottom to auto-populate values inside all the cells.

Note: While calculating the first normalized value in the B2 cell, it should be made sure that the reference address for the B14 and B16 cells should be locked using Fn + F4 button otherwise an error will be thrown.

If we have a close look at the results, we can notice all the values lies in the range 0 to 1.

Standardization (Or Z-score normalization) 

Standardization is a process in which we want to scale our data in such a way that the distribution of our data has its mean as 0 and standard deviation as 1. The mathematical formula for standardization is given as:

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

The process of standardization is generally used when we know the distribution of data follows the gaussian distribution.

Method 1: Calculating z-score normalization manually 

Step 1: Calculate the mean/average of the distribution. It can be done using the AVERAGE() function. The mean value comes out to be 161.8 and is stored in the B14 cell.

Step 2: Calculate the standard deviation of the distribution which can be done using the STDEV() function. The standard deviation comes out to be 8.323994767 which is stored in the B15 cell.

Step 3:  For the first data stored in the A2 cell, we will calculate the standardized value as shown in the image given below.

Step 4: After manually calculating the first value, we can simply use the auto-fill feature of Excel to populate the standardized values for all other records.

Note: While calculating the first standardized value in the B2 cell, it should be made sure that the reference address for the B14 and B15 cells should be locked using  Fn+F4 button otherwise an error will be thrown.

Method 2: Calculating Z-score normalization using the STANDARDIZE() function

We can even use the built-in STANDARDIZE() function to find the standardized value of an element. The syntax for STANDARDIZE() function is given as:

=STANDARDIZE(x,mean,std_dev)

Where x is the specific element/range of cells, mean is the average/arithmetic mean of all the elements in the record, and std_dev is the standard deviation of all the elements in the record

Step 1: Calculate the mean/average of the distribution. It can be done using the AVERAGE() function. The mean value comes out to be 161.8 and is stored in the B14 cell.

Step 2: Calculate the standard deviation of the distribution which can be done using the STDEV() function. The standard deviation comes out to be 8.323994767 which is stored in the B15 cell.

Step 3: For the first data stored in the A2 cell, we will calculate the standardized value as shown in the below image.

Step 4: After manually calculating the first value, we can simply use the auto-fill feature of Excel to populate the standardized values for all other records.

В этом руководстве показано, как использовать Функция СТАНДАРТИЗАЦИЯ в Excel в Excel, чтобы вернуть нормализованное значение (z-оценку) на основе среднего и стандартного отклонения.

Обзор функции STANDARDIZE

Функция СТАНДАРТИЗАЦИЯ в Excel Рассчитывает нормализованное значение (z-показатель) на основе среднего значения и стандартного отклонения.

Чтобы использовать функцию СТАНДАРТИЗАЦИЯ листа Excel, выберите ячейку и введите:

(Обратите внимание, как появляются входные данные формулы)

Функция СТАНДАРТИЗАЦИЯ Синтаксис и входные данные:

Икс — значение, которое нужно нормализовать.

иметь в виду — Среднее арифметическое распределения.

standard_dev — Стандартное отклонение распределения.

Что такое функция STANDARDIZE?

Функция Excel STANDARDIZE возвращает z-оценку значения с учетом указанного среднего значения и стандартного отклонения.

Что такое z-оценка?

Как следует из названия функции, z-оценка — это стандартизированная версия значения. С помощью z-значения вы преобразуете число из его необработанного значения в число, указывающее его расстояние от среднего значения в стандартных отклонениях.

Чтобы стандартизировать оценки таким образом, вы должны знать среднее значение и стандартное отклонение численность населения из которого берется значение. Если вы используете выборочные данные (например, если вы провели опрос и хотите экстраполировать свои результаты на более широкую совокупность), функция СТАНДАРТИЗАЦИЯ не подходит для использования.

Также обратите внимание, что формула, используемая для вычисления z-показателя, предполагает, что ваши данные соответствуют нормальному распределению.

Если ваши данные соответствуют этим требованиям, вы получите z-оценку для заданного значения с помощью следующих вычислений:

Стандартизация оценок позволяет сравнивать значения в разных наборах данных, даже данные, измеренные в разных единицах. Стандартизированные баллы также составляют основу ряда других статистических процедур — их вычисление чаще всего выполняется в качестве промежуточного шага к проведению дальнейших тестов.

Как использовать функцию СТАНДАРТИЗАЦИЯ

Используйте STANDARDIZE следующим образом:

Выше у нас есть список результатов тестирования класса из 10 человек. Их результаты показаны в столбце C, а в столбце D мы рассчитали z-оценку для каждого из них.

STANDARDIZE принимает три аргумента:

• Значение, которое вы хотите стандартизировать
• Среднее значение численность населения из которого взяты данные
• Стандартное отклонение численность населения из которого берутся данные (обратите внимание, что если вы определите стандартное отклонение ниже нуля, вы получите ошибку # ЧИСЛО!)

В этом примере нас интересуют только z-баллы учащихся этого класса. Мы не делаем оценок всех учащихся на основе этих данных, поэтому это данные о населении. В этом случае можно просто взять среднее значение и стандартное отклонение имеющихся данных. Я установил эти значения в таблице справа.

Я вычислил среднее значение в ячейке G3 с помощью функции среднего значения Excel <>:

И я рассчитал стандартное отклонение в G4 с помощью Excel STDEV.P Function <>:

Затем я подключил эти ссылки на ячейки в функцию СТАНДАРТИЗАЦИЯ. Знаки доллара вокруг столбца и строки (например, $ G $ 3) просто блокируют ссылку на ячейку, чтобы она оставалась неизменной при копировании формулы в столбец.

Итак, теперь в столбце D у нас есть z-значения для каждой точки данных. Как отмечалось ранее, z-оценка — это расстояние каждой оценки от среднего, выраженное в стандартных отклонениях. Таким образом, результат теста Ронды на 1,734 стандартного отклонения ниже среднего, оценка Бранди на 1,292 стандартного отклонения выше среднего и так далее.

Функция СТАНДАРТИЗАЦИЯ в Google Таблицах работает точно так же, как и в Excel:

Вы поможете развитию сайта, поделившись страницей с друзьями

Резюме

Функция СТАНДАРТИЗАЦИЯ в Excel возвращает нормализованное значение (z-оценку) на основе среднего значения и стандартного отклонения.

Цель

Рассчитайте нормализованное значение (z-показатель)

Возвращаемое значение

Нормализованное значение

Синтаксис

= СТАНДАРТИЗАЦИЯ (x; среднее; стандартное_откл.)

Аргументы

• x — значение для нормализации.
• среднее — Среднее арифметическое распределения.
• standard_dev — стандартное отклонение распределения.

Версия

Excel 2003

Примечания по использованию

Функция СТАНДАРТИЗАЦИЯ в Excel возвращает нормализованное значение (z-оценку) на основе среднего значения и стандартного отклонения. Чтобы использовать функцию СТАНДАРТИЗАЦИЯ, рассчитайте среднее значение с помощью функции СРЕДНЕЕ, а стандартное отклонение — с помощью функции СТАНДАРТ.П (см. Ниже).

В показанном примере формула в D5:

=STANDARDIZE(C5,$G$4,$G$5)

О z-показателях / стандартных показателях

Z-оценка или стандартная оценка — это способ стандартизации оценок по той же шкале путем деления отклонения оценки на стандартное отклонение в наборе данных. Результатом является стандартная оценка или z-оценка. Он измеряет количество стандартных отклонений данной точки данных от среднего.

Z-оценка может быть отрицательной или положительной. Отрицательный z-показатель указывает на значение меньше среднего, а положительный z-показатель указывает на значение, превышающее среднее. Среднее значение каждого z-показателя для набора данных равно нулю.

Чтобы вычислить z-оценку, необходимо вычислить среднее значение и стандартное отклонение. Формулы в G4 и G5, соответственно:

=AVERAGE(points) =STDEV.P(points)

Где «точки» — именованный диапазон C5: C12.

Если вы хотите узнать больше о z-показателях и статистическом анализе в Excel, я рекомендую книгу Джозефа Шмуллера «Статистический анализ в Excel для чайников».