[엑셀] 특정 문자와 일치하는 행 모아서 추출하기

왼쪽 표에서 Type이 c인 것만 모아 오른쪽처럼 별도의 표를 만들려면 어떻게 해야 할까?

가장 간단한 것은 원본 표에 필터를 거는 방법인데

가끔은 필터를 걸 수 없는 상황도 있다. 

예를 들어 2018/03/06 - [엑셀] - [엑셀] 다른 시트로부터 내용 가져오기 Importrange 에 나온 것 처럼 다른 시트에서 내용을 가져올 경우

최초 필터는 작동하지만, 이후 원본에 새로 기입된 데이터는 필터의 적용을 받지 않는 불편함이 있다.

우리가 오늘 다룰 수식에는 배열수식과 index, small 등의 함수가 사용되며 그리 간단하지는 않다. 차근차근 가 보자.

일단 Type 열만 생각해 보면, 각각 C5, C8, C11 을 가져와야 함을 알 수 있다.

예상하듯, 5, 8, 11 의 숫자를 적절히 반환할 수 있는 수식을 만들고, 이것을 C5, C8, C11 로 인식시키면 되겠다.

첫 단계로 Type 이 c인 것들만 모아 배열수식을 만들어 보자.

(배열수식이 처음이라면 https://lightblog.tistory.com/12 를 참고)

=IF(C1:C12="c", C1:C12)

C1:C12 로 지정된 영역은 나중에 C:C 로 간단히 적을 것인데, 지금은 배열수식의 결과를 눈으로 확인하기 위해 일단 좁은 범위만 설정하였다.

이제 2017/08/22 - [엑셀] - [엑셀] 수식의 부분 결과 검증하기 F9 에 나온대로 F9를 누르면

위와 같이 Type 이 "c" 인 곳만 "c" 라는 문자열이 반환되고 있는 것을 볼 수 있다.

우리에게 필요한 것은 "c"라는 문자열이 아니라 그곳의 행번호이므로, 배열수식의 두 번째 인자를 다음과 같이 ROW(C1:C12) 로 입력해 주자.

=IF(C1:C12="c", ROW(C1:C12))

ROW(C1:C12) 는 말하자면 {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12} 의 숫자 집합인 셈이고,

앞에 있는 IF 와 조합하면 그 중에서 C가 있는 곳의 번호만 반환해 줄 것이다. F9를 누른다.

Type 이 "c" 인 곳의 행번호 5, 8, 11이 보인다.

FALSE 는 사실 없는 값이므로, 여기까지 숫자의 집합은 {5, 8, 11} 이라고 보면 되겠다.

커서가 위치한 I3 에는 그중 5가 반환되어야 하므로, 집합 중 가장 작은 값을 반환하라는 SMALL() 함수로 다음과 같이 둘러싸 준다. 

1은 첫 번째로 작은 값을 의미한다.

=SMALL(수식, 1)

5라는 값이 나온다. 5행을 의미할 것이다.

배열수식이므로 Ctrl + Shift + Enter 를 잊지 않는다.

여기서 SMALL의 두 번째 인자는 행이 증가함에 따라 1, 2, 3... 으로 바뀌어야 하므로

1이라는 숫자로 고정해 두지 않고 ROW() 를 이용해 다음과 같이 바꾸어 준다.

(수식을 적는 행이 3행이므로 ROW()-2 가 된다. 4행에 적는다면 ROW()-3 이 되어야 할 것이다. 혹은 ROW(A1) 등으로 적어도 무방하다.)

=SMALL(IF(C1:C12="c", ROW(C1:C12)), ROW()-2)

이제 5라는 값을 C5로 바꾸기 위해 

2018/06/13 - [엑셀] - [엑셀] 최초로 0이 아닌 값이 나오는 곳 찾기 INDEX() MATCH()에 나온 INDEX()를 활용해

"C열의 5번째 행" 임을 다음과 같이 알려준다.

=INDEX(C:C, SMALL(IF(C1:C12="c", ROW(C1:C12)), ROW()-2))

c라는 값으로 변환되고 있다. 

이제 C1:C12 를 간단히 C:C로 바꾸자.

여기에서 C1:C12="c" 부분은 조건 판별의 기준으로, 수식 복사시 셀이 달라져서는 안되므로 F4를 눌러 다음과 같이 고정한다.

ROW(C:C) 는 단지 {1, 2, 3, 4, 5...}의 수열을 의미하므로 고정하지 않아도 무방하다.

=INDEX(C:C, SMALL(IF($C:$C="c", ROW(C:C)), ROW()-2))

완성이다. 다른 셀에도 채워 보자.

동일한 수식으로 Type c 에 해당하는 행을 잘 불러오는 것을 볼 수 있다.

다만 이것을 한 행 더 확장하면 다음과 같이 에러가 나는데

이는 No. 9 다음에 Type c 인 행이 더 이상 존재하지 않는다는 의미이다.

값이 있을 경우에만 표기하도록 하려면 IFERROR() 를 활용한다.

=IFERROR(INDEX(C:C, SMALL(IF($C:$C="c", ROW(C:C)), ROW()-2)), "")

수식은 적용되어 있으나 값이 없는 경우에는 표시하지 않는다.

만일 원본 데이터에 Type c 인 행이 하나 추가되면

위와 같이 자동으로 해당 행을 추가하게 된다.

혹 이렇게 추가되는 행에 테두리를 자동으로 생성하려면 

2016/10/05 - [엑셀] - [엑셀] 내용 입력시 자동으로 테두리 연장하기 (조건부 서식)에 나온 내용을 참고하자.

원본 데이터가 다른 시트에 있을 경우의 예제는

2018/04/17 - [엑셀] - [엑셀] 특정 문자와 일치하는 행 모아서 다른 시트로 추출하기에서 확인할 수 있다.

셀이 특정 문자와 일치하는 경우가 아니라 특정 문자를 포함하는 경우의 예제는 

2018/10/16 - [엑셀] - [엑셀] 특정 문자가 들어있는 행 모아서 추출하기에서 확인할 수 있다.

---

혹시 구글시트(혹은 오피스365 이상)를 사용할 수 있는 환경이라면

FILTER() 함수를 사용하는 편이 낫겠다.