05223AA12.indd

Size: px

Start display at page:

Download "05223AA12.indd"

槐焦
7 years ago
Views:

1 CHAPTER 第 12 章 12 公式與函數的使用公式與函數的使用單元 3 我們要計算或彙總工作表中的資料時, 通常會在儲存格中建立公式, 或使用當 Excel 預先設計好的運算公式 ( 稱為函數 ) 本章將介紹公式與函數的使用 12-1 公式的使用公式函數很好用! 資料的試算與統計是 Excel 的重要功能, 要進行統計分析的工作, 需在儲存格中建立公式建立公式之後, 若公式中使用到的儲存格, 資料內容有更動時, 公式運算的結果便會自動更新公式的表示 Excel 的公式和一般數學公式相似, 一般數學公式的格式為 : 運算元 (operand) 運算子 (operator) 或運算符號 C = A + B 若以 Excel 的格式來表示上述的公式, 則是在儲存格 C1 中輸入 : = A1 + B1 其中 A1 和 B1 是儲存格位址, 以 A1 與 B1 中的值作運算, 並將運算結果顯示在儲存格 C1 中例如在儲存格 C1 中輸入 "= A1 + B1", 即表示將儲存格 A1 B1 內的資料相加, 並在儲存格 C1 中顯示運算結果 ( 圖 12-1) 當更改儲存格 A1 B1 中的資料時, 儲存格 C1 會自動重新計算圖 12-1 使用公式的範例 ( 內文範例 "ch12-1.xls") 密技報你知選工具 / 選項, 按檢視, 並勾選視窗選項區中的公式, 可使設有公式的儲存格改顯示公式內容, 而非計算結果 ( 常用於偵錯時 ) 299

2 計算機概論表 12-1 為 Excel 常用的運算符號及其範例說明表 12-1 常用的運算符號 ( 假設儲存格 A1 的值為 10 儲存格 B1 的值為 5) 運算類型運算符號意義優先順序範例運算結果 () 括號 1 = (A1 + 2) * B 負號 2 = -A1-10 % 百分比 3 = A1% 0.1 算術運算 ^ 次方 4 = A1 ^ B * 乘法 5 = A1 * B1 50 / 除法 5 = A1 / B1 2 + 加法 6 = A1 + B 減法 6 = A1 - B1 5 文字運算 & 字串連接 7 = A1 & B1 105 <(<=) 小於 ( 小於或等於 ) 8 = A1 < B1 FALSE 比較運算 >(>=) 大於 ( 大於或等於 ) 8 = A1 > B1 TRUE = 等於 8 = A1 = B1 FALSE <> 不等於 8 = A1 <> B1 TRUE 以下舉兩個實例, 就能瞭解運算符號的優先順序 ( 紅字 + 底線為該行之運算 ) * 2 > 10 ^ 2 = * 2 > 100 = > 100 = 16 > 100 = FALSE 2 ( 假設儲存格 A1 的值為 6 B1 的值為 5) C1 = (10 + A1) / 2 * B1 & " 人 " = (10 + 6) / 2 * 5 & " 人 " = 16 / 2 * 5 & " 人 " = 8 * 5 & " 人 " = 40 & " 人 " = 40 人公式的輸入 300 公式輸入的方法和一般資料輸入的方法大致相同, 差別在於輸入公式時, 必須以等號 "=" 為首, 例如 "= A1 + A2", 這樣 Excel 才知道我們所輸入的是公式, 而不是一般資料

3 第 12 章公式與函數的使用單元 3 在儲存格中輸入公式之後, 如果顯示 "#VALUE!", 表示公式內有不正確的儲存格例如在進行加總運算時, 公式中所參照的儲存格資料若有文字資料, 即會顯示此錯誤訊息我們可按錯誤檢查選項鈕 ( 圖 12-2), 來檢視發生錯誤的原因, 以進行修正儲存格 E3 公式內容為 "=B3+C3+D3" 按錯誤檢查選項, 選顯示計算步驟儲存格 C3 的內容 "81W" 為文字資料, 不能拿來作運算圖 12-2 查看公式錯誤的原因 ( 內文範例 "ch12-2.xls") 自動計算功能 Excel 很貼心喔! 如果只是要試算 ( 如加總平均 ) 一下資料, 可直接使用自動計算功能, 不需輸入任何公式, 狀態列就會顯示運算結果 ( 圖 12-3) 1 step 選取要自動計算的儲存格範圍 2 step 狀態列顯示運算結果圖 12-3 自動計算功能 ( 內文範例 "ch12-3.xls") 預設是進行加總運算, 在狀態列上按右鍵, 可選用其他運算方式 1. 假設 Excel 的儲存格 A1 B1 的內容分別為 100 及 10, 若在儲存格 C1 輸入公式 =A1 + B1 & " 人 ", 則儲存格 C1 運算的結果為何? (A)10010 (B)110 (C)10010 人 (D)110 人 2. 在 Excel 中, 儲存格 A1:A3 的內容依序為 2 4 及 10, 下列公式執行後, 何者的結果與其他三個選項內容不同? (A)=A1^A2 (B)=A1+A2+A3 (C)=A3-A1*2 (D)=-A2+A1*A3 3. 在 Excel 的儲存格內輸入公式時, 須以作為起始字元 301

4 一總務股長計算機概論 12-2 認識儲存格參照考完試, 同學將考卷往前傳至排頭的收卷動作, 這種未指定特定位址, 即如同 Excel 的相對參照位址概念收班費時, 全班都必須交給第二排第 3 位的總務股長 ( 圖 12-4), 這種指定固定位址, 則是絕對參照位址的概念二三四五在 Excel 的公式或函數中, 儲存格位址的表示 ( 參照 ) 方式, 有相對參照位址絕對參 5 6 照位址及混合參照位址等 3 種, 說明如下 7 相對參照位址圖 12-4 教室座位圖以圖 12-5 為例, 第 1 位同學的總分 (E4) 計算公式為 "=B4+C4+D4", 當我們複製此公式至儲存格 E5 時, 公式的內容必須依照儲存格的欄列移動變化量, 自動調整為 "=B5+C5+D5", 才能正確計算出第 2 位同學的總分 ; 複製公式至儲存格 E6 時, 公式的內容應自動調整為 "=B6+C6+D6", 才能正確計算出第 3 位同學的總分, 依此類推當有上述的需求時, 我們必須在公式中使用相對參照位址它的表示方式為 : 欄名 + 列號 ( 如 A1 B2) E4 E5 E8 儲存格 E5 相對於 E4 的位移量為下移 1 列右移 0 欄, 因此複製後的公式位址, 會下移 1 列右移 0 欄圖 12-5 相對參照示意圖 ( 內文範例 "ch12-4.xls") 302

