Удаление отсутствующих значений

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

Отсутствующие значения обозначаются как "Не число" или сокращенно "NaN". В Excel NaN обозначается как пустая ячейка или специальное представление "#N/A". Существует несколько встроенных функций, которые можно использовать для определения #N/A, например, NA (), ISNA (.) и IFERROR(.).

Размер набора данных

Наблюдение в наборе данных может быть определено либо парой значений двух переменных (x, y), либо несколькими значениями трех или более переменных (например, (x, y, z, w)). Точка данных в одномерном временном ряду может быть выражена как (t, x_t).

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

В двумерном наборе данных каждая строка представляет собой точку данных, а каждый столбец - входную переменную. Если одна или несколько ячеек в строке имеют пустое значение или значение #N/A, точка данных (т. е. строка) считается точкой данных с отсутствующим значением и, таким образом, исключается.

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

Почему меня это должно волновать?

Работа с набором данных с отсутствующими значениями в Excel представляет собой сложную задачу. Хотя в Excel есть несколько встроенных функций (например, MIN(), MAX(.) COUNT(.)), которые принимают такие наборы данных и молча игнорируют наблюдения с отсутствующими значениями, большинство функций (например, STDEV(.), CORREL(.)) не поддерживают их и возвращают ошибки (например, #VALUE), если такие наборы данных передаются в качестве входных данных.

RMNA(.) Функция

Во многих мастерах NumXL есть отдельная вкладка для работы с точками данных с отсутствующими значениями. На этой вкладке пользователю предлагаются различные политики: не принимать, удалить обс., заменить, интерполировать, подгонка данных и т. д. Если пользователь выбирает политику обработки пропущенных значений "удалить obs.", то входные наборы данных сначала подвергаются предварительной обработке с помощью RMNA(.) в генерируемых формулах.

Что такое RMNA(.)? RMNA - это простая, но мощная служебная функция в NumXL, единственной задачей которой является обнаружение и удаление наблюдений с одним или несколькими отсутствующими значениями.

Функция RMNA принимает два аргумента: X и Y. X и Y состоят из одного или нескольких столбцов, но если задан Y, то количество строк в X должно быть равно Y.

В нотации RMNA(.) точкой данных является (X, Y), но RMNA(X, Y) возвращает обработанный X. Если мы хотим вернуть обработанный Y, то изменим порядок следования пар X и Y (т. е. (Y, X)).

Пример 1: Рассмотрим следующий сценарий: в наборе данных с одним столбцом есть две точки данных с пропущенными значениями. Пропущенные значения представлены в виде #N/A и пустой/неполной ячейки.

Использование встроенных функций Excel: AVERAGE(.), STDEV(.), SKEW(.) и KURT(.) возвращает ошибку #N/A, но функция COUNT(.) молча отбрасывает точки данных с отсутствующими значениями и возвращает подсчет оставшихся точек данных (т.е. 18).

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

Пример 2: Рассмотрим набор данных из двух переменных (X, Y), для которых мы хотим рассчитать кросс-корреляцию (XCF или встроенная в Excel программа CORREL).

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

Мы можем использовать RMNA(.) двумя разными способами:

Метод 1: Передайте весь набор данных (столбцы B и C) в качестве одного аргумента.

Функция RMNA(.) возвращает исходный набор данных, отбросив все строки, содержащие одно или несколько отсутствующих значений.

Метод 2: Предварительно обработайте каждый столбец отдельно, но используйте другой столбец в качестве дополнительной ссылки.

Используя другой столбец в качестве необязательного аргумента ссылки (например, Y), RMNA(.) также проверяет точки данных на наличие отсутствующих значений, но функция возвращает только набор данных, указанный в первом аргументе (например, X).

Обратите внимание, что, поменяв местами X и Y в RMNA(.), мы можем получить набор данных X2 после отбрасывания недостающих значений, найденных в X2 или в матрице X1.

Заметным преимуществом метода 2 перед методом 1 является то, что функция не требует, чтобы столбцы X1 и X2 были смежными.

Пример 3: Давайте настроим модель электронной таблицы на автоматическое обновление по мере поступления новых данных.

Обратите внимание, что при добавлении значения в B21, RMNA(.) будет включать это новое значение, заставляя AVG и другие функции обновить свои вычисления.

Резюме

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

Чтобы узнать больше технических подробностей о функции RMNA в NumXL, пожалуйста, ознакомьтесь с этой статьей страница справочного руководства. Вы можете скачать полнофункциональный 14-дневная пробная версия NumXL и попробуйте сами воспользоваться функцией RMNA.