1 / 31

第 9 章

第 9 章. 常用的進階函數. 9-1 文字函數 - LEFT 與 RIGHT 函數. 定義： LEFT 傳回一文字字串中 第一個 字元或字元組，以您指定字元組的數值為準。 語法： LEFT ( text ,num_chars) Text 為含有所要選錄文字之字串。 Num_char 指定您要 LEFT 選錄的字元數。. 而 RIGHT 函數則是傳回自一文字串的 最後 字元或字元組 ( 依據您所指定的字元組數 ) ，其他用法雷同。. 文字函數 - LEN 函數. 定義： LEN 傳回一文字字串的 字元個數 。 語法： LEN (text)

virginia-ramirez
Download Presentation

第 9 章

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第9章 常用的進階函數

  2. 9-1 文字函數-LEFT與RIGHT函數 • 定義：LEFT傳回一文字字串中第一個字元或字元組，以您指定字元組的數值為準。 • 語法：LEFT (text,num_chars) • Text為含有所要選錄文字之字串。 • Num_char指定您要 LEFT 選錄的字元數。 而RIGHT函數則是傳回自一文字串的最後字元或字元組 (依據您所指定的字元組數)，其他用法雷同。

  3. 文字函數-LEN函數 • 定義：LEN傳回一文字字串的字元個數。 • 語法：LEN (text) • Text為所要計算字元個數的文字串，字串中所有的空白亦當作字元來處理。

  4. 文字函數-CONCATENATE (&)函數 • 定義：將數個文字串連成一個文字串。 • 語法：CONCATENATE (text1,text2,...) • Text1, text2, ...是要連接成一個文字串的 1 到 30 個文字串。文字項目可以是文字字串、數字或單一儲存格的參照位址。

  5. 9-2 邏輯函數 • 簡而言之，邏輯函數就是用來判斷是非黑白的。我們先來看看一般邏輯值的應用。利用且、或來組成的條件就是複合條件。

  6. 9-2 邏輯函數-AND(且)函數 • 定義：如果所有的引數都是 TRUE就會傳回 TRUE；如果有一或多個引數是 FALSE 就會傳回 FALSE。 • 語法：AND(logical1,logical2, ...) • logical1,logical2, ...，係指您要測試的 1 到 30 個條件，可能是 TRUE 或 FALSE。 • 也可以透過函數引數交談窗來協助我們填入適當的引數值哦！在Logical2(第二個條件式) 按一下，就會出現第三個條件式讓你加入。

  7. 9-2 邏輯函數-OR(或)函數 • 定義：如果有任何一個引數的邏輯值為 TRUE，即傳回 TRUE；唯有所有引數的邏輯值均為FALSE時，才會傳回 FALSE。 • 語法：OR(logical1,logical2,...)

  8. 9-2 邏輯函數-NOT函數 • 定義：將引數之數值予以反轉。NOT 可用來確定某一數值不等於某一特定的數值。 • 語法：NOT(logical)

  9. 9-2 邏輯函數-IF函數 • 定義：如果您指定的情況結果為 TRUE，則傳回一個值，若結果為 FALSE，則傳回另一個值。IF 函數可用以測試數值和公式的條件。 • 語法：IF (logical_test, value_if_true, value_if_false) 條件判斷式 條件成立時執行的動作或傳回的值 條件不成立時執行的動作或傳回的值 當 value_if_true 引數或 value_if_false 引數被執行時，則 IF 函數傳回這些引數的運算結果，而非引數本身。

  10. 9-2 邏輯函數-多層次IF函數 為配合 value_if_true 與 value_if_false 引數，以處理更為精巧的條件測試，則可使用多達七層的 IF 函數。

  11. 9-3日期與時間函數-TODAY函數 • 定義：傳回目前日期序列值。 此序列值是 Microsoft Excel 用以從事日期及時間計算的代碼。如果儲存格格式在輸入函數之前是 [通用]，則結果的格式會是日期格式。 • 語法：TODAY( ) 括弧不可省略 • 註解： Excel 以循序序列值儲存日期，因此它可被用於計算。預設序列值為 1，表示是 1900 年1 月 1 日，並且 2008 年 1 月 1日的序列值為 39448， 因為此日期是在 1900 年 1 月 1 日的 39448 天之後。 如果儲存格格式是 [通用]或[日期]就會像下面這樣子。

  12. 9-3日期與時間函數-MONTH(YEAR，DAY)函數 • 定義：傳回 serial_number（序列值）代表日期的月份。月份數為介於 1 （1月）到 12（12月）之間的整數。 • 語法：MONTH(serial_number) • Serial_number（序列值） 為您試著尋找的月份。必須使用 DATE 函數輸入日期，或其他的公式。 例如，使用 DATE(2008,5,23) 表示 2008 年 5月 23 日。 • YEAR，DAY與MONTH用法相同只是分別傳回日期引數的年與日。 也可以配合IF及邏輯函數，在V4儲存格輸入=IF(MONTH(D4)=3,”是”,””)，結果如右。

  13. 9-3日期與時間函數-MONTH(YEAR，DAY)函數-續 • 在W4儲存格輸入=TODAY()-D4，即今天的日期減去出生的日期，結果如下。結果錯誤？因為格式仍為日期。 • 將格式改為[通用]或[數值]。 • 結果為日數。 • 將公式改成=(TODAY()-D4)/365，結果即年齡，為19歲多一些。

  14. 9-4算數(數學)函數-INT函數 • 定義：傳回指定小數位數無條件捨去之整數值。 • 語法：INT (number) • Number想要無條件捨去成為一整數的實數。

  15. 9-4算數(數學)函數-RAND函數 • 定義：傳回一個大於等於 0 且小於 1 的隨機亂數。每當工作表重算時，便會傳回一個新的隨機亂數。 • 語法：RAND( ) • 註解： 如果您希望產生的亂數是介於 a與b之間的實數，請使用公式： • RAND()*(b-a)+a 。 • 試試看在1~10號中要抽出一個去參加消防演習，應該怎麼做？

  16. 9-4算數(數學)函數-COUNTIF函數 • 計算某範圍內符合某搜尋篩選條件的儲存格個數。 • 語法：COUNTIF(range,criteria) • Range是您想計算符合篩選條件之儲存格個數的儲存格範圍。 • Criteria是用以決定是否要列入計算的搜尋篩選條件，可以是數字、表示式或文字。例如，篩選條件可以是 32、“32”、“>32”或 “蘋果”。 • 例如：我們想要知道每一科及格的人數。

  17. 9-4算數(數學)函數-SUMIF函數 • 定義：加總符合某特定搜尋篩選條件的儲存格。 • 語法：SUMIF(range,criteria,sum_range) • Range是要計算加總的儲存格範圍。 • 篩選條件是用以決定要否列入加總的搜尋篩選條件，可以是數字、表示式或文字。 • Sum_range是實際要加總的儲存格。

  18. 9-5 統計函數-RANK函數 • 定義：傳回某數字在一串數字清單中的等級。數字的等級就是數字相對於清單中其他數值的大小。(如果你把這清單中的數字排序，則此數字的等級就是它所在的位置)。 • 語法：RANK(number,ref,order) • Number是要知道等級的數字。 • Ref是一個數值陣列或數值參照位址，非數值將被忽略。 • Order是指定的順序。

  19. 9-5 統計函數-MEDIAN函數 • 定義：傳回引數串列內的中位數。中位數為一組數字的中間數字；即一半數字的值大於中位數，而另一半數字的值小於中位數。 • 語法：MEDIAN(number1,number2,...) • Number1, number2, ... 是 1 到 30 個數字，您需要找出這些數字的中位數。

  20. 9-5 統計函數-STDEV函數 • 定義：根據一組樣本估計其標準差。標準差主要是用以衡量觀測資料與平均數之間的差異量數。 • 語法：STDEV(number1,number2,...) • Number1, number2, ...是對應於某母群體抽樣樣本的 一到三十個數字引數。您也可以使用一個陣列或是點選陣列的參照位址，不必都用逗號分隔開的一串引數。 • 註解： • STDEV 函數假設它的引數是某母群體的抽樣樣本。如果您的觀測資料代表整個母群體，則應該使用 STDEVP 函數來計算標準差。

  21. 9-5 統計函數-VAR函數 • 定義：估計樣本的變異數。 • 語法：VAR(number1,number2,...) • Number1, number2, ...是對應於某母群體抽樣樣本的一到三十個數字引數。 • 註解： VAR 函數假設它的引數串列為母群體的抽樣樣本，如果您的觀測資料代表整個母群體，則使用 VARP 來計算變異數。

  22. 9-6檢視與參照(尋找與參照)函數-CHOOSE函數 • 定義：使用 index_num 自引數清單中傳回相對應的引數數值，引數的個數可由 1 到 29 個。語法：CHOOSE(index_num,value1,value2,...) • Index_num是用以指定要選取第幾個引數值的數值。Index_num 引數必須是個 1 到 29 的數字、公式或參照位址 (參照的儲存格有一個數字，數字範圍從 1 到 29)。 • 如果 index_num 之值為 1，則 CHOOSE 函數會傳回 value1；如果其值為 2，CHOOSE 函數會傳回 value2；依此類推。 • 例如：我們先用WEEKDAY函數得到星期代碼。但是我們想讓它顯示得正式一點，利用CHOOSE函數將B欄的值用星期幾顯示出來。

  23. 9-6檢視與參照函數-VLOOKUP函數 • 定義：在一陣列或表格的最左欄中尋找含有某特定值的欄位，再傳回同一列中某一指定儲存格中的值。語法： • VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) • Lookup_value是您打算在陣列的最左欄中搜尋的值。 Lookup_value 可以是數值、參照位址或文字字串。 • Table_array是要在其中搜尋的資料表格。通常是儲存格範圍的參照位址或類似資料庫或清單的範圍名稱。 Table_array 第一欄裡的值，可以是文字、數字或邏輯值。 字母的大小寫被視為是相同的。 • Col_index_num是個數值，代表所要傳回的值位於 table_array 中的第幾欄。如果 col_index_num 引數值為 1，傳回在 table_array 第一欄的值，如果 col_index_num 引數值為 2，傳回 table_array 第二欄的值，依此類推。 • Range_lookup是個邏輯值，用來指定 VLOOKUP 要尋找完全符合或部分符合的值。 • 如果用來比對的數值位於您所要尋找的資料之左邊直欄時，就必須使用到 VLOOKUP 函數，而非 HLOOKUP 函數。VLOOKUP 中的 V 表示「Vertical」垂直之意。

  24. VLOOKUP函數(續) • 範例：輸入學號後，我們想透過學生學業成績工作表將姓名自動顯示出來，而輸入獎懲代號後，透過獎懲類別對照表工作表將獎懲名稱及加減分資料自動顯示出來。

  25. VLOOKUP函數(續) • 按[Lookup_value]之摺疊鈕。選取D4儲存格表示我們想用學號來搜尋，並按摺疊鈕回來。 • 按[Table_array]之摺疊鈕。 • 選取學生學業成績工作表之A9~B18儲存格，表示我們想用學號到這個資料範圍來搜尋適當的姓名，並按摺疊鈕回來。

  26. VLOOKUP函數(續) • 輸入Col_index_num的值為2，表示我們想用學號來搜尋上述資料範圍中之第2欄相對位置的儲存格資料(第1欄為學號，第2欄為姓名)。 • 利用填滿控點將E4的公式複製直到E20，發現有一些錯誤，原來要將搜尋的資料範圍改為絕對位址哦，改好後再複製一次即可。 • HLOOKUP函數跟VLOOKUP用法大同小異，只是它是參考橫向資料範圍。

  27. 9-6檢視與參照函數-TRANSPOSE函數 • 定義：將儲存格之垂直範圍以水平範圍的格式傳回，反之亦可。TRANSPOSE 必須是個有欄和列的陣列分別以有相同數目的欄和列範圍的陣列公式輸入。使用 TRANSPOSE 來移動工作表上陣列的垂直和水平方向。 • 語法：TRANSPOSE(array) • Array是工作表或巨集表中您所要轉置的矩陣或儲存格範圍。轉置矩陣的建立是以陣列的第一列作為新陣列的第一欄，而 陣列的第 2 列則為新陣列的第 2 欄，依此類推。 • 範例：我們想將獎懲類別對照表的資料轉製一下， • Step1：選取欲轉置目的儲存格範圍，並按[插入函數]。

  28. TRANSPOSE函數-續 • Step2：開啟[插入函數]交談窗，選取類別”檢視與參照”，並選取”TRANSPOSE”函數。 • Step3：按[Array]之摺疊鈕。 • Step4：選取A2~C15儲存格表示我們想轉置這個範圍的資料，並按摺疊鈕回來。 • Step5：並確定鈕回來。

  29. TRANSPOSE函數-續 • Step6：在資料編輯列按一下，並按Control+Shift+Enter。 • Step7：看到如下結果！

  30. 9-7 財務函數-PV函數 • 定義：傳回某項投資的年金現值。年金現值為未來各期年金現值的總和。 • 語法：PV(rate,nper,pmt,fv,type) • Rate為各期的利率。例如，如果您的汽車貸款為年利率 10%，每月付款一次，則每月的利率是 10%/12 或是 0.83%。 • Nper為年金的總付款期數。例如，如果您的汽車貸款為四年期，每月付款一次，則貸款期數為 4*12 (或 48)。您就要在公式的 nper 引數的位置輸入 48。 • Pmt為各期所應給付 (或所能取得) 的固定金額。 • Fv為最後一次付款完成後，所能獲得的現金餘額 (年金終值)。如果省略 fv 引數，會自動假定為 0 (例如貸款的年金終值是 0 )。 • Type為 0 或 1 的數值，用以界定各期金額的給付時點。0 或省略代表期末付款，1代表期初付款。 • 範例1 ：某銀行為推銷某種基金，年利率4.5%，請你預繳45,000，5年內每年領10,000元，我們想計算一下現值，看是否值得投資。輸入A1= PV(5%,5,10000)，這5年給我的錢，換成現在只有43,294.77，比我投資的錢還多，當然不值得投資。

  31. 9-7 財務函數-PMT函數 • 定義：固定利率與固定其數下，傳回每期付款金額。 • 語法：PMT(rate,nper,pv,fv,type) • 有關 PMT 中引數的完整說明，請參閱 PV 函數。 • Rate為各期的利率。 • Nper為年金的總付款期數。 • Pv為未來各期年金現值的總和。 • Fv為最後一次付款完成後，所能獲得的現金餘額 (年金終值)。如果省略 fv 引數，會自動假定為 0，也就是說，貸款的年金終值是 0。 • 範例1 ：假設”酸菜”同學想申請助學貸款，年利率7.8%，可借50,000元，期限為5年，看看每月必須負擔多少貸款。 • Step1：輸入A1= PMT(7.8%/12,5*12,50000)，一個月只要付1,009元，真是鬆了一口氣。

More Related