주소를 PNU로 만들기(2) (보완) - '산' 다음에 공백이 없는 경우

PNU만들기(완성).xlsx

0.59MB

 

1. 필지구분 구하기

가. Match 함수 이용하기

필지구분(산 또는 일반)을 구하는 수식이 =IF(C2="산*",2,1)라고 되어 있는데,

 

Match함수를 이용하면

=IF(ISNUMBER(MATCH("산*",C2,0)),2,1)라고도 할 수 있고,

=IF(ISERROR(MATCH("산*",C2,0)),1,2)라고도 할 수 있습니다.

 

그러나, =IF(ISERROR(MATCH("산",C2,0)),1,2)라고 *를 없애면 안 됩니다.

 

나. CountIf 함수 이용하기

=IF(COUNTIF(C2,"산*"),2,1)로 

Match 함수와 달리 IsError 함수나 IsNumber 함수를 사용하지 않고,

CountIfs 함수의 인수의 위치가 Match와 다르고,

If 함수의 참일 때 값과 거짓일 때 값의 위치가 바뀐 점이 다릅니다.

 

그러나,

=IF(COUNTIF(C2,"산"),2,1)라고

'산'다음의 *를 빼면 '산'이 있을 경우에도 2가 아니라 1로 표시되는 점은 같습니다.

 

2. 본번 구하기

본번을 네 자리로 만들기 위해 "000"과 본번을 결합한 후 Right함수를 이용해 오른쪽 4개를 가져오는 수식인데,

 

Text 함수를 이용해 숫자 네 자리로 만들 수 있니다.

구문은 TEXT(값, format_text)으로서

format_text로 문자열 형식을 정해줍니다.

다시 말해 Text 함수를 사용하면 숫자가 문자로 변환됩니다.

 

숫자는 0 또는 #을 사용해 표시할 수 있는데,

#을 사용하면 앞에 오는 0을 표시하지 않고, 0을 사용하면 앞이 0이더라도 표시하는 차이점이 있습니다.

 

아래와 같이 "####"이라고 표시하면

=TEXT(IF(ISNUMBER(FIND("-",C2)),LEFT(SUBSTITUTE(C2,"산",""),FIND("-",SUBSTITUTE(C2,"산",""))-1),SUBSTITUTE(C2,"산","")),"####")

숫자 111등 앞을 0으로 채우지 않습니다.

 

따라서, "###"을 "0000"으로 수정해야 합니다.

 

 

그러나, 본번의 경우는 숫자 한 자리는 반드시 있으므로 "000#"으로 해도 결과는 같습니다.

 

3. 부번 구하기

부번의 경우는 없는 경우도 있기 때문에 본번의 경우와는 다릅니다.

현재 수식은

=IF(ISNUMBER(FIND("-",C2)),RIGHT("000"&MID(C2,FIND("-",C2)+1,4),4),"0000")

라고 하이픈이 있으면 하이픈 위치 다음부터 4개를 가져온 후 "000"과 결합한 값에서 오른쪽 4개를 가져오고, 하이픈이 없다면 "0000"을 반환하는 것입니다.

 

이것을 Text 함수를 이용하면

=TEXT(IF(ISNUMBER(FIND("-",C2)),MID(C2,FIND("-",C2)+1,4),0),"0000")가 됩니다

 

하이픈이 있으면 하이픈 위치 다음부터 숫자 4개를 가져오고, 아니면 0을 반환한 다음 "0000" 형식으로 표시하는 것입니다.

 

부번은 없을 수도 있으므로 "000#"이라고 하면 안 되고,

If함수의 거짓일 때 값을 0이 아니라 ""이라고 하면 문자이기 때문에 숫자로 표시되지 않습니다.

 

4. &를 TextJoin 함수로 변경하기

TextJoin 함수의 구문은

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)로서

delimiter인 구분 기호 또는 문자, 여기서는 특별한 기호를 넣지 않으므로 빈칸으로 구면 됩니다.

 

ignore_empty는 빈 값(셀)을 무시하는지 여부인데, 마찬가지로 모두 값이 들어 있으므로 빈칸으로 둬도 됩니다.

 

text1, text2는 연결할 문자열을 나열하는 것입니다.

 

따라서, 수식은

=TEXTJOIN(

,

,

INDEX('D:\data\excel\[법정동코드 전체자료.xlsx]법정동코드 전체자료'!$A$2:$A$20555,MATCH(B2,'D:\data\excel\[법정동코드 전체자료.xlsx]법정동코드 전체자료'!$B$2:$B$20555,0)),

IF(COUNTIF(C2,"산*"),2,1),TEXT(IF(ISNUMBER(FIND("-",C2)),LEFT(SUBSTITUTE(C2,"산",""),FIND("-",SUBSTITUTE(C2,"산",""))-1),SUBSTITUTE(C2,"산","")),"000#"),

TEXT(IF(ISNUMBER(FIND("-",C2)),MID(C2,FIND("-",C2)+1,4),0),"0000")

)

이 됩니다.

 

만약 D2셀에서 G2셀 값을 연결한다면

=textjoin(,,d2:g2)라고 간단하게 쓸 수 있습니다.

 

5. &를 Concat  함수로 변경하기

Concat 함수의 구문은

CONCAT(text1, [text2],…)으로서

TextJoin함수와는 달리 구분 기호 또는 문자 (delimiter)를 넣을 수 없는 단점이 있습니다.

 

PNU만들기2(완성)(보완).xlsx

0.41MB