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

¦³Ãösheet1©î¶}¤Î½Æ»s¸ê®Æ¦Üsheet2

¦³Ãösheet1©î¶}¤Î½Æ»s¸ê®Æ¦Üsheet2

¦]¬°¤u§@¤W»Ý­n¤ÀªRÃe¤jªº¸ê®Æ¡A³Â·Ð¦n¤ßªº«e½úÀ°§Ú¸Ñ¤@¤U¡A¤p§Ì·P¿E¤£ºÉ¡AÁÂÁ§r!
°ÝÃD»¡©ú:¦bsheet1¤º AÄæ¨Ì"¡B"©î¦¨¼Æµ§¸ê®Æ
                 ¡ABCJKLÄæ«h¤@¼Ë½Æ»s
                 ¡ADEFGHIÄ椤ªº"1"¶È·|½Æ»sµ¹²Ä1µ§¸ê®Æ¡A"-"«hµ¹©î¶}ªº²Ä2µ§¸ê®Æ¡B²Ä3µ§¸ê®Æ¡B²Ä4µ§¸ê®Æ..
             ¡AMNOPÄ椤«h¨Ì§Ç©ñ¤J©î¶}¦WºÙ¸ê®Æ
                 ¡A§Æ±æ¹F¨ì¦¨ªG¦p"sheet2"¤º®e

°ÝÃD.zip (4.48 KB)

°ÝÃD.zip (4.48 KB)

°ÝÃD

¦^´_ 1# pesi2884
  1. Sub Ex()
  2. Dim Ay(16), Ary()
  3. With Sheet1
  4. For Each a In .Range(.[A2], .[A65536].End(xlUp))
  5. Ar = Split(a, "¡B")
  6. For i = 0 To UBound(Ar)
  7. Ay(0) = Ar(i)
  8.    For j = 1 To 11
  9.       Ay(j) = a.Offset(, j).Value
  10.       If i > 0 And Ay(j) = 1 Then Ay(j) = "-"
  11.    Next
  12.    x = 0
  13.    For k = 0 To UBound(Ar)
  14.       If k <> i Then Ay(12 + x) = Ar(k): x = x + 1
  15.    Next
  16.    ReDim Preserve Ary(s)
  17.    Ary(s) = Ay
  18.    s = s + 1
  19.    Erase Ay
  20. Next
  21. Next
  22. End With
  23. Sheet2.[A2:P65536] = ""
  24. Sheet2.[A2].Resize(s, 16) = Application.Transpose(Application.Transpose(Ary))
  25. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

Hsieh«e½ú:
³o­Ó°ÝÃD§Ú·Q¤F¦n¤[¡A§Ú¤]¹Á¸Õ¦Û¤v¼g¤F¦n¤[®É¶¡¡A
¤À¤F«Ü¦h¬qµ{¦¡¼g¦b«ö¶s¤º¡A»Ý­n¤À¬q§¹¦¨¡A
·Q¤£¨ì§A¤@­Óµ{¦¡¤@¤U¤U´N¸Ñ¨M¤F!
§Úrun¤F¤§«á¨S¦³°ÝÃD¡A
¤ñ§Ú¦Û¤v¼gªºµ{¦¡¤]¤S¯ä¤Sªø¡A¦n¤Ó¦h¤F¡A
§Æ±æ¦³¤@¤Ñ¥i¥H¾Ç¨ì§A¤@¥bªº¹Ò¬É´N¦n°Õ¡C
¯uªº«ÜÁÂÁ§A!

TOP

¦^´_ 4# sping


    µu¤£¤@©w´N¬O¦n
¯à°÷§¹¦¨»Ý¨Dªºµª®×³£¬O¦nµª®×
¤À¬q°õ¦æ¦³¤À¬q°õ¦æªº¦n³B
®e©ö°£¿ù¤Î­×¥¿µ{¦¡
µ{¦¡µu¥i¯à¥Î¨ìªº¨ç¼Æ¦h°õ¦æ®Ä²v¤£¤@©w¤ñ¸û¦n
¦Ó¥B¦pªG¨S¦³§¹¥þ¤F¸Ñµ{¦¡ªº·N«ä
¥H«á­n­×§ï¥i¯à·|³y¦¨«Ü¤jªº§xÃø
©Ò¥H¤£¥²¸r¼}µ{¦¡Â²µu
¾Ç²ß¨ì¤º®e§t·N¤~¬O­«­nªº
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¯¸¦b¥b¸ô¡A¤ñ¨«¨ì¥Ø¼Ð§ó¨¯­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD