Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как категоризировать данные на основе значений в Excel?

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

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

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

Стандартизация (нормализация) — это процесс корректировки значения распределения вероятности для обеспечения согласованности. При стандартизации результатов тестирования целью является приведение распределения оценок учащихся в классе в соответствие с нормальным распределением.

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

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

Стандартизация используется для получения нормализованного значения из распределения, как было указано ранее. Нормированное значение также известно как Z-score. По сути, это стандартное отклонение от среднего значения для конкретной точки данных.

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

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

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

Функция СТАНДАРТЫ должна быть введена как формула в Excel. Вы можете вручную добавить эту формулу в ячейку, выполнив следующие действия

1. вы должны щелкнуть на ячейке

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

3. Вставьте значение x, после которого следует запятая.

4. Добавление среднего значения выполняется через запятую.

5. Добавьте запятую после стандартного отклонения.

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

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

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

X — необходимое и нормируемое значение.

Наиболее точно описывать среднее значение как среднее арифметическое из набора данных.

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

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

Функцию NORMALISE можно также вставить в электронную таблицу Excel. Этот метод предназначен для тех, кто не знаком с синтаксисом функций. Первые шаги по вставке начинаются ниже.

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

Читайте так же:
Как запустить макрос на основе значения, выбранного из раскрывающегося списка в Excel?

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

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

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

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

Примеры синтаксиса функции 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).

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

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

Сортировка данных в Excel

Если данные текстовые, их можно отсортировать по алфавиту («от А до Я» или «от Я до А»). Если данные числовые, их можно отсортировать в порядке возрастания или убывания. Если в диапазоне данных есть строка или столбец, в которых содержатся данные типа время или дата, их можно отсортировать в прямом или обратном хронологическом порядке. Имеется также возможность сортировки предварительно отформатированных данных по элементам этого форматирования.

Вы можете сортировать данные по одному условию (например, сортировать сотрудников по фамилии) или по нескольким условиям (например, сортировать сотрудников по должности и располагать имена в алфавитном порядке в пределах каждой должности). Есть возможность сортировать данные по столбцам (или нескольким столбцам) или по строкам.

Сортировка по одному критерию

  1. Выберите ячейку в столбце, который вы хотите отсортировать (необязательно выбирать весь столбец). Найдите группу команд Сортировка и фильтр на вкладке Данные.
Читайте так же:
Как извлечь уникальные значения из нескольких столбцов в Excel?

Сортировка и фильтр

  1. Выбрать нужную кнопку: Сортировка по возрастаниюсортировка по возрастанию или Сортировка по убываниюсортировка по убыванию.

Обратите внимание, что буквы на этой кнопке указывают только направление сортировки, а внешний вид кнопки одинаков как для текстовых, так и для числовых данных.

Существует и другой удобный способ сортировки данных: щелкнув правой кнопкой мыши по ячейке столбца, по которому будет выполняться сортировка, в контекстном меню выбрать пункт Сортировка [Sort], а далее – требуемый вариант сортировки.

Многоуровневая сортировка

  1. Выберите ячейку из матрицы данных для сортировки.

Пока в массиве данных есть пустые столбцы или строки, Excel интерпретирует их как границы массива данных, подлежащих сортировке. В этом случае следует выделить все данные, которые необходимо отсортировать.

Сортировка данных

  1. Найдите группу команд Сортировка и фильтр на вкладке Данные и выберите в ней команду Сортировка.
  2. Установите уровни сортировки по порядку (определяется именем столбца).

Нажмите на стрелку рядом с тремя полями (Column, Sort, Order) для выбора:

  1. Имя столбца, который нужно отсортировать.
  2. Тип критерия (в зависимости от того, хотите ли вы сортировать по значению данных в столбце, по виду ячейки или по значку ячейки). Порядок сортировки (по убыванию или по возрастанию).

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

Сортировка по форматированию

Заполнение ячеек (или шрифтов) цветом часто используется для анализа данных. Сортировка также может использоваться для упорядочивания данных на основе форматирования.