5 第 12 章公式與函數的使用單元 3 絕對參照位址以圖 12-6 為例, 每位同學的總學分數計算公式皆為 "=B2+C2+D2", 因此當我們在儲存格 F4( 總學分數 ) 輸入公式後, 若複製公式至其他儲存格, 公式的內容不可隨儲存格位置的改變而改變當有上述的需求時, 我們必須在公式中使用絕對參照位址它的表示方式為 : "$" + 欄名 + "$" + 列號 ( 如 $A$1 $B$2) F4 F5 F8 儲存格 F4 中的公式為絕對參照位址, 因此複製儲存格 F4 的公式至 F5 後, 其公式與儲存格 F4 中的公式相同圖 12-6 絕對參照示意圖 ( 內文範例 "ch12-4.xls") 混合參照位址當公式中同時使用了相對參照位址與絕對參照位址時, 稱為混合參照位址公式中的絕對參照位址在複製後不會改變 ; 但相對參照位址的部分則會隨著複製的儲存格位址而改變其表示方式為 :"$" + 欄名 + 列號, 或欄名 + "$" + 列號 ( 如 $A1 B$2) 如圖 12-7 儲存格 F3 的公式內容為 "=B3/B$7", 將此公式複製至儲存格 G6 中, 則儲存格 G6 的公式內容為 "=C6/ C$7" 按鍵可讓公式或函數中的儲存格位址, 在相對參照絕對參照及混合參照間切換 F3 G6 儲存格 G6 相對於 F3 的位移量為下移 3 列右移 1 欄, 因此複製的公式, 相對參照的部分會下移 3 列右移 1 欄, 絕對參照的部分則不改變圖 12-7 混合參照示意圖 ( 內文範例 "ch12-4.xls") 303

6 計算機概論立體參照位址立體參照位址是指參照到其他活頁簿或工作表中的儲存格位址例如 Book1 活頁簿要參照到 Book2 活頁簿中 Sheet1 工作表的儲存格 A1, 其公式為 : = [Book2.xls]Sheet1!$A$1 參照的活頁簿檔名, 以中括號表示參照的工作表名稱, 以驚嘆號表示參照的儲存格, 以相對或絕對參照位址表示 1. 要將 Excel 儲存格以絕對參照位址表示時, 應在欄名及列號前加上哪個符號? (A)$ (B)% (C)& (D): 2. 假設儲存格 A1 A2 A3 A10 的內容依序為數值 , 若在儲存格 B1 輸入公式 =SUM($A1:$A3), 再將此儲存格複製後貼到儲存格 B2, 則儲存格 B2 運算的結果為何? (A)6 (B)9 (C)12 (D)15 3. 在 Microsoft Excel 中, 按鍵可用來切換相對參照位址絕對參照位址及混合參照位址 12-3 函數的使用如 12-1 節所述, 使用公式來運算資料, 使用者須自己輸入儲存格位址當需要輸入的儲存格位址較多時, 會相當沒有效率, 例如要加總 6 科成績, 必須在公式中分別輸入 6 科成績的儲存格位址, 如 =A1+A2+A3+A4+A5+A6 為此,Excel 預先設計好許多常用的公式 ( 稱為函數 ), 以方便我們直接使用, 例如加總平均等函數 304

7 第 12 章公式與函數的使用單元 3 函數的表示 Excel 內建的函數, 有一定的表示方式, 每一個函數的表示包含有函數名稱小括號引數等 3 個部分以下使用加總函數 SUM( ) 為例來做說明函數名稱代表特定的函數, 例如 "SUM" 是加總函數, 可用來將引數資料加總註引數表示函數計算時所須使用的資料, 每個引數之間必須以逗號分開, 如 "A1,A3,A5" 即為 SUM 函數的引數 = SUM ( A1,A3,A5 ) 小括號 ( ) 用來括住引數函數的輸入函數是公式的一種, 在輸入函數時, 也必須以等號 "=" 為首 ; 例如在儲存格輸入 "=AVERAGE(C3:C60)", 即表示要使用 AVERAGE 函數, 來算得 C3 至 C60 儲存格範圍的平均值加總平均計數最大值最小值是 5 種較常使用的函數,Excel 將這些函數安排在自動加總鈕供使用者直接選用 ( 圖 12-8), 以省去動手輸入函數的麻煩自動加總選取要顯示運算結果的儲存格, 按自動加總旁的倒三角形, 選加總 A Excel 自動輸入加總函數圖 12-8 自動加總功能 B 引數範圍 (D3:D5) 可自行修改 ( 內文範例 "ch12-5.xls") C 確認引數範圍無誤後, 按, 即可計算出儲存格 D3 ~ D5 的總和註在 Excel 中, 函數所包含的引數, 最多僅能有 30 個另有些函數 ( 如顯示日期的 TODAY 函數 ) 則不需要引數密技報你知在設定函數的引數時, 可直接利用滑鼠選取要設為引數的儲存格,Excel 便會自動將選取的範圍設定成引數 305

8 計算機概論 1 設定自動計算輸入公式設定絕對參照位址開啟檔案 "Ex12-p1.xls", 依照下列步驟操作, 最後將檔案另存為 "Ans12-p1.xls" 1 選取儲存格 D2, 再按自動加總鈕旁的倒三角形, 選平均 2 選取儲存格 F4 ~ F13, 設定平均函數的引數範圍後, 按鍵選取儲存格 G4, 輸入公式 "= F4 - D2", 按鍵, 將儲存格位址 D2 轉換為絕對參照位址, 再按鍵 4 選取儲存格 G4, 並將指標移至右下角的填滿控點, 向下拉曳到儲存格 G13, 完成各個員工與全體員工的平均考績比較

9 三角函數類常用的函數第 12 章公式與函數的使用單元 3 在 Excel 中, 除了可直接透過如圖 12-8 所示的自動加總鈕來選用 5 種較常使用的函數之外, 也可按資料編輯列的插入函數鈕來選用各種函數以下將介紹圖 12-9 所示的常用函數, 並舉例說明這些函數的使用方法函數名稱說明數學與 SUM ROUND ROUNDUP ROUNDDOWN SUMIF SUMPRODUCT INT 加總四捨五入無條件進位無條件捨去條件式加總乘積總和最大整數統計類檢視與參照類邏輯類 AVERAGE MAX MIN COUNT COUNTA RANK COUNTIF VLOOKUP HLOOKUP AND OR NOT IF 平均最大值最小值含有數值的儲存格個數非空白的儲存格個數排名符合條件式的儲存格個數直向對照查詢橫向對照查詢所有引數的邏輯值均為 TRUE, 傳回 TRUE 任一引數的邏輯值為 TRUE, 傳回 TRUE 反轉引數的邏輯值判斷條件式文字類 MID 擷取特定長度的子字串日期及時間類 TODAY 註 DATEDIF 日期兩個日期之差財務類圖 12-9 Excel 常用的函數 PMT IPMT 每月應付本息 ( 本金 + 利息 ) 某月應付利息註按插入函數鈕, 在開啟的插入函數交談窗中, 並無 DATEDIF 函數可供選用, 如果需使用 DATEDIF 函數必須自行在儲存格中輸入密技報你知按 + 鍵, 可開啟插入函數交談窗 307

10 計算機概論 SUM: 計算總和如圖在儲存格 G3 輸入 "=SUM(C3:F3)", 算出儲存格 C3 ~ F3 等 4 項測驗的總分圖 SUM 函數的使用範例 ( 內文範例 "ch12-6.xls") AVERAGE: 計算平均值如圖在儲存格 C9 輸入 "=AVERAGE(C3: C7)", 算出儲存格 C3 ~ C7 聽力測驗的平均分數圖 AVERAGE 函數的使用範例 ( 內文範例 "ch12-6.xls") MAX/MIN: MAX 用來找出選取範圍中的最大值 ; MIN 用來找出最小值如圖在儲存格 C10 輸入 "=MAX(C3:C7)", 儲存格 C11 輸入 "=MIN (C3:C7)", 找出儲存格 C3 ~ C7 聽力測驗分數中的最高分及最低分圖 MAX/MIN 函數的使用範例 ( 內文範例 "ch12-6.xls") 308 密技報你知按 + 鍵, 可快速輸入 SUM 函數

11 第 12 章公式與函數的使用單元 3 COUNT: 計算含有數值資料的儲存格個數如圖在儲存格 C12 輸入 "=COUNT(C3:C7)", 算出儲存格 C3 ~ C7 有分數的人數 -COUNTA 函數與 COUNT 函數的功能相近, 差異在於 COUNTA 是計算非空白的儲存格個數 ( 內文範例 "ch12-6.xls") 這裡用 =COUNT (E3:E7) 會出現 4 圖 COUNT 函數的使用範例 ROUND/ROUNDUP/ROUNDDOWN: 這 3 種數學函數, 皆是用來將數值進位 ( 或捨去 ) 至指定的位數表 12-2 為 3 種數學函數的比較表種數學函數的比較 ( 假設 F 代表函數名稱 ) 函數語法 =F( ,2) =F( ,1) =F( ,0) =F( ,-1) 取至小數第 2 位取至小數第 1 位取至個位數取至十位數 ROUND ( 四捨五入 ) ROUNDUP ( 無條件進位 ) ROUNDDOWN ( 無條件捨去 ) 註 RANK: 排列出某數值在一串數值清單中的大小順序如圖在儲存格 H3 輸入 "=RANK(G3,$G$3:$G$7)", 排列出儲存格 G3 成績在儲存格 G3 ~ G7 全班成績的排名註圖 12-14( 右 ) 所示的交談窗, 是透過按資料編輯列的插入函數鈕, 在開啟的交談窗, 按或選取類別下拉式方塊, 依前述圖 12-9 所示選取函數的類別 ( 如統計 ), 接著在選取函數列示窗中, 選取要輸入的函數 ( 如 RANK), 並按確定鈕來開啟 309

12 計算機概論 1 設定要排序的數值 2 設定比較的範圍 ( 如某位同學的總分 ) ( 如全班每位同學的總分 ) 圖 RANK 函數的使用範例設定排序規則 ( 省略或 0 代表遞減 ; 其他數值代表遞增 ) ( 內文範例 "ch12-6.xls") 1 2 =RANK(G3,$G$3:$G$7) 在上例中, 利用 RANK 函數來排名時, 任一位同學的名次, 都須以全班每位同學的總分 (G3:G7) 作為比較範圍, 因此輸入的比較範圍必須使用絕對參照位址 ( 圖右 ), 以避免利用填滿控點複製函數後, 儲存格位址產生不正確的改變 ( 圖左 ) 相絕對參照位址對參照位址每位同學排名的比較範圍不相同, 排出來的名次錯誤圖儲存格位址變化的範例每位同學皆比較全班同學的總分來排名, 排名結果正確 ( 總分同分, 名次相同 ) NOT AND OR: 這 3 種邏輯函數, 皆是用來判斷條件是否成立, 以傳回 TRUE 或 FALSE 表 12-3 為 3 種邏輯函數的比較 310 表 12-3 NOT AND OR 邏輯函數的說明與範例邏輯函數功能說明範例結果 NOT AND OR 若條件式成立, 傳回 FALSE; 若條件式不成立, 傳回 TRUE 所有條件成立, 傳回 TRUE; 若有任一條件不成立, 傳回 FALSE 若有任一條件成立, 傳回 TRUE; 所有條件皆不成立, 傳回 FALSE =NOT(60>0) =NOT(60<0) =AND(60>0,70>80) =AND(60>0,70<80) =OR(60>0,70>=80) =OR(60<=0,70>80) FALSE TRUE FALSE TRUE TRUE FALSE

13 第 12 章公式與函數的使用單元 3 IF: 判斷條件是否成立, 並傳回條件成立或條件不成立所對應的設定值如圖在儲存格 G3 輸入 "=IF(F3>=25," 是 "," 否 ")", 可判斷儲存格 F3 的 BMI 值是否超過標準值, 並傳回是或否 =IF(F3>=25," 是 "," 否 ") 1 設定條件式 ( 如判斷 BMI 值是否大於等於 25) 2 條件成立, 傳回的內容 ( 如顯示 " 是 ") ( 內文範例 "ch12-7.xls") 圖 IF 函數的使用範例 3 條件不成立, 傳回的內容 ( 如顯示 " 否 ") 巢狀概念巢狀概念是指在某一函數中還包含另一個函數例如在上例判斷 BMI 值是否大於等於 25 中, 如果希望當 BMI 小於 25 時, 再判斷 BMI 值是否小於 18.5, 以判斷體重是否過輕, 就必須在 IF 函數中再包含另一 IF 函數 ( 即所謂的巢狀 IF) 圖為利用巢狀 IF 判斷體重是否過輕過重或標準的範例 1如果 BMI 值 >= 25 2 顯示 " 過重 " 否則如果 BMI 值 < 18.5 顯示 " 過輕 " 3 否則顯示 " 標準 " ( 內文範例 "ch12-7.xls") 在 IF 函數內又包含另一個 IF 函數圖巢狀 IF 的使用範例 311

14 計算機概論 COUNTIF: 計算符合某條件式的儲存格個數如圖在儲存格 G8 輸入 "=COUNTIF(G3:G7," 是 ")", 算出儲存格 G3 ~ G7 體重過重的人數圖 COUNTIF 函數的使用範例 ( 內文範例 "ch12-7.xls") 2 Excel 函數的使用開啟檔案 "Ex12-p2.xls", 在 Excel 工作表中加入如下圖所示的 8 項函數, 完成如下圖的結果後, 將結果另存為 "Ans12-p2.xls" 1 SUM: 計算第一季至第四季的銷售總額, 例如 =SUM(B3:E3) 2 RANK: 依銷售總額計算排名, 例如 =RANK(F3,$F$3: $F$8) IF: 判斷考績評等, 判斷規則如下如果銷售總額 > 250,000 顯示 " 優 " 否則如果銷售總額 >= 160,000 顯示 " 甲 " 否則顯示 " 乙 " 例如 =IF(F3>250000," 優 ", IF(F3>=160000," 甲 "," 乙 ")) COUNTIF: 計算考績為優甲乙的人數, 例如 =COUNTIF(H3:H8," 優 "), 可加總考績為優的人數 7,8 5 MAX: 找出最高銷售總額, 例如 =MAX(F3:F8) 6 MIN: 找出最低銷售總額, 例如 =MIN(F3:F8) 7 AVERAGE 8 ROUND 計算銷售總額的平均, 並四捨五入取至個位數, 例如 =ROUND(AVERAGE(F3:F8),0) 312

15 第 12 章公式與函數的使用單元 3 SUMIF: 將符合條件的儲存格進行加總如圖在儲存格 F13 輸入 "=SUMIF(A4:A11," 腳踏車 ",F4:F11)", 設定當儲存格 A4 ~ A11 的值為 " 腳踏車 " 時, 加總其銷售總額圖 SUMIF 函數的使用範例 ( 內文範例 "ch12-8.xls") SUMPRODUCT: 計算特定範圍中各相對應儲存格的乘積總和如圖在儲存格 E4 輸入 "=SUMPRODUCT($B$2:$D$2,B4:D4)", 算出儲存格 B2 ~ D2 工作績效出缺勤教育訓練與儲存格 B4 ~ D4 各評分占比相乘後的加總分數 ( 內文範例 "ch12-9.xls") 圖 SUMPRODUCT 函數的使用範例 313

16 計算機概論 VLOOKUP: 是一種具有對照查詢功能的函數, 如圖要利用姓名來查詢分數, 可在儲存格 B66 輸入 "=VLOOKUP(A66,$A$4:$E$63,5,FALSE)", Excel 便會在指定範圍 ($A$4:$E$63) 最左欄中找尋到與儲存格 A66 資料完全相符的儲存格 (A6), 並傳回與該儲存格同一列中第 5 欄的值 ( 即分數 ) =VLOOKUP(A66,$A$4:$E$63,5,FALSE) 1 欲找尋的值 ( 如儲存格 A66 " 莊清媚 ") 2 找尋的範圍 ( 如 $A$4:$E$63) 圖 VLOOKUP 函數的使用範例 ( 內文範例 "ch12-9.xls") -HLOOKUP 函數與 VLOOKUP 函數的使用功能相近, 差異在於 HLOOKUP 是在指定範圍第一列中找尋特定值, 傳回與特定值同一欄第 N 個儲存格的值 3 找到與儲存格 A66(" 莊清媚 ") 相符儲存格 ( 即 A6), 並傳回該列中第 5 欄的值 ( 即分數 ) 4 設定要找尋最相近或完全相符的數值資料 ( 省略或 TRUE 代表找尋最相近的值 ;FALSE 代表找尋完全相符的值 ) INT: 計算小於等於該數值的最大整數如圖儲存格 F3 公式中的 "INT(D3/12)", 可用來將儲存格 D3 事假除以 12 的計算結果, 取最大整數值操行成績 = 老師評分 - 曠課 - 取整數 ( 事假 /12) 314 密技報你知圖 INT 函數的使用範例 ( 內文範例 "ch12-10.xls") ( 請事假 12 次扣 1 分 ) 使用 VLOOKUP 函數, 若設定要找尋最相近的值, 須先排序搜尋範圍 ( 排序方法 : 選搜尋範圍最左欄的任一儲存格, 按一般工具列的遞增排序鈕或遞減排序鈕

17 第 12 章公式與函數的使用單元 3 PMT 與 IPMT 函數 PMT 與 IPMT 皆為財務類函數, 差異在於 PMT 是用來計算貸款每月應付的本息 ( 本金 + 利息 ); 而 IPMT 函數則是用來計算貸款某月應付的利息若以貸款一百萬元, 年利率 3%, 貸款 20 年期來計算, 利用 PMT 可求得每月應付之本息為 5,546 元 ; 利用 IPMT 可求得某期應付之利息, 如第 1 期的利息為 2,500 元 ( 圖 12-23) 月利率總期數 ( 單位 : 月 ) 貸款金額月利率第 n 期總期數 ( 單位 : 月 ) 貸款金額年金終值圖 PMT/IPMT 函數的使用範例 ( 內文範例 "ch12-11.xls") MID: 擷取自起始位置起指定長度的字串如圖儲存格 D4 的公式 "=MID (C4,2,1)", 即是利用 MID 函數擷取儲存格 C4 身分證的第 2 碼來判斷性別 (1 代表男生 ;2 代表女生 ) 圖 MID 函數的使用範例 ( 內文範例 "ch12-12.xls") TODAY: 傳回目前電腦系統的日期 ( 包含年月日 ) 如圖在儲存格 F2 輸入 "=TODAY()", 可顯示目前電腦系統的日期密技報你知 ( 內文範例 "ch12-12.xls") 圖 TODAY 函數的使用範例 ( 假設目前電腦系統日期為 2012/10/22) 按 + 鍵, 會自動輸入今天的日期 ; 按 + + 鍵, 會自動輸入目前的時間 315

18 計算機概論 DATEDIF: 計算兩個日期相差多少天 (D ) 月 ( M ) 或年 (Y ) 如圖在儲存格 F4 輸入 "=DATEDIF(E4,F2, "Y")", 可使用生日與目前日期來計算兩者的年份差, 而計算出年齡日期 1 日期 2 傳回從日期 1 到日期 2 相差多久 (Y 表年數 ) 123 圖 DATEDIF 函數的使用範例 ( 內文範例 "ch12-12.xls") 選擇性貼上在複製儲存格資料時, 若直接使用貼上功能, 會將儲存格的所有設定 ( 包含儲存格的內容樣式等 ) 複製至被貼上的儲存格中以圖為例, 來源儲存格中的數值是利用公式計算出來的, 如果我們只需要複製儲存格中的值至目的儲存格, 就須使用選擇性貼上來達成選取來源儲存格按 + 鍵, 在目的儲存格上按右鍵, 選選擇性貼上儲存格中的數值是利用公式求得僅貼上值 ( 內文範例 "ch12-13.xls") 來源儲存格目的儲存格圖選擇性貼上 - 貼上值的範例 316

19 第 12 章公式與函數的使用單元 3 3 Excel 函數的使用開啟檔案 "Ex12-p3.xls", 在 Excel 工作表中加入如下圖所示的 7 項函數, 完成如下圖的結果後, 將結果另存為 "Ans12-p3.xls" 1 TODAY: 顯示電腦系統的日期, 例如 =TODAY( ) 1 2,3 2 MID 3 IF 使用 MID 函數擷取身分證字號的第 2 碼, 再利用 IF 函數判斷性別 (1 表示男生 2 表示女生 ), 例如 =IF(MID(B4,2,1)="1"," 男 "," 女 ") 4 4 DATEDIF: 計算個人年資 ( 即到職日與日期的年份相減 ), 例如 =DATEDIF(D4,G2,"Y") 5 5 VLOOKUP: 利用編號來查詢產品名稱及單價, 例如 =VLOOKUP(B7,$F$17:$H$19,2), 可查詢產品名稱 SUMPRODUCT: 計算業績獎金 ( 獎金比例與銷售額的乘積加總 ), 例如 =SUMPRODUCT(C17:C19,D17:D19) 6 SUMIF: 分別加總數位相機筆記型電腦行動電話的銷售額, 例如 =SUMIF($C$7:$C$12," 數位相機 ",$G$7:$G$12), 可加總數位相機的銷售額 1. 在 Excel 的工作表中, 欲從出生日期的資料計算出年齡, 可使用下列哪一項函數? (A)WEEKDAY (B)DAYS360 (C)DATEDIF (D)EDATE 2. 博文想在建立好的班上身高體重記錄表中, 找出全班身高最高的同學, 請問他可以利用 Excel 的哪一個函數來完成? (A)AVERAGE( ) (B)COUNT( ) (C)MIN( ) (D)MAX( ) 3. 請依照以下函數功能的敘述, 在空格中填入符合敘述的函數代號 a. TODAY b. ROUND c. DATEDIF d. COUNT e. ROUNDUP f. INT g. MID (1) 計算含有數值資料的儲存格個數 (2) 取最大整數 (3) 將數值無條件進位至指定的位數 (4) 計算兩個日期之差 317

20 計算機概論 4 輸人不輸陣 - 成績統計本範例將以製作成績統計表為例, 讓同學練習輸入公式使用自動加總功能, 及以下 5 項函數的使用方法 1. 輸入公式 2. RANK 函數 3. IF 函數 4. 使用自動加總鈕 5. ROUND 函數 6. TODAY 函數 7. VLOOKUP 函數輸入公式詳細步驟參考 1.1 開啟檔案 "Ex12-1.xls" 選取儲存格 H4, 輸入公式 "=C4+D4+E4+F4+G4", 按鍵也可在儲存格 H4 中, 輸入 "=SUM(C4:G4)" 318

21 第 12 章公式與函數的使用單元選取儲存格 I4, 輸入公式 "= H4/5", 按鍵 1.3 也可在儲存格 H4 中, 輸入 "=AVERAGE(C4:G4)" 2. 使用 RANK 函數詳細步驟參考選取儲存格 J4, 按插入函數鈕, 以開啟插入函數交談窗 2.2 按或選取類別下拉式方塊, 選統計在選取函數列示窗中, 點選 RANK, 按確定鈕, 以開啟函數引數交談窗 2.3 在選取函數列示窗點選任一函數後, 按鍵, 可快速移至字首為 "R" 的函數, 以快速找到 RANK 函數在 Number 欄輸入儲存格位址 "I4", 設定以平均分數來計算學號同學在班上的排名按 Ref 欄的折疊鈕, 以選取全班同學的平均成績 319

22 計算機概論 2.6 選取儲存格 I4~I18, 按鍵, 將儲存格位址轉換為絕對參照位址, 按折疊鈕, 返回函數引數交談窗按確定鈕, 計算成績排名計算結果為 "1", 表示儲存格 I4 資料在 I4 ~ I18 的範圍中, 排行第使用 IF 函數詳細步驟參考 3.1 選取儲存格 K4, 按插入函數鈕, 開啟插入函數交談窗按或選取類別下拉式方塊, 選邏輯在選取函數列示窗中, 點選 IF, 按確定鈕, 以開啟函數引數交談窗 320

23 第 12 章公式與函數的使用單元在 Logical_test 欄輸入條件式 "I4>=85" 3.5 在 Value_if_true 欄輸入 " 優異 ", 設定當條件式成立時,K4 儲存格顯示文字 " 優異 " 在 Value_if_false 欄輸入 "IF(I4>=70," 中等 "," 不佳 ")", 設定當條件式不成立時, 再判斷儲存格 I4 的成績是否大於等於 70, 若是, 顯示文字 " 中等 ", 否則顯示文字 " 不佳 " 3.7 按確定鈕, 完成函數的輸入選取儲存格 H4 ~ K4, 按住右下角的填滿控點, 向下拉曳到儲存格 K18 也可直接雙按填滿控點, 快速在 H5~ K18 儲存格中填入資料 4. 使用自動加總鈕詳細步驟參考選取儲存格 C20, 按自動加總鈕旁的倒三角形, 選計數 321

24 計算機概論 4.2 選取儲存格 C4 ~ C18, 設定引數範圍後, 按鍵, 完成函數的輸入選取儲存格 C21, 按自動加總鈕旁的倒三角形, 選其他函數 4.4 按或選取類別下拉式方塊, 選統計, 在選取函數列示窗中點選 COUNTIF 函數, 按確定鈕, 以開啟函數引數交談窗 4.4 在選取函數列示窗點選任一函數後, 按鍵, 可快速移至字首為 "C" 的函數, 以快速找到 COUNTIF 函數在 Range 欄輸入 "C4:C18" 在 Criteria 欄輸入 "<60", 計算步驟 4.5 輸入的範圍內, 小於 60 分的個數, 最後按確定鈕 322

25 第 12 章公式與函數的使用單元選取儲存格 C22, 按自動加總鈕旁的倒三角形, 選最大值 4.8 選取儲存格 C4 ~ C18, 設定引數範圍後, 按鍵選取儲存格 C23, 按自動加總鈕旁的倒三角形, 選最小值, 並設定引數範圍為 C4 ~ C18, 最後按鍵選取儲存格 C24, 按自動加總鈕旁的倒三角形, 選平均, 並設定引數範圍為 C4 ~ C18, 最後按鍵使用 ROUND 函數詳細步驟參考 5.1 選取儲存格 C24, 並將游標移至資料編輯列中, 在 AVERAGE 函數前輸入 "ROUND(", 在公式最後輸入 ",0)", 並按鍵, 設定將平均分數四捨五入至個位數 323

26 計算機概論 5.2 選取儲存格 C20 ~ C24, 按住右下角的填滿控點, 向右拉曳到儲存格 G24, 以複製公式內容使用 TODAY 函數詳細步驟參考選取儲存格 J2, 輸入函數 "=TODAY()" 後, 按鍵 6.2 切換至個人成績查詢工作表 6.3 選取儲存格 H2, 輸入函數 "=TODAY()" 後, 按鍵 6.3 快速鍵 + : 移至下一個工作表 + : 移至前一個工作表

27 第 12 章公式與函數的使用單元使用 VLOOKUP 函數詳細步驟參考 7.1 選取儲存格 I3, 按插入函數鈕, 以開啟插入函數交談窗按或選取類別下拉式方塊, 選檢視與參照, 在選取函數列示窗中點選 VLOOKUP 函數, 按確定鈕在此欄輸入 "F3", 按鍵, 將儲存格位址轉換成絕對參照位址 7.4 按此欄的折疊鈕, 以選取整份成績單的內容切換至成績總表工作表, 選取儲存格 A4 ~ K18, 按鍵, 再按折疊鈕, 返回函數引數交談窗 325

28 計算機概論 7.6 在此欄輸入 "2", 設定找到儲存格 F3 的值 ( 即學號 : 13101) 後, 傳回與該值同一列第 2 欄的值 ( 即學號為同學的姓名 ), 按確定鈕選取儲存格 I3, 按複製鈕快速鍵 + : 複製選取儲存格 B5 ~ I5, 按住鍵, 再選取儲存格 H7, 以選取多個不連續的儲存格選按編輯 / 選擇性貼上選項, 開啟選擇性貼上交談窗 7.10 點選公式選項按鈕, 按確定鈕, 在儲存格 B5 ~ I5 及 H7 貼上儲存格 I3 的函數選取儲存格 B5, 在資料編輯列中, 將引數 "2", 更改為 "3", 按鍵, 以傳回學號為學生的國文成績 326

29 第 12 章公式與函數的使用單元參照步驟 7.11, 將儲存格 C5 ~ I5 中的引數 "2", 分別更改為 4 ~ 10; 再將儲存格 H7 中的引數 "2", 更改為 "11" 切換至成績總表工作表, 選取儲存格 C24 ~ G24, 並按 + 鍵複製 7.14 切換至個人成績查詢工作表選取儲存格 B6 ~ F6, 並選按編輯/ 選擇性貼上選項, 以開啟選擇性貼上交談窗點選值選項按鈕, 按確定鈕, 只複製成績總表工作表中儲存格 C24 ~ G24 的值選按檔案 / 另存新檔選項, 將檔案另存為 "Ans12-1.xls" 327

30 MENU 選擇題作答處藍色底線表示該題為歷屆統測考題 ; 紅色底線為技能檢定考題 1. 在試算表軟體 Microsoft Excel 中, 在資料編輯列中輸入 =2000/5/5, 結果會顯示以下何者? (A) 顯示 2000 年 5 月 5 日 (B) 顯示 #VALUE! (C) 顯示 80 (D) 顯示 2000/5/5 乙檢 2. 假設儲存格 A1 內的值為 10, 儲存格 B1 內的值為 50, 則在儲存格 C1 輸入公式 =A1>B1%, 則儲存格 C1 運算的結果為何? (A)FALSE (B)TRUE (C) 假 (D) 真 3. 在儲存格 A1 輸入 =10^2, 則儲存格 A1 所顯示的結果為何? (A)100 (B)1024 (C)10^2 (D)A1 4. Excel 的運算符號 <>, 代表的意義為何? (A) 小於 (B) 大於 (C) 等於 (D) 不等於 5. 在 Excel 中, 下列哪一個字元可用來連接兩個字串? (A)^ (B)& (C)$ (D)% 6. 在某一儲存格中輸入公式後, 若該儲存格顯示 "#VALUE!", 最可能的原因是? (A) 公式中所參照的儲存格資料有文字資料 (B) 公式中使用的運算符號有誤 (C) 運算結果為 0 (D) 運算結果為負數 7. 在 Microsoft Excel 2003 環境中, 若要將 Sheet1 的 A1 儲存格之內容與 Sheet2 的 B2 儲存格之內容相乘後, 再將結果儲存至 Sheet3 的 C3 儲存格內, 則可在 Sheet3 的 C3 儲存格內鍵入下列何者來達成? (A) =A1*B2 (B) =Sheet1&A1*Sheet2&B2 (C) =Sheet1$A1*Sheet2$B2 (D) =Sheet1!A1*Sheet2!B2 8. 在 Excel 中, 以何種參照位址製作的公式被複製到其他儲存格時, 位址不會隨著改變? (A) 相對參照 (B) 絕對參照 (C) 相對參照與絕對參照 (D) 位址一定會隨著儲存格而改變 9. 在 Microsoft Excel 中, 儲存格 A2 B2 A3 B3 內容分別為 , 儲存格 B8 內容為 =A$2+B2*2, 將儲存格 B8 內的公式複製到儲存格 B9, 則儲存格 B9 公式計算值為何? (A)8 (B)10 (C)12 (D)14 92 商業語文 10. 在 Microsoft Excel 的工作表中, 若儲存格 C5 存放公式 =$A1+B$3, 將此儲存格複製後貼到儲存格 D7, 則儲存格 D7 的公式為? (A)=$A2+C$3 (B)=$A3+C$3 (C)=$A2+D$3 (D)=$A3+D$3 95 衛生計概 11. 在 Excel 中欲在儲存格 L2 輸入 G2,H2,I2,J2, 和 K2 的加總, 應該如何輸入? (A)(G2+H2+I2+J2+K2) (B)=SUM(G2:K2) (C)=SUM(G2-K2) (D)($G2+$H2+$I2+$J2+$K2) 96 公務初考 12. 在 Microsoft Excel 2003 環境中, 公式 SUM(A1:B6,D3:C2) 表示加總幾個儲存格之和? (A)16 (B)12 (C)8 (D) 公式語法錯誤 13. 在儲存格中輸入公式 =VLOOKUP(D1,A1:C10,2), 當 Excel 找到儲存格 A5 與儲存格 D1 的值相同時, 會傳回下列哪一個儲存格中的值? (A)A5 (B)B5 (C)C5 (D)D1 328

31 14. ROUNDUP(30.57%,1) 的顯示結果為下列何者? (A)30.5 (B)30.6 (C)0.3 (D) 在 Microsoft Excel 中, 已輸入圖 ( 二 )C2..G2,B3..B7 之值, 若欲得到乘法表 C3..G7 顯示的效果, 可先在儲存格 C3 中輸入一公式, 再將其複製到 C3..G7 中試問在儲存格 C3 中所輸 98 二技入的公式應為何? (A)=B3*C2 (B)=$B3*C$2 (C)=B$3*$C2 (D)=$B$3*$C$2 圖 ( 二 ) 16. 在 Microsoft Excel 2003 環境中, 若 A1 至 A3 儲存格中之值分別為 1, 空白,3, 且 A6 儲存格內所定義之公式為 =MIN(A1:A3), 則 A6 儲存格內之值應為下列何者? (A)1 (B) 空白 (C)3 (D)#VALUE! 17. 利用電子試算表軟體 (Microsoft Excel), 在儲存格 A1 A2 A3 A4 A5 中, 分別輸入數值下列運算結果, 何者不正確? (A)SUM(A1:A5) = 30 (B)COUNT(A1:A5) = 2 (C)MAX(A1:A5) = 18 (D)AVERAGE(A1:A5) = 在 Excel 中, 利用下列哪一個函數可以在成績表中計算數學成績不及格的人數? (A)COUNT( ) (B)COUNTIF( ) (C)RANK( ) (D)ROUND( ) 19. 使用電子試算表軟體如 Microsoft Excel, 在儲存格 B1 B2 B3 B10 依序輸入數值 , 若儲存格 B11 的公式為 =MAX(SUM(B1:B10),AVERAGE(B1:B10)) 則儲存格 B11 的值是多少? (A)10 (B)55 (C)60 (D) 在 Microsoft Excel 試算表軟體中, 下列有關函數功能的敘述, 何者正確? (A)ADD 函數主要用來計算總和 (B)AVERAGE 函數主要用來計算平均值 (C)COUNT 函數主要用來計算欄位數目 (D)RANGE 函數主要用來計算排名 97 商業語文 21. 假設在 Excel 試算表中, 儲存格 A1 A2 A3 A4 已存有四筆相異數值資料下列何者其運算結果與 = AVERAGE(A1:A4) 相同? (A)= MAX(A1:A4) (B)= SUM(A1:A4) / 4 (C)= A1 + A2 + A3 + A4 (D)= MIN(A1:A4) 92 工業設計 22. 假設儲存格 A1 到 A5 的值分別為 , 若在儲存格 B1 中輸入公式 =RANK(A2, $A$1:$A$5,1), 則該儲存格顯示的結果為何? (A)1 (B)2 (C)3 (D)4 23. 在 Microsoft Excel 2003 的預設環境中, 若在 C1 儲存格內輸入公式 =MAX(A1:B6) 後按 [Enter] 鍵,C1 儲存格內顯示 12, 此時若再按下 [Delete] 鍵, 則下列敘述何者正確? (A) 計算公式與值仍舊存在 (B) 值消失, 但計算公式仍舊存在 (C) 計算公式消失, 但值仍舊存在 (D) 計算公式與值均消失 329

32 衛生計概 MENU 24. 在 Excel 工作表中, 假設儲存格 A1 到 A5 的值分別為 , 則在儲存格 B1 輸入下列何者所得到的數值最小? (A)=AVERAGE(A1:A5) (B)=COUNT(A1:A5) (C)=IF(A1 < 4, A3, A2) (D)=RANK(A4, A1:A5) 25. 在 Microsoft Excel 中, 假設 A1 A2 A3 A4 A5 都存有數值資料, 下列有關 Excel 函數的敘述何者正確? (A) 計算式 SUM(A1:A3) 的結果等於 (A1 + A2 + A3) / 3 (B) 計算式 AVERAGE(A1:A4) 的結果等於 A1 + A2 + A3 + A4 (C) 計算式 COUNT(A2:A5) 的結果等於 4 (D) 計算式 MAX(A1:A3) 的結果等於 A1 * A2 * A3 90 工業設計 26. 在 Microsoft Excel 中, 如果要在儲存格 B6 中計算儲存格 B2 至 B5 四筆數值的平均值, 則下列公式何者不正確? (A)=AVERAGE(B2+B3+B4+B5) (B)=AVERAGE(B2:B5) (C)=AVERAGE(B2,B3,B4,B5) (D)=(B2+B3+B4+B5)/4 93 二技 27. AVERAGE COUNT MIN 和 SUM 都是 Microsoft Excel 試算表軟體常用的函數, 如果以和 56 等三個數值作為上述四個函數的參數, 計算的結果下列何者正確? (A)AVERAGE(12,34,56) > SUM(12,34,56) (B)COUNT(12,34,56) > MIN(12,34,56) 97 (C)MIN(12,34,56) > AVERAGE(12,34,56) (D)SUM(12,34,56) > COUNT(12,34,56) 28. 在 Excel 中, 儲存格 A1 到 A3 的值分別為 , 若在儲存格 B1 輸入公式 =ROUND(AVERAGE(A1:A2),0), 則儲存格 B1 運算的結果為何? (A)16 (B)17 (C)18 (D) 在 Excel 中, 儲存格 B3 至 B8 的內容依序為及 10, 下列函數執行後, 何者不正確? (A) 執行 MAX(B3:B8) 會得到 17 (B) 執行 MIN(B3:B8) 會得到 6 (C) 執行 COUNT(B3:B8) 會得到 60 (D) 執行 AVERAGE(B3:B8) 會得到電子計概 30. 假設在 A1 儲存格內的數值為 60, 若在 B1 儲存格中輸入公式 =IF(A1>100,A1,IF(A1*2>=120,A1*2,100)), 則 B1 的運算結果為下列何者? (A)60 (B)100 (C)120 (D)

33 實作題 1. 開啟檔案 "Ex12-a.xls", 依照提示內容, 在 Excel 工作表中輸入公式與函數, 並設定格式化條件, 完成如下圖的結果後, 將結果另存為 "And12-a.xls" 提示 : (1) 使用公式計算 BMI 值欄位 (BMI 值 = 體重 ( 身高 100) 2 ) (2) 使用 AVERAGE ROUNDUP 函數, 計算全班的平均身高和體重, 計算結果必須無條件進位至個位數 (3) 使用 COUNTIF 函數計算近視人數, 條件為視力 <0.8 (4) 使用 COUNTIF 函數計算 BMI 值太高的人數, 條件為 BMI 值 >25 (5) 利用格式化條件功能, 為儲存格 A4 ~ H17 中的奇數列套用 " 象牙白 " 色的網底 ( 公式為 "=MOD(ROW(),2) =1") 2. 開啟檔案 "Ex12-b.xls", 依照提示內容, 在 Excel 工作表中輸入公式與函數, 完成如下圖的結果後, 將結果另存為 "Ans12-b.xls" 提示 : (1) 使用 IF 函數判斷考績評等 ( 考績評分 >=90 時顯示文字 " 甲 ";>=80 顯示文字 " 乙 ",<80 顯示文字 " 丙 ") (2) 使用 VLOOKUP 函數以評等來查詢調薪比率 (3) 設定調薪比率為百分比類別, 小數位數 0 (4) 使用公式計算調整後薪資欄位 ( 調薪後薪資 = 薪資 (1 + 調薪比率 )) (5) 設定調整後薪資為數值類別, 加入千分位符號, 小數位數 0 (6) 使用 COUNTIF 函數計算考績評等人數, 條件是評等分別為甲乙丙 331

840 提示 Excel - Excel -- Excel (=) Excel ch0.xlsx H5 =D5+E5+F5+G5 (=) = - Excel 00

Excel - - Excel - -4-5 840 提示 Excel - Excel -- Excel (=) Excel ch0.xlsx H5 =D5+E5+F5+G5 (=) = - Excel 00 ( 0 ) 智慧標籤相關說明提示 -5 -- Excel 4 5 6 7 8 + - * / % ^ = < >= & 9 0 (:) (,) ( ) Chapter - :,