| Excel函數(shù)速查一覽 (一)數(shù)據(jù)庫函數(shù) 1.DAVERAGE 用途：返回數(shù)據(jù)庫或數(shù)據(jù)清單中滿足指定條件的列中數(shù)值 的平均值。 語法：DAVERAGE(database，field，criteria) 參數(shù)：Database 構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域。Field 指定函數(shù)所使用的數(shù)據(jù)列。Criteria 為一組包含給定條件的 單元格區(qū)域。 2.DCOUNT 用途：返回數(shù)據(jù)庫或數(shù)據(jù)清單的指定字段中，滿足給定條 件并且包含數(shù)字的單元格數(shù)目。 語法：DCOUNT(database，field，criteria) 參數(shù)：Database 構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域。Field 指定函數(shù)所使用的數(shù)據(jù)列。Criteria 為一組包含給定條件的 單元格區(qū)域。 3.DCOUNTA 用途：返回數(shù)據(jù)庫或數(shù)據(jù)清單指定字段中滿足給定條件的 非空單元格數(shù)目。 語法：DCOUNTA(database，field，criteria) 參數(shù)：Database 構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域。Field 指定函數(shù)所使用的數(shù)據(jù)列。Criteria 為一組包含給定條件的 單元格區(qū)域。 4.DGET 用途：從數(shù)據(jù)清單或數(shù)據(jù)庫中提取符合指定條件的單個 值。 語法：DGET(database，field，criteria) 參數(shù)：Database 構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域。Field 指定函數(shù)所使用的數(shù)據(jù)列。Criteria 為一組包含給定條件的 單元格區(qū)域。 5.DMAX 用途：返回數(shù)據(jù)清單或數(shù)據(jù)庫的指定列中，滿足給定條件 單元格中的最大數(shù)值。 語法：DMAX(database，field，criteria) 參數(shù)：Database 構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域。Field 指定函數(shù)所使用的數(shù)據(jù)列。Criteria 為一組包含給定條件的 單元格區(qū)域。 6.DMIN 用途：返回數(shù)據(jù)清單或數(shù)據(jù)庫的指定列中滿足給定條件的 單元格中的最小數(shù)字。 語法：DMIN(database，field，criteria) 參數(shù)：Database 構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域。Field 指定函數(shù)所使用的數(shù)據(jù)列。Criteria 為一組包含給定條件的 單元格區(qū)域。 7.DPRODUCT 用途：返回數(shù)據(jù)清單或數(shù)據(jù)庫的指定列中，滿足給定條件 單元格中數(shù)值乘積。 語法：DPRODUCT(database，field，criteria) 參數(shù)：同上 8.DSTDEV 用途：將列表或數(shù)據(jù)庫的列中滿足指定條件的數(shù)字作為一 個樣本，估算樣本總體的標準偏差。 語法：DSTDEV(database，field，criteria) 參數(shù)：同上 4 9.DSTDEVP 參數(shù)：將數(shù)據(jù)清單或數(shù)據(jù)庫的指定列中，滿足給定條件單 元格中的數(shù)字作為樣本總體，計算總體的標準偏差。 語法：DSTDEVP(database，field，criteria) 參數(shù)：同上 10.DSUM 用途：返回數(shù)據(jù)清單或數(shù)據(jù)庫的指定列中，滿足給定條件 單元格中的數(shù)字之和。 語法：DSUM(database，field，criteria) 參數(shù)：同上 11.DVAR 用途：將數(shù)據(jù)清單或數(shù)據(jù)庫的指定列中滿足給定條件單元 格中的數(shù)字作為一個樣本，估算樣本總體的方差。 語法：DVAR(database，field，criteria) 參數(shù)：同上 12.DVARP 用途：將數(shù)據(jù)清單或數(shù)據(jù)庫的指定列中滿足給定條件單元 格中的數(shù)字作為樣本總體，計算總體的方差。 語法：DVARP(database，field，criteria) 參數(shù)：同上 13.GETPIVOTDATA 用途：返回存儲在數(shù)據(jù)透視表報表中的數(shù)據(jù)。如果報表中 的匯總數(shù)據(jù)可見，則可以使用函數(shù)GETPIVOTDATA 從數(shù)據(jù)透視 表報表中檢索匯總數(shù)據(jù)。 語法：GETPIVOTDATA(pivot_table，name) 參數(shù)：Data_field 為包含要檢索的數(shù)據(jù)的數(shù)據(jù)字段的名 稱(放在引號中)。Pivot_table 在數(shù)據(jù)透視表中對任何單元 格、單元格區(qū)域或定義的單元格區(qū)域的引用，該信息用于決定 哪個數(shù)據(jù)數(shù)據(jù)透視表包含要檢索的數(shù)據(jù)。Field1，Item1， Field2，Item2 為1 到14 對用于描述檢索數(shù)據(jù)的字段名和項 名稱，可以任意次序排列。 (二)日期與時間函數(shù) 1.DATE 用途：返回代表特定日期的序列號。 語法：DATE(year，month，day) 參數(shù)：year 為一到四位，根據(jù)使用的日期系統(tǒng)解釋該參 數(shù)。默認情況下，Excel for Windows 使用1900 日期系統(tǒng)， 而Excel for Macintosh 使用1904 日期系統(tǒng)。Month 代表每 年中月份的數(shù)字。如果所輸入的月份大于12，將從指定年份 的一月份執(zhí)行加法運算。Day 代表在該月份中第幾天的數(shù)字。 如果 day 大于該月份的最大天數(shù)時，將從指定月份的第一天 開始往上累加。 注意：Excel 按順序的序列號保存日期，這樣就可以對其 進行計算。如果工作簿使用的是1900 日期系統(tǒng)，則Excel 會 將1900 年1 月1 日保存為序列號1。同理，會將1998 年1 月 1 日保存為序列號35796，因為該日期距離1900 年1 月1 日為 35795 天。 實例：如果采用1900 日期系統(tǒng)(Excel 默認)，則公式 “=DATE(2001，1，1)”返回36892。 2.DATEVALUE 用途：返回date_text 所表示的日期的序列號。該函數(shù)的 主要用途是將文字表示的日期轉(zhuǎn)換成一個序列號。 語法：DATEVALUE(date_text) 參數(shù)：Date_text 是用Excel 日期格式表示日期的文本。 在使用1900 日期系統(tǒng)中，date_text 必須是1900 年1 月1 日 到9999 年12 月31 日之間的一個日期；而在1904 日期系統(tǒng)中， date_text 必須是1904 年1 月1 日到9999 年12 月31 日之間 的一個日期。如果date_text 超出上述范圍，則函數(shù)DATEVALUE 返回錯誤值#VALUE!。 如果省略參數(shù)date_text 中的年代，則函數(shù)DATEVALUE 使用電腦系統(tǒng)內(nèi)部時鐘的當前年代，且date_text 中的時間信 息將被忽略。 實例：公式“=DATEVALUE("2001/3/5")”返回36955， DATEVALUE("2-26")返回36948。 3.DAY 用途：返回用序列號(整數(shù)1 到31)表示的某日期的天數(shù)， 用整數(shù) 1 到 31 表示。 語法：DAY(serial_number) 參數(shù)：Serial_number 是要查找的天數(shù)日期，它有多種輸 入方式：帶引號的文本串(如"1998/01/30")、序列號(如1900 日期系統(tǒng)的35825 表示 的1998 年1 月30 日)，以及其他公式 或函數(shù)的結(jié)果(如DATEVALUE("1998/1/30"))。 實例：公式“=DAY("2001/1/27")”返回27，=DAY(35825) 返回30，=DAY(DATEVALUE("2001/1/25"))返回25。 5 4.DAYS360 用途：按照一年360 天的算法(每個月30 天，一年共計 12 個月)，返回兩日期間相差的天數(shù)。 語法：DAYS360(start_date，end_date，method) 參數(shù)：Start_date 和end_date 是用于計算期間天數(shù)的起 止日期。如果start_date 在end_date 之后，則DAYS360 將返 回一個負數(shù)。日期可以有多種輸入方式：帶引號的文本串(例 如："1998/01/30")、序列號(例如：如果使用1900 日期系統(tǒng)， 則35825 表示1998 年1 月30 日)或其他公式或函數(shù)的結(jié)果(例 如，DATEVALUE("1998/1/30"))。 Method 是一個邏輯值，它指定了在計算中是采用歐洲方 法還是美國方法。若為FALSE 或忽略，則采用美國方法(如果 起始日期是一個月的31 日，則等于同月的30 日。如果終止日 期是一個月的31 日，并且起始日期早于30 日，則終止日期等 于下一個月的1 日，否則，終止日期等于本月的30 日)。 若 為TRUE 則采用歐洲方法(無論是起始日期還是終止日期為一 個月的 31 號，都將等于本月的 30 號)。 實例：公式“=DAYS360("1998/2/1"，"2001/2-1")”返回 1080。 5.EDATE 用途：返回指定日期(start_date)之前或之后指定月份的 日期序列號。 語法：EDATE(start_date，months) 參數(shù)：Start_date 參數(shù)代表開始日期，它有多種輸入方 式：帶引號的文本串(例如："1998/01/30")、序列號(如35825 表示1998 年1 月30 日)或其他公式或函數(shù)的結(jié)果(例如： DATEVALUE("1998/1/30"))。Months 為在start_date 之前或 之后的月份數(shù)，未來日期用正數(shù)表示，過去日期用負數(shù)表示。 實例：公式“=EDATE("2001/3/5"，2)”返回37016 即2001 年5 月5 日，=EDATE("2001/3/5"，-6)返回36774 即2000 年 9 月5 日。 6.EOMONTH 用途：返回start-date 之前或之后指定月份中最后一天 的序列號。 語法：EOMONTH(start_date，months) 參數(shù)：Start_date 參數(shù)代表開始日期，它有多種輸入方 式：帶引號的文本串(如"1998/01/30")、序列號(如1900 日期 系統(tǒng)中的35825)或其他公式或函數(shù)的結(jié)果(如 DATEVALUE("1998/1/30"))。Month 為start_date 之前或之后 的月份數(shù)，正數(shù)表示未來日期，負數(shù)表示過去日期。 實例：公式“=EOMONTH("2001/01/01"，2)”返回36981 即2001 年3 月31 日，=EOMONTH("2001/01/01"，-6)返回36738 即2000 年7 月31 日。 7.HOUR 用途：返回時間值的小時數(shù)。即介于0(12:00 A.M.)到 23(11:00 P.M.) 之間的一個整數(shù)。 語法：HOUR(serial_number) 參數(shù)：Serial_number 表示一個時間值，其中包含著要返 回的小時數(shù)。它有多種輸入方式：帶引號的文本串(如"6:45 PM")、十進制數(shù)(如0.78125 表示6:45PM)或其他公式或函數(shù) 的結(jié)果(如TIMEVALUE("6:45 PM"))。 實例：公式“=HOUR("3:30:30 PM")”返回15，=HOUR(0.5) 返回12 即12:00:00 AM，=HOUR(29747.7)返回16。 8.MINUTE 用途：返回時間值中的分鐘，即介于0 到59 之間的一個 整數(shù)。 語法：MINUTE(serial_number) 參數(shù)：Serial_number 是一個時間值，其中包含著要查找 的分鐘數(shù)。時間有多種輸入方式：帶引號的文本串(如"6:45 PM")、十進制數(shù)(如 0.78125 表示6:45 PM)或其他公式或函數(shù) 的結(jié)果(如TIMEVALUE("6:45 PM"))。 實例：公式“=MINUTE("15:30:00")”返回30， =MINUTE(0.06)返回26，=MINUTE(TIMEVALUE("9:45 PM"))返 回45。 9.MONTH 用途：返回以序列號表示的日期中的月份，它是介于 1(一 月)和12(十二月)之間的整數(shù)。 語法：MONTH(serial_number) 參數(shù)：Serial_number 表示一個日期值，其中包含著要查 找的月份。日期有多種輸入方式：帶引號的文本串(如 "1998/01/30")、序列號(如表示1998 年1 月30 日的35825) 或其他公式或函數(shù)的結(jié)果(如DATEVALUE("1998/1/30"))等。 實例：公式“=MONTH("2001/02/24")”返回2， =MONTH(35825)返回1，=MONTH(DATEVALUE("2000/6/30"))返 回6。 10.NETWORKDAYS 用途：返回參數(shù)start-data 和end-data 之間完整的工作 日(不包括周末和專門指定的假期)數(shù)值。 語法：NETWORKDAYS(start_date，end_date，holidays) 6 參數(shù)：Start_date 代表開始日期，End_date 代表終止日； Holidays 是表示不在工作日歷中的一個或多個日期所構(gòu)成的 可選區(qū)域，法定假日以及其他非法定假日。此數(shù)據(jù)清單可以是 包含日期的單元格區(qū)域，也可以是由代表日期的序列號所構(gòu)成 的數(shù)組常量。 函數(shù)中的日期有多種輸入方式：帶引號的文本串(如 "1998/01/30")、序列號(如使用1900 日期系統(tǒng)的35825)或其 他公式或函數(shù)的結(jié)果(如 DATEVALUE("1998/1/30"))。 注意：該函數(shù)只有加載“分析工具庫”以后方能使用。 11.NOW 用途：返回當前日期和時間所對應的序列號。 語法：NOW() 參數(shù)：無 實例：如果正在使用的是1900 日期系統(tǒng)，而且計算機的 內(nèi)部時鐘為 2001-1-28 12:53，則公式“=NOW()”返回 36919.54。 12.SECOND 用途：返回時間值的秒數(shù)(為0 至59 之間的一個整數(shù))。 語法：SECOND(serial_number) 參數(shù)：Serial_number 表示一個時間值，其中包含要查找 的秒數(shù)。關(guān)于時間的輸入方式見上文的有關(guān)內(nèi)容。 實例：公式“=SECOND("3:30:26 PM")”返回26， =SECOND(0.016)返回2。 13.TIME 用途：返回某一特定時間的小數(shù)值，它返回的小數(shù)值從0 到 0.99999999 之間，代表0:00:00(12:00:00 A.M)到 23:59:59(11:59:59 P.M) 之間的時間。 語法：TIME(hour，minute，second) 參數(shù)：Hour 是0 到23 之間的數(shù)，代表小時；Minute 是0 到59 之間的數(shù)，代表分；Second 是0 到59 之間的數(shù)，代表 秒。 實例：公式“=TIME(12，10，30)”返回序列號0.51，等 價于12:10:30 PM。=TIME(9，30，10)返回序列號0.40，等價 于9:30:10 AM。=TEXT(TIME(23，18，14)，"h:mm:ss AM/PM") 返回“11:18:14 PM”。 14.TIMEVALUE 用途：返回用文本串表示的時間小數(shù)值。該小數(shù)值為從 0 到 0.999999999 的數(shù)值，代表從 0:00:00 (12:00:00 AM) 到 23:59:59 (11:59:59 PM) 之間的時間。 語法：TIMEVALUE(time_text) 參數(shù)：Time_text 是一個用Excel 時間格式表示時間的文 本串(如"6:45 PM"和"18:45"等)。 實例：公式“=TIMEVALUE("3:30 AM")”返回 0.145833333，=TIMEVALUE("2001/1/26 6:35 AM")返回 0.274305556。 15.TODAY 用途：返回系統(tǒng)當前日期的序列號。 參數(shù)：無 語法：TODAY() 實例：公式“=TODAY()”返回2001-8-28(執(zhí)行公式時的 系統(tǒng)時間)。 16.WEEKDAY 用途：返回某日期的星期數(shù)。在默認情況下，它的值為 1(星期天)到7(星期六)之間的一個整數(shù)。 語法：WEEKDAY(serial_number，return_type) 參數(shù)：Serial_number 是要返回日期數(shù)的日期，它有多種 輸入方式：帶引號的文本串(如"2001/02/26")、序列號(如 35825 表示1998 年1 月30 日)或其他公式或函數(shù)的結(jié)果(如 DATEVALUE("2000/1/30"))。Return_type 為確定返回值類型 的數(shù)字，數(shù)字1 或省略則1 至7 代表星期天到數(shù)星期六，數(shù)字 2 則1 至7 代表星期一到星期天，數(shù)字3 則0 至6 代表星期一 到星期天。 實例：公式“=WEEKDAY("2001/8/28"，2)”返回2(星期 二)，=WEEKDAY("2003/02/23"，3)返回6(星期日)。 17.WEEKNUM 用途：返回一個數(shù)字，該數(shù)字代表一年中的第幾周。 語法：WEEKNUM(serial_num，return_type) 參數(shù)：Serial_num 代表一周中的日期。應使用DATE 函數(shù) 輸入日期，或者將日期作為其他公式或函數(shù)的結(jié)果輸入。 Return_type 為一數(shù)字，確定星期計算從哪一天開始。默認值 為 1。 18.WORKDAY 用途：返回某日期(起始日期)之前或之后相隔指定工作日 (不包括周末和專門指定的假日)的某一日期的值，并扣除周末 或假日。 語法：WORKDAY(start_date，days，holidays) 參數(shù)：Start_date 為開始日期；Days 為Start_date 之前 或之后不含周末及節(jié)假日的天數(shù)；Days 是正值將產(chǎn)生未來日 7 期、負值產(chǎn)生過去日期；Holidays 為可選的數(shù)據(jù)清單，表示 需要從工作日歷中排除的日期值(如法定假日或非法定假日)。 此清單可以是包含日期的單元格區(qū)域，也可以是由代表日期的 序列號所構(gòu)成的數(shù)組常量。日期有多種輸入方式：帶引號的文 本串(如"1998/01/30")、序列號(如1900 日期系統(tǒng)時的35825 表示1998 年1 月30 日)或其他公式或函數(shù)的結(jié)果(例如 DATEVALUE("1998/1/30"))。 19.YEAR 用途：返回某日期的年份。其結(jié)果為1900 到9999 之間的 一個整數(shù)。 語法：YEAR(serial_number) 參數(shù)：Serial_number 是一個日期值，其中包含要查找的 年份。日期有多種輸入方式：帶引號的文本串(例如 "1998/01/30")、序列號(例如，如果使用 1900 日期系統(tǒng)則 35825 表示 1998 年 1 月 30 日)或其他公式或函數(shù)的結(jié)果 (例如 DATEVALUE("1998/1/30"))。 實例：公式“=YEAR("2000/8/6")返回2000”， =YEAR("2003/05/01")返回2003，=YEAR(35825)返回1998。 20.YEARFRAC 用途：返回start_date 和end_date 之間的天數(shù)占全年天 數(shù)的百分比。 語法：YEARFRAC(start_date，end_date，basis) 參數(shù)：Start_date 表示開始日期，End_date 代表結(jié)束日 期。函數(shù)中的日期有多種輸入方式：帶引號的文本串(如 "1998/01/30")、序列號(如35829 表示1900 日期系統(tǒng)中的 1998 年1 月30 日)，或其他公式或函數(shù)的結(jié)果(例如 DATEVALUE("1998/1/30"))。Basis 表示日計數(shù)基準類型，其 中0 或省略為US(NASD)30/360，1 實際天數(shù)/實際天數(shù)，2 實 際天數(shù)/360，3 實際天數(shù)/365，4 歐洲30/360。 實例：公式“=YEARFRAC("2001/01/31"，"2001/06/30"， 0)”返回0.416666667，YEARFRAC("2001/01/25"， "2001/09/27")返回0.67222。 三、函數(shù)應用案例──算賬理財 1.零存整取儲蓄 “零存整取”是工薪階層常用的投資方式，這就需要計算 該項投資的未來值，從而決定是否選擇某種儲蓄方式。 (1)函數(shù)分解 FV 函數(shù)基于固定利率及等額分期付款方式，返回某項投 資的未來值。 語法：FV(rate,nper,pmt,pv,type) Rate 為各期利率；Nper 為總投資期，即該項投資的付款 期總數(shù)；Pmt 為各期所應支付的金額，其數(shù)值在整個年金期間 保持不變；Pv 為現(xiàn)值，即從該項投資開始計算時已經(jīng)入賬的 款項，或一系列未來付款的當前值的累積和；Type 為數(shù)字0 或1，用以指定各期的付款時間是在期初還是期末。 (2)實例分析 新建一個工作表，在其A1、B1、C1、D1 單元格分別輸入 “投資利率”、“投資期限”、“投資金額”和“賬戶初始金 額”。假設妻子新建一個賬戶每月底存入300 元，年利2.1%(即 月息0.00175)，連續(xù)存款5 年，可以在A2、B2、C2、D2 單元 格分別輸入“0.00175”、“60”、“500”和“1”。 然后選中E2 單元格輸入公式“=FV(A2,B2,-C2,D2,1)”， 回車即可獲得該投資的到期本金合計為“￥18,994.67”。公 式中的“-C2”表示資金是支出的，“C2”前不加負號也可， 這樣計算出來的結(jié)果就是負值。 如果丈夫也有“零存整取”賬戶，每月初存入200 元，年 利1.28%(即月息0.001667)，連續(xù)存款3 年，可以在A3、B3、 C3、D3 單元格分別輸入“0.001667”、“36”、“200”和 “0”。然后把E2 單元格中的公式復制到E3 單元格(將光標指 向E2 單元格的拖動柄，當黑色十字光標出現(xiàn)后向下拖動一 格)，即可得知該投資的到期本金合計“￥7,426.42”。 提示：上述計算結(jié)果包括本金和利息，但不包括利息稅等 其他費用。 2.還貸金額 如今貸款購買住房進行消費的家庭越來越多，計算貸款的 月償還金額是決策的重要依據(jù)，下面我們就來設計如何知道自 己每月的還款金額。 (1)函數(shù)分解 PMT 函數(shù)基于固定利率及等額分期付款方式，返回貸款的 每期付款額。 語法：PMT(rate,nper,pv,fv,type) Rate 為貸款利率；Nper 為該項貸款的付款總數(shù)；Pv 為現(xiàn) 值，或一系列未來付款的當前值的累積和；Fv 為未來值，或 在最后一次付款后希望得到的現(xiàn)金余額；Type 為數(shù)字0 或1。 (2)實例分析 新建一個工作表，在其A1、B1、C1、D1 單元格分別輸入 “貸款利率”、“還貸年限”、“貸款金額”和“還貸時間”。 假設貸款年利為4.1%(即月息0.00342)，預計的還貸時間為 10 年，貸款金額為10 萬元，且每月底還貸�？梢栽贏2、B2、 C2、D2 單元格分別輸入“0.00342”、“360”、“100000” 和“1”(表示月末還貸，0 表示月初還貸)。然后選中E2 單元 格輸入公式“= PMT(A2,B2,C2,,D2)”，回車就可以獲得每月 的還款金額為“￥-481.78”。 上式中C2 后的兩個逗號之間還有一個參數(shù)，表示還貸期 限結(jié)束時賬戶上的余額，對這個例子來說應該是0，所以可以 忽略該參數(shù)或?qū)懗伞? PMT(A2,B2,C2,0,D2)”。 3.保險收益 保險公司開辦了一種平安保險，具體辦法是一次性繳費 12000 元，保險期限為20 年。如果保險期限內(nèi)沒有出險，每 年返還1 000 元。請問在沒有出險的情況下，它與現(xiàn)在的銀行 利率相比，這種保險的收益率如何。 (1)函數(shù)分解 RATE 函數(shù)返回投資的各期利率。該函數(shù)通過迭代法計算 得出，并且可能無解或有多個解。 語法：RATE(nper,pmt,pv,fv,type,guess) Nper 為總投資期，即該項投資的付款期總數(shù)；Pmt 為各期 付款額，其數(shù)值在整個投資期內(nèi)保持不變；Pv 為現(xiàn)值，即從 該項投資開始計算時已經(jīng)入帳的款項，或一系列未來付款當前 值的累積和；Fv 為未來值，或在最后一次付款后希望得到的 現(xiàn)金余額；Type 為數(shù)字0 或1。 (2)實例分析 新建一個工作表，在其A1、B1、C1、D1 單元格分別輸入 “保險年限”、“年返還金額”、“保險金額”、“年底返還” 和“現(xiàn)行利息”。然后在A2、B2、C2、D2 和E2 單元格分別輸 43 入“20”、“1000”、“12000”、“1”(表示年底返還，0 表示年初返還)和“0.02”。然后選中F2 單元格輸入公式 “=RATE(A2,B2,C2,,D2,E2)”，回車就可以獲得該保險的年收 益率為“0.06”。要高于現(xiàn)行的銀行存款利率，所以還是有利 可圖的。上面公式中的C2 后面有兩個逗號，說明最后一次付 款后賬面上的現(xiàn)金余額為零。 4.個稅繳納金額 假設個人收入調(diào)節(jié)稅的收繳標準是：工資在800 元以下的 免征調(diào)節(jié)稅，工資800 元以上至1 500 元的超過部分按5%的 稅率征收，1 500 元以上至2 000 元的超過部分按8%的稅率征 收，高于2 000 元的超過部分按20%的稅率征收。我們可以按 以下方法設計一個可以修改收繳標準的工作簿： 新建一個工作表，在其A1、B1、C1、D1、E1 單元格分別 輸入“姓名”、“工資總額”、“扣款”、“個稅”和“實付 工資”。為了方便個稅標準的修改，我們可以另外打開一個工 作表(例如Sheet2)，在其A1、B1、C1、D1、E1 單元格中輸入 “免征標準”、“低標準”、“中等標準”和“高標準”，然 后分別在其下方的單元格內(nèi)輸入“800”、“1500”、 “2000”、“2000”。 接下來回到工作表Sheet1 中，選中D 列的D2 單元格輸入 公式“=IF(C2<=Sheet2!A2," ",IF((C2-Sheet2!A2)<=Sheet2!B2,(C2-Sheet2!A2)*0.05,IF (C2-Sheet2!C2<=Sheet2!C2,(C2-Sheet2!C2)*0.08,IF(C2>Sh eet2!D2,(C2-Sheet2!D2)*0.2))))”，回車后即可計算出C2 單元格中的應繳個稅金額。此后用戶只需把公式復制到C3、 C4 等單元格，就可以計算出其他職工應繳納的個稅金額。 上述公式的特點是把個稅的征收標準放到另一個工作表 中，如果征稅標準發(fā)生了變化，用戶只需修改相應單元格中的 數(shù)值，不需要對公式進行修改，可以減少發(fā)生計算錯誤的可能。 公式中的IF 語句是逐次計算的，如果第一個邏輯判斷 “C2-Sheet2!A2)<=Sheet2!B2”成立，即工資收入低于征收標 準，則個稅計算公式所在單元格被填入空格；如果第一個邏輯 判斷式不成立，則計算第二個IF 語句，直至計算結(jié)束。假如 征稅標準多于4 個，可以按上述繼續(xù)嵌套IF 函數(shù)(最多7 個)。 四、函數(shù)應用案例──信息統(tǒng)計 使用Excel 管理人事信息，具有無須編程、簡便易行的特 點。假設有一個人事管理工作表，它的A1、B1、C1、D1、E1、 F1、G1 和H1 單元格分別輸入“序號”、“姓名”、“身份證 號碼”、“性別”、“出生年月”等。自第2 行開始依次輸入 職工的人事信息。為了盡可能減少數(shù)據(jù)錄入的工作量，下面利 用Excel 函數(shù)實現(xiàn)數(shù)據(jù)統(tǒng)計的自動化。 1.性別輸入 根據(jù)現(xiàn)行的居民身份證號碼編碼規(guī)定，正在使用的18 位 的身份證編碼。它的第17 位為性別(奇數(shù)為男，偶數(shù)為女)， 第18 位為效驗位。而早期使用的是15 位的身份證編碼，它的 第15 位是性別(奇數(shù)為男，偶數(shù)為女)。 (1)函數(shù)分解 LEN 函數(shù)返回文本字符串中的字符數(shù)。 語法：LEN(text) Text 是要查找其長度的文本�？崭駥⒆鳛樽址M行計數(shù)。 MOD 函數(shù)返回兩數(shù)相除的余數(shù)。結(jié)果的正負號與除數(shù)相 同。 語法：MOD(number,divisor) Number 為被除數(shù)；Divisor 為除數(shù)。 MID 函數(shù)返回文本字符串中從指定位置開始的特定數(shù)目 的字符，該數(shù)目由用戶指定。 語法：MID(text,start_num,num_chars) Text 為包含要提取字符的文本字符串；Start_num 為文本 中要提取的第一個字符的位置。文本中第一個字符的 start_num 為1，以此類推；Num_chars 指定希望MID 從文本 中返回字符的個數(shù)。 (2)實例分析 為了適應上述情況，必須設計一個能夠適應兩種身份編碼 的性別計算公式，在D2 單元格中輸入 “=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女 "),IF(MOD(MID(C2,17,1),2)=1,"男","女"))”。回車后即可 在單元格獲得該職工的性別，而后只要把公式復制到D3、D4 等單元格，即可得到其他職工的性別。 為了便于大家了解上述公式的設計思路，下面簡單介紹一 下它的工作原理：該公式由三個IF 函數(shù)構(gòu)成，其中 “IF(MOD(MID(C2,15,1),2)=1,"男","女")”和 “IF(MOD(MID(C2,17,1),2)=1,"男","女")”作為第一個函數(shù) 的參數(shù)。公式中“LEN(C2)=15”是一個邏輯判斷語句，LEN 函 數(shù)提取C2 等單元格中的字符長度，如果該字符的長度等于15， 則執(zhí)行參數(shù)中的第一個IF 函數(shù)，否則就執(zhí)行第二個IF 函數(shù)。 在參數(shù)“IF(MOD(MID(C2,15,1),2)=1,"男","女")”中。 MID 函數(shù)從C2 的指定位置(第15 位)提取1 個字符，而MOD 函 數(shù)將該字符與2 相除，獲取兩者的余數(shù)。如果兩者能夠除盡， 說明提取出來的字符是0(否則就是1)。邏輯條件 44 “MOD(MID(C2,15,1),2)=1”不成立，這時就會在D2 單元格中 填入“女”，反之則會填入“男”。 如果LEN 函數(shù)提取的C2 等單元格中的字符長度不等于 15，則會執(zhí)行第2 個IF 函數(shù)。除了MID 函數(shù)從C2 的指定位置 (第17 位，即倒數(shù)第2 位)提取1 個字符以外，其他運算過程 與上面的介紹相同。 2.出生日期輸入 (1)函數(shù)分解 CONCATENATE 函數(shù)將幾個文本字符串合并為一個文本字 符串。 語法：CONCATENATE(text1,text2,...) Text1,text2,...為1～30 個要合并成單個文本項的文本 項。文本項可以為文本字符串、數(shù)字或?qū)蝹�單元格的引用。 (2)實例分析 與上面的思路相同，我們可以在E2 單元格中輸入公式 “=IF(LEN(C2)=15,CONCATENATE("19",MID(C2,7,2),"年 ",MID(C2,9,2),"月",MID(C2,11,2),"日 "),CONCCTENCTE(MID(C2,7,4),"年",MID(C2,11,2),"月 ",MID(C2,13,2),"日"))”。其中“LEN(C2)=15”仍然作為邏 輯判斷語句使用，它可以判斷身份證號碼是15 位的還是18 位的，從而調(diào)用相應的計算語句。 對15 位的身份證號碼來說，左起第7 至12 個字符表示出 生年、月、日，此時可以使用MID 函數(shù)從身份證號碼的特定位 置，分別提取出生年、月、日。然后用CONCATENATE 函數(shù)將提 取出來的文字合并起來，就能得到對應的出生年月日。公式中 “19”是針對早期身份證號碼中存在2000 年問題設計的，它 可以在計算出來的出生年份前加上“19”。對“18”位的身份 證號碼的計算思路相同，只是它不存在2000 年問題，公式中 不用給計算出來的出生年份前加上“19”。 注意：CONCATENATE 函數(shù)和MID 函數(shù)的操作對象均為文本， 所以存放身份證號碼的單元格必須事先設為文本格式，然后再 輸入身份證號。 3.職工信息查詢 Excel 提供的“記錄單”功能可以查詢記錄，如果要查詢 人事管理工作表中的某條記錄，然后把它打印出來，必須采用 下面介紹的方法。 (1)函數(shù)分解 INDEX 函數(shù)返回數(shù)據(jù)清單或數(shù)組中的元素值，此元素由行 序號和列序號的索引值給定。 INDEX 函數(shù)有兩種語法形式：數(shù)組和引用。數(shù)組形式通常 返回數(shù)值或數(shù)值數(shù)組，引用形式通常返回引用。當函數(shù)INDEX 的第一個參數(shù)為數(shù)組常數(shù)時，使用數(shù)組形式。 語法1(數(shù)組形式)：INDEX(array,row_num,column_num) Array 為單元格區(qū)域或數(shù)組常量。如果數(shù)組只包含一行或 一列，則相對應的參數(shù)row_num 或column_num 為可選。如果 數(shù)組有多行和多列，但只使用row_num 或column_num，函數(shù) INDEX 返回數(shù)組中的整行或整列，且返回值也為數(shù)組；Row_num 為數(shù)組中某行的行序號，函數(shù)從該行返回數(shù)值。如果省略 row_num，則必須有column_num；Column_num 為數(shù)組中某列的 列序號，函數(shù)從該列返回數(shù)值。如果省略column_num，則必 須有row_num。 語法2(引用形式)： INDEX(reference,row_num,column_num,area_num) Reference 表示對一個或多個單元格區(qū)域的引用。如果為 引用輸入一個不連續(xù)的區(qū)域，必須用括號括起來。如果引用中 的每個區(qū)域只包含一行或一列，則相應的參數(shù)row_num 或 column_num 分別為可選項；Row_num 引用中某行的行序號，函 數(shù)從該行返回一個引用；Column_num 引用中某列的列序號， 函數(shù)從該列返回一個引用；Area_num 選擇引用中的一個區(qū)域， 并返回該區(qū)域中row_num 和column_num 的交叉區(qū)域。選中或 輸入的第一個區(qū)域序號為1，第二個為2，以此類推。如果省 略area_num，函數(shù)INDEX 使用區(qū)域1。 MATCH 函數(shù)返回在指定方式下與指定數(shù)值匹配的數(shù)組中 元素的相應位置。 語法：MATCH(lookup_value,lookup_array,match_type) Lookup_value 為需要在數(shù)據(jù)表中查找的數(shù)值； Lookup_value 為需要在Look_array 中查找的數(shù)值； Match_type 為數(shù)字-1、0 或1。 (2)實例分析 如果上面的人事管理工作表放在Sheet1 中，為了防止因 查詢操作而破壞它(必要時可以添加只讀保護)，我們可以打開 另外一個空白工作表Sheet2，把上一個數(shù)據(jù)清單中的列標記 復制到第一行。假如你要以“身份證號碼”作為查詢關(guān)鍵字， 就要在C2 單元格中輸入公式 “=INDEX(Sheet1!C2:C600,MATCH( SC S5,Sheet1! SC S2: SC S600,0),1)”。其中的參數(shù)“ SC S5”引用公式所在工作表中 的C5 單元格(也可以選用其他單元格)，執(zhí)行查詢時要在其中 輸入查詢關(guān)鍵字，也就是待查詢記錄中的身份證號碼。參數(shù) “Sheet1!C2:C600”設定INDEX 函數(shù)的查詢范圍，引用的是數(shù) 據(jù)清單C 列的所有單元格。MATCH 函數(shù)中的參數(shù)“0”指定它 查找“Sheet1! SC S2: SC S600”區(qū)域中等于 SC S5 的第一 個值，并且引用的區(qū)域“Sheet1! SC S2: SC S600,0”可以按 任意順序排列。 |
|站長郵箱|小黑屋|手機版|Office中國/Access中國
( 粵ICP備10043721號-1 )
GMT+8, 2025-7-13 08:17 , Processed in 0.085551 second(s), 16 queries .
Powered by Discuz! X3.3
© 2001-2017 Comsenz Inc.
