10 чудових способів очищення даних у електронних таблицях Excel

Перегляд відео - 10 способів очищення даних у Excel

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

У цій публікації в блозі я покажу вам 10 простих способів очищення даних у Excel.

#1 Позбавтеся від зайвих місць

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

Синтаксис: TRIM (текст)

Функція Excel TRIM бере в якості входу посилання на клітинку (або текст). Він видаляє провідні та кінцеві пробіли, а також додаткові пробіли між словами (за винятком одиночних пробілів).

#2 Виберіть і обробіть усі порожні клітинки

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

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

1. Виберіть весь набір даних
2. Натисніть F5 (відкриється діалогове вікно Перейти до)
3. Натисніть кнопку Спеціальна… (внизу ліворуч). Відкриється діалогове вікно Перейти до спеціального
   
4. Виберіть Пусто і натисніть OK
   

Це виділяє всі порожні клітинки у наборі даних. Якщо ви хочете ввести 0 або недоступний у всіх цих клітинках, просто введіть його і натисніть Control + Enter (пам’ятайте, що якщо натиснути лише Enter, значення буде вставлено лише в активну клітинку).

#3 Перетворіть числа, збережені як текст, у цифри

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

1. У будь -якій порожній клітинці введіть 1
2. Виберіть клітинку, де ви ввели 1, і натисніть Control + C
3. Виберіть клітинку/діапазон, який потрібно перетворити на числа
4. Виберіть Вставити -> Спеціальна вставка (Комбінація клавіш на клавіатурі - Alt + E + S)
5. У спеціальному діалоговому вікні "Вставити" виберіть "Помножити" (у категорії операцій)
   
6. Натисніть OK. Це перетворює всі числа у текстовому форматі назад у числа.

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

#4 - Видаліть дублікати

З дублюючими даними можна зробити 2 речі - Виділіть це або Видалити його.

• Виділити дублікати даних:
  • Виберіть дані та перейдіть додому -> Умовне форматування -> Правила виділення клітинок -> Повторювані значення.
  • Вкажіть форматування, і всі повторювані значення будуть виділені.
    
• Видалити дублікати в даних:
  • Виберіть дані та перейдіть до Дані -> Видалити дублікати.
  • Якщо ваші дані мають заголовки, переконайтеся, що прапорець у верхньому правому куті встановлено.
  • Виберіть стовпці (колонки), з яких потрібно видалити дублікати, і натисніть OK.
    

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

Пов'язані: Найкращий посібник із пошуку та видалення дублікатів у Excel.

#5 Виділіть помилки

Виділити помилки в даних у Excel можна двома способами:

Використання умовного форматування

1. Виберіть весь набір даних
2. Перейдіть додому -> Умовне форматування -> Нове правило
3. У діалоговому вікні "Нове правило форматування" виберіть "Форматувати лише клітинки, які містять"
4. У описі правила виберіть Помилки зі спадного меню
5. Встановіть формат і натисніть OK. Це виділяє будь -яке значення помилки у вибраному наборі даних
   

Використання Go To Special

1. Виберіть весь набір даних
2. Натисніть клавішу F5 (відкриється вікно «Перейти до діалогу»)
3. Натисніть спеціальну кнопку внизу зліва
4. Виберіть Формули та зніміть прапорець біля всіх опцій, крім помилок
   

Це вибирає всі клітинки, у яких є помилка. Тепер ви можете вручну виділити їх, видалити або ввести що -небудь.

#6 Змініть текст на нижній/верхній/правильний регістр

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

LOWER () - Перетворює весь текст у нижній регістр.
UPPER () - Перетворює весь текст у верхній регістр.
PROPER () - Перетворює весь текст у належний регістр.

#7 Аналіз даних за допомогою тексту для стовпця

Коли ви отримуєте дані з бази даних або імпортуєте їх із текстового файлу, може статися, що весь текст буде тісним в одній комірці. Ви можете розібрати цей текст на кілька клітинок за допомогою функціоналу «Текст у стовпці» в Excel.

1. Виберіть дані/текст, які потрібно проаналізувати
2. Перейдіть до Дані -> Текст у стовпець (Відкриється майстер перетворення тексту до стовпців)
   • Крок 1: Виберіть тип даних (виберіть Розмежований, якщо ваші дані розташовані не однаково, а розділені такими символами, як кома, дефіс, крапка…). Натисніть Далі
     
   • Крок 2: Виберіть роздільник (символ, який розділяє ваші дані). Ви можете вибрати попередньо визначений роздільник або будь-що інше, використовуючи опцію Інше
     
   • Крок 3: Виберіть формат даних. Також виберіть осередок призначення. Якщо клітинка призначення не вибрана, поточна комірка буде перезаписана
     

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

#8 Перевірка правопису

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

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

Ось детальний підручник про те, як використовувати перевірку правопису в Excel.

#9 Видалити все форматування

У своїй роботі я використовував кілька баз даних, щоб отримати дані в excel. Кожна база даних мала своє форматування даних. Коли у вас є всі дані, ось як можна видалити все форматування за один раз:

1. Виберіть набір даних
2. Перейдіть додому -> Очистити -> Очистити формати
   

Так само можна очистити лише коментарі, гіперпосилання або вміст.

#10 Використовуйте функцію "Знайти та замінити" для очищення даних у Excel

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

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

Це мої 10 найкращих методів очищення даних у Excel. Якщо ви хочете вивчити ще деякі методи, ось посібник команди MS Excel - Чисті дані в Excel.

Якщо ви використовуєте ще якісь методи, поділіться ними з нами у розділі коментарів!