エクセル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と関連する良く見られている質問

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で配列数式はおそらく拒否されると思います。
そこで...続きを読む

QEXCELでマクロを使って、小計、合計の出し方

1.部の中にそれぞれ、営業1課、営業2課、…があり、社員と売上金額が表示されている下記のようなデータがあります。
部、課、社員の数は、実際はもっとたくさんあり、それぞれの件数は、毎月変化します。
マクロを使って、課毎計、部毎計、総合計を出す方法を教えて下さい。
試しに作りましたら、下記のような結果になり、うまくいきません。
元データ
部課社員金額
A営業1課a10
A営業1課b20
A営業1課c30
A営業2課d40
A営業2課e50
A営業2課f60
B営業1課g70
B営業1課h80
B営業1課I90
B営業2課j100
B営業2課k110
B営業2課l120

実行結果
       × 正解
部課社員金額 金額
A営業1課a10 10
A営業1課b20 20
A営業1課c30 30
 営業1課 計 60 60
A営業2課d40 40
A営業2課e50 50
A営業2課f60 60
 営業2課 計210 150
A 合計  110 210
B営業1課g70 70
B営業1課h80 80
B営業1課I90 90
 営業1課 計240 240
B営業2課j100 100
B営業2課k110 110
B営業2課l120 120
 営業2課 計570 330
B 合計  230 570
総合計  780 780

Sub 合計計算()
Sheets("元").Select
Sheets("元").Copy Before:=Sheets(2)
Dim GYO1 As Long '部 グループの先頭行
Dim GYO2 As Long '部 グループの最終行
Dim GYO3 As Long '課グループの先頭行
Dim GYO4 As Long '課グループの最終行
Dim GYO As Long '小計、合計行
Dim strFORMULA As String
GYO = 2
'空白でない間、次の作業を繰り返す
Do While Cells(GYO, 1).Value <> ""
GYO1 = GYO
GYO = GYO + 1
'部が同じ間、次の作業を繰り返す
Do While Cells(GYO, 1).Value = Cells(GYO1, 1).Value
GYO = GYO + 1
'課が同じ間、次の作業を繰り返す
GYO3 = GYO
Do While Cells(GYO, 2).Value = Cells(GYO3, 2).Value
GYO = GYO + 1
Loop

'課計
GYO2 = GYO - 1
Rows(GYO).Insert
Cells(GYO, 2).Value = Cells(GYO3, 2).Value & " 計"
Cells(GYO, 4).FormulaR1C1 = "=SUBTOTAL(9,R" & GYO1 & "C:R" & GYO2 & "C)"
GYO = GYO + 1
Loop

'部計
GYO4 = GYO - 1
Rows(GYO).Insert
Cells(GYO, 1).Value = Cells(GYO1, 1).Value & " 合計"
Cells(GYO, 4).FormulaR1C1 = "=SUBTOTAL(9,R" & GYO3 & "C:R" & GYO4 & "C)"
GYO = GYO + 1
Loop

' 総合計
Cells(GYO, 1).Value = "総合計"
Cells(GYO, 4).FormulaR1C1 = "=SUBTOTAL(9,R1C:R" & GYO2 & "C)"
Range("A1").Select
End Sub
2.尚、この質問のように表形式のデータを間隔をあけて原稿を作成しても確認画面になると、間隔が詰まります。間隔が詰まらない方法も教えて下さい。

1.部の中にそれぞれ、営業1課、営業2課、…があり、社員と売上金額が表示されている下記のようなデータがあります。
部、課、社員の数は、実際はもっとたくさんあり、それぞれの件数は、毎月変化します。
マクロを使って、課毎計、部毎計、総合計を出す方法を教えて下さい。
試しに作りましたら、下記のような結果になり、うまくいきません。
元データ
部課社員金額
A営業1課a10
A営業1課b20
A営業1課c30
A営業2課d40
A営業2課e50
A営業2課f60
B営業1課g70
B営業1課h80
B営業1課I90...続きを読む

Aベストアンサー

こんにちは。

>最初のレイアウトのようにそれぞれの部、課毎の下に計を出せないでしょうか?

#2 さんのご指摘のように、[ピボットテーブル]や、データの中の[集計]を使ったほうが簡単だと思います。

それと、「実行結果」というもののレイアウトが良く理解できていません。なぜ、計算データを二重にする必要があるのか分かりません。

この種のマクロは、素人もベテランの人も、内容はほとんど変わりません。有志の方で、構わない、作りますという方は、ここのカテゴリでも、他の掲示板でもいますが、なるべく、個人のマクロの勉強の過程の中で開発していくようにお願いしたいと思っています。ただ、あまり実務に直結したマクロの勉強には、ほとんどならないとは思います。私も、今回、たまたま別の方の質問の余韻が残っていたので、#1に書いたまでで、本来、以下のようなマクロは現在は掲示板にはほとんど書いていません。


一応、書いた責任上は、ここにコードを出しておきます。

'標準モジュール

Sub SortEnter()
  Dim i As Long
  Dim EndRow As Long
  Dim RowDiff As Long
  
  Application.ScreenUpdating = False
  'ソート
  With Range("A1").CurrentRegion
    .Sort _
    Key1:=.Range("A2"), Order1:=xlAscending, _
    Key2:=.Range("B2"), Order2:=xlAscending, _
    Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom, SortMethod:=xlPinYin, _
    DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
    RowDiff = .Cells(.Cells.Count).Row - .Rows.Count
    EndRow = .Cells(.Cells.Count).Row
    For i = .Rows.Count To 3 Step -1
    '部
      If StrComp(Trim(.Cells(i, 1).Value), Trim(.Cells(i - 1, 1).Value), 1) <> 0 Then
        .Cells(i, 1).Resize(2).EntireRow.Insert
        i = i - 2
      End If
      '課
      If StrComp(Trim(.Cells(i, 2).Value), Trim(.Cells(i - 1, 2).Value), 1) <> 0 Then
        .Cells(i, 1).EntireRow.Insert
        i = i - 1
      End If
    Next i
  End With
  Call FormulaSumEnter
  Application.ScreenUpdating = True
End Sub
Private Sub FormulaSumEnter()
  '数式を入れるマクロ
  Dim FstRow As Long
  Dim FstRow1 As Long
  Dim FstRow2 As Long
  Dim TotalRow As Long
  Dim i As Long
  TotalRow = Range("A65536").End(xlUp).Row + 2 '合計欄の2行を加える
  FstRow = 2 '計算の最初の行
  FstRow1 = FstRow
  FstRow2 = FstRow
  
  For i = 2 To TotalRow
    If Cells(i, 2).Value = "" Then
      Cells(i, 4).FormulaLocal = "=SUBTOTAL(9,R[" & CStr(FstRow1 - i) & _
      "]C:R[-1]C)"
      Cells(i, 2).Value = Cells(i - 1, 2).Value & " 計"
      FstRow1 = i + 1
      
      If Cells(i + 1, 2).Value = "" Then
        Cells(i + 1, 4).FormulaLocal = "=SUBTOTAL(9,R[" & CStr(FstRow2 - i - 1) & "]C:R[-2]C)"
        Cells(i + 1, 1).Value = Cells(i - 2, 1).Value
        Cells(i + 1, 2).Value = "合 計"
        FstRow2 = i
      End If
    End If
  Next i
  Cells(i, 1).Value = "総 合 計"
  Cells(i, 4).FormulaLocal = "=SUBTOTAL(9,R[" & CStr(FstRow - i + 1) & "]C:R[-2]C)"
End Sub

こんにちは。

>最初のレイアウトのようにそれぞれの部、課毎の下に計を出せないでしょうか?

#2 さんのご指摘のように、[ピボットテーブル]や、データの中の[集計]を使ったほうが簡単だと思います。

それと、「実行結果」というもののレイアウトが良く理解できていません。なぜ、計算データを二重にする必要があるのか分かりません。

この種のマクロは、素人もベテランの人も、内容はほとんど変わりません。有志の方で、構わない、作りますという方は、ここのカテゴリでも、他の掲示板でもいますが、...続きを読む

QExcelでセルに「小計」と入力したら自動に計算してくれる関数

