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

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

Sub ¸ü¤J()
Dim S1 As Worksheet, S2 As Worksheet, Rng1 As Range, Rng2 As Range
Dim Arr, R&, C&, Ck%, N&, xR As Range
Set S1 = Sheets("¼t¯Êªí"):   Set S2 = Sheets("¥X³f")
Set Rng1 = S1.[B3:G3]:   Set Rng2 = S1.[B4:H4]:   Set xR = S1.[B3]
Application.ScreenUpdating = False
Call ²M°£
Arr = Range(S2.[a1], S2.UsedRange)
For C = 45 To UBound(Arr, 2)
    Ck = 0
    For R = 4 To UBound(Arr)
        If Val(Arr(R, C)) <= 0 Then GoTo 101
        If Ck = 0 Then
           Rng1.Copy xR
           xR.Resize(1, 6).VerticalAlignment = xlCenter '¸óÄæ¸m¤¤
           xR = Arr(3, C) '¼t¯Ê¦WºÙ
           Set xR = xR(2): Ck = 1
        End If
        '----------------------------
        Rng2.Copy xR
        xR.Resize(1, 4) = Array(Arr(R, 8), "", Arr(R, 7), Arr(R, C))
        xR(1, 7) = Arr(R, 5)
        Set xR = xR(2): N = N + 1
101: Next R
Next C
If N = 0 Then Exit Sub
Rng2.Copy xR(2)
xR(2).Resize(1, 7).ClearContents
xR(2).Resize(1, 6).Interior.ColorIndex = 37
xR(2, 4).Resize(1, 3) = "=SUM(R[-" & xR.Row - 3 & "]C:R[-1]C)"
End Sub

Sub ²M°£()
With Sheets("¼t¯Êªí")
    .UsedRange.Offset(4, 0).EntireRow.Delete
    .[B3] = ""
    .[B4:G4].ClearContents
    .[F4] = "=IF(MIN(D4:E4)=0,"""",INT(E4/D4))"
    .[G4] = "=IF(MIN(D4:E4)=0,"""",MOD(E4,D4))"
    .[H3:H4].ClearContents
End With
End Sub

Xl0000142.rar (26.85 KB)

­Y»Ý¸óÀÉ, ¦Û¦æ¥h­×§ï~~

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

TOP

°µ­Ó¸óÀÉ°õ¦æ, ¦Û¦æ­×§ï®M¥Î:
¼t¯Ê.rar (40.53 KB)

TOP

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

·PÁ­ã¤j,
¦h¤Ñ¨Ó ²{¦b¤~¦³ªÅ¥i¥H¤Wºô¬d¬Ý, ³o´X¤Ñ¦]¬°¦³·s¤u§@,²ÖÀŤF....
µ¥§Ú´ú¸Õ¤U¦A³ø§i

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2019-11-25 22:43 ½s¿è

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

­ã¤j¦n,
§Ú¨Ì²{¦æªº´ú¸ÕÀÉ,§âµ{¦¡­×§ïÀɦW«á
µ{¦¡´ú¸Õµ²ªG¯d¦b¼t¯Êªí¤¤(¤£¬O ¦Û°Ê¼t¯Êªí)
¥¦»P8¼Óªºµ{¦¡´ú¸Õ¬Û¦P,·í"­¸¤ñ"sheet¦³­q³æ¼Æ¾Ú®É,
­q³æ¸ê®Æ·|³s¦P¼t¯Ê,¤@°_§e²{¦b¼t¯Êªí¤¤
¦A³Â·Ð¬Ý¤U, ·PÁÂ
3rd_µ{¦¡»P°õ¦æÀÉ ¤À¶}_amd.rar (255.3 KB)

TOP

¦^´_ 14# PJChen

¬Ý¤£À´§Aªº°ÝÃD,
1)­Y­n¸ê®Æ©ñ¦b"¦Û°Ê¼t¯Êªí",
  ±N Sheets("¼t¯Êªí") §ï¦¨ Sheets("¦Û°Ê¼t¯Êªí")
2)¼t¯ÊÄæ¦ì¼Æ­Y¬O©T©wªº:
  For C = 45 To UBound(Arr, 2)
  §ï¦¨ For C = 45 To 60

©Î
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

TOP

¦^´_ 15# ­ã´£³¡ªL
³o°}¤l¦]¬°§@·~¤º®e¦³ÅÜ°Ê,©Ò¥Hªí®æ¤]¤j´T­×§ï,½ÐÀ°¦£¬Ý¤U....
1)  For C = 45 To UBound(Arr, 2)
  §ï¦¨ For C = 45 To 60
³o­Ó¤èªk´ú¸Õ«áOK,
¦b°õ¦æ¼t¯Êªí«á·|¥X²{¦Û°Ê¥[Á`(¥Ø«e¦b²Ä8¦C)
§Ú·Q¥[­Ó"¦X­p",½Ð°Ý¦bµ{¦¡­þ­Ó¦a¤è¥i¥H¥[¤J?

2) ¥t¥~³o­Ó¤è¦¡,§ÚÁÙ¸Õ¤£¥X¨Ó,½Ð°Ý§Ú¥[¤Jªº¦a¤è¬O§_¤£¹ï?
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
3rd_¼t¯Êªí.rar (286.86 KB)

TOP

¦^´_ 15# ­ã´£³¡ªL
¸É¥R:
¼t¯Êªí.sheet AÄ檺®Æ¸¹,¤£ª¾¯à§_Åý¥¦¤@¨Ö¥X²{?

TOP

¦^´_ 1# PJChen

§Ú¤£·|VBA¡A¥u¯à¥Î¨ä¥L¤èªk°µ¥XÃþ¦üªºµ¹±z°Ñ¦Ò¡C
¥D­n¬O¥Î¸ê®Æ-±qªí®æ/½d³ò  §Q¥Î¥X³fsheet¤º®e¥HPower Query ½s¿è¾¹¥h¾ã²z¸ê®Æ¡A
³Ì«á¦A¥Î¼Ï¯Ã¤ÀªRªí¥h²£¥Í¸É³f©ú²Ó¡C
¥H¤W¡A¤j­P¤º®e¦pªþ¥ó
¦Û°Ê®Mªí-¬d¸ß»P¼Ï¯Ã¤ÀªR.zip (54.33 KB)

TOP

¦^´_ 15# ­ã´£³¡ªL
Dear­ã¤j,
­«·s¾ã²z¤@¤U...
1) °õ¦æ¼t¯Êªí«á·|¥X²{¦Û°Ê¥[Á`,§Ú·Q¥[­Ó"¦X­p",½Ð°Ý¦bµ{¦¡­þ­Ó¦a¤è¥i¥H¥[¤J?
2) ¼t¯Êªí.sheet AÄæ¥[¤J®Æ¸¹,¦pªG­n¤j´T­×§ïµ{¦¡¡B¤£¦n°µªº¸Ü,´Nºâ¤F¡I§Ú¦A¦Û¤v¤â°Ê¥[¤J
3) ¼t¯Êªí F:G(½c²~)¦p¦óÅý¥¦¦Û°Ê­pºâ«á,Åܦ¨­È,¤£­n¦³¤½¦¡?¥[Á`Äæ¤]¬O...

TOP

¦^´_ 19# PJChen

´ú¸ÕÀÉ:
¼t¯Ê_v02.rar (80.72 KB)

TOP

        ÀR«ä¦Û¦b : ¸Ü¦h¤£¦p¸Ü¤Ö¡A¸Ü¤Ö¤£¦p¸Ü¦n¡C
ªð¦^¦Cªí ¤W¤@¥DÃD