火星人的天空之城: 10/01/2011

2011年10月31日星期一

Excel-試算連續開根號

在數學中常見的運算式：

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

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

【基本運算】

以公式代入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

‧資料處理-1	‧資料處理-2	‧資料處理-3	‧資料處理-4	‧資料處理-5	‧資料處理-6
‧資料處理-7	‧資料處理-8	‧資料處理-9	‧資料處理-10	‧資料處理-11	‧資料處理-12
‧資料處理-13	‧資料處理-14	‧資料處理-15	‧資料處理-16	‧資料處理-17	‧資料處理-18

【重點主題】

本學期教學重點
公式輸入方式
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 函數

VLOOKUP：http://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_num：table_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 中使用萬用字元 (問號 (?) 和星號 (*))。問號代表任何單一字元；星號代表任何字元序列。如果您是要尋找實際的問號或星號，請在該字元前輸入波狀符號 (~)。

INDEX：http://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx

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

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

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

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

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

MATCH：http://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 鍵，勾選「頂端列」選項，建立「類別、售價、打折、數量」四個名稱。

【建立公式】

(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),售價,打折,數量)

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)*售價*打折*數量)}

訂閱：文章 (Atom)

2011年10月31日星期一

2011年10月30日星期日

2011年10月29日星期六

‧資料處理-1

‧資料處理-2

‧資料處理-3

‧資料處理-4

‧資料處理-5

‧資料處理-6

‧資料處理-7

‧資料處理-8

‧資料處理-9

‧資料處理-10

‧資料處理-11

‧資料處理-12

‧資料處理-13

‧資料處理-14

‧資料處理-15

‧資料處理-16

‧資料處理-17

‧資料處理-18

‧資料處理-1

‧資料處理-2

‧資料處理-3

‧資料處理-4

‧資料處理-5

‧資料處理-6

‧資料處理-7

‧資料處理-8

‧資料處理-9

‧資料處理-10

‧資料處理-11

‧資料處理-12

‧資料處理-13

‧資料處理-14

‧資料處理-15

‧資料處理-16

‧資料處理-17

‧資料處理-18

‧資料處理-1

‧資料處理-2

‧資料處理-3

‧資料處理-4

‧資料處理-5

‧資料處理-6

‧資料處理-7

‧資料處理-8

‧資料處理-9

‧資料處理-10

‧資料處理-11

‧資料處理-12

‧資料處理-13

‧資料處理-14

‧資料處理-15

‧資料處理-16

‧資料處理-17

‧資料處理-18

‧資料處理-1

‧資料處理-2

‧資料處理-3

‧資料處理-4

‧資料處理-5

‧資料處理-6

‧資料處理-7

‧資料處理-8

‧資料處理-9

‧資料處理-10

‧資料處理-11

‧資料處理-12

‧資料處理-13

‧資料處理-14

‧資料處理-15

‧資料處理-16

‧資料處理-17

‧資料處理-18

‧資料處理-1

‧資料處理-2

‧資料處理-3

‧資料處理-4

‧資料處理-5