34　エクセルで複雑な分類集計する

１　下のような現金出納帳があります。
この表から、7月の分野が管理費で、適用が交通費で、使用者が田中の合計を求めてください。

月 日 分野 適用 使用者 収入 支出 残高
6 12 事業費 交通費 山田 750 567,800
6 15 管理費 雑費 田中 1,850 565,950
6 16 事業費 雑費 佐藤 450 565,500
6 18 管理費 事務費 山田 1,700 563,800
6 22 事業費 雑費 山田 980 562,820
6 25 管理費 交通費 佐藤 440 562,380
6 30 事業費 事務費 山田 1,800 560,580
7 1 管理費 交通費 田中 890 559,690
7 2 事業費 雑費 田中 790 558,900
7 3 事業費 交通費 佐藤 440 558,460
7 3 管理費 雑費 安藤 850 557,610
7 4 管理費 事務費 山田 2,500 555,110
7 4 事業費 事務費 田中 3,900 551,210
7 5 管理費 交通費 田中 440 550,770
7 6 事業費 交通費 田中 640 550,130
7 8 事業費 交通費 安藤 480 549,650
7 10 管理費 雑費 田中 890 548,760
7 11 事業費 事務費 佐藤 1,200 547,560
7 12 管理費 雑費 佐藤 980 546,580
7 14 管理費 交通費 安藤 860 545,720
7 15 事業費 事務費 田中 1,460 544,260
7 15 事業費 事務費 山田 280 543,980
7 18 管理費 交通費 田中 440 543,540
7 19 事業費 交通費 山田 660 542,880
7 22 事業費 雑費 田中 560 542,320
7 25 事業費 交通費 安藤 660 541,660
7 28 管理費 事務費 佐藤 680 540,980
合計 568,550 27,570 540,980

※ＩＦ関数などの基本的な操作がわかる方むけに説明しています。関数や絶対参照のわからない方は、基本操作の勉強をしてからじゃないとむつかしいと思います。ANDやOR関数、DSUM関数、IF関数を複数組み合わせても答えを出すことができます。

２　一般的な方法では、オートフィルターをつかったり、並べ替えたりして、集計するか、ＩＦ関数を組み合わせて計算することになりますが、ＩＦを組み合わせる場合は、計算式がたいへんです。

下のような方法で計算することができます。
Ｊ列に７月だったら、１を表示して、それ以外は０を表示するようにします。

３　次は、管理費だったら１を表示するようにします。

４　次は交通費だったら、１を表示するようにします。

５　次は田中だったら表示するようにします。

６　支出×事業費×管理費×交通費×田中＝で、どれかの条件があわないと０になるので、合計も０になります。この方法で、簡単に複雑な部門別の集計をすることができます。

７　条件をセルに入れて、絶対参照で使う方法だと、いろいろな条件を入れて、簡単に集計することができます。

応用問題として、ペットを飼っている人で、猫を飼っていて、色が白か黒で、メス猫というような条件で集計することができます。

目次に戻る