贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

網友提問:在 Excel 中(如下圖),如何求得在『配編』欄位中各個年月的配編數(幾種不一樣的類別)?

在下圖中,原始資料提供年月日的欄位,而統計表中是要以統計各年月的配編數,例如年月是10501者,有 1, 2, 3, 4 共四種配編類別,該如何處理?

Excel-計算符合條件者的不重覆數量(SUMPRODUCT,COUNTIF)2


【公式設計與解析】

儲存格G2:=SUMPRODUCT((1*LEFT($A$2:$A$26,5)=F2)*(1/COUNTIF
($C$2:$C$26,$C$2:$C$26)))

(1) 1*LEFT($A$2:$A$26,5)

取出儲存格A2:A26陣列儲格中,年月日的前 5 碼文字(年月)。其中『*1』,乃將前述的 5 碼文字轉換為 5 碼數字。

(2) 1*LEFT($A$2:$A$26,5)=F2

SUMPRODUCT 函數中設定條件,用以判斷儲存格A2:A26中前 5 碼和儲存格F2是否相同。

(3) COUNTIF($C$2:$C$26,$C$2:$C$26)

SUMPRODUCT 函數中,計算儲存格C2:C26中每個數的重覆個數。例如:『1, 1, 1, 2, 2, 3, 3, 4, 4』傳回『3, 3, 3, 2, 2, 2, 2, 2, 2』

(4) 1/COUNTIF($C$2:$C$26,$C$2:$C$26)

將第(2)的傳回值取倒數,例如『3, 3, 3, 2, 2, 2, 2, 2, 2』傳回『1/3, 1/3, 1/3, 1/2, 1/2, 1/2, 1/2, 1/2, 1/2』,當在 SUMPRODUCT 函數加總時會得到結果『4』。

, ,
創作者介紹

學不完.教不停.用不盡

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


留言列表 (2)

發表留言
  • Michael
  • dear vincent
    你好
    不好意思,我沒有講清楚而造成再次發問的現象
    我依你的例子做說明變化。
    在C行中1050201~1050209還是具有重複1234的配邊,引用您的公式後發覺讀取的資料因有包含1050201到1050209造成結果不對,而我結果只想限定10501的配編數,請問公式再如何做修改呢?
    再次感謝您~~
  • Michael
  • 不好意思,再度打擾,怕您不清楚我舉的案例
    整個再重新舉個例子
    ex: 時間 種類別
    2017/05/23 09:08 1
    2017/05/23 12:00 2
    2017/05/23 17:00 1
    2017/05/23 18:00 3
    2017/05/24 01:00 1
    2017/05/24 08:23 4
    2017/05/24 17:00 2
    2017/05/24 18:48 5
    2017/05/24 19:56 2
    2017/05/24 20:00 5
    . .
    . .
    . .
    等等

    最後求出
    2015/05/23 種類3種
    2015/05/24 種類4種
    ....等等

    請問這要怎麼求出呢?!
    感恩您~
  • 請參考:http://isvincent.pixnet.net/blog/post/47269611
    公式有點複雜,說明沒有很詳細,請見諒!

    vincent 於 2017/05/23 23:34 回覆

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