|
楼主 |
发表于 2016-3-10 23:42
|
显示全部楼层
心正意诚身修 发表于 2016-3-9 08:37
大括號是表示該公式為數組公式.當公式編寫完成後同時按CTRL+SHIFT+ENTER結束..就自動出現了.
所以數 ...
很謝謝版主的耐心指教
你的措藝太高了
=SUMPRODUCT((YEAR($A$2:$A$342)=E2)*(MONTH($A$2:$A$342)=F2)*($C$2:$C$342=G2)*(MATCH(TEXT($A$2:$A$342,"em")&$B$2:$B$342&$C$2:$C$342,TEXT($A$2:$A$342,"em")&$B$2:$B$342&$C$2:$C$342,)=ROW($A$1:$A$341)))
公式的上半部很容易就明白了( SUMPRODUCT((YEAR($A$2:$A$342)=E2)*(MONTH($A$2:$A$342)=F2)*($C$2:$C$342=G2) )
下半部就想了兩天都想不明白你編公式的思路
因為match語法MATCH(lookup value,lookup_array,match_type)
而你公式中lookup value=TEXT($A$2:$A$342,"em")&$B$2:$B$342&$C$2:$C$342=20163Name 22A
lookup_array=TEXT($A$2:$A$342,"em")&$B$2:$B$342&$C$2:$C$342=20163Name 22A (這裡不是要範圍嗎,為何會是值呢)
match_type=,)
ROW($A$1:$A$341)=1
希望有空能多作講解
謝謝
|
|