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

[µo°Ý] ·j´M¥N¸¹«á¡A´M§ä¹ïÀ³¦C¼ÆªºÀx¦s®æ

[µo°Ý] ·j´M¥N¸¹«á¡A´M§ä¹ïÀ³¦C¼ÆªºÀx¦s®æ

·Q½Ð±Ðª©¤W«e½ú

¦p§Æ±æ¯à¦b"´M§äsheet"¤¤¡A¬d¸ß¤u§@ªí1¤¤ªº¥ô¤@­Ó¥N¸¹

¬d¸ß«á:©¹¥k·j´M¨ì¦³¼Æ¦rªºÀx¦s®æ¡A¦A¥h¹ïÀ³ªº²Ä¤G¦C¬d©ÒÄݪº½s¸¹

Á|¨Ò:´M§äGV8CX3Y00¡A©¹¥k·j´M·|µo²{¦bN¡BR¡BSÄæ¦ì¦³¼Æ¦r
¹ïÀ³¨ì©ÒÄݪº²Ä¤G¦C¬°MM¡BQQ¡BRR

·Q½Ð±Ð«e½ú¦³¨S¦³¨ç¼Æ©ÎVBA¦³¿ìªk°õ¦æ³o¼Ëªºµ²ªG(§ì¥X¬Y¥N¸¹²Ä¤G¦C¹ïÀ³ªº½s¸¹)
¦]¥N¸¹Äæ¸ò½s¸¹¦C·|¨ì¤W¤d¶µ¡A¤â°Ê¬d¸ß¤w¸g§ÖµLªk­t²ü ·PÁÂ

¨ç¼Æ°ß¤@·Q¨ìªº¥u¦³IF¡A·j´M<>""¡A¦ý§ì¨ì«á­n¦p¦ó¨Ï¤§¹ïÀ³¨ì²Ä¤T¦C
¬O¥dÃöªº¨BÆJ

·j´M½d¨Ò.rar (151.65 KB)

¦^´_ 1# abc9gad2016
³o¬O°Ñ¦Ò  jcchiang ¤j¤jªº¼gªk ¦³ªÅ¬Ý¬Ý¬O¤£¬O³o¼Ë ·PÁÂ
  1. Public Sub ´M§ä¬Û¹ïÄæ¦ì½m²ß()

  2.     Arr = [¤u§@ªí1!A1].CurrentRegion
  3.     Set xD = CreateObject("Scripting.Dictionary")
  4.    
  5.     For Y = 1 To UBound(Arr, 2)
  6.         xD(Arr(2, Y)) = Y
  7.     Next Y
  8.    
  9.     For X = 3 To UBound(Arr, 1)
  10.         For Y = 2 To UBound(Arr, 2)
  11.             If Arr(X, 1) = [´M§ä!A2] And Arr(X, Y) <> "" Then
  12.                 E = E + 1
  13.                 Sheets(2).Cells(2, 1 + E) = Arr(2, xD(Arr(2, Y)))
  14.             End If
  15.         Next Y
  16.     Next X

  17. End Sub
½Æ»s¥N½X

TOP

¦^´_ 1# abc9gad2016

½Ð´ú¸Õ¬Ý¬Ý¡A·PÁ¡C

Sub tt()
Set xD = CreateObject("Scripting.Dictionary")
T = Sheets("´M§ä").[A2]
xD(T & "") = ""
Arr = Range([¤u§@ªí1!T2], [¤u§@ªí1!A65536].End(3))
For i = 2 To UBound(Arr)
    If xD.Exists(Arr(i, 1) & "") Then
        For j = 2 To 19
            If Arr(i, j) <> "" Then: M = M + 1: Arr(1, M) = Arr(1, j)
        Next
    End If
Next
If M > 0 Then Sheets("´M§ä").[B2].Resize(1, M) = Arr
End Sub

TOP

¦^´_ 3# samwang


·PÁ samwang «e½úªº«ü¾É ¤p§Ì¨ü¯q¨}¦h  ½Ð°Ý  samwang«e½ú  T & ""  ¬°¬Æ»ò­n¦ê³sªÅ¥Õ  & "" ªº¥Î·N¬O¬Æ»ò
¥i§_»¡©ú¤@¤U  §Ú¤£¤Ó²z¸Ñ ¦]¬°¦r¨å¤£¬O¨º»òª½Æ[

TOP

¦^´_ 4# °a¤ªºµ


    ¬Ý¤F­ã¤jª©¥D±`¨Ï¥Î¦¹¤èªk¡A¦r¨å¥[¤J""¦r¦ê¡A¥i¥H¥[§Ö°õ¦æ³t«×
    ¦p¦³¤£¹ï½Ð¦U¦ì«e½å«ü±Ð¡A·PÁ¡C

TOP

¦^´_ 1# abc9gad2016

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C

Sub tt2()
Set xD = CreateObject("Scripting.Dictionary")
Sheets("´M§ä").[B2:T2] = ""
T = Sheets("´M§ä").[A2]
xD(T & "") = ""
Arr = Range([¤u§@ªí1!T2], [¤u§@ªí1!A65536].End(3))
For i = 2 To UBound(Arr)
    N = xD(Arr(i, 1) & ""): If N = 0 Then GoTo 99
    For j = 2 To 19
        If Arr(i, j) <> "" Then: M = M + 1: Arr(1, M) = Arr(1, j)
    Next
99: Next
If M > 0 Then Sheets("´M§ä").[B2].Resize(1, M) = Arr
End Sub

TOP

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

TOP

¦^´_ 5# samwang

ÁÂÁÂsamwang«e½úªº¦^ÂÐ ¾Ç²ß¤F  ·PÁÂ

TOP

¦^´_ 7# hcm19522


    ·PÁ¤j¤j´£¨Ñ¨ç¼Æªº¸Ñªk ·PÁÂ!

TOP

¦^´_  abc9gad2016

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C

Sub tt2()
Set xD = CreateObject("Scripting.Dictionary" ...
samwang µoªí©ó 2021-1-23 10:31


S¤j¤£¦n·N«ä¡A´ú¸Õ«áµo²{·|¤Ö¦C¨ì¤@¶µ¡A¦p¹Ï
¦]¹ïµ{¦¡¸û¤£¼ô¤£²M·¡­n¦p¦ó­×§ï ÁÂÁÂ

TOP

        ÀR«ä¦Û¦b : ¨ü¤HÂI¤ô¤§®¦¡A¶··í´é¬u¥H³ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD