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

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

학년과 평균 조건을 만족하는 항목을 필터링합니다.

오빠두Tip : 다중조건 필터링 공식에 대한 설명은 이전 영상 강의에서 자세히 정리했습니다. 아래 영상 강의를 참고해주세요!
만약 출력할 범위를 '학년 ~ 영어'까지 일부 범위로 제한하려면, 아래와 같이 함수를 작성할 수 있습니다.
=FILTER(B3:E15,(B3:B15=J2)*(G3:G15>=L2))

연속된 범위일 경우, 일부 범위를 선택해서 출력할 수 있습니다.
하지만 연속된 범위가 아닌, 떨어진 범위를 필터링 결과로 출력해야 할 경우에는 간단한 트릭으로 해결할 수 있습니다. 작성한 FILTER 함수를 다시 FILTER 함수로 묶어서 특정 필드를 출력하면 됩니다. I4셀에 작성한 수식을 아래와 같이 수정하면, 선택한 범위에서 "학년, 이름, 평균" 필드만 출력됩니다.
=FILTER(FILTER(B3:G15,(B3:B15=J2)*(G3:G15>=L2)),{1,1,0,0,0,1})

떨어진 범위를 출력해야 할 경우, {1,0,1,0...} 으로 FILTER 함수를 한번 더 적용합니다.
필터링 결과에 머리글을 포함하는 방법 : 단, 방금과 같이 {1,0,1,0,,,} 으로 특정 필드를 출력할 경우, 필터링 결과에 머리글이 누락되는 문제가 있습니다. M365 버전을 사용중이라면, VSTACK 함수로 이 문제를 쉽게 해결할 수 있지만 엑셀 2021 에서는 VSTACK 함수가 제공되지 않아 다른 해결 방법이 필요합니다.
=VSTACK({"학년","이름","평균"},FILTER(FILTER(B3:G15,(B3:B15=J2)*(G3:G15>=L2)),{1,1,0,0,0,1}))

VSTACK 함수를 사용하면 FILTER 함수의 결과로 머리글을 포함할 수 있습니다.

오빠두Tip : VSTACK 함수를 활용해 FITLER 함수의 머리글까지 완벽하게 자동화하는 방법은, 위캔두 멤버십 라이브 강의 풀영상을 확인해주세요! 👇
오늘은 엑셀 2021 버전 기준으로, FILTER 함수만 사용해서 머리글 출력을 자동화하는 방법을 알아보겠습니다. 먼저 I3:N3 범위에 작성된 머리글을 지운 후, I4셀에 작성한 수식을 잘라내서 I3셀에 붙여넣기합니다.
기존에 작성된 머리글을 지운 후, 작성한 수식을 잘라내서 I3셀에 붙여넣기합니다.
이후 I3셀에 작성된 수식을 아래와 같이 수정합니다. 아래와 같이 수정하면, B3:B15 범위 중에서 값이 "학년"인 항목(=머리글)을 항상 표시할 수 있습니다.
=FILTER(FILTER(B3:G15,(B3:B15=J2)*(G3:G15>=L2)+(B3:B15="학년")),{1,1,0,0,0,1})

범위에서 머리글과 같은 조건을 덧셈으로 연산하여, FILTER 함수 결과에 머리글을 포함할 수 있습니다.

오빠두Tip : 단, 위와 같이 FILTER 함수의 결과로 머리글을 포함하면, 결과를 정렬했을 시 머리글이 함께 정렬되어 잘못된 결과가 반환되는 점을 주의하세요!
수식을 입력하면, FILTER 함수의 결과 값으로 머리글을 포함한 특정 필드의 데이터가 출력됩니다.
FILTER 함수의 결과로 머리글이 포함됩니다.

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

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

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

기존 수식에서 {1,0,1,0,...} 을 TRANPOSE(범위)로 수정합니다.

오빠두Tip : TRANSPOSE 함수를 작성하지 않으면 FILTER 함수의 결과로 #VALUE! 오류가 반환됩니다. 배열의 쉼표와 세미콜론으로 행/열을 구분하는 방법은 영상강의 11:20을 참고하세요!
이제 Q4:Q9 범위의 출력여부를 각각 1,0 으로 변경해서 출력 범위를 자동으로 갱신할 수 있습니다.
출력 여부에서 1과 0을 변경하면 출력 항목이 자동으로 갱신됩니다.
단어 포함 조건, 가로-세로 필터링 : 출력여부 범위에 단어의 포함여부를 확인하는 있어?(ISNUMBER/SEARCH) 공식을 적용하면, 단어 포함 조건으로 가로-세로 필터링을 완벽하게 자동화할 수 있습니다. Q4셀에 아래 공식을 입력한 후, 아래로 자동채우기합니다.
=ISNUMBER(SEARCH(P4,$N$2))*1

출력여부 범위에 ISNUMBER/SEARCH 공식을 작성합니다.
수식을 모두 입력한 후, N2셀에 출력할 항목을 작성하면 출력여부에 1과 0이 계산됩니다.
출력 항목에 출력할 머리글을 작성하면, 출력 여부가 1과 0으로 계산되며 FILTER 결과가 갱신됩니다.
이제 출력 항목에 머리글을 하나씩 작성해보세요! 가로-세로 필터링 보고서가 완성되었습니다.
가로-세로 필터링 보고서가 완성되었습니다.

5 1 투표

게시글평점

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

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

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

엑셀 가로세로 필터링 목차 바로가기

영상 강의

예제파일 다운로드

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

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

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