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

½Ð±Ð·j´M¦WºÙ¡@§ä¥X¤é´Á¡@ÁÂÁÂ

¦^´_ 10# mycmyc


·Ç¤j¼gªº§ó²³æ¤@¨Ç!
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 10# mycmyc


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

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2018-11-5 01:14 ½s¿è

¦^´_ 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)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

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

TOP

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

TOP

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

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2018-11-6 01:28 ½s¿è

¦^´_ 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
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

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

TOP

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


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

TOP

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¡i¬O§_µo´§¤F¨}¯à¡H¡j¤H¶¡¹Ø©R¦]¬°µu¼È¡A¤~§óÅã±o¬Ã¶Q¡CÃø±o¨Ó¤@½ë¤H¶¡¡AÀ³°Ý¬O§_¬°¤H¶¡µo´§¤F¦Û¤vªº¨}¯à¡A¦Ó¤£­n¤@¨ý¨Dªø¹Ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD