2011年10月31日 星期一

Excel-試算連續開根號

在數學中常見的運算式:

如果要以 Excel 來求得結果,要如何運算?之前文章曾提到以循環參照之反覆運算來求結果:

火星人的天空之城: Excel-使用循環參照來運算

 

【基本運算】

image

以公式代入2 為例:

儲存格B3:=B2^0.5

儲存格B4:=(B$2-B3)^0.5

複製儲存格B4,往下各列貼上。

你會發現運算結果愈來愈趨近於「1」,也就是在本例中可得:不須計算至無窮多個,即可得到運算結果。

 

【延伸運算】

同樣原理套用在當代入 6, 12, 20 時,也可以得到整數結果(分別為 2, 3, 4 )。

2011年10月30日 星期日

Google-Google文件新增簡報功能

Google 又針對 Google 文件新增了非常多的簡報功能,目前你可以選擇試用。你可以選取頁面最右上角的「設定」(尺輪狀圖示),在「編輯」項下啟用「使用最新版的簡報編輯器建立新的簡報。」

進入新版的啟動畫面:(新的繪圖工具、製作動畫、協同合作)

可以挑選一個一主題樣式:

進入編輯畫面:

你可以看到一些新增的功能,例如插入文字藝術、插入動畫、新的排列功能表、新的格式功能表等。

當你在[投影片]功能表選擇變更轉場效果,可以設定投影片的轉場動畫。

當你選取了一個物件時,也可以為這個物件設定動畫效果。

試用看看,很快就可以上手的。

Excel-學生考試成績處理分析

在 Excel 中取得一個學生考試成績表(如下圖),其中每個人的平均分數是一個加權平均。

試著建立一個可以藉由改變微調按鈕,可以顯示每個學生的成績和平均成績的對照表,和動態的圖表。

【處理成績表】

1. 計算加權平均

儲存格H3:=SUMPRODUCT($C$1:$G$1,C3:G3)/SUM($C$1:$G$1)

2. 計算名次

儲存格I3:=RANK(H3,$H$3:$H$22)

複製儲存格H3:I3,往下各列貼上。

 

【處理個人成績】

1. 在[開發人員/控制項]下,選取[表單控制項]的「微調按鈕」。

2. 在微調按鈕上按一下右鍵,選取「控制項格式」。

3. 設定最小值:1、最大值:20、遞增值1,儲存格連結:$A$26。

4. 由成績表查詢得到個人成績。

儲存格B26:=INDEX($A$3:$I$22,MATCH($A$26,$A$3:$A$22,0),COLUMN(B:B))

複製儲存格B26,貼至儲存格B26:I26。

MATCH($A$26,$A$3:$A$22,0)

以儲存格A26的內容找到資料在資料表中的第幾列。

INDEX($A$3:$I$22,MATCH($A$26,$A$3:$A$22,0),COLUMN(B:B))

利用查表方式求得指定列的每一欄資料,其中COLUMN(B:B)在往右複製時會變成COLUMN(C:C)、COLUMN(D:D)、…,可以求得第2欄、第3欄、第4欄、…的資料。

 

【處理個人圖表】

1. 選取儲存格C25:G27。

2. 選取[插入/圖表]區中的[直條圖/群組直條圖]。

3. 在[平均]數列上按一下右鍵,選取「變更數列圖表類型」選項。

4. 選取「折線圖」。

 

【進階處理個人圖表】

如果你不想透過個人成績表來動態查詢成績再製作圖表,則可以使用以下的方法:

1. 先定義一個名稱:DATA

參照到:=OFFSET(工作表1!$C$3,MATCH(工作表1!$A$26,工作表1!$A$3:$A$22,0)-1,,,5)

注意到範圍要選「工作表1」(指定給參照範圍在工作表1)

相關位址請使用絶對參照,例如:工作表1!$A$26、工作表1!$A$3:$A$22等。OFFSET函數會產生一個動態的位址,例如儲存格A26為1時,位址為C3:G3,而儲存格A26為2時,位址為C4:G4,…。

2. 選取儲存格C2:G3,按照 Ctrl 鍵,再選取儲存格C23:G23。

(為了便於說明,下圖中將部分列予以隱藏。)

3. 仿【進階處理個人圖表】說明之方式建立圖表。

4. 在圖表中點選個人成績數列(藍色)。

資料編輯列顯示:=SERIES(,工作表1!$C$2:$G$2,工作表1!$C$3:$G$3,1)

5. 在資料編輯列中,將「工作表1!$C$3:$G$3」改成「DATA」。

透過微調按鈕的變動,也可以在一個圖表中,顯示每一個人的成績圖表。

2011年10月29日 星期六