Сортировка данных

Трин для процедуры трина:

  1. Щелкнуть по любой ячейки из столбца, по которому будет выполняться сортировка.
  2. На вкладке Данные [Data] выбрать группу Сортировка и фильтр [Sort&Filter], а затем выбрать команду Сортировка [Sort].
  3. В поле Столбец [Column] укажите столбец по которому будет проводиться сортировка.
  4. В поле Сортировка [Sort On] из всплывающего меню выбрать критерий сортировки: цвет ячейки, цвет шрифта или значок ячейки.
  5. Поле Порядок [Order] содержит два выпадающих списка. В первом нужно выбрать тип критерия, а во втором – размещение ячеек, отсортированных по данному критерию (строку Сверху [On Top] или Снизу [On Bottom]).
  6. При необходимости добавить еще один критерий сортировки, в окне Сортировка нужно выбрать кнопку Добавить уровень.
Читайте так же:
Как изменить цвет формы в зависимости от значения ячейки в Excel?

Сортировка по цвету

Можно также воспользоваться командой «Копировать уровень» [Copy Level], заменив в поле «Порядок» прежнее значение на новое.

Как категоризировать данные на основе значений в Excel?

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

Выпадающий список с контекстным поиском

Поэтому он представляет собой выпадающий список (так называемое комбинированное поле), в который встроена функция динамического поиска подстроки, вводимой пользователем с клавиатуры. Рассмотрим пример с 300 крупнейшими городами России. Анимированная иллюстрация показывает, как мы динамически сужаем список, вводя подстроку «cr» или «lower», экономя огромное количество времени. Более того, список меняется по мере ввода каждого нового персонажа! Это выглядит очень привлекательно и профессионально, не так ли? Давайте выясним, как это работает.

Файл примера

Скачать

Пошаговая инструкция

Предварительные замечания

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

Шаг 1. Готовим таблицу для списка

Подготовьте таблицу с четырьмя столбцами: Город (или то, что вы хотите), Статус, Индекс, Фильтр. Заполните столбец «Город» значениями. В остальных трех колонках будут формулы, которые мы обсудим ниже. Я рекомендую всем использовать умную электронную таблицу, так как это намного проще.

Шаг 2. Формулы для столбца Статус

Используя ячейку F2 в качестве примера, давайте рассмотрим формулу, аналогичную той, которая используется для всего столбца State (столбец F ). Начиная с F2, формулу можно растянуть до конца, а в случае с «умной» электронной таблицей Excel сделает это за вас. Это также относится ко всем формулам, которые мы обсудим в этой статье.

Читайте так же:
Как массово преобразовать текст в дату в Excel?

$B$2 — это ячейка, к которой будет привязан выпадающий список (добавленный в шаге 6). Что вы имеете в виду под словом «связанный»? Все, что вы введете в выпадающем списке, немедленно появится в ячейке B2 .

Формула SEARCH возвращает ошибку, если содержимое B2 не найдено в $E2 . ERROR перехватит ошибку и вернет TRUE, если ошибка действительно была, и FALSE, если строка была найдена. Функция НЕ делает из true false и наоборот (инвертирует результат). Таким образом, мы получим TRUE в этом столбце, если подстрока находится в текущем городе, и наоборот. Обратите внимание, что пустая подстрока содержится в любой строке, поэтому все ячейки в столбце Status имеют значение TRUE, когда мы еще ничего не ввели в B2 .

= НЕ( ЕОШИБКА ( ПОИСК ( $B$2 ; $E2 ) ) )
=NOT( ISERROR( SEARCH( $B$2; $E2) ) )
= НЕ ( ЕОШИБКА ( ПОИСК ( $B$2 ; [@ Город ]) ) )
=NOT( ISERROR( SEARCH( $B$2; [@Город]) ) )

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

Шаг 3. Формула для столбца Индекс

