INDIRECTを使わず excelで複数シートの同じセルを抽出したい

excel2021を使用しています。
データベースから抽出したexcelデータはシート毎に入出金明細があり、それらすべてのシートの同じセルの数字だけ抽出したいです。
シート名が複雑かつシート数も100以上あり、INDIRECTを使うことが出来ません。
何か方法はありませんでしょうか。
シート名を一括で変更することができればINDIRECTを使用して欲しいデータだけ抽出することも出来ます。

①シート名は記号英数字漢字全て混ざっており複雑
②シート数は100以上ある
③全てのシートのＡ１にあるCD番号とＣ１にある残高を別シートにまとめたい

以上の条件で何か手段があれば教えて下さい。
色々試しましたが思いつきませんでした。

No.3 回答者： fujillin 回答日時： 2024/04/12 15:13

こんにちは

すこしトリッキーな方法になりますが・・

2021ということですので、もしかするとTEXTSPLIT関数が使えないかも知れませんけれど・・
Webの機能制限付き365で試したところ、以下で可能でした。


以下は、串刺し計算を利用して展開する方法です。
ただし、対象となるシートが連続している必要があります。
（串刺しなので、シート名ではなくタブが連続しているという意味です）

例えば、Sheet1～Sheet20が連続しているとして、別シートのA1セルに
=TEXTSPLIT(TEXTJOIN(CHAR(27),0,Sheet1:Sheet20!A1),,CHAR(27))
を入力すると、Sheet1とSheet20の間にあるシートのA1セルの値が、A列にスピルされます。
（両端にあるシート名を指定すれば、それに挟まれているシート全体が対象になります）

※ 2021が手元にはありませんので、TEXTSPLITが使えない場合に展開する方法がわかりません。
手元にある2019でも、
　TEXTJOIN(CHAR(27),0,Sheet1:Sheet20!A1)
までは計算可能ですので、うまく展開する方法がわかれば、2021でも可能ではないかと思います。

No.2 回答者： Qchan1962 回答日時： 2024/04/12 13:33

私の場合

参考程度で（参考にもならないかも知れません）
ユーザー定義関数
https://support.microsoft.com/ja-jp/office/excel …
https://valmore.work/excel-vba-function/

ファンクションコード
Public Function jOtherSHEET_CELLVALUE(cell As Range) As Variant()
Application.Volatile '複数シートの為不要
Dim callSheet As Worksheet
Set callSheet = ActiveSheet
Dim chkAddress As String
chkAddress = cell.Address(0, 0)
Dim ws As Worksheet
Dim Ans(), i As Long
For Each ws In Worksheets
ReDim Preserve Ans(i)
If callSheet.Name <> ws.Name Then
Ans(i) = ws.Range(chkAddress).Value
i = i + 1
End If
Next
Application.DisplayAlerts = False '循環参照対策
jOtherSHEET_CELLVALUE = Application.Transpose(Ans)
Application.DisplayAlerts = True
End Function

別シートの任意のセル
（参照先セルと同じセルアドレスでは無いのが望ましい）

A2セルに　=jOtherSHEET_CELLVALUE(A1)
B2セルに　=jOtherSHEET_CELLVALUE(C1)

対象セルは単セルのみ
関数入力セルの下には十分な空きセルが必要
参照先セルの値を変えても再計算されませんので変更時は再計算が必要

関数名は予約語などと被らない様に変更可能
頭文字のjはマイルールです　=jと入力時に選び易いため

No.1 回答者： d-q-t-p 回答日時： 2024/04/12 13:14

> 何か方法はありませんでしょうか。

マクロを作るか別ブックから Power Queryを使って読み込むかです。

> データベースから抽出したexcelデータは
データベースがあるならそっちから抽出してマージしたほうが早いです。
シートで分けてしまったデータを読み込み直すなんてのは時間の無駄です。