Excel 2016 操作練習

Excel 是 生活中非常常用的資料分析工具,讓我們一起來做一些練習吧!

軟體安裝

Excel 2016 試用版軟體下載 (32位元 64位元)

練習資料下載

安裝完成後, 請下載這一份資料來進行練習

將資料轉換為表格

首先先將資料全選:

Ctrl + Shift + 右: 將有資料的所有欄位選取

Ctrl + Shift + 下: 將有資料的所有列選取

選好所有資料後, 點選上方[常用]->[格式化為表格]-> 挑選喜歡的樣式, 將這份資料轉換為表格

image-2

按下確定轉換成表格

image-4

為表格命名

為了方便後續操作取用這一張表格的資料,所以我們將表格命名為Sales

image-5

產生合計列

利用表格現成的功能, 產生合計列

image-6

增加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, 產生於新工作表中

image-7

接著在右方的樞紐分析表欄位中, 將以下項目拉至對應位置:

  • Country -> 列
  • State -> 列
  • Year -> 欄
  • Revenue -> 值

就可以在左邊看見每一個地區在每一年的營收統計

image-8

如果不想看到這麼細的資料,也可以在Country的資料欄上按下右鍵

展開/摺疊 -> 摺疊整個欄位

image-9

產生圖表

插入 -> 樞紐分析圖 -> 群組直條圖

image-10

資料一目了然

image-11

加入交叉分析篩選器

如果想要方便地觀察單一一年的各國營收狀況, 則可以加入交叉分析篩選器, 這個例子我們選擇Year

image-13

接著就可以透過右方的交叉分析篩選器,  輕鬆切換年份來進行資料觀察!

image-14

年齡與營收的觀察

接著讓我們透過另一個樞紐分析表, 來觀察年齡與營收的關聯性

插入樞紐分析表,一樣取用資料來自Sales, 加入於目前的工作表中

image-15

  • Age Group -> 列
  • Customer Age -> 列
  • Revenue -> 值

image-16

自訂排序方式

當我們將資料折疊後, 會發現我們原先設定的Age Group排列不如預期

image-17-1

此時我們必須自己定義排序的方式:

將這一組列標籤(<=20,>=60,21-30,31-40,41-50,51-60)複製到其他地方, 全選後, 資料->排序…

image-19

選項 -> 自訂清單…

image-20

新增:

<=20,21-30,31-40,41-50,51-60,>=61

image-21

製作完成後,回到原本的樞紐分析表, 列標籤, 下拉選單, 從A到Z排序

image-22

資料終於如預期順序呈現了!

image-23

報表連線

如果我們希望多個樞紐分析表, 在交叉分析篩選器運作時都能連動, 則可以透過報表連線功能來達成這個效果

在交叉分析篩選器上按下右鍵->報表連線…

image-24

兩個樞紐分析表都打勾, 這時再選擇年份, 則兩邊產生的圖表都會產生變化!

image-25

矩形式樹狀結構圖

最後, 我們來實作一個常見的矩形式樹狀結構圖圖表

調整樞紐分析表欄位

  • Age Group -> 列
  • Product Category -> 列
  • Revenue -> 值

image-26

將全部Age Group展開, 並且點選上方設計->報表版面配置->以列表方式顯示, 重複所有項目標籤

image-28

設計->小計->不要顯示小計

image-29

由於不能直接從樞紐分析表來建立這類型的圖表, 所以我們做了以上的調整,調整完之後,將這些資料複製到其他地方

image-31

接著按下插入 -> 矩形式樹狀結構圖

image-34

漂亮的圖表終於產生了!

image-33

是不是對於各年齡層購買不同商品的比例更加一目了然呢?

試試看, 一起實作以上的這些功能吧!

(以上內容節錄自大專生限定 [未來之星 – 菁英培育計畫] )