エクセル 列・行の複数条件でブック内の合計

質問

Excel 2007です。
以下のようなシートが複数あるブックがあります。
複数のシートの並び（１STやBARTERなどの項目）はまったく同じです。

A　　　　B　　　　C　　　　　　D　　　　　　E　　　　　　　　 J　　　K　　　　　　L　　　　M
ID　　　ST　　　BARTER　　ST　　　　BARTER ・・・・ ST　　BARTER　　ST　　　BARTER・・・・
A01　　350　　　50　　　　　　350　　　　50　　　　 ・・・・ 30　　200　　　　　30　　　200　　　
A02　　250　　　　　　　　　　　250　　　　　　　　　 ・・・・ 250　　　　　　　　250
：
D10　　100　　　250　　　　　　　　　　　　　　　　　　・・・・ 100　　　　　　　　100　　　　
：　　　　　　　　　300　　　　　　　　　　　300　　　　　・・・・　　　　300　　　　　　　　　300
C35　　50　　　　500　　　　　50　　　　500
：
A01　　30　　　　　　　　　　　30　　　　　　　　　　　　・・・・ 30　　　　　　　　　30　　　
：
項目のIDには口座番号、１STやBARTERには金額が入っています。
実際は１STやBARTER以外の項目が２，３種類あります。
そのため条件としてIDと項目名で合計を出したいと思っています。
IDの並び順はまちまち、金額も入っていたり無かったり(空白)します。
金額の代わりに文字が入っているセルもあるので、そこはエラーが出ず無視できれば幸いです。
IDが1シート内に重複していたり、複数シートに載っている場合も有ります。

そこから、
IDごとに１STとBARTERの複数シートの合計額を出し別シートに並べたいと思っています。
また、例えばB～E列にある１ST、BARTERの合計と、J～M列の１STとBARTERの合計なども、列の範囲指定で出したいと思っています。

例えば以下のようにです。

ID　　　B～E合計　　 　　　　　　J～M合計　・・・・・・・　　　X～AA合計・・・・　総合計
　　　 　ST　　　　BARTER　　　ST 　　BARTER　　　　　ST　　BARTER　　　１ST　　BARTER
A01　　760　　　　　100　　　　　120　　　400　　　　　　　　　　　　　　　　　　　　880　　　500　
A02　　500　　　　　　　　　　　　　　　　　　　　250
：
：
D10　　100　　　　　250　　　　　　　200
：
可能でしょうか？
宜しくお願い致します。

回答No.5

keithin

Sheet1：
１行目のＢ～Ｅ列には「ST1」および「BARTER1」が何回か出てくる
１行目のＪ～Ｍ列には「ST2」および「BARTER2」が何回か出てくる
１行目のＸ～ＡＡ列には「ST3」および「BARTER3」が何回か出てくる
Ａ列Ａ２以下にはＩＤが何回か繰り返し出てくる。仮に２０行まで。

Sheet2：
Ａ２以下にはＩＤを「繰り返しなく」記入して準備が整えてある
Ｂ１にST1
Ｃ１にBARTER1
Ｄ１にST2
Ｅ１にBARTER2
Ｆ１にST3
Ｇ１にBARTER3
と記入済みである

手順：
Ｂ２に
=SUMPRODUCT((Sheet1!$A$2:$A$20=$A2)*(Sheet1!$B$1:$AA$1=B$1)*Sheet1!$B$2:$AA$20)
右にコピー、下にコピー。

＃補足
統合を使えばメンドクサイ数式とか何も考える必要がありませんよ、とどれだけお話ししても、やっぱり「やっぱり関数を誰かに考えてもらってそれをコピーするだけで使いたい」関数大好きさんばっかりです。
結局こうして二度手間三度手間になっちゃうんですから、統合とかお話しするのもやめた方がお互いのためでしたね。ごめんなさい。

お礼

こちらにお礼を投稿するべきでしたね。
有難うございました。

補足

