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

[µo°Ý] ¦p¦ó¨ú±o³Ìªñªº»Ý¨D¤é´Á¥H¤Î¼Æ¶q

[µo°Ý] ¦p¦ó¨ú±o³Ìªñªº»Ý¨D¤é´Á¥H¤Î¼Æ¶q

½Ð±Ð¦U¦ì°ª¤â¡A¸Ó¦p¦ó³]©w¤½¦¡
20140531-1.jpg
2014-6-1 00:08

¤@¥÷²£¯à»Ý¨Dªí~·Q­nª¾¹D¦©°£¦b»s¶q¥H¥~³Ìªñ¤@­Ó»Ý¨D¤é&»Ý¨D¼Æ¶q
¨ä¤¤AAAAAAA®Æ¸¹¦b»s¦³6000¡A¥i¥Hº¡¨¬~6/19«eªº»Ý¨D¶q¡A6/19»Ý¨Dªº3000«h»Ý­n¦A¸É2500
BBBBBBB®Æ¸¹¦b»s¦³10000¡A­è¦n¥i¥Hº¡¨¬~6/18ªº»Ý¨D¡A©Ò¥H«Ý§ë®Æªº´N¬O6/20¡A¼Æ¶q«h¬°3000
­n¦p¦ó³]©wE & FÄ椽¦¡¡A±æ¦U¦ì°ª¤â«üÂI¡A·P¿E¤£ºÉ¡I
20140531.zip (4.85 KB)
Adam

¥»©«³Ì«á¥Ñ zz5151353 ©ó 2014-6-1 17:42 ½s¿è

C2
=SUM(G2:W2)

D2

=SUM(G2:W2)-B2
300 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

·PÁ zz5151353 ªº¦^À³
¤£¹L§Úªº°ÝÃD¬O­n³]©w
EÄ檺«Ý§ë¤é´Á¸òFÄ檺«Ý§ë¼Æ¶qªº¤½¦¡¡AÁÂÁ¡I
Adam

TOP

F2
{=IF(B2=MAX((SUBTOTAL(9,OFFSET(G2,,,,COLUMN(G2:W2)))<=B2)*SUBTOTAL(9,OFFSET(G2,,,,COLUMN(G2:W2)+1)))-B2,"",MAX((SUBTOTAL(9,OFFSET(G2,,,,COLUMN(G2:W2)))<=B2)*SUBTOTAL(9,OFFSET(G2,,,,COLUMN(G2:W2)+1)))-B2)}
300 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

·PÁ zz5151353 ªº¼ö¤ß¦^À³¡A¦ý¬O´ú¸Õ«á¦³¤@¨Ç°ÝÃD (¦b¤U¥\¤O¤£¨¬¬Ý¤£¥X­þ¸Ì¦³°ÝÃD?)
20140531-2.jpg
2014-6-1 23:13

20140531-1.zip (5.92 KB)
¥t¥~´N¬O¦]¬°­q³æ¤é´ÁÄæ¼Æªøµu¤£¤@(¤£¬O¨C¦¸³£¬OG~VÄæ)¡A©Ò¥H¦bC2Äæ¦ì¥Îªº¬O=-SUM(G2:AZ2)/2 (¹w¯d¦h¤@¨ÇÄæ¦ì¡A¥þ³¡¥[Á`·|¥]§t¨ì­ì¨Óªº¥[Á`«á¦A°£¥H2)
¦Ó¤£¬Oª½±µ¥Î=SUM(G2:V2)
¤]³\¬O§Ú´y­z±o¤£°÷²M·¡¡AÁ`¤§´N¬O§Æ±æ¯à°÷³]©w¤½¬O¯à°÷§Ö³t­pºâ¥X¦UºØª«®Æ¦b­þ¤@­Ó¤é´ÁÁٯʤ֦h¤Ö¼Æ¶q
¦Ó¤£¥²¤@­Ó¤@­Ó¤â°Ê­pºâ¨C¤@ºØª«®Æ­þ¤@¤Ñªº­q³æ§ë®ÆÁÙ¤£¨¬¡H
³o¼Ë´N¥i¥H®Ú¾Ú¯Ê®Æ¤é´Á¬Ý¥X«æ­¢©Ê¦Ó±N¦@¥Î®Æ¥óÀu¥ý°tµ¹¥æ´Á¸ûªñªºª«®Æ
Adam

TOP

¸Õ¤F¤@­Ó§«ôÁÙ¬O¤£¦æ¡A½Ð¦U¦ì°ª¤â¦ù¥X´©¤â¡A¦b¤U·P¿E¤£ºÉ~~
Adam

TOP

¦^´_ 1# adam2010
¸Õ¸Õ¬Ý¡G
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim ¦b»s¶q, »Ý¨D¶q As Long
  3.     Dim r1, c1 As Integer
  4.    
  5.     '¦pªG ¦b»s¶q(ÄæB) ©Î »Ý¨D¶q(ÄæG¥H«á) ¦³²§°Ê, «h
  6.     If Target.Column = 2 Or Target.Column > 6 Then
  7.         r1 = Target.Row    '¸ê®Æ²§°Ê©Ò¦b¦C
  8.         ¦b»s¶q = Cells(r1, 2)
  9.         »Ý¨D¶q = 0
  10.         c1 = 6
  11.         Do
  12.             c1 = c1 + 1
  13.             »Ý¨D¶q = »Ý¨D¶q + Cells(r1, c1)
  14.         Loop Until »Ý¨D¶q > ¦b»s¶q
  15.         Cells(r1, 5) = Cells(1, c1)  '«Ý§ë¤é´Á
  16.         Cells(r1, 6) = »Ý¨D¶q - ¦b»s¶q '«Ý§ë¼Æ¶q
  17.     End If
  18. End Sub
½Æ»s¥N½X
ps:
1. ¬°¦ó D2 ¤£¬O =C2-B2, ¦Ó¬O  =B2+C2?(µ§»~?)
2. Á`¶q¤½¦¡(CD2)»P C2¤½¦¡¤@¼Ë, ¥i§R, («O¯dC2¤½¦¡§Y¥i, §ó¦³¼u©Ê)

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2014-6-7 12:44 ½s¿è

