Day 16? ? 2019.01.25
查找函數(shù)(Vlookup欠拾、Hlookup汤纸、Index衩茸、Match)
日期函數(shù)(Year、Month贮泞、Datedif)
今天的豎屏終于做出來楞慈,也用PPT制作了一個視頻幔烛,因時間關(guān)系沒錄旁白,有時間再修正啦~
(一)查找函數(shù)(Vlookup囊蓝、Hlookup饿悬、Index、Match)
一聚霜、基本用法
=VLOOKUP(D2,$A$2:$B$12,2,0)
參數(shù)解釋:
=VLOOKUP(要查找的銷售員,包含銷售員和其銷售額的數(shù)據(jù)源區(qū)域,找到后返回第2列,精確查找)
第一參數(shù):找什么(或者說按什么查找)狡恬,按銷售員查找,所以輸入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ù):找什么(或者說按什么查找)报嵌,按月份查找,所以輸入G2
第二參數(shù):在哪找熊榛,數(shù)據(jù)源區(qū)域在A:E列锚国,所以輸入$A$1:$E$9
第三參數(shù):找到后返回第幾行,我們要查找的是獨孤求敗玄坦,位于第6行血筑,即第二參數(shù)中的第6行,所以輸入6
第四參數(shù):這里要精確查找煎楣,所以輸入0
MATCH函數(shù)
作用:在一個區(qū)域或數(shù)組中查找指定數(shù)值的位置豺总,如果查找不到則返回錯誤值
=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))
綜合運用
作用:反向查詢
利用Match函數(shù)查找位置($B$2:$B$8)
二、借助名稱框查找
=VLOOKUP(D2,查找區(qū)域,2,0)
注意:利用名稱框抓取數(shù)據(jù)區(qū)域困曙,就不用在手工去選擇區(qū)域
三表伦、通配符模糊查找
=VLOOKUP("*老師*",$A$2:$B$12,2,0)
說明:
通配符星號*通配任意個字符
問號?通配單一字符
四、查找返回多列數(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)取對應(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)取對應(yīng)的數(shù)據(jù)纲熏,黃色區(qū)域是需要填寫公式的區(qū)域。
六、多條件查找(一對多查找)
①? ? ? 需要借助輔助列 =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),"")
說明:
=ROW(F1)? 例:結(jié)果是 1
=COLUMN(B2)? 例:結(jié)果是 2
操作步驟:
步驟1 在左表前插入一列并設(shè)置公式局劲,用countif函數(shù)統(tǒng)計客戶的銷售額并用&連接成 產(chǎn)品名稱+序號的形式勺拣。=C2&COUNTIF($C$2:C2,C2)
步驟2 在F9設(shè)置公式并復(fù)制即可得到F2單元格中產(chǎn)品的所有銷售記錄。
=IFERROR(VLOOKUP($F$2&ROW(A1),$A:$D,COLUMN(B1),0),"")
解決一對多查找的關(guān)鍵點是把重復(fù)的值用Countif變成不同的容握,然后用Vlookup函數(shù)再進行查找
七宣脉、區(qū)間查找
=VLOOKUP(B2,$I$2:$J$5,2)
要求: 需要按照等級劃分規(guī)則,將成績劃分到其對應(yīng)的等級中剔氏。
說明:
=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")))
注意:
必須是升序排列
八、動態(tài)圖表
=C9&" 銷售額"? 要查找的內(nèi)容
=VLOOKUP($C$9,$A$2:$G$7,COLUMN(),0)? 用vlookup查找一整行數(shù)值竹祷,即可
(二)? 日期函數(shù)(Year谈跛、Month、Datedif)
一塑陵、基本用法
=TODAY()? ? ? 2019/1/25? ? ? 當天日期
=NOW()? ? ? ? ? 2019/1/25 16:31? ? ? 現(xiàn)在日期和時間
=YEAR(D6)? ? 2019? ? 取得年份
=MONTH(D6)? 1? ? ? ? 取得月份
=DAY(D6)? ? ? 25? ? ? 取得天數(shù)
=HOUR(D7)? ? 16? ? ? 取得小時數(shù)
=MINUTE(D7)? 31? ? ? 取得分鐘數(shù)
=SECOND(D7)? ? 7? ? ? 取得秒數(shù)
=TIME(B7,B8,B9)? ? ? 16:31:07? ? 根據(jù)小時數(shù)分鐘和秒數(shù)組合成時間
=DATE(2017,12,6)? ? 2017/12/6? 根據(jù)年月日數(shù)組合成日期
=WEEKDAY(D7,1)? ? 5? ? 一周中的第幾天感憾,第二個參數(shù)是從哪一天開始算起
二、計算出生日期
出生年? =MID(B2,7,4)
出生月? ? =MID(B2,11,2)
出生日? ? =MID(B2,13,2)
出生日期? =DATE(C2,D2,E2)
年齡? ? ? =YEAR(TODAY())-C2
三令花、銷售明細分析
年? =YEAR(D2)&"年"
月? =MONTH(D2)&"月"
日? =DAY(D2)&"日"
四阻桅、基本用法-Datedif
計算年數(shù)? ? ? 29? ? =DATEDIF(C2,D2,"y")? ? ? y? ? 返回整年數(shù)
計算月份? ? ? 348? =DATEDIF(C2,D2,"m")? ? m? ? 返回整月數(shù)
計算天數(shù)? ? ? 10616? ? =DATEDIF(C7,D7,"d")? ? ? d? ? 返回整天數(shù)
忽略年計算月數(shù)? ? 0? ? =DATEDIF(C2,D2,"ym")? ym? 返回參數(shù)1和參數(shù)2的月數(shù)之差嫂沉,忽略年和日
忽略年計算天數(shù)? ? 24? ? =DATEDIF(C2,D2,"yd")? ? yd? ? 返回參數(shù)1和參數(shù)2的天數(shù)之差慎王,忽略年蜀漆。按照月、日計算天數(shù)
忽略月計算天數(shù)? ? 24? ? =DATEDIF(C2,D2,"md")? md? 返回參數(shù)1和參數(shù)2的天數(shù)之差蠕嫁,忽略年和月
函數(shù)解釋:DATEDIF(起始日期,終止日期,間隔單位)
計算2個日期的間隔
五、計算工齡
間隔年份? ? ? 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")
七轰豆、身份證號碼計算年齡
=DATEDIF(TEXT(MID(B2,7,8),"0-00-00"),TODAY(),"y")