エクセルで二つの条件での合計値

質問

こんにちわ。
関数でいろいろやってみているのですが、どうしてもうまくいきません。

シート１に一覧表があります。Ａ列にコード番号（約５００番）、Ｂ列に名前、Ｃ列に種類わけ（１０種類・リストにしています）、Ｄ列に数字が入っています。これが約５０００行あります。

これをシート２に、コード番号（Ａ列）及び種類（Ｃ列）が一致するものの数字（Ｄ列）の合計を出した、自動更新される集計表を作りたいのです。

しかも、Ｃ列の種類わけしているものの中から、２つずつペアリングしての数字（Ｄ列）の合計を出したいのです。

ピボットテーブルやオートフィルターでひとつずつ出していってもいいのですが、次々と追加で行が増えていくので、その都度集計表を更新していくのもタイヘンだと思い、自動的に更新されるといいなと思っています。

説明不足なことがありましたら、返答します。
相当困っています。
どなたか助けてください。
よろしくお願いします。

回答No.6

miwaki · ベストアンサー

シート２には＃５の回答のような表があるとして、セルＣ２に次の関数を入れてください。
=SUMIF(Sheet1!$F:$F,Sheet2!$A2&Sheet2!C$1,Sheet1!$D:$D)
これをコピーしてＣ列からＧ列までのすべてのセルに貼り付けてください。
シート１のセルＦ2に次の関数を入れてください。
=A2&LEFT(C2,2)
これをコピーしてデータの最下端まで貼り付けてください。もしデータ行が増えたら、追加貼り付けをしてください。
これで、シート２にご所望の表ができると思います。

お礼

ありがとうございます！

#5よりも、最初にこちらのほうができました！！
私は、二つの条件を「＆」でつなぐことで関数を簡単にすることができることを知らなかったのでとても参考になりました。
エクセルって奥が深いですね。
ご丁寧に教えていただきましてありがとうございました！！

回答No.5

junjun2004

まず、sheet1のＣ列が
「東京・新宿」・・・「東海・静岡」と入っているとしてやってみました。

集計表が下のようになればいいのですね？
　　Ａ　　　　　Ｂ　　　Ｃ　　　Ｄ　　　E　　・・・
1　コード　名前　　　東京　　関東　　東海　・・・
2　xx1　　　aaa　　　100　　　200　　　550 ・・・
3　xx2　　　bbb　　　　0　　　350　　　400 ・・・

Ｂ列にはvlookupで参照されているとのことなので、省略します。
それで、Ｃ列ですが、東京ではじまるデータを集計すればいいので、
sheet1から
「Ａ列のコードが同じ」で
「Ｃ列の種類の最初の２文字がが集計表の１行目と同じ」場合の
「「Ｄ列の合計」をだせ！という配列数式を入れます。

{=SUM(IF(Sheet1!$A$2:$A$5000=$A2,IF(LEFTB(Sheet1!$C$2:$C$5000,4)=C$1,Sheet1!$D$2:$D$5000,0)))}

（配列数式はShiftとCtrlを押しながらenterキーを押します。そうすると「｛　｝」で囲まれます。）

Ａ列を参照するものを＄Ａとして列を固定し、
Ｃ列を参照するものは＄１として行を固定します。

それを集計表の集計したい部分にコピーすれば、ＯＫです。

もしもsheet1の行が増えた場合は、
集計表の$5000を$6000とかに一気に置き換えることができます。
むしろ最初から多めにしておけばいいと思いますよ。

また、念のため計算チェックと入力ミスの発見のため？
確認欄をもうけることをおすすめします。
たて・よこそれぞれの合計と、配列数式で出した合計の数値が同じになるかどうか。

その場合、例として
東京の合計は
{=SUM(IF(Sheet1!$A$2:$A$5000<>"",IF(LEFTB(Sheet1!$C$2:$C$5000,4)=C$1,Sheet1!$D$2:$D$5000,0)))}

コード1の合計は
{=SUM(IF(Sheet1!$A$2:$A$5000=$A2,Sheet1!$D$2:$D$5000,0))}

ということになります。
もし、行や列をsumで出した合計が、上記数式の数値と違っている場合は、どこか空白のデータがあるということになります。
（全く空白のセルがないデータを使うと仮定しての間違い探しです）

お礼

ありがとうございます！

おかげでできました！！
配列数式に関する知識がなかったのでわからなかったのですが、すごく便利ですね！！

本当にありがとうございました！！

回答No.4

miwaki

＃１の回答関連
シート２のセルA1に"=シート１!A1&シート１!C1"
シート２のセルB1に"=SUMIF(A:A,A1,シート１!D:D)"
として、セルA1とセルB1をコピーしてシート１のデータ列の数だけ下に貼り付けしてください。するとシート２のB列に要望の合計値が出ます。
しかしこれだけだと次の２点の問題が残ります。
１．シート１の行の削除や追加があったとき、シート２の関数のコピー貼り付け直しをしなくてはならない。
２．シート１のA列のコード番号とC列の区分わけの組み合わせで同じものが、繰り返しシート２上に出てしまう。
この２点を解決するために、マクロが必要だと申しました。
この２点が許容できるなら、このように関数だけで処理できます。

補足

ありがとうございます！
・・・すごいですね！ここまでできるとはびっくりしました！！

でも、どうやらマクロが必要なようです。
最終的には、コード番号順に並んでいる表に仕上げたいからです。

そして、
最初の質問内容にある
＞Ｃ列の種類わけしているものの中から、２つずつペアリングしての数字（Ｄ列）の合計を出したいのです。

これが私にはできませんでした。

たとえば、
「東京・新宿」「東京・八王子」「関東・さいたま」「関東・横浜」「関西・大阪」「関西・神戸」「九州・福岡」「九州・熊本」「東海・名古屋」「東海・静岡」
このように１０種類の分別があります。
シート１の一覧表ではこの１０種類を使用するのですが、集計表では「東京」「関東」「関西」「九州」「東海」この５種類で集計したいのです。

理想としては、集計表（シート２）では
Ａ列：コード番号　Ｂ列：名前（これはＡ列のコード番号にあわせてVLOOKUPを使用しています）　Ｃ列に「東京」の合計値　Ｄ列に「関東」の合計値・・・
といった具合にできるといいな、と考えています。

なんとかなりますでしょうか？
わがままなお願い、まことに申し訳ありませんが、よろしくお願いいたします。

回答No.3

imogasi

ご要望に近いところまでは下記行きつけます。
（例データ）
Ｓｈｅｅｔ３のＡ１：Ｃ７に
コード１ コード２ コード３
a x 8
b y 2
c z 3
b y 4
a x 5
f t 6
Ｓｈｅｅｔ４のＡ１：Ｂ２に
コード１ コード２
a x
を入れる。
Ｄ２セルに式=DSUM(Sheet3!$A$1:$C$70,Sheet3!C$1,A1:B2)
C70はデータがない下の方の行を指定してもエラーが出ないようです。
結果は１３と出ます。
これを組み合わせの数だけＳｈｅｅｔ４のＡ列Ｂ列に条件
をいれ、Ｃ列に関数式を入れれば合計がでます。
この方式の苦しいところは、見出し「コード１ コード２」を毎組み合わせ入れなくてはならないので１行おきになり困ることと、１行おきになるので式の複写が旨く行くかです。
Ｓｈｅｅｔ４のＡ１：Ｂ４に
コード１ コード２
a x
コード１ コード２
b y
としてＤ２をＤ４まで複写してみると
13
0
6
とＤ２とＤ４の結果は正しく出ます。
０はなんとか消せるでしょうが１行おきは間延びしてますね。
前から個人的に見出しは第１行を見て、条件はその各行を見るようにエクセル側が改めて欲しいと思ってますが、どうしようもない。

お礼

ありがとうございます。
１行おきってのは確かに痛いですね。

こちらでも、少し模索してみます。
ありがとうございます。

回答No.2

junjun2004

シート２に配列数式を入れれば簡単に出ると思いますが、
Ａ列が500種類、Ｃ列が10種類ですか？
集計表も5000行ということでしょうか？

補足

ありがとうございます。
シート１が５０００行あるのですが、
集計表は、コード別に整理したいと思っていますので、５００行程度で考えています。

もう、どうしていいかわかりません。
よろしくお願いします。

回答No.1

miwaki

VBAマクロで実現できそうです。エクセル関数のみでなきゃ駄目なんですか。
シート１のデータが変わるごとに自動的にシート２が更新されるようにしたいなら、関数でなくてもシート１にchangeイベントで上記のマクロが起動するようにしておけば自動的に更新されます。

補足

ありがとうございます。
ＶＢＡマクロはいまいちよくわかってないだけなんです。
まったくダメというわけではないのですが、changeイベントもよくわからないんです。
もし、可能でしたらもう少し細かく教えていただけるとたいへんありがたいです。
よろしくお願いします。