[[20121208131951]]　『セル内の一部一致を参照したい』（くれいん）　ページの最後に飛ぶ

『セル内の一部一致を参照したい』（くれいん）
Excel2007、Windows Vista

 初めてお世話になります。
 早速本題なのですが、例えばセル内に
 「東京都立大学」とあるとします。また別のセルに
 「東京都立大学図書館」とあるとします。
 この「東京都立大学」という一つのセルの言語が入っているセルを参照していです。
 また「東京都立大学事務室」も検索対象です。

 ただ、この言語は例であげたように「東京都立大学」と決まっているわけではなく
 大量のセルから同じような一部一致の言語を探したいと思っています。

 上記のようなことは可能でしょうか？
 よろしくお願いします。

 どんな「言葉」を検索したいのか、はじめに明確にする必要があります。
 例えば「東京」、「大学」等は無数にヒットする可能性があります。

 無数にヒットするからどうだ、ということはありませんが、
検索したいコトバをどこに用意しておくのか、
検索対象となるデータはどのように用意されているデータなのか、
検索した結果をどのように出力（表示）したいのかなどがあいまいですね。
　
回答者は質問者の状況を再現して試した結果よさそうなものを
投稿しますから、あいまいな部分が多いと再現が出来ないので
回答もあいまいになります。
まず、そちらのデータが一部だけでも再現できるように表現してみましょう。
投稿する前に、自分の書いた文章だけで自分のデータが再現できるか
試してみてください。
　
部分一致の検索なら、
検索したい文字列セルを選択してCtrl+C、Ctrl+F、Ctrl+V、
オプションの「セル内容が完全に一致するものを検索する」がOffに
なっているのを確認して検索対象の範囲を選択してAlt+I　で検索できます。
　
（みやほりん）

お返事ありがとうございます。
やはり明確にしなければなりませんか…。
うまく伝えられないのですが、セル内にある言葉が完全一致で他のセルにあった場合に抽出したいです。

言葉がコレと決まっているわけではなく、ブックの中全てから重複セルを色付け等で明確にしたいです。
セル内の短い単語（東京都立大学）に対し他の言葉が付属したセル（東京都立大学【図書館】←この例でいくと図書館が付属部分）を参照したいです。
上手く伝えられなくて申し訳ございません。
でも、仰る感じだと特定の言葉を入れないとだめなようですね…。一気に検索をかけたかったのですが…。

一部ですが
「札幌大学」
「札幌大学○○研究室」
「札幌大学植物園」
「札幌ビール園」
「琉球大学」
「琉球大学附属植物園」
「福岡大学」etc…
というデータがあり、この場合だと「札幌大学」という一番短い言語のセルを検索する言葉とし
「札幌大学植物園」「札幌大学○○研究室」の2つのセルの重複を確認したいです。
もちろん「琉球大学」と「琉球大学附属植物園」も重複とみなします。
ただそれが、大量にデータがあるもので、一つ一つ言語を特定し検索をかけることは難しいです。

本当に上手く伝えられなくてすみません。
上記の例の場合「札幌」でかけてしまうと重複確認したくない「札幌ビール園」も抽出してしまうので
そうではなく、「札幌大学」という同じ建物を重複とみなしたいのです。

 ＞「札幌大学」という一番短い言語のセルを検索する言葉とし 
この部分を「エクセル君、勝手に見つけてね」というのは難しいんじゃ
ないでしょうか。探したい単語をどこかにリストにして
「札幌大学」 
「琉球大学」 
例えば、上記のリストの単語を含むセルは色付けする、または抽出する
というようなことは出来るかもしれません。
（みやほりん）

 フィルタオプションでの抽出例
【Ａ列】                【Ｄ列】　　　　【Ｆ列】
施設名　　　　　　　　　施設名　　　　　施設名
札幌大学　　　　　　　　札幌大学　　　　*札幌大学*
札幌大学○○研究室　　　琉球大学　　　　*琉球大学*
札幌大学植物園					 
札幌ビール園
琉球大学					 
琉球大学附属植物園					
福岡大学
　
一行目は必ず見出しとして使います。
Ｄ列に検索したい文字列を入力してリスト作成（セル参照でも良い）
Ｆ列に次の式
F2　=IF(D2<>"","*"&D2&"*"," ")　これを同時に検索したい単語数分用意。
　
http://www.eurus.dti.ne.jp/yoneyama/Excel2007/excel2007-filter2.html
上記のようなサイトを参考にして「フィルタオプションの設定」を行います。
　
リスト範囲にA列の範囲、検索条件範囲にF列の数式を入力した範囲を指定して
実行、抽出します。
　
（みやほりん）

 蛇足です。
例えば、次の三つのデータ、
　
「札幌大学」 
「札幌大学○○研究室」 
「札幌大学植物園」
　
これらは私を含めた部外者には「異なるデータ」です。 
これらを「重複したデータとみなす」というのは、[くれいん]さん側の
独自のルールですよね。
エクセルにもこのルールを教えてあげる必要があるので、
多少の入力作業があるのはやむをえないと思うのですが、いかがでしょうか。
（みやほりん）

みやほりんさん
お返事遅れてすみません。

＞「札幌大学」という一番短い言語のセルを検索する言葉とし
この部分を「エクセル君、勝手に見つけてね」というのは難しいんじゃ
ないでしょうか。
これが答えになりそうです。
例えば1万件あるうちの6000件が重複しているような、何が重複しているか分からない状態です。

ですがとても勉強にになりました。
丁寧にご指導いただき本当にありがとうございました。

 一発作業では難しいかもしれませんが、「同じフレーズを含むもの」を
カウントして「重複」とみなすデータにアタリをつける、というのは
COUNTIF関数で出来るかもしれません。
　
A列にチェックしたい元データがあるとして、作業列に
=COUNTIF($A$2:$A$10000,A2&"*")
のような式を入力、カウント数の多いものが「重複とみなしたいデータ」
である確率は高いですよね。
　
ご提示の例では「札幌大学なんとか」が３件あります。
で、さらに別の作業列で
=COUNTIF($A2,"札幌大学*")
のようにカウントすれば、「札幌大学なんとか」が「１」にカウントされますから、
ある程度見つけやすくはなると思われます。
　
機械的にやっちゃうとまずいかな、と思うのは、
「先頭４文字までは同じだけど実は違う」なんていうのが本当にないのか、
という点。最終確認は人間がやったほうがいいかなと。
(みやほりん)(-_∂)b

 横から失礼します

 やりたいことは、こんなことかな(あくまでも参考程度に)

 B1=IF(COUNTIF($A$1:$A$7,"*"&A1&"*")>1,"検査値",IF(SUMPRODUCT(COUNTIF(A1,"*"&$A$1:$A$7&"*"))>1,"重複有り",""))

 B1=IF(COUNTIF($A$1:$A$7,"*"&A1&"*")>1,"検査値",IF(SUMPRODUCT(COUNTIF(A1,"*"&$A$1:$A$7&"*"))=2,SUMPRODUCT(COUNTIF(A1,"*"&$A$1:$A$7&"*")*ROW($A$1:$A$7))-ROW(),""))

 または
 =IF(COUNTIF($A$1:$A$7,"*"&A1&"*")>1,"検査値",IF(SUMPRODUCT(COUNTIF(A1,"*"&$A$1:$A$7&"*"))=2,INDEX(A:A,SUMPRODUCT(COUNTIF(A1,"*"&$A$1:$A$7&"*")*ROW($A$1:$A$7))-ROW()),""))

 エラー処理や重複は考慮していません

By

 とりあえず、共通する3文字以上の文字列を赤くする

 Option Explicit

 Sub test()
    Dim rng As Range, r As Range, a() As String, txt, i As Long, m As Object
    Set rng = Range("a1", Range("a" & Rows.Count).End(xlUp))
    rng.Font.ColorIndex = xlAutomatic
    txt = Join$(Application.Transpose(rng.Value))
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "(\S{3,}).*(\1)"
        Do While .test(txt)
            i = i + 1
            ReDim Preserve a(1 To i)
            a(i) = .Execute(txt)(0).submatches(1)
            txt = Replace(txt, a(i), "")
        Loop
        .Pattern = Join$(a, "|")
        For Each r In rng
            For Each m In .Execute(r.Value)
                r.Characters(m.firstindex + 1, m.Length).Font.Color = vbRed
            Next
        Next
    End With
End Sub
(seiya)

コメント返信：

[ 一覧(最新更新順) ]

[[20121208131951]] 『セル内の一部一致を参照したい』（くれいん） ページの最後に飛ぶ

[[20121208131951]]　『セル内の一部一致を参照したい』（くれいん）　ページの最後に飛ぶ