8 лучших методов очистки данных Excel, которые нужно знать в 2023 году

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

Несколько простых шагов могут легко выполнить процедуру очистки данных в Excel с помощью Excel Power Query. Этот урок поможет вам узнать о некоторых фундаментальных и простых методах очистки данных в Excel.

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

Удалить дубликаты

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

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

В исходном наборе данных есть две строки-дубликаты. Чтобы удалить дубликаты данных, вам нужно выбрать параметр данных на панели инструментов, а на ленте инструментов данных выбрать параметр «Удалить дубликаты». Это предоставит вам новое диалоговое окно, как показано ниже.

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

Далее вам необходимо сравнить все столбцы, поэтому проверьте все столбцы, как показано ниже.

Нажмите «ОК», и Excel выполнит необходимые операции и предоставит вам набор данных после фильтрации дубликатов, как показано ниже.

В следующей части курса «Очистка данных Excel» вы познакомитесь с процессом анализа данных из текста в столбец.

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

Анализ данных из текста в столбец

Иногда существует вероятность, что одна ячейка может иметь несколько элементов данных, разделенных разделителем данных, например запятой. Например, предположим, что есть один столбец, в котором хранится информация об адресе.

В столбце адреса хранятся улица, район, штат и страна. Запятые разделяют все элементы данных. Теперь вам нужно разделить улицу, район, штат и страну из столбцов адреса на отдельные столбцы.

Встроенная функция Excel под названием «текст в столбец» может этого добиться. Теперь попробуйте пример для того же самого.

Здесь у вас есть производитель автомобиля и название модели автомобиля, разделенные пробелом в качестве разделителя данных. Табличные данные показаны ниже.

Выберите данные, щелкните параметр данных на панели инструментов, а затем выберите «Текст в столбец», как показано ниже.

На экране появится новое окно, как показано ниже. Выберите опцию разделителя и нажмите «Далее». В следующем окне вы увидите еще одно диалоговое окно.

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

В последнем диалоговом окне выберите формат данных столбца «Общий», а следующим шагом будет нажатие кнопки «Готово», как показано на следующем рисунке.

Окончательные результирующие данные будут доступны, как показано ниже.

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

Станьте самым высокооплачиваемым экспертом по бизнес-анализу с магистерской программой по бизнес-анализуУзнайте сейчас

Удалить все форматирование

Другой хороший способ очистки данных в Excel — обеспечить равномерное форматирование или, в некоторых случаях, даже удалить форматирование. Форматирование может быть таким же простым, как раскрашивание ячеек и выравнивание текста в ячейках. Это может быть логическое условие, примененное к вашим ячейкам с помощью опции условного форматирования Excel на вкладке «Главная».

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

Теперь используйте опцию очистки, чтобы удалить форматы. Выберите табличные данные, как показано ниже. Выберите опцию «home» и перейдите в группу «editing» на ленте. Опция «clear» доступна в группе, как показано ниже.

Выберите опцию «очистить» и нажмите на опцию «очистить форматы». Это очистит все форматы, примененные к таблице.

Итоговая таблица данных будет выглядеть так, как показано ниже.

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

Чтобы отменить условное форматирование в Excel, выберите столбец или таблицу с условным форматированием, как показано ниже.

Затем перейдите в раздел «Главная» и выберите условное форматирование.

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

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

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

АТЛ + Э + А + Ф

Далее в этом уроке по очистке данных Excel вы узнаете о проверке орфографии.

Проверка орфографии

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

Теперь перейдите к опции обзора, как показано ниже.

Microsoft Excel автоматически покажет правильное написание в диалоговом окне, как показано ниже. Вы можете заменить слова в соответствии с требованиями, как показано ниже.

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

В следующем разделе этого руководства по очистке данных Excel вы узнаете об изменении регистра текста.

Изменить регистр – нижний/верхний/правильный

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

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

Выберите ячейку рядом со столбцом и примените формулу в соответствии с требованиями, как показано ниже.

=UPPER(адрес ячейки) – для преобразования в верхний регистр

=LOWER(адрес ячейки) – для преобразования в нижний регистр

=ПРОПНАЧ(адрес ячейки) – для преобразования регистра предложений

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

Итоговая таблица данных будет выглядеть так, как показано ниже.

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

Выделение ошибок

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

Представьте, что вы опрашиваете всех студентов. Есть критерии соответствия. Вы можете включить студентов в шорт-лист, если они набрали 60% совокупных баллов. Теперь примените условное форматирование и отсортируйте студентов, которые подходят и не подходят.

Сначала выберите столбец «Агрегат/Процент», как показано ниже.

Выберите «Главная» и в группе «Стили» выберите условное форматирование, как показано ниже.

В параметре условного форматирования выберите параметр выделения, а в следующем раскрывающемся списке выберите параметр «меньше», как показано ниже.

В окне настроек найдите поле для указания совокупного значения в процентах «60» и нажмите «ОК».

Excel теперь выберет и выделит ячейки с совокупностью менее 60 процентов. В следующей части Excel Data Cleaning вы поймете функцию обрезки.

Стратегии лидерского совершенстваБесплатный вебинар | 6 декабря, среда | 19:00 ISTРЕГИСТРИРУЙТЕСЬ СЕЙЧАС!

Функция TRIM

Функция TRIM используется для устранения лишних пробелов и табуляций в ячейках листа Excel. Избыточные пробелы и табуляционные пробелы затрудняют понимание данных. Использование функции “TRIM” может устранить эти лишние пробелы.

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

Примените функцию TRIM() и перетащите ячейку, как показано ниже.

Окончательные данные после устранения лишнего пространства показаны ниже.

Далее в руководстве по очистке данных Excel вы рассмотрите функцию «Найти и заменить».

Найти и заменить

Find and Replace поможет вам извлечь и заменить данные на всем листе, чтобы помочь в организации и очистке данных в Excel. Рассмотрим пример с данными сотрудников.

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

Опция «найти и заменить» присутствует на домашней ленте в группе редактирования, как показано ниже.

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