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

[µo°Ý] ²Î­p¶µ§t¦³¤å¦r

B5:B6 °Ï°ì°}¦C¤½¦¡¡A¼Æ¶q¥Ñ¤j¦Ü¤p±Æ¦C¡A(¤p¦Ü¤j Åý§A½m²ß)
=INDEX($A:$AE,{1;2},MOD(LARGE(TEXT($B2:$AE2,"0;;0;!0")*10^4+COLUMN($B:$AE),COLUMN(A1)),10^4))

1 ¥ý½Æ»s¤½¦¡
2 ¿ï¾Ü B5:B6 Àx¦s®æ
3 ½s¿è¦C¶K¤W¤½¦¡
4 ¹«¼Ð¦ì©ó½s¿è¦C¡A¥Î¤TÁä(CTRL+SHIFT+ENTER)¿é¤J¤½¦¡
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ johnny1680 ©ó 2021-10-7 19:12 ½s¿è

¦^´_ 11# ML089
²Ä1¦¸¨£¨ì"°Ï°ì°}¦C"¤½¦¡
·PÁ«ü¾É¡C

²{¦b§Ú·Q±NLARGE§ï¬°SMALL
°é¿ïB5:B6¡A¦b½s¿è¦C±NLARGE§ï¬°SMALL«á¡A¦P®É«ö3Áä¡A«o¶]¥X"§A¤£¯à¥u§ïÅÜ1­Ó°}¦Cªº¤@³¡¤À"ªºÄµ»y¡F
§Ú±N½s¿è¦C¤½¦¡²¾°£©Î±NÀÉ®×Ãö³¬³£¤£¦æ¡A¤£ª¾¹D«ç»ò¿ì?
µ¥µo§¹³o½g¤å½Z¡A§Ú±j¨îÃö¾÷¬Ý¬Ý^^"

ÁÙ¬O½Ð±z«ü¾ÉB6ªº³æ¿W¤½¦¡¡A¥B²Ä2¦C=""ªºÀx¦s®æ¡A²Ä6¦C¤]=""(¤£Åã¥Ü0)
µª®×¦pB8:AE8
¦]¬°²Ä2¦C=""ªºÀx¦s®æ=²Ä1¦C"³f«~¶µ"¤w°±°â¡A©Ò¥HBOSS¤£·QÅã¥Ü0(¦o»¡ : Åã¥Ü0´N=­nºò«æ¸É³f¤F)¡C
ÁÂÁ±z !

TOP

¥»©«³Ì«á¥Ñ johnny1680 ©ó 2021-10-7 19:32 ½s¿è

¦^´_ 11# ML089
§Æ±æB6ªºµª®×¯à¦pB8:AE8¤@¼Ë(¦³ÁY±Æ)
ÁÂÁ±z !

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2021-10-7 20:07 ½s¿è

¦^´_ 13# johnny1680

B5 °}¦C¤½¦¡ (¤TÁä¿é¤J)
=INDEX($A:$AE,ROW(A1),MOD(SMALL(TEXT($B$2:$AE$2,"0;;\9\E\5;\8\E\5")*10^4+COLUMN($B:$AE),COLUMN(A1)),10^4))&""
¥k©Ô¤U©Ô¡A¤å¦r®æ¦¡


±Æ¦C¶¶§Ç¡G¼Æ¶q ¥Ñ¤p¦Ü¤j¡A¸É³f¤¤¡AªÅ®æ¶µ¥Ø
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 14# ML089
ª©¤j±z¤]¤Ó±j¤F!!!
¤@¦¡¸Ñ¨M2­Ó»Ý¨D¡C
ÁÂÁ§AªºÀ°¦£!

TOP

¦^´_ 12# johnny1680

²{¦b§Ú·Q±NLARGE§ï¬°SMALL
°é¿ïB5:B6¡A¦b½s¿è¦C±NLARGE§ï¬°SMALL«á¡A¦P®É«ö3Áä¡A«o¶]¥X"§A¤£¯à¥u§ïÅÜ1­Ó°}¦Cªº¤@³¡¤À"ªºÄµ»y¡F
§Ú±N½s¿è¦C¤½¦¡²¾°£©Î±NÀÉ®×Ãö³¬³£¤£¦æ¡A¤£ª¾¹D«ç»ò¿ì?
µ¥µo§¹³o½g¤å½Z¡A§Ú±j¨îÃö¾÷¬Ý¬Ý^^"
   


¤W¦¸§Ñ°O¦^Âгo°ÝÃD
°Ï°ì°}¦C¤½¦¡­×¥¿½T¹ê«Ü³Â·Ð¡A­ì¥ý¬O³æ®æ°}¦C¤½¦¡¡A¤£¯àª½±µ®M¤W¥h­×§ï¡A»Ý¥ý§R°£Â¤½¦¡¡A¤~¯à­«·s¤W°Ï°ì°}¦C¤½¦¡¡C

