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

[µo°Ý] ¦p¦ó³]¤@«ö¶s¡A´N¯à§â¤½¦¡Âà´«¹Lªº­È¡A¨ú¥N­ì¦³ªº­È?

[µo°Ý] ¦p¦ó³]¤@«ö¶s¡A´N¯à§â¤½¦¡Âà´«¹Lªº­È¡A¨ú¥N­ì¦³ªº­È?

¦U¦ì¤j¤j¦n

§Ú¦³¤@­ÓEXCELÀɮסAÃþ¦ü­û¤u¸ê®Æ

©m¦W    ¤u§@²Õ§O    ¦~¸ê

  A            B                    C
¤p©ú    ¹q¾÷A²Õ     1¦~0¤ë20¤é
¤pµØ    ¹q¾÷B²Õ     2¦~10¤ë0¤é
¤p¤ý    ªoº£A²Õ     0¦~0¤ë25¤é

¤j·§¹³³o¼Ë

¦~¸êªº³¡¤À¡A¤w¸g°Ý¨ìµª®×¤F¡A¦]¬°§Ú­nÂà´«¦¨OO¦~OO¤ëOO¤é
D¥h³]¤½¦¡      =TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C1,"¤é",),"¦~",":"),"¤ë",":"),"[hh]¦~mm¤ëss¤é")

¤u§@²Õ§O¡A§Ú¥u»Ý­n¤jÃþ¡A¹q¾÷¤£ºÞ¦bA©ÎB¡A´NÅã¥Ü¹q¾÷¡Aªoº£´NÅã¥Üªoº£
E¥h³]¤½¦¡     =IF(MID(B1,1,2)="¹q¾÷","¹q¾÷",IF(MID(B1,1,2)="ªoº£","ªoº£",""))  ´N¬O¨úB1«e¨â­Ó¦r¡A¬O¹q¾÷´NÅã¥Ü¹q¾÷

¥u¬O¸ê®Æ¦³¤W¦Êµ§¡A§Ú¥Ø«e¥²¶·¥ý³]D¸òE ¡A¦A§â¤½¦¡¶K¤W¦Êµ§
µM«á±o¨ì¥¿½T®æ¦¡ªº¼Æ­È«á¡A¦A½Æ»s¡A¶K¤W­È¡A§âB¨ú¥N¡A§âC¨ú¥N
±o¨ì¥H¤Uªº¼Ë¤l

  A            B               C
¤p©ú    ¹q¾÷     01¦~00¤ë20¤é
¤pµØ    ¹q¾÷     02¦~10¤ë00¤é
¤p¤ý    ªoº£     00¦~00¤ë25¤é

¦³¨S¦³¿ìªk³]¤@­Ó«ö¶s
¥i¥H¦Û°Ê°»´ú§Ú¶K¶i¨Óªº¸ê®Æ¦³´Xµ§
µM«á¥i¥H¦Û°Ê§âB¸òC¡A¨Ì·Ó§Ú³]ªº¤½¦¡¥hÂàÅÜ
¨Ã¨ú¥N­ì¥»ªº­È

ÁÂÁÂ
«¢Åo~¤j®a¦n§r

¤W¶ÇÀÉ®×, ¤~¦n³B²z~~

TOP

¦^´_ 1# iceandy6150

¸Õ¥Î¬Ý¬Ý¡H §Ú·QÀ³¸ÓÁÙ·|¦³¤H¼gªº¤ñ§Ú§ó²µu§a¡C
¦pªGBªº ¹q¾÷¡Bªoº£...µ¥¡A³£¬O©T©w¦b«e­±¨â­Ó¡A§Ú·Q¥u»Ý­n¨Ï¥ÎMID´N¥i¡C
  1. Sub test()
  2. x = [A1].CurrentRegion.Rows.Count
  3. For i = 2 To x
  4.     Cells(i, "B") = Mid(Cells(i, "B"), 1, 2)
  5.     y = Application.Substitute(Cells(i, "C"), "¤é", ":")
  6.     y = Application.Substitute(y, "¤ë", ":")
  7.     y = Application.Substitute(y, "¦~", ":")
  8.     Cells(i, "C") = Application.Text(y, "hh¦~mm¤ëss¤é")
  9. Next i
  10. End Sub
½Æ»s¥N½X

TOP

¦^´_ 3# »a³·

ÁÂÁ§Aªºµ{¦¡¡A¥i¥H¹B§@µL»~
¤Ó´Î°Õ~
·PÁÂ

¦^ ­ã´£³¡ªL ¤j¤j
¦]¬°³o­Óµ{¦¡§Ú¤£ª¾¹D«ç»ò¼g
©Ò¥H´N¨S¥áÀɮפW¨Ó¤F
«¢Åo~¤j®a¦n§r

TOP

¦^´_ 3# »a³·


    ¤j¤j¡A¥i¥H¦A°Ý­Ó¶i¶¥ÃD¶Ü

³o­Ó«ö¶sªº«ü¥O¡A¥i¤£¥i¥H "§@¥Î¦b" §OªºÀÉ®×? ©Î¬O§OªºSHEET?

¨Ò¦p§Ú²{¦b«ö¶s¬O¼g¦bÀÉ®×AªºSheet1¤W­±¡A«ö¤U¥h¤§«á

§Ú·QÅýSheet2¸Ì­±ªº¸ê®Æ§@ÅÜ°Ê

©Î¬O§Ú·QÅýÀÉ®×BªºSheet2¸Ì­±ªº¸ê®Æ§@ÅÜ°Ê

¦³¿ìªk¶Ü?  ÁÂÁÂ
«¢Åo~¤j®a¦n§r

TOP

¤£¦n·N«ä....­è­è¤~·Q¨ì¤@­Ó°ÝÃD

­ì¥»ªº³]©w¬O
   A       B        C
©m¦W  ²Õ§O   ¦~¸ê

¦ý¦³®É­Ô¡A±Æ¦C¤£¤@©w·|·Ó³o¼Ë
¤]³\²Ä¤@­ÓÀɮ׬O·Ó¶¶§Ç±Æ
¦ý²Ä¤G­ÓÀɮסA¸Ì­±´N¤£¤@¼Ë¤F
¥i¯à¬O

   A       B        C
©m¦W   ¦~¸ê  ²Õ§O

µ{¦¡¦³¨S¦³¿ìªk»¡¡A¦Û¤v¥ý¥h§ä¼ÐÃD¦C(¦]¬°¤@©w·|¦³¼ÐÃD¦C)
¦pªG¸ÓÀɮסA¦~¸ê¦bBÄd¡A«h§@¥Î¦bBÄd
¦P²z¡A¸ÓÀɮצ~¸ê¦bCÄd¡A´N§@¥Î¦bCÄd

¤£¾å±o°µ¤£°µ±o¨ì
ÁÂÁÂ
«¢Åo~¤j®a¦n§r

TOP

¦^´_ 4# iceandy6150

Sub Âà´«()
Dim Arr, i&, j%, T$
Arr = Range([C1], [A65536].End(xlUp))
For i = 2 To UBound(Arr)
For j = 1 To 3
    T = Arr(i, j)
    If Arr(1, j) = "¦~¸ê" Then
       T = Application.Text(Replace(Replace(Replace(T, "¤é", ""), "¤ë", ":"), "¦~", ":"), "[hh]¦~mm¤ëss¤é")
    ElseIf Arr(1, j) = "²Õ§O" And T Like "*[A-z]²Õ*" Then
       T = Left(T, Len(T) - 2)
    End If
    Arr(i, j) = T
Next j
Next i
[A:C].NumberFormatLocal = "@"
[A1:C1].Resize(UBound(Arr)) = Arr
End Sub

­n§A¤W¶ÇÀÉ®×, ¬OÅý·QÀ°¦£ªº¤H¤£¥Î¦A¦hªá®É¶¡¥h«Ø¥ß´ú¸Õ¸ê®Æ, ³o¬O³Ì°ò¥»ªº´£°Ý³W«h:
X0002.rar (8.45 KB)

TOP

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

«z~ ª©¥D¦n±jªü  
   
