Как очистить беспорядочные данные в Excel: ОБРЕЗАТЬ, ОЧИСТИТЬ и пространство, которое вы не видите

1. Чего это стоит: 865 человек отсутствуют в общем объеме по региону

Пять из шестнадцати орденов относятся к Северному региону, и их общее количество составляет 2495. Две из этих пяти строк прибыли с проблемой, которую вы не видите: одна имеет завершающий пробел после «Север», а другая заканчивается неразрывным пробелом, символом, который веб-страница или документ Слово оставляет после себя, когда через него вставляется текст.

Ничто на экране не отличается. Вот что написано в трудовой книжке.

North revenue, clean data                       2495
North revenue, same data with two bad cells     1630
Rows that are exactly "North" out of five          3
Distinct regions, clean                            4
Distinct regions, messy                            6

Всего осталось 865, то есть 425 плюс 440, два затронутых заказа. И количество регионов увеличилось с четырех до шести, потому что «Север» и «Север» плюс неразрывный пробел — это каждый отдельный регион с точки зрения Эксель.

Скажите вслух, какой из этих двух симптомов вы заметите в реальной рабочей тетради первым. Недостающее число 865 невидимо; ничто не говорит о том, что общая сумма должна была быть больше. Количество регионов — это показатель, и именно по этой причине первое, что нужно сделать с любым новым текстовым столбцом, — это подсчитать его отдельные значения и спросить, является ли это число тем числом, которое вы ожидали.

2. Найдите проблему, прежде чем исправлять: LEN и CODE.

Две формулы делают весь диагноз, и ни одна из них ничего не меняет.

LEN подсчитывает символы и превращает невидимую проблему в число.

=LEN(D8)      6      you can see five letters
=LEN(D12)     6      you can see five letters

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

=CODE(RIGHT(D8,1))      32     an ordinary space
=CODE(RIGHT(D12,1))    160     a non-breaking space

32 — пробел. 160 — персонаж, созданный   на веб-странице и в Слово, когда два слова находятся в одной строке. Они выглядят одинаково в каждом шрифте. Только один из них удаляется функцией, к которой все обращаются первыми.

Сохраняйте эти две формулы скорее как привычку, а не как спасение. =LEN(A2) заполнил столбец и =CODE(RIGHT(A2,1)) в одной слишком длинной строке появляется тридцатисекундный диагноз, который превращает «поиск не работает» в «в строке 12 есть символ 160».

3. TRIM: что удаляет, а что сжимает

Перед объяснением: TRIM везде описывается как удаление пробелов. Скажи, что, по твоему мнению, это делает, чтобы "Dana Reyes", с тремя пробелами посередине.

=TRIM("Dana   Reyes")       Dana Reyes
=LEN(TRIM("Dana   Reyes"))          10

Он выполняет две работы, а не одну. Он полностью удаляет пробелы в начале и конце и сжимает любую серию пробелов. внутри текст до одного пробела. Десять символов: девять букв и один пробел. Это второе поведение обычно является тем, чего вы хотите, а иногда и нет, поэтому его стоит знать, а не открывать.

В ячейке с конечным пространством все работает точно так, как заявлено.

=LEN(D8)            6
=LEN(TRIM(D8))      5

Один символ исчез, и значение теперь соответствует всем остальным «северам» в столбце. Если ваша проблема в обычных пространствах, то это все решение, и именно поэтому TRIM вокруг ключа поиска — это стандартное первое, что нужно попробовать, когда поиск возвращает #N/A по значению, которое вы можете увидеть.

4. Пробел TRIM не уберет

Перед объяснением: другая плохая ячейка заканчивается на символ 160. Предскажите, что TRIM делает это.

=LEN(D12)              6
=LEN(TRIM(D12))        6
=LEN(CLEAN(D12))       6

Ничего. Ни одна из функций не касается его. TRIM удаляет символ 32 и только символ 32, и CLEAN удаляет управляющие символы с номерами от 0 до 31, включая 160. Это самая распространенная причина, по которой кто-то говорит: «Я уже обрезал это, но оно все еще не соответствует».

Исправление состоит в том, чтобы сначала преобразовать символ в обычный пробел, а затем обрезать.

=TRIM(SUBSTITUTE(D12, CHAR(160), " "))

=LEN(  that  )                          5
=  that  = "North"                   TRUE

SUBSTITUTE берет текст, вещь, которую нужно найти, и вещь, которую нужно поставить на место, и заменяет каждое вхождение. Вложение его внутрь TRIM дает одну формулу, которая обрабатывает оба типа пространства одновременно, и эту версию стоит сохранить.

Сделайте эту формулу очистки по умолчанию для любого текстового столбца, поступающего из-за пределов вашей книги:

=TRIM(SUBSTITUTE(A2, CHAR(160), " "))

Это ничего не стоит для данных, которые уже были очищены, и экономит время для данных, которые еще не были очищены.

5. ЧИСТЫЙ, и два слова, которые оно объединяет

CLEAN удаляет непечатаемые управляющие символы, наиболее распространенным из которых является разрыв строки, который кто-то нажал Альт+Ввод, или который прибыл в поле, экспортированное из другой системы.

Внутри одного из имен представителей в «грязной» копии есть разрыв строки, поэтому в ячейке имя отображается в двух строках. Вот что происходит.

=LEN(C10)                    9
=LEN(CLEAN(C10))             8
=CLEAN(C10) = "OwenPark"  TRUE

Прочтите последнюю строчку дважды. CLEAN удалил разрыв строки, а не заменил его, поэтому «Оуэн» и «Парк» теперь объединены в одно слово без пробела. Ячейка больше не выглядит сломанной, что еще хуже, чем просто выглядеть сломанной, поскольку имя теперь неверно и выдерживает каждую последующую проверку.

Разрыв строки является разделителем, поэтому он должен стать пробелом, а не ничем.

=TRIM(SUBSTITUTE(C10, CHAR(10), " "))       Owen Park

Символ 10 — это перевод строки, а символ 13 — возврат каретки; текст из старых систем может содержать и то, и другое. Если вы очищаете столбец, который может содержать многострочные примечания, замените оба и используйте только CLEAN когда вы искренне хотите, чтобы персонаж исчез, а не был заменен.

6. СОБСТВЕННОЕ, ВЕРХНЕЕ и НИЖНЕЕ, и названия СОБСТВЕННЫЕ ошибаются.

В другом случае два одинаковых значения становятся двумя разными. UPPER и LOWER звучат именно так, и в них нет никаких сюрпризов. PROPER делает первую букву каждого слова заглавной, а их несколько.

=PROPER("owen park")      Owen Park       right
=PROPER("o'brien")        O'Brien         right
=PROPER("mcdonald")       Mcdonald        wrong
=PROPER("IBM")            Ibm             wrong

Он правильно определяет регистр апострофов, что действительно полезно. Он не может знать, что в «Макдоналдсе» есть заглавная буква посередине, и он с радостью уничтожит уже правильную аббревиатуру, потому что он применяет одно правило к каждому встречному слову.

Итак, правило для имен и кодов: никогда не бегайте. PROPER над колонкой реальных имен и считайте ее почищенной. Используйте его для данных, которые, как вы знаете, состоят из одного регистра и обычных слов, проверяйте вывод на глаз и сохраняйте исходный столбец, чтобы ущерб был обратимым. В любом случае для целей сопоставления вам редко нужны красивые заглавные буквы; сравнения в Excel уже игнорируют регистр, поэтому UPPER с обеих сторон — это более безопасный способ добиться согласия, чем пытаться сделать так, чтобы каждое значение выглядело красиво.

Представьте на секунду свою худшую колонку: имена клиентов, имена поставщиков, коды продуктов, набранные четырьмя разными людьми. Сколько из этих записей будет PROPER незаметно повредить, и заметит ли кто-нибудь до того, как посылка будет отправлена?

7. Числа, хранящиеся в виде текста

Та же проблема в числовых столбцах, с другим симптомом. Для одного из шестнадцати заказов значение Единицы введено в виде текста, а не числа. Ничего не видно, кроме маленького зеленого треугольника в углу, который большинство людей научились игнорировать.

=SUM(clean Units)      101
=SUM(messy Units)       95
=COUNT(messy Units)     15
=ISTEXT(that cell)    TRUE

SUM пропускает текст, поэтому общая сумма сокращается ровно на пропущенное значение, и COUNT говорит 15, где 16 рядов. Этот COUNT — самый дешевый детектор: поместите =COUNT(range) рядом с любым числовым столбцом и сравните его с количеством строк. Несоответствие — это текст, притворяющийся числом.

