2018年08月20日

Excel 雛形シートを複製して新規ワークブックを保存するマクロ

雛形シートを複製して新規ワークブックを保存するマクロです。
「雛型1」という名前のシートを用意しておき、そのシートをコピーして別ファイル名で保存します。
'雛形シートを複製して新規ワークブックを保存するマクロ

'変数宣言の強制
Option Explicit

'固定値 雛形シート名
Const TemplateSheet1 As String = "雛型1"
'固定値 出力ファイル名
Const outputFilename As String = "ABCファイル"

'実行ボタン
Private Sub BtnExec_Click()

'確認メッセージ
Dim res
res = MsgBox("処理を開始します。" & vbNewLine & "よろしいですか?", vbYesNo + vbDefaultButton2 + vbQuestion, "確認")
If res = vbNo Then Exit Sub

'デスクトップのパス取得
Dim path As String
Dim WSH As Object
Set WSH = CreateObject("WScript.Shell")
path = WSH.SpecialFolders("Desktop") & "\"

'新規ワークブックを追加
Dim targetBook As Workbook
Set targetBook = Workbooks.Add

'雛形シートを特定
Dim sourceSheet As Worksheet
Set sourceSheet = ThisWorkbook.Worksheets(TemplateSheet1)

'新規ワークブックへ雛形シートを複製
sourceSheet.Copy After:=targetBook.Sheets(targetBook.Sheets.Count)

Dim targetSheet As Worksheet
Set targetSheet = targetBook.Sheets(sourceSheet.Name)
targetSheet.Visible = xlSheetVisible

'==================================================
'ここにtargetSheetに対しての作成処理を追加する
'==================================================

'不要シート削除
Application.DisplayAlerts = False
targetBook.Worksheets("Sheet1").Delete
Application.DisplayAlerts = True

'先頭シートの選択
targetBook.Worksheets(1).Select

'名前を付けて保存
Dim newFileName As String
newFileName = outputFilename & "_" & Format(Now, "yyyymmddhhnnss") & ".xlsx"
targetBook.SaveAs path & newFileName

'閉じる
targetBook.Close

'完了メッセージ
Call MsgBox("ファイルを出力しました。" & vbNewLine & "ファイル名:" & newFileName, vbOKOnly + vbInformation, "情報")

End Sub
タグ:Excel VBA
posted by Hiro at 21:57| Comment(0) | プログラム

2018年08月12日

エンジニアなら知っておきたい損益分岐点の話

損益分岐点の求め方と、その仕組みについて腑に落ちない部分があったので、自分なりに咀嚼してまとめました。
ポイントは、グラフ上で売上と費用の変動費が重なるように描くことで、視覚的に分かり易くしたところです。


売上高について

売上高は次の式で求められます。
単価が一定なら販売数量に比例して売上高も増減します。

売上高=単価×販売数量

売上高から費用を引けば利益が求まりますので、売上高は次のようにも表せます。

売上高=費用+利益

費用のうち、売上に比例して額が増えるものを変動費と言います。例えば材料費などが該当します。
(あくまでも売上高に含まれる額のみです。在庫の額は含みません。)
売上高のうち、変動費の占める割合を変動費率といいます。この比率は一定であると考えます。
売上高から変動費を差し引いた額を限界利益といいます。
また売上高に対するその割合を限界利益率といいます。
限界利益は変動費に反比例しますので、限界利益率も一定と考えます。
(限界利益の構造は「限界利益=固定費+利益」という式で表せます。固定費については後述。)
(限界利益の事を貢献利益ともいいます。)

費用について

売上高のグラフからはいったん離れて、次は費用について見ていきます。
費用は次のように表せます。

費用=固定費+変動費

変動費は前述したとおり、売上高に比例して額が変動する費用です。
一方、固定費とはテナントの賃借料や保険料のように売上高とは関係なく、期間内に必ず発生する費用のことです。
ところで、上のグラフでは下に固定費、上に変動費が描かれていますが、これを入れ替えてみます。
グラフの描き方を変えただけなので、値そのものは変わりません。

損益分岐点

さて、ここからが本題です。
売上高のグラフと、費用のグラフを重ね合わせます。
そうすると、変動費の部分がぴったりと一致します。
そして、売上高の線と、費用の線が交差する点が浮かび上がってきます。
この部分を損益分岐点売上高、または単に損益分岐点と言います。単位は金額です。
損益分岐点は費用と売上がトントンの状態、すなわち「赤字は出てないけど、利益もゼロ」の状態です。
ここが、利益が出るか、赤字になるかの境目になります。

