sumproductの複数条件

質問

=SUMPRODUCT((貼り付け用!$J$2:$J$15000="3")*(貼り付け用!$K$2:$K$15000={"3","4","5","6","7","8","9"})*(貼り付け用!$I$2:$I$15000={"通過","NG"}))とやると#N/Aと表示されますが、何が間違っているのでしょうか？

回答No.4

imogasi

質問には、式だけ書くのでなく、そのやろうとしている内容・意味も併せて書いておくべきだと思う。
何万人の読者が、判っても、そうでなくても、読まされる仕組みだから。
ーー
J列が３、　　（A)
K列が３から９　（B)
I列が通過"か"NG　（C)
の３つをAND条件で判定したいということかと思う。
ーー
Aは該当が１つの値で問題ないとして、比較値が複数の場合、配列では表現させてくれないようだ。
Bはたまたま連続値で（３より大）＊（９より小）で２項に分ければよい。
ｃはOR条件なので、１つ１つ判別し、両者を加えて、全体を（）で囲って１項の扱いにする。
指定の値が何十何百になれば式に書いてられないが１０個ぐらいまでなら、列挙する方法があると、都合が良いと思うが、無いようだ。今回の既出ご回答の面々はエキスパートの方ばかりだが、解決策は上がってないようだ。
ーー
配列数式の場合
F列にデータがあるとしてF1:F10
通過
あ
い
通過
NG
あ
NG
通過
う
え
ーー
VLOOKUP用の検索表
J1:J2
通過
NG
K1:K10を範囲指定して
=IF(ISERROR(VLOOKUP(F1:F10,$J$1:$J$2,1,FALSE)),0,1)
を入れて、SHIFT,CTRL,ENTERを押すと
結果
1
0
0
1
1
0
1
1
0
0
こういうのが使えないかと思ったが、まだ小生では未消化。
VLOOKUPの代わりにMATCH関数でも同じか。
配列数式やSUMPRODUCTは行数が増えると処理が重たくなるようなので
その方面からは、即時反映を犠牲にしても、VBAでやることも考えられる。

回答No.3

Wendy02

こんばんは。

数字は、可変型だから、あえて、文字を入れなければ、引数は数値のままで良いはずです。また、数値を、文字列型の書式にしておくと、別の問題が発生します。だから、数値は数値して扱ったほうがよいです。

=SUMPRODUCT((貼り付け用!$J$2:$J$15000=3)*(貼り付け用!$K$2:$K$15000>=3)*(貼り付け用!$K$2:$K$15000<=9)*((貼り付け用!$I$2:$I$15000="通過")+(貼り付け用!$I$2:$I$15000="NG")))

なお、こういう数式で、15000行を使うなら、出来れば、データベース関数のほうがよいと思います。数式が軽くなります。また、この数式は、バージョンに依存します。

回答No.2

noname#176215

「"3"」となっていますが J列や K列の数字は文字列なのですか？
もし数値なら「""」は不要です。

それより気になるのは データ範囲です。もし本当に 15000行にデータが
埋まる可能性があるなら この数式は全く適していません。 重すぎて使い
物にならないでしょうから。

> 何が間違っているのでしょうか？
乗算している配列の数が一致しません。

=SUMPRODUCT(($J$2:$J$15000&$K$2:$K$15000={"33","34","35","36","37","38","39"})
*(($I$2:$I$15000="通過")+($I$2:$I$15000="NG")))

フリーズする可能性を考えるなら 範囲を減らした方がいいです。
あと 早い段階で数式と表を見直すことをお勧めします。

回答No.1

mu2011

配列の大きさが相違する為です。
貼り付け用!$K$2:$K$15000={"3","4","5","6","7","8","9"}はｎ行×７列、貼り付け用!$I$2:$I$15000={"通過","NG"}はｎ行×２列になるからです。
仮に数式を成立させたいのであれば、"通過"、"ＮＧ"で数式を２つに分けて加算したら如何でしょうか。
SUMPRODUCT((貼り付け用!$J$2:$J$15000="3")*(貼り付け用!$K$2:$K$15000={"3","4","5","6","7","8","9"})*(貼り付け用!$I$2:$I$15000="通過"))
＋
SUMPRODUCT((貼り付け用!$J$2:$J$15000="3")*(貼り付け用!$K$2:$K$15000={"3","4","5","6","7","8","9"})*(貼り付け用!$I$2:$I$15000="NG"))