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

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

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

Dear,
§Ú¤£ª¾¹D³o­Ó®Mªí°ÝÃD¯à§_¥Î¨ç¼Æ¸Ñ¨M,¦ý¦]¬°ªí®æ¤¤ªº¸ê®Æ¤j¦h¥Î¨ç¼Æ®M¥X¨Óªº,©Ò¥H¦b³o¸Ìµo°Ý...    ¦Û°Ê®Mªí.rar (34.84 KB)
        ¥X³fsheet¬O¤@­Ó¨C¤Ñ¥X³f¥Îªºªí®æ,©Ò¥H¸ê®Æ¬OÅܰʪº
        ¦]¬°Àɮ׫ܤj,§Ú§â»P¸É³f©ú²ÓµLÃöªº¸ê®Æ³£Delete,Á×§K¤zÂZ
        ¨C¤Ñªº¯Ê³f³£»Ý­n³o±i³øªí¥hµoµ¹¸É³fªº³æ¦ì
        °£¤FªíÀY1:2¥~,³o±i³øªí¨C¤Ñªì©l³£¬OªÅ¥Õªº
        §Ú­n§â¥X³fsheetªº¸ê®Æ®M¶i¨Ó,½Ð±Ð¥Î¨ç¼Æ¤ñ¸û¦n°µ,ÁÙ¬OVBA?
       
        ¥X³fsheet­n®M¹L¨Óªº³f®Æ:
1..        ¦³¯Ê³fªº³¡¥÷¡A¨Ì§Ç±qAS:BH±a¸ê®Æ
2..        §Ú¥ý°µ¤@­Ó2­Ó½d¨Ò"ªL¤f" & "·x·x1"
3..        ²{¦bµLªk¹F¦¨ªº³¡¥÷¦b©ó
        ­n¦p¦óÅý¦³¯Ê³fªºAS:BHªºªíÀY¦Û°Ê±a¨ì³o¸ÌªºBÄæ?¦ýµL¯Ê³fªº¦Û°Ê¸õ¹L¡I
        ¨Ã¥BÅý¯Ê³fªº®Æ¸¹¦Û°Ê±a¨ìAÄæ?
4..        ²Ä2µ§¯Ê³fªºªíÀY "·x·x1"±a¤JBÄæ,®Æ¸¹¦Û°Ê±a¨ìAÄæ«á
        ¦p¦óÅýEÄæªº¤½¦¡¥H·x·x1¬°¹ï¶H?¦Ó¤£¥Î¦U§O­×§ï¤½¦¡?
¨Ò¡G        =SUMPRODUCT((¥X³f!$F$4:$F$12=$A7)*(¥X³f!$AS$3:$BH$3=B$6)*(¥X³f!$AS$4:$BH$12))
        ¦p¦óÅý(¥X³f!$AS$3:$BH$3¦Û°Ê·j´MB$6),¦Ó¤£¥Î¦U§O§ï?

¦^´_ 30# PJChen

Sub ³Ì«á®Ä´Á()
Dim Arr, Brr, Crr, R&, i&, j%, N&(1 To 2), NN&, 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
For j = 1 To 2
    If Val(Brr(i, j)) > 0 Then
       N(j) = N(j) + 1: Crr(N(j), j) = Arr(i, 1)
       If N(j) > NN Then NN = N(j)
    End If
Next j
Next i
If NN = 0 Then Exit Sub
With Sheets("³Ì«á®Ä´Á")
    .[J4:K4].Resize(NN) = Crr
    If NN <= 1 Then Exit Sub
    .[L4:AB4].Copy .[L5:AB5].Resize(NN - 1)
    .[A4:H4].Copy .[A5:H5].Resize(NN - 1)
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?
¥¿½T.jpg
2020-6-16 16:52

³Ì·s®w¦sA.rar (85.21 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

¥»©«³Ì«á¥Ñ 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)
20200616.123221.jpg
2020-6-16 12:41

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

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

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

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

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)
20200417.221214.jpg
2020-4-17 22:13

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

        ÀR«ä¦Û¦b : ¦n¨Æ­n´£±o°_¡A¬O«D­n©ñ±o¤U¡A¦¨´N§O¤H§Y¬O¦¨´N¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD