エクセルの関数（～以外）

質問

http://okwave.jp/qa4938678.html
にて質問させて頂いた者です。

また、ご存知の方がいらっしゃいましたら教えていただけたら幸いです。

　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　　　中国
7　6　　A　　　a　　　台湾
[シート名：入力]

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

(Q1)=SUMPRODUCT(入力!$C$2:$C$7="a")-((COUNTIF(入力!$H$2:$H$7,"関東")+COUNTIF(入力!$H$2:$H$7,"関西")))
と入力すると、本来の答えは「２」のはずがマイナスと出てきてしまいます。

求めたい式は

「a員であって、関東と関西以外の取引先」です。

メインの取引先が「関東」と「関西」で、一方で海外の取引先が（この表からは伺えませんが…）あまりないのですが、地域が広く数が少ない（例えばアメリカ：１、タイ：１、といった具合）ので、「その他」という項目をたて、そこに全て落とし込みたいと考えています。

前任者のデータを引き継ぐ形となり、去年とは少し違う集計方法・データ抽出になるに伴い出てきた問題です。

前任者はSUMPRODUCT関数を多用しているので、できれば去年までのデータに干渉しないかたちでできるだけおおくの部分を引き継ぎたいと考えているのですが…何か良い方法があれば教えて頂ければ幸いです。

回答No.3

merlionXX · ベストアンサー

> 「a員であって、関東と関西以外の取引先」

課員がC列、取引先がD列なら

=SUMPRODUCT((入力!$C$2:$C$7="a")*(入力!$D$2:$D$7<>"関東")*(入力!$D$2:$D$7<>"関西"))

です。

お礼

早速のご回答、しかも簡潔にありがとうございます。また、列の解釈もして頂き、すみませんでした…

修正したつもりだったんですが、コピペをした部分が多少残ってしまいました…失礼しました。

回答No.7

cafe_au_lait

すみません、シート名が間違っていました。

=COUNTIF(入力!$C$2:$C$7,"a")-SUM(合計!$B$2:$B$3)

No.5さんが答えられているとおりです。

回答No.6

imogasi

3条件の件数カウントか合計か、はっきりしないが
引き算をする必要はなくて、ＳＵＭＰＲＯＤＵＣＴ関数の条件に組み込めば仕舞いではないのか。
実例の書き方がややこしくて、良く質問が読めてないが。
例データ
Ａ１：Ｃ７
課員 取引先 計数
a 関西 10
c 関東 8
b アメリカ 7
a 関東 6
a 中国 5
a 台湾 4
ーーー
式
=SUMPRODUCT((A2:A7="a")*(B2:B7<>"関東")*(B2:B7<>"関西")*(C2:C7))
結果
９
aの中国と台湾を足している。
これでよいのでは。
ーーー
質問にミスリードされて混乱しちゃう。
＞a員であって、関東と関西以外の取引先」です
と言えば、取引先名を思っちゃう。
取引先らしいデータが無い。
「a員であって、関東と関西以外の取引先のＸＸを○○したい」とはっきりさせるべきでは。
また質問の例でシート名は上に書くのが適当（判り易い）と思う。
ーー
a氏以外もいて、それの計数も出したいらしい。
であれば、C$2:$C$7="a"と具体的に（リテラルと言う）例だけではなく
総合的に質問しないといけないのでは。
下記の例の数字が入ってない段階の表を示し、そこに入れる式を
全般的に聞くべきだろう。
それは前の質問の時から言えることではないか。
ーー
Ｅ列　　Ｆ列　　Ｇ列　　Ｈ列
ーー a b c
関東 6 0 8
関西 10 0 0
その他 9 7 0
a,b,cと関東、関西（その他）は手入力しておく。
Ｆ２の式　=SUMPRODUCT(($A$2:$A$7=F$1)*($B$2:$B$7=$E2)*($C$2:$C$7))
Ｈ２まで式複写。
Ｆ２：Ｈ２の式をＦ３：Ｈ３まで式複写。
Ｆ４の式　=SUMPRODUCT(($A$2:$A$7=F$1)*($B$2:$B$7<>$E$2)*($B$2:$B$7<>$E$3)*($C$2:$C$7))
Ｆ４をＨ４まで式複写。
ーー
このように出来るだけ式の複写で、正しい答えを出そうとすると、＄の有無、＄の場所が大切で、エクセルの相当の経験がいるのだ。

回答No.5

sige1701

何を質問しているか不明ですし、回答もでていますので参考までに

B4=COUNTIF(入力!$C$2:$C$7,B1)-sum(B2:B3)
こんな感じで良いのでは

回答No.4

cafe_au_lait

=SUMPRODUCT((入力!$C$2:$C$7="a")*(入力!$D$2:$D$7<>"関東")*(入力!$D$2:$D$7<>"関西"))

自分なら、
=COUNTIF(入力!$C$2:$C$7,"a")-sum(入力!$B$2:$B$3)
とします。

お礼

早速お返事いただきまして、ありがとうございました。

そうなんです、もしもSUMPRODUCT以外で出来れば・・と考えておりました。ただ、前任者のデータを引き継いだかたちで、その式がものすごく複雑（私の許容をこえてまして…）なものですから、新しい式などを打ち込んだりすると他の箇所でエラーがでてきてしまう場合がありました。

ただ、今回に限っては（今のところ）他の箇所でのエラーが出ていない様子なので、頂いた

=COUNTIF(入力!$C$2:$C$7,"a")-sum(入力!$B$2:$B$3)

で入力してみたのですが、なぜか正しい数値が出てきません（仮に、その数値をここでＸとします）。

例えば

=COUNTIF(入力!$C$2:$C$7,"a")-SUM(入力!$D$2:$D$7,{"関東","関西"})

という式も前述の式と同じ内容でしょうか？答えが同様にＸとでてきたので。。。

反応が遅くなる、と伺いましたのでできればSUMPRODUCTから離れたいと考えているのですが、私の式の入力が間違ってますでしょうか。

回答No.2

mt2008

こういうことですか？
H列をD列に直してあります。
=SUMPRODUCT((入力!$C$2:$C$7="a")*(入力!$D$2:$D$7<>"関東")*(入力!$D$2:$D$7<>"関西"))

SUMPRODUCTを多用しすぎると、重くなりますし、判りづらくなりますよ。

お礼

昨日に引き続き、今日もご回答、ありがとうございます。

そうなんです…反応が遅い、と思ったらやっぱりSUMPRODUCT関数は重いんですね、理由がわかりました。

この質問に関しては割りと他のデータとは影響を受けないところにあるようなので、他の方に教えて頂いたCOUNTIF等、を使ってみたんですが、データの干渉なのかそれとも私の入力が間違っているのか、正しい答えが導き出されません…

ちなみに、打ち込んだ式は

=COUNTIF(入力!$C$2:$C$7,"a")-SUM(入力!$D$2:$D$7,{"関東","関西"})
あるいは
=COUNTIF(入力!$C$2:$C$7,"a")-SUM(入力!$D$2:$D$3)
です。

お手数をおかけして、申し訳ありません。

回答No.1

A88No8

こんにちは
例では取引先はＨ列ではなくＤ列になっているのでCOUNTIFへの検索範囲の与え方が違っている．．ってことではないですよね(^^;

お礼

早速のお返事、ありがとうございます。

そうでした…列が違ってましたね、気がつきませんでした。。。

実際の元のデータをそのまま載せるわけにもいかず、加工したかたちにデータはしたのですが、式はすべて修正し忘れてしまいました、申し訳ありませんでした。列はここに記載したもので、式の方が間違いということでご回答いただければ幸いです。