[Excel] 엑셀 필터 후 번호 차례대로 표시

데이터를 다룰 때 필터를 많이 사용합니다.

간혹 엑셀 필터 후 순번이 띄엄띄엄 나오는 것(Org 순번) 보다는 필터 된 상태에서 차례(보이는 대로 순번)로 표시되었으면 하는 경우가 생깁니다.

필터를 적용하기 전(왼쪽)과 후(오른쪽)의 예시입니다.

Org 순번 : 단순 숫자 입력 / 보이는 대로 순번 : 오늘 소개할 수식 입력

 

오늘의 함수 subtotal

SUBTOTAL은 필터링으로 숨겨진 행의 값들을 제외하고 계산하는 아주 유용한 함수입니다.

조건에 맞는 값으로만 필터링 한 뒤 평균값을 구하는 것 등이 가능하게 됩니다.

SUBTOTAL(function_num,ref1,[ref2],...)

유용한 function_num는 아래와 같습니다. 오늘은 셀 범위 중 텍스트가 있는 셀의 수를 반환하는 COUNTA 함수 기능인 3번을 사용할 예정입니다.

function_num	함수
1	AVERAGE
3	COUNTA
9	SUM

 

기능 구현 상세

저는 표(Excel Table)를 기본적으로 사용하기 때문에 수식을 구조적 참조(Structured Reference) 형식으로 소개해드립니다.

'보이는 대로 순번'이라는 열에 수식이 입력되고 열 전체에 동일한 수식이 적용됩니다. C3 셀을 선택하고 다음과 같이 입력하면 됩니다.

=SUBTOTAL(3,OFFSET(표1[[#머리글],[항목]],1,):[@항목])

• SUBTOTAL( ) : 필터 후 보이는 값들로만 계산하는 함수
• function_num : 3 (텍스트가 입력된 셀의 수를 세기 위해, COUNTA 함수로 기능토록 함)
• 참조 범위 : 아래 따로 설명드립니다.

참조 범위 구현 상세

컨셉

항목 A는 A에서 A까지 행을 Count 하여 1개로 계산코자 합니다. 항목 B는 A에서 B까지 행을 Count 하여 2개로 계산코자 합니다. 같은 방식으로 C, D..  즉, A는 항상 고정되고, 항목의 행이 바뀌면 count 할 범위는 점점 커집니다.

표의 일부를 절대 참조하는 방법

표는 행 주소를 항상 상대 참조(Relative reference)를 합니다. 즉, 수식이 입력된 행이 바뀌면 참조하는 셀의 행도 같이 바뀌는 거죠. 그래서 첫 번째 항목인 A로 고정하는 방법을 궁리해야 합니다.

 

표에서 행이 절대 참조가 되는 경우가 있는데, 바로 머리글입니다.

그래서 항목 중 A를 절대 참조하기 위해 우선 머리글을 참조한 뒤 offset 함수로 한 행 아래를 참조하도록 수정하는 방법을 사용했습니다.

OFFSET(표1[[#머리글],[항목]],1,)

• OFFSET() : 기준 셀에서 행, 열을 가감하여 참조 범위를 변경할 수 있는 함수
• 표 1 : 참조하고자 하는 표의 이름 (현재 표 이름에 맞게 입력하거나 마우스 클릭으로 수식 자동 입력하도록 함)
• [#머리글],[항목] : 머리글 중 '항목'만 참조 (마우스로 머리글 중 '항목'만 클릭하면 여기까지 자동 입력됨)
• 1 : 기준 셀 주소에서 한 행 아래 참조를 의미

셀 범위

셀 범위는 콜론 (:)으로 표현할 수 있습니다. A1:C1이라면 A1, B1, C1의 범위를 의미합니다.

고정될 참조 범위는 위 수식을 입력하고 콜론을 입력한 뒤 항목을 클릭해주면 참조 범위에 대한 수식이 완성됩니다.

OFFSET(표1[[#머리글],[항목]],1,):[@항목]

 

이상입니다.