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

ÂI¿ï¥[Á`

ÂI¿ï¥[Á`

¦U¦ì«e½ú§A­Ì¦n!
         Sub ¼úª÷()
             [E2] = Selection
             [G1] = "=SUMIF(INDIRECT(""$E$3:$E$300""),$E$2,INDIRECT(""$G$3:$G$300""))"
             [G1] = [G1]'.Value
         End Sub
         °ÝÃD¦pªþÀÉ»¡©ú
         ½Ðª¾¹Dªº«e½ú,¤£§[½ç±ÐÁÂÁ¦A¤T!!

LeoV71.rar (11.08 KB)

¦^´_ 7# stillfish00
«e½ú!«z!!¯u¥Ì®¦´£¨Ñ¦p¦¹§¹¾ãµ{¦¡...¨¯­WÅoÁÂÁ¦A¤T!!

TOP

¦^´_ 6# myleoyes
´XºØ¤èªk§A°Ñ¦Ò¬Ý¬Ý
Sub Ex1_1()  '®e©öÀ´
    Dim r As Long
    r = [K65536].End(xlUp).Row + 1
    Range("K" & r) = Sheet4.[I1]
    Range("L" & r) = "=K" & r & "-K" & r - 1 & "+" & Sheet2.[AZ19] & "-" & Sheet2.[AZ20]
End Sub

Sub Ex1_2()   '¤½¦¡¤¤¦ì§}¥Îaddress¨ú´N¥i¥H¤F
    With [K65536].End(xlUp).Offset(1, 0)
        .Value = Sheet4.[I1]
        .Offset(0, 1).Value = "=" & .Address(0, 0) & "-" & .Offset(-1, 0).Address(0, 0) & "+" & Sheet2.[AZ19] & "-" & Sheet2.[AZ20]
    End With
End Sub   

Sub Ex1_3()  '¤£«O¯d¤½¦¡ºâ¥X¨Ó¤]¬O¤@¼Ëªº
    With [K65536].End(xlUp).Offset(1, 0)
        .Value = Sheet4.[I1]
        .Offset(0, 1).Value = .Value - .Offset(-1, 0).Value + Sheet2.[AZ19] - Sheet2.[AZ20]
    End With   
End Sub

TOP

¦^´_ 5# stillfish00
«e½ú!¤£¦n·N«ä¤p§Ì§â§A§Ë½k¶î??
     À³¸Ó¬O³o»ò·Q¹³±NL3=K3-K2+Sheet2!$AZ$19-Sheet2!$AZ$20
     «ö¶sµ{¦¡½Æ»sL3¦ÜL4©Ò¥H
     L4=K4-K3+Sheet2!$AZ$19-Sheet2!$AZ$20
     ¦ý¬O»Ý¨D¬O«O¯dK4-K3ªº¤½¦¡
     ±N+Sheet2!$AZ$19-Sheet2!$AZ$20Åܦ¨­È
     ²{¦bSheet2!$AZ$19=5689
     Sheet2!$AZ$20=12356
     ©Ò¥HL4=K4-K3+5689-12356
     ¦]¬°Sheet2!$AZ$19»PSheet2!$AZ$20¬O°ÊºA­È
     ¦]¦¹$AZ$19»P$AZ$20¤£¥H¤½¦¡«¬ºA§e²{¥²¶·¬°­È
     ¦p¦¹¸ÑÄÀ«e½ú!©ú¥Õ§_?½Ð¦A½ç±Ð¨¯­WÅo!ÁÂÁ¦A¤T!!

TOP

¦^´_ 4# myleoyes
¬Ý¤£À´§Aªº»Ý¨D...
[L4]=[K4]-[K3]+5689-12356  ?

TOP

¦^´_ 3# Hsieh
stillfish00 «e½ú!µ{¦¡OK!ÁÂÁ¦A¤T!!
°¸¹³«e½ú!µ{¦¡¤]OK!¦ý©¿²¤¤p§Ìªº­n¨D¡Ð½Ð°Ý¦b¤£¼W¥[E2¸É§U®æ¤§¤UÁÂÁ¦A¤T!!
³o¼Ë¤]¥i¥H
Sub ¼úª÷()
     [G1] = Evaluate("=SUMIF($E$3:$E$300," & Selection.Address & ",$G$3:$G$300)")
End Sub
¦A³Â·Ð¨â¦ì«e½ú³o­Ó°ÝÃD¦p¤U
  µ²ªGL4=K4-K3+5689-12356
Sub Ex()
        [K65536].End(xlUp)(2, 1) = Sheet4.[I1]
        [L3].Copy [L65536].End(xlUp)(2, 1)
        [L65536].End(xlUp)(1, 1) = "=" & [L65536].End(xlUp)(1, 1) & "+" & Sheet2.[AZ19] & "-" & Sheet2.[AZ20]
        Sheet2.[AZ19:AZ20] = ""
        Columns("K:L").EntireColumn.AutoFit
End Sub

LeoV71-1.rar (12.79 KB)

TOP

¦^´_ 1# myleoyes
  1. Sub ¼úª÷()
  2.          [E2] = Selection
  3.          [G1] = Application.SumIf([E3:E300], [E2], [G3:G300])
  4. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 1# myleoyes
  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2. If Target.Count = 1 Then
  3. '¿ï¾Üªº¬°³æ¤@Àx¦s®æ
  4.     If Not Intersect(Target, Range("E3:E300")) Is Nothing Then
  5.     '¿ï¾ÜªºÀx¦s®æ¦bE3:E300¤º
  6.         [G1] = Evaluate("=SUMIF($E$3:$E$300," & Target.Address & ",$G$3:$G$300)")
  7.         '­pºâ¤½¦¡«á¶Ç¦^G1
  8.     End If
  9. End If
  10. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¯Ê¤fªºªM¤l¡A¦pªG´«¤@­Ó¨¤«×¬Ý¥¦¡A¥¦¤´µM¬O¶êªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD