【Excel】クロス表での集計は「スピル」にお任せ！　難しい「参照」問題を考えずに済むマル秘テク

複合参照を利用した基本の処理

　まずは「複合参照」を利用した基本の処理を考えてみましょう。ここでは、会員種別と利用時間ごとに金額を集計します。以下は会員種別が「ゴールド」、利用時間が「デイ」でフィルターをかけた状態です。

会員種別「ゴールド」（②）、利用時間「デイ」（③）でフィルターをかけています

　「集計する」のですから、フィルターの結果の利用金額を合計するだけです。複数条件を指定して合計する関数といえば、SUMIFS関数です。構文を復習しておきましょう。

SUMIFS関数の構文。最初に［合計対象範囲］を指定します。［条件範囲］と［条件］をセットで複数指定します。

　クロス表に集計するなら［ゴールド］と［デイ］が交差するセルにSUMIFS関数を入力すれば解決です。［合計対象範囲］は利用料金、［条件範囲1］は会員種別、［条件1］はゴールド、［条件範囲2］は利用時間、［条件2］はデイとなり、以下の数式が考えられます。

=SUMIFS(D2:D11,B2:B11,"ゴールド",C2:C11,"デイ")

　ただ、この数式はコピー＆ペーストには向きません。［合計対象範囲］と［条件範囲1］［条件範囲2］のセル範囲は絶対参照にしておきます。

=SUMIFS($D$2:$D$11,$B$2:$B$11,"ゴールド",$C$2:$C$11,"デイ")

　もうひと工夫しましょう。クロス表にある会員種別と利用時間の見出しを参照して、参照方式は「複合参照」とします。

=SUMIFS($D$2:$D$11,$B$2:$B$11,G$1,$C$2:$C$11,$F2)

　「G$1」と「$F2」が混乱しがちなポイントです。「G$1」はセルG1を参照して1行目を固定しています。数式を横方向（右方向）へコピーした時に列番号はずらして、縦方向（下方向）へコピーした時は行番号をずらさないという意図です。

　一方、「$F2」はセルF2を参照してF列を固定しています。数式を横方向（右方向）へコピーした時に列番号はずらさず、縦方向（下方向）へコピーした時に行番号をずらす動きになります。実際に数式を入力してコピーしてみましょう。

セルG2に「=SUMIFS($D$2:$D$11,$B$2:$B$11,G$1,$C$2:$C$11,$F2)」と入力しました（④）

横方向（右方向）へコピーすると数式が「=SUMIFS($D$2:$D$11,$B$2:$B$11,I$1,$C$2:$C$11,$F2)」と変化（⑤）していることがわかります

　「G$1」は「I$1」に変化していますが「$F2」はそのままですよね。縦方向（下方向）へのコピーの動作も見てみましょう。

縦方向（下方向）へコピーすると数式が「=SUMIFS($D$2:$D$11,$B$2:$B$11,I$1,$C$2:$C$11,$F3)」と変化（⑥）します

「I$1」はそのままで「$F2」は「$F3」と変化しています。複合参照は難しく感じるかもしれませんが、行と列のどちらを固定したいか？ を考えてチャレンジしてみてください。

セルを編集状態にしてセル参照の状態を確認してみましょう

「スピル」を利用して集計する

　Excel 2021とMicrosoft 365のExcelをお使いの場合は「スピル」であっという間に集計できます。SUMIFS関数に指定するセル参照がポイントです。絶対参照も複合参照も気にしなくてOK。オートフィルでのコピーも不要です。ただし、作成したファイルを旧バージョンのExcelで利用する可能性があるなら、前述の処理を利用してください。

セルG2に「=SUMIFS(D2:D11,B2:B11,」と入力します（⑦）。絶対参照にする必要はありません

続けてセルG1～I1をドラッグします（⑧）。引数の指定が間違っているように見えますが問題ありません

同様に［条件範囲2］の「C2:C11」と［条件2］の「F2:F3」を指定します（⑨）

数式を確定すると「スピル」で結果が表示されます（⑩）

　スピルの結果として表示されたセルの数式は「ゴースト」と呼ばれ灰色で表示されます。編集できないことを覚えておきましょう。なお、スピルの結果が表示されるセル範囲にデータが入力されているとエラーになることに注意してください。

定番の処理とあわせて「スピル」も覚えておこう

　複合参照は、Excelを扱ううえで大切な考え方です。「スピル」を利用するテクニックは今後利用することが増えるでしょう。どちらもまとめて練習できるクロス表で慣れておきましょう。