表格常用公式大全：表格公式怎麼設置

在日常工作中，我們經常用EXCEL函數處理表冊，函數的運用，大幅度提升了我們的工作效率。筆者集合工作實際，把最常用的EXCEL函數進行逐一詳細講解，課程中有大量案例，為便於朋友們更加深入了解各個函數的用法，將於每天上午7:00同步發布視頻教程和圖文教程（包含公式）。只要不懈努力和不斷實踐，通過30天的系統學習，你也能成為EXCEL函數高手，從此告別加班，讓同事和朋友刮目相看。

今天，我將和大家一起分享學好EXCEL函數和公式必須學會的基礎知識和操作，涉及的知識點有點多，如果有恆心和毅力想學好EXCEL函數和公式，請耐心往下看；如果你只是想隨便看看，抱着無所謂的態度，那請止步，首先你的學習態度就不端正，談何學好？我花費大量時間整理學習資料，把每個知識點做成實例進行講解，自己從中又鞏固了一遍，進步不是一點點，要說學習感受，真的非常辛苦。

俗話說得好：“基礎不牢，地動山搖”“不積跬步，無以至千里；不積小流，無以成江海”，只有把基礎打好了、打牢了，一點一點積累經驗，才會在以後的學習過程中得心應手。真心希望點進來的朋友抱着必學會的態度認真學習，反覆練習，對課程中的知識點要學會舉一反三進行利用，學會變通。學無止境，只有學到手的東西才是自己的，只有自己學會了，才會在工作中如魚得水，在職場上如虎添翼，否則，神馬都是浮雲。

說得有點多，戳到痛點的朋友請多諒解，這也說明你有學習的態度和決心。

下面，我們進行今天的課程。

本套課程以WPS2019版的EXCEL進行講解，其實，在一般用戶使用過程中，WPS和OFFICE基本上沒太大差別，甚至WPS更加人性化，但是在一些專業領域，WPS處理EXCEL表格的某些複雜函數可能就不如微軟的OFFICE。

一、什麼是EXCEL工作簿、工作表、單元格

（一）工作簿

所謂工作簿，是指EXCEL環境中用來儲存並處理工作數據的文件。也就是說，EXCEL文檔就是工作簿，它是EXCEL工作區中一個或多個工作表的集合，其擴展名有XLS、XLSX、XLSM、XLSB等。每一本工作簿可以擁有許多不同的工作表，工作簿中最多可建立255個工作表。

（二）工作表

工作表是顯示在工作簿窗口中的表格，在後續新版本的EXCEL中，一個工作表可以由1048576行和256列構成，行的編號從1到1048576，列的編號依次用字母A、B……XFD表示，行號顯示在工作簿窗口的左邊，列號顯示在工作簿窗口的上邊。

EXCEL默認一個工作簿有三個工作表，用戶可以根據需要添加工作表，但每一個工作簿中的工作表個數受可用內存的限制，當前的主流配置已經能輕鬆建立超過255個工作表了。

每個工作表有一個名字，工作表名字顯示在工作表標籤上。工作表標籤顯示了系統默認的前三個工作表名：Sheet1、Sheet2、Sheet3。其中白色的工作表標籤表示活動工作表。單擊某個工作表標籤，可以選擇該工作表為活動工作表。

工作薄中的每一張表格稱為工作表。工作薄如同活頁夾，工作表如同其中的一張張活頁紙。工作表是EXCEL存儲和處理數據最重要的部分，其中包含排列成行和列的單元格，它是工作簿的一部分，也稱電子表格。可以同時在多張工作表上輸入並編輯數據，並且可以對來自不同工作表的數據進行匯總計算。

（三）單元格

單元格是表格中行與列的交叉部分，它是組成表格的最小單位，可拆分或者合併。單個數據的輸入和修改都是在單元格中進行的。

單元格按所在的行列位置來命名，例如：地址“A1”指的是“A”列與第1行交叉位置上的單元格；地址“A1:D8”指的是單元格A1到D8之間的區域。

二、什麼是函數、公式和運算符

（一）EXCEL公式

EXCEL中，公式是對EXCEL工作表中的值進行計算的等式，在EXCEL中可以使用常量和算術運算符創建簡單的公式。

公式總是以“=”開始，然後將各種計算數據（單元格地址、名稱、常量、函數等）使用不同的運算符連接起來，從而有目的地完成某種數據結果的計算。

例如：=A1+B1+C1就是一個簡單的公式。

（二）EXCEL函數

EXCEL中，函數實際上是一個預先定義的特定計算公式，按照這個特定的計算公式對一個或多個參數進行計算，並得出一個或多個計算結果，叫做函數值。使用這些函數不僅可以完成許多複雜的計算，而且還可以簡化公式的繁雜程度。

例如：SUM(A1:A8)就是一個簡單的函數。

每個函數都有唯一的名稱，並且不區分大小寫，它決定了函數的功能和用途。在函數中，括號是成對出現的，它是函數的標識。

要想讓函數起作用，必須在公式中使用，直接在函數前添加“=”，是函數最簡單的應用。因此，從廣義上講，函數是一種特殊的公式。

（三）運算符

運算符是公式的基本要素，利用它可對公式中的參數進行特定類型的運算，它是影響數據計算結果的重要因素之一。

在EXCEL中，運算符包括：算術運算符、比較運算符、文本連接運算符和引用運算符等4種類型。

1.算術運算符：算術運算符主要用於對數據進行各種數學運算，各種算術運算符的具體作用和實例如下：

提示：“+”和“-”運算符的特殊用法：“+”和“-”運算符既可以連接兩個參數，也可以連接一個參數，當只連接一個參數時，用於標識數據的正負數，例如：“+8”表達式表示正數8，“-8”表達式表示負數8。

2.比較運算符：比較運算符主要用於比較兩個不同數據的值，當等式成立，則結果返回邏輯值TRUE；如果等式不成立，則結果返回邏輯值FALSE。各種比較運算符的具體作用和實例如下：

3.文本運算符：在EXCEL中，文本運算符只有一個，即和號（&），通常被稱為文本串聯符或文本連接符，其具體作用和實例如下：

4.引用運算符：引用運算符主要用於對指定的單元格區域進行合併計算，在EXCEL中，引用運算符只有兩個，即“:”和“,”，其具體作用和實例如下：

通過以上學習，我們已經了解幾種運算符的作用，那麼運算順序是怎樣的呢？

在使用公式計算數據時，單純的一個運算符的數據計算比較少，通常都是同時使用多個運算符，此時就有必要了解計算過程中的運算順序，這樣才能知道數據的計算結果是什麼。

在EXCEL中，系統遵循從高到低的順序進行計算，相同優先級的運算符，遵循從左到右的原則進行計算。

引用運算符>算術運算符>文本連接運算符>比較運算符

對於算術運算符來說，同級運算符的優先順序為：負數→百分比→乘方→乘和除→加和減。

如果要改變運算符的優先順序，可以使用括號，而且在Excel中允許括號嵌套使用，系統將按照先內後外的順序優先處理最內部括號的內容，然後依次向外擴展。

例如：

要計算((3+2)*5-1)/6，計算過程為：

第一步，計算嵌套括號中的3+2，結果為(5*5-1)/6

第二步，在括號中先計算5*5，結果為(25-1)/6

第三步，計算括號中的25-1，結果為24/6

第四步，計算24/6，結果為4

三、EXCEL中的引用方式

（一）數據的引用方式

在EXCEL中，數據的引用方式有三種，分別是相對引用、絕對引用和混合引用，各種引用方式的說明如下：

在公式中，如果要快速在各種引用方式之間進行切換，可以選擇單元格地址或者將文本插入點定位到單元格地址的前、中、後，然後按鍵盤上的【F4】鍵切換。

例如：在公式“=A1”中，將文本插入點定位到“A”和“1”之間（或在“A”的前面，或在“1”的後面），連續按鍵盤上的【F4】鍵的順序效果如下：

第一次按【F4】鍵，結果為=$A$1

第二次按【F4】鍵，結果為=A$1

第三次按【F4】鍵，結果為=$A1

第四次按【F4】鍵，結果為=A1

無論單元格的初始狀態為哪種引用，按【F4】鍵後都會按如上順序變化，如初始狀態為上面第二次按【F4】鍵的效果，在A1單元格地址中按【F4】鍵後變為如上第三次按【F4】鍵的效果，而不是如上第一次按【F4】鍵的效果。

知識拓展：

複製$A$1到其他單元格，引用的單元格永遠是A1單元格。

複製A$1，往右複製時，會變為B$1、C$1，以此類推；往下複製時，會固定在A1單元格。

複製$A1，往右複製時，會固定在A1單元格；往下複製時，會變為$A2、$A3，以此類推。

複製A1，往右複製時，會變為B1、C1，以此類推；往下複製時，會變為A2、A3，以此類推。

（二）同一工作簿跨表引用

在使用公式和函數計算數據時，有時候會引用當前工作簿的其他工作表的數據，這時就涉及到跨表引用。

在同一工作簿的不同工作表中引用單元格或單元格區域，其引用格式為：“工作表名稱!單元格地址”，這裡的單元格地址引用也可以包括相對引用、絕對引用和混合引用。

例如：“工作表!A1”或“工作表!A1:D1”

除了按照引用格式直接手動輸入公式外，還可以通過選擇單元格的方式快速生成引用，具體操作步驟如下：

第一，打開文件，切換到“個人基本信息”工作表，在B2單元格中輸入“=”，如下圖：

第二，選擇“信息匯總表”工作表，用鼠標點擊A3單元格，然後按回車鍵，即可引用該單元格的數據，見下圖：

返回“個人基本信息”工作表，我們發現，這時候已經把數據引用過來了，見下圖：

提示：如果跨表引用的工作表的名稱以數字、空格、特殊符號（如$、%、#、~、!、+、-、@、=等）開頭，則在公式中引用工作表名稱時，需要使用一對半角單引號引起來，如：“1月會員費!A1”。

（三）跨工作簿引用

在EXCEL中，除了在同一個工作簿中引用數據以外，使用公式計算數據時，還經常會跨工作簿引用數據，具體的引用格式為：“=[工作簿名稱]工作表名稱!單元格地址”。在跨表、跨工作簿引用數據時，有兩種方法，具體如下：

1.選擇工作表數據。如果當前在EXCEL中同時打開了被引用的工作簿，可以通過直接選擇工作表中的數據單元格完成引用，具體操作方法與上面講的方法類似。

2.手動輸入引用。如果被引用的工作簿當前沒有打開，只能通過手動輸入的方式完成引用，需要注意的是，在輸入引用時，必須包含完整的文件路徑，並且要用半角單引號將文件路徑、工作簿和工作簿名稱引起來。

例如：='[工作簿名稱]工作表名稱!單元格地址’

四、邏輯值和常用的邏輯函數

（一）邏輯值

在EXCEL中，邏輯值有兩個，分別是TRUE和FALSE，主要作用是對某條件判定是否成立，成立則為真（TRUE），不成立則為假（FALSE）。

例如：在單元格中輸入”=5+5=10″（輸入時不包括引號），按回車鍵後顯示結果為TRUE，5+5=10是正確的，即真值，說明等式成立；如果在單元格中輸入”=5+5=8″，則結果顯示FALSE，計算結果不正確，說明等式不成立。

1.邏輯值的轉換規則

在EXCEL公式計算時，邏輯值可以用相應的數值代替，但是，在不同的運算中有不同的要求，邏輯值的轉換規則有三種，具體如下：

（1）在四則運算中的轉換規則

在四則運算中，通常用1代替TRUE，用0代替FALSE。

例如：

公式“=1*TRUE”，結果為1

公式“=1+TRUE”，結果為2

公式“=1*FALSE”，結果為0

公式“=1+FALSE”，結果為1

（2）在邏輯運算中的轉換規則

在邏輯運算中，通常用非零值（不是0的任何數值）代替TRUE，用0代替FALSE。

例如：

公式“=IF(1,”正確”,”錯誤”)”，結果為：正確

公式“=IF(-1,”正確”,”錯誤”)”，結果為：正確

公式“=IF(0.1,”正確”,”錯誤”)”，結果為：正確

公式“=IF(0,”正確”,”錯誤”)”，結果為：錯誤

（3）在比較運算中的轉換規則

在比較運算中，邏輯值與數值和文本之間存在一定的關係，即：“數值<文本<FALSE<TRUE”。

例如：

公式“=TRUE>2”，結果為：TRUE

公式“=TRUE<2”，結果為：FALSE

公式“=FALSE=2”，結果為：FALSE

公式“=FALSE>2”，結果為：TRUE

以上三條轉換規則，對編寫公式和優化公式運算有很大的作用，在以後的課程中你將會體會得到。

2.邏輯值在EXCEL中的運用案例：根據性別計算退休年齡

我們在填寫職工信息表時，需要計算職工的退休年齡，不同性別的職工，其退休年齡不同，男性職工的退休年齡為60歲，女性職工的退休年齡為55歲，現在需要根據B3單元格中職工的性別自動填寫退休年齡。見下圖：

B3單元格中的性別為“女”，則C3單元格計算退休年齡的公式過程如下：

公式：=60-(B3=”女”)*5

第一步，引用B3單元格的值，結果為：60-(“女”=”女”)*5

第二步，先計算出括號中的值(“女”=”女”)，邏輯值為TRUE，結果為：60-(TRUE)*5

第三步，根據轉換規則，將TRUE轉換為1，結果為：60-1*5

第四步，計算1*5，結果為：60-5

第五步，計算60-5，結果為：55

最終結果和公式見下圖：

本例是利用邏輯值計算不同性別職工的退休年齡，只要理解了，不用其他函數也可以計算。當然，利用邏輯值計算類似的數據，一般只針對簡單的運算，複雜的公式需要用函數處理，後續課程中會針對此類運算作詳細講解。

（二）常用的邏輯函數

在公式計算中，對於邏輯判斷，我們通常會使用邏輯函數OR（“或者”）和AND（“與”或“並且”）進行運算。

其中，AND函數是“與”或“並且”的意思，即所有的條件都滿足時返回TRUE，只要任意一個條件不滿足則返回FALSE；OR函數是“或者”的意思，即在多條件時，滿足任意一個條件或所有條件都滿足返回TRUE，如果一個條件都不滿足則返回FALSE。

在複雜的公式中，為了簡化公式，我們可以用數學運算代替相應的邏輯函數，加法運算代替OR，乘法運算代替AND。

1.用加法運算代替邏輯函數OR

例如：要判斷只有局長和副局長才有審批權限，否則無法審批，此時需要用條件判斷函數IF和邏輯函數OR進行運算。見下圖：

2.用乘法運算代替邏輯函數AND

例如：要判斷學生成績考試語文、數學、英語分數都大於等於60才算及格，否則不及格，此時需要用條件判斷函數IF和邏輯函數AND進行運算。見下圖：

當然，以上只是舉例，並不是說只要一個科目不及格就統統不及格，大家不要糾結。

五、空單元格和空文本的區別

在EXCEL中，默認狀態下，單元格中不顯示任何內容，或將有內容的單元格清空後，此時的單元格即為空單元格。而空文本則是使用一對半角雙引號（””）來表示。

如果某單元格的數據是通過公式引用的空文本，即單元格的值為“=”””，此時，該單元格的顯示效果和空單元格的顯示效果是一樣的，不過，二者在公式運用中有明顯的區別。

（一）兩者的區別

1.從公式的角度看，空單元格和空文本等同於一樣

例如：A1單元格為空單元格，A2單元格為空文本，在A3單元格中輸入公式“=A1=A2”，計算結果為TRUE。

2.空單元格的值視為0

例如：A1單元格為空單元格，在A2單元格中輸入公式“=A1=0”，計算結果為TRUE；在A2單元格中輸入公式“=A1*2”，計算結果為0。

3.空文本的值不能視為0

例如：A1單元格為空文本，在A2單元格中輸入公式“=A1=0”，計算結果為FALSE；在A1單元格中輸入公式“=A1*2”，計算結果為#VALUE錯誤值。

（二）屏蔽空單元格顯示0的方法

在EXCEL中，如果公式的結果是對某個空單元格的引用，公式的計算結果不是空文本，而是數值0，在某些情況下，數值0和空單元格還是有區別的。

例如：下圖所示的是學生的語數外考試成績，學生“姓名三”的數學考試成績為空，我們在對該名學生的數據進行查詢時，在數學列顯示為0，這個0值代表該生的數學成績為0，還是缺考或者漏錄成績呢？

為了區別這兩種情況，避免公式結果引用空單元格時公式結果顯示0的情況，可以在公式末尾使用&運算符強制將引用空單元格的結果轉換為空文本。如下圖：

此時，程序雖然查到了該生的數學成績單元格為空單元格，但沒有顯示0值，而是顯示空文本。

六、處理數據計算的幾種方法

（一）計算公式結果

輸入公式結束後，可通過多種方法計算公式結果，如點擊編輯欄中的“√”（即“輸入”按鈕）顯示公式結果。

此外，還可以通過按快捷鍵的方式計算公式結果，這是最便捷、快速的計算公式結果的方法，具體快捷鍵及計算效果如下：

1.【Enter】鍵：輸入公式後，按【Enter】鍵可在計算出結果的同時選擇該單元格下方的單元格。

