アフィリエイト広告を利用しています
ファン
最新記事
カテゴリーアーカイブ
日別アーカイブ

広告

この広告は30日以上更新がないブログに表示されております。
新規記事の投稿を行うことで、非表示にすることが可能です。
posted by fanblog

2018年09月30日

ExcelVBAで車庫証明アプリを作ろう

【パソコン買取アローズ】






以前紹介した「車庫証明」アプリですが、何回にも分かれた細切れ状態だったので

まとめてみました。

車庫1.png

上は架空のマンションの駐車場です。3階建てとします。

1階はbP〜bP0・2階はbP1〜bQ0・3階はbQ1〜bR0とします。

車庫2.png

架空のマンションの住人を上記の名簿の方とします。

マンションは6階建てでワンフロアに5部屋とします。

分かりやすいように順番に駐車場を契約しているとします。

※105号室は契約者と使用者が違うという事にします。(賃貸という事です)

車庫3.png

上記のような標準的な「車庫証明」とします。

これはExcelシートで作ってくださいね。


具体的なイメージとして

・部屋番号を入力し、「発行ボタン」を押すと自動的に部屋番号・電話番号・枠番号・

 使用者と契約者の関係・使用者と契約者の氏名が記載された「車庫証明」が印刷され、
 
 一緒に「駐車区画」と周辺地図が印刷され、発行した履歴が残るという動作を考えています。


実際は戸数も500戸あり、駐車場も500台あります。

またマンションの所有者と車両の所有者が違う「賃貸」の場合とか、

同じ家庭内でも使用者が違う場合とかがありますね。

そのあたりの様々な条件を踏まえて作成しております。

今回はある程度端折っているので少し簡単になると思います。

それでもできるだけ実際に近い形でご紹介したいと思います。


今エクセルのページとしては「メイン」「名簿」「車庫証明」「車庫図面」「地図」

「発行履歴」と6ページあります。

実際はメインページを入力ページとして、そこに入力セル・発行ボタンを配置しています。


今回はせっかくなので入力フォームを作ってみましょう。

メインページを選択(アクティブ)すると入力フォームが開くという事にしましょう。





入力フォームの内容

 ・部屋番号を入力するテキストボックス

 ・使用者と契約者の関係を選択する「オプションボタン」の設置と、
  
  その他を選択した場合の記入用テキストボックス
 
・発行ボタン

一応上記くらいですね。

実際は1家庭で2台以上持っておられる方もいますので、駐車場のデータベースもあり、

居住者のデータベースと別になっています。

そのため、「部屋番号」と「駐車場番号」の両方を入力するようになっていて、

整合性がなければ赤表示になり間違いを示唆するようになっています。


とりあえず入力フォームを作ってみました。

車庫4.png

「部屋番号を入力してください」というところは「ラベル」ですね。

そしてフレームを入れて「使用者と契約者の関係」という名前を付けています。

その他の下にあるテキストボックスは「その他」の場合にどのような関係か記入するところです。

部屋番号を入力するテキストボックスは数字が入るのでフォームのテキストボックスの

プロパティの中にある「IMEMode」で「8-fmIMEModeAlpha」にします。

ここの設定の詳細ですが

車庫5.png

0-fmIMEModeNoControl・・・IMEのモードを変更しない

1-fmIMEModeOn・・・IMEをONにします

2-fmIMEModeOff・・・IMEのモードOFFにします

3-fmIMEModeDisable・・・IMEのモードをOFFにします。
            
             このモードではユーザーのキー操作でもIMEをONする
            
             ことはできなくなります

4-fmIMEModeHiragana・・・全角ひらがなにします

5-fmIMEModeKatakana・・・全角カタカナにします

6-fmIMEModeKatakanaHalf・・・半角カタカナにします

7-fmIMEModeAlphaFull・・・全角英数モードにします

8-fmIMEModeAlpha・・・半角英数モードにします

上記のようにテキストボックスのIME設定が出来ます。





最初にメインページにフォームを開くボタンを設置しましょう。

メインページに「車庫証明発行」ボタンを設置しました。

車庫6.png

フォームを開くVBAを記載しています。

車庫7.png

メインページの「車庫証明発行」ボタンを押すと上記のようにフォームが開きます。


ここから「印刷」ボタンのVBAを考えて行きましょう。

