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

[µo°Ý] INDIRECT«á¨ú­È

[µo°Ý] INDIRECT«á¨ú­È

¥»©«³Ì«á¥Ñ yc1031 ©ó 2020-5-28 10:38 ½s¿è

½Ð°Ý¤j®a

1. ¥ÎINDIRECT¨ú¤£¦P¤u§@ªíªº¸ê®Æ­n«ç»ò¿ì¨ì? ¥Ø«e¬d¨ìªº¥Î=INDIRECT($C$1&""&"B4"), ¦ý¤]µLªk±a¥X³f®Æ
2. ±a¤J¸ê®Æ«á, ¦A«ç»ò¥þ³¡¥H¨ú­Èªº¸ê®Æ±a¨ì ¸ê®Æ¶K¤J°Ï? §Ú¦³¸ÕµÛ¥Î f5 ¥h¨ú, ¦ý³£·|¨ú¨ìªÅ¥Õ®æ
    *±a¨ì¸ê®Æ¶K¤J°Ï®É, ­n¸õ¨ìªÅ¥Õ¦C¤~¯à¶K, §Ú¬O¥ÎRange("L4").End(xlDown).Offset(1, 0).Select

§Ú¤]¦³§â§Úªº°ÝÃD¦C¦bexcelÀɸÌ, ¦A½Ð¤j®aÀ°¦£, ·PÁÂ!

2020¦~_±b¤á¶i¥X°O¿ýTEST.rar (63.28 KB)

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-5-28 12:45 ½s¿è

¦^´_ 1# yc1031


²Ä¤G¨B, ¦bC3~G1000Äæ¦ì¸Ì±a¤J¤À§Oªº¤u§@ªí¸ê®Æ, ¥ÎINDIRECT¤£¦¨¥\

­n¥Î³æ¤Þ¸¹ ' §âSheet¦W¤l ¥]°_¨Ó ¦b¥[ Åå¹Ä¸¹ !

C3¡G=IF(INDIRECT("'"&$C$1&"'!"&ADDRESS(ROW()+1,COLUMN()-1))="","",INDIRECT("'"&$C$1&"'!"&ADDRESS(ROW()+1,COLUMN()-1)))¡@¡@

¥k©Ô¤U©Ô



²Ä¤T¨B, ¦b¶K¤J­È°Ï, §âC3~G1000ªº"­È", ¶K¤W¨ìJ3~P1000, ¶K¤J­È¤£¦¨¥\

Range("J3:P1000") = Range("A3:G1000").Value

¦pªG§A²Ä3¨B¶K¤Wªº­È¬O­n¤@ª½©¹¤U²Ö¥[ªº¶K¡A¦Ó¤£¬OÂл\­ì¥»¸ê®Æªº¸Ü

¨º²Ä2¨B¤]¤£«Øij¥Î¨ç¼Æ°µ¡A²Î¤@¥Î¥¨¶°°µ·|¤ñ¸û¦n

(¥u­nÀx¦s®æ¦³¨ç¼Æ¡A¤£½×¦³µL¼Æ­È¡A¹ï©ó¥¨¶° End ¤èªk¨Ó»¡¡A³£¬O¦³­È)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-5-28 13:16 ½s¿è

¦^´_ 2# n7822123


¨ú¤u§@ªí¦WºÙ°Ïªº¤½¦¡µLªk¦Û°Ê§ó¥¿, ¦³¨S¦³¿ìªk¥i¥H¦Û°Ê? (¦]¬°¤é«á¦³¥i¯à¤@ª½¼W¥[¤u§@ªí)

°õ¦æ«á¡AV3©¹¤Uªº¤u§@ªí¦WºÙ·|§ó·s (¦WºÙ¤¤¦³"#"¤~·|³Q¦C¤J)
  1. Sub §ó·s¤u§@ªí¦WºÙ()
  2. Dim ShAll
  3. For Each sh In Sheets
  4.   If InStr(sh.Name, "#") Then
  5.     ShAll = ShAll & "," & sh.Name
  6.   End If
  7. Next
  8. ShAll = Split(Mid(ShAll, 2), ",")
  9. [V3].Resize(UBound(ShAll) + 1) = Application.Transpose(ShAll)
  10. End Sub
½Æ»s¥N½X
¦pªG§A·Q­n·s¼W¤u§@ªíªº®É­Ô´N "¦Û°Ê°õ¦æ"

