Excel 2つ以上の異なる列で異なる条件が一致する個数

質問

たとえばA列のバックでC列の未着が一致する行数をカウントする方法がわかりません。
Excelに詳しい方誰か教えていただけないでしょうか？
=SUMPRODUCT((A1:A10="バック")*(FIND("未着",C1:C10)))
この関数を指定してみたのですが、#VALUEでエラーを返してきます。

サンプルデータ
バック 1001 到着
箱 1002 タグ未着
箱 1003 タグ未着
キャリア 1004 現在調査中
バック 1005 転送中
箱 1006 未着不明
バック 1007 到着
箱 1008 到着
キャリア 1009 到着
バック 1010 到着

回答No.9

ka-zu-mama · ベストアンサー

これでどうですか？

＜バック：未着＞
=SUMPRODUCT(($A$1:$A$65536=$E2)*ISNUMBER(FIND($F$1,$C$1:$C$65536)))
＜箱：未着＞
=SUMPRODUCT(($A$1:$A$65536=$E3)*ISNUMBER(FIND($F$1,$C$1:$C$65536)))
＜キャリア：未着＞
=SUMPRODUCT(($A$1:$A$65536=$E4)*ISNUMBER(FIND($F$1,$C$1:$C$65536)))

＜バック：到着＞
=SUMPRODUCT(($A$1:$A$65536=$E2)*ISNUMBER(FIND($G$1,$C$1:$C$65536)))
＜箱：到着＞
=SUMPRODUCT(($A$1:$A$65536=$E3)*ISNUMBER(FIND($G$1,$C$1:$C$65536)))
＜キャリア：到着＞
=SUMPRODUCT(($A$1:$A$65536=$E4)*ISNUMBER(FIND($G$1,$C$1:$C$65536)))

お礼

ありがとうございました。簡単な表を作成して使用して動作確認後、実際のデータに置き換えたところうまく動作しましました。

ありがとうございます。
関数の奥深さを感じました。

回答No.8

merlionXX

No5 merlionXXです。

> 部分一致の項目がうまくカウントされませんでした。

No5で回答した式

=SUMPRODUCT((A1:A10="バック")*NOT(ISERROR(FIND("未着",C1:C10))))

は、そのままコピペしていただけば部分一致でもカウントするはずです。（「Shift」+「Ctrl」+「Enter」を押す必要はありません。）
No5に画像を添付しておきましたが式の部分が切れましたので、再度貼ります。
ただし、質問で提示のデータには「バック」で「未着」がなかったので2番目と6番目の「箱」を「バック」に変えてあります。

お礼

なぜかゼロになる私のExcel。
コピペしてみたのですが・・・・・
もう一度チャレンジしてみます。

回答No.7

tom04

こんばんは！
色々回答が出ていますが・・・
確かにNo.2のasapinya様の回答でもちゃんと動作しますね！

他の方法の一例ですが・・・
ひと手間かかりますけど、作業列を使うやり方です。

↓の画像のようにD列挿入して
D1セルに=COUNTIF(C2:C2,"*未着*")
と入力してオートフィルで下へコピーします。
次に「品名」がバック・「状態」が未着を含んでいる物を表示させたいセルに
=SUMPRODUCT((A2:A11=A2)*(D2:D11=1))
としてみてはどうでしょうか？

考え方としては判り易いと思います。
以上、参考になれば幸いですが、
的外れの回答なら読み流してください。m(__)m

お礼

Counfifでフラグを立てて、その数をSUMPRODUCTで計算するのはわかりやすいですね。
これでちょっとチャレンジしてみます。
別のシートにデータを引っ張り出して、そこで計算かけてみます。

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

回答No.6

asapinya

２度目の書き込みです。

一通り目を通させていただきましたが、いずれの回答も問題なく動きますよ。
それでも「うまくカウントされない」とのお答えですが、配列数式はどのように入力されていますか？
「関数の挿入」は使わないでくださいね。基本的にすべて「手」入力です。
確定に「Enter」を押していませんか？もし押していたらそれは間違いです。
何度もお答えがあるように、式を入力し終えたら「Shift」と「Ctrl」を同時に押したまま「Enter」で数式を確定してください。

それでのダメな場合、元の表に問題があるかもしれませんね。
シンプルなデータを入力した表を使い、まずは試してみてください。

