공통 데이터 정리를 위한 Excel 수식

Excel 데이터 정리 수식

수년 동안 나는 일을하는 방법을 설명 할뿐만 아니라 나중에 찾아 볼 수 있도록 기록을 유지하기 위해이 출판물을 리소스로 사용했습니다! 오늘 우리는 재난이었던 고객 데이터 파일을 우리에게 건네 준 고객이있었습니다. 거의 모든 필드의 형식이 잘못되었습니다. 그 결과 데이터를 가져올 수 없습니다. Visual Basic을 사용하여 정리를 수행 할 수있는 Excel 용 추가 기능이 있지만 매크로를 지원하지 않는 Mac 용 Office를 실행합니다. 대신 우리는 도움이 될 직접적인 공식을 찾습니다. 다른 사람들이 사용할 수 있도록 여기에서 일부를 공유 할 것이라고 생각했습니다.

숫자가 아닌 문자 제거

시스템에서는 종종 구두점없이 국가 코드를 사용하여 특정 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로 여러 필드 평가

가져 오기에서 불완전한 레코드를 종종 제거합니다. 사용자는 복잡한 계층 구조 수식을 항상 작성할 필요가 없으며 대신 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에 빈 항목이있는 경우에도 IT가 처리합니다.

=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을 사용하여 스팸을 줄입니다. 댓글 데이터 처리 방법 알아보기.