엑셀 피벗테이블 값 필드 텍스트 출력하는 방법 :: 데이터모델 응용
피벗테이블 텍스트 출력 목차 바로가기
피벗테이블 텍스트 출력 영상 강의
패치노트
- 19.08.23 : 고유값 (UNIQUE VALUE)만 출력하는 공식을 추가했습니다.
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀고급] 피벗테이블 텍스트 출력하는 방법예제파일[엑셀고급] 피벗테이블 텍스트 출력하는 방법완성파일
0. 피벗테이블 텍스트 출력 공식 요약
① 일반 공식 (모든 값 표시)
=CONCATENATEX(FILTER('표','표'[필드]<>BLANK()),'표'[필드],"
")② 중복값 제거 공식 (고유 값만 표시)
=CONCATENATEX(SUMMARIZE(FILTER('표','표'[필드]<>BLANK()),'표'[필드]),'표'[필드],"
")1. 데이터모델 피벗테이블 생성하기
엑셀 2013 이후 버전 및 오피스 365에서 ‘데이터모델’ 기능을 제공합니다. 이 데이터모델 기능을 활용하면 피벗테이블을 더욱 다양한 방법으로 응용할 수 있는데요. 그 중 가장 대표적인 기능은 피벗테이블의 값 필드로 '숫자가 아닌 텍스트를 출력하는 것'입니다.
이번강의에서는 데이터모델과 DAX 함수를 사용하여 피벗테이블 값으로 텍스트를 출력하는 방법을 알아보겠습니다.
- 데이터모델을 적용하기 위하여 범위를 표로 변경합니다. (표를 참조하여 피벗테이블을 생성합니다)
단축키 : Ctrl + T
- 표를 생성하였으면, 상단 ‘디자인’ 탭의 ‘표 이름’에서 생성된 표의 이름을 변경합니다. 본 예제에서는 표 이름을 ‘tbl고객평가’로 변경하였습니다.
생성된 표의 이름을 변경합니다. - 다시 표를 선택한 뒤, 단축키 Alt + N + V 를 눌러 피벗테이블을 생성합니다. 이때 반드시, '데이터 모델에 이 데이터 추가' 체크박스를 활성화한 뒤 피벗테이블을 생성합니다.
데이터 모델에 이 데이터 추가’ 항목을 반드시 체크한 후 피벗테이블을 생성합니다.
2. 새로운 측정값 생성하기
- 생성된 데이터모델 피벗테이블에 측정값을 추가합니다. 피벗테이블을 선택하면 우측에 필드목록이 나타납니다. 우측 필드목록에서 표 이름을 우클릭 한 뒤, [측정값 추가] 를 클릭합니다.
데이터모델 피벗테이블의 측정값을 추가합니다. - 측정값 이름은 피벗테이블에 새롭게 생성될 필드명입니다. 본 예제에서는 ‘고객평가’로 작성하였습니다.
값 필드에 추가할 항목 이름을 작성합니다. - 수식에는 2개의 DAX 함수가 사용됩니다.
- CONCATENATEX 함수 (엑셀 2016 이후 버전에서 사용가능합니다.)
= CONCATENATEX ( 표, 조합할 범위, 구분자 ) - FILTER 함수 :
= FILTER ( 표, 필터 조건 )
- CONCATENATEX 함수 (엑셀 2016 이후 버전에서 사용가능합니다.)
- 본 예제에서 사용된 수식은 아래와 같습니다. 수식을 입력한 뒤 'DAX 수식 확인' 버튼을 눌러 입력한 수식에 오류가 있는지 검토합니다. 줄바꿈은 키보드 SHIFT + ENTER 키로 입력합니다.
- =CONCATENATEX(FILTER('tbl고객평가','tbl고객평가'[비고]<>BLANK()),'tbl고객평가'[비고],"
")
[DAX 수식확인] 버튼을 눌러 입력된 수식에 오류가 있는지 검토합니다. - =CONCATENATEX(FILTER('tbl고객평가','tbl고객평가'[비고]<>BLANK()),'tbl고객평가'[비고],"
3. 측정값을 피벗테이블에 추가하기
- 측정값 생성을 완료하면 피벗테이블 필드목록에 새로운 항목이 추가됩니다. 새로 생성된 항목을 값 필드에 추가합니다. 그 후 사용자가 원하는 피벗테이블 디자인으로 행/열에 각 원하는 항목을 추가합니다.
- 열 : [점수]
- 행 : [성별], [항목]
- 값 : [고객평가]
- 값 필드에 텍스트가 추가되었지만, 셀 안에 줄바꿈이 되지않아 깔끔하게 정리되지 않은 것을 확인할 수 있습니다.
텍스트는 추가되었으나, 텍스트의 줄 바꿈이 되지않아 테이블이 한눈에 들어오지 않습니다. - 시트의 전체 범위를 선택한 뒤, 우클릭 - 셀서식 으로 이동합니다. (단축키 Ctrl + 1) 셀서식 대화상자가 나타나면, '맞춤 - 자동 줄 바꿈 항목을 활성화합니다.
시트 전체 범위를 선택한 뒤, 셀 서식에서 [자동 줄 바꿈] 항목을 활성화합니다. - 피벗테이블의 디자인을 사용자 편의에 맞게 변경하면 필드에 텍스트가 입력된 피벗테이블 생성이 완료됩니다.
피벗테이블 값에 텍스트가 추가되어 깔끔하게 완성되었습니다.
4. 피벗테이블 텍스트 중 고유값만 출력하려면 어떻게 해야 하나요?
DAX 함수 중 SUMMARIZE 함수를 사용하면 필드에 고유값만 출력할 수 있습니다.
- SUMMARIZE 함수:
선택된 테이블에서 원하는 필드를 선택한 뒤, 해당 필드로 그룹화된 테이블을 반환합니다. (*해당 필드의 고유값만 남게됩니다.)
= SUMMARIZE ( 테이블, 그룹할 필드 1, [그룹할 필드 2], ... )
기존 측정값에 사용된 공식을, 아래 SUMMARIZE 함수를 사용한 공식으로 변경하면 필드에 고유값만 출력됩니다..
=CONCATENATEX(SUMMARIZE(FILTER('tbl고객평가','tbl고객평가'[비고]<>blank()),'tbl고객평가'[비고]),'tbl고객평가'[비고],"
")
엑셀 2010입니다.
엑셀 2010과 2013버전을 사용중이실경우, 파워피벗 추가기능을 설치하셔야합니다.
설치파일은 아래 링크에서 다운받을 수 있습니다.
https://www.microsoft.com/ko-kr/download/details.aspx?id=29074
이후 설치방법은 아래 링크에서 확인해보시겠어요?^^
https://docs.microsoft.com/ko-kr/previous-versions/sql/sql-server-2012/gg413462(v=sql.110)?redirectedfrom=MSDN
혹시라도 문제가 있을경우 언제든지 댓글남겨주시면 확인 후 답변드리겠습니다. 감사합니다.^^
죄송합니다. 제가 잘못 안내해 드렸습니다.
MS 홈페이지를 확인해보니, power pivot 데이타모델은 아래 버전에서만 지원이 된다고 합니다.
Office 365-데스크톱 버전의 Windows 용 Excel을 포함 하는 모든 버전
Office Professional 2019
Office Home & Business 2019
Office Home & Student 2019
Office 2016 Professional Plus (볼륨 라이선스를 통해서만 제공)
Office 2013 Professional Plus
Excel 2013 독립 실행형
Excel 2016 독립 실행형
따라서 엑셀 상위버전을 구매하시거나 오피스 365를 구독하셔야 합니다. ㅠ-ㅠ
자세한 내용은 아래 링크를 확인해보시겠어요?
https://support.office.com/ko-kr/article/%ed%8c%8c%ec%9b%8c-%ed%94%bc%eb%b2%97%ec%9d%80-%ec%96%b4%eb%94%94%ec%97%90-%ec%9e%88%eb%82%98%ec%9a%94-aa64e217-4b6e-410b-8337-20b87e1c2a4b?ui=ko-KR&rs=ko-KR&ad=KR
감사합니다.
측정값 추가 관련해서 한가지 문의사항이 있습니다.
원본 데이터가 많아서 그런지 피벗테이블 상에 측정값 추가는 되나, 이후 추가된 측정값을 ∑값으로 추가하려고 클릭시 아무 반응이 없습니다.
원본데이터를 어느정도 삭제했을때는 ∑값이 추가가 되는데요,
혹시 원본데이터 갯수나 텍스트 양 등에 제한이 있어서 그런건지, 있다면 해당 제한을 해제 할수 있는 방법이 있을지 문의드립니다.
측정값 추가는 원본데이터 크기에 상관없이 추가하실 수 있습니다.
∑ 로 추가하셨다고 하시니, 값을 우클릭하여 추가하신듯 한데요.
표를 우클릭하신 뒤 측정값을 추가해보시겠어요?^^
제 답변이 도움이 되셨길 바랍니다.
감사합니다.
엑셀2007에선 사용이 안되겠죠??
네.. ㅠ-ㅠ..! 해당 기능은 엑셀 2013 이후버전에서만 사용가능합니다.
감사합니다.
로우데이타의 범위를 "표"로 설정해줘야 피벗 업데이트가 가능할까요?
유용하게 사용하겠읍니다.
CONCATENATEX 함수 : (엑셀 2016 이후 버전에서 사용가능합니다.)
파워피벗 추가기능이 되면 엑셀 2013 버전에서도 사용 가능한것으로 알고 있습니다.
추가기능을 설치해 보셨나요?
감사합니다.
함수 목록에 표시되지 않는다면, 현재 사용중인 버전이 엑셀 2013의 최신버전인지 확인해보시겠어요?
만약 업데이트를 해도 표시되지 않는다면, 엑셀 2016 이상의 버전을 이용하는것 외에는 방법이 없을듯 합니다 ^^;
'FILTER'가 '테이블' 이 아닌듯 합니다..??
작성하신 수식에 오타가 있는지 확인해보세요