Перейти к основному контенту
Office

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

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

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

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

Для очистки данных нужно выполнить следующие основные действия:

  1. Импортируйте данные из внешнего источника.

  2. Создайте резервную копию исходных данных в отдельной книге.

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

  4. Выполните сначала задачи, которые не требуют операций со столбцами, такие как проверка орфографии или использование диалогового окна Найти и заменить.

  5. Затем выполните задачи, требующие операций со столбцами. Для работы со столбцами нужно выполнить следующие действия:

    1. Вставьте новый столбец (B) рядом с исходным (A), который требуется очистить.

    2. Добавьте формулу, которая будет преобразовывать данные, вверху нового столбца (B).

    3. Заполните вниз формулу в новом столбце (B). В таблице Excel будет автоматически создан вычисляемый столбец с заполненными вниз значениями.

    4. Выберите новый столбец (B), скопируйте его, а затем вставьте как значения в новый столбец (B).

    5. Удалите исходный столбец (A). При этом новый столбец B станет столбцом A.

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

Дополнительные сведения

Описание

Общие сведения о подключении к данным (импорту)

Описание всех способов импорта внешних данных в Office Excel.

Автоматическое заполнение ячеек листа данными

Инструкции по использованию команды Заполнить.

Создание и форматирование таблиц

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

в таблице Excel с помощью вычисляемых столбцов

Инструкции по созданию таблицы 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.

После импорта данных из внешнего источника в общую задачу можно объединить несколько столбцов в один или разделить один столбец на несколько столбцов. Например, может потребоваться разделить столбец, содержащий полное имя, в имя и фамилию. Кроме того, может потребоваться разделить столбец, содержащий поле адреса, в отдельные столбцы "улица", "город", "регион" и "почтовый индекс". Кроме того, обратное может быть верно. Возможно, потребуется объединить первый столбец имени и фамилии в столбец с полным именем или объединить отдельные столбцы адреса в один столбец. Дополнительные распространенные значения, для которых может потребоваться объединение в один столбец или разделение на несколько столбцов, включают коды продуктов, пути к файлам и IP-адреса.

Дополнительные сведения

Описание

Объединение имени и фамилии

Объединение текста и чисел

Объединение текста с датой или временем

Объединение двух и более столбцов с помощью функции

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

Разделение текста на столбцы с помощью мастера распределения текста по столбцам

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

Разделение текста по столбцам с помощью функций

Инструкции по использованию функций ЛЕВСИМВ, ПСТР, ПРАВСИМВ, ПОИСК и ДЛСТР для разделения столбца имени на несколько столбцов.

Объединение или разделение содержимого ячеек

Инструкции по использованию функции СЦЕПИТЬ, оператора & (амперсанда) и мастера текстов.

Объединение ячеек и разделение объединенных ячеек

Инструкции по использованию команд Объединить ячейки, Объединить по строкам и Объединить и выровнять по центру.

СЦЕПИТЬ

Соединяет несколько текстовых строк в одну строку.

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

Дополнительные сведения

Описание

ТРАНСП

Возвращает вертикальный диапазон ячеек в виде горизонтального и наоборот.

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

Дополнительные сведения

Описание

Поиск значений в списке данных

Часто используемые способы поиска данных с помощью функций поиска.

ПРОСМОТР

Возвращает значение из строки, столбца или массива. Функция ПРОСМОТР имеет две синтаксические формы: векторную и форму массива.

ГПР

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

ВПР

Ищет значение в первом столбце таблицы и возвращает значение в той же строке из другого столбца таблицы.

ИНДЕКС

Возвращает значение или ссылку на значение из таблицы или диапазона. Функция ИНДЕКС имеет две формы: ссылочную и форму массива.

ПОИСКПОЗ

Возвращает относительное положение элемента массива, который соответствует заданному значению указанным образом. Функция ПОИСКПОЗ используется вместо функций типа ПРОСМОТР, если нужна позиция элемента в диапазоне, а не сам элемент.

СМЕЩ

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

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

Примечание: Корпорация Майкрософт не поддерживает сторонние продукты.

Поставщик

Продукт

Add-in Express Ltd.

Максимальный набор для Excel, мастер слияния таблиц, повторяющиеся ремовер, консолидация листов, мастер объединения строк, Очистка ячеек, случайный генератор, объединение ячеек, Экспресс-инструменты для Excel, с дополнительнымhttps://www.ablebits.com/downloads/index.phpпоиском Разделенные имена, мастер разделенных таблиц, диспетчер книг

Add-Ins.com

Duplicate Finder

AddinTools

AddinTools Assist

J-Walk &Associates, Inc.

Power Utility Pak Version 7

WinPure

ListCleaner Lite
ListCleaner Pro
Clean and Match 2007

К началу страницы

Примечание:  Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

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

×