엑셀 피벗테이블 값 필드 텍스트 출력방법 :: 데이터모델 응용

엑셀 데이터모델을 활용하여 피벗테이블 값 필드에 텍스트를 출력하는 방법을 단계별로 살펴봅니다.

# 피벗테이블 # 엑셀기능&팁

작성자 :
오빠두엑셀
최종 수정일 : 2022. 01. 29. 15:54
URL 복사
메모 남기기 : (90)
엑셀 피벗테이블 텍스트 출력 썸네일 _R
작성자:

엑셀 피벗테이블 값 필드 텍스트 출력하는 방법 :: 데이터모델 응용

피벗테이블 텍스트 출력 목차 바로가기
  1. 예제파일 다운로드
  2. 데이터모델 피벗테이블 생성하기
  3. 새로운 측정값 생성하기
  4. 측정값을 피벗테이블에 추가하기
  5. 텍스트 중 고유값만 출력하려면 어떻게 해야 하나요?
피벗테이블 텍스트 출력 영상 강의

패치노트
  • 19.08.23 : 고유값 (UNIQUE VALUE)만 출력하는 공식을 추가했습니다.

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [엑셀고급] 피벗테이블 텍스트 출력하는 방법
    예제파일
    다운로드
  • [엑셀고급] 피벗테이블 텍스트 출력하는 방법
    완성파일

0. 피벗테이블 텍스트 출력 공식 요약

① 일반 공식 (모든 값 표시)

=CONCATENATEX(FILTER('표','표'[필드]<>BLANK()),'표'[필드],"
")

② 중복값 제거 공식 (고유 값만 표시)

=CONCATENATEX(SUMMARIZE(FILTER('표','표'[필드]<>BLANK()),'표'[필드]),'표'[필드],"
")

1. 데이터모델 피벗테이블 생성하기

엑셀 2013 이후 버전 및 오피스 365에서 ‘데이터모델’ 기능을 제공합니다. 이 데이터모델 기능을 활용하면 피벗테이블을 더욱 다양한 방법으로 응용할 수 있는데요. 그 중 가장 대표적인 기능은 피벗테이블의 값 필드로 '숫자가 아닌 텍스트를 출력하는 것'입니다.

이번강의에서는 데이터모델과 DAX 함수를 사용하여 피벗테이블 값으로 텍스트를 출력하는 방법을 알아보겠습니다.

  1. 데이터모델을 적용하기 위하여 범위를 표로 변경합니다. (표를 참조하여 피벗테이블을 생성합니다)
    단축키 : Ctrl + T
  2. 표를 생성하였으면, 상단 ‘디자인’ 탭의 ‘표 이름’에서 생성된 표의 이름을 변경합니다. 본 예제에서는 표 이름을 ‘tbl고객평가’로 변경하였습니다.

    엑셀 피벗테이블 표이름 변경
    생성된 표의 이름을 변경합니다.
  3. 다시 표를 선택한 뒤, 단축키 Alt + N + V 를 눌러 피벗테이블을 생성합니다. 이때 반드시, '데이터 모델에 이 데이터 추가' 체크박스를 활성화한 뒤 피벗테이블을 생성합니다.

    데이터모델 피벗테이블 생성
    데이터 모델에 이 데이터 추가’ 항목을 반드시 체크한 후 피벗테이블을 생성합니다.

2. 새로운 측정값 생성하기

  1. 생성된 데이터모델 피벗테이블에 측정값을 추가합니다. 피벗테이블을 선택하면 우측에 필드목록이 나타납니다. 우측 필드목록에서 표 이름을 우클릭 한 뒤, [측정값 추가] 를 클릭합니다.

    피벗테이블 측정값 추가
    데이터모델 피벗테이블의 측정값을 추가합니다.
  2. 측정값 이름은 피벗테이블에 새롭게 생성될 필드명입니다. 본 예제에서는 ‘고객평가’로 작성하였습니다.

    측정값 고객평가
    값 필드에 추가할 항목 이름을 작성합니다.
  3. 수식에는 2개의 DAX 함수가 사용됩니다.
    • CONCATENATEX 함수 (엑셀 2016 이후 버전에서 사용가능합니다.)
      = CONCATENATEX ( 표, 조합할 범위, 구분자 )
    • FILTER 함수 :
      = FILTER ( 표, 필터 조건 )
  4. 본 예제에서 사용된 수식은 아래와 같습니다. 수식을 입력한 뒤 'DAX 수식 확인' 버튼을 눌러 입력한 수식에 오류가 있는지 검토합니다. 줄바꿈은 키보드 SHIFT + ENTER 키로 입력합니다.
    • =CONCATENATEX(FILTER('tbl고객평가','tbl고객평가'[비고]<>BLANK()),'tbl고객평가'[비고],"
      ")

    피벗테이블 수식오류확인
    [DAX 수식확인] 버튼을 눌러 입력된 수식에 오류가 있는지 검토합니다.

3. 측정값을 피벗테이블에 추가하기

  1. 측정값 생성을 완료하면 피벗테이블 필드목록에 새로운 항목이 추가됩니다. 새로 생성된 항목을 값 필드에 추가합니다. 그 후 사용자가 원하는 피벗테이블 디자인으로 행/열에 각 원하는 항목을 추가합니다.
    • 열 : [점수]
    • 행 : [성별], [항목]
    • 값 : [고객평가]
  2. 값 필드에 텍스트가 추가되었지만, 셀 안에 줄바꿈이 되지않아 깔끔하게 정리되지 않은 것을 확인할 수 있습니다.

    엑셀 줄바꿈 안됨
    텍스트는 추가되었으나, 텍스트의 줄 바꿈이 되지않아 테이블이 한눈에 들어오지 않습니다.
  3. 시트의 전체 범위를 선택한 뒤, 우클릭 - 셀서식 으로 이동합니다. (단축키 Ctrl + 1) 셀서식 대화상자가 나타나면, '맞춤 - 자동 줄 바꿈 항목을 활성화합니다.
    엑셀 자동줄바꿈 체크
    시트 전체 범위를 선택한 뒤, 셀 서식에서 [자동 줄 바꿈] 항목을 활성화합니다.
  4. 피벗테이블의 디자인을 사용자 편의에 맞게 변경하면 필드에 텍스트가 입력된 피벗테이블 생성이 완료됩니다.

    피벗테이블 값 필드 텍스트 추가 완성
    피벗테이블 값에 텍스트가 추가되어 깔끔하게 완성되었습니다.

4. 피벗테이블 텍스트 중 고유값만 출력하려면 어떻게 해야 하나요?

DAX 함수 중 SUMMARIZE 함수를 사용하면 필드에 고유값만 출력할 수 있습니다.

  • SUMMARIZE 함수:
    선택된 테이블에서 원하는 필드를 선택한 뒤, 해당 필드로 그룹화된 테이블을 반환합니다. (*해당 필드의 고유값만 남게됩니다.)
    = SUMMARIZE ( 테이블, 그룹할 필드 1, [그룹할 필드 2], ... )

기존 측정값에 사용된 공식을, 아래 SUMMARIZE 함수를 사용한 공식으로 변경하면 필드에 고유값만 출력됩니다..

=CONCATENATEX(SUMMARIZE(FILTER('tbl고객평가','tbl고객평가'[비고]<>blank()),'tbl고객평가'[비고]),'tbl고객평가'[비고],"
")
5 44 추천
게시글평점
90 댓글
Inline Feedbacks
View all comments
90
0
Would love your thoughts, please comment.x