10 супер чисти начина за почистване на данни в електронни таблици на Excel

Гледайте видео - 10 начина за почистване на данни в Excel

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

В тази публикация в блога ще ви покажа 10 прости начина за почистване на данни в Excel.

#1 Отървете се от допълнителните пространства

Допълнителните пространства са болезнено трудно забележими. Въпреки че по някакъв начин може да забележите допълнителните интервали между думи или числа, задните интервали дори не се виждат. Ето един чист начин да се отървете от тези допълнителни пространства - Използвайте функцията TRIM.

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

Функцията Excel TRIM приема препратката към клетката (или текст) като вход. Той премахва предните и задните интервали, както и допълнителните интервали между думите (с изключение на единични интервали).

#2 Изберете и обработете всички празни клетки

Празните клетки могат да създадат хаос, ако не бъдат третирани предварително. Често срещам проблеми с празни клетки в набор от данни, който се използва за създаване на отчети/табла за управление.

Може да искате да попълните всички празни клетки с „0“ или „Не е налично“ или просто да искате да го маркирате. Ако има огромен набор от данни, това ръчно може да отнеме часове. За щастие, има начин да изберете всички празни клетки наведнъж.

1. Изберете целия набор от данни
2. Натиснете F5 (това отваря диалоговия прозорец Go To)
3. Кликнете върху бутона Специален… (долу вляво). Това отваря диалоговия прозорец Go To Special
   
4. Изберете празно и щракнете върху OK
   

Това избира всички празни клетки във вашия набор от данни. Ако искате да въведете 0 или Не е налично във всички тези клетки, просто го въведете и натиснете Control + Enter (не забравяйте, че ако натиснете само enter, стойността се вмъква само в активната клетка).

#3 Преобразувайте числа, съхранени като текст в числа

Понякога, когато импортирате данни от текстови файлове или външни бази данни, числата се съхраняват като текст. Също така някои хора имат навика да използват апостроф (‘) преди число, за да го превърнат в текст. Това може да създаде сериозни проблеми, ако използвате тези клетки при изчисления. Ето един глупав начин да превърнете тези числа, съхранявани като текст, обратно в числа.

1. Във всяка празна клетка въведете 1
2. Изберете клетката, в която сте въвели 1, и натиснете Control + C
3. Изберете клетката/диапазона, който искате да преобразувате в числа
4. Изберете Поставяне -> Специално поставяне (Пряк път на клавиатурата - Alt + E + S)
5. В диалоговия прозорец Paste Special Dialog изберете Multiply (в категория операции)
   
6. Щракнете върху OK. Това преобразува всички числа в текстов формат обратно в числа.

Можете да направите много повече с опциите за специални операции за поставяне. Ето различни други начини за умножаване в Excel с помощта на Специално поставяне.

#4 - Премахване на дубликати

Можете да направите 2 неща с дублирани данни - Маркирайте го или Изтрий го.

• Маркирайте дублирани данни:
  • Изберете данните и отидете на Начало -> Условно форматиране -> Правила за маркиране на клетки -> Дублирани стойности.
  • Посочете форматирането и всички дублирани стойности ще бъдат подчертани.
    
• Изтриване на дубликати в данни:
  • Изберете данните и отидете на Данни -> Премахване на дубликати.
  • Ако вашите данни имат заглавки, уверете се, че квадратчето в горния десен ъгъл е поставено.
  • Изберете колоните (колоните), от които искате да премахнете дубликати, и щракнете върху OK.
    

Това премахва дублиращите се стойности от списъка. Ако искате оригиналният списък да бъде непокътнат, копирайте и поставете данните на друго място и след това направете това.

Свързани: Най -доброто ръководство за намиране и премахване на дубликати в Excel.

#5 Маркирайте грешки

Има 2 начина, по които можете да маркирате грешки в данни в Excel:

Използване на условно форматиране

1. Изберете целия набор от данни
2. Отидете на Начало -> Условно форматиране -> Ново правило
3. В диалоговия прозорец Ново правило за форматиране изберете „Форматиране само на клетки, които съдържат“
4. В описанието на правилото изберете Грешки от падащото меню
5. Задайте формата и щракнете върху OK. Това подчертава всяка стойност на грешка в избрания набор от данни
   

Използване Go Go Special

1. Изберете целия набор от данни
2. Натиснете F5 (това отваря диалоговия прозорец Go To)
3. Кликнете върху Специален бутон в долния ляв ъгъл
4. Изберете Формули и премахнете отметката от всички опции с изключение на Грешки
   

Това избира всички клетки, които имат грешка в него. Сега можете да ги маркирате ръчно, да го изтриете или да въведете нещо в него.

#6 Променете текста на Долен/Горен/Правилен регистър

Когато наследите работна книга или импортирате данни от текстови файлове, често имената или заглавията не са последователни. Понякога целият текст може да бъде с малки/главни букви или може да е комбинация от двете. Можете лесно да направите всичко последователно, като използвате тези три функции:

LOWER () - Конвертира целия текст в малки букви.
UPPER () - Конвертира целия текст в главни букви.
PROPER () - Преобразува целия текст в подходящ регистър.

#7 Анализирайте данните, като използвате текст за колона

Когато получавате данни от база данни или ги импортирате от текстов файл, може да се случи целият текст да е тесен в една клетка. Можете да анализирате този текст в множество клетки, като използвате функцията Текст в колона в Excel.

1. Изберете данните/текста, които искате да анализирате
2. Отидете на Данни -> Текст в колона (Това отваря съветника за текст в колони)
   • Стъпка 1: Изберете типа данни (изберете Разделени, ако данните ви не са на еднакво разстояние и са разделени със знаци като запетая, тире, точка …). Щракнете върху Напред
     
   • Стъпка 2: Изберете Разделител (знакът, който разделя вашите данни). Можете да изберете предварително определен разделител или нещо друго, като използвате опцията Други
     
   • Стъпка 3: Изберете формата на данните. Също така изберете клетката дестинация. Ако клетката дестинация не е избрана, текущата клетка се презаписва
     

Свързани: Извличане на потребителско име от имейл име, като се използва текст в колона.

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

Нищо не намалява достоверността на работата ви от правописна грешка.

Използвайте клавишната комбинация F7, за да изпълните проверка на правописа за вашия набор от данни.

Ето подробен урок за това как да използвате Проверка на правописа в Excel.

#9 Изтрийте цялото форматиране

В работата си използвах множество бази данни, за да получа данните в Excel. Всяка база данни имаше собствено форматиране на данни. Когато разполагате с всички данни, ето как можете да изтриете цялото форматиране наведнъж:

1. Изберете набора от данни
2. Отидете на Начало -> Изчистване -> Изчистване на формати
   

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

#10 Използвайте Find and Replace за почистване на данни в Excel

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

Прочетете повече за това как Find and Replace може да се използва за почистване на данни.

Това са моите топ 10 техники за изчистване на данни в Excel. Ако искате да научите още някои техники, ето ръководство от екипа на MS Excel - Чисти данни в Excel.

Ако имате още някакви техники, които използвате, споделете ги с нас в секцията за коментари!