[[20190625135739]] 『先頭の文字列を検索し、その下の数値のセルの色を』(しろごま) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『先頭の文字列を検索し、その下の数値のセルの色を変えるマクロ』(しろごま)

初めて投稿させていただきます。

日頃エクセルで血液検査データを扱うことが多く、各項目の基準値(上限・下限)によってセルの背景色を変える、というマクロを組みたいと思っております。

実際可能なのかわかりませんが、次のようなことができるのであれば教えていただけませんでしょうか。

	B	C	D	E	F	G	H
7		GLU	T.CHOL	TG	TP	ALB	GLOB
8		(5)	(5)	(5)	(3)	(3)	(3)
9	One	90 	121 	28 	7.6 	3.5 	4.1 
10	Tue	64 	150 	36 	7.4 	3.1 	4.3 
11	Three	71 	123 	16 	6.9 	3.2 	3.7 

日頃このような形でデータが出ます。項目(C列〜)は30ほど、検体数(9行〜)は20ほどあります。

しかし、GLUなどの項目は必ず同じ行ではないことがあります(例:TGの項目がD行だったりE行だったり)。
そのため、7行目の文字を検索(例:"GLU"を検索)し、その下の数値を判定(例:GLUであれば、70以下のセルを青くする→C10を青く)するなど、自動化できればと思っております。
その際に、一つのマクロで全ての項目の処理をできれば楽なのですが、そうすると何か不具合などありますでしょうか。ご教授いただければ幸いです。

マクロはごく簡単なものしか理解できておらず、複雑なものを一から書くことは厳しい状況のため、どうかご助力いただければと思い投稿致しました。

説明が拙く申し訳ございません。何卒よろしくお願い致します。

< 使用 Excel:Excel2013、使用 OS:Windows8 >


こんにちは
例えば、GLUが書かれている列を検索して、同列の以降の行で70以下を着色することは、for nextを用いて比較的簡単にできると思いますが、
GLU以降の行でGLUに関する値が(9行目ではD、10行目ではFなど)ばらばらに記載されている場合、条件分岐などが必要となってくるため複雑化します。

>複雑なものを一から書くことは厳しい
もし、コード案をお望みでしたら、もう少し詳しい説明を望みます。

(例えば、日頃からデータが出ると言うことであれば、同シートに日付を跨いで記入していくのか、別シートに記入するのか等)
(ケル) 2019/06/25(火) 14:44


マクロならば何とでもなりますが、上限値、下限値と7行目の項目文字列は、どこかにまとまって表にしていたりするでしょうか? 構成次第では、条件付き書式だけでも実現できそうに思いますよ。

マクロで実現する場合、データはひとつひとつ手入力なのか、別のブックから範囲コピペするのか、CSVファイル等から取り込むのか等によってどうするのがベストなのか変わってきますので、もう少し活用シーンの説明をお願いします。

あと、2行目の括弧は、マイナス値表現? B8セル部分が空欄なので、違いそう…。
(???) 2019/06/25(火) 14:49


条件付き書式の例なぞ。 例えば、Sheet2が以下のようになっていると仮定します。
	A	B	C
1		下限	上限
2	GLU	70	80
3	T.CHOL	122	148
4	TG	17	32
…

8行目は無関係として、C9セルから右下まで、行数は多めに範囲指定した後、「条件付き書式」で以下の条件時に着色するよう定義してみてください。

 =AND(B9<>"",OR(C9<INDIRECT(ADDRESS(MATCH(C$7,Sheet2!$B$3:$B$99,0)+1,2,1,1,"Sheet2")),INDIRECT(ADDRESS(MATCH(C$7,Sheet2!$B$3:$B$99,0)+1,3,1,1,"Sheet2"))<C9))
(???) 2019/06/25(火) 15:09

しまった、セルをずらしたときに、セル参照がずれていたようです。 以下に改めます。
 =AND(B9<>"",OR(C9<INDIRECT(ADDRESS(MATCH(C$7,Sheet2!$A$2:$A$99,0)+1,2,1,1,"Sheet2")),INDIRECT(ADDRESS(MATCH(C$7,Sheet2!$A$2:$A$99,0)+1,3,1,1,"Sheet2"))<C9))
(???) 2019/06/25(火) 16:27

皆様早速のご回答ありがとうございます。

ケルさん

GLU以降の行でGLUに関する値が(9行目ではD、10行目ではFなど)ばらばらに記載されている場合 ばらばらに記載されることはありません。列がそのまま動く、といった感じになります。

もし、コード案をお望みでしたら、もう少し詳しい説明を望みます。 ぜひコード案をいただけましたら幸いです。

日頃からデータが出ると言うことであれば、同シートに日付を跨いで記入していくのか、別シートに記入するのか データは別のシート(ファイル自体別)で毎回出ることになります。
ですがシート名は毎回同じのため、使い回しはできると思います。

以下に改めて詳細を記載します。

Sheet1

	B	C	D	E	F	G	H
7		GLU	T.CHOL	TG	TP	ALB	GLOB
8		(5)	(5)	(5)	(3)	(3)	(3)
9	One	90 	121 	28 	7.6 	3.5 	4.1 
10	Tue	64 	150 	36 	7.4 	3.1 	4.3 
11	Three	71 	123 	16 	6.9 	3.2 	3.7 
※Sheet1 8行目のカッコ数字に意味はありません。

項目はC〜AD、検体は9〜28までありますが、今はこちらで例をいただければと思います。

毎回同じ列に同じ項目が入れば条件付き書式で事足りるのですが、

	B	C	D	E	F	G	H
7		GLU	TG	T.CHOL	TP	ALB(G)	GLOB
8		(5)	(5)	(5)	(3)	(3)	(3)
9	One	90 	28 	121 	7.6 	3.5 	4.1 
10	Tue	64 	36 	150 	7.4 	3.1 	4.3 
11	Three	71 	16 	123 	6.9 	3.2 	3.7 
このように列がずれることがしばしばあります。
そのため、列の項目名で検索して自動化して書式変更ができるマクロが使えればとても助かります。

各項目の上限下限は仮に以下のようにします。

	GLU	T.CHOL	TG	TP	ALB(G)	GLOB
上限	100 	140 	30 	7.5 	3.5 	4.0 
下限	70 	120 	20 	7.0 	3.0 	3.5 

上限を超えた場合、セルの背景をRGB(255,200,200)に。
下限未満の場合、セルの背景をRGB(200,255,255)に。

こうした形で大丈夫でしょうか?
他に不足している情報があればおっしゃってください。


???さん
上限値、下限値と7行目の項目文字列は、どこかにまとまって表にしていたりするでしょうか? 表にはしておりませんが、することは可能です。

マクロで実現する場合、データはひとつひとつ手入力なのか、別のブックから範囲コピペするのか、CSVファイル等から取り込むのか データはエクセルファイル(.xlsx)でもらう形になります。
もらうデータのため、手入力ではなく入力された状態で手元に来る形になります。
そのため、マクロを設定したファイルを用意し、そこにデータのシートをコピーしてマクロを使用する方法になるかと思っていました。

条件付き書式でのご提案までありがとうございます。
今日は時間がなさそうですので明日試させていただきます。
(しろごま) 2019/06/25(火) 16:54


半角不等号を使ってしまったせいでレイアウトが見づらくなってしまいました。申し訳ございません。
(しろごま) 2019/06/25(火) 16:56

上限越えと下限未満で色を変えるのですね。 私が書いた例だと、一緒に判定しているので1色です。 上限判定と下限判定で式を分けて、2つの条件付き書式を設定してみてください。
 青: =AND(B9<>"",C9<INDIRECT(ADDRESS(MATCH(C$7,Sheet2!$A$1:$A$99,0),2,1,1,"Sheet2")))
 赤: =AND(B9<>"",INDIRECT(ADDRESS(MATCH(C$7,Sheet2!$A$1:$A$99,0),3,1,1,"Sheet2"))<C9)

列の違いは判りましたが、開始行は不変でしょうか? こちらもばらばらとなると、データ範囲を手動で指定して、対象シートに値貼り付けする方が楽に思います。 値貼り付けなら、条件付き書式が有効そうです。
(???) 2019/06/25(火) 18:30


???さん
>開始行は不変でしょうか?
行は不変です。
いただいたコードを試させていただきました。条件付き書式でここまでできるのですね!
おっしゃる通り、値の貼り付けで使用できそうです。ありがとうございます。
(しろごま) 2019/06/26(水) 12:09

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.