Microsoft EXCEL (II)

電子計算機中心 系統發展組

江維信

Why data analyze

當專案失敗時

牽拖/推卸責任

檢討

千錯萬錯都是你的錯

政策制定

新企畫案

構想

參考前例

測試

實作

修正

資料蒐集

數據

主/客觀因素

專家意見

資訊

方案 決策

實作

檢討與修正

Why data analyze

資料蒐集

數據

主觀因素

客觀因素

專家意見

資訊 方案

決策

檢討與修正

1秒鐘 ?

1分鐘 ?

1天?

1個月?

1年?

Why data analyze

資料收集(資料)

資料統計(資訊)

資料分析(策略)

依據理論(學理)

依據過去經驗

依據專家建議

…..

試算表

EXCEL 基礎應用

工作表

儲存格

資料類型

輸入輔助工具

格式化條件

格式化表格

自訂公式

內建函數

連結

保密

繪圖(統計表)

大綱

資料庫概念

EXCEL資料應用

資料取代

資料排序

資料小計

資料篩選

資料列印

EXCEL 進階應用

樞紐分析

統計學上的應用

資料匯入/匯出

商業管理實例應用

範例:問卷調查應用

資料庫的概念

資料 VS 資訊

資料屬性 / 欄位 / 一筆資料 / 表格 / 資料庫 / 資料倉儲

EX: G892906 / 江大胖 / 資訊系 / 19721002 / 174 / 85

G892907 / 王大頭 / 資訊系 / 19760112 / 165 / 50

G892908 / 甄美麗 / 中文系 / 19851210 / 152 / 45

欄位

一筆資料

學生基本資料表

學生成績資料表

學生選課資料表

學生兵役資料表

學生借書資料表

學生資料庫

EXCEL-簡易資料庫應用

主要鍵値(primary key

問卷調查應用(問卷調查範例_1.xls)

原稿 編碼

EXCEL應用-問卷調查

利用編輯/取代簡化輸入動作

EXCEL資料應用-資料取代

資料排序/小計1.注意標題列

2.選擇鍵值

排序方式

EXCEL資料應用-資料排序

資料排序/小計

1.選擇欄位

2.選擇使用函數

EXCEL資料應用-資料小計

資料排序/小計

EXCEL資料應用-資料小計

自動篩選過濾資料

複合式

EXCEL資料應用-資料篩選

EXCEL資料應用-資料列印

EXCEL在統計上的應用

樞紐分析

EXCEL統計應用-樞紐分析

樞紐分析

EXCEL統計應用-樞紐分析

1.決定要分析的問題

2.決定主要鍵值

3.將欄位拖曳至 [ ∑ 值]

4.選擇欄位值設定

樞紐分析

EXCEL統計應用-樞紐分析

1.選擇分析項目(因素)

2.欄位調整

3.資料篩選

4.繪製統計圖

5.列印資料

樞紐分析

EXCEL統計應用-統計圖表

樞紐分析

EXCEL統計應用-統計圖表

統計學中的t檢定

常以樣本平均數對母群體中某數值做假設檢定(單一獨立樣本檢定)，或是以實驗組與對照組兩母群體樣本的樣本平均數做假設檢定(兩獨立樣本檢定)；並藉由 t 值換算與對照「t分佈表」，以判定統計量差異是否顯著(落在拒絕區域，p≦α)。

喜歡網路交易的人，對於各項網路交易特色重視程度，和不喜歡網路交易的人，是否有差異。

EXCEL統計應用-t檢定

設定使用分析工具增益集

EXCEL統計應用-t分配

t 檢定是用來檢查兩個母體的平均數是否相等

EX:【喜歡使用網路交易】與【網路交易特色的重視程度】是否有關

EXCEL統計應用-t分配

喜歡網路交易和不喜歡網路交易者的重視程度平均數相等, 表示兩者之間並沒有明顯的差別；反之, 若兩者的重視程度平均數不相等, 則表示喜歡網路交易和不喜歡網路交易者對於各項網路交易條件的重視程度並不相同。

EXCEL統計應用-t分配

由上面的分析結果中我們可以得知, P ( T<=t ) 雙尾的數值為0.10 大於預設的α 值0.05 , 表示母體之間的平均數並無明顯差異, 因此我們可以說：無論喜不喜歡網路交易, 大家對於網路交易各項條件的重視程度都是相同的。

卡方檢定

卡方 檢定是用來檢定兩個變數是否“獨立”

EX:「收入多寡」與「希望在網路上進行的交易為何」之間是否獨立, 若兩者是獨立的, 表示這兩項之間並不會互相影響；反之, 若它們不獨立, 便表示「收入多寡」會影響「希望在網路上進行的交易為何」。

卡方檢定會使用到2 個變數：實際觀察次數及理論次數。其中理論次數是利用實際觀察次數的機率所求出。此外, 此項檢定的虛無假設為：兩個變數之間彼此獨立, 若最後計算出來的P –值小於α(通常α都預設為0.05 ) , 表示拒絕虛無假設, 即兩個變數不獨立；若P –值大於α, 則接受虛無假設, 兩變數彼此獨立。

EXCEL統計應用-卡方檢定

EXCEL統計應用-卡方檢定

利用樞紐分析結果計算理論次數

公式F5*$B$11/$F$11

EXCEL統計應用-卡方檢定

理論次數算出來之後,就可以插入函數求出卡方檢定的P

值。

請選定B22 儲存格,

切換到公式頁次, 按下函數程式庫區的插入函數鈕, 在統計函數類別中選擇CHITEST 函數。

EXCEL統計應用-卡方檢定

由於P 值= 0.09 大於α值0.05 , 表示不拒絕虛無假設, 則「收入多寡」與「希望在網路上進行的交易為何」之間獨立, 也就是說「希望在網路上進行的交易為何」和「收入多寡之間」並沒有絕對的關係。

EXCEL統計應用

其他應用

次數分配

交叉分析

集中趨勢

離散程度

估計

假設檢定

單因子變異數分析

回歸

樞紐分析實例應用

本校研究所招生範例

立法委員選舉

請同學練習:

1. 請至教學平台上下載 選舉.XLS

2. 將當選欄【◎】取代成【當選】 【●】取代成【保障名額】

【空白欄】取代成【未當選】

3.利用樞紐分析功能整理出

您戶籍縣市的當選人政黨比例並繪製統計圖

當選人號次比例並繪製統計圖

當選人男女比例並繪製統計圖

4.加上頁首頁尾資料

5.列印

(可參考範例或自由發揮)

EXCEL在統計上的應用練習

EXCEL的特性

繪圖 / 樞紐分析 / 函數應用

針對大量原始資料(row data)

資料來源:後端資料庫/資料光碟/實驗機器/網頁 …

轉換資料格式

資料匯出/匯入

資料匯入:最常用來匯入EXCEL的資料為文字檔(間格以逗號或tab最常使用)

存放於大型資料庫中的資料

利用工具存成

不同的檔案格式

資料匯出/匯入

資料匯入:最常用來匯入EXCEL的資料為文字檔(間格以逗號或tab最常使用)

標準文字檔

EXCEL檔

資料匯出/匯入

將文字檔匯入EXCEL

資料匯出/匯入

將文字檔匯入EXCEL

1.設定分隔資料方式

2.1分隔符號

2.2自訂寬度

資料匯出/匯入

將文字檔匯入EXCEL設定資料屬性

資料匯出/匯入

將資料匯出

EXCEL可以匯出不同檔案型態資料格式

資料匯出/匯入

成績計算

產品銷售分析

現金卡、信用卡、小額信貸還款評估

購屋貸款計畫

商業管理應用實例

函數—應用於企業商務

範例: 成績計算

步驟:

1. 輸入學號(編號) -- 利用填滿方式

2. 輸入姓名、成績等資料 -- (略)

3. 計算總成績 – 利用SUM()函數

4. 計算平均 – 利用AVERAGE

5. 計算加權分數 – 利用絕對位置複製

6. 排列名次 – 利用RANK()函數

7. 查詢個別成績 – 利用VLOOKUP()函數

8. 計算合格與不合格人數 利用COUNTIF()函數

函數—應用於企業商務

輸入學號(編號) -- 利用填滿方式

函數—應用於企業商務

計算總成績 – 利用SUM()函數

函數—應用於企業商務

計算平均 – 利用AVERAGE

函數—應用於企業商務

計算加權分數 – 利用絕對位置複製

函數—應用於企業商務

排列名次 – 利用RANK()函數

要用絕對位置

函數—應用於企業商務

查詢個別成績 – 利用VLOOKUP()函數

第 n 個欄位

資料搜尋範圍

搜尋資料的條件依據

邏輯值 0:全部符合

函數—應用於企業商務

計算合格與不合格人數 利用COUNTIF()函數

資料範圍

資料條件