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

[µo°Ý] ¦Û°Ê¤À°t¥æ´Á

[µo°Ý] ¦Û°Ê¤À°t¥æ´Á

½Ð±Ð¦U¦ì¥ý¶i°ª¤â¡A¤p§Ì¦³¤@­Ó­q³æªº¼Ï¯Ã¤ÀªR
¤é´ÁÄæ¦ì±qHÄæ¶}©l¡AºI¤î¤é´Á¤ÎÄæ¦ì¼Æ¶q¤£¤@©w¡A³Ì«á­±·|¦³¤@­Ó¥[Á`¡A¦³­q³æªº¤~·|¥X²{¤é´Á¡A«D¬G©w¶¡¹j

Excel°ÝÃD-20221001.rar (15.64 KB)
¦]¬°§Æ±æ¯à°÷²{©Ê¥Í²£¡A·Q­n±N­q³æ©¹«e¥­§¡Åu¥­¡A©¹«e¥­§¡¤£¸ó¤ë (¦p¤U~)¡A½Ð°Ý¦U¦ì¬O§_¦³¤ñ¸û¦nªº¤èªk¡AÁÂÁ¡I




Adam

¦^´_ 1# adam2010


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾Ç¾Ç²ß¹Lµ{¤¤½m²ß¹L±Æµ{¬ÝªO,«e½úªº»Ý¨D¸ò±Æµ{¬ÝªOÃþ¦ü!
·í¤ë´£«e¤À°t,¥Î¤°»ò³W«h°µ¤À°t?
1.»P¤W¯Z¤u®É¦³Ãö¶Ü?¨Ò¦p:24¤p®É¥Í²£!¥þ¦~µL¥ð?©Î0800~1200 & 1300~1700¥Í²£¨Ò°²¤é¥ð®§?
2.¨C­Ó¦¨«~¥Í²£¶g´Á¦³Ãö¶Ü?¨Ò¦p:¨C­Ó¦¨«~¥Í²£¶g´Á10¤ÀÄÁ?©Î¥Í²£¹Lµ{¦³«Ý®Æ®É¶¡?
3.¸ò¦¨«~¨}²v¦³Ãö¶Ü?¨Ò¦p:²Î­p¼Æ¾Ú10­Ó¦¨«~¬ù8­Ó¬O¨}«~?

TOP

¥»©«³Ì«á¥Ñ adam2010 ©ó 2022-10-3 20:12 ½s¿è

¦^´_ 2# Andy2483
±z¦n~
·í¤ë´£«e¤À°t,¥Î¤°»ò³W«h°µ¤À°t?
¡÷ «áÄò·|¦A°w¹ï¦U®Æ¸¹¤À§Gªº¯¸ÂIª¬ªp¤À°t¥æ´Á¡A©Ò¥H¦pªG¨S¦³¥ý±N¥æ´Á§@¤@­ÓÅu¥­°Ê§@¡A¦³¥i¯à¥X²{¦h§å¦¸¦P¤é¥æ´Á¡AÃø¥H°Ï¤ÀÀu¥ý¶¶§Ç¡AµLªk¹F¦¨½u©Ê¥Í²£ªº¥Ø¼Ð¡A¦Ó­q³æªº©ñ¸m¦U«È¤á¦³·í¤ë³Ì²×¥æ³f¤éªº°ÝÃD¡AµLªk®Ú¾Ú¥ÍºÞªº»Ý¨DÅu¥­©ñ¸m¡A©Ò¥H¤~»Ý­n¦¹«á»s
1.»P¤W¯Z¤u®É¦³Ãö¶Ü?¨Ò¦p:24¤p®É¥Í²£!¥þ¦~µL¥ð?©Î0800~1200 & 1300~1700¥Í²£¨Ò°²¤é¥ð®§?
¡÷¥Ø«e¤½¥q°£¤F·³­×¥H¥~°ò¥»¤W¬O24H¥Í²£«¬ºA¡A¬G¤£»Ý¦Ò¼{©µ®É¥[¯Zªº°ÝÃD
2.¨C­Ó¦¨«~¥Í²£¶g´Á¦³Ãö¶Ü?¨Ò¦p:¨C­Ó¦¨«~¥Í²£¶g´Á10¤ÀÄÁ?©Î¥Í²£¹Lµ{¦³«Ý®Æ®É¶¡?
3.¸ò¦¨«~¨}²v¦³Ãö¶Ü?¨Ò¦p:²Î­p¼Æ¾Ú10­Ó¦¨«~¬ù8­Ó¬O¨}«~?
¡÷¥Í²£¶g´Á¬O«áÄò±Æµ{­n¦Ò¶q¦U®Æ¸¹³~µ{»P¦U¯¸ÂI¼Ð·Ç¤u®É¡B¨}²vµ¥°ÝÃD¤À°tµ¹¦U¥Í²£§å¦¸¡F¦U¯¸ÂI¥Í²£¶¶§Ç°ò¥»¤W¥ÎÃþ¦üCR­È (critical ratio¡A­pºâ¤èªk=¥æ´Á´î¥h¥Ø«e¤é´Á¤§®tÃB,¦A°£¥H¤u´Á¡A¼Æ­È¶V¤pªí¥Üºò«æµ{«×¶V°ª¡A±Æµ{Àu¥ý¯Å°ª ) ªº¤è¦¡´«ºâµ{¶¶§ÇÅý²£½uª¾¹D­n¥ý²£­þ¨Ç§å¦¸
¤£ª¾¹D¦³¨S¦³¦^µª¨ì±zªº°ÝÃD?
Adam

TOP

¦^´_ 3# adam2010


    ÁÂÁ«e½ú¦^ÂÐ
½Ð«e½ú¥ý¸Õ¬Ý¬Ý
1.Âà¸m¤è¦¡¬O§_¾A¦X
2.Åu¥­¤è¦¡¬O§_¾A¥Î

¤µ¤Ñ²ß±o ¸ê®ÆÂà¸m,Åu¥­¤Ñ¼Æ
½m²ß°}¦C
  1. Option Explicit
  2. Sub ¸ê®ÆÂà¸m()
  3. Dim Brr, Crr, c&, i&, j&, x&, xR, R, T, v, Y, Z, xA, Sc&, Sr&
  4. Dim B#, K%, P$, Q, Ra, Rs, Rn, n&, Avgd&, Avgv
  5. Set xA = ActiveSheet.Cells
  6. Set Y = CreateObject("Scripting.Dictionary")
  7. For Each Ra In xA.SpecialCells(2)
  8.    R = Ra.Address
  9.    Rn = IIf(Ra = "Á`­p" And Rn = "" And Rs <> "", Ra.Address, Rn)
  10.    Rs = IIf(Ra = "Á`­p" And Rs = "", Ra.Address, Rs)
  11. Next
  12. Brr = xA.Range(Rs, Rn)
  13. Rs = Brr(1, 8)
  14. Rn = Brr(1, UBound(Brr, 2) - 1)
  15. Sc = Rn - Rs + 1 '¤Ñ¼Æ
  16. Sr = UBound(Brr) - 2 '«~¸¹¼Æ
  17. ReDim Crr(1 To Sc * Sr, 1 To 13)
  18. For j = 2 To Sr + 1
  19.    For i = 1 To Sc
  20.       n = n + 1
  21.       Crr((j - 2) * Sc + i, 1) = n
  22.       Crr((j - 2) * Sc + i, 2) = Rs + (i - 1)
  23.       Crr((j - 2) * Sc + i, 13) = Brr(j, UBound(Brr, 2))
  24.       For x = 1 To 7
  25.          Crr(n, x + 2) = Brr(j, x)
  26.       Next
  27.       
  28.       For x = 8 To UBound(Brr, 2) - 1
  29.          If Brr(1, x) = Rs + (i - 1) Then
  30.             Crr((j - 2) * Sc + i, 12) = Brr(j, x)
  31.          End If
  32.       Next
  33.    Next
  34. Next
  35. For j = n To 1 Step -1
  36.    If Crr(j, 12) > 0 Then
  37.       For i = j - 1 To 1 Step -1
  38.          If Crr(i, 12) > 0 Or Crr(i, 3) <> Crr(j, 3) Or i = 1 Then
  39.             Avgd = j - i
  40.             If Avgd > 0 Then
  41.                Avgv = Crr(j, 12) / Avgd
  42.             End If
  43.             If Crr(1, 12) = "" And i = 1 Then
  44.                Avgd = j - i + 1
  45.                If Avgd > 0 Then
  46.                   Avgv = Crr(j, 12) / Avgd
  47.                End If
  48.             End If
  49.             Exit For
  50.          End If
  51.       Next
  52.    End If
  53.    Crr(j, 10) = Avgd
  54.    Crr(j, 11) = Avgv
  55.    If j > 1 Then
  56.       Avgd = IIf(Crr(j, 3) = Crr(j - 1, 3), Avgd, 0)
  57.       Avgv = IIf(Crr(j, 3) = Crr(j - 1, 3), Avgv, 0)
  58.    End If
  59. Next
  60. If Crr(1, 12) <> "" Then
  61.    Crr(1, 10) = 1
  62.    Crr(1, 11) = Crr(1, 12)
  63. End If
  64. Workbooks.Add
  65. [A1].Resize(1, 13) = Split("NO/¤é´Á/®Æ¸¹/¦T/ºU/¥i¥Î®w¦s/1300/" _
  66. & "¦b¨î¶q/Var/Åu¥­¤Ñ¼Æ/Åu¥­¼Æ¶q/­q³æ¼Æ¶q/Á`­p", "/")
  67. [A2].Resize(UBound(Crr), UBound(Crr, 2)) = Crr
  68. Cells.Columns.AutoFit
  69. Cells.Rows.AutoFit
  70. Cells.Columns.AutoFit
  71. [2:2].Select
  72. ActiveWindow.FreezePanes = True
  73. [1:1].AutoFilter
  74. [A1].Select
  75. Cells.Borders.LineStyle = xlContinuous
  76. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ adam2010 ©ó 2022-10-4 21:00 ½s¿è

¦^´_ 4# Andy2483
·PÁÂAndy2483¤j¤j¹©¤O¬Û§U¡A¤w¸g¦³Åu¥­ªº®ÄªG¡A¤£¹L¦]¬°«áÄò±µÄò¤½¥q±Æµ{¨t²Î¡A¤W¶Ç»Ý­n­ì¨Óªº¼Ï¯Ã®æ¦¡
Sorry~¤§«e¨S¦³»¡²M·¡¡A¤£¹LÁÙ¬O«Ü·PÁ±zªº¨ó§U~
­ì©l¸ê®Æ~

°õ¦æ«á~
1.A~GÄ椣ÅÜ
2.±N°_°W¤é´Á¤¤¶¡ªº¤é´Á¶ñº¡(¶À©³³¡¤À)
2.±N­q³æ¥­§¡©¹«eÅu¥­(¥|±Ë¤­¤J¨ú¾ã¼Æ)¦ý¤£¥i¸ó¤ë
¦p¤U~

Excel°ÝÃD-20221004-2.jpg (16.19 KB)

Excel°ÝÃD-20221004-2.jpg

Excel°ÝÃD-20221004.rar (12.02 KB)

Adam

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 5# adam2010


    ÁÂÁ«e½ú¦^ÂÐ
½Ð«e½ú¸Õ¬Ý¬Ý
¤µ¤Ñ²ß±o¶Ã¼Æ±a¤J»²§UÀË´ú
½m²ß°}¦C»P¦r¨å
Excel°Ñ¦Ò-20221007.zip (43.85 KB)

