Day 16? ? 2019.01.25
查找函數(shù)（Vlookup、Hlookup、Index、Match）
日期函數(shù)（Year、Month、Datedif）
今天的豎屏終于做出來(lái)，也用PPT制作了一個(gè)視頻，因時(shí)間關(guān)系沒(méi)錄旁白，有時(shí)間再修正啦～
（一）查找函數(shù)（Vlookup、Hlookup、Index、Match）
一、基本用法
=VLOOKUP(D2,$A$2:$B$12,2,0)
參數(shù)解釋：
=VLOOKUP(要查找的銷售員,包含銷售員和其銷售額的數(shù)據(jù)源區(qū)域,找到后返回第2列,精確查找)
第一參數(shù)：找什么（或者說(shuō)按什么查找），按銷售員查找，所以輸入D2
第二參數(shù)：在哪找，數(shù)據(jù)源區(qū)域在A:B列，所以輸入$A$2:$B$12
第三參數(shù)：找到后返回第幾列，我們要查找的是銷售額，銷售額位于B列，即第二參數(shù)中的第二列，所以輸入2
第四參數(shù)：這里要精確查找，所以輸入0
參數(shù)解釋：
=HLOOKUP(G2,$A$1:$E$9,6,0)
=HLOOKUP(要查找的月份,包含銷售員和其月份的數(shù)據(jù)源區(qū)域,找到后返回第6行,精確查找)
第一參數(shù)：找什么（或者說(shuō)按什么查找），按月份查找，所以輸入G2
第二參數(shù)：在哪找，數(shù)據(jù)源區(qū)域在A:E列，所以輸入$A$1:$E$9
第三參數(shù)：找到后返回第幾行，我們要查找的是獨(dú)孤求敗，位于第6行，即第二參數(shù)中的第6行，所以輸入6
第四參數(shù)：這里要精確查找，所以輸入0
MATCH函數(shù)
作用:在一個(gè)區(qū)域或數(shù)組中查找指定數(shù)值的位置，如果查找不到則返回錯(cuò)誤值
=MATCH(A13,$A$2:$A$8,0)
第一參數(shù)：查找的值
第二參數(shù)：查找的區(qū)域or數(shù)組
第三參數(shù)：0和FALSE表示精確，1和TRUE表示模糊
INDEX函數(shù)
作用：根據(jù)指定的行數(shù)和列數(shù)，返回指定區(qū)域的值。
①? ? ? =INDEX($A$2:$A$8,B14)
第一參數(shù)：指定的區(qū)域
第二參數(shù)：數(shù)值所在的位置
②? ? ? =INDEX($A$2:$C$8,4,2)
第一參數(shù)：指定的區(qū)域
第二參數(shù)：行數(shù)
第三參數(shù)：列數(shù)
③? ? ? ? =INDEX($A$2:$A$8,MATCH(A36,$B$2:$B$8,0))
綜合運(yùn)用
作用：反向查詢
利用Match函數(shù)查找位置（$B$2:$B$8）
二、借助名稱框查找
=VLOOKUP(D2,查找區(qū)域,2,0)
注意：利用名稱框抓取數(shù)據(jù)區(qū)域，就不用在手工去選擇區(qū)域
三、通配符模糊查找
=VLOOKUP("*老師*",$A$2:$B$12,2,0)
說(shuō)明：
通配符星號(hào)*通配任意個(gè)字符
問(wèn)號(hào)?通配單一字符
四、查找返回多列數(shù)據(jù)
=COLUMN()? 例：G列? =COLUMN()? 結(jié)果是 7
=COLUMN(B1)? 例：結(jié)果是 2
=VLOOKUP($G2,$A$2:$E$9,COLUMN(B1),0)
要求：
左側(cè)表格是數(shù)據(jù)源區(qū)域，需要在右側(cè)區(qū)域根據(jù)姓名調(diào)取對(duì)應(yīng)的數(shù)據(jù)
操作步驟：
選中H2:K5單元格區(qū)域，輸入以下公式=VLOOKUP($G2,$A$2:$E$9,COLUMN(B1),0)后按<Ctrl+Enter>組合鍵
五、查找指定區(qū)域
=VLOOKUP($G2,$A$2:$E$9,MATCH($H$1,$A$1:$E$1,0),0)
要求：
左側(cè)表格是數(shù)據(jù)源區(qū)域，需要在右側(cè)區(qū)域根據(jù)姓名調(diào)取對(duì)應(yīng)的數(shù)據(jù)，黃色區(qū)域是需要填寫公式的區(qū)域。
六、多條件查找（一對(duì)多查找）
①? ? ? 需要借助輔助列 =C2&COUNTIF($C$2:C2,C2) 設(shè)產(chǎn)品序列
②? ? ? 依據(jù)產(chǎn)品序列查找
=IFERROR(VLOOKUP($F$2&ROW(A1),$A$2:$D$15,COLUMN(B1),0),"")
說(shuō)明：
=ROW(F1)? 例：結(jié)果是 1
=COLUMN(B2)? 例：結(jié)果是 2
操作步驟：
步驟1 在左表前插入一列并設(shè)置公式，用countif函數(shù)統(tǒng)計(jì)客戶的銷售額并用&連接成 產(chǎn)品名稱+序號(hào)的形式。=C2&COUNTIF($C$2:C2,C2)
步驟2 在F9設(shè)置公式并復(fù)制即可得到F2單元格中產(chǎn)品的所有銷售記錄。
=IFERROR(VLOOKUP($F$2&ROW(A1),$A:$D,COLUMN(B1),0),"")
解決一對(duì)多查找的關(guān)鍵點(diǎn)是把重復(fù)的值用Countif變成不同的，然后用Vlookup函數(shù)再進(jìn)行查找
七、區(qū)間查找
=VLOOKUP(B2,$I$2:$J$5,2)
要求: 需要按照等級(jí)劃分規(guī)則，將成績(jī)劃分到其對(duì)應(yīng)的等級(jí)中。
說(shuō)明:
=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=60,"C","D")))
=IF(B2<60,"D",IF(B2<80,"C",IF(B2<90,"B","A")))
注意:
必須是升序排列
八、動(dòng)態(tài)圖表
=C9&" 銷售額"? 要查找的內(nèi)容
=VLOOKUP($C$9,$A$2:$G$7,COLUMN(),0)? 用vlookup查找一整行數(shù)值，即可
（二）? 日期函數(shù)（Year、Month、Datedif）
一、基本用法
=TODAY()? ? ? 2019/1/25? ? ? 當(dāng)天日期
=NOW()? ? ? ? ? 2019/1/25 16:31? ? ? 現(xiàn)在日期和時(shí)間
=YEAR(D6)? ? 2019? ? 取得年份
=MONTH(D6)? 1? ? ? ? 取得月份
=DAY(D6)? ? ? 25? ? ? 取得天數(shù)
=HOUR(D7)? ? 16? ? ? 取得小時(shí)數(shù)
=MINUTE(D7)? 31? ? ? 取得分鐘數(shù)
=SECOND(D7)? ? 7? ? ? 取得秒數(shù)
=TIME(B7,B8,B9)? ? ? 16:31:07? ? 根據(jù)小時(shí)數(shù)分鐘和秒數(shù)組合成時(shí)間
=DATE(2017,12,6)? ? 2017/12/6? 根據(jù)年月日數(shù)組合成日期
=WEEKDAY(D7,1)? ? 5? ? 一周中的第幾天，第二個(gè)參數(shù)是從哪一天開始算起
二、計(jì)算出生日期
出生年? =MID(B2,7,4)
出生月? ? =MID(B2,11,2)
出生日? ? =MID(B2,13,2)
出生日期? =DATE(C2,D2,E2)
年齡? ? ? =YEAR(TODAY())-C2
三、銷售明細(xì)分析
年? =YEAR(D2)&"年"
月? =MONTH(D2)&"月"
日? =DAY(D2)&"日"
四、基本用法-Datedif
計(jì)算年數(shù)? ? ? 29? ? =DATEDIF(C2,D2,"y")? ? ? y? ? 返回整年數(shù)
計(jì)算月份? ? ? 348? =DATEDIF(C2,D2,"m")? ? m? ? 返回整月數(shù)
計(jì)算天數(shù)? ? ? 10616? ? =DATEDIF(C7,D7,"d")? ? ? d? ? 返回整天數(shù)
忽略年計(jì)算月數(shù)? ? 0? ? =DATEDIF(C2,D2,"ym")? ym? 返回參數(shù)1和參數(shù)2的月數(shù)之差，忽略年和日
忽略年計(jì)算天數(shù)? ? 24? ? =DATEDIF(C2,D2,"yd")? ? yd? ? 返回參數(shù)1和參數(shù)2的天數(shù)之差，忽略年。按照月、日計(jì)算天數(shù)
忽略月計(jì)算天數(shù)? ? 24? ? =DATEDIF(C2,D2,"md")? md? 返回參數(shù)1和參數(shù)2的天數(shù)之差，忽略年和月
函數(shù)解釋：DATEDIF(起始日期,終止日期,間隔單位)
計(jì)算2個(gè)日期的間隔
五、計(jì)算工齡
間隔年份? ? ? 9? ? 9年? ? =DATEDIF(C2,D2,"y")
間隔月份? ? ? 0? ? 0月? ? =DATEDIF(C2,D2,"ym")
間隔天數(shù)? ? ? 24? ? 24日? ? ? =DATEDIF(C2,D2,"md")
=DATEDIF(C2,D2,"y")&"年"&DATEDIF(C2,D2,"ym")&"月"&DATEDIF(C2,D2,"md")&"日"
六、生日提醒
=365-DATEDIF(C3,TODAY(),"yd")
七、身份證號(hào)碼計(jì)算年齡
=DATEDIF(TEXT(MID(B2,7,8),"0-00-00"),TODAY(),"y")
