查找函數(shù)和日期函數(shù)

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ù)值竹祷,即可

6期—3隊—Cathy? 制作



(二)? 日期函數(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")

圖片發(fā)自簡書App
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末斑司,一起剝皮案震驚了整個濱河市,隨后出現(xiàn)的幾起案子胡桃,更是在濱河造成了極大的恐慌揉抵,老刑警劉巖,帶你破解...
    沈念sama閱讀 216,744評論 6 502
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件龟糕,死亡現(xiàn)場離奇詭異衬以,居然都是意外死亡,警方通過查閱死者的電腦和手機互妓,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,505評論 3 392
  • 文/潘曉璐 我一進店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來坤塞,“玉大人冯勉,你說我怎么就攤上這事〕呙” “怎么了珠闰?”我有些...
    開封第一講書人閱讀 163,105評論 0 353
  • 文/不壞的土叔 我叫張陵,是天一觀的道長瘫辩。 經(jīng)常有香客問我,道長坛悉,這世上最難降的妖魔是什么伐厌? 我笑而不...
    開封第一講書人閱讀 58,242評論 1 292
  • 正文 為了忘掉前任,我火速辦了婚禮裸影,結(jié)果婚禮上挣轨,老公的妹妹穿的比我還像新娘。我一直安慰自己轩猩,他們只是感情好卷扮,可當我...
    茶點故事閱讀 67,269評論 6 389
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著均践,像睡著了一般晤锹。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上彤委,一...
    開封第一講書人閱讀 51,215評論 1 299
  • 那天鞭铆,我揣著相機與錄音,去河邊找鬼焦影。 笑死车遂,一個胖子當著我的面吹牛,可吹牛的內(nèi)容都是我干的斯辰。 我是一名探鬼主播舶担,決...
    沈念sama閱讀 40,096評論 3 418
  • 文/蒼蘭香墨 我猛地睜開眼,長吁一口氣:“原來是場噩夢啊……” “哼彬呻!你這毒婦竟也來了衣陶?” 一聲冷哼從身側(cè)響起柄瑰,我...
    開封第一講書人閱讀 38,939評論 0 274
  • 序言:老撾萬榮一對情侶失蹤,失蹤者是張志新(化名)和其女友劉穎祖搓,沒想到半個月后狱意,有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 45,354評論 1 311
  • 正文 獨居荒郊野嶺守林人離奇死亡拯欧,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 37,573評論 2 333
  • 正文 我和宋清朗相戀三年详囤,在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片镐作。...
    茶點故事閱讀 39,745評論 1 348
  • 序言:一個原本活蹦亂跳的男人離奇死亡藏姐,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出该贾,到底是詐尸還是另有隱情羔杨,我是刑警寧澤,帶...
    沈念sama閱讀 35,448評論 5 344
  • 正文 年R本政府宣布杨蛋,位于F島的核電站兜材,受9級特大地震影響,放射性物質(zhì)發(fā)生泄漏逞力。R本人自食惡果不足惜曙寡,卻給世界環(huán)境...
    茶點故事閱讀 41,048評論 3 327
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望寇荧。 院中可真熱鬧举庶,春花似錦、人聲如沸揩抡。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,683評論 0 22
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽峦嗤。三九已至蕊唐,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間寻仗,已是汗流浹背刃泌。 一陣腳步聲響...
    開封第一講書人閱讀 32,838評論 1 269
  • 我被黑心中介騙來泰國打工, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留署尤,地道東北人耙替。 一個月前我還...
    沈念sama閱讀 47,776評論 2 369
  • 正文 我出身青樓,卻偏偏與公主長得像曹体,于是被迫代替她去往敵國和親俗扇。 傳聞我的和親對象是個殘疾皇子,可洞房花燭夜當晚...
    茶點故事閱讀 44,652評論 2 354

推薦閱讀更多精彩內(nèi)容