Åu¥­«e:


Åã¥ÜÅu¥­Á`«á»~®t¶q:


©¹«eÅu¥­µ²ªG:

TOP

¦^´_ 5# adam2010


    »²§UÀË´ú¥¿½T©Ê»¡©ú¦p¤U:
¥t³]¤@«ö¶s,»²§UÀË´úµ{¦¡½X°õ¦æµ²ªGªº¥¿½T©Ê:


¶Ã¼Æ±a¤Jªí®æ,ÀË´ú100¦¸ªºµ²ªG:


§e²{³Ì«á¤@µ§¶Ã¼Æ±a¤Jªí®æ,ÀË´úªºµ²ªG

TOP

¦^´_ 6# hcm19522


    ·PÁÂhcm19522¤j´£¨Ñªº¤½¦¡¡A«e´X¤Ñ¶i¤£¤F¯¸½u¦b¤~¬Ý¨ì¡AÁÂÁÂ~
Adam

TOP

¦^´_ 7# Andy2483

·PÁÂAndy2483¤j´£¨Ñªº¥¨¶°¡A«e´X¤Ñ¶i¤£¨Ó²{¦b¤~¬Ý¨ì¡A¹ê¦b¤Ó·PÁ±z¤F
¨ä¹ê¦]¬°«áÄò¥Í²£¥X¨Óªº¼Æ¶q·|¦]¬°¨}²v¹w¦ô»P¹ê»Ú¨}²vªº¸¨®t¦Ó·|¸ò­q³æ¶q¦³¨Ç³\¸¨®t¡A
©Ò¥H¥|±Ë¤­¤Jªº¤Ö³\»~®t¬O¥i¥H©¿²¤ªº¡A¬Ý¦U²£·~ªºOTD­pºâ¤è¦¡¡A¦³ªº¤@©w­n¥æ¨¬¦³ªº®e³\¤@©wªº»~®t¶q´Nºâ¹F¦¨
¦Û»s¾P°â»P¥N¤u¤]·|¦³©Ò¤£¦P
Adam

TOP

        ÀR«ä¦Û¦b : ¡i¬O§_µo´§¤F¨}¯à¡H¡j¤H¶¡¹Ø©R¦]¬°µu¼È¡A¤~§óÅã±o¬Ã¶Q¡CÃø±o¨Ó¤@½ë¤H¶¡¡AÀ³°Ý¬O§_¬°¤H¶¡µo´§¤F¦Û¤vªº¨}¯à¡A¦Ó¤£­n¤@¨ý¨Dªø¹Ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD