Ch04 第二類題目 電子試算表檢定.

Presentation on theme: "Ch04 第二類題目 電子試算表檢定."— Presentation transcript:

1 Ch04 第二類題目 電子試算表檢定

2 202. 各家銀行指數型房貸利率分析表 「房屋貸款（單變數）」工作表： 在E1輸入公式：利用PMT財務函數，計算分期付款每月攤還金額。
製作單變數運算列表：以運算列表功能自動填入E2~E12公式。 E1~E11格式：會計專用$數字格式，小數位數0位。 「房屋貸款（雙變數）」工作表： 複製「各銀行指數型房貸利率」A1~A13的銀行至本工作表的D1~D13，複製「各銀行指數型房貸利率」E2~E13的貸款利率至本工作表的E2~E13（只能複製值，不可複製框線格式）。 製作雙變數運算列表：以運算列表功能自動填入F2~J13公式。 E2~E13格式：百分比格式，小數位數2位。 F2~J13格式：會計專用$數字格式，小數位數0位。 將結果儲存於指定路徑下，檔名為EXA02.xlsx。

3 EXD202 各家銀行指數型房貸利率分析表 PMT(Rate, Nper, Pv, Fv, Type)：每期付款金額及利率固定之下計算年金期付款數額，包含本金和利息在內。 Rate為各期的利率。例如，使用6%/4表示6%之下的每季付款利率 Ｎper為年金的付款期數 Pv為未來各期年金現值的總和 Fv為最後一次付款完成後，所能獲得的現金餘額（年金終值） Type：為０時：給付時點為期末 　　　為１時：期初給付

4 請確定您用來指定 rate 和 nper 的衡量單位是一致的。如果以四年期貸款，年利率為 12 %，每月付款一次，rate 請設定為 12%/12，而 nper 則設定為 4*12。如果相同的貸款每年付款一次，rate 請設定為 12%，而 nper 則設定為 4。 秘訣  若要求得年金付款的金額總數，只須將 PMT 所傳回的值乘上 nper (期數) 即可。

5 資料/假設情況分析

6 204. 成績計算表 『國文』、『數學』、『英文』工作表：
「日常紙筆」欄（I7~I16）：計算四次高分成績之平均。（使用SUM與MIN函數） 「日常考查」欄（L7~L16）：計算兩次小考之平均。（使用AVERAGE） 「學期成績」欄（P7~P16）：計算各成績與所佔比例之和。 『統計』工作表： 將『國文』、『數學』、『英文』工作表之「學期成績」（P7~P16）資料置入C3~C12、D3~D12、E3~E12。（註：請用貼上連結的方式製作） 「總分」欄（F3~F12）：計算每科學期成績之總和。（使用SUM） 「名次」欄（G3~G12）：依據「總分」成績填入每人之排名。（使用RANK函數） 將結果儲存於指定路徑下，檔名為EXA02.xlsx。

7 204. 成績計算表 在第一題中，要處理三張工作表，而且動作相同，有沒有什麼可以省時的方法呢？ 設定群組：
選［國文］工作表按住shift不放再選[英文],設定為一個群組 在群組的任一工作表中作處理,群組的其他工作表也一起工作

8 206. 血型分析 以下為「基本資料」工作表內的設定： 生日：格式為民國年月日，例如民國70/9/6顯示為「70年09月06日」。
年齡：利用DATEDIF及TODAY函數計算實際年齡（提示：DATEDIF(生日, TODAY(), “y”)）。 手機：組合「電信業者」、「門號區碼」、「門號末碼」，並在最前面加上「09」，在門號區碼的前後各加上「-」符號，如「 」（提示：利用字串連結符號＆、REPT、LEN函數）。 血型分析： 依據「血型」欄位搜尋「血型」工作表內各血型的「評價」與「分析」，在「評價」的前後加上「」符號（注意：必須利用檔案內範圍名稱）（提示：利用BIG5、VLOOKUP函數）。 字型大小為10、自動換列。 列高：第2~101列的列高為60。 A1~J101範圍套用「表格樣式中等深淺14」表格樣式，再轉換為資料範圍。 將結果儲存於指定路徑下，檔名為EXA02.xlsx。

