ＥＸＣＥＬでセルに小計と入力したら自動で計算してくれる

どのような関数を使えばよいのか教えてください。


　Ａ列　　　　　Ｂ列　　　　　Ｃ列　　　　　Ｄ列　　　　Ｅ列　　　　Ｆ列
　品物　　　　　摘要　　　　　数量　　　　　単位　　　　単価　　　　金額（数量×単価）
1○○　　　　　　　　　　　　　1　　　　　　個
2○○　　　　　　　　　　　　　1　　　　　　個
3○○　　　　　　　　　　　　　2 　　　　　　個
4○○　　　　　　　　　　　　　2　　　　　　個
5○○　　　　　　　　　　　　　1　　　　　　個

小計　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　○○○○○

6経費　　　　　　　　　　　　　1　　　　　 式
7経費　　　　　　　　　　　　　1　　　　　 式
8経費　　　　　　　　　　　　　1　　　　　 式

　合計　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　○○○○○

1～5行（行（品物）は3行しかないときもあれば何十行あるときもあります）の小計をＡ列のセルに小計と入力したら自動でＦ列に○○○○自動計算（出来ましたら1行あけて小計を入れたいです。）

また、小計の下に経費を入力し、品物と経費の合計を　合計といれた行のＦ列に自動計算したいの
ですが、、、

どなたか教えてください。どうぞよろしくお願い致します。

質問者からの補足コメント

• shien-r様より教えて頂きました　サイトより勉強をと思っておりますが、
  やはり私には少し時間がかかりそうです。
  
  ひとまず、上記のように動作するコードを教えていただけませんでしょうか？
  
  どうぞよろしくお願い致します。

    補足日時：2016/07/05 09:34

No.6 回答者： くんこば 回答日時： 2016/07/15 00:18

エクセルの「ピボットテーブル」じゃ出来ないのかな？

No.5 回答者： WindFaller 回答日時： 2016/07/12 13:45

ご質問の題材とは興味を感じたものの、ご質問者さんのリクエストのような、「小計」という文字をイベント(起動）とするマクロは、ご動作の元になるから、賛成できません。

趣旨が違うと思ったら、こちらのマクロは無視なさって構いません。

あくまでも、コマンドボタンなどに付けていただければよいのすが、最初は難しく感じるかと思います。せめて、QAT (Quick Access Tool)につけていただければ、とは思います。ある程度の汎用性のあるマクロを作りましたのでご披露させていただきます。

画像は、AutoSumFormat としていますが、このイメージ画像がふさわしいかは分かりません。

今回、利用したアドインは、多くある中で、一番、使いやすいRibbonカスタマイザーです。RibbonX Visual Designer (Andy Pope氏作）

なお、合計の計算は、SUBTOTAL 関数を使っています。小計があっても、小計を無視して計算してくれるからです。

使い方は、一通りの入力を終わった最後の次の空白行にマウスカーソルを置いてから、ボタンをクリックすることです。


'標準モジュール
Public Sub Button1_onAction(control As IRibbonControl)
　　Call MakingFormulas
End Sub

Sub MakingFormulas()
　Dim Rng As Range
　Dim rArea As Range
　Dim i As Long
　Dim c As Range
　Dim bln As Boolean
　Const SP As Long = 1 '最下行からいくつ開けるか？
　Const KEIHI As Long = 3
　　If TypeName(Selection) = "Range" Then
　　　Set Rng = Selection
　　　If Rng.Row < 3 Then GoTo EndLine
　　　If WorksheetFunction.CountA(Rng) <= 2 And Rng.Cells(1).Offset(-1).Value = "" Then
　　　　　GoTo EndLine:　　　　Exit Sub
　　　　End If
　End If
　
　Set rArea = Rng.CurrentRegion
　With rArea
　　　Set Rng = .Rows(.Cells(.Cells.Count).Row)
　End With
　For i = 1 To Rng.Cells.Count
　　　If i = 1 Then
　　　　　Rng.Cells(SP, i).Value = "小計"
　　　ElseIf WorksheetFunction.Count(rArea.Columns(i)) >= 2 Then
　　　　　If fmlc = "" Then
　　　　　　For j = rArea.Columns(i).Cells.Count To 1 Step -1
　　　　　　　　If VarType(rArea.Columns(i).Cells(j)) = vbString Then
　　　　　　　　　　Exit For
　　　　　　　　End If
　　　　　　Next
　　　　　　If j = 0 Then
　　　　　　　　j = 1
　　　　　　Else
　　　　　　　　j = j + 1
　　　　　　End If
　　　　　　fmlc = "=SUBTOTAL(9,R[-" & SP & "]C:R" & j & "C)"
　　　　End If
　　　　Rng.Cells(SP, i).FormulaLocal = fmlc
　　　End If
　　Next i
　　'合計欄まで
　　Rng.Columns(1).Offset(1).Resize(KEIHI).Value = "経費"
　　With Rng.Columns(1).Offset(1)
　　　　.Cells(KEIHI + 1, 1).Value = "合計"
　　　　.Cells(KEIHI + 1, Rng.Columns.Count).FormulaLocal = "=SUBTOTAL(9,R[-1]C:R" & j & "C)"
　End With
　Exit Sub
EndLine:
　　MsgBox "データのある最下行の空白の行を選択してください。", vbExclamation
End Sub

この回答へのお礼

ありがとうございましたm(__)m

お礼日時：2016/07/14 13:00

No.4 回答者： shien-r 回答日時： 2016/07/05 17:32

すみません、訂正があります。

二つ目のモジュール内、セルの上段を選択し変数に入れていく部分で
機能自体にはなんら問題ありませんが、補足部分『'』以下が違います。

以下が正しい補足です。

ーーーーーーーーーーここからーーーーーーーーーー

Worksheets("Sheet1").Cells(S, 6).Select
Selection.End(xlUp).Select
'セルCells(S, 6)を選択し、そこから上の数値のあるセルを選択します（経費の最下段セル）

S1 = ActiveCell.Row
Selection.End(xlUp).Select
'変数『S1』に選択したセルの行ナンバーを入れ、さらに入力のあるセルの最上段を選択します（経費の最上段セル）

S2 = ActiveCell.Row
Selection.End(xlUp).Select
'変数『S2』に選択したセルの行ナンバーを入れ、さらに入力のあるセルの最上段を選択します（小計のセル）

S3 = ActiveCell.Row
'変数『S3』に選択したセルの行ナンバーを入れます

ーーーーーーーーーーここまでーーーーーーーーーー

失礼しました。
（最終項関係ありませんがまとまりなので入れてます）

この回答へのお礼

ご丁寧にありがとうございました。助かりました。

お礼日時：2016/07/06 09:27

No.3 回答者： shien-r 回答日時： 2016/07/05 17:26

補足になります。

基本的なマクロ、ＶＢＡの操作は理解できている前提です。

まずは、目的のシートのＶＢＡコードに下記を記入してください。
例ではSheet1（名前もSheet1）になっています。
（『ーーーーーーーーーーここからーーーーーーーーーー』と
　『ーーーーーーーーーーここまでーーーーーーーーーー』を含みません）

ーーーーーーーーーーここからーーーーーーーーーー

Private Sub Worksheet_Change(ByVal Target As Range)
'シート内のセルに変更があった時に起動します

Dim RowNum As Long
'『RowNum』という変数を『Long』という型で使います（変数宣言）
Dim ColNum As Integer
'『ColNum』という変数を『Integer』という型で使います（変数宣言）

RowNum = ActiveCell.Offset(-1, 0).Row
'変数『RowNum』に、選択したセルより一つ上のセルの行ナンバーを入れます
ColNum = Target.Column
'変数『ColNum』に、選択した（ターゲットになっている）セルの列ナンバーを入れます

If ColNum = 1 Then
'もし列ナンバーが１（Ａ列）であるときには以下の処理をします
If Cells(RowNum, 1) = "小計" Then Call 小計
'もしセル（RowNum, 1）（Ａ列のRowNum行目）の内容が"小計"の時は、マクロ『小計』を呼び出し（Call）ます。
If Cells(RowNum, 1) = "合計" Then Call 合計
'もしセル（RowNum, 1）（Ａ列のRowNum行目）の内容が"合計"の時は、マクロ『合計』を呼び出し（Call）ます。
End If
'If終り

End Sub

ーーーーーーーーーーここまでーーーーーーーーーー

次に、標準モジュールを二つ作成します。
一つのめの標準モジュールに以下を記入してください。

ーーーーーーーーーーここからーーーーーーーーーー

Sub 小計()

Dim Ins As Boolean
'『Ins』という変数を『Boolean』という型で使います（変数宣言）
Dim Sum As String
'『Sum』という変数を『String』という型で使います（変数宣言）

Sum = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Value
'変数『Sum』に"Sheet1"という名前のワークシートのCells(Rows.Count, 1)というセルの内容を入れます

If Sum = "小計" Then
'もしSumの値が"小計"だった場合以下の処理をします
Ins = True
'変数『Ins』に"True"の値を入れます
End If
'If終り

Dim S As Long
'『S』という変数を『Long』という型で使います（変数宣言）

S = ActiveCell.Offset(-1, 0).Row
'変数『S』に選択しているセルの一つ上のセルの行ナンバーを入れます

If Ins = True Then
Cells(S, 6) = WorksheetFunction.Sum(Range(Cells(2, 6), Cells(S, 6)))
'セルCells(S, 6)に、Cells(2, 6)からCells(S, 6)まで合計した数値を記入します
End If
'If終り

End Sub

ーーーーーーーーーーここまでーーーーーーーーーー

二つ目のモジュールに以下を記入してください。

ーーーーーーーーーーここからーーーーーーーーーー

Sub 合計()

Dim Ins As Boolean
'『Ins』という変数を『Boolean』という型で使います（変数宣言）
Dim Sum As String
'『Sum』という変数を『String』という型で使います（変数宣言）

Sum = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Value
'変数『Sum』に"Sheet1"という名前のワークシートのCells(Rows.Count, 1)というセルの内容を入れます

If Sum = "合計" Then
'もしSumの値が"合計"だった場合以下の処理をします
Ins = True
'変数『Ins』に"True"の値を入れます
End If
'If終り

Dim S As Long
Dim S1 As Long
Dim S2 As Long
Dim S3 As Long
'『S～S3』という変数を『Long』という型で使います（変数宣言）

S = ActiveCell.Offset(-1, 0).Row
'変数『S』に選択しているセルの一つ上のセルの行ナンバーを入れます

Worksheets("Sheet1").Cells(S, 6).Select
Selection.End(xlUp).Select
'セルCells(S, 6)を選択し、そこから上の数値のあるセルを選択します

S1 = ActiveCell.Row
Selection.End(xlUp).Select
'変数『S1』に選択したセルの行ナンバーを入れ、さらに入力のあるセルの最上段を選択します（経費の最下段セル）

S2 = ActiveCell.Row
Selection.End(xlUp).Select
'変数『S2』に選択したセルの行ナンバーを入れ、さらに入力のあるセルの最上段を選択します（経費の最上段セル）

S3 = ActiveCell.Row
'変数『S3』に選択したセルの行ナンバーを入れます（小計のセル）

If Ins = True Then
Cells(S, 6) = WorksheetFunction.Sum(Range(Cells(S1, 6), Cells(S2, 6)), Cells(S3, 6))
'セルCells(S, 6)に、Cells(S1, 6)（経費の最下段）からCells(S2, 6)（経費の最上段）までと、小計を合計した数値を記入します
End If
'If終り

Worksheets("Sheet1").Cells(S, 6).Offset(1, -5).Select
'合計と入力したセルの下のセルを選択します

End Sub

ーーーーーーーーーーここまでーーーーーーーーーー

添付画像のような形で、品物などの記入を全て済ませた後でＡ列に『小計』と記入すると
記入した行と同行Ｆ列の各金額の小計値が、一つセルを開けて記入されます。
次に、経費を記入し、Ａ列に『合計』と記入すると
記入した行と同行Ｆ列の各経費金額の小計値と、前述小計の金額が合計された値が
一つセルを開けて記入されます。

ただし、小計作業が全て終わってからでないと、合計作業が出来ません。
また、合計作業が終わった後で、小計をし直したい場合は
経費の全項目を消去してやり直す必要があります。

もろもろ穴や不備がある状態であるとは思いますが
順番さえ守れば、本来の目的は果たせる状態であると思います。
シート名を変更したい、ＶＢＡの記述の各詳細を知りたい
という時は、前回答のサイトを参照するか、検索してみて下さい。

また、記入例中の『'』以下の文章は、解説です。
消去しても動作に支障はありませんが、間違って必要な部分を消すと、動作しなくなりますので
お気を付けください。

No.2 回答者： shien-r 回答日時： 2016/07/04 15:40

補足になります。

マクロを勉強されるのであれば以下サイトが参考になると思います。

参考１：http://home.att.ne.jp/zeta/gen/excel/
参考２：http://www.moug.net/tech/exvba/
参考３：http://www.tipsfound.com/vba

いずれも、私自身が勉強した時、また今でも分からないときに参照しているサイトです。
どのサイトも分かりやすく解説してありますので
目的から、または基礎から学んで行けば、マクロを使った目的の作業自体は
さほど難解な作業でもないと思います。

この回答へのお礼

ありがとうございます！参考にさせていただきます

お礼日時：2016/07/04 15:50

No.1 回答者： shien-r 回答日時： 2016/07/04 15:11

関数だけではなく、マクロを使う必要があります。

マクロを使ってでもやりたい、ということであれば多少勉強が必要になります。
そこまでやる気がない、出来ないのであれば
「特定のセルに『小計』と入力した場合に自動的に目的セルに、一定セルの合計値を入力』
という作業は、不可能と思われます。

毎回この表を作成するのであれば、Ｆ列の金額を合計し、数値を一行開けて
コピー＆ペーストが楽だとは思いますが。
操作については
（http://www.becoolusers.com/excel/sum.html）
上記ページの下の方、「関数を入力する、別の方法」という項目をみて下さい。

この回答へのお礼

ご回答ありがとうございました。

マクロを使う必要もあるのですね。急ぎではありましたが、もう少し勉強して作成してみたいと思います。

お礼日時：2016/07/04 15:28