エクセルの関数（SUMPRODUCT?)

質問

ご存知の方がいらっしゃいましたら教えていただけたら幸いです。
　A列　B列　　C列　　D列…
1　ID　部名　課名　都道府県…
2　1　　A　　　a　　　北海道
3　2　　A　　　c　　　大阪
4　3　　A　　　b　　　東京
5　4　　A　　　a　　　東京
6　5　　A　　　a　　　福島
[シート名：入力]

　　A列　　B列　　C列　D列　　E列…
1　地域　　　a　　　b　　c　　　合計(←上記シート「入力」の課名)
2　北海道　2(Q2)　0　　0　　　2(Q3)
　　東北
3　東京　　1(Q1)　1　　0　　　2
4　関西　　0　　　　0　　1　　　1
[シート名：合計]

(Q1)=SUMPRODUCT((入力!$C$2:$C$6="a")*(入力!$D$2:$D$6="東京"))
で、地名を１つにしている場合は問題なく反映されています。

(Q2)★ここが一番聞きたいところです★
=SUMPRODUCT((入力!$C$2:$C$6="a")*(入力!$D$2:$D$6="北海道")*(入力!$D$2:$D$6="青森")*(入力!$D$2:$D$6="岩手")*(入力!$D$2:$D$6="宮城")*(入力!$D$2:$D$6="秋田")*(入力!$D$2:$D$6="山形")*(入力!$D$2:$D$6="福島"))
で、エラーがでてしまいます。（イヤミではないんですが…）東京など大きなところでは(1)の処理でよいのですが、例えばここだと北海道や青森、秋田…とあまりその都道府県だけでは数の少ないところは「地域」として「北海道・東北」とまとめたく、上の式では、

「aの課で、且つ北海道か、青森か、岩手か、宮城か、秋田か、山形か、福島であればカウントする」としたいのです。

ちなみに(2)の式を入力すると、エラーは出ないのですが該当するものがあるにも関わらずゼロ（２ではなく０）と表示されてしまいます。

(Q3)よって、本当であればここの関数も

「aの部で、且つaの課で、且つ北海道か、青森か、岩手か、宮城か、秋田か、山形か、福島であればカウントする」としたいところ、(2)でつまずいてしまっているため、E2のセルの表示「2」はそのシートのB2-D2のSUM関数で合計を出しているという状況です。

(3)は以上のような対処で問題はないかと思いますが、(2)の部分はどうしても表示されません。

前任者のデータを引き継ぐ形となり、去年とは少し違う形式になるに伴い出てきた問題です。できれば去年のものをできるだけ引き継ぎたいと考えているのですが…何か良い方法があれば教えて頂ければ幸いです。

回答No.1

mt2008 · ベストアンサー

なるべく元の式を活かすなら
=SUMPRODUCT((入力!$C$2:$C$6="a")*((入力!$D$2:$D$6="北海道")+(入力!$D$2:$D$6="青森")+(入力!$D$2:$D$6="岩手")+(入力!$D$2:$D$6="宮城")+(入力!$D$2:$D$6="秋田")+(入力!$D$2:$D$6="山形")+(入力!$D$2:$D$6="福島")))

もう少し簡素にするなら
=SUMPRODUCT((入力!$C$2:$C$6="a")*(ISNUMBER(FIND(入力!$D$2:$D$6,"北海道青森岩手宮城秋田山形福島"))))

でも、一番良いのは、入力シートの表に地域の情報を示す列を追加する事だと思います。

お礼

早速のご回答、ありがとうございます。

北海道～福島までを(( ))二重カッコにする、というところを見落としてました…目からウロコです。

また、２番目の式も大変参考になりました。私の中では新しいISNUMBERというのがでてきたのでビックリしましたが、またどこかの場面で利用させて頂きます。

「地域」の情報を入力する案は職場でもでたのですが、前任者がどのようにプログラミングしたのかの全体像をまだ把握し切れていないのか、入力方法が間違っているのか、エラーを起こしてしまったため、今回はあくまでSUMPRODUCTにこだわらせて頂きます。

ありがとうございました。

回答No.5

sige1701

こんな感じでもいいかな
=SUMPRODUCT((入力!$C$2:$C$6="a")*(入力!$D$2:$D$6={"北海道","青森","岩手","宮城","秋田","山形","福島"}))

お礼

ご回答、ありがとうございました。

地域だけ{　　}で囲む、というアイデアも目からウロコです…スッキリしますね。

的確なアドバイス、感謝致します。

回答No.4

ookami1969

確認してないので解決するかどうか微妙ですが
どこかに「北海道、青森、岩手、宮城、秋田、山形、福島」を入力して
仮に「北海道・東北」という名前をつけて
=SUMPRODUCT((入力!$C$2:$C$6="a")*(入力!$D$2:$D$6="北海道・東北")）
とされてはいかがでしょうか？
数式が すっきりして良いかと思いました。

お礼

ご回答、ありがとうございました。

別立てで北海道から福島までを入力し、それを名前付けしてからという案…トライしてみました。

私が持っているデータのなかでそれに置き換えた形でやってみたところ、今までの年度の分のデータの量やそれ以外での複雑な絡み合いがあるのか、それとも私の理解不足なのか、うまく反映されませんでした…

ただ、新しくエクセルを立ち上げて今年度の分として、教えて頂いたやり方でやった場合うまくいきましたので、今までのデータ量も考え今年から別ファイルで、というやり方も良いのかな、という新しいアイデアを頂きました。

ありがとうございました。

回答No.3

ka-zu-mama

下記の式でどうでしょうか？

=SUMPRODUCT((入力!$C$2:$C$6="a")*(入力!$D$2:$D$6="北海道")+(入力!$D$2:$D$6="青森")+(入力!$D$2:$D$6="岩手")+(入力!$D$2:$D$6="宮城")+(入力!$D$2:$D$6="秋田")+(入力!$D$2:$D$6="山形")+(入力!$D$2:$D$6="福島"))

お礼

早速にご回答くださり、ありがとうございました。

一瞬No.1で回答くださった方の式と同じかな？と思ったのですが、北海道～福島を(( ))二重カッコでくくっていないタイプなんですね。

じつは質問したデータは現在私がもっているデータを簡略化したもので、実際頂いたデータをコピペしつつこちらのデータに置き換えたところ、データ自体が複雑なのか、うまく反映されませんでした。

せっかくご回答くださったのに、申し訳ありませんでした。

回答No.2

wisemac21

論理式
1.AND条件は乗算
2.OR条件は加算
東北の各県と北海道を加算にするとどうですか

お礼

簡潔明瞭なご回答、ありがとうございました。わたしの入力方法が間違えているのか、イマイチ理解していないせいなのか、うまく反映されませんでした。現在エクセル関係の本を積み上げて学習中です…ゴメンナサイm(_ _)m