補足

おはようございます。
手打ちで[Ctrl]+[Shift]+[Enter]を押ってしながら、配列数式にしたのですが、カウント数が0となってカウントされていないようなのですが、表をチェックしてみます。
コピーして貼り付けてやっても見たのですが、やっぱり0になります。
実際のデータを計算すると14件あるはずなのですが・・・・

簡単な表を作成して、もう一度トライしてみます。ありがとうございました。

回答No.5

merlionXX

Find関数を使っているということは完全一致ではなく、「未着」を含むものを検索したいのですよね？
ならばこれでいかがでしょう？

=SUMPRODUCT((A1:A10="バック")*NOT(ISERROR(FIND("未着",C1:C10))))

でもバッグじゃなくバックなんですね、ご提示の例をバッグでやって０になり、あせりました（笑）

補足

ありがとうございます。上記の条件でやってみましたが、うまくカウントされませんでした。

部分一致の項目がうまくカウントされませんでした。
"が付いていたので、バッグに見えてしまいましたね。すみません。

回答No.4

noname#204879

》 =SUMPRODUCT((A1:A10="バック")*(FIND("未着",C1:C10)))
》 この関数を指定してみたのですが、#VALUEでエラーを返してきます。
貴方の気持ちは分かりますが、「(FIND("未着",C1:C10))」の部分が間違ってます。「未着」を含まないセルが #VALUE を返すので、上式もそうなるのです。

=SUMPRODUCT((A1:A10="バック")*NOT(LEN(C1:C10)=LEN(SUBSTITUTE(C1:C10,"未着",""))))
ただし、この式は 0 を返します。なぜなら、お示しの「サンプルデータ」では未着のバッグは存在しないから。

補足

ご指摘の通りバックで未着がないですね。すみません。サンプルデータを取得するときにそこまで気が回っていませんでした。

関数が難しすぎて、意味を理解するのが大変ですね。
関数使いこなせるとほんといろいろなことができるんですね。

回答No.3

ka-zu-mama

=SUMPRODUCT(($A$2:$A$65536="バック")*($C$2:$C$65536="到着"))
上記の式の結果は「３」となりますが、こういう意味でよろしいのでしょうか？

補足

最終的にA列とC列に記載されている文言の部分一致するものの行数を確認したいと思っています。

文言は、関数に直接入力ではなくできればセルの内容を参照したいと考えています。("*"&I4&"*")のようにできればうれしいのですが・・・・

回答No.2

asapinya

配列ならこんなんでいかがでしょう？
{=SUM(IF((A1:A10="バック")*(C1:C10="未着"),1,0))}
普通には入力できないので
=SUM(IF((A1:A10="バック")*(C1:C10="未着"),1,0))
を入力したあと[CTRL]+[SHIFT]+[ENTER]です。

他の方法としては、オートフィルタを使って「SUBTOTAL」関数で表示項目をカウントするのも良いかと思います。
=SUBTOTAL(3,A1:A10)
とかですかね。

この方法のほうが商品が変わっても使えるし良いかも。

補足

ありがとうございます。このやり方でもうまくカウントできないんですよ。
やっぱりフィルター使うほうが便利ですかね。
できればフィルターを使わない方法を考えているのですが・・・・

回答No.1

ookami1969

配列数式ですか？
であれば「Shift」+「Ctrl」+「Enter」押しましたか？

ちょっと方法は違いますが、フィルタを設定し「A列 箱」と「C列 未着を含む」で抽出して その数は範囲選択して数える
というのをマクロ記録しておいて、ボタンに登録するという方法はいかがでしょうか？

普段はフィルタを設定しなくても そこからマクロを記録しておけば
勝手にフィルタを設定する所から始めてくれるし、その方が良いのでは？

補足

自分が質問することで、いろいろな方から配列数式など新しい単語を教えてもらえて、ほんと勉強になります。
配列数式という言葉を知らなかったのですが、調べてみたらこの方法に該当します。

マクロも考えたのですが、リアルタイムに変化する情報をいかにビジュアル化するかということで悩んでいまして・・・・
アクセスなどで管理することも考えたのですが、現場ではExcelが一番使いやすいと・・・・

もう少し悩んでみます。
ありがとうございました。