Excelは私の会計監査人です

みなさんこんにちは。 背景のビット。

確かに、妻のように、お金を数えるのが大好きです。 さらに、それは単純ではありません。「彼らが稼いだ金額と費やした金額」。 いいえ、彼女はそれらをグループに分け、毎日カウントします。 このビジネスは非常に骨が折れるものであり、かなりの時間がかかります。 彼女の仕事を促進するために、そして私の利己的な動機のために、私は小さな計算機を作りました。



数日間、私は彼女が読書室に何を望んでいるかを見つけようとした。 信じられないほどの努力を犠牲にして、かなり小さなリストが得られました。

必要条件

• 収益会計
  
  
  1. 受領日
  2. 受け取った量
  3. カードまたは現金で
  
  
  
• 原価計算
  
  
  1. 費用の日付
  2. 費用額
  3. 何を使いましたか
  
  
  
• 残金はいくらですか
• 私たちは何にお金を使っていますか（スケジュール）

選択肢

他のプログラマと同じように、「何を書けばいいの？」という質問がありました。 PythonからJavaまで多くのオプションがありました。 しかし、私はかなり怠け者なので、ボタンを「描画」したくありませんでした。 次に、Excelを詳しく調べました。 「これがインターフェースです」と私は自分に言いました。 それはあなたのお金がテーブルよりもどこに行ったのかをさらに明確に説明することができ（私はインフォグラフィックについて話していません）、誰もがそれを扱うことができます。

プロセス

したがって、Excelブックを作成します。デフォルトでは3つのシートがありますが、これで十分です。

最初のシート（「シート1」）

「1」行に、日付、金額、カード/現金、合計を入力します







そのため、「日付」列の形式は「日付」、「金額」-最も近い整数に丸めたお金です。 [カード/現金]列を少し工夫する必要があります。 まず、この列に表示されるリストを作成します。

「Ctrl」+「右矢印」を押すと、シートの右端の列に表示されます。 ここにリストを書きます：

• 私のカード
• 妻カード
• 現金

次に、セル「D2」の上に立ち、「Ctrl」+「下矢印」を押します。 そのため、ヘッダーなしで列全体を選択します。

これらのセルのドロップダウンリストを作成します。 「データ」->「検証...」。 開いているウィンドウで、「データ型」：「リスト」を選択し、「ソース」でリストが書き込まれた列を示し、「= $ IV：$ IV」の例を取得しました。

列全体を選択するので、アイテムを追加するときにメニューを再割り当てする必要はありません。

セルにドロップダウンリストが追加されました。







最後に、セル「D2」の「合計」列で、式は次のようになります-「= SUM（B2：B65536）」

最初のシートの準備ができました。 これが収入です。

セカンドシート（​​「シート3」）

ここではすべてが同じです。「Card / Cash」列の代わりに「What」列があり、ドロップダウンリストが変更されます。たとえば、このようなレコードがまだ含まれています。

• 製品
• たばこ
• 道順
• ビール

3番目のシート（「データ」）

最も興味深いこと。 シートは次のようになります。





セル「A2」の数式は「= Sheet1！D2-Sheet3！D2」であり、シートにグラフを追加します。







私には、回覧がより視覚的に見えるようです。



次に、マクロを実行しやすくするためのパネルを作成します。 ツールバーを右クリック->「設定」ボタン「作成」。 私のメニューは「MyToolBar」と呼ばれ、この名前はマクロで使用されるので、変更する場合は忘れずに変更してください。

ツールバーが表示されたら、「Alt」+「F11」を押します。

