作者|盧子
一起來看看這28個公式，你還記得幾個？
1.
根據成績的比重，獲取學期成績。
=C8*$C$5+D8*$D$5+E8*$E$5
引用方式有絕對引用、混合引用、相對引用，可以藉助F4鍵快速切換。
如果學了SUMPRODUCT函式，也可以換種方式。
=SUMPRODUCT（C8：E8，$C$5：$E$5）
2.
根據成績的區間判斷，獲取等級。
=IF（B5>=90，“優秀”，IF（B5>=80，“良”，“及格”））
IF函式語法：
=IF（條件，條件為真返回值，條件為假返回值）
IF函式圖解
3.
重量±5以內為合格，否則不合格。
=IF（AND（A4>=-5，A4
=IF（ABS（A4）
AND函式當所有條件都滿足的時候返回TRUE，否則返回FALSE。
ABS函式是返回數字的絕對值。
4.
判斷三個單元格是否滿足條件。
5.
一圖看懂SUM、AVERAGE、COUNT、MAX、MIN、RANK 6個常用函式用法。
6.
根據對應表，查詢2月銷量。
=VLOOKUP（A4，F：G，2，0）
VLOOKUP函式語法：
=VLOOKUP（查詢值，在哪個區域查詢，返回區域第幾列，精確或模糊匹配）
第4引數為0時為精確匹配，1時為模糊匹配。
VLOOKUP函式圖解
7.
根據番號查詢品名和型號。
=VLOOKUP（$A4，$E：$G，2，0）
=VLOOKUP（$A4，$E：$G，3，0）
正常情況下可以用VLOOKUP函式，然後將引數3分別設定為2和3，不過考慮到列數可能比較多，也就是通用的情況下，所以用COLUMN函式作為第3引數。這個函式是獲取列號，B1的列號就是2，C1的列號就是3，依次類推。
=VLOOKUP（$A4，$E：$G，COLUMN（B1），0）
8.
根據員工姓名查詢員工號。
9.
根據姓名查詢在資料來源中的排位。
10.
根據銷售員，反向查詢產品名稱。
11.
提取字元：擷取A2單元格第九位以後字元。
12.
今天的日期是？今天是星期幾？如何快速獲取這些資訊呢？
13.
獲取銷售額前N大跟後N小。
14.
認識D字頭函式。
15.
獲取總金額，也就是數量*單價後再求和。
16.
正確顯示文字+日期的組合。
=A4&TEXT（B4，“！_yyyy-m-d”）
=A4&TEXT（B4，“！_e-m-d”）
&的作用就是將兩個內容合併起來，不過遇到日期，合併後日期就變成數字。有日期存在的情況下要藉助TEXT函式，顯示年月日的形式用yyyy-m-d，4位數的年份也可以用e代替。這裡新增_是為了防止以後有需要處理，可以藉助這個分隔符號分開，因為是特殊字元前面加！強制顯示。
17.
計算收入大於3萬的人的累計收入總和。
=SUMIF（C：C，“>30000”， C：C）
SUMIF函式語法：
=SUMIF（條件區域，條件，求和區域）
對區域進行條件求和。
18.
序列號為102開頭的累計收入總和。
=SUMIF（A：A，“102*”，C：C）
萬用字元號有2個，一個是*代表全部，102開頭就是102*，如果是包含102用*102*。另一個萬用字元是？代表一個字元，比如現在有3個字元，就用？？？。
說明：萬用字元只能針對文字格式進行處理，數字格式的序列號不可以用。
19.
統計每一種水果的購買次數和統計每一種水果運費大於20元的次數。
=COUNTIF（B：B，G5）
=COUNTIFS（B：B，G14，E：E，“>20”）
COUNTIF函式語法：
=COUNTIF（條件區域，條件）
COUNTIFS函式語法：
=COUNTIFS（條件區域1，條件1，條件區域2，條件2……）
COUNTIF（COUNTIFS）對區域進行條件計數，有S可以多條件計數。
20.
寶貝標題包括耳釘，就返回首飾，否則為其他。
=IF（COUNTIF（A4，“*耳釘*”），“首飾”，“其他”）
=IF（ISERROR（FIND（“耳釘”，A4）），“其他”，“首飾”）
根據SUMIF函式支援萬用字元的特點，COUNTIF函式也支援，包含就用*耳釘*。
當然也能借助FIND函式判斷，如果有出現就返回數字，否則返回錯誤值，而ISERROR函式就是判斷是否為錯誤值。
21.
根據身份證號碼，獲取性別、生日、週歲。
性別：從15位提取3位，如果奇數就是男，偶數就是女。
=IF（MOD（MID（A4，15，3），2），“男”，“女”）
MOD函式就是取餘數的意思，奇數除以2的餘數就是1，偶數除以2的餘數就是0。1在這裡相當於TRUE也就是返回男，0就是FALSE返回女。
高版本中用ISODD函式判斷是不是奇數，用ISEVEN函式判斷是不是偶數，所有也可以將公式改成高版本的。
=IF（ISODD（MID（A2，15，3）），“男”，“女”）
生日：從第7位提取8位，設定公式後將單元格設定為日期格式。
=——TEXT（MID（A4，7，8），“0-00-00”）
週歲：
=DATEDIF（D4，TODAY（），“y”）
TODAY也可以換成NOW。
22.
把歌曲和作者合併到一個單元格。
=A4&“-”&B4
&就是將字元連線起來，叫連字元。
23.
將字串合併成一個單元格。
=PHONETIC（A4：K4）
PHONETIC這是一個很神奇的文字合併函式，可以輕鬆將內容合併起來，不過只針對文字，切記！
24.
根據產品名稱和城市查詢銷售額。
=VLOOKUP（G4，$A$3：$E$9，MATCH（H4，$A$3：$E$3，0），0）
=SUMPRODUCT（（$A$4：$A$9=G4）*（$B$3：$E$3=H4）*$B$4：$E$9）
25.
品牌月度銷售額查詢。
=SUMIFS（C：C，A：A，E4，B：B，F4）
26.
分別提取產品和編碼。
=LEFT（A4，LENB（A4）-LEN（A4））
=RIGHT（A4，2*LEN（A4）-LENB（A4））
27.
從起始時間提取日期和時間。
=——LEFT（A4，FIND（“ ”，A4）-1）
=——RIGHT（A4，LEN（A4）-FIND（“ ”，A4））
28.
將省份（區）和城市分離出來。
=LEFT（A4，FIND（IF（ISNUMBER（FIND（“區”，A4）），“區”，“省”），A4））
=RIGHT（A4，LEN（A4）-LEN（B4））
文章編輯：Blean
