Excelを使っていてこんなことありませんか？
”テンプレートファイルを用意しCSVデータを読み込ませてたけど、サーバ側の仕様変更・カラム変更により集計したい列がずれた。”
例えばこんな風にCSVのデータが変わった。
事象の例を掘り下げて知りたい方は下記のボックスをクリックしてください。
+ クリックして開く
B列を別のシートで集計したい場合は？
そうです！こうしてシートを参照すれば良いですね。
なんらかの事情で集計対象列がC列に移動しちゃった場合は？
テンプレートファイルはB列を参照したままだと集計できません。
VBAマクロが使える方はCells.Findで検索すれば力業でできるでしょう。
この記事は以下の方に向けて書いています。
- VBAマクロが使えない環境である。
- マクロで作り込むと誰もメンテナンスできない。
- 意地でもExcel関数だけで実装したい。
Excelで別のシートの列を集計する関数
集計したいシート名を【データシート】、集計したいデータのタイトルを【集計対象列】とします。
まずは関数のみを紹介します。
=SUM(INDIRECT("データシート!"&LEFT(ADDRESS(1,MATCH("集計対象列",データシート!1:1,FALSE),4),LEN(ADDRESS(1,MATCH("集計対象列",データシート!1:1,FALSE),4))-1)&":"&LEFT(ADDRESS(1,MATCH("集計対象列",データシート!1:1,FALSE),4),LEN(ADDRESS(1,MATCH("集計対象列",データシート!1:1,FALSE),4))-1)))
呪文かよ。
Excelで別のシートの列を集計する関数の解説
まずはADDRESSとMATCH関数でタイトルである”集計対象列”がどの列にあるか探します。
データシートのC1にありましたね。
次に、はじめに出てくるLEFTで列情報である”C”だけを抽出します。
列のアルファベットが1文字とは限らないためLEFTの文字数を定めず、LENで取得していますね。
データがすんごい増えてAZ列とかに移動してしまうケースの想定です。
同じことを繰り返せばもう一度”C”が抽出できます。
”C”と”C”をコロン：で繋げましょう。
ここまでくればあとちょっとです。
INDIRECT関数でシート名を設定して、対象の列が抜き出せました。
あとはSUMやSUMIFで集計すれば、列がどんなにずれようがタイトルさえ変わらなければ集計できます。
