エクセル関数の質問

質問

こんにちは。エクセルについて教えてください。
まずは伝わりやすいように下記を見てもらえますか？(と思いましたが、逆にうまく伝わるか書いたあとに不安になりました)
↓シート１
___A______________B____C 　
1_[アイウエ]_[T]_[ ]
2_[カキクケ]_[T]_[ ]
3_[サシスセ]_[Z]_[ ]

↓シート２
___A_______B
1_[アイ]_[(1)]
2_[カキ]_[(2)]
3_[シス]_[(3)]

上段と左側のアルファベットと数字はセル番地です。
[__]がひとつのセルです。
シート１のC列は、空白です。

やりたいことはというと
１．シート１のA列にシート２の文字が含まれている場合にシート１のC列にシート２のB列の(1)～(3)を入れる。（Vlookup関数でできるかな？と思ったら、完全に同じものじゃないとうまく機能しないようなので・・・）

２．「１」の作業のあとに、シート１でB列が「T」＆C列が「(1)」のものをカウント。（Countif関数かな？と漠然と思いつつ、二つの条件の入れ方が分からずエクセル本を見ていたら、Dsum関数を見つけて「これ？」と思いましたが、それもよく分からず・・・）

よろしくお願い致します。

回答No.1

noname#70958 · ベストアンサー

１．
C1セルを
　=INDEX(シート2!$B$1:$B$3,MATCH(FALSE,INDEX(ISERR(SEARCH("*"&シート2!$A$1:$A$3&"*",シート1!A1)),),0))
として下方にフィル。

※
シート1!A列の値に対して、
シート2!A列の値をそれぞれ部分一致検索(SEARCH)
結果がエラーであるかどうかを確かめて(ISERR)
エラーでない最初の行位置を取得し(MATCH)
対応するシート2!B列の値を返す(INDEX)
-------------------------------------------
２．
任意のセルを
　=SUMPRODUCT((B1:B3="T")*(C1:C3="(1)"))
とする。

COUNTIF関数では残念ながら複数の条件を設定することはできません。

Excel2007であれば、
複数の条件を設定できるCOUNTIFS関数というものができたので、
　=COUNTIFS(B1:B3,"T",C1:C3,"(1)")
としてもOKです。
（ただし、Excel2003以前の環境で開くと機能しません）

データベース関数でもできますが、
見出し行を用意したり、検索条件(Criteria)を別途書き出す必要があり、
（説明が）大変なので割愛します。スミマセン。

以上ご参考まで。

お礼

ご回答ありがとうございます。
１）教えられた数式をコピーして、実際のリストにペーストして微調整をして使うことができました。正直、数式の中の関数を理解しきれていないのですが……ちょっと感動でした

２）こちらもコピペをしてみました。するとどうしてもうまくいきません。たぶん、シート１のC列に「(1)～(3)」に該当するものがなく「＃N/A」になってしまうところがあるせいだと思うのですが、どうすればいいのか分かりません。申し訳ないですが、もう一度助けてください！

よろしくお願い致します。

回答No.3

noname#70958

三たび#1です。

>「NOT」の前の「--」はハイフン二つでしょうか？
そうです。マイナス符号二つです。
論理値(TRUEあるいはFALSE)で返ってきた配列に対して、
マイナスを二度掛けることで1と0に変換しています。

