こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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)
何かよい方法はありませんでしょうか?

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2009-07-16 23:20:51

QNo.5131560

困ってます

質問者が選んだベストアンサー

私はいつも質問には例を挙げてくださいと書いてますが、このケースは過剰かつ自己関係に偏った例の挙げ方です。
抽象化して適当数の例を挙げてください。
>A列を検索し、「ny*」「ch*」を満たすデータを網掛け
>A列が>ny-*」「ch-*」に関しては、網かけもせず
で判ると思う。
ーー
エクセルの「書式」-「条件付き書式」は知っているのだろうね。質問異1言も出ないが。
ーー
知っておれば「数式が」で、式の書き方がどうなるかの問題だけ。
ーー
「含む」はCountif(条件に*付き)が短いでしょう。Find関数は長くなる。回答文字列を、空白で置き換えて長さの変化を見る手も有る。
ーー
例データ
式の確認のため
ny1g
si1
in1
ny4g
ba1
ny24g
ny31-hg
ny71-hg
ny2g
ny3g
ca1
ch1g
ch2g
ch-3
右列の式は 
=IF(AND(OR(COUNTIF(A1,"ny*")>0,COUNTIF(A1,"ch*")>0),NOT(COUNTIF(A1,"ny-*")>0),NOT(COUNTIF(A1,"ch-*")>0)),"g","")
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
これでセルの値がg行に色がついた。ただしgの列は無くても良い。
)
nyなどはA列に有る例で説明した。あり場所が変わると式も変わる。

投稿日時 - 2009-07-17 23:48:12

お礼

すいません、また例の提示がうまく抽象化できず、冗長になってしまいました(>_<)
それでも、回答していただき本当にありがとうございます!
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>

投稿日時 - 2009-07-18 12:15:43

ANo.5

このQ&Aは役に立ちましたか?

6人が「このQ&Aが役に立った」と投票しています

回答(6)

ANo.6

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

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

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

投稿日時 - 2009-07-18 12:05:55

お礼

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

投稿日時 - 2009-07-20 23:51:34

ANo.4

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

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

投稿日時 - 2009-07-17 01:51:39

ANo.3

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

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

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

以上、お試しあれ。

投稿日時 - 2009-07-17 01:39:59

お礼

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

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

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

投稿日時 - 2009-07-18 11:43:10

ANo.2

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

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

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

投稿日時 - 2009-07-17 00:30:15

お礼

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

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

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

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

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

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

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

投稿日時 - 2009-07-18 11:33:19

ANo.1

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

投稿日時 - 2009-07-16 23:52:52

お礼

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

=COUNTIF(A1,"ch*")

で塗りつぶして、次に

=COUNTIF(A1,"ch-*")

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

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

投稿日時 - 2009-07-18 11:24:45

あなたにオススメの質問