1 Excel 的資料處理與分析架構的方式呈現, 而在上述的範例資料當中, 各種商品名稱並沒有以資料欄位的方式呈現, 因此, 我們必須建立一個名為商品的欄位來存放各種商品名稱 ; 同理, 上述的範例資料當中, 年度各季名稱也沒有以資料欄位的方式呈現, 而是以水平方向的報表標題方式陳列於儲存格

Size: px

Start display at page:

Download "1 Excel 的資料處理與分析架構的方式呈現, 而在上述的範例資料當中, 各種商品名稱並沒有以資料欄位的方式呈現, 因此, 我們必須建立一個名為商品的欄位來存放各種商品名稱 ; 同理, 上述的範例資料當中, 年度各季名稱也沒有以資料欄位的方式呈現, 而是以水平方向的報表標題方式陳列於儲存格"

奠各焉
5 years ago
Views:

特定題目的回應比例分析表性別地區的人數統計分析表因為, 這些分析表才是老闆所關心或決策者所要掌握的 1. 鉅細靡遺的記載逐筆資料記錄是原生的資料表格資料庫的結構, 並不是老闆與客戶所要檢視的最終資訊例如 :758 份未經整理的問卷調查資料 2.

1 1.1 Excel 資料處理與分析的能力對一個資料庫而言, 資料表裡所存放的資料僅僅是一筆筆資料記錄的集合, 對很多人來說, 這不過是一個蒐集儲存資料的大容器但是, 若要從中取得具備特定意義或特殊目的的資訊, 就非得透過有效的資料分析技巧來完成了! 例如 : 一份訪問了 758 位用戶的客戶滿意度調查資料, 記錄著 758 筆資料記錄, 您絕對不會將這 758 資料記錄的原始資料 (Raw Data) 列印成冊, 呈遞給老闆 ( 不被 Fire 才怪 ), 您一定會經過資料分析的技巧, 統計或摘要出各年齡層的結構分析表特定題目的回應比例分析表性別地區的人數統計分析表因為, 這些分析表才是老闆所關心或決策者所要掌握的 1. 鉅細靡遺的記載逐筆資料記錄是原生的資料表格資料庫的結構, 並不是老闆與客戶所要檢視的最終資訊例如 :758 份未經整理的問卷調查資料 2. 經過摘要整理運算的資訊表格, 才是老闆與客戶所需的有用資訊例如 : 不同年齡層不同性別的各地區人數統計 3. 針對問卷裡的題目進行回應的摘要分析, 例如 : 各地區是否裝接數位電視系統的摘要統計才是決策者所重視的 Excel 提供了許多操作工具與函數運算, 可以讓您直覺地應用於資料處理工作, 甚至設計出複雜的資料分析, 以符合種種的需求 1-3

1 Excel 的資料處理與分析架構的方式呈現, 而在上述的範例資料當中, 各種商品名稱並沒有以資料欄位的方式呈現, 因此, 我們必須建立一個名為商品的欄位來存放各種商品名稱 ; 同理, 上述的範例資料當中, 年度各季名稱也沒有以資料欄位的方式呈現, 而是以水平方向的報表標題方式陳列於儲存格 D2:G2 中, 因此, 我們也必須建立一個名為季別

2 1 Excel 的資料處理與分析架構的方式呈現, 而在上述的範例資料當中, 各種商品名稱並沒有以資料欄位的方式呈現, 因此, 我們必須建立一個名為商品的欄位來存放各種商品名稱 ; 同理, 上述的範例資料當中, 年度各季名稱也沒有以資料欄位的方式呈現, 而是以水平方向的報表標題方式陳列於儲存格 D2:G2 中, 因此, 我們也必須建立一個名為季別的欄位來存放年度各季名稱地區雖以資料欄位方式呈現, 但是, 合併儲存格的效果必須移除, 每一個單一儲存格裡都必須要填入資料此外, 表格裡的空白欄列也都要刪去最後, 整理出以下 5 個資料欄位的資料表 : 雖然這是一個篇幅頗長的資料表格, 但是卻是個道道地地不折不扣, 可進行樞紐分析與任何資料處理作業的資料表 (Data Table) 1-16

也可以設定資料橫條的框線格式與色彩步驟 09 Excel 2016 提供了調整資料橫條圖方向的功能設定請改為由右至左的方向, 最後, 點按 [ 確定 ]

