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

abc9gad2016

·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)

·j´M½d¨Ò
¤U¸ü¦¸¼Æ: 33

2021-1-22 16:58

°a¤ªºµ

¦^´_ 1# abc9gad2016
³o¬O°Ñ¦Ò  jcchiang ¤j¤jªº¼gªk ¦³ªÅ¬Ý¬Ý¬O¤£¬O³o¼Ë ·PÁÂ

1. Public Sub ´M§ä¬Û¹ïÄæ¦ì½m²ß()
   
2. 
   
3.     Arr = [¤u§@ªí1!A1].CurrentRegion
   
4.     Set xD = CreateObject("Scripting.Dictionary")
   
5.    
   
6.     For Y = 1 To UBound(Arr, 2)
   
7.         xD(Arr(2, Y)) = Y
   
8.     Next Y
   
9.    
   
10.     For X = 3 To UBound(Arr, 1)
    
11.         For Y = 2 To UBound(Arr, 2)
    
12.             If Arr(X, 1) = [´M§ä!A2] And Arr(X, Y) <> "" Then
    
13.                 E = E + 1
    
14.                 Sheets(2).Cells(2, 1 + E) = Arr(2, xD(Arr(2, Y)))
    
15.             End If
    
16.         Next Y
    
17.     Next X
    
18. 
    
19. End Sub

½Æ»s¥N½X

samwang

¦^´_ 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

°a¤ªºµ

¦^´_ 3# samwang


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

samwang

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


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

samwang

¦^´_ 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

hcm19522

https://blog.xuite.net/hcm19522/twblog/589575552

°a¤ªºµ

¦^´_ 5# samwang

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

abc9gad2016

¦^´_ 7# hcm19522


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

abc9gad2016

¦^´_  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¹Ï

¤U¸ü (60.13 KB)
pic

2021-1-25 16:19

¦]¹ïµ{¦¡¸û¤£¼ô¤£²M·¡­n¦p¦ó­×§ï ÁÂÁÂ