Как найти пропущенные значения в Excel

Обновлено: 02.07.2024

Одна из проблем, возникающих при сборе данных, заключается в том, что некоторые данные могут отсутствовать. Например, при проведении опроса с десятью вопросами некоторые из участников опроса могут не ответить на все десять вопросов. В разделе «Выявление выбросов и отсутствующих данных» показано, как определить отсутствующие данные с помощью инструмента анализа данных, входящего в пакет ресурсов Real Statistics.

Простым подходом к работе с отсутствующими данными является удаление всех данных для любой выборки, в которой отсутствует один или несколько элементов данных. Одна проблема с этим подходом заключается в том, что размер выборки будет уменьшен. Это особенно актуально, когда уменьшенный размер выборки слишком мал для получения значимых результатов анализа. В этом случае может потребоваться сбор дополнительных элементов выборочных данных. Эта проблема серьезнее, чем может показаться на первый взгляд. Например. если в анкете с 5 вопросами случайным образом отсутствуют 10 % данных, то в среднем около 41 % выборки будет содержать по крайней мере один вопрос.

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

Как правило, существуют следующие типы средств правовой защиты отсутствующих данных:

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

Удаление недостающих данных

Особое значение имеет случайность отсутствующих данных. Например. предположим, что многие люди не ответили на вопрос 5, но все ответили на вопрос 7. Если частота ответов на вопрос 7 значительно меняется, когда отбрасываются выборки, в которых отсутствуют ответы на вопрос 5, то недостающие данные не являются случайными, и поэтому падение образцов может привести к искажению результатов анализа. В этом случае следует либо применить другое средство, либо провести анализ дважды: один раз с сохранением образцов с отсутствующими данными (например, путем добавления «нет ответа» для отсутствующих данных) и один раз с удалением этих образцов.

Функции рабочего листа

Отсутствующие данные можно удалить с помощью следующих функций из пакета ресурсов Real Statistics.

Функции реальной статистики

DELBLANK(R1, s) — заполняет выделенный диапазон данными из диапазона R1 (по столбцам), опуская все пустые ячейки

DELNonNum(R1, s) — заполняет выделенный диапазон данными из диапазона R1 (по столбцам), опуская любые нечисловые ячейки

DELROWBLANK(R1, head, s) — заполняет выделенный диапазон данными из диапазона R1, опуская все строки, содержащие одну или несколько пустых ячеек; если head имеет значение TRUE, то первая строка R1 (предположительно содержащая заголовки столбцов) всегда копируется (даже если она содержит пустую ячейку); этот аргумент является необязательным и по умолчанию имеет значение head = FALSE.

DELROWNonNum(R1, head, s) — заполняет выделенный диапазон данными из диапазона R1, опуская все строки, содержащие одну или несколько нечисловых ячеек; если head имеет значение TRUE, то первая строка R1 (предположительно содержащая заголовки столбцов) всегда копируется (даже если она содержит нечисловую ячейку); этот аргумент является необязательным и по умолчанию имеет значение head = FALSE.

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

DELROWS(R1, head, blank): выводит массив с данными в R1, опуская любую строку, содержащую один или несколько пустых элементов, если blank< /em> = TRUE или один или несколько нечисловых элементов, если blank = FALSE (по умолчанию); если head = TRUE, то в вывод всегда включается первая строка; в противном случае (по умолчанию) первая строка обрабатывается как любая другая строка.

Связанные функции

Кроме того, существует функция CountFullRows(R1, blank), где blank = TRUE (по умолчанию) или FALSE.

CountFullRows(R1, TRUE) = количество строк в диапазоне R1, в которых нет пустых ячеек

CountFullRows(R1, FALSE) = количество строк в диапазоне R1, в которых нет нечисловых ячеек

Есть также связанная функция реальной статистики CountPairs(R1, R2, blank), где blank = TRUE (по умолчанию) или FALSE. Здесь мы рассматриваем пары ячеек из R1 и R2: iячейка в R1 соединена с i-ой ячейкой в R2

CountPairs(R1, R2, TRUE) = количество пар, для которых ни одна ячейка в паре не пуста

CountPairs(R1, R2, FALSE) = количество пар, для которых ни одна ячейка в паре не является пустой или нечисловой

Обратите внимание, что в стандартном Excel эквивалент CountPairs(R1, R2, TRUE) можно вычислить с помощью

CountPairs(R1, R2, FALSE) можно вычислить с помощью

Чтобы вычислить количество пар с одинаковыми числовыми элементами, мы можем использовать формулу

Примеры

Пример 1. Удалите все отсутствующие данные по списку (обозначенные пустой ячейкой) из образца в A3:G22 на рис. 1.

Рисунок 1. Удаление отсутствующих данных по списку

Поскольку мы хотим удалить любую строку, содержащую одну или несколько пустых ячеек (кроме первой строки, содержащей заголовки столбцов), мы используем формулу массива =DELROWBLANK(A3:G22,TRUE) для получения вывода в диапазоне I3: O22 на рис. 1.

Пользователи Excel 2021 или 365 могут использовать следующую формулу Excel для достижения того же результата:

Ячейка M24 содержит формулу =CountFullRows(A4:G22).

Инструмент анализа данных

Инструмент анализа реальной статистики: пакет ресурсов Real Statistics содержит инструмент анализа данных "Переформатирование диапазона данных по строкам", который предоставляет более простые в использовании версии функций DELROWBLANK и DELROWNonNum, описанных выше.

Мы также можем использовать инструмент анализа данных «Переформатирование диапазона данных» вместо функций DELBLANK и DELNonNum. Мы не будем демонстрировать здесь этот инструмент, но дополнительную информацию о том, как использовать этот инструмент, см. в разделе Преобразование данных и переформатирование.

Пример 2. Повторите пример 1, используя инструмент анализа данных "Переформатирование диапазона данных по строкам".

Чтобы использовать этот инструмент анализа данных, нажмите Ctrl-m и выберите параметр «Переформатирование диапазона данных по строкам». Появится диалоговое окно, как на рисунке 2. Заполните диалоговое окно, как показано, и нажмите OK. Появится точно такой же результат, как мы видели ранее (а именно, диапазон I3:O22 на рис. 1).

Рисунок 2. Диалоговое окно для переформатирования диапазона данных по строкам

Если бы мы ввели число 0 в поле Заполнитель, то все ячейки выходного диапазона содержали бы формулу массива

хотя значения всех ячеек в диапазоне I19:O22 будут пустыми. Как и раньше, если мы изменим содержимое ячейки B15 на 10,2, тогда в выходных данных появится строка для штата Айдахо и появятся только три строки с пустыми ячейками. Все ячейки в выходном диапазоне по-прежнему будут иметь одну и ту же формулу массива, а именно

Если бы мы отметили элемент Зафиксировать размер выходного диапазона, то инструмент анализа данных определил бы, что в четырех строках отсутствуют данные, и выдал бы диапазон с четырьмя строками меньше, а именно диапазон I3:O18. Хотя выходные данные будут отображаться точно так же, как в случае, описанном в предыдущем абзаце, на этот раз только диапазон I3:O18 будет содержать формулу =DELROWBLANK(A3:G22,TRUE). На этот раз, если ячейка B15 изменится на 10,2, то Айдахо будет добавлен к выходному диапазону, но поскольку выходной диапазон сводится только к строке 18, последняя входная строка (для штата Мэн) не будет отображаться, что, вероятно, не так. что мы хотим.

В заключение отметим, что параметр "Зафиксировать размер диапазона вывода" делает вывод более чистым (поскольку все строки содержат данные), но его не следует использовать, если есть вероятность того, что некоторые отсутствующие данные могут быть добавлены позже.

Мы будем использовать несколько формул Excel. Начнем с логической функции ЕСЛИ. Вот мой образец набора данных. Я ясно вижу, что некоторые числа от 1 до 20 отсутствуют. Но какие именно?

Найти пропущенные значения в Excel.

Начнем с функции ЕСЛИ.

Включите функцию ВПР.

Завершение функции ЕСЛИ.

Перетаскивание формулы Excel.

Итак, мой последний этап формулы — просто перетащить формулу в строку 20, чтобы Excel мог увидеть, какие строки до 20 включительно отсутствуют. Милая. Как это круто?. См. результаты ниже. Excel точно определил, какие значения отсутствуют. Вы использовали этот метод раньше или другим способом? Поделитесь в комментарии ниже. Вы бы использовали функцию ПОИСКПОЗ или как насчет кода Excel VBA?.

Вот и все. Отличный способ заполнить пропущенные значения в Excel.

Что дальше? Хотите больше советов по Excel?

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

Полный список постов блога Formula Friday

Вам нужна помощь с проблемой Excel?.

