iT邦幫忙

0

請教 Excel 如何用不同工作天進行加總及平均 ??

請教 Excel 高手
我在 Excel 在 10 月份有不同工作天 (第四列)
我如何在 A3 欄輸入工作天後,可利用公式將 X3 & Y3 進行加總及平均

如 A3 輸入 5,則 X3, Y3 需計算 10/1 ~10/5 的加總及平均
A3 輸入 15,則 X3, Y3 需計算 10/1 ~10/17 的加總及平均

https://ithelp.ithome.com.tw/upload/images/20181113/20112266sOaqMyuDQd.jpg

感謝指導

0
taiwanbrian
iT邦新手 4 級 ‧ 2018-11-13 15:59:01

..

kenkotw iT邦新手 5 級 ‧ 2018-11-13 16:27:33 檢舉

好像不行吔

..

0
張小馬
iT邦新手 5 級 ‧ 2018-11-13 16:08:11

最簡單的方法,新增一橫列當作輔助:
https://ithelp.ithome.com.tw/upload/images/20181113/20111566ThFy5lZKIU.png
既然你都寫了第4列,想來是要拿來被判斷吧?那就直接增加一列,當作【判斷完的當天數字】,即上圖的第6列,公式蠻簡單的,就是當小於等於你指定的天數時,呈現當天的數字,否則就是0。
上圖用5,下圖用15:
https://ithelp.ithome.com.tw/upload/images/20181113/20111566AdGfQluwDi.png


接著你一定就會了,sum, avg就是針對第6列去下就好:
https://ithelp.ithome.com.tw/upload/images/20181113/20111566SHhBSflw8X.png

最後如果覺得第6列很礙眼,可以把它移去第10萬列(!?),或直接隱藏(沒有潔癖的話),總之就是把它藏或移去平常版面看不到的地方,它的概念和第4列很像。

kenkotw iT邦新手 5 級 ‧ 2018-11-13 16:32:44 檢舉

張小馬
謝謝,只是我們資料有上百列,如果要增加列數會有點困難

張小馬 iT邦新手 5 級 ‧ 2018-11-13 16:41:56 檢舉

那這樣建議你用Neish的方法,弄清楚每個函式定義,其實不太難。
話說回來,上百列其實也能適用上面這套,只要善用F4($$),寫好,要拉一次上百列就拉完了,也不困難。
是如果資料列數會變動(例如今天325列,明天變成328列這種),才不適合我這方法。

kenkotw iT邦新手 5 級 ‧ 2018-11-13 18:46:57 檢舉

張小馬
感謝您

0
Neish
iT邦研究生 4 級 ‧ 2018-11-13 16:11:51

如果不改變原本格式

用這樣呢?
SUM
=SUM(B5:INDEX(A5:V5,0,MATCH(A3,A4:V4)))
MATCH是找出對應WORKING DAY數字的位置
INDEX是找出你要加總到哪裡

AVG
=X5/A3

看更多先前的回應...收起先前的回應...
kenkotw iT邦新手 5 級 ‧ 2018-11-13 16:43:30 檢舉

Neish
可以了,結果正確
感謝您的指導

pcw iT邦研究生 4 級 ‧ 2018-11-13 18:05:56 檢舉

SUM
=SUM(B5:INDEX(A5:V5,0,MATCH(A3,A4:V4)))
MATCH是找出對應WORKING DAY數字的位置
INDEX是找出你要加總到哪裡
AVG
=X5/A3

A3 = 5,
取得10/1~10/5的數值,但是其中只有4天有值。
計算平均時,應該要除以4還是除以5呢?

kenkotw iT邦新手 5 級 ‧ 2018-11-13 18:48:00 檢舉

10/1~10/5的數值,但是其中只有4天有值。
平均要除以四,不然會失真

pcw iT邦研究生 4 級 ‧ 2018-11-13 18:52:42 檢舉

所以

AVG
=X5/A3

應該要修改一下

0
pcw
iT邦研究生 4 級 ‧ 2018-11-13 18:25:10

X5:

=SUM(OFFSET(B5,0,0,,MATCH($A$3,$B$4:$V$4,0)))

Y5:

=AVERAGE(OFFSET(B5,0,0,,MATCH($A$3,$B$4:$V$4,0)))

kenkotw iT邦新手 5 級 ‧ 2018-11-13 18:48:41 檢舉

pcw
感謝您

pcw iT邦研究生 4 級 ‧ 2018-11-13 19:07:30 檢舉

狀況遇多了,所以分享一下。
如果我的答案能解決你的問題,記得選為最佳解答。

0
ccenjor
iT邦新手 5 級 ‧ 2018-11-13 20:15:40

=SUMIF(B$2:V$2,"<="&WORKDAY.INTL($B$2,$A$3-1,11),B5:V5)
=AVERAGEIF(B$2:V$2,"<="&WORKDAY.INTL($B$2,$A$3-1,11),B5:V5)

pcw iT邦研究生 4 級 ‧ 2018-11-14 08:34:11 檢舉

我建議省略WORKDAY.INTL函數。
直接採用:

X5

=SUMIF($B$4:$V$4,"<="&$A$3,$B$5:$V$5)

Y5

=AVERAGEIF($B$4:$V$4,"<="&$A$3,$B$5:$V$5)

這樣看來簡潔許多

0
wolfyww
iT邦新手 5 級 ‧ 2018-11-15 22:57:27

感謝各位大大!

我要發表回答

立即登入回答