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

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

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

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

피벗테이블 텍스트 출력 목차 바로가기
피벗테이블 텍스트 출력 영상 강의

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

예제파일 다운로드

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

  • [엑셀고급] 8강 피벗테이블 텍스트 출력
    예제파일
  • [엑셀고급] 피벗테이블 텍스트 출력
    완성파일

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 27 투표
게시글평점
guest
59 댓글
Inline Feedbacks
모든 댓글 보기
maynine
maynine
2020년 1월 22일 9:09 오전
게시글평점 :
     

'데이터 모델에 이 데이터 추가' 체크박스에 체크박스가 저는 안떠요ㅠㅠ
엑셀 2010입니다.

maynine
maynine
2020년 1월 29일 11:21 오전
답글 남기기  오빠두엑셀

PowerPivot는 설치되었는데 체크박스는 여전히 안뜨네요..ㅜㅜ

asddg
asddg
2021년 2월 22일 1:07 오전
답글 남기기  오빠두엑셀

저 노트북살때 엑셀이 들어잇엇는데(따로 사지 않고 내장?되어 잇엇어요 삼촌이 사준거라 잘 모르겟는데 microsoft안에 들어잇는것 같아요) 근데 2010 버전인데 이럴 경우에도 엑셀 상위 버전을 새로 사거나 microsoft 구독을 해야하나요?? 참고로 이것저것… 더보기 »

keungkeung
keungkeung
2020년 3월 27일 11:27 오전
게시글평점 :
     

안녕하세요! 유튜브 강의 잘 보고 있으며 항상 많은 도움 받고 있습니다. 감사합니다. ^^ 측정값 추가 관련해서 한가지 문의사항이 있습니다. 원본 데이터가 많아서 그런지 피벗테이블 상에 측정값 추가는 되나, 이후 추가된… 더보기 »

크리슈
크리슈(@1373)
2020년 3월 30일 6:50 오전
게시글평점 :
     

이게....가능하다니!! 감사합니다!

재현주
재현주
2020년 4월 1일 12:12 오후
게시글평점 :
     

항상 도움 많이 받고 있습니다. 일하다 이런거 되지 않을까 생각이 들때 오빠두를 찾아오면 그 해결책이 있네요ㅎㅎ

사람만들기
사람만들기(@-1622)
2020년 4월 7일 1:43 오후
게시글평점 :
     

잘배웠습니다.
엑셀2007에선 사용이 안되겠죠??

재현주
재현주
2020년 4월 9일 3:58 오후
게시글평점 :
     

[사용중질문] 올려주신 강좌 따라서 잘 사용하고 있습니다. 피벗테이블의 원본범위를 offset으로 설정하고 로우데이타를 추가한 후, 피벗의 "새로고침"이 작동되지 않습니다.

로우데이타의 범위를 "표"로 설정해줘야 피벗 업데이트가 가능할까요?

고재봉
고재봉(@-298)
2020년 4월 10일 9:35 오전
게시글평점 :
     

감사합니다.
유용하게 사용하겠읍니다.

초코악마
초코악마
2020년 4월 14일 9:17 오전
게시글평점 :
     

안녕하세요. 2013 이후 사용가능하다고 되어있는데 아래 함수는 2016 버전이상 사용가능하면, 2013 버전 에서는 함수를 어떻게 작성하여야 되나요?
CONCATENATEX 함수 : (엑셀 2016 이후 버전에서 사용가능합니다.)

초코악마
초코악마
2020년 4월 14일 4:01 오후
답글 남기기  오빠두엑셀

네. 추가 기능 설치하고 CONCATENATE 함수는 있으나, CONCATENATEX 함수는 보이지 않네요..

광치파
광치파
2021년 5월 26일 5:25 오전
답글 남기기  초코악마

2016부터 있는 함수입니다. ^^;

iviolin****
iviolin****(@iviolin2003jr-naver-com)
2020년 6월 2일 10:23 오전
게시글평점 :
     

새로운함수사용법이랑 기능 잘봤습니다

헐이업
헐이업(@hulhul2naver-com)
2020년 8월 10일 2:06 오후
게시글평점 :
     

