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

¸û½ÆÂøªº­«·s±Æ§Ç

¸û½ÆÂøªº­«·s±Æ§Ç

³o¦¸¬O¸û½ÆÂøªº­«·s±Æ§Ç
­º¥ý¡A¥ý·PÁ¦b¤W¤@©«¨ó§Uªº¦U¦ì
²{¦b¸I¨ì¤@­Ó§ó³Â·Ðªº±Æ§Ç(¥ý«e¨º­ÓÀ³¸Óºâ¬O¥H2­«±Æ)
³o¦¸ªº¦³¥i¯à¬O9¡B12¡B15
»Ý¨D¤@¼Ë¡A¥ý±NÁ`¦æ¼Æ°£¥HÁ`¼Æ¡A¨Ã­«·s±Æ¦C
¥H15¬°¨Ò¡AÁ`¼Æ°²©w¬°75 (¨ä¹ê¤j·§¬O´X¦Ê¨ì¤@¤d¡A¤Ó¦h§Ú¤£¦n°µ½d¨Ò)
­ì¥»ªº¶¶§ÇÀ³¸Ó¬O1~75
·sªº±Æ¦C¡A§Úª½±µ¥Îexcel¥´¥X¨ÓÀ³¸Óªø³o¼Ë
¹Ï1

1±iA4¯È¤W­±¥i¥H¦L15®æ¡A¦ý¥L¬O3*5 µM«á·Ó¶¶§Ç¦L¥X
¦ý³o15®æ­n¼¹¶}¨Ó¡A´N·|Åܦ¨¤£·Ó¶¶§Ç¤F(­n¤@±i¤@±i±Æ)

§Ú§Æ±æªº¬OÅý¥LÅܦ¨³o¼Ë
¹Ï2


©Ò¥H·s±Æ¦CÀ³¸Ó¬O (¸Ô¨£ªþ¥óªºDÄæ)
1
6
11
16
21
26
31
36
41
46
51
56
61
66
71

³o¼Ë§Ú´N¥i¥H¦b­ì©l¸ê®Æ°µ¤@­Ó»²§U¦C¡A¥ý±N¥Î1~75±Æ¦C(³o­Ó¼Æ¨ä¹ê¤£©T©w)
µM«á¦A¥[¤@­Ó²Ä¤G»²§U¦C¡A¨Ã¥Î²Ä¤G»²§U¦C°µ­«·s±Æ§Ç
¥H¹F¨ì±N­ì¥»ªº¹Ï1¶¶§Ç§ï¦¨¹Ï2ªº¥Øªº


¥i¥Hªº¸Ü¡A°µ¥X¨Óªºªí®æ¦³¤@­ÓÄæ¦ìÅý§Ú¶ñÅܼƪº¼Æ¶q(A4¯È­nµô¦¨9®æ¡B12®æ¡B15®æ)
¤£¯àªº¸Ü¥ý¦³15®æªºª©¥»¤]¦n
·PÁ±zªº¨ó§U m(_ _)m

ªþ¥ó
±Æ§Ç»Ý¨D.rar (3.15 KB)

G1:K4=IF(MOD(ROW(A1),4)=0,"",(MOD(ROW(A1),4)-1)*5+INT(ROW(A1)/4)*15+COLUMN(A1))

G1:K4½Æ»s¨ìG5:K20
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¥»©«³Ì«á¥Ñ wei9133 ©ó 2018-7-20 00:03 ½s¿è
G1:K4=IF(MOD(ROW(A1),4)=0,"",(MOD(ROW(A1),4)-1)*5+INT(ROW(A1)/4)*15+COLUMN(A1))

G1:K4½Æ»s¨ìG5:K20
hcm19522 µoªí©ó 2018-7-19 17:29


¤£¦n·N«ä¡A¬Ý¼Ë¤l¬O§Ú¨SÁ¿²M·¡
§Ú»Ý­nªº³Ì«áµ²ªGÀ³¸Ó¬O³o±i¹Ï


­ì©«ªº¹Ï1¸ò¹Ï2¬O¹ê»Ú¦L¥X¨Óªº®ÄªG(¹ê»Ú¦L»s¦¨¬O¦Yªº¬O¤å¦rÀÉ¡A¨Ã³v¦æÀò¨ú¡A©Ò¥H§Ú§Æ±æ¥Îexcel¥ý°µ­«·s±Æ§Ç¡A¥H¹F¹Ï2®ÄªG)
¹Ï1¹Ï2¬O§Ú¤£·|ºâ¥u¦n¤@®æ¤@®æ¶ñ¥X¨Óªº...

TOP

¦^´_ 3# wei9133

{=INDIRECT(TEXT(SMALL(IF(M$1:Q$19>0,COLUMN(M$1:Q$19)+ROW(M$1:Q$19)/1%),ROW(A1)),"!R0C00"),)
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_  wei9133

{=INDIRECT(TEXT(SMALL(IF(M$1$19>0,COLUMN(M$1$19)+ROW(M$1$19)/1%),ROW(A1)),"! ...
hcm19522 µoªí©ó 2018-7-20 09:50


§A¦n¡A¨Ì·Ó§Aµ¹ªº¤½¦¡¥á¶i¥h¬O¨S¦³¤ÏÀ³©Î¿ù»~ªº¡A¬O§Ú­þ¸Ì§Ë¿ù?


¥t¥~¡A­«ÂI¤£¬O¨º­Ó°}¦C¡A¬O³o­Ó

­n¹ï­ì¥»¶¶§Ç¬O1~????°µ­«·s±Æ§Ç (³o¸Ì¥ý°²³]¬°75)
¦b³o¸Ì¬O¥H15®æ¬°·Ç­«·s±Æ

Åý¾ãÅ|¯È¼¹¥X¨Óªº²Ä¤@®æ¦ì¸mÅܦ¨¨Ì§Ç
°²³]¦³5±i¯ÈÅ|¦b¤@°_¼¹²Ä¤@®æªº¦ì¸m·|¬O1~5¡A²Ä¤G®æ·|¬O6~10
³o­Ó³¡¤À¤~¬O­n±ÆÄòªº­«ÂI¡A·Ó­ì¥»ªº¿é¥X§Ç¦C²Ä¤@®æ·|¬O1,16,31,46,61
©Ò¥H½Ð¤£­nºÞ¨º­Ó¬Ý°_¨Ó¹³°}¦CªºªF¦è

°²¦p¬Ý¤£À´§ÚªºÄò­zªº¸Ü¡A´N¦Ò¼{¦p¦óÅý¤½¦¡²Å¦X³o±i´X¹Ï§a



·PÁ¦U¦ìªá®É¶¡Æ[¬Ý

TOP

¦^´_ 5# wei9133


§A¦n¡A
§Ú¬O§Q¥ÎVBAªº¤è¦¡²£¥Íªí®æ¡A
§A¬Ý¬Ý³o¼Ëªº³]­p¬O§_¦³²Å¦X§Aªº»Ý¨D : )
ªí®æ±Æ§Ç (2).zip (17.66 KB)
  1. Sub CreateForm()
  2.     Dim C&, i%, iStart%, iEnd%, J%, jStart%, jEnd%, P%, PEnd%
  3.     Range(Cells(1, 5), Cells(Cells.Rows.Count, Cells.Columns.Count)).Clear
  4.     PEnd = Range("C5") - 1
  5.     For P = 0 To PEnd
  6.         If Range("C2") = "" Then
  7.             C = 1 + P
  8.         Else
  9.             C = Range("C2") + P
  10.         End If
  11.         iStart = 2 + P * (Range("C3") + 2)
  12.         iEnd = iStart + Range("C3")
  13.         jStart = 5
  14.         jEnd = jStart + Range("C4")
  15.         For i = iStart To iEnd
  16.             For J = jStart To jEnd
  17.                 Cells(i, J) = C
  18.                 C = C + Range("C5")
  19.             Next
  20.         Next
  21.         SetFormat Range(Cells(iStart, jStart), Cells(iEnd, jEnd))
  22.     Next
  23. End Sub

  24. Sub SetFormat(R As Range)
  25.     With R
  26.         .HorizontalAlignment = xlCenter
  27.         .VerticalAlignment = xlCenter
  28.         .Borders(xlDiagonalDown).LineStyle = xlNone
  29.         .Borders(xlDiagonalUp).LineStyle = xlNone
  30.         .Borders(xlEdgeLeft).LineStyle = xlContinuous
  31.         .Borders(xlEdgeTop).LineStyle = xlContinuous
  32.         .Borders(xlEdgeBottom).LineStyle = xlContinuous
  33.         .Borders(xlEdgeRight).LineStyle = xlContinuous
  34.         .Borders(xlInsideVertical).LineStyle = xlContinuous
  35.         .Borders(xlInsideHorizontal).LineStyle = xlContinuous
  36.     End With
  37. End Sub
½Æ»s¥N½X

TOP

§A¦n¡A­º¥ý·PÁ§A´£¨Ñªº¤èªk
«ö¤U¥hµ²ªG¬O¹ïªº¡A¤£¹L§Ú¶K¤F¤W­±¨º­Ó®æ¤l¹Ï¯uªº¬O±Ñµ§
(ÃD¥~¸Ü¡A§Aªº¦C¼Æ¸òÄæ¼Æ¹ê»Ú¥X¨Óªº°}¦C¦h¤@)

¦]¬°¨º­Ó¬O¶×¤Jµ{¦¡«á«á¦]¥X¨Óªº®ÄªG
¹ê»Ú¤W§Ú»Ý­nªº¬O­«·s±Æ§Ç¦b¦P¤@Ä椤

¤]´N¬O¥ý­pºâÁ`¦@¦³´X¦C
µM«á°£¥H»Ý¨DÅܼƥثe¥i¯à¬°(9¡B12¡B15)
µM«á­«·s±Æ¦C
ų©ó¤j®a³£¥u¬Ý­º¼Ó¡A©Ò¥H§Ú­«µo¤F¤@­Ó©«¤l
[url=http://forum.twbts.com/viewthread.php?tid=20994&extra=]¶WÁåµ²[\url]
­Y»Õ¤U¦³¸ÑªkÁٽв¾¾r½ç±Ð

p.s.¦hÁ«§A³o­Ó°}¦C¡A­n¤£µM§Ú¨º­Ó´£°Ýªº¼Æ¦r±Æ§Ç´N°÷§Ú¶æªº¤F

m(_ _)m ·PÁ§A

TOP

§A¦n¡AVBAªº³¡¤À¯à§_À°§Ú¼W¥[Á`¼ÆÄæ¦ì
ªí®æ¼Æ»PÁ`¼Æ¾Ü1¿ï¥Î(¥i³]©wÀu¥ý¥þ¬°ªí®æ)
¦ÓÁ`¼Æªº³¡¤À¼Æ¦r°£¥HÄæ*¦C
¤]´N¬O»¡­YÁ`¼Æ¬°1234 ¦C=3 Äæ=5 (¶·¥ý­×§ï­ì¥»vbaÄæ¦C¦U¦h1ªº°ÝÃD)
ªí®æ¼Æ´N¬O =1234/(3*5)
¥X¨Óªº­È¬O82.26667¡AµL±ø¥ó¶i¦ì¬°83
³o¼Ë§Ú¥u­n¶ñ¤JÁ`¼Æ´N¤£¥ÎºâÁ`¦@´X­Óªí®æ¤F

µM«áÁٻݭn±N­È½Æ»s¨ì·sªºÄæ©Î·sªº¬¡­¶Ã¯ (³o¸Ì¥i¥Hª½±µ±Æ¦bDÄæ)
¥Ø«e§Ú¥Îªº¤èªk¬O¥ý¤gªk·Ò¿ûªº¥Î"=¤u§@ªí1!E2" (¥i¥H¬Ý¤@¤Uªþ¥ósheet1ªº³¡¤À)
¦ý¦h¤F§Ë¤£°_¨Ó¡A¥BµLªkÀHµÛ°}¦CÅܧó(¥u¯à©T©w3*5)