資料處理-3

資料處理-1

資料處理-2

資料處理-3

資料處理-4

資料處理-5

資料處理-6

資料處理-7

資料處理-8

資料處理-9

資料處理-10

資料處理-11

資料處理-12

資料處理-13

資料處理-14

資料處理-15

資料處理-16

資料處理-17

資料處理-18

【重點主題】

  • 文字辨識
  • 輸入法整合器的應用
  • 數值格式設定

課程連結】

練習題】

  • 利用輸入法整合器,利用手寫、筆劃、部首等方式輸入「資料處理」。
  • 試利用掃描器掃描一張A4大小的印刷字體文件,並執行文字辨識。
  • 試利用數位相機拍攝一張A4大小的印刷字體文件,並執行文字辨識。

延伸學習】

資料處理-1

【重點主題】

  • 本學期教學重點
  • 公式輸入方式
  • SUM函數練習
  • 自訂清單練習

課程連結】

※練習題

  • 試以各種方式練習產生SUM函數的運算結果。

※延伸學習

資料處理-18

資料處理-1

資料處理-2

資料處理-3

資料處理-4

資料處理-5

資料處理-6

資料處理-7

資料處理-8

資料處理-9

資料處理-10

資料處理-11

資料處理-12

資料處理-13

資料處理-14

資料處理-15

資料處理-16

資料處理-17

資料處理-18

【重點主題】

課程連結】

練習題】

延伸學習】

資料處理-17

資料處理-1

資料處理-2

資料處理-3

資料處理-4

資料處理-5

資料處理-6

資料處理-7

資料處理-8

資料處理-9

資料處理-10

資料處理-11

資料處理-12

資料處理-13

資料處理-14

資料處理-15

資料處理-16

資料處理-17

資料處理-18

【重點主題】

課程連結】

練習題】

延伸學習】

資料處理-16

資料處理-1

資料處理-2

資料處理-3

資料處理-4

資料處理-5

資料處理-6

資料處理-7

資料處理-8

資料處理-9

資料處理-10

資料處理-11

資料處理-12

資料處理-13

資料處理-14

資料處理-15

資料處理-16

資料處理-17

資料處理-18

【重點主題】

課程連結】

練習題】

延伸學習】

資料處理-15

資料處理-1

資料處理-2

資料處理-3

資料處理-4

資料處理-5

資料處理-6

資料處理-7

資料處理-8

資料處理-9

資料處理-10

資料處理-11

資料處理-12

資料處理-13

資料處理-14

資料處理-15

資料處理-16

資料處理-17

資料處理-18

【重點主題】

課程連結】

練習題】

延伸學習】

資料處理-14

資料處理-1

資料處理-2

資料處理-3

資料處理-4

資料處理-5

資料處理-6

資料處理-7

資料處理-8

資料處理-9

資料處理-10

資料處理-11

資料處理-12

資料處理-13

資料處理-14

資料處理-15

資料處理-16

資料處理-17

資料處理-18

【重點主題】

課程連結】

練習題】

延伸學習】

資料處理-13

資料處理-1

資料處理-2

資料處理-3

資料處理-4

資料處理-5

資料處理-6

資料處理-7

資料處理-8

資料處理-9

資料處理-10

資料處理-11

資料處理-12

資料處理-13

資料處理-14

資料處理-15

資料處理-16

資料處理-17

資料處理-18

【重點主題】

  • VLOOKUP 函數+ INDEX 函數+ MATCH 函數

VLOOKUPhttp://office.microsoft.com/zh-tw/excel-help/HP010343011.aspx

VLOOKUP:用來搜尋儲存格範圍的第一欄,然後從範圍同一列的任何儲存格傳回一個值。

語法:VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value:在表格或範圍的第一欄中搜尋的值。

table_array:包含資料的儲存格範圍。可以使用範圍的參照,也可以使用範圍名稱。

col_index_numtable_array 引數中必須傳回相符值的欄號。

range_lookup:這是一個邏輯值,用以指定VLOOKUP應該要尋找完全符合還是大約符合的值。

特別說明:在使用VLOOKUP函數時,因為 range_lookup 設為 FALSE,則 VLOOKUP 只會尋找完全符合的值。如果 table_array 第一欄中有兩個以上的值與 lookup_value 相符,將會使用第一個找到的值。如果找不到完全符合的值,則傳回 #N/A 錯誤值。

註解