行タイトルが品名、数量、金額の表で、品名の列に途中で「小計」と入力すると、金額の列に数字が表示されるようにしたいです。
行が長くなり、「小計」が数回にわたり出てくる場合は、「前回小計をした次のセルから今回小計するセルの前まで」を計算させる訳ですが、どんな関数を使えばいいのかわかりません。(マクロを組むようになるのでしょうか)
どうぞ、よろしくお願いいたします。

Aベストアンサー

>(マクロを組むようになるのでしょうか)
『「小計」と入力すると』をキッカケにするなら、マクロしかないでしょう。

質問にあるように動作するコードを書いてみました。

表のカタチは、
A1に表題『品名』、B1に表題『数量』、C1に表題『金額』があるとしています。

シート1で実行してみます。
ツール→マクロ→Visual Basic Editor でVBE画面に移り、 表示→プロジェクトエクスプローラでプロジェクトエクスプローラを表示します。プロジェクトエクスプローラのSheet1をダブルクリック。出てきたコードウインドウに下記マクロをコピーして貼り付けます。

ここから

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column <> 1 Then Exit Sub 'A列でなければ何もしない
  If Target.Text <> "小計" Then Exit Sub '『小計』と入力されなければ何もしない

  Dim curRow As Long '現在行
  Dim startRow As Long '小計を開始する行
  Dim rw As Long '行カウンタ

  curRow = Target.Row

  rw = curRow - 1: startRow = 2
  '前回の『小計を探す』
  While rw > 1
    If Cells(rw, 1) <> "小計" Then
      rw = rw - 1
    Else
      startRow = rw + 1: rw = 1
    End If
  Wend

  If startRow > (curRow - 1) Then Exit Sub '『小計』行が続いたら何もしない

  'B、C列に算式を書き込む
  '数量を書き込む
  'Cells(curRow, 2).Formula = "=SUM(B" & startRow & ":B" & (curRow - 1) & ")"
  '金額を書き込む
  Cells(curRow, 3).Formula = "=SUM(C" & startRow & ":C" & (curRow - 1) & ")"
End Sub

>(マクロを組むようになるのでしょうか)
『「小計」と入力すると』をキッカケにするなら、マクロしかないでしょう。

質問にあるように動作するコードを書いてみました。

表のカタチは、
A1に表題『品名』、B1に表題『数量』、C1に表題『金額』があるとしています。

シート1で実行してみます。
ツール→マクロ→Visual Basic Editor でVBE画面に移り、 表示→プロジェクトエクスプローラでプロジェクトエクスプローラを表示します。プロジェクトエクスプローラのSheet1をダブルクリック。出てきたコー...続きを読む

Q見積書の書き方(項目ごと小計を出したい)を教えて下さい

大きな項目で分けて、それぞれの計を出して、トータルを出さなければならないのですが、その場合、小計・合計などの書き方を教えていただきたいです。以下のようになるのでしょうか?
ex
1)広告掲載料
yahoo   3000000
excite   2000000
1)小計   5000000
2)広告ページ制作
企画・構成200000
ディレクション150000
2)小計     350000
1)+2)  5350000
     消費税      267500
     合計       5617500

*1)2)それぞれの小計を出さなくてよいなら、以下のようでいいと思うのですが。。。

1)広告掲載料
yahoo   3000000
excite   2000000
2)広告ページ制作
企画・構成200000
ディレクション150000
     小計        5350000
     消費税      267500
     合計       5617500

金額がどうのではなく、小計、合計、などの書き方が知りたいです。サンプルなどがあがっているものがあれば、教えていただければ幸いです。

大きな項目で分けて、それぞれの計を出して、トータルを出さなければならないのですが、その場合、小計・合計などの書き方を教えていただきたいです。以下のようになるのでしょうか?
ex
1)広告掲載料
yahoo   3000000
excite   2000000
1)小計   5000000
2)広告ページ制作
企画・構成200000
ディレクション150000
2)小計     350000
1)+2)  5350000
     消費税      267500
     合計       5617500

*1)2)それぞれの小計を出さなくてよいなら、...続きを読む

Aベストアンサー

項目ごとに分類してトータルを表示したいとき小計を使用します。最初のほうが正解です。

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

Q次のような見積書フォームを作成することは可能でしょうか。

よくある見積書の形式通りなのですが、明細行は内容・明細・数量・単位・単価・金額・備考の横並びセル群から成り、その明細行は下方へ適当回繰り返されて、最後にそれらを合算する小計行が来ます。

そこでご質問は、3行程度の明細行+小計行の簡単なものを作っておいて、最初の一行を記入すれば自動的に4行目の空欄明細行が生成されて、小計行は計算結果を表示しつつ自動的に一行繰り下がる、というものは可能でしょうか。同様に2行目を記入すれば新しい明細行の5行目を小計行の一段上に生成したいのです。

エクセルで見積書を作ってあげるのですが、見積が大量になった時に明細行が不足するのを恐れてPC上でトイレットペーパーのように長大な見積用紙を用意した挙句にオートフィルタを使って短縮表示するという無駄と使いにくさを解消したいのです。 

Aベストアンサー

No2です。
マクロはエクセルでAlt+F11でVBAが出てきます。
左側にあるプロジェクトのSheet1を右クリックし、コードの表示を選択。
Sheet1(コード)という画面が出てきますのでそこに下記を貼り付けし。
No2で書いてあったように
1.A2~G2まで適当なデータを入力。
2.H2をクリックするとA5~G2に転記される。
3.I2をクリックすると新規見積書というメッセージボックスが表示。

練習としてやってみて下さい。VBAの楽しさも分ると思いますし、質問者さんのしたいことも、イメージとして出来てくると思います。
頑張ってください。


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Worksheets(1)
La = .Range("a65536").End(xlUp).Row
If La = 2 Then La = 5
Select Case Target.Column & Target.Row
Case "82"
For i = 1 To 7
.Cells(La + 1, i).Value = .Cells(2, i).Value
Next
Case "92"
MsgBox "新規見積書作成"
End Select
End With

End Sub

No2です。
マクロはエクセルでAlt+F11でVBAが出てきます。
左側にあるプロジェクトのSheet1を右クリックし、コードの表示を選択。
Sheet1(コード)という画面が出てきますのでそこに下記を貼り付けし。
No2で書いてあったように
1.A2~G2まで適当なデータを入力。
2.H2をクリックするとA5~G2に転記される。
3.I2をクリックすると新規見積書というメッセージボックスが表示。

練習としてやってみて下さい。VBAの楽しさも分ると思いますし、質問者さんのしたいことも、イメージと...続きを読む

Qエクセルの表の集計をVBAでやりたいのです。

エクセルのsheet1にこのような表があります。
A B C D E
1 名称 金額
2 あああああ 10000
3 あああああ 12000
4 あああああ 9000
5 いいいいい 9500
6 いいいいい 11000
7 ううううう 15000
8 えええええ 12000
9 おおおおお 10000
10 おおおおお 14000
11 かかかかか 13000
12 ききききき 800

以下多数

この表で、それぞれ同じ名称の個数と合計金額を求めたいのです。
たとえば「ああああ」なら個数 3、合計 31000 と、D4とE4に、
「いいいい」なら個数 2、合計 25000 と、D6とE6に入れたいのです。
実際の表はデータ件数が1万件を越えますのでいちいち手で入れるわけにはいきません。
VBAでやるにはどう記述したらいいでしょうか?
どうかお助けください。よろしくお願いします。

エクセルのsheet1にこのような表があります。
A B C D E
1 名称 金額
2 あああああ 10000
3 あああああ 12000
4 あああああ 9000
5 いいいいい 9500
6 いいいいい 11000
7 ううううう 15000
8 えええええ 12000
9 おおおおお 10000
10 おおおおお 14000
11 かかかかか 13000
12 ききききき 800

以下多数

この表で、それぞれ同じ名称の個数と合計金額を求めたいのです。
たとえば「ああ...続きを読む

Aベストアンサー

A列は名称でB列は金額でよろしいんですよねというか
その前提で書きましたが。
デバッグはしてません。またオーバーフロー等あると思うのでそのあたりはご自分で。

