網友根據這篇文章:

Excel-在幣別+金額的清單中計算各種幣別的小計(SUMPRODUCT,LEFT,MID)

想要詢問他人做法,儲存格F2:

=SUMPRODUCT(COUNTIF(E2,LEFT($A$2:$A$9)&"*")*MID($A$2:$A$9,4,10))

其中 COUNTIF(E2,LEFT($A$2:$A$9)&"*") 的意義為何?

Excel-在幣別+金額的清單中計算各種幣別的小計2(COUNTIF)

【公式解析】

此公式使用 SUMPRODUCT 函數,可以執行『陣列』的運算。

1. COUNTIF(E2,LEFT($A$2:$A$9)&"*")

公式相當於:=COUNTIF(E2,LEFT($A$2:$A$9,1)&"*"),其中參數1被省略了。

結果:=COUNTIF("NT$",{"N*","H*","N*","H*","N*","N*","H*","N*"})

結果:=COUNTIF({1,0,1,0,1,1,0,1})(第一個字是 N 者傳回 1,是 H 者傳回 0。)

 

2. MID($A$2:$A$9,4,10))

結果:=MID({"1247","647","1040","1514","397","926","1516","258"})

 

3. SUMPRODUCT(COUNTIF(E2,LEFT($A$2:$A$9)&"*")*MID($A$2:$A$9,4,10))

公式相當於:=SUMPRODUCT(COUNTIF({1,0,1,0,1,1,0,1})*MID({"1247","647",
"1040","1514","397","926","1516","258"})

結果:=SUMPRODUCT(1247,0,1040,0,397,926,0,258)

結果:=3868

arrow
arrow
    文章標籤
    Excel COUNTIF SUMPRODUCT
    全站熱搜
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

    vincent 發表在 痞客邦 留言(2) 人氣()