2.【Tab】鍵：輸入公式後，按【Tab】鍵可在計算出結果的同時選擇該單元格右側的單元格。

3.【Ctrl+Enter】組合鍵：輸入公式後，按【Ctrl+Enter】組合鍵，在計算出結果的同時仍然保持該單元格的選中狀態，該方法可同時查看數據結果和所使用的公式。

4.【Ctrl+Shift+Enter】組合鍵：如果是數組公式，要計算出結果，必須在輸入公式結束後按【Ctrl+Shift+Enter】組合鍵完成，以上3種方法都不能正確計算公式結果。數組公式不能在合併單元格使用。

（二）使用【F9】鍵將公式轉換為運算結果

【F9】鍵在公式應用中具有公式重算的功能，如果將文本插入點定位到公式中，按【F9】鍵後系統自動將整個公式轉換為運算結果。

例如：未按【F9】鍵前顯示的是公式。見下圖：

當我按下【F9】鍵後，直接顯示公式結果。見下圖：

提示：在使用【F9】鍵將公式轉換為結果後，原來的公式將被替換，如果想改回公式，可以通過撤銷操作將結果還原為公式。

（三）批量輸入和複製公式

1.拖動控制柄複製公式

在EXCEL中，選擇單元格後，其右下角有一個實心的小方塊，我們將其稱之為控制柄，將鼠標光標移動到控制柄上時，鼠標光標會變成黑色十字形狀，通過拖動該控制柄到需要的位置，可完成公式的批量複製。

拖動控制柄批量複製公式後，在結束位置將出現“自動填充選項”按鈕，單擊該按鈕，在彈出的列表中包括“複製單元格”“僅填充格式”“不帶格式填充”和“智能填充”4個單選按鈕。見下圖：

在公式運用中，這4個選項的作用如下：

“複製單元格”：選擇該選項，程序自動將源單元格的公式和單元格格式全部填充到其他單元格。

“僅填充格式”：選擇該選項，程序只將源單元格的格式填充到其他單元格，而不複製源單元格的公式。

“不帶格式填充”：選擇該選項，程序自動將源單元格的公式填充到其他單元格，而不複製源單元格的格式。

“智能填充”：快捷鍵【Ctrl+E】，是一個相當不錯的快捷鍵，可以批量提取身份證號碼中的出生日期，從姓名手機號中提取手機號，輕鬆合併多列數據等。

2.雙擊控制柄複製公式

雙擊控制柄批量複製公式比拖動控制柄複製更加快捷，只需要雙擊包含公式的單元格的控制柄，程序自動向下填充公式到整個表格的結束位置。見下圖：

雙擊後的最終結果見下圖：

需要注意的是，雙擊控制柄完成公式的批量複製時，對數據源的表格結構有一定的條件限制，具體如下：

第一，需要批量複製公式的單元格是在某列單元格上向下填充，不能在某列向上填充，也不能在某行上向右填充。

第二，批量複製公式的單元格相鄰列必須有數據，否則雙擊控制柄無效。

3.用【Ctrl+Enter】組合鍵批量輸入公式

在EXCEL中，如果某一列數據的計算公式相似，那麼可以用【Ctrl+Enter】組合鍵批量錄入公式。首先選擇所有的結果單元格，輸入公式後，按【Ctrl+Enter】組合鍵即可快速錄入選中單元格的公式結果。

例如：要計算每位同學的考試總成績，首先選擇總分列需要計算總分的E3:E8區域，輸入公式“=SUM(B3:D3)”，見下圖：

然後按【Ctrl+Enter】組合鍵即可得出所有學生的總分，如下圖：

4.用【Ctrl+D】和【Ctrl+R】組合鍵批量複製公式

我們在處理表冊數據時，遇到內容相同的單元格，往往採用複製【Ctrl+C】、粘貼【Ctrl+V】的辦法進行，需要按兩次按鍵，雖然省去了輸入，但還是不夠快，對於需要向下複製公式的時候，我們可以使用【Ctrl+D】組合鍵。這個功能其實很實用，因為一般我們都是從上往下填充數據，如果這個單元格的內容同上，就可以直接按【Ctrl+D】組合鍵。

例如：我在E3單元格中輸入的公式，需要把公式向下填充到數據表中的最後一個單元格，只需要選擇該單元格和最後一個單元格的區域，然後按【Ctrl+D】組合鍵即可完成複製。見下圖：