¼Ë¤l¤j·§¹³³o¼Ë
  1. =¤u§@ªí1!E2
  2. =¤u§@ªí1!F2
  3. =¤u§@ªí1!G2
  4. =¤u§@ªí1!H2
  5. =¤u§@ªí1!I2
  6. =¤u§@ªí1!E3
  7. =¤u§@ªí1!F3
  8. =¤u§@ªí1!G3
  9. =¤u§@ªí1!H3
  10. =¤u§@ªí1!I3
  11. =¤u§@ªí1!E4
  12. =¤u§@ªí1!F4
  13. =¤u§@ªí1!G4
  14. =¤u§@ªí1!H4
  15. =¤u§@ªí1!I4
  16. =¤u§@ªí1!E6
  17. =¤u§@ªí1!F6
  18. =¤u§@ªí1!G6
  19. =¤u§@ªí1!H6
  20. =¤u§@ªí1!I6
  21. =¤u§@ªí1!E7
  22. =¤u§@ªí1!F7
  23. =¤u§@ªí1!G7
  24. =¤u§@ªí1!H7
  25. =¤u§@ªí1!I7
  26. =¤u§@ªí1!E8
  27. =¤u§@ªí1!F8
  28. =¤u§@ªí1!G8
  29. =¤u§@ªí1!H8
  30. =¤u§@ªí1!I8
  31. =¤u§@ªí1!E10
  32. =¤u§@ªí1!F10
  33. =¤u§@ªí1!G10
  34. =¤u§@ªí1!H10
  35. =¤u§@ªí1!I10
  36. =¤u§@ªí1!E11
  37. =¤u§@ªí1!F11
  38. =¤u§@ªí1!G11
  39. =¤u§@ªí1!H11
  40. =¤u§@ªí1!I11
  41. =¤u§@ªí1!E12
  42. =¤u§@ªí1!F12
  43. =¤u§@ªí1!G12
  44. =¤u§@ªí1!H12
  45. =¤u§@ªí1!I12