動作として考えなくてはいけない点を順不同で列挙していきます。

・「使用者と契約者の関係」で初期状態を「同じ」に設定する。

・部屋番号が入力されていない状態で「印刷」ボタンを押された場合の動作
  
    ・・・「部屋番号を入力してください」というメッセージを表示。

・「使用者と契約者の関係」で「その他」を選択された場合は、

 関係性を記入してもらう必要があるが、記入せずに「印刷」ボタンを押された場合の動作。

    ・・・関係性を入力してくださいというメッセージを表示。

・部屋番号を車庫証明シートの2か所に入力

・氏名(使用者と契約者の2か所)の入力

・電話番号の入力・・・これも2か所

・使用者と契約者の欄に丸数字で入力し、その他の場合は関係性を入力する

・車庫図面の該当区画に色を付ける

・車庫証明の印刷

・車庫図面の印刷(該当ページのみ)

・地図の印刷

・発行履歴への記載

大体このくらいでしょうか。

それから後でこのフォームに「終了」ボタンと取扱い説明を追加しましょう。


もう一つ、一度発行して、終了するとその方の部屋番号・名前・電話番号等々と

車庫図面の色が残ったままですね。次に作成するときに残ったままだと

好ましくないのでどこかのタイミングで初期化しましょう。

印刷ボタンを押して、最初にその処理を入れるか、または印刷終わってから

処理を入れるかですか、大体私は最初に入れております。

印刷後に入れた場合、何らか不具合等で印刷が実行できなかった場合、

初期化されていると入力からやり直す必要があります。

最初に処理を入れておけば、印刷が失敗しても、データーは残っているので、

個別で印刷すればOKだからです。


このくらいの要素を入れてVBAを作成すれば問題ないでしょう。

実際は使用者と契約者の電話番号も違うので別々の処理を入れています。

また使用者と契約者が同じ場合は「同じ」というボタンを入れて処理しています。

また駐車場図面は2シートあって全部で6ページあります。

それ以外に「車庫証明発行に際して」という注意文書も一緒に印刷しております。

Hulu




とりあえずフォームに終了ボタンと説明書を追加しましょう。

車庫8.png

説明と「終了」ボタンを追加して、レイアウトも若干さわりました。

・最初に前回のデータを消去することから始めましょう。

フォームの「印刷」ボタンをダブルクリックしてVBAを記載できるようにして

下記のコードを書きました。

Private Sub CommandButton1_Click()

Sheets("車庫証明").Range("g6").ClearContents

Sheets("車庫証明").Range("g9").ClearContents

Sheets("車庫証明").Range("m7").ClearContents

Sheets("車庫証明").Range("m10").ClearContents

Sheets("車庫証明").Range("f7:g7").ClearContents

Sheets("車庫証明").Range("f10:g10").ClearContents

Sheets("車庫証明").Range("n4:r4").ClearContents

Sheets("車庫証明").Range("p6:p9").ClearContents

Sheets("車庫証明").Range("p6") = 1

Sheets("車庫証明").Range("p7") = 2

Sheets("車庫証明").Range("p8") = 3

Sheets("車庫証明").Range("p9") = 4

End Sub


とにかく一つ一つ書いてあるので非常に分かりやすいと思いますので説明は省きます。

ClearContentsもまとめて指定することも出来ます。

例)

Sheets("車庫証明").Range("g6,g9,m7").ClearContentsとすると3つのセルが選択され、

消去されます。

・使用者と契約者の関係ですが、初期状態は「同じ」にしましょう。

車庫9.png

上記のように「UserForm」の「Initialize」にして、

Private Sub UserForm_Initialize()

OptionButton1.Value = True

End Sub

上記のように記載しましょう。

フォームを開いたときに「同じ」にチェックが入っています。

・「部屋番号」を入力されないで「印刷」ボタンを押された場合はエラーメッセージが

 表示されるようにしましょう。

車庫10.png

If UserForm1.TextBox1 = "" Then

MsgBox ("部屋番号を入力してください")

Exit Sub

End If

上記のように記載しました。内容は分かりますね。

車庫11.png

上記は部屋番号を記入せずに「印刷」ボタンを押したときの状態です。


次に「使用者と契約者の関係」で「その他」を選択した場合「関係性」を記入しなかった

場合もエラーメッセージを出しましょう。

車庫12.png

If UserForm1.OptionButton4 = True Then

