昨天,表格學(xué)院群里有人提了這樣一個(gè)問題:
大概的需求是,將每個(gè)季度(如Q1)的數(shù)據(jù)快速填到對應(yīng)的月份(1、2、3月),來看表格:
例如,A列2000年1季度對應(yīng)的B列值是0.5171,則目標(biāo)是將0.5171快速填入到D列的D2、D3、D4單元格;而2季度的值是0.6141,因此,要將此值填入到D列的4、5、6三個(gè)月。
你可能會說,直接復(fù)制粘貼不就好了嗎?費(fèi)勁干嘛!
在這個(gè)簡單的案例里,當(dāng)然直接復(fù)制粘貼就OK,花不了多少時(shí)間。但是,假如是一張大表,幾百上千行呢?
所以,我們還是要找到高效的處理方法。
為了解答這個(gè)問題,我照著提問模擬了一組數(shù)據(jù):
▲ 案例:將季度值填充到對應(yīng)的月
如圖,C列是季度對應(yīng)的值,D列是月份,現(xiàn)在要想辦法將C列的數(shù)值根據(jù)所屬的季度填入到E列的對應(yīng)月份中。
如果是你,你會怎么解決這個(gè)問題呢,思考1分鐘……。
下面說說我的思路。
在這里,我們可以把B、C兩列看成一個(gè)數(shù)據(jù)源,現(xiàn)在要想辦法根據(jù)D列的月份從數(shù)據(jù)源中匹配季度值,這樣就把這個(gè)問題轉(zhuǎn)化成了一個(gè)數(shù)據(jù)匹配的問題。
我們自然而然地想到VLOOKUP函數(shù),縱向查詢和匹配。
但是,問題在于D列的年月與B列的年和季度格式上完全不同,怎么匹配呢?
顯然,這里涉及到一個(gè)季度和月份之間的轉(zhuǎn)化,也就是我們怎么把1、2、3月份與1季度形成對應(yīng)關(guān)系,把4、5、6月份與2季度形成對應(yīng)關(guān)系,依此類推。
這是解決問題的關(guān)鍵。
單純從數(shù)字講,我們要想辦法把1、2、3轉(zhuǎn)化為1,4、5、6轉(zhuǎn)化為2,7、8、9轉(zhuǎn)化為3,10、11、12轉(zhuǎn)化為4。
我想到了兩種函數(shù)的方法——
1、用Roundup函數(shù)處理
首先用月份值除以3,得到的結(jié)果一定是一個(gè)小數(shù)(每季度前兩個(gè)月)或者整數(shù)(每個(gè)季度最后一個(gè)月),而只要把結(jié)果用Roundup向上舍入,保留0位小數(shù)就能得到對應(yīng)的季度。
比如,1月份,1/3=0.333,用Roundup(1/3,0)向上舍入,結(jié)果為1,即1季度;3月份,用Roundup(3/3,0),結(jié)果仍然為1。
因此,我們可以編寫公式如下:
先在E列(E2)中輸入公式:
=YEAR(D2)&" "&ROUNDUP(MONTH(D2)/3,0)&"Q"
該公式的目的是構(gòu)造一個(gè)與B列季度信息格式一致的輔助列,然后把問題轉(zhuǎn)化成VLOOKUP數(shù)據(jù)匹配。
構(gòu)造好E列之后,就很簡單了,用VLOOKUP在F列直接寫公式完成匹配。
2、用Ceiling函數(shù)處理
除了用Roundup函數(shù),這個(gè)問題也可以使用Ceiling函數(shù)來解決。
Ceiling是“天花板”的意思,在函數(shù)中也是一個(gè)向上舍入函數(shù),它可以將一個(gè)數(shù)字向上舍入為基數(shù)的最小倍數(shù)。
舉例,公式“=Ceiling(15,6)”意為把15向上舍入為6的最小倍數(shù),即6的3倍,結(jié)果為18;公式“=Ceiling(0.56,1)”意為把0.56向上舍入為1的最小倍數(shù),也就是1倍,結(jié)果就是1。
因此,這個(gè)案例中,我們?nèi)绻褂肅eiling函數(shù),前面的公式就修改如下:
=YEAR(D2)&" "&CEILING(MONTH(D2),3)/3&"Q"
小小案例,包含諸多技巧。Year、Month、Roundup、Ceiling、Vlookup,你學(xué)會了嗎?
好了,今天的內(nèi)容就介紹到這里,我是徐老師,感謝閱讀。如果你還有其它思路和解決辦法,歡迎留言。
——Excel暢銷書作者,表格學(xué)院創(chuàng)始人,著有國內(nèi)首部Excel動態(tài)圖表書籍《左手?jǐn)?shù)據(jù),右手圖表》,唯庫、一起聽課星球、人人都是產(chǎn)品經(jīng)理等平臺約講師。