どの関数を使えばいいのかわかりません。

質問

『AとBを含む文字で、Dの数』という式を作りたいのです。

表の現状としては、

1│A│D
2│A│G
3│B│D
4│A│F
5│C│D
6│A│D
7│B│F

な感じ。

これの、左列のAとBを対象に、右列のDを抽出してその数を表示したいです。
Dが終われば今度はF、Gと条件を変えて数を出さなければいけません。
これが200個近いデータでの抽出なので、数えるのも一苦労です。

調べてIF、COUNTIF、NOT、SUMIFで挑戦しましたが、出来ませんでした……
抽出となるとVLOOKUPはどうかとも思いましたが、仕様的に違う。

昨夕からずっと悩んでます。
時間のある方教えてください。

回答No.3

MackyNo1 · ベストアンサー

＞『AとBを含む文字で、Dの数』という式を作りたいのです。

この条件ならExcel2007以降のバージョンをご使用ならSUMIFS関数で集計するのがお勧めです。

=SUM(COUNTIFS(A:A,{"*A*","*B*"},B:B,"D"))

実際の運用では、A,B,Dの部分はセル参照する数式（ワイルドカード部分は「*」を＆で連結する必要がある）にするとよいと思います。

＃Officeソフトはバージョンによって、使用できる機能や操作方法が大きく違いますので、質問の際には必ずバージョンを明記するようにしましょう。

お礼

回答ありがとうございます。
あ、バージョンの事、すっかり忘れていました！
以後、気をつけます。

早速教えていただいた式を入力してみました。
初めて数字が返ってきて喜びましたが、何故か数が合いません……使い方が悪かったのでしょうか。

この式の場合、ABが重なっていると重複で数えてしまうんでしょうかね。
そうすると納得出来ます。

回答No.13

coldblade

ご確認ありがとうございます。
恐らくOffice2013の64bitを使われていますでしょうか。

▼操作手順：バージョンを確認する
［ファイル］タブ－［アカウント］をクリック
　↓
［○○のバージョン情報］ボタンをクリック
　↓
［Microsoft ○○のバージョン情報］ダイアログでバージョンを確認
例えばWordの場合、下図の［ファイル］タブ－［アカウント］－［Wordのバージョン情報］ボタンをクリックする

http://technet.microsoft.com/ja-jp/library/ee681792.aspx

お礼

丁寧な説明、ありがとうございます。

調べた所、32ビットでした。

補足

理由はわかりませんが、プログラムがきちんと起動しました！

お騒がせしました。

回答No.12

coldblade

OSとExcelのバージョンは何でしょうか。
インストールが出来ていますか。メニューが出ていますか。
どのステップで止まっていますか。
こちらで試して成功したのですが...

お礼

再度ありがとうございます。
OSはWindows7、バージョンは2013です。
インストールは出来ています。
後はエクセルで使えるようにするだけなのですが、エクセルシート上で右クリックしても、読み込んでくれません。

補足

保存先が悪いのでしょうか？

現在、C:\Program Files (x86)に保存されています。

回答No.11

bunjii

>コピペという芸のない行動で申し訳ありませんが、やってみました。
>FINDの対象が　="―"　となり、結果が0になってしまいました。
それは何ですか？
論理が分からなければIF関数も扱えないでしょう。

式全体をコピー&ペーストしないと使えない状態なら応用もできません。
関数を覚える前に四則演算と論理演算について学習し直してください。

=SUMPRODUCT(ISNUMBER(FIND("砂",A$1:A$200))+ISNUMBER(FIND("シルト",A$1:A$200)),(B$1:B$200="砂質土")*1)
　　　　　　↓
=SUMPRODUCT(((ISNUMBER(FIND("砂",C$1:C$200))+ISNUMBER(FIND("シルト",C$1:C$200)))>0)*1,(D$1:D$200="砂質土")*1)

