(15點) Excel 問題, please help~

2014-07-26 8:35 am

..... A....B...C...D
1..........中..英..數
2..Sam.60..72..80
3..Tom..28..65..52
4..May..98..38..66
5........................
6..中...May...........

"A"行:人名,
"1"行:科目

根據A6+B6(中+MAY) 找出於A1:D4當中的分數

本想用sumproduct, 但好似FAIL 左
=sumprouct((A6=A1:A4)*(B6=B1:B4),(B2:D4))

HELP!

thank you

回答 (3)

MintyB

2014-07-26 2:12 pm

✔ 最佳答案

........A.............B.............C.............D
1.....Name......Chinese..English...Maths
2.....Sam........60............72...........80
3.....Tom.........28............65...........52
4.....May.........98............38...........66
5
6.....Chinese...May........98

响C6輸入:
=VLOOKUP(B6,A1:D4,LOOKUP(A6,B1:D1,{2,3,4}),FALSE)

👍 0 👎 0

Eddie

2014-10-21 1:19 am

Another alternative formula:
=INDEX(B2:D4,MATCH(B6,A2:A4,0),MATCH(A6,B1:D1,0))

👍 0 👎 0

Andy Chan

2014-07-27 3:35 am

=SUMPRODUCT((B1:D1=A6)*(A2:A4=B6)*(B2:D4))

👍 0 👎 0

收錄日期: 2021-04-11 20:44:05
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20140726000051KK00007

檢視 Wayback Machine 備份