8 Способов очистки данных в Microsoft Excel

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

К сведению: нужно также очистить данные на вашем диске “C”? Мы покажем вам как.

Содержание

• 1. Устранение лишних пробелов
• 2. Удаление непечатаемых символов
• 3. Удаление пустых строк
• 4. Исправление регистра букв
• 5. Поиск и удаление дубликатов
• 6. Очистка форматирования
• 7. Преобразование текста в столбцы
• 8. Выделение ошибок
• Часто задаваемые вопросы

1. Устранение лишних пробелов

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

Существует два простых способа удалить лишние пробелы в вашей таблице Excel: функция TRIM и инструмент Найти и Заменить.

Используйте функцию TRIM

Функция TRIM в Excel работает специально для удаления пробелов из ваших данных. Синтаксис формулы прост: TRIM(text). Введите ссылку на ячейку или текст для аргумента.

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

=TRIM(A1)

Функция удаляет пробелы и предоставляет нам чистые данные.

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

=TRIM(“   John    Jacob    Smith   ”)

Вы увидите, что пробелы удалены.

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

Совет: вы можете найти эти продвинутые формулы Excel очень полезными.

Используйте инструмент Найти и Заменить

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

1. Перейдите на вкладку “Главная”, откройте выпадающее меню “Найти и выбрать” в разделе Редактирование и выберите “Заменить”.

1. В появившемся окне Найти и Заменить используйте поле “Найти что”, чтобы ввести один пробел, если вы хотите удалить их все, или точное количество пробелов, которое вы хотите удалить.

1. В поле “Заменить на” введите ничего, если вы хотите удалить все пробелы, или точное количество пробелов, которое вы хотите сохранить.

1. Выберите “Заменить все”, чтобы удалить лишние пробелы, и вы увидите, как ваша таблица обновится немедленно.

2. Удаление непечатаемых символов

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

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

В этом примере у нас есть непечатаемые символы CHAR(10) в начале и в конце нашей формулы в ячейке A1.

Используйте эту формулу, чтобы удалить их:

=CLEAN(A1)

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

=CLEAN(CHAR(10)&"John Jacob Smith"&CHAR(10))

Непечатаемые символы удалены, оставив только текст.

3. Удаление пустых строк

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

1. Перейдите на вкладку “Главная”, откройте выпадающее меню “Найти и выбрать” в разделе Редактирование и выберите “Перейти к специальному”.

1. В появившемся окне выберите “Пустые” и нажмите “ОК”.

1. Вы увидите все пустые ячейки временно выделенными. Прежде чем удалить строки, просмотрите их, чтобы подтвердить, что там нет других данных. Это особенно важно, если вы не можете видеть дополнительные столбцы без прокрутки вправо. Чтобы удалить пустые строки, вернитесь на вкладку “Главная”, откройте выпадающее меню “Удалить” и выберите “Удалить строки листа”.

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

Хорошо знать: узнайте, как объединять текст в Microsoft Excel.

4. Исправление регистра букв

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

LOWER преобразует все буквы в строчные, UPPER преобразует все буквы в заглавные, а PROPER делает первую букву каждого слова в строке текста заглавной и изменяет все остальные буквы на строчные. Кроме того, PROPER делает первую букву после символа, отличного от буквы, заглавной, например, 1a становится 1A.

Синтаксис для каждой из них одинаковый: LOWER(text), UPPER(text), и PROPER(text), где вы можете использовать текст или ссылку на ячейку для аргумента. Давайте рассмотрим примеры.

Чтобы изменить все буквы на строчные в ячейке A1, используйте эту формулу:

=LOWER(A1)

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

=UPPER(A1)

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

=PROPER(A1)

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

=PROPER(A1:A5)

5. Поиск и удаление дубликатов

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

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

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

1. Начните с выбора диапазона ячеек, содержащих дубликаты, затем перейдите на вкладку “Данные” и нажмите “Удалить дубликаты” в группе Инструменты данных.

1. В появившемся окне вы можете сузить данные для проверки и удаления. Сначала отметьте флажки или используйте “Выбрать все” для тех столбцов, которые вы хотите проверить на дубликаты. Затем отметьте флажок, если ваши данные содержат заголовки. Нажмите “ОК”, чтобы продолжить.

1. Если Excel успешно удалит дублирующиеся данные, вы увидите всплывающее сообщение, которое сообщит вам количество удаленных и сохраненных данных.

1. Ваша таблица будет очищена от удаленных дубликатов.

К сведению: узнайте, как взять ваши данные в PDF и превратить их в таблицу Excel.

6. Очистка форматирования

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

Обратите внимание, что если вы настроили условное форматирование в вашей таблице, очистка форматирования, как объяснено здесь, также удалит это форматирование.

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

1. Перейдите на вкладку “Главная”, откройте выпадающее меню “Очистить” и выберите “Очистить форматы”.

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

7. Преобразование текста в столбцы

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

1. Выберите ячейки, которые вы хотите преобразовать, перейдите на вкладку “Данные” и выберите “Текст по столбцам” в группе Инструменты данных.

1. Когда мастер преобразования текста в столбцы откроется, пройдите через несколько шагов, чтобы преобразовать ваши данные в зависимости от их текущего состояния и того, как вы хотите их отобразить. Начните с выбора “Разделенный” или “Фиксированной ширины”. Excel предоставляет вам рекомендацию на основе ваших данных, но вы можете выбрать любой вариант, который наиболее применим. Нажмите “Далее”.

1. В зависимости от того, какой вариант вы выбрали на первом шаге, вы увидите соответствующий второй шаг. Например, если вы выбрали разделенные данные, вы можете выбрать разделитель, а если у вас фиксированные данные, вы можете настроить ширину столбцов. Нажмите “Далее”.

1. Выберите формат данных для столбца, например текст или дата, и введите место назначения для преобразованных данных. Нажмите “Готово”.

1. Вы увидите, как ваш текст преобразуется в столбцы, готовые к работе.

Хорошо знать: узнайте, что использовать: Grammarly или Microsoft Editor.

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

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

1. Выберите весь лист, нажав кнопку “Выбрать все” (треугольник) в верхнем левом углу листа. Если вы предпочитаете проверять только определенные ячейки, выберите их вместо этого.

1. Перейдите на вкладку “Главная”, откройте выпадающее меню “Условное форматирование” в группе Стиль и выберите “Новое правило”.

1. Когда появится окно параметров нового правила форматирования, выберите “Форматировать только ячейки, которые содержат” вверху и “Ошибки” в выпадающем списке внизу.

1. Нажмите кнопку “Формат”, чтобы выбрать форматирование, которое вы хотите применить. Вы можете сделать такие вещи, как залить ячейки цветом, сделать текст жирным или добавить темную границу. Выберите “ОК” после настройки форматирования.

1. Вы увидите предварительный просмотр вашего выбора внизу окна нового правила форматирования. Нажмите “ОК”, чтобы сохранить и применить правило.

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

Часто задаваемые вопросы

Как проверить орфографические ошибки в Excel?

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

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

Как я могу узнать, как исправить ошибку в Excel?

Если вы видите определенную ошибку в вашей таблице, выберите ячейку и перейдите на вкладку “Формулы”. Нажмите “Проверка ошибок” в группе Аудит формул. Вы увидите окно проверки ошибок с краткими сведениями об ошибке. Вы можете выбрать, чтобы получить помощь с ошибкой от Microsoft в Интернете, показать шаги вычисления, игнорировать ошибку или редактировать формулу.

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

Как я могу преобразовать строки в столбцы или наоборот в Excel?

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

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

Кредит изображения: Pixabay. Все скриншоты сделаны Сэнди Уритенхаус.