Hey, Scripting Guy!
Scripting Guys が皆さんの質問にお答えします
TechNet コラムへようこそ。このコラムでは、よく寄せられるシステム管理スクリプトに関する質問に Scripting Guys がお答えします。システム管理スクリプトについて質問がある場合は、scripter@microsoft.com (英語のみ) までお送りください。すべての質問に回答することはできないかもしれませんが、可能な限り対応いたします。
詳細情報
| • | |
| • | |
| • |
Excel ワークシートに空白行 (および数式) を挿入する方法はありますか
Scripting Guy さん、よろしくお願いします。膨大な量のデータが格納された Excel ワークシートがあり、240 行おきに空白行を挿入する必要があります。また、その新しい行に、それより上にある 240 行のデータの平均を計算する数式を挿入する必要があります。ご想像のとおり、これは非常にうんざりするような作業です。また、このようなワークシートはたくさんあります。このような行と数式を挿入するスクリプトを記述する方法はありますか。
-- BS
BS さん、こんにちは。きっと多くの方は、Scripting Guys が空き時間に何をしているのか疑問に思っていることでしょう。他の Scripting Guys について代わりにお答えすることはできませんが、このコラムを執筆している Scripting Guy が非常に興味深い趣味を持っているということはお伝えできます。理由は彼自身にもよくわかっていないのですが、彼は空き時間のほとんどを、人里離れた場所にある空港を訪れることに費やしています。
たとえば、今年の前半、このコラムを執筆している Scripting Guy は、シントン州ワラワラにある空港を訪れる機会があることに胸を躍らせていました。しかし、残念ながら、彼にその機会は訪れませんでした。彼は、トライシティーズからワラワラに向かって車で 45 マイル走ったところで、自分が搭乗予定の便が欠航になったことを知りました。こんな目に遭うのは、実際には飛行しない飛行機を必要とする現代の空の旅の宿命ですね。結局、彼は車でトライシティーズに戻り、(時刻変更された便が 1 回延期され、そして再び延期された後で) トライシティーズ空港をしばらくブラブラしてから、車でワラワラに戻りました。
ワラワラに行った目的は単に息子の野球の試合を観戦することでしたが。
そして、先週の金曜日に、この Scripting Guy (とその息子) は、ワシントン州プルマンにあるプルマン モスコー地域空港を訪れる機会を得ました (楽しくて興奮に満ちたアイダホ大学のオープン キャンパスに参加するためです)。プルマン モスコー地域空港は小さな空港か、ですって。そう言って差し支えないでしょうね。何しろ、エア ターミナル全体が 1 室に収まっていて、その部屋の中に、レンタカー窓口、チケット カウンタ、それから手荷物受取所があるのです。このコラムを執筆している Scripting Guy とその息子は、午後 11 時 10 分に着陸する予定の便に乗るため、午後 10 時 45 分ごろに空港に到着しました。それから約 20 分間、空港にいるのは彼ら 2 人だけでした。どこにも人っ子 1 人見当たりませんでした。空港で働いている人がいてもよさそうなものだと思うのですが、そのような人もいませんでした。
なんだか薄気味悪かったのではないか、ですって。そうでもありませんでした。あの空港では、することがたくさんありました。たとえば、その辺に何組かのトランプがあったので、Scripting Guy とその息子は熱のこもった戦争ゲームを繰り広げました (勝ったのはもちろん Scripting Guy です)。また、エア ターミナルにはセルフサービスの書店もありました。“書店” と言っても、実際は、本がぎっしり詰まった本棚が 2 つと、お金の投入口があるだけですが。このコラムを執筆している Scripting Guy は、20 ドルで 4 冊の本 (しかも、すてきな本) を買いました。これはすばらしいことでした。大都市にある一般的な空港で、20 ドルで本が 1 冊でも買えるか試してみてください。
ともかく、プルマン モスコー地域空港には、セルフサービスの書店、トランプ、2 台の自動販売機など (無料駐車場もあったのは言うまでもありません)、旅行者 (または、旅行者を車で迎えに行く人) に必要なものはほぼすべて揃っていました。ただし、1 つだけ足りないものがありました。それは、Excel ワークシートに一定の間隔で空白行 (および数式) を挿入できるスクリプトです。でも、問題ありません。このコラムを執筆している Scripting Guy がこのようなスクリプトを持たずに家を出ることなんてありませんから。では、そのスクリプトをご紹介しましょう。
Const xlShiftDown = -4121
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets(1)
i = 1
j = 1
Do Until objWorkSheet.Cells (i, 1) = ""
If j = 241 Then
Set objRange = objWorksheet.Cells(i, 1).EntireRow
objRange.Insert(xlShiftDown)
strFormula = "=AVERAGE(A" & i - 1 & ":A" & i - 240 & ")"
objWorksheet.Cells(i, 1) = strFormula
objWorksheet.Cells(i, 1).Interior.ColorIndex = 44
j = 0
End If
j = j + 1
i = i + 1
Loop
If j <> 0 Then
Set objRange = objWorksheet.Cells(i, 1).EntireRow
objRange.Insert(xlShiftDown)
strFormula = "=AVERAGE(A" & i - 1 & ":A" & i - j + 1 & ")"
objWorksheet.Cells(i, 1) = strFormula
objWorksheet.Cells(i, 1).Interior.ColorIndex = 44
End If
このスクリプトのしくみはどうなっているのか、ですって。そう質問されるのではないかと恐れていたのです。確かに、複雑そうに見えるコードですよね。でも、このコードのしくみをじっくり解き明かしてみましょう。
ご覧のとおり、まず xlShiftDown と言う名前の定数を定義し、値を -4121 に設定します。この定数は、新しい行を挿入する際に既存のセルをどちらの方向にシフトするかということを、Excel に通知するのに使用します。
Scripting Guys 豆知識 : 奇遇なことに、-4121 というのは、編集者のソフトボールの昨シーズンの打率でもあり、編集者が大学進学適正試験で取った点数でもあります。こんな偶然の一致が起こる確率はどれくらいでしょうか。 |
定数を定義したら、Excel.Application オブジェクトのインスタンスを作成し、Visible プロパティを True に設定します。これにより、Excel のインスタンスが実行され、画面上に表示されます。次に以下の 2 行のコードを使用して、C:\Scripts\Test.xls ファイルを開き、そのファイルの 1 つ目のワークシートにバインドします。
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets(1)
その後、i および j という 2 つのカウンタ変数に値 1 を代入します。i はワークシート内での現在位置を追跡するのに使用し、j は行を挿入するタイミングを把握するのに使用します。運が良ければ、このような 2 つの離れ業を成し遂げる具体的な方法がこの後すぐに明らかになります。
理解しやすくするために、ここでは、A 列にたくさんの数字が入力されていて、その一連の数字の間に空白行が存在しない非常に単純なワークシートがあると仮定します。これを念頭に置いて、次は、A 列に空白のセルが見つかるまで実行される Do Until ループを設定します。A 列に空白のセルが見つかったら、ワークシートの最後に到達したと見なします。そして、既に述べたとおり、次のようにカウンタ変数 i を使用してワークシート内での位置を追跡します。
Do Until objWorkSheet.Cells (i, 1) = ""
i の値は 1 なので、1 回目のループ処理では、行 1 の列 1 にあるセルの値を確認することになります。では、次のコード行に移りましょう。
If j = 241 Then
この行は何のためにあるのでしょうか。BS さんは 240 行おきに新しい行を挿入する必要がありますよね。ワークシート内を下に向かって処理する際に、カウンタ変数 j を使用して行数を追跡します。j の値が 241 でない場合は、数えた行数がまだ 240 行に達していないということです。したがって、If-Then ブロックをスキップし、両方のカウンタ変数の値を 1 ずつ増加します。その後、ワークシート内の次の行についても同じ処理を繰り返します。
では、カウンタ変数 j の値が 241 の場合を考えてみましょう。これはつまり、行番号 240 を渡し終えたところだということになり、空白行を挿入するときが来たということです。空白行の挿入は、次のようにして行います。
Set objRange = objWorksheet.Cells(i, 1).EntireRow objRange.Insert(xlShiftDown)
ここで何をしているのかというと、まず、Excel の Range オブジェクトのインスタンスを作成しています。この Range オブジェクトには、現在の行が含まれます (どうして、この Range オブジェクトに現在の行が含まれることがわかるのかというと、objWorksheet.Cells(i, 1) では A 列の現在のセルを指定し、EntireRow プロパティは行内の他のすべてのセルを返すからです)。次に、Insert メソッドを呼び出して新しい行を挿入します。その際、xlShiftDown 定数を使用して、既存のセルを下方向に 1 行シフトして新しい行を挿入するようにスクリプトに指示します。
必要な処理が、ワークシートに一定の間隔で空白行を挿入することだけだったら、これで処理は完了です。実際、必要な処理が本当にワークシートに一定の間隔で空白行を挿入することだけであれば、次のスクリプトを使用してまさにその処理を行えます。
Const xlShiftDown = -4121
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets(1)
i = 1
j = 1
Do Until objWorkSheet.Cells (i, 1) = ""
If j = 241 Then
Set objRange = objWorksheet.Cells(i, 1).EntireRow
objRange.Insert(xlShiftDown)
j = 0
End If
j = j + 1
i = i + 1
Loop
もちろん、ここで必要な処理はワークシートに空白行を挿入することだけではありません。挿入した空白行に、平均を計算する数式を挿入する必要があります。これは次のようにして行います。
strFormula = "=AVERAGE(A" & i - 1 & ":A" & i - 240 & ")" objWorksheet.Cells(i, 1) = strFormula
1 行目のコードについて質問のある方がいらっしゃるかもしれないと思っていました。通常、セルに数式を挿入する際には、次のような構文を使用する必要があります。
=AVERAGE(A240:A1)
信じられないかもしれませんが、1 行目のコードでは、まさにこの数式を作成しています。数式を作成するために、次の要素を結合します。
| • | =AVERAGE(A という文字列値。 |
| • | 現在の行から 1 を引いた値 (i – 1)。なぜ 1 を引くのかというと、現在位置が行 241 で、数式で平均を計算する対象となるのは行 1 〜 240 だけだからです。ですから、現在の行番号から 1 を引く必要があるのです。そうしないと、多くの循環参照エラーが発生してしまいます。 |
| • | :A という文字列値。 |
| • | 240 行のセットの、開始行。これはどのようにして計算するのでしょうか。カウンタ変数 i から 240 を引くのです。241 から 240 を引くと結果は 1 です。データセットの最初の行は行 1 なので、これは適切です。 |
| • | ) という文字列値。 |
これらをすべて結合すると、次のようになります。
=AVERAGE(A240:A1)
ええ、確かに、これはなんだか逆ですね。皆さんが一般的に想定するのは、次のように、A1 で始まって A240 で終わる数式でしょう。
=AVERAGE(A1:A240)
でも、別にかまいません。私たちは、この “逆” の方法の方が少し実行しやすいと思ったのです。それに、“逆” でも Excel 側では特に問題ありません。そのような数式を入力すると、Excel では自動的に修正されます。
Excel っていいやつですね。
現在のセルに数式を書き込んだら、次は、j の値を 0 にリセットします。なぜかというと、次の 240 行を数え始める必要があるからです。そのためには、次のように両方のカウンタ変数の値を 1 ずつ増加し、ループ処理の先頭に戻り、ワークシート内の次の行についてもこの処理を繰り返します。
j = j + 1 i = i + 1
ちなみに、240 行以外の間隔で空白行を挿入するようにこのスクリプトを変更するのは簡単です。たとえば、25 行おきに空白行を挿入する必要があるとします。これは簡単です。これを行うには、まず、次のように If Then ステートメントを変更して、j の値が 26 (目的の間隔より 1 大きい値) かどうかを確認するようにします。
If j = 26 Then
それから、次のように、数式を結合する際に間隔の値 (25) を引くのをお忘れなく。
strFormula = "=AVERAGE(A" & i - 1 & ":A" & i - 25 & ")"
とても簡単ですよね。
ああ、いいところに気が付きましたね。次のコード行は何のためにあるのでしょうか。
objWorksheet.Cells(i, 1).Interior.ColorIndex = 44
これは省略可能な処理ですが、数式のセルを A 列内のその他のセルと少し区別しやすくするために、数式のセルの背景色をオレンジに変更します (試してみれば、私たちの言いたいことがおわかりいただけると思います)。
ループを抜けたら、j の値が 0 かどうかを確認します。0 の場合は、最後の 1 組の全行の平均を計算したということなので、処理は完了です。j の値が 0 でない場合 (たとえば j の値が 113 の場合) は、平均の計算に含まれていない行が何行か (この場合は 113 行) あるということになります。次のコード ブロックを使用して、このちょっとした問題に対処します。
Set objRange = objWorksheet.Cells(i, 1).EntireRow objRange.Insert(xlShiftDown) strFormula = "=AVERAGE(A" & i - 1 & ":A" & i - j + 1 & ")" objWorksheet.Cells(i, 1) = strFormula objWorksheet.Cells(i, 1).Interior.ColorIndex = 44
これでようやく、処理は完了です。
何ですって。最後にもう 1 つ質問があるのですか。このコラムを執筆している Scripting Guy はワークシートに空白行と数式を挿入するという問題に対処し終わったので、膨大な時間を持て余しているのではないかということ、そして、持て余しているとしたら、彼は間もなく、ワシントン州ワラワラやアイダホ州モスコーよりもさらに風変わりな、新たな場所や新たな空港へ旅立つのではないかということを知りたいのですね。こう言わせてください。私たちは、そうならないことを心から願っています。
