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

How to Normalize Data Excel – Normalization in Excel

As an analyst, you must have encountered the concept of data normalization many times while working. This grave concept demands recognizing its importance for the success of an analyst. Therefore, you need to learn how to normalize data Excel for successful project completion.

What is Normalization?

Normalization is often considered as standardization, in which you can transform data based on the mean and standard deviation for the complete dataset. This transformed data simply refers to a standard distribution with a mean of 0 and variance of 1.

what is normalization

Next, you need to find the standard deviation. For this, you can use the following formula:

=STDEV(range of value)

Since we have everything we need to find the normalized data, let’s find it out by following the below-given formula:

=STANDARDIZE(X, mean of range, standard deviation of the range)

Final Words:

Normalization or standardization functions are equally important when you are assigned to make a presentation on a specific subject or else when you need to minimize the data size to make deductions. Both functions are used for calculating the mean and standard deviation and each function has its formula that you can use.

Excel as you know is an extensive tool for analyzing datasets of multiple kinds that’s why advancement has been made in this software to perform functions of any kind.

Функция НОРМАЛИЗАЦИЯ возвращает нормализованное значения в Excel

Функция НОРМАЛИЗАЦИЯ в Excel предназначена для нахождения нормализованного значения некоторой величины из распределения, характеризующегося известными показателями стандартного отклонения и среднего.

Примеры использования функции НОРМАЛИЗАЦИЯ в Excel

Значение, определяемое функцией НОРМАЛИЗАЦИЯ, используется для вычисления вероятности нахождения некоторой величины в диапазоне значений. Эту вероятность можно рассчитать в Excel с помощью функции НОРМ.СТ.РАСП. Таким образом, эти функции имеют следующую взаимосвязь: =НОРМ.СТ.РАСП(НОРМАЛИЗАЦИЯ(аргументы)).

Таким образом, функция НОРМАЛИЗАЦИЯ может быть использована для преобразования нормального распределения к стандартному нормальному. У такого распределения дисперсия равна 1, а математическое ожидание – 0. Таким образом, рассматриваемая функция использует следующий алгоритм вычислений:

Z – вычисляемая величина, распределенная по стандартному нормальному закону;
x — исходное значение;
M – математическое ожидание;
D – дисперсия.

Пример 1. Определить вероятность того, что некоторая величина, которая распределена по нормальному закону, меньше или равна значению 5. Для ряда значений этой величины известны следующие показатели: среднее – 1,7, стандартное отклонение – 2,4.

Вид таблицы данных:

Пример 1.

Для нахождения вероятности используем следующую формулу:

Для вычисления вероятности вхождения в диапазон (<=5) используем функцию НОРМ.СТ.РАСП со вторым аргументом, принимающим значение ИСТИНА (интегральная). Значение z (нормализованное) определено с помощью рассматриваемой функции.

Искомое число вероятности:

НОРМАЛИЗАЦИЯ.

В итоговом результате вычисления функции получаем относительное – 92%.

Расчет процента вероятности с помощью нормализации в Excel

Пример 2. Данные о прочности изделий из исследуемой партии приведены в таблице Excel. Определить вероятность того, что потребитель купит партию изделий, прочность которых будет равна 20 Мпа или превысит это значение.

Вид таблицы данных:

Пример 2.

Для нахождения вероятности используем следующую формулу:

С помощью функции НОРМ.СТ.РАСП определяем вероятность того, что прочность изделий из партии не будет соответствовать условию (больше 20Мпа). Поэтому искомое значение получаем в виде разности 1 и найденной вероятности. Для определения среднего значения и стандартного отклонений для исследуемого ряда используем функции СРЗНАЧ и СТАНДОТКЛОН.В соответственно.

Как нормализовать данные в Excel?

Термин «нормализация» сам по себе является модным словечком, которое популярно среди людей, работающих в различных областях, таких как машинное обучение, наука о данных, статистика и т. д. Нормализация — это общий термин, означающий уменьшение значений в пределах определенного диапазона. Происхождение слова «нормализация» как модного слова связано с тем, что оно часто неправильно понимается людьми и взаимозаменяемо используется с другим статистическим термином «стандартизация». В этой статье мы собираемся демистифицировать оба этих термина, а позже мы прочитаем, как мы можем реализовать эти методы на образце набора данных в Excel.

Нормализация (или масштабирование Min-Max) данных в excel

