問題描述
使用切片器時 Excel 顏色變化 (Excel Color Change When Using Slicer)
我在使用 Excel 彩色圖表時遇到問題。首先，我使用的是我之前設置的顏色主題。假設 A：藍色 B：紫色 C：橙色 D：綠色
但是在我使用切片器之後包括 B：紫色，我之前設置的顏色變為
A：藍色 C：紫色 D：橙色
我可以使用切片器以某種方式獲得與之前設置的顏色相同的顏色嗎？我的意思是這樣的
A：藍色 C：橙色 D：綠色
參考解法
方法 1:
This is a known issue. Whether bug or feature is up to the beholder.
There's an article on Jon Peltier's blog, written by Jon Acampora, Pivot Chart Formatting Changes when Filtered, discussing the problem and possible workarounds. Check it out. It has a sample file to download and play around with. The essence is:
Simple Workarounds One way to prevent the formatting from changing is to create a regular chart that references the PivotTable data. With either of the following solutions we want to avoid the PivotChart all together, and instead create a regular chart. Again, the regular charts do NOT suffer from the formatting issues when filters are applied.
Solution #1 This can be accomplished by applying the technique in Jon’s article Making Regular Charts from PivotTables. With that technique you basically create a regular chart and then carefully add the data series to the chart by referencing areas within the PivotTable. You just have to be careful with the areas you select or reference because Excel has a tendency to want to convert the regular chart to a PivotChart.
Solution #2 The other option is to create a new area on the worksheet that contains a replica of the PivotTable. This area just contains formulas that reference the PivotTable. You can either use the direct cell references like (=C9) or the GETPIVOTDATA() function to point to the PivotTable. You might want to use the GETPIVOTDATA function if you only want to display a subset of your PivotTable data in the chart.
The advantage of this technique is that once you have the formula based PivotTable setup, you can reference the entire area of the source data range to quickly create a chart that includes all your series. It might save you time over having to add the series individually as you do in solution #1.
方法 2:
Slicer filters the data, so Excel only sees the filtered results and applies the colour mapping to them without knowing how many results it skips, hence the strange colouring system.
One solution (but it doesn't use a slicer) is to set the data value of a pie slice to zero. This way the data is still rendered, but as a zero‑width slice, and the colour count increases as normal.
(by Dede Soetopo、teylyn、JMP)