Наконец, я рад сообщить, что объединился с Excel Rescue, где вы можете БЫСТРО получить помощь.

Комментарии

Не совсем верно... у вас 20 в списке "отсутствующих чисел", потому что ваша формула работает только с A8.

Ширли, вы правы, спасибо, что указали на опечатку. Действительно, формула должна ссылаться на A9. Спасибо за указатель. С уважением!.

Предположим, у нас есть два списка, список A содержит всех студентов, список B содержит только студентов, сдавших экзамен. Итак, список A длиннее списка B, и все студенты из списка B включены в список A. Если мы хотим выяснить, какой студент не сдал экзамен, мы можем сравнить два списка и найти недостающее значение, чтобы подтвердить список имен. Собственно, в своей работе мы часто встречаемся с ситуациями, когда нужно сравнить два списка и выяснить пропущенные значения. В этом руководстве мы поможем вам найти пропущенные значения двумя способами: первый — с помощью функции условного форматирования в Excel, второй — с помощью формулы с функцией ВПР.

Предварительное условие:

Подготовьте два списка. Список A содержит всех студентов. Список B содержит часть из них.

Метод 1. Сравните два столбца, чтобы найти пропущенное значение с помощью условного форматирования

Шаг 1. Выберите список А и список Б.

Шаг 2. Нажмите «Главная» на ленте, затем «Условное форматирование» в группе «Стили».

Шаг 3. В раскрывающемся списке «Условное форматирование» выберите «Правила выделения ячеек» -> «Повторяющиеся значения».

Шаг 4. В диалоговом окне «Повторяющиеся значения» выберите «Уникальные» в раскрывающемся списке. Сохранить значение по умолчанию в значениях с раскрывающимся списком. Затем нажмите ОК.

Шаг 5. Убедитесь, что уникальные значения правильно помечены темно-красным цветом.

Этот способ можно использовать для поиска уникальных значений из двух списков.

Метод 2. Сравните два столбца, чтобы найти пропущенное значение по формуле

Вставьте новый столбец между списком A и списком B.

Шаг 1. В поле B2 введите формулу =ЕОШИБКА(ВПР(A2,$C$2:$C$11,1,ЛОЖЬ)). Функция VLOOLIP может помочь нам найти значение соответствия из списка B.Функция ЕОШИБКА используется для проверки того, существует ли значение совпадения или нет, она возвращает ИСТИНА или ЛОЖЬ.

Шаг 2. Перетащите маркер заполнения вниз. Убедитесь, что B4 и B10 заполнены TRUE.

Итак, в списке А Кальвин и Питер — это студенты, не сдавшие экзамен.

Шаг 3. Если вы хотите напрямую отобразить имя учащегося в новом списке, вы можете обновить формулу =ЕСЛИ(ЕОШИБКА(ВПР(A2,$C$2:$C$11,1,ЛОЖЬ)),A2, "") .

Шаг 4. После применения приведенной выше формулы для следующих ячеек отсутствующее значение будет отображаться непосредственно в новом списке.

Связанные функции

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

В этом посте рассказывается, как найти и заменить сразу несколько значений с помощью макроса VBA или формулы в Excel. Как сделать множественный поиск и замену в Excel. Предположим, что у вас есть несколько ячеек, содержащих .

Функция Excel XLOOKUP была добавлена в Excel в качестве бета-функции в августе 2019 г. и теперь доступна исключительно в Microsoft 365 (по состоянию на июль 2021 г.). Однако если вы попадаете в эту категорию и часто имеете дело с большими наборами файлов .

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

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

ВПР — одна из ключевых функций среди всех функций поиска и ссылок в Excel. Сегодня мы покажем вам применение функции ВПР при наличии двух интерполяционных таблиц. ПРИМЕР В таблицах 1 и 2 указаны курсы на 2020 год.

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

Введите следующий код:

Столбец "B" относится к списку номеров, а номера строк относятся к диапазону данных, поэтому в этом примере в столбце "B" выполняется поиск чисел от 8951 до 11859, а недостающие числа будут выводиться в пустой столбец.

Нажмите «Ctrl-Shift-Enter», чтобы ввести введенную формулу в виде массива. Если вы просто нажмете «Ввод», формула не будет работать.

Читайте также:


Безопасен ли браузер tor

Как сделать эффект полосы в фотошопе

Как установить одноклассники в опере

Прошивка для ремонта платы S9

Поиск слов онлайн