Excelで複数のシートからデータを集計するにはINDIRECT関数が便利です。
ややマイナーな関数ですが、覚えておくと役に立ちます。
他のシートから数値を参照
たとえば、次のような事例があります。
1月〜6月の月ごとに給与一覧表がある例です。
通常だと、次のような数式を入れるでしょう。
=を入れて、マウスで選択して、という操作を6回繰り返せば、完成します。
この操作をもっと簡単にするのがINDIRECT関数です。
INDIRECT関数ならオートフィルで簡単に集計
先ほどの数式は、
=’1月’!D19
という数式です。
このうち、’1月’の部分だけが変化します。
[1月]はセルにすでに入力されていますので、これを利用するのです。
セルA2に[1月]とありますので、
=A2&”!D19″
と入力します。
(この[!]は、シート名とセル名の区切りに入れるものです)
すると、次のようになってしまいます。
単純に[1月]と[!D19]という文字をつなげただけだからです。
Excelは指示通りのことをしてくれているので、なんら悪くありません。
文字列ではなく、「セルの参照として読み取ってほしい」と正しくExcelに伝える必要がありますので、INDIRECT関数を使い、
=INDIRECT(A2&”!D19″)
と入力します。(画面では小文字で入力しています)
INDIRECT関数は、「この文字列はセルの参照だよ」とExcelに伝えることができる関数です。
前述の「’1月’!D19」を「A2」と「!D19」で表現しています。
これをコピーすれば、簡単に複数のシートの値を合計することができます。
さらにコピーすると、7月以降のシートがないため、エラーになります。
合計欄を各シートの同じセルに入れる
この方法で集計するときに重要なのは、各シートで合計すべき数値が同じセルにあることです。
「1月のシートではセルD19にあり、2月はセルD20にある」という状況では正しく集計できません。
そのため、事例では、合計欄を表の左側に置いています。
右側に置いていると、社員が増えた場合に合計欄の位置がずれるからです。
もちろん、項目の追加もしてはいけません。
「一定のルールに従って規則正しくデータを作る」のはExcelの鉄則です。
Excelでは原則として、データを複数のシートに分散させない方が好ましいですが、どうしてもシートを分けなければいけない場合には、このINDIRECT関数が役立ちます。
(もちろん、マクロで集計することもできます)
集計したい数値の位置がシートごとに異なる場合はこういった方法もあります。
複数シートに関する記事です。
1/26 WordPressブログ入門セミナー 残1名
2/18 「売上の柱」入門セミナー
2/25 ブログ道場
■個別コンサルティング
■動画音声オンラインショップ
火曜日配信!週刊メルマガ『井ノ上陽一の雇われない雇わない生き方への道』
YouTubeチャンネル『ひとり税理士井ノ上陽一の自宅仕事術』
■税務顧問・ブログ
■メールコンサルティング
■執筆の依頼
■メルマガ「税理士進化論」
新刊『独立後の勉強法』
『雇われない・雇わない生き方2021』無料送付
昨日は、幕張でトライアスロンでした。
やはり海はきれいとはいえませんでしたね(^_^;)
自己タイムは2分縮めましたが、スイムが2分半遅くなり、バイクが5分半速く、ランが1分遅いという内訳です。
ボランティアの方から応援がうれしい大会でした。
■著書
ひとり税理士のセーフティネットとリスクマネジメント | 陽一, 井ノ上 |本 | 通販 | Amazon 税理士のためのRPA入門~一歩踏み出せば変えられる!業務効率化の方法~
やってはいけないExcel――「やってはいけない」がわかると「Excelの正解」がわかる
AI時代のひとり税理士
新版 そのまま使える 経理&会計のためのExcel入門
ひとり税理士のIT仕事術
フリーランスとひとり社長のための 経理をエクセルでトコトン楽にする本
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』
【監修】十人十色の「ひとり税理士」という生き方