Excel 系列（四）- 如何在Excel 的Pivot Table values 區域顯示文字?

Views

如何在Excel 的樞紐分析表(pivot table) 值(values) 的區域顯示文字, 而非僅僅是加總或是平均值??


 有用過Excel的樞紐分析表(pivot table) 的人, 都會覺得它真的很好用. 雖說大部份的時間都是用它來做財務報表相關的數據分析, 但有時候它的功能不僅僅在數據分析上, 還可以用來做資料分析和資料驗証/data validation, 或是資料整合/data consolidation, 和資料清理/data cleaning.

 這時候, 就希望在"Values"區出現的不是數值而是文字了.

舉例來說, 如下表, 如果同一個產品(如A行)有不同的廠商(如B行)可以供貨.

而每個廠商有自己的產品代號(如C行), 而經銷商自己也有內部經銷商貨號(如D行) 和不同廠商所提供的進貨價-成本(如E行)


但如果你現在想看到的表格是以下面格式出現時, Pivot Table也可以幫你做到. 


1）先把原來的表單用 "Ctrl+T" 變成table 格式.

2）然後按 Inset → PivotTable
這張圖片的 alt 屬性值為空，它的檔案名稱為 image-85.png


3）然後記得要勾選最下方的 "Add this data to the Data Model"

4）然後把左側和上方標題從PivotTable Fields列表中分別拉到Rows and Columns的位置.



如果你的資料很大, 記得把"總和"欄(Grand Total)取消再進行下一步.
這張圖片的 alt 屬性值為空，它的檔案名稱為 image.png
5）接下來就是要增加文字資料到"Values"區了. 用滑鼠點選Table1按右鍵→選"Add Measure..."
這張圖片的 alt 屬性值為空，它的檔案名稱為 image-89.png
6）按下"Add Measure..."後會出現如下畫面:
Measure Name: 自行輸入相關名字（必填欄位）
Value Description: 可以空白
Formula: 輸入如下指令
=CONCATENATEX(Table1, Table1[你要出現文字資料的欄位],",")

7）現在PivotTable Fields下就會多一個選功能選項, 勾選後會以文字型態出現在Values區.



 8）以文字型態的樞紐分析表PivotTable 就完成了. 


當然啦, 還可以用不同的方式呈現你想要的表格, 如下圖

<延伸閱讀>
Excel 系列(五) - 為什麼VLOOKUP不到相對應的數值?
民國109年庚子(鼠)年1月1日, 西元2020年1月25日開始 Views

張貼留言

