【Excel】条件付き書式設定を行数分コピペするExcelVBAサンプル

2020年10月15日2024年1月24日

今回使用する表は、「種牡馬別馬齢別勝利数一覧表」

馬のピークをザックリ把握する

種牡馬の勝利数を馬齢別に集計することで、馬のピークをザックリ把握したい。

ＧＩ戦の単勝を決める最終段階では結構な有益な情報だったりする。

結果DをAccessでクロス集計し、該当の表を作成

※Accessの使用方法はまた別の機会もしくは他のサイトでご確認下さい。

今回はExcelの条件付き書式設定を複数回コピペするVBAサンプルの説明になります。

Access クロス集計の設定サンプル

Access クロス集計のサンプル表示

これを選択して、Excelにコピペする。

馬齢のピークを探りたいだけなので”せん馬”は非表示にして牡馬と牝馬だけの表示にしてみる。

また列幅の関係上、牡７、牝６までの表記にしている。

「種牡馬別馬齢別勝利数一覧表」をタグ変換してWordpressに貼る

※無料ブログで試したところタグ変換すると、文字数オーバーでどのブログにも入らなかったので内容は薄いもののWordpressでテストしたくなって記事を書いているというのが本当のところ。テストだから途中できることもなく、２年分の集計をそのまま表示させているわよ♡内容は個人的にデータを作成しているので、正確な数字とは多少異なるけども種牡馬のピークの傾向を見るという点ではあながち間違っていないと思うので、興味がある方は参考にしてみてね～(^_-)-☆

父	勝数	牡2	牡3	牡4	牡5	牡6	牡7	牝2	牝3	牝4	牝5	牝6
American Pharoah	71	76	356	278	206	61	50	43	145	55	84
Animal Kingdom	22		10	12
Australia	4								4
Authorized	4		3
Awesome Again	1	1
Bernardini	57	6		8		21			10	12
Blame	13								13
Bodemeister	2								2
Cairo Prince	47		6					6	35
Camelot	11							4	7
Candy Ride	10								10
Cape Blanco	7			7
Carpe Diem	4								4
Central Banker	7		7
City Zip	3
Closing Argument	11
Congrats	1	1
Constitution	13	6	7
Creative Cause	9				9
Curlin	22	5	8						9
Daiwa Major	52	3	23					5	7	14
Dansili	29									29
Dark Angel	8		6						2
Dawn Approach	20	1
Declaration of War	53	16	24						13
Distorted Humor	38	8	24						6
Dubawi	61	1	23		21				16
Elusive Quality	2								2
Emcee	4		4
Exceed And Excel	16	2	6	8
Fastnet Rock	57		12	12	11			6	8
Fed Biz	1		1
First Samurai	23		2	10	11
Flatter	3								3
Frankel	130	5	18	7		22		7	43	19	9
Ghostzapper	28								4	24
Giant’s Causeway	13		8	5
Gleneagles	13	13
Golden Horn	23	5	18
Goldencents	1		1
Graydar	3								3
Hansen	8										8
Hard Spun	2	2
Harlan’s Holiday	24										24
Hat Trick	5		5
Haynesfield	10				10
Heart’s Cry	8		3						5
Henny Hughes	24			12	12
Honor Code	8								8
Iffraaj	9	9
Into Mischief	44	10	11						4	19
Invincible Spirit	24	5	4	7				4	4
Itsmyluckyday	4	4
Jimmy Creed	12			12
Kingman	29	5	24
Kitten’s Joy	35			12	11
Le Havre	49							9	11	29
Lemon Drop Kid	19		7							12
Leroidesanimaux	8								8
Liam’s Map	4								4
Lonhro	33				33
Lope de Vega	5								5
Lucky Pulpit	2		2
Maclean’s Music	3		3
Magician	12								12
Majestic Warrior	49		7	31	11
Majesticperfection	13	5	8
Malibu Moon	46	1	8	10				5	14	8
Mastercraftsman	8		8
Mayson	12	1			11
Medaglia d’Oro	3	2						1
Midnight Lute	1								1
Mineshaft	20		9			11
Mizzen Mast	12					12
More Than Ready	34				22							12
Munnings	1			1
New Approach	25	5	11	9
No Nay Never	27		26					1
Noble Mission	2		2
Oasis Dream	6	6
Overdriven	7				7
Pioneerof the Nile	30		4	12				2	3	8
Pivotal	27
Point of Entry	16		7					5
Pyro	8							8
Quality Road	2								2
Raven’s Pass	33			33
Redoute’s Choice	8										8
Regal Ransom	11					11
Roman Ruler	8			8
Rulership	24			24
Run Away and Hide	31		9	22
Scat Daddy	48			37				1		10
Sea The Stars	12
Sebring	1		1
Shackleford	5							5
Shakin It Up	2	2
Shalaa	5							5
Shamardal	97	8	12	54	23
Shanghai Bobby	42							6	29	7
Siyouni	17	4	13
Smart Missile	6		6
Smart Strike	19					4	5
Snitzel	4		4
Speightstown	78	8	5	12	17				4	21	11
Spring At Last	56
Square Eddie	33							13	10	10
Stormy Atlantic	1
Street Boss	5	5
Street Cry	30			18	12
Street Sense	7
Summer Front	2		2
Super Saver	1		1
Take Charge Indy	11	2	8		1
Tale of the Cat	4							4
Tanino Gimlet	24		24
Tapit	92	10	41	18		10			6		7
Tapizar	27			6	12	9
Temple City	13		4					1	8
Teofilo	30				30
The Factor	43		23		20
Tiz Wonderful	11						11
Tiznow	2								2
To Honor and Serve	11			11
Tonalist	3		3
Uncle Mo	78		23	12				9	27		7
Verrazano	17		3						14
Violence	46		7	18	11				10
War Command	12							3	9
War Front	39	5	7	11				2	14
Warrior’s Reward	12				12
Will Take Charge	36	7	17						12
アーネストリー	20		1						4	12
アイルハヴアナザー	591	27	65	200	104	16		17	40	40	33	7
アグネスデジタル	133		10	21	36	11	21	16	6		12
アサクサキングス	76	6	23	11	4		1		15	9	7
アジアエクスプレス	11							11
アスカクリチャン	2	2
アッミラーレ	17		7
アドマイヤオーラ	72		10	33	6	5	18
アドマイヤコジーン	18			11					7
アドマイヤマックス	53		7		23		5		3		15
アドマイヤムーン	338	5	34	24	49	75	27	25	64	30
アポロキングダム	86	7	21	19				6	8	2	16
アポロソニック	4		4
アルデバラン2	67	2	9	7					20		18	11
アンライバルド	25	6	9			9		1
ヴァーミリアン	183		20	21	41	33	8	6	2	11	25	12
ヴァンセンヌ	42	10	9					7	16
ヴィクトワールピサ	697	50	129	80	61	8	8	43	148	76	45	12
ウインバリアシオン	18		18
ウォーターリーグ	1
エイシンアポロン	15		9					6
エイシンヒカリ	6							6
エイシンフラッシュ	581	50	151	141	7			25	172	12
エーシンフォワード	18		7		11
エスケンデレヤ	90	7	70					7	6
エスポワールシチー	240	10	58	51	37			2	47	35
エピファネイア	437	70	119					103	145
エンパイアメーカー	582	4	27	118	129	38	12		48	45	69	38
オウケンブルースリ	8		2						6
オーシャンブルー	18	4	14
オペラハウス	4					4
オルフェーヴル	1352	77	352	229	21			79	300	232	11
オレハマッテルゼ	16					12						4
オンファイア	11	2	9
カジノドライヴ	312	7	39	64	12	33	11	6	43	48	33	12
カネヒキリ	268	1	29	108	53	10			36	16	15
カリズマティック	3		3
カルストンライトオ	21					12				9
カレンブラックヒル	89	13	54					14	8
カンパニー	16			16
キズナ	591	99	197					84	211
キモンノカシワ	34		22	12
キャプテントゥーレ	45		2	15					5	13
キングカメハメハ	1442	76	356	278	206	61	50	43	145	55	84
キングズベスト	291	10	39	77	36			7	62	31		29
キングヘイロー	173	10	53	4	37	27			3	1	11	11
キンシャサノキセキ	811	46	185	109	26	48		56	90	108	76	12
グラスワンダー	55		20	7	9				7	7
グランデッツァ	1							1
グランプリボス	102	7	53	23				8	4	7
クリーンエコロジー	5							5
クリエイター2	8	8
クリストワイニング	8				8
クレスコグランド	12								4	8
クロフネ	727	20	103	108	101	48		46	131	47	54	18
ケイムホーム	126	2	8	22	22	9			3	48	12
ケープブランコ	102	16	49					2	9	26
ゴーカイ	4
ゴールドアリュール	1073	72	299	202	117	65	32	16	97	66	38	12
ゴールドシップ	206	32	70					26	78
ゴールドヘイロー	24	4	15					5
ゴスホークケン	19		7
コパノリチャード	1							1
コンデュイット	36					20	4					4
サウスヴィグラス	659	44	163	78	47	39	4	15	179	44	28
サウンドボルケーノ	3		3
サクラオリオン	21			21
サクラゼウス	1	1
サクラバクシンオー	34						30
サクラプレジデント	20		2	1								17
ザサンデーフサイチ	9		9
サダムパテック	5		5
サマーバード	135			24	81					3	10	17
サムライハート	54	5	2	19		4	10	4	1
サンライズペガサス	24				12	12
シニスターミニスター	362	25	98	63	63	4			38	25	21
シビルウォー	25		16	9
ジャスタウェイ	649	62	269	81				29	163	45
ジャングルポケット	260	10	57	50	68	21		8	12	8		9
ショウナンカンプ	111		13		43				6	9	31
ジョーカプチーノ	110	4	34		17			24	20	11
シルポート	5		3						2
シングンオペラ	49	4
シンボリクリスエス	327	17	51	80	33	39			30	27	14	8
スウィフトカレント	10
スウェプトオーヴァーボード	238	13	41	28	29			19	53	19	25
スーパーホーネット	8										8
スクリーンヒーロー	612	67	120	49	21	19	11	29	163	33	46	12
スクワートルスクワート	23							23
スズカコーズウェイ	80	9	15		21	11			17	7
スズカフェニックス	84		18	45		9					12
スズカマンボ	107		7	9		16	8			7	21
スターリングローズ	14	5								9
スタチューオブリバティ	32					22
ステイゴールド	1072		58	198	315	124	59			71	131	20
ストーミングホーム	108		15	19	39	4	7				8	9
ストリートセンス	117			22	48	11				10	26
ストロングリターン	129	17	56	22				1	10
スピルバーグ	27	4	3					5	12
スペシャルウィーク	22		6		7	9
スマートファルコン	317	10	126	52	38			2	9	35	16	21
スマートボーイ	9		9
スマートロビン	10									6
セレスハント	3		3
ゼンノエルシド	1				1
ゼンノロブロイ	255	10	68	55	46	4	6		21	19	12
ソングオブウインド	18						9
タートルボウル	365	30	150	23	34			11	55	30
タイキシャトル	130	3	7	7	19	12	23	5	33		10
ダイシンオレンジ	1	1
タイセイレジェンド	4		4
タイムパラドックス	67		22	22	18				5
ダイワメジャー	1220	105	215	157	180	94	4	138	97	55	69	18
タガノロックオン	2								2
タニノギムレット	93	6	8	12	10	12			1	17	11
ダノンシャンティ	218	5	22	17	45	11		14	24	7	47	11
ダノンバラード	83	2	20	54					7
ダノンレジェンド	11	1						10
ダンカーク	280	49	100	52				13	58	8
ダンスインザダーク	27			12		10
チチカステナンゴ	1
ディープインパクト	4086	272	912	558	233	167	59	235	762	530	238	17
ディープスカイ	179	1	3	30	47		35	3		12	26
ディープブリランテ	430	26	86	61	47	10		19	66	39	26	18
テイエムオペラオー	2	1
ディスクリートキャット	11	10						1
ティンバーカントリー	7			7
トウケイヘイロー	15	5							10
トゥザグローリー	145	1	67	42				15	20
トゥザワールド	39	1	12					8	18
ドゥラメンテ	61	50						11
トーセンジョーダン	52	3	21	8					13
トーセンファントム	37	4	27						6
トーセンブライト	18		6						12
トーセンホマレボシ	205	3	72	29	19	11		9	32	30
トーセンラー	118	4	27	18					45	20
トビーズコーナー	118	2	15	26	26				25	6	9	9
トランセンド	102	8	37	19	14	9		7	1
ドリームジャーニー	236	33	34	30	49	12	11	5	10	20	32
トワイニング	12										12
ナカヤマフェスタ	132	3	60	33		10			4	15
ニホンピロアワーズ	2		2
ニューイングランド	8		6	2
ネオユニヴァース	440	18	48	105	49	22		1	15	37	31	11
ノヴェリスト	501	29	173	108	16			26	55	67	20
ノーザンリバー	4							4
ノボジャック	46		4		2				16	17	7
ハーツクライ	2057	138	344	373	221	69	37	76	256	336	141	9
バーディバーディ	12		12
ハードスパン	111		6	30	23					35	10
ハービンジャー	886	58	178	60	87	10		24	212	128	21	18
ハイアーゲーム	7					7
パイロ	467	30	114	80	33	18	8	36	80	23	14
バゴ	178	16	45	44		12	8	9	22	22
パドトロワ	60	3	8	30				1	18
バトルプラン	171	4	1	33	31	6			44	32	8	12
バンブーエール	57	1	24	10		22
ヒルノダムール	15	1						6		8
ファスリエフ	30			18	12
ファルブラヴ	19						11
フィガロ	5		5
フェノーメノ	77	11	31					9	26
フォーティナイナーズサン	9				9
フサイチセブン	20		2						8	10
フサイチリシャール	19				9
フジキセキ	16
ブラックタイド	597	44	167	59	32	8	8	29	100	58	26	21
ブラックタキシード	29			6	9	10
フリオーソ	193		60	80	30				7	9	7
プリサイスエンド	124	10	12	21	8			9	7	15	7	6
ブレイクランアウト	41			12	18	10
フレンチデピュティ	110	2	32			19				12	11
ベーカバド	221	8	45	28	64	8			22	26		20
ヘニーヒューズ	936	97	222	121	51			41	183	132	46
ベルシャザール	138	8	69	21				9	11	11
ホッコータルマエ	5	5
ホワイトマズル	88		3	4	51					10	8	12
マクフィ	34	10						24
マジェスティックウォリアー	151	45	64					15	23
マツリダゴッホ	278	17	45	13	35			46		26	26	21
マヤノトップガン	18						18
マンハッタンカフェ	502	8	65	52	128	43			50	73	28
ミスキャスト	20
ミッキーアイル	38	15						23
メイショウサムソン	212	8	32	19	19			1	21	29	49	22
メイショウボーラー	333	5	60	64	72	21		12	50	31	14
メジロダイボサツ	1			1
モーリス	67	45						22
モンテロッソ	188	2	86	7	32				21	20	20
ヨハネスブルグ	386	23	111	67	38	7	10	12	61	33	15
ラブイズブーシェ	3	3
ラブリーデイ	14	9						5
リアルインパクト	132	44	50					9	29
リーチザクラウン	121	14	31	8	5			23	10	20	7
リオンディーズ	39	16						23
ルーラーシップ	1583	88	359	311	180	5		45	283	152	62
レッドスパーダ	109	3	24	12					10	27
ローエングリン	222	1	49	20	57		8		13	44	11	8
ロージズインメイ	179	7	40	25	42	8			25		19	4
ローズキングダム	96	8	27	7				10	30	10
ロードアルティマ	72		9		11	7		5		9	19	12
ロードカナロア	2332	169	474	540	135			98	423	309	78
ローレルゲレイロ	40	15	10						15
ロジユニヴァース	80	3	30	35				1	11
ワークフォース	354	4	64	61	50	31		13	71	56
ワールドエース	79	14	37					28
ワイルドラッシュ	23			8					1	8		6
ワイルドワンダー	9								2	7
ワンダーアキュート	4		2						2