3 1.4 視覺化的格式效果步驟 05 步驟 06 步驟 07 開啟 [ 設定格式化的條件規則管理員 ] 對話方塊, 點選顯示格式化規則為 [ 目前的選取 ] 點選所設定的資料橫條規則點按 [ 編輯規則 ] 按鈕步驟 08 開啟 [ 編輯格式化規則 ] 對話方塊, 在此可以調整資料橫條的填滿格式與色彩, 也可以設定資料橫條的框線格式與色彩步驟 09 Excel 2016 提供了調整資料橫條圖方向的功能設定請改為由右至左的方向, 最後, 點按 [ 確定 ] 按鈕結束此對話操作步驟 10 返回 [ 設定格式化的條件規則管理員 ] 對話方塊, 完成格式化規則的編輯, 點按 [ 確定 ] 按鈕步驟 11 工作表裡的 B 欄資料, 已改成由右至左方向的資料橫條效果 1-29

2 樞紐分析表的基本操作 2.2 2.2.1 建立樞紐分析表以下的實作演練中, 將針對 13 萬筆交易的資料進行樞紐分析, 以瞭解各縣市各種不同交易方式的總交易金額統計步驟 01 開啟北風公司 12-15 交易資料 (13 萬筆 ).

4 2 樞紐分析表的基本操作建立樞紐分析表以下的實作演練中, 將針對 13 萬筆交易的資料進行樞紐分析, 以瞭解各縣市各種不同交易方式的總交易金額統計步驟 01 開啟北風公司交易資料 (13 萬筆 ).xlsx 活頁簿檔案, 切換至交易記錄工作表, 此工作表內含括 13 萬筆交易資料步驟 02 步驟 03 點按插入索引標籤點按表格群組裡的樞紐分析表命令按鈕 2-12

2 樞紐分析表的基本操作步驟 05 點按設定交叉分析篩選器格式窗格裡的位置和版面配置區段, 展開此區段的設定操作步驟 06 調整版面配置的欄數為 2 步驟 07 步驟 08 步驟 09 點按設定交叉分析篩選器格式窗格裡的大小區段, 展開此區段的設定操作, 並在此調整交叉分析篩選器的高度與寬度經手人交叉分析篩選器立即變成兩欄式指定大小按鈕版面若有需求,

5 2 樞紐分析表的基本操作步驟 05 點按設定交叉分析篩選器格式窗格裡的位置和版面配置區段, 展開此區段的設定操作步驟 06 調整版面配置的欄數為 2 步驟 07 步驟 08 步驟 09 點按設定交叉分析篩選器格式窗格裡的大小區段, 展開此區段的設定操作, 並在此調整交叉分析篩選器的高度與寬度經手人交叉分析篩選器立即變成兩欄式指定大小按鈕版面若有需求, 亦可在此調整按鈕的高度與按鈕的寬度同樣的操作方式也可以針對其他 [ 交叉分析篩選器 ] 進行版面配置與按鈕大小的變更例如 : 縣市 [ 交叉分析篩選器 ] 調整為四欄式的按鈕版面 ; 商品類別 [ 交叉分析篩選器 ] 調整為兩欄式的按鈕版面此外, 在工作表上的 [ 交叉分析篩選器 ] 由於都是浮貼式的物件, 因此, 若有調整工作表欄寬列高或新增 / 刪除欄列的操作時, 所在位置的 [ 交叉分析篩選器 ] 是否會自動改變其大小或所在位置, 或是期望能保持在工作表上的固定位置, 都可以藉由 [ 設定交叉分析篩選器格式 ] 窗格裡的 [ 屬性 ] 區段, 展開各項相關設定不同資料屬性的資料欄位, 在建置成 [ 交叉分析篩選器 ] 後, 其版面配置與按鈕大小的變更, 若能取決於實質資料欄位內容, 將可以讓 [ 交叉分析篩選器 ] 的外觀更美觀也更符合需求, 製作出更具彈性與操控性的數位儀表板 2-46

6 4.1 美化樞紐分析表步驟 05 開啟 [ 儲存格格式 ] 對話方塊, 點選 [ 數值 ] 類別步驟 06 設定小數位數為 0 步驟 07 步驟 08 步驟 09 步驟 10 勾選 [ 使用千分位 (,) 符號 ] 核取方塊點按 [ 確定 ] 按鈕, 結束 [ 儲存格格式 ] 對話方塊回到 [ 值欄位設定 ] 對話方塊, 點按 [ 確定 ] 按鈕樞紐分析表上的交易筆數數值, 已經改成具備千分位符號的數值顯示格式空白與零值的處理一個好的財務報表設計, 在統計數字中應該不能留有空白的區塊, 讓人有事後填入的遐想空間而樞紐分析表 [ 值 ] 區域的統計結果或許會出現空白儲存格 ( 沒有資料 ) 的答案, 極有可能是資料來源裡含有空白儲存格, 導致交叉統計的結果以空格呈現不過, 藉由 [ 樞紐分析表選項 ] 對話方塊的操作, 您可以將這些空白儲存格改以填入 0 的格式來顯示此樞紐分析表 4-9

7 4.1 美化樞紐分析表 5. 僅勾選樞紐分析表樣式選項裡的 [ 帶狀欄 ] 核取方塊 6. 勾選樞紐分析表樣式選項裡的所有核取方塊設定預設的樞紐分析表樣式不論是 Excel 內建的樞紐分析樣式, 還是使用者自訂的客製化樞紐分析表樣式, 都可以透過以下的操作步驟, 將其設定為預設的樞紐分析表樣式, 如此, 爾後在建立新的樞紐分析表時, 即可立即以此預設的樞紐分析表樣式呈現 4-19

8 4 自訂化樞紐分析表步驟 01 步驟 02 步驟 03 步驟 04 點按 [ 樞紐分析表工具 ] 底下的 [ 設計 ] 索引標籤展開 [ 樞紐分析表樣式 ] 群組裡的樞紐分析表樣式清單以滑鼠右鍵點按想要成為預設樞紐分析表樣式的樞紐分析表樣式圖示例如 : 自訂的樞紐分析表樣式再從展開的快顯功能表中點選 [ 設為預設 ] 選項佈景主題的套用通常一份文件的內容包羅萬象, 或許包含了文字區塊表格圖案圖表圖形, 面對這些不同性質的元件, 有各自不同的格式化操作與屬性設定, 要分別為這些元件選擇相互輝映的色彩樣式, 的確是捉襟見肘又難得完美不過, 有了佈景主題的功能, 不但簡化了格式化物件的程序, 建立風格一致外觀專業的文件, 已經變得更加容易且人人皆可上手如下圖所示的三個不同應用程式文件,Excel Word 與 PowerPoint 都套用了相同的佈景主題, 具備了相同色系的色彩與格式效果 4-20

5.2 彙整資料的函數與值的顯示方式步驟 01 步驟 02 步驟 03 步驟 04 步驟 05 以滑鼠右鍵點選樞紐分析表裡某年某季某地區某種車款的總交易金額, 例如儲存格 C18 從顯示的快顯功能表中點選 [ 值的顯示方式 ] 功能選項在展開的副功能選單中點選 [ 父項總和百分比 ] 選項開啟 [ 值的顯示方式 ( 總交易金額 )] 對話方塊, 基本欄位請選年點按

9 5.2 彙整資料的函數與值的顯示方式步驟 01 步驟 02 步驟 03 步驟 04 步驟 05 以滑鼠右鍵點選樞紐分析表裡某年某季某地區某種車款的總交易金額, 例如儲存格 C18 從顯示的快顯功能表中點選 [ 值的顯示方式 ] 功能選項在展開的副功能選單中點選 [ 父項總和百分比 ] 選項開啟 [ 值的顯示方式 ( 總交易金額 )] 對話方塊, 基本欄位請選年點按 [ 確定 ] 按鈕, 關閉 [ 值的顯示方式 ( 總交易金額 )] 對話方塊的操作如此, 樞紐分析表裡的各摘要值將與其所隸屬的群組合計進行百分比例的運算, 顯示出百分比例以此實作為例, 樞紐分析表裡的摘要值 ( 項目值 ) 將除以所選 [ 年 ] 度欄位的群組合計 ( 父項目值 ), 以顯示各地區各車款在第 1 季與第 2 季的交易金額佔上半年度合計交易金額的百分比 5-29

6 視覺化樞紐分析圖表步驟 04 步驟 05 滑鼠游標停在展開的圖表樣式之縮圖上, 立即預覽樞紐分析圖套用該樣式後的結果點按縮圖後即可將選擇的圖表樣式套用於樞紐分析圖上步驟 06 步驟 07 步驟 08 在展開的圖表樣式與色彩清單上, 點選 [ 色彩 ] 選項滑鼠游標停在展開的圖表色彩清單上, 立即預覽樞紐分析圖套用該色彩組合的結果點按選定的色彩組合後,

10 6 視覺化樞紐分析圖表步驟 04 步驟 05 滑鼠游標停在展開的圖表樣式之縮圖上, 立即預覽樞紐分析圖套用該樣式後的結果點按縮圖後即可將選擇的圖表樣式套用於樞紐分析圖上步驟 06 步驟 07 步驟 08 在展開的圖表樣式與色彩清單上, 點選 [ 色彩 ] 選項滑鼠游標停在展開的圖表色彩清單上, 立即預覽樞紐分析圖套用該色彩組合的結果點按選定的色彩組合後, 即可在樞紐分析圖上看到套用色彩的成果 6.3 迷你圖表 (Mini Charts) 以及超迷你圖表 (Tiny Carts) 目前在歐美是極為風行與流傳的趨勢分析工具, 透過這種型態的圖表可以讓人在龐大的數據資料中, 輕鬆瞭解資料彼此之間複雜的關係最典型的迷你圖表便是 Sparklines, 這是一種可以在文字中顯示卻又不會破壞排版的圖表 ( 如圓餅圖趨勢線等 ), 也就是說,Sparklines 是一種視覺化資訊圖表類型的統計圖表, 其特性是僅佔一列文字高度的小尺寸高資料密度之統計圖表利用 Sparklines 可以根據諸如平均溫度或股市行情變化等等部份資料的預估與測量, 以簡單明瞭濃縮聚焦的方式, 來呈現資訊的趨勢與變動您只要 Google 一下關鍵字 Sparklines 便可以看到許多 Sparklines 圖表的案例圖片 6-22

7.2 匯入 Access 資料庫步驟 27 輸入地區學校錄取分數及 PR 等欄位標題文字步驟 28 選取儲存格範圍 A2:A78 步驟 29 步驟 30 步驟 31 以滑鼠右鍵點按選取範圍從展開的快顯功能表中點選 [ 刪除 ] 功能選項開啟 [ 刪除 ] 對話方塊後, 點選 [ 右側儲存格左移 ] 選項, 然後, 點按 [ 確定 ] 按鈕最後,

11 7.2 匯入 Access 資料庫步驟 27 輸入地區學校錄取分數及 PR 等欄位標題文字步驟 28 選取儲存格範圍 A2:A78 步驟 29 步驟 30 步驟 31 以滑鼠右鍵點按選取範圍從展開的快顯功能表中點選 [ 刪除 ] 功能選項開啟 [ 刪除 ] 對話方塊後, 點選 [ 右側儲存格左移 ] 選項, 然後, 點按 [ 確定 ] 按鈕最後, 只要再透過儲存格格式或格式化為資料表的操作, 即可完成美觀大方的資料表成品了 1. 變裝前 2. 變裝後透過格式化為表格的操作, 快速將選取的儲存格範圍 A2:D78 轉換為具備專屬樣式的資料表格 7.2 Access Microsoft Access 也是中小企業裡經常會使用到的資料庫系統, 在大型企業的一般使用者, 也常以此資料庫作為中介資料或用戶端的存取界面在大型資料庫系統或有安全性顧慮的資料庫系統中, 亦常會將需要使用的資料經過篩選過濾, 再以 Access 檔案格式匯出給使用者, 所以, 如何將 Access 料庫檔案匯入連結到 Excel 工作表再進行資料分析, 也是您必修的學分喔 ~ 7-29

12 7 外部資料的連結及匯入直接開啟 Access 資料庫裡的資料表或查詢以下的操作範例練習中, 我們將準備以直接開啟舊檔的操作, 取得 Access 資料庫檔案格式之 [ 北風公司.accdb] 資料庫裡的 [ 供應商 ] 資料表因為,Excel 2016 可以直接開啟 Access 資料庫檔案, 形成一張資料表這是 Access 資料庫系統典型的資料庫範例 - 北風資料庫, 目前開啟的畫面是 [ 供應商 ] 資料表 7-30

ACI pdf

ACI pdf 09 9.1 -...9-2 9.1.1...9-2 9.1.2...9-3 9.2 -...9-4 9.2.1 PMT - ()...9-4 9.2.2...9-6 9.3 -...9-8 9.3.1 PMT - ()...9-8 9.4...9-10 9.4.1... 9-11 9.4.2...9-12 9.4.3...9-14 9.5 -...9-17 9.5.1...9-18 1 Excel...9-21