수식의 집계
이 항목에서는 집계에 대해 소개하고 PowerPivot for Excel에서 사용 가능한 집계의 유형에 대한 개요를 제공합니다. PowerPivot for Excel에는 집계를 작성하는 데 사용할 수 있는 다음과 같은 도구가 포함되어 있습니다.
PowerPivot 데이터를 기반으로 하는 피벗 테이블과 피벗 차트를 작성할 수 있습니다. Excel 피벗 테이블은 워크시트의 데이터를 그룹화 및 요약하는 데 자주 사용되는 도구입니다. PowerPivot은 Excel의 피벗 테이블 기능과 통합되어 많은 향상된 기능을 제공합니다.
DAX 수식 언어를 사용하여 사용자 지정 집계를 디자인할 수 있습니다. DAX를 사용하여 PowerPivot 테이블에서 계산 열을 만들고 피벗 테이블이나 피벗 차트에서 측정값을 생성할 수 있습니다.
이 항목의 마지막 섹션에서 집계를 작성하는 자세한 방법에 대한 링크를 제공합니다.
집계 소개
집계는 데이터를 축소, 요약 또는 그룹화하는 방법입니다. 테이블이나 다른 데이터 원본의 원시 데이터로 시작할 경우 플랫 데이터인 경우가 많습니다. 이는 많은 세부 항목이 있지만 어떤 식으로도 그룹화되거나 구조화되어 있지 않다는 의미입니다. 이 경우 구조나 개요가 부족하므로 데이터에서 패턴을 검색하기가 어렵습니다. 따라서 분석에서 수행해야 하는 가장 중요한 작업은 특정 비즈니스 질문에 답변하기 위해 패턴을 단순화하거나, 추상화하거나, 요약하는 집계를 정의하는 작업입니다.
집계에 사용할 그룹 선택
데이터를 집계하는 경우 제품, 가격, 지역 또는 날짜와 같은 특성을 기준으로 데이터를 그룹화한 다음 그룹의 모든 데이터에 적용되는 수식을 정의합니다. 예를 들어 연도별 합계를 만들 때 집계를 만듭니다. 그런 다음 이전 연도에 대한 금년의 비율을 만들고 이를 백분율로 표시하는 경우에는 다른 유형의 집계를 만듭니다.
데이터를 그룹화하는 방법은 비즈니스 질문에 따라 결정됩니다. 예를 들어 집계는 다음과 같은 질문에 답변하는 데 사용될 수 있습니다.
개수 한 달에 몇 개의 트랜잭션이 있었습니까?
평균 이 달의 판매 사원별 평균 판매량은 어느 정도였습니까?
최소값 및 최대값 판매된 단위 수를 기준으로 상위 5개 영업 지역은 어디였습니까?
이러한 질문에 답할 수 있는 계산을 만들려면 개수나 합계를 구할 수치가 포함된 상세 데이터가 있어야 하며 결과를 구성하는 데 사용할 그룹에 숫자 데이터가 어떤 식으로든 연관되어야 합니다.
데이터에 그룹화하는 데 사용할 수 있는 제품 범주나 상점이 위치한 지역 이름 등의 값이 없을 경우 범주를 추가하여 데이터에 그룹을 생성할 수 있습니다. Excel에서 그룹을 만들 경우 사용하려는 그룹을 워크시트의 열에서 선택하거나 직접 입력해야 합니다.
그러나 관계형 시스템에서는 제품 범주 등의 계층이 팩트 또는 값 테이블과는 다른 테이블에 저장되는 경우가 많습니다. 일반적으로 범주 테이블은 일종의 키로 팩트 데이터에 연결됩니다. 예를 들어 데이터에 제품 ID가 포함되어 있지만 제품 이름이나 범주는 없다고 가정해 봅니다. 플랫 Excel 워크시트에 범주를 추가하려면 범주 이름을 포함하는 열에서 복사해야 합니다. 하지만 PowerPivot 통합 문서에서는 제품 범주 테이블을 통합 문서로 가져와서 수치 데이터가 포함된 테이블과 제품 범주 목록 간에 관계를 만든 다음 범주를 사용하여 데이터를 그룹화할 수 있습니다. 자세한 내용은 테이블 간 관계를 참조하십시오.
집계에 사용할 함수 선택
사용할 그룹화를 식별 및 추가한 후에는 집계에 사용할 수치 연산 함수를 결정해야 합니다. 집계라는 단어는 합계, 평균, 최소값, 개수와 같이 집계에 사용되는 수치 연산 또는 통계 연산의 동의어로 사용되는 경우가 많습니다. 그러나 PowerPivot for Excel에서는 Excel에 있는 표준 집계 외에 집계를 위한 사용자 지정 수식을 만들 수 있습니다.
예를 들어 앞의 예에 사용된 것과 동일한 값 및 그룹화 집합이 있는 경우 다음 질문에 답변하는 데 사용할 수 있는 사용자 지정 집계를 만들 수 있습니다.
필터링된 개수 월말에 있는 유지 관리 기간을 제외하고 한 달에 몇 개의 트랜잭션이 있었습니까?
시간별 평균을 사용한 비율 작년의 동일한 기간과 비교했을 때 판매량 증가율 또는 감소율은 어느 정도였습니까?
그룹화된 최소값 및 최대값 각 제품 범주 또는 각 판매 홍보에서 상위권에 든 영업 지역은 어디였습니까?
수식 및 피벗 테이블에 집계 추가
의미 있는 데이터를 얻기 위해 데이터를 그룹화하는 방법과 사용할 값이 대략적으로 파악되면 피벗 테이블을 작성할지, 아니면 테이블 내에서 계산을 만들지를 결정할 수 있습니다. PowerPivot for Excel은 Excel의 기본 기능을 확장하고 향상시켜 합, 개수 또는 평균과 같은 집계를 생성합니다. PowerPivot의 PowerPivot 창 내에서 또는 Excel 피벗 테이블 영역 내에서 사용자 지정 집계를 만들 수 있습니다.
현재 행 컨텍스트를 고려하여 다른 테이블의 관련 행을 검색한 다음 관련 행에서 해당 값의 합계, 개수 또는 평균을 구하는 집계를 계산 열에서 만들 수 있습니다.
측정값에서는 수식 내에 정의된 필터와 피벗 테이블 디자인 및 슬라이서, 열 머리글, 행 머리글의 선택에 따라 적용되는 필터를 사용하는 동적 집계를 만들 수 있습니다.
자세한 내용은 계산 수식 작성을 참조하십시오.
피벗 테이블에 그룹화 추가
피벗 테이블을 디자인할 때는 그룹화, 범주 또는 계층을 나타내는 필드를 피벗 테이블의 열 및 행 섹션으로 끌어서 데이터를 그룹화합니다. 그런 다음 숫자 값이 포함된 필드를 값 영역으로 끌어 해당 개수, 평균 또는 합계를 구합니다.
피벗 테이블에 범주를 추가하지만 범주 데이터가 팩트 데이터와 관련이 없는 경우 오류가 발생하거나 이상한 결과가 나타날 수 있습니다. 일반적으로 PowerPivot for Excel은 관계를 자동으로 검색 및 제안하여 문제를 해결하려고 합니다. 자세한 내용은 피벗 테이블에서 관계 작업을 참조하십시오.
필드를 슬라이서로 끌어 표시할 특정 데이터 그룹을 선택할 수도 있습니다. 슬라이서는 Excel과 PowerPivot for Excel의 새로운 기능으로, 이 기능을 사용하면 피벗 테이블의 결과를 대화형으로 그룹화, 정렬 및 필터링할 수 있습니다.
수식에서 그룹화 작업
테이블 간의 관계를 만든 다음 이러한 관계를 활용하여 관련 값을 조회하는 수식을 만들면 그룹화 및 범주를 사용하여 테이블에 저장된 데이터를 집계할 수도 있습니다.
즉, 범주에 따라 값을 그룹화하는 수식을 만들려면 먼저 관계를 사용하여 세부 데이터가 들어 있는 테이블과 범주가 들어 있는 테이블을 연결한 다음 수식을 작성합니다.
조회를 사용하는 수식을 작성하는 방법은 수식의 관계 및 조회을 참조하십시오.
집계에서 필터 사용
PowerPivot에는 사용자 인터페이스와 피벗 테이블 또는 차트뿐만 아니라 집계를 계산하는 데 사용하는 수식 내에서도 데이터 열과 테이블에 필터를 적용할 수 있는 새로운 기능이 추가되었습니다. 필터는 계산 열과 측정값 모두에서 수식에 사용할 수 있습니다.
예를 들어 새 DAX 집계 함수에서는 합계나 개수를 구할 값을 지정하는 대신 전체 테이블을 인수로 지정할 수 있습니다. 해당 테이블에 필터를 적용하지 않은 경우 테이블의 지정된 열에 있는 모든 값에 집계 함수가 적용됩니다. 그러나 필터 조건과 현재 컨텍스트에 따라 서로 다른 데이터 하위 집합에 집계가 적용되도록 DAX에서 테이블에 대한 동적 또는 정적 필터를 만들 수 있습니다.
수식에서 조건과 필터를 결합하면 수식에 제공된 값에 따라 변경되거나 피벗 테이블의 행 머리글과 열 머리글 선택에 따라 변경되는 집계를 만들 수 있습니다.
자세한 내용은 수식의 데이터 필터링을 참조하십시오.
Excel 집계 함수와 DAX 집계 함수의 비교
다음 표에서는 Excel에서 제공되는 몇 가지 표준 집계 함수를 나열하고 이러한 함수의 PowerPivot for Excel 구현에 대한 링크를 제공합니다. 이러한 함수의 DAX 버전은 구문과 특정 데이터 형식의 처리 방법에서 약간의 차이가 있기는 하지만 Excel 버전과 거의 동일하게 동작합니다.
표준 집계 함수
함수 |
용도 |
한 열에 있는 모든 숫자의 평균(산술 평균)을 반환합니다. |
|
한 열에 있는 모든 값의 평균(산술 평균)을 반환합니다. 텍스트와 숫자가 아닌 값을 처리합니다. |
|
열에 있는 숫자 값의 수를 셉니다. |
|
열에서 비어 있지 않은 값의 수를 셉니다. |
|
열에서 가장 큰 숫자 값을 반환합니다. |
|
테이블에서 계산되는 식 집합에서 가장 큰 값을 반환합니다. |
|
열에서 가장 작은 숫자 값을 반환합니다. |
|
테이블 전체에서 평가한 식 집합에서 가장 작은 값을 반환합니다. |
|
열에 있는 모든 숫자를 더합니다. |
DAX 집계 함수
DAX에는 집계가 수행되는 테이블을 지정할 수 있는 집계 함수가 포함되어 있습니다. 따라서 열의 값을 단순히 더하거나 평균을 계산하는 대신 이러한 함수를 사용하여 집계할 데이터를 동적으로 정의하는 식을 만들 수 있습니다.
다음 표에서는 DAX에서 사용 가능한 집계 함수를 나열합니다.
함수 |
용도 |
테이블에 대해 계산되는 식 집합의 평균을 구합니다. |
|
테이블에서 계산되는 식 집합의 수를 셉니다. |
|
열에서 비어 있는 값의 수를 셉니다. |
|
테이블에 있는 행의 총 수를 셉니다. |
|
필터 함수와 같은 중첩된 테이블 함수에서 반환되는 행 수를 셉니다. |
|
테이블에서 계산되는 식 집합의 합을 반환합니다. |
DAX와 Excel 집계 함수 간의 차이
이러한 함수는 Excel 함수와 동일한 이름을 갖지만 PowerPivot VertiPaq 엔진을 사용하며 테이블 및 열 작업을 위해 다시 작성되었습니다. DAX 수식을 Excel 통합 문서에서 사용할 수 없으며 그 반대의 경우도 마찬가지입니다. PowerPivot 창과 PowerPivot 데이터를 기반으로 하는 피벗 테이블에서만 사용할 수 있습니다. 또한 함수의 이름이 같더라도 동작은 약간 다를 수 있습니다. 자세한 내용은 개별 함수 참조 항목을 참조하십시오.
집계에서 열이 계산되는 방법 또한 Excel에서 집계가 처리되는 방법과 다릅니다. 예를 들어 설명해 보겠습니다.
Sales 테이블에서 Amount 열의 값에 대한 합계를 구하기 위해 다음 수식을 만든다고 가정하겠습니다.
=SUM('Sales'[Amount])
가장 간단한 경우 이 함수는 필터링되지 않은 한 열에서 값을 가져오며 결과는 Amount 열에 단순히 값을 더하는 Excel의 경우와 동일합니다. 하지만 PowerPivot에서 이 수식은 "Sales 테이블의 각 행에 대해 Amount의 값을 가져와서 그러한 개별 값을 더하기"로 해석됩니다. PowerPivot은 집계가 수행되는 각 행을 평가하고 각 행에 대해 단일 스칼라 값을 계산한 다음 그러한 값에 대해 집계를 수행합니다. 따라서 테이블에 필터가 적용되었거나 필터링되었을 수 있는 다른 집계를 기준으로 값이 계산되었을 경우 수식 결과가 다를 수 있습니다. 자세한 내용은 DAX 수식의 컨텍스트를 참조하십시오.
DAX 시간 인텔리전스 함수
DAX에는 이전 섹션에 설명된 새로운 테이블 집계 함수 외에도 기본 제공 시간 인텔리전스를 제공하기 위해 지정 날짜 및 시간을 사용하는 집계 함수가 있습니다. 이러한 함수는 날짜 범위를 사용하여 관련 값을 가져오고 값을 집계합니다. 날짜 범위에서 값을 비교할 수도 있습니다.
다음 표에서는 집계에 사용할 수 있는 시간 인텔리전스 함수를 나열합니다.
함수 |
용도 |
달력에서 지정한 기간의 끝 값을 계산합니다. |
|
달력에서 지정한 기간 앞에 오는 기간의 끝 값을 계산합니다. |
|
특정 기간의 첫 번째 날부터 시작하여 지정된 날짜 열의 가장 최근 날짜로 끝나는 기간을 계산합니다. |
시간 인텔리전스 함수 단원(시간 인텔리전스 함수(DAX))의 기타 함수는 집계에 사용할 날짜 또는 사용자 지정 날짜 범위를 검색하는 데 사용할 수 있습니다. 예를 들어 DATESINPERIOD 함수를 사용하여 날짜 범위를 반환하고 이 날짜 집합을 다른 함수에 인수로 사용하여 그 날짜에만 해당하는 사용자 지정 집계를 계산할 수 있습니다.