¥i¥H¦b¬¡­¶Ã¯¨Æ¥ó¤¤¡A©I¥s¤W­±ªº µ{§Ç
  1. Private Sub Workbook_NewSheet(ByVal Sh As Object)
  2.   §ó·s¤u§@ªí¦WºÙ
  3. End Sub
½Æ»s¥N½X
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 3# n7822123


    ¤Q¤À·PÁÂ,  ¥i¥H¦Û°Ê¨ú¤u§@ªí¦WºÙ¤F !

TOP

¦^´_ 2# n7822123


§A¦n,

ÁÂÁ§A¼·ªÅÀ°§Ú¦^µª°ÝÃD

²Ä¤T¨Bªº¨ú­È, ¬O¦]¬°§Ú¤£·|¥Îvba, ©Ò¥H§Ú¤~·Q¥Îindirect¥h±a¸ê®Æ«á, ¿ývba¨ú­È¶K¨ì"¸ê®Æ¶K¤J°Ï", ¤§«á¦A¿ývba¿z¿ï±Æ§Ç, §Ë¨ìÁ`ªí¥h
¦pªG¥i¥Hªº¸Ü, ¤]¬Oª½±µ¥Îvba °µ²Ä¤T¨B, ¤£¾å±o¬O§_¥i¥H±Ð§Ú«ç»ò°µ?
* §Ú¤é«á·|¦A·s¼W±b¤á

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-5-28 14:12 ½s¿è

¦^´_ 5# yc1031


ÁÂÁ§A¼·ªÅÀ°§Ú¦^µª°ÝÃD

²Ä¤T¨Bªº¨ú­È, ¬O¦]¬°§Ú¤£·|¥Îvba, ©Ò¥H§Ú¤~·Q¥Îindirect¥h±a¸ê®Æ«á, ¿ývba¨ú­È¶K¨ì"¸ê®Æ¶K¤J°Ï", ¤§«á¦A¿ývba¿z¿ï±Æ§Ç, §Ë¨ìÁ`ªí¥h
¦pªG¥i¥Hªº¸Ü, ¤]¬Oª½±µ¥Îvba °µ²Ä¤T¨B, ¤£¾å±o¬O§_¥i¥H±Ð§Ú«ç»ò°µ?
* §Ú¤é«á·|¦A·s¼W±b¤á

¥Î¨ç¼Æ¦A¥ÎVBAªº End¤èªk ·|¦³°ÝÃDªº End(xlUP) = «ö¦íCtrl + Áä½L¤è¦V½b ¦V¤W

§A¥i¥H°µ­Ó¤p¹êÅç¡A¶}¤@­Ó·s¤u§@ªí

¦b[B2]Àx¦s®æ ¶ñ¤J¤½¦¡ B2 =""  (ªÅ¸ê®Æ)

¤â°Ê½Æ»sB2Àx¦s®æ¡A¿ï¾Ü A8Àx¦s®æ ·Æ¹«¥kÁä > ¿ï¾Ü©Ê¶K¤W > ­È

µM«á¿ï¾Ü A15 Àx¦s®æ (A8¥H«á³£¦æ) «ö¦íCtrl + Áä½L¤è¦V½b ¦V¤W  Àx¦s®æ·|°±¯d¦bA8 ¤£¬O A1

ÁöµMA8 ¬Ý°_¨Ó¨S¦³¤H¥ô¦óªF¦è!  ³o¬OExcel ¤@ª½¦³ªº°ÝÃD ¡AUsedRange¤]¦³¦P¼Ë°ÝÃD

·Ç³Æ¤W¯Z¥h¤F¡A§A­nªº²Ä3¨B¤£Ãø¡A

¬Ý³o¸Ì¦³¨S¦³¤HÀ°§A¼g¡A¨S¦³ªº¸Ü§Ú¤W¯Z¦^¨Ó¦AÀ°§A

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

TOP

1) ©w¸q¦WºÙ
    =GET.WORKBOOK(1)&T(NOW())  ¥[³o­Ó¥i¥H¤u§@ªí·s¼W©Î§R°£¦Ó¦Û°Ê§ó·s
2) C1¤½¦¡
   =IF(A1="","",TRIM(RIGHT(SUBSTITUTE(LOOKUP(1,0/(RIGHT(QM)=A1),QM),"]",REPT(" ",99)),99)))
3) C3¤½¦¡/¥k©Ô¤U©Ô
   =INDEX(INDIRECT("'"&$C$1&"'!B:F"),ROW($A1)+3,COLUMN(A$1))&""
4) B3¤½¦¡/¤U©Ô
  =IF(C3="","",(N(B2)+1)^(C3=C2))

5) 刦KVBA
Sub 刦K()
Dim R, xE As Range
R = Application.Match(9E+307, [B:B])
If IsError(R) Then Exit Sub
Set xE = [J1].Cells(Rows.Count, 1).End(xlUp)(2)
With Range("A3:G" & R)
     xE.Resize(.Rows.Count, .Columns.Count) = .Value
End With
End Sub

¥HBÄæ¼Æ¦r¬°MATCH½d³ò, ¥Î9E+307§ä¨ì³Ì«á¤@µ§


====================================

TOP

¦^´_ 6# n7822123



  ·PÁ§A!¡@§Ú¤]¸Õ¤F´X¦¸¡A­ì¨Ó³æ¦ì®æ¸Ì¦³¤½¦¡ªº¡A¡@§Y¨ÏµL¸ê®Æ¡A³£·|³Q¢Ñ¢Ý¢Þ¢ç¨ì¡A©Ò¥H¥Î¢Ô¢´¡A¡@§Ú´N¿z¤£¥X¨Ó¤F¡C

TOP

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


·PÁ§A¸Ñµª§Ú©Ò¦³ªº°ÝÃD¡I

²Ä¤@¨B: SÄ榳·s¼W/§R´î±b¤á¤u§@ªí®É¥i¦Û°Ê§ó·sÀɦW  <----¤wOK
²Ä¤G¨B: ±a¤J¦U±b¤á©ú²Ó¨ìC~GÄæ¦ì <----¤wOK
²Ä¤T¨B: §âC~GÄæ¦ìªº­È¶K¤JJ~P ( ¤£Âл\­ì¥»ªº¸ê®Æ) <--¤wOK
* §Ú¸ÕµÛ¥Î¿ýVBAªº¤è¦¡§âUÄæ¦ìªº¥N½X¿é¤JA1, ¦A°õ¦æ"¶K¤J­ÈVBA", ¦ý³o¼Ë¥u¯à¤@µ§¤@µ§¸Õ¨ìZ,  ¦³¨S¦³¿ìªk¥i¥H¥ÎTÄæ¦ì¨Ó¿ë§O, ·N«ä¬O: §Ú¥u¦³A~Gªº±b¤á, ¨º´N¬OA1, ¥u¿é¤J¨ìG,  ¨ºH~Z´N¤£¥Î°µ¤F (¥i¤é«á­Y¬O·s¼W±b¤á´N¬O©¹¤UÄ~Äò)

2020¦~_±b¤á¶i¥X°O¿ýTEST.rar (66.56 KB)

TOP

¦^´_ 9# yc1031

¥Î¤£¨ì¤½¦¡,
¥u­n¤u§@ªí¦WºÙ³Ì«á¬°"#"¥[­^¤å¦r, §Y¦Û°Ê³°Äò§ì¥X¦³®Ä½d³ò¶K¤J­È:

Sub ¨Ì±b¤á¶K¤J­È()
Dim Sht As Worksheet, T$, R&, xE As Range
[Åçºâ!I3:P6000].ClearContents
For Each Sht In Sheets
    T = Right(Replace(UCase(Sht.Name), "#", "$"), 2)
    If Not T Like "$[A-Z]" Then GoTo 101
    R = Sht.Cells(Rows.Count, 2).End(xlUp).Row - 3
    If R <= 0 Then GoTo 101
    Set xE = [Åçºâ!I1].Cells(Rows.Count, 1).End(xlUp)
    xE(2, 3).Resize(R, 6) = Sht.[A4].Resize(R, 7).Value
    xE(2, 1).Resize(R) = UCase(Sht.Name)
    xE(2, 2).Resize(R) = Right(T, 1)
101: Next
End Sub


'=====================================

TOP

        ÀR«ä¦Û¦b : ¡i¬°µ½Ävª§¡j¤H¥Í­n¬°µ½Ävª§¡A¤À¬í¥²ª§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD