通路王

Excel 系列(十四)- 如何在Excel 的 Pivot Table values 區域顯示文字? (二)

Views

想要在Pivot Table 展示區內顯示文字, 最容易的方法便是利用excel本身的 CONCATENATEX 函數.

請看這篇示範如何利用CONCATENATEX 在 Pivot Table 顯示文字.

然而似乎有些 excel 的用戶無法在 Pivot Table 裡找到這個 CONCATENATEX 函數, 不論是因為使用的版本不對, 或是因為使用的 excel 沒有版權, 又或者在嚐試重新安裝 Add-in 之後仍無法在 [+Add Measure...] 下找到 CONCATENATEX 函數時, 該如何在 Pivot Table 數值區中顯示文字呢?

本篇將示範一個替代方案, 利用 excel 的 Conditional Formatting 手動的設定數值區中的數字為相應的文字, 不過, 如果需要展示的文字過多, 這將是個繁瑣的動作.

所以, 如果您無法在您的 excel Pivot Table 裡的[+Add Measure...] 下找到 CONCATENATEX 函數, 或是在 Pivot Table 下根本看不到 [+Add Measure...]時, 請先確認:

➤在加 Pivot Table 時, 有勾選[Add this data to the Data Model]
➤excel 的版本
➤重新安裝Add-in

如果你是Excel 2013 Professional Plus的版本, 請參考下面二個網址, 如何加入excel add-in
http://bit.ly/2013-pivot
http://bit.ly/addin-pivot

如果上述步驟都做了, 還是找不到 CONCATENATEX 函數, 那麼請試著在 Microsoft Excel 官方留言版留言求解, 也歡迎您分享解決方案.

目前在還沒有其他方法時, 本站目前能找到的替代方法是利用 excel 的 Conditional Formatting 手動的設定要出在 Pivot Table 數值區塊的文字值.

舉例如下: 想要把左邊的表格裡的店面分號, 依城巿排列, 並顯示出相對應的區塊, 如右邊的格式.

做法便是先把要出現在 Pivot Table 中的文字設定一個相對應的數值ID, 然後利用 Conditional Formatting 來手動把每個數值ID對值到文字值上, 以這樣的方式在 Pivot Table 的數值區中呈現文字格式.

以上圖左邊的表格為例, 想要顯示的文字為"區域"值, 示範做法如下:
1) 設定一個相對應於每個"區域"的數值ID, 參照 I 和 J 欄.
2) 然後利用 Vlookup 把與每個區域相對應的數值ID 加到"區域"欄位旁
3) 選取整個要轉成 Pivot Table 的表格區塊, 然後點選[Insert]→[Tables]→[Pivot Table]
4)如果之前在嚐試使用 CONCATENATEX 函數時, 並沒有勾選下圖的[Add this data to the Data Model], 請勾選後再試試看是否接下來會有 CONCATENATEX 函數, 如果已經試過了, 那麼這裡勾不勾選這個選項都沒有差別了. 按下[OK]鍵創建一個Pivot Table.
5) 接下來分別把"城巿"加到 Rows, 把"店面分號"加到 Columns 裡. 然後到 [Design]→[Grand Totals]→按下[Off for Rows and Columns]取消行與列的總和加總
然後, 把"區域ID"加到[Values]區, [Value Field Settings...]設為Max後按下[OK]鍵
6) 此時Pivot Table 會看起來如下圖, 在顯示區會出現與"區域"相對應的"區域ID", 此時, 我們可以利用 excel 的 Conditional Formatting 把這些"區域ID"連到相對應的"區域", 於是顯示區便會出現"區域"文字了.
現在先選取 Pivot Table 的顯示區 B5:U11 → 然後依序如下操作:
在(5)的地方輸入: =b5=1
在(8)的地方輸入: [=1]"北區";;
然後按下[OK]鍵後, 再按一次[OK]鍵, 此時, Pivot Table 裡面原有的數值1, 現在已被"北區"2字取代了. 如下圖所示.
7) 接下來, 依序按照步驟6, 
在(5)的地方輸入: =b5=2
在(8)的地方輸入: [=2]"中區";;
然後按下[OK]鍵後, 再按一次[OK]鍵, 此時, Pivot Table 裡面原有的數值  2, 現在已被"中區"2字取代

繼續按照步驟6,
在(5)的地方輸入: =b5=3
在(8)的地方輸入: [=3]"南區";;
然後按下[OK]鍵後, 再按一次[OK]鍵, 此時, Pivot Table 裡面原有的數值  3, 現在已被"南區"2字取代了.

張貼留言

0 留言