Excel作為數(shù)據(jù)產(chǎn)品經(jīng)理常用的工具之一，其最強(qiáng)大的功能莫過(guò)于其內(nèi)嵌強(qiáng)大的函數(shù)體系，能夠大大減少重復(fù)人工操作。但作為工具，和SQL一樣，我們對(duì)其學(xué)習(xí)也是遵循二八法則，能夠掌握常見(jiàn)的函數(shù)和常規(guī)的透視表使用即可，而對(duì)于復(fù)雜的函數(shù)和VBA等高級(jí)功能，若有需要我們?cè)龠M(jìn)一步學(xué)習(xí)，本文章會(huì)分享在此課程中學(xué)習(xí)到的關(guān)于Excel常用函數(shù)的功能，個(gè)人認(rèn)為這寫(xiě)功能已經(jīng)能夠滿足工作中80%以上的數(shù)據(jù)問(wèn)題 。但而言之，函數(shù)最總要的是對(duì)問(wèn)題的分析邏輯，理清邏輯關(guān)系是寫(xiě)所有函數(shù)的前提
課程鏈接： https://www.bilibili.com/video/BV11t41177t9?from=search&seid=17417743717554379011

絕對(duì)引用和相對(duì)引用： 在做函數(shù)拖拽式，根據(jù)情況是否固定某單元格內(nèi)容不變，則需要采用絕對(duì)引用和相對(duì)引用，選中某單元格，按F4快捷鍵進(jìn)行絕對(duì)引用，循環(huán)切換
①絕對(duì)引用，兩個(gè)$，比如：$A$1，拖拽單元格，公式相關(guān)的固定A1的值

②相對(duì)引用,無(wú)$，比如：A1，拖拽單元格，A1位置的值對(duì)應(yīng)變化

③混合引用，一個(gè)$，比如：$A1，A$1

簡(jiǎn)單函數(shù)： 簡(jiǎn)單函數(shù)使用時(shí)記得絕對(duì)引用和相對(duì)引用的選擇，特別是rank函數(shù)

跳躍式求和： 分段求和時(shí)，可以用自動(dòng)求和工具+定位工具，實(shí)現(xiàn)跳躍式求和
①選中需求和的多段數(shù)據(jù)
②使用定位工具，定位空值，點(diǎn)擊上方求和按鈕，實(shí)現(xiàn)多段自動(dòng)求和

IF()函數(shù)： 如果logical_test為真，取值為[value_if_true]，否則取值為：[value_if_false] 函數(shù)語(yǔ)法：IF(logical_test,[value_if_true],[value_if_false])
巧用IF嵌套：IF(logical_test,[value_if_true],IF(logical_test,[value_if_true],[value_if_false]))
iserror()函數(shù)： 代表對(duì)值的正確或者錯(cuò)誤的判定，返回TRUE或者FALSE，常用于結(jié)合IF函數(shù)使用進(jìn)行相關(guān)運(yùn)算
舉例：=If(iserror(D35/C35),0,D35/C35),如果D35除以C35是個(gè)錯(cuò)誤值，那么就返回為0，如果不是錯(cuò)誤，那就直接運(yùn)算D35/C35

AND()函數(shù)： 表示“且”的關(guān)系，括號(hào)中可以加多個(gè)條件，舉例：=IF(AND(A3=“男”,B3>=60),1000,0)，當(dāng)中的A3=“男”,B3>=60為且的關(guān)系
OR()函數(shù)： 表示“或”的關(guān)系，括號(hào)中可以加多個(gè)條件，舉例：=IF(OR(B12>=60,B12<40),1000,0)
-----------AND()函數(shù)和OR()的嵌套使用------------

COUNTIF()函數(shù)： Countif(range,criteria)，range表示計(jì)數(shù)范圍，criteria表示計(jì)數(shù)項(xiàng)或計(jì)數(shù)條件
注意：countif()只能統(tǒng)計(jì)前十五位的數(shù)據(jù)，如果一個(gè)數(shù)的長(zhǎng)度超過(guò)15位，則會(huì)自動(dòng)截?cái)?#xff0c;只判斷前15位，解決方案，可以考慮用連接的方式，比如：COUNTIF(A2:A3,A2&"*")連字符加星號(hào)的使用，假如A2=123123123123123123123456

COUNTIF()設(shè)置數(shù)據(jù)有效性： 在數(shù)據(jù)有效性上選擇公式設(shè)置

COUNTIFS()函數(shù)： 實(shí)現(xiàn)多條件計(jì)數(shù) Countifs(ceiteria_range1,criterial1,[ceiteria_range21,criterial3]…)：ceiteria_range1表示第一個(gè)條件區(qū)域，criterial1表示第一個(gè)條件，后面的類推

SUMIF()函數(shù)： 實(shí)現(xiàn)某條件限制下的數(shù)據(jù)求和，和COUNTIF()相似，對(duì)單元格長(zhǎng)度也有限制，也只能處理15位的數(shù)據(jù)，故對(duì)于超出15為的單元格需要用連接處理。
格式：=sumif(range,criteria,[sum_range])

SUMIFS()函數(shù): 實(shí)現(xiàn)多條件求和 格式：=SUMIFS(待求和項(xiàng)，條件區(qū)域1，條件1，條件區(qū)域2，條件2…)

Vlookup函數(shù)(): 實(shí)現(xiàn)查找與引用，支持同表或者跨表匹配，有四個(gè)參數(shù)，待查找項(xiàng)，查找區(qū)域，返回列，模糊或精確查找，工作中基本都是使用精確查找
語(yǔ)法：VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])，
注意：區(qū)域要包含引用列和查找列，且查找列必須是區(qū)域的最左列，要注意是否需要對(duì)數(shù)據(jù)區(qū)域的絕對(duì)引用（如果選中整列則無(wú)需考慮）

Vlookup模糊匹配： 模糊匹配會(huì)默認(rèn)查找小于當(dāng)前查找值的那些數(shù)中的最大值 注意：使用模糊匹配是，進(jìn)行匹配的列必須先進(jìn)行從小到大排序，比如例子中的等級(jí)劃分，里面的值必須從小到大排列好，才能進(jìn)行模糊匹配

查找函數(shù)MATCH()+引用函數(shù)INDEX(): 實(shí)現(xiàn)Vlookup的功能，并解決Vlookup中匹配列必須在引用區(qū)域第一列的限制
match():有三個(gè)參數(shù)（查找目標(biāo)，range，是否精確查找）其中，Match：0-精確匹配；1-小于；-1-大于，返回第一參數(shù)對(duì)應(yīng)的單元格的列號(hào)
index():有兩個(gè)參數(shù)（在哪里拿（范圍），拿第幾個(gè)），返回第二參數(shù)對(duì)應(yīng)的單元格的值

Vlookup嵌套Match： 實(shí)現(xiàn)多列匹配，注意向右和向下拖拽時(shí)的絕對(duì)引用和相對(duì)引用

日期函數(shù)：
①取一個(gè)日期的年份：YEAR()函數(shù) 取一個(gè)日期的月份：MONTH()函數(shù) 取一個(gè)日期的日：DAY()函數(shù)
②組合成一個(gè)日期：Date()函數(shù)

③計(jì)算日期間隔函數(shù)：DatedIF()函數(shù)，格式：Datedif(開(kāi)始日期，結(jié)束日期，類型)
類型可選項(xiàng)："Y"時(shí)間段中的整年數(shù)，"M"時(shí)間段中的整月數(shù)，"D"時(shí)間段中的天數(shù)，"MD"日期中天數(shù)的差，忽略日期中的月和年，"YM"日期中月數(shù)的差，忽略日期中的日和年，"YD"日期中天數(shù)的差。忽略日期中的年

④計(jì)算某個(gè)時(shí)間是第幾周的函數(shù)：Weeknum()函數(shù)，其中第二參數(shù)Return_type取值：
取1時(shí)：表示星期從星期日開(kāi)始，星期內(nèi)的天數(shù)從1 到 7 記數(shù)
取2時(shí):星期從星期一開(kāi)始。星期內(nèi)的天數(shù)從 1 到 7 記數(shù)
⑤計(jì)算某個(gè)時(shí)間是周幾的函數(shù)：WeeknDay()函數(shù)，第二參數(shù)取值和Weeknum()函數(shù)一樣
⑥利用TEXT()函數(shù)：轉(zhuǎn)化自定義格式的時(shí)間顯示

使用函數(shù)條件格式解決跨行條件問(wèn)題： 系統(tǒng)自帶的條件格式一般都是針對(duì)單列單元格的設(shè)置，若涉及到多行單元格之間的條件格式，則需要自定義規(guī)則，采用函數(shù)實(shí)現(xiàn)，要注意絕對(duì)引用和相對(duì)引用的問(wèn)題

文本函數(shù)：
①LEFT()函數(shù)： left(待切分的單元格，從左往右取幾位)
②RIGHT()函數(shù)： rignt(待切分的單元格，從右往左取幾位)
③MID()函數(shù)： mid(帶切分的單元格，從左開(kāi)始從第幾位開(kāi)始取，取幾位)
④FIND()函數(shù)： find(“待查找的字符”，要查找的單元格)，實(shí)現(xiàn)查找某個(gè)字符在單元格是第幾位,find()的第三參數(shù)表示從什么位置開(kāi)始找，一般不會(huì)用到，默認(rèn)找第一個(gè)待查找的字符

⑤LEN()函數(shù)： 表示取某個(gè)單元格的字符數(shù)
LENB()函數(shù)： 表示取某個(gè)單元格的字節(jié)數(shù)

數(shù)學(xué)函數(shù)：
①round()函數(shù): 四舍五入,=round（待處理數(shù)字，四舍五入位數(shù)）,round(1.234,2)=1.23
②roundup() : 直接向上進(jìn)位,roundup(1.1)=2
rounddown(): 直接舍掉,rounddown(1.5)=2
③int()函數(shù): 直接取整,int（待處理數(shù)），處理負(fù)值時(shí)與rounddown有區(qū)別,int(-1.2)=2
④mod()函數(shù): 求余數(shù)
=mod(除數(shù)，被除數(shù))
=mod(除數(shù)，2)判斷奇偶
=mod(除數(shù)，1)得到小數(shù)部分
⑤row()與column()
求單元格的行數(shù)=row（單元格）
求單元格的列數(shù)=column（單元格）
=row（）不填參數(shù)，返回公式所在單元格行數(shù)。
=column（）不填參數(shù)，返回 公式所在單元格列數(shù)

數(shù)組： 利用一列和一個(gè)單元格一一對(duì)比得出TRUE和FALSE,并結(jié)合TRUE=1，FALSE=0的原理進(jìn)行相乘累加，得到全為TRUE的條件下某一列的累加值
注意： ①查看數(shù)組的判斷結(jié)果：條件判斷要切換成顯示出判斷結(jié)果按F9，退出按esc
②數(shù)組最后公式寫(xiě)完后不能直接敲回車鍵，要按住ctrl和shift敲回車鍵會(huì)出現(xiàn)花括號(hào)
③選擇數(shù)組的時(shí)候不能直接選A:A這樣的整列，應(yīng)選擇有內(nèi)容的每個(gè)單元格即 A1：A10，同時(shí)區(qū)域要絕對(duì)引用
④可以使用sumproduct 函數(shù)代替control +shift + enter 的麻煩，相當(dāng)于帶了花括號(hào)結(jié)尾的sum，公式內(nèi)容跟sum 函數(shù)的內(nèi)容一樣

indirect()函數(shù): 間接引用某個(gè)具體單元格的內(nèi)容，=indirect(E14)，a1中的內(nèi)容為"哈哈"，E14中內(nèi)容為a1,indirect間接把單元格類似地址的內(nèi)容翻譯出來(lái)，得到a1對(duì)應(yīng)的內(nèi)容"哈哈"
利用indirect()設(shè)計(jì)二級(jí)下拉菜單：
①先定義二級(jí)下拉全部選項(xiàng)區(qū)域名稱為一級(jí)下拉的名稱
②利用數(shù)據(jù)有效性構(gòu)建一級(jí)下拉選項(xiàng)
③利用數(shù)據(jù)有效性中的自定義函數(shù)，結(jié)合indirect()函數(shù)構(gòu)建二級(jí)下拉選項(xiàng)欄

總結(jié)

以上是生活随笔為你收集整理的数据产品-数据分析和可视化工具Excel函数使用的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。