엑셀 문자추출 하기(배열수식)

배열수식에서 ROW함수는 순번, rangking, 정렬 등에 활용하기 위해 자주 사용되는 함수 중에 하나입니다.

오늘은 ROW 함수로 어떻게 문자를 추출하지 설명을 드립니다. 문자출출을 위해 MID 함수와 같이 사용하지만 배열수식을 설명 드리기 위해서는 ROW 함수에 포커스를 두겠습니다.

아래 예시는 1개의 배열수식에 들어있는 각각의 배열 값들이 어떻게 만들어지는지 설명 드리기 위해 행으로 나열하였으며, ROW 함수로 문자를 추출하기 위한 과정으로 이해하시기 바랍니다.

배열수식 이해하기(ROW 함수)

1. [수식1]은 MID 함수로 start_num 가 1~5까지 하나씩 증가하며 문자(숫자)를 하나씩 추출 한 것입니다.

2. [수식1]처럼 입력하면 각 셀마다 수식을 일일이 입력해겠죠?

3. 그래서 [수식2]와 같이 1~5를 ROW()-3 로 사용하면 간단히 수식을 복사해서 사용할 수 있습니다.

4. 예를 들어 4행에서는 ROW( ) - 3 = 4 - 3 = 1, 5행에서는 5 - 3 = 2, ... 8행에서는 5가 됩니다. 

1. [수식3] : [수식2]의 ROW( ) - 3 을 ROW(1:1) 으로 바꾸어 보겠습니다. 

2. ROW( ) - 3 을 하는 것 보다 더 이해가 쉽고 간단하죠?

3. [수식4]는 ROW(1:1)을 ROW(1:5)로 바꾸어 사용하였습니다. ROW(1:5) = {1;2;3;4;5}

4. E11셀에서 수식을 입력하고 Ctrl + Shift + Enter 하여 배열수식을 만들고 이후 드래그 해서 수식을 채워보니 ROW(1:5)의 범위가 1씩 커지는 것을 알수 있습니다.

5. 다행이 문자(숫자)는 정상적으로 추출되는것 같습니다.

6. 하지만 수식입력창에서 F9로 배열을 확인해보면 각셀의 배열이 다르다는 것을 알 수 있습니다.(바람직한 방법은 아님)

1. 5개의 행을 하나의 배열 수식으로 입력하려고 한다면 [수식4]와 같이 수식을 드래그 하지 말고 B18:B22 을 선택하고 F2를 누른후 Ctrl + Shift + Enter 하시면 [수식5]와 같이 모든 수식이 똑같이 입력되는 것을 알 수 있습니다.

2. 수식 양쪽에는 {=수식} 와 같이 { } 대괄호가 붙어 있습니다.

3. 수식 입력줄에서 F9로 배열을 확인해보면 모든셀의 수식에서 {"1";"2";"3";"4";"5"} 와 같은 배열을 확인 할 수 있습니다.

4. 이렇듯 배열수식은 1개의 수식에 많은 데이터(값)를 담을 수 있습니다.

5. [수식6]은 배열수식을 이름정의 하여 나타난 결과입니다.

배열수식 연습1 (숫자를 역순으로 바꾸기)

문자(숫자)를 하나씩 추출하는 방법을 알았으니 추출한 숫자를 다시 뭉쳐 보겠습니다.

단, 반대(12345 → 54321)로 뭉쳐 보겠습니다.

12345 = 1*10000 + 2*1000 + 3*100 + 4*10 + 5*1 입니다.

이걸 반대로 하면 54321 = 5*10000 + 4*1000 + 3*100 + 2*10 + 1*1 이 되겠죠!

여기서 규칙을 찾을 수 있습니다. 보이시죠?

수식 {=SUM(MID(B25,6-ROW(1:5),1)*10^(5-ROW(1:5)))}

☞ 수식설명

1. MID(B25,6-ROW(1:5),1) : 문자를 역순으로 추출하여 {"5";"4";"3";"2";"1"}의 배열을 반환 합니다.

2. 10^(5-ROW(1:5)) : {10000;1000;100;10;1}의 배열을 반환 합니다.

3. SUM({"5";"4";"3";"2";"1"}*{10000;1000;100;10;1}) : SUM(5*10000 + 4*1000 + 3*100 + 2*10 + 1*1)

배열수식 연습2 (주민등록번호 추출하고 검증하기)

조금더 고난위도 입니다. (클릭)

첨부파일 보기

ROW함수 활용(배열수식).xlsx