參考資料：
七周成為數(shù)據(jù)分析師
知乎 | 怎樣快速掌握 VLookup啤呼？
【訓(xùn)練營(yíng)】職場(chǎng)Excel零基礎(chǔ)入門
簡(jiǎn)介
什么是函數(shù)
可以把函數(shù)理解為一個(gè)可以控制的黑箱子烹困，輸入X到黑箱子中谍肤，他就會(huì)輸出Y荞下，參數(shù)就是黑箱子的控制開關(guān)傍衡，打到不同的檔位蠢壹，黑箱子會(huì)輸出不同的Y超升。
常見函數(shù)分類
- 文本清洗函數(shù)
- 關(guān)聯(lián)匹配函數(shù)
- 邏輯運(yùn)算函數(shù)
- 計(jì)算統(tǒng)計(jì)函數(shù)
- 時(shí)間序列函數(shù)
文本清洗函數(shù)
常用的文本清洗函數(shù)
- 清除字符串空格：
TRIM - 合并單元格：
CONCATENATE - 截取字符串：
LEFT/RIGHT/MID - 替換單元格中的內(nèi)容：
REPLACE/SUBSTITUTE - 查找文本在單元格中的位置：
FIND/SEARCH
清除字符串空格
TRIM
清除字符串text左右的空格入宦。
=TRIM(text)
合并單元格
CONCATENATE
將幾個(gè)文本字符串合并為一個(gè)文本字符串。
=CONCATENATE(text1, text2, ...)
截取字符串
LEFT：從text中室琢，提取num_chars個(gè)字符（從左開始）乾闰。
=LEFT(text, num_cahrs)
RIGHT：從text中，提取num_chars個(gè)字符（從右開始）盈滴。
=RIGHT(text, num_chars)
MID：從text中涯肩，從stat_num開始，提取num_chars個(gè)字符串。
=MID(text, start_num, num_chars)
例子
替換單元格中內(nèi)容
替換指定位置：REPLACE
從“原字符串”的“開始位置”開始，選擇“字符個(gè)數(shù)”個(gè)硫朦，替換為“新字符串”
=REPLACE(原字符串, 開始位置, 字符個(gè)數(shù), 新字符串)
例子
從A1的位置1開始贷腕，選取4個(gè)字符串，替換為新的字符串“2018”咬展。
替換指定文本：SUBSTITUTE
在text中用new_text替換old_text花履，instance_num指定要替換第幾次出現(xiàn)的old_text，如果不指定則替換old_text挚赊。
=SUBSTITUTE(text, old_text, new_text, instance_num)
例子
查找文本在單元格中的位置
FIND & SEARCH
從within_test中查找FIND_text诡壁，返回查找字符的起始位置編號(hào)。
=FIND(FIND_text, within_text, start_num)
=SEARCH(要查找字符, 字符所在的文本, 從第幾個(gè)字符開始查找)
FIND 和 SEARCH 兩個(gè)函數(shù)幾乎相同荠割，區(qū)別在于FIND 精確查找妹卿，區(qū)分大小寫； SEARCH 模糊查找蔑鹦，不區(qū)分大小寫夺克。
例子
關(guān)聯(lián)匹配函數(shù)
VLOOKUP
VLOOKUP 函數(shù)簡(jiǎn)介
VLOOKUP 函數(shù)總共有4個(gè)參數(shù)嚎朽，分別是：用誰(shuí)去找铺纽、匹配對(duì)象范圍、返回第幾列哟忍、匹配方式（0/FALSE表示精確匹配狡门，1/TRUE表示模糊匹配）。
四種查詢方式
1.單條件查找
根據(jù)工號(hào)锅很，將左邊檢索區(qū)域的“電腦銷售額”匹配到右邊對(duì)應(yīng)位置其馏，只需要使用VLOOKUP函數(shù)，結(jié)果存在則顯示對(duì)應(yīng)的“電腦銷售額”爆安；結(jié)果不存在則顯示#N/A叛复。
=VLOOKUP(F2,$A$2:$D$55,4,0)
注意：檢索關(guān)鍵字必須在檢索區(qū)域的第1列，也就是說(shuō)如果是根據(jù)“姓名”檢索扔仓，那么檢索區(qū)域應(yīng)該從B列開始褐奥。
2.反向查找
當(dāng)檢索關(guān)鍵字不在檢索區(qū)域的第1列，可以使用虛擬數(shù)組公式IF來(lái)做一個(gè)調(diào)換翘簇。
=VLOOKUP(G2,IF({1,0},B2:B8,A2:A8),2,0)
反向查找的固定公式用法：
=VLOOKUP(檢索關(guān)鍵字撬码，IF({1,0},檢索關(guān)鍵字所在列，查找值所在列)缘揪，2耍群，0)
注意：其實(shí)反向查找除了檢索區(qū)域改成一個(gè)虛擬數(shù)組公式IF之外义桂，其他和單條件查找沒(méi)有區(qū)別找筝。
3.多條件查詢
在匹配數(shù)據(jù)時(shí)蹈垢，往往條件不是單一的，那么就可以利用&將字段拼接起來(lái)袖裕，并且利用IF數(shù)組公式構(gòu)建出一個(gè)虛擬的區(qū)域曹抬。
=VLOOKUP(F2&G2,IF({1,0},A2:A53&B2:B53,D2:D53),2,0)
注意事項(xiàng)，所有使用了數(shù)組的公式急鳄，不能直接回車谤民，需要使用Ctrl+Shift+Enter，否則會(huì)出錯(cuò)疾宏。
4.查詢返回多列
查找返回多列需要用到另外一個(gè)輔助函數(shù)——COLUMN函數(shù)张足。
返回結(jié)果為單元格引用的列數(shù)。
例如：column(B1)返回值為2坎藐，因?yàn)锽1為第2列为牍。
=COLUMN(待查詢單元格/區(qū)域)
需要注意的是第三個(gè)參數(shù)“返回第幾列”的寫法。
=VLOOKUP($G2,$A$2:$E$55,COLUMN(D1),0)
返回多列的固定公式用法：
=VLOOKUP(混合引用關(guān)鍵字岩馍，查找范圍碉咆，COLUMN(xx)，0)
返回第幾列就用COLUMN函數(shù)引用第幾列的單元格即可蛀恩。
HLOOKUP
=HLOOKUP(用誰(shuí)去找, 匹配對(duì)象范圍, 返回第幾行, 匹配方式)
和VLOOKUP的區(qū)別：HLOOKUP返回的值與查找的值在同一列上疫铜，而VLOOKUP返回的值與查找的值在同一行上。
INDEX
返回?cái)?shù)組array中指定索引的單元格的值双谆。
=INDEX(array, Row_num, Column_num)
MATCH
功能：在區(qū)域內(nèi)查找指定的值壳咕，返回第一個(gè)查找值的位置。
lookup_value：需要查找的值顽馋；
lookup_array：查找的區(qū)域囱井；
match_type：-1、0或1趣避，0表示查找等于lookup_value的值庞呕。
=MATCH(lookup_value, lookup_array, [match_type])
Index & Match聯(lián)合使用 = VLookup
ROW & COLUMN
ROW：返回指定引用的行號(hào)；
COLUMN：返回指定引用的列號(hào)程帕。
=ROW(reference)
=COLUMN(reference)
例子
OFFSET
OFFSET：以指定的引用reference為起點(diǎn)住练，按照偏移量偏移之后，返回值愁拭。
rows：向下偏移多少行讲逛；
columns：向右偏移多少列；
height：返回多少行岭埠；
width：返回多少列盏混。
=OFFSET(reference, rows, columns, height, width)
例子
計(jì)算返回的兩行兩列的和，如果不求和许赃，則會(huì)報(bào)錯(cuò)止喷，因?yàn)橐粋€(gè)cell不能填充四個(gè)cell的內(nèi)容。
HYPERLINK
HYPERLINK：創(chuàng)建一個(gè)超鏈接指向link_location混聊，以friendly_name的字符串進(jìn)行顯示弹谁，link_location可以是URL鏈接或文件路徑。
=HYPERLINK(link_location, friendly_name)
邏輯運(yùn)算函數(shù)
一般用于條件運(yùn)算句喜，在Excel中预愤，True代表數(shù)值1，F(xiàn)alse代表0咳胃。
IF
如果滿足判斷條件植康，則返回“真值”，否則返回“假值”展懈。
=IF(判斷條件, 真值, 假值)
=IF(AND(條件1, 條件2), 真值, 假值)
=IF(OR(條件1, 條件2), 真值, 假值)
例子
計(jì)算統(tǒng)計(jì)函數(shù)
求最值
MAX MIN
MAX：求某區(qū)域中的最大值销睁；
MIN：求某區(qū)域中的最小值。
求數(shù)目
COUNT COUNTIF COUNTIFS
COUNT：計(jì)數(shù)标沪。
COUNTIF：?jiǎn)螚l件計(jì)數(shù)榄攀。
COUNTIFS：多條件計(jì)數(shù)。
=COUNTIF(區(qū)域, 條件)
=COUNTIFS(區(qū)域1, 條件1, [區(qū)域2, 條件2], ...)
例子
單條件計(jì)數(shù)：
多條件計(jì)數(shù)：
求和
SUM
功能：計(jì)算單元格區(qū)域中所有數(shù)值的和金句。
SUMIF
功能：求滿足條件的單元格的和檩赢。
=SUMIF(條件判斷區(qū)域, 判斷條件, 求和區(qū)域)
SUMPRODUCT
將數(shù)組間的對(duì)應(yīng)元素相乘，并返回乘積之和违寞。
=SUMPRODUCT(array1, array2, ......)
例子
如果只選取一列贞瞒，和SUM一樣只是求和。
只選一列
選取多列趁曼，就會(huì)返回對(duì)應(yīng)元素乘積之和军浆。
取整
INT ROUND
INT：向下取整；
ROUND：四舍五入取整挡闰，num_digits指定精確到哪一位乒融。
=INT(number)
=ROUND(number, num_digits)
例子
排序：RANK
功能：計(jì)算number在reference中排名。
order：0或默認(rèn)摄悯，則為降序排列赞季；其他數(shù)值則為升序排列。
=RANK(number, reference, order)
描述統(tǒng)計(jì)
AVERAGEA
求算數(shù)平均值奢驯。
QUARTILE
求分位數(shù)申钩。
STDEV
求標(biāo)準(zhǔn)差。
SUBTOTAL
該函數(shù)相當(dāng)于以上幾個(gè)函數(shù)的匯總瘪阁，通過(guò)輸入function_num參數(shù)撒遣，選擇不同的函數(shù)邮偎。
=SUBTOTAL(function_num, ref1, ref2, ...)
時(shí)間序列函數(shù)
時(shí)間的本質(zhì)是數(shù)字。
YEAR MONTH DAY
分別返回日期序號(hào)的年义黎、月禾进、日。
=YEAR(日期序號(hào))
=MONTH(日期序號(hào))
=DAY(日期序號(hào))
DATE
將year轩缤，month命迈，day組合成一個(gè)日期贩绕，相當(dāng)于這三個(gè)函數(shù)的逆操作火的。
=DATE(year, month, day)
WEEKDAY WEEKNUM
WEEKDAY：根據(jù)一個(gè)日期是星期幾來(lái)返回一個(gè)數(shù)字。
return_type：設(shè)置返回?cái)?shù)字的規(guī)則淑倾。
WEEKNUM：根據(jù)一個(gè)日期是今年的第幾周來(lái)返回一個(gè)數(shù)字馏鹤。
return_type：
=WEEKDAY(serial_number, return_type)
=WEEKNUM(serial_number, return_type)
例子
2019/2/28屬于第九周星期四。
NOW TODAY
返回當(dāng)前的時(shí)間娇哆，now精確到時(shí)間湃累，today只精確到日期。
=NOW()
=TODAY()
