[[20130820150620]]　『文字列の中から抜き出して集計したい』（ぐりむ）　ページの最後に飛ぶ

『文字列の中から抜き出して集計したい』（ぐりむ）

 お久しぶりです。
 前回は大変お世話になりました。
 今回もまた、お手間を取らせてしまいますが、
 付き合っていただけると嬉しいです。

 今回は１つのファイルにSheet1～3まであり、

 Sheet2のA列に、
 #ss  ?????????????????????????????????????????????????????????????AAAAA????.model /CATIA/ooo/k_kkk/pp_____/
 という文字列が何行もあります(全て異なる文字列)。

 この文字列から5文字(文字は違うが数は同じ)の文字列を、
 Sheet1のB3から下へ入れていきたいのと、
 /CATIAから始まって最後までのものを、
 Sheet1のC3から下へ入れていきたいです。

 そして、↑のデータを使ったとしたら、
      A    B          C                           D
  2        氏名       パスワード                  有無 
  3        AAAAA	    /CATIA/ooo/k_kkk/pp_____/
 こんな風になることが希望なのですが、

 Sheet3にはファミリーとなる5文字の文字列が何行も入っており、
 その中にSheet1のB列の文字列が入っていたらSheet1のD列(有無)に"○"、
 入っていなかったら"×"を入れたいです。
 (これはIF文使えばいけると思っています。)

 Sheet2からSheet1への集計？はどのような操作をすればよいでしょうか？
 もしくはVBAを使わないと無理でしょうか？
 どなたかご教授お願い致します。

【追記】

 色々いじってみたところ、Sheet2の行は、
 #ss  ?????????????????????????????????????????????????????????????AAAAA????.model /CATIA/ooo/k_kkk/pp_____/
 という集計したい行だけにすることができました。
 修正で注意書きを書きましたが消しておきます。

 ちなみに#ssで初めってはいますがssで始まっている所もあります。
 ?と?で挟まれている文字を抜き出そうと調べて実行しましたが、
 ?が多いせいなのか何故か上手くいきませんでした。

 =MID(A1,FIND("/",A1)-16,5)
 =MID(A1,FIND("/",A1),1000)
 最初の/からAAAAAまでの文字数が16ならば・・・

 （稲葉）

【追記2】

 何度も編集してしまい、申し訳ないです。
 試行錯誤して置換などをし、氏名となる5文字は全て抜き出すことができました。

 ●稲葉さん
 =MID(A1,FIND("/",A1),1000)
 を使ってみたところ、出来なかった/CATIA～が抜き出すことが出来ました！
 本当にありがとうございます！
 ちなみに=MID(A1031,65,5)を5文字の文はやったのですが、
 稲葉さんの式のほうが正確かもしれません。
 (スタートが66文字目もありそこは変えてやりました)

 残りは有無の方を調べることになりました。
 なんとか関数でできないか少しがんばってみます。

 (ぐりむ)

 Sheet3の5文字がSheet1のB列に抜き出した文字と完全一致なら以下の関数が役に立つと思います。
 VLOOKUP・・・検索値が見つからなかったら#N/Aを返す
 ISNA・・・対象が#N/AならTrueを返す

 頑張ってください。
 （稲葉）

 =IF(VLOOKUP(B3,Sheet3!$A$1:$A$946,1)=B3,"○","×")
 こんな感じの式をD3に入れて下にもダダーッと下げてみた所、
 "○"と"×"はしっかり識別されて映りました！
 (なんか綺麗じゃない関数になってしまいましたが・・笑)

 長々と書いてしまい、何度も追記を書いてしまってすみません。
 稲葉さん、関数まで教えて頂いて本当にありがとうございます。
 ISNA関数は知らなかったので教えて頂けて嬉しいです。
 そして閲覧してくださった方もありがとうございました(^U^)

 (ぐりむ)

 話は↓へ続きましたが
[[20130821084624]] 『IFとVLOOKUP関数で式を作りたい』（ぐりむ）
 リンク貼り付けがてら。。。

 ＞=IF(VLOOKUP(B3,Sheet3!$A$1:$A$946,1)=B3,"○","×")
 VLOOKUP関数の四番目の引数が省略されています。
 ここを省略すると TRUE が指定されたとみなされ、近似検索をすることになってしまいます。
 検索値が昇順に並んでいる事が前提で、完全に一致する値が無かった場合
 その次の値を返してくれます。

 そこで「=B3」と言う発想が出てきたのかもしれませんが。。。

 今回は、完全に一致するものを探せば良いので　四番目の引数は
 FALSE を指定しておかれるのが良いと思います。

 すると、今度は　同じものがある場合は　無事 =B3 が TRUE になるので「○」となりますが
 同じものが無かった場合 #N/A が返され IF関数全体でも #N/A エラーになります。

 つまり、VLOOKUP関数の結果が #N/A だった場合に「×」　それ以外の時「○」
 と言うIF関数にすれば良いですね。
 =IF(ISNA(VLOOKUP(B3,Sheet3!$A$1:$A$946,1,FALSE)),"×","○")

 ただ、今回はVLOOKUP関数の戻り値を何かに使う意図があるわけではなく
 「検索値があった場合となかった場合で違う結果を返してくれる」関数なら良いので
 MATCH関数でも良かったと思います。
 =IF(ISNA(MATCH(B3,Sheet3!$A$1:$A$946,0)),"×","○")
 MATCH関数だと、VLOOKUP関数より引数が一つ少なくて済みます。

 VLOOKUP関数では4番目の引数が検索方法を指定する引数でした。
 MATCH関数では、3番目の引数が同様の「照合の種類」を指定する引数になります。

 続きのスレで、COUNTIF関数で確認する案も出ましたが
 COUNTIF関数だと、MATCH関数よりさらに引数が一つ少なくて済みます。
 また、戻り値の個数をIF関数の論理式の部分で使う事になるので ISNA関数が不要になります。
 =IF(COUNTIF(Sheet3!$A$1:$A$946,B3),"○","×")
 式がかなりすっきりしますが　参照先（Sheet3!$A$1:$A$946）が別ブックに有った場合
 COUNTIF関数ではそのブックを開かないと計算されないので、注意が必要です。

 (HANA)

 ●HANAさん
 #N/Aが出てしまった理由が分かりました！
 =B3じゃ完全に一致しているわけじゃないですしダメでしたね。
 >>=IF(ISNA(VLOOKUP(B3,Sheet3!$A$1:$A$946,1,FALSE)),"×","○")
 この関数でも、求めていた結果が出ました！

 何故COUNTIF関数だと良いか、簡潔で済むことも理解しました。
 参照先（Sheet3!$A$1:$A$946）は、別ブックになることはなく、
 Sheet3にあるのでCOUNTIF関数で良かったです。

 丁寧に今までの関数のことを説明して頂けて嬉しいです、
 ありがとうございます。

 (ぐりむ)

コメント返信：

[ 一覧(最新更新順) ]

[[20130820150620]] 『文字列の中から抜き出して集計したい』（ぐりむ） ページの最後に飛ぶ

[[20130820150620]]　『文字列の中から抜き出して集計したい』（ぐりむ）　ページの最後に飛ぶ