엑셀 가로-세로 필터링, 1페이지 보고서 작성 | 초보자 맞춤 완벽정리

by 오빠두엑셀

학습시간 14분
난이도 초급
작성일 2023.12.05

직장인을 위한, 엑셀 1페이지 보고서 작성 핵심 스킬! 가로-세로 필터링 공식의 동작 원리부터 고급 활용법까지, 13분 총정리 강의로 빠르게 확인하세요!🔥

이 강의에서는 엑셀 FILTER 함수만으로 출력 필드를 자유롭게 선택하고, 단어 검색 조건까지 더해 가로-세로 필터링이 모두 가능한 1페이지 보고서를 만드는 방법을 다룹니다. 함수 중첩과 TRANSPOSE, ISNUMBER/SEARCH 조합을 단계별로 정리해, 원본 데이터가 바뀌어도 결과가 자동으로 갱신되는 동적 보고서를 안정적으로 설계할 수 있습니다.

DOWNLOADS

실습자료를 준비했어요

수업에서 사용한 예제 파일과 보충 자료를 한 곳에 정리했습니다!👇

실습파일.xlsx

로그인 후 다운로드

엑셀 가로-세로 필터링, 1페이지 보고서 만들기 | 기초부터 활용까지

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.

FILTER 함수로 특정 필드만 출력하는 방법

엑셀 FILTER 함수는 데이터 분석과 보고서 작성에 자주 사용되는 동적 배열 함수입니다. 기본 동작은 지정한 범위의 모든 필드를 그대로 출력하는 방식이지만, 보고서 작성 단계에서 일부 필드만 선택해 표시해야 한다면 간단한 응용으로 해결할 수 있습니다.

=FILTER(FILTER(범위,조건),{1,0,1,0...})
'출력할 필드는 1, 숨길 필드는 0으로 입력합니다.

FILTER 함수로 특정 필드만 출력하기 : 예제파일을 다운로드한 뒤 [출력필드설정] 시트로 이동합니다. I4셀에 아래 수식을 입력하면, 학년이 동일하면서 평균 점수가 기준값 이상인 항목이 필터링됩니다.
=FILTER(B3:G15,(B3:B15=J2)*(G3:G15>=L2))

오빠두Tip : 다중조건 필터링 공식의 동작 원리는 이전 영상 강의에서 자세히 정리했습니다. 함께 확인하면 이번 강의의 응용 흐름을 더 빠르게 이해할 수 있습니다.
출력 범위를 '학년 ~ 영어' 구간처럼 일부 연속 범위로 제한하려면, FILTER 함수의 첫 번째 인수 범위만 좁혀서 다음과 같이 작성하면 됩니다.
=FILTER(B3:E15,(B3:B15=J2)*(G3:G15>=L2))
반면 연속되지 않은, 떨어진 필드를 골라 출력해야 하는 경우에는 FILTER 함수를 한 번 더 중첩하는 방법으로 해결할 수 있습니다. 작성한 FILTER 함수를 다시 FILTER 함수로 감싸 특정 필드만 선택하면 됩니다. I4셀의 수식을 아래와 같이 수정하면, 필터링 결과에서 "학년, 이름, 평균" 필드만 출력됩니다.
=FILTER(FILTER(B3:G15,(B3:B15=J2)*(G3:G15>=L2)),{1,1,0,0,0,1})
필터링 결과에 머리글을 포함하는 방법 : 다만 위와 같이 {1,0,1,0,…} 배열로 특정 필드만 출력하면, 필터링 결과에 머리글이 함께 표시되지 않는 한계가 있습니다. M365 버전에서는 VSTACK 함수로 손쉽게 해결할 수 있지만, VSTACK 함수가 제공되지 않는 엑셀 2021 이하 버전에서는 다른 접근이 필요합니다.
=VSTACK({"학년","이름","평균"},FILTER(FILTER(B3:G15,(B3:B15=J2)*(G3:G15>=L2)),{1,1,0,0,0,1}))

오빠두Tip : VSTACK 함수를 활용해 FILTER 함수 결과의 머리글까지 자동화하는 전체 흐름은, 위캔두 멤버십 라이브 강의 풀영상에서 단계별로 확인할 수 있습니다. 👇
이번 강의에서는 엑셀 2021 버전 기준으로, FILTER 함수만 사용해 머리글 출력을 자동화하는 방법을 정리합니다. 먼저 I3:N3 범위에 입력된 머리글을 모두 지운 뒤, I4셀의 수식을 잘라내어 I3셀에 붙여넣기합니다.
이제 I3셀의 수식을 다음과 같이 수정합니다. 이렇게 작성하면, B3:B15 범위 중 값이 "학년"인 항목(=머리글)이 항상 결과에 포함되도록 조건을 확장할 수 있습니다.
=FILTER(FILTER(B3:G15,(B3:B15=J2)*(G3:G15>=L2)+(B3:B15="학년")),{1,1,0,0,0,1})

오빠두Tip : 단, 위와 같이 FILTER 함수의 결과에 머리글을 포함시키면, 결과를 정렬할 때 머리글이 데이터와 함께 정렬되어 잘못된 결과가 반환될 수 있다는 점을 주의해야 합니다!
수식을 입력하면, FILTER 함수의 결과로 머리글이 포함된 특정 필드의 데이터가 출력됩니다.

FILTER 함수 가로-세로 필터링 자동화 공식

이전 강의에서 정리한 있어?!(ISNUMBER/SEARCH) 공식과 FILTER 함수의 특정 필드 출력 원리를 함께 활용하면, FILTER 함수만으로 가로-세로 필터링이 모두 가능한 자동화 공식을 완성할 수 있습니다. 특정 단어 포함 여부를 확인하는 있어?! 공식의 사용법은 이전 강의에서 자세히 다루었으니, 아래 링크에서 먼저 확인해주세요. 👇

가로-세로 필터링 공식 기초 : 시트에서 Q4:Q9 범위에 미리 작성된 필드 목록 옆에 출력여부를 1과 0으로 입력합니다. 이번 예제에서는 학년·이름·평균을 출력하도록 {1,1,0,0,0,1} 형태로 작성합니다.
앞 단계에서 I3셀에 작성한 수식 중 {1,0,1,0,…} 배열을 지운 뒤, Q4:Q9 범위를 직접 참조하고, TRANSPOSE 함수로 묶어 입력 범위를 세로에서 가로로 전환합니다.
=FILTER(FILTER(B3:G15,(B3:B15=J2)*(G3:G15>=L2)+(B3:B15="학년")),TRANSPOSE(Q4:Q9))

오빠두Tip : TRANSPOSE 함수를 적용하지 않으면 FILTER 함수의 결과로 #VALUE! 오류가 반환됩니다. 배열에서 쉼표와 세미콜론으로 행과 열을 구분하는 방법은 영상강의 11:20 부분을 참고하세요!
이제 Q4:Q9 범위의 출력여부 값을 1·0으로 바꾸기만 하면, 결과로 출력되는 필드 구성이 자동으로 갱신됩니다.
단어 포함 조건, 가로-세로 필터링 : 출력여부 범위에 단어 포함 여부를 판정하는 있어?(ISNUMBER/SEARCH) 공식을 적용하면, 단어 검색 조건만으로 가로-세로 필터링을 자동화할 수 있습니다. Q4셀에 아래 공식을 입력한 뒤, 아래로 자동채우기합니다.
=ISNUMBER(SEARCH(P4,$N$2))*1
수식을 모두 입력한 뒤, N2셀에 출력할 필드를 검색어로 입력하면 출력여부가 1과 0으로 자동 계산됩니다.
마지막으로 출력 항목 위에 머리글을 입력하면, 가로-세로 필터링이 모두 적용된 1페이지 보고서가 완성됩니다.