次のグラフで言うと、損益分岐点より右側は費用より売上が多くなっていますので、その分の差額が利益となります。
反対に、損益分岐点より左側は、費用より売上が下回っていますから、赤字(損失)となります。
では、具体的に損益分岐点を算出するにはどうしたらよいでしょうか?
グレーの矢印の高さを求めることになります。
それを求めるには、冒頭で説明した限界利益が登場します。
グラフに限界利益を重ね合わせます(下図斜線部分)。
そうすると、損益分岐点において、固定費と限界利益の額が一致することが分かります。(下図赤線部分)
限界利益はそのときの売上高に限界利益率をかけると求められます。
また、ここでは売上高は損益分岐点ですので次の式が成り立ちます。

固定費=損益分岐点×限界利益率

式を変形すると

損益分岐点=固定費÷限界利益率

となります。
これでめでたく損益分岐点の式が求められました。
posted by Hiro at 13:34| Comment(1) | 簿記会計

2018年08月08日

VBA 拡張したコレクションの実装例

拡張コレクション(自動ソート機能)
'拡張コレクション(自動ソート機能)
'CollectionEx

Option Explicit

'内部コレクションクラス
Private innerCollection As Collection

'初期化
Private Sub Class_Initialize()
Set innerCollection = New Collection
End Sub

'要素を追加
Public Sub add(ByRef elm As Object, ByVal key As String)
'IDが順番通りになるようにインスタンスを追加する
Dim index As Integer
For index = 1 To innerCollection.Count
If elm.id < innerCollection.Item(index).id Then
Call innerCollection.add(elm, elm.id, index)
Exit For
End If
Next

'ループ内で追加されなかった場合はリストの終端に要素を追加する
If index > innerCollection.Count Then
Call innerCollection.add(elm, elm.id)
End If
End Sub

'全要素を削除
Public Sub Clear()
Set innerCollection = New Collection
End Sub

'要素数を返す
Public Function Count() As Long
Count = innerCollection.Count
End Function

'指定番号の要素を返す
Public Function Item(index As Integer) As UserClass
Item = innerCollection.Item(index)
End Function

'先頭の要素を返す(リストからは要素を削除)
Public Function popHead() As UserClass
If innerCollection.Count = 0 Then Exit Function

Set popHead = innerCollection.Item(1)
innerCollection.Remove (1)
End Function

'終端の要素を返す(リストからは要素を削除)
Public Function popTail() As UserClass
If innerCollection.Count = 0 Then Exit Function

Set popTail = innerCollection.Item(innerCollection.Count)
innerCollection.Remove (innerCollection.Count)
End Function
Userクラス
'UserClass

Option Explicit

'///// メンバ変数 /////
'ユーザーID
Private m_id As String
'ユーザー名
Private m_name As String
'年齢
Private m_age As Integer
'居住地
Private m_address As String

'///// プロパティプロシージャ /////
Property Get id() As String
id = m_id
End Property

Property Let id(ByVal id As String)
m_id = id
End Property

Property Get name() As String
name = m_name
End Property

Property Let name(ByVal name As String)
m_name = name
End Property

Property Get age() As Integer
age = m_age
End Property

Property Let age(ByVal age As Integer)
m_age = age
End Property

Property Get address() As String
address = m_address
End Property

Property Let address(ByVal address As String)
m_address = address
End Property

'ユーザーの属性をセットする
Public Sub setAttribute(ByVal id As String, ByVal name As String, ByVal age As Integer, ByVal address As String)
m_id = id
m_name = name
m_age = age
m_address = address
End Sub
呼び出し側(ActiveXボタンを貼り付けてイベントプロシージャを記述)
'拡張したコレクションの実装例

Option Explicit

Private Sub CommandButton1_Click()
'ユーザークラスの宣言
Dim u1 As New UserClass
Dim u2 As New UserClass
Dim u3 As New UserClass
Dim u4 As New UserClass
Dim u5 As New UserClass

'ユーザーリストの宣言
Dim userList As New CollectionEx

'ユーザーに属性をセット
Call u1.setAttribute("1090", "Taro", "20", "Tokyo")
Call u2.setAttribute("1050", "Hanako", "28", "Osaka")
Call u3.setAttribute("1010", "Ichiro", "18", "Fukuoka")
Call u4.setAttribute("1080", "Yoshiko", "25", "Nagoya")
Call u5.setAttribute("1020", "Kensuke", "15", "Sapporo")

'ユーザーのIDをKEYとしてユーザーリストに追加
Call userList.add(u1, u1.id)
Call userList.add(u2, u2.id)
Call userList.add(u3, u3.id)
Call userList.add(u4, u4.id)
Call userList.add(u5, u5.id)

'要素数
Debug.Print "要素数:" & userList.Count

'先頭から要素を取得し名前を表示する(ID順で表示される)
Debug.Print userList.popHead.name
Debug.Print userList.popHead.name
Debug.Print userList.popHead.name
Debug.Print userList.popHead.name
Debug.Print userList.popHead.name

'要素数
Debug.Print "要素数:" & userList.Count
End Sub

タグ:Excel VBA
posted by Hiro at 21:55| Comment(0) | プログラム