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

[µo°Ý] Excel ¿z¿ï«á½Æ»s¡C

[µo°Ý] Excel ¿z¿ï«á½Æ»s¡C

§Ú¦³¤@­Ó¬¡­¶Ã¯¤º¦³¦h­Ó¤u§@ªí,§Ú­n±N¬ÛÃö¤u§@ªí¥ý°µ¿z¿ï¦A½Æ»s¶K¤W°µ
¼Ï¯Ã¤ÀªRªí,¨Ò¦p¤u§@ªíQC3,QC4,QC5 ³o¤T­Ó¤u§@ªí,§Ú¬O¥Î¤½¦¡ µ¥©ó "=" ¥t¥~¤@­Ó¤u§@ªíªº¤º®e,¨º´N¬OQC3-QC5 ¥Ñ²Ä¤G¦C¶}©l,¥þ³¡¬°¤½¦¡= ¥t¥~¤@­Ó¬¡­¶Ã¯
¤ºªº¸ê®Æ¤º®e,¦P®É¦]¬°§Ú¬O¥Î¤½¦¡= ¥t¥~¤@±i¤u§@ªíªº¤º®e,©Ò¥H¦b³Ì«áªº¦C¬O·|¦³0 ªº°ÝÃD,0ªºÅã¥Ü¬O¸Ó¤u§@ªíªº¦C¨S¦³¸ê®Æ,¦]¬°§Ú¤£­n¨C¦¸³£§â¤½¦¡¦V¤U©Ô¦A¨ú¸ê®Æ,¦Ó¬O¥ý=¸Ó¤u§@ªíªº¤º®e,§Ú­n±NQC3¥ý ¿z¿ïKÄæ "year" ¬°2022-1 ¤ë¥÷,¦A¥ÑA2:K2 ¦V¤U½Æ»s¦A¶K¤W¦ÜQC Summary ªºA2 Àx¦s®æ,QC4 ¤]¬O¿z¿ï2022-1 , ¦A¥ÑA2:K2 ½Æ»s¦ÜQC Summary,¦¹®É½Æ»s¶K¤W«h¬O¦bQC3 ­è¤~½Æ»sªº¸ê®Æ¤U¤è,¦P®ÉQC5 ³£¬O¤@¼Ë°µªk,

¦ÓCLS-QC5-CLS-QC7 ¤]¬O¤@¼Ë°µªk3­Ó¤u§@ªí,³o¦¸«K¬OA2:K2½Æ»s¦ÜCLS Summary³o¼Ë, ¦ÓTotal Summary «h§Ú¥H¤â°Ê¤è¦¡,±NQC Summary ¤ÎCLS-Summary °µ¼Ï¯Ã¤ÀªRªí¾ã¦X¸ê®Æ³o¼Ë¡C

QC3-QC5 ³o¼Ë¥u¬O3­Ó¤u§@ªí,CLS-QC5-7 ¤]¬O­è¦n3­Ó,¦pªG¤U¦¸¦³·s¼Wªº¸Ü,³£»Ý­n®Ú¾Ú¤u§@ªíªº¦h¤Ö¨Ó¦Û°Ê½Æ»s¶K¤W¡C

³Ì¦n¬O¦Û°Ê»{§O¨ì¥HQC ¶}ÀYªº¤u§@ªí«h¦Û°Ê¿z¿ï½Æ»s¶K¤W¦ÜQC Summary ªí¤º,¦P®ÉCLS-¶}©lªº¤]¬O¦Û°Ê¿z¿ï¦A½Æ»s¶K¤W¡C
¦Ó2022-1 ¬O·|®Ú¾Ú¤ë¥÷¿z¿ï, ¨ì2¤ë®É,«h­n¦Û°Ê¿z¿ï2022-2¡C


©Ò¦³QC¤u§@ªí¬O¥H¤½¦¡µ¥©ó= ¥t¥~¤@±i¤u§@ªíªº¸ê®Æ,©Ò¥H­n¥H¹ê¼Æ¶K¤WSummary ¡C ¦P®É¨C±i¤u§@ªíªº¤º®e¸ê®Æ¦h¤Ö³£¤£¤@¼Ë¡C

½Ð°Ý¦p¦ó¥HVBA °µ¨ì¥H¤W­n¨D,ÁÂÁÂ
Report.rar (286.46 KB)

