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

bridetobe

¦s³f.rar (34.12 KB)

¤U¸ü¦¸¼Æ: 9

2014-7-11 08:57

·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§@°_¨Ó¶Ü

Hsieh

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

¤U¸ü (31.55 KB)

2014-7-11 10:53

bridetobe

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

¤U¸ü¦¸¼Æ: 7

2014-7-17 08:42

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?

Hsieh

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

bridetobe

¦^´_ 4# Hsieh


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

bridetobe

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

¤U¸ü¦¸¼Æ: 6

2014-8-15 21:29

GBKEE

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