エクセル関数でこんな判定？

質問

A1:B20にリストがあります。
A列は文字列です。
B列は数値です。

質問1
A列に入力があり、同行のB列が空白または0というものが1つでもあるかどうかを判定する関数を教えてください。

質問2
B列に入力があり、同行のA列が空白というものが1つでもあるかどうかを判定する関数を教えてください。

多分SUMPRODUCTを使うのだとは想像するのですが、手がでません・・・・。（泣）

回答No.1

noname#204879 · ベストアンサー

回答1
=IF(SUMPRODUCT((A1:A100<>"")*(B1:B100=0)),"あり","なし")

回答2
=IF(SUMPRODUCT((A1:A30="")*(B1:B30<>0)),"あり","なし")
ただし、Ｂ列は 0 以外の数値しか入力されないものとします。0 が入力されても無視します（空白扱い）。
スペースまたは文字を入力すると誤答します。

お礼

ありがとうございます。
これで明日の会社での仕事がはかどりそうです！
たすかりました。

ところで、質問2なのですが、B列だけでなくC～D列まで検索範囲を広げる場合は
=IF(SUMPRODUCT((A1:A30="")*(B1:D30<>0)),"あり","なし")
で良いのでしょうか？やってみたら良さそうなのですが仕組みがわかってないので自信がありません。

回答No.6

noname#262398

#5のNNAQです。

> カッコが一個多いようです。
おっと、
=SUMPRODUCT((A1:A10="")*(((B1:B10<>0)+(C1:C10<>0)+(D1:D10<>0))>0))

=SUMPRODUCT((A1:A10="")*(B1:D10<>0))
あるかないかの判定ならこれでも良いかも知れません。

お礼

ありがとうございます。

=SUMPRODUCT((A1:A10="")*(((B1:B10<>0)+(C1:C10<>0)+(D1:D10<>0))>0))
ですと、A列が空白、同行のB～D列に入力がある「行数」を返すんですね！
=SUMPRODUCT((A1:A10="")*(B1:D10<>0))
は、「セル数」を返すようです。

そういう理解でよろしいですか？
それならいろいろと使い分けが出来そうです。

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

回答No.5

noname#262398

> =SUMPRODUCT((A1:A10="")*(B1:D10<>0))

=SUMPRODUCT((A1:A10="")*(((B1:B10<>0)+(C1:C10<>0))+(D1:D10<>0))>0))
こんな感じでしょうか。

SUMPRODUCT関数を使うのもSUMの配列数式を使うのも同じです。
配列と論理式をちゃんと式に組み込められるかどうかですね。

> 判定する関数
じゃなくて確認するだけなら、ちょっと手間だけど
オートフィルターでA列から順に抽出すれば、一応判定は出来ますね。

お礼

ありがとうございます。

=SUMPRODUCT((A1:A10="")*(((B1:B10<>0)+(C1:C10<>0))+(D1:D10<>0))>0))　ではエラーになってしまいました。
カッコが一個多いようです。
=SUMPRODUCT((A1:A10="")*(((B1:B10<>0)+(C1:C10<>0))+(D1:D10<>0))>0)　としてみましたが反応してくれませんでした。（0表示のままです）

回答No.4

noname#204879

Ａ．[No.3回答に対するお礼]に対するコメント
　　「B～D列の“何れかに”入力があり」、かつ、A列に「同行のA列が
　　空白というものが1つでもあるかどうかを判定する」には、セル E1
　　に式 =(A1="")*COUNT(B1:D1) を入力して、此れを下方にズズーッと
　　複写しておけば、式 =IF(SUM(E1:E30),"あり","なし") で可能かと。

Ｂ．[No.3回答に対する補足]に対するコメント
　　「…の合計値」が「…と一致する」ことが、ご希望の判定に使える
　　とは思えないです。

お礼

ありがとうございます。

上記補足の最終行を以下の通り訂正します。

（希望は、A列の空白セルと同一行のB～Dに一つでも0以外の数値の入力があるかないかの判定ですから。）

補足

なんどもありがとうございます。

ご教示のA.の方法だと作業列が必要です。
また、試した結果、 =IF(SUM(E1:E30)の答えは=SUMPRODUCT((A1:A10="")*(B1:D10<>0))の答えと一致します。

合計値が一致すれば希望の判定に使えますが。
（希望は、A列の空白セルと同一行のB～Dに一つでも数値の入力があるかないかですから）

回答No.3

noname#204879

[No.1回答に対するお礼]に対するコメント、

》 質問2なのですが、B列だけでなくC～D列まで検索範
》 囲を広げる場合は
》 =IF(SUMPRODUCT((A1:A30="")*(B1:D30<>0)),"あり","なし")
》 で良いのでしょうか？

多分良くないと思います。
「C～D列まで検索範囲を広げる」という表現でなく「B～D列の“全てに”入力があり」とか「B～D列の“何れかに”入力があり」とかの条件をキッチリ述べてください。

何れにしても、貴方自身が「仕組みがわかってない」ものを鵜呑みに使うのはヤバイです。SUMPRODUCTも「敷居が高い」なら、作業列を幾つか設けて、IF関数だけで実現されることをお奨めします。

お礼

「B～D列の“何れかに”入力があり」が条件です。
よろしくお願いします。

>「仕組みがわかってない」ものを鵜呑みに使うのはヤバイです。

その通りですね。ありがとうございます。
理解できるよう努めます。

補足

A列が未入力で同行のD～D列のいづれかが０でないものの検索は、
=SUMPRODUCT((A1:A10="")*(B1:B10<>0))　でB列を
=SUMPRODUCT((A1:A10="")*(C1:C10<>0))　でC列を
=SUMPRODUCT((A1:A10="")*(D1:D10<>0))　でD列を　それぞれ検索すればいいと思うのですが、この３つの式の答えの合計値は何度か試しましたが、
=SUMPRODUCT((A1:A10="")*(B1:D10<>0))　の答えと一致するようです。

このような書き方はおかしいのでしょうか？

回答No.2

imogasi

配列数式による回答を上げます。ＯＲ条件があるので、＋を使います。
質問１
例データ
Ａ１：Ｂ１２
a 2
c
qw 0
qwe
a 2
s 3
dfg 1
d
f 0
g 2
h
n
どこでも上記以外のセルに
=SUM(IF((A1:A20<>"")*((B1:B20="")+(B1:B20=0)),1,""))
と入れて、ＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲを同時に押す。
先頭と後尾に｛・・｝がつき
、配列数式ですが
結果
７
第２，３，４、５，９，１１，１２行の７つです。
あるかどうかはこの数が＞０で判定します（略）
上記式の中の＋はＯＲ条件を表します。
質問２
=SUM(IF((B1:B20<>"")*((A1:A20="")),1,""))
と入れて、ＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲを同時に押す。
先頭と後尾に｛・・｝がつき、配列数式です。
ＳＵＭＰＲＯＤＵＣＴでは””の行は０として、チェックされるようで、＃１の式で答えは有無をチェックするときは正しいようだが、数を数えるときはどうなんでしょうか。

お礼

ありがとうございます。
配列数式はSUMPRODUCT以上に敷居が高いです。（泣）

勉強します。