Home-theater-designers
Excelは強力です。あなたがそれをたくさん使うなら、あなたはおそらくすでに数式や自動フォーマットを使ったたくさんのトリックを知っていますが、それを利用しています 細胞 と 範囲 VBAの機能を使用すると、Excel分析をまったく新しいレベルに引き上げることができます。
VBAでCellsand Range関数を使用する際の問題は、上級レベルでは、ほとんどの人がこれらの関数が実際にどのように機能するかを理解するのに苦労していることです。それらを使用すると、非常に混乱する可能性があります。おそらく想像もしなかった方法でそれらを利用する方法は次のとおりです。
細胞機能
セル関数と範囲関数を使用すると、次のことがわかります。 VBAスクリプト ワークシートの正確に取得したい場所、またはデータを配置したい場所。 2つのセルの主な違いは、それらが参照するものです。
細胞 通常、一度に1つのセルを参照しますが、 範囲 セルのグループを一度に参照します。この関数の形式は次のとおりです。 セル（行、列） 。
これは、シート全体のすべてのセルを参照します。これは、Cells関数が単一のセルを参照しない1つの例です。
Worksheets('Sheet1').Cellsこれは、一番上の行の左から3番目のセルを参照します。セルC1：
Worksheets('Sheet1').Cells(3)次のコードはセルD15を参照しています。
Worksheets('Sheet1').Cells(15,4)必要に応じて、セルD15を「Cells（15、 'D'）」で参照することもできます---列文字を使用できます。
列とセルの数値を使用してセルを参照できるようにすることには、特に次のことができるスクリプトを使用する場合に、多くの柔軟性があります。 ループスルー 多数のセル（およびそれらのセルで計算を実行）を非常に迅速に実行します。これについては、以下で詳しく説明します。
範囲関数
多くの点で、範囲関数は、単一のセルまたは特定の範囲のセルを一度に参照できるため、セルを使用するよりもはるかに強力です。セルの参照は数値ではないため、Range関数をループする必要はありません（Cells関数をその中に埋め込んでいない限り）。
この関数の形式は次のとおりです。 範囲（セル＃1、セル＃2） 。各セルは文字番号で指定できます。
いくつかの例を見てみましょう。
FacebookとFacebookLiteの違いは何ですか
ここで、範囲関数はセル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コードでこれらの関数をクリエイティブに使用する方法について詳しく見ていきましょう。
セル機能によるデータ処理
セル関数は、複数のセル範囲にわたって実行する複雑な数式がある場合に最も役立ちます。これらの範囲は、複数のシートにまたがって存在する場合もあります。
簡単な例を見てみましょう。 11人の営業チームを管理していて、毎月その業績を確認したいとします。
あなたが持っているかもしれません Sheet1 それは彼らの販売数と彼らの販売量を追跡します。
オン Sheet2 ここで、会社のクライアントからの過去30日間のフィードバック評価を追跡します。
2枚のシートの値を使用して最初のシートのボーナスを計算する場合、これを行うには複数の方法があります。 2つのシートのデータを使用して計算を実行する数式を最初のセルに記述し、それを下にドラッグすることができます。それはうまくいくでしょう。
これに代わる方法は、シートを開くたびに実行するようにトリガーするか、シートのコマンドボタンによってトリガーして、いつ計算するかを制御できるVBAスクリプトを作成することです。とにかく、VBAスクリプトを使用して、外部ファイルからすべての販売データを取得することができます。
では、その時点で同じスクリプトのボーナス列の計算をトリガーしないのはなぜですか？
細胞は実際に機能している
これまでExcelでVBAを作成したことがない場合は、[開発者]メニュー項目を有効にする必要があります。これを行うには、に移動します ファイル >> オプション 。クリック リボンをカスタマイズする 。最後に、左側のペインから[開発者]を選択します。 追加 右側のペインに移動し、チェックボックスが選択されていることを確認します。
今、あなたがクリックすると わかった メインシートに戻ると、[開発者]メニューオプションが表示されます。
あなたは使用することができます 入れる メニューをクリックしてコマンドボタンを挿入するか、クリックするだけです コードを見る コーディングを開始します。
この例では、ブックを開くたびにスクリプトを実行します。これを行うには、クリックするだけです コードを見る 開発者メニューから、次の新しい関数をコードウィンドウに貼り付けます。
Private Sub Workbook_Open()
End Subコードウィンドウは次のようになります。
これで、計算を処理するコードを作成する準備が整いました。単一のループを使用して、11人の従業員全員をステップスルーし、Cells関数を使用して計算に必要な3つの変数を取得できます。
Cells関数には、個々のセルを識別するためのパラメーターとして行と列があることに注意してください。 'x'を行にし、数値を使用して各列のデータを要求します。行数は従業員数であるため、これは1から11になります。列IDは、販売数の場合は2、販売量の場合は3、フィードバックスコアの場合はシート2の2になります。
最終的な計算では、次のパーセンテージを使用して、合計ボーナススコアの最大100パーセントを合計します。これは、理想的な販売数が50、販売量が$ 50,000、フィードバックスコアが10であることに基づいています。
- （販売数/ 50）×0.4
- （販売数量/ 50,000）×0.5
- （フィードバックスコア/ 10）x 0.1
この単純なアプローチは、営業担当者に加重ボーナスを提供します。カウントが50、ボリュームが$ 50,000、スコアが10の場合、その月の最大ボーナス全体を獲得できます。ただし、どの要素でも完璧に満たないものはボーナスを減らします。理想より良いものはボーナスを後押しします。
次に、非常に単純で短いVBAスクリプトで、そのすべてのロジックをどのように実行できるかを見てみましょう。
Private Sub Workbook_Open()
For x = 2 To 12
Worksheets('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 x
End Sub
このスクリプトの出力は次のようになります。
ファイルエクスプローラーを暗くする方法
ボーナス列にパーセンテージではなく実際のドルボーナスを表示させたい場合は、最大ボーナス額を掛けることができます。さらに良いことに、その量を別のシートのセルに配置し、コードで参照します。これにより、コードを編集しなくても、後で値を簡単に変更できるようになります。
セル機能の優れている点は、データを取得するための非常にクリエイティブなロジックを構築できることです。 多くの細胞 多くの異なるシートにまたがって、いくつかを実行します かなり複雑な計算 彼らと一緒に。
セル機能を使用して、セルに対してあらゆる種類のアクションを実行できます。たとえば、セルのクリア、フォントの書式の変更などです。
さらにできることをすべて調べるには、 MicrosoftMSDNページ Cellsオブジェクトの場合。
範囲関数を使用したセルのフォーマット
一度に多くのセルをループする場合、Cells機能は完璧です。ただし、セルの範囲全体に一度に何かを適用する場合は、範囲関数の方がはるかに効率的です。
この使用例の1つは、特定の条件が満たされた場合に、スクリプトを使用してセルの範囲をフォーマットすることです。
たとえば、すべての販売員のすべての販売量の合計が合計$ 400,000を超える場合、チームが追加のチームボーナスを獲得したことを示すために、ボーナス列のすべてのセルを緑色で強調表示するとします。
それをどのように行うことができるかを見てみましょう IFステートメント 。
Private Sub Workbook_Open()
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then
ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4
End If
End Subこれが実行されると、セルがチームの目標を超えている場合、範囲内のすべてのセルが緑色で塗りつぶされます。
これは、範囲関数を使用してセルのグループに対して実行できる多くのアクションの簡単な例の1つにすぎません。あなたができる他のことは次のとおりです。
- グループの周りにアウトラインを適用する
- セル範囲内のテキストのスペルを確認してください
- セルをクリア、コピー、またはカットする
- 「検索」メソッドを使用して範囲を検索します
- はるかに
必ずお読みください MicrosoftMSDNページ Rangeオブジェクトがすべての可能性を確認できるようにします。
Excelを次のレベルに引き上げる
Cells関数とRange関数の違いを理解したので、次はVBAスクリプトを次のレベルに引き上げます。 Excelでのカウントおよび追加関数の使用に関するDannの記事を使用すると、すべてのデータセットにわたって値を非常に迅速に蓄積できるさらに高度なスクリプトを作成できます。
また、ExcelでVBAを使い始めたばかりの場合は、すばらしいものがあることを忘れないでください。 ExcelVBAの入門ガイド あなたにも。
共有 共有 つぶやき Eメール キヤノン対ニコン：どちらのカメラブランドが優れていますか？キヤノンとニコンは、カメラ業界で最も有名な2社です。しかし、どのブランドがより良いカメラとレンズのラインナップを提供していますか？
次を読む 関連トピック- 生産性
- プログラミング
- VisualBasicプログラミング
- マイクロソフトエクセル
ライアンは電気工学の理学士号を取得しています。彼は自動化エンジニアリングで13年間、ITで5年間働いており、現在はアプリエンジニアです。 MakeUseOfの元編集長であり、データの視覚化に関する全国会議で講演され、全国のテレビやラジオで取り上げられました。
RyanDubeのその他の作品ニュースレターを購読する
ニュースレターに参加して、技術的なヒント、レビュー、無料の電子書籍、限定セールを入手してください。
購読するにはここをクリックしてください