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

2018/03/07 - [엑셀] - [엑셀] 특정 문자가 들어있는 행 모아서 추출하기의 내용을 토대로

자료가 다른 시트에 있을 경우 어떻게 해야 하는지 예제로 살펴보자.

Sheet1에 다음과 같이 자료가 있고

그중 거래처가 A인 것만 모아서 Sheet2 에 표시하려면

Sheet2에서 일단 다음과 같이 시작하면 되겠다.

=IF(Sheet1!E:E="A", ROW(A:A))

배열수식이므로 Ctrl + Shift + Enter를 누른다.

이제 위 배열은 거래처가 A인 행의 행번호를 포함하고 있을 것이므로 Small 함수로 첫 번째 값을 추출한다.

=SMALL(IF(Sheet1!E:E="A", ROW(A:A)), 1)

수식이 변경될 때는 항상 Ctrl + Shift + Enter를 눌러야 수식이 정상적으로 작동한다.

2라는 값은 Sheet1의 2행을 의미할 것이다. 

Small 함수의 두 번째 인자는 행이 증가할 수록 1, 2, 3... 과 같이 증가해야 하므로 ROW()-1 로 바꾸어 적어준다.

=SMALL(IF(Sheet1!E:E="A", ROW(A:A)), ROW()-1)

이제 2라는 값을 Sheet1 의 A2로 바꾸어 주어야 하므로

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

Sheet1!A:A 열의 2번째 행이라고 위치를 지정해 주자.

=INDEX(Sheet1!A:A, SMALL(IF(Sheet1!E:E="A", ROW(A:A)), ROW()-1))

Sheet1 의 A2 에 해당하는 값(1)이 반환된다. 

이제 고정할 것을 고정하고 다른 셀에도 채워 보자.

Sheet1!E:E="A" 에서 기준이 되는 E:E 열은 셀이 바뀌어도 고정되어야 하므로 F4를 눌러 고정해 준다.

=INDEX(Sheet1!A:A, SMALL(IF(Sheet1!$E:$E="A", ROW(A:A)), ROW()-1))

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

동일한 수식으로 거래처 A에 해당하는 각 항목을 잘 불러오는 것을 볼 수 있다.

에러메시지는 넘버 15 이후에 거래처 A인 항목이 더 이상 없다는 의미이며,

값이 있을 경우에만 표시하게 하려면 IFERROR() 함수를 활용한다.

=IFERROR(INDEX(Sheet1!E:E, SMALL(IF(Sheet1!$E:$E="A", ROW(E:E)), ROW()-1)) ,"")

마지막으로 내용이 있는 곳에만 테두리를 그리려면 

원하는 영역(A:E)을 선택하고 [조건부서식] - [새 규칙] 으로 들어간다.

2016/10/05 - [엑셀] - [엑셀] 내용 입력시 자동으로 테두리 연장하기 (조건부 서식)의 내용과 같이

[수식을 사용하여 서식을 지정할 셀 결정] 항목에 =LEN(A1)>0 을 입력하고 테두리를 설정해 준다.

내용이 입력된 영역만 테두리가 그려진다.

---

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

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