Если B2 содержит поисковую подстроку, не все ячейки в столбце Status будут принимать значение TRUE. Статус будет TRUE только в том случае, если название города содержит соответствующую подстроку. Кроме того, мы вычисляем номер по порядку для всех строк, содержащих подстроку. Например, на рисунке ниже B2 содержит «nor», что делает столбец Status истинным для строк с городами Нижний Новгород, Калининград, Магнитогорск и т.д., а в столбце Index мы начинаем считать срабатывающие факты по F: Нижний Новгород — первое срабатывание, Калининград — второе, и так далее.

Функция IF отсекает все значения в F, которые не равны TRUE. Подсчитывает количество истинных значений в F.

= ЕСЛИ( $F2 ; СЧЁТЕСЛИ ( $F$2:$F2 ; ИСТИНА ); «»)
=IF( $F2; COUNTIF( $F$2:$F2; TRUE ); «»)
= ЕСЛИ ( [@ Статус ]; СЧЁТЕСЛИ ( $F$2 :[@ Статус ]; ИСТИНА ); «»)
=IF( [@Статус]; COUNTIF( $F$2:[@Статус]; TRUE ); «»)

Обратите внимание, что диапазон условия в SCREEN захватывается при перетаскивании — вторая координата не фиксирована — и растет пропорционально массиву по мере его растяжения. С помощью этого трюка мы получаем механизм подсчета значения TRUE. Например, строка 6 будет считать TRUE в диапазоне $F$2:$F6 (есть одно значение — Нижний Новгород), а строка 41 будет считать TRUE в диапазоне $F$2:$F41 (есть 2 значения — Нижний Новгород и Калининград). Это основной механизм. Это полезный прием, который нужно запомнить.

Читайте так же:
Как найти версию Excel, которую вы используете сейчас?

Шаг 4. Формула для столбца Фильтр

В результате нам потребуется создать в столбце H отфильтрованный список городов для выпадающего меню на основе столбца Index.

= ЕСЛИОШИБКА( ИНДЕКС ( стлГород ; ПОИСКПОЗ ( ЧСТРОК ( $G$2:$G2 ); стлИндекс ; 0) ); «»)
=IFERROR( INDEX( стлГород; MATCH( ROWS($G$2:$G2); стлИндекс; 0) ); «» )

Обратите внимание на динамические именованные диапазоны stlCity и stlIndex, которые мы должны создать для диапазона, чтобы придать решению соответствующую степень универсальности. Техника, с помощью которой создаются эти именованные интервалы, объясняется здесь.

= ЕСЛИОШИБКА ( ИНДЕКС ( [ Город ]; ПОИСКПОЗ ( ЧСТРОК ( $G$2 :[@ Индекс ]); [ Индекс ]; 0) ); «»)
=IFERROR( INDEX( [Город]; MATCH( ROWS($G$2:[@Индекс]); [Индекс]; 0) ); «» )

Не перепутайте: [ Index ] — это ссылка на весь столбец, а [ @ Index ] — это ссылка на ячейку в текущей строке этого столбца. Поскольку при ссылке на столбцы мы используем встроенный сервис в смарт-таблицах, нам не нужно создавать дополнительные именованные диапазоны.

Формула CHSTROCK ( $G$2:$G2 ) используется для генерации последовательных чисел от 1 (для второй строки) до N (на строке N+1), равных количеству найденных подстрок. Просто создайте диапазон соответствующего размера, и формула CHSTROCK вернет его высоту строки.

S EARCHPOZ ищет номер фактической строки, которая содержит индекс. Например, мы видим Магнитогорск на позиции 3 в колонке Filter, но на самом деле он взят из E45, потому что G45 содержит 3 в TITLE, который мы нашли с помощью SEARCHPOZ. То есть SEARCHARCH сообщил нам, что Магнитогорск находится в 45-й строке, и мы извлекли его оттуда с помощью формулы INDEX.

Когда происходит ошибка извлечения (текущая строка находится ниже строки N+1), формула возвращает пустую строку. За это отвечает функция IF ERROR .

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector