Слова з помилками, вперті пробіли в кінці, небажані префікси, неправильні регістри та недруковані символи справляють погане перше враження. Це далеко не повний перелік способів, якими можуть бути забруднені дані. Засукайте рукава. Настав час провести генеральне прибирання аркушів у програмі Microsoft Excel.
Основи очищення даних
Ви не завжди можете контролювати формат і тип даних, які імпортуються із зовнішнього джерела даних, наприклад бази даних, текстового файлу або веб-сторінки. Перед аналізом даних часто виникає потреба їх очистити. На щастя, у програмі Excel є багато функцій, які допомагають отримати дані саме в потрібному форматі. Іноді завдання просте, і є певна функція, яка робить це за вас. Наприклад, за допомогою перевірки орфографії можна легко видалити слова з помилками в стовпцях, які містять примітки або описи. Якщо потрібно видалити повторювані рядки, це можна швидко зробити за допомогою діалогового вікна "Видалення повторень ".
В інших випадках може знадобитися виконати операції з одним або кількома стовпцями за допомогою формули для перетворення імпортованих значень на нові. Наприклад, якщо потрібно видалити пробіли вкінці, можна створити новий стовпець і очистити дані за допомогою формули, заповнити новий стовпець, перетворити формули нового стовпця на значення, а потім видалити початковий стовпець.
Основні етапи очищення даних полягають у наступному:
Імпорт даних із зовнішнього джерела даних.
Створіть резервну копію вихідних даних в окремій книзі.
Переконайтеся, що дані в табличному форматі складаються з рядків і стовпців, які містять схожі дані в кожному стовпці, видно всі стовпці й рядки, а пустих рядків у діапазоні немає. Щоб отримати найкращі результати, використовуйте таблицю Excel.
Виконуйте завдання, які не потребують спочатку маніпуляцій зі стовпцями (наприклад, перевірку орфографії або використання діалогового вікна "Пошук і заміна ").
Далі виконайте завдання, для яких потрібні маніпуляції зі стовпцями. Ось загальні кроки для роботи зі стовпцем:
- Вставте новий стовпець (B) поруч із вихідним (A), який потрібно очистити.
- У верхній частині нового стовпця (B) додайте формулу, яка перетворюватиме дані.
- Заповніть формулою новий стовпець (B). У таблиці Excel автоматично створюється обчислюваний стовпець із заповненими вниз значеннями.
- Виділіть новий стовпець (B), скопіюйте його та вставте інші значення в новий стовпець (B).
- Вилучіть вихідний стовпець (A), який перетворить новий стовпець із "B" на "A".
Щоб періодично очищати одне й те саме джерело даних, радимо записувати макрос або писати код, щоб автоматизувати весь процес. Існує також ряд зовнішніх надбудов сторонніх постачальників, які можна використовувати, якщо немає часу або ресурсів, щоб автоматизувати цей процес власноруч.
| Додаткові відомості | Опис |
|---|---|
| Автоматичне вставлення даних у клітинки аркуша | Показано, як використовувати команду заповнення . |
|
Створення та форматування таблиць Змінення розміру таблиці за допомогою додавання або видалення рядків і стовпців Використання обчислюваних стовпців у таблиці Excel |
Дізнайтеся, як створити таблицю Excel і додати або видалити стовпці або обчислювані стовпці. |
| Створення макросу | Відображає кілька способів автоматизації повторюваних завдань за допомогою макросу. |
Перевірка орфографії
За допомогою перевірки орфографії можна не тільки знаходити слова з помилками, а й значення, які використовуються непослідовно, наприклад назви продуктів або компаній (додавши їх до додаткового словника).
| Додаткові відомості | Опис |
|---|---|
| Перевірка орфографії та граматики | Тут показано, як виправляти слова на аркуші з помилками. |
| Додавання слів до засобу перевірки орфографії за допомогою спеціальних словників | У цій статті описано, як користуватися настроюваними словниками. |
Видалення повторюваних рядків
Повторювані рядки – поширена проблема під час імпорту даних. Варто відфільтрувати унікальні значення, перш ніж видаляти повторювані значення.
| Додаткові відомості | Опис |
|---|---|
| Фільтрування за унікальними значеннями або вилучення повторюваних значень | Відображаються дві тісно пов'язані процедури: фільтрування унікальних рядків і видалення повторюваних рядків. |
Пошук і заміна тексту
Можливо, потрібно буде видалити загальний початковий рядок, наприклад мітку з двокрапкою та пробілом, або суфікс, як-от фразу з дужками в кінці рядка, яка застаріла чи непотрібна. Це можна зробити, знайшовши екземпляри цього тексту та замінивши його на жодний або інший текст.
| Додаткові відомості | Опис |
|---|---|
|
Перевірка наявності тексту в клітинці (без урахування регістра) Перевірка наявності в клітинці тексту (з урахуванням регістра) |
Дізнайтеся, як використовувати команду Find і кілька функцій для пошуку тексту. |
| Видалення символів із тексту | Тут показано, як використовувати команду заміни та кілька функцій для видалення тексту. |
| Пошук і заміна тексту й чисел на аркуші | Дізнайтеся, як використовувати діалогові вікна " Пошук і заміна ". |
|
FIND, FINDB SEARCH, SEARCHB REPLACE, REPLACEB SUBSTITUTE LEFT, LEFTB RIGHT, RIGHTB LEN, LENB MID, MIDB |
Це функції, які можна використовувати, щоб виконувати різні операції з рядками, як-от пошук і заміна вкладеного рядка в рядку, видобування частин рядка або визначення довжини рядка. |
Змінення регістра тексту
Інколи текст надходить у змішаному пакеті, особливо, коли йдеться про текст. Використовуючи одну або кілька із трьох функцій Case, можна перетворити текст у нижньому регістрі, наприклад адресу електронної пошти, великі букви, наприклад коди продуктів, або змішаний, наприклад імена або назви книг.
| Додаткові відомості | Опис |
|---|---|
| Змінення регістра тексту | Тут показано, як використовувати три функції Case. |
| 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 (ТЕКСТ) | Перетворює значення на текст у певному числовому форматі. |
| ВИПРАВЛЕНО | Округлює число до вказаної кількості десяткових знаків, форматує його в десятковому форматі за допомогою коми та пробілів і повертає результат у вигляді тексту. |
| VALUE (ЗНАЧЕННЯ) | Перетворює текстовий рядок, що представляє число, на число. |
Виправлення дат і часу
Оскільки існує дуже багато різних форматів дат, ці формати можна сплутати з нумерованими кодами частин або іншими рядками, які містять скісні риски або дефіси, значення дати й часу часто потрібно перетворювати та форматувати.
| Додаткові відомості | Опис |
|---|---|
| Змінення системи дат, формату або інтерпретації двозначного року | Тут описано, як працює система дат у програмі Office Excel. |
| Перетворення значень часу | Тут показано, як перетворювати дані між різними одиницями часу. |
| Перетворення дат, збережених у текстовому форматі, на формат дати | У цій статті показано, як перетворювати форматовані в клітинках дати в текстовому форматі, що може спричинити проблеми з обчисленнями або порядком сортування, на формат дати. |
| DATE (ДАТА) | Повертає порядковий номер, який відповідає вказаній даті. Якщо перед застосуванням функції формат клітинки було задано як Загальний, результат буде відформатовано як дату. |
| DATEVALUE | Перетворює дату, представлену текстом, на числове подання. |
| TIME | Повертає десяткове значення конкретного часу. Якщо перед застосуванням функції формат клітинки було задано як Загальний, результат буде відформатовано як дату. |
| TIMEVALUE | Повертає десяткове значення конкретного часу, представленого текстовим рядком. Десяткове значення – це значення в діапазоні від 0 (нуля) до 0,99999999, яке відповідає часу від 0:00:00 (12:00:00 AM) до 23:59:59 (11:59:59 PM). |
Об'єднання та розділення стовпців
Імпортувавши дані із зовнішнього джерела даних, зазвичай можна об'єднати кілька стовпців в один або розділити один стовпець на два або кілька. Наприклад, може знадобитися розділити стовпець, який містить повне ім'я, на ім'я та прізвище. Або можна розділити стовпець, який містить поле адреси, на окремі стовпці вулиць, міст, областей і поштових індексів. Може бути і зворотне. Ви можете об'єднати стовпці імені й прізвища зі стовпцем повного імені або об'єднати окремі стовпці адреси в один стовпець. До додаткових спільних значень, які може знадобитися об'єднати в один або кілька стовпців, належать коди продуктів, шляхи до файлів і адреси протоколу Інтернету.
| Додаткові відомості | Опис |
|---|---|
|
Поєднання імен і прізвищ Поєднання тексту та чисел Поєднання тексту з датою або часом Об’єднання двох або кількох стовпців за допомогою функції |
Покажіть типові приклади поєднання значень із кількох стовпців. |
| Розділення тексту на різні стовпці за допомогою майстра текстів | У цьому майстрі показано, як розділити стовпці за різними спільними роздільниками. |
| Розділення тексту на кілька стовпців за допомогою функцій | Тут показано, як за допомогою функцій LEFT, MID, RIGHT, SEARCH і LEN розділити стовпець імені на два або кілька. |
| Об'єднання або розділення вмісту клітинок | Тут показано, як використовувати функцію CONCATENATE, оператор & (амперсанд) і майстер перетворення тексту на стовпці. |
| Об’єднання та розділення об’єднаних клітинок | Тут показано, як використовувати команди "Об'єднати клітинки", " Об'єднати потрібні", " Об'єднати" та "Розташувати в центрі ". |
| CONCATENATE | Об'єднує два або більше текстових рядків в один. |
Трансформація та перевпорядкування стовпців і рядків
Більшість функцій аналізу й форматування в програмі Office Excel припускають, що дані існують в одній плоскій двовимірній таблиці. Іноді буває потрібно, щоб рядки стали стовпцями, а стовпці – рядками. В інших випадках дані навіть не структуровані в табличному форматі. Натомість потрібен спосіб перетворити дані з нетабличного на табличний.
| Додаткові відомості | Опис |
|---|---|
| TRANSPOSE | Повертає вертикальний діапазон клітинок у вигляді горизонтального діапазону або навпаки. |
Узгодження даних таблиці за допомогою об'єднання або зіставлення
Іноді адміністратори баз даних використовують Office Excel, щоб знаходити та виправляти помилки зіставлення, коли об'єднуються дві або більше таблиць. Для цього може знадобитися узгодити дві таблиці з різних аркушів, наприклад, щоб побачити всі записи в обох таблицях або порівняти таблиці та знайти рядки, які не збігаються.
| Додаткові відомості | Опис |
|---|---|
| Пошук значень у списку даних | Показано поширені способи пошуку даних за допомогою функцій підстановки. |
| LOOKUP | Повертає значення з діапазону, який складається з одного рядка або одного стовпця, або з масиву. Функція LOOKUP має дві форми синтаксису: векторну та форму масиву. |
| HLOOKUP | Шукає значення у верхньому рядку таблиці або масиві значень, а потім повертає значення в тому ж стовпці рядка, указаного в таблиці або масиві. |
| VLOOKUP | Шукає значення в першому стовпці масиву таблиці та повертає значення в тому ж рядку з іншого стовпця масиву таблиці. |
| ПОКАЗНИК | Повертає значення або посилання на значення з таблиці або діапазону. Існує дві форми функції INDEX: форма масиву та форма посилання. |
| MATCH | Повертає відносне розташування елемента в масиві, який відповідає вказаному значенню у вказаному порядку. Використовуйте функцію MATCH замість однієї з функцій LOOKUP, якщо потрібно отримати позицію елемента в діапазоні замість самого елемента. |
| OFFSET | Повертає посилання на діапазон, віддалений від клітинки або діапазону клітинок на вказану кількість рядків і стовпців. Посилання, що повертається, може бути однією клітинкою або діапазоном клітинок. Кількість рядків і стовпців, які повертаються, можна вказати. |
Сторонні постачальники
Нижче наведено неповний список сторонніх постачальників, продукти яких використовуються для очищення даних різними способами.
Примітка.
Корпорація Майкрософт не надає підтримку продуктів сторонніх виробників.