table_array 的第一欄中搜尋文字值時,請確定 table_array 第一欄中的資料不包含前置空格、結尾空格、不成對的直引號 ( ' " ) 及彎引號 ( ‘ “ ),以及非列印字元。否則,VLOOKUP 可能會傳回不正確或非預期的值。

如果 range_lookup FALSE lookup_value 為文字,則您可以在 lookup_value 中使用萬用字元 (問號 (?) 和星號 (*))。問號代表任何單一字元;星號代表任何字元序列。如果您是要尋找實際的問號或星號,請在該字元前輸入波狀符號 (~)

 

INDEXhttp://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx

INDEX:傳回表格或範圍內的某個值或值的參照。

語法:INDEX(array, row_num, [column_num])

Array:儲存格範圍或陣列常數。

Row_num:選取陣列中傳回值的列。

Column_num:選取陣列中傳回值的欄。

 

MATCHhttp://office.microsoft.com/zh-tw/excel-help/HP010342679.aspx

MATCH:搜尋某儲存格範圍內的指定項目,然後再傳回該項目在範圍內的相對位置。

語法:MATCH(lookup_value, lookup_array, [match_type])

lookup_value:在 lookup_array 中尋找比對的值。

lookup_array:要搜尋的儲存格範圍。

match_type:這是一個數字,其值有三種可能:(預設值為 1)

1 或省略:找到等於或僅次於 lookup_value 的值。lookup_array 引數內的值必須以遞增次序排列。

0:找第一個完全等於 lookup_value 的比較值。lookup_array 引數內的值可以依任意次序排列。

-1:找到等於或大於 lookup_value 的最小值。lookup_array 引數內的值必須以遞減次序排序。

 

課程連結】

 

練習題】

 

延伸學習】

資料處理-12

資料處理-1

資料處理-2

資料處理-3

資料處理-4

資料處理-5

資料處理-6

資料處理-7

資料處理-8

資料處理-9

資料處理-10

資料處理-11

資料處理-12

資料處理-13

資料處理-14

資料處理-15

資料處理-16

資料處理-17

資料處理-18

【重點主題】

  • SUMPRODUCT 函數介紹

選取儲存格A1:D26,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」選項,建立「類別、售價、打折、數量」四個名稱。

image[7]

【建立公式】

(1) 所有售出的原價總和

儲存格G2:=SUMPRODUCT(售價,數量)

(2) 所有售出折扣後總和

儲存格G3:=SUMPRODUCT(售價,打折,數量)

(3) 售價大於1500的項目數

儲存格G4:=SUMPRODUCT(--(售價>1500))

公式中的「--」,乃是為了將公式中的 True/False 陣列轉換為 1/0 陣列來進行運算。

(4) 售價大於1500的總數量

儲存格G5:=SUMPRODUCT(--(售價>1500),數量)

也可以寫成如下形式:

儲存格G5:=SUMPRODUCT((售價>1500)*數量)

當使用「*」運算時,也會自動將公式中的 True/False 陣列轉換為 1/0 陣列進行運算。

(5) 類別B的銷售數量總和

儲存格G6:=SUMPRODUCT(--(類別="B"),數量)

(6) 類別A的銷售金額總和

儲存格G7:=SUMPRODUCT(--(類別="A"),售價,打折,數量)

(7) 打折不大於80%的銷售總額

儲存格G8:=SUMPRODUCT(--(打折<80%),售價,打折,數量)

(8) 類別D中打折不大於80%的銷售數量

儲存格G9:=SUMPRODUCT(--(類別="D"),--(打折<80%),數量)

(9) 打折50%的銷售總額

儲存格G10:=SUMPRODUCT(--(打折=50%),售價,打折,數量)

(10) 類別C中數量大於10的銷售總額

儲存格G11:=SUMPRODUCT(--(類別="C"),--(數量>10),售價,打折,數量)

相關函數說明,請參閱微軟網站。

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

 

課程連結】

 

練習題】

 

延伸學習】

SUMPRODUCT 函數是將參數中的陣列相乘後加總,如果要將以上10個運算,全部改以「陣列公式」來運算,該如何撰寫公式?記得:輸入完陣列公式,要按 Ctrl+Shift+Enter 鍵。

儲存格G2:={=SUM(售價*數量)}

儲存格G3:{=SUM(售價*打折*數量)}

儲存格G4:{=SUM(--(售價>1500))}

儲存格G5:{=SUM((售價>1500)*數量)}

儲存格G6:{=SUM((類別="B")*數量)}

儲存格G7:{=SUM((類別="A")*售價*打折*數量)}

儲存格G8:{=SUM((打折<80%)*售價*打折*數量)}

儲存格G9:{=SUM((類別="D")*(打折<80%)*數量)}

儲存格G10:{=SUM((打折=50%)*售價*打折*數量)}

儲存格G11:{=SUM((類別="C")*(數量>10)*售價*打折*數量)}

好康東東