ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

¨Ö°£¤p©ó¤@©w¼Æ¶q¦ý¤£­«ÂЪ«¥ó ªº¼Æ¶q

¨Ö°£¤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»Ý­n­pºâ¬Û¦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)

o.test.png

¦^´_ 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

TOP

¥»©«³Ì«á¥Ñ 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

TOP

¦^´_ 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

TOP

¥»©«³Ì«á¥Ñ 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

TOP

¥»©«³Ì«á¥Ñ 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))

«ä¦Ò¤¤....

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¤H¤£©È¿ù¡A´N©È¤£§ï¹L¡A§ï¹L¨Ã¤£Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD