|
就用生日舉例子吧，最終的效果是介樣?jì)饍旱模?/span> 所有信息都是隨機(jī)生成的，如有雷同，純屬巧合。 那么我們來(lái)看看原表吧，如下圖所示： 表一： 表二： 姓名、日期縱向排列，沒(méi)有空行或者合并單元格，生日列也都是日期格式。太好了，這才是原始數(shù)據(jù)應(yīng)有的自覺(jué)！ 在這里插一句：對(duì)于日期格式，Excel只認(rèn)“/”、“-”做為間隔，“.”或者無(wú)間隔是不可以的。日期本身對(duì)Excel來(lái)說(shuō)就是個(gè)數(shù)字，1900-1-1對(duì)應(yīng)的是1，這樣Excel才能進(jìn)行日期的計(jì)算。 回到正題，首先我們要告訴Excel周一到周日的日期是哪兩天 = TODAY()-WEEKDAY(TODAY(),2)+1 這個(gè)公式涉及兩個(gè)函數(shù)：TODAY()告訴我今天是幾號(hào)，WEEKDAY()第一個(gè)參數(shù)是指定日期，第二個(gè)參數(shù)是告訴我們?cè)撊掌谑撬谥艿牡趲滋欤聢D為WEEKDAY()第二個(gè)參數(shù)所對(duì)應(yīng)的返回值如下： 以2021-1-29星期五為例，上述公式得到的結(jié)果是：44225-5+1=44221，轉(zhuǎn)換為日期格式就是2021-1-25，即本周一所對(duì)應(yīng)的日期，這個(gè)日期得出后，我們就可以輕松的得出本周日的日期啦，只需要在G1單元格輸入“=F1+6”，調(diào)整為日期格式就好啦~ 再炫技拓展一下，我想要根據(jù)E列所選擇的結(jié)果得到相應(yīng)的日期，即上周、本周、下周分別對(duì)應(yīng)的日期是什么。 首先，在E1單元格設(shè)置下拉菜單，為使用者提供特定的選擇： 【E1】—【數(shù)據(jù)】—【數(shù)據(jù)驗(yàn)證】—【序列】—【本周,上周,下周】—【確定】 注意：間隔符是英文輸入法下的逗號(hào)！ 再將F2單元格公式改一下： =TODAY()-WEEKDAY(TODAY(),2)+1+VLOOKUP(E1,{'本周',0;'下周',7;'上周',-7},2,0) 即在原有基礎(chǔ)上加了一個(gè)VLOOKUP函數(shù)，該函數(shù)的作用是搜索表區(qū)域首列滿(mǎn)足條件的元素，確定待檢索單元格在區(qū)域中的行序號(hào)，再進(jìn)一步返回選定單元格的值，翻譯成普通話(huà)的意思就是： VLOOKUP(找啥，在哪找，要第幾列數(shù)據(jù)，怎么找） 在這個(gè)公式中的意思就是，如果E1為本周，該函數(shù)返回值為0；如果E1為下周，該函數(shù)返回值為7；如果E1為上周，該函數(shù)返回值為-7。這三個(gè)數(shù)與之前的公式相加，就能得出相應(yīng)的日期啦！ 好了，現(xiàn)在如何判斷表一中的日期是否符合條件呢？ 我在姓名列前插入了一列輔助列，用來(lái)將符合日期條件的信息篩選出來(lái)，公式如下： =IF(AND(C2>=$F$1,C2<=$G$1),A1+1,A1) IF函數(shù)的作用是判斷是否滿(mǎn)足條件，如果滿(mǎn)足返回一個(gè)值，如果不滿(mǎn)足返回另一個(gè)值，用法為： IF(啥條件,是真的就給你一朵小紅花,是假的就給你一個(gè)大嘴巴) 因?yàn)樾枰瑫r(shí)滿(mǎn)足兩個(gè)條件，所以我在IF里嵌套了AND函數(shù)，公式翻譯過(guò)來(lái)就是：如果C列的日期在F1和G1所得出的日期中間，則返回值為上一單元格的值+1，否則與上一單元格內(nèi)容一致，下拉/雙擊填充，就得到了表一中A列的結(jié)果。 從開(kāi)篇的動(dòng)圖中可以看出，表二中序號(hào)列是動(dòng)態(tài)取數(shù)的，這是怎么做到的呢？ E3單元格直接輸入1，E4單元格中輸入公式： =IF(E3<MAX(A:A),E3+1,'') 意思是如果上一單元格的值小于A列最大值，那么就返回上一單元格+1的值，否則為空。換句話(huà)說(shuō)，如果上一單元格的值等于A列最大值了，那這個(gè)單元格就為空。下拉/雙擊填充！這樣序號(hào)就可以做到動(dòng)態(tài)改變了，可能有點(diǎn)繞，你得細(xì)品品~ 表二中的姓名列和生日列的公式就簡(jiǎn)單了： =IFERROR(VLOOKUP($E3,$A:$C,COLUMN(B1),0),'') 最外面的IFERROR函數(shù)是避免返回錯(cuò)誤值的；最里面COLUMN函數(shù)返回的是指定的列號(hào)，可用于生成序列1、2、3……，COLUMN(B1)返回的是2，如果我們需要取的列數(shù)比較多，就可以像我這樣用—VLOOKUP第三參數(shù)嵌套COLUMN函數(shù)，而不用一個(gè)個(gè)公式去改。這個(gè)公式的意思是：查找在A列中查找序號(hào)列的對(duì)應(yīng)值，返回相應(yīng)的姓名和日期，由于VLOOKUP只能返回第一個(gè)符合條件的值，所以就分別得出了序號(hào)1、2、3……所對(duì)應(yīng)的第一個(gè)結(jié)果: 右拉、下拉/雙擊填充，哦了！ 是不是很簡(jiǎn)單呢？ 原載公眾號(hào)：Office問(wèn)題粉碎機(jī) |
|
|
來(lái)自： hercules028 > 《excel》
