集計と連携

別の表からデータを抜き出す、複数シートに分けた表の数値を集計、セルをクリックすると製品画像ファイルを表示するなどの別表・シート・ファイルとの連携方法を説明します。

別の表からデータを捜して表示する

◆ 条件に合うデータを別表から転記する

納品書などで「品番」を入力すると別にある商品一覧から品番に合致した商品名や単価を取り出して、自動表示したい時があります。このような場合は、VLOOKUP（ブイルックアップ）関数を使います。

セルB4に商品名を自動表示させたいので、セルB4に「=VLOOKUP(A4,$G$4:$I$9,2,FALSE)」と入力します。引数「検索値」に品番を指定し、範囲には別表を指定します。この時、検索値で探す列が左端になっていることがポイントです。列番号には、表示したいデータの列番号を指定します。この列番号は別表の左から数えた数になります。ここでは、商品名を表示したいので、別表（商品一覧表）の左から2列目を指定しています。文字が完全に一致するものを探しますので、「検索の型」にはFALSEを指定します。なお、関数を入力した後に、下のセルにコピーすることを考慮して、別表の範囲には絶対参照を設定します。
品番が空欄になっているとエラー表示になります。これを回避するには、次のように入力しなおします。
「=IF(A4="","",VLOOKUP(A4,$G$4:$I$9,2,FALSE))」

また、別表となる一覧表が横方向に作成してある場合は、HLOOKUP（エイチルックアップ）関数を使います。

◆ 「～以上」で区切った表からデータを転記する（VLOOKUP関数）

別表にある配送料金表から、商品の購入代金に対応する配送料金を自動表示したい場合は、VLOOKUP関数の「検索の型」を「TRUR」にすることで、範囲の左端の列から「検索値以下の最も近い値」を取り出すことが出来ます。

例題では、別表の配送料金表が「～以上」のような範囲で作成されています。また、基準となる左端の数値が昇順（小さい順）に並べてあります。

セルE11に「=VLOOKUP(E10,H13:I17,2,TRUE)」と入力します。

◆ 別シートの表から条件に合うデータを転記する（VLOOKUP関数）

納品書と商品一覧表が別シートで作成されていても、VLOOKUP関数を使って自動転記できます。ここでのポイントは、引数「範囲」を指定する時に、参照したいシート名を選択して範囲指定します。つまり、VLOOKUP関数を立てている段階で、範囲の指定の時に参照したいシート名をクリックして選択して範囲をドラッグします。

セルB4に「=VLOOKUP(A4,商品一覧表!A3:C10,2,FALSE)」と入力します。エクセルでは、数式の中で異なるシートのセルやセル範囲を指定する時には、シート名に続けて「！」を付け、その後にセル番地を入力します。
なお、範囲指定をより簡単に行うには、参照先のセル範囲に名前を付けておくと便利です。
また、商品一覧表に新しい商品を追加するたびに、参照範囲を設定しなおす必要があります。これが面倒な場合は、あらかじめ参照する列全体を「範囲」に指定すればよいです。この例題であれば、セルB4を「=VLOOKUP(A4,商品一覧表!A:C,2,FALSE)」とします。

◆ 参照先を切り替えて検索する（INDIRECT、VLOOKUP関数）

別表から条件に合致するデータを探して表示するVLOOKUP関数と、参照先を指定するINDIRECT（インダイレクト）関数を組み合わせることで、参照する別表を切り替えて目的のデータを探すことが出来ます。ただし、数式を入力する前に参照表にそれぞれ名前をつけておく必要があります。

まず、セルB8からC13のセル範囲に「大人」、セルE8からF13のセル範囲に「子供」と名前をつけます。名前の付け方は、名前をつけるセル範囲を選択し、［名前ボックス］をクリックして、名前を入力します。

セルC4に「=VLOOKUP(C2,INDIRECT(C3),2,FALSE)」と入力します。

◆ 表を「縦横」に検索してデータを表示する(MATCH、INDEX関数)

表を縦と横から検索して該当するセルの値を取り出すには、MATCH（マッチ）関数とINDEX（インデックス）関数を組み合わせます。

例題では、指定した「重量」と「送り先」がそれぞれ先頭から何番目にあたるかをまず探す必要があります。そこで、右のセルにMATCH関数を用いて調べます。

セルD2に「=MATCH(C2,B9:B13,1)」、セルD3に「=MATCH(C3,C8:G8,0)」と入力します。セルD2の照合の型は「1」を指定し、検査値以下で最も近い値を調べるようにします。
これで、重量と送り先の表における位置が判定できたので、セルC5に「=INDEX(C9:G13,D2,D3)」と入力して、配送料を表示させます。

