エクセルの文字列検索

質問

Office2003のエクセルで例えば
　　Ａ　Ｂ
１　一　山
２　一　山
３　一　川
４　二　海
５　二　海
というような表があるとします。下のように
　Ｃ　Ｄ　Ｅ
１一　山　川
２二　海
Ｃ１のセルに「一」を入力すると、対応する山と川をＤ１とＥ１の
セルに表示してくれるような関数は無いでしょうか？
配列関数など色々探しましたが良いのが分かりません。
宜しくお願いします。

回答No.6

noname#79209 · ベストアンサー

チョットだけシートの変更をすれば．．．
Ａ列とＢ列の間に列を挿入します。
１行目の上に１行、表題行を挿入します。
Ｂ2に以下の数式を入れ,、下までコピーします。
=IF(ROW()<>1,IF(A1=A2,B1+IF(C1<>C2,1,0),1),1)

　　Ａ 　　　Ｂ 　 Ｃ
　　キー１　　　　キー２
１　一　　　　　　山
２　一　　　　　　山
３　一　　　　　　川
４　二　　　　　　海
５　二　　　　　　海

Ａ列Ｂ列Ｃ列を選択し、「データ」－「並び替え」をクリック。
第一優先列をＡ列、第一優先列をＣ列、「範囲の先頭行」で「タイトル行」をクリックして「・」を付け「ＯＫ」をクリックします。
Ｅ２に次の式を入れます。
=VLOOKUP(1,INDIRECT("$B$"&MATCH($D2,$A$2:$A$7,0)+1,TRUE):INDIRECT("$C$"&MATCH($D2,$A$2:$A$7,1)+1,TRUE),2,FALSE)
Ｆ２に次の式を入れます。
=VLOOKUP(2,INDIRECT("$B$"&MATCH($D2,$A$2:$A$7,0)+1,TRUE):INDIRECT("$C$"&MATCH($D2,$A$2:$A$7,1)+1,TRUE),2,FALSE)
Ｅ２とＦ２の違いは、「=VLOOKUP(1,」と「=VLOOKUP(2,」の違いだけです。
Ｇ２以降にも入れる場合は「=VLOOKUP(3,」などと1づつ増加させてください。

ただ、検索した値がないと「#N/A」が表示されてしまいます。

お礼

おおっ！すごい！できました！
一旦この列だけを別シートにコピーしてやってます！
「#N/A」はISERROR関数で消します。
助かりましたー＾＾ありがとうございます！！

回答No.11

ka_na_de

＃８です。

＞元データは触りたくないので、一旦別シートにコピーした後にマクロでできますか？

一旦別シートにコピーすることを含めて、マクロでできます。

しかし、私もvizzarさんの意見がもっともだと思いますので、
紹介は控えます。

お礼

回答ありがとうございます。
今回は関数で何とか解決できましたので
良しとします！
ありがとうございました^^

回答No.10

noname#79209

#6です。
これは私だけのポリシーなのですが、エクセルでは出来うる限りマクロは使用せずすませるようにしています。
自分だけで使うなら良いのですが、他のエンドユーザーが使うような場合、使用しているPCのセキュリティーレベルによっては、
「マクロが入っている。ウイルスかもしれない。」という警告メッセージが表示されてしまいます。
エンドユーザーの中には、この「ウイルス」という語句に過剰反応して、「ウイルスが入っている！！」と騒ぐ人が必ずいますので．．．

お礼

確かにvizzarさんのおっしゃられる通りだと思います。
私もユーザーからのヘルプではマクロを組むべきか悩む時があります。
ユーザーから、「前任者が作ってくれたマクロだけど、うまく動かないから見て」と言われたことがあって困った経験があります。
ユーザーヘルプの場合、必ず「これは私の特例ですので、別の方は対応できないです」と一言言ってますねー。

回答No.9

ka_na_de

＃８で回答したものです。

もし、検索することが目的ではなく、
C,D,E・・列のようなデータ形式に変換したいという内容であれば、
エクセルの「並べ替え」「フィルタ」「マクロ」を使えば、
比較的簡単にできますが・・・

やっぱり、エクセルの関数で検索することが目的なのでしょうか？

お礼

ふむふむ。。。
関数にこだわってはいないです。
関数だと無駄な処理なくすぐに表示されるから、他のユーザーにも
使えるかなって思いまして。。。
元データは触りたくないので、一旦別シートにコピーした後にマクロでできますか？

回答No.8

ka_na_de

やっぱり、エクセル上で検索できないとダメですか？
検索した結果を使って、次の処理をする予定ですか？

もし、そうではなく単に検索だけを簡単に実行できればいいのであれば、
私ならフリーの辞書ソフトPDIC用の辞書ファイルとして
変換して使います。

１）エクセルデータを，（カンマ）区切りのCSVファイルで保存
２）テキストエディターで[,]を[ /// ]に置換（１行テキスト形式）
３）PDICの辞書のコンバートでPDIC形式に変換
　　ここで、重複データは自動で削除され、同一見出しの複数データは
　　マージさせる事ができます。

とんちんかんな回答かも。その場合は無視してください。

お礼

回答ありがとうございます！
一応No６さんの方法でできました。
CSVで保存するという方法は思いつきませんでしたー。
色々あるんですねー。
ただ、できればエクセルだけで解決したいところです。。。

回答No.7

noname#79209

#6です。補足を．．．
B列が見えてしまって見栄えが悪いとお思いなら、数式を入力して下へコピーした後、B列の幅をゼロにしておけば良いでしょう。

E２、F2・・・に入れる式は、
=VLOOKUP(1,INDIRECT("$B$"&MATCH($D2,$A:$A,0)+1,TRUE):INDIRECT("$C$"&MATCH($D2,$A:$A,1)+1,TRUE),2,FALSE)
と
「MATCH($D2,$A$2:$A$7,0)」の部分を２箇所「MATCH($D2,$A:$A,0)」とした方が、
行が限定されなくて良いでしょう。

お礼

なるほどー
こちらの関数を使ったほうが良さそうですね。
こちらにしてみますー＾＾
ありがとうございます！

回答No.5

nobu555

#3です。
失礼しました。
重複するものは、一つにして表示させることも
条件にあったのを見落としていました。

ただ、関数だけでは、重複するものを除くことは、
非常に難しいと思われます。
作業セルを用いても、ネストの関係で限界があるのでは。

マクロなら、出来そうですが、
人に教えられるほど、知識が無いので、
アドバイスだけにしておきます。
changeイベントマクロというのが使えるかも。

参考までに

お礼

ありがとうございます！
そうなんですよー重複が厳しいですねー。
マクロですね。chengeイベントマクロというのは初めて聞きました。
勉強してみます！ありがとです＾＾

回答No.4

mu2011

NO2です。
ご質問の解釈を誤りました事、お詫びします。
一つの同一項目を抽出する数式の為、山・山・川となりますので読み捨て下さい。

お礼

いえいえ。回答ありがとうございます＾＾
もう少しなのですが、惜しい感じでした！
まだ解決には至っていないですが、関数では厳しそうですねー

回答No.3

nobu555

下記の数式で如何でしょう。
=IF(COLUMN()-3>COUNTIF($A:$A,$C1),"",INDIRECT("R"&MATCH($C1,$A:$A,0)-1+COLUMN()-3&"C2",0))

必要なだけ縦横にコピーしてください。

お礼

回答ありがとうございます！
やはりNo.２さんと同じく、コピーしても
E1が山になります・・・。川にはできないのでしょうか・・・。

回答No.2

mu2011

長い数式ですが、次の方法は如何でしょうか。
D1セルに=IF(COLUMN(A1)-1<COUNTIF(Sheet1!$A$1:$A$200,$C1),
INDEX(Sheet1!$B$1:$B$200,SMALL(IF(Sheet1!$A$1:$A$200=$C1,
ROW(Sheet1!$A$1:$A$200),999),COLUMN(A1))),"")
を設定し、縦横にコピーして下さい。
数式は、配列数式の為、入力完了時にshift＋crtl＋enterキーを同時押下して下さい。

お礼

回答ありがとうございます！
配列を横にコピーしてみましたが、E１に川が
出ません。E１も山になります。
なぜでしょう？？

回答No.1

tomomi5289

[vlookup]関数でできるのですが、
E1の川は表示できません。

A,Bのところのデータベース配列を見直すことをお勧めします。
そうすれば[vlookup]で十分対応できます。

お礼

早速の回答ありがとうございます。
このままの表では難しいですか・・・。
元の表は変更することができないので、このままで
何とかできないかと模索中です。
ありがとうございました！