ToolBar`eでボタンを作成するためのマクロ：



Public Sub InitToolBar()

Dim cmdBarSm As CommandBar

Dim ctlNewbtn As CommandBarButton

'Application.CommandBars("MyToolBar").Controls(" ").Delete

'Application.CommandBars("MyToolBar").Controls(" ").Delete

'Application.CommandBars("MyToolBar").Controls(" ").Delete

Set cmdBarSm = Application.CommandBars("MyToolBar")

Set ctlNewbtn = cmdBarSm.Controls.Add(msoControlButton)

ctlNewbtn.Caption = " "

ctlNewbtn.FaceId = 26

ctlNewbtn.OnAction = "getMonth"

Set ctlNewbtn = cmdBarSm.Controls.Add(msoControlButton)

ctlNewbtn.Caption = " "

ctlNewbtn.FaceId = 28

ctlNewbtn.OnAction = "getDay"

Set ctlNewbtn = cmdBarSm.Controls.Add(msoControlButton)

ctlNewbtn.Caption = " "

ctlNewbtn.FaceId = 31

ctlNewbtn.OnAction = "GetGroup"

End Sub







ロックされたコードには、これらのボタンを削除するコマンドがあります。 このようなマクロを初めて実行する必要がある場合、そうでない場合、ボタンが見つからないと誓います。

コストを計算するための3つのボタンを作成しました：「日別」、「月別」、「グループ別」。 アイコンは、OnActionでマクロと呼ばれるFaceIdで設定されます。



getMonthマクロ：



Sub getMonth()

Dim ActSheet As Worksheet

Dim BeginCell As Integer

Dim SumCell As Integer

Dim CurSumCell As Integer

Dim NewData As Boolean

Dim CurData As Date

Dim ValueP As Integer

Dim MonthNow As String



'

Set ActSheet = Worksheets.Item("")

ActSheet.Activate

ActSheet.Range("A5", "D65536").Select

Selection.Clear

'

Set ActSheet = Worksheets.Item("3")

BeginCell = 2

CurSumCell = 5

ActSheet.Activate



While ActSheet.Cells(BeginCell, 1).Value <> Empty

NewData = True

SumCell = 5

CurData = ActSheet.Cells(BeginCell, 1).Value

Set ActSheet = Worksheets.Item("")

ActSheet.Activate

'

While ActSheet.Cells(SumCell, 1).Value <> Empty

If MonthName(Month(CurData)) = ActSheet.Cells(SumCell, 1).Value Then

NewData = False

End If

SumCell = SumCell + 1

Wend

'

If NewData Then

ActSheet.Cells(CurSumCell, 1).Value = MonthName(Month(CurData))

Set ActSheet = Worksheets.Item("3")

ActSheet.Activate

ValueP = ActSheet.Cells(BeginCell, 2).Value

Set ActSheet = Worksheets.Item("")

ActSheet.Activate

ActSheet.Cells(CurSumCell, 3).Value = ActSheet.Cells(CurSumCell, 3).Value + ValueP

CurSumCell = CurSumCell + 1

End If

'

If Not NewData Then

Set ActSheet = Worksheets.Item("3")

ActSheet.Activate

ValueP = ActSheet.Cells(BeginCell, 2).Value

Set ActSheet = Worksheets.Item("")

ActSheet.Activate

ActSheet.Cells(CurSumCell - 1, 3).Value = ActSheet.Cells(CurSumCell - 1, 3).Value + ValueP

End If

Set ActSheet = Worksheets.Item("3")

ActSheet.Activate

BeginCell = BeginCell + 1

Wend



'

Set ActSheet = Worksheets.Item("1")

BeginCell = 2

CurSumCell = 5

ActSheet.Activate



' A

While ActSheet.Cells(BeginCell, 1).Value <> Empty

NewData = True

SumCell = 5

CurData = ActSheet.Cells(BeginCell, 1).Value

Set ActSheet = Worksheets.Item("")

ActSheet.Activate

'

MonthNow = ActSheet.Cells(SumCell, 1).Value

While ActSheet.Cells(SumCell, 1).Value <> Empty

If MonthName(Month(CurData)) = ActSheet.Cells(SumCell, 1).Value Then

NewData = False

End If

SumCell = SumCell + 1

Wend

'

If NewData Then

ActSheet.Cells(CurSumCell, 1).Value = MonthName(Month(CurData))

Set ActSheet = Worksheets.Item("1")

ActSheet.Activate

ValueP = ActSheet.Cells(BeginCell, 2).Value

Set ActSheet = Worksheets.Item("")

ActSheet.Activate

ActSheet.Cells(CurSumCell, 2).Value = ActSheet.Cells(CurSumCell, 2).Value + ValueP

CurSumCell = CurSumCell + 1

End If

'

If Not NewData Then

Set ActSheet = Worksheets.Item("1")

ActSheet.Activate

ValueP = ActSheet.Cells(BeginCell, 2).Value



Set ActSheet = Worksheets.Item("")

ActSheet.Activate

If ActSheet.Cells(CurSumCell - 1, 1).Value <> MonthName(Month(CurData)) Then

ActSheet.Cells(CurSumCell, 4).FormulaR1C1 = "=RC[-2]-RC[-1]"

CurSumCell = CurSumCell + 1

End If

ActSheet.Cells(CurSumCell - 1, 2).Value = ActSheet.Cells(CurSumCell - 1, 2).Value + ValueP

End If

Set ActSheet = Worksheets.Item("1")

ActSheet.Activate

BeginCell = BeginCell + 1

Wend

Set ActSheet = Worksheets.Item("")

ActSheet.Activate

End Sub







マクロgetDay：



Sub getDay()

Dim ActSheet As Worksheet

Dim BeginCell As Integer

Dim SumCell As Integer

Dim CurSumCell As Integer

Dim NewData As Boolean

Dim CurData As Date

Dim ValueP As Integer

Dim MonthNow As String



'

Set ActSheet = Worksheets.Item("")

ActSheet.Activate

ActSheet.Range("F5", "G65536").Clear

'

Set ActSheet = Worksheets.Item("3")

BeginCell = 2

CurSumCell = 5

ActSheet.Activate



While ActSheet.Cells(BeginCell, 1).Value <> Empty

NewData = True

SumCell = 5

CurData = ActSheet.Cells(BeginCell, 1).Value

Set ActSheet = Worksheets.Item("")

ActSheet.Activate

'

While ActSheet.Cells(SumCell, 6).Value <> Empty

If CurData = ActSheet.Cells(SumCell, 6).Value Then

NewData = False

End If

SumCell = SumCell + 1

Wend

'

If NewData Then

ActSheet.Cells(CurSumCell, 6).Value = CurData

Set ActSheet = Worksheets.Item("3")

ActSheet.Activate

ValueP = ActSheet.Cells(BeginCell, 2).Value

Set ActSheet = Worksheets.Item("")

ActSheet.Activate

ActSheet.Cells(CurSumCell, 7).Value = ActSheet.Cells(CurSumCell, 7).Value + ValueP

CurSumCell = CurSumCell + 1

End If

'

If Not NewData Then

Set ActSheet = Worksheets.Item("3")

ActSheet.Activate

ValueP = ActSheet.Cells(BeginCell, 2).Value

Set ActSheet = Worksheets.Item("")

ActSheet.Activate

ActSheet.Cells(CurSumCell - 1, 7).Value = ActSheet.Cells(CurSumCell - 1, 7).Value + ValueP

End If

Set ActSheet = Worksheets.Item("3")

ActSheet.Activate

BeginCell = BeginCell + 1

Wend



Set ActSheet = Worksheets.Item("")

ActSheet.Activate

End Sub







そして最後に、最後のGetGroup

Sub GetGroup()

Dim ActSheet As Worksheet

Dim BeginCell As Integer

Dim SumCell As Integer

Dim CurSumCell As Integer

Dim NewData As Boolean

Dim CurGroup As String

Dim ValueP As Integer



Set ActSheet = Worksheets.Item("")

ActSheet.Activate

ActSheet.Range("I5", "J65536").Clear

Set ActSheet = Worksheets.Item("3")

BeginCell = 2

CurSumCell = 5

ActSheet.Activate



While ActSheet.Cells(BeginCell, 3).Value <> Empty

NewData = True

SumCell = 5

CurGroup = ActSheet.Cells(BeginCell, 3).Value

Set ActSheet = Worksheets.Item("")

ActSheet.Activate

'

While ActSheet.Cells(SumCell, 9).Value <> Empty

If CurGroup = ActSheet.Cells(SumCell, 9).Value Then

NewData = False

CurSumCell = SumCell + 1

End If

SumCell = SumCell + 1

Wend

'

If NewData Then

If ActSheet.Cells(CurSumCell, 9).Value <> Empty Then

CurSumCell = CurSumCell + 1

End If

ActSheet.Cells(CurSumCell, 9).Value = CurGroup

Set ActSheet = Worksheets.Item("3")

ActSheet.Activate

ValueP = ActSheet.Cells(BeginCell, 2).Value

Set ActSheet = Worksheets.Item("")

ActSheet.Activate

ActSheet.Cells(CurSumCell, 10).Value = ActSheet.Cells(CurSumCell, 10).Value + ValueP

CurSumCell = CurSumCell + 1

End If

'

If Not NewData Then

Set ActSheet = Worksheets.Item("3")

ActSheet.Activate

ValueP = ActSheet.Cells(BeginCell, 2).Value

Set ActSheet = Worksheets.Item("")

ActSheet.Activate

ActSheet.Cells(CurSumCell - 1, 10).Value = ActSheet.Cells(CurSumCell - 1, 10).Value + ValueP

End If

Set ActSheet = Worksheets.Item("3")

ActSheet.Activate

BeginCell = BeginCell + 1

Wend

Set ActSheet = Worksheets.Item("")

ActSheet.Activate

ActiveSheet.Shapes("Chart 1").Select

ActiveChart.SetSourceData Source:=Sheets("").Range("I5:J" + CStr(CurSumCell - 1)), PlotBy:= _

xlColumns

End Sub









ここに興味深い点があります

ActiveSheet.Shapes("Chart 1").Select

ActiveChart.SetSourceData Source:=Sheets("").Range("I5:J" + CStr(CurSumCell - 1)), PlotBy:= _

xlColumns







[範囲]で、[データ]シートの書式を変更した場合はもちろん、範囲を示すことを忘れないでください。

おわりに

これですべて準備完了です。 もちろん、完璧なマクロコードのふりをするわけではありませんが、その仕事はうまくいきます。 この読者は多くの人にとって有用であり、これらの正月休みにあなたの愛する人と過ごす時間を少し節約すると思います。