Это процесс масштабирования данных таким образом, чтобы все точки данных находились в диапазоне от 0 до 1. Таким образом, этот метод позволяет привести все точки данных к общему масштабу. Математическая формула нормализации имеет вид:

, где X — точка данных, X _max и X _min — максимальное и минимальное значение в группе записей соответственно. Процесс нормализации обычно используется, когда распределение данных не соответствует распределению Гаусса.

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

Рост (в см)
152
155
168
175
153
162
173
166
158
156

Шаг 1: Рассчитайте минимальное значение в распределении. Его можно рассчитать с помощью функции MIN(). Минимальное значение получается равным 152, которое хранится в ячейке B14.

Шаг 2: Рассчитайте максимальное значение в распределении. Его можно рассчитать с помощью функции MAX(). Максимальное значение равно 175, которое хранится в ячейке B15.

Шаг 3: Найдите разницу между максимальным и минимальным значениями. Их разница получается 175 – 152 = 23, которая хранится в ячейке B16.

Шаг 4: Для первых данных, хранящихся в ячейке A2, мы рассчитаем нормализованное значение, как показано на видео ниже.

Шаг 5: Мы можем вручную вычислить все значения по одному для каждой записи данных или мы можем напрямую получить значения для всех других ячеек, используя функцию автоматического заполнения Excel. Для этого перейдите в правый угол ячейки B2, пока не появится символ (+), а затем перетащите курсор вниз, чтобы автоматически заполнить значения внутри всех ячеек.

Примечание. При вычислении первого нормализованного значения в ячейке B2 следует убедиться, что эталонный адрес для ячеек B14 и B16 должен быть заблокирован с помощью кнопки Fn + F4, иначе будет выдана ошибка.

Если мы внимательно посмотрим на результаты, то заметим, что все значения лежат в диапазоне от 0 до 1.

Стандартизация (или нормализация Z-оценки)

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

, где где X — точка данных, X _mean — среднее значение распределения, а σ _x — стандартное отклонение распределения.

Процесс стандартизации обычно используется, когда мы знаем, что распределение данных соответствует распределению Гаусса.

Метод 1: расчет нормализации z-показателя вручную

Шаг 1: Рассчитайте среднее/среднее значение распределения. Это можно сделать с помощью функции СРЗНАЧ(). Среднее значение получается равным 161,8 и сохраняется в ячейке B14.

Шаг 2: Рассчитайте стандартное отклонение распределения, которое можно выполнить с помощью функции СТАНДОТКЛОН(). Стандартное отклонение получается равным 8,323994767, которое хранится в ячейке B15.

Шаг 3: Для первых данных, хранящихся в ячейке A2, мы рассчитаем стандартизированное значение, как показано на изображении ниже.

Шаг 4: После ручного вычисления первого значения мы можем просто использовать функцию автоматического заполнения Excel, чтобы заполнить стандартизированными значениями все остальные записи.

Примечание. При вычислении первого стандартизированного значения в ячейке B2 следует убедиться, что эталонный адрес для ячеек B14 и B15 должен быть заблокирован с помощью кнопки Fn+F4, иначе будет выдана ошибка.

Метод 2. Вычисление нормализации Z-показателя с помощью функции СТАНДАРТИЗАЦИЯ()

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

Где x — конкретный элемент/диапазон ячеек, mean — среднее/среднее арифметическое всех элементов в записи, а std_dev — стандартное отклонение всех элементов в записи.

Шаг 3: Для первых данных, хранящихся в ячейке A2, мы рассчитаем стандартизированное значение, как показано на рисунке ниже.

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

В этой статье описаны синтаксис формулы и использование функции НОРМАЛИЗАЦИЯ в Microsoft Excel.

Описание

Возвращает нормализованное значение для распределения, характеризуемого средним и стандартным отклонением.

Синтаксис

Аргументы функции НОРМАЛИЗАЦИЯ описаны ниже.

X Обязательный. Нормализуемое значение.

Среднее Обязательный. Среднее арифметическое распределения.

Стандартное_откл Обязательный. Стандартное отклонение распределения.

Замечания

Если standard_dev ≤ 0, возвращается стандартное #NUM! значение ошибки #ЗНАЧ!.

Уравнение для нормализованного значения имеет следующий вид:

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

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

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

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

Нормализованное значение числа 42, полученное с использованием числа 40 в качестве среднего арифметического и числа 1,5 в качестве стандартного отклонения.

Электронная почта