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

[µo°Ý] ½Ð°Ý¦p¦ó§ìÀx¦s®æ¸ê®Æ·í¦¨¤À­¶¦WºÙ§ì¸ê®Æ

[µo°Ý] ½Ð°Ý¦p¦ó§ìÀx¦s®æ¸ê®Æ·í¦¨¤À­¶¦WºÙ§ì¸ê®Æ

¦U¦ì¥ý¶i:
¦]­n°µ¤ÀªR¸ê®Æ¡A§Æ±æ¥i¥H³z¹L­×§ïÀx¦s®æ¸ê®Æ¡A·í¤À­¶¦WºÙ¡A¦A¥h§ì¤£¦P¤À­¶¸ê®Æ¡A¦pªþ¥ó¸ê®Æ¡A§Æ±æ­×§ïA2¸ê®Æ«á¡A¨ÌA2¸ê®Æ¥h§ì¤£¦P¤À­¶¸ê®Æ Test.rar (7.82 KB)

¦^´_ 1# tsuan


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹¥DÃD½m²ß°}¦C»P¦r¨å,½Ð«e½ú¸Õ¸Õ¬Ý

¦b[A2]©Î[B1] ¿é¤JÃöÁä¦r 2021 ªºµ²ªG:


¦b[A2]©Î[B1] ¿é¤JÃöÁä¦r BB ªºµ²ªG:


¦b[A2]©Î[B1] ¿é¤JÃöÁä¦r 2023 ªºµ²ªG:


1.¦b©ú²Óªí¤u§@ªí¼Ò²Õ´Ó¤J¥H¤Uµ{¦¡½X:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
   If InStr("$A$2$B$1", .Address) Then
      ÃöÁä¦r = .Value
      Call ¥HÃöÁä¦r¿ëÃѤu§@ªí¦W_¶×¤Jªí¤º¸ê®Æ
      
   End If
End With
End Sub

2.±N¤U¦Cµ{¦¡½X©ñ¤J Module1:
Option Explicit
Public ÃöÁä¦r$
Sub ¥HÃöÁä¦r¿ëÃѤu§@ªí¦W_¶×¤Jªí¤º¸ê®Æ()
Dim Awh As Worksheet, xA As Range, Y, Z
Set Y = CreateObject("Scripting.Dictionary")
Set Awh = Sheets("©ú²Óªí")
For Each Z In Worksheets
   If Not Z Is Awh Then
      Set Y(Z.Name) = Range(Z.[A1], Z.UsedRange).Offset(2)
      Else
         Z.Range(Z.[A1], Z.UsedRange).Offset(3).Clear
   End If
Next
If Not Y.Exists(ÃöÁä¦r) Then
   MsgBox "¨S¦³ " & ÃöÁä¦r & " ¤u§@ªí!"
   GoTo 111
   Else
      Y(ÃöÁä¦r).Copy Awh.[A4]
End If

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

TOP

¥ÎINDIRECT//
Xl0000297.rar (4.73 KB)

TOP

·PÁ¨â¦ì¥ý¶i¡A¨ó§U¸Ñ¨M°ÝÃD¤F

TOP

¦^´_ 4# tsuan


    ÁÂÁ«e½ú¦^´_
¤µ¤Ñ¦A½Æ²ß¤@¦¸,¥H¤U¬O¤ß±oµù¸Ñ¨Ñ°Ñ¦Ò

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
'¡ô¥H¤U¬OÃö©óIJµoªºµ{§Ç
   If InStr("$A$2$B$1", .Address) Then
   '¡ô¦pªG "$A$2$B$1" ¦r¦ê¸Ì ¦³¥]§tIJµoÀx¦s®æªº¦ì§}(¦r¦ê)
      ÃöÁä¦r = .Value
      '¡ô¥OÃöÁä¦r³o¥þ°ìÅܼƬO³o IJµo®æªº­È
      Call ¥HÃöÁä¦r¿ëÃѤu§@ªí¦W_¶×¤Jªí¤º¸ê®Æ
      '¡ô°õ¦æ ¥HÃöÁä¦r¿ëÃѤu§@ªí¦W_¶×¤Jªí¤º¸ê®Æ(°Æµ{¦¡)
      
   End If
End With
End Sub

Option Explicit
Public ÃöÁä¦r$
'¡ô«Å§i(ÃöÁä¦r)³oÅܼƬO¦r¦ê«¬ªº¥þ°ìÅܼÆ
Sub ¥HÃöÁä¦r¿ëÃѤu§@ªí¦W_¶×¤Jªí¤º¸ê®Æ()
Dim Awh As Worksheet, xA As Range, Y, Z
'¡ô«Å§i(Awh)¬O¤u§@ªí,(xA)¬OÀx¦s®æ,(Y,Z)¬O³q¥Î«¬ÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OY¬O¦r¨å
Set Awh = Sheets("©ú²Óªí")
'¡ô¥OAwh ¬O"©ú²Óªí" ¤u§@ªí
For Each Z In Worksheets
'¡ô³]¶¶°j°é!X¥OZ¬O¬¡­¶Ã¯¸Ì¤u§@ªí¤§¤@,±q«e¶]¨ì«á
   If Not Z Is Awh Then
   '¡ô¦pªG°j°é¤u§@ªí¤£¬O "©ú²Óªí" ¤u§@ªí??
      Set Y(Z.Name) = Range(Z.[A1], Z.UsedRange).Offset(2)
      '¡ô¥O°j°é¤u§@ªí¦W¦r·íkey,Item¬O°j°é¤u§@ªí¸Ì[A1]¨ì °j°é¤u§@ªí¦³¨Ï¥ÎÀx¦s®æ,
      '¯à¥]§t¤W­zªº³Ì¤p¤è¥¿Àx¦s®æ½d³ò,¦A©¹¤U°¾²¾2 ¦Cªº Àx¦s®æ½d³ò

      Else
         Z.Range(Z.[A1], Z.UsedRange).Offset(3).Clear
         '¡ô§_«h¯à¥]§t("©ú²Óªí" ¤u§@ªí)[A1]¨ì¦³¨Ï¥ÎÀx¦s®æªº ³Ì¤p¤è¥¿Àx¦s®æ½d³ò,
         '¦A©¹¤U°¾²¾3 ¦Cªº Àx¦s®æ½d³ò ²M°£

   End If
Next
If Not Y.Exists(ÃöÁä¦r) Then
'¡ô¦pªGY¦r¨å¸Ìªºkey¨S¦³ ÃöÁä¦r³oÅܼƪº¦r¦ê
   MsgBox "¨S¦³ " & ÃöÁä¦r & " ¤u§@ªí!"
   '¡ô¸õ¥X´£¥Üµ¡
   GoTo 111
   '¡ô¸õ¨ì 111¦ì¸mÄ~Äò°õ¦æ
   Else
      Y(ÃöÁä¦r).Copy Awh.[A4]
      '¡ô§_«h´N¥H ÃöÁä¦r³oÅܼƪº¦r¦ê·íkey!
      '§â¹ïÀ³ªºitemÀx¦s®æ¶°½Æ»s¨ì "©ú²Óªí" ¤u§@ªíªº[A4]

End If

111
Set Y = Nothing
'¡ôÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-12-12 09:35 ½s¿è

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


    ÁÂÁ«e½ú«ü¾É
¤ß±o¦p¤U,½Ð«e½ú¦A«ü¾É

1.[©ú²Óªí!A4]=IF(INDEX(INDIRECT("'"&$B$1&"'!A:D"),ROW(A3),COLUMN(A$1))="","",INDEX(INDIRECT("'"&$B$1&"'!A:D"),ROW(A3),COLUMN(A$1)))
=IF(§PÂ_¦¡,True­È,False­È)

1.1.§PÂ_¦¡:  'INDEX(INDIRECT("'"&$B$1&"'!A:D"),ROW(A3),COLUMN(A$1))=""  :½d³ò°Ñ·Ó­È¦pªG¬OªÅ®æ??
INDEX ¨ç¼Æ: INDEX ¨ç¼Æ·|¶Ç¦^ªí®æ©Î½d³ò¤ºªº¬Y­Ó­È©Î­Èªº°Ñ·Ó¡C ¶Ç¦^«ü©wÀx¦s®æ©ÎÀx¦s®æ°}¦Cªº­È
INDIRECT("'"&$B$1&"'!A:D")
INDIRECT()
¶Ç¦^¤å¦r¦ê©Ò«ü©wªº°Ñ·Ó¡C ¸Ó°Ñ·Ó·|¥ß¨è¶i¦æ­pºâ¨ÃÅã¥Ü¨ä¤º®e¡C INDIRECT ³q±`¬O±z·Q¦b¤½¦¡¤¤ÅܧóÀx¦s®æ°Ñ·Ó«o¤£·Q§ïÅܤ½¦¡¥»¨­®É¨Ï¥Î¡C
'"&$B$1&"' : ¬O­n«ü©w¤u§@ªí¦W¦r! ¥þ©T©w®æ[B1]Àx¦s®æ¸Ìªº­ÈÂà¤Æ¬°¦r¦ê¨Ã¥H³æ¤Þ¸¹ ' ¥]Âа_¨Ó(ÁקKªÅ®æ¶¡Â_¦r¦ê¾É­Pªº»~§P)
"'"&$B$1&"'!A:D" :«ü©w¤u§@ªíªºAÄæ¨ìDÄæ½d³ò
ROW(A3),COLUMN(A$1) :¦Û¨­©Ò¦bªº¦C¸¹,¦C¸¹©T©wªºÄ渹

1.2.True­È: ""

1.3.False­È: §PÂ_¦¡¸Ìªº½d³ò°Ñ·Ó­È
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 1# tsuan


    ¦A¦¸ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹©«½m²ß ¦Û­q¸q¨ç¼Æ,¾Ç²ß´ú¸Õ¨ì«Ü¦hª¾ÃÑ,¥H¤U¾Ç²ßµ²ªG¨Ñ°Ñ¦Ò

[A4]Àx¦s®æ¿é¤J " =§ì¸ê®Æ($B$1,ROW()-1,COLUMN()) "


½Æ»s[A4]Àx¦s®æ,¿ï¾Ü©Ê¶K¤W ¤½¦¡


¥H¤Uµ{¦¡½X©ñ¨ìModule1¸Ì

Function §ì¸ê®Æ(xA As String, R As Long, C As Long) As Variant
Dim Y
Application.Volatile
Set Y = Range(Sheets(xA).[A1], Sheets(xA).UsedRange)
§ì¸ê®Æ = IIf(Y.Item(R, C) <> 0, Y.Item(R, C), "")
End Function
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ­n¥Î¤ß¡A¤£­n¾Þ¤ß¡B·Ð¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD