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

[µo°Ý] ½Ð°Ý¯S©wÄæ¦ìªº­pºâ

[µo°Ý] ½Ð°Ý¯S©wÄæ¦ìªº­pºâ

¦s³f.rar (34.12 KB)

·Q½Ð°Ý¤@¤U
¦pªG§Ú·Q¦b¤p­pÄæ«á­±·s¼W¤@±ø¨Ï¥Î¶q
´N¬O¤ñ¦p±N7/9ªº"#702"¤p­pÄ檺¼Æ¦r§ì¥X¨Ó
µM«á¥Î7/10ªº"#702"¤p­pÄæ¼Æ¦r´î¥h7/9ªº¼Æ¦r
¦pªG«e¤@¤Ñ§ì¤£¨ì ´NÅã¥Ü0
¦]¬°¸ê®Æ±Æ§Ç·|ÅÜ°Ê(EX:¥H¤p­p»¼¼W±Æ§Ç©Î¬O¥H¤é´Á±Æ§Ç©Î¬O¥H«~¦W±Æ§Ç)
µLªk¥Î¯S©wÄæ¦ì¬Û´î
¦³Åܰʪº¸ê®Æ
³o­Ó³¡¥÷¦³¿ìªk§@°_¨Ó¶Ü

¦^´_ 1# bridetobe


    ¥u­pºâ«e¤@¤Ñªº¥Î¶q¶Ü?
¸Õ¸Õ¬Ý
H2¤½¦¡
=SUMPRODUCT((OFFSET($A$2,,,COUNT(A:A),)=A2-1)*(OFFSET($B$2,,,COUNT(A:A),)=B2)*OFFSET($G$2,,,COUNT(A:A),))
¦V¤U½Æ»s
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh


¤£¦n·N«ä «e­±¦³ÂI´y­z¿ù»~
À³¸Ó¬O¬Q¤Ñªº¤p­p+¬Q¤Ñªº¶i³f-¤µ¤Ñªº¤p­p
¦bªþ¤W¤@¦¸ÀÉ®×
¦s³fºÞ²z(20140716).rar (41.07 KB)

7/4ªº#702¨Ï¥Î¶q´N¬O¬Q¤Ñªº¤p­p24+¬Q¤Ñªº¶i³f34-¤µ¤Ñªº¤p­p34=8
³o¼Ë¤l
¦pªG­pºâ¥X¨Ó¬O­t¼Æ ´N¼g0

³o¼Ë¤l©O?

TOP

¦^´_ 3# bridetobe
H2¤½¦¡
=MAX(0,(SUMPRODUCT(($B$2:$B$1063=B2)*($A$2:$A$1063=A2-1)*$C$2:$C$1063)+SUMPRODUCT(($B$2:$B$1063=B2)*($A$2:$A$1063=A2-1)*$G$2:$G$1063))-SUMPRODUCT(($B$2:$B$1063=B2)*($A$2:$A$1063=A2)*$G$2:$G$1063))
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh


¤F¸Ñ «D±`·P¿E!!!

TOP

¦^´_ 4# Hsieh


ª©¤j ¤£¦n·N«ä §Ú·Q½Ð°Ý¤@¤U
¦]¬°«á¨Ó¨Ï¥Î¤W¦³¨â­Ó«Ü¤jªº°ÝÃD
¤£ª¾¹D¦p¦ó¸Ñ¨M
1.¦]¬°¦pªG¹J¨ì¬P´Á¤@ ´N¬O¥H¬P´Á¤­·í°µ«e¤@¤Ñ ¦ý¬O¹w³]¬O´î¤@¤Ñ ©Ò¥H¬P´Á¤@ªº´NµLªk­pºâ
ex 7/21ªº#722¤p­p¬O10
     7/18ªº#722¤p­p¬O11
·Ó³o¼Ë¨Ó»¡ ¨Ï¥Î¶qÀ³¸Ó¬O1
¦ý¬O¦]¬°¹J¨ì¤»¤é ¨S¿ìªk­pºâ
³o¦³¿ìªk¸Ñ¨M¶Ü?
2.¥Ñ©ó¸ê®Æ·|¤@ª½·s¼W
H2¤½¦¡=MAX(0,(SUMPRODUCT(($B$2:$B$1063=B2)*($A$2:$A$1063=A2-1)*$C$2:$C$1063)+SUMPRODUCT(($B$2:$B$1063=B2)*($A$2:$A$1063=A2-1)*$G$2:$G$1063))-SUMPRODUCT(($B$2:$B$1063=B2)*($A$2:$A$1063=A2)*$G$2:$G$1063))

¦p¦¹¤@¨Ó ¬õ¦r³¡¤À´N¥²¶·¦A¨C¤Ñ­×§ï«á ¦A¥h¤@¤@­×§ï°Ñ·Ó¨ì³Ì¤U­±¨º¦C µM«á¦b¦V¤U½Æ»s
¤£ª¾¹D³o³¡¤À¦³¨S¦³¿ìªk¸Ñ¨M

³Ì¥D­nªº°ÝÃD¬O²Ä1ÂI
¦pªG¨ç¼Æ³¡¤À¨S¿ìªk¸Ñ¨M
¬O§_¦³¨ä¥L¸û¦nªº¤èªk¥i¥Hºâ¥X¨Ï¥Î¶q?

ÁÂÁÂ
ªþ¤W»¡©úÀÉ®×»¡©ú¤W­±ªºex
¶î®Æ¦s³fºÞ²z(20140808).rar (68.86 KB)

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-8-16 11:50 ½s¿è

¦^´_ 6# bridetobe
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ū¥X()
  3.     Dim a_date, Rng(1 To 2) As Range
  4.     Application.Calculation = xlCalculationManual  '¤â°Ê­pºâ
  5.     Application.StatusBar = False
  6.     With Sheets("¾Þ§@")
  7.         Set Rng(1) = .[A2:F2]
  8.         Set Rng(2) = .Cells(1, .Columns.Count)
  9.        .Range(Rng(1)(2, 1), Rng(1).End(xlDown)).Resize(, 7) = ""
  10.         .[B1].Value = InputBox("¿é¤J¤é´Á(¨Ò2014/7/1):", , "2014/7/1")
  11.         Rng(2) = "¤é´Á"
  12.         Rng(2).Offset(1) = .[B1]
  13.         'AdvancedFilter ¶i¶¥¿z¿ï
  14.       '¶i¶¥¿z¿ï:ªº·Ç«h½d³ò -> Rng(2).Resize(2)
  15.       Sheets("¦s³f¸ê®Æ").UsedRange.AdvancedFilter xlFilterCopy, Rng(2).Resize(2), Rng(1)
  16.         Rng(2).EntireColumn = ""
  17.         If Rng(1).End(xlDown).Row <> .Rows.Count Then
  18.             Set Rng(2) = Rng(1).Cells(2, 1)
  19.             Do While Rng(2) <> ""
  20.                 Rng(2).Cells(1, "G") = ¤W¤@­Ó¤p­p(.[B1], Rng(2).Text) - Rng(2).Cells(1, "F")
  21.                 If Rng(2).Cells(1, "G") < 0 Then Rng(2).Cells(1, "G") = 0
  22.                  Set Rng(2) = Rng(2).Cells(2)
  23.             Loop
  24.             MsgBox "Ok"            
  25.         Else
  26.             MsgBox "¨S¦³¸ê®Æ"
  27.         End If
  28.     End With
  29.    ' Application.Calculation = xlAutomatic            '¦Û°Ê­pºâ
  30.     '¦Û°Ê¿z¿ï«á·|¦³¤u§@ªí­«ºâªº°Ê§@,µ{¦¡·|µ¥­Ô­«ºâ§¹²¦,¦AÄ~Äò°õ¦æ¤U¥h.
  31. End Sub
  32. 'AutoFilter: ¦Û°Ê¿z¿ï
  33. Private Function ¤W¤@­Ó¤p­p(¤é´Á As String, «~¦W As String) As Integer
  34.     Dim Rng As Range
  35.     With Sheets("¦s³f¸ê®Æ").UsedRange
  36.         '¦Û°Ê­pºâ:·í¦Û°Ê¿z¿ï«á·|¦³¤u§@ªí­«ºâªº°Ê§@,¼vÅTµ{¦¡°õ¦æªº³t«×
  37.         '¦Û°Ê¿z¿ï «ü©wÄæ¦ì,·Ç«h
  38.         .AutoFilter 1, ">" & ¤é´Á
  39.         .AutoFilter 2, "=" & «~¦W        
  40.         Set Rng = .SpecialCells(xlCellTypeVisible)
  41.         If Rng.Areas(1).Rows.Count > 1 Then
  42.             ¤W¤@­Ó¤p­p = Rng.Areas(1).Cells(2, "g")
  43.         Else
  44.             ¤W¤@­Ó¤p­p = Rng.Areas(2).Cells(1, "g")
  45.         End If
  46.     End With
  47. End Function
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ª¾ÃÑ­n¥Î¤ßÅé·|¡A¤~¯àÅܦ¨¦Û¤vªº´¼¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD