涉及函數(shù)及相關(guān)知識點
| 函數(shù)名 | 格式 | 含義 |
|---|---|---|
| ROW | =row([reference]) | 提取行號古劲，參數(shù)可省略 |
| ROWS | =rows(reference) | 計算行數(shù)恰矩，參數(shù)不可省 |
| COLUMNS | =column([reference]) | 提取列號，參數(shù)可省略 |
| COLUMN | =columns(reference) | 計算列數(shù)熏版，參數(shù)不可省 |
其中參數(shù)為引用區(qū)域，以ROW函數(shù)為例：
若參數(shù)省略黄鳍，則提取當前單元格的行號柠横；若參數(shù)為單元格窃款，則提取單元格的行號；
若參數(shù)為區(qū)域或多行單元格牍氛，需配合數(shù)組使用晨继；
根據(jù)函數(shù)的定義，有以下幾種用法：
1搬俊、 構(gòu)造數(shù)列紊扬，如等差數(shù)列，其拓展可用于制作九九乘法表
2唉擂、 有規(guī)律的單元格引用餐屎，可配合相關(guān)數(shù)組對單元格數(shù)據(jù)有規(guī)律的引用
| 函數(shù)名 | 格式 | 含義 |
|---|---|---|
| IF | =IF(logical_test, value_if_true, value_if_fause) | 條件判斷函數(shù) |
其中l(wèi)ogical_test可為邏輯值或數(shù)字，若為0玩祟，意味著FALSE腹缩，否則意味著TRUE
value_if_fause可省略，此時空扎，若判斷值為FALSE藏鹊，結(jié)果為0
根據(jù)函數(shù)的定義，有以下幾種用法：
1转锈、 條件求平均伙判。在未學(xué)習(xí)AVERAGEIF函數(shù)之前，一般用average結(jié)合if函數(shù)求平均黑忱，由于在判斷過程中宴抚，如果直接用條件值與數(shù)值相乘，會得到數(shù)值甫煞，那么在計算平均值過程中會導(dǎo)致結(jié)果不準確菇曲，所以利用if函數(shù)，把邏輯值為FALSE時的值設(shè)為空抚吠，則average函數(shù)不會將其納入計算范圍常潮。（注意，需要結(jié)合數(shù)組使用）
| 函數(shù)名 | 格式 | 含義 |
|---|---|---|
| IFERROR | =IFERROR(value, value_if_error) | 檢查是否存在錯誤的參數(shù) |
| ISODD | =Isodd(number) | 判斷參數(shù)是不是奇數(shù) |
| ISEVEN | = ISEVEN(number) | 判斷參數(shù)是不是偶數(shù) |
注意：IFERROR函數(shù)中參數(shù)不可省略楷力；ISODD/ ISEVEN的參數(shù)只能為單個數(shù)值喊式，不能為數(shù)組，但可以結(jié)合數(shù)組進行計算萧朝。
參數(shù)類型判斷函數(shù)較多岔留，一般以is開頭，這里不多舉例检柬。
| 函數(shù)名 | 格式 | 含義 |
|---|---|---|
| PRODUCT | =PRODUCT（number1,number2.....） | 用于計算給出的數(shù)字的乘積 |
注意：product函數(shù)中參數(shù)可以用“献联，”或“”連接，且參數(shù)不能為字符（串）或錯誤值，若參數(shù)為邏輯值里逆，且參數(shù)用“进胯，”連接時，需要轉(zhuǎn)換為數(shù)值原押。*
| 函數(shù)名 | 格式 | 含義 |
|---|---|---|
| SUMIF | =SUMIF(range, criteria, [sum_range] ) | 條件求和胁镐，若條件區(qū)域和求和區(qū)域一致，求和區(qū)域可省略 |
| SUMIFS | =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) | 多條件求和 |
| COUNTIF | =countif( range, criteria) | 條件計數(shù) |
| COUNTIFS | =countifs(criteria_range1,criteria1,criteria_range2,criteria2,…) | 多條件計數(shù) |
| AVERAGEIF | AVERAGEIF(range, criteria, [average_range]) | 條件求平均 |
| AVERAGEIFS | =averageifs(average_range,criteria_range1,criteria1,criteria_range2,criteria2,...) | 多條件求平均 |
若條件參數(shù)涉及數(shù)值的單元格引用诸衔，可用&連接使用,本表格的函數(shù)中條件判斷均可使用通配符希停，用法類似。
| 通配符 | 含義 | 備注 |
|---|---|---|
| * | 任何字符串 | |
| ? | 單一字符 | 注意中英文的區(qū)別 |
| ~ | 用于識別通配符自身 |
經(jīng)典案例：
注意：以下案例均來自Scalers成長會2020Excel小組署隘，歡迎來參加Excel小組第二期宠能，具體參加方式見群文件。
案例一：九九乘法表
| 1×1=1 | 2×1=2 | 3×1=3 | 4×1=4 | 5×1=5 | 6×1=6 | 7×1=7 | 8×1=8 | 9×1=9 |
|---|---|---|---|---|---|---|---|---|
| 1×2=2 | 2×2=4 | 3×2=6 | 4×2=8 | 5×2=10 | 6×2=12 | 7×2=14 | 8×2=16 | 9×2=18 |
| 1×3=3 | 2×3=6 | 3×3=9 | 4×3=12 | 5×3=15 | 6×3=18 | 7×3=21 | 8×3=24 | 9×3=27 |
| 1×4=4 | 2×4=8 | 3×4=12 | 4×4=16 | 5×4=20 | 6×4=24 | 7×4=28 | 8×4=32 | 9×4=36 |
| 1×5=5 | 2×5=10 | 3×5=15 | 4×5=20 | 5×5=25 | 6×5=30 | 7×5=35 | 8×5=40 | 9×5=45 |
| 1×6=6 | 2×6=12 | 3×6=18 | 4×6=24 | 5×6=30 | 6×6=36 | 7×6=42 | 8×6=48 | 9×6=54 |
| 1×7=7 | 2×7=14 | 3×7=21 | 4×7=28 | 5×7=35 | 6×7=42 | 7×7=49 | 8×7=56 | 9×7=63 |
| 1×8=8 | 2×8=16 | 3×8=24 | 4×8=32 | 5×8=40 | 6×8=48 | 7×8=56 | 8×8=64 | 9×8=72 |
| 1×9=9 | 2×9=18 | 3×9=27 | 4×9=36 | 5×9=45 | 6×9=54 | 7×9=63 | 8×9=72 | 9×9=81 |
公式一：=CONCATENATE(COLUMN(A:A),"×",ROW(1:1),"=",ROW(1:1)*COLUMN(A:A))
公式二：=CONCATENATE(COLUMN(A:I),"×",ROW(1:9),"=",ROW(1:9)*COLUMN(A:I))
注釋：CONCATENATE為連接函數(shù)磁餐，可用“&”代替
區(qū)別：公式一的結(jié)果為“1×1=1”违崇，其他的算式需經(jīng)過填充，通過改變行號和列號得到不同的算式诊霹；公式二需結(jié)合數(shù)組羞延，可直接一步得到整個九九乘法表
案例二：
總業(yè)績（需結(jié)合數(shù)組使用）
公式一：
=SUM(IF(F12=B3,B4,IF(F12=C3,C4,IF(F12=D3,D4,IF(F12=E3,E4,IF(F12=F3,F4))))):IF(F13=B3,B9,IF(F13=C3,C9,IF(F13=D3,D9,IF(F13=E3,E9,IF(F13=F3,F9))))))
公式二：=SUM(OFFSET(A3:F9,1,MATCH(F12,B3:F3,FALSE),COUNTA(A4:A9),F13-F12+1))
注釋：求和需要選中特定區(qū)域，公式一唯一的要點是用到了引用運算符“：”脾还，盡管平時很常見伴箩，但可能不太容易想到這樣的用法
公式二則是利用offset函數(shù)匹配起始年份和結(jié)束年份所對應(yīng)的區(qū)域。
案例三：
單月業(yè)績
公式一：=SUM(ISODD(ROW(D119:D130))*E119:E130)
公式二：=SUM(IF(ISODD(ROW(D119:D130)),E119:E130,0))
解析：兩個公式基本類似鄙漏，就是變了寫法嗤谚，但是要注意的一點是：isodd函數(shù)的參數(shù)不能直接引用12個月份，會報錯怔蚌；而ROW(D119:D130)雖然結(jié)合數(shù)組計算結(jié)果是{119;120;121;122;123;124;125;126;127;128;129;130}巩步，但并非一次引用多個數(shù)，所以可行
案例四：中國式排名
公式：=SUMPRODUCT((B$20:B$24>=B20)/COUNTIF(B$20:B$24,B$20:B$24))
解析：公式乍一看不是太好理解桦踊，但是用到自帶的【公式求值】功能椅野，就能一步一步觀察其求值過程。
(B24>=B20)是對整體所有分數(shù)進行判斷籍胯，看是否大于等于目標分數(shù)竟闪；
COUNTIF(B24,B
24)則是計算所有分數(shù)重復(fù)的次數(shù)，最后得到{1;1;2;2;1}
“/”使得{1;1;2;2;1}變?yōu)閧1;1;0.5;0.5;1}杖狼，那么大于等于目標分數(shù)的為TRUE炼蛤，與對應(yīng)的數(shù)值相乘并累加后即可得到排名。
這個過程類似于有多種顏色的多個小球混合在一起本刽，通過COUNTIF可求得每個小球?qū)?yīng)顏色的小球個數(shù)鲸湃，“/”使得數(shù)值轉(zhuǎn)化為每個小球占對應(yīng)顏色小球個數(shù)的比，而且這個比值累加得1子寓。
