Данные, которые вы получаете из разных источников, не всегда находятся в форме, готовой для анализа. В этой главе вы поймете, как подготовить ваши данные в форме текста для анализа.
Изначально вам нужно очистить данные. Очистка данных включает в себя удаление нежелательных символов из текста. Далее вам необходимо структурировать данные в той форме, которая вам необходима для дальнейшего анализа. Вы можете сделать то же самое путем —
- Поиск необходимых текстовых шаблонов с помощью текстовых функций.
- Извлечение значений данных из текста.
- Форматирование данных с помощью текстовых функций.
- Выполнение операций с данными с текстовыми функциями.
Удаление нежелательных символов из текста
Когда вы импортируете данные из другого приложения, они могут содержать непечатаемые символы и / или лишние пробелы. Лишние места могут быть —
- ведущие пробелы и / или
- лишние пробелы между словами.
Если вы сортируете или анализируете такие данные, вы получите ошибочные результаты.
Рассмотрим следующий пример —
Это необработанные данные, которые вы получили в отношении информации о продукте, включая идентификатор продукта, описание продукта и цену. Символ «|» разделяет поле в каждой строке.
Когда вы импортируете эти данные в таблицу Excel, она выглядит следующим образом:
Как вы заметили, все данные находятся в одном столбце. Вам необходимо структурировать эти данные для выполнения анализа данных. Однако изначально вам необходимо очистить данные.
Вам необходимо удалить все непечатаемые символы и лишние пробелы, которые могут присутствовать в данных. Для этого вы можете использовать функцию CLEAN и функцию TRIM.
| S.No. | Описание функции |
|---|---|
| 1. |
ЧИСТАЯ Удаляет все непечатаемые символы из текста |
| 2. |
ОТДЕЛКА Удаляет пробелы из текста |
ЧИСТАЯ
Удаляет все непечатаемые символы из текста
ОТДЕЛКА
Удаляет пробелы из текста
- Выберите ячейки C3 — C11.
- Введите = TRIM (CLEAN (B3)), а затем нажмите клавиши CTRL + Enter.
Формула заполняется в ячейках C3 — C11.
Результат будет таким, как показано ниже —
Поиск необходимых текстовых шаблонов с помощью текстовых функций
Чтобы структурировать ваши данные, вам, возможно, придется выполнить определенное сопоставление с текстовым шаблоном, на основе которого вы можете извлечь значения данных. Некоторые из текстовых функций, которые полезны для этой цели:
| S.No. | Описание функции |
|---|---|
| 1. |
EXACT Проверяет, идентичны ли два текстовых значения |
| 2. |
НАХОДИТЬ Находит одно текстовое значение внутри другого (с учетом регистра) |
| 3. |
ПОИСК Находит одно текстовое значение внутри другого (без учета регистра) |
EXACT
Проверяет, идентичны ли два текстовых значения
НАХОДИТЬ
Находит одно текстовое значение внутри другого (с учетом регистра)
ПОИСК
Находит одно текстовое значение внутри другого (без учета регистра)
Извлечение значений данных из текста
Вам нужно извлечь необходимые данные из текста, чтобы структурировать их. Например, в приведенном выше примере вам необходимо разместить данные в трех столбцах — ProductID, Product_Description и Price.
Вы можете извлечь данные одним из следующих способов —
- Мастер извлечения значений данных с помощью мастера преобразования текста в столбцы
- Извлечение значений данных с помощью текстовых функций
- Извлечение значений данных с помощью Flash Fill
Мастер извлечения значений данных с помощью мастера преобразования текста в столбцы
Вы можете использовать Мастер преобразования текста в столбцы, чтобы извлечь значения данных в столбцы Excel, если ваши поля —
- Разграничены символом или
- Выравнивается по столбцам с пробелами между каждым полем.
В приведенном выше примере поля разделены символом «|». Следовательно, вы можете использовать мастер преобразования текста в столбцы .
-
Выберите данные.
-
Скопируйте и вставьте значения в одном месте. В противном случае для преобразования текста в столбцы в качестве входных данных используются функции, а не сами данные.
Выберите данные.
Скопируйте и вставьте значения в одном месте. В противном случае для преобразования текста в столбцы в качестве входных данных используются функции, а не сами данные.
-
Выберите данные.
-
Нажмите « Текст в столбцы» в группе « Инструменты данных» на вкладке « Данные» на ленте.
Выберите данные.
Нажмите « Текст в столбцы» в группе « Инструменты данных» на вкладке « Данные» на ленте.
Шаг 1 — Мастер преобразования текста в столбцы — появляется шаг 1 из 3.
- Выберите с разделителями.
- Нажмите кнопку «Далее.
Шаг 2 — Мастер преобразования текста в столбцы — появляется шаг 2 из 3.
-
Под разделителями выберите « Другое» .
-
В поле рядом с Other введите символ |
-
Нажмите Далее .
Под разделителями выберите « Другое» .
В поле рядом с Other введите символ |
Нажмите Далее .
Шаг 3 — Мастер преобразования текста в столбцы — появляется шаг 3 из 3.
На этом экране вы можете выбрать каждый столбец ваших данных в мастере и установить формат для этого столбца.
-
В качестве пункта назначения выберите ячейку D3.
-
Вы можете нажать « Дополнительно» и установить десятичный разделитель и разделитель тысяч в открывшемся диалоговом окне « Дополнительные параметры импорта текста» .
-
Нажмите Готово .
В качестве пункта назначения выберите ячейку D3.
Вы можете нажать « Дополнительно» и установить десятичный разделитель и разделитель тысяч в открывшемся диалоговом окне « Дополнительные параметры импорта текста» .
Нажмите Готово .
Ваши данные, которые преобразуются в столбцы, отображаются в трех столбцах — D, E и F.
- Назовите заголовки столбцов как ProductID, Product_Description и Price.
Извлечение значений данных с помощью текстовых функций
Предположим, что поля в ваших данных не разделены символом и не выровнены в столбцах с пробелами между каждым полем. Вы можете использовать текстовые функции для извлечения значений данных. Даже если поля разделены, вы все равно можете использовать текстовые функции для извлечения данных.
Некоторые из текстовых функций, которые полезны для этой цели:
| S.No. | Описание функции |
|---|---|
| 1. |
ОСТАВИЛ Возвращает крайние левые символы из текстового значения |
| 2. |
ПРАВО Возвращает самые правые символы из текстового значения |
| 3. |
MID Возвращает определенное количество символов из текстовой строки, начиная с указанной вами позиции |
| 4. |
LEN Возвращает количество символов в текстовой строке |
ОСТАВИЛ
Возвращает крайние левые символы из текстового значения
ПРАВО
Возвращает самые правые символы из текстового значения
MID
Возвращает определенное количество символов из текстовой строки, начиная с указанной вами позиции
LEN
Возвращает количество символов в текстовой строке
Вы также можете объединить две или более из этих текстовых функций в соответствии с имеющимися у вас данными, чтобы извлечь необходимые значения данных. Например, используя комбинацию функций LEFT, RIGHT и VALUE или комбинацию функций FIND, LEFT, LEN и MID.
В приведенном выше примере
-
Все персонажи, оставленные до первого | дать название ProductID.
-
Все персонажи справа на второй | дайте имя цена.
-
Все персонажи, которые лежат между первым | и второй | дать название Product_Description.
-
Каждый | есть место до и после.
Все персонажи, оставленные до первого | дать название ProductID.
Все персонажи справа на второй | дайте имя цена.
Все персонажи, которые лежат между первым | и второй | дать название Product_Description.
Каждый | есть место до и после.
Наблюдая эту информацию, вы можете извлечь значения данных с помощью следующих шагов —
-
Найти позицию первого | — Первый | Позиция
-
Вы можете использовать функцию НАЙТИ
-
-
Найти позицию второго | — Второй | Позиция
-
Вы можете снова использовать функцию НАЙТИ
-
-
Начало ( Первая | Позиция — 2) Символы текста дают ProductID
-
Вы можете использовать левую функцию
-
-
( First | Position + 2) to ( Second | Position — 2) Символы текста дают описание продукта
-
Вы можете использовать функцию MID
-
-
( Вторая | Позиция + 2), чтобы конечные символы текста дают цену
-
Вы можете использовать ПРАВУЮ функцию
-
Найти позицию первого | — Первый | Позиция
Вы можете использовать функцию НАЙТИ
Найти позицию второго | — Второй | Позиция
Вы можете снова использовать функцию НАЙТИ
Начало ( Первая | Позиция — 2) Символы текста дают ProductID
Вы можете использовать левую функцию
( First | Position + 2) to ( Second | Position — 2) Символы текста дают описание продукта
Вы можете использовать функцию MID
( Вторая | Позиция + 2), чтобы конечные символы текста дают цену
Вы можете использовать ПРАВУЮ функцию
Результат будет таким, как показано ниже —
Вы можете заметить, что значения в столбце цены являются текстовыми значениями. Чтобы выполнить вычисления для этих значений, вы должны отформатировать соответствующие ячейки. Вы можете посмотреть в разделе ниже, чтобы понять форматирование текста.
Извлечение значений данных с помощью Flash Fill
Использование Excel Flash Fill — это еще один способ извлечения значений данных из текста. Однако это работает только тогда, когда Excel может найти шаблон в данных.
Шаг 1 — Создайте три столбца для ProductID, Product_Description и Price рядом с данными.
Шаг 2 — Скопируйте и вставьте значения для C3, D3 и E3 из B3.
Шаг 3 — Выберите ячейку C3 и нажмите « Flash Fill» в группе « Инструменты данных » на вкладке « Данные ». Все значения для ProductID будут заполнены.
Шаг 4 — Повторите приведенные выше шаги для Product_Description и Price. Данные заполнены.
Форматирование данных с помощью текстовых функций
В Excel есть несколько встроенных текстовых функций, которые вы можете использовать для форматирования данных, содержащих текст. К ним относятся —
Функции, которые форматируют текст в соответствии с вашими потребностями —
| S.No. | Описание функции |
|---|---|
| 1. |
НИЖНИЙ Преобразует текст в нижний регистр |
НИЖНИЙ
Преобразует текст в нижний регистр
| S.No. | Описание функции |
|---|---|
| 1. |
ВЕРХНИЙ Преобразует текст в верхний регистр |
| 2. |
ПРАВИЛЬНОЕ Прописывает первую букву в каждом слове текстового значения |
ВЕРХНИЙ
Преобразует текст в верхний регистр
ПРАВИЛЬНОЕ
Прописывает первую букву в каждом слове текстового значения
Функции, которые преобразуют и / или форматируют числа как текст —
| S.No. | Описание функции |
|---|---|
| 1. |
ДОЛЛАР Преобразует число в текст, используя валютный формат $ (доллар) |
| 2. |
ФИКСИРОВАННЫЙ Форматирует число как текст с фиксированным числом десятичных знаков |
| 3. |
ТЕКСТ Форматирует число и преобразует его в текст |
ДОЛЛАР
Преобразует число в текст, используя валютный формат $ (доллар)
ФИКСИРОВАННЫЙ
Форматирует число как текст с фиксированным числом десятичных знаков
ТЕКСТ
Форматирует число и преобразует его в текст
Функции, которые преобразуют текст в числа —
| S.No. | Описание функции |
|---|---|
| 1. |
ЗНАЧЕНИЕ Преобразует текстовый аргумент в число |
ЗНАЧЕНИЕ
Преобразует текстовый аргумент в число
Выполнение операций с данными с текстовыми функциями
Возможно, вам придется выполнить определенные текстовые операции с вашими данными. Например, если идентификаторы входа в систему для сотрудников заменены на новый формат в организации, основанный на изменении формата, может потребоваться замена текста.
Следующие текстовые функции помогут вам в выполнении текстовых операций с вашими данными, содержащими текст —
ЗАМЕНА
Заменяет символы в тексте
ЗАМЕНА
Заменяет новый текст на старый текст в текстовой строке
CONCATENATE
Объединяет несколько текстовых элементов в один текстовый элемент
CONCAT
Объединяет текст из нескольких диапазонов и / или строк, но не содержит аргументов разделителя или IgnoreEmpty.
TEXTJOIN
Объединяет текст из нескольких диапазонов и / или строк и содержит разделитель, который вы указываете между каждым текстовым значением, которое будет объединено. Если разделитель представляет собой пустую текстовую строку, эта функция будет эффективно объединять диапазоны.
ПОВТОР
Повторяет текст заданное количество раз