４つのエクセルのデータを参照しひとつの表を作成 シート複製時に参照先を列を＋1したい

Question

４つのエクセルのデータを参照しひとつの表を作成　シート複製時に参照先を列を＋1したい

Answer 1

こんにちは。

他の方も書かれているように、質問内容が漠然としていて、どのような表を作成されたいのかが良く分かりません。

なので、具体的に何をどのようにお答えしたら良いかも思いつかない状態です。

ただ、

＞シート複製時に参照先を列を＋1したい

と書いておられる部分だけに絞ってみると、

《 新たなシートを作成する際に作成済みのシートをそのままコピーして流用していきたいが、参照先の列だけは自動的に変更されるようにしたい。》

といったことのように思えます。

そういった場合、CELL 関数を応用して使えば良いかなと思います。

・CELL 関数

https://support.microsoft.com/ja-jp/office/cell-%E9%96%A2%E6%95%B0-51bd39a5-f338-4dbe-a33f-955d67c2b2cf

これだけでは分かり難いかと思いますので、CELL 関数の使用方法の具体例も考えてみました。

必要なければ読み飛ばしていただいて構いません。

ただ、表の構成やセル配置、表の個数、シートの個数やシート名、データは文字列／数値？（あるいは両方混在？）、Excel のバージョンは？、等々が一切不明のままでは先に進めないため、

《 図１のような表が『元表』シートに有ると仮定して、この表の中から四半期ごと（ 3か月ごと）の「データ1」列の値を、図２のように『データ1』シートの B2:B5 のセル範囲に表示させ、更にこの『データ1』シートをコピーし新たなシートを作成した場合には、そのシート内のデータは『元表』シートの「データ2」列の値としたい。》

というような場合で考えてみました。

・図１（シート名『元表』）

・図２（シート名『データ1』）

図２では、『元表』シートから取り出したい「月」の値を A列に手入力しています。（図１の A列の値と同じ形式。）

その状態から、下記の各数式を入れています。

図２は各数式が正常に機能している状態です。

「月」の値は数式で自動的に入力していくことも出来ますが、4行だけならば手入力で良いかなと思います。

＞４つのエクセルのデータ

と書かれているのは、こういった 4行のデータのことかと推測しました。

全く違っているかもしれませんが、こういった仮定の上で話を進めないと先に進めませんので。

・数式１

※図２の B1セルに入れる数式です。

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

この数式では、このファイルのパスを含むファイル名を取得し、その最後に加えられているシート名（開いているシート名）を表示させています。

※ただし、このファイルを一度保存してから開き直さないと、正しいファイル名（シート名）は取得できませんのでご注意ください。

CELL 関数の第 2引数は省略可能なのですが、これまでの経験からして省略しない方が良いように思います。

なので A1をダミーとして入れています。

・数式２

※図２の B2セルに入れる数式です。

=INDEX(元表!A$2:F$13,MATCH(A2,元表!A$2:A$13,0),MATCH(B$1,元表!A$1:F$1,0))

※この数式は、入力後にこれをコピーし、必要なだけ行方向（ここでは B5セルまで）に貼り付けています。

（慣れておられるのでしたら「オートフィル」を使ったほうが簡単です。）

この数式では、A2セルの値を『元表』シートの A列で検索し一致する行位置を取得、B1セルの値を『元表』シートの 1行目で検索し一致する列位置を取得し INDEX 関数に渡すことで、『元表』シートのデータ全体から該当のデータのみを表示させています。

ここからが本題になります。

『データ1』シートをそのままコピーし新しいシートを作成すると、『データ1 (2)』というシート名のシートが出来上がるはずです。

ここで、シート名を『シート2』に変更すると、それだけで『元表』シートの「データ2」列の該当の値が表示された図３の表が出来上がります。

これが CELL 関数を活用した数式１の効果です。

・図３（シート名『データ2』）

※以後、同様に、コピーしたシート名を変更していくだけで目的のシートを簡単に作成していくことが出来ます。

尚、『元表』シートでは列見出しが「データ1」「データ2」・・というように順番に並んでいますのでシート名も『データ1』『データ2』・・というように順番に並べれば、ご希望のように、参照する列位置は順に右の列に移っていきます。

また、『元表』シートの列見出しは順番に並んでいなくても、全く関連の無い名前の並びでも問題ありません。

肝心なのは、コピーしたシート名を変更する際に、参照する列位置が順に右側に移っていくようにシート名（＝列見出し名）を指定することです。

以上です。

ご自身で作成したいと思っておられる表がどのような表なのか分かりませんので、ご紹介出来る具体例はこのくらいです。

尚、Microsoft365 の Excel をお使いの場合でしたら、数式２はもう少し簡単で効率の良い数式に出来るかもしれません。

Windows11 と Excel2021 の組み合わせで動作確認しています。

ご参考になれば幸いです。

Answer 2

「４つのエクセルのデータ」とは具体的に何を意味しているのでしょうか。

・ある 1 つの Excel ブック（ .xlsx ファイル）の中に
　4 つのワークシートが存在し、それぞれのワークシート上に
　表が 1 つずつ存在する。

・4 つの Excel ブック（ .xlsx ファイル）が別々に存在し、
　それぞれのブック内のある 1 つのワークシート上に
　表が 1 つずつ存在する。

・ある 1 つの Excel ブック（ .xlsx ファイル）の
　ある 1 つのワークシート上に 4 つの表が存在する。

・上記以外のブック/ワークシート/表の構成。

> ひとつの表を作成> シート複製時に参照先を列を＋1したい

また、何らかの計算や自動処理（数式、マクロ、Power Query など）によって
それを実現なさりたいのか、それともコピーアンドペーストなどの手動操作に
よって実現できれば充分なのかも不明瞭です。

まずどのようなデータを扱おうとしていて、最終的にどのような結果を得られればよいのか、
その背景と目的について可能な限り具体的に明記されることをお奨めします。