Excel 是 生活中非常常用的資料分析工具，讓我們一起來做一些練習吧!
軟體安裝
Excel 2016 試用版軟體下載 (32位元 64位元)
練習資料下載
安裝完成後, 請下載這一份資料來進行練習
將資料轉換為表格
首先先將資料全選:
Ctrl + Shift + 右: 將有資料的所有欄位選取
Ctrl + Shift + 下: 將有資料的所有列選取
選好所有資料後, 點選上方[常用]->[格式化為表格]-> 挑選喜歡的樣式, 將這份資料轉換為表格
按下確定轉換成表格
為表格命名
為了方便後續操作取用這一張表格的資料,所以我們將表格命名為Sales
產生合計列
利用表格現成的功能, 產生合計列
增加Age Group欄位來將年紀分群
選擇F欄按下右鍵->插入,產生一個新的欄位,命名為Age Group
在F2欄位中輸入以下公式:
=IF([Customer Age]<=20,”<=20″,IF([Customer Age]<31,”21-30″,IF([Customer Age]<41,”31-40″,IF([Customer Age]<51,”41-50″,IF([Customer Age]<61,”51-60″,”>=61″)))))
Age Group欄位則會利用Customer Age欄位的資料,分群為:
- <=20
- 21-30
- 31-40
- 41-50
- 51-60
- >=61
利用樞紐分析觀察都市與營收的關係
在表格中,點選插入->樞紐分析表,表格來源輸入Sales, 產生於新工作表中
接著在右方的樞紐分析表欄位中, 將以下項目拉至對應位置:
- Country -> 列
- State -> 列
- Year -> 欄
- Revenue -> 值
就可以在左邊看見每一個地區在每一年的營收統計
如果不想看到這麼細的資料,也可以在Country的資料欄上按下右鍵
展開/摺疊 -> 摺疊整個欄位
產生圖表
插入 -> 樞紐分析圖 -> 群組直條圖
資料一目了然
加入交叉分析篩選器
如果想要方便地觀察單一一年的各國營收狀況, 則可以加入交叉分析篩選器, 這個例子我們選擇Year
接著就可以透過右方的交叉分析篩選器, 輕鬆切換年份來進行資料觀察!
年齡與營收的觀察
接著讓我們透過另一個樞紐分析表, 來觀察年齡與營收的關聯性
插入樞紐分析表,一樣取用資料來自Sales, 加入於目前的工作表中
- Age Group -> 列
- Customer Age -> 列
- Revenue -> 值
自訂排序方式
當我們將資料折疊後, 會發現我們原先設定的Age Group排列不如預期
此時我們必須自己定義排序的方式:
將這一組列標籤(<=20,>=60,21-30,31-40,41-50,51-60)複製到其他地方, 全選後, 資料->排序…
選項 -> 自訂清單…
新增:
<=20,21-30,31-40,41-50,51-60,>=61
製作完成後,回到原本的樞紐分析表, 列標籤, 下拉選單, 從A到Z排序
資料終於如預期順序呈現了!
報表連線
如果我們希望多個樞紐分析表, 在交叉分析篩選器運作時都能連動, 則可以透過報表連線功能來達成這個效果
在交叉分析篩選器上按下右鍵->報表連線…
兩個樞紐分析表都打勾, 這時再選擇年份, 則兩邊產生的圖表都會產生變化!
矩形式樹狀結構圖
最後, 我們來實作一個常見的矩形式樹狀結構圖圖表
調整樞紐分析表欄位
- Age Group -> 列
- Product Category -> 列
- Revenue -> 值
將全部Age Group展開, 並且點選上方設計->報表版面配置->以列表方式顯示, 重複所有項目標籤
設計->小計->不要顯示小計
由於不能直接從樞紐分析表來建立這類型的圖表, 所以我們做了以上的調整,調整完之後,將這些資料複製到其他地方
接著按下插入 -> 矩形式樹狀結構圖
漂亮的圖表終於產生了!
是不是對於各年齡層購買不同商品的比例更加一目了然呢?
試試看, 一起實作以上的這些功能吧!