Десять найкращих способів очищення даних

Примітка.: Ми хочемо надавати найновіший вміст довідки рідною мовою користувачів якомога швидше. Цю сторінку перекладено за допомогою засобу автоматичного перекладу, тому вона може містити смислові, синтаксичні або граматичні помилки. Ми вважаємо, що цей вміст стане вам у пригоді. Повідомте нас, чи була інформація корисною, унизу цієї сторінки. Для зручності цю статтю можна переглянути англійською мовою.

Слова з помилками, впертий пробіли наприкінці, небажаних префікси, випадки неправильного і недруковані символи зробити погано перше враження. І це ще не повний список способів дані можна отримати dirty. Прокрутити вгору до рукавами. Настав час для деяких основних spring-cleaning аркушів в програмі Microsoft Excel.

Ви не завжди мають контроль над формат і тип даних, які потрібно імпортувати з зовнішнього джерела даних, таких як бази даних, текстовий файл або веб-сторінки. Перш ніж можна аналізувати її дані, часто потрібно очистити його. На щастя, програма Excel містить багато можливостей, щоб допомогти вам отримати дані в точно потрібний формат. Іноді завдання – це простий і є функція, яка робить роботу за вас. Наприклад, можна легко використовувати засіб перевірки орфографії для очищення слова з помилками в стовпці, які містять примітки або описи. Або, якщо потрібно видалити повторювані рядки, ви можете швидко зробити це за допомогою діалогового вікна Видалення повторень .

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

Основні кроки для збирання даних доступні.

  1. Імпорт даних із зовнішнього джерела даних.

  2. Створення резервної копії вихідні дані з окремої книги.

  3. Переконайтеся, що дані в табличному форматі рядків і стовпців із: однакові дані в кожному стовпці, всі стовпці та рядки видимі та немає пустих рядків у діапазоні. Щоб отримати найкращі результати використовуйте таблиці Excel.

  4. Виконуйте завдання, які не потребують стовпець обробки по-перше, зокрема перевірку орфографії або за допомогою діалогового вікна Пошук і заміна .

  5. Потім виконайте завдання, які потребують обробки стовпця. Які загальні кроки для роботи з стовпця.

    1. Вставити новий стовпець (B) поряд із вихідного стовпця (А), що потребує очищення.

    2. Додавання формули, яка перетворить даних у верхній частині новий стовпець (B).

    3. Щоб заповнити формулу в новий стовпець (B). У таблиці Excel заповнений значеннями автоматично створюється обчислюваний стовпець.

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

    5. Видалення вихідного стовпця (А), яка перетворює новий стовпець від B до відповіді.

Періодично очищення одного джерела даних, розгляньте можливість записування макросу або написання коду для автоматизації всього процесу. Існує ряд зовнішніх надбудов, написаний сторонніх постачальників, наведені в розділі програми сторонніх постачальників , ви можете скористатися програмою якщо немає часу або ресурсів для автоматизації процесу власні.

Докладні відомості

Опис

Загальні відомості про підключення даних (імпорт)

Описано всі шляхи імпорт зовнішніх даних у програмі Excel.

Автоматичне вставлення даних у клітинки аркуша

Показано, як за допомогою команди заповнити .

Створення або видалення таблиці Excel

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

використання обчислюваних стовпців у таблиці Excel

Показати, як створити таблицю Excel і додавання або видалення стовпців або обчислюваних стовпців.

Створення макросу

Показано кілька способів, щоб автоматизувати виконання повторюваних завдань за допомогою макросу.

Можна використовувати засіб перевірки орфографії не лише слова з помилкою, але знайти значення, які не використовуються послідовно, наприклад, назви товарів або компаній, якщо додати їх до настроюваного словника.

Докладні відомості

Опис

Перевірка орфографії та граматики

Показано, як виправити неправильно написане слово на аркуші.

Додавання слів до засобу перевірки орфографії за допомогою настроюваних словників

Пояснюється, як за допомогою настроюваних словників.

Повторювані рядки, поширені проблеми під час імпорту даних. Варто відфільтрувати унікальні значення спочатку, щоб переконатися, що результати не відповідає вашим потребам перед видаленням повторюваних значень.

Докладні відомості

Опис

Фільтрування за унікальними значеннями або вилучення повторюваних значень

Відображаються дві споріднені процедури: як відфільтрувати унікальні рядки та як видалити повторювані рядки.

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

Докладні відомості

Опис

Перевірка наявності в клітинці тексту (без урахування регістру)

перевіряти, якщо клітинка містить текст (з урахуванням регістра)

Показати, як знайти текст за допомогою команди знайти » а також кілька функцій.

Видалення символів із тексту

Показано, як використовувати команду замінити а також кілька функцій для видалення тексту.

Пошук і заміна тексту й чисел на аркуші

Показати, як використовувати діалогові вікна Пошук і заміна .

FIND, FINDB

SEARCH, SEARCHB

REPLACE, REPLACEB

SUBSTITUTE

ЗЛІВА, LEFTB

справа, RIGHTB

LEN і LENB

MID, MIDB

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

Іноді текст постачається у змішаному пакет, особливо якщо це стосується регістр тексту. Використовувати одну або кілька функцій три сценарію виконання, ви можете перетворити текст малими буквами, наприклад адреси електронної пошти, великі букви, наприклад коди продуктів або великої букви, наприклад імена або книги.

Докладні відомості

Опис

Змінення регістра тексту

Показано, як використовувати три сценарію виконання функції.

LOWER

Перетворює всі букви в текстовому рядку на малі букви.

PROPER

Перетворює першу букву в текстовому рядку та всі інші букви, що стоять після небуквених символів, на великі букви. Решту букв перетворює на малі.

UPPER

Перетворює текст на великі букви.

Іноді текстові значення, які містять каталог, кінці, або кілька вбудованих пробіли (символЮнікод встановлено значення 32 і 160) або недруковані символи (символ Юнікод встановлено значення 0 до 31, 127, 129, 141, 143, 144 та 157). Наведені нижче символи може іноді призвести до неочікуваних результатів під час сортування, фільтрування або пошук. Наприклад, в зовнішньому джерелі даних, користувачів може зробити друкарських помилок, ненавмисно додаючи зайві пробіли або імпортованого тексту даних із зовнішніх джерел може містити недруковані символи, вбудовані в тексті. Оскільки ці символи не легко помітити, неочікуваних результатів може бути складно зрозуміти. Для видалення цих небажаних символів, скористайтеся комбінацією функцій TRIM, CLEAN і SUBSTITUTE.

Докладні відомості

Опис

Показано, як видалити всі пробіли та недруковані символи з набору символів Юнікод.

КОД

Повертає числовий код першого символу в текстовому рядку.

ОЧИЩЕННЯ

Видаляє з тексту першого 32 недруковані символи в 7-розрядного коду ASCII (зі значеннями від 0 до 31).

TRIM

Символ пробілу 7-розрядного ASCII (зі значенням 32) видаляє з тексту.

SUBSTITUTE

За допомогою функції SUBSTITUTE заміни символів Юнікод вище значення (значення 127, 129, 141, 143, 144, 157 і 160) з 7-розрядні символи ASCII, для яких були призначені функції TRIM і CLEAN.

Існують дві основні проблеми з числами, які може знадобитися очищення даних: число було випадково імпортовані як текст і знаком «мінус», необхідно буде змінено на стандартне для вашої організації.

Докладні відомості

Опис

Перетворення чисел із текстового формату на числовий

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

DOLLAR

Перетворює число на текст і застосовується символу грошової одиниці.

ТЕКСТ

Перетворює значення на текст у вказаному числовому форматі.

ФІКСОВАНА

Округлює число до вказаної кількості десяткових знаків, форматує число в десятковому форматі за допомогою коми та пробілів і повертає результат у вигляді тексту.

ЗНАЧЕННЯ

Перетворює текстовий рядок, що представляє число, на число.

Оскільки є багато різних форматів дати, і тому, що наведені нижче формати можуть плутати нумерований частини коди або іншими рядками, які містять знаки скісна риска або дефіси, дат і часу часто потрібно перетворення й переформатування.

Докладні відомості

Опис

Змінення системи дат, форматування або двома цифрами року

Описано, як працює система дат у програмі Excel.

Перетворення значень часу

Показано, як перетворити між різними часових одиниць.

Перетворення дат, збережених у текстовому форматі, на формат дати

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

ДАТИ

Повертає порядковий номер, який відповідає вказаної дати. Якщо форматом клітинки був загальний, перш ніж було введено функцію, результат буде відформатовано як дату.

DATEVALUE

Перетворює дату представлені тексту на числове значення.

ЧАС

Повертає десяткове значення конкретного часу. Якщо перед застосуванням функції формат клітинки було задано як Загальний, результат буде відформатовано як дату.

TIMEVALUE

Повертає десяткове значення часу, представлені у текстовому рядку. Десяткове число в інтервалі від 0 (нуль) до 0,99999999, яке відповідає часу від 0:00:00 (12:00:00 AM) до 23:59:59 (23:59:59: 00).

Поширені завдання після імпортування даних із зовнішнього джерела даних є об'єднання двох або більше стовпців в одному або розділення одного стовпця на два або більше стовпців. Наприклад, можна розділити на стовпець, який містить повне ім'я на ім'я та прізвище. Або, якщо потрібно розділити стовпець, який містить поле адреси в окремих вулиці, місто, регіон і поштовий індекс стовпців. У зворотному порядку може бути значення true. Ви можете об'єднати першого та останнього ім'я стовпця в повне ім'я стовпця, або поєднання окремих адресу стовпці в один стовпець. Типових значень, які може вимагати об'єднання в один стовпець або розділення на окремі стовпці містять коди продуктів, шлях до файлу та Інтернет-протоколу (IP) адрес.

Докладні відомості

Опис

Поєднання ім'я та прізвище

об'єднання тексту й чисел

поєднання тексту з дати або часу

об'єднання двох або кількох стовпців за допомогою функції

Відображення Типові приклади об'єднання значень із двох або більше стовпців.

Розділення тексту на різні стовпці за допомогою майстра текстів

Показано, як використовувати цей майстер розділення стовпців за різноманітними типовими розділювачами.

Розділення тексту на кілька стовпців за допомогою функцій

Показано, як у лівій ЧАСТИНІ екрана за допомогою функції MID, RIGHT, SEARCH і LEN для розділення імен стовпців на два або більше стовпців.

Об'єднання та розділення вмісту клітинок

Показано, як за допомогою функції CONCATENATE & (амперсанд) Оператор і стовпці майстра Перетворення тексту на.

Об’єднання та розділення об’єднаних клітинок

Показано, як за допомогою команди Об'єднати клітинки, Об'єднання пота об'єднати та розташувати в центрі .

CONCATENATE

Об'єднує два або більше текстових рядків в один.

Більшість аналізу та функції форматування у програмі Excel і вважати, що дані існує, плоскої двовимірної таблиці. Іноді може знадобитися зробити рядки стають стовпців і стовпців стають рядків. В інших випадках дані не структуровано навіть в табличному форматі, і вам потрібно перетворити дані з в табличному в табличному форматі.

Докладні відомості

Опис

TRANSPOSE

Повертає вертикальний діапазон клітинок як горизонтальний діапазон або навпаки.

Іноді адміністратори баз даних за допомогою Office Excel для пошуку та виправлення помилок у відповідних під час двох або кількох таблиць. Це може включати узгодження двох таблиць із різних аркушів, наприклад, щоб переглянути всі записи в обох таблицях або для порівняння таблиць і знайти рядки, які не збігаються.

Докладні відомості

Опис

Пошук значень у списку даних

Відображає поширені способи пошуку даних за допомогою функції підстановки.

ПІДСТАНОВКИ

Повертає значення з одного рядка або одного стовпця в діапазоні або в масиві. Функція LOOKUP має дві синтаксичні форми: Векторна форма та форма масиву.

HLOOKUP

Шукає значення в першому рядку таблиці або масив значень і повертає значення в тому самому стовпці з рядка, указаної в таблиці або масиву.

VLOOKUP

Шукає значення в першому стовпці таблиці-масиву та повертає значення в одному рядку від іншого стовпця таблиці-масиву.

ІНДЕКС

Повертає значення або посилання на значення з таблиці або діапазону. Існують дві форми функція INDEX: форма посилання та форму масиву.

MATCH

Ця функція повертає відносне положення елемента масиву, яка відповідає заданого значення в певному порядку. Використовуйте MATCH замість однієї з функцій LOOKUP, коли потрібно розташування елементів у діапазоні, а не сам елемент.

ЗСУВ

Повертає посилання на діапазон, віддалений від клітинки або діапазону клітинок на вказану кількість рядків і стовпців. Посилання, що повертається, може бути однією клітинкою або діапазоном клітинок. Кількість рядків і стовпців, які повертаються, можна вказати.

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×