共通の単語のセルを緑色に塗りつぶす方法

質問

いつもお世話になっています。VISTA、Office 2007 です。

語彙検索の途中で、二つの言葉の集まりを比較して共通部分を素早く取り出したいのですが、うまくいきません。

次のような表があるとき、MATCH関数を使い、

セルC1=MATCH(A1,B:B,0), D1=MATCH(B1,A:A,0),・・・などと各セルに入力しておくと、列C、列Dに次のような結果が出ます。

列A 列B 列C 列D
1 ada goo 3 5
2 dac xyz #N/A #N/A
3 cat ada #N/A 1
4 dog mat #N/A #N/A
5 goo tea 1 #N/A

ご存じの通り、C1の3は、adaは列A、Bに共通で、列Bの3行目にあることを意味します。#N/Aは共通する単語がないということです。

ところが実際に結果をチェックしてみると、

列A 列B 列C 列D
1 ada goo #N/A 5
2 dac xyz #N/A #N/A
3 cat ada #N/A #N/A
4 dog mat #N/A #N/A
5 goo tea 1 #N/A

セルC1が#N/A（正しくは３）、D3が#N/A（正しくは１）と間違って表示される場合があります。すべての場合でそうだというのではありません。ところが、こういうとき、B3のadaを消してもう一度adaと入れ直すと正しい結果になります。

どうしてこのように誤った表示になるのか分かりません。どうぞよろしくお願いいたします。

それが解決されたとして共通の単語のセルを緑色に塗りつぶすことができる方法があったらお教えください。

要は列AとBに共通の単語のセルに緑色を塗りたいだけなので、ほかの関数でできるようでしたらこれだけでも教えていただけるとありがたいです。

なお、私はいまだマクロがさっぱり分かりませんので、すみませんがマクロ以外の解決方法ということでお願いいたします。

集計の最終段階でこのような事態に陥り、大変困っております。なるべく早くご返事いただけると幸いです。勝手言ってすみませんが、ご教示、よろしくお願いいたします。

なお、投稿すると表が左詰に固まってしまうようですが、よろしくご判読ください。

回答No.4

KURUMITO · ベストアンサー

例に示されているようなデータでしたらＡ列を選択してから「ホーム」タブの「条件付き書式」で「ルールの管理」を選択し、「新規ルール」から「数式を利用して・・・・」を選びます。
数式には次を入力します。
=AND(OR(COUNTIF(B:B,TRIM(A1))>0,COUNTIF(B:B,A1&"*")>0),A1<>"")
「書式」をクリックして「塗りつぶし」のタブから緑色を選択してＯＫします。
次のＢ列を選択して上記と同様にしますが、数式には次を入力します。
=AND(OR(COUNTIF(A:A,TRIM(B1))>0,COUNTIF(A:A,B1&"*")>0),B1<>"")
これで目的は達せられることでしょう。

お礼

休日にもかかわらず教えていただきましてありがとうございました。

さっそくやってみたところ成功しました。下のような現象は発生しておりません。
skylark wrote:（質問文の中）
>セルC1が#N/A（正しくは３）、D3が#N/A（正しくは１）と間違って表示される場合があります。すべての場合でそうだというのではありません。

これまで、うまくいかなかった方法は、セルに何か目に見えない制御文字があるとかシート自体になにか問題があるというのではなく、私の入力の仕方に問題があったのではないかと思うようになり、仕事が一段落したらもう一度検証をしてみたいと思いました。

KURUMITOさん、そしてご教示のみなさん、どうもありがとうございました。この場をお借りして心よりお礼申し上げます。

回答No.3

tom04

こんばんは！
大きく外している可能性がありますので、
その場合は無視してください。

↓の画像のように表を作ってみて
数式を入れてみました。

質問内容の数式で問題なく希望の結果が出ると思います。

尚、A・B列の条件付書式の設定をすることと考えると
エラーを表示させるより空白の方が簡単だと思い、
次のような数式にしています。

画像のC1セルに
=IF(ISERROR(MATCH(A1,B:B,0)),"",MATCH(A1,B:B,0))
D1セルに
=IF(ISERROR(MATCH(B1,A:A,0)),"",MATCH(B1,A:A,0))

という数式を入れ、C1・D1セルを範囲指定した後に、オートフィルハンドルの（＋）マークでダブルクリック。

そして、A1・B1セルを範囲指定し
メニュー　→　書式　→　条件付書式で
「数式が」　→　数式欄に　=(C1)<>""　として　塗りつぶしのパターンで好みの色を選択

最後にA1・B1セルを範囲指定し、オートフィルで下へコピーして
「オートフィルオプション」で「書式のみ」を選択
これで完了です。

以上、参考になれば幸いですが、
最初に書いたように大外れの可能性もありますので
その場合はごめんなさいね。m(__)m

お礼

ご教示、ありがとうございました。確かにうまくいきました。しかし、

skylark wrote:
>セルC1が#N/A（正しくは３）、D3が#N/A（正しくは１）と間違って表示される場合があります。すべての場合でそうだというのではありません。ところが、こういうとき、B3のadaを消してもう一度adaと入れ直すと正しい結果になります。

という現象は変わりませんでした。なぜなのか、相変わらず首をひねっております。

しかし、ともかく、ありがとうございました。

回答No.2

imogasi

考えられるケースは
検索範囲の番地に＄がついてない（付いてないと式の複写で範囲がづれる）
これは質問では、Ａ：Ａのような書き方で逃れられているでしょう。
ーー
あとはデータのあとに余分な文字（画面では見えない、制御文字など）がついているのではないかな。
＝ＣＬＥＡＮ（Ａ１）といった関数があるから、制御文字を取り除いてくれる。
ほか＝ＴＲＩＭ（Ａ１）関数もある。
これらで質問で言う、不思議な行（セル）の２、３をやってみて、予想通りのＭＡＴＣＨ関数の値になるかどうか、チェックして、それらが原因と納得したら、対策を考えよう。
ーーー
それとＭＡＴＣＨやＶＬＯＯＫＵＰなどは２番目に出てきたもの（セル）は検知できない。
課題によっては、致命的になる。１つでも見つかればよい問題なのかな。
ーー
＃Ｎ／Ａは
=IF(ISERROR(MATCH(A2,$B$1:$B$4,0)),"",MATCH(A2,$B$1:$B$4,0))
のようなパターンの式で空白化が良く行われる。

補足

さっそくのご教示、ありがとうございました。
imogasiさん wrote:
>検索範囲の番地に＄がついてない（付いてないと式の複写で範囲がづれる） これは質問では、Ａ：Ａのような書き方で逃れられているでしょう。

この点ですが、どうも＄Ａ：＄Ａと入力した方が「精度」があがるようです。

imogasiさん wrote:
>＝ＣＬＥＡＮ（Ａ１）といった関数があるから、制御文字を取り除いてくれる。ほか＝ＴＲＩＭ（Ａ１）関数もある。

これらの関数はどこのセルに設定したらいいでしょうか？空白の列Cとか列Dに設定してみたのですが、変化はありませんでした。

imogasiさん wrote:
>それとＭＡＴＣＨやＶＬＯＯＫＵＰなどは２番目に出てきたもの（セル）は検知できない。課題によっては、致命的になる。１つでも見つかればよい問題なのかな。

アドバイス、ありがとうございました。今回は一つ見つかればよかったのでこの問題には抵触しませんでした。しかし、心にとめておきます。

いろいろ、ありがとうございました。

回答No.1

hige_082

単に
A列の条件付書式の条件に
=MATCH(A1,B:B,0)を
A列の条件付書式の条件に
=MATCH(B1,A:A,0)を設定するだけでは？

条件付書式が分らないということかな？
A列必要な行までを選択しておく
メニューの書式、条件付書式を選択
条件1の「セルの値が」を「数式が」に変更し
その右に数式「=MATCH(A1,B:B,0)」を入力する
[書式]ボタンを押し、書式を設定し[OK]、[OK]
これを、B列にも適用すれば終了

お礼

下記を除いて、うまくできました。緑色の塗りつぶしは手作業を覚悟していましたので自動的にやってくれてありがたいです。

skylark wrote:
>セルC1が#N/A（正しくは３）、D3が#N/A（正しくは１）と間違って表示される場合があります。すべての場合でそうだというのではありません。ところが、こういうとき、B3のadaを消してもう一度adaと入れ直すと正しい結果になります。

特別なことをやった覚えはないし・・・と首をひねっております。

いずれにせよどうもありがとうございました。