作者 主題: Excel 併除小於一定數量但不重覆物件的數量  (閱讀 1235 次)

0 會員 與 1 訪客 正在閱讀本文。

baldur

  • 懷疑的國中生
  • **
  • 文章數: 55
    • 檢視個人資料
小弟想請教各位大神, 附件內第一列有 ( date, po, item, o.qty, s.qty, r.qty)
1. 目前需要計算相同年月下, o.qty 小於 20 但 item 不重複, 及重複的次數.
2. 加總各年月下不同週數的 s.qty 依PO 分類
對陣列及多條件計算苦手中, 可否惠予指點呢 ?
環境為 office 2000 excel.

檢查相同年月是可以正常計數.
=SUMPRODUCT((YEAR(test!$A$2:$A$1018)=YEAR($a3))*(MONTH(test!$A$2:$A$1018)=MONTH($a3)))
加上小於多少數量, 但算出來的數值是 D 這欄的全部加總......
=SUMPRODUCT((YEAR(test!$A$2:$A$1018)=YEAR($a3))*(MONTH(test!$A$2:$A$1018)=MONTH($a3))*countif(test!$d2:D1018,"<20"))
加上有多少重複, 也還是 D 有多少重複而不是小弟要的數值呢....
=SUMPRODUCT((YEAR(test!$A$2:$A$4000)=YEAR($A3))*(MONTH(test!$A$2:$A$4000)=MONTH($A3))*COUNTIF(test!$D$2:$D$4000,test!$D$2:$D$4000))

思考中....
« 上次編輯: 2014-09-03 14:19 由 baldur »

baldur

  • 懷疑的國中生
  • **
  • 文章數: 55
    • 檢視個人資料
麻辣家族討論版版 讀了一下置頂公告, "公式輸入的常用技巧(新手必讀)"

使用 sumproduct( (year A* monthA * week Z * ponumber Y), sum ) 第二個功能已經完成了.
week 使用時間加上工式換算置於 Z 欄中比對.
ponumber 使用 right 截取後二碼比對.

現在思考第一項功能如何完成了.

日京三子

  • 全區板主
  • 俺是博士!
  • *****
  • 文章數: 8827
    • 檢視個人資料
    • http://www.24online.cjb.net
引用
1. 目前需要計算相同年月下, o.qty 小於 20 但 item 不重複, 及重複的次數.
  引用來源  <===========

引用
輸入公式:

儲存格D2:=SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20))

其中COUNTIF(A1:A20,A1:A20))可以算出在A欄中的數值重覆的個數。

而1/COUNTIF(A1:A20,A1:A20))取重覆個數的倒數,參考C欄。

利用SUMPRODUCT這個函數將全部的數值陣列加總,

其中重覆的字數不管重覆幾個加起來為1。

所以最後的結果會剛好為不重覆的個數。
哈克不愛的多合一輸入平台----->新香草口味
過去的時間不斷流逝,抹去的眼淚已成追憶;
乾枯的雙手無力阻止,再會了我遠去的曾經。

baldur

  • 懷疑的國中生
  • **
  • 文章數: 55
    • 檢視個人資料
不好意思, 小弟是要比對當 D 欄小於 20, 且 c 欄不重覆.

小弟有試著用 sumproduct(1/countif(c2:c29), 單純比對 c 這功能是可以用的 ( 取不重覆數量 )
加上小於 20 的條件後, 也可以取得不重覆數量 ....
                   sumproduct( d2:d29<20, (1/countif(c2:c29))

但再加上時間來比對時, 就為0... 昏了 ~

                   sumproduct( month(a2:a29)=month(a2), d2:d29<20, (1/countif(c2:c29))

請惠予解惑呢... QQ
« 上次編輯: 2014-09-04 15:59 由 baldur »