Ch04 第二類題目

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

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

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

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

資料/假設情況分析

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。

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

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

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

206. 血型分析 • 手機： • 本來可以在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

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

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

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

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

儲存格註解 • 選擇儲存格後，按滑鼠右鍵，會出現選單 • 選擇［插入註解］

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

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

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

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

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

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

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

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

文字函數 - LEFT與RIGHT函數

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

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

LEN函數

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

CONCATENATE (&)函數

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ch04 第二類題目