>うまく機能しないのですが、なんででしょう？
う～ん、なんででしょうね(^^;;

こちらでテストした限りでは機能するんですけどね。
（Excel2000，2003，2007で動作確認）
------------------------------------------------------
以下、可能性、というより推測・憶測の類ですが…。

１．転記・修正ミスはありませんか？

　・B列に対する参照とC列に対する参照は
　　【行数が等しくなるように】設定してください。
　・B:BとかC:Cのように【列全体を指定することはできません】
　・カッコの数や位置に注意してください。

２．シート1のB列やシート2のB列は適切に入力されていますか？

　・元データに余分なスペース等は入っていませんか？

３．シート2のB列の値は見た目通りの値ですか？

　・このサイトでは「丸付き数字」（○の中に数字が入っている文字）を投稿すると、
　　自動的に「丸カッコで囲んだ数字」に変換されてしまいますが、
　　もし、実データが「丸付き数字」なのであれば、
　　数式の比較部分も「丸付き数字」に直す必要があります。

　・セルの書式設定（表示形式）を使って、
　　「数値を丸カッコで囲んで表示している場合」や
　　「負の数値を丸カッコで囲んで表示している場合」は、
　　見た目は文字列"(1)"のように見えても実際は1や-1といった数値データですから、
　　=SUMPRODUCT(--NOT(ISERROR(1/((B1:B3="T")*(C1:C3=1)))))
　　のように数値を指定して比較する必要があります。
------------------------------------------------------
うまくいかない状況について、下記の点を補足していただければ、
より状況がはっきりするかも知れません。

Q1.うまく機能しないというのは、
　・エラー値が返るということでしょうか？
　・エラーメッセージが出るということでしょうか？
　・それとも適切な値が返らないということでしょうか？

Q2.うまく機能しないのはA案だけですか？
　・B案，C案もダメですか？

Q3.うまく機能しない状況の
　・B列の値
　・C列の値
　・入力した数式

Q4.質問文のようにシート1のC列すべてに値が返る場合（該当ナシがない場合）
　・A案，C案の結果はどうですか？
　・当初の回答のように素朴にSUMPRODUCTを使った場合はどうですか？
------------------------------------------------------
実際にファイルを見ることができればおそらく一発で解決すると思うんですが…お互いにもどかしいですね^^;
取り急ぎご参考まで。

お礼

三度目のご回答ありがとうございます。
マイナス符号二つで1と0に変換って、ちょっともうついていけないんですけど、理解していないのに、ほしい結果になるっていうのもちょっと悔しい気もします……

会社では、教えてgooにアクセスできないので、お礼が遅くなってすみません。

実は２）のカウントは別シートに作成しようと思ったのですが、それでうまくいかなかったみたいです、たぶん。分からないんですけど。とりあえず、うまくカウントできました。ですがエラーが出たら修復できないと思います(汗)

何度もご丁寧にありがとうございます。
Kater_Kurzさんのように関数を使いこなせるようになるにはどうすればいいんでしょう？何からどう勉強していけばいいんでしょう？
ISERROR関数で(1/)の部分は何？って感じです。

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

回答No.2

noname#70958

#1です。
●Ａ案

　=SUMPRODUCT(--NOT(ISERROR(1/((B1:B3="T")*(C1:C3="(1)")))))

迂遠…というより少しテクニカルですが。
・条件に合致する場合　⇒　1
・条件に合致しない場合　⇒　#DIV/0!
・C列が#NAの場合　⇒　#N/A
としておいて、エラー値以外を数える。
-------------------------------------------------------
他のアプローチとしては…

●Ｂ案：１の数式で【#N/Aが返らないようにする】

　=IF(ISNA(MATCH(FALSE,INDEX(ISERR(SEARCH("*"&シート2!$A$1:$A$3&"*",シート1!A1)),),0)),"",INDEX(シート2!$B$1:$B$3,MATCH(FALSE,INDEX(ISERR(SEARCH("*"&シート2!$A$1:$A$3&"*",シート1!A1)),),0)))

　長いですね;;

●Ｃ案：２の数式を【配列数式】にしてIFを使う

　=SUM((B1:B3="T")*(IF(ISNA(C1:C3),0,C1:C3="(1)")))

　普通の数式は、数式を入力した後Enterキーで確定しますが、
　この数式は配列数式なので、CtrlとShiftを押しながらEnterで確定してください。

以上ご参考まで。

お礼

二回目のご回答ありがとうございます。
うまく機能しないのですが、なんででしょう？
A案の「NOT」の前の「--」はハイフン二つでしょうか？
もうちょっといろいろテストしてみたいと思います。
１）がうまくいったので、最悪手段としてはソートして、カウントすることもできますし・・・・