If UserForm1.TextBox2 = "" Then

MsgBox ("関係性を入力してください")

Exit Sub

End If

End If

上記のように記載しました。

これも非常にシンプルに記述していますので、分かりますね。

車庫13.png

上記は部屋番号を入力し、「その他」を選択したが、「関係性」を空白のまま

「印刷」ボタンを押したときの状態です。

ここまでで、最初の仕掛けは出来たと思うので、ここからは入力に従って、

車庫証明にコピーして行きましょう。

注意するところは使用者と契約者が違う場合だけですね。「同じ」を選択した時は、

同じものをコピーすればOKですね。

それ以外は使用者(賃貸者)をコピーする。まあ実際はもっと複雑ですが(本店・支店等)、

今回はこのようにしましょう。





名簿の修正

最初に名簿を作成した時、あまり深く考えずに下記のように

車庫14.png

氏名・住所・部屋番号・駐車場aE電話番号・使用者という並びで作ったのですが、

部屋番号で検索するにはA列に「部屋番号」を持ってきた方が良いと判断し、

下記のように修正しました。

車庫15.png

簡単に言うと、「部屋番号」を基準にしてVlookup関数を使用したいためです。

Vlookup関数は検索するセルから見て右側しか抽出できません。

もちろん、以前紹介したように、他の関数と組み合わせれば、左側も抽出できるのですが、

今回はシンプルにしたいと思うので上記のように変更して、Vlookup関数だけ使用したいと思います。


それから105号室は「賃貸」という前提で進めていますが、本来ならば、

オーナー様は別住所で、賃貸の「塚田さとる」がこの住所という事になります。

本来ならば、契約者の住所と使用者の住所は別になります。

この部分もとりあえず同じにしておきましょう。

初期費用0円!格安スマホ【UQmobile】


車庫証明へのコピー

保管場所の使用者欄でF6に住所・J6に部屋番号・M7に電話番号ですね。

保管場所の契約者欄はF9に住所・J9に部屋番号・M10に電話番号ですね。

それから、枠番号がN4になり、使用者と契約者の関係がP6〜P9になります。

その際、4その他を選択された場合、関係性をQ10にコピーする必要があります。


こちらで使用しているものは先ほど記載したように、オーナー様と賃貸者の両方の住所・

電話番号欄があり、「使用期間」も入力できるようになっています。

後、入力欄には部屋番号と駐車場bフ両方を入力するようにしております。

両方ともデータベースがあり、両方の整合性が取れない場合はエラーとなります。

車庫16.png

上記のように記載しました。

VBA上で関数を使用する場合は上記のように、

値 = WorksheetFunction.VLookup(検索値,検索範囲, 列数)という記載になります。

現時点、賃貸に関する考慮はされておりません。

Dim n As Long

Dim tbl As Range

Set tbl = Sheets("名簿").Range("a2:g29")

n = UserForm1.TextBox1.Value


Sheets("車庫証明").Range("m7").ClearContents

Sheets("車庫証明").Range("m10").ClearContents

Sheets("車庫証明").Range("j6").ClearContents

Sheets("車庫証明").Range("j9").ClearContents

Sheets("車庫証明").Range("f7:g7").ClearContents

Sheets("車庫証明").Range("f10:g10").ClearContents

Sheets("車庫証明").Range("n4:r4").ClearContents

Sheets("車庫証明").Range("p6:p9").ClearContents

Sheets("車庫証明").Range("q10").ClearContents

Sheets("車庫証明").Range("p6") = 1

Sheets("車庫証明").Range("p7") = 2

Sheets("車庫証明").Range("p8") = 3

Sheets("車庫証明").Range("p9") = 4


Sheets("車庫証明").Range("F6").Value = WorksheetFunction.VLookup(n, tbl, 3, False)

Sheets("車庫証明").Range("j6").Value = WorksheetFunction.VLookup(n, tbl, 1, False)

Sheets("車庫証明").Range("m7").Value = WorksheetFunction.VLookup(n, tbl, 5, False)

Sheets("車庫証明").Range("F9").Value = WorksheetFunction.VLookup(n, tbl, 3, False)

Sheets("車庫証明").Range("J9").Value = WorksheetFunction.VLookup(n, tbl, 1, False)

Sheets("車庫証明").Range("m10").Value = WorksheetFunction.VLookup(n, tbl, 5, False)

