數(shù)據(jù)分析常用的Excel函數(shù)

參考資料：
七周成為數(shù)據(jù)分析師
知乎 | 怎樣快速掌握 VLookup？
【訓(xùn)練營(yíng)】職場(chǎng)Excel零基礎(chǔ)入門

Excel常用函數(shù)

簡(jiǎn)介

什么是函數(shù)

可以把函數(shù)理解為一個(gè)可以控制的黑箱子，輸入X到黑箱子中，他就會(huì)輸出Y，參數(shù)就是黑箱子的控制開關(guān)，打到不同的檔位，黑箱子會(huì)輸出不同的Y。

函數(shù)示意圖

常見函數(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)
清除A1單元格左右的空格

合并單元格

CONCATENATE

將幾個(gè)文本字符串合并為一個(gè)文本字符串。

=CONCATENATE(text1, text2, ...)
合并字符串以及單元格內(nèi)容

截取字符串

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)

例子

在A2中從左開始提取2個(gè)字符

在A1中從右開始提取2個(gè)字符
在A1中，從位置3開始，提取2個(gè)字符

替換單元格中內(nèi)容

替換指定位置：REPLACE

從“原字符串”的“開始位置”開始，選擇“字符個(gè)數(shù)”個(gè)，替換為“新字符串”

=REPLACE(原字符串, 開始位置, 字符個(gè)數(shù), 新字符串)

例子

從A1的位置1開始，選取4個(gè)字符串，替換為新的字符串“2018”。

將2019替換為2018

替換指定文本：SUBSTITUTE

在text中用new_text替換old_text，instance_num指定要替換第幾次出現(xiàn)的old_text，如果不指定則替換old_text。

=SUBSTITUTE(text, old_text, new_text, instance_num)

例子

用“k”替換A1中第二次出現(xiàn)的“應(yīng)屆”

查找文本在單元格中的位置

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ū)分大小寫。

例子

從A1中查找k，并返回第一個(gè)k的起始位置編號(hào)

關(guān)聯(lián)匹配函數(shù)

VLOOKUP

VLOOKUP 函數(shù)簡(jiǎn)介

VLOOKUP 函數(shù)總共有4個(gè)參數(shù)，分別是：用誰去找、匹配對(duì)象范圍、返回第幾列、匹配方式（0/FALSE表示精確匹配，1/TRUE表示模糊匹配）。

vlookup簡(jiǎn)介

四種查詢方式

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列，也就是說如果是根據(jù)“姓名”檢索，那么檢索區(qū)域應(yīng)該從B列開始。

2.反向查找

當(dāng)檢索關(guān)鍵字不在檢索區(qū)域的第1列，可以使用虛擬數(shù)組公式IF來做一個(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之外，其他和單條件查找沒有區(qū)別。

3.多條件查詢

在匹配數(shù)據(jù)時(shí)，往往條件不是單一的，那么就可以利用&將字段拼接起來，并且利用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(用誰去找, 匹配對(duì)象范圍, 返回第幾行, 匹配方式)

和VLOOKUP的區(qū)別：HLOOKUP返回的值與查找的值在同一列上，而VLOOKUP返回的值與查找的值在同一行上。

INDEX

返回?cái)?shù)組array中指定索引的單元格的值。

=INDEX(array, Row_num, Column_num)
返回指定區(qū)域第2行第2列的單元格內(nèi)容

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])
查找A1到A4中6的位置

Index & Match聯(lián)合使用 = VLookup

ROW & COLUMN

ROW：返回指定引用的行號(hào)；
COLUMN：返回指定引用的列號(hào)。

=ROW(reference)
=COLUMN(reference)

例子

求C列為第幾列

OFFSET

OFFSET：以指定的引用reference為起點(diǎn)，按照偏移量偏移之后，返回值。
rows：向下偏移多少行；
columns：向右偏移多少列；
height：返回多少行；
width：返回多少列。

=OFFSET(reference, rows, columns, height, width)

例子

A1向下偏移一行，向右偏移一列

計(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ù)：

A1到A3中大于等于10的數(shù)量

多條件計(jì)數(shù)：


A1到A3中大于5小于10的數(shù)量

求和

SUM

功能：計(jì)算單元格區(qū)域中所有數(shù)值的和。

SUMIF

功能：求滿足條件的單元格的和。

=SUMIF(條件判斷區(qū)域, 判斷條件, 求和區(qū)域)
計(jì)算一班的總成績(jī)

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)

例子

向下取整

0表示精確到個(gè)位數(shù)

排序：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ù)的匯總，通過輸入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))
DAY

DATE

將year，month，day組合成一個(gè)日期，相當(dāng)于這三個(gè)函數(shù)的逆操作。

=DATE(year, month, day)
DATE

WEEKDAY WEEKNUM

WEEKDAY：根據(jù)一個(gè)日期是星期幾來返回一個(gè)數(shù)字。
return_type：設(shè)置返回?cái)?shù)字的規(guī)則。

WEEKNUM：根據(jù)一個(gè)日期是今年的第幾周來返回一個(gè)數(shù)字。
return_type：

=WEEKDAY(serial_number, return_type)
=WEEKNUM(serial_number, return_type)

例子

2019/2/28屬于第九周星期四。

WEEKDAY

WEEKNUM

NOW TODAY

返回當(dāng)前的時(shí)間，now精確到時(shí)間，today只精確到日期。

=NOW()
=TODAY()
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

友情鏈接更多精彩內(nèi)容