Excel的公式是其數(shù)據(jù)處理的重要工具。

1 公式的組成

所有公式是以“=”號為引導(dǎo)，通過運(yùn)算符按照一定的順序組合進(jìn)行數(shù)據(jù)運(yùn)算處理的等式。簡單的公式有加、減、乘、除等計(jì)算。

公式里可以包含函數(shù)，以函數(shù)的返回結(jié)果參與運(yùn)算，函數(shù)則是按照特定算法進(jìn)行計(jì)算的產(chǎn)生一個或者一組結(jié)果的預(yù)定義的特殊公式，函數(shù)也可以嵌套。

1 以等于號=開頭，也可以以加號+開關(guān)或以@開頭；

2 運(yùn)算符；

3 單元格引用，包括命名的單元格和范圍，既可以是當(dāng)前工作表，也可以是當(dāng)前工作簿的其他工作表的單元格，或是其他工作薄中的單元格；

4 值或字符串；

5 工作表函數(shù)和參數(shù)；

6 括號：控制著公式中表達(dá)式的計(jì)算順序；

1.1 運(yùn)算符：

運(yùn)算符有算術(shù)、比較、文本、引用、邏輯運(yùn)算符等；使用運(yùn)算符，可以對數(shù)據(jù)執(zhí)行各種操作。

1.2 運(yùn)算符優(yōu)先級：

運(yùn)算符的優(yōu)先級是指在一個運(yùn)算符表達(dá)式中同時有多個運(yùn)算符時，優(yōu)先級高的運(yùn)算符優(yōu)先計(jì)算，用小括號括住的運(yùn)算符具有最高的優(yōu)先級。

1.3 A1引用樣式

公式和函數(shù)的強(qiáng)大之處在于可以引用工作表中的單元格并使用其值，引用的實(shí)質(zhì)是使用了一個行、列交叉的地址（Excel中行編號為1，2，3……，列編列為A，B，C……，C3即表示第3行第3列）。在公式或函數(shù)中，對單元格的引用就相當(dāng)于使用了一個變量。為方便單元格的引用，Excel使用了兩種引用方式，一種是絕對引用，是指對單元格的引用不相對于公式所在的位置而變化（公式復(fù)制時）；而相對引用則相反，公式的地址相對于公式所在位置而變化（公式復(fù)制時），這是因?yàn)楣剿趩卧衽c引用單元格有一個相對的位置。如B2單元格有公式'=C3'時，此時兩個單元格的編址有一個相對性，引用單元格在公式所在單元格的左下角，也就是下偏一行，左偏一列的位置，相對引用時，保持相對性不變，如將公式復(fù)制到B3時，使用相對引用，則公式變?yōu)?=C4'，還有一樣的相對性，即引用的是公式左下角的單元格，這樣在公式復(fù)制時帶來了極大的方便性。特性情況下，如果認(rèn)定對C3的引用不變，則使用絕對引用，公式變?yōu)?=$C$3'，公式復(fù)制時，不管公式所在單元格在哪個位置，其都是對C3的絕對引用，沒有考慮到兩者相對位置的變化。

用戶不但可以引用工作表中的單元格，還可以引用工作簿中多個工作表的單元格，這種引用方式稱為三維引用。三維引用的一般格式為：“工作表標(biāo)簽！單元格引用”，例如，要引用“Sheet1”工作表中的單元格B2，則應(yīng)該在相應(yīng)單元格中輸入“Sheet1！B2”。若要分析某個工作簿中多張工作表中相同位置的單元格或單元格區(qū)域中的數(shù)據(jù)，應(yīng)該使用三維引用。

創(chuàng)建跨工作表和跨工作簿引用的公式：

=工作表名稱!單元格地址

='Sheet 2'!A1*5

='工作簿路徑[工作簿名稱]工作表名稱'!單元格地址

=[銷售數(shù)據(jù).xlsx]Sheet2!A1*5

創(chuàng)建對多個工作表中相同單元格區(qū)域的三維引用：

起始工作表的名稱:終止工作表的名稱!單元格地址

=SUM(Sheet1:Sheet3!A1:A10)

=SUM(Sheet1!A1:A10,Sheet2!A1:A10,Sheet3!A1:A10)

=SUM('*'!A1:A10)

如多表相同位置求和：

=SUM('1月:12月'!C9)

在輸入公式時，用戶有時會將一個公式直接或者間接引用了自己的值，即出現(xiàn)循環(huán)引用。例如，在單元格A3中輸入“＝A1＋A2＋A3”，由于單元格A3中的公式引用了單元格A3，因此就產(chǎn)生了一個循環(huán)引用。此時，Excel中就會彈出一條信息提示框，提示剛剛輸入的公式將產(chǎn)生循環(huán)引用。

如果打開迭代計(jì)算設(shè)置，Excel就不會再次彈出循環(huán)引用提示。設(shè)置迭代計(jì)算的操作步驟如下。

步驟1：選擇“文件”菜單中的“選項(xiàng)”命令，打開“選項(xiàng)”對話框，再選擇“公式”選項(xiàng)卡。

步驟2：選中“啟用迭代計(jì)算”復(fù)選框。

步驟3：在“最多迭代次數(shù)”文本框中輸入循環(huán)計(jì)算的次數(shù)。

步驟4：在“最大誤差”文本框中設(shè)置誤差精度。

步驟5：單擊“確定”按鈕。

系統(tǒng)將根據(jù)設(shè)置的最多迭代次數(shù)和最大誤差計(jì)算循環(huán)引用的最終結(jié)果，并將結(jié)果顯示在相應(yīng)的循環(huán)引用單元格當(dāng)中。但是，在使用Excel時，最好關(guān)閉“啟用迭代計(jì)算”設(shè)置，這樣就可以得到對循環(huán)引用的提示，從而修改循環(huán)引用的錯誤。

2 公式類型

公式可以按參與運(yùn)算的數(shù)據(jù)的類型區(qū)分為以下五種：

與普通公式不同，數(shù)組公式可以完成多步計(jì)算，而且需要使用【Ctrl+Shift+Enter】組合鍵輸入數(shù)組公式，而不只是用【Enter】鍵。Excel會自動使用一對大括號將輸入好的整個公式包圍起來，以此來表明這是一個數(shù)組公式而非普通公式

公式應(yīng)避免循環(huán)引用，包含直接和間接引用自己。

3 函數(shù)類型

根據(jù)公式所處理的數(shù)據(jù)類型不同，函數(shù)共12種，如下圖所示，除了自定義函數(shù)之外，2003版本自帶的函數(shù)有300多個，2007以及以上版本函數(shù)有400多個，一般來說，掌握常用的30～50個函數(shù)基本可以應(yīng)對工作中的日常需求。

4 引用類型

Excel的工作表的單元格由行、列交叉而成，由行和列共同構(gòu)成一個單元格的地址，在Excel中稱為引用。是公式最重要的數(shù)據(jù)源。

引用的地址在進(jìn)行公式復(fù)制時，并非固定不變，如B2的單元格輸入=A2，復(fù)制到B3時，公式變更為=A3，復(fù)制到C4時，公式變更為=B4，引用的地址相對變化，這個公式可以理解為公式所在單元格等于左邊單元格的值。

這樣的引用稱為相對引用。這是公式的強(qiáng)大之處，給公式復(fù)制和填充帶來極大的方便。

再舉個例子，下面E8=C8*D8，復(fù)制到F10的公式會是什么？

F10=D10*E10

上面公式使用相對引用，可以理解為“此單元格的值等于左邊第二行特許以左邊第二行的值”。

相對引用是指公式復(fù)制時隨著單元格的變化而變化，引用的地址不固定（對于復(fù)制公式時特別有效）。

絕對引用是指公式復(fù)制時單元格固定不變。絕對引用前面有個$，相對引用則沒有，混合引用就是行與列一個是相對引用，一個是絕對引用。利用F4鍵可以靈活切換相對引用和絕對引用。對于初學(xué)者，可以這樣去記憶，“有錢能使鬼推磨”，有$就是絕對引用，一心一意跟著你不跑，沒有$就是相對引用，像墻頭草隨風(fēng)倒。

5 數(shù)組公式

一個基本的公式可以按照一個或多個參數(shù)或者數(shù)值來產(chǎn)生一個單一的結(jié)果，用戶既可以輸入對包含數(shù)值的單元格的引用，也可以輸入數(shù)值本身。在數(shù)組公式中，通常使用單元格區(qū)域引用，但也可以直接輸入數(shù)值數(shù)組。輸入的數(shù)值數(shù)組稱為數(shù)組常量。

數(shù)組公式可能是功能最強(qiáng)大的公式，因?yàn)樗梢栽谝粋€公式中執(zhí)行多步計(jì)算，一次性處理多個操作，這是普通公式無法實(shí)現(xiàn)的。

數(shù)組中使用的常量可以是數(shù)字、文本、邏輯值（“TRUE”或“FALSE”）和錯誤值等。數(shù)組有整數(shù)型、小數(shù)型和科學(xué)計(jì)數(shù)法形式。文本則必須使用引號引起來，例如“星期一”。在同一個數(shù)組常量中可以使用不同類型的值。數(shù)組常量中的值必須是常量，不可以是公式。數(shù)組常量不能含有貨幣符號、括號或百分比符號。所輸入的數(shù)組常量不得含有不同長度的行或列。

數(shù)組常量可以分為一維數(shù)組與二維數(shù)組。一維數(shù)組又包括垂直和水平數(shù)組。在一維水平數(shù)組中元素用逗號分開，如｛10，20，30，40，50｝；在一維垂直數(shù)組中，元素用分號分開，如｛100；200；300；400；500｝。而對于二維數(shù)組中，常用逗號將一行內(nèi)的元素分開，用分號將各行分開。

數(shù)組公式與相同功能的普通公式：

{=SUM(B2:B7*C2:C7)}

=SUMPRODUCT(B2:B7,C2:C7)

6 count相關(guān)函數(shù)

COUNT只計(jì)數(shù)，文本、邏輯值、錯誤信息、空單元格都不統(tǒng)計(jì)。

COUNTA統(tǒng)計(jì)非空單元格個數(shù)，只要單元格有內(nèi)容，就會被統(tǒng)計(jì)，包括有些看不見的字符

COUNTIF：滿足一定條件計(jì)數(shù)

COUNTIF函數(shù)是對指定區(qū)域中符合指定條件的單元格計(jì)數(shù)的函數(shù)，該函數(shù)的語法規(guī)則如下：

COUNTIF（range，criteria）

參數(shù)：range 要計(jì)算其中非空單元格數(shù)目的區(qū)域；

參數(shù)：criteria 以數(shù)字、表達(dá)式或文本形式定義的條件。

判斷A列的身份證號碼是否重復(fù)。

=IF(COUNTIF($A$2:$A$10,A2)>1,'重復(fù)','')

COUNTIFS語法：

COUNTIFS（條件區(qū)域1，條件1，條件區(qū)域2，條件2，…）

7 vlookup函數(shù)使用

7.1 VLOOKUP函數(shù)多條件查找：

將不同條件用&連接起來，使多個條件變?yōu)橐粋€條件。

如下圖所示，要查找產(chǎn)品名稱和型號都匹配的單價，可以把產(chǎn)品名稱和型號2個字段合并為一個字段，即輔助列內(nèi)容，再用VLOOKUP查找。

7.2 VLOOKUP函數(shù)模糊查找

例如，要計(jì)算不同的銷售額對應(yīng)的提成比例，如果用IF函數(shù)，公式會很長，用VLOOKUP模糊查找，最后一個參數(shù)省略或者為TRUE或1，表明該查找模式為模糊查找；如果找不到精確匹配值，則返回小于lookup_value 的最大數(shù)值。table_array 第一列中的值必須以升序排序，否則 VLOOKUP 可能無法返回正確的值。D3公式為

=VLOOKUP(B3,$G$3:$H$11,2)

8 實(shí)例，怎樣把中英文分開

如下圖，需要把A列中英文分開

B1公式為：

=RIGHT(A1,LENB(A1)-LEN(A1))。

公式解析：LENB按字節(jié)數(shù)計(jì)算，LEN按字符數(shù)計(jì)算，一個漢字算2個字節(jié)，公式=LEN('騰訊')返回結(jié)果是2，公式=LENB('騰訊')返回結(jié)果是4，因此LENB與LEN函數(shù)結(jié)果相減得到中文漢字字符數(shù)，再用RIGHT函數(shù)提取位于右邊的中文字符。

C1公式為=LEFT(A1,LEN(A1)-(LENB(A1)-LEN(A1)))

公式解析：LENB(A1)-LEN(A1)得到中文漢字字符數(shù)，再用總字符數(shù)LEN(A1)減去中文漢字字符數(shù)就得到英文字符數(shù)，再用LEFT函數(shù)提取位于左邊的英文字符。

這個問題也可以用快速填充功能實(shí)現(xiàn)，用公式的好處是如果A列原始數(shù)據(jù)變了，分開的中英文自動跟著變，而快速填充則需要重新操作，這充分體現(xiàn)了公式的魅力。

也可以使用以下公式和函數(shù)來實(shí)現(xiàn)：

B2公式：=LEFTB(A2,SEARCHB('?',A2)-1)

C2公式: =MIDB(A2,SEARCHB('?',A2),11)

公式說明：SEARCHB是在一個字符串中查找特定字符位置的函數(shù)，

而且可以區(qū)分單雙字節(jié)，它和FIND的區(qū)別是可以使用通配符。公式中的？就是表示任意一個單字節(jié)的字符，屬通配符，不是真的查找問號。

－End－