※上記が2018.10月～2020.10.11の約２年間分を集計した表だが、数字だけだとちょっと何んだかよく分からん表に見えるので、これに最大値をちょっと濃いめの色、上位４番までをちょっと薄い色に色づけしてみる。またこの後は上記表だと行が多いので、勝利数400以上の種牡馬だけを抜粋してサンプル画像を表示していく。

AccessデータをExcelにコピペし使える表に加工する

のだが、ちょっと簡単にExcel表作成時、よく使う機能を画像付きで紹介する。

しばし内容が脱線しているので、そんなんもうエ～から！という人は読み飛ばしてもらってAでぇ～

番外編：Excel見やすい簡単機能講座

フィルター機能

原表から勝利数４００だけを表示するのにフィルターで抽出しコピペし別シートを作成。

そんなんAccessでやってからExcelにコピペしたらエ～やん！って自慢マンマンの方は・・・

どんどん読み飛ばしていってなぁ～

４００勝以上（シート名）を作成

説明用なので、とりあえず値だけをコピーする。

今回のように元シートのネタをフィルターで抽出して、別シートにコピペする場合。

元ネタをコピーするのに、シート全体ではなく表の範囲を指定してコピーした方がExcel使用のメモリオーバーを回避できるので、ぐるぐる回ったままダンマリになったり応答しなくなったりということがかなり少なくなる。特に会社の低性能パソコンを割り当てられてる職場ではExcelコピペの仕方で時間のロスとイライラが自分の大切な時間に大きく影響してくるので、これは知っておくと精神衛生上とても効果があると確信している。

値貼付けを選択しペーストすると⇊下図になる。

ここから種牡馬別ピークを知りたいので、、、

牡８・９　牝７の列を削除
幅を整頓
一行目の見出しに色を付ける

と、こんな感じになる。（下図）

ウィンドウ枠の固定（見出しの固定）

今回のように１ページに収まる表だとこれで完成だが、原表のように行数が３００行以上もある表だとこのままスクロールすると見出しが消えてしまうので、データ表示開始行と列の一番右空白列（今回上記の表だと’N２’をクリックし、「ウィンドウ枠の固定」→「先頭行の固定」を選択すると見出しが固定される。

条件付き書式設定のやり方

選択シートに関係ないルールを全て削除する

これはコピペ元や自分以外の人が作成したシートやブックだと、今回必要のないルールが既にコピペされている場合があり、これをそのまま使用すると何かこのシートでトラブルがあったとき修復するやり方を知っていないと何度も同じ間違いをすることになるので、先ずは「ルールの消し方」から習得することが優先なのよ～ね～

シート全選択
条件付き書式→ルールのクリア
シート全体からルールをクリア

②　③　を拡大すると・・・

強調したい範囲を選択し、条件付き書式設定（ルールを設定）する

今回は牡２～牡７と牝２～牝６までという２つの項目ごとに書式設定を行い。一番勝利数の多い馬齢を濃いめの色で３番目までをちょっと薄い色づけをして数字だけでなく視覚的に分かりやすい表にしたいというのが目的。牡２～牡７だけでも良いのだが、補助的に牝馬も分けて載せてみた。

２行目の牡２～牡７を選択（Ｃ２：Ｈ２）

「条件付き書式」→「カラースケール」→「赤白　カラースケール」を選択

２行目の牝２～牝６を選択（Ｉ２：Ｍ２）

「条件付き書式」→「カラースケール」→「緑白　カラースケール」を選択

３行目以降も同様に行ごとに設定していけば良いのだが、今回のように１行毎にルールを設定するのはちょっと面倒になってくる。ここからがようやく今回のＶＢＡサンプルの紹介になるが、プログラム的には、For-Next文、もしくは、Do-Loop文を使えば簡単にできることは想像できるだろう。

ただガチガチに書式からVBAに組み込むのではなく、１行目を手動で設定するのがミソで、こうすると１行目の書式を変更するだけで、これから自分で作成するどんなExcelシートも選択セル値を変更すれば、同様な見やすいシートに変換できる。

関数化するよりも表作成したら終了なので、都度VBAのシート名、行数、列を手動で変更する方が使い勝手が良いと思う。報告資料として添付するときはExcelブックとして保存しなおせば.xlsmとなりVBA記述は添付されないので、安全だ。

既に上記で、ルールの削除の仕方を習得しているので、ルールだけを全消しして再度１行指定しＶＢＡを実行すれば色々な条件付き書式を試せるので、数字と感覚的に色や簡易グラフを組み合わせたいときは便利な機能である。私はグラフよりもルールを結構な割合で多用している。

バクチから学べることがあるとしたら、％やヘンテコな数式よりもいくら必要でいくら儲かるのか？を実際カネが動くので、習得しやすいということがある。これは株やＦＸ、ビットコインなどでも同じだが、実際にコインや現金が動くバクチの方が骨身に染みる感じがある。と、私は思う。。。♡

では、今回のサンプルデータを示し、１行目にルールを設定しコピペするＶＢＡサンプルを書いていくことにしよう～～既に「１行目にルールを設定」はできてしまっているのだよ～～

Excel：４００勝以上（２年間）種牡馬ピーク判定表を作成

２行目の書式を行数分コピペするVBAサンプル

Sub syosiki1_set()
'
'   ルール（条件付き書式設定）を一行だけ指定し必要行コピペする
'
    Dim i As Integer
    '''セルをA1表示にする
    Application.ReferenceStyle = xlA1
    Sheets("400勝以上").Select
    
    '''ベースをコピーする
    Range(Cells(2, 3), Cells(2, 13)).Select
    Selection.Copy
    
    '''ベースを必要行ペーストする
    For i = 1 To 28
        Cells(i + 2, 3).Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Next i
End Sub

完成したシート

Excel VBA 逆引き大全 600の極意 Microsoft 365/Office 2021/2019/2016/2013対応

Amazon

楽　天

Excel VBA 逆引き大全 600の極意 Microsoft 365

※私が持っているのが2016年版（以下は2016）

ExcelVBAの基本構文
セルの操作
関数
ワークシート、ウィンドウの操作
ブック操作
データ操作
テーブル・ピボットテーブル
図形
グラフ
ユーザーフォーム
印刷
ファイルとフォルダ
データ連携
イベント
バージョン・トラブルシューティング・エラー処理
高度なテクニック
索引

目次が巻頭で検索が巻末。昔のタイプの本の構成だが、索引がページでなくTipsという見出しNo.になっていて慣れるまでは検索しづらい。
Amazonレビューは★3.9

私のKindle-Unlimited評価は★4

2024年4月
月	火	水	木	金	土	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30