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

[µo°Ý] ¦Û°Ê®Mªí

¥»©«³Ì«á¥Ñ PJChen ©ó 2020-3-1 19:54 ½s¿è

¦^´_ 20# ­ã´£³¡ªL
­ã¤j,
°õ¦æ«áµo²{³øªí§ï¦¨¤F­q³æ²Î­p¦Ó¤£¬O¯Ê®Æªí,¥¦ªºÀ³§ì¨ú½d³ò¬OAP:BH

§Ú¥D­n¬O·Q¤F¸Ñ±z¤§«e¦^ÂЪºµ{¦¡
For C = 45 To UBound(Arr, 2)
    Ck = 0
    If Arr(3, C) = "¹º³æ¦X­p" Then Exit For  '¥[¤J³o¤@¦æ, ¥H[¹º³æ¦X­p]§PÂ_¼t¯ÊÄæ¦ìªºµ²§ôÂI
³o­Ó¤è¦¡,§Ú¸Õ¤£¥X¨Ó,·Qª¾¹D­n«ç»ò­×§ï

¥H¤U¬O¤§«eµ{¦¡runªºµ²ªG,§Ú¥u¬O·Q­n¥[¤J¬õ¦rªº³¡¥÷ & ÀÉ®× ¼t¯Êªí_amd3.rar (184.24 KB)

TOP

¤j¤j¦n,

³Ìªñ¦³¤@­Ó·sªºªí®æ»Ý¨D: ±N¼t¯Ê¸ê®Æ¶×Á`,±ø¥ó¬°¥H¤U,½Ð°ÝVBA­n¦p¦ó¼g?
1..        ­¸¤ñBIÄæ¤j©ó0ªº¸ê®Æ,¶ñ¤JA3:F
2..        A3:F¸ê®Æ­È¤Æ
3..        ±N²Ä3¦Cªº®æ¦¡¦V¤U½Æ»s
¼t¯Ê¶×Á`.rar (179.22 KB)

TOP

¦^´_ 22# PJChen

Sub ¼t¯Ê¶×Á`_¶×¤J()
Dim Arr, R&, ¼t¯Ê¼Æ&, ¤J¼Æ&, N&
Call ¼t¯Ê¶×Á`_²M°£
Arr = Range([­¸¤ñ!A1], [­¸¤ñ!BI65536].End(xlUp))
For R = 4 To UBound(Arr)
    ¼t¯Ê¼Æ = Val(Arr(R, UBound(Arr, 2)))
    ¤J¼Æ = Val(Arr(R, 7))
    If ¼t¯Ê¼Æ * ¤J¼Æ = 0 Then GoTo 101
    N = N + 1
    Arr(N, 1) = Arr(R, 6)
    Arr(N, 2) = Arr(R, 5)
    Arr(N, 3) = Arr(R, 8)
    Arr(N, 4) = ¤J¼Æ
    Arr(N, 5) = ¼t¯Ê¼Æ Mod ¤J¼Æ
    Arr(N, 6) = Int(¼t¯Ê¼Æ / ¤J¼Æ)
101: Next R
If N = 0 Then Exit Sub
With [¼t¯Ê¶×Á`!A3:F3].Resize(N)
     .Rows(1).Copy .Cells
     .Value = Arr
End With
End Sub


Sub ¼t¯Ê¶×Á`_²M°£()
With Sheets("¼t¯Ê¶×Á`")
    .UsedRange.Offset(3, 0).EntireRow.Delete
    .[A3:F3].ClearContents
End With
End Sub


============================

TOP

¦^´_ 23# ­ã´£³¡ªL

­ã¤j¦n,

¤£¦n·N«ä,³o­Ó·sªí®æ¬O³o´X¤Ñ¤~±Ò¥Î,µo²{®æ¦¡¨S°µ¦n,­n§ó§ï¦p¤U
¥t¥~ ªí®æ¤¤¨ú¥Xªº²~¼Æ¤Î½c¼Æ ¼Æ­È³£¤£¥¿½T,§Ú§â¥¿½Tµª®×¦C¥X,¤]½ÐÀ°¦£­×¥¿...¦A¦¸·PÁÂ
³Ì·s®w¦sA_2.rar (178.15 KB)

TOP

¦^´_ 23# ­ã´£³¡ªL

­ã¤j,
·PÁ§A,§Ú§ï¦n¤F

TOP

¦^´_ 23# ­ã´£³¡ªL

­ã¤j,
§Ú­×§ï¤F³o­Óµ{¦¡,·QÅý
HDÄæ>0,«h¶ñ¤J,³Ì«á®Ä´ÁsheetªºJÄæ
HEÄæ>0,«h¶ñ¤J,³Ì«á®Ä´ÁsheetªºKÄæ
¥Ø«e¥utry¤FHEÄæ,¦ýµLªk¶ñ¤J,¥i¥HÀ°§Ú¬Ý¬Ý¶Ü?    ³Ì·s®w¦s.rar (82.58 KB)
  1. Sub  ³Ì«á®Ä´Á()
  2. Dim Arr, Brr, R&, ²Î&, N&, BK As Workbook
  3. Set BK = Workbooks("³Ì·s®w¦s.xlsx")
  4. BK.Sheets("³Ì«á®Ä´Á").Activate
  5. Arr = Range([­¸¤ñ!A1], [­¸¤ñ!HE65536].End(xlUp))
  6. For R = 4 To UBound(Arr)
  7.     ²Î = Val(Arr(R, UBound(Arr, 2)))
  8.     If ²Î = 0 Then GoTo 101
  9.     N = N + 1
  10.     Arr(N, 11) = Arr(R, 5) '®Æ¸¹
  11. 101: Next R
  12. If N = 0 Then Exit Sub

  13. With [³Ì«á®Ä´Á!A4:H4].Resize(N)
  14.      .Rows(1).Copy .Cells
  15. End With
  16. With [³Ì«á®Ä´Á!L4:AB4].Resize(N)
  17.      .Rows(1).Copy .Cells
  18. End With
  19. End Sub
½Æ»s¥N½X

TOP

¦^´_ 26# PJChen

§A³oµ{¦¡½X®Ú¥»®³¤£¨ì¥ô¦ó¸ê®Æ!!!
À³¸Ó¬O§¹¥þ¤£¤F¸Ñ­ìµ{¦¡½Xªº·N«ä, ³o¼Ë¬OµLªk®M¥Îªº~~

¥ú¬O³o¤£¦¨®Mªºµ{¦¡½X¤Î²³æªº»¡©ú, µLªk¤F¸Ñ¸Ô²Ó»Ý¨D³W«h,
¦n¹³¨C¦¸ªº´£°Ý, §Ú´X¥G³£§ì¤£¨ì­nªº¬O¤°»ò???¥u¯à¥Î²q,
³oÁ`¤£¬O¿ìªk, ©Î³\¦A¬ã¨s¤@¤U´£°Ý¤è¦¡, Åý§O¤H³£¥i¤F¸Ñ²M·¡§Aªº¥Øªº!!!

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2020-6-16 12:45 ½s¿è

¦^´_ 27# ­ã´£³¡ªL

³Ì«á®Ä´Á,A4:H4,L4:AB4­ì¨Ó³£¦³¤½¦¡,§@¬°°ÊºAªí®æ¨Ï¥Î,
µ{¦¡¥u¦³°µ¤G­Ó°Ê§@:
1) ±N¨Ó·½®Æ¸¹¶ñ¤JJ:KÄæ,
2) ³Ì«á¨Ì¾ÚKÄ檺¦C¼Æ,±NA4:H4,L4:AB4ªº¤½¦¡¤U©Ô§Y¥i  
³Ì«á®Ä´Á.rar (82.19 KB)

TOP

¦^´_ 28# PJChen


Sub ³Ì«á®Ä´Á()
Dim Arr, Brr, Crr, R&, i&, N&, BK As Workbook
Set BK = Workbooks("³Ì·s®w¦s.xlsx")
BK.Sheets("³Ì«á®Ä´Á").Activate
R = [­¸¤ñ!HE65536].End(xlUp).Row
Arr = Sheets("­¸¤ñ").Range("F1:F" & R)
Brr = Sheets("­¸¤ñ").Range("HD1:HE" & R)
ReDim Crr(1 To R, 1 To 2)
For i = 4 To R
    If Val(Brr(i, 1)) + Val(Brr(i, 2)) = 0 Then GoTo 101
    N = N + 1
    If Brr(i, 1) > 0 Then Crr(N, 1) = Arr(i, 1)
    If Brr(i, 2) > 0 Then Crr(N, 2) = Arr(i, 1)
101: Next i
If N = 0 Then Exit Sub
With Sheets("³Ì«á®Ä´Á")
    .[J4:K4].Resize(N) = Crr
    If N > 1 Then
      .[L4:AB4].Copy .[L5:AB5].Resize(N - 1)
      .[A4:H4].Copy .[A5:H5].Resize(N - 1)
    End If
End With
End Sub

TOP

¦^´_ 29# ­ã´£³¡ªL

­ã¤j,
1) ¦p¹Ï,¦p¦ó¨ÏJÄæ&KÄæ¸ê®Æ¥i¥H³sÄò,¥B³£±q²Ä4¦C¶}©l¸ü¤J¸ê®Æ?
2) ½Ð°ÝFor i = 4 To R
¸Ó¦p¦ó²z¸Ñ¬õ¦r³¡¥÷?¬°¦ó¬O4?

³Ì·s®w¦sA.rar (85.21 KB)

TOP

        ÀR«ä¦Û¦b : ¤H¥Í³Ì¤jªº¦¨´N¬O±q¥¢±Ñ¤¤¯¸°_¨Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD