排名怎麼算excel公式

一、相關概念

(一)函式語法

由函式名+括號+引數組成

例： 求和函式：SUM（A1，B2，…） 。引數與引數之 間用逗號“

,

”隔開

(二)運算子

1。 公式運算子：加（+）、減（-）、乘（*）、除（/）、百分號（%）、乘冪（^）

2。 比較運算子：大與（>）、小於（=） 、不等於（<>）

3。 引用運算子：區域運算子（：）、聯合運算子（，）

(三)單元格的相對引用與絕對引用

例： A1

$A1 鎖定第A列

A$1 鎖定第1行

$A$1 鎖定第A列與第1行

二、常用函式

(一)數學函式

1。 求和 =SUM（數值1，數值2，……）

2。 條件求和 =SUMIF（查詢的範圍，條件（即物件），要求和的範圍）

例：（1）=SUMIF（A1：A4，”>=200”，B1：B4）

函式意思：對第A1欄至A4欄中，大於等於200的數值對應的第B1列至B4列中數值求和

（2）=SUMIF（A1：A4，”<300”，C1：C4）

函式意思：對第A1欄至A4欄中，小於300的數值對應的第C1欄至C4欄中數值求和

3。 求個數 =COUNT（數值1，數值2，……）

例：（1） =COUNT（A1：A4） 函式意思：第A1欄至A4欄求個數

（2） =COUNT（A1：C4） 函式意思：第A1欄至C4欄求個數

4。 條件求個數 =COUNTIF（範圍，條件）

例：（1） =COUNTIF（A1：A4，”<>200”）

函式意思：第A1欄至A4欄中不等於200的欄求個數

（2） =COUNTIF（A1：C4，”>=1000”）

函式意思：第A1欄至C4欄中大於等1000的欄求個數

5。 求算術平均數 =AVERAGE（數值1，數值2，……）

例：（1） =AVERAGE（A1，B2）

（2） =AVERAGE（A1：A4）

6。 四捨五入函式 =ROUND（數值，保留的小數位數）

7。 排位函式 =RANK（數值，範圍，序別） 1-升序 0-降序

例：（1） =RANK（A1，A1：A4，1）

函式意思：第A1欄在A1欄至A4欄中按升序排序，返回排名值。

（2） =RANK（A1，A1：A4，0）

函式意思：第A1欄在A1欄至A4欄中按降序排序，返回排名值。

8。 乘積函式 =PRODUCT（數值1，數值2，……）

9。 取絕對值 =ABS（數字）

10。 取整 =INT（數字）

(二)邏輯函式

條件函式：=IF（條件，執行條件，執行條件假）-可執行七層巢狀

例： A1=5 A2=3 A3=6 A4=7

=IF（A1>A2，1，0）=1（真）

=IF（A1

=IF（A1>A2，IF（A3>A4，8，9），1）=9

(三)文字函式

1。 擷取函式： =LEFT（文字，擷取長度）-從左邊擷取

=RIGHT（文字，擷取長度）-從右邊擷取

=MID（文字，開始位，擷取長度）-從中間擷取

2。 計算字元長度：LEN（文字）-文字的長度

3。 合併字元函式：CONCATENATE（文字1，文字2，…）

4。 在字串中查詢特定字元：FIND（文字，範圍，數值）-數值表示查詢第幾個

=FIND（“a”，“abcadeafga”，2）=4

=FIND（“a”，“abcadeafga”，3）=7

=FIND（“a”，“abcadeafga”，4）=10

5。 比較兩個字元是否完全相符：EXACT（文字1，文字2）

=EXACT（“a”，“a”）=TRUE

=EXACT（“a”，“ab”）=FALSE

=EXACT（“a”，“A”）=FALSE

6。 將數值轉化為文字：TEXT（數值，引數） ）-引數一般為0

7。 將數值型字元轉換為數值：VALUE（數值文字）

(四)判斷公式

1。 把公式產生的錯誤顯示為空

公式：C2=IFERROR（A2/B2，””）

說明：如果是錯誤值則顯示為空，否則正常顯示。

2。 IF多條件判斷返回值

公式：C2=IF（AND（A2<500，B2=”未到期”），”補款”，””）

說明：兩個條件同時成立用AND，任一個成立用OR函式。

(五)統計公式

1。 統計兩個表格重複的內容

公式：B2=COUNTIF（Sheet15！A：A，A2）

說明：如果返回值大於0說明在另一個表中存在，0則不存在。

2。 統計不重複的總人數

公式：C2=SUMPRODUCT（1/COUNTIF（A2：A8，A2：A8））

說明：用COUNTIF統計出每人的出現次數，用1除的方式把出現次數變成分母，然後相加。

（六）求和公式

1。 隔列求和

公式：H3=SUMIF（$A$2： $G$2，H$2，A3：G3）

或者： =SUMPRODUCT（（MOD（COLUMN（B3：G3），2）=0）*B3：G3）

說明：如果標題行沒有規則用第2個公式

2。 單條件求和

3。 單條件模糊求和

4。 多條件模糊求和

5。 多表相同位置求和

公式：B2=SUM（Sheet1：Sheet19！B2）

說明：在表中間刪除或新增表後，公式結果會自動更新。

6。 按日期和產品求和

（七）查詢與引用公式

1。 單條件查詢公式

公式1： C11=VLOOKUP（B11，B3：F7，4，FALSE）

說明：查詢是VLOOKUP最擅長的，基本用法

2。 雙向查詢公式

公式=INDEX（C3：H7，MATCH（B10，B3：B7，0），MATCH（C10，C2：H2，0））

說明：利用MATCH函式查詢位置，用INDEX函式取值

3。 查詢最後一條符合條件的記錄。

公式：詳見下圖

說明：0/（條件）可以把不符合條件的變成錯誤值，而lookup可以忽略錯誤值

4。 多條件查詢

公式：詳見下圖

說明：公式原理同上一個公式

5。 指定區域最後一個非空值查詢

6。 按數字區域間取對應的值