¯u¬O¤Ó¼F®`¤F

·PÁÂ

¥H«áµo°Ý§Ú·|¥ý°µ­ÓÀɮ׶ǤW¨Ó
ÁÂÁÂ
«¢Åo~¤j®a¦n§r

TOP

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

ª©¥D±z¦n  §Ú¬ã¨s¤F´X¤Ñ
¦³¤@¨Ç¤p°ÝÃD
¦A³Â·Ð¤F

1.±zªºÀɮ׬O.xls¡AµM«á§ÚÂI[¶}µo¤H­û]¡A[³]­p¼Ò¦¡]¡A¥hÂI«ö¶s¡A©~µM¨S¶]¥Xµ{¦¡½X¡C¤£¾å±o±z¬O«ç»òÅý«ö¶s¥i¥H±Ò°Êµ{¦¡ªº?

  (§Ú³£¬O[´¡¤J]¡AActiveX±±¨î¶µ¡A¸Ì­±ªº«ö¶s¡C¦A¥Î³]­p¼Ò¦¡ÂI¶}¡A©ñ¤Jµ{¦¡½X¡C¦ý¬O³o¼ËÀÉ®×¥²¶·¦s¦¨.xlsmÀÉ)


2.±zªºµ{¦¡¤¤
   Arr = Range([C1], [A65536].End(xlUp))  ---->±q¥k¤WC1¨ì¥ª¤UA65536³o­Ó½d³ò¤º¡A¦³¨Ï¥Îªº½d³ò¨ì©³(¥i¥H±o¥X¦³´Xµ§¸ê®Æ¡A¤]´N¬O´X¦C)
For i = 2 To UBound(Arr)  ----->±q2¨ì¦³¨Ï¥Îªº¦C¼Æ¡A¹ï¶Ü?

¦pªG§ÚªºÀɮסA¤£¥u¦³ ©m¦W¡A²Õ§O¡A¦~¸ê¡CÁÙ¦³¥X¥Í ¦~¤ë¤é¡B¦í§}¡B¹q¸Üµ¥µ¥
¨º»ò¡A³o¼Ëªº¼gªk¡AÁÙ¬O¥i¥H§ä¥X¡A¨ì©³¦³´X¦Cªº¸ê®Æ¡A¹ï§a? (§Ú´ú¸Õ¬O³£¨S°ÝÃD)


3.©Ó¤W¡A±zªºµ{¦¡³Ì«á­±
[A:C].NumberFormatLocal = "@"
[A1:C1].Resize(UBound(Arr)) = Arr
³oÃä¬O¦b§â²Ä¤@Äæ¨ì²Ä¤TÄ檺¸ê®Æ¡A­×§ï¦nªº¡A©ñ¦^¥h¨ú¥N­ì¨Óªº¡A¹ï§a

¨º¡A¦pªG§ÚªºÀɮסA¤£¥u¦³ ©m¦W¡A²Õ§O¡A¦~¸ê¡CÁÙ¦³¥X¥Í ¦~¤ë¤é¡B¦í§}¡B¹q¸Üµ¥µ¥
¤]´N¬OÄæ¡A¤w¸g¤£¥u3Äæ¤F¡A§Ú­n¦Û°Ê°»´ú¦³´XÄæ
¥Ø«e§Úªº¼gªk¬O
¦h³]¤@­ÓK
k = Range("A1", Range("A1").End(xlToRight)).Count
For i = 2 To UBound(Arr)
For j = 1 To k
³o¼Ë§Ú´ú¸Õ¬O³£¥¿±`¡A¥i¥H§ä¥X¨ì©³¥Î¤F´XÄæ¡A¨Ã¥B¨C¤@Äæ³£·|Àˬd¨ì

¤§«e°O±o¡A¦n¹³¦³¤H±Ð¹L¤@­Ó»yªk
¬O...   
.UsedRange.Rows.Count
¬Û«H¦pªG­n­pºâÄæ
À³¸Ó¬O§ï¦¨
.UsedRange.Column.Count  ¹ï§a?

¤£¾å±o
Arr = Range([C1], [A65536].End(xlUp))
For i = 2 To UBound(Arr)
¸ò
k = Range("A1", Range("A1").End(xlToRight)).Count
For j = 1 To k
¸ò
.UsedRange.Rows.Count

³o´XºØ¬O®t¦b­þ¸Ì? ³£¥i¥H¥Î¶Ü  ÁÙ¬O¥\¯à¦³®t©O

¥H¤W´X­Ó°ÝÃD½Ð±Ð
·PÁÂ
«¢Åo~¤j®a¦n§r

TOP

¹ï¤F¡AÁÙ¦³­Ó°ÝÃD§Ú¤£·|¸Ñ¨M

´N¬Oª©¥Dµ{¦¡³Ì«á­±
[A:C].NumberFormatLocal = "@"
[A1:C1].Resize(UBound(Arr)) = Arr

­ì¥»¥u¦³¤TÄæ¡A³o¼Ë¼gOK
¥i¬O¦pªG¤£ª¾¹D¦³´XÄæ©O?

§Ú¥ÎK¨Ó±oª¾¦³´XÄæ
k = Range("A1", Range("A1").End(xlToRight)).Count

©Ò¥H¬O­n§ï¦¨
[A:Text(k)].NumberFormatLocal = "@"
[A1:Text(k)&1].Resize(UBound(Arr)) = Arr
³o¼Ë¶Ü?
¦n¹³·|¦³°ÝÃD­C

¦A½Ð¦U¦ì¥ý¶i¸Ñ´b¡AÁÂÁÂ
«¢Åo~¤j®a¦n§r

TOP

        ÀR«ä¦Û¦b : ¦³Ä@©ñ¦b¤ß¸Ì¡A¨S¦³¨­Åé¤O¦æ¡A¥¿¦p¯Ñ¥Ð¤£¼½ºØ¡A¬Ò¬OªÅ¹L¦]½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD