Excelの質問です。A列に条件を満たす文字列があれば、A～L列に網掛けor色をつけたいです。

質問

いつも皆様にお世話になっております。
今回も、Excelで困ってしまいまして(汗)
今、『ホテル.xls』というファイルのSheet1に、以下のようなデータが並んでいます。

ny1|メイフェア・ホテル・バリョーニ(ニューヨーク)
si1|ラッフルズ(シンガポール)
in1|アマンキラ(インドネシア・バリ島)
ny4|グラマシー・パーク・ホテル(ニューヨーク)
ba1|スコタイ(バンコク)
ny24|ウォルドルフ=アストリア(ニューヨーク)
ny31-h|ヘルムズレイ・パレス(ニューヨーク)
ny71-h|エセックス・ハウス(ニューヨーク)
ny2|モーガンズ(ニューヨーク)
ny3|リッツ・カールトン(ニューヨーク)
ca1|オーシャン・クラブ(カリブ海)
ch1|ホテル・ニッコー・シカゴ(シカゴ)
ch2|ドレイク(シカゴ)
ch-3|フォーシーズンズ・シカゴ(シカゴ)
・
・
・

“|(縦棒)”は、列と列の区切りです。

データは1000行以上あります(ToT)
A列にはIDのようなデータ、B列には、ホテルの名称がずらりと記載されております。
B列は全て、データが埋まっていますが、A列には空白のセルもあり、データは点在している状況です。
また、省略しますがC～L列には、近隣の観光地や料金などのデータが記載されています。

このデータの中から、A列を検索し、「ny*」「ch*」を満たすデータ、つまり、ニューヨークとシカゴのホテルのデータがあれば、その行データのA～L列全てに網をかけるか、濃い色をつけたいのです。

ただ、「ny*」「ch*」の中でも、例外として「ny-*」「ch-*」に関しては、網かけもせず、色もつけないようにしたいのです(>_<)

ny1|メイフェア・ホテル・バリョーニ(ニューヨーク)・・・A～L列まで網掛けor塗りつぶし
si1|ラッフルズ(シンガポール)
in1|アマンキラ(インドネシア・バリ島)
ny4|グラマシー・パーク・ホテル(ニューヨーク)・・・A～L列まで網掛けor塗りつぶし
ba1|スコタイ(バンコック)
ny24|ウォルドルフ=アストリア(ニューヨーク)・・・A～L列まで網掛けor塗りつぶし
ny31-h|ヘルムズレイ・パレス(ニューヨーク)
ny71-h|エセックス・ハウス(ニューヨーク)
ny2|モーガンズ(ニューヨーク)・・・・・・A～L列まで網掛けor塗りつぶし
ny3|リッツ・カールトン(ニューヨーク)・・・・A～L列まで網掛けor塗りつぶし
ca1|オーシャン・クラブ(カリブ海)
ch1|ホテル・ニッコー・シカゴ(シカゴ)・・・・A～L列まで網掛けor塗りつぶし
ch2|ドレイク(シカゴ)・・・・・・A～L列まで網掛けor塗りつぶし
ch-3|フォーシーズンズ・シカゴ(シカゴ)
・
・
・

このように、ニューヨークとシカゴの地名を示すローマ字2文字の後に、すぐ数字が来るデータにのみ、処理を施したいのです。

「すぐ数字が来るデータ」における“数字”は、3桁まであります。(例:ny102など)

マクロを記録してみたのですが、全然うまくいきませんでした・・・(ToT)
何かよい方法はありませんでしょうか？

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.5

imogasi · ベストアンサー

私はいつも質問には例を挙げてくださいと書いてますが、このケースは過剰かつ自己関係に偏った例の挙げ方です。
抽象化して適当数の例を挙げてください。
＞A列を検索し、「ny*」「ch*」を満たすデータを網掛け
＞A列が＞ny-*」「ch-*」に関しては、網かけもせず
で判ると思う。
ーー
エクセルの「書式」－「条件付き書式」は知っているのだろうね。質問異1言も出ないが。
ーー
知っておれば「数式が」で、式の書き方がどうなるかの問題だけ。
ーー
「含む」はCountif（条件に＊付き）が短いでしょう。Finｄ関数は長くなる。回答文字列を、空白で置き換えて長さの変化を見る手も有る。
ーー
例データ
式の確認のため
ny1 g
si1
in1
ny4 g
ba1
ny24 g
ny31-h g
ny71-h g
ny2 g
ny3 g
ca1
ch1 g
ch2 g
ch-3
右列の式は　
=IF(AND(OR(COUNTIF(A1,"ny*")>0,COUNTIF(A1,"ch*")>0),NOT(COUNTIF(A1,"ny-*")>0),NOT(COUNTIF(A1,"ch-*")>0)),"g","")
ｇは該当した行の意味。
これを条件付書式にもっていく。そのままではないことに注意。
まず関係列を含めてデータを全て範囲指定する。
「数式」がで
=(AND(OR(COUNTIF($A1,"ny*")>0,COUNTIF($A1,"ch*")>0),NOT(COUNTIF($A1,"ny-*")>0),NOT(COUNTIF($A1,"ch-*")>0)))
前記の式でIFを除き、かつ,"g",""の部分を除く。
またA1は$A1とする。（この意味がわかるかな）
あとは書式（セルのパターン色）の設定をして、OK
これでセルの値がｇ行に色がついた。ただしｇの列は無くても良い。
)
ｎｙなどはA列に有る例で説明した。あり場所が変わると式も変わる。

お礼

すいません、また例の提示がうまく抽象化できず、冗長になってしまいました(>_<)
それでも、回答していただき本当にありがとうございます！
M列に、imogasiさんに教えていただいた、

=IF(AND(OR(COUNTIF(A1,"ny*")>0,COUNTIF(A1,"ch*")>0),NOT(COUNTIF(A1,"ny-*")>0),NOT(COUNTIF(A1,"ch-*")>0)),"g","")

を入れて下にオートフィルすると、「A列において"ny*"と"ch*"を満たすデータ」全てに、"g"という文字が加わりました。

その後、

=(AND(OR(COUNTIF($A1,"ny*")>0,COUNTIF($A1,"ch*")>0),NOT(COUNTIF($A1,"ny-*")>0),NOT(COUNTIF($A1,"ch-*")>0)))

で条件付き書式を設定すると、うまくいきました！
A～L列全てが、塗りつぶされました(^_^;)

ただ、「ny31-h」「ch-3」のような、間に-(ハイフン)があるデータに関しては、塗りつぶしたくないのですが・・・「ch-3」は、塗りつぶすことなくうまくいったのですが、「ny31-h」「ny71-h」がある行は、塗りつぶされてしまいました(ToT)

たぶん、imogasiさんが書いてくださった、「～を除く」を指定する条件式、

