實作題
請下載 midterm.ods 的試算表，其中共有5個工作表，完成下列要求
- 為sale工作表的客戶代號與產品代號欄位設定"有效"功能(Excel中稱之為驗證)，參考的對象為 customer與 product 工作表。
- 請根據sale工作表，新增一個 "90年銷售狀況" 工作表，其中的欄位有 (使用vlookup函數)
業務姓名 部門名稱 客戶代號 客戶寶號 產品代號 產品名稱 數量 單價 總額 聯絡人 交易年 交易月 - 運用"小計"功能，根據 "90年銷售狀況" 工作表，新增一份 "90年業務員業績統計表" 工作表。
- 以"資料助理"(Excel中稱樞紐分析表)，根據 "90年銷售狀況" 工作表，新增一份 "90年業務部門銷售狀況統計表" 工作表，要求如下：
- 分組順序：部門名稱，業務姓名，客戶寶號，連絡人
- 上述分組順序的部門名稱與業務姓名必須以遞增排序方式排列。
- 部門名稱與業務姓名必須加上合計。
- 金額必須加上貨幣符號與千分位。
- 報表範例如下：
- 依照上題的統計結果，繪製業務一課到業務四課的部門統計圖(3D圓形)
- 加上圖例，12pt
- 資料標籤以百分比呈現，12pt
- 標題加上 "90年業務部門銷售狀況統計圖"。
- 在 product 工作表中加入下列2欄位，
1. 以陣列公式完成90年銷售數量的計算 (sum函數與if函數)90年銷售數量 90年銷售合計
2. 以 sumif 函數完成90年銷售合計的計算 - 根據90年業務員業績統計表，新增一張工作表，命名為 "90年業務員銷售排行榜"，欄位如下
以 rank 函數完成排名業務員 90年業績 排名
90年業績必須加上貨幣符號與千分位。
以業務員姓名, 業績依次排序
| 部門名稱 | 業務姓名 | 客戶寶號 | 連絡人 | 合計 |
|---|---|---|---|---|
| 業務一課 | 王玉治 | 析全電器 | 陳水扁 | $54,520,000 |
| 朝歌實業 | 馬英九 | $32,250,000 | ||
| 王玉治 合計 | $86,770,000 | |||
| 業務一課 合計 | $86,770,000 | |||
| 業務二課 | 向大鵬 | 周家合板 | 徐賢德 | $121,717,980 |
| ....... | ..... | ..... | ..... |
答案繳交方式說明
完成上述要求後，請檢查工作表總數是否為9個，除了原有的5個之外，你應該增加了4個，之後請將結果命名為「OA學號.ods」作為附件，寄到老師的信箱 : peterju.tw@gmail.com
範例參考如下：
主旨：OA期中考
內容：
學號:79432999
身份證:A123456789
附件：OA79432999.ods
操作解答
- 步驟1
- 點選"sale"工作表
- 選取整個A欄
- 功能表：資料/有效
- 允許(A)：選擇儲存格範圍
- 來源(S)：輸入customer.$A$2:$A$61
- 確定
- 選取整個C欄
- 功能表：資料/有效
- 允許(A)：選擇儲存格範圍
- 來源(S)：輸入product.$A$2:$A$21
- 步驟2
- 點選"sale"工作表
- 點選清單中任一儲存格
- 功能表：資料/篩選/自動篩選
- 篩選交易年為 90，並選取篩選結果，滑鼠右鍵，選複製
- 新增工作表並命名為"90年銷售狀況"
- 在"90年銷售狀況"工作表貼上複製的結果
- 依題目要求調整或插入需要的欄位
- 在部門名稱欄位B2儲存格輸入下列公式後，雙擊控點
=VLOOKUP(VLOOKUP(A2;employee.$A$2:$B$98;2;0);dept.$A$2:$B$18;2;0) - 在客戶寶號欄位D2儲存格輸入下列公式後，雙擊控點
=VLOOKUP(C2;customer.$A$2:$B$61;2;0) - 在產品名稱欄位F2儲存格輸入下列公式後，雙擊控點
=VLOOKUP(E2;product.$A$2:$B$21;2;0) - 在單價欄位H2儲存格輸入下列公式後，雙擊控點
=VLOOKUP(E2;product.$A$2:$D$21;3;0) - 在總額欄位I2儲存格輸入下列公式後，雙擊控點
=G2*H2 - 在連絡人欄位J2儲存格輸入
=VLOOKUP(C2;customer.$A$2:$F$61;6;0) - 步驟3
- 複製"90年銷售狀況"工作表中的清單
- 新增工作表並命名為"90年業務員業績統計表"
- 在"90年業務員業績統計表"工作表貼上複製的結果
- 點選清單中任一儲存格
- 功能表：資料/小計
- 勾選小計用於"總額"
- 步驟4
- 點選"90年銷售狀況"工作表清單中任一儲存格
- 功能表：資料/資料助理/啟動：確定
- 依序將欄位依下列順序拖曳到列欄位(F)
部門名稱，業務姓名，客戶寶號，連絡人 - 將總額拖曳到資料欄位(G)
- 點選右下角的"其他"鈕，將匯出區域改為新建工作表
- 點選列欄位(F)中的"部門名稱"，再按右邊的選項鈕
- 小計選擇使用者定義：總計，再按右邊的選項鈕
- 排序順序選擇"升冪"，確定，確定
- 點選列欄位(F)中的"業務姓名"，再按右邊的選項鈕
- 小計選擇使用者定義：總計，再按右邊的選項鈕
- 排序順序選擇"升冪"，確定，確定，確定
- 選取E欄，功能表：格式/儲存格
- 數字頁籤中選擇貨幣分類
- 格式選擇英文(美國)，選擇最上方的樣式
- 工作表命名為"90年業務部門銷售狀況統計表"
- 步驟5
- 點選"90年業務部門銷售狀況統計表"工作表
- 按住Ctrl然後選取業務一課到業務四課的小計欄位名與金額
A23, E23, A44, E44, A65, E65, A83, E83 - 點選圖表的圖示，選擇清單旁邊的空白處
- 圖表類型選取圓餅圖，勾選3D外觀
- 插入資料標籤，勾選顯示數值，當作百分比
- 插入標題，填入 90年業務部門銷售狀況統計圖
- 雙擊資料標籤，改變文字大小為12pt
- 雙擊圖例，改變文字大小為12pt
- 步驟6
- 點選"product"工作表
- 在E1到F1儲存格依序輸入90年銷售數量 , 90年銷售合計
- 在E2儲存格輸入下列公式，輸入完成按下Ctrl+Shift+Enter
=SUM(IF(90年銷售狀況.$E$2:$E$94=A2;90年銷售狀況.$G$2:$G$94;0)) - 在F2儲存格輸入下列公式，然後雙擊控點
=SUMIF(90年銷售狀況.$E$2:$E$94;A2;90年銷售狀況.$I$2:$I$94) - 因為陣列公式在此例不可使用雙擊控點的方式複製公式，必須先複製E2儲存格，再逐一貼到E3..E21 (若使用資料範圍的名稱定義作法，則不在此限)
- 步驟7
- 新增一個"90年業務員銷售排行榜"工作表
- 在A1到C1儲存格依序輸入
業務員 , 90年業績 , 排名 - 使用參照儲存格或複製"90年業務員業績統計表"工作表中每個業務員的姓名與業績額
- 在C2儲存格輸入下列公式，並雙擊控點
=RANK(B2;$B$2:$B$17) - 選取整個清單 A1:C17
- 功能表：資料/排序
- 依題意排定先後順序，確定