Sheets("車庫証明").Range("n4").Value = WorksheetFunction.VLookup(n, tbl, 4, False)

If UserForm1.OptionButton1 = True Then

Sheets("車庫証明").Range("F7").Value = WorksheetFunction.VLookup(n, tbl, 2, False)

Else

Sheets("車庫証明").Range("F7").Value = WorksheetFunction.VLookup(n, tbl, 6, False)

End If


Unload UserForm1

Sheets("車庫証明").Select

上記のように記載しました。

初めの下の部分ですが、ここで変数宣言しております。通常は頭で宣言するのですが、

今回はここでの宣言になっております。

Dim n As Long

Dim tbl As Range

Set tbl = Sheets("名簿").Range("a2:g29")

n = UserForm1.TextBox1.Value

「n」はユーザーフォームのテキストボックスなので、入力された部屋番号となります。

「tbl」は名簿範囲を変数として設定しています。

次に「ClearContents」というのがいくつか並んでいます。

以前一度同じような記述をしましたが、若干範囲を変更しましたので、

以前の部分を削除して、今回クリアを実施しましょう。


次に

Sheets("車庫証明").Range("F6").Value = WorksheetFunction.VLookup(n, tbl, 3, False)

というよく似た内容のものが並んでいます。

これはVlookup関数を使用して部屋番号から抽出した「住所」「部屋番号」「電話番号」を

車庫証明シートにコピーしているところです。

部屋番号は「=n」でもいいのですが、一応Vlookup関数にしております。

あと駐車場b焜Rピーしております。

VBAで関数を使用する場合はこのように、WorksheetFunction.VLookup

(検索値,検索範囲,列番号)という記述になります。

If UserForm1.OptionButton1 = True Then

Sheets("車庫証明").Range("F7").Value = WorksheetFunction.VLookup(n, tbl, 2, False)

Else

Sheets("車庫証明").Range("F7").Value = WorksheetFunction.VLookup(n, tbl, 6, False) End If

上記の部分ですが、契約者と使用者の関係が「本人」の場合は使用者に契約者の

名前をそのままコピーします。

違う場合は使用者(賃貸者)の名前をコピーします。

Unload UserForm1

Sheets("車庫証明").Select

上記の部分ですが、ユーザーフォーム1を閉じて、「車庫証明」のシートを表示する命令です。

「フォームを閉じる」なので「close」でいいように思えますが、フォームを閉じる場合は

「Unload」を使用してください。

楽天スーパーポイントプレゼント!



・使用者と契約者の関係性のVBA

車庫17.png

If UserForm1.OptionButton1 = True Then

Sheets("車庫証明").Range("p6").Value = "@"

ElseIf UserForm1.OptionButton2 = True Then

Sheets("車庫証明").Range("p7").Value = "A"

ElseIf UserForm1.OptionButton3 = True Then

Sheets("車庫証明").Range("p8").Value = "B"

ElseIf UserForm1.OptionButton4 = True Then

Sheets("車庫証明").Range("p9").Value = "C"

Sheets("車庫証明").Range("q10").Value = UserForm1.TextBox2

End If

「Unload UserForm1」の前に上記のような記述をしました。

ここも分かると思います。

オプションボタン1が選択されていれば、「P6」に「@」を入れなさいという命令ですね。

オプションボタン4までは同じです。

最後にSheets("車庫証明").Range("q10").Value = UserForm1.TextBox2

というところがありますね。

これは関係性をコピーせよという意味合いです。


ここまでで、車庫証明に必要事項は記載できたと思います。


次は駐車区画に色付けしたいと思います。

Dim m As Integer

m = Sheets("車庫証明").Range("n4") '変数mに駐車場番号を指定

Sheets("車庫図面").Select

Sheets("車庫図面").Cells.Select

With Selection.Interior

.Pattern = xlNone

.TintAndShade = 0

.PatternTintAndShade = 0

End With

Cells.Find(what:=m).Select

With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = 16764159

.TintAndShade = 0

.PatternTintAndShade = 0

End With

上記のような記載をしました。

変数のところは分かりますね。

次の

Sheets("車庫図面").Select

Sheets("車庫図面").Cells.Select

With Selection.Interior

.Pattern = xlNone

.TintAndShade = 0

.PatternTintAndShade = 0

End With

