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

　暱稱: 厚臉皮的學生

帖子: 1446
主題: 40
精華: 0
積分: 1470
點名: 0
作業系統: Windows 7
軟體版本: Excel 2010 & 2016
閱讀權限: 50
性別: 男
來自: 台灣
註冊時間: 2020-7-15
最後登錄: 2024-10-21

2^#

發表於 2022-12-9 09:13 | 只看該作者

回復 1# tsuan

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

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

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

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

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

用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

版主 Rank: 7 Rank: 7 Rank: 7

准提部林

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-23

3^#

發表於 2022-12-9 20:09 | 只看該作者

用INDIRECT//

Xl0000297.rar (4.73 KB)

tsuan

中學生 Rank: 2

帖子: 41
主題: 10
精華: 0
積分: 163
點名: 216
作業系統: WIN2000
軟體版本: OFFICE 2003
閱讀權限: 20
性別: 男
註冊時間: 2010-12-28
最後登錄: 2024-11-22

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章 2011兔年全勤獎章

4^#

發表於 2022-12-10 15:21 | 只看該作者

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

　暱稱: 厚臉皮的學生

帖子: 1446
主題: 40
精華: 0
積分: 1470
點名: 0
作業系統: Windows 7
軟體版本: Excel 2010 & 2016
閱讀權限: 50
性別: 男
來自: 台灣
註冊時間: 2020-7-15
最後登錄: 2024-10-21

5^#

發表於 2022-12-12 08:29 | 只看該作者

回復 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

用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

　暱稱: 厚臉皮的學生

帖子: 1446
主題: 40
精華: 0
積分: 1470
點名: 0
作業系統: Windows 7
軟體版本: Excel 2010 & 2016
閱讀權限: 50
性別: 男
來自: 台灣
註冊時間: 2020-7-15
最後登錄: 2024-10-21

6^#

發表於 2022-12-12 09:30 | 只看該作者

本帖最後由 Andy2483 於 2022-12-12 09:35 編輯

回復 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值: 判斷式裡的範圍參照值

用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

　暱稱: 厚臉皮的學生

帖子: 1446
主題: 40
精華: 0
積分: 1470
點名: 0
作業系統: Windows 7
軟體版本: Excel 2010 & 2016
閱讀權限: 50
性別: 男
來自: 台灣
註冊時間: 2020-7-15
最後登錄: 2024-10-21

7^#

發表於 2022-12-14 13:16 | 只看該作者

回復 1# tsuan

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

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

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

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

用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流