Общие формулы очистки данных в Excel

формулы Excel

В течение многих лет я использовал эту публикацию как ресурс, чтобы не только описывать, как что-то делать, но и вести записи, которые я могу найти позже! Сегодня у нас был клиент, который передал нам файл с данными о клиентах, который был катастрофой. Фактически каждое поле было искажено и; в результате нам не удалось импортировать данные. Хотя для Excel есть несколько отличных надстроек для очистки с помощью Visual Basic, мы запускаем Office для Mac, который не поддерживает макросы. Вместо этого мы ищем в помощь простые формулы. Я подумал, что поделюсь некоторыми из них здесь, чтобы другие могли их использовать.

Удалить нечисловые символы

Системы часто требуют, чтобы телефонные номера вводились в определенную формулу из 11 цифр с кодом страны и без знаков препинания. Однако люди часто вводят эти данные через тире и точки. Вот отличная формула для удаление всех нечисловых символов в Excel. Формула проверяет данные в ячейке A2:

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

Теперь вы можете скопировать полученный столбец и использовать Правка> Вставить значения для записи поверх данных с правильно отформатированным результатом.

Оценить несколько полей с помощью ИЛИ

Мы часто удаляем неполные записи из импорта. Пользователи не понимают, что вам не всегда нужно писать сложные иерархические формулы и что вместо этого вы можете написать оператор OR. В этом примере ниже я хочу проверить A2, B2, C2, D2 или E2 на отсутствие данных. Если какие-либо данные отсутствуют, я верну 0, в противном случае - 1. Это позволит мне отсортировать данные и удалить неполные записи.

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

Обрезать и объединить поля

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

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

Проверить действительный адрес электронной почты

Довольно простая формула, которая ищет как @, так и. на адрес электронной почты:

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

Извлечь имя и фамилию

Иногда проблема в обратном. В ваших данных есть поле с полным именем, но вам нужно разобрать имя и фамилию. Эти формулы ищут пробел между именем и фамилией и при необходимости получают текст. ИТ также обрабатывает, если нет фамилии или есть пустая запись в A2.

=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))

И фамилия:

=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")

Ограничьте количество символов и добавьте…

Вы когда-нибудь хотели очистить свои метаописания? Если вы хотите вывести содержимое в Excel, а затем обрезать его для использования в поле мета-описания (от 150 до 160 символов), вы можете сделать это, используя эту формулу из Мое место. Он чисто разбивает описание на пробел, а затем добавляет…:

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

Конечно, они не должны быть исчерпывающими ... просто несколько быстрых формул, которые помогут вам быстро начать работу! Какие еще формулы вы используете? Добавьте их в комментарии, и я буду благодарен вам за обновление этой статьи.

Как вы думаете?

Этот сайт использует Akismet для уменьшения количества спама. Узнайте, как обрабатываются ваши данные комментариев.