シートごとの項目別集計を1つの表にまとめる
SUMIF関数の範囲を指定する際に『INDIRECT関数』を使えば、数式内で範囲や条件を設定し直すことなく、すべての科目の会場別受験者数が計算できます。
INDIRECT関数は、指定されたセルに入力されている文字を介し、間接的なセルの指定を行います。
例えば、下図のようにセルA1に入力されている文字列を介し、セルC1に値を返します。
▼ 図2-1 INDIRECT関数@
- INDIRECT関数でセルA1を参照します。
- セルA1には文字列「B1」が入力されているため、
- INDIRECT関数はセルB1に入力されている「300」を返します。
今回はSUMIF関数の範囲/合計範囲部分にINDIRECT関数を使って、数式内でシート名を変更していきます。
それでは、[会場別] シートに切り替えましょう。
▼ 図2-2 科目ごとの会場別受験者数を求める
- 東京A会場のExcel受験者数を求めるセルB4を選択する
- [関数の挿入]ボタンをクリックし、[関数の挿入]ダイアログボックスを表示する
▼ 図2-3 [関数の挿入]ダイアログボックス
- [関数の挿入]ダイアログボックスの[関数の分類]から[数学/三角]を選択する
- [関数名]から[SUMIF]を選択する
- [OK]ボタンをクリックし、[関数の引数]ダイアログボックスを表示する
▼ 図2-4 関数のネスト
- [範囲]ボックスにカーソルがある状態で、数式バーの[関数]ボックスから[その他の関数]を選択する
▼ 図2-5 [関数の挿入]ダイアログボックス
- [関数の挿入]ダイアログボックスの[関数の分類]から[検索/行列]を選択する
- [関数名]から[INDIRECT]を選択する
- [OK]ボタンをクリックし、[関数の引数]ダイアログボックスを表示する
▼ 図2-6 INDIRECT関数A
- [参照文字列]には、シート名の参照先セルB3をクリックして指定する
※ この後、数式をコピーするため、行を固定する複合参照にします。([F4]キーを2回押します)
▼ 図2-7 INDIRECT関数B
- 続いて「&」を入力し、[Excel] シートの会場名が入力されているセル範囲C4からC39をドラッグする。
- [範囲]ボックスに「B$3&Excel!C4:C39」と入力されるが、「Excel」部分は不要なので削除し、「!」以降を” ”で囲む。
▼ 図2-8 [関数の挿入]ダイアログボックス
- 数式バー内の「SUMIF」部分をクリックし、SUMIFの[関数の引数]ダイアログボックスに戻る。
- [検索条件]ボックスには[会場別] シートのセルA4を指定する。
※ 範囲同様、複合参照にしておかないとコピーしたときに選択範囲がずれてしまいます。
ここでは列を固定するので[F4]キーを3回押します。 - [合計範囲]には範囲同様、INDIRECT関数を使って[Excel] シートの受験者数が入力されているセルD4からD39を指定する。
- [OK]ボタンをクリックする
▼ 図2-9 Excelの東京A会場での受験者数が表示
Excelの東京A会場での受験者数が確認できます。
他の会場・科目も数式をコピーすると、受験者数が表示されます。
さらに、SUM関数を使えばE列と9行目に会場ごとの受験者数、科目ごとの受験者数も表示できます。
知っておくと便利!
《縦横集計》
SUM関数を使って、縦計・横計を出したいときは下図のように
参照元データと結果を表示したいセルを範囲選択して、
[オートSUM]ボタンをクリックすればまとめて数式が入力できます。