Sub 集計()
Dim i, MyTotal As Double, MyCount As Double
MyTotal = Sheets(1).Range("A2")(1, 2)
MyCount = 1
For i = 2 To 65535
If Sheets(1).Range("A2")(i) = "" Then Exit For
If Sheets(1).Range("A2")(i) = Sheets(1).Range("A2")(i - 1) Then
MyTotal = MyTotal + Sheets(1).Range("A2")(i, 2)
MyCount = MyCount + 1
Else
Sheets(1).Range("A2")(i - 1, 4).Formula = MyCount
Sheets(1).Range("A2")(i - 1, 5).Formula = MyTotal
MyTotal = Sheets(1).Range("A2")(i, 2)
MyCount = 1
End If
Next i
Sheets(1).Range("A2")(i - 1, 4).Formula = MyCount
Sheets(1).Range("A2")(i - 1, 5).Formula = MyTotal
End Sub

A列は名称でB列は金額でよろしいんですよねというか
その前提で書きましたが。
デバッグはしてません。またオーバーフロー等あると思うのでそのあたりはご自分で。

Sub 集計()
Dim i, MyTotal As Double, MyCount As Double
MyTotal = Sheets(1).Range("A2")(1, 2)
MyCount = 1
For i = 2 To 65535
If Sheets(1).Range("A2")(i) = "" Then Exit For
If Sheets(1).Range("A2")(i) = Sheets(1).Range("A2")(i - 1) Then
MyTotal = MyTotal + Sheets(1).Ran...続きを読む

QExel VBA 別ブックから該当データを検索し、必要なデータを取得する方法について

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数ではなく、マクロで処理を希望します。

自分では、部品表の商品番号をコピーして、コード一覧表で検索し、検索結果の右隣のセル(B列のコード)の値を部品表のC列に貼り付ければよいかと思い、書いてみたんですが…

Sub 別ブックから貼り付ける()
  Dim 検索する As Long
Windows("部品表.xls").Activate
検索する = cells(i,2).Value
Windows("コード一覧表.xls").Activate
ActiveWindow.SmallScroll Down:=-3
Selection.AutoFilter Field:=3, Criteria1:="=検索する", Operator:= xlAnd

と、してみたものの、検索しても、その検索結果の隣のセルのコードをどうやって取得すればいいのかが、わかりませんでした。

基本事項は本で学びましたが、呪文のようなコードはよく理解できません。懸命にネットで検索して、訳して理解する努力をしてはいますが。

どうぞよろしくお願いします。

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数...続きを読む

Aベストアンサー

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks.Open("C:\★★\コード一覧表.xls") '★要変更★
 I = 2
 Do While Range("A" & I).Value <> ""
  ThisWorkbook.Worksheets("Sheet1").Range("C" & I).Value = Application.VLookup(ThisWorkbook.Worksheets("Sheet1").Range("B" & I).Value, xlBook.Worksheets("Sheet1").Range("A2:B65535"), 2, 0)
  I = I + 1
 Loop
 xlBook.Close
 Application.ScreenUpdating = True
 MsgBox ("完了")
End Sub

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks....続きを読む

Qエクセルで条件に一致したセルの隣のセルを取得したい

下のような「得点」という名前のシートがあります。
(「田中」のセルがA1です。)

 [ 田中 ][ 10 ][ 200 ]
 [ 山田 ][ 21 ][ 150 ]
 [ 佐藤 ][ 76 ][ 250 ]
 [ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

 [ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

 [ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは!
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー!
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m

Qエクセル イベントマクロ Changeイベントを複数作りたい

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

以下マクロの内容

End Sub

これでセルA1が変わるとイベントマクロが発生しますが
セルB1が変わると別のマクロが発生し
セルC1が変わるとまた別のマクロが発生し…とするには
どうすればよいのですか
単純に並べて書いたらだめみたいだったんですけど

Aベストアンサー

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$A$1"
 A1のマクロ文
Case "$B$1"
 B1のマクロ文
Case "$C$1"
 C1のマクロ文
・・・・
End Select
End Sub
こんな感じでは?


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

人気Q&Aランキング

おすすめ情報