1. 邏輯判斷函數(shù)：IF AND OR

廣泛應(yīng)用于有分支的情況，滿足情況1時返回值A(chǔ)，滿足情況2時返回值B。例如 語文成績在70分-80分時設(shè)置一朵花，80分-90分時設(shè)置兩朵花，90分-100分時設(shè)置三朵花

函數(shù)說明：

IF(判斷條件，條件滿足時的返回值，條件不滿足時的返回值)

示例：

IF(G4>=5000,'有折扣','沒有折扣'): 含稅金額RMB在5000以上返回有折扣，否則返回沒有折扣

其中G2>=5000這一段為邏輯表達式，如果成立的話，在是否有折扣列中 返回有折扣，不成立的話，返回沒有折扣.

擴展：如果邏輯表達式里面包含有多個條件怎么辦？我們可以使用邏輯表達式中的AND, OR等，

這里再舉一個例子，如果幣種為人民幣并且含稅金額大于1000，則設(shè)置為1，否則設(shè)置為0.

結(jié)果：IF(AND([@幣種]='CNY',[@含稅金額]>1000),1,0)

2. 求和以及按條件求和函數(shù)SUM, SUMIF, SUMIFS

廣泛用于對某列或者某行的數(shù)值進行合計，或者按照條件合計，即滿足條件的數(shù)值才進行合計。例如統(tǒng)計 花費在100元以上的交易的總花費是多少

函數(shù)說明：

SUM(求和區(qū)域)：對指定的所有單元格求和

SUMIF(作為條件的區(qū)域，作為條件區(qū)域需要滿足的條件，求和的區(qū)域)：對指定單元格區(qū)域中，滿足條件的單元格求和，只有一個條件

SUMIFS(求和的區(qū)域, 作為條件的區(qū)域1，作為條件區(qū)域1需要滿足的條件, 作為條件的區(qū)域2，作為條件區(qū)域2需要滿足的條件...)：對指定單元格區(qū)域中，滿足條件的單元格求和，可以有多個條件

注意：[作為條件區(qū)域需要滿足的條件] 參數(shù)，可以為某個單元格，或者類似 '>=10' '<>

示例：

SUM函數(shù)：

SUM(E2:E16)，返回的是含稅金額（RMB）的合計金額

SUMIF函數(shù)：

SUMIF(C2:C16, '>=10', G2:G16)，返回一次性采購數(shù)量在10以上的含稅金額（RMB）的合計。

SUMIFS函數(shù)：

SUMIFS(G2:G16, C2:C16, '>=10', F2:F16, J2)，返回一次性采購數(shù)量在10以上，并且?guī)欧N為CNY的含稅金額（RMB）合計

3. 統(tǒng)計個數(shù) 以及 統(tǒng)計滿足條件的個數(shù)：COUNTIF, COUNTIFS,COUNT, COUNTA, COUNTBLANK

廣泛用于統(tǒng)計某列或者某行的個數(shù)，或者統(tǒng)計滿足條件的個數(shù)，例如總成績大于80分的學(xué)生個數(shù)。

函數(shù)說明：

COUNTIF(作為條件的區(qū)域，作為條件區(qū)域需要滿足的條件)： 對指定單元格區(qū)域中，滿足條件的單元格個數(shù)，只有一個條件

COUNTIFS(作為條件的區(qū)域1，作為條件區(qū)域需要滿足的條件1，作為條件的區(qū)域2，作為條件區(qū)域需要滿足的條件2...)：對指定單元格區(qū)域中，滿足條件的單元格個數(shù)，可以有多個條件

COUNT（求和區(qū)域）: 計算區(qū)域內(nèi)包含數(shù)字的個數(shù)

COUNTA（求和區(qū)域）：計算區(qū)域內(nèi)非空的個數(shù)

COUNTBLANK（求和區(qū)域）：計算區(qū)域內(nèi)空的個數(shù)

示例：

COUNTIF函數(shù)：

COUNTIF(C2:C16, '>=10'), 返回 一次性采購數(shù)量在10以上的個數(shù)。

COUNTIFS函數(shù)：

COUNTIFS(C2:C16,'>=10', F2:F16, J2)，返回 一次性采購數(shù)量在10以上，幣種為CNY的個數(shù)。

4. 查詢主檔案信息 根據(jù)某個關(guān)鍵字查詢對應(yīng)記錄的詳細信息 VLookup, lookup

這個查詢函數(shù)用途非常廣泛，出鏡率非常高，常用于根據(jù)某些值去其他地方定義的數(shù)據(jù)檔案中查找對應(yīng)的其他信息，例如根據(jù)學(xué)號查成績，根據(jù)身份證查籍貫 年齡等。

Vlookup(要查詢的字段，查詢區(qū)域，返回查詢區(qū)域的第幾列，查找方式 0:精確查找 1:非精確查找):

到參數(shù)2指定的查詢區(qū)域中的第一列中，查詢參數(shù)1指定的字段值，查到了就返回 查詢區(qū)域中 參數(shù)3指定的第幾列。參數(shù)4 指定是精確查找還是非精確查找，如果為非精確查找，就會找到最接近于查詢字段值的那個值（字符串為模糊搜索，數(shù)字的話為最接近這個數(shù)字）。

如果查到多行，也只返回從上到下第一個找到的。只能實現(xiàn)在指定區(qū)域中從左到右查找。

示例：

Vlookup函數(shù)：

VLOOKUP(F2, K$2:L$3, 2), 根據(jù)幣種去取對應(yīng)人民幣的匯率。

注意：這里查詢區(qū)域要設(shè)置為K$2:L$3, 不能設(shè)置為K2:L3，不然在該列往下復(fù)制到其他行的時候查詢區(qū)域也會跟著變，會導(dǎo)致結(jié)果異常。

根據(jù)幣種查找匯率

LookUp函數(shù)：

有以下兩種基本的形式

數(shù)組型查找 LOOKUP（要查找的值，查找區(qū)域）：從參數(shù)2指定的查詢區(qū)域中的第一列中 查找參數(shù)1指定的值，找到后，返回查找區(qū)域的最后一列值。如果查到多行，也只返回從上到下第一個找到的。這個函數(shù)只能實現(xiàn)從左到右查詢。

我們還是用上面的例子（根據(jù)幣種查找匯率），結(jié)果為：LOOKUP(F2, K$2:L$3)

這個函數(shù)局限性很大，使用上還不如Vlookup靈活，都可以用VLookup替代，建議不要使用。

那如何實現(xiàn)從右到左逆向查詢呢？我們可以用到Looup函數(shù)的第二種形式。

向量型查找 LOOKUP（要查找的值，查找區(qū)域， 結(jié)果區(qū)域）：

從參數(shù)2指定的查詢區(qū)域中的第一列中 查找參數(shù)1指定的值，找到后，返回參數(shù)3指定的結(jié)果區(qū)域中的結(jié)果。如果查到多行，也只返回從上到下第一個找到的。結(jié)果區(qū)域可以位于查找區(qū)域的左邊。

示例：

LOOKUP(F2, K$2:K$3, J$2:J$3)： 根據(jù)參數(shù)1指定的幣種，去參數(shù)2指定的區(qū)域中的第一列中查找，查找到后，返回參數(shù)3指定的結(jié)果。

擴展：

Lookup還有一種很經(jīng)典的用法，可以根據(jù)多個條件正向或者逆向查詢。

Lookup(1, 0/(判斷條件1)*(判斷條件2)...(判斷條件N), 結(jié)果區(qū)域): 根據(jù)參數(shù)2指定的多個條件，返回參數(shù)3的結(jié)果區(qū)域。

LOOKUP(1, 0/(C2>=K$8:K$11)*(G2>=L$8:L$11), J$8:J$11)： 根據(jù)采購數(shù)量和含稅金額RMB去找相應(yīng)的優(yōu)惠折扣。(C2>=K$8:K$11)作為條件1表示采購數(shù)量大于折扣表中的采購數(shù)量，(G2>=L$8:L$11)作為條件2表示含稅金額RMB大于折扣表中含稅金額RMB，J$8:J$11表示返回的結(jié)果集。從示例中知道，在折扣表中會找到多行的情況，默認返回找到的最后一行，也就是最下面一行。這個和前面說的VLookup和Lookup函數(shù)的兩種基本形式是不一樣的，因為它們返回找到的第一條記錄，也就是最上面的記錄。

5. 字符處理函數(shù) find Mid Len，用于字符串截取，比如把家庭住址截取成大地址和明細地址

函數(shù)說明：

find(要查詢的關(guān)鍵字，從哪個目的字符串中查詢，從目的字符串中的第幾位開始查): 查詢關(guān)鍵字在目的字符串的位置

Mid(要截取的字符串， 起始位置， 截取多少位字符）：從字符串的第幾位開始，截取多少位字符，返回截取后的字符串

Len(字符串): 返回字符串有多少個字符

示例：

MID(A2, 1, FIND('號', A2))： 從地址中取得具體住在多少路多少號。

思路：先獲取'號'字符在目的字符串A2的位置，然后從第一位開始截取到'號'的位置。

MID(A2, FIND('號', A2) + 1, LEN(A2) - FIND('號', A2))： 從地址中取得具體住在哪個房間。

思路：從號的位置+1位置開始截取，一直到最后。當(dāng)然，也可以用RIGHT(A2, LEN(A2) - FIND('號', A2))來替代，是一個意思。

當(dāng)然上面兩個公式可以分別被下面兩個函數(shù)替代：

LEFT(A2, FIND('號', A2))

RIGHT(A2, LEN(A2) - FIND('號', A2))

但是最為靈活的還是Mid函數(shù)，可以截取任意位置的字符。而left和right只能截取最左方的字符和最右方的字符。

6. 日期處理函數(shù)

示例：

獲取交易日期的 年: Year(date1)，季度，月: Month(date1)，日: Day(date1), 該月最后一天: EODate(date1, monthNum)，處于周幾: WeekDay(date1)， 周數(shù): WeekNum(date1)， 下一個工作日: WorkDay(date1, Daynum)。

7. 格式轉(zhuǎn)換函數(shù) Text，例如將八位字符串轉(zhuǎn)化為日期

函數(shù)說明：

Text(需要轉(zhuǎn)換的字符串， 需要的格式)

示例：

將身份證上的出生年月轉(zhuǎn)化為日期格式，并算出對應(yīng)年齡

--TEXT(MID(A2, 7, 8), '00-00-00')：先截取身份證的出生日期，然后通過Text轉(zhuǎn)換函數(shù)轉(zhuǎn)換為日期格式的字符串，然后在前面加上--，就能轉(zhuǎn)換為真正的日期格式

DATEDIF(B2, TODAY(), 'Y')：該函數(shù)計算 Today()也就是今日 和 出生年月 差了多少年，其中第三個參數(shù)“Y”指定的是要計算相差多少年 , 如果要計算相差多少月時可以把第三個參數(shù)換成'M'。

Text函數(shù)，不僅可以把字符串轉(zhuǎn)為日期格式，還可以做數(shù)值格式轉(zhuǎn)換等等各類轉(zhuǎn)換。

8. 區(qū)段分布函數(shù) Frequency 例如計算學(xué)生的成績區(qū)段分布

函數(shù)說明：

Frequency(源數(shù)據(jù)數(shù)組區(qū)域， 區(qū)段數(shù)組區(qū)域)： 返回一個數(shù)組，表示參數(shù)1指定的源數(shù)據(jù)數(shù)組區(qū)域中，落于參數(shù)2指定的各區(qū)段的頻度分布。

示例：

例如 計算落于各個采購數(shù)量區(qū)段中的采購訂單行數(shù)量。

注意：該函數(shù)返回的是一個數(shù)組，所以在編寫函數(shù)之前，先要選擇返回值落在哪個區(qū)域，而不是單單選擇一個單元格。然后函數(shù)編寫好之后，需要一起按Ctrl + shift + Enter，數(shù)組返回值才會設(shè)置到選擇的區(qū)域中。

9. Offset函數(shù)

函數(shù)說明：

Offset（參照單元格，行偏移量，列偏移量，返回幾行，返回幾列）：參考單元格 按參數(shù)2指定的行偏移量在行上偏移，以及按參數(shù)3指定的列偏移量在列上偏移后，返回參數(shù)4和5指定的單元格區(qū)域。

示例：

獲取最后三筆訂單行的采購訂單號，行號，數(shù)量，單價

結(jié)果：OFFSET($A$1, COUNTA($A:$A) - 3, 0, 3, 4)

首先，選擇結(jié)果區(qū)域，編寫公式。

公式中，COUNTA($A:$A) 計算A列中非空單元格有多少，返回值應(yīng)該為16

然后將$A$1單元格，行偏移13位，列偏移0位，也就是到了$A$14的位置。最后返回 參數(shù)4和參數(shù)5指定的3行4列的單元格。

公式完成后，需要同時按住 Ctrl + Shift +Enter，才能將返回的數(shù)組返回到選擇的區(qū)域中。

10. 四舍五入

函數(shù)說明：

round: 四舍五入

rounddown: 向下取整

roundup: 向上取整