°Ï°ì°}¦C¤½¦¡¥H¤j¶q¸`¬Ù­pºâ¸ê·½¡A¤j¶q¼Æ¾Ú®É¤~Åé·|¡C
¨Ò¦p 10¸Uµ§¸ê®Æ¨ú³Ì¤p«e10­Ó¡A
  SMALL(10¸Uµ§¸ê®Æ¡Arow(a10)) ¥k©Ô10®æ¡Aªí¥Ü³o10­Ó³£»Ý­n³B²z 10¸Uµ§¸ê®Æ±Æ§Ç°µ10¦¸
  SMALL(10¸Uµ§¸ê®Æ¡Arow(1:10)) ª½±µ®M¦b10®æ¡Aªí¥Ü³o10­Ó¥u­n³B²z 10¸Uµ§¸ê®Æ±Æ§Ç°µ1¦¸¡A¤@¦¸ª½±µ¨ú¥X«e10²Õ¸ê®Æ¡A°Z¤£¬O§Ö«Ü¦h¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 16# ML089
ÁÂÁ±zªº¸Ô²Ó»¡©ú¡A
¨ü¯q¨}¦hb^_^d
·P®¦~~~~~

TOP

¦^´_ 1# johnny1680


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò,ÁÂÁ¦U¦ì«e½ú«ü¾É
¤µ¤Ñ«á¾ÇÂǦ¹©«¾Ç²ß¨ì«Ü¦hª¾ÃÑ,½m²ß¨ì«Ü¦h¤èªk,¥H¤U¾Ç²ß¤ß±o½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æ«e:


°õ¦æµ²ªG:



Option Explicit
Sub TEST_1()
Dim Brr, C%, j%, i&, xR As Range
'¡ô«Å§iÅܼÆ:Brr¬O³q¥Î«¬ÅܼÆ,(C,j)¬Oµu¾ã¼ÆÅܼÆ,
'i¬Oªø¾ã¼ÆÅܼÆ,xR¬OÀx¦s®æÅܼÆ

C = Cells(1, Columns.Count).End(xlToLeft).Column
'¡ô¥OC³oµu¾ã¼ÆÅܼƬO ²Ä1¦C³Ì¥k°¼¦³¤º®eÀx¦s®æÄ渹
If C <= 2 Then Exit Sub
'¡ô¦pªGCÅܼÆ<=2!´Nµ²§ôµ{¦¡°õ¦æ
Set xR = Range([A2], Cells(1, C))
'¡ô¥OxR³oÀx¦s®æÅܼƬO [A2]¨ì(²Ä1¦C²ÄCÅܼÆÄæÀx¦s®æ),
'¥OxR³oÀx¦s®æÅܼƬO³o½d³òÀx¦s®æ(ª«¥óÅܼÆ)

Brr = xR
'¡ô¥OBrr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C,¥HxRÅܼÆ(Àx¦s®æ­È)±a¤J
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é!i±q1¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¸¹¦C
   For j = 1 To UBound(Brr, 2)
   '¡ô³]¶¶°j°é!j±q1¨ìBrr°}¦C¾î¦V³Ì¤j¯Á¤Þ¸¹Äæ
      Brr(i, j) = Format(Brr(i, j), "000|")
      '¡ô¥Oi°j°é¦Cj°j°éÄæBrr°}¦C­È¬O«ü©w¤å¦r®æ¦¡ªº¦r¦ê
      '«ü©w¤å¦r®æ¦¡ªº¦r¦ê:­Y­ì¬O¼Æ¦r!´N¸É0¦¨3½X³s±µ"|"

   Next
Next
With xR.Offset(4, 0)
'¡ô¥H¤U¬OÃö©óxRÅܼƦV¤U°¾²¾4¦C½d³òÀx¦s®æªºµ{§Ç
   .Value = Brr
   '¡ô¥O¸Ó½d³òÀx¦s®æ­È¬OBrr°}¦C­È
   .Offset(0, 1).Sort KEY1:=.Item(2, 1), _
   Order1:=1, Key2:=.Item(1), Order2:=1, _
   Orientation:=xlLeftToRight
   '¡ô¥O¸Ó½d³òÀx¦s®æ¦V¥k°¾²¾1Äæ½d³òÀx¦s®æ,
   '°µ¨â¼h¦¸¾î¦V¶¶±Æ§Ç:
   '²Ä1¼h¦¸¥H¸Ó½d³ò¤º¤U¤è2¦C²Ä1ÄæÀx¦s®æ¬°±Æ§Ç°ò·Ç,
   '²Ä2¼h¦¸¥H¸Ó½d³ò¤º²Ä1®æÀx¦s®æ¬°±Æ§Ç°ò·Ç

   .Replace "|*", "", Lookat:=xlPart
   '¡ô¥O¸Ó½d³òÀx¦s®æ­È°µ¤å¦r¸m´«:
   '±N"|"²Å¸¹(§t)¥k°¼ªº©Ò¦³¤å¦r ¸m´«¬°ªÅ¦r¤¸

End With
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : µêªÅ¦³ºÉ¡D§ÚÄ@µL½a¡AµoÄ@®e©ö¦æÄ@Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD