併除小於一定數量但不重覆物件 的數量

帖子: 8
主題: 1
精華: 0
積分: 13
點名: 0
作業系統: Windows
軟體版本: 7
閱讀權限: 10
性別: 男
註冊時間: 2014-9-3
最後登錄: 2014-9-12

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2014-9-3 11:35 | 只看該作者

併除小於一定數量但不重覆物件的數量

本帖最後由 baldur 於 2014-9-3 11:37 編輯

小弟想請教各位大神, 附件內第一列有 ( date, po, item, o.qty, s.qty, r.qty)
1. 目前需要計算相同年月下, o.qty 小於 20 但 item 不重複, 及重複的次數.
2. 加總各年月下不同週數的 s.qty 依PO 分類
[attach]19078[/attach]
對陣列及多條件計算苦手中, 可否惠予指點呢 ?
環境為 office 2000 excel.

o.test.rar (2.83 KB)

o.test.png (52.54 KB)

帖子: 8
主題: 1
精華: 0
積分: 13
點名: 0
作業系統: Windows
軟體版本: 7
閱讀權限: 10
性別: 男
註冊時間: 2014-9-3
最後登錄: 2014-9-12

2^#

發表於 2014-9-3 14:15 | 只看該作者

本帖最後由 baldur 於 2014-9-3 14:17 編輯

檢查相同年月是可以正常計數.
=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))

思考中....

帖子: 8
主題: 1
精華: 0
積分: 13
點名: 0
作業系統: Windows
軟體版本: 7
閱讀權限: 10
性別: 男
註冊時間: 2014-9-3
最後登錄: 2014-9-12

3^#

發表於 2014-9-3 18:03 | 只看該作者

本帖最後由 baldur 於 2014-9-3 18:05 編輯

= 區分每週出貨加總 =
在 test 中的 z 欄先加入
=ROUNDUP((INT((WEEKDAY(DATE(YEAR(A?),MONTH(A?),1),2)-1)+DAY(A?))/7),0)
來計算每月第幾週.
再區分加總, 以取得次數
=SUMPRODUCT((YEAR(test!$A$4:$A$40000)=YEAR($a3))*(MONTH(test!$A$4:$A$40000)=MONTH($a3))*(test!!$z4:$z40000=$b$2))
但加總數量, 該加在那呢 .... 昏

跳著試, 沒一個功能齊備的, 繼續想想囉... QQ

=_= )) zZ

帖子: 8
主題: 1
精華: 0
積分: 13
點名: 0
作業系統: Windows
軟體版本: 7
閱讀權限: 10
性別: 男
註冊時間: 2014-9-3
最後登錄: 2014-9-12

4^#

發表於 2014-9-4 09:29 | 只看該作者

回復 3# baldur
讀了一下置頂公告, "公式輸入的常用技巧(新手必讀)"
使用 sumproduct( (year A* monthA * week Z * ponumber Y), sum ) 第二個功能已經完成了.
week 使用時間加上工式換算置於 Z 欄中比對.
ponumber 使用 right 截取後二碼比對.

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

=_= )) zZ

帖子: 8
主題: 1
精華: 0
積分: 13
點名: 0
作業系統: Windows
軟體版本: 7
閱讀權限: 10
性別: 男
註冊時間: 2014-9-3
最後登錄: 2014-9-12

5^#

發表於 2014-9-4 14:21 | 只看該作者

本帖最後由 baldur 於 2014-9-4 14:22 編輯

回復 4# baldur
第一項功能用
sumproduct( (year A* monthA * week Z * <20), sumproduct(1-1/countif(E)) )
但除出來的數值有小數點且不正確呢...
sumproduct( (year A* monthA * week Z * <20), 1-1/countif(E) )
這方法也不行.

有建議嗎 ?

=_= )) zZ

帖子: 8
主題: 1
精華: 0
積分: 13
點名: 0
作業系統: Windows
軟體版本: 7
閱讀權限: 10
性別: 男
註冊時間: 2014-9-3
最後登錄: 2014-9-12

6^#

發表於 2014-9-4 16:06 | 只看該作者

回復 5# baldur
小弟目前發現不加上時間來判讀是可以算出不重複的數量. ( 手動填入月的列數位置 )
sumproduct( ((d<20)*(1/countif(E:E)) )

但加上時間來判讀後就每個月都為 0 了....
sumproduct( (year A* monthA * week Z * d<20), (1/countif(E) )

請問那有相關資料可以參考呢 ? 連取錯都沒方向呢 ... QQ

=_= )) zZ