n7822123

¦^´_ 10# mycmyc


·Ç¤j¼gªº§ó²³æ¤@¨Ç!

mycmyc

¦^´_ 10# mycmyc


    ¤£¦n·N«ä   ¦A½Ð°Ý ¨Ì¤é´ÁÄæ ¨ú¦^ ·í¤é¤£¬°0ªº¤u§@¤º®e   ¦p¬õ¦â¤§¤å¦r  ÁÂÁ±z
¦A½Ð±Ð-1104.rar (10.99 KB)

¤U¸ü¦¸¼Æ: 5

2018-11-4 13:18

n7822123

¦^´_ 12# mycmyc

¨Ï¥Îªº¨ç¼Æ³£¨º¨Ç¦Ó¤w¡A§A¥i¥H¥ý¦Û¤v¬ã¨s¬Ý¬Ý¡A
¯uªº¼g¤£¥X¨Ó¦A½Ð§O¤HÀ°¦£¡A§A¤]¥i¥H¾Ç¨ìªF¦è¡A
¦pªG·Q³£¤£¥h·Q¡A´Nµo°Ýªº¸Ü¡A¥H«á¹J¨ìÃþ¦üªº°ÝÃD¡AÁÙ¬O¥u¯à¤@ª½½Ð§O¤HÀ°¦£

³o¦¸ªº¤ñ¸û½ÆÂø¤@ÂIÂI¡A´N¥ýÀ°§A¤F¡A¤£¹L§Ú¨S¦³§â¦r¦ê¦A±µ°_¨Ó(§A¥i¥H¦b§OªºÀx¦s®æ°µ¦ê±µ)
§Ú¼gªº¦³¨Ç½ÆÂø¡AÀ³¸Ó¦³§ó¦n§ó²µuªº¼gªk¡AÃi±o·Q¤F¡A¦³½Ð¤j¯«½ç±Ð!

¿ï¨úK6:Q6¡A«á¿é¤J
=IFERROR(INDEX($A$1:$H$1,,SMALL(IF(VLOOKUP($J6,$A$6:$H$30,COLUMN($B$2:$H$2)) <> "",COLUMN($B$2:$H$2),""),COLUMN($A$1:$G$1))),"")

¥ý«ö¦íCtrl+Shift ¦A«ö Enter «á©ñ¶}
¦A©¹¤U©Ô

¦A½Ð±Ð-1104.rar (11.37 KB)

¤U¸ü¦¸¼Æ: 4

2018-11-5 01:04

­ã´£³¡ªL

¦^´_ 12# mycmyc


¦Û­q¨ç¼Æ:
Function GetItem(xA As Range, xB As Range) As String
Dim xR As Range, N%, TT$
For Each xR In xB
    N = N + 1
    If Val(xR) > 0 Then TT = TT & "¡B" & xA(N)
Next
GetItem = Mid(TT, 2)
End Function

K6/¤½¦¡:=GetItem(B$1:H$1,INDEX(B:H,MATCH(J6,A:A,),))  ¤U©Ô

mycmyc

¦^´_ 13# n7822123
¹ï¤£°_  ½T¹ê¨S¾Ç¦n
­ì¥»·Q¥Î ¦Û­q¤½¦¡ §â¤é´Á¤U¤u¶µ °µ¤@°}¦C  ¦b·j´M¤£µ¥©ó"" ¦C¥X
³o¼ËÅܦ¨¨C¤@­Ó¤é´Á ¦h»Ý­n¤@­Ó¦Û­q¤½¦¡    ¦]¦¹¨S¿ìªk
¹ï¤£°_ ³Â·Ð§A¤F  ÁÂÁ§A   
À³¸Ó¬O§Ú ¨S§â¨C­Ó¨ç¼Æ ¿Ä·|³e³q    ¤~¥d¦í   §Ú¦A¬ã¨s¤@¤U  ±`¥Î¨º¨Ç¨ç¼Æ
¯uªº·P®¦§A  ÁÂÁÂ

mycmyc

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


ÁÂÁ§A  §Ú¹êÅç½T©w¥i¥H
¦sÀɬO§_¨Ì©w­n¬°.xlsm  ¥¨¶°¼Ò¦¡
·PÁ¤G¦ì«e½ú
¥»¤H½T¹êÀ³¸Ó§ó¥Î¤ß¾Ç²ß  ÁÂÁ³ᠠ¤£¦n·N«ä ·P®¦ ¦A½Ð±Ð-1104-1.rar (16.69 KB)

¤U¸ü¦¸¼Æ: 3

2018-11-5 21:31

n7822123

¦^´_ 15# mycmyc


¤Ì......¨S¨Æ¡A§Ú¨S¦³­n°á§Aªº·N«ä¡A

¨S¦³¤H¤@¥X¥Í´N¤°»ò³£·|¡A¤£¥Î¸ò§Ú»¡¹ï¤£°_

¦]¬°§A¦b¦P¤@­ÓCaseµo°ÝªºÀW²v¦³ÂI°ª¡A©Ò¥H§Ú»~¥H¬°§A·Q³£¨S·Q¡A´N¤W¨Ó­nµª®×¡A³oÂI§Ú»~¸Ñ§A¤F

¦³¦Û¤v·Q¹L´NOK¤F! ¡A·í§A¥d¦íªº®É¡A±o¨ìµª®×«á¡A¦L¶H·|§ó²`¨è¡A¤U¦¸´N¤£·|¥d¦b¦P¤@­Ó¦a¤è

¦pªG¨S¦Û¤v·Q¹L¡Aª½±µ±o¨ìµª®×¡A¹ï©óµª®×¬O¨S¦³·Pıªº¡A¤@¼Ò¤@¼ËªºªF¦è¡A´«­ÓÄd¦C´N¥i¯à¥d¦í¤F

­n¦s¥¨¶°ªºÀɦW¥i¥H¬O.xls(ª©) ©ÎªÌ .xlsm(·sª©) ¡A

.xlsx ¤£·|¦sVBA¥¨¶°¡A¦sÀÉ«áÃö±¼¦A¶}¡A¥¨¶°´N¨S¦³¤F

­ã´£³¡ªL

¦^´_ 16# mycmyc

­Y¸ê®Æ¦h, ª½±µ¥Îvba¨ú¥X¬ÛÃö¸ê®Æ:
Sub GetDateItem()
Dim Arr, i&, j%, N&, T$
[¤u§@ªí2!A:B].ClearContents
[¤u§@ªí2!A1:B1] = Array("¤é´Á", "¬I¤u¶µ¥Ø")
Arr = Range([¤u§@ªí1!H1], [¤u§@ªí1!A65536].End(xlUp))
For i = 6 To UBound(Arr)
    If Not IsDate(Arr(i, 1)) Then GoTo 101
    For j = 2 To UBound(Arr, 2)
        If Val(Arr(i, j)) <> 0 Then T = T & "¡B" & Arr(1, j)
    Next j
    If T = "" Then GoTo 101
    N = N + 1
    Arr(N, 1) = Arr(i, 1):  Arr(N, 2) = Mid(T, 2): T = ""
101: Next i
If N > 0 Then [¤u§@ªí2!A2:B2].Resize(N) = Arr
Application.Goto [¤u§@ªí2!A1]
End Sub

Xl0000244(¤é´Á-¬I¤u¶µ¥Ø).rar (13.92 KB)

¤U¸ü¦¸¼Æ: 5

2018-11-6 10:11

mycmyc

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


    ÁÂÁ·Ǥj
§Ú¬ã¨s¤@¤U ÁÂÁ§A ¼ö¤ß«ü¾É

GBKEE

¦^´_ 19# mycmyc
¤]¥i¥Î Application.Match¨ç¼Æ

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Rng(1 To 2) As Range, e As Range, M As Variant, d As Object
   
4.      With Sheets("¤u§@ªí2")
   
5.         .UsedRange.Clear
   
6.         .[a1:b1] = Array("¤é´Á", "¬I¤u¶µ¥Ø")
   
7.     End With
   
8.     With Sheets("¤u§@ªí1")
   
9.         Set Rng(1) = .Range("B6", "B" & .[A6].End(xlDown).Row).Resize(, .[A1].End(xlToRight).Column - 1).SpecialCells(xlCellTypeConstants, 1)
   
10.         ' ***   .SpecialCells(xlCellTypeConstants, 1)   ¬O¼Æ¦rªºÀx¦s®æ  ***
    
11.         For Each e In Rng(1)
    
12.             M = Application.Match(.Cells(e.Row, 1).Text, Sheets("¤u§@ªí2").Columns(1), 0)
    
13.             If IsError(M) Then                             'Match¤£¨ì                 '
    
14.                 Set Rng(2) = Sheets("¤u§@ªí2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    
15.                 Rng(2) = .Cells(e.Row, 1).Text             'AÄ檺¤é´Á
    
16.                 Rng(2).Cells(1, 2) = .Cells(1, e.Column)   '²Ä¤@¦Cªº¬I¤u¶µ¥Ø
    
17.             Else
    
18.                 Set Rng(2) = Sheets("¤u§@ªí2").Range("A" & M)   'Match¨ì ªº¦C¸¹
    
19.                 Rng(2).Cells(1, 2) = Rng(2).Cells(1, 2) & "¡B" & .Cells(1, e.Column)
    
20.             End If
    
21.         Next
    
22.     End With
    
23. End Sub

½Æ»s¥N½X