ISNUMBER(FIND("砂",A$1:A$200))+ISNUMBER(FIND("シルト",A$1:A$200)) → 2 と言う結果になることが重複であり、これを1にするための工夫として (ISNUMBER(FIND("砂",C$1:C$200))+ISNUMBER(FIND("シルト",C$1:C$200)))>0)*1 → 1 のように変更しています。
1つのセルについては複数の条件にマッチしても1を返さなければ集計誤差が生じます。

((1+1)>0)*1 → (2>0)*1 → 1
((1+0)>0)*1 → (1>0)*1 → 1
((0+0)>0)*1 → (0>0)*1 → 0

お礼

ご指摘ありがとうございます。

しかし、IF関数くらいなら理解できています。
今まで使う事なかった関数だったので、その辺での知識は乏しかったのは事実です。

改めて関数の事を調べ、理解しました。
初めの式が元々間違っていたようです。
お騒がせしました。

回答No.10

bunjii

>しかし、他の方と同様、重複してしまっているのでそれも含まれてしまいます。
1つのデータで"砂"と"シルト"が含まれるときに重複カウントします。
そのために"シルト"を含むサンプルデータが無いことを指摘しました。

SUMPRODUCT関数の第1引数（配列）を次のように変更すれば良いでしょう。

((ISNUMBER(FIND("砂",C$1:C$200))+ISNUMBER(FIND("シルト",C$1:C$200)))>0)*1
加算した結果が0より大きいときに1を返すようにすれば重複を避けられます。

お礼

再度ありがとうございます。
コピペという芸のない行動で申し訳ありませんが、やってみました。

FINDの対象が　="―"　となり、結果が0になってしまいました。
IFやVLOOKUP程度の関数しかわからない当方ですので、理由がわかりません…(苦)

回答No.8

bunjii

>例の式に当てはめていたアルファベットは以下のように置き換えました。
>A=砂(土でも可)
>B=シルト
>D=砂質土
貼付画像で"シルト"を含むサンプルデータがありません。
また、解像度が悪いので文字が判読できない部分があります。
下記のように空白で区切った文字列を提示して頂けるとサンプルを入力する手間が省けます。

礎湿り砂質粘土 粘性土
礎・粘土湿り砂 砂質土
礎湿り砂質粘土 粘性土

基本的にはSUMPRODUCT関数を使って集計しますが、チェック方法で文字列中の特定の文字列を検索するにはFIND関数またはSEARCH関数を使います。
しかし、検出されないときは#VALUE!（エラー）が返りますので数値化する必要があります。
FIND関数の結果をISNUMBER関数に渡してTRUEまたはFALSEに変換して乗算または加算で0または1にします。

前回の数式を次のように修正してみると良いでしょう。

=SUMPRODUCT(ISNUMBER(FIND("砂",A$1:A$200))+ISNUMBER(FIND("シルト",A$1:A$200)),(B$1:B$200="砂質土")*1)

お礼

画像はすみません、少し縮小がかかってしまったので見づらくなってしまいました……
あと、エラーが出る原因がわかりました。

どうも結合セルを式に入れるとダメみたいですね。
なので、強制的に一列にして今回の式で計算してみた所、きちんと返ってきました！

しかし、他の方と同様、重複してしまっているのでそれも含まれてしまいます。
その辺は引くよう、bunjii様が教えてくださった式を見ながら対処したいと思います。

長々と失礼しました。
そして、細かく優しい対応をありがとうございます！

回答No.7

MackyNo1

>この式の場合、ABが重なっていると重複で数えてしまうんでしょうかね。

基本的にどの式の場合も1つの文字列に「A」と「B」の両方の検索文字列が含まれていると重複カウントされます。

もし、このようなデータがあるなら、この組み合わせを引く必要があります。

=元の数式-SUM(COUNTIFS(A:A,{"*A*B*","*B*A*"},B:B,"D"))

お礼

やはり重複カウントしてしまうんですね。
早速コピペ、修正でやってみました！

何とかできました！
ありがとうございます。

多少別問題で数が合わない事もわかり、その辺は何とか自分で出来そうです、ありがとうございました！

回答No.6

mshr1962

No.5です。
＞しかし、何がいけないのか、VALUEと返ってきてしまいます。

COUNTIFS関数において範囲のセル数が不一致だとVALUEエラーとなります。
セルの行がずれても問題ないですが範囲のずれたものがあると思います。

これはSUMIFS関数、AVERAGEIFS関数、SUMPRODUCT関数のいずれも同じです。

=COUNTIFS(A1:A100,"A",B1:B100,"B") ＝＞正常な結果が表示
=COUNTIFS(A1:A100,"A",B1:B99,"B") ＝＞#VALUE!
=COUNTIFS(A2:A100,"A",B1:B99,"B") ＝＞ずれているがカウントは有効

お礼

行がずれて、その行数が合わないといけないわけですね。

しかし、見直したところ、行数は合っていました。

=COUNTIFS(A50:A60,"*砂*",B50:B60,"砂質土")+COUNTIFS(A50:A60,"*シルト*",B50:B60,"砂質土")

アルファベットを置き換えたものですが、上のように入力してありました。
それでもきちんと返してくれないのは何故でしょう……

質問ばかりですみません。

補足

お礼をしてしまったあとなので、こちらで失礼します。

エラーの原因がわかりました。
結合セルを式に入れるとエラーになるようです。

なので、一列になるよう打ち込んだ所、きちんと返ってきました！

しかし、他の方と同様、重複してしまっているのでそれも含まれてしまいます。
その辺は引くよう、mshr1962様が教えてくださった式を見ながら対処したいと思います。

長々と失礼しました。
細かく易しい対応をありがとうございます！

回答No.5

mshr1962

EXCEL2007以降なら
=COUNTIFS(A1:A7,"*A*",B1:B7,"D")+COUNTIFS(A1:A7,"*B*",B1:B7,"D")

EXCEL2007以前なら
=SUMPRODUCT(COUNTIF(A1:A7,"*A*")*(B1:B7="D"))+SUMPRODUCT(COUNTIF(A1:A7,"*B*")*(B1:B7="D"))

お礼

回答ありがとうございます。
バージョンが2013なので、上記の方を使わせてもらいました。

しかし、何がいけないのか、VALUEと返ってきてしまいます。
アルファベットと範囲を置き換えただけなのですが……

回答No.4

bunjii

>恐らく"A"が"*A*"に変えてしまったからだとは思うのですが……
A列の文字列の一部として"*A*"で比較してもエラーにはなりませんが目的通りの検出はできません。
質問ではA列が1文字として提示されていますので文字列の1部一致の場合は別の方法になります。

どのような関数式で集計が可能かを検証するのに少々時間が掛かります。
必要な要件を正確に提示してください。
実データの必要はありませんが同等のサンプルを提示して頂ければ検証してみます。
尚、Excelのバージョンの指定が無ければ最新版で検証します。

お礼

バージョンは2013です。

画像の方が現状の表となります。
この内の右２列を使って数を出したいのです。

例の式に当てはめていたアルファベットは以下のように置き換えました。
A=砂(土でも可)
B=シルト
D=砂質土

よろしくお願いします。

回答No.2

aokii

一行目は、一行追加して、項目行にし、
一行目のC1、D1、E1セルに、D、F、Gと入力して、

C2セルに、
=AND(OR($A2="A",$A2="B"),$B2=C$1)*1
と入力して、右と下にそれぞれドラッグコピーする。

抽出した数の合計表示は、各列の合計（Σ）で、算出します。

お礼

回答ありがとうございます。
そんな関数の使い方があるのですね！

しかし、表を崩したくないというのもあり、aokii様の式は、今回は見送らせていただきます。
次、同じような状況にあたった時、使わせていただきたいと思います。