10 супер аккуратных способов очистки данных в таблицах Excel

Посмотреть видео - 10 способов очистки данных в Excel

Данные составляют основу любого анализа, который вы проводите в Excel. А когда дело доходит до данных, есть масса вещей, которые могут пойти не так - будь то структура, размещение, форматирование, лишние пробелы и так далее.

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

# 1 Избавьтесь от лишних пробелов

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

Синтаксис: TRIM (текст)

Функция Excel TRIM принимает в качестве входных данных ссылку на ячейку (или текст). Он удаляет начальные и конечные пробелы, а также дополнительные пробелы между словами (кроме одиночных пробелов).

# 2 Выберите и обработайте все пустые ячейки

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

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

1. Выбрать весь набор данных
2. Нажмите F5 (откроется диалоговое окно "Перейти").
3. Нажмите кнопку Special… (внизу слева). Откроется диалоговое окно «Перейти к специальному».
   
4. Выберите «Пусто» и нажмите «ОК».
   

Это выберет все пустые ячейки в вашем наборе данных. Если вы хотите ввести 0 или Недоступно во всех этих ячейках, просто введите его и нажмите Control + Enter (помните, что если вы нажмете только ввод, значение будет вставлено только в активную ячейку).

# 3 Преобразование чисел, сохраненных в виде текста, в числа

Иногда, когда вы импортируете данные из текстовых файлов или внешних баз данных, числа сохраняются как текст. Кроме того, некоторые люди имеют обыкновение использовать апостроф (‘) перед числом, чтобы сделать его текстом. Это может создать серьезные проблемы, если вы будете использовать эти ячейки в расчетах. Вот надежный способ преобразовать эти числа, сохраненные в виде текста, обратно в числа.

1. В любой пустой ячейке введите 1
2. Выделите ячейку, в которой вы ввели 1, и нажмите Control + C.
3. Выберите ячейку / диапазон, который вы хотите преобразовать в числа
4. Выберите Вставить -> Специальная вставка (Сочетание клавиш на плате - Alt + E + S)
5. В диалоговом окне «Специальная вставка» выберите «Умножить» (в категории «Операции»).
   
6. Щелкните ОК. Это преобразует все числа в текстовом формате обратно в числа.

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

# 4 - Удалить дубликаты

Есть 2 вещи, которые вы можете сделать с повторяющимися данными: Выделите это или Удалите это.

• Выделите повторяющиеся данные:
  • Выберите данные и перейдите на главную страницу -> Условное форматирование -> Выделить правила ячеек -> Повторяющиеся значения.
  • Укажите форматирование, и все повторяющиеся значения будут выделены.
    
• Удалить дубликаты в данных:
  • Выберите данные и перейдите в раздел «Данные» -> «Удалить дубликаты».
  • Если у ваших данных есть заголовки, убедитесь, что установлен флажок в правом верхнем углу.
  • Выберите столбцы, из которых вы хотите удалить дубликаты, и нажмите «ОК».
    

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

Связанный: Полное руководство по поиску и удалению дубликатов в Excel.

# 5 Выделите ошибки

Выделить ошибки в данных в Excel можно двумя способами:

Использование условного форматирования

1. Выбрать весь набор данных
2. Перейдите на главную страницу -> Условное форматирование -> Новое правило.
3. В диалоговом окне «Новое правило форматирования» выберите «Форматировать только содержащиеся ячейки».
4. В описании правила в раскрывающемся списке выберите Ошибки.
5. Установите формат и нажмите ОК. Это выделяет любое значение ошибки в выбранном наборе данных.
   

Использование Go To Special

1. Выбрать весь набор данных
2. Нажмите F5 (откроется диалоговое окно "Перейти к").
3. Нажмите на специальную кнопку в левом нижнем углу.
4. Выберите формулы и снимите все флажки, кроме ошибок.
   

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

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

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

LOWER () - переводит весь текст в нижний регистр.
UPPER () - переводит весь текст в верхний регистр.
PROPER () - преобразует весь текст в правильный регистр.

# 7 Разбор данных с использованием текста в столбец

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

1. Выберите данные / текст, который хотите проанализировать
2. Перейти к данным -> Текст в столбец (откроется мастер преобразования текста в столбцы)
   • Шаг 1. Выберите тип данных (выберите «С разделителями», если ваши данные расположены неравномерно и разделены такими символами, как запятая, дефис, точка…). Нажмите кнопку "Далее
     
   • Шаг 2: Выберите разделитель (символ, разделяющий ваши данные). Вы можете выбрать предопределенный разделитель или что-нибудь еще, используя параметр Другое
     
   • Шаг 3: Выберите формат данных. Также выберите ячейку назначения. Если ячейка назначения не выбрана, текущая ячейка перезаписывается
     

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

# 8 Проверка правописания

Ничто не снижает доверие к вашей работе, чем орфографическая ошибка.

Используйте сочетание клавиш F7, чтобы запустить проверку орфографии для вашего набора данных.

Вот подробное руководство по использованию проверки орфографии в Excel.

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

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

1. Выберите набор данных
2. Перейти на главную -> Очистить -> Очистить форматы
   

Точно так же вы также можете удалить только комментарии, гиперссылки или контент.

# 10 Используйте поиск и замену для очистки данных в Excel

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

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

Это мои 10 лучших методов очистки данных в Excel. Если вы хотите узнать больше о методах, вот руководство от команды MS Excel - Очистить данные в Excel.

Если есть еще какие-то методы, которые вы используете, поделитесь ими с нами в разделе комментариев!