½Æ»s¥N½X
¶¶§Ç¤]´N¬O (?¨ÌÄæ¼Æ¦Ó©w)
²Ä¤@­Óªí®æªº²Ä¤@¼hE~?
²Ä¤G­Óªí®æªº²Ä¤@¼hE~?
³Ì«áªºªí®æªº²Ä¤@¼hE~?

²Ä¤@­Óªí®æªº²Ä¤G¼hE~?
²Ä¤G­Óªí®æªº²Ä¤G¼hE~?
³Ì«áªºªí®æªº²Ä¤G¼hE~?

²Ä¤@­Óªí®æªº³Ì¤U¼hE~?
²Ä¤G­Óªí®æªº³Ì¤U¼hE~?
³Ì«áªºªí®æªº³Ì¤U¼hE~?

ªþ¥ó§Ú¦³¥ý§â­È©ñ¦bDÄæ¨Ã¥H¬õ¦â¼Ðª`¡A¤£¹L¦]¬°vba¥»¨­«ö¤U°õ¦æ«á·|­«»s¡A©Ò¥H§Ú°ò¥»¬O¥Îsheet1¤¤ªº³¡¤À
©ñ¨ì¥D­¶­±¬O¤è«K§A²z¸Ñ¡C
ªí®æ±Æ§Ç.rar (25.62 KB)

³Â·Ð§A¤F!!

TOP

¦^´_ 8# wei9133


§Ú©~µM·|¥Ç³oºØ§C¯Å¿ù»~¡A
ı±o¦n²Û®¢­ò~  QQ


§Ú¦³­×§ï¦n¦¨§A§Æ±æªº¤è¦¡¦^ÂЦb§A³Ì·sªº¥DÃD·í¤¤¡A
½Ð¤p¤ß­¹¥Î¡C

TOP

        ÀR«ä¦Û¦b : ¤f»¡¤@¥y¦n¸Ü¡A¦p¤f¥X½¬ªá¡F¤f»¡¤@¥yÃa¸Ü¦p¤f¦R¬r³D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD