条件のある合計を求めるSUMIF関数、セル個数を求めるCOUNTIF関数があります。
のちに条件が複数個使えるSUMIFS関数、COUNTIFS関数が登場しました。しかし、それらは、それ以前のExcelでは使えません。
このように昔のExcelでは今では簡単にできることでもできないことがたくさんありました。
でも、集計をしていると、条件で集計するというのが必要な場合もあり、そのような計算方法が必要になります。
当時、そのような時はどうしていたのでしょうか。
それを紹介します。
今のExcelの関数ではできないことがあったときのヒントになるかもしれません。
データベース関数
そもそも、SUMIF関数が使えない以前のExcelではどんな形で条件付き合計を求めていたのでしょうか。
Excelには、そのような条件付き集計をするデータベース関数という種類の万能な関数が初期のExcelからありました。
例えば合計を求めるにはDSUM関数、個数を求めるにはDCOUNT関数があります。集計関数の前にDが付く関数です。
データベース関数の書式はすべて共通で、DSUM関数で説明すると、
=DSUM(データベース範囲,集計する項目,検索条件範囲)
となります。
上記の表で、分類１、２ともAのものの数値の合計を求める場合は次の計算式です。
=DSUM(A1:C9,A1,E1:F2)
データベース範囲は、一覧表全部の範囲です。合計範囲や一覧表範囲と書かなかったのは、それらと違い、必ず一番上の行に項目名が入っている必要があります。ここが少し使いにくいところなのかもしれません。
集計する項目は、集計する値の入っている項目名です。項目名ではなく、データベース範囲の中の何列目と指定することもできます。
検索条件範囲も少し面倒で、項目名とそれに対する条件を次のように用意します。
横方向には、なおかつ条件、縦方向にはまたは条件となります。
なので、分類がAまたはBの日付が2018年のものを出すという条件は、次のようになります。
日付のセルに=”>=”&A1という計算式を入れることによって、セルA1で日付をコントロールすることができるので、そのような使い方をおススメします。
データテーブル機能
データベース関数では、条件範囲に必ず項目名を書かなければならないので、次のような集計表を作るのが大変です。単純に1個計算式を作って下にコピーというわけにはいきません。
そこで活用したいのが、データテーブル機能です。
データテーブル機能は、あるセルに値を代入し、1つのセルに入っている計算式の結果をその下のセルにも反映してくれる機能です。
この機能を使うには準備が必要です。
今回の場合は、セルD1、D2の範囲にデータベース関数を使うための条件を作る必要があります。
条件は分類１がAという条件なのですが、このAは仮に入れておいています。
そして、セルB13には、=DSUM(A1:B9,B1,D1:D2)という数式が入っています。
この数式、絶対参照にしたとしても、下にコピーするには難しいですよね。
条件範囲が2行使うので、ROW関数とINDIRECT関数を組み合わせるなど、コピーするにはなかなか複雑な数式になってしまいそうです。
ここで、変化する値の範囲、A13からA15、さらに、数式の入っているB13のセルが一番上になるように範囲選択します。つまり、A13からB15の範囲を選択します。
次に、データタブの中のWhat-If分析の中のデータテーブルをクリックします。
そうするとこんな画面が表示されます。
今回、変化させるA、B、Cは1「列」の中に入っています。さらにこのA、B、CはセルD2に代入していきます。
ということで、列の代入セルにD2をセットします。
OKすると、それぞれの値が計算されます。
ピボットテーブル
こんな面倒な方法はいらないかもしれません。
ピボットテーブルで集計すれば一発で求まります。
しかし、ピボットテーブルには決定があります。
自動で再計算をしません。
これがなかなか使いにくくなるケースもあって、値を入力したら自動でいつの間にか集計されているような集計シートではピボットテーブルは向きません。
やはりそんなときはピボットテーブルではなく、計算式にしておく必要があります。
ピボットテーブルは元データにデータを追加してしまうと範囲を広げなければいけないというデメリットもありますね。だからといって列ごと指定すると空白まで集計してしまうし。
条件の結果を計算する列を使う
にしても、上記の方法は面倒なので、できれば項目名とか条件範囲とかそういったものも入れたくない場合、簡単な方法があります。
非常に単純なので、間違いの少ない、考える時間が短縮される方法でもあります。
上の表でセルD1には、次のような計算式が入っています。
=IF(A1=$D$9,B1,0)
それが下にコピーされており、セルD10はその合計を求めています。
このように別のセルに一旦条件の結果を計算すれば、集計も楽です。
あとは横方向にBとCを作って、うまい具合に切り取り、貼り付けすればいいのではないでしょうか。
この方法のデメリットは、余計にセルが必要なことですが、たったそれだけのことです。
邪魔なら非表示にすればいいだけの話です。
メリットの方が多くて、AND関数で複数条件を設定することもできますし、COUNTIFS関数でもできない、または条件もOR関数を使えばできます。
コメント