9 206. 血型分析 注意：這張工作表有凍結窗格，在作選取時，要注意是否有選到未出現在螢幕上的 日期格式：
在EXCEL中有日期格式: “70年9月1日”，可是題目要求是”70年09月01日，所以先選格式： 再改為：[

10 206. 血型分析 本來可以在F2輸入：“09”&G2&”-”&H2&”-”&I2
手機： 本來可以在F2輸入：“09”&G2&”-”&H2&”-”&I2 但是，G2、H2、I2都是數值資料，不一定全為3位數，如果不是3位數，就要補上”0” REPT(text,number_times )函數：依指定的次數重複顯示文字。REPT 函數通常用來在儲存格中填入重複出現的文字字串。 Text:   是您所要重複顯示的文字資料。 Number_times   是個正數，用以指定所要重複的次數。 在F2輸入： “09”&G2&”-”&REPT(“0”, 3-LEN(H2))&H2&”-”&REPT(“0”, 3-LEN(I2))&I2

11 206. 血型分析 血型分析： 表格： 先測試vlookup函數： 本題公式？？？ 樣式＼格式化為表格 工具＼轉換為範圍
VLOOKUP(E2,血型,2,0)與VLOOKUP(E2,血型,2,1)的差別 本題公式？？？ 表格： 樣式＼格式化為表格 工具＼轉換為範圍

12 208 土木工程學會會員資料 匯入Unicode文字檔EXD02.txt，工作表名稱為「會員資料」：
會員編號：設定為文字型態欄位。 生日：民國日期型態。（匯入時必須是民國日期EMD資料型態）　 年齡：不匯入。 轉換為「表格樣式中等深淺9」的表格樣式。 更改地址欄位資料：將所有開頭為「北市」改為「台北市」，「北縣」改為「台北縣」。 刪除所有電子郵件地址欄位首字元的空白。 隱藏年薪欄位（無論數值或文字均須隱藏，儲存格內容與資料顯示均須是隱藏狀態，請使用自訂數字格式與隱藏功能）。 在「李軾遠」（儲存格B33）插入註解「會長」，在「李絲純」（儲存格B40）插入註解「副會長」。（必須隱藏註解顯示） 允許使用者輸入密碼「168168」，指定編輯範圍為D2~I49、K2~K49。 保護工作表。 2017/3/6

13 取得外部資料 選擇［資料］\［從文字檔］選擇文字檔所在 出現「匯入字串精靈」
資料分隔類型：含分隔符號，例如：tab->、空白格、或是逗號 資料中所包含的分隔符號：此題可選”空格”（解題秘笈是用tab->)　（第４題就一併解決了，可是有後遺症，因為這會造成年齡的欄位抓取有問題） 設定各資料欄位的格式設定 2017/3/6

14 在回答第３題時，要注意：資料中有“l北市”和“台北市”，
如果只是使用取代功能會發生，這兩者都會被取代成“台北市”和”台台北市”，第２個不對，，但評分時“台台北市”是不用再改一次 如果你自己以後在製作時，應該要將”台台北市“取代成”台北市”，所以要再進行一次取代 2017/3/6

15 儲存格註解 選擇儲存格後，按滑鼠右鍵，會出現選單 選擇［插入註解］ 2017/3/6

16 保護工作表及儲存格 以隱藏方式保護儲存格： 需要密碼才可以修改指定範圍內的內容 保護工作表： ［常用］／［數值］／［保護］
在保護標籤下，選擇鎖定及保護二項。 只有當工作表受到保護時，鎖定儲存格或隱藏公式才會生效。 需要密碼才可以修改指定範圍內的內容 ［校閱］索引標籤，［變更］群組，［允許使用者編輯範圍］ 保護工作表： ［校閱］索引標籤，［變更］群組，［保護工作表］按鈕 2017/3/6

17 210. 合併第一季至第四季報表 至『第四季』工作表： 計算每個成本項目的平均、總計、標準差：
取至千位數值：四張工作表內所有數值，其實際值均取千位，例如：815750變成81500（提示：利用ROUNDDOWN函數）。 計算每月總計：以SUM函數計算四張工作表內每月成本總和。 計算每個成本項目的平均、總計、標準差： 以AVERAGE函數計算四張工作表內每個成本項目的平均。 以SUM函數計算總計。 以STDEVP函數計算標準差。（E11與F11使用ＡＶＥＲＡＧＥ、SUM函數、G11使用STDEVP函數計算） 『年度報表』工作表：應用合併彙算功能，彙總「第一季」至「第四季」工作表內「一月」至「十二月」所有成本項目，結果如下頁所示，並需建立自動更新功能（刪除B欄，所有欄寬為11.25）。 2017/3/6

18 210. 合併第一季至第四季報表 ROUNDDOWN函數 ：將數值作無條件捨去。
語法：ROUNDDOWN(number,num_digits) Number   是要無條件捨去的任何實數。 Num_digits   是做無條件進位時所採用的位數。 如果 num_digits 大於 0 (零)，則無條件捨去到小數點後面指定的位數。 如果 num_digits 等於 0，數字將無條件捨去為整數。 如果 num_digits 小於 0，則無條件捨去到小數點左邊指定的位數。 備註：ROUNDDOWN 和 ROUND 類似，除了它一定無條件將數字捨去。 2017/3/6

19 STDEVP標準差函數 將引數串列視為母群體本身，傳回其母群體標準差。該標準差主要是用以衡量觀測資料與其平均數之間的差異量數。
語法：STDEVP(number1,number2,...) Number1, number2, ...   是對應於某母群體的 1 到 255 個數字引數。您也可以使用一個陣列或是陣列的參照位址，不必都用逗號分隔開的一串引數。 備註 STDEVP 函數假定它的引數串列是整個母群體。如果您的觀測資料代表該母群體的抽樣樣本，則應該使用 STDEV 函數來計算標準差。 當樣本個數愈大時，STDEV 與 STDEVP 函數所算出的標準差估計值會愈趨於相等。 標準差的計算是採用 n 方法。 引數可以是數值或包含數值的名稱、陣列或參照。 會計算直接輸入引數清單之邏輯值及數字的文字格式。 如果引數為陣列或參照，則只可使用該陣列或參照中的數字。陣列或參照中的空白儲存格、邏輯值、文字或錯誤值將被忽略。 若引數為錯誤值或無法轉換成數字的文字，則會產生錯誤。 若要將參照中的邏輯值及數字的文字格式列入計算，請使用 STDEVPA 函數。 STDEVP 函數的計算公式是： 其中，x 為樣本平均數 AVERAGE(number1,number2,…)，而 n 為樣本大小。 2017/3/6

20 合併彙算多個工作表中的資料 若要從個別的工作表摘要及回報結果，可以從個別的工作表將資料合併彙算到主工作表中。這些工作表可以和主工作表位於同一個活頁簿中，也可以位於其他活頁簿中。當您在合併彙算資料時，其實是將資料組合起來，以便能更輕鬆地進行定期或臨時更新與彙總。 例如，如果工作表內容是記載各區辦公室的支出數目，您可能需要使用合併彙算這個功能，將這些數字整理至企業的支出工作表。此主工作表可能包含銷售總額與平均值、目前的存貨水準以及整個企業銷售額最高的產品。 若要合併彙算資料，請使用 [資料] 索引標籤上 [資料工具] 群組中的 [合併彙算] 指令。 2017/3/6

21 文字函數 邏輯函數 日期與時間函數 數學與三角函數 統計函數 查閱與參照函數 財務函數
Ch05 函數 文字函數 邏輯函數 日期與時間函數 數學與三角函數 統計函數 查閱與參照函數 財務函數

22 前言 透過常用的進階函數從製作學生基本資料表、學生學業成績、學生操性成績，進而整合出個人成績查詢如下。

23 前言 我們說明一個函數時，會利用四個元素來闡示： 定義：說明函數的作用，可以用它來做什麼。 語法：函數的拼字及引數的用法。
註解：解釋函數在何種狀況下，會傳回什麼值，及其他特別要注意的地方。 範例：舉例說明之。

24 文字函數 - LEFT與RIGHT函數

25 範例5-1 假設我們要知道同學姓什麼？ 在O4儲存格輸入=LEFT(B4,1)，並複製公式到O13。

26 範例5-1 假設我們要知道同學姓什麼？ 我們也可以透過函數引數交談窗來協助我們填入適當的引數值。

27 LEN函數

28 範例5-2 假設我們要知道同學姓名共幾個字？ 我們在P4儲存格輸入=LEN(B4)，並複製公式到P13，將B6的「吳 尊」中間加一個空格，結果就不同了。

29 CONCATENATE (&)函數

30 範例5-3 假設我們要顯示同學的家長，姓後面加「先生」的尊稱
請在Q4儲存格輸入=CONCATENATE(O4,“先生”)或=LEFT(B4,1)&“先生”。 我們也可以到引數函數交談窗看進一步說明。

31 邏輯函數 簡而言之，邏輯函數就是用來判斷是非黑白的。我們先來看看一般邏輯值的應用。
假設我們要判斷哪些是男生，在K4儲存格輸入=C4="男"（也就是=(C4="男")），會看到結果傳回如下邏輯值，TRUE表示「是」，FALSE表示「非」。

32 邏輯函數 假設我們要判斷哪些身高在170（含）以上，在L4儲存格輸入=E4>=170（也就是=(E4>=170)），會看到結果傳回如下邏輯值，TRUE表示「是」，FALSE表示「非」，這也是單一條件的判斷。 在L4儲存格輸入=E4>=170

33 AND(且)函數 這些引數必須評估邏輯值，像是 TRUE 或 FALSE，或者，這些引數必須是裡面含有邏輯值的陣列或參照位址。
如果陣列或參照引數中包含文字或空白儲存格，則這些值都會略過。 如果我們指定的範圍裡面沒有邏輯值，AND 會傳回 #VALUE! 錯誤值。

34 範例5-4 自動選出身高在170(含)以上的男生擔任司儀
在M4儲存格輸入=AND(C4="男:,E4>=170)，會看到結果傳回如下邏輯值， ~ 是符合資格的。

35 範例5-5 選出身高在165跟175之間的同學來擔任司儀 依據需要我們的條件也就是身高>=1 6 5 且身高<=1 7 5 的同學， 在N4 儲存格輸入=AND(E4>=165,E4<=175) 在N4儲存格輸入=AND(E4>=165,E4<=175)

36 當然我們也可以透過函數引數交談窗來協助我們填入適當的引數值。
在Logical2（第二個條件式） 按一下 就會出現第三個條件式讓我們加入

37 OR(或)函數 所有的引數都必須評估為邏輯值TRUE或FALSE，或是在包含邏輯值的 陣列或參照中。
如果陣列或參照引數中包含文字或空白儲存格，則這些值都會略過。 如果所指定的範圍中並未包含邏輯值，則OR會傳回錯誤值#VALUE!。

38 範例5-6 假設我們要找出住在「台北」或「桃園」的同學
在R4儲存格輸入=OR(LEFT(I4,2)="台北",LEFT(I4,2)="桃園")，結果如下，只有 不是。

39 NOT函數 如果logical為FALSE，NOT傳回TRUE；如果logical為TRUE，NOT則傳回FALSE。

40 範例5-7 找出住在「台北」以外的同學 S4儲存格輸入=NOT(LEFT(I4,2)=「台北」)，結果如下，只有 與 不是。

41 IF函數 為配合value_if_true與value_if_false引數，以處理更為精巧的條件測試，則可使用多達64層的IF函數（上一版只提供7層）。 當value_if_true引數或value_if_false引數被執行時，則 IF 函數傳回這些引數的運算結果，而非引數本身。 Excel還提供了以條件式為主來分析資料的其他函數。例如，若要計算一個以文字字串或儲存格範圍中值的數量，可以使用COUNTIF工作表函數。 若要計算以文字字串或範圍內的值之總和，可以使用SUMIF工作表函數。

42 範例5-8 產生男女稱謂 在T4儲存格輸入=IF(H4="男",LEFT(G4,1)&" 先生",LEFT(G4,1)&" 女士")，結果如下。

43 範例5-9 自動顯示當日的日期 假設我們要想要每次開啟學生基本資料工作表時，都會自動顯示當日的日期。 在V1儲存格輸入=TODAY()

44 TODAY函數 Excel以循序序列值儲存日期，因此它可被用於計算。預設序列值為1，表示是1900年1月1日，並且2008年1月1日的序列值為39448， 因為此日期是在1900年1月1日的39448天之後。

45 NOW函數 Excel以循序數列的序列值來儲存日期，以至於它們可以用來計算。 依預設值，1900年1月1日是序號1，而2008年1月1日則是序號39448，因為這是1900年1月1日之後的第39,448天。在序列值小數點右邊的數字代表時間；左邊的數字代表日期。 例如，序列號碼 .5代表中午12:00。

46 範例5-10 自動顯示時間 假設我們要想要每次開啟學生基本資料工作表時，都會自動顯示現在的時間。 在V2儲存格輸入=NOW()

47 MONTH(YEAR，DAY)函數 依YEAR、MONTH和DAY函數傳回來的值為西曆，不論所供給日期的顯示格式。

48 範例5-11 顯示當月壽星與年齡 在U4儲存格輸入=MONTH(D4)，結果如下。
接下來我們要判斷月份的欄位中， 為3 月的同學， 請在V4 儲存格輸入=IF(MONTH(D4)=3,"是"," ")，得到3月份的壽星結果 U4儲存格輸入=MONTH(D4) V4儲存格輸入=IF(MONTH(D4)=3,"是","")

49 範例5-11 顯示當月壽星與年齡 再進一步的使用，用來計算同學的年齡，請在W4儲存格輸入=TODAY()-D4，即今天的日期減去出生的日期
將格式改為「通用」或「數值」。預覽結果如下，為日數。 在W4儲存格輸入=TODAY()-D4，在日期格式下，會顯示年月日

50 範例5-11 顯示當月壽星與年齡 將年齡的單位變更為年，請將公式改成=(TODAY()-D4)/365，結果如下，年齡為25歲多一些。

51 INT函數

52 範例5-12 顯示實際年齡 假設我們要想要知道同學的年齡是滿幾歲

53 ROUND函數 如果 num_digits 大於 0 ，則數字將被四捨五入到指定的小數位數。

54 範例5-13 延上題，顯示四捨五入的年齡 假設我們不是想要知道同學的年齡是滿幾歲，而是要四捨五入到小數第1位
W4公式改成=ROUND((TODAY()-D4)/365,1)

55 RAND函數 如果我們希望產生的亂數是介於a 與b 之間 的實數， 請使用公式： RAND()*(b-a)+a 。
如果我們想用RAND函數來產生隨機亂數， 但是不希望其值因工作表重算而不斷地改變， 請在資料編輯列輸入=RAND()，並按下 F9鍵， 我們所輸入的公式將立即變成隨機亂數。

56 COUNTIF函數 雖然COUNTIF函數被歸類在統計類函數中，但太常用了，所以我們拿來跟SUMIF一起討論！
Excel 提供了以條件式為主來分析資料的其他函數。例如，若要計算一個以文字字串或範圍中數值為主的和，可以使用SUMIF工作表函數。若要公式傳回以條件式為主的兩個值中的其中一個，如指定銷售量的銷售金額，可以使用 IF 工作表函數。

57 範例5-14 產生及格人數 我們先選取學生學業成績工作表，想要知道每一科及格的人數
將C21公式改成=COUNTIF(C9:C18,">=60")

58 COUNTIFS函數 對於範圍中的每個儲存格，只有指定的所有對應準則都為true，該儲存格才能用於計算。
我們可以在準則中使用萬用字元、問號(?)及星號(*)。問號可替代任一個字元；星號可替代任一連續字元。如果我們確實要尋找實際的問號或星號，請在該字元前輸入波狀符號(~)

59 範例5-15 顯示區間分數的人數 先選取學生學業成績工作表，想要知道每一科分數在60~80之間的人數。
將C22公式改成=COUNTIFS(C9:C18,">=60",C9:C18,"<=80")

60 SUMIF函數 sum_range和範圍是相對應的，當範圍中的儲存格符合搜尋篩選條件時，其對應的sum_range儲存格會被加入總數。

61 範例5-16 自動加總學期總分 我們可以透果有條件的加總函數SUMIF，動計算出每人的總學分數。
J9 = SUMIF(C9:G9,">0",$C$5:$G$5)

62 範例5-16 自動加總學期總分 SUMIF就是很的個IF傳回值的加總的簡約式寫法，看加總很多個IF多繁雜！

63 SUMIFS函數

64 SUMIFS函數 SUMIFS和SUMIF 中的引數順序是不同的。更明確地說，sum_range引數在SUMIFS中，是第一個引數，但是在SUMIF中，卻是第三個引數。 對於sum_range中的每個儲存格，只有指定的所有對應準則都為true，該儲存格才能用於加總。 會將sum_range中包含TRUE的儲存格視為1；而將sum_range中包含FALSE的儲存格視為0（零）。 與SUMIF函數中的範圍和準則引數不同，SUMIFS中的每個criteria_range都必須具有與sum_range相同的大小和形狀。 我們可以在準則中使用萬用字元、問號（?）及星號（*）。問號可替代任一個字元；星號可替代任一連續字元。如果我們確實要尋找實際的問號或星號，請在該字元前輸入波狀符號（~）。

65 範例5-17 顯示區段分數人數 接著我們想要知道每人的平均分數，由於有選修必修，分數是按加權平均來算，也就是(該科分數*該科學分)之所有科目總計除以總學分數。 將K9的公式改成=(C9*$C$5+D9*$D$5+E9*$E$5+F9*$F$5+G9*$G$5)/(IF(C9>0,$C$5,0)+ IF(D9>0,$D$5,0)+IF(E9>0,$E$5,0)+IF(F9>0,$F$5,0)+IF(G9>0,$G$5,0))

66 範例5-17 顯示區段分數人數 我們先選取學生學業成績工作表，想要知道每人所修科目分數在50~59之間的學分數。
將J9公式改成= SUMIFS($C$5:$G$5,C9:G9,">=50",C9:G9,"<=59")

67 範例5-17 顯示區段分數人數 可以利用J9的總學分數，J9 = SUMIF(C9:G9,">0",$C$5:$G$5)，如下。再利用總成績除以總學分數也會得到相同答案

68 SUMRODUCT函數 各陣列必須有相同的維度（ 相同的列數， 相同的欄數）。否則SUMPRODUCT函數會傳回錯誤值#VALUE!。
SUMPRODUCT函數會將所有非數值資料的陣列元素當成0來處理。我們觀察到上例的分子其實就是二個陣列C5~G5及C9~G9對應儲存格相乘後再加總，就可以將L9之加權平均改成如下了。

69 RANK函數 如果order為0（零）或被省略，則ref 成從大到小排序來評定number的等級。 如果order不是0，則ref當成從小到大排序來評定number的等級。 RANK把相同的數字評為同一等級，可是其後數字的等級還是會受到影響。例如，在一個整數串列中有二個10，等級為5，則11的等級將是7（而空出等級6）。

70 範例5-18 加權名次的製作 我們先選取學生學業成績工作表，想要知道依加權平均的名次。將M9公式改成=RANK(K9,$K$9:$K$18,0)，名次是不是都排好了

71 範例5-19 假設學生的評語是根據「加權平均1」分數而訂
在N9儲存格輸入=IF(K9>89,"優等",IF(K9>79,"甲等",IF(K9>69,"乙等",IF(K9>59,"及格","不及格")))) 。

72 MEDIAN函數 引數應為數字或包含數字的名稱、陣列，或參照。如果陣列或參照引數包含文字、邏輯值或空白儲存格，則忽略這些數值；但包含零值儲存格。 如果一組數字為偶數個數，MEDIAN 將計算中間兩個數字的平均值。

73 範例5-20 製作成績的中位數 選取學生學業成績工作表，想要知道每一科成績的中位數。 將C24公式改成= MEDIAN(C9:C18)

74 STDEV函數 STDEV函數假設它的引數是某母群體的抽樣樣本。如果我們的觀測資料代表整個母群體，則應該使用STDEVP函數來計算標準差。
標準差的計算是採用不偏估計或n-1法。

75 範例5-21 成績標準差 選取學生學業成績工作表，假設這些同學是隨機選取出來的，我們想要知道每一科成績的標準差。
C25公式改成= MEDIAN(C9:C18)

76 VAR函數 VAR函數假設它的引數串列為母群體的抽樣樣本，如果我們的觀測資料代表整個母群體，則使用VARP來計算變異數。

77 範例5-22 成績變異數 選取學生學業成績工作表，假設這些同學是隨機選取出來的，我們想要知道每一科成績的變異數。
C26公式改成= VAR(C9:C18)

78 FREQUENCY函數 在選定相鄰的儲存格範圍後，FREQUENCY會被輸入做為陣列公式，然後傳回的資料分佈情形就儲存到我們選定的範圍裏。
傳回陣列的元件數值在bins_array中為超過一個元件的數值在傳回陣列中的其他元件會傳回以上區間的任何數值。FREQUENCY會忽略空白儲存格及文字。 傳回陣列結果的公式必須以陣列公式的方式輸入。

79 範例5-23 整數的成績變異數 選取範圍 J22:J31 儲存格開始公式。在資料編輯列輸入=FREQUENCY(，出現公式引數提示。

80 選取適當的引數範圍，按下 CTRL+SHIFT + ENTER

81 CHOOSE函數 如果index_num之值為1，則CHOOSE函數會傳回value1；如果其值為2，CHOOSE函數會傳回value2；依此類推。 如果 index_num小於1或大於引數清單中最後值的個數，則CHOOSE傳回錯誤值#VALUE!。 如果index_num不是整數，則在運算之前，會先將它無條件捨去到最接近的整數。

82 範例5-24 獎逞紀錄統計 選取學生操性成績工作表，假設這些是同學9月份的獎懲紀錄，輸入日期後，我們先用WEEKDAY函數得到星期代碼。
B4公式改成= WEEKDAY(A4,2)

83 利用CHOOSE函數將B欄的值用星期幾顯示出來，C4= CHOOSE(B4,"星期一","星期二","星期三","星期四","星期五","星期六","星期日")

84 VLOOKUP(與HLOOKUP)函數 如果VLOOKUP函數找不到lookup_value，且range_lookup為TRUE時，則使用僅次於lookup_value的值。 如果lookup_value比table_array第一欄中的最小值還小，則VLOOKUP傳回錯誤值#N/A。 如果VLOOKUP函數找不到lookup_value，且range_lookup為TRUE時，則VLOOKUP傳回錯誤值#N/A。

85 範例5-24 自動顯示獎逞與加減分數資料 透過基本資料工作表將學生操性成績工作表中的姓名自動顯示出來，而輸入獎懲代號後，透過獎懲類別對照表工作表將獎懲名稱及加減分資料自動顯示出來。 選取E4儲存格，並按「公式→查閱及參照→VLOOKUP」函數。

86 開啟「函數引數」交談窗，按「Lookup_value」之摺疊 鈕。

87 選取D4儲存格表示我們想用學號來搜尋，並按摺疊 鈕回來，表示去找第一筆獎懲紀錄同學學號9651110是誰。

88 按「Table_array」之摺疊 鈕，選取學生基本資料工作表之A4~B13儲存格，表示我們想用學號到這個資料範圍來搜尋適當的姓名，並按摺疊 鈕回來。

89 假如我們有一個獎懲類別對照表如圖。

90 輸入Col_index_num的值為2，表示我們想用學號來搜尋上述資料範圍中之第2欄相對位置的儲存格資料(第1欄為學號，第2欄為姓名)。

91 可以利用複製公式的方式來顯示加減分

92 通常我們會做一個對照表，為了：（1）節省輸入這些上百上千的獎懲記錄的名稱或分數，（2）避免錯誤或不一致的資料而造成資料計算等處理錯誤，而抓取對照表時就要遵守一些規則，也就是函數參數的用法
用VLOOKUP參照函數。 參數1(lookup_value)，上百筆獎懲紀錄中每筆記錄資料的獎懲代號；如果學過資料庫的同學，這個欄位就相當於外鍵。 參數2(table_array)，即獎懲類別對照表，我們想拿參數1--獎懲代號到這個對照表來找其他對應的資料

93 參數3(col_index_num)，定義為找到比對資料後，傳回那一列中某欄的值；如參數1的資料為5，到獎懲類別對照表的最左欄(第1欄)去比對，結果找到第7列。
參數4(range_lookup)，當此引數值為TRUE或被省略了，會傳回最靠近符合的數值；也就是說，如果找不到完全符合的值時，會傳回僅次於lookup_value的值。

94 綜合應用一下上述的函數跟資料，假設操性成績是80去加或減學生獎懲紀錄之累積小計，我們可以作出如下之個人成績查詢。

95 PMT函數 有關PMT中引數的完整說明，請參閱PV函數。 如果省略fv引數，會自動假定為0，也就是說，貸款的年金終值是0。

96 利用下拉式選單，再查另一位同學學號，結果如下，是否又向資料處理核心又邁向一步了。

97 範例5-25 貸款金額的計算 假設某些同學想申請助學貸款，年利率3.5%，可借50,000元，期限為5年，試算看看每月必須負擔多少貸款。
輸入B1= PMT(3.5%/12,5*12,50000)，一個月只要付909.59元，真是鬆了一口氣，用公式的方式可修改貸款金額、或年利率、或年限，就可立刻看到重新計算的每月應繳金額。

98 PV函數 請確定我們用來指定rate和nper的單位是一致的。如果以四年期貸款，年利率為12 %，每月付款一次，rate請設定為12%/12，而nper則設定為 4*12。如果相同的貸款每年付款一次，rate請設定為12% ，而nper則設定為4。 年金是指在一段連續期間內一系列的固定金額給付活動。

99 範例5-26 投資計算 假設同學想看某銀行推銷某種基金，年利率5%，請我們預繳45,000，5年內每年領10,000元，看是否值得投資，我們可計算一下現值來幫我們做決策。 輸入A1= PV(5%,5,10000)，這5年給我的錢，換成現在只有43,294.77，比我投資的錢還多，當然不值得投資。