- ベストアンサー
昨日の続き：行挿入時の合計範囲について
昨日の質問の件ですが、合計行が明細の最後ではなく、明細の先頭にある場合は難しいですよね。いろいろ試行錯誤してみたのですが、いい方法が見つかりません。 （知識不足もありますが） 何か方法があれば、ご教授ください。 よろしくお願いします。
- satoshi5050
- お礼率61% (60/97)
- Excel（エクセル）
- 回答数10
- ありがとう数7
- みんなの回答 （10）
- 専門家の回答
質問者が選んだベストアンサー
↓でわかりますか？ B C 2 Ａ合計行 4 [C2]=SUM(OFFSET(C2,1,0):OFFSET(C7,-1,0)) 3 Ａ明細 1 4 Ａ明細 1 5 Ａ明細 1 6 Ａ明細 1 7 Ｂ合計行 40 [C7]=SUM(OFFSET(C7,1,0):OFFSET(C12,-1,0)) 8 Ｂ明細 10 9 Ｂ明細 10 10 Ｂ明細 10 11 Ｂ明細 10 12 datend ----
その他の回答 (9)
- chayamati
- ベストアンサー率41% (254/607)
＞昨日の続き？ この事情はわかりませんが 合計行が1行だけなら明細行の外であればどこにあってもよいです。 私は、見出し行の次に合計行、以下を明細行としています
お礼
ご指摘ありがとうございます。
- bunjii
- ベストアンサー率43% (3589/8248)
別解 B1セル（A合計）に次の数式を設定します。 =SUM(OFFSET(A1,1,1,COUNTIF(OFFSET(A1,1,0,COUNTA(A:A)),LEFT(A1,1)&"*"))) B1をB5セル（B合計）へコピーすれば自動的にA合計とB合計が算出されます。 但し、A列の?明細nは中間にブランクがあると正しい値になりません。
お礼
いろんなやり方を教えて頂き、ありがとうございました。
- bunjii
- ベストアンサー率43% (3589/8248)
回答No.5への補足にある模擬データでしたらB1セルへ次の数式を入力してください。 =SUMIF(A2:A5,LEFT(A1,1)&"*",B2:B5) B5セルの数式はA明細とB明細の行数が同じなのでB1セルをコピー&ペーストでB5セルへ複写すれば数式の行番号が自動的に参照範囲に変化します。 B1=SUMIF(A2:A5,LEFT(A1,1)&"*",B2:B5) ↓ コピー&ペースト B5=SUMIF(A6:A9,LEFT(A5,1)&"*",B6:B9)
お礼
このやり方も出来ました。 本当にありがとうございました。
- msMike
- ベストアンサー率20% (363/1775)
[No2補足]へのコメント、 内容が支離滅裂でサッパリポンです～ゥ
- bunjii
- ベストアンサー率43% (3589/8248)
>合計行が明細の最後ではなく、明細の先頭にある場合は難しいですよね。 明細は最後の行に順次追記するだけですか？ それとも、最後の行の下へセルの挿入して追記するのでしょうか？ >データは、Ａ合計行+Ａ明細+Ｂ合計行+Ｂ明細・・・の繰り返しの為 何を言っているのか読み取れません。 模擬データを提示してください。
補足
すみません。具体的に書いた方がいいですよね。 1 Ａ合計 100 ← (1)1行目～4行目の間であれば、Ａの明細を 2 Ａ明細1 20 挿入した時に1行目のＡ合計行の関数範囲が 3 Ａ明細2 30 変わる 4 Ａ明細3 50 ← (2)4行目・5行目の間に、Ａ明細を挿入した 5 Ｂ合計 170 時は、1行目のＡ合計行の関数範囲が変わら 6 Ｂ明細1 50 ない。 7 Ｂ明細2 50 8 Ｂ明細3 70 (2)の時にも、関数の範囲が変わって1行目の合計が正常に計算されるようにしたいとということです。 よろしくお願いします。
- HohoPapa
- ベストアンサー率65% (454/691)
VBAを使った「カスタム関数」でよければ 次のようなコードで期待の動作を実現できます。 =spTotal(E2,E:E) 第一引数は合計するセル群の最上位セルです。 第二引数は合計を求めるセル群を含む列を指定します。 動作は 第一引数のセルから開始して 下方向に、空欄のセル、または計算式の埋まったセルの直前までの セルたちの合計を求めます。 なお、第二引数は計算には無関係です。 合計対象範囲が変化したときに再計算させるために与えています。 Option Explicit Function spTotal(rng As Range, DmyRange As Range) As Double Dim RowCnt As Long Dim wkTotal As Double RowCnt = 0 wkTotal = 0 Do If rng.Offset(RowCnt, 0).Value = "" Then Exit Do If rng.Offset(RowCnt, 0).HasFormula = True Then Exit Do wkTotal = wkTotal + rng.Offset(RowCnt, 0).Value RowCnt = RowCnt + 1 Loop spTotal = wkTotal End Function
お礼
細かく記述して頂き、ありがとうございます。 後で、試させて頂きます。
- Thiotimoline
- ベストアンサー率60% (3/5)
>合計行が明細の最後ではなく、明細の先頭にある場合 ということは、私の回答は試されてないのですね。 応用すれば、 =SUM(OFFSET(C1,1,0):C10) でＯＫなことはすぐわかると思います。
補足
説明不足で申し訳ありません。 データは、Ａ合計行+Ａ明細+Ｂ合計行+Ｂ明細・・・の繰り返しの為 一括で集計することができません。Ａ、Ｂ・・・それぞれ集計する必要があります。 ↓ Ｂ合計行の直前にＡの明細を追加した時に 「=SUM(OFFSET(C1,1,0):C10)」の「C10」が自動的に「C11」に変わってほしいのですが。 Ｂ合計行の直前ではなく、Ａの明細の途中に追加した時はC11に変わります。 何度もすみません。
- msMike
- ベストアンサー率20% (363/1775)
C1: =SUM(C2:C1000)
お礼
ご回答ありがとうございます。
補足
説明不足で申し訳ありません。 データは、Ａ合計行+Ａ明細+Ｂ合計行+Ｂ明細・・・の繰り返しの為 一括で集計することができません。Ａ、Ｂ・・・それぞれ集計する必要があります。
- meitoku
- ベストアンサー率22% (2258/10048)
C1にSUM(C1:C10)をコピーして SUM(C2:C11)と書き換えれば良いだけです。 Ｃ11）の後ろにカソールをあてれば計算範囲が現れます。 ＳＵＭ（）の数値は削除・書き換えができます。
関連するQ&A
- EXCEL行挿入・行削除しても正しく集計する方法
例えばSUM関数で、1～10行目までが明細行で、11行目に合計行がある場合、明細の途中で1行挿入しても、自動的に集計範囲が変わってくれますが、最終明細行の下に行挿入した場合、集計範囲が変わってくれません。 (1)SUM(C1:C10) → 明細途中に1行挿入 → SUM(C1:C11) (2)SUM(C1:C10) → 明細最後に1行挿入 → SUM(C1:C10) (2)の場合でも、全明細行集計するようにする方法（関数）があれば教えてください。（Office365使用） よろしくお願いします。
- ベストアンサー
- Excel（エクセル）
- 行を追加した時の、値の合計範囲
行を追加するマクロがあり、実行すると「合計」行の1行上に行が追加されます。 行が追加された分「合計」のある行が下がっていくんですが、その関数が変動してくれなくて困っています。 Ａ Ｂ Ｃ Ｄ Ｅ Ｆ Ｇ Ｈ Ｉ 1 商品の基本データ ・ ・ ・ ・ 14 商品の基本データ 15 16 日付 入庫 出庫 本日在庫 17 2012/04/01 100 100 18 2012/04/05 50 150 19 2012/05/03 100 50 20 2012/05/04 300 350 21 ・ 150 200 22 ・ 50 150 23 ・ 200 350 24 ・ 10 340 (ここに行が挿入されていく) 25 合計 (1) (2) 340(=(1)-(2)) (1)=IF(COUNT($C$17:$C24)=0,"",SUM($C$17:$C24)) (2)=IF(COUNT($D$17:$D24)=0,"",SUM($D$17:$D24)) 現在こんな感じで式を入れています。$C24と$D24が$C25...、$D25...等動いていってくれるといいんですが・・。 他にもSUMPRODUCTやOFFSETなど色々試しましたが、上手くいきません。 行が増えていく(セル参照範囲が変わってくれない)、C:CやD:Dとして設定できない、データが連続していない、などが原因だと思っています。 A列には何もありません。 E～H列は他にもデータが入っています。 マクロで、B17からHの合計の1行上までを日付でソートすることがあります。 合計行の上に空白行C25を入れ、合計行C26にSUM(C17:C25)とし、 挿入する位置を空白行C25の上とすれば、SUMは挿入行を抱えていってくれるんですが、 この場合空白の行25を高さ0の不可視にさせなければならず、利用する側としては21・22・23・24・26と、25が飛んでいるのが気になるものと思います。 できればそういう疑問を持たせることなく可動させたいです。 因みにVBAでWorksheet_ChangeやWorksheet_Selectionhangeなどは既に使用しているので、 マクロで自動で合計値を入力することはできないと思います。 ボタンで動かすマクロでなら出来そうですが、合計を逐一表示するのにマクロボタンを設置するのもどうかと思うので・・。 いくらネットで探しても方法が見つからなかったので、何方かご教授宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- エクセル 行を挿入しても正しい合計値を出す方法。
添付のようなエクセルの表を作成して管理台帳としています。 （実データは載せられないため、簡易版にしています。） 今、B9セルには(=SUM(B3:B8)が入っています。 8行目と9行目の間に行を挿入しても、合計値がきちんと(=SUM(B3:B9)の値を持ってくるようにしたいです。 過去の質問 http://okwave.jp/qa/q4644868.html 参考にして、『あ』ではできたのですが、『い』、『う』、『え』、『お』でも同様に合計値を出したいのです。 『あ』で行を挿入すると、『い』、『う』、『え』、『お』の各項目の行がずれて、 さらに『い』、『う』、『え』、『お』でも行の挿入を行います。 （挿入する行は、『あ』、『い』、『う』、『え』、『お』すべて合計値の前の行に挿入します。） きちんと、『あ』、『い』、『う』、『え』、『お』の各合計値を出す方法はありますでしょうか。 私が使っているのはエクセル2007ですが、エクセル2000を使っている人に渡すデータです。
- ベストアンサー
- オフィス系ソフト
- エクセル２行ごとに空白を挿入し２行の項目を挿入する
エクセルに以下のような一覧表が何百件あるとします。 氏名 社会保険料 雇用保険料 源泉徴収税 控除額計・・・・←項目はセル結合で２行です 田中 6,742 357 1,214 8,313・・・・・←関数合計が入ってます 小林 5,821 321 1.101 7,243・・・・・ 佐藤 5,943 228 1,581 7,752・・・・・ ・ ・ ・ ・ ・ それぞれの人に明細を切り取って渡す場合、田中さん以外は項目がないので、小林さん以下の人にも項目が分かるようにしたいのですが簡単に項目を挿入出来る方法がありましたら教えてください。 ２行ごとに空白を挿入（タイトル（項目）が２行なので）する方法は分かったのですが、 その空白すべてにタイトル（項目）２行を一気にはりつけたいです。 ジャンプ機能で空白を一気に選択し、２行の空白を結合、項目をコピー貼り付ける方法ではできました。 ただマクロを使ったり、関数を使ったり、誰が使用しても簡単にできる方法がありましたら助かります 。
- ベストアンサー
- Windows XP
- EXCELマクロを使い、空白行ではさまれた範囲の数字を合計
下記のようなデータがあります。 条件 並び方：コード順 行数：変動する 見出し行の下とコードが変わる毎に空白行が1行あります。 マクロで空白行にコード毎の計と最後に合計を入れる方法を教えてください。 元データ コード 金額 1 10 1 20 1 50 2 40 2 20 2 30 3 50 3 10 マクロ実行結果 コード 金額 1 10 1 20 1 50 計 80 2 40 2 20 2 30 計 90 3 50 3 10 計 60 合計 230
- ベストアンサー
- オフィス系ソフト
- エクセルで行を挿入し、小計、合計を出したい
質問いたします。 Ａ列に支店コード（４桁の数値）、Ｊ列に金額、Ｋ列に手数料があります。 支店は５箇所でデータは１支店あたり１００～５００行ほどあります。全支店のデータが連続しています。 １．支店コードの最終行の下に１行挿入し、Ｊ列，Ｋ列の小計を計算する。 ２．最後の支店の小計の下に一行あけてＪ列，Ｋ列の合計をしたい。 どのようにしたら良いか教えてください。
- ベストアンサー
- Visual Basic
- 特定範囲の合計を求める方法
OFFICE2010 Ａ列に題目の文字列(文字列の長さは不定） Ｂ列に価格としての数値データ が入ったリストがあります（5000行くらい） それぞれの題目の先頭には空白行が３桁入ったものと、6桁入った題目が存在している。 題目先頭空白3桁のものは、題目先頭空白6桁のもので構成されているイメージ。 このリストで、それぞれの題目先頭空白3桁のものの金額を算出したい。 合計金額の表示はＣ列で、題目先頭空白3桁と同行に配置したい。 下記の様なリストがあったとして 題目 価格 A001 b001 100 c001 50 B001 d002 300 e004 500 J009 1000 下記の様に合計を表示したい 題目 価格 合計 A001 150 b001 100 c001 50 B001 1800 d002 300 e004 500 J009 1000 対応方法は、関数でもマクロでもどちらでも可です。 よろしくお願いします
- ベストアンサー
- オフィス系ソフト
- エクセルで行を挿入して小計合計を出したい
質問ですが，以下の参考としたマクロについて，データが１支店１件しかない場合は行を挿入せずにこのままとしたい場合はどのように修正すれば良いか教えていただけませんでしょうか。 1支店２件以上のデータがある場合は，以下の参考としたマクロのとおり行を挿入して小計を計算表示する。 支店コード1001 20000円 200円 ← 行挿入不要 小計無し 1002 30000円 200円 1002 45000円 300円 小計 75000円 500円 参考にした質問・アドレス Ａ列に支店コード（４桁の数値）、Ｊ列に金額、Ｋ列に手数料があります。 支店は５箇所でデータは１支店あたり１００～５００行ほどあります。全支店のデータが連続しています。 １．支店コードの最終行の下に１行挿入し、Ｊ列，Ｋ列の小計を計算する。 ２．最後の支店の小計の下に一行あけてＪ列，Ｋ列の合計をしたい。 Sub test01() d = Range("a2").CurrentRegion.Rows.Count ' MsgBox d Cells(d + 1, 1) = "END" Dim st1, gt1, st2, gt2 As Long st1 = 0: gt1 = 0: st2 = 0: gt2 = 0 mk = Cells(2, 1) '========== For i = 2 To 10000 If Cells(i, 1) = "END" Then Exit For '最終行判定 If Cells(i, 1) = mk Then '前行とコード同じか '------今回行分加算 st1 = st1 + Cells(i, 2) st2 = st2 + Cells(i, 3) Else mk = Cells(i, 1) '--------小計 Cells(i, 1).EntireRow.Insert Cells(i, 1) = "小計" Cells(i, 2) = st1 gt1 = gt1 + st1 st1 = 0 Cells(i, 3) = st2 gt2 = gt2 + st2 st2 = 0 '-----今回行分加算 i = i + 1 st1 = st1 + Cells(i, 2) st2 = st2 + Cells(i, 3) End If Next i '============終了 '-------小計 Cells(i, 1) = "小計" MsgBox st1 Cells(i, 2) = st1: gt1 = gt1 + st1: st1 = 0 Cells(i, 3) = st2: gt2 = gt2 + st2: st2 = 0 '-------合計 Cells(i + 1, 1) = "合計" Cells(i + 1, 2) = gt1 Cells(i + 1, 3) = gt2 End Sub アドレス http://okwave.jp/qa/q414647.html
- ベストアンサー
- Excel（エクセル）
- 範囲内に合計を出す
毎日SUM関数で合計を出していますが、もっと簡単に出せないかと思い質問しました。H7からデーター(数字)が下に入っていきます。(H7.H8.H9.・・・・・)日によ って最後の行が違います。(シートは毎日違いますが最初に入る場所はH7からと決まっています。)昨日はH7～H20でした。今日はH7～H27ですと日によってばらばら です。計算のしかたですが、H20で終わった時は、合計ボタンを押したらH7～H20までをH1に合計を表示してH列の最終行の次も合計を表示する所まではなんとか 出来ましたが、L列から以降のセルにデーターがあっても最終行と判断しない様にするにはどうすればいいですか。（A列～K列で最終行がK25の時M列のM30にデーターがある時はH31が最終行の次と表示しますが本当はH26と表示してほしいです）アプリケーションはExcel2000です。よろしくお願いします。 Private Sub CommandButton1_Click() Dim maxRow As Long maxRow = Application.Max(Range("H65536").End(xlUp).Row, 5) Range("H1") = Application.Sum(Range("H7:H" & maxRow)) Range("A1:K2").Copy With ActiveSheet.UsedRange .Cells(.Count + 1).Select End With ActiveSheet.Paste Application.CutCopyMode = False With ActiveSheet.UsedRange .Cells(.Count + 1).Select End With End Sub
- ベストアンサー
- Visual Basic
- 範囲内にある数量の合計
どなたかご教授下さい。 下の表があるとします。 A B C D E F G 1 重量 40.0 40.1 40.2 40.3 40.4 40.5 2 検体数 1 5 3 4 1 2 3 重量 40.6 40.7 40.8 40.9 41.0 41.1 4 検体数 3 7 8 6 4 9 5 6 40.0～40.2 40.3～40.5 40.6～40.8 40.9～41.1 7 9 7 18 19 6行目に範囲を設定します。 7行目に6行目で設定した範囲で上の重量範囲内にあてはまる場合 検体数を合計したいのです。 例は6行目・7行目に表しています。 ”～”は例えで使用していますので実際の表は空白です。 宜しくお願いします。
- ベストアンサー
- オフィス系ソフト
お礼
分かりました。 試してみましたが、うまくいきました。 本当にありがとうございました。