誤解があって申し訳ないです。
なぜ関数が好ましいかというと、抽出したデータは他の作業の一環で使われることと、複数の人間が閲覧するデータで数値の変動が常にあるため、常に自動的にアップデートされる方法が良かったわけです。
各人がそれぞれ統合をした時の数値で結果がバラバラだと困ります。

しかしどうやら他に方法が無いようですし、統合のお陰で可能になったのでとても感謝しています。
有難うございました。

回答No.4

keithin

＞離れたデータを統合することはできますか？

出来ないので、それぞれのグループの左端にＩＤ列を追記するようにお話ししました。

敢えて言うと、Ｂ～Ｅ列のＳＴはＳＴ１と記入、Ｊ～Ｍ列のＳＴはＳＴ２と記入、Ｘ～ＡＡ列のＳＴはＳＴ３と記入してすべてを一括で統合する事でも、目的は達成できます。

補足

一歩先まで考えたご返答だったんですね、失礼いたしました。
それから、グループごとに合計を出すことも、項目を変えることで可能な件理解いたしました。
有難うございました！

数値の変動が常に反映される関数式がない場合は、統合で可能であることが分かって良かったです。

回答No.3

masa830

ご要望としては、同一フォーマットのExcelシートの集計を行いたいとの事で良いでしょうか？

やり方は色々あると思いますが、基本的な手順としては次の様に行うのが良いと思います。

手順1) 集計対象のシートを適当なシート（中間データ）にマージする
手順2) （手順1）で作成したシートを集計して、最終的に欲しい結果を作成する

手順1についてはVBAマクロを利用すると比較的容易に実現出来るかと思います。
→末尾にサンプルコードをご案内しますので、ご参考にして頂ければと思います。

手順2についてはExcel関数やピボットテーブル、またはAccessにインポートして集計すれば対応可能と思われます。

なお、文字を含むデータの集計をした時にエラーになる事を懸念されているようですが、Excel関数（Sum, SumIf）やピボットテーブルを利用する場合は、そもそも文字は無視される為、あまり心配する必要は無いと思います。

ただし、数式のエラーが混ざっている場合はその限りではないのでご注意下さい。

ピボットテーブルの使い方については以下のサイトなどに詳しく説明があるので、ご参考にして頂ければと思います。
<http://www.officepro.jp/exceltips/pivot/>

◆サンプルコード
'前提条件:
' タイトル行は1行目かつデータは2行目から始まるものとする
' ID列はA列とし、途中に空白列は無いものとする
Public Sub MergeSheets()
'データ収集用シートの名称
Const TMPSHEET As String = "中間データ"

'変数の定義（収集用ワークシート）
Dim tmpWorksheet As Worksheet

'変数の定義（ワークシート）
Dim w As Worksheet

'変数の定義（カーソル）
Dim c As Range, d As Range

'変数の定義（収集対象ワークシートの末端行）
Dim endRow As Integer

'一時的に警告メッセージを停止する
Application.DisplayAlerts = False

'データ収集用シートを取得する
On Error Resume Next
Set tmpWorksheet = Sheets(TMPSHEET)
On Error GoTo 0

On Error GoTo ErrorHandler
If Not tmpWorksheet Is Nothing Then
'データ収集用シートを初期化する
tmpWorksheet.Cells.Clear
Else
'データ収集用シートを作成する
Set tmpWorksheet = Sheets.Add(Before:=ThisWorkbook.Sheets(1))
tmpWorksheet.Name = TMPSHEET
End If

'起点を取得する
Set c = tmpWorksheet.Range("A1")
tmpWorksheet.Select
c.Select

'データの収集
For Each w In Sheets
If (w.Name <> TMPSHEET) Then 'データ収集対象とならないワークシートは処理対象としない
If (c.Row = 1) Then
'タイトル行を生成
w.Range("1:1").Copy
tmpWorksheet.Paste

'カーソルを進める
Set c = c.Offset(1)
c.Select
End If

'起点を取得する
Set d = w.Range("A1")

If Not IsEmpty(d.Offset(1)) Then
'収集対象ワークシートの末端行を取得
endRow = d.End(xlDown).Row

'データのコピー&ペースト
w.Range("2:" & endRow).Copy
tmpWorksheet.Paste

'カーソルを進める
Set c = c.End(xlDown).Offset(1)
c.Select
End If
End If
Next
GoTo Finally
ErrorHandler: 'エラー処理
'エラーメッセージを表示する
MsgBox Err.Description

Finally: '終了処理
'コピー操作を取り消す
Application.CutCopyMode = False

'警告メッセージの停止を解除する
Application.DisplayAlerts = True
End Sub

補足

有難うございます。
ブックには他にも内容の違うシートがあります。
マージシートのVBAをシートと範囲指定で行うことは可能ですか？

たとえば、あ・い・う・え・お・か・き・く・け・この10枚のシートがあり、その内上記内容のデータシートは「あ～お」の5枚のみ。
更に恐縮ですが、上記のデータはの項目は4行目で、1～3行にはにどうしても省けない別の項目名(カテゴリー)があり、A~B 列にも情報が入っているためIDはC列です。
そのため、もし可能であれば「あ～お」シート内それぞれの範囲指定(例えば　C5:CE列のRange("C65536").End(xlUp).Row行)までを別シートにマージできたら、大変助かります。

また、マージしたシートを並び替えた後、同じIDの行は1～複数行(最大でも10行以下)あるわけですが、それらをIDごとに、列指定の合計を出すことは可能でしょうか？
例えばA1セルのIDの、A,D,G,J,M・・・列の合計(ST項目列)、B,E,H,N,・・・列の合計(BARTER項目)など。
その場合同じIDは何度も集計されてしまいますが、後で重複するID行は削除する方法でかまいません。

データシートの金額は不定期に変更します。
なので、合計を見たいときにマクロをランする方法でもいいかと思いますが、元データのシートは常に維持している必要があります。
集計が出たら、その数値を使って同じエクセル内で別の作業をするため、全てエクセル内で行いたいです。

大変お手数をお掛けしますがよろしくお願いします。

回答No.2

Nouble

串刺し計算で…、 と私も思ったのですが
同じIDが多数行出てくると云うことですよね？
もしそうだとしたら
串刺し計算では容易ではないですね…　工夫が必要ですね
残念です。

関数式、又は集計機能、又はVBAになるでしょうか？

関数式作成には少し時間が掛かります。
当てにせずお待ち頂ければ幸いです。

補足

空白だったところに数値が入ったり、入っていた数値が変わったりするため、関数だと随時最新のデータが見えるのでありがたいです。
よろしくお願いいたします。

回答No.1

keithin

集計を表示したいまっさらなシートを１枚開く
データタブの統合を開始する
データのＡ：Ｅ列を追加、必要なら他のシートのＡ：Ｅ列も追加
左端列、上端行にチェックを入れてＯＫする
以上で出来ます。
メンドクサイ数式とか考えたり工夫にアタマをひねる必要は全くありません。

＃ただし
「ST」とか「1ST」とかイミフメイの記号が混在しているので、ちゃんと合計したいなら全てSTならSTに統一しなければいけません。

＃参考
ＳＴとＢＡＲＴＥＲだけが実際には欲しいというお話の時は
集計シートのＡ１を空に、Ｂ１にST、C1にBARTERと記入
Ａ１：Ｃ１を選択した状態から統合の作業を行います

Ｊ：Ｍ列について同様に行いたいときは、Ｉ列にＩＤを再掲し、Ｉ：Ｍ列を対象に同様に行います。
Ｘ：ＡＡ列について同様に行いたいときは、Ｙ列にＩＤを再掲し、Ｙ：ＡＡ列を対象に同様に行います。

補足

STと１STが混在していました、失礼いたしました。
全てのデータの統合はスッキリできました、有難うございます。

追加質問ですが、元データのシートを変更せずに(列挿入をしない)、離れたデータを統合することはできますか？
たとえば集計シートで、データシートのA列のIDと、J:Mの数値の統合をする場合です。
よろしくお願い致します。