投稿者: nagolabMH

[マニュアルでのマクロ作成]

入力された家計簿の明細データをもとに、プログラムを作成します。

①自動的に合計をだす。　　　　　　　　　　　　　　　　　　　　　　　②保存されている先月データの合計を取得、先月との差分を確認できるようにする。　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　③当月の家計簿データとしてファイルを作成して保存する

という仕組みを作成します。

では、実際にプログラムを作成します。２つのマクロに処理を分けています。「SetDateTokakeiSum()」では①、②の処理を行っています。

「ExportFile()」では③の処理を行っています。

では順に解説していきます。

プログラム上で使用している関数も同じモジュール内で作成しています。関数はFunction XXX ~ End Functionの中に記述し作成します。

<<CalcKakei関数>>

反復処理

For x=a to b　

※この中に反復して実行する処理を記述

Next x

※x=aの場合、 x=a+1の場合・・・・、x=bの場合というように、aに1づつ加算し、bになるまで反復して処理を実行します。

 

<<GetLastMonth関数>>

 

Left/Right(対象の文字、取得する文字数)：

•  Leftの場合は左から
• Rightの場合は右から

※ 指定した文字数の文字を戻り値として取得する関数

 

CStr/CInt(データ形式を変換するデータ)：Cstrは文字データに、Cintは整数データに引数として設定されたデータを変換する。

Format(対象のデータ、”表示したい表示形式”)：対象のデータの表示形式を指定した形式で表示します。

+/&：基本的には数値を加算する場合には+を利用。文字データを結合したい場合には&を利用する

<<GetThisMonth関数>>

[ボタンにマクロを登録する]

作成したマクロをそれぞれ、Excelシートの上のボタンをクリックすることで実行できるようにします。開発のメニュータブから、挿入/左上のボタンをクリックして、エクセルに2つのボタンを作成します。

作成の際に、当該ボタンに連携するマクロを指定するためのマクロ登録画面が表示されます。　　　　　　　　　　　　　　

消してしまった場合には、ボタンを選択して、右クリックでメニューを表示し、マクロの登録を選択して画面を起動します。　　　　　　　　　　　　　　　

それぞれのボタンに、SetDataToKakeiSum、ExportFileのマクロを登録します。各ボタンの文字を「集計」「ファイル作成」と変更します。(最初はButton1等と記載されています。)マクロを登録すると各ボタンをクリックすることで処理が実行されます。

前の投稿/メニューページへ戻る

マクロとは、Excel上で実行する各種操作/作業をプログラムを作成して、自動的に実行するプログラムです。(マクロもユーザー関数もVBAという言語で記述します。)

例えば、「あるエリアに入力されたデータを、別のエリアにコピーして、集計し、Excelを名前を付けて保存する」という作業が毎週発生しているとした場合に、データ入力後の処理を自動化することは比較的簡単にできます。最も簡単な方法は「マクロの記録」という方法を利用することによって、簡単に自動化ができます。

[マクロの記録と実行]

1. 最初にマクロの記録をクリックします。
2. マクロの記録画面が起動しますので、OKをクリックします。(本来は必要に応じて、マクロ名やマクロの保存先を設定しますが、今回はデフォルトのままとします。)
3. マクロの記録画面が終了したタイミングから、Excelに関連するすべての操作の記録がスタートします。基本的にはすべての操作が記録されます。
4. 下記は「Sheet1」です。下記の表を元に「Sheet2」で集計を行います。「Sheet2」には昨年の12月の成績がマクロの記録を行う前にピボットテーブルにて集計されているとします。今回は、古いデータをマクロの記録を使って、ピボットテーブルを最新の物に自動で更新できるようにするマクロを作成します。　　　　　　　　　　　　　

5. 「Sheet2」の集計用のシートを選択し、日付の部分に「Sheet1」の日付をコピー、また、同じ様に成績データの欄に「Sheet1」の成績のデータをコピーします。ここまでできたら、ピボットテーブルを更新してください。12月の古いデータが１月の最新のものになります。

6. 日付でファイルを保存します。ここまでを記録するものとして、メニューから記録終了をクリックして記録を終了します

