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

¦p¦ó¨D¥X¦P¤@¤é´ÁªºÁ`µ§¼Æ

¦p¦ó¨D¥X¦P¤@¤é´ÁªºÁ`µ§¼Æ

¸ê®Æ¦³¤£¦P¤é´Áªº,¦P¤@¤é´Á¤S¦³¦hµ§,¦baÄ榳½s¸¹,½Ð°Ý,¦p¦ó¨D¥X¦P¤@¤é´ÁªºÁ`¦@µ§¼Æ,¦bJÄ椺

¦P¤é¥[Á`¦C¤J³Ì«á¤@¦C(SUM&IF) - ½Æ»s.zip (38.76 KB)

joyce

¦^´_ 1# leiru
¿ï¨úJ4Àx¦s®æ
³]©w¤U¦C¦U­Ó©w¸q¦WºÙ
k=COUNT(Sheet1!$C:$C)
y=OFFSET(Sheet1!$C$4,,,k,)
m=OFFSET(Sheet1!$C$4,,1,k,)
d=OFFSET(Sheet1!$C$4,,2,k,)
s=LOOKUP(2,1/(y&"/"&m&"/"&d=Sheet1!$I4),ROW(y))
a=OFFSET(Sheet1!$A$1,,,s,)
J4¤½¦¡
=IF(SUMPRODUCT((y&"/"&m&"/"&d=I4)*1)<>0,LOOKUP(k+1,a),"")
¦V¤U½Æ»s
¦P¤é¥[Á`¦C¤J³Ì«á¤@¦C(SUM&IF) .zip (40.67 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-6-30 08:56 ½s¿è

¦^´_ 1# leiru
1¡B©w¸q¦WºÙ¡u½s¸¹¡v¡A°Ñ·Ó¨ì¿é¤J
=Sheet1!$A$4:$A$354
©w¸q¦WºÙ¡udate¡v¡A°Ñ·Ó¨ì¿é¤J
=Sheet1!$C$4:$C$354&"/"&Sheet1!$D$4:$D$354&"/"&Sheet1!$E$4:$E$354
2¡B¨Ì1#»Ý¨D¡uJÄæ¬O°Ñ¦ÒAÄæ²Î­p¡v¡A¬GÀx¦s®æJ4¿é¤J
=SUMPRODUCT((date=$I4)*(½s¸¹<>" "))
¦V¤U½Æ»s¤½¦¡
¥H¤W©å¨£½Ð°Ñ¦Ò¡I

TOP

¦^´_ 2# softsadwind

·|¦³¤@ÂI­·ÀI(¦pªG­è¦nª÷ÃB¦³2µ§¤@¼Ë®É)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¸Õ¸Õ
J4=OFFSET($A$1,MATCH(K4,G:G,0)-1,)  
¤U©Ô

TOP

¦^´_ 1# leiru


    J4=MATCH(K4,G:G,0)-3
    J5=MATCH(K5,G:G,0)-MATCH(K4,G:G,0)   ¡A¤U©Ô
    °Ñ¦Ò¬Ý¬Ý
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

        ÀR«ä¦Û¦b : ¦Û¤v®`¦Û¤v¡A²ö¹L©ó¶ÃµoµÊ®ð¡C
ªð¦^¦Cªí ¤W¤@¥DÃD