ここは車庫図面に前回の色が残っているという前提で、シート全部の色の指定を解除しています。

Cells.Find(what:=m).Select

With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = 16764159

.TintAndShade = 0

.PatternTintAndShade = 0

End With

次にFind関数を用いて車庫番号のセルを探して、色を付けています。

例)

105号室を選択して、このVBAを実行させると下記のようになります。

車庫18.png

5番のところがピンクになりましたね。

これで指定の駐車場に色が付きました。

エーハチネット


Find関数について

Cells.Find(what:=m).Selectについて

条件に当てはまるセルを探します。

【構文】

Object.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection,

MatchCase, MatchByte, SearchFormat)

・Object・・・Rangeオブジェクト

・What・・・検索するデータを指定します。省略できません。

・After・・・単一のセルを指定。この引数に指定したセルの次のセルから検索を開始します。

      省略するとObjectで指定したセル範囲の左上の次のセルから検索します。(省略可能)

・LookIn・・・検索の対象を指定。(省略可能)

       数式・・・(xlFormulas)

       値・・・(xlValues)

       コメント・・・(xlComments)

・LookAt・・・完全に同一のセルだけ検索します。(xlWhole)

       一部でも一致するセルを検索。(xlPart)

       (省略可能)

・SearchOrder・・・検索方向を指定する。(省略可能)

          列方向(xlByColumns)

          行方向(xlByRows)

・SearchDirection・・・前方に検索する場合(xlNext:既定値)

            後方に検索する場合(xlPrevious)

            (省略可能)

・MatchCase・・・大文字と小文字を区別する場合(True)

          区別しない場合(False)

          (省略可能)

・MatchByte・・・半角と全角を区別する場合(True)

区別しない場合(False)

          (省略可能)

・SearchFormat・・・書式を検索する場合(True)

書式を検索しない場合(False)

          (省略可能)

上記のような内容になります。

また、条件に当てはまるセルが複数存在する場合は、見つかったセルの次を

検索するためにFindNextメソッドとFindPreviousメソッドを使用します。

・Object.FindNext(After)

・Object.FindPrevious(After)


今回は「what」以外は全て省略して「Cells.Find(what:=m)」となっております。


今回は必ず検索する駐車場が見つかるという前提でVBAを作成していますが、

本来は見つからない場合も想定に入れる必要があります。

見つからない場合はエラーが発生します。

Findメソッドはセルを見つけた場合、そのセルを表すRangeオブジェクトを返し、

見つからなかった場合はNothingを返します。

下に例文を記載します。

Dim Rng As Range

Set Rng = Cells.Find(What:=m)

If Not Rng Is Nothing Then

   Cells.Find(what:=m).Select

With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = 16764159

.TintAndShade = 0

.PatternTintAndShade = 0

 End With

End If

上記のようにすると、もし該当する駐車場bェ無い場合は、そのまま終了します。


・印刷する場合

このままの状態でも「車庫図面」でページを設定して印刷すれば済むことですが、

何ページを印刷すれば良いか分かるようにしましょう。


ページ番号を取得する方法

少し長くて複雑ですが下記に記載します。


Dim Lastpage As Long, Lastpage2 As Long, p As Integer, pp As Integer

Dim LastR As Long, LastC As Integer, i As Integer, j As Integer

Dim Nrow As Long, NLrow As Long, Ncol As Integer, NLcol As Integer

Dim Crow As Long, Ccol As Integer


Dim page As Integer 'ページ番号の変数

Application.ScreenUpdating = False '画面の動きを固定

Crow = Selection.Row '選択セルの行番号

Ccol = Selection.Column '選択セルの列番号

'正しく改ページ位置を取得する為、一旦改ページプレビューにする

ActiveWindow.View = xlPageBreakPreview

'縦方向最終改ページ数取得

Lastpage = ActiveSheet.HPageBreaks.Count

'横方向最終改ページ数取得

Lastpage2 = ActiveSheet.VPageBreaks.Count

'入力済み最終行、最終列取得

With ActiveSheet.UsedRange

LastR = .Row + .Rows.Count - 1

LastC = .Column + .Columns.Count - 1

End With

Ncol = 1

p = 0 'ページ数カウント初期値

'横方向改ページ分繰り返し

For j = 1 To Lastpage2 + 1

If j = Lastpage2 + 1 Then

If Ncol > LastC Then Exit For

NLcol = LastC

Else

NLcol = ActiveSheet.VPageBreaks(j).Location.Column - 1

End If

Nrow = 1

'縦方向改ページ分繰り返し

For i = 1 To Lastpage + 1

If i = Lastpage + 1 Then

If Nrow > LastR Then Exit For

NLrow = LastR

Else

NLrow = ActiveSheet.HPageBreaks(i).Location.Row - 1

End If

p = p + 1 'ページ数カウント

If Crow >= Nrow And Crow <= NLrow Then

If Ccol >= Ncol And Ccol <= NLcol Then

pp = p

End If

End If

Nrow = NLrow + 1

Next

Ncol = NLcol + 1

Next


'改ページプレビュー、画面の固定を戻す

ActiveWindow.View = xlNormalView

Application.ScreenUpdating = True

ActiveWindow.SelectedSheets.PrintOut pp, pp ‘必要なページを印刷する

上記に関しては詳しい説明は省きます。

ステップインで一つずつ動かしていけば、ある程度動作は分かると思います。

印刷の手前で「MsgBox "選択中のセルは、" & pp & " ページ目です"」という

記述をすれば下のようなメッセージが表示されます。

車庫19.png

印刷をする場合は下記の記述をすれば必要なページが印刷されます。

ActiveWindow.SelectedSheets.PrintOut pp, pp



・履歴の保存

車庫20.png

最初にメインページのA1に今日の日付を入れるようにしましょう。

A1に=today()と入力すると今日の日付が表示されます。

車庫21.png

それから上記のように記述しました。

Sheets("発行履歴").Select

Sheets("発行履歴").Rows("2:2").Select

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

'(履歴に1行挿入する)

Sheets("発行履歴").Range("A2").Value = Sheets("メイン").Range("A1")

Sheets("発行履歴").Range("B2").Value = Sheets("車庫証明").Range("j6")

Sheets("発行履歴").Range("c2").Value = Sheets("車庫証明").Range("f10")

Sheets("発行履歴").Range("d2").Value = Sheets("車庫証明").Range("f7")

最初は発効履歴に1行挿入しています。

その後、車庫証明のシートからコピーしているだけです。

これで下記のように発行履歴が残ってきます。

車庫22.png

・フォームの「終了」ボタン

車庫24.png

最初、この「終了」ボタンは、Excelの終了を考えていましたが、

途中でフォームが閉じられるので、このボタンでExcelの終了は出来ません。

ですから「フォーム」の終了に変更します。

ボタンの名称も「終了」から「閉じる」に変更しましょう。

車庫25.png

Private Sub CommandButton2_Click()

Unload UserForm1

End Sub

記述内容は上記です。

それから、Excelを起動したときに、自動的にこのフォームが開くようにしましょう。

車庫26.png

左の「ThisWorkbook」というところを選択して、上記のように記述してください。

そうするとエクセルを立ち上げた時に、自動的にユーザーフォームが立ち上がります。

一応メインページにフォームの呼出しボタンを設けていますので不要かと思いますが、

それ以外で利用する場合もあるかと思い紹介しておきます。

これ以外に何か検討することを考えると、例えば「部屋番号」を入力すると

「駐車場」番号が表示されるとか、「部屋番号」はコンボボックスにして、

そこから選択できるようにし、存在しない部屋番号を入力された場合はエラーが

出るようにするとか、色々考えられますね。





国内&海外ホテル予約『トリバゴ』

2018年09月02日

Excelでグラフを作成しよう

【パソコン買取アローズ】





マイクロソフトアクセスの記事はこちら

人気ブログランキングへ



にほんブログ村 IT技術ブログ ソフトウェアへ
にほんブログ村

私の仕事ではあまりグラフを多用することはありません。

というかほぼ使用しません。

しかし先日あることから集計表を作りグラフ化する必要が生じました。

その時に使用したグラフは単純な棒グラフでしたが、リボン上に

あまり見たことがないアイコン等があり、少々興味がわいてみました。

「ある程度はグラフも使いこなせるようにならなくては」という思いから

「グラフ」について勉強して行きたいと思います。



まずは簡単な表を作ってみましょう。


グラフ1.png

上記のような採点表があったとします。

まず下のように範囲を選択してください。

グラフ2.png

グラフ3.png

リボンの「挿入」タブの「おすすめグラフ」を押します。

グラフ4.png

上記のような表示が現れます。

ここでグラフの種類を選べます。

「すべてのグラフ」を選択すると下のように様々なグラフを選択できます。

グラフ5.png

代表的なグラフの種類といえば

1 棒グラフ

2 円グラフ

3 折れ線グラフ

4 散布図

がありますね。

グラフ6.png

左の「折れ線」を選択すると折れ線グラフのイメージが表示されます。

色々選んでみてください。普段使用しないグラフ等が出てきます。


【棒グラフ】

グラフ7.png

棒グラフは各項目の量(ここでは点数)を比較する場合に見やすいグラフです。


【円グラフ】

グラフ8.png

円グラフは各項目の割合を比較する場合に見やすいグラフです。


【折れ線グラフ】

グラフ9.png

折れ線グラフは項目の推移(変化)を見る場合に使用されます。


【散布図】

グラフ10.png

散布図は項目に関係性を把握するのに便利なグラフです。

このグラフではあまり特徴がつかめませんが、何か1教科でも点数の高い人は、

他の教科も高いという関係性が見えます。


【グラフの行と列を入れ替える】

実際にやってみましょう。

下のようにデザインタブの「行/列の切り替え」を押してください。

グラフ11.png

下のようなグラフになりました。

先ほどは名前別だったのが、教科別になりましたね。

どちらのデータが欲しいかでグラフが変わると思うので、欲しいデータに

ふさわしいグラフを選択する必要があります。

グラフ12.png

【棒グラフ】

グラフ13.png

上のように、ある商品の1年間の売り上げを表にして「棒グラフ」にしました。

棒グラフを使用する目的として

・ある時点における複数の項目を比較して、大きいか小さいか把握したいとき。

・ある項目における時間経過を比較して、大きいか小さいか把握したいとき。

(時間経過の変化を見る場合は折れ線グラフが見やすいと思います)

棒グラフでは一番多いときとか一番少ないとき等を比較するときには見やすいと思います。

後はいかに見やすくするかですね。

グラフ14.png

先ほどのグラフから修正した部分は

・タイトルを記述

・枠線の廃止

・補助線の廃止

・月が斜めになっていたのでまっすぐにしました。

・軸ラベルに「(千円)」という記述を追加しました。

・グラフ内の塗りつぶしを「無し」にしました。ここはあった方が良い場合もありますね。

どちらにしてもExcelで自動作成したままの表ではちょっと手抜きかなと思います。

それからもう少しメリハリのあるグラフにしたい場合は、下のように軸ラベル

右クリックして「軸の書式設置」から「単位」を変更してください。

グラフ15.png

グラフ16.png

今「軸の単位」を100万から10万に変更して上下の幅を広くしました。

これで先ほどの表に比べるとメリハリが出たと思います。


メッセージ性の高いグラフの作成方法

下のように横の表をコピーして縦横変換して貼り付けし、グラフを作りました。

グラフ17.png

一旦グラフを削除して表を売り上げ別に並べ替えしましょう。

並べ替えした状態でグラフにしましょう。

グラフ18.png

上のように何月が多くて、何月が少ないのか一目瞭然ですね。

このように並べ替えると売り上げの少ない月が明確になり、対策を立てやすくなります。


【ウォーターフォール図の作成】

グラフ19.png

表のような決算があったとします。

()内はマイナスです。

この表を選択して「挿入」タブからグラフを選択して「すべてのグラフ」から

「ウォーターフォール」を選択します。

グラフ20.png

上記のように期間中の合計値を加算または減算します。

グラフ21.png

また上のように表に「合計」欄を設け、その状態でウォーターフォール図を作成し、

595の軸上で書式設定を開き、右側の赤枠に「チェック」を入れると「合計」になります。


【円グラフ】

円グラフは複数の項目の割合を表すために用いられます。

どのような数値であっても円グラフで表示すると、すべて100%に収まるという事です。

グラフ22.png

通常は上記のように大きいものから順番に表示されるのが普通です。

グラフ23.png

少しグラフを加工して見やすくしました。

グラフ24.png

先ほどのグラフは上のように「データラベルの書式設定」で必要な項目を設定して作成したものです。

最近のExcelの機能は本当に優秀ですね。

大概のことがマウスクリックだけでできてしまいます。


【ドーナツグラフ】

グラフ25.png

上記のような「ドーナツ型」の円グラフを作ることもできます。

売り上げの少ない月をまとめて「その他」という項目にして作成しています。

円グラフを作るときの「項目」ですが、大体8項目くらいが上限だと思います。

それ以上の項目は「その他」にしてしまうと見やすくなります。

グラフ27.png

項目を追加してから、真ん中にテキストボックスを追加して「総合計」を記入しました。

プロパティで色々出来るので試してみてください。

グラフ45.png

今度は狭いところのデータボックスを外に出してみました。

もともとの文字色が白なので、外に出すと数字が見えなくなるので注意してください。

動かす前にテキストボックス内の文字色を変更してください。

外に引き出すと自動で引き出し線が付きます。

これもプロパティで消すことが可能です。


【折れ線グラフ】

グラフ28.png

先ほどの表ですが、月別に並べ替えて折れ線グラフにしました。

折れ線グラフは「項目」の推移を見るためのグラフです。

折れ線の角度が急であればあるほど「大きな変化」があったことが分かります。

グラフ29.png

上記のような表があるとします。

この表はある工場の水道使用量の推移を表にしています。

この表を折れ線グラフにしてみましょう。

グラフ30.png

上記の表では金額の推移は分かりますが、水量の推移が全く分かりませんね。

このような場合はどうするのか?

グラフ31.png

青のラインにマウスを当ててクリックすると上記のように選択状態になります。

グラフ33.png

右クリックで「データ系列の書式設定」を選択します。

グラフ34.png

右側の「データ系列の書式設定」で「第2軸」にチェックを入れます。

これでグラフの右側にも目盛りが表示され、水量の変化も見やすくなりました。

このように極端に数値が違う場合は2軸式にしてください。

グラフ35.png

最終的に「グラフタイトル」を入れ、横線は削除、グラフの枠線も削除、

軸目盛の最低値を修正してグラフのメリハリを付けました。

どのようにすれば見やすくなるかは各々色々やってみて、

自分なりに見やすいグラフを作れば良いと思います。

まあ私のセンスは大したことありませんね!!

グラフ46.png

今度は2軸で棒グラフにしました。こうすると「金額」が全く見えなくなりましたね。

これでは意味がないので、少し工夫をしましょう。

グラフ36.png

青部分を選択して「右クリック」して「系列グラフの種類の変更」をクリックします。

グラフ37.png

グラフの種類の変更が表示されますので「使用量」の右側をクリックすると、

その上に表示されているグラフの種類が表示されます。

ここで折れ線グラフを選択して「OK」してください。

グラフ47.png

上記のような複合グラフが完成しました。

このようにすると見やすいですね。


【散布図】

散布図は項目同士の関係性を把握するために利用します。

グラフ39.png

上記は使用量と金額だけ選択して散布図を作っています。

見方としては水量が上がると金額も上がります(当たり前ですね!!)

グラフ40.png

上の散布図は軸の最低値を修正したものです、少し見やすくなりました。


【スパークラインの挿入】

スパークラインは個々のセル内に収まる小さなグラフです。

大きなデータのパターンを分かりやすく示すことができます。

グラフ41.png


先ほどのデータを縦横を入れ替えて上のようにしました。

グラフ42.png

上のようにデータ部分だけを選択して「挿入」タブの「スパークライン」で

棒グラフを選択してください。

グラフ43.png


データ範囲は上のように「B4:M5」で選択されていますね。

次は「場所の範囲」を選択してください。この場所に「スパークライン」が表示されます。

今回は「N4:N5」を選択しまし「OK」を押してください。

グラフ44.png


上のように「N4:N5」に小さなグラフが出現しました。

このスパークラインもリボンのデザインで色々細工できますので試してください。

グラフに関してはこのくらいにします。

細かいところは自分で色々やりながら覚えてください。




業界最高クラスの高価買取【ゲーム買取ブラザーズ】


検索
タグクラウド
プロフィール
yoshimatsuさんの画像
yoshimatsu
60過ぎのおっちゃんです。同じ年齢層の中ではそこそこパソコンが使える方だと自負しています。今まで会社等で学んだ知識等を紹介していきたいと思います。
プロフィール
google-site-verification: google948a2bbbd2fd5164.html
×

この広告は30日以上新しい記事の更新がないブログに表示されております。