Excel2010常用函數(shù)

1、Excel2010 常用函數(shù) SUM(number1,number2,.) 例如，SUM(A1:A5) 將單元格 A1 至 A5 中的所有數(shù)字相加，再如，SUM(A1, A3, A5) 將單元格 A1、A3 和 A5 中的數(shù)字相加。 SUMIF(range, criteria, sum_range)   例如，假設在含有數(shù)字的某一列中，需要讓大于 5 的數(shù)值相加，請使

2、用以下公式： =SUMIF(B2:B25,">5") SUMIF 函數(shù)語法具有以下參數(shù)： range  必需。用于條件計算的單元格區(qū)域。每個區(qū)域中的單元格都必須是數(shù)字或名稱、數(shù)組或包含數(shù)字的引用。空值和文本值將被忽略。  criteria  必需。用于確定對哪些單元格求和的條件，其形式可以為數(shù)字、表達式、單元格引用、文本或函數(shù)。例如，條件可以表示為 32、">32"、B5、32、"32"、"蘋果"

3、; 或 TODAY()。  要點  任何文本條件或任何含有邏輯或數(shù)學符號的條件都必須使用雙引號 (") 括起來。如果條件為數(shù)字，則無需使用雙引號。 sum_range  可選。要求和的實際單元格（如果要對未在 range 參數(shù)中指定的單元格求和）。如果 sum_range 參數(shù)被省略，Excel 會對在 range 參數(shù)中指定的單元格（即應用條件的單元格）求和。 SUMIFS(sum_range,

4、 criteria_range1, criteria1, criteria_range2, criteria2, .) 對區(qū)域中滿足多個條件的單元格求和。例如，如果需要對區(qū)域 A1:A20 中符合以下條件的單元格的數(shù)值求和：B1:B20 中的相應數(shù)值大于零 (0) 且 C1:C20 中的相應數(shù)值小于 10，則可以使用以下公式： =SUMIFS(A1:A20, B1:B20, ">0", C1

5、:C20, "<10") SUMIFS 函數(shù)語法具有以下參數(shù) （參數(shù)：為操作、事件、方法、屬性、函數(shù)或過程提供信息的值。）： sum_range  必需。對一個或多個單元格求和，包括數(shù)字或包含數(shù)字的名稱、區(qū)域或單元格引用 （單元格引用：用于表示單元格在工作表上所處位置的坐標集。例如，顯示在第 B 列和第 3 行交叉處的單元格，其引用形式為“B3”。）。忽略空白和文本值。  criteria_range1  必需

6、。在其中計算關聯(lián)條件的第一個區(qū)域。  criteria1  必需。條件的形式為數(shù)字、表達式、單元格引用或文本，可用來定義將對 criteria_range1 參數(shù)中的哪些單元格求和。例如，條件可以表示為 32、">32"、B4、"蘋果" 或 "32"。  criteria_range2, criteria2,   可選。附加的區(qū)域及其關聯(lián)條件。最多允許 127 

7、個區(qū)域/條件對。 SUMPRODUCT(array1, array2, array3, .) （1）計算工作表內(nèi)多列中對應值相乘之后的和，即乘積之和。其中，Array1, array2, array3, 為 2 到 30 個數(shù)組，其相應元素需要進行相乘并求和。數(shù)組參數(shù)必須具有相同的維數(shù)，否則，函數(shù) SUMPRODUCT 將返回錯誤值 #VALUE!。該函數(shù)將非數(shù)值型的數(shù)組元素作為 0 處理。（2）SUMPRODUCT函數(shù)乘積之和舉例ABCD11210公式： =SUMPRODUCT(A1:B3, C1:D3)，說明：兩個數(shù)組的所有元素對應相乘，然

8、后把乘積相加，即 1*10 + 2*20 + 3*30 + 4*40 + 5*50 + 6*60(結果為910)2023430403565060（3）多條件求和+求個數(shù)、使用SUMPRODUCT進行多條件計數(shù)  ，計數(shù)就是統(tǒng)計滿足條件的個數(shù)。語法：SUMPRODUCT(條件1)*(條件2)*(條件3)* (條件n)作用：統(tǒng)計同時滿足條件1、條件2到條件n的記錄的個數(shù)。實例：=SUMPRODUCT(A2:A10="男")*(B2:B10="中級職稱")，公式解釋：統(tǒng)計性別為男且職稱為中級職稱的職工的人數(shù)、使用SUMPRODUCT進行多條件求和 &

9、#160;語法：SUMPRODUCT(條件1)*(條件2)* (條件3) *(條件n)*某區(qū)域)  作用：  匯總同時滿足條件1、條件2到條件n的記錄指定區(qū)域的匯總金額。實例： =SUMPRODUCT(A2:A10="男")*(B2:B10="中級職稱")*C2:C10)  公式解釋：統(tǒng)計性別為男性且職稱為中級職稱的職工的工資總和（假設C列為工資）SUMPRODUCT(條件1*條件2*條件3.條件N)利用此函數(shù)進行多條件計數(shù)時，* ：滿足所有條件；SUMPRODUCT(條件1+條件2+條件3.+條件N) + ：滿足任一條件例

10、子一：=SUMPRODUCT(G1:G3="男")*(E1:E3<=60)這個公式的意思就是要這樣來統(tǒng)計，G1:G3滿足條件是男的，同時E1:E3的數(shù)值小于等于60，滿足這兩個條件的總人數(shù)。首先這是一個數(shù)組公式，要按Ctrl+Shift+Enter結束。例子二：多條件求和求男性及格人數(shù)：=SUMPRODUCT(C2:C13>=60)*1,(B2:B13="男")*1)例子三：多條件求和匯總三班籍貫為浙男性人數(shù)，=SUMPRODUCT(B2:B13="男")*1,(D2:D13="三班")*1,(E2:E

11、13="浙")*1)PRODUCT(number1, number2, .)  PRODUCT 函數(shù)使所有以參數(shù)形式給出的數(shù)字相乘并返回乘積。例如，如果單元格 A1 和 A2 中包含數(shù)字，則可以使用公式 =PRODUCT(A1,A2) 將這兩個數(shù)字相乘。您也可以通過使用乘 (*) 數(shù)學運算符（例如 =A1*A2）執(zhí)行相同的操作。當需要使很多單元格相乘時，PRODUCT 函數(shù)很有用。例如，公式 =PRODUCT(A1:A3, C1:C3) 等價于 =A1 *

12、 A2 * A3 * C1 * C2 * C3number1    必需。要相乘的第一個數(shù)字或范圍。number2, .    可選。要相乘的其他數(shù)字或單元格區(qū)域，最多可以使用 255 個參數(shù)。注意   如果參數(shù)是一個數(shù)組或引用，則只使用其中的數(shù)字相乘。數(shù)組或引用中的空白單元格、邏輯值和文本將被忽略。CHOOSE(index_num, value1, value2, .)使用index_num返回數(shù)值參數(shù)列表中的數(shù)值。使用 CHOOSE 可以根據(jù)索引號從最多 254 個數(shù)值中選擇一個。

13、例如，如果 value1 到 value7 表示一周的 7 天，那么將 1 到 7 之間的數(shù)字用作index_num時，CHOOSE 將返回其中的某一天。index_num    必需。用于指定所選定的數(shù)值參數(shù)。index_num必須是介于 1 到 254 之間的數(shù)字，或是包含 1 到 254 之間的數(shù)字的公式或單元格引用。value1, value2, .    Value1 是必需的，后續(xù)值是可選的。 1 到 254 個數(shù)值參數(shù)，CHOOSE 將根據(jù)index_num從中選擇一個數(shù)值或一項要執(zhí)行的操作。參數(shù)可以

14、是數(shù)字、單元格引用、定義的名稱、公式、函數(shù)或文本。如：=CHOOSE(2,A2,A3,A4,A5) 是第二個列表參數(shù)的值（單元格 A3 中的值）AVERAGE(number1, number2, .) 公式 =AVERAGE(A1:A20) 將返回這些數(shù)字的平均值。再例如：AVERAGE(A1，A5，B4)將單元格A1、A5、B4之和的平均數(shù)返回給應用的單元格。  AVERAGEIF(range, criteria, average_range)range 必需。要計算平均值的一個或多個單元格，其中包括數(shù)字或包含數(shù)字的名稱、

15、數(shù)組或引用。 criteria 必需。數(shù)字、表達式、單元格引用或文本形式的條件，用于定義要對哪些單元格計算平均值。例如，條件可以表示為 32、"32"、">32"、"蘋果" 或 B4。average_range可選。要計算平均值的實際單元格集。如果忽略，則使用 range。IF(logical_test, value_if_true, value_if_false) Logical_test表示計算結果為 TRUE 或 FALSE 的任意值或表達式。Value_if_truelogical_test

16、為 TRUE 時返回的值。Value_if_falselogical_test為 FALSE 時返回的值。如果指定條件的計算結果為 TRUE，IF 函數(shù)將返回某個值；如果該條件的計算結果為 FALSE，則返回另一個值。例如，如果 A1 大于 10，公式 =IF(A1>10,"大于 10","不大于 10") 將返回“大于 10”，如果 A1 小于等于 10，則返回“不大于 10”。 LEFT

17、(text,num_chars) Text 是包含要提取字符的文本字符串，可以直接輸入含有目標文字的單元格名稱。  Num_chars 指定要由 LEFT 所提取的字符數(shù)。 Num_chars 必須大于或等于 0。  如果 num_chars 大于文本長度，則 LEFT 返回所有文本。  如果省略 num_chars，則假定其為 1。RIGHT(text,num_chars)  

18、0; RIGHTB(text,num_bytes)Text 是包含要提取字符的文本字符串，可以直接輸入含有目標文字的單元格名稱。  Num_chars 指定希望 RIGHT 提取的字符數(shù)。    注意：Num_chars 必須大于或等于 0。    如果 num_chars 大于文本長度，則 RIGHT 返回所有文本。  如果忽略 num_chars，則假定

19、其為 1INT(number)Number 需要進行向下舍入取整的實數(shù)。MID(text, start_num, num_chars) Text是包含要提取字符的文本字符串，可以直接輸入含有目標文字的單元格名稱。Start_num 是文本中要提取的第一個字符的位置。Num_chars 指定希望 MID 從文本中返回字符的個數(shù)。如果 start_num 大于文本長度，則 MID 返回空文本 ()。如果 start_num 小于文本長度，但 

20、;start_num 加上 num_chars 超過了文本的長度，則 MID 只返回至多直到文本末尾的字符。  如果start_num小于 1，則 MID 返回錯誤值 #VALUE!。如果 num_chars 是負數(shù)，則 MID 返回錯誤值 #VALUE!MIDB(text, start_num, num_bytes)  MID 和 MIDB 函數(shù)語法具有下列參數(shù) （參數(shù)：為操作、事件、方法、屬性

21、、函數(shù)或過程提供信息的值。）： Text  必需。包含要提取字符的文本字符串。  Start_num  必需。文本中要提取的第一個字符的位置。文本中第一個字符的 start_num 為 1，依此類推。  Num_chars  必需。指定希望 MID 從文本中返回字符的個數(shù)。  Num_bytes  必需。指定希望 MIDB 從文本中返回字符的個數(shù)（字節(jié)數(shù)）。 INDE

22、X(array, row_num, column_num) 返回表格或數(shù)組中的元素值，此元素由行號和列號的索引值給定。 MATCH(lookup_value, lookup_array, match_type)  返回行號?？稍趩卧駞^(qū)域 （區(qū)域：工作表上的兩個或多個單元格。區(qū)域中的單元格可以相鄰或不相鄰）中搜索指定項，然后返回該項在單元格區(qū)域中的相對位置。例如，如果單元格區(qū)域 A1:A3 包含值 5、25 和 38，則以下公式：=MATCH(25,A1:

23、A3,0)會返回數(shù)字 2，因為值 25 是單元格區(qū)域中的第二項。 VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)  是用于在一組數(shù)組里查找某一個數(shù)據(jù)。然后返回所對應需要返回的參數(shù)。1、Lookup_value是需要查找的參數(shù).2、table_array是需要查找參數(shù)的查找區(qū)域。3、col_index_num是lookup_value在table_arry查找到過后返回的對應的參數(shù)列。4、  range_lo

24、okup是模糊匹配TRUE（或不填或填1則模糊匹配） /FALSE或填0則精確匹配。VLOOKUP函數(shù)的第三個參數(shù)是查找返回值所在的列數(shù)，如果我們需要查找返回多列時，這個列數(shù)值需要一個個的更改，比如返回第2列的，參數(shù)設置為2，如果需要返回第3列的，就需要把值改為3。如果有十幾列會很麻煩的。那么能不能讓第3個參數(shù)自動變呢？向后復制時自動變?yōu)?，3，4，5。       在EXCEL中有一個函數(shù)COLUMN，它可以返回指定單元格的列數(shù)，比如       

25、60; =COLUMNS（A1）返回值1         =COLUMNS（B1）返回值2   而單元格引用復制時會自動發(fā)生變化，即A1隨公式向右復制時會變成B1，C1，D1。這樣我們用COLUMN函數(shù)就可以轉換成數(shù)字1，2，3，4。 HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)LOOKUP函數(shù)是Excel等電子表格中的橫向查找函數(shù)，它與LOOKUP函數(shù)和VLOOKUP函數(shù)屬于一類函數(shù)，HLOOKUP

26、是按行查找的，VLOOKUP是按列查找的。POWER(number, power) 返回數(shù)字乘冪的結果。 POWER 函數(shù)語法具有下列參數(shù)： Number  必需。底數(shù)，可以為任意實數(shù)。  Power  必需。指數(shù)，底數(shù)按該指數(shù)次冪乘方。 可以使用“”代替 POWER，以表示基數(shù)乘冪運算的冪，例如 52。RANK(number,ref,order)  是返回一列數(shù)字的數(shù)字排位。數(shù)字的排位是其相對于列表中其他值的大小。（如果要對列表進行排序，則數(shù)字排位可作為

27、其位置。 RANK 函數(shù)語法具有下列參數(shù) （參數(shù)：為操作、事件、方法、屬性、函數(shù)或過程提供信息的值。）： Number   必需。需要找到排位的數(shù)字。  Ref   必需。數(shù)字列表數(shù)組或對數(shù)字列表的引用。Ref 中的非數(shù)值型值將被忽略。  Order   可選。一數(shù)字，指明數(shù)字排位的方式。  如果 order 為 0（零）或省略，Microsoft Exce

28、l 對數(shù)字的排位是基于 ref 為按照降序排列的列表。 如果 order 不為零，Microsoft Excel 對數(shù)字的排位是基于 ref 為按照升序排列的列表。 RANK.EQ(number,ref,order)是回一列數(shù)字的數(shù)字排位。其大小與列表中其他值相關；如果多個值具有相同的排位，則返回該組值的最高排位。如果要對列表進行排序，則數(shù)字排位可作為其位置。Number    必需。要找到其排位的數(shù)字。Ref   

29、 必需。數(shù)字列表的數(shù)組，對數(shù)字列表的引用。 Ref 中的非數(shù)字值會被忽略。如果 Order 為 0（零）或省略，Excel 對數(shù)字的排位是基于 Ref 為按降序排列的列表。如果 Order 不為零， Excel 對數(shù)字的排位是基于 Ref 為按照升序排列的列表。RANK.EQ 賦予重復數(shù)相同的排位。但重復數(shù)的存在將影響后續(xù)數(shù)值的排位。例如，在按升序排序的整數(shù)列表中，如果數(shù)字 10 出現(xiàn)兩次，且其排位為 5，則 11 的排位為 7（沒有排位為 6 的數(shù)值）。RANK.AVG(number,ref,order)是返回一列數(shù)字的數(shù)字排位：數(shù)字的排位是其大小與列表中其他值的比值；如果多個值具

30、有相同的排位，則將返回平均排位。Number    必需。要找到其排位的數(shù)字。Ref    必需。數(shù)字列表的數(shù)組，對數(shù)字列表的引用。 Ref 中的非數(shù)字值會被忽略。Order    可選。一個指定數(shù)字排位方式的數(shù)字。SQRT(number)  number 指需要計算平方根的數(shù) MOD(number, divisor) MOD 函數(shù)語法具有下列參數(shù)： Number  必需。被除

31、數(shù)。  Divisor  必需。除數(shù)。 MAX(number1, number2, .) max函數(shù)語法具有下列： Number1, number2, .  Number1 是必需的，后續(xù)數(shù)值是可選的。這些是要從中找出最大值的返回給引用的單元格。 MIN(number1, number2, .) MIN 函數(shù)語法具有下列： Number1, number2, . &#

32、160;Number1 是必需的，后續(xù)數(shù)值是可選的。這些是要從中查找最小值的值的返回給引用的單元格 COUNT(A1:A20) COUNT 函數(shù)計算包含數(shù)字的單元格以及參數(shù)列表中數(shù)字的個數(shù)。使用函數(shù) COUNT 可以獲取區(qū)域或數(shù)字數(shù)組中數(shù)字字段的輸入項的個數(shù)。例如，輸入以下公式可以計算區(qū)域 A1:A20 中數(shù)字的個數(shù)： COUNTA(value1, value2, .) COUNTA 函數(shù)計算表中不為空的單元格的個數(shù)。COUNTIF(range, crit

33、eria)  例如： =COUNTIF(B2:B25,"Nancy") COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2) COUNTIFS 函數(shù)語法具有以下參數(shù)： criteria_range1  必需。在其中計算關聯(lián)條件的第一個區(qū)域。  criteria1  必需。條件的形式為數(shù)字、表達式、單元格引用或文本，可用來定義將對哪些單元格進行

34、計數(shù)。例如，條件可以表示為 32、">32"、B4、"蘋果" 或 "32"。  criteria_range2, criteria2, .  可選。附加的區(qū)域及其關聯(lián)條件。最多允許 127 個區(qū)域/條件對。 要點  每一個附加的區(qū)域都必須與參數(shù) criteria_range1 具有相同的行數(shù)和列數(shù)。這些區(qū)域無需彼此相鄰。 OFFSET(reference,row

35、s,cols,height,width)Offset函數(shù)主要應用在單元格區(qū)域的定位和統(tǒng)計方面，一般做數(shù)據(jù)透視表定義名稱都需要用到Offset函數(shù)。Offset函數(shù)屬于查找與引用類的函數(shù)。OFFSET函數(shù)以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以為一個單元格或單元格區(qū)域，并可以指定返回的行數(shù)或列數(shù)。OFFSET函數(shù)的語法是：OFFSET(reference,rows,cols,height,width)，按照中文的說法即是：OFFSET(引用區(qū)域,行數(shù),列數(shù),高度,寬度)Reference 作為偏移量參照系的引用區(qū)域。Reference 必須為對單元格或相連單元格區(qū)域的引用

36、；否則，函數(shù) OFFSET 返回錯誤值 #VALUE!。Rows 相對于偏移量參照系的左上角單元格，上（下）偏移的行數(shù)。如果使用 5 作為參數(shù) Rows，則說明目標引用區(qū)域的左上角單元格比 reference 低 5 行。行數(shù)可為正數(shù)（代表在起始引用的下方）或負數(shù)（代表在起始引用的上方）。Cols 相對于偏移量參照系的左上角單元格，左（右）偏移的列數(shù)。如果使用 5 作為參數(shù) Cols，則說明目標引用區(qū)域的左上角的單元格比 reference 靠右 5 列。列數(shù)可為正數(shù)（代表在起始引用的右邊）或負數(shù)（代表在起始引用的左邊）。Height 高度，即所要返回的引用區(qū)域的行數(shù)。Height

37、必須為正數(shù)。Width 寬度，即所要返回的引用區(qū)域的列數(shù)。Width 必須為正數(shù)。COLUMN(reference)reference為需要得到其列標的單元格或單元格區(qū)域。含義是返回一引用的列號。ROW(reference)reference為需要得到其行號的單元格或單元格區(qū)域。含義是返回一個引用的行號。TEXT(value,format_text)使用函數(shù) TEXT 可以將數(shù)值轉換為帶格式的文本，而其結果將不再作為數(shù)字參與計算。Value 為數(shù)值、計算結果為數(shù)字值的公式，或對包含數(shù)字值的單元格的引用。Format_text為"單元格格式"對話框中"數(shù)字"

38、;選項卡上"分類"框中的文本形式的數(shù)字格式。Format_text不能包含星號 (*)。通過"格式"菜單調用"單元格"命令，然后在"數(shù)字"選項卡上設置單元格的格式，只會更改單元格的格式而不會影響其中的數(shù)值。Format_text參數(shù)代碼(常用)格式代碼數(shù)字顯示的值TEXT(A,B)說明G/通用格式1010常規(guī)格式“000.0”10.25“010.3”小數(shù)點前面不夠三位以0補齊，保留1位小數(shù)，不足一位以0補齊#10.0010沒用的0一律不顯示00.#1.25301.25小數(shù)點前不足兩位以0補齊，保留兩位，不足兩位不補位正數(shù);負數(shù);零1正數(shù)大于0，顯示為“正數(shù)”正數(shù);負數(shù);零0零等于0，顯示為“零”正數(shù);負數(shù);零-1負數(shù)小于0，顯示為“負數(shù)”0000-00-00198205061982-05-06按所示形式表示日期0000年00月00日198205061982年05月06日dddd2007-12-31Monday顯示為英文星期幾全稱>=90優(yōu)秀;>=60及格