Неправильно написанные слова, упрямые конечные пробелы, нежелательные префиксы, неправильные случаи и непечатающие символы создают плохое первое впечатление. И это даже не полный список способов получения данных грязное. Сверните рукава. Найдите время для крупной весенней очистки ваших листов с помощью Microsoft Excel.
Формат и тип данных, импортируемых из внешнего источника данных, например базы данных, текстового файла или веб-страницы, не всегда определяются вами. Прежде чем эти данные можно будет анализировать, часто требуется их очистка. К счастью, в Excel есть много функций, помогающих получить данные именно в том формате, который требуется. Иногда это простая задача, для которой достаточно использовать определенную функцию. Например, для исправления слов с ошибками в столбцах, содержащих примечания или описания, можно просто использовать средство проверки орфографии. Или, если вы хотите удалить повторяющиеся строки, можно быстро сделать это с помощью диалогового окна Удалить дубликаты.
В других случаях может потребоваться обработать один или несколько столбцов с помощью формулы, чтобы преобразовать импортированные значения. Например, чтобы удалить пробелы в конце строки, можно создать столбец для очистки данных, применить к нему формулу, заполнить новый столбец, преобразовать формулы нового столбца в значения, а затем удалить исходный столбец.
Для очистки данных нужно выполнить следующие основные действия:
-
Импортируйте данные из внешнего источника.
-
Создайте резервную копию исходных данных в отдельной книге.
-
Убедитесь, что данные имеют формат таблицы: в каждом столбце находятся однотипные данные, все столбцы и строки видимы и в диапазоне нет пустых строк. Для обеспечения наилучших результатов используйте таблицу Excel.
-
Выполните сначала задачи, которые не требуют операций со столбцами, такие как проверка орфографии или использование диалогового окна Найти и заменить.
-
Затем выполните задачи, требующие операций со столбцами. Для работы со столбцами нужно выполнить следующие действия:
-
Вставьте новый столбец (B) рядом с исходным (A), который требуется очистить.
-
Добавьте формулу, которая будет преобразовывать данные, вверху нового столбца (B).
-
Заполните вниз формулу в новом столбце (B). В таблице Excel будет автоматически создан вычисляемый столбец с заполненными вниз значениями.
-
Выберите новый столбец (B), скопируйте его, а затем вставьте как значения в новый столбец (B).
-
Удалите исходный столбец (A). При этом новый столбец B станет столбцом A.
-
Чтобы периодически очищать один и тот же источник данных, рассмотрите возможность записи макроса или написания кода для автоматизации всего процесса. Существует также ряд внешних надстроек, написанных сторонними поставщиками, перечисленных в разделе Сторонние поставщики , которые можно использовать, если у вас нет времени или ресурсов для самостоятельной автоматизации процесса.
|
Дополнительные сведения |
Описание |
|---|---|
|
Инструкции по использованию команды Заполнить. |
|
|
Создание и форматирование таблиц
|
Инструкции по созданию таблицы Excel и добавлению или удалению столбцов и вычисляемых столбцов. |
|
Несколько способов автоматизировать повторяющиеся задачи с помощью макроса. |
Функцию проверки орфографии можно использовать не только для поиска слов с ошибками, но и для поиска значений, используемых несогласованно, например названий товаров или компаний, добавив эти значения в настраиваемый словарь.
|
Дополнительные сведения |
Описание |
|---|---|
|
Инструкции по исправлению слов с ошибками на листе. |
|
|
Инструкции по использованию настраиваемых словарей. |
Повторяющиеся строки — это распространенная проблема, возникающая при импорте данных. Рекомендуется сначала выполнить фильтрацию по уникальным значениям, чтобы просмотреть результаты перед удалением повторяющихся значений.
|
Дополнительные сведения |
Описание |
|---|---|
|
Фильтр уникальных значений или удаление повторяющихся значений |
Описание двух тесно связанных процедур: фильтрации по уникальным строкам и удаления повторяющихся строк. |
Вам может потребоваться удалить общую начальную строку, например метку с последующим двоеточием или пробелом, или суффикс, например фразу в скобках в конце строки, которая устарела или больше не нужна. Это можно сделать путем поиска вхождений такого текста и замены их другим текстом или пустой строкой.
|
Дополнительные сведения |
Описание |
|---|---|
|
Проверка ячейки на наличие в ней текста (без учета регистра)
|
Инструкции по использованию команды Найти и нескольких функций по поиску текста. |
|
Инструкции по использованию команды Заменить и нескольких функций для удаления текста. |
|
|
Инструкции по использованию диалоговых окон Найти и Заменить. |
|
|
НАЙТИ, НАЙТИБ
|
Это функции, которые можно использовать для выполнения различных задач со строками, таких как поиск и замена подстроки, извлечение частей строки или определение длины строки. |
Иногда в тексте используется несогласованный регистр знаков. Используя функции "Регистр", можно преобразовать текст в нижний регистр (например, для адресов электронной почты), в верхний регистр (например, для кодов продуктов) или использовать такой же регистр, как в предложениях (например, для имен или названий книг).
|
Дополнительные сведения |
Описание |
|---|---|
|
Инструкции по использованию трех функций "Регистр". |
|
|
Преобразует все прописные буквы в текстовой строке в строчные. |
|
|
Первая буква в строке текста и все первые буквы, следующие за знаками, отличными от букв, преобразуются в прописные (верхний регистр). Все прочие буквы в тексте преобразуются в строчные (нижний регистр). |
|
|
Преобразует все буквы текста в прописные. |
Иногда текстовые значения содержат начальные, конечные либо последовательные пробелы (значения 32 и 160 кодировки Юникод) или непечатаемые знаки (значения Юникода с 0 по 31, 127, 129, 141, 143, 144 и 157). Наличие таких знаков может иногда приводить к непредсказуемым результатам при сортировке, фильтрации или поиске. Например, во внешнем источнике данных пользователь может сделать опечатку, нечаянно добавив лишний пробел; импортированные из внешних источников текстовые данные также могут содержать непечатаемые знаки внутри текста. Поскольку такие знаки незаметны, неожиданные результаты бывает трудно объяснить. Чтобы удалить эти ненужные знаки, можно использовать сочетание функций СЖПРОБЕЛЫ, ПЕЧСИМВ и ПОДСТАВИТЬ.
|
Дополнительные сведения |
Описание |
|---|---|
|
Возвращает числовой код первого знака в текстовой строке. |
|
|
Удаляет из текста первые 32 непечатаемых знака в 7-битном коде ASCII (значения с 0 по 31). |
|
|
Удаляет из текста знак пробела в 7-битной кодировке ASCII (значение 32). |
|
|
Функцию ПОДСТАВИТЬ можно использовать для замены символов Юникода с более высокими значениями (127, 129, 141, 143, 144, 157 и 160) знаками 7-битной кодировки ASCII, для которых предназначены функции СЖПРОБЕЛЫ и ПЕЧСИМВ. |
Существует две основных проблемы с числами, которые требуют очистки данных: число было случайно импортировано как текст и необходимо изменить отрицательный знак числа в соответствии со стандартом, принятым в организации.
|
Дополнительные сведения |
Описание |
|---|---|
|
Инструкции по преобразованию в числовой формат чисел, которые были отформатированы как текст и сохранены таким образом в ячейках, что может вызывать проблемы при вычислениях или приводить к неправильному порядку сортировки. |
|
|
Преобразует число в текст и добавляет обозначение денежной единицы. |
|
|
Преобразует значение в текст в заданном числовом формате. |
|
|
Округляет число до заданного количества десятичных цифр, форматирует число в десятичном формате с использованием запятой и разделителей тысяч и возвращает результат в виде текста. |
|
|
Преобразует строку текста, отображающую число, в число. |
Так как существует много различных форматов дат и эти форматы можно перепутать с артикулами или другими строками, содержащими косые черты или дефисы, часто бывает необходимо преобразовать и переформатировать дату и время.
|
Дополнительные сведения |
Описание |
|---|---|
|
Изменение системы дат, формата даты и двузначного представления года |
Описание системы дат в Office Excel. |
|
Инструкции по преобразованию значений времени в различные единицы. |
|
|
Инструкции по преобразованию в формат даты дат, которые были отформатированы как текст и сохранены таким образом в ячейках, что может вызывать проблемы при вычислениях или приводить к неправильному порядку сортировки. |
|
|
Возвращает целое число, представляющее определенную дату. Если до ввода этой функции форматом ячейки был "Общий", результат будет отформатирован как дата. |
|
|
Преобразует дату, представленную в виде текста, в порядковый номер. |
|
|
Возвращает десятичное число, представляющее определенное время. Если до ввода этой функции для ячейки был задан формат Общий, результат будет отформатирован как дата. |
|
|
Возвращает время в виде десятичного числа, представленное текстовой строкой. Значение времени — это десятичное число в интервале от 0 до 0,99999999, представляющее время от 0:00:00 до 23:59:59. |
Обычной задачей после импорта данных из внешнего источника данных является слияние двух или нескольких столбцов в один или разделение одного столбца на два или более столбцов. Например, может потребоваться разделить столбец, содержащий полное имя, на имя и фамилию. Кроме того, может потребоваться разделить столбец, содержащий поле адреса, на отдельные столбцы улиц, городов, регионов и почтовых индексов. Обратное также может иметь значение true. Может потребоваться объединить столбец "Имя" и "Фамилия" в столбец "Полное имя" или объединить отдельные столбцы адресов в один столбец. Дополнительные распространенные значения, которые могут потребовать объединения в один столбец или разделения на несколько столбцов, включают коды продуктов, пути к файлам и IP-адреса.
|
Дополнительные сведения |
Описание |
|---|---|
|
Объединение имени и фамилии
|
Типичные примеры объединения значений из нескольких столбцов. |
|
Разделение текста на столбцы с помощью мастера распределения текста по столбцам |
Инструкции по использованию мастера для разделения столбцов с учетом различных часто используемых разделителей. |
|
Инструкции по использованию функций ЛЕВСИМВ, ПСТР, ПРАВСИМВ, ПОИСК и ДЛСТР для разделения столбца имени на несколько столбцов. |
|
|
Инструкции по использованию функции СЦЕПИТЬ, оператора & (амперсанда) и мастера текстов. |
|
|
Инструкции по использованию команд Объединить ячейки, Объединить по строкам и Объединить и выровнять по центру. |
|
|
Соединяет несколько текстовых строк в одну строку. |
В большинстве функций анализа и форматирования в Office Excel предполагается, что данные находятся в одной плоской двухмерной таблице. Иногда может потребоваться сделать строки столбцами, а столбцы — строками. В других случаях данные могут даже не иметь нужной структуры и их может требоваться преобразовать в табличный формат.
|
Дополнительные сведения |
Описание |
|---|---|
|
Возвращает вертикальный диапазон ячеек в виде горизонтального и наоборот. |
|
Иногда администраторы баз данных используют Office Excel для поиска и исправления ошибок соответствия, когда объединяются несколько таблиц. Этот процесс может включать сверку двух таблиц на различных листах, например для того, чтобы просмотреть все записи в обеих таблицах или сравнить таблицы и найти строки, которые не согласуются.
|
Дополнительные сведения |
Описание |
|---|---|
|
Часто используемые способы поиска данных с помощью функций поиска. |
|
|
Возвращает значение из строки, столбца или массива. Функция ПРОСМОТР имеет две синтаксические формы: векторную и форму массива. |
|
|
Ищет значение в первой строке таблицы или массива и возвращает значение, находящееся в том же столбце в заданной строке таблицы или массива. |
|
|
Ищет значение в первом столбце таблицы и возвращает значение в той же строке из другого столбца таблицы. |
|
|
Возвращает значение или ссылку на значение из таблицы или диапазона. Функция ИНДЕКС имеет две формы: ссылочную и форму массива. |
|
|
Возвращает относительное положение элемента массива, который соответствует заданному значению указанным образом. Функция ПОИСКПОЗ используется вместо функций типа ПРОСМОТР, если нужна позиция элемента в диапазоне, а не сам элемент. |
|
|
Данная функция возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов. Возвращаемая ссылка может быть отдельной ячейкой или диапазоном ячеек. Можно задавать количество возвращаемых строк и столбцов. |
Ниже приведен неполный список сторонних поставщиков, продукты которых используются для очистки данных различными способами.
Примечание: Корпорация Майкрософт не поддерживает сторонние продукты.
|
Поставщик |
Продукт |
|---|---|
|
Add-in Express Ltd. |
|
|
Add-Ins.com |
|
|
AddinTools |
|
|
WinPure |
