Excel テーブル機能③~関数における参照について~

Excel
スポンサーリンク

Excelで集計する際に使うと便利なテーブル機能について紹介します。今回は関数における参照についての注意点と名前の定義などについて紹介します。

結論として、関数を使う時にテーブル内のセルや列、行を参照する際、通常の絶対参照は使えません。そのかわりに、別の方法で参照元を固定することができます。

別の方法とは

  • 名前の定義の活用
  • コピペ
  • 参照範囲指定の方法変更

読んでいただければ10分程度でテーブル機能の基礎から関数・ドロップダウンリストでの活用場面などを理解できると思います。

前回までの記事はこちら

Excel テーブル機能①~基本的な使い方~
Excelのテーブル機能の基本的な使い方についてまとめています。
Excel テーブル機能②~入力規則リストと名前の定義~
Excelで集計する際に使うと便利なテーブル機能について紹介します。今回は入力規則のリストの参照先にテーブルを設定する際の注意点と名前の定義について紹介しています。
スポンサーリンク
スポンサーリンク

SUMIFS関数を使って各営業社員の四半期の販売実績を集計(通常リスト)

今回は、「よっちゃんいか」販売実績表を集計して、各営業社員の四半期の販売実績を集計します。その場合、複数の条件を指定して合計を返してくれるSUMIFS関数を使います。

(事例として使う関数として簡単なSUMIF関数でもよかったですが、敢えてちょっと複雑な関数にしています。)

使用にあたっての引数は画像の通りとなります。合計したい範囲(販売額フィールド)については、絶対参照となっています。条件範囲①(営業社員フィールド)と条件範囲②(四半期フィールド)も絶対参照となっています。

条件範囲①と条件範囲②に対応する条件については、「J5」に設定した関数をコピーするので複合参照が使われています。条件①については列固定、条件②については行固定です。

通常リストでの参照範囲設定
スポンサーリンク

SUMIFS関数を使って各営業社員の四半期の販売実績を集計(テーブル)

「よっちゃんいか」販売実績表を集計して、各営業社員の四半期の販売実績を集計します。同じく複数の条件を指定して合計を返してくれるSUMIFS関数を使いますが、今回は集計に使う販売実績表はテーブルにしてあります。

すると、画像の通りテーブルにすることで関数の引数である参照元がテーブルのフィールド名になっており、絶対参照が使えなくなっています。つまり、コピーした際に参照がずれてしまいます。

テーブルの利用

①名前の定義の活用

テーブルでは絶対参照が使えない代わりに名前の定義を使って参照元を固定します。

名前の定義

各フィールドを範囲選択してから、「数式」タブ→「選択範囲から作成」で各フィールドに名前を定義します。上端行にチェックを入れることで、上端行の「販売額」「営業社員」「四半期」が名前になります。

動画②
名前の定義②

SUMIFS関数の引数の範囲選択部分に名前の定義で設定した名前を入力します。名前の呼び出しはF3キー(ファンクション3)で簡単に呼び出すことができます。

動画③

動画③のとおり、引数に名前を使うことで、コピーしても参照がずれません。

Excel 名前の定義の活用
Excelの名前の定義の活用について書いています。基本的な設定方法や名前の定義を使って参照範囲が増加した際の自動参照範囲の設定(可変設定)などを解説しています。

②コピペ

名前の定義は使用せず、通常のコピー&ペーストを使います。「J5」にSUMIFS関数を設定します。

コピペ①

「I5」をコピーします。

コピペ②

貼り付けます。関数の参照範囲がずれることなく、正しい数字がでました。

コピペ③

参照範囲指定の方法変更

名前の定義は使用せず、参照範囲の引数を以下のようにします。

=SUMIFS(テーブル3[[販売額]:[販売額]],テーブル3[[営業社員]:[営業社員]],$I5,テーブル3[[四半期]:[四半期]],J$4)

参照範囲変更

引数のテーブル3[[フィールド名]:[フィールド名]]については、テーブル3の[フィールド名]から[フィールド名]までという意味になります。(Excelで:(ダブルコロン)は~からという意味です。)

絶対参照にしたい場合は、同じフィールド内ですが敢えて:(ダブルコロン)を使って複数範囲選択のような書き方をします。

・事務職の人向け、筆者おすすめの書籍はこちらから↓

エクセルの勉強に役立つおすすめ本【事務職編】
エクセルを実務で多用している私が特におすすめのエクセルを勉強するときに役立つ本を紹介しています。明日から即使えるスキルを学びたいという方や書籍選びに迷っている方は是非読んでください。

・お仕事で集計業務が多い人は、この本がおすすめ

ピボットテーブルを使うのか?関数を使うのか?Excelのデータ集計から分析までをExcelの機能を多数紹介して体系的に学べます

スポンサーリンク

まとめ

3回にわたってテーブルとその活用場面についてまとめました。

日々のお仕事にご活用いただければ大変うれしいです。次回以降もExcelについての情報を記していきます。楽しみにお待ちください。

ExcelVBAのクラスモジュールの基本①
ExcelVBAのクラスの基本について、図解や実際のコードを使って解説しています。
ピボットテーブルの押さえておくべき基本機能
ピボットテーブルを使うに当たって最低限知っておくべき機能や基本的な使い方を紹介しています。

コメント

タイトルとURLをコピーしました