Excelで集計する際に使うと便利なテーブル機能について紹介します。今回は関数における参照についての注意点と名前の定義などについて紹介します。
結論として、関数を使う時にテーブル内のセルや列、行を参照する際、通常の絶対参照は使えません。そのかわりに、別の方法で参照元を固定することができます。
別の方法とは
- 名前の定義の活用
- コピペ
- 参照範囲指定の方法変更
読んでいただければ10分程度でテーブル機能の基礎から関数・ドロップダウンリストでの活用場面などを理解できると思います。
前回までの記事はこちら
SUMIFS関数を使って各営業社員の四半期の販売実績を集計（通常リスト）
今回は、「よっちゃんいか」販売実績表を集計して、各営業社員の四半期の販売実績を集計します。その場合、複数の条件を指定して合計を返してくれるSUMIFS関数を使います。
（事例として使う関数として簡単なSUMIF関数でもよかったですが、敢えてちょっと複雑な関数にしています。）
使用にあたっての引数は画像の通りとなります。合計したい範囲（販売額フィールド）については、絶対参照となっています。条件範囲①（営業社員フィールド）と条件範囲②（四半期フィールド）も絶対参照となっています。
条件範囲①と条件範囲②に対応する条件については、「J5」に設定した関数をコピーするので複合参照が使われています。条件①については列固定、条件②については行固定です。
SUMIFS関数を使って各営業社員の四半期の販売実績を集計（テーブル）
「よっちゃんいか」販売実績表を集計して、各営業社員の四半期の販売実績を集計します。同じく複数の条件を指定して合計を返してくれるSUMIFS関数を使いますが、今回は集計に使う販売実績表はテーブルにしてあります。
すると、画像の通りテーブルにすることで関数の引数である参照元がテーブルのフィールド名になっており、絶対参照が使えなくなっています。つまり、コピーした際に参照がずれてしまいます。
①名前の定義の活用
テーブルでは絶対参照が使えない代わりに名前の定義を使って参照元を固定します。
各フィールドを範囲選択してから、「数式」タブ→「選択範囲から作成」で各フィールドに名前を定義します。上端行にチェックを入れることで、上端行の「販売額」「営業社員」「四半期」が名前になります。
SUMIFS関数の引数の範囲選択部分に名前の定義で設定した名前を入力します。名前の呼び出しはF3キー（ファンクション３）で簡単に呼び出すことができます。
動画③のとおり、引数に名前を使うことで、コピーしても参照がずれません。
②コピペ
名前の定義は使用せず、通常のコピー&ペーストを使います。「J５」にSUMIFS関数を設定します。
「I5」をコピーします。
貼り付けます。関数の参照範囲がずれることなく、正しい数字がでました。
参照範囲指定の方法変更
名前の定義は使用せず、参照範囲の引数を以下のようにします。
=SUMIFS(テーブル3[[販売額]:[販売額]],テーブル3[[営業社員]:[営業社員]],$I5,テーブル3[[四半期]:[四半期]],J$4)
引数のテーブル３[[フィールド名]:[フィールド名]]については、テーブル３の[フィールド名]から[フィールド名]までという意味になります。（Excelで：（ダブルコロン）は～からという意味です。）
絶対参照にしたい場合は、同じフィールド内ですが敢えて：（ダブルコロン）を使って複数範囲選択のような書き方をします。
・事務職の人向け、筆者おすすめの書籍はこちらから↓
・お仕事で集計業務が多い人は、この本がおすすめ
ピボットテーブルを使うのか？関数を使うのか？Excelのデータ集計から分析までをExcelの機能を多数紹介して体系的に学べます↓
まとめ
３回にわたってテーブルとその活用場面についてまとめました。
日々のお仕事にご活用いただければ大変うれしいです。次回以降もExcelについての情報を記していきます。楽しみにお待ちください。
コメント