¨Ö°£¤p©ó¤@©w¼Æ¶q¦ý¤£«ÂЪ«¥ó ªº¼Æ¶q
- ©«¤l
- 8
- ¥DÃD
- 1
- ºëµØ
- 0
- ¿n¤À
- 13
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows
- ³nÅ骩¥»
- 7
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-9-3
- ³Ì«áµn¿ý
- 2014-9-12
|
¨Ö°£¤p©ó¤@©w¼Æ¶q¦ý¤£«ÂЪ«¥ó ªº¼Æ¶q
¥»©«³Ì«á¥Ñ baldur ©ó 2014-9-3 11:37 ½s¿è
¤p§Ì·Q½Ð±Ð¦U¦ì¤j¯«, ªþ¥ó¤º²Ä¤@¦C¦³ ( date, po, item, o.qty, s.qty, r.qty)
1. ¥Ø«e»Ýnpºâ¬Û¦P¦~¤ë¤U, o.qty ¤p©ó 20 ¦ý item ¤£«½Æ, ¤Î«½Æªº¦¸¼Æ.
2. ¥[Á`¦U¦~¤ë¤U¤£¦P¶g¼Æªº s.qty ¨ÌPO ¤ÀÃþ
[attach]19078[/attach]
¹ï°}¦C¤Î¦h±ø¥ópºâW¤â¤¤, ¥i§_´f¤©«üÂI©O ?
Àô¹Ò¬° office 2000 excel.
o.test.rar (2.83 KB)
|
-
-
o.test.png
(52.54 KB)
|
|
|
|
|
|
- ©«¤l
- 8
- ¥DÃD
- 1
- ºëµØ
- 0
- ¿n¤À
- 13
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows
- ³nÅ骩¥»
- 7
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-9-3
- ³Ì«áµn¿ý
- 2014-9-12
|
¦^´_ 5# baldur
¤p§Ì¥Ø«eµo²{¤£¥[¤W®É¶¡¨Ó§PŪ¬O¥i¥Hºâ¥X¤£«½Æªº¼Æ¶q. ( ¤â°Ê¶ñ¤J¤ëªº¦C¼Æ¦ì¸m )
sumproduct( ((d<20)*(1/countif(E:E)) )
¦ý¥[¤W®É¶¡¨Ó§PŪ«á´N¨CӤ볣¬° 0 ¤F....
sumproduct( (year A* monthA * week Z * d<20), (1/countif(E) )
½Ð°Ý¨º¦³¬ÛÃö¸ê®Æ¥i¥H°Ñ¦Ò©O ? ³s¨ú¿ù³£¨S¤è¦V©O ... QQ |
|
=_= )) zZ
|
|
|
|
|
- ©«¤l
- 8
- ¥DÃD
- 1
- ºëµØ
- 0
- ¿n¤À
- 13
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows
- ³nÅ骩¥»
- 7
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-9-3
- ³Ì«áµn¿ý
- 2014-9-12
|
¥»©«³Ì«á¥Ñ baldur ©ó 2014-9-4 14:22 ½s¿è
¦^´_ 4# baldur
²Ä¤@¶µ¥\¯à¥Î
sumproduct( (year A* monthA * week Z * <20), sumproduct(1-1/countif(E)) )
¦ý°£¥X¨Óªº¼ÆȦ³¤p¼ÆÂI¥B¤£¥¿½T©O...
sumproduct( (year A* monthA * week Z * <20), 1-1/countif(E) )
³o¤èªk¤]¤£¦æ.
¦³«Øij¶Ü ? |
|
=_= )) zZ
|
|
|
|
|
- ©«¤l
- 8
- ¥DÃD
- 1
- ºëµØ
- 0
- ¿n¤À
- 13
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows
- ³nÅ骩¥»
- 7
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-9-3
- ³Ì«áµn¿ý
- 2014-9-12
|
¦^´_ 3# baldur
Ū¤F¤@¤U¸m³»¤½§i, "¤½¦¡¿é¤Jªº±`¥Î§Þ¥©(·s¤â¥²Åª)"
¨Ï¥Î sumproduct( (year A* monthA * week Z * ponumber Y), sum ) ²Ä¤GÓ¥\¯à¤w¸g§¹¦¨¤F.
week ¨Ï¥Î®É¶¡¥[¤W¤u¦¡´«ºâ¸m©ó Z Ä椤¤ñ¹ï.
ponumber ¨Ï¥Î right ºI¨ú«á¤G½X¤ñ¹ï.
²{¦b«ä¦Ò²Ä¤@¶µ¥\¯à¦p¦ó§¹¦¨¤F. |
|
=_= )) zZ
|
|
|
|
|
- ©«¤l
- 8
- ¥DÃD
- 1
- ºëµØ
- 0
- ¿n¤À
- 13
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows
- ³nÅ骩¥»
- 7
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-9-3
- ³Ì«áµn¿ý
- 2014-9-12
|
¥»©«³Ì«á¥Ñ baldur ©ó 2014-9-3 18:05 ½s¿è
= °Ï¤À¨C¶g¥X³f¥[Á` =
¦b test ¤¤ªº z Äæ¥ý¥[¤J
=ROUNDUP((INT((WEEKDAY(DATE(YEAR(A?),MONTH(A?),1),2)-1)+DAY(A?))/7),0)
¨Ópºâ¨C¤ë²Ä´X¶g.
¦A°Ï¤À¥[Á`, ¥H¨ú±o¦¸¼Æ
=SUMPRODUCT((YEAR(test!$A$4:$A$40000)=YEAR($a3))*(MONTH(test!$A$4:$A$40000)=MONTH($a3))*(test!!$z4:$z40000=$b$2))
¦ý¥[Á`¼Æ¶q, ¸Ó¥[¦b¨º©O .... ©ü
¸õµÛ¸Õ, ¨S¤@Ó¥\¯à»ô³Æªº, Ä~Äò·Q·QÅo... QQ |
|
=_= )) zZ
|
|
|
|
|
- ©«¤l
- 8
- ¥DÃD
- 1
- ºëµØ
- 0
- ¿n¤À
- 13
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows
- ³nÅ骩¥»
- 7
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-9-3
- ³Ì«áµn¿ý
- 2014-9-12
|
¥»©«³Ì«á¥Ñ baldur ©ó 2014-9-3 14:17 ½s¿è
Àˬd¬Û¦P¦~¤ë¬O¥i¥H¥¿±`p¼Æ.
=SUMPRODUCT((YEAR(test!$A$2:$A$1018)=YEAR($a3))*(MONTH(test!$A$2:$A$1018)=MONTH($a3)))
¥[¤W¤p©ó¦h¤Ö¼Æ¶q, ¦ýºâ¥X¨Óªº¼ÆȬO D ³oÄ檺¥þ³¡¥[Á`......
=SUMPRODUCT((YEAR(test!$A$2:$A$1018)=YEAR($a3))*(MONTH(test!$A$2:$A$1018)=MONTH($a3))*countif(test!$d2:D1018,"<20"))
¥[¤W¦³¦h¤Ö«½Æ, ¤]ÁÙ¬O D ¦³¦h¤Ö«½Æ¦Ó¤£¬O¤p§Ìnªº¼ÆÈ©O....
=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))
«ä¦Ò¤¤.... |
|
|
|
|
|
|