・・・NOT(COUNTIF($A1,"ny-*")>0),NOT(COUNTIF($A1,"ch-*")

の辺りで、"ny-*"が、「ny31-h」「ny71-h」をカバーする条件ではないからだと思い、"ny*-h"と書きかえると、問題なくうまく動作しました(^_^;)

あと、imogasiさんが書いてくださった1番目の式は使わずに、2番目の式、

=(AND(OR(COUNTIF($A1,"ny*")>0,COUNTIF($A1,"ch*")>0),NOT(COUNTIF($A1,"ny*-h")>0),NOT(COUNTIF($A1,"ch-*")>0)))

だけ使っても、うまく塗りつぶすことができました！
本当にありがとうございました<m(__)m>

回答No.6

kmetu

順序が逆だったんですね。記載する時に間違ったみたいです。

> ただ、A列だけしか塗りつぶしてくれないのです・・・同時にL列まで塗りつぶしてほしいのですが、どうすればいいでしょうか(ToT)

=COUNTIF($A1,"ch*")
=COUNTIF($A1,"ch-*")
というようにA1のAの前に$をつけてください。

お礼

うまくいきました！
ありがとうございます<m(__)m>

回答No.4

Hachi5592

No.3の回答者です。
補足します。
「ny123-h」のように数字3桁の後ろに「-h」などが付く場合は
条件式の部分を以下のようにしてください。
「=AND(LEFT(A2,2)="ny",VALUE(MID(A2,3,4))<1000,VALUE(MID(A2,3,4))>0)」

条件式２のchについても同じです

回答No.3

Hachi5592

条件付き書式を使います。
行２の列Ａ：Ｌにいれる条件付き書式を下に書きますので他の行へは
この行の書式をコピーしてください。

＜条件１＞　（「」は式に含みません）
「数式が」　「=AND(LEFT(A2,2)="ny",VALUE(MID(A2,3,3))<1000,VALUE(MID(A2,3,3))>0)」
として書式を網掛け等に

＜条件２＞　（同上）
「数式が」　「=AND(LEFT(A2,2)="ch",VALUE(MID(A2,3,3))<1000,VALUE(MID(A2,3,3))>0)」
として書式を網掛け等に

以上、お試しあれ。

お礼

あれ、うまくいかないです・・・AからL列まで、データのある箇所全てを範囲選択した後、Hachi5592さんが書いてくださった、条件1の

=AND(LEFT(A2,2)="ny",VALUE(MID(A2,3,3))<1000,VALUE(MID(A2,3,3))>0)

で、書式を設定してみたのですが、A列の、ny*やch*に該当しない箇所が塗りつぶされてしましました(>_<)
たぶん、私のやり方に問題があるからだと思いますが・・・何しろ、条件付き書式というのは、今回初めて知りましたので(汗)

回答No.2

mshr1962

A:Lの範囲を選択した状態で「書式」「条件付き書式」

「セルの値が▼」の▼をクリックして「数式が▼」に変更
「=COUNTIF($A1,"ch*")=1」として書式ボタンでパターンを変更
追加ボタンを押し条件2にて
「セルの値が▼」の▼をクリックして「数式が▼」に変更
「=COUNTIF($A1,"ny*")=1」として書式ボタンでパターンを変更

変更するパターンが1種類でいいなら条件式を
「=COUNTIF($A1,"ch*")+COUNTIF($A1,"ny*")=1」
でOKです。
この設定は条件の列名であるAの前に$を付けて条件列を固定することで可能となります。

お礼

条件書式ってすごいですね！mshr1962さんが書いてくださった

=COUNTIF($A1,"ch*")+COUNTIF($A1,"ny*")=1

を入力すると、L列まで全部塗りつぶしてくれました(^_^;)

kmetuさんへの疑問も解決しました、$を付ければ選択範囲まるごと、A列に指定した条件に従ってくれるみたいですね。

ただ、「ny-*」や「ch-*」も塗りつぶしてしまったので、ここから何とか、これらの塗りつぶしを取り除きたいのですが・・・

=COUNTIF($A1,"ch*")+COUNTIF($A1,"ny*")=1

という式に、「"ch-*"と"ny-*"は除く」みたいな条件式、追加することはできないでしょうか？
図々しくてすいません、お気が向かれましたら、また回答いただければ幸いです(>_<)

回答No.1

kmetu

A～L列まで条件式書式を使って
数式が　=COUNTIF(A1,"ch-*")
網掛けOR塗りつぶしの指定
数式が　=COUNTIF(A1,"ch*")
網掛けOR塗りつぶしなしの設定
の順に作成してみてください。

お礼

初めて知りました、「条件付き書式」という便利な機能があるんですね(^_^;)
逆ではありませんか？
先に、

=COUNTIF(A1,"ch*")

で塗りつぶして、次に

=COUNTIF(A1,"ch-*")

で「塗りつぶしなし」で書式を設定すると、うまくいきました！「ch-*を除く、ch*のみ」塗りつぶすことができました！
ありがとうございます<m(__)m>

ただ、A列だけしか塗りつぶしてくれないのです・・・同時にL列まで塗りつぶしてほしいのですが、どうすればいいでしょうか(ToT)