依據(jù)日期和品名查詢對(duì)應(yīng)時(shí)間范圍的商品價(jià)格
?
作者:老菜鳥(niǎo)來(lái)源:部落窩教育發(fā)布時(shí)間:2020-07-31 15:12:14點(diǎn)擊:8536
編按:
哈嘍,大家好!如何根據(jù)日期和名稱等多個(gè)條件查詢相應(yīng)時(shí)間范圍或者時(shí)間段所對(duì)應(yīng)的產(chǎn)品價(jià)格、數(shù)量呢?品名、編號(hào)等都需要精確查找,但不需要對(duì)日期進(jìn)行精確查找,而是查找最接近或等于查找日期的某個(gè)時(shí)間段。趕緊看看下面的文章吧!
【問(wèn)題說(shuō)明】
小王負(fù)責(zé)策劃公司商品的促銷(xiāo)活動(dòng),需要根據(jù)商品特性和市場(chǎng)反應(yīng)做一些非常有針對(duì)性的單品促銷(xiāo)方案,這些年來(lái)僅促銷(xiāo)的價(jià)格清單就有成百上千條。
最近公司領(lǐng)導(dǎo)讓小王做一個(gè)針對(duì)以往活動(dòng)價(jià)格的查詢模板,要求輸入商品名稱和查詢?nèi)掌诰湍苷{(diào)取對(duì)應(yīng)的執(zhí)行價(jià)格,類似于下面的效果。
圖中只是隨便列舉了一些數(shù)據(jù),實(shí)際的數(shù)據(jù)量要更大。例如要查詢商品2在2020年6月20日所執(zhí)行的價(jià)格,就要在活動(dòng)明細(xì)(A~D列)中找到商品2,再看看查詢?nèi)掌趯儆趯?duì)應(yīng)的哪一個(gè)方案,從而確定出6月20日的價(jià)格是600。
領(lǐng)導(dǎo)的需求小王算是搞明白了,但這要怎么實(shí)現(xiàn)呢?
小王陷入了沉思……
【思路分析】
很明顯,這個(gè)問(wèn)題屬于多條件查找,條件1是品名,條件2是日期,要查找的結(jié)果是價(jià)格。但是有一個(gè)問(wèn)題,品名是可以精確對(duì)應(yīng)的,但日期不行,需要對(duì)應(yīng)的是查找日期之前最近的一個(gè)日期。
例如商品2可能就有很多種方案,查找6月20日的價(jià)格,就要在商品2的方案中找到6月20日之前的,并且是最接近的那個(gè)日期,也就是6月14日開(kāi)始執(zhí)行的價(jià)格。如果6月20日作為條件,在對(duì)應(yīng)的條件區(qū)域中正好存在的可能性很小。當(dāng)然也可以將條件設(shè)置為小于等于查詢?nèi)掌?,但是這樣的話,就有可能存在多條記錄,如何確保在小于查詢?nèi)掌诘亩鄺l記錄中匹配到的是最后一條呢?
“當(dāng)查找區(qū)域中有多個(gè)滿足條件的數(shù)據(jù)時(shí),LOOKUP會(huì)與最后一條數(shù)據(jù)進(jìn)行匹配,并得到結(jié)果區(qū)域中對(duì)應(yīng)的數(shù)據(jù)?!崩眠@一特性,就可以解決按某個(gè)條件查找最新數(shù)據(jù)的問(wèn)題。
【函數(shù)公式】
使用LOOKUP函數(shù)進(jìn)行多條件查找的套路為:
=LOOKUP(1,0/((查找范圍1=查找值1)*(查找范圍2=查找值2)*……*(查找范圍n=查找值n)),結(jié)果范圍)
對(duì)于小王的這個(gè)問(wèn)題來(lái)說(shuō),只需要兩個(gè)條件,按照這個(gè)套路寫(xiě)出的公式是這樣的:
=LOOKUP(1,0/(($A$2:$A$17=F2)*($C$2:$C$17<=G2)),$D$2:$D$17)
驗(yàn)證結(jié)果發(fā)現(xiàn)個(gè)別地方會(huì)得到錯(cuò)誤值,如圖所示。
錯(cuò)誤的原因顯而易見(jiàn),查找的日期早于該商品的最早生效日期。
如何將這種錯(cuò)誤值替換成文字性的說(shuō)明呢?
這當(dāng)然難不住小王了,只需要在LOOKUP函數(shù)的外面嵌套一個(gè)IFERROR函數(shù)就可以搞定,完善后的公式為:
=IFERROR(LOOKUP(1,0/(($A$2:$A$17=F5)*($C$2:$C$17<=G5)),$D$2:$D$17),"無(wú)此日期對(duì)應(yīng)價(jià)格")
將錯(cuò)誤值顯示為“無(wú)此日期對(duì)應(yīng)價(jià)格”,結(jié)果如圖所示。
至此,小王完美的完成了領(lǐng)導(dǎo)交代的任務(wù),獲得了大家的一致好評(píng)。
但是小王心里清楚,通過(guò)這個(gè)問(wèn)題還是發(fā)現(xiàn)了自己的基本功不夠牢固,還需要好好的總結(jié)一下。
【心得小結(jié)】
在這個(gè)實(shí)例中,有下面幾個(gè)很關(guān)鍵的問(wèn)題需要著重強(qiáng)調(diào)。
1.遇到問(wèn)題一定要冷靜,明確問(wèn)題的類型才能找到解決問(wèn)題的突破口,小王能夠準(zhǔn)確的將這個(gè)問(wèn)題定性為多條件查找,就是找準(zhǔn)了解決問(wèn)題的方向。
2.對(duì)于一些不太常用的函數(shù)以及一些函數(shù)的常用套路,或許不能應(yīng)用自如,但是有印象很重要,這樣在查找資料時(shí)就能很快的找到線索。
3.一些函數(shù)或公式的重要特點(diǎn)必須牢記,例如存在多個(gè)符合查找條件的結(jié)果時(shí),VLOOKUP找到的是第一個(gè),而LOOKUP找到的是最后一個(gè)。
4.多個(gè)函數(shù)的嵌套往往不是一蹴而就的,而是在不斷測(cè)試的過(guò)程中逐步完善的,分析問(wèn)題解決問(wèn)題的過(guò)程也是一種很有效的學(xué)習(xí)方式。
5.關(guān)于LOOKUP函數(shù)的應(yīng)用,如果只單純套用公式模型是比較容易的,但是要真的搞明白這個(gè)函數(shù)的話,之前的相關(guān)教程還得好好再研究一番。
只有不斷的修煉,不斷的強(qiáng)大,才能無(wú)懼于未來(lái)遇到的一切問(wèn)題。小王這樣鼓勵(lì)自己,充滿信心的等待下一個(gè)新的挑戰(zhàn)。
本文配套的練習(xí)課件請(qǐng)加入QQ群:1023684854下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車(chē)》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
智能會(huì)議時(shí)間表《excel如何批量生成間隔值相同的連續(xù)時(shí)間段》
EDATE函數(shù)的應(yīng)用《到期日計(jì)算,EDATE甩DATE函數(shù)兩條街!》
自動(dòng)到期提醒《自動(dòng)提醒產(chǎn)品還有多少天過(guò)期的5種Excel方法》
關(guān)于時(shí)間日期的公式《20個(gè)可分別提取年月日時(shí)分秒數(shù)據(jù)等的excel公式》
最熱教程
- 像綠皮火車(chē)一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒(méi)有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問(wèn)題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說(shuō)是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- SHEETSNAME,一鍵獲得所有工作表名稱完成目錄制作
- 延遲退休落地,快速查詢你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)
- Excel表10萬(wàn)行數(shù)據(jù),篩選卡頓怎么辦?
- 兩個(gè)不用嵌套的萬(wàn)能提取公式,含用REGEXP函數(shù)提取
- 用Excel制作刷題器,備考無(wú)憂 !
- 如何用LET+LAMBDA直接寫(xiě)遞歸公式?
- 用函數(shù)合并多個(gè)工作表數(shù)據(jù)
- 用LAMBDA自定義顏色求和函數(shù)