2行で1データの表で縦計算をする時にセル結合を効果的に使う

「知っていると得するかもしれないExcelの癖」シリーズ第七回、テーマは「1行おきの縦計算」になります。
Excel 2016バージョン1902を使用して確認しています。

---

1行の表であれば、簡単に縦計算して合計を求めることはできますが、2行で1データになっていると考慮が必要です。

説明に使用するサンプル表

下記のサンプル表を元に説明いたします。
第六弾「元表シートを参照する別表シートを作るときの注意点」で使用した表に1行追加をしています。

そして今回も元表を直接修正するのではなく、元表シートを参照する別表シートとして「集計表」を作った上で縦計算をしたいと存じます。
従いまして、大変恐縮ではございますが、最初に「元表シートを参照する別表シートを作るときの注意点」をご一読いただければ幸いです。

そこでセル参照ではなく、OFFSET関数で数式を設定することになりますが、1つのセルに含むことができる合計文字数は32,767文字の制限がありますので、おのずと計算できる行数に上限ができてしまいます。

縦計算を意識した書式にする

そのため、集計表ではまずはC列とD列の間に1列挿入をします。
そのうえで元表ではD列が2行で上段が売上、下段が在庫数であったところを、D列に在庫数、E列に売上となるように列を分けます。
これによりE列に対してセル範囲でSUM関数が設定できるようになります。

設定する数式についての説明

項目	数式
1行目、日付、コード、数量 2行目はすべて	=IF(ISBLANK(INDIRECT(“元表!”&ADDRESS(ROW(),COLUMN()))),””,INDIRECT(“元表!”&ADDRESS(ROW(),COLUMN())))
1行目、売上	=IF(ISBLANK(INDIRECT(“元表!”&ADDRESS(ROW(),COLUMN()-1))),””,INDIRECT(“元表!”&ADDRESS(ROW(),COLUMN()-1)))
売上合計	=SUM(E3:E12)
1行目D列、2行目E列	空白

結果的に売上項目だけが、元表から1列右にずれているので、元表を参照するためにはカラムを「-1」します。
数式の詳細な説明につきましては「元表シートを参照する別表シートを作るときの注意点」をご参照ください。

なお集計表から元の書式に戻すための「戻し表」を考えてみました。

最後のまとめ

既にこのようなやり方を実践されている方もいらっしゃることと存じますが、あえてテーマといたしました。

その理由といいますか、どうしてもお伝えしたかった内容は下記になります。

• 表の書式の設定の仕方によって、使用する数式が変わってくる。
• できるだけセル範囲が使えるように書式を考慮するのが望ましい。
• 後から書式を変更するのは大変
  • 特に条件付き書式を設定ていると、場合によって作り直した方が早いケースもある。

なお、限られたスペースに多くの情報を表示させたいとなると、2行表示を選択しなければならないケースもあることと存じます。
そのため2行表示の際の、並べ替えとかフィルター設定の仕方などについて、今後テーマにして行ければと思います。

以上、最後までご一読いただきありがとうございました。