タグ別アーカイブ: 大きい表

とてつもなく大きい表を扱いやすくする~動く条件付き書式~

【カオスな現場経験 x 分析力を組み合わせた、少し尖ったマクロのレシピ集】

とてつもなく大きい表を使っているときに迷子になることがありませんか?

「今、どの行どの列セルを扱っているんだろう・・・」

しまいには、ミスが怖いので表を印刷して「定規」を当てたりしたことはありませんか?

今回、この迷子対策として参考にしたのがこの記事です

参考記事

今回は迷子対策として、上の記事を基にした「動く条件付き書式」の事例を紹介します

この動く条件付き書式では、カーソルを置いた位置の行列の色が変わるので、「迷子対策」にはバッチリです!

紹介する事例は2つあります!

1つ目の事例は、CELL関数とROW関数、そしてCOLUMN関数を使用して条件付き書式を設定します

こちらは、手動で条件付き書式を設定する内容になっています

 *イベントプロシージャ―に1行コードを書く必要があります

この方法の場合には、重たいファイルの場合だと「条件付き書式」の動きがかなり重たくなります 

 2つ目の内容は、イベントプロシージャ―を使用して、カーソルが動く都度「条件付き書式」をマクロで自動設定します

 こちらは条件付き書式の範囲をで指定するわけではなく、「行列」双方向の2本の線で範囲を設定しますので、あまり動きを重くしないですみます

 後、2つ目の方法の場合には色をシート上で設定できるようにするなどの、実践的に使えるようにする為の工夫を複数行っています

こちらは、サンプルファイルをダウンロードして、実際の動きを確認できます!!

①条件付き書式を全て手動で設定

まずは、条件付き書式を設定する範囲を手動で指定します

その後、条件付き書式の設定画面を開き、ルールの種類の選択を「数式を使用して、書式設定する設定を決定」にします

「次の数式を満たす場合に値を書式設定」には、画像のような数式を設定します

数式:=OR(CELL(“ROW”)=ROW(),CELL(“COL”)=COLUMN())

*上の数式をそのままコピーする際には、が半角になっていることをご確認ください

これで条件付き書式の設定は終了です

実はもう一つ、仕掛けが必要です

イベントプロシージャ―を使用し、セルの選択が変更になった場合に「更新処理」を行う必要があります

 更新処理を設定するには、まずは、上の画像のように該当のシートで右クリックをして「コード表示」を選択します

 次に開いたVBE(Visual Basic Editor)画面では、下の画像の黄色の印の箇所がそれぞれ「Worksheet」「SelectionCange」になっていることを確認します

プロシージャ―には1行だけコード*を入れれば、条件付き書式の更新処理が設定されます

 *Application.ScreenUpdating = True

②都度、条件付き書式の範囲をマクロで設定

動く条件付き書式

こちらは、前述のようにこの「動く条件付き書式」を実践的に使えるように様々な工夫を行っています

各種設定シートというシートを設けてあり、様々な設定が行えます

工夫一覧

1.シート上での色設定

各種設定シートにて、色を指定すれば、条件付き書式で設定する色を変更できます

2.開始行や開始列の設定

条件付き書式の設定を行う開始行と開始列はシート上で調整できます

上の画像では開始行が6行目、開始列が1列目ですので下の画像のように色が変わります

3.停止・再開処理他

この動く条件付き書式は「停止」したい時があると思います

この際は、停止ボタンを押せば停止ができる仕掛けになっています

停止処理を行った場合には、停止前に設定された色が残ってしまっています

ですので、上の画像の消去ボタンを押せば「設定残り」の色は消去できます

コード上の工夫

こちらは、ごく簡単なポイントだけを解説させて頂きます

この事例は、前述のように条件付き書式の範囲が拡大したり縮小する場合も想定しつつ、処理が重たくならないようにします

こちらの場合は、色の付け方が、1番目の方法と変えてあります

カーソルがある位置が、色のつく範囲の最終行と最終列になります(1番目の方法も可能ではありますが、今回は極力色設定する範囲を小さくするという趣旨でこの方法にしました)

ポイント1 ⇒面ではなく、行方向1行、列方向1列で範囲設定

カーソルを移動させた際は、イベントプロシージャ―にてカーソル位置を取得します(Targetを引数にして取得)

カーソル位置を取得したら、2つ範囲を設定します

1つ目は行方向です。範囲の開始列は1列目などを固定で指定して範囲設定します

2つ目は列方向です。範囲の開始行は1行目などを固定で指定して範囲設定します

ポイント2 ⇒設定済みの条件付き書式は事前にクリアしておく

重複して条件付き書式が設定されないように、設定済みの条件付き書式はクリアしておきます

ポイント3 ⇒マクロ処理の途中でイベントプロシージャ―は止めておきます

1つの範囲に条件付き書式を設定した際に、更にイベントが起動してしまいますので、次の1行のコードでイベントを中止しておきます(後で再開が必要です)

Application.EnableEvents = False

<まとめ>

 今回はとてつもなく大きい表を扱う際、迷子にならないようにカーソルがある行と列の色を変える方法を紹介しました

但し、

特に2番目の方法などが当てはまるのですが、実際に業務で活用する場合は、さらに一工夫が必要です

 例えば、2番目の方法は設定済みの条件付き書式をクリアしてしまいますので、「迷子対策」以外の条件付き書式の設定が消えてしまうケースも想定されます

ですので、別途「迷子対策以外」の条件付き書式も自動設定する必要があります

 ちなみに、2番目の事例のサンプルファイルですが、こちらは複数のシートで「動く条件付き書式」を試せるようになっています

ぜひ、各種設定シートで「シート名」を変えながら試してみてください!

なお、コードについては非公開とさせて頂いていますので、予めご了承ください 

それでは、最後まで長文にお付き合い頂きありがとうございました!

 今回紹介した仕組みは、実務では更に工夫の余地があると思いますが、エクセルVBAならではの便利さが生み出せると思います!

少しでも、大きい表を扱う際の「イライラ」を消すことに貢献できたら幸いです

もし、質問や要望等がありましたら「お問合せ」からご連絡をお願い致します


にほんブログ村


にほんブログ村

カーソルの位置を行列双方向でハイライトする

行列双方向で大きな表を扱っていると、今、どこを操作しているのかわからなくなり、迷子になることがあります

昨日、twitterで見つけた記事↓から、この迷子問題に対する大いなる示唆を得ました

https://hamachan.info/win7/Excel/active.html

こちらの記事を元にして、作成したのが下の表です

勤務スケジュール内で、カーソルを置いた行列がハイライトされつつ、勤務者の顔が表示され、ダイナミックに動きます

kinmusukejurue.gif

!今回のポイント

・条件付き書式の使い方

ⅰ)Cell関数とColumn関数、Row関数

Cell関数はセルの情報を返す関数です

このCell関数と列番号と行番号を返すColumn関数と、Row関数を組み合わせて条件付き書式の条件にします

ⅱ)OR条件

ハイライトしたいのは、カーソルを置いた行と列の位置です

こちらを「カーソルを置いた行」もしくは「カーソルを置いた列」

と解釈し直します

ⅰ)ⅱ)を組み合わせて設定した条件付き書式の画面が次の画像です

・イベントプロシージャ―

条件付き書式を設定しただけでは、うまくハイライトされません

カーソルの位置を変えた時に、更新処理が強制的に行われるようにイベントプロシージャ―を入れます

ⅰ)コードを表示

ⅱ)更新処理の組み入れ

下の「Worksheet」⇒「SelectionChange」から更新を行うコードを入力します

Application.ScreenUpdating = True

上のコードを入力することで、カーソルの位置を変更すると強制的に画面更新が行われ、条件付き書式もうまく稼働します

・画像のダイナミック表示

更に、2つの仕掛けを付け加えて”選択したセルにある名前”と対応する画像を表示できるようにします

こちらは、名前機能とカメラ機能を3段階で活用します

セル内の名前⇒別シートにコピー⇒別シートをカメラで参照

1.別シート(計算シート)に選択したセルの内容をコピーする

条件付き書式を強制的に稼働させるために作成したイベントプロシージャ―内に以下のコードを入れます

Worksheets(“計算シート”).Range(“C3”).Value = Target.Value

すると、計算シートにカーソルを置いたセルにある内容がコピーされます

以下は、実際に設定したコードです

複数セルが選択されると、条件付き書式がうまく動かない為、制御を入れています

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
If Target.Count > 1 Then
MsgBox “複数のセルは選択できません!”, vbCritical
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If

Worksheets(“計算シート”).Range(“C3”).Value = Target.Value

End Sub

2.名前とカメラ機能を組み合わせて画像をダイナミック表示する

①下のような表を作成し、各画像が入ったセルに「人の名前」と同じ名前(名前管理)をつける

キャプチャ0221

こちらの名前を作成する処理は、以下の画面で簡単に設定できます

「選択範囲から作成」をクリックし、下の画像の「チェック」の箇所で設定します(上の表の名前の列が、左端列になるようにします)

②名前の管理画面にて、計算シートに入力された名前を参照する名前(名前管理)を設定する

こちらはINDIRECT関数を使用します!

③カメラ機能で②の名前を参照する(数式バーにて)

カメラ機能が参照しているセルは数式バーに表示されます

数式バーを②で作成した名前を組み入れます

カメラ機能の詳細については以下を参照してください

カメラ機能

INDIRECT関数については以下を参照してください

画像変える技術については、こちらもぜひご参照ください

以上です。ちなみに画像は以下より拝借させていただきました

http://kara1.blog92.fc2.com/blog-category-9.html

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

にほんブログ村