Три способа исправить это в порядке того, насколько они вам должны понравиться. Лучше всего исправить импорт, чтобы он никогда не поступал в виде текста, что для CSV означает объявление типов столбцов в диалоговом окне импорта, а не двойной щелчок по файлу; диалоговое окно импорта, которое удаляет ваши почтовые индексы охватывает тот же диалог с противоположной стороны. Далее лучше всего преобразовать в новый столбец с помощью =VALUE(A2), который возвращает действительное число, или умножить на 1, что тоже работает. В крайнем случае, выберите столбец, щелкните предупреждающий треугольник и выберите «Преобразовать в число», при этом данные редактируются на месте и не остается никаких записей о том, что это произошло.

Одно предупреждение о VALUE: это терпит неудачу с #VALUE! для всего, что не является чистым числом, включая значение с случайным пробелом или символом валюты. Эта ошибка полезна, поскольку она указывает на строки, требующие участия человека. Обертывание его, чтобы скрыть неудачу, вернет вас туда, где скрытие ошибки тебя всегда ставит.

8. Очистите новый столбец и докажите, что это работает.

Два рабочих правила имеют большее значение, чем любая из функций.

Никогда не чистите на месте. Поместите формулу очистки в новый столбец рядом с исходным, который называется Region_clean или что-то подобное, и направьте свой анализ на это. Перезапись необработанного столбца уничтожает единственное свидетельство того, что поступило, поэтому, если в следующем месяце цифры не будут соответствовать исходной системе, сравнивать будет не с чем. Это также делает очистку повторяемой: вставляется файл следующего месяца, и столбец пересчитывается.

Докажите это, а не предполагайте это. Уборка — это единственный вид деятельности, при котором работа и чек выглядят одинаково, поэтому люди пропускают чек. Три формулы, выполняемые после каждого прохода:

=COUNTA(UNIQUE(Region_clean))     distinct values, is it the number you expect?
=SUMPRODUCT(--(LEN(A2:A17)<>LEN(TRIM(A2:A17))))    rows still carrying stray spaces
=COUNT(Units)  against  =COUNTA(Units)             numbers against everything

В «грязной» копии после очистки количество отдельных регионов увеличилось с 6 до 4, а общее количество северных регионов вернулось к 2495. Эти две цифры являются доказательством. Без них колонка будет выглядеть чище, а это совсем не то же самое.

Когда значения различаются так, что ни одна функция не может совместить «ООО "Акме"» с «АКМЕ ЛИМИТЕД» с «Акме Ко», вы перешли от очистки к Разрешение объекта, это другая работа с другим методом.

Краевые корпуса, выдержавшие очистку

Шесть, которые прошли очевидные исправления.

Другой невидимый персонаж. Обычно используется значение 160, но пробелы нулевой ширины и указатели направления также перемещаются при копировании и вставке. Если LEN после замены 160 все еще слишком долго, используйте =CODE(MID(A2,n,1)) пройти и назвать то, что на самом деле там сидит.

Апострофы и кавычки, отличные от тех, которые вы набрали. Фигурный апостроф и прямой — это разные символы, поэтому «О’Брайен» из Слово никогда не совпадает с «О’Брайен» на вашей клавиатуре. Прежде чем сравнивать, замените одно на другое.

Ведущий апостроф, который вы не видите. Ввод текста '1001 заставляет Excel хранить текст и скрывать апостроф. LEN не считает это и ISTEXT сообщает об этом, поэтому существует числовая проверка в седьмом разделе.

Уборка, которая изменила смысл. TRIM свертывание внутренних прогонов подходит для имен и неправильно для всего, где интервал несет информацию, например, для кодов фиксированной ширины. Прежде чем применять его ко всей колонке, сверьте образец с источником.

Формулы, которые теперь указывают на необработанный столбец. Добавление чистого столбца ничего не дает, пока все последующие его не прочитают. Найдите в книге ссылку на старый столбец; наполовину переписанная рабочая тетрадь хуже, чем незавершенная, потому что обе половины расходятся во мнениях.

Текст, который выглядит числовым в одном месте, а не в другом. После неправильного импорта столбец может состоять наполовину из чисел, наполовину из текста, и в каждой формуле эти половины ведут себя по-разному. Сравнивать COUNT с COUNTA вместо чтения столбца и никогда не доверяйте выравниванию по правому краю как доказательству, потому что формат может его переопределить.