¦^´_ 1# stephenlee
½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim Arr, Brr(1 To 10000, 1 To 11), Crr(1 To 10000, 1 To 11)
Dim T$, T1$, n%, n1%, i%, j%, sh
T = Year(Date) & "-" & Month(Date)
For x = 4 To Sheets.Count
    sh = UCase(Left(Sheets(x).Name, 2))
    If InStr(sh, "QC") Then
        With Sheets(x)
            Arr = .Range("a1").CurrentRegion
            For i = 2 To UBound(Arr)
                T1 = Arr(i, 11): If Arr(i, 5) = 0 Then Exit For
                If T = T1 Then n = n + 1: For j = 1 To 11: Brr(n, j) = Arr(i, j): Next
            Next
        End With
    ElseIf InStr(sh, "CL") Then
        With Sheets(x)
            Arr = .Range("a1").CurrentRegion
            For i = 145 To UBound(Arr)
                T1 = Arr(i, 11): If Arr(i, 5) = 0 Then Exit For
                If T = T1 Then n1 = n1 + 1: For j = 1 To 11: Crr(n1, j) = Arr(i, j): Next
            Next
        End With
    End If
Next
If n > 0 Then
    With Sheets("QC Summary")
        .Range("a1").CurrentRegion.Offset(1, 0) = ClearContents
        .[a2].Resize(n, 11) = Brr
    End With
End If
If n1 > 0 Then
    With Sheets("CLS Summary")
        .Range("a1").CurrentRegion.Offset(1, 0) = ClearContents
        .[a2].Resize(n1, 11) = Crr
    End With
End If
End Sub

TOP

¦^´_ 1# stephenlee

Report V1.zip (330.61 KB)

TOP

Sub TEST_A1()
Dim Arr, Brr(2), N(2), i&, j%, YM$, SS, S As Worksheet, T$, k%
YM = Format(Date, "yyyy-m")
ReDim Arr(1 To 20000, 1 To 11)
Brr(1) = Arr: Brr(2) = Arr
For Each S In Sheets
    T = UCase(S.Name)
    k = Switch(T Like "QC#*", 1, T Like "CLS-QC#*", 2, T = T, 0)
    If k = 0 Then GoTo s99
    Arr = S.Range("a1").CurrentRegion
    For i = 2 To UBound(Arr)
        If Arr(i, 5) = 0 Then Exit For
        If Arr(i, 11) = YM Then
           N(k) = N(k) + 1
           For j = 1 To UBound(Arr, 2)
               Brr(k)(N(k), j) = Arr(i, j)
           Next j
        End If
    Next i
s99: Next
Set SS = Sheets(Array("QC Summary", "CLS Summary"))
For k = 1 To 2
    SS(k).UsedRange.Offset(1, 0).EntireRow.Delete
    If N(k) > 0 Then SS(k).[a2].Resize(N(k), 11) = Brr(k)
Next k
End Sub

TOP

¥»©«³Ì«á¥Ñ stephenlee ©ó 2022-2-4 10:34 ½s¿è
Sub TEST_A1()
Dim Arr, Brr(2), N(2), i&, j%, YM$, SS, S As Worksheet, T$, k%
YM = Format(Date, "yy ...
­ã´£³¡ªL µoªí©ó 2022-1-30 08:59


·PÁ²a¤j,§Ú³o¨Ç¸ê®Æ¨Ó·½¬O¥Î¤½¦¡= ¨ä¥L¤u§@ªí¤ºªº¸ê®Æ, ¶K¤W«ü¥O½X«á,¥L¦b13¦æ,¥H¤U¥y¤l¤¤¥X²{¿ù»~»¡


²Ä1¦Ü10Äæ¬O¥Î¤½¦¡=¨ä¥L¤u§@ªíªº¸ê®Æ,¦Ó11Äæ¬O§Ú¥Î¤½¦¡±N²Ä1Äæªº¤é´ÁÂର¦~¥÷¤Î¤ë¥÷,¤è«K²Î­p¾ã¦X¡C

"Type mismatch"
if Arr(i, 11) = YM Then

¯à¤£¯à³Ò·ÐÀ°§Ú¬Ý¤@¤U,ÁÂÁ¡C

TOP

¦^´_ 5# stephenlee


¦pªG´ú¸ÕÀɳ£¨S°ÝÃD, ¬O§_¥i¯à¹ê»Ú¸ê®Æ¦³¤½¦¡²£¥Íªº¿ù»~­È???
¦Û¦æ¥ý§ä¨ì¿ù»~©Ò¦b, §Y¿ù»~¦b¸ê®Æ, ¦Ó¤£¬Oµ{¦¡, ³o­n¦Û¤v­×§ï¤½¦¡¥h°£¿ù»~

TOP

        ÀR«ä¦Û¦b : ¯¸¦b¥b¸ô¡A¤ñ¨«¨ì¥Ø¼Ð§ó¨¯­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD