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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

※上記が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）

1. ExcelVBAの基本構文
2. セルの操作
3. 関数
4. ワークシート、ウィンドウの操作
5. ブック操作
6. データ操作
7. テーブル・ピボットテーブル
8. 図形
9. グラフ
10. ユーザーフォーム
11. 印刷
12. ファイルとフォルダ
13. データ連携
14. イベント
15. バージョン・トラブルシューティング・エラー処理
16. 高度なテクニック
17. 索引

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

私のKindle-Unlimited評価は★4

あわせて読みたい

【Excel】を無料で独学で学ぶKindle-Unlimitedのお得な活用方法 Excelを独学で学ぶにはKindle-Unlimited無料期間を有効活用するのがお得 Kindle ビジネスツールの第一歩はWordではなくExcelで文書が打てるようになることの方が大事。…

それでは、またね～