經(jīng)常有讀者拿類似下面的圖表問我如何做。

老讀者朋友會(huì)發(fā)現(xiàn)，我很少寫這類新奇特圖表的帖子，因?yàn)檫@種圖表多由那些PPT模板制作者拿形狀繪制，只是追求樣式新穎花哨，不但無助于數(shù)據(jù)比較，還存在極大誤導(dǎo)，是典型的垃圾圖表。

網(wǎng)絡(luò)上也有人寫這種圖表的 Excel 制作教程，一般使用柱形圖+遮罩或者填充的方法來做，但大多數(shù)比例是錯(cuò)誤的，魚缸里水的多少并沒有正確反映數(shù)據(jù)的大小，尤其在水面接近底部和頂部時(shí)，誤導(dǎo)極大。

因此，盡管我們完全不建議使用這種圖表，但還是寫個(gè)正確做法的帖子，以免新人朋友被以訛傳訛。

作圖思路

使用柱形圖+貼入形狀的方法來會(huì)繪制魚缸和水面，這是一種常用作圖技巧。關(guān)鍵是作圖數(shù)據(jù)準(zhǔn)備，指標(biāo)數(shù)據(jù)如何正確轉(zhuǎn)換為魚缸里水面的高度，使水的側(cè)面積代表指標(biāo)大小(因是平面圖表，不必使用水的體積去代表)。

圖中陰影部分面積，占圓的面積的比例，代表要反映的指標(biāo)的大小。一般都是已知 h 求 s，有以下公式：

s = arccos(1-h)-(1-h)*sqrt(2h-h^2)

如果不理解這個(gè)公式，請自行搜索腦補(bǔ)。

但我們是已知陰影面積 s，求水面高 h 是多少，我的數(shù)學(xué)知識有限，沒法從這個(gè)公式解出 h=f(s) 的公式，只好變通處理，根據(jù)這個(gè)公式做個(gè)對照表，按 s 來反查 h 的取值。如果有人能解出公式，還請教教我：)

數(shù)據(jù)準(zhǔn)備

圖中，

C列，h 從1% 變化到200%

D列，計(jì)算出陰影面積 s，D7：=ACOS(1-C7)-(1-C7)*SQRT(2*C7-C7^2)

E列，為這個(gè) s 占圓的面積的比例，也就是業(yè)務(wù)指標(biāo)百分比，E7：=D7/(PI()*1^2)

F列，為對應(yīng)的正確水深，F(xiàn)7：=C7/2

這里E、F兩列就構(gòu)成一個(gè) s~h 的關(guān)系對應(yīng)表，供下面調(diào)用查找。

轉(zhuǎn)換作圖數(shù)據(jù)：

C列為業(yè)務(wù)指標(biāo)，D列為轉(zhuǎn)換后的數(shù)據(jù)，也就是柱形圖的高度，水面的高度：

D7：=VLOOKUP(C7,計(jì)算反查!$E$7:$F$206,2,1)

注意***參數(shù)為1，模糊近似查找方式。對照表是有限精度，結(jié)果也會(huì)略有誤差，不過已經(jīng)可以忽略，咱們畫個(gè)圖表而已。

E列為輔助數(shù)據(jù)，固定為100%，用來繪制魚缸外圈。

作圖步驟

1、用B、D、E數(shù)據(jù)做簇狀柱形圖。

2、用形狀繪制一個(gè)圓圈，復(fù)制后貼入到輔助序列柱形圖;把圓圈復(fù)制1份，設(shè)置填充色，復(fù)制后貼入到折算序列柱形圖，填充方式選 層疊并伸展。

3、設(shè)置柱形圖重疊比例100%，分類間距50%。這時(shí)看到變形的魚缸圖。

4、折算序列添加數(shù)據(jù)標(biāo)簽，指定為C列的原指標(biāo)數(shù)據(jù)，并拉寬圖表到正圓形。

如果是只做一個(gè)魚缸，就可以使用一個(gè)輔助的餅圖來保持正圓，但這里有多組魚缸，所以只能手動(dòng)調(diào)整，不過你可以按住shift后繪制一個(gè)正圓來驗(yàn)證。

5、其他格式化到如下樣式。

對比下面直接用原數(shù)據(jù)做柱形圖的圖表，你就可以看到?jīng)]有轉(zhuǎn)換的誤差了，小的偏小，大的偏大，有誤差誤導(dǎo)。

不過，即使轉(zhuǎn)換為正確的數(shù)據(jù)和面積，我們還是難以從圖表直觀看出數(shù)據(jù)差異，因?yàn)樗娓叨群椭笜?biāo)大小不是線性關(guān)系，這也是我們不推薦使用這種圖表類型的原因。

近似簡化

以上，因?yàn)闊o法直接寫出 h=f(s) 的公式，需要使用一個(gè)對照表來轉(zhuǎn)換數(shù)據(jù)，略有不便，我想能夠怎么簡化。

我把對照表的 s~h 數(shù)據(jù)做成圖表，利用Excel的添加預(yù)測趨勢線功能，模擬出它們之間的關(guān)系，得到如下公式，擬合度0.9999：

h = 3.7785 *s^5 – 9.4188 *s^4 + 9.0867 *s^3 – 4.2298 *s^2 + 1.7532 *s + 0.0157

這么復(fù)雜的方程，確實(shí)難以解出來。如果有數(shù)學(xué)學(xué)霸，還請告知正確的公式。

那么，回到前面數(shù)據(jù)組織的地方，可以寫個(gè)簡化的近似公式，直接得到轉(zhuǎn)換后的數(shù)據(jù)，不再需要查找了：

F7：=3.7785*C7^5 – 9.4188*C7^4 + 9.0867*C7^3 – 4.2298*C7^2 + 1.7532*C7 + 0.0157

這樣，數(shù)據(jù)轉(zhuǎn)換的工作就簡單了，把這個(gè)公式復(fù)制保存好，以后一個(gè)公式就可以搞定，雖然還是略有誤差，不過不重要了。

知識點(diǎn)

本例作圖技巧較簡單，就是形狀填充，主要難點(diǎn)在數(shù)據(jù)轉(zhuǎn)換。做完這個(gè)圖表，你可以練習(xí)到以下知識點(diǎn)：

1. 構(gòu)圖思路，柱形圖+填充+重疊
2. 數(shù)學(xué)知識，復(fù)習(xí)高中數(shù)學(xué)，三角函數(shù)與幾何，略難
3. vlookup，參數(shù)1，模糊近似查找
4. 貼入形狀，填充方式：層疊并伸展
5. 重疊比例，前后關(guān)系
6. 標(biāo)簽工具，引用其他位置
7. 趨勢線，多項(xiàng)式，擬合方程，擬合度R2