7. 記録されたマクロは、記録をスタートさせる際のマクロ名でModule1に記録されます。(どこの標準モジュールに記録されるのかは、記録スタート時の設定によります。指定しない場合には、新規に標準モジュールが作成され、記録されます。)

8. マクロはSub xxx ～ End Subの形式で記述されます。関数との違いは、戻り値がないということです。中に記載されているプログラムを順番にし実行していくことになります。

9. 作成されたマクロを実行する場合には、メニューからマクロを選択して、マクロ画面を起動。実行したいマクロを選択して実行ボタンをクリックすると記録されたマクロが実行されます。

10. ▶ボタンをクリックで実行する事もできます。

11. 実行すると、「Sheet1」の最新データを「Sheet2」のピボットテーブルに自動で更新できるようになります。（「Sheet1」の成績データは最新のデータに書き換えなければ結果は変わりません。マクロ実行前に最新のものに書き換えるようにしましょう。）マクロの実行方法としては、自分で作成したものであっても同様の実行方法となります。

12. マクロ(プログラム)の中身を一つ一つ説明するとプログラム内部のコメントのようになります。

13. 操作しているすべての処理を記録していることから、本来必要でない処理/操作についても記録されているケースがあります。また、システム的に自動的に記録されているものが意図している操作と同様のものにならないことや、無駄な処理が入っている場合があります。その際には、プログラムを必要な部分だけ利用し、一部は効率的な処理に変更することにより、より効率的なプログラム(マクロ)を作成/用意することができます。

14. 自動で生成したプログラムを効率的な形で修正すると以下のようになります。

15. 効率的に処理を行うために、コピー元やコピー先のセルに名前を付けてプログラムの処理を見やすく/簡単になるように調整しています。また、プログラム上、わかりやすくするために保存するファイル名(パス付)を変数に定義しています。

16. 不要なコメントなどを削除し、さらに効率化すると以下のようになります。

17. このように、Excelには、処理の自動化を行うためのプログラムを自動的に記述(記録)する機能があり、さらに、そのプログラムを修正していくことで効率的にプログラムを作成することができます。当然、1から自分でプログラムを記述して作成することも可能です。また、その中で作成したプログラムや関数をプログラムの中で利用することもできます。

[デバッグ]

プログラムの記述は1回記述して、スムーズ動くプログラムを作成することは難しく、通常は複数回の錯誤の結果として出来上がるのが一般的で、プログラムを作成している過程で、処理がエラーで止まった場合にその原因を特定して修正する必要があります。そのエラーを特定する方法として、プログラムの検証機能がいくつか用意されています。

<<ウォッチングウィンドウ>>

1. ウォッチウインドウをメニューの表示/ウォッチウインドウから表示すると、通常はプログラムが記述されているウインドウの下に表示されます。

2. このウインドウに、変数などを直接記述するか、プログラムから特定の変数などを選択して、ドラッグ&ドロップでウォッチウインドウに表示します。

3. この操作により、対象の変数などが現在どのような値(その他情報も含めて)となっているのかを確認することができます。
   ※1行づつのプログラム実行などと併用して、プログラムの問題点を確認し、修正するのに利用します

<<１行づつプログラムを実行する>>

1. プログラムは通常実行するとすべての処理を連続的に最後まで実行されます。もし、1行づつ実行したい場合には、「F8キー」を押すと1ライン、プログラムが実行されます。実行している部分は黄色いハイライトで表示されます。

2. また、もし、どこかの処理で処理を停止したい場合にはプログラムの左端をクリックすると赤茶色のハイライトが表示されます。そうすると一機にプログラムを実行した場合でも、このハイライトの実行直前でプログラムが停止します。

   そこから、1行づつ実行して処理の正しいことを確認したい場合にはF8キーで1行づつ実行することが可能です。

   ※F5キーを押すと一機にすべてのプログラムを実行(▶ボタンと同じ)

[検索]

プログラムを記述している場合に、特定の変数等の特定の文字を検索して確認するケースがよく発生します。この場合に検索機能を利用します。メニューから編集/検索を選択して、検索画面を表示します。　　　　　　　　　　　　　　　　　　　　検索する文字列を入力後、次を検索をクリックすると、順番に検索対象の文字が選択されていくことになります。

 

前の投稿/次の投稿/メニューページへ戻る

[ピボットテーブル集計の元データの表示]

行に学期を追加することにより、集計単位が追加されます。(集計項目として学期が追加され、個人ごとの学期毎の合計が確認できます。)

ピボットテーブル内のデータをダブルクリックすると、集計データの元データが表示されます。〇をクリックして元データを表示させます。　　　　　　　　　元データが表示されました。

[ピボットテーブル表示形式の変更]

ピボットテーブル内のデータの表示形式を設定するためには、メニューの「表示形式」を選択してください。

　　　　　設定画面から設定を行います。設定するとデータすべてに設定した表示形式が反映されます。　　

[ピボットテーブル小計の有無及び表示制御]

[ピボットテーブル行/列に対しての集計を行うかなどの制御]

[ピボットテーブルの表示の体裁の制御]

[ピボットテーブルにフィルター機能]

ピボットテーブル内のデータに対して、フィルターをかけて、必要な情報だけを表示することが可能です。

また、ピボットテーブル全体に対してフィルターをかける場合には、上部の項目をドラッグ&ドロップで下段のフィルターの欄に配置することでフィルタを作成できます。実際に下記の手順で行ってみます。

1. フィルターに学期をドラッグします。
2. 学期をドラッグすると学期がフィルターとして集計表に追加されます。
3. 表示操作は設定したフィルターの▼をクリックしてフィルター設定画面を起動して設定します。

[ピボットグラフ]

作成したピボットテーブルのグラフを簡単に作成することが可能です。ピボットテーブルを選択し、メニュータブの分析/ピボットグラフを選択することでグラフの挿入画面を起動します。

表示したいグラフの種類を選択することでグラフが作成されます。　　　　　　　　　　　　グラフが作成されました。

前の投稿/メニューページへ戻る

ピポットテーブルを使って簡単に集計を行う事ができます。

[ピボットテーブルの作成]

下記のようなデータのクロス集計をピボットテーブルの機能を利用すると簡単に集計できます。まずは、集計対象データを選択します。この際にデータの項目行を含めて選択してください。(□の項目がない場合には集計が正確にできないので、項目行を設定してください。)

メニュータブの挿入からピボットテーブルを選択し、ピボットテーブルの作成画面を起動してください。

ピボットテーブルの作成画面から表の作成場所を指定してください。今回は、□の既存のワークシートに作成してみます。☑で選択し、OKボタンをクリックするとピボットテーブル作成の準備ができます。

※下記のようなピボットテーブルを作成する画面などが表示された状態となります。　　　　　　

ピボットテーブルのフィールドの画面にて表の項目を行、列、値にそれぞれ下記のように設定しました。

1. -行　→　名前
2. –列　→　科目
3. -Σ値　→　点数

　　　　　　　この操作だけで、下記の集計表が作成されます

[集計方法の変更]

Σ値は集計対象のデータを設定しますが、集計方法はいくつかの種類を選択することが可能です。設定した項目の▼部分をクリックしてメニューを起動し、値フィールドの設定を選択します。　　　　　　　　　　　設定画面が起動したら、集計方法をリストから選択することで集計方法が変更可能です。

【合計から個数を選択】

【合計から平均を選択】

[ピボットテーブルの更新]

ピボットテーブルの元データが変更された場合に、ピボットテーブルを選択し、右クリックでメニューを表示。更新を選択することで再集計されます。

[ピボットテーブルのデータソースの変更]

ピボットテーブルの元データの取得先や範囲を変更したい場合対象のピボットテーブルを選択し、メニュータブの分析/データソースの変更をクリックしてデータソースの変更画面を起動してください。

ピボットテーブルのデータソースの変更画面には、現在選択されているデータの範囲が表示され、選択範囲は破線にて囲み表示されますので、適宜必要な範囲/場所に設定しなおしてOKボタンをクリックすることで元データの取得先が変更されます。

[ピボットテーブルの削除]

ピボットテーブルを削除したい場合には、対象のピボットテーブルを選択してDeleteキーを押すことで削除可能です。

前の投稿/次の投稿/メニューページへ戻る