「知っていると得するかもしれないExcelの癖」シリーズ第七回、テーマは「1行おきの縦計算」になります。
Excel 2016バージョン1902を使用して確認しています。
1行の表であれば、簡単に縦計算して合計を求めることはできますが、2行で1データになっていると考慮が必要です。
説明に使用するサンプル表
下記のサンプル表を元に説明いたします。
第六弾「元表シートを参照する別表シートを作るときの注意点」で使用した表に1行追加をしています。
そして今回も元表を直接修正するのではなく、元表シートを参照する別表シートとして「集計表」を作った上で縦計算をしたいと存じます。
従いまして、大変恐縮ではございますが、最初に「元表シートを参照する別表シートを作るときの注意点」をご一読いただければ幸いです。
項目の1行目は「日付、コード、数量、売上(金額)」、2行目は「販売条件、回収条件、納入日、在庫数」としています。
このような表がある時に、売上を求めるためにどのようにするのが良いか?というのが今回のテーマになります。
その場合このサンプル表にように少量な行の縦計算であれば、該当セルを1行つづ選択して数式を作れば良いのですが、これが数百行とか、もっと大量になった時には大変です。
ちなみに、セル参照で数式を作ってしまうと、表に追加削除があった時に数式に影響が起こります。
このテーマにつきましては、文字列結合の場合で説明していますが、「数式で文字列を結合する時の注意点」を参照いただければ幸いです。数式で数値を計算する場合でも同じ現象が起こります。
そこでセル参照ではなく、OFFSET関数で数式を設定することになりますが、1つのセルに含むことができる合計文字数は32,767文字の制限がありますので、おのずと計算できる行数に上限ができてしまいます。
縦計算を意識した書式にする
そのため、集計表ではまずはC列とD列の間に1列挿入をします。
そのうえで元表ではD列が2行で上段が売上、下段が在庫数であったところを、D列に在庫数、E列に売上となるように列を分けます。
これによりE列に対してセル範囲でSUM関数が設定できるようになります。
なお、売上左のD列、在庫数右のE列は空セルにします。
設定する数式についての説明
| 項目 | 数式 |
| 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」します。
数式の詳細な説明につきましては「元表シートを参照する別表シートを作るときの注意点」をご参照ください。
なお集計表から元の書式に戻すための「戻し表」を考えてみました。
- 売上の数式以外
=IF(ISBLANK(INDIRECT("集計表!"&ADDRESS(ROW(),COLUMN()))),"",INDIRECT("集計表!"&ADDRESS(ROW(),COLUMN())))
- 売上の数式
- 集計表では1列右にずれているので、戻し表から見ると「+1」になります。
=IF(ISBLANK(INDIRECT("集計表!"&ADDRESS(ROW(),COLUMN()+1))),"",INDIRECT("集計表!"&ADDRESS(ROW(),COLUMN()+1)))
最後のまとめ
既にこのようなやり方を実践されている方もいらっしゃることと存じますが、あえてテーマといたしました。
その理由といいますか、どうしてもお伝えしたかった内容は下記になります。
- 表の書式の設定の仕方によって、使用する数式が変わってくる。
- できるだけセル範囲が使えるように書式を考慮するのが望ましい。
- 後から書式を変更するのは大変
- 特に条件付き書式を設定していると、場合によって作り直した方が早いケースもある。
なお、限られたスペースに多くの情報を表示させたいとなると、2行表示を選択しなければならないケースもあることと存じます。
そのため2行表示の際の、並べ替えとかフィルター設定の仕方などについて、今後テーマにして行ければと思います。
以上、最後までご一読いただきありがとうございました。
