エクセル2007でVBAを何度も挫折しています
任意のセルに小計、合計と入力したら自動計算してくれるVBAの方法を教えてください

セル
G21 商品名
L21 詳細
V21 数量
X21 単位
Z21 単価
AD21 金額(V21:数量*Z21:単価)

の何処にでもある見積書なのですが
任意のG行に小計・合計と入力すると
AD行に金額が自動で入力させるVBAの仕方が知りたいです。

3行ほどの見積もあれば100行を超える見積もあります

教えてください。

このQ&Aに関連する最新のQ&A

A 回答 (5件)

シート名タブを右クリックしてコードの表示を選び,現れたシートに下記をコピー貼り付ける



private sub worksheet_change(byval Target as excel.range)
 dim ta as range
 dim ha as range
 dim h as range
 dim d as range
 set ta = application.intersect(target, range("G22:G" & cells.rows.count))
 if ta is nothing then exit sub

 for each ha in ta.areas
 for each h in ha

 if h = "合計" then
  cells(h.row, "AD").formula = "=SUBTOTAL(9,AD21:AD" & h.row - 1 & ")"

 elseif h = "小計" then
  set d = range(range("G21"), h.offset(-1)).find(what:="小計", after:=h.offset(-1), lookin:=xlformulas, lookat:=xlwhole, searchdirection:=xlprevious)
  if d is nothing then set d = range("AD21")
  cells(h.row, "AD").formula = "=SUBTOTAL(9,AD" & d.row + 1 & ":AD" & h.row - 1 & ")"
  set d = range(h.offset(1), cells(cells.rows.count, "G")).find(what:="小計", after:=h.offset(1), lookin:=xlformulas, lookat:=xlwhole, searchdirection:=xlnext)
  if not d is nothing then
   cells(d.row, "AD").formula = "=SUBTOTAL(9,AD" & h.row + 1 & ":AD" & d.row - 1 & ")"
  end if
 end if
 next
 next
end sub

G列に小計や合計と記入する。


合計はまぁ1つしか無いという事にして,小計は上下で重ならない範囲を計算することにします。これに伴い,既存の小計範囲に割り込んで小計を挿入した場合は,既設小計の範囲を縮小します。
2層以上の中計小計をしたいときは,アウトラインの機能などを組み合わせてエクセルが最初からもっている集計の機能などを使って下さい。
ていうか,一体どこからどこまでの小計を出させたいのかは,あなたが本来考えて必要なら「こうしたい」をご相談すべき内容です。
    • good
    • 0

>任意のG行に小計・合計と入力すると


Gなどは列を指定するために使う。
だから何のいみかわからない。
数量X単価を出すなら、VBAで処理するにしても、
=Range("V21")*Range("Z21")
あるいはセルにFormulaで式を入れる方法もある。
で良いのでは。
>小計・合計と入力すると
なぜこういう文字列を入れる必要があるのか。
ーー
私が質問の意味を、誤解しているかも知れないが、原因はデータ例の構成を(数行で良いが)を質問文に書かないからだと思う。
ーーー
これはどういう場面で計算するのか
1社の明細が入力を終わったとき?
1行のデータを入力が終わったとき?
何か合計ボタンに様なものをクリックしたとき?(<-小計、合計と入力したら、はこの意味か?)
ーーー
>3行ほどの見積もあれば100行を超える見積もあります
これは1社辺りのことか。
ひょっとしてSUMPRODUCT関数で出来ることを言っているのか
Σ単価X数量のいみで
単価X数量の行合計か?
    • good
    • 0

ワークシート関数でもできる事をVBAで実現しようとしている事はご質問者がVBAをマスターしたいという事と想定すると質問の丸投げはやめて挫折しているコード等を質問すべきと思います。

    • good
    • 0

VBAでなく、エクセルの普通の計算式を入れればいいですね。



AD21に「=V21*Z21」の数式を入れておけば、自動的に入りますが、普段は、非表示にしておくのであれば、IF関数を使います。

任意のG列を仮にG2とします。

それでAD21に次の式を入れます。

=IF(G2="小計",V21*Z21,"")

これは、G2に小計と入力された場合、V21とZ21をかけた答えを表示する、という意味です。
    • good
    • 0

セル


G21 商品名
・・・・・AD21 金額(V21:数量*Z21:単価)
これは見出し行で、その下にデータが入力されるという事でいいのかな??

>エクセル2007でVBAを何度も挫折しています
ちなみに今回はどういうVBAを考えて、どこで挫折しているんでしょうか??

G行の最下段を選択して、その直情のデータ入力行(Ctrl+↑)へ飛んで
>任意のG行に小計・合計と入力すると
この位置を選び、そこから相対的に23セル右(ADxx)を選んで、AD22からの合計を出す式を入力する
・・・・・こんなんじゃ駄目かな??
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QVBAで、アクティブなBOOKのファイル名を取得し

エクセルのVBAを使用して、選択されている、BOOKのファイル名を取得し、下記のように編集してA1セルに入れたいのですが、可能でしょうか?


BOOKのファイル名が「大阪_たこ焼き_1234.xls」の場合

大阪_と.xlsをは省いて、「たこ焼き_1234」がA1セルに入るようにしたい。

Aベストアンサー

拡張子なんでもござれ!
Sub TheBody()
Const xSeparator = "_"
Const xPeriod = "."
Dim KitCut As Variant
KitCut = Split(ActiveWorkbook.Name, xPeriod)
KitCut = Split(KitCut(0), xSeparator)
Range("A1").Value = KitCut(1) & xSeparator & KitCut(2)
Columns("A").AutoFit
End Sub

Qエクセル2007で小計機能を使い、集計しています。 小計を2回つかい、

エクセル2007で小計機能を使い、集計しています。 小計を2回つかい、ある数値を、「学年」→「氏名」で集計しています。画面では氏名で集計され、その見えているまま別シートに貼り付けしたいのですが、
貼り付けをすると、隠されているデータもすべてが貼り付けされます。(人により氏名データが1~30くらいありそれを集計している)
見えているまま、貼り付けできる方法を教えていただけないでしょうか?

Aベストアンサー

見えているセルだけをコピーしたいと言う事ですよね?
以下は2003での操作ですが、2007でも大差ないでしょう。

1.コピーする範囲選択
2.「Ctrl」+「G」を同時に押し、「ジャンプ」画面を表示
3.ジャンプ画面の「セル選択」ボタンを押下
4.「可視セル」にチェック、「OK」
5.「Ctrl」+「C」でコピー

あとは、普通に「Ctrl」+「V」で貼り付け

これで如何でしょう

QExcelVBA:自己のBook名を取得したい

WindowsXP-Proです。
Excelヴァージョンは2003です。

ExcelVBAでコーディングしています。
で、自分自身(つまり、このVBAコードを記述しているExcel本体)のBook名を取得したいのですが、何か関数は用意されていますでしょうか?

自分自身のBook名を取得したい理由は、VBAコードを記述しているExcel本体のファイル名(Book名)の名前が変更されても、VBAが正常に機能するように、今現在のBook名を取得したいのです。

複数のExcelファイルを、このVBAで操作しているため、
Workbooks("本体のBook名").Activate
を用いており、仮にファイル名(本体のBook名)の名前が変更されても、VBAが正常に機能できるように、"本体のBook名"部分を固定ではなく、可変で持てるようにしたいからです。

Aベストアンサー

Public Sub Auto_Open()
  MsgBox ActiveWorkbook.Name
  MsgBox ThisWorkbook.Name
End Sub

Private Sub Workbook_Open()
  MsgBox Me.Name
End Sub

いずれも、ブック名が表示されました。

Q【Excel VBA】特定の列で時間を入れているセルだけ合計して数式をセルにいれたい

Excel VBA初心者です。

特定の列の各セル(B列とします)に時間(数字)、件数(数字)、文字列(○や×)が入っています。
この中でランダムな位置に入っている時間(数字)のみ拾い出し、合計欄(セルB20とします)に「数式(例)=sum(b3,b6,b8,b13,b18)」を入れたいのですが、マクロの組み方がわかりません。上記の特定のセルを抽出するところまではできたのですが。

マクロの組み方をご教授願えますか。

Aベストアンサー

No.1です。
AA列に対象セルの一覧がある場合はこんなのではどうでしょうか?
Sub sample()
Dim lastRow As Long
Dim rngStr As String
Dim r As Long
’AA列の最終行取得
lastRow = Range("AA" & Rows.Count).End(xlUp).Row
'1列から最終行まで
For r = 1 To lastRow
'rngStrが空白でない場合(2個目以降)はカンマを付ける
If rngStr <> "" Then rngStr = rngStr & ","
'セルを追加
rngStr = rngStr & Range("AA" & r).Value
Next
Range("B20").Formula = "=sum(" & rngStr & ")"
End Sub

実際は上記よりも、AA列を作る時点で計算をすれば、AA列を作る必要が無いと思います。
合計の変数を用意して
Range("AA1") = "b3"
または、
Cells(1, 27) = "b3"
にする場所で、AA列に代入せずに、その値を利用して、合計変数にセルの値を加えればいいと思います。
ただ、質問の内容から細かい仕様がわからないので、既に出来ているAA列があるなら、それを利用して計算するのも間違いない方法だと思います。

No.1です。
AA列に対象セルの一覧がある場合はこんなのではどうでしょうか?
Sub sample()
Dim lastRow As Long
Dim rngStr As String
Dim r As Long
’AA列の最終行取得
lastRow = Range("AA" & Rows.Count).End(xlUp).Row
'1列から最終行まで
For r = 1 To lastRow
'rngStrが空白でない場合(2個目以降)はカンマを付ける
If rngStr <> "" Then rngStr = rngStr & ","
'セルを追加
rngStr = rngStr & Range("AA" & r).Value
Next
Range("B20").Formula = "=sum(" & rngStr & ")"
End Sub

実...続きを読む

Q他のワークシート名の取得方法 (VBAを使用せずに)

VBAを用いずに、ワークシート関数のみでワークシート名を取得できないか探しています。

自分のシート名は、以下の出力結果の一部より取得することができました。
=CELL("filename")

しかし、他のシート名を取得する方法が思いもつきません。

VBAを用いずにシート名を取得することはできないのでしょうか?

Aベストアンサー

Excel2000でしたら、
1.[挿入]-[名前]-[定義] から、名前を2つ定義します。
  ・名前:PPP  参照範囲:=GET.WORKBOOK(1)
  ・名前:QQQ  参照範囲:=GET.DOCUMENT(88)
2.A1 に =SUBSTITUTE(INDEX(PPP,ROW()),"["&QQQ&"]","") と入力します。
3.A1 を下方にドラッグコピーすると、シート名が一覧で表示されます。

例えば3枚目のシート名のみを取得する場合は、任意のセルに
=SUBSTITUTE(INDEX(PPP,3),"["&QQQ&"]","") と入力します。

※マクロ関数というものですが、最近のバージョンにこれが付帯されているのかどうか
  わかりませんが。   ^_^;

Qツリー状の集計表の小計、合計を同じ行で計算するには?

   A   B   C   D   E   F   G
 1            商品名  単価  個数 
 2 合計                     1800※
 3     食物                 1600※
 4         果物              800※
 5             りんご 100   5 500
 6             ばなな 150   2 300
 7         さかな             800※
 8             いか  200   1 200
 9             さんま 200   3 600
10     文房具                 200※
11         紙               200※
12             A3   10  10 100
13             A4    5  20 100

上のような表があるとします。
G4、G7、G11には下2品目の小計、G3、G10にはそのその分
類の小計、G2にはその合計、と言ったように※印ついている数字の部
分を自動的に計算するような良い方法はないでしょうか?

このような感じの表を作ることが多いのですが、その都度sumを使って
計算させてましたが、間違うことが多く(自分のせいなのですが(^^;)
マクロを使って、ボタン一つで計算出来るようにしたいのですが・・。
良い方法をご存じの方、いらっしゃいましたらお願いいたします。

   A   B   C   D   E   F   G
 1            商品名  単価  個数 
 2 合計                     1800※
 3     食物                 1600※
 4         果物              800※
 5             りんご 100   5 500
 6             ばなな 150   2 300
 7         さかな            ...続きを読む

Aベストアンサー

>Dが空白でない場合、GにE*Fの答えを入力させるには、どうしたらよいのでしょうか?

E*Fを計算できるようにしてみました。
操作方法は前回と同じで、A2~G13を選択状態にしてマクロを実行します。

こういう風に小計を出していくには、このようなTree構造のほうが処理が行いやすいかもしれませんね。判定に未入力セルを有効に使えます。


Sub TreeTotal()
  Dim Area As Range '選択範囲
  Dim top As Range '一番左上のセル
  Dim TTLcolumns As Integer '選択範囲の列数
  Dim TTLrows As Integer '選択範囲の行数

  Set Area = Selection
    TTLcolumns = Area.Columns.Count - 1
    TTLrows = Area.Rows.Count - 1
  Set top = Area.Range("A1")

  Dim c As Integer '列カウンタ
  Dim r As Integer '行カウンタ
  Dim TTL As Long 'トータル
  Dim Elm As Long '単価×個数の値
  With top
    '単価×個数の計算
    For r = TTLrows To 0 Step -1
      If .Offset(r, TTLcolumns - 3) <> "" Then
        Elm = .Offset(r, TTLcolumns - 2) * .Offset(r, TTLcolumns - 1)
        .Offset(r, TTLcolumns) = Elm
      End If
    Next

    '小計、合計の計算
    For c = TTLcolumns - 4 To 0 Step -1
      For r = TTLrows To 0 Step -1
        If .Offset(r, TTLcolumns - 3) <> "" Then
          TTL = TTL + .Offset(r, TTLcolumns)
        ElseIf .Offset(r, c) <> "" Then
          .Offset(r, TTLcolumns) = TTL
          TTL = 0
        End If
      Next
    Next
  End With
End Sub

>Dが空白でない場合、GにE*Fの答えを入力させるには、どうしたらよいのでしょうか?

E*Fを計算できるようにしてみました。
操作方法は前回と同じで、A2~G13を選択状態にしてマクロを実行します。

こういう風に小計を出していくには、このようなTree構造のほうが処理が行いやすいかもしれませんね。判定に未入力セルを有効に使えます。


Sub TreeTotal()
  Dim Area As Range '選択範囲
  Dim top As Range '一番左上のセル
  Dim TTLcolumns As Integer ...続きを読む

QEXCEL VBA で現在開いているブックのファイル名を取得する方法

EXCEL2003 VBAで業務を簡素化するために、現在開いているブックのファイル名を取得する方法が分かりません。
作業手順をマクロを使って処理していますが、オリジナルのワークブックをファイル名を変えて保存し、以後、このワークブックを読み込んで使用しています。
このときのVBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり、以後の業務に使用できません。
常にファイル名を取得出来るVBAをどなたか、教えて下さい。

Aベストアンサー

>現在開いているブックのファイル名
 ちょっと曖昧な表現かなぁという気もいたしますが、VBAが書いてあるブックのブック名は
ThisWorkbook.Name
で、現在 "アクティブにして" 操作対象になっているブックの名前は
ActiveWorkbook.Name
ですね。

 しかし、
>VBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり
というような文脈からすると、
ThisWorkbook.Name
の方ですかね。

Qエクセルのオートザムで計算した合計金額のセルをコピーして、他のセルに貼

エクセルのオートザムで計算した合計金額のセルをコピーして、他のセルに貼り付けると、数式も一緒にコピーされます。
貼り付けた後、数式を削除しても金額が計算されたままの金額が表示(残る)されるようにすることは可能でしょうか。
非常にわかりづらい質問で恐縮ですが、よろしくお願いいたします。

Aベストアンサー

ただ単に、コピー、貼り付け、をすると、セルの内容全体(数式も書式もなにもかも)が

複製されてしまいます。


もし、セルに表示されている数字(値)だけをコピーする場合には、

コピーして、コピー先で、右クリックすると表示されるメニューの中から、

「形式を選択して貼り付け」 を選びます。

そうすると、別の画面が表示されるので、上部右側中央にある、値(V) を
選んで、ok してください。

QVBAでアカウント名を取得する方法

VBAで処理したEXCELブックをデスクトップに自動保存しようとしています。VBAで現在作業中のユーザーアカウント名を自動で取得する方法を教えていただきたいのですが。

デスクトップ上にブックを保存するには、パスを記述すればよいのですが、現在PC毎にユーザーアカウントを設定しユーザー名が異なっています。
このため、PC毎にこのユーザー名をデスクトップへのパスに入れ込まなければなりません。毎回キーボードからこのユーザー名を入力する方法もありますが、自動的にユーザー名を取得し、正しいパスを指定する方法を検討しています。
どなたか、VBAでこのユーザー名を取得する方法が有れば教えていただきたいのですが。
よろしくお願いいたします。

Aベストアンサー

Environ関数で、環境変数[USERNAME]を取得する。

MsgBox Environ("USERNAME")

QExcel VBA 「小計」と入力したら自動計算

エクセルの小計の計算について質問いたします。

   A列    B列      C列   D列     E列    F列

1 施設名 
2      商品名,内容   1    単位     単価    金額(数量×単価)
3      商品名,内容   1    単位     単価    金額(数量×単価)
4                              小計     ●●●●
―――――――――――――――――――――――――――――――――
5 施設名
6      商品名,内容   1    単位     単価    金額(数量×単価)
7      商品名,内容   1    単位     単価    金額(数量×単価)
8      商品名,内容   1    単位     単価    金額(数量×単価)
9                              小計     ●●●●
―――――――――――――――――――――――――――――――――
                               合計     ●●●●

施設名ごとに項目が複数あり、施設ごとに項目の数も違います。
これを施設ごとに●の所に小計を出したいです。

「小計」が数回にわたり出てくる場合は、「前回小計をした次のセルから今回小計するセルの前まで」を計算させる訳ですが、どんなVBAを組めばいいのかわかりません。

理想としてはにE列に小計という文字を入れるとF列に自動で計が出るようにして、さらに小計の合計を最後の行のセルにだしたいです。。
さらに、見やすくなるように小計の下のセルにA~Fの間に太線を引いて施設ごとの区切りがわかるようにしたいです。。

繰り返し作業ばかりで大変です。

どなたか知恵をお貸しください。

エクセルの小計の計算について質問いたします。

   A列    B列      C列   D列     E列    F列

1 施設名 
2      商品名,内容   1    単位     単価    金額(数量×単価)
3      商品名,内容   1    単位     単価    金額(数量×単価)
4                              小計     ●●●●
―――――――――――――――――――――――――――――――――
5 施設名
6      商品名,内容   1    単位...続きを読む

Aベストアンサー

続けてお邪魔します。
No.9の補足を読みました。
この際ですので、とことん付き合っちゃいます!
今までのコードはすべて無視してください。

結局、↓の画像でB・E(A列も含めて)変更があるたびにマクロを実行しなければならなくて、
なおかつ小計・合計の行に関しては数式で表示!
がご希望だというコトですので、もう一度考えてみました。

合計の場合は単にSUMIF関数で対応できますが、小計の行は数式でやる場合
配列数式を使う必要があると思います。
VBAで配列数式はおそらく拒否されると思います。
そこで一案です。
↓の画像のように作業用の列を2列設けます。
G1セルに
=IF(E2="小計",SUM(INDIRECT("F"&LARGE($H$1:$H1,2)&":F"&MAX($H$1:$H1))))
H1セルに
=IF(COUNTA(A2:E2),IF(E2="",ROW()+2,IF(OR(E2="",E2="小計"),ROW(),"")))
という数式を入れG1・H1セルを範囲指定 → H1セルのフィルハンドルでずぃ~~~!っと
これ以上データはないというくらい「これでもか!」っとオートフィルでコピーしておきます。
作業列が目障りであれば、オートフィル後にG・H列を非表示にしておきます。
そうした操作を行った上で、↓のコードをコピー&ペーストしてデータを入力してみてください。
データ配置は画像通りとします。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim i As Long
If Application.Intersect(Target, Range("A:A,C:C,E:E")) Is Nothing Or Target.Count <> 1 Then Exit Sub

With Target
i = .Row
If .Column = 1 Then
Range(Cells(i, "E"), Cells(i, "F")).ClearContents
ElseIf .Column = 3 Or .Column = 5 Then
If IsNumeric(.Value) Then
Cells(i, "F") = Cells(i, "C") * Cells(i, "E")
Else
If IsNumeric(.Value) Then
.Offset(, 1) = .Offset(, -2) * Target
ElseIf .Value = "小計" Then
.Offset(, 1).ClearContents 'この行を追加
.Offset(, 1).Formula = .Offset(-1, 2).Formula '←G列の数式をそのまま引用
With Cells(i, 1).Resize(1, 6).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
ElseIf .Value = "合計" Then
.Offset(, 1).Formula = "=SUMIF(E:E,""小計"",F:F)"
End If
End If
End If
End With
End Sub 'この行まで

この程度しか思いつきません。
お役に立ちますかね?m(_ _)m

続けてお邪魔します。
No.9の補足を読みました。
この際ですので、とことん付き合っちゃいます!
今までのコードはすべて無視してください。

結局、↓の画像でB・E(A列も含めて)変更があるたびにマクロを実行しなければならなくて、
なおかつ小計・合計の行に関しては数式で表示!
がご希望だというコトですので、もう一度考えてみました。

合計の場合は単にSUMIF関数で対応できますが、小計の行は数式でやる場合
配列数式を使う必要があると思います。
VBAで配列数式はおそらく拒否されると思います。
そこで...続きを読む


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング