동적 배열로 요약 표 만들기

요약 표는 많은 원본 데이터 행을 작고 읽기 쉬운 블록으로 압축합니다. 범주별로 한 행씩 배치하고 옆에 합계를 표시하는 방식입니다. 수백 개의 행으로 이루어진 판매 기록이 각 지역과 해당 지역의 총매출을 보여 주는 깔끔한 표로 바뀌는 모습을 생각해 보세요.

예전 방식은 직접 만든 피벗 테이블을 새로 고쳐야 했습니다. 현대적인 방식은 데이터가 변경되는 즉시 자동으로 업데이트되는 동적 배열 수식을 사용합니다. 버튼도, 새로 고침도 필요 없습니다.

이 레슨에서는 세 가지 강력한 도구를 함께 사용합니다. UNIQUE로 범주를 나열하고, SUMIFS로 각 범주의 합계를 계산하며, FILTER로 일치하는 행을 가져옵니다. 이 세 가지를 함께 사용하면 실시간 요약 표를 만들 수 있습니다.

Sales라는 이름의 시트에 세 개의 열이 있다고 상상해 보세요. A열에는 지역, B열에는 제품, C열에는 금액이 있고, 데이터는 2행부터 200행까지 채워져 있습니다.

목표는 각 고유 지역과 해당 지역의 총매출을 보여 주는 요약 표를 만드는 것입니다. 첫 번째 과제는 지역을 직접 입력하지 않고 깔끔한 지역 목록을 만드는 것입니다. 나중에 새 지역이 추가될 수 있기 때문입니다.

• A2:A200에는 동부, 서부, 동부, 북부처럼 반복되는 지역 이름이 많이 들어 있습니다.
• 원하는 결과는 동부, 서부, 북부를 각각 한 번씩만 나열하는 것입니다.

이 고유 목록이 전체 요약 표의 기반이 됩니다.

UNIQUE 함수는 범위를 받아 각 값을 한 번씩만 반환합니다. 이 함수는 스필됩니다. 즉, 고유한 값의 개수만큼 하나의 수식이 여러 셀을 채웁니다.

E2 셀에 이 수식을 입력하면 지역 목록이 그 아래에 자동으로 나타납니다:

나중에 데이터에 새 지역을 추가하면 스필된 목록도 자동으로 늘어납니다. 수식을 직접 수정할 필요가 없습니다.

이제 E열에 있는 각 지역의 금액 합계가 필요합니다. SUMIFS는 다른 범위가 조건과 일치할 때만 한 범위의 값을 더합니다.

구조는 SUMIFS(sum_range, criteria_range, criteria)입니다. 첫 번째 지역 옆인 F2 셀에 다음 수식을 입력합니다:

E2# 참조가 핵심입니다. # 기호는 E2에서 시작하는 전체 스필 범위를 의미합니다. 따라서 이 수식 하나로 UNIQUE가 만들어 낸 모든 지역의 합계를 계산할 수 있습니다.

스필 참조인 E2#는 수식이 만들어 낸 전체 블록을 가리킵니다. 블록이 얼마나 커지든 항상 전체 범위를 가리키므로 요약 표를 동적으로 만들 수 있습니다.

UNIQUE가 지역 3개를 찾으면 E2#는 높이가 3셀인 범위를 가리키고 SUMIFS는 합계 3개를 반환합니다. 데이터가 지역 5개로 늘어나면 아무것도 수정하지 않아도 두 범위가 함께 확장됩니다.

• E2 = 맨 위의 단일 셀만 가리킵니다.
• E2# = E2에서 시작하는 스필 배열 전체를 가리킵니다.

# 기호에 익숙해지세요. 이 기호는 대시보드 수식의 핵심입니다.

합계를 정렬하면 요약 표를 더 쉽게 읽을 수 있습니다. 지역 목록을 SORT로 감싸 범주를 가나다순으로 표시하거나, 전체 표를 합계 기준으로 정렬할 수 있습니다.

E2에서 지역을 가나다순으로 나열하려면 다음과 같이 입력합니다:

F열의 합계는 여전히 E2#를 참조하므로 지역을 정렬하면 합계도 자동으로 다시 맞춰집니다. 두 열이 서로 어긋나지 않고 함께 유지됩니다.

때로는 합계만이 아니라 한 범주의 원본 행이 필요할 수 있습니다. FILTER는 조건을 충족하는 모든 행을 반환하고 결과를 스필합니다.

지역이 H1 셀의 값과 같은 모든 판매 행을 표시하려면 다음과 같이 입력합니다:

H1에 동부가 있으면 모든 동부 행이 표시됩니다. H1을 서부로 변경하면 블록이 즉시 새로운 결과로 다시 작성됩니다. 이것이 대시보드에서 상세 조회 화면을 만드는 기반입니다.

일치하는 항목이 없으면 FILTER는 #CALC! 오류를 발생시킵니다. 결과를 깔끔하게 유지하려면 선택적 세 번째 인수로 대체 메시지를 지정하세요.

세 번째 인수는 일치하는 항목이 0개일 때 표시됩니다:

이제 판매 내역이 없는 지역에는 오류 대신 알기 쉬운 안내가 표시됩니다. 대시보드에는 항상 이 대체 처리를 추가하여 잘못된 선택 하나 때문에 배치가 깨지지 않도록 하세요.

요약 표에는 금액뿐 아니라 각 지역의 주문 건수도 표시하는 경우가 많습니다. COUNTIFS는 조건과 일치하는 행의 개수를 셉니다. 합산할 범위가 없다는 점만 다를 뿐 SUMIFS와 비슷합니다.

합계 옆의 G열에 다음 수식을 입력합니다:

이제 세 개의 열로 구성된 요약 표에 지역, 총매출, 주문 수가 표시됩니다. 이 모든 결과는 E2#의 하나의 스필된 지역 목록을 기준으로 계산됩니다. 모든 결과가 함께 새로 고쳐집니다.

전체 수식을 나란히 정리하면 다음과 같습니다:

• E2: =SORT(UNIQUE(Sales!A2:A200))는 지역을 나열합니다.
• F2: =SUMIFS(Sales!C2:C200, Sales!A2:A200, E2#)는 각 지역의 합계를 계산합니다.
• G2: =COUNTIFS(Sales!A2:A200, E2#)는 각 지역의 개수를 셉니다.

행 방향으로 직접 입력하는 수식은 E2뿐입니다. F열과 G열은 # 참조를 통해 스필됩니다. Sales의 어느 곳에든 새 판매 내역을 추가하면 클릭하지 않아도 세 열이 모두 업데이트됩니다.

값을 직접 입력하거나 피벗 표를 새로 고치는 것보다 수식으로 만든 요약 표가 실제로 더 유리한 점이 많습니다:

• 실시간: 데이터가 변경되는 즉시 다시 계산됩니다.
• 자동 확장: UNIQUE와 # 참조를 통해 새 범주가 자동으로 나타납니다.
• 명확함: 누구나 셀 안의 논리를 읽을 수 있습니다.

다만 스필 범위가 확장될 빈 공간이 필요합니다. 다음 레슨에서 스필이 막히는 경우를 다루겠습니다. 지금은 수식 아래에 충분한 공간을 남겨 두세요.

스스로 업데이트되는 요약 표를 만드는 방법을 학습했는지 확인해 보세요.

스스로 관리되는 요약 표를 만들었습니다:

• UNIQUE는 각 범주를 한 번씩 나열하고 결과를 스필합니다.
• SORT는 읽기 쉽도록 목록을 정렬합니다.
• SUMIFS와 COUNTIFS는 E2# 스필 참조를 사용하여 각 범주의 합계를 계산하고 개수를 셉니다.
• FILTER는 상세 조회를 위해 일치하는 행을 가져오며, 일치하는 항목이 없을 때 표시할 대체 메시지도 지정할 수 있습니다.

모든 수식이 스필된 목록을 기준으로 작동하므로 새 데이터를 추가하면 수동 작업 없이 전체 요약 표가 업데이트됩니다. 다음에는 수식만으로 완전한 피벗 스타일 보고서를 다시 만들어 보겠습니다.