Первые 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

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

Ultimate Suite for Excel, Merge Tables Wizard, Duplicate Remover, Consolidate Worksheets Wizard, Combine Rows Wizard, Cell Cleaner, Random Generator, Merge Cells, Quick Tools for Excel, Random Sorter, Advanced Find & Replace, Fuzzy Duplicate Finder, Split Names, Split Table Wizard, Workbook Manager

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

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

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

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

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

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

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

×