Excelは強力です。普段から使っている方は、数式や自動書式設定の使い方のコツをすでにご存じだと思いますが、VBAのセルや範囲関数を使えば、Excelの分析をまったく新しい次元に引き上げることができます。

VBAでCells関数やRange関数を使用する際の問題は、上級者になると、これらの関数が実際にどのように機能するのかを理解するのに苦労することです。使い方は非常にわかりにくいものです。ここでは、想像もしなかったような活用方法をご紹介します。

細胞の機能

セル」と「範囲」関数を使うと、VBAスクリプトに、ワークシート上のデータの取得場所や配置場所を正確に指示することができます。この2つのセルの主な違いは、参照するコンテンツです。

この関数は、セル(行、列)の書式で表示されます。

これは、Cells関数が1つのセルを参照しない例です。

Worksheets("Sheet1").Cells

これは、最初の行の左から3番目のセルを参照しています。

Worksheets("Sheet1").Cells(3)

次のコードは、セルD15を参照しています。

Worksheets("Sheet1").Cells(15,4)

ご希望であれば、セルD15を参照するために「セル（15、D）」を使用することもできます - 列文字を使用することができます。

列やセルへの数値参照は、特に大量のセルを高速にループさせる（その上で計算を行う）スクリプトでは、非常に柔軟な使い方が可能です。これについては、後ほど詳しく説明します。

レンジ機能

一つのセルや特定の範囲のセルを同時に参照できるRange機能は、多くの点でセルを使うよりずっと強力です。セル参照は数字ではないので、Range関数をループさせることはないでしょう（セル関数を埋め込んでいる場合は別ですが）。

この関数の書式は範囲（セル#1, セル⑭2）であり、各セルは文字番号で指定できる。

いくつかの例を見てみましょう。

ここでは、範囲指定関数はセルA5を参照しています。

Worksheets("Sheet1").Range("A5")

ここでは、範囲指定関数はA1からE20までのすべてのセルを参照する。

Worksheets("Sheet1").Range("A1:E20")

前述したように、セルの割り当ては数字とアルファベットを使う必要はない。実は、Range関数の中の2つのCells関数を使って、次のようにワークシート上の範囲を特定することができます。

With Worksheets("Sheet1") .Range(.Cells(1, 1), _ .Cells(20, 5))End With

上のコードは、range（"A1:E20"）関数が参照する範囲と同じ範囲を参照しています。これを使う価値は、ループを使って動的に範囲を処理するコードを書けるようになることです。

セルの書式設定と範囲指定関数を理解したところで、これらの関数をVBAコードで創造的に使用する方法について説明します。

cels関数でデータを扱う

セル機能は、複数のセル領域で複雑な数式を実行したい場合に最も有効です。これらの範囲は、複数の図面に存在することもできます。

例えば、11人の営業チームを管理し、毎月その営業成績がどうなっているかを確認したいとします。

その売上や販売量を記録するためのテーブルを用意してもよい。

表2には、過去30日間の自社の顧客のフィードバック評価を把握することができます。

両方のシートの値を使って、1枚目のシートのボーナスを計算したい場合、いろいろな方法があります。最初のセルに数式を書き、両方のワークシートのデータを使って計算を実行し、それを下にドラッグすることができます。それでいい。

もう一つの方法は、VBAスクリプトを作成して、ワークシートを開いたときに実行するようにトリガーしたり、計算するタイミングを制御するために、ワークシートのコマンドボタンから実行するようにトリガーすることができます。VBAスクリプトを使用して、外部ファイルからすべての売上データを抽出することができます。

では、同じスクリプトでボーナスカラムの計算をトリガーしてはどうでしょうか。

細胞の働き

ExcelでVBAを書いたことがない場合、「開発者」メニュー項目を有効にする必要があります。そのためには、「ファイル >オプション」を開きます。リボンのカスタマイズ」をクリックします。最後に、左ペインからDeveloperを選択し、右ペインに追加して、チェックボックスが選択されていることを確認します。

さて、OKをクリックしてメインのワークシートに戻ると、「開発者」メニューのオプションが表示されます。

メニューの**コマンドボタンを使うか、"View Code "をクリックしてコーディングを開始することができます。

この例では、ワークブックを開くたびにスクリプトが実行されるようにします。そのためには、DeveloperメニューのView Codeをクリックし、以下の新しい関数をCodeウィンドウに貼り付けるだけです。

Private Sub Workbook_Open() End Sub

コードウィンドウはこのように表示されます。

これで、計算を処理するためのコードを書くことができます。単一のループを使用することで、11人の従業員全員を一度に反復処理し、Cells関数を使って計算に必要な3つの変数を取り込むことができます。

Cells関数は、各セルを識別するためのパラメータとして、行と列を持つことを忘れないでください。行を "x "とし、各列のデータを数値で要求しています。行数は従業員数なので1から11まで、列の識別子は2が売上数、3が売上高、2ページ目から2がフィードバックスコアとなります。

最終的な計算では、以下のパーセンテージを使用して、ボーナススコアの合計が100％になるようにします。理想的な販売数50、売上高5万ドル、フィードバックスコア10を基準としています。

• (販売台数／50台）×0.4
• (売上高／50,000）×0.5
• (フィードバックスコア／10）×0.1

このシンプルな方法は、営業マンにボーナスの重み付けをするものです。50ポイント、5万円、10ポイントで、その月の最高額のボーナスを獲得します。ただし、何らかの要因でパーフェクトを達成した場合は、ボーナスが減少します。理想的な状態よりも良い場合は、ボーナスが増加します。

それでは、すべてのロジックが非常にシンプルで短いVBAスクリプトで実装される方法を見てみましょう。

Private Sub Workbook_Open()For x = 2 To 12Worksheets("Sheet1").Cells(x, 4) = (Worksheets("Sheet1").Cells(x, 2).Value / 50) * 0.4 _ + (Worksheets("Sheet1").Cells(x, 3).Value / 50000) * 0.5 _ + (Worksheets("Sheet2").Cells(x, 2).Value / 10) * 0.1 _Next xEnd Sub

これがこのスクリプトの出力です。

ボーナス欄にパーセンテージではなく、実際のドルボーナスを表示させたい場合は、最大ボーナス額を掛けることができます。より良い方法は、その金額を別のワークシートのセルに入れ、コードでそれを参照することです。こうすることで、後でコードを編集することなく、簡単に値を変更することができます。

セルズ機能の優れた点は、複数の異なるテーブルの複数のセルからデータを抽出し、それを使って非常に複雑な計算を行うという、非常に独創的なロジックを構築できることです。

セルを消去したり、フォントの書式を変更したりと、セルに対するさまざまな操作をセルス機能で行うことができます。

できることの詳細については、Cellsオブジェクトのmicrosoftsdnのページをご覧ください。

範囲指定機能によるセルの書式設定

セル機能は、複数のセルを一度に循環させるのに最適です。しかし、セルの全範囲に一度に何かを適用したい場合は、RANGE関数の方がはるかに効率的です。

一定の条件を満たせば、一連のセルをスクリプトで書式設定することができる。

例えば、営業スタッフ全員の売上合計が40万ドルを超えた場合、ボーナス欄のすべてのセルを緑色にハイライトして、チームに追加のチームボーナスが支給されたことを示します。

IF文を使ってどのように実現できるかを見てみましょう。

Private Sub Workbook_Open()If Worksheets("Sheet1").Cells(13, 3).Value > 400000 Then ActiveSheet.Range("D2:D12").Interior.ColorIndex = 4End IfEnd Sub

実行すると、あるセルがチームターゲットから外れている場合、範囲内のすべてのセルが緑色に塗りつぶされます。

これは、Range機能を使って複数のセルのグループに対して実行できる多くの操作のほんの一例です。他にも以下のようなことができます。

• グループの周囲にアウトラインを描く
• セル領域内のテキストのスペルチェック
• セルのクリア、コピー、カット
• Find」メソッドで範囲を検索する
• もっと見る

Rangeオブジェクトのmicrosoftsdnのページを読んで、すべての可能性を確認してください。

エクセルを次のレベルへ

ExcelのCounts関数とRange関数の違いを理解したところで、VBAスクリプトを次のレベルに進める時が来ました。dannの記事「Excelでcountsとadd関数を使う」では、すべてのデータセットにわたって値を非常に速く蓄積できる、より高度なスクリプトを構築することができます。

ExcelでVBAを始めたばかりの方は、excelvbaを始めるための優れたガイドも提供していますので、どうぞお忘れなく。