½Ð°Ý¸ê®Æ¤ñ¹ï¥B¹Bºâ¨ì¤u§@ªí¤T
- ©«¤l
- 10
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 20
- ÂI¦W
- 0
- §@·~¨t²Î
- windows xp
- ³nÅ骩¥»
- vb2008
- ¾\ŪÅv
- 10
- µù¥U®É¶¡
- 2011-1-28
- ³Ì«áµn¿ý
- 2011-8-2
|
½Ð°Ý¸ê®Æ¤ñ¹ï¥B¹Bºâ¨ì¤u§@ªí¤T
¦U¦ì«e½ú¦n
ª¦¤å§ä¤F«Ü¦h¤å³¹ÁöµM¦³§ä¨ìÃþ¦üªº°ÝÃD
¦ý¬OÀɮפ]¤£¯à¤U¸ü¥u¦n«½Æ°Ý¤@¤UÃþ¦üªº°ÝÃD
¤u§@ªí¤@
¤µ¤Ñªº¥æ©ö«~¶µ»P¼Æ¶q
¤u§@ªí¤G¬O
¤µ¤Ñ°Ó«~ªº³ø»ù
¤u§@ªí¤T¬O
Àç·~ÃBªº°O¿ý°Ï
°Ó«~¦³«Ü¦hºØ¦ý¬O¨C¤Ñ¥æ©öªº°Ó«~ºØÃþ¤£©T©w¼Æ¶q¤]¤£©T©w
¨Ò¦p°Ó«~Á`¦@¦³99ºØ
²Ä¤@¤Ñ¥æ©öªº°Ó«~¬O1,3,5,8,16,72,44,56
²Ä¤G¤Ñ¥æ©öªº°Ó«~¬O9,61,77,51,34,26,89,54,59,68,
¨C¤Ñ°Ó«~ªº³ø»ù¤]¤£¦P
§Æ±æ¦b¤u§@ªí¤T°O¿ý¤U
©Ò¦³°Ó«~¨C¤éªºÀç·~ÃB
¤u§@ªíÄæ¦ì¤À§O¬°
A1:²£«~¦WºÙ
B1:²Öp20¤éÀç·~ÃB
C1~V1:¤é´Á
¨C¦¸·s¼W¤@¤éªºÀç·~ÃB³£¦bCÄæ,¦Óì¬ö¿ý¦bCÄæ~VÄæ«h©¹¥k²¾
쥻VÄæ³Ì¦ªº°O¿ý«h§R°£
¤]´N¬O»¡¤u§@ªí¤T¥Ã»·¥u«O¯d¤G¤Q¤éªºÀç·~ÃB°O¿ý
¥æ©ö¬ö¿ý.rar (24.91 KB)
|
|
|
|
|
|
|
- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¦^´_ 1# enhrulee
¸Õ¸Õ¬Ý- Sub Ex()
- Dim D As Object, DX As Object, Rng As Range
- Set D = CreateObject("SCRIPTING.DICTIONARY") '¤µ¤é³ø»ù ª«¥ó
- Set DX = CreateObject("SCRIPTING.DICTIONARY") '¤µ¤é³ø»ù*¤µ¤é³æ¶q ª«¥ó
- Set Rng = Sheets("¤µ¤é³ø»ù").Range("A2")
- Do While Rng <> "" '¨ú±o ²£«~¤µ¤é³ø»ùª«¥óªº °j°é
- D(Rng.Value) = Rng.Offset(, 1).Value
- Set Rng = Rng.Offset(1) '³]©w¬°©¹¤U¤@¦C
- Loop
- Set Rng = Sheets("¤µ¤Ñ¥æ©ö").Range("A2")
- Do While Rng <> "" '¨ú±o ¤µ¤é²£«~³ø»ù * ¤µ¤Ñ³æ¶q =ª÷ÃB ªº°j°é
- If DX.EXISTS(Rng.Value) Then '¤µ¤é²£«~³æ¶q¤w¥X²{
- DX(Rng.Value) = DX(Rng.Value) + D(Rng.Value) * Rng.Offset(, 1).Value
- Else
- DX(Rng.Value) = D(Rng.Value) * Rng.Offset(, 1).Value
- End If
- 'DX.EXSITS(Rng.Value) ¤µ¤Ñ¥æ©öªº²£«~¦WºÙ¦s¦b
- 'D(Rng.Value) ²£«~ ¤µ¤é³ø»ù
- 'Rng.Offset(, 1).Value ²£«~ ¤µ¤é³æ¶q
- 'DX(Rng.Value) ¤µ¤Ñ³æ¶q*¤µ¤é³ø»ù
- Set Rng = Rng.Offset(1)
- Loop
- With Sheets("¥æ©ö¬ö¿ý")
- If .Range("C1") <> Date Then '¤£¬O·í¤é
- .Columns("C:C").Insert
- .Columns("V:V") = ""
- .Range("C1") = Date
- End If
- Set Rng = .Range("A2")
- Do While Rng <> "" '¨ú±o ¤µ¤Ñ¦³¥æ©ö¬ö¿ý ²£«~ ªº°j°é
- If DX.EXISTS(Rng.Value) Then Rng.Offset(, 2) = DX(Rng.Value)
- '²£«~¤µ¤é¦³¥æ©ö CÄæ¿é¤JÁ`ª÷ÃB
- Rng.Offset(, 1) = "=SUM(" & Rng.Offset(, 2).Resize(1, 20).Address & ")" '
- 'BÄæ¿é¤J¤½¦¡
- Set Rng = Rng.Offset(1)
- Loop
- End With
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤l
- 10
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 20
- ÂI¦W
- 0
- §@·~¨t²Î
- windows xp
- ³nÅ骩¥»
- vb2008
- ¾\ŪÅv
- 10
- µù¥U®É¶¡
- 2011-1-28
- ³Ì«áµn¿ý
- 2011-8-2
|
¦^´_ enhrulee
¸Õ¸Õ¬Ý
GBKEE µoªí©ó 2011-7-23 14:42
·PÁª©¥Dªº¼ö¤ßÀ°¦£
§Ú¦³µy·Lקï¤F¤@¤p³¡¤À
If .Range("C1") <> Date Then '¤£¬O·í¤é
.Columns("C:C").Insert
.Columns("W:W") = ""
.Range("C1") = Date
End If
À³¸Ó¬O°õ¦æ¶¶§Çªºì¦]
쥻ª©¥D¼gªºµ{¦¡·|§R°£±¼ì²Ä¤G¤Ñ(UÄæ)ªº¸ê®Æ
¤p¤pªº×¥¿´NOK¤F~
¶W¯Å·PÁªºÁÙ¦³¤¤¤åµù¸Ñ
¯uªº«Ü¶K¤ß
:$ |
|
|
|
|
|
|
- ©«¤l
- 1446
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1470
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-10-21
|
¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-5-24 11:04 ½s¿è
ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
¤µ¤é¥æ©öªí & ¤µ¤é³ø»ùªí:
µ²ªGªí°õ¦æ«e:
°õ¦æµ²ªG:
Option Explicit
Sub TEST()
Dim Arr, Brr, Crr, Y, i&
'¡ô«Å§iÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO ¦r¨å
Brr = Range([¤µ¤é³ø»ù!B2], [¤µ¤é³ø»ù!A65536].End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥H¤µ¤é³ø»ùªí Àx¦s®æȱa¤J°}¦C¸Ì
For i = 1 To UBound(Brr): Y(Brr(i, 1) & "/³ø") = Brr(i, 2): Next
'¡ô³]¶¶°j°é,¥O²£«~¦WºÙ ³s±µ "/³ø"·íkey,item¬OBÄ檺³ø»ù
Brr = Range([¤µ¤Ñ¥æ©ö!B2], [¤µ¤Ñ¥æ©ö!A65536].End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥H¤µ¤Ñ¥æ©öªíÀx¦s®æȱa¤J°}¦C¸Ì
For i = 1 To UBound(Brr): Y(Brr(i, 1) & "/©ö") = Y(Brr(i, 1) & "/©ö") + Brr(i, 2): Next
'¡ô³]¶¶°j°é,¥O²£«~¦WºÙ ³s±µ "/©ö"·íkey,item¬OBÄ檺³æ¶q°µ²Ö¥[
Arr = Range([¥æ©ö¬ö¿ý!A1], [¥æ©ö¬ö¿ý!A65536].End(3))
'¡ô¥OArrÅܼƬO ¤Gºû°}¦C,¥H¥æ©ö¬ö¿ýªí AÄæÀx¦s®æȱa¤J°}¦C¸Ì
If [¥æ©ö¬ö¿ý!C1] = Date Then
'¡ô¦pªG[¥æ©ö¬ö¿ý!C1]Àx¦s®æȬO ¤µ¤Ñ¤é´Á,¥Nªí¤µ¤é¤w¸g°õ¦æ¹L
Crr = Range([¥æ©ö¬ö¿ý!V1], [¥æ©ö¬ö¿ý!C65536].End(3))
'¡ô¥OCrrÅܼƬO ¤Gºû°}¦C,¥H¥æ©ö¬ö¿ýªíC~VÄæÀx¦s®æȱa¤J°}¦C
Else '§_«h
Crr = Range([¥æ©ö¬ö¿ý!V1], [¥æ©ö¬ö¿ý!B65536].End(3))
'¡ô¥OCrrÅܼƬO ¤Gºû°}¦C,¥H¥æ©ö¬ö¿ýªíB~VÄæÀx¦s®æȱa¤J°}¦C
End If
For i = 2 To UBound(Crr)
'¡ô³]¶¶°j°é
Crr(i, 1) = Y(Arr(i, 1) & "/³ø") * Y(Arr(i, 1) & "/©ö")
'¡ô¥OCrr°}¦Cªº²Ä1Äæ±a¤J¬dY¦r¨åpºâ¥X¨Óªº¨CÓ²£«~¥æ©öª÷ÃB
Next
Crr(1, 1) = Date
'¡ô¥OCrr°}¦C1¯Á¤Þ¸¹¦C1¯Á¤Þ¸¹Äæ°}¦CȬO ¤µ¤Ñ¤é´Á
[¥æ©ö¬ö¿ý!C1].Resize(UBound(Crr), 20) = Crr
'¡ô¥OCrr°}¦Cȱq[¥æ©ö¬ö¿ý!C1]¶}©l¼g¤JÀx¦s®æ¸Ì
Set Y = Nothing: Erase Arr,Brr, Crr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub |
|
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y
|
|
|
|
|