스프레드시트 변환:5가지 동적 배열 함수(FILTER, UNIQUE, SORT)

동적 배열 기능은 가장 유용하고 강력한 기능 중 하나입니다. 수백 개의 행을 복사하는 복잡한 수식을 작성하는 대신 필요한 만큼의 셀에 결과를 자동으로 "분산"하는 하나의 수식을 작성합니다. 데이터가 변경되면 이러한 기능이 실시간으로 업데이트됩니다. 이를 통해 보고서와 요약이 더욱 효율적이고 오류 발생 가능성이 줄어듭니다.

이 튜토리얼에서는 동적 배열 함수(FILTER, UNIQUE, SORT)가 작업 방식을 변화시키는 5가지 방법을 보여줍니다.

Excel의 동적 배열 및 스필링

동적 배열: 셀에 수식을 삽입하면 Excel에서 자동으로 결과를 인접한 셀에 채우거나 쏟아 붓습니다. 결과 크기가 변경되면(행 수가 많거나 적음) 분산된 범위가 자동으로 늘어나거나 줄어듭니다. 다음과 같은 이유로 유출된 범위를 인식하게 됩니다:

• 수식은 왼쪽 상단 셀에만 있습니다.
• 다른 셀에는 밝은 테두리가 표시되며, 클릭하면 수식이 회색으로 표시됩니다.
• 전체 유출 범위를 A2#(해시 기호)로 참조할 수 있습니다.

동적 배열은 Microsoft 365(Microsoft 365용 Excel), Excel 2021 이상에서 사용할 수 있습니다.

1. 중복되지 않은 요약에 대한 고유 목록 생성

동적 배열 이전에는 중복 항목을 제거하려면 "중복 항목 제거" 또는 복잡한 수식이 필요했습니다. UNIQUE는 빠른 요약을 위해 유출 가능한 고유 목록을 만듭니다. 드롭다운, 유효성 검사 목록 및 피벗 없는 대시보드에 이상적입니다.
고유 지역 나열:

• 셀을 선택하고 다음 수식을 삽입하세요.

이 공식은 고유한 제품 목록을 유출합니다.

고유한 조합:
지역과 영업사원의 독특한 조합을 얻으세요:

그러면 모든 고유한 조합을 보여주는 2열 유출이 반환됩니다.

순 주문 수 계산:

• 요약을 보려면 UNIQUE와 COUNTA를 결합하세요.

이 공식은 총 고유 주문 수를 계산합니다. 데이터세트에 새 행을 추가하면 목록이 자동으로 새로 고쳐집니다.

정확히 한 번만 나타나는 값:
한 번만 발생하는 값을 찾습니다.

세 번째 인수(TRUE )는 반복되지 않는 값만 반환합니다.

데이터 유효성 검사에 UNIQUE 사용(드롭다운):

1. 드롭다운을 원하는 셀을 선택하세요.
2. 데이터 로 이동 탭>> 데이터 유효성 검사를 선택합니다. .
3. 허용 설정 목록에 .
4. 소스에서 , 다음을 입력하세요:

이제 드롭다운에는 항상 데이터를 기반으로 현재 고유 지역이 표시됩니다.
UNIQUE를 사용한 그룹화된 요약:
UNIQUE와 SUMIF를 결합하여 동적 그룹화된 요약을 작성할 수 있습니다.
다양한 지역의 총 수익을 계산하세요:

=SUMIF(B2:B61, I15#, G2:G61)

이 수식은 유출된 영역(I15#)의 전체 목록을 나타냅니다. ). SUMIF는 각 지역의 총 수익을 반환합니다. 매출에서 행을 추가하거나 변경하면 지역과 합계가 자동으로 조정됩니다.

이제 지역을 변경하면 선택 사항에 따라 총 수익이 자동으로 업데이트됩니다.

2. 동적 보고서의 데이터 자동 필터링

기존 필터링에는 수동 필터나 복잡한 수식이 필요합니다. FILTER() 함수는 동적 배열을 위한 가장 강력한 도구 중 하나입니다. 조건에 맞는 행만 추출하고 결과를 표와 같은 범위로 표시합니다. 이 기능을 사용하면 즉시 업데이트되는 보고서를 작성할 수 있습니다.
지역별 판매 필터링:

• FILTER 함수의 동적 동작을 표시하려면 해당 지역에 대한 드롭다운을 사용하세요.

=FILTER(A2:G61, B2:B61="East")

• 더 동적으로 만들려면 드롭다운에서 기준 셀을 참조하세요.

=FILTER(A2:G61, B2:B61=I4)

이렇게 하면 지역이 "동부"인 모든 행이 유출됩니다. 데이터를 추가하거나 제거하면 늘어나거나 줄어드는 미니 리포트입니다. I4 변경 "North"로 변경하면 보고서가 자동으로 업데이트됩니다. VBA나 수동 새로 고침이 필요하지 않습니다.

이렇게 하면 데이터의 정적 복사본이 제거됩니다. 보고서에는 항상 출처가 반영됩니다.
여러 기준:
동부 지역 및 $1,000 이상의 금액으로 필터링:

=FILTER(A2:G61, (B2:B61="East")*(G2:G61>1000), "No matches")

별표(* )는 AND로 작동합니다. 더하기(+)를 사용할 수 있습니다. ) OR 조건의 경우.
사용자가 드롭다운에서 기준을 선택하는 대화형 대시보드를 구축할 수 있습니다.

3. SORT() 및 SORTBY()를 사용하여 자동으로 정렬된 목록 만들기

정렬이란 데이터를 복사하거나 테이블을 사용하는 것을 의미하는 데 사용됩니다. SORT는 동적이고 유출 가능한 정렬 뷰를 생성합니다. 데이터세트를 추가, 제거, 업데이트할 때마다 자동으로 데이터가 정렬됩니다.
자동 정렬된 판매 리더보드:

이 수식은 전체 데이터 범위를 7열(금액)을 기준으로 내림차순(-1)으로 정렬합니다. ). 원본 데이터는 그대로 유지됩니다. 새로운 최고 판매를 추가하면 자동으로 올바른 위치에 나타납니다.

다양한 정렬 수준:
영업사원을 기준으로 정렬한 다음 금액을 기준으로 정렬:

=SORT(A2:G61, {3,7}, {1,-1})

중괄호는 배열을 만듭니다. 즉, 3열을 오름차순으로 정렬한 다음 7열을 내림차순으로 정렬합니다.

다른 기준으로 정렬:
SORTBY 함수를 사용하면 다른 범위의 값을 기준으로 한 범위를 정렬할 수 있습니다.
모든 열을 유지하면서 영업사원 이름으로 전체 범위를 정렬하세요:

=SORTBY(A2:G61, C2:C61, 1)

4. 타겟 고유 요약을 위해 FILTER와 UNIQUE 결합

고급 요약의 경우 함수를 결합하여 먼저 필터링한 다음 단일화하여 깔끔한 자동 업데이트 목록을 제공할 수 있습니다. 기능을 결합하여 자체적으로 유지되는 보고서를 만듭니다.

• 셀을 선택하고 다음 수식을 삽입하세요.

=UNIQUE(FILTER(D2:D61, B2:B61="North"))

북부 지역의 제품을 필터링한 다음 고유한 제품을 유출합니다.

• 다음으로 SORT 함수를 추가하여 요약을 정렬합니다.

=SORT(UNIQUE(FILTER(D2:D61, B2:B61="North")))

이제 수식을 통해 정렬된 고유한 제품 목록이 표시됩니다.

이는 고유하게 필터링된 목록에 대해 {=INDEX(…)}와 같은 번거로운 배열 수식을 대체합니다. 데이터나 기준을 변경하면 지역별 제품 재고와 같은 보고서에 대한 업데이트가 원활하게 제공됩니다.

5. 동적, 기준 중심 요약 페이지(FILTER, UNIQUE, SORT 결합)

이제 이러한 기능을 몇 가지 기준 셀에서 업데이트되는 미니 요약/보고 페이지로 결합합니다.
지역 수준 대시보드를 구축하세요:지역 드롭다운(UNIQUE로 구동), 필터링된 지역 목록(FILTER), 지역별 상위 제품 목록(FILTER + SORT).

1단계:UNIQUE를 사용한 지역 드롭다운

고유한 지역 목록이 생성되어 드롭다운을 만드는 데 사용되었습니다.

2단계:지역별 판매 세부정보

=FILTER(SalesData!A2:G61, SalesData!B2:B61=B4, "No sales in this region")

이 수식은 지역을 기준으로 판매 데이터를 필터링합니다. 드롭다운에서 지역을 변경하면 판매 테이블이 자동으로 업데이트됩니다.

3단계:선택한 지역의 인기 제품

선택한 지역에서 어떤 제품이 가장 많이 팔리는지 확인하세요.

• 제품 및 총 수익 헤더가 포함된 작은 테이블을 만듭니다.
• L4에서는 선택한 지역에서 판매되는 고유 제품을 가져옵니다.

=UNIQUE(FILTER(SalesData!D2:D61, SalesData!B2:B61=B4))

그러면 해당 지역의 제품 목록이 유출됩니다.

• M4에서 해당 지역의 제품당 총 수익을 계산합니다.

=SUMIFS(SalesData!G2:G61, SalesData!B2:B61, B4, SalesData!D2:D61, L4#)

그러면 L4#의 각 제품과 일치하는 총 수익의 유출된 목록이 반환됩니다. .

• 수익별로 정렬(내림차순)된 항목을 표시하려면 유출된 두 열을 함께 정렬하세요.

=SORT(CHOOSE({1,2}, L4#, M4#), 2, -1)

여기 CHOOSE({1,2}, L4#, M4#) 2열 배열(제품 및 총 수익)을 작성합니다. 2 "두 번째 열(총 수익)을 기준으로 정렬"을 의미합니다. -1 내림차순을 의미합니다.

[선택한 지역] 보고서의 동적 상위 제품:

• B4에서 지역 드롭다운을 변경합니다. 모든 요약이 업데이트됩니다.
• 판매에 새 데이터를 추가하면 보고서에 추가됩니다.
• 복사된 수식, 수동 정렬, 피벗 테이블 새로고침이 없습니다.

결론

이 튜토리얼에서는 동적 배열 함수(FILTER, UNIQUE, SORT)가 작업 방식을 변경하는 5가지 방법을 보여줍니다. 동적 배열 기능은 스프레드시트를 유지 관리하는 바쁜 작업을 제거합니다. 수식을 복사하고 깨진 참조를 수정하는 대신 분석에 집중합니다. 보고서는 자동으로 업데이트됩니다. 대시보드는 자동으로 업데이트됩니다. 이러한 기능을 사용하기 시작하면 요약 및 대시보드에 이상적이라는 것을 알게 될 것입니다. 수동 새로 고침, 복잡한 배열 수식 및 도우미 열 없이 즉시 업데이트되는 보고서를 작성할 수 있습니다.