[[20030426125541]]　『小計と合計のを入れた集計表の作り方』（あまちゃん）　ページの最後に飛ぶ

『小計と合計のを入れた集計表の作り方』（あまちゃん）

マクロの初心者ですがよろしくお願いいたします

次の様な表で小計をマクロで挿入したいと思いますが、小計を

入れるまでの行数がまちまちですから、数量か単価の列で行数を取得

してと思いますが何か良い方法はないでしょうかご指導ください。

最初の小計は文字列までの行数になり、次の小計は空白までの行数に

なりどのようにして変数Ｎを数えたら良いか分かりません。

それとも他に良い小計の出し方があるでしょうか？

Ｎが出せれば下記のようなマクロでと思いますが

合計は総行数を取得して同じようにと思います。これも何か

良い方法はあるでしょうか。

管理費のように1行だけで小計の無い場合もあります。

なお、上から順番に入力してゆき１つのグループが終わったら小計のセル

をクリックしてマクロボタンで実行する予定です。

 項目	数量	単価	金額

 AAA	100	20	2000

 BBB	10	30	300

 CCC	20	20	400

 [小計]			2700

 DDD	30	10	300

 FFF	50	20	1000

 [小計]			4000

 管理費			10000

 [合計]			14000

 　dim N as integer

 　ActiveCell.FormulaR1C1 = "[小計]"

   ActiveCell.Offset(0, 3).Select

   ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-N]C:R[-1]C)"

   Range("D1000").End(xlUp).Offset(1).Select

 あまちゃん
間違いがありましたので修正します。
２番目の小計は1300で、式はSUBTOTAL(9,R[-" & N & "]C:R[-1]C)です.
今のとこらは良い案が見つかりませんので手動で範囲指定しておいて
オートＳＵＭボタンで小計をとり、合計のまくろにＳＵＭとＳＵＢＴＯＴＡＬ
の置換をして間に合わせています。
良い方法はないでしょうかよろしくお願いします。

 マクロではないので、見栄えは悪いですが、
A2に
=IF(AND(A1="[小計]",COUNTIF(INDIRECT("A1:A"&ROW()-1),"[合計]")=0),
"[合計]",IF(COUNTIF(INDIRECT("A1:A"&ROW()-1),"[合計]")>0,"",IF(A1<>"[小計]","[小計]","")))
D2に
=IF(A2="[合計]",SUM(INDIRECT("D$1:D"&ROW()-1))/2,IF(E2<>"",SUM(INDIRECT
("D"&MAX(INDIRECT("E1:E"&ROW()-1))+1&":D"&E2-1)),IF(A2<>"",C2*B2,"")))
E2に　（E1には1を入力しておく事）
=IF(A2="[小計]",ROW(),"")
と数式を入力し、必要行までコピーします。
下の様に表示されますので、
項目	数量	単価	金額	1
[小計]			0	2
[合計]			0	
　注：D2セルに循環参照のコメントが表示されますが、
　　　[キャンセル]をクリックし、数式をそのまま使うにしてください。
　　　→A2セルに項目を手入力すれば、解消されます。
各項目、数量・単価あるいは金額に入力してゆくと設定してある数式に上書きされ、
（あまちゃん）さんの求める形になります。但し、管理費にも[小計]が付きます。
マクロを組む際の参考にでもなればと思いＵＰしました。お役に立てば良いのですが (sin)