§ó¥¿, ¼W¥[¤@¦C(ÁקK¿ù»~²£¥Í):
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ¦b»s¶q, »Ý¨D¶q As Long
    Dim r1, c1 As Integer
   
    '¦pªG ¦b»s¶q(ÄæB) ©Î »Ý¨D¶q(ÄæG¥H«á) ¦³²§°Ê, «h
    If Target.Column = 2 Or Target.Column > 6 Then
        r1 = Target.Row    '¸ê®Æ²§°Ê©Ò¦b¦C
        If Not IsNumber(Cells(r1, 2)) Then Exit Sub
        ¦b»s¶q = Cells(r1, 2)
        »Ý¨D¶q = 0
        c1 = 6
        Do
            c1 = c1 + 1
            »Ý¨D¶q = »Ý¨D¶q + Cells(r1, c1)
        Loop Until »Ý¨D¶q > ¦b»s¶q
        Cells(r1, 5) = Cells(1, c1)  '«Ý§ë¤é´Á
        Cells(r1, 6) = »Ý¨D¶q - ¦b»s¶q '«Ý§ë¼Æ¶q
    End If
End Sub

TOP

·PÁ yen956 ¦ù¥X´©¤â~
1. ¬°¦ó D2 ¤£¬O =C2-B2, ¦Ó¬O  =B2+C2?(µ§»~?)¡÷¦]¬°C2= - SUM(G2:AZ2)/2 (a.¦ÑÁ󻡧e²{­t¼Æ¥L¤ñ¸û¯à²z¸Ñ¬O¤£¨¬ªº¡A¦ý¬O­n§ï¬°¥Î¥¿¼Æ¤]¥i¥H b.©Ôªø°£¥H2¬O¦]¬°¨C¦¸¼´¥X¨Óªº¥æ´Áªø«×¤£¤@©w)¡A©Ò¥HD2=B2+C2
2. Á`¶q¤½¦¡(CD2)»P C2¤½¦¡¤@¼Ë, ¥i§R, («O¯dC2¤½¦¡§Y¥i, §ó¦³¼u©Ê)¡÷¹ï§r~§R°£«áC2ªº¤½¦¡´N¤£¥Î°£¥H2¤F¡A¤Ï¥¿³Ì¥kÃ䪺¥[Á`¤]¥Î¤£¤Ó¨ì
¸g´ú¸ÕOK¡A¤£¹L¼W¥[¨º¬q°»¿ù«h·|¥X²{¦ì©w¸qªº¿ù»~¡A§Ú¥ýmark¨º¦æ¡A´ú¸ÕOK¡I¯u¬O¤Ó¼F®`¤F¡A¦A¦¸·PÁ yen956 ¤j~
20140531-3.jpg
2014-6-7 14:28
Adam

TOP

¦^´_ 9# adam2010
©êºp, À³¸Ó¥[¤W Application.IsNumber ¤~¹ï!!
        If Not Application.IsNumber(Cells(r1, 2)) Then Exit Sub

TOP

        ÀR«ä¦Û¦b : ¸Ü¦h¤£¦p¸Ü¤Ö¡A¸Ü¤Ö¤£¦p¸Ü¦n¡C
ªð¦^¦Cªí ¤W¤@¥DÃD