2.Excel – ITって

2.3.3.マクロ②

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

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

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

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

では、実際にプログラムを作成します。２つのマクロに処理を分けています。「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等と記載されています。)マクロを登録すると各ボタンをクリックすることで処理が実行されます。

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

2.3.3.マクロ①

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

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

[マクロの記録と実行]

最初にマクロの記録をクリックします。
マクロの記録画面が起動しますので、OKをクリックします。(本来は必要に応じて、マクロ名やマクロの保存先を設定しますが、今回はデフォルトのままとします。)
マクロの記録画面が終了したタイミングから、Excelに関連するすべての操作の記録がスタートします。基本的にはすべての操作が記録されます。
下記は「Sheet1」です。下記の表を元に「Sheet2」で集計を行います。「Sheet2」には昨年の12月の成績がマクロの記録を行う前にピボットテーブルにて集計されているとします。今回は、古いデータをマクロの記録を使って、ピボットテーブルを最新の物に自動で更新できるようにするマクロを作成します。　　　　　　　　　　　　　
「Sheet2」の集計用のシートを選択し、日付の部分に「Sheet1」の日付をコピー、また、同じ様に成績データの欄に「Sheet1」の成績のデータをコピーします。ここまでできたら、ピボットテーブルを更新してください。12月の古いデータが１月の最新のものになります。
日付でファイルを保存します。ここまでを記録するものとして、メニューから記録終了をクリックして記録を終了します
記録されたマクロは、記録をスタートさせる際のマクロ名でModule1に記録されます。(どこの標準モジュールに記録されるのかは、記録スタート時の設定によります。指定しない場合には、新規に標準モジュールが作成され、記録されます。)
マクロはSub xxx ～ End Subの形式で記述されます。関数との違いは、戻り値がないということです。中に記載されているプログラムを順番にし実行していくことになります。
作成されたマクロを実行する場合には、メニューからマクロを選択して、マクロ画面を起動。実行したいマクロを選択して実行ボタンをクリックすると記録されたマクロが実行されます。
▶ボタンをクリックで実行する事もできます。
実行すると、「Sheet1」の最新データを「Sheet2」のピボットテーブルに自動で更新できるようになります。（「Sheet1」の成績データは最新のデータに書き換えなければ結果は変わりません。マクロ実行前に最新のものに書き換えるようにしましょう。）マクロの実行方法としては、自分で作成したものであっても同様の実行方法となります。
マクロ(プログラム)の中身を一つ一つ説明するとプログラム内部のコメントのようになります。
操作しているすべての処理を記録していることから、本来必要でない処理/操作についても記録されているケースがあります。また、システム的に自動的に記録されているものが意図している操作と同様のものにならないことや、無駄な処理が入っている場合があります。その際には、プログラムを必要な部分だけ利用し、一部は効率的な処理に変更することにより、より効率的なプログラム(マクロ)を作成/用意することができます。
自動で生成したプログラムを効率的な形で修正すると以下のようになります。
効率的に処理を行うために、コピー元やコピー先のセルに名前を付けてプログラムの処理を見やすく/簡単になるように調整しています。また、プログラム上、わかりやすくするために保存するファイル名(パス付)を変数に定義しています。
不要なコメントなどを削除し、さらに効率化すると以下のようになります。
このように、Excelには、処理の自動化を行うためのプログラムを自動的に記述(記録)する機能があり、さらに、そのプログラムを修正していくことで効率的にプログラムを作成することができます。当然、1から自分でプログラムを記述して作成することも可能です。また、その中で作成したプログラムや関数をプログラムの中で利用することもできます。

[デバッグ]

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

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

ウォッチウインドウをメニューの表示/ウォッチウインドウから表示すると、通常はプログラムが記述されているウインドウの下に表示されます。
このウインドウに、変数などを直接記述するか、プログラムから特定の変数などを選択して、ドラッグ&ドロップでウォッチウインドウに表示します。
この操作により、対象の変数などが現在どのような値(その他情報も含めて)となっているのかを確認することができます。
※1行づつのプログラム実行などと併用して、プログラムの問題点を確認し、修正するのに利用します

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

プログラムは通常実行するとすべての処理を連続的に最後まで実行されます。もし、1行づつ実行したい場合には、「F8キー」を押すと1ライン、プログラムが実行されます。実行している部分は黄色いハイライトで表示されます。
また、もし、どこかの処理で処理を停止したい場合にはプログラムの左端をクリックすると赤茶色のハイライトが表示されます。そうすると一機にプログラムを実行した場合でも、このハイライトの実行直前でプログラムが停止します。

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

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

[検索]

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

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

2.3.2.ユーザー関数(2)

関数を作成する際に、プログラムで利用できるさまざまな関数が用意されています。

また、Excel上で利用できる関数もプログラムの中で利用することができます。

下記と同じプログラムを記載し、関数名を少し変えて動くかどうかを確認してみましょう。

・「Len(文字データ)」は文字データの文字数を返す関数です。

・「Vlookup」はExcelのワークシート関数。利用する場合には、Application.関数名として利用可能(利用できない関数もあり)です。利用の際には引数に入れるデータについて考慮が必要な場合があります。

・今回の引数はRangeデータ形式です。Excelシートのセル(もしくは複数セル)を引数としています。Rangeというデータ形式はExcel独特のデータ形式です。

【IF関数の処理】

Excelの関数でもプログラム的な動きをするものがあります。

左記のIF関数は条件が真(True)の場合、偽(False)の場合で処理を変更することが可能です。ただし、複雑なものを記述しようとすると複雑になり、何の処理をしているのかが混乱し、わかりにくくなってしまいます。

ユーザー関数やマクロ等ではこれと同じようなことをプログラムを記述して実現します。ただし、より複雑な処理をわかりやすく記載することができます。

【複雑なユーザー関数(条件分岐処理)】

より複雑なユーザー関数を作成するためには少しプログラム的な記述を行う必要があります。

その際の基本となるのは、

　条件分岐処理(特定の条件毎に違う処理を行う)

　反復処理(複数回、反復して処理を行う)

になります。

まず、条件分岐処理ですが、例えば、ある数字が奇数であれば、奇数、偶数であれば偶数という文字を返す関数を作成します。

If 「条件」 then

「処理を記載」

Else

　「処理を記載」

End IF

【複雑なユーザー関数(反復処理)】

反復処理(For Each x in y)

例えば、引数としてある範囲のデータを与えて、各データを2乗して、すべて足した答えを返します。

処理順序として

aがセル”E3”の場合

kotae=0

0+28の2乗=784

aがセル”E4”の場合

kotae=784

784+58の2乗=4,148

・

aがセル”E14”の場合

kotae=22,847

22,847+38の2乗=24,291

ここまで反復して、処理が終了します。

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

2.3.2.ユーザー関数(1)

Excelでは、さまざま関数が提供されています。関数は処理を単純化するのに役立つとともに、Excelの表計算を見やすくすることができます。

このような関数をユーザーが自由に開発することができる機能が提供されています。

まず、簡単なユーザー関数を作成しましょう。

ユーザー関数を記述するための画面を起動します。

【ユーザー関数を使うための準備】

①ユーザタブから開発を選択し、Visual Basicを選択。Microsoft Visual Basic for Applicationsの画面を起動します。

②プロジェクトのエリアを選択して、右クリックし、メニューから挿入/標準モジュールを選択します。

③右側のグレーなエリアにModule1という画面が表示されると、関数を作成する準備ができたことになります。

【ユーザー関数を作成する】

①まずは、単純にコードの記述を写して記述してみてください。

②次に標準モジュールの名前をプロパティのオブジェクト名をUserKansuと修正して、名称を変更し、保存します。これで、Excel上で他の関数と同様に利用することができます。

③これで、Excel上で他の関数と同様に利用することができます。

④保存する場合には、拡張子はxlsmで保存する必要があります。xlsxのまま保存すると、プログラム部分は保存されません。

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

2.3.1.マクロ機能を使うための準備

マクロ/VBAを利用するためには、メニュータブからファイル/オプションを選択し、オプション画面を起動します。

オプション画面からリボンのユーザー設定を選択して、右下の「開発」にチェックを入れます。これにより、メニュータブに開発のタブが追加されます。

また、同様にオプション画面から、セキュリティセンターを選択し、セキュリティセンターの設定をクリックし、セキュリティセンターの画面を起動します。

セキュリティセンターの画面からマクロの設定を選択し、「すべてのマクロを有効にする」を選択します。

これにより、マクロ機能を利用することができるようになります。

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

2.2.8.ピボットテーブル②

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

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

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

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

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

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

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

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

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

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

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

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

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

[ピボットグラフ]

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

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

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

2.2.7.ピボットテーブル①

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

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

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

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

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

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

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

-行　→　名前
–列　→　科目
-Σ値　→　点数

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

[集計方法の変更]

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

【合計から個数を選択】

【合計から平均を選択】

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

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

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

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

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

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

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

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

2.2.4.計算式・関数

Excelには、面倒な計算や複雑な計算を簡潔に記述できるように登録された仕組みがあります。そのような仕組みを関数といいます。今回は3つの関数を覚えましょう。

[関数]

SUM関数
AVERAGE関数
VLOOKUP関数

下図のページでは、検索条件に一致したデータを指定範囲の中から取り出す関数を学習します。

[関数の検索]

[名前を付けたセルを関数で利用する]

[よく利用される関数]

IF　　　　 ⇒条件を満たす/満たさないで処理を変える場合に使用する

COUNT ⇒数値や日付、時刻の個数を求める

COUNTIF ⇒条件に合うデータを数える(10より大きい値が何個あるか等)

Date　　　⇒日付を求める

MAX/MIN ⇒範囲内の最大値を求める(MAX)範囲内の最小値を求める(MIN)

VALUE ⇒数値を表す文字列を数値に変換する

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

2.2.3.セル・データの操作

[セル]

セルに名前を付ける　　　　　　　　　　　　　　　　　　　　　　　　　
名前を付けたセルの参照　　　　　　　　　　　　　　　　　　　　
名前を付けたセルの管理

[データの操作]

日付の表示形式　　　　　　　　　　　　　　　　　　　　　　　　　　　　
・Excelでは日付は特殊なデータとなります。図のデータはすべて同じデータとなります。すべて書式の設定により、表示形式をコントロールしています。中でも「43101」というデータは日付データに見えませんが、Excelでは日付データは数値としてとらえられています。日付データは「1900/01/01 00:00」を1として、1日毎に1増えていきます。なので、43101は1900/1/1から、43100日目ということになります。　　　　　　　　　　　　　　　　　　　時間は少数で表現されます。0.5は12:00ということになります。書式はユーザーがルールに基づき作成することが可能です。(ユーザー定義)一番下段の書式は、もともと存在しない書式をセルの書式設定からマニュアルで作成しています。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
リンクを設定する　　　　　　　　　　　　　　　　　　　　　　　　　　リンクとは、クリックする事により他の場所に移動できる仕組みの事です。　　　　　　　　　　
リストからデータを入力する　　　　
セルの相対位置・絶対位置　　　　　　　
参照先のトレース