SINさん　ご回答ありがとうございました。
式がながくて、私の能力では理解ができません。
「小計」と言うマクロボタンをクリックすれば小計が出来ることを考えていましたので
もう少し検討をしてみます。

 マクロを組むと言う事では、記録マクロをやっと使える程度の私ではお役に立てません。
 どなたか『ＨＥＬＰ』！
 数式に関しては、INDIRECT関数をEXCELのヘルプで理解していただいた上で、
 IF関数を分割して見ていただければ解りやすいと思います。
 全体の考え方は、必要データを上書きする事を前提として
 項目列で小計の下に合計を表示させ、合計は1個だけ最後に表示するようにしています。
 また、項目列に文字（[小計]以外）が、あればすぐ下に[小計]と表示する仕組みです。
 小計の計算は、項目列に表示された[小計]を読み取り、E列の数字（何行目のセルか）
 を使ってSUM関数の範囲に代用しています。　説明下手ですが、こんな感じです。 (SIN)

 SINさんの関数すごいですね。感動したので釣られてみました（笑）。

 >最初の小計は文字列までの行数になり、次の小計は空白までの行数に 
 >なりどのようにして変数Ｎを数えたら良いか分かりません。

 あの～　これがはっきりしないとNを数えられないわけで、
 だれもレスの付けようがないかと（汗）。
 文字列までの行数とは？
 どこの文字列？

 ↓は小計を入れるだけのマクロです（汗）。
 上の例だと、金額のD2:D4を選択してマクロを実行するとD5に一行挿入されて
 [小計]行が入るという、それだけです。

 （ramrun）

 Sub macro()
    Dim buf As Variant
    Dim adr As String

    adr = ActiveWindow.RangeSelection.Address
    ans = "=SUBTOTAL(9," & adr & ")"

    Range(Right(adr, Len(adr) - InStr(1, adr, ":", vbTextCompare))).Offset(1, 0).Select

    Selection.EntireRow.Insert

    ActiveCell.Value = ans
    ActiveCell.Offset(0, -3).Value = "[小計]"
 End Sub

 追記

 Nをカウントする条件さえはっきりすればいいので、
 私がよく使う方法は

 N = 1
 do while(1)
  if range("a2").offset(N,0).value = [doを抜ける条件] then exit do 
  N = N + 1
 Loop

 といった感じで条件にあうセルの値を検索するまで、doでまわして
 どこかを基点とした（この場合 a2 ）移動量をカウントします。

 （ramrun）

 ramrunさん　呼応していただき、ありがとうございます。
 私が思うに、Nに固執するのではなく、
 （あまちゃん）さんの表で言うと、例えば、CCC→20→20と入力確定した後に（金額の400は計算式？）、
 もしくは、A5セルに移動した後に  <<『小計ボタン』をクリック>>  すると、
 データを入力した行の次の行のA列に（A5セルに）[小計]と表示し、
 同じ行のD列に（D5セルに）小計を算出させるマクロにしたいんだと思い、
 データを上書きしたすぐ下のセルが、そのデータに反応する式にし、
 しかもその表示させる[小計]をキーとしてその行間を読み取り、
 合計を算出入力される式にしました。（お分りだとは思いましたが、あえて記しました）
 何にしても、主役にご登場願わねば、意味が無いですね。(sin)

 （あまちゃん）	
 ＳＩＮさん、rumrun　ご指導ありがとうございました。	
 返事が遅くなりもうしわけございません。	
 ＳＩＮさんには最初の質問の説明が悪く申し訳ございません。	
 INDIRECT関数は時々使って少しは理解していますが、	
 ＞式を入力し、必要行までコピーする
 ＞管理費のような１行にも小計が入る。
 がひかかって、熟読しなくてすみませんでした。

 rumrunさんへ
 ほとんど、希望どおりにマクロを作っていただきありがとうございます。
 もう１つ出来ますれば、CCCの下のセルをクリックして置いて、小計の
 マクロボタンを押せば[小計]と2700が表示されれば大満足ですが
 むつかしいでしょうか
 そんなことで、最初の小計の行数は文字列の下からデータがあり、次の
 小計には単価と数量の列に空白のセルがっ混ざってきますから変数を
 どのように取ればよいのかと変な質問をしてしまいました。
 DDD、FFF行を入力したら同様に小計を入れます。
 最後の合計は次のマクロボタンで実行すれば良いかと思っています。

 ActiveCell.Value = "[合計]"
 ActiveCell.Offset(0, 3).Select
 ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-" & N & "]C:R[-1]C[1])"

 よろしくお願いします。

 >文字列の下からデータがあり...
 項目、数量、単価、金額のこと？

 >CCCの下のセルをクリックして置いて...
 これは入力時に空行になってるってことでいいのかな？
 行を挿入する必要はないということで考えます。
 必要でしたら空行に Selection.EntireRow.Insert を入れてください。

 （ramrun）

 Sub macro()
 Dim cp, sp, ep, buf As Variant
 Dim n As Integer 
 cp = ActiveCell.Address
 ep = ActiveCell.Offset(-1, 3).Address

 ActiveCell.Value = "[小計]"
 n = 1
 Do While (1)
    buf = ActiveCell.Offset(-n, 0).Value
    If buf = "項目" Then
        Exit Do
    ElseIf buf = "[小計]" Then
        Exit Do
    End If
    n = n + 1
 Loop
 sp = Range(cp).Offset(-n + 1, 3).Address
 ActiveCell.Offset(0, 3).Value = "=SUBTOTAL(9," & sp & ":" & ep & ")"
 End Sub

rumrunさま
早速のご指導誠にありがとうございました。
希望した通りばっちりです。解決いたしました。
追記でヒントを与えてくださったのに、私ではプログラムが完成できませんでした。
こんな面倒なことでも出来てしまうのですね。
本当に感心しました。
プログラムの内容（作り方）は、十分理解できませんのでＤｏ…Ｌｏｏｐステートメント　
を勉強して理解するよう頑張ります。

ＳＩＮさんにも長い関数の組み方を教えていただきありがとうございました。
エクセルのほとんど解らない者に入力を頼む関係でどうしてもマクロで解決したかった
わけです。
ｋａｚｕさんにもお世話になりありがとうございました。

コメント返信：

[ 一覧(最新更新順) ]

[[20030426125541]] 『小計と合計のを入れた集計表の作り方』（あまちゃん） ページの最後に飛ぶ

[[20030426125541]]　『小計と合計のを入れた集計表の作り方』（あまちゃん）　ページの最後に飛ぶ