excel2021を使用しています。
データベースから抽出したexcelデータはシート毎に入出金明細があり、それらすべてのシートの同じセルの数字だけ抽出したいです。
シート名が複雑かつシート数も100以上あり、INDIRECTを使うことが出来ません。
何か方法はありませんでしょうか。
シート名を一括で変更することができればINDIRECTを使用して欲しいデータだけ抽出することも出来ます。
①シート名は記号英数字漢字全て混ざっており複雑
②シート数は100以上ある
③全てのシートのA1にあるCD番号とC1にある残高を別シートにまとめたい
以上の条件で何か手段があれば教えて下さい。
色々試しましたが思いつきませんでした。
A 回答 (3件)
- 最新から表示
- 回答順に表示
No.3
- 回答日時:
こんにちは
すこしトリッキーな方法になりますが・・
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
- 回答日時:
私の場合
参考程度で(参考にもならないかも知れません)
ユーザー定義関数
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
- 回答日時:
> 何か方法はありませんでしょうか。
マクロを作るか別ブックから Power Queryを使って読み込むかです。
> データベースから抽出したexcelデータは
データベースがあるならそっちから抽出してマージしたほうが早いです。
シートで分けてしまったデータを読み込み直すなんてのは時間の無駄です。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 複数セルデータを別シートの単一セルにコピーしたい。(詳細をご参照ください) 1 2022/12/14 15:08
- Excel(エクセル) Excelマクロ 差分抽出の方法が知りたいです。 2 2023/03/07 13:25
- Excel(エクセル) マクロか関数で処理したいのですが、教えて頂けませんか。 8 2022/10/31 15:18
- Excel(エクセル) シート参照を含む数式を連続コピー 3 2022/12/10 11:42
- Excel(エクセル) エクセル 別シートの各セルそれぞれの比率を計算したい 4 2023/08/05 15:20
- Excel(エクセル) エクセルの数式について ブック内の別シートの値の含まれたセルの個数を集計したい 全シート一覧のシート 1 2022/07/21 19:28
- Visual Basic(VBA) Excel VBA 最終行を取得しVlookup関数をコピーする方法をコーディングで教えてください。 3 2023/05/11 13:14
- Excel(エクセル) Excel>マクロ>特定のセルで同じ情報が登録されている行を1行にまとめたい(文字連結) 6 2023/01/05 16:30
- Excel(エクセル) 【Excel関数】値が合致するセルの隣のセルを表示させたい 8 2022/10/12 17:44
- Visual Basic(VBA) 複数のシートを一つのシートにまとめたい 左から3番目以降のシートのE列のセルに⚪︎が入っている行を抽 2 2022/09/18 18:42
このQ&Aを見た人はこんなQ&Aも見ています
-
「環境が人を育てる」って本当?環境によって人格や生き方は本当に変わるのか
環境が人生に与える影響は実際どれほどのものなのか、専門家の田宮由美さんに伺った。
-
[関数得意な方]教えて下さい・・[困ってます]
Excel(エクセル)
-
[関数について]わかる方教えてください
Excel(エクセル)
-
式の説明をお願いします。
Excel(エクセル)
-
-
4
Excelで写真のような表を作った時、毎月鍵の所有者が変わります。所有者が毎月変わるため表が右に伸び
Excel(エクセル)
-
5
Excel初心者です。 詳しい方、効率の良いやり方を教えてください。 職場で、抽出したデータの表を見
Excel(エクセル)
-
6
エクセル関数に詳しい方教えてください
Excel(エクセル)
-
7
エクセル関数に詳しい方、教えてください(テキスト関数と条件付書式)
Excel(エクセル)
-
8
Excel初心者です。 詳しい方、効率の良いやり方を教えてください。 職場で、抽出したデータの表を見
Excel(エクセル)
-
9
[オートフィルタ]の適用範囲の確認は可能か?
Excel(エクセル)
-
10
自動入力で今月はMONTH(TODAY())で良いのですが、翌月はどのようにすれば良いでしょうか。
Excel(エクセル)
-
11
スマートな関数を教えて下さい。
Excel(エクセル)
-
12
エクセルの表示について
Excel(エクセル)
-
13
エクセルに詳しい方教えて下さい! 以下の画像のデータ入力表の内容を運行日報平日に反映させたいです。
Excel(エクセル)
-
14
Excelで全角を半角にしたいのですが、
Excel(エクセル)
-
15
EXACT関数とIF関数の組み合わせについて
Excel(エクセル)
-
16
エクセル2013で月間勤務表から週間勤務表へ自動転記する方法と関数の仕組みを教えて下さい。
Excel(エクセル)
-
17
Excelでの時間帯の入力
Excel(エクセル)
-
18
エクセルの行の抽出について質問です 部署、人名、コメントと3列の表があります リンゴ出荷担当部署とそ
Excel(エクセル)
-
19
Excelにてある膨大なデータを管理しています。 そこで、特定の市町村にのみ色を付けたいです。 Ex
Excel(エクセル)
-
20
エクセルで日付の入ったセルの月別の個数が知りたい
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
日付が未入力の際はゼロか、空...
-
Excelの「0」だけ非表示、小数...
-
エクセルで1月0日と表示される!!
-
エクセルで条件に一致したセル...
-
Excelで複数シートの選択セルを...
-
Rangeメソッドは失敗しました。...
-
シート参照で変数を使いたい(EX...
-
エクセルで20万行あるシート...
-
(Excel)あるセルに文字を入力...
-
別シートのセルを絶対参照にする
-
Excelのファイル容量が減らない...
-
エクセルで別シートからの最大...
-
ExcelでTODAY関数を更新させな...
-
VBAで変数に関数式の結果をセッ...
-
労基法の週40時間を超える時...
-
エクセルで、加筆修正したセル...
-
エクセル ハイパーリンクで画像...
-
=HYPERLINK(VLOOKUPに関して。
-
エクセルで特定の文字を打つと...
-
Excelにて、カタカナだけのセル...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの「0」だけ非表示、小数...
-
日付が未入力の際はゼロか、空...
-
エクセルで1月0日と表示される!!
-
エクセルで条件に一致したセル...
-
(Excel)あるセルに文字を入力...
-
Excelで複数シートの選択セルを...
-
Rangeメソッドは失敗しました。...
-
シート参照で変数を使いたい(EX...
-
別シートのセルを絶対参照にする
-
エクセルで複写のように自動入...
-
Excelシートの保護時にデータの...
-
excelでハイパーリンクになって...
-
エクセルで別シートからの最大...
-
複数シートの同じセル内容を1シ...
-
エクセルで20万行あるシート...
-
ExcelでTODAY関数を更新させな...
-
EXCEL関数でシート名が変わる可...
-
エクセルで、加筆修正したセル...
-
Excelのファイル容量が減らない...
-
エクセル ハイパーリンクで画像...
おすすめ情報