Неправильні слова, уперті пробіли в кінці, небажані префікси, неправильні випадки та недруковані символи справили погане перше враження. І це навіть не повний список способів, якими дані можуть забруднитися. Закочуйте рукава. Настав час для деяких великих весняних очищення аркушів за допомогою Microsoft Excel.
Ви не завжди маєте контроль над форматом і типом даних, які імпортуються із зовнішнього джерела даних, наприклад бази даних, текстового файлу або веб-сторінки. Перш ніж аналізувати дані, часто потрібно очистити їх. На щастя, у програмі Excel є багато функцій, які допомагають отримати дані в потрібному форматі. Іноді завдання просте, і є певна функція, яка виконує цю роботу за вас. Наприклад, за допомогою засобу перевірки орфографії можна легко очистити слова з помилками в стовпцях, які містять примітки або описи. Або, якщо потрібно видалити повторювані рядки, це можна швидко зробити за допомогою діалогового вікна Видалення повторень .
В інший час може знадобитися виконати роботу з одним або кількома стовпцями за допомогою формули, щоб перетворити імпортовані значення на нові значення. Наприклад, якщо потрібно видалити пробіли в кінці, можна створити новий стовпець, щоб очистити дані за допомогою формули, заповнивши новий стовпець, перетворивши формули цього нового стовпця на значення, а потім видаливши вихідний стовпець.
Нижче наведено основні кроки з очищення даних.
-
Імпорт даних із зовнішнього джерела даних.
-
Створіть резервну копію вихідних даних в окремій книзі.
-
Переконайтеся, що дані мають табличний формат рядків і стовпців: схожі дані в кожному стовпці, усі видимі стовпці та рядки, а також відсутні пусті рядки в діапазоні. Щоб отримати найкращі результати, використовуйте таблицю Excel.
-
Виконувати завдання, які спочатку не вимагають керування стовпцями, наприклад перевірка орфографії або використання діалогового вікна Пошук і заміна .
-
Потім виконайте завдання, для яких потрібна обробка стовпців. Щоб керувати стовпцем, виконайте такі дії:
-
Вставте новий стовпець (B) поруч із вихідним стовпцем (A), який потребує очищення.
-
Додайте формулу, яка перетворить дані у верхній частині нового стовпця (B).
-
Заповніть формулу в новому стовпці (B). У таблиці Excel автоматично створюється обчислюваний стовпець зі значеннями, заповненими вниз.
-
Виділіть новий стовпець (B), скопіюйте його та вставте як значення в новий стовпець (B).
-
Видаліть вихідний стовпець (A), який перетворює новий стовпець із B на A.
-
Щоб періодично очищувати те саме джерело даних, радимо записати макрос або код запису, щоб автоматизувати весь процес. Є також ряд зовнішніх надбудов, написаних сторонніми постачальниками, перелічених у розділі Сторонні постачальники , які можна використовувати, якщо у вас немає часу або ресурсів, щоб автоматизувати процес самостійно.
|
Додаткові відомості |
Опис |
|---|---|
|
Показано, як скористатися командою Заповнити . |
|
|
Створення та форматування таблиць Змінення розміру таблиці за допомогою додавання або видалення рядків і стовпців Використання обчислюваних стовпців у таблиці Excel |
Дізнайтеся, як створити таблицю Excel і додати або видалити стовпці або обчислювані стовпці. |
|
У цьому шаблоні показано кілька способів автоматизації повторюваних завдань за допомогою макросу. |
За допомогою засобу перевірки орфографії можна не лише знайти слова з помилками, але й знайти значення, які не використовуються послідовно, наприклад назви продуктів або компаній, додавши ці значення до настроюваного словника.
|
Додаткові відомості |
Опис |
|---|---|
|
У цій статті описано, як виправити слова з орфографічною помилкою на аркуші. |
|
|
Додавання слів до засобу перевірки орфографії за допомогою додаткових словників |
У цій статті пояснюється, як використовувати спеціальні словники. |
Повторювані рядки – це поширена проблема під час імпорту даних. Радимо спочатку відфільтрувати унікальні значення, щоб переконатися, що результати потрібні, перш ніж видаляти повторювані значення.
|
Додаткові відомості |
Опис |
|---|---|
|
Фільтрування за унікальними значеннями або вилучення повторюваних значень |
Тут показано дві тісно пов'язані процедури: фільтрування для унікальних рядків і видалення повторюваних рядків. |
Ви можете видалити загальний рядок на початку, наприклад підпис, після якого йде двокрапка та пробіл, або суфікс, наприклад дужки в кінці рядка, який є застарілим або непотрібним. Це можна зробити, знайшовши екземпляри цього тексту, а потім замінивши його без тексту або іншого тексту.
|
Додаткові відомості |
Опис |
|---|---|
|
Перевірте, чи клітинка містить текст (нечутливий до регістра) Перевірка наявності в клітинці тексту (з урахуванням регістра) |
Дізнайтеся, як знайти текст за допомогою команди Знайти та кількох функцій. |
|
Тут показано, як видалити текст за допомогою команди Замінити та кількох функцій. |
|
|
Дізнайтеся, як використовувати діалогові вікна Пошук і заміна . |
|
|
FIND, FINDB SEARCH, SEARCHB REPLACE, REPLACEB ЗАМІНИТИ LEFT, LEFTB RIGHT, RIGHTB LEN, LENB MID, MIDB |
Це функції, які можна використовувати для виконання різних завдань із керування рядками, наприклад пошуку та заміни підрядка в рядку, видобування частин рядка або визначення довжини рядка. |
Іноді текст входить у мішаний мішок, особливо якщо йдеться про регістр тексту. Використовуючи одну або кілька з трьох функцій Case, можна перетворити текст на малі букви, як-от адреси електронної пошти, великі букви, наприклад коди товарів або регістр, наприклад імена або назви книг.
|
Додаткові відомості |
Опис |
|---|---|
|
Показано, як використовувати три функції Case. |
|
|
Перетворює всі великі букви в текстовому рядку на малі букви. |
|
|
Перетворює першу букву в текстовому рядку та всі інші букви, що стоять після небуквених символів, на великі букви. Решту букв перетворює на малі. |
|
|
Перетворює текст на великі букви. |
Іноді текстові значення містять символи на початку, в кінці або кілька вбудованих символів (Юнікод значення набору символів 32 та 160) або недруковані символи (значення набору символів Юнікоду від 0 до 31, 127, 129, 141, 143, 144 і 157). Іноді ці символи можуть призвести до неочікуваних результатів під час сортування, фільтрування або пошуку. Наприклад, у зовнішньому джерелі даних користувачі можуть робити друкарські помилки, випадково додаючи зайві пробіли, або імпортовані текстові дані із зовнішніх джерел можуть містити недруковані символи, вбудовані в текст. Оскільки ці символи не легко помітити, неочікувані результати може бути складно зрозуміти. Щоб видалити ці непотрібні символи, можна скористатися комбінацією функцій TRIM, CLEAN і SUBSTITUTE.
|
Докладні відомості |
Опис |
|---|---|
|
Ця функція повертає числовий код першого символу в текстовому рядку. |
|
|
Видаляє з тексту перші 32 недруковані символи в 7-розрядному коді ASCII (значення від 0 до 31). |
|
|
Видаляє з тексту 7-розрядний символ пробілу ASCII (значення 32). |
|
|
За допомогою функції SUBSTITUTE можна замінити більш високі символи Юнікоду (значення 127, 129, 141, 143, 144, 157 і 160) на 7-розрядні символи ASCII, для яких створено функції TRIM і CLEAN. |
Є дві основні проблеми з числами, які можуть вимагати очищення даних: число випадково імпортовано як текст, а знак від'ємного значення потрібно змінити на стандартний для вашої організації.
|
Докладні відомості |
Опис |
|---|---|
|
У цій статті описано, як перетворити числа, відформатовані та збережені в клітинках як текст, що може спричинити проблеми з обчисленнями або ускладняти порядок сортування в числовому форматі. |
|
|
Перетворює число на текстовий формат і застосовує символ грошової одиниці. |
|
|
Перетворює значення на текст у певному числовому форматі. |
|
|
Округлює число до вказаної кількості десяткових знаків, форматує число в десятковому форматі за допомогою крапки та коми та повертає результат як текст. |
|
|
Перетворює текстовий рядок, що представляє число, на число. |
Оскільки форматів дат так багато, тому їх можна плутати з кодами нумерованих частин або іншими рядками, які містять скісні риски або дефіси, дати й час часто потрібно перетворювати та переформатувати.
|
Додаткові відомості |
Опис |
|---|---|
|
Змінення системи дат, формату або двозначного інтерпретації року |
У цій статті описано, як працює система дат в Office Excel. |
|
У цьому розділі показано, як перетворювати одиниці часу. |
|
|
Перетворення дат, збережених у текстовому форматі, на формат дати |
Тут показано, як перетворити формат дати, відформатовані та збережені в клітинках як текст, що може спричинити проблеми з обчисленнями або ускладняти порядок сортування у форматі дати. |
|
Повертає послідовне числове значення, яке представляє певну дату. Якщо перед застосуванням функції формат клітинки було задано як Загальний, результат буде відформатовано як дату. |
|
|
Перетворює дату, представлену текстом, на серійний номер. |
|
|
Повертає десяткове значення конкретного часу. Якщо перед застосуванням функції формат клітинки було задано як Загальний, результат буде відформатовано як дату. |
|
|
Повертає десяткове значення конкретного часу, представленого текстовим рядком. Десяткове число – це значення від 0 (нуля) до 0,999999999, яке представляє час від 0:00:00 (12:00:00 AM) до 23:59:59 (11:59:59). |
Зазвичай після імпорту даних із зовнішнього джерела даних потрібно об'єднати два або кілька стовпців в один або розділити один стовпець на два або кілька стовпців. Наприклад, можна розділити стовпець, який містить повне ім'я, на ім'я та прізвище. Крім того, можна розділити стовпець, який містить поле адреси, на окремі стовпці вулиці, міста, регіону та поштового індексу. Зворотне також може бути істинним. Ви можете об'єднати стовпець "Ім'я" та "Прізвище" в стовпець "Повне ім'я" або об'єднати окремі стовпці адреси в один стовпець. Додаткові спільні значення, які можуть потребувати об'єднання в один стовпець або розділення на кілька стовпців, включають коди продуктів, шляхи до файлів і IP-адреси.
|
Докладні відомості |
Опис |
|---|---|
|
Об'єднання імен і прізвищ Об'єднання тексту та чисел Поєднання тексту з датою або часом Поєднання двох або більше стовпців за допомогою функції |
Відображення типових прикладів поєднання значень із двох або більше стовпців. |
|
Розділення тексту на різні стовпці за допомогою майстра текстів |
У цьому майстрі описано, як розділяти стовпці на основі різних поширених роздільників. |
|
У цій статті описано, як за допомогою функцій LEFT, MID, RIGHT, SEARCH і LEN розділити стовпець імен на два або більше стовпців. |
|
|
Тут показано, як використовувати функцію CONCATENATE, оператор & (амперсанд) і майстер перетворення тексту на стовпці. |
|
|
У цій статті описано, як використовувати команди Об'єднати клітинки, Об'єднати через і Об'єднати та Розташувати в центрі . |
|
|
Об'єднує два або більше текстових рядків в один текстовий рядок. |
Більшість функцій аналізу та форматування в Програмі Office Excel передбачають, що дані містяться в одній плоскій двовимірній таблиці. Іноді може знадобитися зробити рядки стовпцями, а стовпці – рядками. В інший час дані навіть не структуровано в табличному форматі, і вам потрібен спосіб перетворення даних із нетабличного на табличний формат.
|
Докладні відомості |
Опис |
|---|---|
|
Повертає вертикальний діапазон клітинок як горизонтальний діапазон або навпаки. |
|
Іноді адміністратори баз даних використовують Програму Office Excel, щоб знаходити та виправляти помилки, що збігаються, коли об'єднуються дві або кілька таблиць. Це може включати узгодження двох таблиць із різних аркушів, наприклад, для перегляду всіх записів в обох таблицях або порівняння таблиць і пошуку рядків, які не збігаються.
|
Докладні відомості |
Опис |
|---|---|
|
Типові способи пошуку даних за допомогою функцій підстановки. |
|
|
Повертає значення з діапазону в один рядок або один стовпець або з масиву. Функція LOOKUP має дві синтаксичні форми: векторну форму та форму масиву. |
|
|
Шукає значення у верхньому рядку таблиці або масиві значень, а потім повертає значення в тому самому стовпці з рядка, указаного в таблиці або масиві. |
|
|
Шукає значення в першому стовпці масиву таблиці та повертає значення в тому самому рядку з іншого стовпця в масиві таблиці. |
|
|
Повертає значення або посилання на значення з таблиці або діапазону. Є дві форми функції INDEX: форма масиву та форма посилання. |
|
|
Повертає відносне положення елемента в масиві, який відповідає вказаному значенню у вказаному порядку. Використовуйте функцію MATCH замість однієї з функцій LOOKUP, якщо потрібно отримати позицію елемента в діапазоні замість самого елемента. |
|
|
Повертає посилання на діапазон, віддалений від клітинки або діапазону клітинок на вказану кількість рядків і стовпців. Посилання, що повертається, може бути однією клітинкою або діапазоном клітинок. Кількість рядків і стовпців, які повертаються, можна вказати. |
Нижче наведено частковий список сторонніх постачальників, які мають продукти, які використовуються для очищення даних різними способами.
Примітка.: Корпорація Майкрософт не надає підтримку для сторонніх продуктів.
|
Provider |
Продукт |
|---|---|
|
Add-in Express Ltd. |
|
|
Add-Ins.com |
|
|
Додавання документів |
|
|
WinPure (WinPure) |
