중괄호 안에 값을 넣어 배열 만들기 (3)

나. 간격이 불규칙적인 경우 공백 넣기

배열수식3.xlsx

0.01MB

 

 

아래와 같이 주소를 붙여서 쓴 경우 도, 시, 구, 길, 로를 기준으로 나눈 다음 공백을 넣어보려고 합니다.

 

(1) 위치를 암산으로 계산해서 넣는 경우

B2셀에 Mid 함수를 이용해서 수식을 입력하는데,

문자열, 시작위치, 개수이므로

=mid(a2,{1,6,8,12},{5,2,4,10})라고 입력할 수 있습니다.

 

개수를 입력할 때 네 번째 10은 개수를 크게 10이라고 준 것으로 있는 문자열의 길이보다 길면 되므로 위의 경우는 2라고 줘도 됩니다. 그러나, 10으로 주면 문자의 개수를 셀 필요가 없으므로 편합니다.

 

'서울특별시중구덕수궁길15'가 서울특별시, 중구, 덕수궁길, 15로 잘 분리되었습니다.

 

이제 TextJoin 함수를 이용해 문자열을 결합하는데, 중간에 공백을 넣으면 됩니다.

=TEXTJOIN(" ",,MID(A2,{1,6,8,12},{5,2,4,10}))

 

그런데 이렇게 하는 것의 문제점은 간격이 모두 다르기 때문에

B2셀의 채우기 핸들을 더블 클릭하면 원하는 결과가 나오지 않는 것입니다.

 

(2) 위치를 수식으로 계산해서 넣기

 

(가) 분리할 기준 문자의 위치 찾기

 

① Find 함수로 분리할 기준 문자의 위치 찾기

분리할 기준 문자는 "도", "시", "구", "길", "로"입니다.

먼저 B2셀에서 B5셀까지 지우고,

B2셀에

=find({ '도', '시', '구', '길', '로'},a2)

라고 입력하면

 

#VALUE!, 5, 7, 11, #VALUE!가 표시되는데,

첫 번째 #VALUE!는 "도"란 글자를 찾을 수 없다는 의미이고,

두 번째부터 네 번째까지의 숫자는

"시', "구", "길"의 위치이고, 

다섯 번째 #VALUE!는 "로"란 글자를 찾을 수 없다는 의미입니다.

 

② #VALUE!를 없애고, 순서대로 정렬하기

#VALUE!는 IfError함수를 이용해 공백으로 바꿀 수 있고,

공백, 5, 7, 11, 공백에서 5, 7, 11을 빼내는 것을 글자가 작은 순으로 추출해야 하므로 Small 함수를 사용할 수 있습니다.

=SMALL(IFERROR(FIND({"도","시","구","길","로"},A2),""),{1,2,3,4})

 

Small 함수의 두 번째 인수인 순서도 {1,2,3,4}로 중괄호를 이용해서 배열로 입력했습니다.

 

그런데, 문제는 최대 개수 4를 기준으로 4까지 입력했더니 네 번째 값이 #NUM!로 반환된다는 것입니다.

그렇다면 Sequence함수를 이용해 수식을 입력할 때 column의 개수인 columns를 숫자의 개수로 입력하면 됩니다.

 

숫자는  IFERROR(FIND({"도","시","구","길","로"},A2),"")로 구하고,

숫자의 개수이므로 앞에 Count를 붙이면 되므로

 

전체 수식은

sequence(,count( IFERROR(FIND({"도","시","구","길","로"},A2),""))

가 됩니다.

두 번째 인수인 열의 개수만 입력하고, Sequence 함수의 첫 번째 인수의 행의 개수 1과, 세 번째 인수인 시작값 1, 그리고 간격 1은 생략가능하므로 생략한 것입니다.

 

이것을 {1,2,3,4} 자리에 넣으면

=SMALL(IFERROR(FIND({"도","시","구","길","로"},A2),""),SEQUENCE(,COUNT(IFERROR(FIND({"도","시","구","길","로"},A2),""))))

가 됩니다.

 

원하는 위치인 5, 7, 11이 잘 구해졌습니다.

 

B2셀의 채우기 핸들을 B5셀까지 끌면

원하는 값이 모두 구해집니다.

 

(나) Mid 함수의 시작 위치 구하기

A2셀을 기준으로 하면 시작위치는 1,6,8,12가 되고,

A5셀을 기준으로 하면 1,4,7,10,13이 됩니다.

 

① 열을 삽입하고 0 입력하기

이것은 찾은 위치에 1을 더한 값인데 1을 구할 수 없으니

B열을 선택한 후 마우스 오른쪽 버튼을 누른 후 삽입을 눌러 

 

열을 추가하고, 모두 0을 채웁니다.

 

0을 채울 때 B2셀에 0을 입력하고, 복사해서 붙여 넣기를 할 수도 있지만,

B2셀에서 B5셀을 선택한 다음 B2셀에 0을 입력하고 Ctrl + Enter 키를 눌러도 됩니다.

 

② 1을 더해서 시작위치 구하기

 

열 너비가 숫자의 길이에 비해 너무 넓으므로 B열부터 Q열까지 선택한 후 마우스 오른쪽 버튼을 누르고, 열 너비를 2.5로 입력한 후 확인 버튼을 눌러 좁힙니다.

 

G2셀에 =B2+1이라고 입력하고, G2셀의 채우기 핸들을 K2열까지 끌면

K2셀의 값이 1이 되는데, 그러면 A2셀의 문자열을 모두 가져오므로 J2셀의 값에 넉넉한 숫자인 10을 더한 값으로 합니다.

 

그런데 조건이 있어야 하므로

if(len(b2),b2+1,a2+10)이라고 해서 "b2셀의 길이가 0보다 크면"이라는 조건을 줍니다.

len(b2)라고 줬지만 0만 False이고, 0이 아니면 True이므로 길이가 0보다 크면(또는 0이 아니면)이 되는 것입니다.

 

숫자가 있으면 +1을 한 값이 구해지고, 없으면 왼쪽 셀인 E2셀의 값에 10을 더한 값이 표시됩니다.

 

다시 위 수식을 G2셀에 넣고, K2셀까지 끕니다.

 

그러면 K4셀까지는 E열의 값이 10을 더한 값이 구해지고,

K5셀의 경우는 F5셀에 값이 있으므로 여기에 1을 더한 13이 구해졌습니다.

 

Mid함수의 세 번째 인수인 가져올 문자의 개수를 구하는 것부터는 다음 편에서 다루도록 하겠습니다.

 

배열수식3-1.xlsx

0.01MB