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

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

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

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

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

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

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

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

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

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

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

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

Поднимите свои навыки специалиста по данным на новый уровень с помощью магистерской программы Data Scientist от IBMExplore Programme

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

АТЛ + Е + А + Ф

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выделить ошибки

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

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

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

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

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

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

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

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

Функция ПОДРЕЗКИ

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

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

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

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

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

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

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

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

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

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

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

На этом вы подошли к концу руководства «Очистка данных Excel».

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