Hey, Scripting Guy!
Scripting Guys が皆さんの質問にお答えします
TechNet コラムへようこそ。このコラムでは、よく寄せられるシステム管理スクリプトに関する質問に Scripting Guys がお答えします。システム管理スクリプトについて質問がある場合は、scripter@microsoft.com (英語のみ) までお送りください。すべての質問に回答することはできないかもしれませんが、可能な限り対応いたします。
詳細情報
| • | |
| • | |
| • |
ワークシートのセルに背景色を割り当て、それらのセルの合計を算出する方法はありますか
Scripting Guy さん、よろしくお願いします。背景色を (指定された値に基づいて) ワークシートのセルに割り当て、それらのセルの "合計" を算出する方法はありますか。
-- JC
JC さん、こんにちは。ご質問ありがとうございます。そうです。確かにこう言いました。Scripting Guy の息子は、最近出場した試合で 4 打数 4 安打を記録し、3 本のシングル ヒットと 1 本のツーベース ヒットを放ちました。その試合で、彼の今シーズンの平均打率は 5 割に達しました。総合的に考えると、まったく悪い記録ではありません。
当然、Scripting Guy は、息子に「平均打率が 5 割ということは、まだ 2 打席中 1 打席は打ち損ねているということだ」と釘を刺しました。考えてみれば、2 回に 1 回しか失敗しないのは、Scripting Guy がこのコラムを執筆するときの成功率よりもはるかに高い確率ですけどね。うーん。
でも、JC さんは 2 つの理由で幸運ですよ。1 つは、今回が実際に質問に対する回答を用意できるまれなケースであることです。そして、もう 1 つは、私たちが今日は他にもやるべきことがたくさんあることです。つまり、いつものようにあまりだらだらと無駄話をしている時間がありません。したがって、すぐに本題に入ってスクリプトをご紹介します。
あ、でも、あと少しだけ。それから本題に入りましょう。"本題に入る" を意味する英語の "cut to the chase" (直訳すると "すぐにカー チェイスを撮る") という表現が、1920 年代にハリウッドで生まれた表現であることはご存知でしたか。映画界で、"cut to the chase" とは、登場人物が自分の夢や目標を語るような映画の退屈なシーンは飛ばして、山場となるシーン、つまりカー チェイスを撮影することを意味します。普通は、Hey, Scripting Guy! では "cut to the phrase" という表現は使いません。何といっても、退屈な部分を飛ばすと、コラムの量が非常に少なくなってしまいますから。
そのことを念頭に置いて、ワークシートのセルに背景色を割り当て、それらのセルの合計 を算出するという、カー チェイスのシーンに相当するスクリプトを次に示します。
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.Cells(1, 1).Value = "A"
objExcel.Cells(2, 1).Value = "B"
objExcel.Cells(3, 1).Value = "C"
objExcel.Cells(4, 1).Value = "D"
objExcel.Cells(5, 1).Value = "E"
objExcel.Cells(6, 1).Value = "F"
objExcel.Cells(7, 1).Value = "G"
objExcel.Cells(8, 1).Value = "H"
objExcel.Cells(1, 1).Interior.ColorIndex = 7
objExcel.Cells(2, 1).Interior.ColorIndex = 8
objExcel.Cells(3, 1).Interior.ColorIndex = 9
objExcel.Cells(4, 1).Interior.ColorIndex = 10
objExcel.Cells(5, 1).Interior.ColorIndex = 7
objExcel.Cells(6, 1).Interior.ColorIndex = 7
objExcel.Cells(7, 1).Interior.ColorIndex = 8
objExcel.Cells(8, 1).Interior.ColorIndex = 10
i = 1
Do Until objExcel.Cells(i, 1).Value = ""
intColor = objExcel.Cells(i, 1).Interior.ColorIndex
Select Case intColor
Case 7 intSum = intSum + 5
Case 8 intSum = intSum + 10
Case 9 intSum = intSum + 15
Case 10 intSum = intSum + 20
End Select
i = i + 1
Loop
Wscript.Echo intSum
まず、ここではいくつか勝手なまねをしたことをお知らせしておきます。たとえば、JC さんは、特定の値をセルに入力し、入力された値に基づいて、そのセルの背景色を設定するようにデザインされたスクリプトを持っています。私たちは、このような洒落たシステムをわざわざ設定しませんでした。代わりに、いくつかのデータをいくつかのセルに入力し、各セルの背景色を独断で変更しただけです。でも、心配はいりません。セルの背景色を変更することと、セルの背景色を読み取ることは、とても簡単なので、このスクリプトを必要に応じて調整するのは、それほど難しくないことがおわかりいただけると思います。
そういえば、"cut to the chase" という言葉の語源については説明しましたっけ。しましたか。それならば、本題に入り (cut to the chase)、スクリプトのしくみについて説明しましょう。まず、Excel.Application オブジェクトのインスタンスを作成し、Visible プロパティを True に設定します (本当にすばらしい方法です)。これにより、Excel のインスタンスが実行され、画面に表示されます。次に、以下の 2 行のコードを使用して、新しいブックを作成し、そのブックの最初のワークシートにバインドします。
Set objWorkbook = objExcel.Workbooks.Add() Set objWorksheet = objWorkbook.Worksheets(1)
その後には、単にセル A1 からセル A8 まで値を入力する、何行かのコードを記述します。たとえば、次のコマンドで、セル A1 (行 1、列 1) の値を文字 "A" に設定します。
objExcel.Cells(1, 1).Value = "A"
セルに値を代入したら、次のようなコードを使用して、各セルの背景色を (繰り返しになりますが、任意の色に) 変更します。
objExcel.Cells(1, 1).Interior.ColorIndex = 7
セルの色を変更するのがいかに簡単かがわかりますね。必要な作業は、Interior.ColorIndex プロパティに適切な色の整数を代入するだけです。ここで注意が必要なのは、各色に対応する整数値を把握しておくことです。たとえば、7 に対応する色は何でしょうか。さいわい、「Office Space の記事」の該当するトピックを参照すると、色と色の値に関する必要な情報をすべて入手できます。
さて、どこまで説明したでしょうか。次のようなワークシートが表示されるところまででしたね。
ここで、ワークシートのセルの合計を算出します。ただし、ワークシートの値を追加することで、これを実行するのではありません。明らかに、A + B + C という式では意味がありませんから。代わりに、セルの背景色を特定して、色に基づいてそのセルに値を割り当て、それらの値を加算します。難しいでしょうか。心配しないでください。後ですべて詳しく説明します。それが終わったら、私たちの夢や目標を語ることにしましょう。
各セルの読み取りを開始する前に、値 1 を i という名前のカウンタ変数に代入する次のコード行があります。この変数は、ワークシート内の位置 (行) を追跡するのに使用します。その後、列 A で空白セルが見つかるまで実行する Do ループを設定します (空白セルが見つかった時点で、それ以降のセルにはデータが入力されていないと見なします)。
Do Until objExcel.Cells(i, 1).Value = ""
このループ内では、まず、セルの背景色を特定してから、intColor 変数に整数値を代入します。この処理を行うコード行を次に示します。
intColor = objExcel.Cells(i, 1).Interior.ColorIndex
セルの背景色を特定したら、次のような簡単な計算を開始できます。
Select Case intColor
Case 7 intSum = intSum + 5
Case 8 intSum = intSum + 10
Case 9 intSum = intSum + 15
Case 10 intSum = intSum + 20
End Select
ここでは、intColor 変数の値に基づいて、Select Case ステートメントを設定しました。また、ワークシートで使用されている各色に数値を代入しました。たとえば、色 7 (ピンク色と紫色が混ざったような色) には値 5、色 8 (シアンのような色) には 10 を代入しました。Select Case ステートメント内では、別の変数 (intSum) の値を増加します。この変数の値は、いくつ増加すればよいでしょうか。そうですね、それはセルの色によって異なります。たとえば、ピンク色と紫色がj混ざったような色のセルがあるとします。この場合、intColor 変数の値は 7 なので、スクリプトは次のコード行を実行し、intSum 変数の値を 5 増加します。
Case 7 intSum = intSum + 5
繰り返しますが、特別難しいことはしていません。intColor 変数の値が 7 である場合は、新しい値を intSum 変数に代入します。つまり、intSum 変数の現在の値に 5 を加算した値を代入します。ループ処理の 1 回目では、intSum 変数の値は 0 です。そのため、そのピンク色と紫色が混ざったような色のセルが行 1 で見つかったら、列 1 の数式は 0 + 5 = 5 となります。その結果、intSum 変数の値は 5 になります。
Select Case ステートメントを終了したら、カウンタ変数の値を 1 増加し、ループの先頭に戻って行 2、列 1 のセルに対して同じ処理を繰り返します。すべてのセルの処理が終了したら、スクリプトで次のすばらしい結果がエコー バックされます。
90
そうですね、でも、実際のところ、この処理は説明を読むよりも実際にやってみた方がおもしろいですよ。
JC さん、これでうまくいくはずです。先ほど言いましたが、午後にいくつかやらなければならないことが他にあるので、今日のコラムは手短に済ませます。Scripting Guy の息子について、あまりお伝えできなくて申し訳ありません。でも、がっかりしないでください。いつものように、彼は 8 月まで野球の試合に出場し続けるでしょう。きっとシーズンが終わるまで "たっぷり" お伝えしますよ。
