EXCEL之函數調用

（其實也算不上普及，共同學習而已）。

很多人都會有這樣一個概念，遇到問題再百度呗，我覺得那樣是被動的，不系統的，如果提前了解一下到時候再百度也會有大概一個方向。

excel較常用的是文本函數，邏輯函數，日期與時間函數，查找與引用函數，數學函數等，很多人一看到這些就頭大，感覺太多了，沒有頭緒？那麼哪些函數是最常用的呢？

下面我打破這個順序，按照類型講一下。為節省字數，我盡量多用圖，并且把同一類型的對比着來講，不會很散：

相對引用于絕對引用：

相對引用：單元格或單元格區域的相對引用是指相對于包含公式的單元格的相對位置。例如，單元格 B2 包含公式 ＝A1 ;Excel 将在距單元格 B2 上面一個單元格和左面一個單元格處的單元格中查找數值。

絕對引用：1 乘以單元格 A2 (=A1*A2)放到A4中，現在将公式複制到另一單元格中，則 Excel 将調整公式中的兩個引用。如果不希望這種引用發生改變,須在引用的"行号"和"列号"前加上美元符号($)，這樣就是單元格的絕對引用。A4中輸入公式如下:

=$A$1*$A$2 複制A4中的公式到任何一個單元格其值都不會改變

數組：關于這一部分，由于本篇所講都是基本，不涉及到嵌套，具體用法可以去Excel 一起來認識數組公式看一下。

基本函數：

1.邏輯值，and，or，not

1.邏輯值：true，false

能産生或傳回邏輯值的情況：

 比較運算符

 is類資訊函數

 and,or,not

2.與(and),或(or),非(not)

and:所有條件為true，則傳回true，，否則傳回false

or：其中之一條件為true，則傳回true，否則傳回false

not:如果條件參數結果為true，則傳回false，同理結果為false，則傳回true

true ：正确-成立-是

false ：錯誤-不成立-否

true 相當于1

false 相當于0

AND、OR 與 *、+

現象推定：

=AND(TRUE,TRUE,TRUE,TRUE,TRUE,FALSE)=AND(1,1,1,1,1,0)

=1*1*1*1*1*0

=OR(FALSE,FALSE,FALSE,FALSE,FALSE,TRUE) =OR(0,0,0,0,0,1)

=0+0+0+0+0+1

總結規律：

AND可以用*來代替

OR可以用+來代替

這個類似于高中數學的邏輯或與非。

2.IF函數

=IF(條件，True，False)

If函數的簡寫模式：

結論:

如果參數未寫,用逗号隔開則看做0

如果第三個參數未寫,當傳回結果時看做"FALSE"

3.IS類判斷函數

正确則傳回為true，錯誤傳回fause

4.Min，Max函數

MIN(number1,number2,...)Number1, number2, ... 是要從中找出最大值的 1 到 30 個數字參數。

傳回一組值中的最小值。

說明

可以将參數指定為數字、空白單元格、邏輯值或數字的文本表達式。如果參數為錯誤值或不能轉換成數字的文本，将産生錯誤。

如果參數是數組或引用，則函數 MIN 僅使用其中的數字，空白單元格，邏輯值、文本或錯誤值将被忽略。如果邏輯值和文本字元串不能忽略，請使用 MINA 函數。

如果參數中不含數字，則函數 MIN 傳回 0。

5.SUM函數

SUM傳回某一單元格區域中所有數字之和。

文法:SUM(number1,number2, ...)Number1, number2, ... 為 1 到 30 個需要求和的參數。

直接鍵入到參數表中的數字、邏輯值及數字的文本表達式将被計算

如果參數為數組或引用，隻有其中的數字将被計算。數組或引用中的空白單元格、邏輯值、文本或錯誤值将被忽略。

如果參數為錯誤值或為不能轉換成數字的文本，将會導緻錯誤。

6. SUMPRODUCT函數

在給定的幾組數組中，将數組間對應的元素相乘，并傳回乘積之和。

SUMPRODUCT(array1,array2,array3, ...)其相應元素需要進行相乘并求和。

數組參數必須具有相同的維數，否則，函數 SUMPRODUCT 将傳回錯誤值 #VALUE!。

函數 SUMPRODUCT 将非數值型的數組元素作為 0 處理。

7.Sumif函數

文法：

SUMIF ( range , criteria , sum_range )

range：為用于條件判斷的單元格區域

criteria：為确定哪些單元格将被相加求和的條件，其形式可以為數字、表達式或文本

sum_range:求和的實際單元格,如果忽略了則對區域中的單元格求和

本例來舉個例子：

備注：花生為A15，160為B23

8.COUNT、COUNTA、COUNTBLANK函數

9.Countif函數

COUNTIF(range,criteria)

range：可以使用引用函數，criteria：可以使用通配符，數組

Range 為需要計算其中滿足條件的單元格數目的單元格區域。

Criteria 為确定哪些單元格将被計算在内的條件，其形式可以為數字、表達式或文本。例如，條件可以表示為 32、"32"、">32" 或 "apples"。

日期函數篇

10.常用日期函數

傳回某個月份最後一天的序列号，該月份與 start_date 相隔(之後或之後)訓示的月份數。使用函數 EOMONTH 可以計算正好在特定月份中最後一天到期的到期日。

額外小知識

輸入目前系統日期：ctrl+;

輸入目前系統時間：ctrl+shift+;

11.DATEVALUE、EDATE、WEEKDAY日期函數

12.DATEIF函數

datedif年數、月數、日數傳回年數月數日數

=DATEDIF(起始日期，結束日期，傳回機關) 類似于

=DATEDIF($B16,TODAY(),"ym")

13. HOUR,MINUTE,SECOND,TIME函數

數學函數篇

14.Mod函數

MOD(number,divisor)

Number 為被除數。Divisor 為除數。

傳回兩數相除的餘數。

1.結果的正負号與除數相同。

2.餘數的絕對值必定小于除數絕對值

15.INT，TRUNC函數

INT(number)将數字向下舍入到最接近的整數。

TRUNC(number,num_digits)将數字的小數部分截去，傳回整數。

Number 需要截尾取整的數字。

Num_digits 用于指定取整精度的數字。Num_digits 的預設值為 0。

總結：TRUNC與INT的不同之處

1.TRUNC可以指定小數部分，INT不能

2.對負數的處理方式不同

16.ROUND系列函數

ROUND

ROUND(number,num_digits)傳回某個數字按指定位數取整後的數字。

Number 需要進行四舍五入的數字。

Num_digits 指定的位數，按此位數進行四舍五入。

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

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

如果 num_digits 小于 0，則在小數點左側進行四舍五入。

ROUNDUP

ROUNDUP(number,num_digits)遠離零值，向上舍入數字。

Number 為需要向上舍入的任意實數。

Num_digits 四舍五入後的數字的位數。

函數 ROUNDUP 和函數 ROUND 功能相似，不同之處在于函數 ROUNDUP 總是向上舍入數字。

如果 num_digits 大于 0，則向上舍入到指定的小數位。

如果 num_digits 等于 0，則向上舍入到最接近的整數。

如果 num_digits 小于 0，則在小數點左側向上進行舍入。

ROUNDDOWN

ROUNDDOWN(number,num_digits)靠近零值，向下(絕對值減小的方向)舍入數字。

Number 為需要向下舍入的任意實數。

函數 ROUNDDOWN 和函數 ROUND 功能相似，不同之處在于函數 ROUNDDOWN 總是向下舍入數字。

如果 num_digits 大于 0，則向下舍入到指定的小數位。

如果 num_digits 等于 0，則向下舍入到最接近的整數。

如果 num_digits 小于 0，則在小數點左側向下進行舍入。

總結：

我們發現rounddown與trunc取數方式完全一緻，通常會用trunc來代替rounddown函數

17.CEILING和FLOOR函數

FLOOR:向下舍入為最接近的指定基數的倍數=FLOOR(基數，倍數)

CEILING:向上舍入為最接近的指定基數的倍數=CEILING(基數，倍數)

FLOOR 類似 于ROUNDDOWN

CEILING類似ROUNDUP

18. RAND、RANDBETWEEN函數（生成随機數）

RAND( )

RAND括号中沒有參數

傳回大于等于 0 及小于 1 的随機數，每次計算工作表時都将傳回一個新的數值。

RANDBETWEEN

傳回位于兩個指定數之間的一個随機數。每次計算工作表時都将傳回一個新的數值。

如果該函數不可用，并傳回錯誤值 #NAME?，請安裝并加載“分析工具庫”加載宏。

操作方法

1. 在“工具”菜單上，單擊“加載宏”。

2. 在“可用加載宏”清單中，選中“分析工具庫”框，再單擊“确定”。

3. 如果必要，請遵循安裝程式中的訓示。

文法

RANDBETWEEN(bottom,top)

Bottom 函數 RANDBETWEEN 将傳回的最小整數。

Top 函數 RANDBETWEEN 将傳回的最大整數。

生成5到10之間的數

=RANDBETWEEN(5,10)

19. PRODUCT、POWER(脫字元^)函數

product(*)乘積=PRODUCT(4,5)相當于"*"

power(脫字元^)乘幂

POWER(number,power) 傳回給定數字的乘幂。

Number 底數，可以為任意實數。

Power 指數，底數按該指數次幂乘方。

可以用“^”運算符代替函數 POWER 來表示對底數乘方的幂次，例如 5^2。

文本函數

在講之前，先來普及一下字元與位元組

位元組：

位元組(Byte): 位元組是通過網絡傳輸資訊(或在硬碟或記憶體中存儲資訊)的機關。

位元組是計算機資訊技術用于計量存儲容量和傳輸容量的一種計量機關

1B=8b

字元:

字元是指計算機中使用的字母、數字、字和符号，隻是一個符号。

字元 人們使用的記号，抽象意義上的一個符号。 '1', '中', 'a', '$', '￥', ……

注意：

當啟用支援 DBCS 的語言的編輯并将其設定為預設語言時，有些文本類函數會将每個雙位元組字元按 2 計數，支援 DBCS 的語言包括日語、中文(簡體)、中文(繁體)以及北韓語。

20.LEFT RIGHT函數

= LEFT ( TEXT , Num_chars )

= RIGHT ( TEXT , Num_chars )

其中：

TEXT必需。包含要提取的字元的文本字元串。

Num_chars可選。指定要由 LEFT/RIGHT 提取的字元的數量.

1.如果省略 num_chars，則假設其值為 1。

2.Num_chars 必須大于或等于零。

3.如果 num_chars 大于文本長度，則 LEFT 傳回全部文本。

21 MID函數

= MID ( text , start_num , num_chars )

text必需。包含要提取字元的文本字元串。

start_num必需。文本中要提取的第一個字元的位置。

num_chars必需。指定希望 MID 從文本中傳回字元的個數。

=MIDB(text, start_num, num_bytes)

必需。指定希望 MIDB 從文本中傳回字元的個數(位元組數)

1.如果 start_num 大于文本長度，則 MID 傳回空文本 ("")。

2.如果 start_num 小于文本長度，但 start_num 加上 num_chars 超過了文本的長度，則 MID 隻傳回至多直到文本末尾的字元。

3.如果 start_num 小于 1，則 MID 傳回錯誤值 #VALUE!。

4.如果 num_chars 是負數，則 MID 傳回錯誤值 #VALUE!。

5.如果 num_bytes 是負數，則 MIDB 傳回錯誤值 #VALUE!。

22.LEN函數

=LEN(text)

=LENB(text)

text必需。要查找其長度的文本。空格将作為字元進行計數。

左邊是LEN，右邊是LENB

23.Find函數

FIND( find_text , within_text , [start_num] )

FINDB(find_text, within_text, [start_num])

三個參數的要求：

必需。要查找的文本。

必需。包含要查找文本的文本。

可選。指定要從其開始搜尋的字元。within_text 中的首字元是編号為 1 的字元。如果省略 start_num，則假設其值為 1。

24.SEARCH函數

SEARCH( find_text , within_text , [start_num] )

SEARCHB(find_text,within_text,[start_num])

三個參數說明：

必需。要在其中搜尋 find_text 參數的值的文本。

可選。within_text 參數中從之開始搜尋的字元編号。

25.REPLACE函數

=REPLACE( old_text , start_num , num_chars , new_text )

=REPLACEB( old_text , start_num , Num_bytes , new_text )

參數說明

必需。要替換其部分字元的文本。

必需。要用 new_text 替換的 old_text 中字元的

必需。new_text 替換 old_text 中字元(位元組)的個數。

必需。将用于替換 old_text 中字元的文本。

26.SUBSTITUDE函數

=SUBSTITUTE( text , old_text , new_text , [instance_num] )

參數說明：

必需。需要替換其中字元的文本，或對含有文本(需要替換其中字元)的單元格的引用。

必需。需要替換的舊文本。

必需。用于替換 old_text 的文本。

可選。用來指定要以 new_text 替換第幾次出現的 old_text。

如果指定了 instance_num，則隻有滿足要求的 old_text 被替換;否則會将 Text 中出現的每一處 old_text 都更改為 new_text。

REPLACE與SUBSTITUTE的差別：

1.如果需要在某一文本字元串中替換指定位置處的任意文本，請使用函數 REPLACE。

2.如果需要在某一文本字元串中替換指定的文本，請使用函數 SUBSTITUTE;

單文本替換還是建議用Ctrl+H查找替換

27.CHAR與CODE

= CHAR ( number )

必需。介于 1 到 255 之間用于指定所需字元的數字。

傳回對應于數字代碼的字元。函數 CHAR 可将其他類型計算機檔案中的代碼轉換為字元。

= CODE ( text )

必需。需要得到其第一個字元代碼的文本

傳回文本字元串中第一個字元的數字代碼。傳回的代碼對應于計算機目前使用的字元集。

此方法可快速輸入A,B,C序列。

28.UPPER\LOWER\EXACT

= UPPER ( text )

（text）必需。需要轉換成大寫形式的文本。Text 可以為引用或文本字元串。

= LOWER ( text )

（text）必需。要轉換為小寫字母的文本。函數 LOWER 不改變文本中的非字母的字元。

= EXACT ( text1 , text2 )

必需。第一個文本字元串。

必需。第二個文本字元串。

該函數用于比較兩個字元串：如果它們完全相同，則傳回 TRUE;否則，傳回 FALSE。函數 EXACT 區分大小寫，但忽略格式上的差異。利用 EXACT 函數可以測試在文檔内輸入的文本。

29.REPT函數

=REPT ( text , number_times )

必需。需要重複顯示的文本

必需。用于指定文本重複次數的正數。

1.如果 number_times 為 0，則 REPT 傳回 ""(空文本)。

2.如果 number_times 不是整數，則将被截尾取整。

3.REPT 函數的結果不能大于 32,767 個字元，否則，REPT 将傳回錯誤值 #VALUE!。

30.TRIM函數

= TRIM ( text )

必需。需要删除其中空格的文本。

要想全部去除，查找替換空格。

31.TEXT函數

= TEXT ( value , format_text )

必需。數值、計算結果為數值的公式，或對包含數值的單元格的引用。

必需。使用雙引号括起來作為文本字元串的數字格式。

格式可以如下：

上圖看不清點選 這裡

查找與引用函數

32.ROW 與COLUMN

= ROW ( [reference] ) 傳回單元格的行号

=COLUMN([reference])傳回單元格的列号

= ROWS ( array )計劃行數

33.VLOOKUP與HLOOKUP

vlookup 非常常用，且不難，留給你自己探索。

34.LOOKUP

稍微有些複雜：可參考下面兩圖：

35.CHOOSE函數

= CHOOSE ( index_num , value1 , value2,...)

Index_num 必須為 1 到 29 之間的數字、或者是包含數字 1 到 29 的公式或單元格引用

函數 CHOOSE 基于 index_num，從中選擇一個數值或執行相應的操作。參數可以為數字、單元格引用、已定義的名稱、公式、函數或文本。

36.MATCH函數

= MATCH ( lookup_value , lookup_array , match_type)

為需要在資料表中查找的數值。可以為數值(數字、文本或邏輯值)或對數字、文本或邏輯值的單元格引用

可能包含所要查找的數值的連續單元格區域。Lookup_array 應為數組或數組引用

為數字 -1、0 或 1。Match-type 指明 Microsoft Excel 如何在 lookup_array 中查找 lookup_value。

37.INDEX函數

= INDEX ( array , row_num , column_num )

為單元格區域或數組常量

數組中某行的行序号，函數從該行傳回數值。如果省略 row_num，則必須有 column_num。

數組中某列的列序号，函數從該列傳回數值。如果省略 column_num，則必須有 row_num。

多與MATCH函數連用

38.OFFSET函數

=OFFSET ( reference , rows , cols , height , width)

以指定的引用為參照系，通過給定偏移量得到新的引用。傳回的引用可以為一個單元格或單元格區域。并可以指定傳回的行數或列數。

注意：如果省略 height 或 width，則其高度或寬度與 reference 相同。

39.INDIRECT函數

傳回由文本字元串指定的引用。此函數立即對引用進行計算，并顯示其内容。當需要更改公式中單元格的引用，而不更改公式本身，請使用函數 INDIRECT。

= INDIRECT ( ref_text , a1 )

此單元格可以包含 A1-樣式的引用、R1C1-樣式的引用、定義為引用的名稱或對文本字元串單元格的引用。不是合法的單元格的引用，函數 傳回錯誤值。

為一邏輯值，指明包含在單元格 ref_text 中的引用的類型。

增補：

1.名稱的含義

名稱：是一種特殊的公式，由使用者自己定義，程式運作時存在于記憶體當中，通過其辨別進行調用。

2.定義名稱的方法

通過名稱框

通過菜單

通過所選内容

3.名稱命名的規則

名稱命名可以是任意字元與數字的組合，不能以純數字或以數字開頭

不能以字母R、 r 、C、c命名，也不能是單元格名稱

命名不超過255個字元

不區分大小寫

4.選用名稱的原因

5.名稱的引用類别（包括5項）

多區域引用

常量引用

數組引用 ={1;2;3;4;5;6;7;8;9}

公式引用 =SUM(D5:E8)

名稱修改

宏表函數

這部分略講，因為我還不太會。

1.宏表函數概念：早期低版本excel中使用的，現在已由VBA頂替它的功能，但仍可以在工作表中使用。

2.使用宏表函數注意事項：

A.不能在單元格中使用，要定義的名稱"(菜單：插入——名稱——定義)

B.有的宏表函數不能自動更新，需結合易失性函數來輔助完成自動更新

=函數&T(NOW()) 适用文本

=函數+TODAY()*0适用數字………

=函數&T(RAND())适用文本=函數+NOW()*0适用數字

隻要最後什麼都沒有就可以隻要後結果為0都可以

C.宏表函數對公式的長度有限制

D.宏表函數運算速度較慢使用易失性函數後，會引發工作簿重新計算(now,today,rand)

3.常用函數

GET.CELL

GET.DOCUMENT

GET.WORKBOOK