[發問] 請問如何抓儲存格資料當成分頁名稱抓資料

tsuan

各位先進:
因要做分析資料，希望可以透過修改儲存格資料，當分頁名稱，再去抓不同分頁資料，如附件資料，希望修改A2資料後，依A2資料去抓不同分頁資料 Test.rar (7.82 KB)

下載次數: 7

2022-12-8 15:53

Andy2483

回復 1# tsuan


    謝謝前輩發表此主題與範例
後學藉此主題練習陣列與字典,請前輩試試看

在[A2]或[B1] 輸入關鍵字 2021 的結果:

下載 (13.49 KB)

2022-12-9 09:06

在[A2]或[B1] 輸入關鍵字 BB 的結果:

下載 (13.3 KB)

2022-12-9 09:07

在[A2]或[B1] 輸入關鍵字 2023 的結果:

下載 (13.75 KB)

2022-12-9 09:07

1.在明細表工作表模組植入以下程式碼:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
   If InStr("$A$2$B$1", .Address) Then
      關鍵字 = .Value
      Call 以關鍵字辨識工作表名_匯入表內資料
      
   End If
End With
End Sub

2.將下列程式碼放入 Module1:
Option Explicit
Public 關鍵字$
Sub 以關鍵字辨識工作表名_匯入表內資料()
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(關鍵字) Then
   MsgBox "沒有 " & 關鍵字 & " 工作表!"
   GoTo 111
   Else
      Y(關鍵字).Copy Awh.[A4]
End If

111
Set Y = Nothing
End Sub

准提部林

用INDIRECT//
Xl0000297.rar (4.73 KB)

下載次數: 11

2022-12-9 20:09

tsuan

感謝兩位先進，協助解決問題了

Andy2483

回復 4# tsuan


    謝謝前輩回復
今天再複習一次,以下是心得註解供參考

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
'↑以下是關於觸發的程序
   If InStr("$A$2$B$1", .Address) Then
   '↑如果 "$A$2$B$1" 字串裡 有包含觸發儲存格的位址(字串)
      關鍵字 = .Value
      '↑令關鍵字這全域變數是這 觸發格的值
      Call 以關鍵字辨識工作表名_匯入表內資料
      '↑執行 以關鍵字辨識工作表名_匯入表內資料(副程式)
      
   End If
End With
End Sub

Option Explicit
Public 關鍵字$
'↑宣告(關鍵字)這變數是字串型的全域變數
Sub 以關鍵字辨識工作表名_匯入表內資料()
Dim Awh As Worksheet, xA As Range, Y, Z
'↑宣告(Awh)是工作表,(xA)是儲存格,(Y,Z)是通用型變數
Set Y = CreateObject("Scripting.Dictionary")
'↑令Y是字典
Set Awh = Sheets("明細表")
'↑令Awh 是"明細表" 工作表
For Each Z In Worksheets
'↑設順迴圈!X令Z是活頁簿裡工作表之一,從前跑到後
   If Not Z Is Awh Then
   '↑如果迴圈工作表不是 "明細表" 工作表??
      Set Y(Z.Name) = Range(Z.[A1], Z.UsedRange).Offset(2)
      '↑令迴圈工作表名字當key,Item是迴圈工作表裡[A1]到 迴圈工作表有使用儲存格,
      '能包含上述的最小方正儲存格範圍,再往下偏移2 列的 儲存格範圍
      Else
         Z.Range(Z.[A1], Z.UsedRange).Offset(3).Clear
         '↑否則能包含("明細表" 工作表)[A1]到有使用儲存格的 最小方正儲存格範圍,
         '再往下偏移3 列的 儲存格範圍 清除
   End If
Next
If Not Y.Exists(關鍵字) Then
'↑如果Y字典裡的key沒有 關鍵字這變數的字串
   MsgBox "沒有 " & 關鍵字 & " 工作表!"
   '↑跳出提示窗
   GoTo 111
   '↑跳到 111位置繼續執行
   Else
      Y(關鍵字).Copy Awh.[A4]
      '↑否則就以 關鍵字這變數的字串當key!
      '把對應的item儲存格集複製到 "明細表" 工作表的[A4]
End If

111
Set Y = Nothing
'↑釋放變數
End Sub

Andy2483

回復 3# 准提部林


    謝謝前輩指導
心得如下,請前輩再指導

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(判斷式,True值,False值)

1.1.判斷式:  'INDEX(INDIRECT("'"&$B$1&"'!A:D"),ROW(A3),COLUMN(A$1))=""  :範圍參照值如果是空格??
INDEX 函數: INDEX 函數會傳回表格或範圍內的某個值或值的參照。 傳回指定儲存格或儲存格陣列的值
INDIRECT("'"&$B$1&"'!A:D")
INDIRECT()
傳回文字串所指定的參照。 該參照會立刻進行計算並顯示其內容。 INDIRECT 通常是您想在公式中變更儲存格參照卻不想改變公式本身時使用。
'"&$B$1&"' : 是要指定工作表名字! 全固定格[B1]儲存格裡的值轉化為字串並以單引號 ' 包覆起來(避免空格間斷字串導致的誤判)
"'"&$B$1&"'!A:D" :指定工作表的A欄到D欄範圍
ROW(A3),COLUMN(A$1) :自身所在的列號,列號固定的欄號

1.2.True值: ""

1.3.False值: 判斷式裡的範圍參照值

Andy2483

回復 1# tsuan


    再次謝謝前輩發表此主題與範例
後學藉此帖練習 自訂義函數,學習測試到很多知識,以下學習結果供參考

[A4]儲存格輸入 " =抓資料($B$1,ROW()-1,COLUMN()) "

下載 (35.66 KB)

2022-12-14 13:10

複製[A4]儲存格,選擇性貼上 公式

下載 (70.62 KB)

2022-12-14 13:11

以下程式碼放到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