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

1 公式的組成

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

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

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

2 運(yùn)算符；

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

4 值或字符串；

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

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

1.1 運(yùn)算符：

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

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

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

1.3 A1引用樣式

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

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

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

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

='Sheet 2'!A1*5

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

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

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

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

=SUM(Sheet1:Sheet3!A1:A10)

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

=SUM('*'!A1:A10)

如多表相同位置求和：

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

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

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

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

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

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

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

步驟5：?jiǎn)螕簟按_定”按鈕。

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

2 公式類型

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

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

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

3 函數(shù)類型

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

4 引用類型

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

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

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

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

F10=D10*E10

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

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

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

5 數(shù)組公式

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

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

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

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

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

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

=SUMPRODUCT(B2:B7,C2:C7)

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

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

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

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

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

COUNTIF（range，criteria）

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

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

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

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

COUNTIFS語(yǔ)法：

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

7 vlookup函數(shù)使用

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

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

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

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

例如，要計(jì)算不同的銷售額對(duì)應(yīng)的提成比例，如果用IF函數(shù)，公式會(huì)很長(zhǎng)，用VLOOKUP模糊查找，最后一個(gè)參數(shù)省略或者為TRUE或1，表明該查找模式為模糊查找；如果找不到精確匹配值，則返回小于lookup_value 的最大數(shù)值。table_array 第一列中的值必須以升序排序，否則 VLOOKUP 可能無(wú)法返回正確的值。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ì)算，一個(gè)漢字算2個(gè)字節(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ù)提取位于左邊的英文字符。

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

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

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

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

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

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

－End－