§ì¨ú¯S©wÀx¦s®æªº¸ê®Æ?

q1a2z5

°ÝÃD¦pªþ¥ó,ÁÂÁ¡I

­ã´£³¡ªL

¤U¸ü (6.41 KB)

2020-12-18 19:08

q1a2z5

¤£¦n·N«ä¡A°ÝÃD¤£¬O«Ü²M·¡¡A¦U¦ì¤j¤j¬Ýªº¤@ÀYÃú¤ô¡A
³o­Óªí®æ¬O±qºô¸ô¨t²Î¤¤¶×¥XªºEXCELªí®æ¡A¦³¤@¨ÇÀx¦s®æ«h¨S¦³¸ê®Æ¡A
°ÝÃD¤w­«·s¾ã²z¡A°ÝÃD¦pªþ¥ó¡CÁÂÁ¡I

­ã´£³¡ªL

¤£ª¾¸ê®Æ¦³¦h¤Ö, ¥ý¥Î¤½¦¡³»¤@³»:
³£¬O°}¦C¤½¦¡(¤TÁä¿é¤J)
M3:¤U©Ô
=INDEX(A:A,SMALL(IF(A$1:A$40="¦WºÙ",ROW($1:$40)+1,4^8),ROW(A1)))&""

N3:¥k©Ô¤@®æ//¤U©Ô
=TEXT(INDEX($G:$G,SMALL(IF($G$1:$G$40="µn¤J¤é´Á,®É¶¡",ROW($1:$40)+COLUMN(A$1),4^8),ROW(A1)))&"",IF(COLUMN(A$1)=1,"yyyy-mm-dd","hh:mm"))

P3:¥k©Ô¤@®æ//¤U©Ô
=TEXT(INDEX($H:$H,SMALL(IF($H$1:$H$40="µn¤J¤é´Á,®É¶¡",ROW($1:$40)+COLUMN(A$1),4^8),ROW(A1)))&"",IF(COLUMN(A$1)=1,"yyyy-mm-dd","hh:mm"))

­ã´£³¡ªL

Sub TEST()
Dim Arr, Brr, i&, N&
Arr = Intersect(Sheet1.UsedRange, Sheet1.[A:H])
ReDim Brr(1 To UBound(Arr), 1 To 5)
For i = 1 To UBound(Arr)
    If Arr(i, 1) = "¦WºÙ" Then
       N = N + 1
       Brr(N, 1) = Arr(i + 1, 1)
       Brr(N, 2) = Format(Arr(i + 4, 7), "yyyy-mm-dd")
       Brr(N, 3) = Format(Arr(i + 5, 7), "hh:mm")
       Brr(N, 4) = Format(Arr(i + 4, 8), "yyyy-mm-dd")
       Brr(N, 5) = Format(Arr(i + 5, 8), "hh:mm")
    End If
Next i
Sheet1.[M3:Q2000].ClearContents
With Sheet1.[M3].Resize(N, 5)
     .NumberFormatLocal = "@"
     .Value = Brr
End With
End Sub

q1a2z5

¨º¦pªG­n§ì¨ú©T©wÀx¦s®æ¸ê®Æ®É,¤½¦¡¦p¦ó³]©w?
­n§ì¨úªºÀx¦s®æ¸ê®Æ³£¬O©T©w¡C
¨Ò¦p¡G§ì¨úA1,A9,A17¤ÎG5,G6¡FG21,G22¡FH5,H6,H21,H22¡A¨Ì¦¹Ãþ±À¡C
¢²¢ß

­ã´£³¡ªL

¦^´_ 6# q1a2z5

À³¸Ó¬O A2§a???
§ìAÄæ
=INDEX(A:A,ROW(A1)*8-6)&""

§ìGÄæ/¤é´Á:
=TEXT(INDEX(G:G,ROW(A1)*8-3),"yyyy-mm-dd;;;")

§ìGÄæ/®É¶¡:
=TEXT(INDEX(G:G,ROW(A1)*8-2),"hh-mm;;;")

HÄæ¨Ì¼Ë§ï§ï