Introduction
Ver. all 2013 2016 2019 2021 365
エクセルのデータを別のワークシートに関数を使用して転記する方法です。
通常、別シートのデータを転記したいときには、
「=」を入力して、
別シートの転記したいセルをクリックして指定するだけです。
後は、その式を下にコピーすれば転記終了。
これを、あえて関数を使用してやってみましょう。
セル参照の知識があると役立つにゃ
別シートのデータを転記する方法
「INDIRECT関数」を使用して転記します。
この「INDIRECT関数」は転記するための関数ではありません。
指定された文字列の参照を返してくれる関数です。
文字で説明すると何を言っているか分かりませんね。
下の表をご覧ください。
この表の「E1」に「INDIRECT関数」で「A1」を指定してみます。
「A1」には「C3」と入力されていますね。
その「C3」を参照して、結果は「三毛猫」が返されます（表示されます）。
「A1」に入力された「C3」という文字列をセル番地として認識して、その番地の結果を返してくれました。
つまり、言葉を短くすると、
「指定された文字列の参照を返してくれた」ということになります。
この関数をワークシート名の指定に利用します。
INDIRECT関数・・・指定された文字列の参照を返す
=INDIRECT（参照文字列,参照形式［省略可］）
- 参照文字列・・・セル参照や、文字列、定義された名前を指定する
- 参照形式・・・［参照文字列］で指定したセル参照を「TRUE」か「FALSE」で指定する。省略するかTRUEの場合はA1形式、FALSEの場合はR1C1形式になる。
※［参照形式］は特殊な場合を除き、R1C1形式を使用していることは無いと思いますので説明を省略します。
では、下の画像のように、「C1」セルに「新宿店」ワークシートの「C5」セルの内容を転記してみます。
転記したいセルを選択して［関数の挿入］ボタンをクリック。
［関数の分類］を［すべて表示］か［検索/行列］に切り替えて「INDIRECT」を選んで［OK］をクリック。
ワークシートのセルを参照したい場合は、下のように設定します。
※シート名の後ろに「!」を入れます。
例： =Sheet1!D2
今回の場合は「新宿店」ワークシートの「C5」番地を参照したいので、
“新宿店!C5”
と指定します。
※文字列として指定するので、ダブルクォーテーション「”」で囲む必要があります。
ワークシート名と見出しの名前を一致させておけばセル参照でも指定できますよ。
その場合は、
C4&”!C5″
となります。
「C4」で「新宿店」を指定して、「“!C5”」で「!C5」。
その２つを「&」演算子で繋ぎます。
別シートのセルを文字列でどのように表現するかがポイントです。
複数シートのデータを転記する方法
最後に応用的な転記方法を紹介しておきます。
見出しとシート名を一致させておくと、INDIRECT関数を使用することで複数シートのデータをまとめることができます。
今回は、「売上一覧」シートに、「新宿店」「渋谷店」「吉祥寺店」の３店舗の合計部分だけを転記したかったとします。
下の画像をご覧いただき、作業内容を把握しておいてください、
また、見出しとシート見出しの名前を同じにしています。
必要な知識は、今回説明したINDIRECT関数に追加して下の２つです。
- VLOOKUP関数
- ネスト（関数の中で関数を使用する）
両方とも説明に時間のかかる知識なので、ここでの説明は割愛して解説リンク先だけ載せておきます。
今回作成する式は下のようになります。
=VLOOKUP(B5,INDIRECT(C4&"!B4:F9"),5,FALSE)
VLOOKUP関数の引数入力画面を表示したのが下の画像です。
これで、下のように「新宿店」の合計が表示されます。
せっかくセル参照を使用しているので、この式を下と右にコピーして終わらせたいですね。
そうなると複合参照の知識が必要になります。
作成した式を
=VLOOKUP(B5,INDIRECT(C4&"!B4:F9"),5,FALSE)
下のように変更します。
=VLOOKUP($B5,INDIRECT(C$4&"!B5:F9"),5,FALSE)
２か所に「$」記号を追加しただけですが、これで下と右にコピーできます。
最終的に「C5」に入れる式の引数入力画面は下のようになります。
複合参照については下のリンク先で説明しています。
まとめ
最後に紹介した方法では、VLOOKUP関数にINDIRECT関数をネストするので、簡単に設定できるとは言えませんね。
また、「&」演算子や文字列を「”」で囲む方法や、最終的には複合参照の知識も使いました。
初心者の方はVLOOKUP関数の学習も兼ねてゆっくり設定するのがお勧めです。
INDIRECT関数で指定する文字列は「A1」のようなセル番地だけではなく、定義された名前も参照可能です。
INDIRECT関数は他の機能と組み合わせても便利に使うことができますよ。
下のリンク先で使用例があります。
「=」のセル参照だけを使う方が楽だにゃー