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

1公式的組成

所有公式都用“=”符號(hào)指導(dǎo)，是通過運(yùn)算符根據(jù)特定順序組合處理數(shù)據(jù)運(yùn)算的表達(dá)式。簡單的公式有加、減、乘、除等計(jì)算。

公式可以包含函數(shù)，作為函數(shù)的返回結(jié)果參與運(yùn)算，函數(shù)是根據(jù)特定算法生成計(jì)算結(jié)果或結(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)算符、文本運(yùn)算符、引用運(yùn)算符、邏輯運(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)算符，則首先計(jì)算優(yōu)先級(jí)較高的運(yùn)算符，括在括號(hào)中的運(yùn)算符具有最高優(yōu)先級(jí)。

1.3 A1參考樣式

公式和函數(shù)的優(yōu)點(diǎn)是可以引用工作表中的單元格并使用其值。引用本質(zhì)上是行與列交叉的地址(Excel行號(hào)為1，2，3).列為a、b、c.C3是使用第3行的列)。在公式或函數(shù)中，對(duì)單元格的引用與使用變量相同。為了便于單元格引用，Excel使用兩種引用方法：絕對(duì)引用。也就是說，對(duì)單元格的引用相對(duì)于公式位置不變(復(fù)制公式時(shí))。相對(duì)引用相反，包含公式的單元格相對(duì)于引用單元格的位置(復(fù)制公式時(shí))，因此公式的地址取決于公式所在的位置。如果單元格B2包含公式“=C3”，則兩個(gè)單元格的尋址是相對(duì)的。引用單元格保留公式所在單元格的左下角，即下行、左列位置，如果存在相對(duì)引用，則保留相對(duì)引用。例如，如果在將公式復(fù)制到B3時(shí)使用相對(duì)引用，公式將變?yōu)?#39;，因?yàn)槠涮匦?，如果確定對(duì)C3的引用不會(huì)更改，則使用絕對(duì)引用將公式更改為'=$C$3不考慮相對(duì)位置的變化。

用戶不僅可以引用工作表中的單元格，還可以引用工作簿中多個(gè)工作表中的單元格。這稱為3d參考。3d參考的一般格式是“工作表選項(xiàng)卡！單元格引用。例如，要引用Sheet1工作表中的單元格B2，請(qǐng)?jiān)谠搯卧裰休斎搿癝heet1！必須輸入“B2”。要分析工作簿中多個(gè)工作表中同一位置的單元格或單元格區(qū)域中的數(shù)據(jù)，必須使用3d引用。

要?jiǎng)?chuàng)建工作表和工作簿之間引用的公式：

=圖紙名稱！單元地址

='床單2 '！A1*5

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

=[銷售數(shù)據(jù)。xlsx]表2！A1*5

建立對(duì)多個(gè)工作表中相同儲(chǔ)存格區(qū)域的3d參考。

開始工作表名稱3360結(jié)束工作表名稱！單元地址

=sum(圖紙1:圖紙3！(A1:A10)

=總計(jì)(圖紙1！A1:A10、Sheet2！A1:A10、Sheet3！(A1:A10)

=總計(jì)(' * '！(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：單擊“確定”按鈕。

系統(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è)公式包圍起來，以此來表明這是一個(gè)數(shù)組公式而非普通公式

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

3 函數(shù)類型

根據(jù)公式所處理的數(shù)據(jù)類型不同，函數(shù)共12種，如下圖所示，除了自定義函數(shù)之外，2003版本自帶的函數(shù)有300多個(gè)，2007以及以上版本函數(shù)有400多個(gè)，一般來說，掌握常用的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ù)制和填充帶來極大的方便。

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

F10=D10*E10

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

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

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

5 數(shù)組公式

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

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

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

數(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ù)的語法規(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語法：

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

7 vlookup函數(shù)使用

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

將不同條件用&連接起來，使多個(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ì)很長，用VLOOKUP模糊查找，最后一個(gè)參數(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ì)算，一個(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è)問題也可以用快速填充功能實(shí)現(xiàn)，用公式的好處是如果A列原始數(shù)據(jù)變了，分開的中英文自動(dòng)跟著變，而快速填充則需要重新操作，這充分體現(xiàn)了公式的魅力。

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

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

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

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

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

－End－