17 留言

  1. 匿名2020年9月16日 凌晨12:48

    接下來就是要增加文字資料到"Values"區了. 用滑鼠點選Table1按右鍵→選"Add Measure..."

    這一步沒辦法執行~~是版本問題嗎

    回覆刪除
    回覆
    1. 作者2020年9月16日 晚上11:41

      首先, 先確定你有做到這一步
      :img:https://1.bp.blogspot.com/-gbmQHDSmC_I/XmwljKS5ymI/AAAAAAAACk4/noLlyqF3O8QU8RY9DEQ6DKu8_Dd26k59ACLcBGAsYHQ/s1600/Capture.PNG:eimg:
      勾選最下方的 "Add this data to the Data Model", 因為如果沒有勾選這一項, 你的Pivot Table就不會有"+Add Measure" 這個選項.
      所以, 請問你有看到這個選項嗎?

      如果有看到但不能選, 另一個可能是, 你原本的excel檔是從text file 轉成的, 建議可以先重存為.xlsx檔之後, 再試一次.

      如果還是不行, 可以提供更詳細 "這一步沒辦法執行~" 的說明嗎? 謝謝

      最後, 如果你的資料很大, 記得把"總和"欄(Grand Total)取消再試一次.

      刪除
    2. 匿名2020年9月17日 凌晨1:08

      作者~好~
      有用CTRL+T選取範圍後 插入樞紐表，也有勾選Add this data to the Data Model",也有出可以選取TABLE，但是按右鍵後只出現Remove from active tab..這個選項，沒有+Add Measure..可以選~~

      求助感謝~~mail. fuyuan429@gmail.com

      刪除
    3. 作者2020年9月17日 凌晨2:06

      您用的版本是excel 2013嗎? 根據這裡的說法, add measure在excel 2013是被移除了, 你需要自己加進去, 但前提是你必須是Excel 2013 Professional Plus的版本.
      https://techcommunity.microsoft.com/t5/excel/pivot-tables-calculated-fields/m-p/693985
      (請看:img:https://1.bp.blogspot.com/-qFrVWelOTCg/X2Mmm5FYtyI/AAAAAAAAEXA/ULIqhyaaL7AHNA1BShEnDjND5AOrYDvjwCLcBGAsYHQ/s938/592943d67b3.PNG:eimg:)

      如果你是Excel 2013 Professional Plus的版本, 請參考下面二個網址, 如何加入excel add-in
      https://answers.microsoft.com/en-us/msoffice/forum/all/solved-cant-enable-powerpivot-in-excel-2013-pro/c7b89a78-797e-42b6-a86f-76723407826a
      https://support.microsoft.com/en-us/office/start-the-power-pivot-add-in-for-excel-a891a66d-36e3-43fc-81e8-fc4798f39ea8?ui=en-us&rs=en-us&ad=us

      刪除
  2. Unknown2020年9月17日 清晨7:41

    大神~~感謝@@@可以加入了!!謝謝

    回覆刪除
  3. 匿名2020年9月22日 晚上8:15

    您好，我是Excel Professional Plus 2013 的版本，但函數裡沒有CONCATENATEX此函數，所以一直顯示「無法解析名稱 'concatenatex'。它是無效的資料表或函數名稱。」

    想求助，謝謝!!

    回覆刪除
    回覆
    1. 作者2020年9月23日 凌晨2:08

      請問您是用Windows版的excel嗎? concatenatex在Android版Excel，iOS版Excel或Mac版Excel中無法使用。

      刪除
    2. 作者2020年9月23日 凌晨2:16

      當你在add measure後, 輸入=con時, 出現什麼?
      好像我只要打=con, excel會自動帶出所有con開頭的函數.
      :img:https://1.bp.blogspot.com/-dipmBOSF8xI/X2sRn42EYMI/AAAAAAAAEhU/g73YPR6OAicjxlDTjrfcHR7nV0zZtXVkACLcBGAsYHQ/s664/Picture2.PNG:eimg:

      刪除
    3. 作者2020年9月23日 清晨5:26

      如果你用的是Windows版的Excel, 也試了上面打了=con還是沒有這個函數, 那麼試試這個: 取消grand total --> https://www.keepersreview.tk/2019/10/pivot-table.html#grand

      有人也是有同樣的問題, 取消grand total 後, 問題解決了. 請參考:
      https://techcommunity.microsoft.com/t5/excel/could-not-add-the-field-to-the-pivot-table-because-the-formula/m-p/758030
      :img:https://1.bp.blogspot.com/-cuVjfGzFuXs/X2s81xGQaeI/AAAAAAAAEhg/oYmqDpkOTeU0wV4xDrKWx26qCUIauIqYgCLcBGAsYHQ/s950/Picture2.PNG:eimg:

      刪除
  4. 匿名2020年9月23日 晚上11:09

    您好，我是使用Windows版的excel

    在add measure後, 輸入=con時，只出現CONCATENATE跟CONTAINS，2個函數。
    有試過取消grand total，還是出不來。

    還是我的版本是沒有這個函數的。

    回覆刪除
  5. Unknown2021年5月3日 上午9:27

    我跟UNKNOWN遇到一樣的問題，只出現CONCATENATE, 沒有CONCATENATEX可以選

    回覆刪除
    回覆
    1. 站長2021年5月3日 晚上11:00

      您好, 請參考之前的幾個留言, 基本上如果不是excel版本的問題, 就是要自行重新加入Add-in, 再不, 就是一些文內要注意的總和設定或是忘記勾選[Add this data to the Data Model], 如果仍不能使用, 請參考這篇: 利用 Conditional Formatting 來手動設定讓 Pivot Table 數值區顯示文字. 希望能幫到你, 謝謝.

      刪除
  6. 別再燈下嘆息2021年6月22日 凌晨12:18

    作者已經移除這則留言。

    回覆刪除
  7. 別再燈下嘆息2021年6月22日 凌晨12:43

    是想將excel資料用不同方式列印呈現 例如取幾個欄位作為班的通訊錄 取幾個欄位作為老師需要的家長教育程度工作單位等
    但輸入完
    =CONCATENATEX('表格1',表格1[身分證號],",")
    出現
    此公式無效或不完整。
    請修正公式以繼續。

    請問我要如何調正呢?

    回覆刪除
    回覆
    1. 站長2021年6月24日 凌晨1:13

      :img:https://1.bp.blogspot.com/-rdlcjX-svl0/YNQ9ajHOmWI/AAAAAAAAJE0/sX1A3xtofCYeQeJTk1Xzwz1-jEpf53g5ACLcBGAsYHQ/s1435/116.JPG:eimg:
      希望這有幫助.

      刪除
  8. 匿名2024年1月15日 晚上7:26

    請問樞紐"值"顯示文字後，想要上下移動位置可以嗎? 謝謝
    P/NO.01A-5A
    65
    2876.4
    @15.98KGS 120*100*197
    **120*100*197想要上移到65的旁邊欄位...

    回覆刪除