高效處理數據的Excel函數家族

例：找出單元格A2中字符串“我Love數據Analysis”中的“數據”、大寫“L”、小寫“l”以及“*數據*”的位置。公式分別為：“=SEARCH("數據",A2,1)”、“=SEARCH("L",A2,1)”、“=SEARCH("l”,A2,1)”、“=SEARCH("*數據*",A2,1)”。

提示：

SEARCH函數不區分大小寫，從第一個字符開始查找大寫“L”的位置是2，從第一個字符開始查找小寫“l”的位置也是2。

2.8 SUBSTITUTE函數

功能說明：用于在某一個文本字符串中替換指定的文本，把old_text替換成new_text。

語法：SUBSTITUTE(text,old_text,new_text,[instance_num])

參數：

1.text 必需。要替換其中字符的文本。

2.old_text 必需。要替換的文本。

3.new_text 必需。替換old_text的文本。

4.instance_num 可選。指定要用new_text替換old_text的事件。如果指定了instance_num，只有滿足要求的old_text被替換。如果省略，文本中所有的old_text都會被替換為new_text。

例：分別將單元格A2中字符串“#我愛數據#數據愛我#”中的第一個#替換為@、第二個#替換為@、所有的#替換為@、前兩個#替換為@。公式分別為：“=SUBSTITUTE(A2,"#","@",1)”、“=SUBSTITUTE(A2,"#","@",2)”、“SUBSTITUTE(A2,"#","@")”、“SUBSTITUTE(SUSTITUTE(A2,"#","@"1),"#","@",1)”。

提示：

1.SUNSTITUTE函數里面的參數instance_num可以省略，如果省略表示替換所有的old_text，如果instance_num=2，表示替換第二次出現的字符串。

2.如需分別替換字符串為其他不同字符串，可以使用SUBSTITUTE函數進行嵌套替換，每次替換第一次出現的字符串即可。

2.9 REPLACE函數

功能說明：根據指定字符數，REPLACE將部分文本字符串替換成不同的文本字符串。

語法：ERPLACE(old_text,start_num,num_chars,new_text)

參數：

1.old_text 必需。要替換其中字符的文本。

2.start_num 必需。old_text中要替換為new_text的字符的起始位置。

3.num_chars 必需。使用new_text來進行替換的字符數。

4.new_text 必需。要替換old_text中字符的文本。

例1：將單元格A2內字符串“#我愛數據#數據愛我#”中的“我愛數據”替換為“Data"。公式為：“=REPLACE(A2,2,4,"Data")”。

例2：將單元格A3內手機號碼“13013013000”中間的五位數字替換為“*****”。公式為：“=REPLACE(A3,4,4,"*****")”。

提示：

1.REPLACE函數與SUBSTITUTE函數的區別：REPLACE函數是指定起始位置和字符長度進行替換的；而SUBSTITUTE函數是將給定的原始字符串替換成新的字符串。

2.REPLACE函數與MID函數由相似之處：MID函數是根據起始位置和字符長度進行截取；而REPLACE函數除了截取之外，還要將截取的字符串替換掉。

2.10 CONCATENATE函數

功能說明：將兩個或多個字符串連接成一個字符串。

語法：CONCATENATE(text1,[text2],...]

例：將單元格A2中的“我”、單元格A3中的“愛”、單元格A4中的“數據分析”這三個字符串合并成一個字符串。公式為：“=CONCATENATE(A2,A3,A3)”或“=A2&A3&A4”。

2.11 EXACT函數

功能說明：比較兩個文本字符串，如果他們完全相同，則返回TRUE，否則返回FALSE。

語法：EXACT(test1,test2)

例：分別比較單元格A2與A3、A2與A4的字符串是否相同。公式分別為：“=EXACT(A2,A3)”、“=EXACT(A2,A4)”。

提示：

1.EXACT函數區分大小寫。

2.符號“=”不能區分大小寫，例如，公式“=("A"="a")”，結果返回TRUE，不是FALSE。

2.12 TRIM函數

功能說明：除了單詞之間的單個空格之外，移除文本中的所有空格。

語法：TRIM(text)

例：用TRIM去除單元格A2中字符串“ Data Analysis ”中的空格。公式為：“=TRIM(A2)”。

三、便捷的數值運算函數

3.1 RAND函數

功能說明：返回一個大于等于0且小于1的、平均分布的隨機實數，每次計算工作表時都會返回一個新的隨機實數。

語法：RAND()

例：在單元格B2中隨機生成一組用戶的性別。公式為：“=IF(RAND()>0.5,"男","女")”。

3.2 RANDBETWEEN函數

功能說明：返回位于兩個指定數之間的一個隨機整數。每次計算工作表時都將返回一個新的隨機整數。

語法：RANDBETWEEN(bottom,top)

參數：

1.bottom 必需。RANDBETWEEN函數返回的最小整數。

2.top 必需。RANDBETWEEN函數返回的最大整數。

例：隨機生成一組學生的年齡（20~25）、語文成績（0~100分）以及手機號碼（以133開頭）。公式分別為：“=RANDBETWEEN(20,25)”、“=RANDBETWEEN(0,100)”、“="133"&RANDBETWEEN(10000000,99999999)”。

提示：

RANDBETWEEN可以取到bottom和top的數值。

2.3 ABS函數

功能說明：返回數字的絕對值。

語法：ABS(number)

例：取單元格A2中數值-2的絕對值。公式為：“=ABS(A2)”。

2.4 MOD函數

功能說明：返回兩數相除的余數。返回結果的符號與除數相同。

語法：MOD(number,divisor)

參數：

1.number 必需。要計算余數的被除數。

2.divisor 必需。除數。

例：區域A2:A5是被除數，區域B2:B5是對應的除數，計算每行數據的余數。公式為：“=MOD(A2,B2)”。

2.5 POWER函數

功能說明：返回數字乘冪的結果。

語法：POWER(number,power)

例：區域A2:A4是基數，區域B2:B4是對應的指數，計算每行數據的乘冪。公式為：“=POWER(A2,B2)"

2.6 PRODUCT函數

功能說明：將參數形式給出的數字相乘并返回乘積。

語法：PRODUCT(number1,[number2],...)

例：計算區域A2:A5的數字乘積，公式為：”PRODUCT(A2:A5)；計算區域A2:A5的數字乘積再乘以3，公式為：“=PRODUCT(A2:A5,3)”。

提示：

可以使用字符“*”代替PRODUCT函數，表示數字之間的乘法運算。例如，數字1、2、4、8的乘積公式可以寫成“=1*2*4*8。

2.7 CEILING函數

功能說明：返回將參數number向上舍入（沿絕對值增大的方向）為最接近的指數基數的倍數。

語法：CEILING(number,significance)

參數：
1.number 必需。要舍入的值。

2.significance 必需。要舍入的倍數。

例：區域A2:A6是要舍入的值，區域B2:B6是基數，用CEILING函數進行向上舍入為最接近的指數基數的倍數。公式為：”=CEILING(A2,B2)”。

提示：

1.如果number正好是significance的倍數，則不進行舍入。

2.如果number和significance都為負，則按遠離0的方向進行向下舍入。

3.如果number為負，signifance為正，則按朝向0的方向進行向上舍入。

4.如果number為正，signifance為負，則結果返回錯誤值#NUM！。

3.8 FLOOR函數

功能說明：將參數number向下舍入（沿絕對值減小的方向）為最接近的指定基數的倍數。

語法：FLOOR(number,signifance)

例：區域A2:A6是要舍入的值，區域B2:B6是基數，用FLOOR函數進行向下舍入為最接近的指定基數的倍數。公式為：“=FLOOR(A2,B2)”。

提示：

1.如果number正好是significance的倍數，則不進行舍入。

2.如果number和significance都為負，則按遠離0的方向進行向上舍入。

3.如果number為負，signifance為正，則按朝向0的方向進行向下舍入。

4.如果number為正，signifance為負，則結果返回錯誤值#NUM！。

3.9 ROUND函數

功能說明：ROUND函數將數字四舍五入到指定的位數。

語法：ROUND(number,num_digits)

參數：

1.number 必需。要四舍五入的數字。

2.num_digits 必需。要進行四舍五入運算的位數。

提示：

1.如果num_digits大于0，則將數字四舍五入到指定的小數位數。

2.如果num_digits等于0，則將數字四舍五入到最接近的整數。

3.如果num_digits小于0，則將數字四舍五入到小數點左邊的相應位數。

3.10 ROUNDUP函數

功能說明：朝著遠離數值0的方向將數字進行向上舍入。

語法：ROUNDUP(number,num_digits)

3.11 ROUNDDOWN函數

功能說明：朝著數值0的方向將數字進行向下舍入。

語法：ROUNDDOWN(number,num_digits)

3.12 TRUNC函數

功能說明：將數字進行截取返回整數。

語法：TRUNC(number,[num_digits])

參數：

1.number 必需。需要截尾取整的數字。

2.num_digits 可選。用于指定取整精度的數字，默認值為0。

提示：

TRUNC與INT在對數值的整數部分進行截取時有些相似，TRUNC是直接刪除數字的小數部分，而INT根據數字小數部分的值將數字向下舍入為最接近的整數，只有當處理負數的時候，INT和TRUNC會有區別。例如，TRUNC(-3.14)返回-3，而INT(-3.14)返回-4。

四、經典的邏輯判斷函數

4.1 AND函數

功能說明：檢查是否所有的參數均為TRUE，如果所有的參數值均為TRUE，則返回TRUE。

語法：AND(logical1,[logical2],...)

例：“=AND(1>2,2>1)”返回FALSE；“=AND(2>1,1)”返回TRUE。

提示：

1.數值0作為參數的邏輯值被當成FALSE使用。

2.非0數值作為參數返回邏輯值被當成TRUE使用。

3.任意一個參數的邏輯值出現FALSE（或者數值0）的時候，結果返回FALSE。

4.所有的參數的邏輯值都是TRUE的時候，結果返回TRUE。

4.2 OR函數

功能說明：如果任意參數為TRUE，即返回TRUE；只有當所有的參數值均為FALSE時才返回FALSE。

語法：OR(logical1,[logical2],...)

提示：

1.任意一個參數的邏輯值出現TRUE（或者非0數值）的時候，結果返回TRUE。

2.所有的參數的邏輯值均為FALSE的時候，結果返回FALSE。

4.3 NOT函數

功能說明：對參數的邏輯值求反：參數為TRUE時返回FALSE，參數為FALSE時返回TRUE。

語法：NOT(logical)

例：“=NOT(1>2)”返回TRUE。

4.4 IF函數

功能說明：判斷是否滿足某個條件，如果滿足返回一個值，如果不滿足則返回另外一個值。

語法：IF(logical_test,[value_if_true],[value_if_false])

參數：

1.logical_test 必須。可以為數值或邏輯表達式。

2.value_if_true 可選。當logical_test為TRUE時返回的結果。

3.value_if_false 可選。當logical_test為FALSE時返回的結果。

4.5 IFERROR函數

功能說明：如果表達式是一個錯誤，則返回value_if_error，否則返回表達式自身的值。

語法：IFERROR(value,value_if_error)

4.6 ISERROR函數

功能說明：檢查一個值是否為錯誤（#N/A、#VALUE!、#REF!、#DIV0!、#NUM!、#NAME?、#NULL!），結果返回TRUE或FALSE。

語法：ISERROR(value)

4.7 ISTEXT函數

功能說明：檢查一個值是否是文本，返回TRUE或FALSE。

語法：ISTEXT(value)

4.8 ISNUMBER函數

功能說明：檢查一個數是否為數值，返回TRUE或FALSE。

語法：ISNUMBER(value)

五、實用的日期計算函數

5.1 TODAY函數

功能說明：返回當前日期的序列號（序列號是Eecel用于日期和時間計算的日期-時間代碼）。如果在輸入函數之前單元格格式為“常規”，Excel會將單元格格式更改為“日期”、若要顯示序列號，必須將單元格格式更改為“常規”或“數字”。在默認情況下，1900年1月1日的序列號為1，2018年1月日的序列號為43101，因為它距1900年1月1日有43100天。

語法：TODAY()

例：“=TODAY()”返回當前日期，如“2018-10-23”。

5.2 NOW函數

功能說明：返回當前日期和時間的序列號。

語法：NOW()

例：“=NOW()”返回當前的日期和時間，如“2018-10-23 23:20:29”。

5.3 YEAR、MONTH、DAY函數

功能說明：YEAR返回對應于某個日期的年份，YEAR作為1999~9999的整數返回。MONTH返回日期（以序列數表示）中的月份，月份是1~12的整數。DAY返回以序列表示的某日期的天數，天數是介于1~31的整數。

語法：YEAR(serial_number)、MONTH(serial_number)、DAY(serial_number)

例：“=YEAR("2018-05-01)”返回“2018”。

5.4 HOUR、MINUTE、SECOND函數

功能說明：HOUR返回時間值的小時值，小時是介于0到23的整數；MINUTE返回時間值的分鐘數，分鐘是一個介于0到59的整數；SECOND返回時間值的秒數，秒數是0到59的整數。

語法：HOUR(serial_number)、MINUTE(serial_number)、SECOND(serial_number)

例：“=HOUR(12:15:30)”返回“12”。

5.5 DATE函數

功能說明：返回表示特定日期的連續序列號。

語法：DATE(year,month,day)

例：“=DATE(2018,5,10)”返回“2018/5/10”。

5.6 TIME函數

功能說明：返回特定時間的十進制數字。

語法：TIME(hour,minute,second)

例：“=TIME(10,20,45)”返回“10:20:45”；“=TIME(12,0,0)”返回“0.5”。

5.7 DATEDIF函數

功能說明：計算兩個日期之間間隔的年數、月數或天數。

語法：DATEDIF(start_date,end_date,unit)

參數：
1.start_date 必需。某個時間段的起始日期。

2.end_date 必需。某個時間段的結束日期。

3.unit 必需。要返回的計算類型。參數類型有"Y" "M" "D" "MD" "YM" "YD"。

提示：
1.參數unit為“MD”表示start_date與end_date之間的天數之差。忽略日期中的月份和年份。

2.參數unit為“YM”表示start_date與end_date之間的月份之差。忽略日期中的天和年份。

3.參數unit為“YD”表示start_date與end_date的日期部分之差。忽略日期中的年份。

六、高效的匹配查找函數

6.1 CHOOSE函數

功能說明：根據索引號index_num返回數值參數列表中的數值。

語法：CHOOSE(index_num,value1,[value2],...)

參數：
1.index_num 必需。用于指定所選定的數值參數。index_num必須是介于1~254的數字，或是包含1到254的數字的公式或單元格引用。

2.value1,[value2],...，value1必需，后續值可選。1到254個數值參數，CHOOSE將根據index_num從中選擇一個數值或一項要執行的操作。參數可以是數字、單元格引用、定義的名稱、公式函數或文本。

例：取A列里面A3單元格的值，公式為：“=CHOOSE(2,A2,A3,A4,A5)；取B列里面B4單元格的值，公式為：“=CHOOSE(3,B2,B3,B4,B5)；計算區域B2:B5的值之和，公式為：“=SUM(CHOOSE(2,A2:A5,B2;B5))。

6.2 VLOOKUP函數

功能說明：將查找值在某個區域中的第一列進行查找，根據列號返回右側第col_index_num列與查找值處于同行的數值。

語法：VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

參數：

1.lookup_value 必需。要查找的值。

2.table_array 必需。要在其中查找值的區域。

3.col_index_num 必需。區域中包含返回值的列號。

4.range_lookup 可選。精確匹配或近似匹配，精確匹配用0或FALSE指代，近似匹配用1或TRUE指代。參數省略時默認為近似匹配。

提示：
1.lookup_value可以為模糊值，例如，查找姓李的同學的成績，姓名使用“李*”。

2.table_array的第一列必須是lookup_value查找范圍的所在列，范圍選擇方向從左往右，

3.VLOOKUP函數的column_index_num必須是大于0 的整數。

4.如需精確匹配，最后一個參數設置為0或FALSE。

5.當查找的數據在查找范圍內有重復的時候，返回查找范圍內第一列首次出現的查找值所對應的數值。

6.3 HLOOKUP函數

功能說明：將查找值在某個區域中的第一行進行查找，根據行號返回下方第row_index_num行與查找值處于同列的數值。

語法：HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])

6.4 LOOKUP函數

功能說明：將查找值在一行或一列進行查找，返回一行或列中的相同位置的數值。LOOKUP函數可以精確匹配和近似匹配。

語法：LOOKUP(lookup_value,array);LOOKUP(lookup_value,lookup_vector,[result_vector])

6.5 MATCH函數

功能說明：在區域內搜索特定的項，然后返回該項在此區域中的相對位置。

語法：MATCH(lookup_value,lookup_array,[match_type])

參數：

1.lookup_value 必需。要在lookup_array中匹配的值。

2.lookup_array 必需。要搜索的單元格區域。

3.match_type 可選。數字-1、0或1。參數默認值為1。精確匹配必須為0，近似匹配則為-1或1。

6.6 INDEX函數

功能說明：返回表格或區域中的值或值的引用。

語法：INDEX(array,row_num,[column_num])

參數：
1.array 必需。單元格區域或數組常量。

2.row_num 必需。選擇數組中的某行，函數從該行返回數值。

3.column_num 可選。選擇數組中的某列，函數從該列返回數組。

提示：
1.如果array參數僅包含一行或一列，參數row_num或column_num為可選參數。

2.如果array參數中包含多行多列，而且僅使用了row_num或column_num一個參數，函數結果返回數組中的正行或整列。

3.如果array參數中包含多行多列，而且同時使用了row_num和column_num參數，函數結果返回某一行和某一列的交叉單元格中的值。

6.7 OFFSET函數

功能說明：返回對單元格區域中指定行數或列數的區域的引用。返回的引用可以是單個單元格會單元格區域。

語法：OFFSET(reference,rows,cols,[height],[width])

參數：
1.reference 必需。作為偏移基準的參照。

2.rows 必需。需要左上角單元格引用的向上會哦向下行數。

3.cols 可選。需要結果的左上角單元格引用的從左到右的列數。

4.height 可選。需要返回的引用的行高。

5.width 可選。需要返回的引用的列寬。

6.8 INDIRECT函數

功能說明：返回由文本字符串指定的引用。此函數立即對引用進行計算，并顯示其內容。

語法：INDIRECT(ref_text,[a1])

參數：
1.ref_text 必需。對單元格的引用，此單元格包含A1樣式的引用、R1C1樣式的引用、定義為引用的名稱或對作為文本字符串的單元格的引用。

2.[a1] 可選。邏輯值，用于指定在ref_text中的引用的類型。參數值為TRUE指定的是A1樣式，參數值為FALSE指定R1C1引用樣式。

本站是提供個人知識管理的網絡存儲空間，所有內容均由用戶發布，不代表本站觀點。請注意甄別內容中的聯系方式、誘導購買等信息，謹防詐騙。如發現有害或侵權內容，請點擊一鍵舉報。

轉藏分享

QQ空間 QQ好友新浪微博微信

獻花（0） +1

來自： wangyong670 > 《Excel資料》

舉報/認領

0條評論

發表

請遵守用戶評論公約

類似文章 更多

wangyong670

關注對話

TA的最新館藏

Access
Access
Access
Access
Access
Access

喜歡該文的人也喜歡更多

熱門閱讀換一換