Microsoft Excel では、他のワークシート上のセルや、別の Excel ファイル内のセルを参照することは一般的な作業です。最初は、これは少し難しくて混乱するように思えるかもしれませんが、仕組みを理解すれば、それほど難しいことではありません。
この記事では、同じ Excel ファイル内の別のシートを参照する方法と、別の Excel ファイルを参照する方法について説明します。また、関数内でセル範囲を参照する方法、定義された名前を使用して処理を簡素化する方法、動的参照に VLOOKUP を使用する方法などについても説明します。
同じ Excel ファイル内の別のシートを参照する方法
基本的なセル参照は、列の文字とそれに続く行番号として記述されます。
したがって、セル参照 B3 は、列 B と行 3 の交差点にあるセルを指します。
他のシートのセルを参照する場合、このセル参照の前に他のシートの名前が付けられます。たとえば、以下はシート名「1 月」のセル B3 への参照です。
=1月!B3
感嘆符 (!) はシート名とセルのアドレスを区切ります。
シート名にスペースが含まれている場合は、参照内で名前を一重引用符で囲む必要があります。
='1月のセール'!B3
これらの参照を作成するには、セルに直接入力します。ただし、Excel に参照を書き込ませる方が簡単で確実です。
セルに等号 (=) を入力し、[シート] タブをクリックして、相互参照するセルをクリックします。
これを行うと、Excel によって数式バーに参照が書き込まれます。
Enter キーを押して数式を完成させます。
別の Excel ファイルを参照する方法
同じ方法で別のブックのセルを参照できます。数式の入力を開始する前に、必ず他の Excel ファイルが開いていることを確認してください。
等号 (=) を入力し、他のファイルに切り替えて、そのファイル内の参照するセルをクリックします。完了したら Enter キーを押します。
完成した相互参照には、角括弧で囲まれた他のワークブック名と、その後にシート名とセル番号が含まれます。
=[シカゴ.xlsx]1 月!B3
ファイル名またはシート名にスペースが含まれている場合は、ファイル参照 (角括弧を含む) を一重引用符で囲む必要があります。
='[ニューヨーク.xlsx]1 月'!B3
この例では、セル アドレスの中にドル記号 ($) が表示されます。これは絶対セル参照です ( )。
異なる Excel ファイル上のセルと範囲を参照する場合、デフォルトでは参照が絶対参照になります。必要に応じて、これを相対参照に変更できます。
参照されているブックを閉じたときに数式を見ると、そのファイルへのパス全体が含まれています。
他のワークブックへの参照の作成は簡単ですが、問題が発生しやすくなります。ユーザーがフォルダーを作成または名前変更し、ファイルを移動すると、これらの参照が破損し、エラーが発生する可能性があります。
可能であれば、データを 1 つのワークブックに保持する方が信頼性が高くなります。
関数内のセル範囲を相互参照する方法
単一のセルを参照するだけでも十分役立ちます。ただし、別のワークシートまたはブック上のセル範囲を参照する関数 (SUM など) を作成することもできます。
通常どおり関数を開始し、前の例で行ったのと同じように、シートとセル範囲をクリックします。
次の例では、SUM 関数は、Sales という名前のワークシート上の範囲 B2:B6 の値を合計しています。
=SUM(売上!B2:B6)
定義された名前を単純な相互参照に使用する方法
Excel では、セルまたはセル範囲に名前を割り当てることができます。これは、後で振り返ってみると、セルや範囲のアドレスよりも意味があります。スプレッドシートで多くの参照を使用する場合、それらの参照に名前を付けると、何を行ったかが非常に簡単にわかります。
さらに良いことに、この名前は、その Excel ファイル内のすべてのワークシートに対して一意です。
たとえば、セルに「ChicagoTotal」という名前を付けると、相互参照は次のようになります。
=シカゴ合計
これは、次のような標準的な参照に代わる、より意味のあるものです。
=営業!B2
定義された名前を作成するのは簡単です。まず、名前を付けるセルまたはセル範囲を選択します。
左上隅の名前ボックスをクリックし、割り当てる名前を入力して Enter キーを押します。
定義名を作成する場合、スペースは使用できません。したがって、この例では、単語が名前に結合され、大文字で区切られています。ハイフン (-) やアンダースコア (_) などの文字で単語を区切ることもできます。
Excel には、将来これらの名前を簡単に監視できるようにする名前マネージャーもあります。 [数式] > [名前マネージャー] をクリックします。 [名前マネージャー] ウィンドウでは、ワークブック内のすべての定義された名前のリスト、それらの場所、および現在格納されている値を確認できます。
次に、上部にあるボタンを使用して、これらの定義された名前を編集および削除できます。
データをテーブルとしてフォーマットする方法
関連データの広範なリストを操作する場合、Excel の表形式機能を使用すると、その中のデータを参照する方法が簡素化されます。
次の簡単な表を見てください。
これはテーブルとしてフォーマットできます。
リスト内のセルをクリックし、[ホーム] タブに切り替え、[表として書式設定] ボタンをクリックして、スタイルを選択します。
セルの範囲が正しいこと、および表にヘッダーがあることを確認します。
その後、[デザイン] タブからテーブルに意味のある名前を割り当てることができます。
次に、シカゴの売上を合計する必要がある場合は、(任意のシートから) その名前でテーブルを参照し、その後に角かっこ ([) を付けてテーブルの列のリストを表示できます。
リスト内で列をダブルクリックして選択し、右角括弧を入力します。結果の式は次のようになります。
=SUM(売上[シカゴ])
テーブルを使用すると、SUM や AVERAGE などの集計関数のデータ参照が標準のシート参照よりも簡単になることがわかります。
このテーブルはデモンストレーション用に小さいものです。テーブルが大きくなり、ワークブック内のシートが増えるほど、より多くのメリットが得られます。
動的参照に VLOOKUP 関数を使用する方法
これまでの例で使用されている参照はすべて、特定のセルまたはセル範囲に固定されています。これは素晴らしいことであり、多くの場合、ニーズには十分です。
しかし、新しい行が挿入されたとき、または誰かがリストを並べ替えたときに、参照しているセルが変更される可能性がある場合はどうなるでしょうか?
このようなシナリオでは、必要な値が最初に参照したのと同じセル内に存在することは保証できません。
これらのシナリオの代替方法は、Excel 内の検索関数を使用してリスト内の値を検索することです。これにより、シートの変更に対する耐久性が向上します。
次の例では、VLOOKUP 関数を使用して、別のシート上の従業員を従業員 ID で検索し、開始日を返します。
以下は従業員リストの例です。
VLOOKUP 関数は、テーブルの最初の列を下方向に検索し、右側の指定された列の情報を返します。
次の VLOOKUP 関数は、上記のリストのセル A2 に入力された従業員 ID を検索し、列 4 (表の 4 列目) から結合された日付を返します。
=VLOOKUP(A2,従業員!A:E,4,FALSE)
以下は、この数式がどのようにリストを検索し、正しい情報を返すかを示しています。
この VLOOKUP が前の例と比べて優れている点は、リストの順序が変わっても従業員が見つかることです。
注: VLOOKUP は非常に便利な数式ですが、この記事ではその価値の表面をなぞっただけです。 VLOOKUP の使用方法について詳しくは 、このテーマに関する記事を ご覧ください。
この記事では、Excel スプレッドシートとワークブックの間で相互参照を行う複数の方法について説明しました。目の前のタスクに適しており、快適に作業できるアプローチを選択してください。
