Как нормализовать данные в excel
С помощью простой формулы Excel позволяет нормализовать значения на основе среднего арифметического и стандартного отклонения, выбранного пользователем. Просто сообщите программе, какие ячейки имеют вышеупомянутые значения, и расчет выполняется полностью автоматически. Если вы хотите больше информации и хотите узнать подробно как нормализовать данные Excel, продолжайте читать.
Как шаг за шагом нормализовать данные Excel
Как уже отмечалось в начале поста, функция Excel NORMALIZE нормализует значения на основе среднего арифметического и стандартного отклонения, выбранного пользователем.
Более подробно это позволяет получить нормированное значение распределения, характеризующегося средства массовой информации y standard_dev, Синтаксис для использования заключается в следующем.
В только что указанной формуле x представляет значение, которое вы хотите нормализовать; МЕДА будет средним арифметическим распределения и standard_dev стандартное отклонение распределения (которое было бы квадратным корнем из дисперсии).
Как нормализовать данные Excel на ПК
Если вы хотите учиться как нормализовать данные Excel на ПК или Мак, первое, что нужно сделать, это запустить программу, открыть рабочая книга на котором вы хотите действовать, выделите мышкой клетка чтобы показать результат нормализации и запись по следующей формуле.
Вместо X, M y D координаты значение для нормализации, от среднее арифметическое распределения и стандартное отклонение распределения.
Чтобы привести практический пример, если вам нужно нормализовать значение 42 что в клетке A2 рабочий лист с использованием среднего арифметического 40 (помещен в камеру B2 ) и стандартное отклонение 1.5 (помещен в камеру C2 ), вы должны написать формулу = нормализовать (A2; B2; C2) и нажмите кнопку Enter в клавиатура с вашего ПК.
После нажатия Enter значение будет отображаться в ячейке, в которую вы ввели формулу. 1,333333333, что соответствует нормализации значения 42 на основе параметров, перечисленных выше.
Как видите, для нормализации данных в Excel достаточно воспользоваться формулой = нормализовать и сообщить программе, какие значения использовать в расчетах, указав их координаты в книге.
И если вы измените значения ячеек, участвующих в процессе, значение ячейки с результатом изменится автоматически без вашего вмешательства.
Если вы не хотите вручную вводить координаты каждой ячейки, вы также можете набрать Нормализовать = ( в ячейку, в которую вы вставляете результат операции и выбираете мышью значения нормализации (в предыдущем примере ячейки A2, B2 y C2 ) удерживая кнопку Ctrl клавиатура (или CMD на Mac). В конце выбора нажмите кнопку ключ Enter на клавиатуре ПК и все.
Как нормализовать данные в Excel Online
Если по тем или иным причинам вы не можете установить Excel на свой ПК, вы можете нормализовать данные в книге, используя Excel онлайн, веб-версия известного программного обеспечения Microsoft, доступная бесплатно из любого браузера для просмотра Интернет, после аутентификации с помощью учетной записи Microsoft.
Как вы можете легко догадаться, Excel Online не включает в себя все функции классического Excel для Windows или macOS, но он полностью совместим с функцией NORMALIZE, поэтому вы можете легко использовать ее для своих целей.
Сказав это, Excel Online очень прост: после подключения к домашней странице службы, войдите в свою учетную запись Microsoft (при необходимости) и выберите, хотите ли вы создать новая рабочая тетрадьвыбрав одну из моделей, показанных на экране, или, если загрузить рабочую книгу с ПКнажав на соответствующий элемент в правом верхнем углу.
На этом этапе он должен работать как в классическом Excel для Windows или MacOS, поэтому вы должны выбрать мышью клетка где вы хотите увидеть результат нормализации и должны написать следующую формулу.
Опять же вместо X, M y D необходимо ввести, соответственно, координаты значение для нормализации, от среднее арифметическое распределения и стандартное отклонение распределения.
Нажмите на Enter на клавиатуре, и вы увидите результат функции в ячейке, которую вы выбрали.
Как нормализовать данные Excel на мобильных телефонах и планшетах
Как и Excel Online, Excel для мобильных телефонов и планшетов не так универсален, как классическая версия программы для настольных компьютеров.
Тем не менее, он включает полную поддержку функции NORMALIZE, которая может использоваться, как показано выше, в варианте программного обеспечения Windows и macOS.
Поэтому после установки приложения все, что вам нужно сделать, это запустить его, войти в свою учетную запись Microsoft и выбрать, создавать ли новая рабочая тетрадь, выбрав одну из моделей, доступных на листе Nuevaили, если вы открываете существующий файл, выберите вкладку открыт.
Все основные услуги облачного хранения поддерживаются, например OneDrive, Google Диск, Dropbox и ICloud Drive.
После того, как вы открыли интересующий вас файл, нажмите кнопку клетка где вы хотите отобразить результат нормализации и введите следующую формулу.
Как всегда вместо X, M y D необходимо ввести, соответственно, координаты значение для нормализации, la среднее арифметическое распределения и стандартное отклонение распределения, Затем нажмите кнопку √ и вместо формулы вы увидите, что результат нормализации отображается в выбранной ячейке.
Функция НОРМАЛИЗАЦИЯ в Excel предназначена для нахождения нормализованного значения некоторой величины из распределения, характеризующегося известными показателями стандартного отклонения и среднего.
Примеры использования функции НОРМАЛИЗАЦИЯ в Excel
Значение, определяемое функцией НОРМАЛИЗАЦИЯ, используется для вычисления вероятности нахождения некоторой величины в диапазоне значений. Эту вероятность можно рассчитать в Excel с помощью функции НОРМ.СТ.РАСП. Таким образом, эти функции имеют следующую взаимосвязь: =НОРМ.СТ.РАСП(НОРМАЛИЗАЦИЯ(аргументы)).
Таким образом, функция НОРМАЛИЗАЦИЯ может быть использована для преобразования нормального распределения к стандартному нормальному. У такого распределения дисперсия равна 1, а математическое ожидание – 0. Таким образом, рассматриваемая функция использует следующий алгоритм вычислений:
- Z – вычисляемая величина, распределенная по стандартному нормальному закону;
- x - исходное значение;
- M – математическое ожидание;
- D – дисперсия.
Пример 1. Определить вероятность того, что некоторая величина, которая распределена по нормальному закону, меньше или равна значению 5. Для ряда значений этой величины известны следующие показатели: среднее – 1,7, стандартное отклонение – 2,4.
Вид таблицы данных:
Для нахождения вероятности используем следующую формулу:
Искомое число вероятности:
В итоговом результате вычисления функции получаем относительное – 92%.
Расчет процента вероятности с помощью нормализации в Excel
Пример 2. Данные о прочности изделий из исследуемой партии приведены в таблице Excel. Определить вероятность того, что потребитель купит партию изделий, прочность которых будет равна 20 Мпа или превысит это значение.
Вид таблицы данных:
Для нахождения вероятности используем следующую формулу:
С помощью функции НОРМ.СТ.РАСП определяем вероятность того, что прочность изделий из партии не будет соответствовать условию (больше 20Мпа). Поэтому искомое значение получаем в виде разности 1 и найденной вероятности. Для определения среднего значения и стандартного отклонений для исследуемого ряда используем функции СРЗНАЧ и СТАНДОТКЛОН.В соответственно.
Анализ данных в Excel предполагает сама конструкция табличного процессора. Очень многие средства программы подходят для реализации этой задачи.
Excel позиционирует себя как лучший универсальный программный продукт в мире по обработке аналитической информации. От маленького предприятия до крупных корпораций, руководители тратят значительную часть своего рабочего времени для анализа жизнедеятельности их бизнеса. Рассмотрим основные аналитические инструменты в Excel и примеры применения их в практике.
Инструменты анализа Excel
Одним из самых привлекательных анализов данных является «Что-если». Он находится: «Данные»-«Работа с данными»-«Что-если».
Средства анализа «Что-если»:
- «Подбор параметра». Применяется, когда пользователю известен результат формулы, но неизвестны входные данные для этого результата.
- «Таблица данных». Используется в ситуациях, когда нужно показать в виде таблицы влияние переменных значений на формулы.
- «Диспетчер сценариев». Применяется для формирования, изменения и сохранения разных наборов входных данных и итогов вычислений по группе формул.
- «Поиск решения». Это надстройка программы Excel. Помогает найти наилучшее решение определенной задачи.
Практический пример использования «Что-если» для поиска оптимальных скидок по таблице данных.
Другие инструменты для анализа данных:
Анализировать данные в Excel можно с помощью встроенных функций (математических, финансовых, логических, статистических и т.д.).
Сводные таблицы в анализе данных
Чтобы упростить просмотр, обработку и обобщение данных, в Excel применяются сводные таблицы.
Программа будет воспринимать введенную/вводимую информацию как таблицу, а не простой набор данных, если списки со значениями отформатировать соответствующим образом:
- Перейти на вкладку «Вставка» и щелкнуть по кнопке «Таблица».
- Откроется диалоговое окно «Создание таблицы».
- Указать диапазон данных (если они уже внесены) или предполагаемый диапазон (в какие ячейки будет помещена таблица). Установить флажок напротив «Таблица с заголовками». Нажать Enter.
К указанному диапазону применится заданный по умолчанию стиль форматирования. Станет активным инструмент «Работа с таблицами» (вкладка «Конструктор»).
Составить отчет можно с помощью «Сводной таблицы».
- Активизируем любую из ячеек диапазона данных. Щелкаем кнопку «Сводная таблица» («Вставка» - «Таблицы» - «Сводная таблица»).
- В диалоговом окне прописываем диапазон и место, куда поместить сводный отчет (новый лист).
- Открывается «Мастер сводных таблиц». Левая часть листа – изображение отчета, правая часть – инструменты создания сводного отчета.
- Выбираем необходимые поля из списка. Определяемся со значениями для названий строк и столбцов. В левой части листа будет «строиться» отчет.
Создание сводной таблицы – это уже способ анализа данных. Более того, пользователь выбирает нужную ему в конкретный момент информацию для отображения. Он может в дальнейшем применять другие инструменты.
Анализ «Что-если» в Excel: «Таблица данных»
Мощное средство анализа данных. Рассмотрим организацию информации с помощью инструмента «Что-если» - «Таблица данных».
- данные должны находиться в одном столбце или одной строке;
- формула ссылается на одну входную ячейку.
Процедура создания «Таблицы данных»:
- Заносим входные значения в столбец, а формулу – в соседний столбец на одну строку выше.
- Выделяем диапазон значений, включающий столбец с входными данными и формулой. Переходим на вкладку «Данные». Открываем инструмент «Что-если». Щелкаем кнопку «Таблица данных».
- В открывшемся диалоговом окне есть два поля. Так как мы создаем таблицу с одним входом, то вводим адрес только в поле «Подставлять значения по строкам в». Если входные значения располагаются в строках (а не в столбцах), то адрес будем вписывать в поле «Подставлять значения по столбцам в» и нажимаем ОК.
Анализ предприятия в Excel: примеры
Для анализа деятельности предприятия берутся данные из бухгалтерского баланса, отчета о прибылях и убытках. Каждый пользователь создает свою форму, в которой отражаются особенности фирмы, важная для принятия решений информация.
Для примера предлагаем скачать финансовый анализ предприятий в таблицах и графиках составленные профессиональными специалистами в области финансово-экономической аналитике. Здесь используются формы бухгалтерской отчетности, формулы и таблицы для расчета и анализа платежеспособности, финансового состояния, рентабельности, деловой активности и т.д.
Часто при расчетах в Excel у нас возникают ситуации, когда нужно просчитать формулу при изменении переменной или переменных. За примерами далеко ходить не надо:
- Какой будет ежемесячный платеж, если берется кредит на 100 тыс. руб. на 3 года при различных процентных ставках.
- Сколько необходимо платить в месяц, если взять в кредит на год, два, три, четыре, пять при различных процентных ставках банка.
Смотрите также видеоверсию статьи «Инструмент Таблица данных в Excel».
Задача 1. А ведь ничего сложного
На самом деле, ничего сверхъестественного в вычислениях нет. Для расчета ежемесячного платежа по аннуитету используется финансовая функция Excel ПЛТ (PMT). Соответственно, зная желаемую сумму займа и срок кредитования, можно рассчитать ежемесячный платеж.
Расчет ежемесечного платежа с помощью ПЛТ
Общая сумма выплат рассчитывается как ежемесячный платеж умноженный на количество периодов (всего 36 месяцев), проценты переплаты – это общая сумма выплат минус сумма займа.
Теперь, для того, чтобы сделать аналогичные расчеты, достаточно добавить желаемые варианты годового процента, в формулах расчета правильно воспользоваться абсолютными и относительными ссылками, ссылаясь на данные для расчета, и воспользоваться автозаполнением.
Благодаря правильному использованию различных типов ссылок и автозаполнению, можно сделать подсчет для различных процентных ставок
Решение первой задачи с помощью инструмента «Таблица данных».
Шаг 1. Делается первый необходимый расчет и создается заготовка под заполнение данными остальных расчетов. Здесь, кстати, абсолютно не важно каким типом ссылок пользоваться, поскольку автозаполнение применятся не будет.
При использовании «Таблица данных» не важен тип ссылок в формуле
Шаг 2. Выделяется диапазон с тестовым расчетом и заготовкой под остальные данные (в нашем случае это диапазон B5:G8), дальше выбирается инструмент «Таблица данных» на вкладке Данные, группа «Прогноз», команда «Анализ что если» (для версии Excel 2016, если у вас версия 2013, то таблица данных находится в группе «Работа с данными»).
Выбор инструмента «Таблица данных» на ленте интерфейса
Шаг 3. В диалоговом окне необходимо указать ссылку на ячейку, которая является переменной для расчетов. В нашем случае мы хотим посчитать различные варианты задачи при различных вариантах процентной ставки, значит необходимо поставить ссылку на годовой процент, который используется в вычислениях. Ссылку на годовой процент необходимо поставить в поле «Подставлять значения по столбцам в:», т.к. у нас значения заполняются по столбцам, а варианты годового процента расположены горизонтально. Если бы варианты различного процента были расположены вертикально, тогда ссылку необходимо было ставить во второе поле.
Также, поскольку у нас расчет идет по одной переменной, то заполняем только одно поле диалогового окна, а второе оставляем пустым.
Выбор ссылки на переменную в диалоговом окне
После нажатия «ОК» мы получи результат, а при выборе любого из значений, которое было рассчитано, в строке формул увидим формулу массива «», а не формулы расчета, как в случае с автозаполнением.
После расчета в строке формул находится только специальная функция ТАБЛИЦА
При решении первой задачи с одной переменной, очевидно, что проще воспользоваться просто автозаполнением, однако, не стоит спешить с выводами, ведь в инструменте «Подбор параметра» можно использовать для прогноза вычислений двух переменных.
Задача 2. Рассчитать ежемесячный платеж при различных сроках займа и различных процентных ставках.
Подготовительная таблица выглядит следующим образом.
Заготовка для прогноза значений с двумя переменными
При выделении таблицы важно, чтобы в левой верхней ячейке находилась формула с расчетом, в которую должны будут подставляться новые значения переменных.
Расстановка ссылок на переменные в диалоговом окне
Результат работы инструмента «Таблица данных»
Результат работы инструмента «Таблица данных» с двумя входами
Конечно, данный инструмент работает не только с функцией расчет платежа по аннуитету ПЛТ, а с любой формулой, где необходимо проследить изменения в зависимости от изменения определенных переменных.
Больше примеров работы с финансовыми функциями, инструментом «Таблица данных» в восьмом занятии курса «Excel от новичка до профессионала».
Здесь мы рассмотрим все, что вам нужно знать, чтобы лучше понять функцию СТАНДАРТИЗАЦИЯ, начиная с понимания, что такое стандартизованное значение и как мы можем его использовать.
Какова стандартизированная ценность распределения?
Когда кто-то стандартизирует (нормализует) значение из распределения, цель состоит в том, чтобы скорректировать значение распределения вероятностей для согласования. Когда результаты тестов стандартизированы, цель состоит в том, чтобы выровнять распределение оценок учащихся в классе с нормальным распределением.
Стандартизированные оценки полезны, потому что они позволяют определить вероятность получения оценки и возможность сравнить две оценки из разных нормальных распределений.
Назначение функции 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 - это стандартное отклонение.
Рекомендации
Статьи по Теме
Чтобы узнать больше об использовании функций в Excel, я рекомендую следующую книгу. Я использую Библию Excel в течение многих лет, чтобы лучше понять все аспекты этого продукта Microsoft.
Читайте также: