どのような関数を使えばよいのか教えてください。
A列 B列 C列 D列 E列 F列
品物 摘要 数量 単位 単価 金額(数量×単価)
1○○ 1 個
2○○ 1 個
3○○ 2 個
4○○ 2 個
5○○ 1 個
小計 ○○○○○
6経費 1 式
7経費 1 式
8経費 1 式
合計 ○○○○○
1~5行(行(品物)は3行しかないときもあれば何十行あるときもあります)の小計をA列のセルに小計と入力したら自動でF列に○○○○自動計算(出来ましたら1行あけて小計を入れたいです。)
また、小計の下に経費を入力し、品物と経費の合計を 合計といれた行のF列に自動計算したいの
ですが、、、
どなたか教えてください。どうぞよろしくお願い致します。
A 回答 (6件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
ご質問の題材とは興味を感じたものの、ご質問者さんのリクエストのような、「小計」という文字をイベント(起動)とするマクロは、ご動作の元になるから、賛成できません。
趣旨が違うと思ったら、こちらのマクロは無視なさって構いません。
あくまでも、コマンドボタンなどに付けていただければよいのすが、最初は難しく感じるかと思います。せめて、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
No.4
- 回答日時:
すみません、訂正があります。
二つ目のモジュール内、セルの上段を選択し変数に入れていく部分で
機能自体にはなんら問題ありませんが、補足部分『'』以下が違います。
以下が正しい補足です。
ーーーーーーーーーーここからーーーーーーーーーー
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』に選択したセルの行ナンバーを入れます
ーーーーーーーーーーここまでーーーーーーーーーー
失礼しました。
(最終項関係ありませんがまとまりなので入れてます)
No.3
- 回答日時:
補足になります。
基本的なマクロ、VBAの操作は理解できている前提です。
まずは、目的のシートのVBAコードに下記を記入してください。
例では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
'もし列ナンバーが1(A列)であるときには以下の処理をします
If Cells(RowNum, 1) = "小計" Then Call 小計
'もしセル(RowNum, 1)(A列のRowNum行目)の内容が"小計"の時は、マクロ『小計』を呼び出し(Call)ます。
If Cells(RowNum, 1) = "合計" Then Call 合計
'もしセル(RowNum, 1)(A列の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
ーーーーーーーーーーここまでーーーーーーーーーー
添付画像のような形で、品物などの記入を全て済ませた後でA列に『小計』と記入すると
記入した行と同行F列の各金額の小計値が、一つセルを開けて記入されます。
次に、経費を記入し、A列に『合計』と記入すると
記入した行と同行F列の各経費金額の小計値と、前述小計の金額が合計された値が
一つセルを開けて記入されます。
ただし、小計作業が全て終わってからでないと、合計作業が出来ません。
また、合計作業が終わった後で、小計をし直したい場合は
経費の全項目を消去してやり直す必要があります。
もろもろ穴や不備がある状態であるとは思いますが
順番さえ守れば、本来の目的は果たせる状態であると思います。
シート名を変更したい、VBAの記述の各詳細を知りたい
という時は、前回答のサイトを参照するか、検索してみて下さい。
また、記入例中の『'』以下の文章は、解説です。
消去しても動作に支障はありませんが、間違って必要な部分を消すと、動作しなくなりますので
お気を付けください。
No.2
- 回答日時:
補足になります。
マクロを勉強されるのであれば以下サイトが参考になると思います。
参考1:http://home.att.ne.jp/zeta/gen/excel/
参考2:http://www.moug.net/tech/exvba/
参考3:http://www.tipsfound.com/vba
いずれも、私自身が勉強した時、また今でも分からないときに参照しているサイトです。
どのサイトも分かりやすく解説してありますので
目的から、または基礎から学んで行けば、マクロを使った目的の作業自体は
さほど難解な作業でもないと思います。
No.1
- 回答日時:
関数だけではなく、マクロを使う必要があります。
マクロを使ってでもやりたい、ということであれば多少勉強が必要になります。
そこまでやる気がない、出来ないのであれば
「特定のセルに『小計』と入力した場合に自動的に目的セルに、一定セルの合計値を入力』
という作業は、不可能と思われます。
毎回この表を作成するのであれば、F列の金額を合計し、数値を一行開けて
コピー&ペーストが楽だとは思いますが。
操作については
(http://www.becoolusers.com/excel/sum.html)
上記ページの下の方、「関数を入力する、別の方法」という項目をみて下さい。
ご回答ありがとうございました。
マクロを使う必要もあるのですね。急ぎではありましたが、もう少し勉強して作成してみたいと思います。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) SUBTOTAL SUMIF?? 2 2023/03/16 11:25
- Excel(エクセル) エクセル 自動計算 1 2023/01/30 13:28
- Excel(エクセル) アウトラインの小計のやり方 1 2023/03/20 11:51
- Excel(エクセル) B列に、A列の数字が偶数の場合は1減算した数字、奇数の場合はそのまま数字を自動表示したい 4 2022/04/16 12:01
- Excel(エクセル) マクロだと数式が表示される 2 2022/09/10 14:48
- Excel(エクセル) エクセルで値ではなく関数を参照する方法 6 2023/03/19 00:50
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- Excel(エクセル) SUMIFのIF分岐について 4 2023/04/15 12:57
- Excel(エクセル) エクセル/列追加時、合計行の計算式 7 2023/03/15 11:14
- Excel(エクセル) エクセル 計算式を教えてください 3 2022/10/19 08:58
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelで数式内の文字色を一部だ...
-
【エクセル】IF関数 Aまたは...
-
対象セル内(複数)が埋まった...
-
エクセルで指定したセルのどれ...
-
Excelでのコメント表示位置
-
エクセル 足して割る
-
貼り付けで複数セルに貼り付けたい
-
Excelで教えてください。 バー...
-
枠に収まらない文字を非表示に...
-
セルをクリック⇒そのセルに入力...
-
エクセルの一つのセルに複数の...
-
【Excel】 セルの色での判断は...
-
excelのCOUNTIF関数で、『範囲=...
-
エクセル “13ヶ月”を“1年1ヶ月...
-
(Excel)数字記入セルの数値の後...
-
Excel2003 の『コメント』の編...
-
セルの高さ(行高)を求めるには?
-
エクセルでオブジェクトを常に...
-
EXCELのセルの中の半角カンマの...
-
VBAで特定の文字が入力されたセ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで指定したセルのどれ...
-
【エクセル】IF関数 Aまたは...
-
セルをクリック⇒そのセルに入力...
-
Excelで数式内の文字色を一部だ...
-
エクセル 足して割る
-
対象セル内(複数)が埋まった...
-
貼り付けで複数セルに貼り付けたい
-
Excelでのコメント表示位置
-
エクセルのセルの枠を超えて文...
-
エクセル オートフィルタで絞...
-
エクセルの一つのセルに複数の...
-
EXCEL VBA セルに既に入...
-
【Excel】 セルの色での判断は...
-
(Excel)数字記入セルの数値の後...
-
エクセル “13ヶ月”を“1年1ヶ月...
-
excelのCOUNTIF関数で、『範囲=...
-
Excel2003 の『コメント』の編...
-
枠に収まらない文字を非表示に...
-
Excelで住所を2つ(町名迄と番...
-
複数のセルのいずれかに数字が...
おすすめ情報
shien-r様より教えて頂きました サイトより勉強をと思っておりますが、
やはり私には少し時間がかかりそうです。
ひとまず、上記のように動作するコードを教えていただけませんでしょうか?
どうぞよろしくお願い致します。