用Excel制作一個(gè)漂亮的分類散點(diǎn)圖

? ? ? ?前段時(shí)間小編在朋友圈發(fā)了一個(gè)散點(diǎn)圖，好多朋友說很漂亮，想要學(xué)習(xí)制作方法，今天小編就為大家解析這個(gè)散點(diǎn)圖的做法。我們先來看看這個(gè)散點(diǎn)圖：

? ? ??

? ? ? ?這個(gè)散點(diǎn)圖一共分為四類，每一類按照順序進(jìn)行散點(diǎn)圖的繪制，不同類別使用不同的顏色區(qū)分，并在圖中標(biāo)出平均值進(jìn)行連線。

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

將分組數(shù)據(jù)準(zhǔn)備成如下形式：

此處僅僅截取兩個(gè)Group，大家可以根據(jù)需要選擇各個(gè)group，此處不用進(jìn)行排序，分組展示即可，下面我們來構(gòu)造輔助列：

1、第一個(gè)輔助列，作用是將每個(gè)分組按照1，2，3。。。的順序標(biāo)個(gè)序號(hào)，具體公式是：

2、第二個(gè)輔助列，是用來規(guī)定散點(diǎn)圖每個(gè)分組橫坐標(biāo)的起點(diǎn)，由于圖中各個(gè)散點(diǎn)圖之間要有一定的間隔，所以起點(diǎn)以1，3，5等奇數(shù)開始。

要構(gòu)造這個(gè)奇數(shù)，此處首先構(gòu)造一個(gè)第幾組的輔助列，這一列有兩種方式構(gòu)造，可以提取B列中的1，2，3等字符構(gòu)造，使用RIGHT函數(shù)。當(dāng)然，也不排除分組名稱中沒有1，2，3，因而本文使用第二種方式構(gòu)造：

然后再使用2n-1的公式構(gòu)造奇數(shù)，當(dāng)然高手可以兩個(gè)公式嵌套，一步到位。

3、第三個(gè)就是規(guī)定每個(gè)分組之間的每個(gè)散點(diǎn)的橫向間隔，此處間隔則是將1按照每個(gè)分組點(diǎn)的個(gè)數(shù)進(jìn)行分割，本文樣例中的每個(gè)分組，散點(diǎn)個(gè)數(shù)是不同的，因而使用如下公式：

此處使用countif公式，是分組計(jì)數(shù)的公式，當(dāng)然本文是以1作為一個(gè)散點(diǎn)圖的長度，如果點(diǎn)非常多，想要以其他的長度作為分組，則乘以其他值。當(dāng)然，要記得在前面設(shè)置間隔的過程中，相應(yīng)的修改。

4、最后就是對(duì)每個(gè)坐標(biāo)點(diǎn)的橫坐標(biāo)進(jìn)行定義，使用輔助3（F列）加上輔助4（G列）的累計(jì)相加即可，使用公式如下：

自此，所有的輔助列便已經(jīng)構(gòu)造完畢，當(dāng)然這里面的這些輔助列只是為了講解構(gòu)造思路，可以將公式進(jìn)行疊加，減少輔助列的數(shù)量。

畫圖

首先選擇Group1的數(shù)據(jù)畫散點(diǎn)圖：

分別選擇橫坐標(biāo)和縱坐標(biāo)，并對(duì)散點(diǎn)圖的樣式進(jìn)行設(shè)置，選擇散點(diǎn)，更改系列樣式：

根據(jù)自己的需求逐個(gè)選擇分組，增加數(shù)據(jù)并進(jìn)行描點(diǎn)畫圖即可。

這樣，我們就形成了一個(gè)初步的分類散點(diǎn)圖了。

畫完散點(diǎn)，我們來計(jì)算中間的均值數(shù)據(jù)，均值數(shù)據(jù)我們用透視表透視一下即可得出：

然后將均值數(shù)據(jù)也增加到圖中，這里注意X軸選擇每個(gè)分組的中間值，Y軸是選擇剛剛透視的均值：

再將數(shù)據(jù)點(diǎn)樣式改為中間白色，邊框黑色即可，這樣一個(gè)分類散點(diǎn)圖就大功告成了，其他圖表元素可以根據(jù)需要再增添即可。