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

´Æ¤âªºexcel¹Bºâ°ÝÃD¡A¦p¦ó§ïµ½??

´Æ¤âªºexcel¹Bºâ°ÝÃD¡A¦p¦ó§ïµ½??

¥»©«³Ì«á¥Ñ ÂŤÑÄR¦À ©ó 2016-1-26 14:18 ½s¿è

©ú²ÓÅÜ°Ê°O¿ý.rar (148.6 KB)
excel.jpg
2016-1-26 14:01

ªþÀɬO¤@­Ó¤p§Ì¥­±`¥Î¦b¬ö¿ýªºexcel¥Ø«e¦³¨Ç´Æ¤âªº¹Bºâ°ÝÃDÁٳ·Ъ©¤W¤j¤jÀ°§Ú¤@¤U

¦p¹Ï©Ò¥Ü¡A¥ªÃä¬O§Ú¥­®É¦b¬ö¿ýªºÀx¦s®æ¡A¥kÃä¬O­pºâ¥ÎªºÀx¦s®æ¡A¦ý¬O¦]¬°¥kÃä­pºâªºÀx¦s®æ¸Ì­±§Ú¦³¼g¤@¨Ç¨ç¼Æ¡A³y¦¨¾ã­Óexcel¦b¶]ªº®É­Ô¥ªÃäµLªk¬ö¿ý©Î¬O¾ã­Ó·í±¼(¦]¬°©Ò¼g¨ç¼Æ¤Ó¦YCPU©M°O¾ÐÅé)¡A½Ð°Ý¤@¤Uª©¤W¤j¤j¤pªº³o­Ó°ÝÃDÀ³¸Ó«ç»ò¸Ñ¨M¤~¦n??

§Ú¦³·Q¥X¤@¨Ç¸Ñ¨M¤è¦¡¡AµL©`¹ïVBA¤£¬O¤Ó¼ô¡A¦b·Ð½Ðª©¤W°ª¤âÀ°À°¦£

¸Ñ¨M¤è¦¡
1.Åý¥ªÃäA-F¦C§Y®É¹Bºâ(A2-F2¬ODDE©Ò¥H»Ý­nÀH®É§ó·s¤~¯à±µ¦¬¸ê®Æ)¡AR-T¦C¨C¤ÀÄÁ¹Bºâ¤@¦¸¡A§ó·s§¹«á¼g¦¨­È¦Ó¤£¬O¤½¦¡¡A³o¼Ëªº¤è¦¡¥i¥H¶Ü??(¤£ª¾¹D¦P¤@­Ósheet¥i¤£¥i¥H¤£¦PÀW²v§Y®É¹Bºâ)

2.ÅܧóR-T¦Cªº¨ç¼Æ¼gªk¡AÅý¾ã­Óµ{¦¡¶]°_¨Ó¤£­n¨º»ò¦Y¸ê·½

3.±NS-T¦Cªº¨ç¼Æ¼g¦bVBA¸Ì­±¡A¨C¤ÀÄÁ°õ¦æ¤@¦¸°õ¦æ§¹«á±N¤½¦¡¼g¦¨­È

·Ð½Ðª©¤Wªº°ª¤â¤j¤jÀ°À°¤p§Ì

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2016-1-26 20:00 ½s¿è

¦^´_ 1# ÂŤÑÄR¦À
¦b shtRTD (RTD) ªí³æ¤º RecordPrice()
¥[¤J STSumifs WR, WR¡A ¦p¤U¡G
  1. Option Explicit

  2. Sub RecordPrice()
  3.     Dim WR As Long
  4.     Dim I As Byte

  5.     If Range("P2") < 1 Then Exit Sub

  6.     WR = Range("A1").End(xlDown).Row + 1
  7.     '  ActiveWindow.ScrollRow = WR - 5     '  ¥uÅã¥Ü³Ì·s´Xµ§¸ê®Æ
  8.     If (WR = 3) Or _
  9.             (Range("F" & WR - 1) <> Range("F2")) Then   '  Á`¶q¦³²§°Ê®É¤~°O¿ý
  10.         For I = 1 To 6
  11.             Cells(WR, I) = Cells(2, I)
  12.         Next 'I
  13.         
  14.         STSumifs WR, WR                         '  ¸ê®Æ¦P¨B±N¼Æ­È¼g¤J¨ì R¡AS¡AT ¤TÄ椺
  15.     End If
  16.     '  With ActiveWindow
  17.         '  If Intersect(Cells(WR, "B"), .VisibleRange) Is Nothing Then .SmallScroll 5
  18.     '  End With
  19. End Sub

  20. Private Function STSumifs(ByVal endST As Long, Optional startST As Long = 3)
  21.     Dim cts As Long
  22.     Dim btm As Long
  23.    
  24.     btm = Range("A1").End(xlDown).Row
  25.    
  26.     For cts = startST To endST
  27.         Cells(cts, "R") = IIf(cts = 2, "", IIf(cts = 3, "=MAX(B3:B" & btm & ")", "=R" & (cts - 1) & "-1"))
  28.         Cells(cts, "R") = Cells(cts, "R").Value
  29.         Cells(cts, "S") = "=IF(SUMIFS($E3:$E" & btm & ",$D3:$D" & btm & ",S$1,$B3:$B" & btm & ",$R" & (cts - 1) & ")=0," & Chr(34) & Chr(34) & ",SUMIFS($E3:$E" & btm & ",$D3:$D" & btm & ",S$1,$B3:$B" & btm & ",$R" & (cts - 1) & "))"
  30.         Cells(cts, "S") = Cells(cts, "S").Value
  31.         Cells(cts, "T") = "=IF(SUMIFS($E3:$E" & btm & ",$D3:$D" & btm & ",T$1,$B3:$B" & btm & ",$R" & (cts - 1) & ")=0," & Chr(34) & Chr(34) & ",SUMIFS($E3:$E" & btm & ",$D3:$D" & btm & ",T$1,$B3:$B" & btm & ",$R" & (cts - 1) & "))"
  32.         Cells(cts, "T") = Cells(cts, "T").Value
  33.     Next cts
  34. End Function

  35. Sub Test()
  36.     Dim WR As Long
  37.    
  38.     WR = Range("A1").End(xlDown).Row
  39.     STSumifs WR
  40. End Sub
½Æ»s¥N½X
³Ì«á­±¤§ Test() ¬O¥iÅý§A¤@¦¸¦Û°Ê°õ¦æ§ó´« ²Ä 3 ¦æ ~ ²Ä 4903 ¦æ
R¡AS¡AT ¤TÄ檺¸ê®Æ­È¡C¦pªG¤£²§°Êªº¸Ü¡A´N¤£»Ý°õ¦æ¥¦¡C
¨ä¥¦µ{¦¡³¡¤À§¡«O¯d­ì»ª¡C¦]§ÚµL¨é°Ó³s½uµLªk´úª¾ª¬ªp¡C

TOP

¥»©«³Ì«á¥Ñ jackyq ©ó 2016-1-26 19:55 ½s¿è

1, 3 ¤èªk¥i¦æ
R-T¦Cªº»Ý¨D¨C¤ÀÄÁ¤~¹Bºâ¤@¦¸
«oÅܦ¨³vµ§¹Bºâ
¬Æ¦ÜÁÙ³v¾ú¥v
¤£ºC³£Ãø

TOP

SUMIFS , ºâ¨Ó´NºC, ¦óªp¥þÄæ¤Þ¥Î¡I¡I¡I

¥[¨â­Ó»²§UÄ椽¦¡¡G
¢Õ¢±¡G=IF($D2=S$1,$E2,"-")¡@¥k©Ô¢Ö¢±¡A¤U©Ô¨ì©³
¢á¢²¡G=SUMIF($B:$B,$R2,G:G)¡@¥k©Ô¢â¢²¡A¤U©Ô¨ì©³¡AÀx¦s®æ®æ¦¡¦Û­q¬°¡G#¡@¡]¢¯­È¤£Åã¥Ü¡^

¥t¥~¡A®É¶¡¤½¦¡§ï¦p¤U¡G
¢Ò¢±¡G=--TEXT(A2,"hhmm")
¢á¢°¡G=--TEXT(S2,"hhmm")
¢â¢°¡G=--TEXT(T2,"hhmm")

TOP

TOP

¦^´_ 2# c_c_lai
°õ¦æ«á¦³´X­Ó°ÝÃD¸òC¤j»¡¤@¤U
1.R¦C¦]¬°·|¸òµÛ«ü¼Æ³Ð·s°ª¦Ó§ïÅÜ©Ò¥H¤£¯à¬O¼g¦ºªº

2.R¦C¥u»Ý­n§ì³Ì°ª¨ì³Ì§C°Ï¶¡200­Ó¥ª¥k¡A¤£»Ý­n¤@ª½¦V¤U¼W¥[

3.°õ¦æ«áS¸òT¦C¨S¦³¥ô¦ó°Ê§@

¥t¥~¡A¦pªG§Ú·Q¦bS¦C¤§«á§e²{8:45-13:45¤§¶¡ªº©Ò¦³µ²ªG³o¼Ëªº¸Ü¤½¦¡­n«ç»ò§ï(·N«ä´N¬OS¼g¤J8:45¡BT¼g¤J8:46....¤@ª½¼g¨ì13:45)
PS.§Ú¬Ý¤F¤@¤U¥ÎR1C1ªº¤è¦¡¼g¤½¦¡¦n¹³´N¤£¥ÎÅܧó
=IF(SUMIFS(C5,C4,R1C,C2,R[-1]C18)=0,"",SUMIFS(C5,C4,R1C,C2,R[-1]C18))---¨C¤@¦æ³£³o¼Ë¡A³o¼Ë¦pªG­n¼g¨ì13:45¤À´NÀ³¸Ó¤£¥Î¿é¤J¤Ó¦h¤½¦¡¤F

C¤j§Úªº·Qªk¬O³o¼Ë§A¬Ý¬Ý¥i¤£¥i¦æ
R¦C¤£°Ê¡AS¦C¦b8:46:01-8:46:05¤§¤º¼g¤J¤½¦¡¡A5¬í¤º¬Æ¦Ü§óµu®É¶¡§¹¦¨­pºâ¡A¤§«á±N¤½¦¡¼g¦¨­È¡AT¡BU¡BV¦C¨Ì¦¹Ãþ±À(´N¬OÅý°õ¦æ®É¶¡¤£­n¤Ó¤[¡A°õ¦æ§¹¥ß¨è±N¤½¦¡¼g¦¨­È)
³o¥u¬O¤p§Ìªº¤@­Ó·Qªk¡AÁÙ½ÐC¤j¬Ý¬Ý¥i¤£¥i¦æ

TOP

¦^´_ 6# ÂŤÑÄR¦À
»¡¯uªº¡A§Ú¤£¤ÓÀ´ SUMIFS ªº¥Î·N¡A¥u¬O¨Ì¼Ëµe¸¬¡C
¦p§A¦³®É¶¡ªº¸Ü¡Aµyµy»¡©ú¤@¤U¥¦ªº¨Ï¥Î¤è¦¡»P§Aªº·Qªk¡A
¦p¦¹·|§ó©úÁA¨ä§@¥Î¡AÁÂÁ¡C

TOP

¦^´_ 6# ÂŤÑÄR¦À


8:45-13:45 = ¢²¢¯¢¯¤ÀÄÁ¡@
R¦C¥u»Ý­n§ì³Ì°ª¨ì³Ì§C°Ï¶¡200­Ó¥ª¥k
¡@

300*200 ­ÓSUMIFS¡A¶]±o°Ê¶Ü¡H¡H¡H

TOP

°µ­Ó°Æµ{¦¡¡A©T©w®É¶¡¥h CALL §Y¥i¡A
Äæ¦ì¤£°÷¡A¥u°µ¨ì 08:45 ~ 12:00 ¦@ 196 Äæ¡A¦Û¦æ¥h½Õ¾ã¡G
¡@
Sub ²Î­p()
Dim R&, C&, Arr, Brr(1 To 200, 1 To 196), uMax, i&
R = Cells(Rows.Count, 1).End(xlUp).Row
If R < 2 Then Exit Sub
Arr = Range("A2:E" & R).Value
uMax = [R3] '³Ì¤j¦¨¥æ¼Æ
For i = 1 To UBound(Arr)
¡@¡@R = uMax - Arr(i, 2) + 1  '³Ì¤j¦¨¥æ¼Æ - ¢ÐÄ榨¥æ¼Æ + 1 = ¦C¦ì
¡@¡@If R < 1 Or R > 200 Then GoTo 101
    ¡@
¡@¡@C = Int(Arr(i, 1) * 1440) - 524 '¢ÏÄæ®É¶¡¤ÀÄÁ¼Æ - 8:44¤ÀÄÁ¼Æ = Äæ¦ì
¡@¡@If C < 1 Or C > 196 Then GoTo 101
    ¡@
¡@¡@Brr(R, C) = Brr(R, C) + Arr(i, 5)
101: Next
[S3].Resize(200, 196) = Brr
Beep
End Sub

°Ñ¦ÒÀÉ¡G
Xl0000328.rar (82.19 KB)
¡@

TOP

¥»©«³Ì«á¥Ñ ÂŤÑÄR¦À ©ó 2016-1-27 14:01 ½s¿è

¦^´_ 7# c_c_lai

C¤j§Ú»¡©ú¤@¤U¡A§Úªº¥D­n¥Î·N¬O¥ªÃä¬ö¿ý¶q¡A¥kÃä§Ú®Ú¾Ú®É¶¡©M¤£¦Pªº¦¨¥æÂI¦ì¨Ó¥[Á`
¨Ò¦p¡G
12:58:55        7775        1        1258        -1                                             
12:58:56        7776        1        1258        -1
12:58:56        7776        1        1258        -1
12:58:58        7775        25        1258        1
12:58:58        7775        1        1258        -1
12:58:58        7775        1        1259        -1
12:59:00        7774        4        1259        -1
12:59:00        7774        1        1259        -1
12:59:00        7774        1        1259        -1
12:59:01        7775        1        1259        -1
12:59:01        7774        1        1259        -1
12:59:02        7774        1        1259        -1
12:59:02        7774        5        1259        -1
12:59:03        7774        12        1259        1

¥kÃä¬O±N12:58¸Ì­±ªº©Ò¦³7775¡B7776¡B7774ªº¶q¥[Á`¡A¦ý¤£¥[Á`12:58¤À¥H¥~ªº¶q¡A³o´N¬O§Ú¬°¤°»ò¥ÎSUMIFSªº­ì¦]¡A¦pªG¥ÎSUMIF«h·|±N·í¤Ñ©Ò¦³¦P»ù¦ì©Î¦P®É¶¡ªº¼Æ¦r³£¥[Á`¡A¦Ó§Ú­nªº¥u¬O¬Y­Ó®É¶¡¬q¸Ì­±¦³¥X²{ªº»ù¦ìªº¥[Á`
¦p¤W©Ò¥Ü
7776  -2        12:58¤À¸Ì­±7776¥X²{2¦¸¡A³Ì«á­±ªº¼Æ¦r¥[Á`¬O-2
7775  -3        12:58¤À¸Ì­±7775¥X²{3¦¸¡A³Ì«á­±ªº¼Æ¦r¥[Á`¬O-3
7774   0         12:58¤À¸Ì­±7774¥X²{0¦¸¡A³Ì«á­±ªº¼Æ¦r¥[Á`¬O0
¤j·§´N¬O³o¼Ë¡AC¤j¯à²z¸Ñ¶Ü??

TOP

        ÀR«ä¦Û¦b : §ïÅܦۤv¬O¦Û±Ï¡A¼vÅT§O¤H¬O±Ï¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD