Очистка данных с помощью текстовых функций

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

Изначально нужно очистить данные. Очистка данных включает удаление нежелательных символов из текста. Далее вам необходимо структурировать данные в той форме, которая вам нужна для дальнейшего анализа. Вы можете сделать то же самое -

Поиск необходимых текстовых шаблонов с помощью текстовых функций.
Извлечение значений данных из текста.
Форматирование данных с помощью текстовых функций.
Выполнение операций с данными с помощью текстовых функций.

Удаление ненужных символов из текста

Когда вы импортируете данные из другого приложения, они могут содержать непечатаемые символы и / или лишние пробелы. Лишние места могут быть -

ведущие пробелы и / или
лишние пробелы между словами.

Если вы отсортируете или проанализируете такие данные, вы получите ошибочные результаты.

Рассмотрим следующий пример -

Это необработанные данные, которые вы получили по информации о продукте, включая идентификатор продукта, описание продукта и цену. Символ «|» разделяет поля в каждой строке.

Когда вы импортируете эти данные в лист Excel, они выглядят следующим образом:

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

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

S.No.	Описание функции
1.	CLEAN Удаляет все непечатаемые символы из текста
2.	TRIM Удаляет пробелы из текста

Выберите ячейки C3 - C11.
Введите = ОБРЕЗАТЬ (ЧИСТЫЙ (B3)) и нажмите CTRL + Enter.

Формула заполняется в ячейках C3 - C11.

Результат будет таким, как показано ниже -

Поиск необходимых текстовых шаблонов с помощью текстовых функций

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

S.No.	Описание функции
1.	EXACT Проверяет, идентичны ли два текстовых значения
2.	FIND Находит одно текстовое значение в другом (с учетом регистра)
3.	SEARCH Находит одно текстовое значение в другом (без учета регистра)

Извлечение значений данных из текста

Вам нужно извлечь необходимые данные из текста, чтобы их структурировать. В приведенном выше примере, скажем, вам нужно разместить данные в трех столбцах - ProductID, Product_Description и Price.

Вы можете извлечь данные одним из следующих способов -

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

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

Вы можете использовать Convert Text to Columns Wizard для извлечения значений данных в столбцы Excel, если ваши поля -

Разделен символом, или
Выровнены по столбцам с пробелами между каждым полем.

В приведенном выше примере поля разделены символом «|». Следовательно, вы можете использоватьConvert Text to Columns волшебник.

Выберите данные.
Скопируйте и вставьте значения в одно и то же место. Иначе,Convert Text to Columns принимает на вход функции, а не сами данные.

Выберите данные.
Нажмите на Text to Columns в Data Tools группа под Data Вкладка на ленте.

Step 1 - Мастер преобразования текста в столбцы - появляется шаг 1 из 3.

Выберите с разделителями.
Нажмите кнопку "Далее.

Step 2 - Мастер преобразования текста в столбцы - появляется шаг 2 из 3.

Под Delimiters, Выбрать Other.
В поле рядом с Other, введите символ |
Нажмите Next.

Step 3 - Мастер преобразования текста в столбцы - появляется шаг 3 из 3.

На этом экране вы можете выбрать каждый столбец ваших данных в мастере и установить формат для этого столбца.

За Destination, выберите ячейку D3.
Вы можете нажать Advanced, и установите Decimal Separator и Thousands Separator в Advanced Text Import Settings диалоговое окно, которое появляется.
Нажмите Finish.

Ваши данные, преобразованные в столбцы, отображаются в трех столбцах - D, E и F.

Назовите заголовки столбцов как ProductID, Product_Description и Price.

Извлечение значений данных с помощью текстовых функций

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

Некоторые из текстовых функций, которые полезны для этой цели:

S.No.	Описание функции
1.	LEFT Возвращает крайние левые символы из текстового значения
2.	RIGHT Возвращает крайние правые символы из текстового значения
3.	MID Возвращает определенное количество символов из текстовой строки, начиная с указанной вами позиции.
4.	LEN Возвращает количество символов в текстовой строке.

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

В приведенном выше примере

Все символы остались до первого | укажите имя ProductID.
Все персонажи до второго | назовите Прайс.
Все персонажи, лежащие между первым | и второй | дайте имя Product_Description.
Каждый | есть пробел до и после.

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

Найдите позицию первого | -First | Position
- Вы можете использовать функцию НАЙТИ
Найдите позицию секунды | -Second | Position
- Вы можете снова использовать функцию НАЙТИ
Начиная с (First | Position - 2) Символы текста дают ProductID
- Вы можете использовать функцию LEFT
(First | Position + 2) до (Second | Position - 2) Символы текста дают Product_Description
- Вы можете использовать функцию MID
(Second | Position + 2) для конечных символов текста указать цену
- Вы можете использовать функцию ВПРАВО

Результат будет таким, как показано ниже -

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

Извлечение значений данных с помощью Flash Fill

Использование Excel Flash Fill- еще один способ извлечения значений данных из текста. Однако это работает только тогда, когда Excel может найти закономерность в данных.

Step 1 - Создайте три столбца для ProductID, Product_Description и Price рядом с данными.

Step 2 - Скопируйте и вставьте значения для C3, D3 и E3 из B3.

Step 3 - Выберите ячейку C3 и нажмите Flash Fill в Data Tools группа по Dataтаб. Заполняются все значения ProductID.

Step 4- Повторите указанные выше шаги для Product_Description и Price. Данные заполнены.

Форматирование данных с помощью текстовых функций

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

Functions that format the Text as per your need -

S.No.	Описание функции
1.	LOWER Преобразует текст в нижний регистр

S.No.	Описание функции
1.	UPPER Преобразует текст в верхний регистр
2.	PROPER Делает первую букву в каждом слове текстового значения заглавной.

Functions that convert and/or format the Numbers as Text -

S.No.	Описание функции
1.	DOLLAR Преобразует число в текст в денежном формате $ (доллар).
2.	FIXED Форматирует число как текст с фиксированным количеством десятичных знаков
3.	TEXT Форматирует число и преобразует его в текст

Functions that convert the Text to Numbers -

S.No.	Описание функции
1.	VALUE Преобразует текстовый аргумент в число

Executing Data Operations with the Text Functions

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

Следующие текстовые функции помогут вам в выполнении текстовых операций с вашими данными, содержащими текст -

S.No.	Описание функции
1.	REPLACE Заменяет символы в тексте
2.	SUBSTITUTE Заменяет новый текст на старый текст в текстовой строке
3.	CONCATENATE Объединяет несколько текстовых элементов в один текстовый элемент
4.	CONCAT Объединяет текст из нескольких диапазонов и / или строк, но не предоставляет аргументы разделителя или IgnoreEmpty.
5.	TEXTJOIN Объединяет текст из нескольких диапазонов и / или строк и включает разделитель, который вы указываете между каждым текстовым значением, которое будет объединено. Если разделителем является пустая текстовая строка, эта функция будет эффективно объединять диапазоны.
6.	REPT Повторяет текст заданное количество раз