◆ 検索対象のデータを含む行を丸ごと表示する(OFFSET、MATCH関数)

会員名を入力すると、該当する会員の会員番号や住所などをすべて表示したいケースがあります。このような表では、MATCH（マッチ）関数とOFFSET（オフセット）関数の組み合わせで実現できます。OFFSET関数は、「基準」セルから「行数」、「列数」だけ移動した先の、「高さ」、「幅」分のセル範囲を参照します。

例題では、セルC2に検索したい会員の名前が入力されますので、この名前が表の先頭から何番目にあるのかを調べる必要があります。そこで、セルD2にMATCH関数を使って「=MATCH(C2,B10:B65536,0)」と入力します。引数「検査範囲」は、会員が増えたことを考慮してB10：B65536としています。また、「照合の型」は完全一致の「0」とします。
次に、セルA6に「=OFFSET(A9,$D$2,0)」と入力します。基準となるのは会員番号なので、表の項目名を指定します。引数「行数」は、移動する行数ですからMATCH関数で調べた数値になります。引数「列数」は、右に0列移動するので「0」と指定します。高さと幅は1つのセル分ですから省略します。
セルA6の数式を右側のセルにコピーすれば、検索する名前の1行分のデータが表示されます。

異なるシートの表で集計などを行う

◆ 複数シートの売上金額を合計する(SUM関数)

月ごとに別シートに分けて作成した売上表の金額を合計して、1枚のシートにまとめて表示したい場合、合計対象のセルが各シートの同じ位置にあるなら、SUM（サム）関数でシート間を「串刺し合計」できます。

例題として、10月、11月12月のシートの売上金額の合計を、「第4四半期計」シートに作ることにします。手順は以下のとおり行います。

①「第4四半期計」シートのセルB3を選択します

②ツールバーの「オートSUM」ボタンをクリックします。

③そのまま、「10月」のシート見出しをクリックし、「10月」シートのセルB3をクリックします。

④次に、Shiftキーを押したまま、「12月」シートのシート見出しをクリックします。

⑤最後に、ツールバーの「オートSUM」ボタン、またはEnterキーを押します。

数式バーに「=SUM('10月:12月'!B3)」と表示されていることを確認します。別シートのセルを参照する場合、「シート！セル番地」のように、シート名とセル番地を「！」でつなぎます。複数シートをまたいで合計するには、先頭と末尾のシート名を「：」でつなぎます。数式ができたら、下のセルにコピーします。

◆ 複数シートにある値を１つの表に集約する(INDIRECT関数)

月別の売上表を別のシートに作成しているときに、各シートのデータを1つの表に集約して表示するのであれば、INDIRECT関数を使います。

引数に「参照文字列」を指定すると、文字列として入力したセル番地や範囲名を計算式で参照できる形に変換できます。そこでまず、「第4四半期計集約」シートのB2からD2までの項目名を10月、11月、12月のようにシート名と同じ名前にします。

セルB3に、「10月」と入力されているセルB2と、「！B7」という文字列を「＆」でつなげて、INDIRECT関数の引数に指定します。セルB3に「=INDIRECT(B2&"!b7")」と入力します。
この数式の意味は、「10月シートのセルB7」の値を参照することになります。
後は、この数式をセルC3とD3にコピーします。参照するシート名が切り替わり、売上合計を取り出せます。

なお、この方法では、取り出したセルがすべて同じ位置にないとうまくいきません。また、参照するシートの名前と合計金額の項目名が同じである必要もあります。それと、シート名にカッコが含まれると、INDIRECT関数がエラーになるので、その場合はシート名を変更しておきます。

◆ 目次をクリックして該当するシートを表示する(HYPERLINK関数)

例題の表で、「7月」の行にある「詳細」をクリックしたら、「7月」シートが開くような仕掛けを作るには、HYPERLINK（ハイパーリンク）関数を利用します。引数に「リンク先、別名」を指定します。ここでは、「合計」シートの項目名を参照して、「リンク先」を指定しています。

同じブック（ファイル）内のセルを指定するには、先頭に「#」をつけます。それにシート名のセルA3と、セル番地「!A1」を「&」で結合します。文字の部分を「"」で挟みます。セルD3に「=HYPERLINK("#"&A3&"!A1","詳細")」と入力します。別名には「詳細」の文字を指定しています。あとは、下のセルにコピーします。