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

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

Неправильно написані слова, непіддатливі кінцеві пробіли, небажані префікси, неправильні регістри та недруковані символи справляють погане перше враження. І це далеко не повний перелік того, що може трапитися з вашими даними. Приготуйтеся до роботи. Настав час для генерального прибирання на аркушах Microsoft Office 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

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

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

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

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

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

Опис

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

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

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

Описано використання настроюваних словників.

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

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

Опис

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

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

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

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

Опис

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

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

Показано використання команди Знайти та деяких інших функцій для пошуку тексту.

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

Показано використання команди Замінити та деяких інших функцій для видалення тексту.

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

Пошук і заміна

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

FIND, FINDB

SEARCH, SEARCHB

REPLACE, REPLACEB

SUBSTITUTE

LEFT, LEFTB

RIGHT, RIGHTB

LEN, LENB

MID, MIDB

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

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

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

Опис

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

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

LOWER

Перетворює всі знаки в текстовому рядку з верхнього регістра на нижній.

PROPER

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

UPPER

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

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

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

Опис

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

CODE

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

CLEAN

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

TRIM

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

SUBSTITUTE

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

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

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

Опис

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

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

DOLLAR

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

TEXT

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

FIXED

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

VALUE

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

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

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

Опис

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

Описано роботу системи дат у програмі Office Excel.

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

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

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

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

DATE

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

DATEVALUE

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

TIME

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

TIMEVALUE

Повертає числове подання часу, представленого текстовим рядком. Числове подання часу — це десятковий дріб в інтервалі від 0 (нуля) до 0,99999999, який представляє час від 0:00:00 (12:00:00 AM) до 23:59:59 (11:59:59 P.M.).

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

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

Опис

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

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

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

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

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

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

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

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

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

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

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

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

Показано використання команд Об’єднати клітинки, Об’єднати за рядками й Об’єднати та розмістити в центрі.

CONCATENATE

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

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

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

Опис

TRANSPOSE

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

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

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

Опис

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

Показано типові способи перегляду даних для пошуку значень.

LOOKUP

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

HLOOKUP

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

VLOOKUP

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

INDEX

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

MATCH

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

OFFSET

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

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

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

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

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

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

×