마지막의 SUMMARIZE 삽입하는 위치가 맞는지 확인 부탁드립니다.
'FILTER'가 '테이블' 이 아닌듯 합니다..??

얍얍얍
얍얍얍
2020년 8월 28일 11:23 오전
게시글평점 :
     

마우스 우클릭을 했는데 '측정값 추가'가 아뜨는데.... 뭐가 문제인걸까요?

얍얍얍
얍얍얍
2020년 8월 31일 10:36 오전
답글 남기기  오빠두엑셀

Ctrl T 눌러서 표만들기 했고, 그 다음에 데이터 모델로 피벗 생성했습니다.
그런데 확성탭에서 제거 밖에 안뜹니다.

엑셀열공
엑셀열공
2020년 9월 21일 7:05 오후
게시글평점 :
     

데이터모델로 피벗을 만들었고 '범위'이름으로 테이블이 생성되었습니다. 엑셀 버전은 2013 입니다.원래 예전엔 '범위' 우클릭 시 '측정값추가'가 나왔는데, 지금은 '활성탭에서 제거' 만 나오고 '측정값추가'는 안나옵니다. 예전엔 '활성탭에서 제거'도 나오지 않았는데...뭐가 잘못된걸까요? 엑셀… 더보기 »

연선우
연선우
2020년 11월 19일 1:02 오후
답글 남기기  오빠두엑셀

지난해부터 꾸준히 도움을 받는 구독자인데요
알려주신 피벗테이블 후 '범위' 우클릭시 '측정값추가'가 안나오고
[데이터] - [쿼리및연결]는 확인이 안되는데 무엇때문인지 알수있을까요?
명령창에 [데이터]_Access/ 웹/텍스트 창 비활성화 되었습니다.
도움 부탁 드려요 ㅜ

Last edited 1 년 전 by 연선우
Daegi Shin
Daegi Shin
2020년 10월 7일 9:50 오전
게시글평점 :
     

유용한 강의 감사드립니다. 조합할 범위가 여러개있을 때 일일이 하지 않고 한번에 하는 방법이 있을까요?

쿵야
쿵야(@-4017)
2020년 11월 30일 11:35 오후
게시글평점 :
     

안녕하세요? 강의 감사합니다. 월마다 새로운 데이터가 생성이 되는데 열은 같고 행은 데이터와 행의 숫자는 늘어날 수도 있고 줄어들 수도 있습니다. 예를들어 1월의 데이터를 가지고 피벗테이블을 만들어서 원하는 내용을 추출하고 추출된… 더보기 »

쿵야
쿵야(@-4017)
2020년 12월 3일 9:51 오전
답글 남기기  오빠두엑셀

감사드립니다.

Baro_KIm
Baro_KIm
2020년 12월 21일 10:54 오전
게시글평점 :
     

안녕하세요ㅎ 유튜브 구독과 좋아요 항상 알람까지 설정하여 강의를 잘듣고 있는 어린이입니다. 다름이 아니라, 현재 텍스트를 가져오면서 중복되는 텍스트의 경우에는 하나의 값만 조인하는 방법이 있을까요..

maeng
maeng
2021년 1월 18일 2:33 오후
게시글평점 :
     

안녕하세요~ 피벗텍스트로 시간을 표시하는데, 원본 셀이 4:05이면 오전 4:05:00으로 나오고, 00:13이라면 오전 12:13:00 처럼 표시되고 있습니다. 원본 셀의 서식도 변경해보고, 피벗의 해당 열의 셀 서식이나 필드 표시 형식을 hh:mm처럼 바꿔도… 더보기 »

엑셀을잘하고싶다
엑셀을잘하고싶다(@766)
2021년 2월 4일 4:34 오후
게시글평점 :
     

안녕하세요, 항상 좋은 강의로 많이 배우고 있습니다.
피벗으로 텍스트 출력까지는 되었는데, 같은 칸에 2개 이상의 데이터가 출력될 경우에 한줄로 쭉 연결되서 값이 출력되는데, 혹시 줄바꿈 설정을 하는 방법이 있을까요?

몽이
몽이
2021년 2월 5일 5:32 오후
게시글평점 :
     

안녕하세요. 피벗테이블의 고유값만 출력하는 방법에 대해 문의 드려요. 행 레이블이 중복되는 결과들이 있는데, 텍스트로 출력되는 값들이 중복되는 갯수만큼 모두 나옵니다. 고유값만 출력하는 방법의 수식 'summarize' 함수를 추가했는데, '이 수식은 완전하지… 더보기 »

네비악
네비악(@6720kimchsnaver-com)
2021년 3월 19일 10:15 오전
게시글평점 :
     

2013 이상의 엑셀 or 365 구독자가 아니면 ... 동적 다중유효성? 이걸 해야겠네요

호롤롤로
호롤롤로
2021년 4월 7일 2:33 오후
게시글평점 :
     

겟피벗으로 다시 가져오려면 수식이 어떻게 되나요?ㅠㅠ
=GETPIVOTDATA("[Measures].[고객평가]",$A$3,"[tbl고객평가].[점수]","[tbl고객평가].[점수].&[보통]","[tbl고객평가].[성별]","[tbl고객평가].[성별].&[남]")

'성별'과 '남"을 특정 셀로 지정하고 싶은데,,지정하면 자꾸 틀어지네요ㅜㅜ

Last edited 8 월 전 by 호롤롤로
호동이
호동이
2021년 4월 25일 2:07 오후
게시글평점 :
     

맨아래 총합계부분은 안뜨게 할수 없나요?

dr****
dr****(@drap23naver-com)
2021년 4월 27일 4:41 오후
게시글평점 :
     

여러개 항목을 가지고 오려면 어떻게 해야 할까요?
텍스트로 보여질 피벗 값이 여러 항목인 경우는 일일이 하나씩 만들어줘야 할까요?

Brightsu
Brightsu
2021년 6월 14일 9:51 오전
게시글평점 :
     

너무나도 유용한 정보입니다.. 정말 감사합니다!

슝슝
슝슝(@yeonsama_comnaver-com)
2021년 7월 6일 8:47 오전
게시글평점 :
     

오빠두님 안녕하세요? CONCATENATEX 수식 잘 따라서 입력하고 DAX 수식 확인까지 해서 오류가 없다고 떴는데.. 확인 버튼이 활성화가 안되는데요.. 어떻게 해야할까요

슝슝
슝슝(@yeonsama_comnaver-com)
2021년 7월 6일 9:23 오전
답글 남기기  슝슝

측정값 이름을 추가안해서 안됐어요..ㅋㅋ 이제 잘 됍니다~

슝슝
슝슝(@yeonsama_comnaver-com)
2021년 7월 6일 9:33 오전
게시글평점 :
     

값하고 텍스트를 하나의 피벗테이블에서 같이 나오게 할 수는 없을까요?

hi****
hi****(@hik77777nate-com)
2021년 7월 7일 5:06 오후
게시글평점 :
     

안녕하세요
자동줄바꿈후 피벗테이블을 원하는 필터로 설정해서 볼때마다 자동줄바꿈이 해제되는데 계속해서 유지될 수 잇는 방법은 없을까요

김선미
김선미
2021년 7월 13일 5:08 오후
게시글평점 :
     

저두요 ㅠㅠ 근데 다른건 부분합이 필요하고 측정값이거만 텍스트라 합이 필요없어요 ㅠㅠ

riri
riri
2021년 7월 28일 2:55 오후
게시글평점 :
     

안녕하세요. 하나 이상의 열 이름이 유효하지 않아 데이터 원본으로 선택한 범위를 사용할 수 없습니다. 라고 나오는데 왜그럴까요?ㅠㅠ

곽동찬
곽동찬(@da3rdikakao-com)
2021년 8월 17일 7:30 오전
게시글평점 :
     

항상 똑같은 기능만 사용하다보니 이런 기능이 있는줄 생각도 못했네요. 감사합니다.

오빠두최고
오빠두최고
2021년 9월 7일 5:34 오후
게시글평점 :
     

정말 정말 감사합니다!!!!

59
0
여러분의 생각을 댓글로 남겨주세요.x