エクセル関数について

質問

添付画像（上記）のような出納帳を作っているのですが、別シートの集計に自動に反映されるよう関数を入れたいのですが、どのようにしたら良いのでしょうか？
（１）上記の表に入力された時に、下記表の項目へ合計が出るようにしたいです
例：営業１課の「高速代・・・その他までの合計」
B13には、「A２～Ａ９が（A13）で（実際は別シートになります）、Ｂ２～Ｂ９が（B12）の場合、Ｄ２～Ｄ９までを足した合計

回答No.6

kagakusuki · ベストアンサー

実際には、下の表は上の表とは別のシート存在していて、上の表は9行目までしかないとは限らず、その時々で行数が変化するのだと思います。
　又、上の表を作成する際には、セルや行の削除や挿入、切取り&貼付け、等の編集作業にも対応していた方が便利ではないかと思います。
　以下は、元の表の行数が変化しても関数を書き替える必要が無く、セルや行の削除や挿入、切取り&貼付け、等の編集作業を行っても、集計結果が正しく表示される方法です。

　今仮に、上の表で「課」と入力されているセルがSheet1のA1セルであり、下の表で「課」と入力されているセルがSheet2のA1セルであるものとします。

●ExcelのバージョンがExcel2007以降のバージョンである場合
　まず、Sheet2のB2セルに次の数式を入力して下さい。

=IF(COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$B:$B,B$1)=0,"",SUMIFS(Sheet1!$D:$D,Sheet1!$A:$A,$A2,Sheet1!$B:$B,B$1))

　次に、Sheet2のB2セルをコピーして、Sheet2のB2セル～「集計表の中で最も右下にあるセル」の範囲に貼り付けて下さい。

●ExcelのバージョンがExcel2007よりも前のバージョンで、且つ、作業列を使用する場合
　今仮に、Sheet3のA列を作業列として使用するものとします。
　まず、Sheet3のA2セルに次の数式を入力して下さい。

=INDEX(Sheet1!$A:$A,ROW())&INDEX(Sheet1!$B:$B,ROW())

　次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に、Sheet1の表の行数を上回るのに充分な行数となるまで貼り付けて下さい。

　次に、Sheet2のB2セルに次の数式を入力して下さい。

=IF(COUNTIF(Sheet3!$A:$A,$A2&B$1)=0,"",SUMIF(Sheet3!$A:$A,$A2&B$1,Sheet1!$D:$D))

　次に、Sheet2のB2セルをコピーして、Sheet2のB2セル～「集計表の中で最も右下にあるセル」の範囲に貼り付けて下さい。

●ExcelのバージョンがExcel2007よりも前のバージョンで、且つ、作業列を使用しないで、関数のみで集計を行う方法
　まず、Sheet2のB2セルに次の数式を入力して下さい。

=IF(SUMPRODUCT((Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9E+99,Sheet1!$D:$D))=$A2)*(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9E+99,Sheet1!$D:$D))=B$1))=0,"",SUMPRODUCT((Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9E+99,Sheet1!$D:$D))=$A2)*(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9E+99,Sheet1!$D:$D))=B$1)*Sheet1!$D$1:INDEX(Sheet1!$D:$D,MATCH(9E+99,Sheet1!$D:$D))))

　次に、Sheet2のB2セルをコピーして、Sheet2のB2セル～「集計表の中で最も右下にあるセル」の範囲に貼り付けて下さい。

※尚、一般的に、SUMPRODUCT関数を使用した方法は、計算処理に要するパソコンの負荷が大きくなり、表の行数が過大になりますと、計算が処理が終了して結果が表示されるまでに時間が掛かる場合が多くなりがちです。（数百行程度であれば許容範囲だと思いますが）
　ANo.2様がSUMPRODUCT関数を使用した作業列が必要のない方法を回答された後であるにも関わらず、ANo.4様が作業列を使用した方法を回答されておられるのも、おそらくはSUMPRODUCT関数を使用すると計算処理が重くなるため、という理由もあるのではないかと思います。

回答No.5

tom04

こんばんは！
横からお邪魔します。

No.2さんの補足に
＞「0」の場合は空白にしたいのですが・・・
とありますので、当方使用のExcel2003での場合です。

メニュー　→　ツール　→　オプション　→　「表示」タブ　→　「ゼロ値」のチェックを外す方法があります。

この方法ではなく、関数でやる場合は
↓の画像のSheet2のB2セルに

=IF(SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1:$B$100=B$1),Sheet1!$D$1:$D$100)=0,"",SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1:$B$100=B$1),Sheet1!$D$1:$D$100))

（Sheet1の100行目まで対応できる数式です）
という数式を入れ列方向と行方向にオートフィルでコピーしています。

もし、「ゼロ値」のチェックを外すのであれば
B2セルには

=SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1:$B$100=B$1),Sheet1!$D$1:$D$100)

という数式だけでOKです。
尚、すでに回答が出ていますが、Excel2007以降のバージョンではSUMIFS関数が使用可能です。

どうもお邪魔しました。m(_ _)m

お礼

お忙しい中、ありがとうございました。設定を変更する事で空白になりました。大変助かりました。

回答No.4

KURUMITO

初心者でも分かり易く計算にも負担とならない方法は作業列を作って対応することです。
E2セルには次の式を入力してE9セルまでオートフィルドラッグします。

=A2&B2

その後にB13セルには次の式を入力してG13セルまでオートフィルドラッグしたのちに下方の15行までオートフィルドラッグします。

=IF(COUNTIF($E$2:$E$9,$A13&B$12)=0,"",SUMIF($E$2:$E$9,$A13&B$12,$D$2:$D$9))

回答No.3

某HN クロメート(Chromate)（@CoalTar）

関数を使わない案です

B1セルに「内訳」とタイトルを入れました

[Ctrl]+[F3]名前の定義
名前 ピボット用
参照範囲 =$A$1:INDEX($D:$D,COUNTA($A:$A))

ピボットテーブル ウィザード
データ範囲 =ピボット用
レイアウトは添付図参照

データが追加削除された場合、ピボットテーブルを更新すれば追随してくれます
参考まで

回答No.2

mshr1962

本当はピボットテーブルを使ったほうが楽だと思いますけど．．．
数式で集計する場合は
Sheet1!A1:D100を参照してSheet2!A12:G15の表へ合計として

営業1課の高速代
Excel2007以降なら
B13=SUMIFS(Sheet1!$D$2:$D$100,Sheet1!$A$2:$A$100,$A13,Sheet1!$B$2:$B$100,B$12)

Excel2003以前なら
B13=SUMPRODUCT(Sheet1!$D$2:$D$100,(Sheet1!$A$2:$A$100=$A13)*(Sheet1!$B$2:$B$100=B$12))

B13の式をG15までコピーする

補足

詳しく教えていただいた中からこちらが初心者にはわかりやすかったので参考にさせていただきました。
この関数で、合計が自動入力されるようになりましたが、下の表（実際にはシート２）で、空白にしたい部分には「0」と表示されてしまいます。「0」の場合は空白にしたいのですが、可能ですか？

回答No.1

keithin

B13には
=SUMIFS(Sheet1!$D$2:$D$9,Sheet1!$A$2:$A$9,$A13,Sheet1!$B$2:$B$9,B$12)
のように記入し，右に下にコピーします。