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

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

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

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

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

Основні кроки з очищення даних полягають у наступному:

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

  2. Створіть резервну копію вихідних даних у окремій книзі.

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

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

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

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

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

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

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

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

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

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

Опис

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

У цій статті описано всі способи імпортування зовнішніх даних у програму Office Excel.

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

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

Створення та форматування таблиць

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

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

У цій статті описано, як створити таблицю Excel і додати або видалити стовпці або обчислювані стовпці.

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

У цій програмі наведено кілька способів автоматизації повторюваних завдань за допомогою макросу.

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

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

Опис

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

У цій статті описано, як можна виправляти слова з помилками на аркуші.

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

У цій статті пояснюється, як використовувати додаткові словники.

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

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

Опис

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

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

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

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

Опис

Перевірка наявності тексту (

якщо в ній міститься текст), якщо в ній єтекст (чутливий до регістру).

У цій статті описано, як використовувати команду Find , а також кілька функцій, щоб знаходити текст.

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

У цій статті описано, як використовувати команду замінити та кілька функцій, щоб видалити текст.

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

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

знайти, FINDB

Search, SEARCHB

замінити, REPLACEB

замінити

вліво, ledtb

Right, RIGHTB

LEN, LENB MID, MIDB

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

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

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

Опис

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

У цій статті описано, як використовувати три функції Case.

LOWER

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

НАЛЕЖНОГО

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

UPPER

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

Інколи текстові значення містять символи інтерліньяжу, заднього або кількох вбудованих символів (_ z0z_ символи, які встановлюють значення 32 і 160), або недруковані символи (набір символів Юнікоду – від 0 до 31, 127, 129, 141, 143, 144 та 157). Ці символи можуть інколи спричиняти несподівані результати під час сортування, фільтрування або пошуку. Наприклад, у зовнішньому джерелі даних користувачі можуть робити друкарські помилки, ненавмисно додаючи зайві пробіли, або імпортовані текстові дані з зовнішніх джерел можуть містити недруковані символи, вбудовані в текст. Оскільки ці символи не легко помітити, несподівані результати можуть бути важкими для розуміння. Щоб видалити ці непотрібні символи, можна скористатися комбінацією функцій обробки, очищення та ЗАМІНИ.

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

Опис

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

КОД

Ця функція повертає числовий код першого символу в текстовому рядку.

Чистий

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

TRIM

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

SUBSTITUTE

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

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

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

Опис

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

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

Долар

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

TEXT

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

ВИПРАВЛЕНО

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

VALUE

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

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

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

Опис

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

У цій статті описано, як система дат працює в Office Excel.

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

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

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

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

DATE

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

DATEVALUE

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

TIME

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

TIMEVALUE

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

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

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

Опис

Поєднання перших і останніх імен

Комбінуйте текст і числа

, Комбінуйтетекст із датою або часом

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

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

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

У цій статті описано, як використовувати цей майстер для розділення стовпців на основі різних поширених роздільників.

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

У цій статті описано, як використовувати функції LEFT, MID, RIGHT, SEARCH і LEN, щоб розділити стовпець ім'я на два або кілька стовпців.

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

У цій статті описано, як використовувати функцію "CONCATENATE", "_ Amp_ (амперсанд)" і "перетворити текст на стовпці".

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

У цій статті описано, як використовувати об'єднання "злиття", " об'єднатив" та "об'єднати" та "Вирівняти ".

CONCATENATE

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

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

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

Опис

TRANSPOSE

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

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

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

Опис

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

Загальні способи пошуку даних за допомогою функцій підстановки.

ПІДСТАНОВКИ

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

HLOOKUP

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

VLOOKUP

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

INDEX

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

MATCH

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

OFFSET

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

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

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

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

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

×