1.求2015年每個(gè)月的平均毛利率 如下圖所示,是2015年某產(chǎn)品毛利率的區(qū)間,求每個(gè)月的平均毛利率,具體算法就是下圖的描述。我這里再說(shuō)明一下,以9月為例,在3個(gè)區(qū)間中都有9月,分別是序號(hào)8,9,10的區(qū)間。在序號(hào)8的區(qū)間中,也就是2015/9/1到2015/9/15,9月占15天,對(duì)應(yīng)的毛利率是20%;同樣的,序號(hào)9的區(qū)間9月占7天,毛利率是11%;序號(hào)10的區(qū)間9月占8天,毛利率是10%;所以最后9月的平均毛利率是(15*20%+7*11%+8*10%)/30。其他月份也是同樣的算法。 這個(gè)題目如果沒有找到好的思路,還是挺難的;如果找到好的思路就會(huì)容易很多。先說(shuō)公式吧,在G7單元格輸入公式=AVERAGE(LOOKUP(ROW(INDIRECT(DATE(2015,ROW(A1),1)&":"&EOMONTH(DATE(2015,ROW(A1),1),0))),B$7:B$18,D$7:D$18)),按ctrl+shift+enter三鍵結(jié)束,向下填充。 其中ROW(INDIRECT(DATE(2015,ROW(A1),1)&":"&EOMONTH(DATE(2015,ROW(A1),1),0)))這一長(zhǎng)串是為了得到2015年每個(gè)月的所有天數(shù),現(xiàn)在是2015年1月份的所有天數(shù),也就是從2015-1-1到2015-1-31。公式下拉就得到了2015年2月份的所有天數(shù)。 這里有2個(gè)日期函數(shù),一個(gè)是date,一個(gè)是eomonth。其中eomonth在文章《多條件if嵌套計(jì)算房租費(fèi)用,差點(diǎn)蒙了!》中簡(jiǎn)單說(shuō)過(guò),大家可以參考一下,日期函數(shù)以后還會(huì)單獨(dú)講解。那么今天就來(lái)簡(jiǎn)單說(shuō)說(shuō)date函數(shù)。它是由代表年月日的3個(gè)數(shù)字組成一個(gè)完整的日期。函數(shù)語(yǔ)法為DATE(year,month,day),有3個(gè)參數(shù),分別代表年,月,日。如下圖所示,D2就是由A2,B2,C2組成的一個(gè)日期。是不是很簡(jiǎn)單。 知道了date函數(shù)的意思,就來(lái)看看上面row函數(shù)那一串的意思,DATE(2015,ROW(A1),1)得到了2015-1-1,也就是1月份的第1天;EOMONTH(DATE(2015,ROW(A1),1),0)得到了2015-1-31,也就是1月份的最后1天。 DATE(2015,ROW(A1),1)&":"&EOMONTH(DATE(2015,ROW(A1),1),0)這部分是1月的第1天和最后1天連接起來(lái),最后的結(jié)果為{"42005:42035"},得到了文本型單元格地址,因?yàn)樵趀xcel函數(shù)中日期的本質(zhì)就是數(shù)字,只不過(guò)用日期的格式展示出來(lái)。然后用indirect返回引用,也就是42005行到42035行,最后用row函數(shù)得到了引用的行號(hào),相當(dāng)于row(42005:42035),這樣就得到了2015年1月份的所有天數(shù)。 然后用lookup在B$7:B$18這個(gè)區(qū)域中,查找1月份的所有天數(shù),然后返回對(duì)應(yīng)的D$7:D$18?,F(xiàn)在lookup的第1參數(shù)是個(gè)數(shù)組,其中2015-1-1到2015-1-15都找到了第2參數(shù)的2015-1-1,返回對(duì)應(yīng)第3參數(shù)的15%;2015-1-16到2015-1-31都找到了第2參數(shù)的2015-1-16,返回對(duì)應(yīng)第3參數(shù)的16%;這樣的話就得到了15個(gè)15%,16個(gè)16%。 最后用average求平均值,就得到了1月份的平均毛利率,是不是和(15*15%+16*16%)/31是一樣的呢?所以我說(shuō)乘法和加法你真的會(huì)嗎?其實(shí)是說(shuō)我自己。因?yàn)樽铋_始的思路一直是求每個(gè)月在各個(gè)區(qū)間各占多少天,然后再乘以對(duì)應(yīng)的毛利率,求和,最后再除以每個(gè)月的天數(shù)。沒有想到用lookup先求出每個(gè)月每一天的毛利率,然后再求平均值。思路決定出路。 如果用我上面說(shuō)的每個(gè)月在各區(qū)間占多少天的思路來(lái)完成,公式為=SUMPRODUCT(MMULT(--TEXT(DATE(2015,ROW(A2),1)-COLUMN(INDIRECT("c1:c"&DAY(DATE(2015,ROW(A2),1)-1),)),"[<"&B$7:B$18&"]!0;[>"&C$7:C$18&"]!0;1"),ROW(INDIRECT("1:"&DAY(DATE(2015,ROW(A2),1)-1)))^0)*D$7:D$18)/DAY(DATE(2015,ROW(A2),1)-1),下拉填充。 文件鏈接: ttps://pan.baidu.com/s/1dREJv3nIc52S1YbS1fwU6A 提取碼:ewfw |
|
來(lái)自: 劉卓學(xué)EXCEL > 《excel函數(shù)》