[[20121018115916]]　『複数のシートにある同じ対象物の合計を出したい』（lvrpl1127）　ページの最後に飛ぶ

『複数のシートにある同じ対象物の合計を出したい』（lvrpl1127）

Excel初心者です。タイトルから訳分からなくてすいません。

説明下手なので、例をあげて質問します。

シート１の内容
　　Ａ　　　　　Ｂ
商品コード　　　金額　
123456　　　　 5,000
456000　　　　10,000
246810　　　　15,000

シート２の内容
　　Ａ　　　　　Ｂ
商品コード　　　金額　
123456　　　　 7,000
456000　　　　10,000
789111　　　　30,000

シート３の内容
　　Ａ　　　　　Ｂ
商品コード　　　金額　
123456　　　　 8,000
789111　　　　30,000

上記の３つのシートがあります。

各シートには、商品コードとその金額が入力されています。
例えば「123456」は３つのシート全てに入力されています。
一方「456000」はシート１，２に入力されています。
１つのシートにしか入力されていないものもあります。

各シートの商品コードをキーにして、合計と内訳を以下のように
別シートに算出したいです。

シート（合計）
　　Ａ　　　　　Ｂ　　　　内訳
商品コード　　　金額　　　シート１　　シート２　　シート３　
123456　　　　20,000　　　5,000　　　　7,000　　　8,000
456000　　　　20,000　　10,000　　　　10,000　　　　　0
789111　　　　60,000　　　　　0　　　 30,000　　　30,000
246810　　　　15,000　　15,000　　　　　　 0　　　　　0

環境はExcel2003、WindowsXPです。

乱文ですが、よろしくお願いいたします。

 SUMIF関数が使えると思いますので
 調べてみて下さい。

 cf:(e3h) SUMIFとSUMPRODUCT
http://www.excel.studio-kazu.jp/lib/e3h/e3h.html

 (HANA)

ｼｰﾄ1から3までA列にコード、B列に金額があったとして、1行目から500行目までデータがあったと仮定した場合
=sumif($A$1:$A$500,商品コード,$B$1:$B$500)+sumif($A$1:$A$500,商品コード,$B$1:$B$500)+sumif($A$1:$A$500,商品コード,$B$1:$B$500)
で出ると思います。（2つ目3つ目のsumifのセル参照はｼｰﾄ2,3のを参照しておくこと）

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　(mayou)

HANA様

回答ありがとうございます。

リンクを参照してみましたが・・・・・

商品コード毎にSUMIFを使って合計を出すという意味でしょうか？

それでしたら、私の伝え方が間違っているかと思われます。

まず、上記の１～３のシートはブックは同じですが、
それぞれ別々のシートであり、新しい別のシートに合計を出したいです。

更に商品コードは複数のシートに同じコードがある場合もあれば、
１つのしーとにしか無い場合もあります。

極端な例を１つあげますが。。。

シート１の内容
　　Ａ　　　　　Ｂ
商品コード　　　金額　
120000　　　　 5,000
130000　　　　10,000
140000　　　　15,000

シート２の内容
　　Ａ　　　　　Ｂ
商品コード　　　金額　
150000　　　　 7,000
160000　　　　10,000
170000　　　　30,000

シート３の内容
　　Ａ　　　　　Ｂ
商品コード　　　金額　
180000　　　　 8,000
190000　　　　30,000

と、どのシートにも重複したコードが存在していない場合でも
新しい別のシートに以下のように表示させたいです。

シート（合計）
　　Ａ　　　　　Ｂ　　　　内訳
商品コード　　　金額　　　シート１　　シート２　　シート３　
120000　　　　 5,000　　　5,000 　　　　　0　　　　　　　0
130000　　　　10,000 　　10,000 　　　　　0　　　　　　　0
140000　　　　15,000 　　15,000 　　　　　0　　　　　　　0
150000　　　　 7,000　　　　　0　　　　7,000　　　　　　　0
160000　　　　10,000 　　　　0　　　　10,000　　　　　　　0
170000　　　　30,000　　　　　0　　　　30,000　　　　　　0
180000　　　　 8,000　　　　　0　　　　　　0　　　　　8,000
190000　　　　30,000　　　　　0　　　　　　0　　　　 30,000

上手く伝わるでしょうか・・・・

よろしくお願いいたします。

（lvrpl1127）

そのパターンであれば合計のｼｰﾄの　ｼｰﾄ1の列に
=SUMIF(Sheet1!$A$1:$A$500,A1,Sheet1!$B$1:$B$500)
同じくｼｰﾄ2の列に先の式のSheet1!の部分をSheet2!に変える。　ｼｰﾄ3も同じ。

あとはB列にsumで取ればいいのでは。　(mayou)

 順番としては逆になる気もしますが、まず新規シートに統合機能で全商品コードと合計を出す。

 シート（合計）のA列とB列がこれで埋まります。

 次に、C2に、

 =IF(COUNTIF(シート1!$A:$A,$A2)=0,0,VLOOKUP($A2,シート1!$A:$B,2,FALSE))

 これを、D、E列にそれぞれシート名を変えて入力。

 とかでは如何でしょうか？

 （コナミ）

mayou様

回答ありがとうございます。

１ヶ月毎での使用をイメージしています。
（年間の合計は必要ないのですが・・・）

毎月商品コードが変わるので、その方法だとSUMIFの中身の
商品コードを毎月変えなければならないような気がします。
違っていたらすいません。

上記のHANA様へのコメントも参考にしていただきたいのですが
イメージとしては、
シート１～３から、商品コードを（重複しないように）抜出して
抜出した商品コードに対して金額を表示する
（商品コードが重複しているものは足し算をする）

といういめーじでしょうか・・・・

伝わりにくいですかね・・・

（lvrpl1127）

 シート（合計） のA列の「商品コード」は、
 事前に決まっているの（入力済み）かと思いましたが
 シート１～３にあるものを自動的に生成する必要があるのですか？

 でしたら、コナミさんが書いておられる統合機能を
 検討してみられてはどうでしょう。

 (HANA)

こんにちは

Sheet1～Sheet3 をCtrlキーを押しながら選択して、
セルC1 に「シート名」とかの項目名を入力
セルC2 に「=IF(B2<>"",RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))),"")」と入力
セルC2をC3以下適当なセルまでフィルコピー

と準備しておいて、複数のワークシートをピボットテーブルで集計すれば出来ますよ。
行項目が商品コードで、列項目がシート名として。

（ウッシ）

皆様ありがとうございます。

こちらの対応が追いつかず申し訳ございません。

今統合機能を使ってみているのですが、
商品コード毎の抜出が上手くできません。

統合の「集計の方法」は何を選択すればいいのでしょうか？

 集計の方法は、「合計」でいいですよ。
 統合元範囲は各シートのAB列を選択して追加を押したら自動的に下の統合元に行きます。
 全部のシートのAB列を指定したら統合の基準の上端行と左端列にチェックを入れてOKで
 どうでしょうか？

 （コナミ）

 このページとか、どうでしょう？
http://www.excel7.com/chotto19.htm
 　データの統合機能を使う - Excel生産性向上委員会

 (HANA)

皆様

何度もコメントありがとうございます。

統合のテストはできたのですが。。。
リンクの意味も理解できたのですが。。。。

統合を使うには１つ問題がありました。

月ごとの集計に使うことを考えているのですが
月が変わると同じ商品コードは使わないのです。

例えばHANA様がリンクしていただいた例は「テレビ」とか「ラジオ」
とかなので繰り返し使えるのですが

商品コードというより発注コードといったほうがいいでしょうか・・・

１つの商品に対して固有のコードを持たせるためコードを繰り返し使う
ということがないのです。

何度もお手を煩わせて申し訳ございません。
やはり、マクロになるのでしょうか・・・・・

 同じコードを使わないのでしたら、同じ商品だというのは何をもって判断するのでしょうか？
 それをキーにする事はできませんか？
 できましたら、完全に同じでなくて構いませんができるだけ実際に近いデータを提示して
 いただけるといいのですが。

 （コナミ）

こんにちは

シート名は、Sheet1～3 として、提示されたデータをセットして試して下さい。

Sub test()

    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    Sheets("Sheet1").Activate
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "シート名"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]<>"""",RIGHT(CELL(""filename""," & _
        "R[-1]C[-2]),LEN(CELL(""filename"",R[-1]C[-2]))-" & _
        "FIND(""]"",CELL(""filename"",R[-1]C[-2]))),"""")"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C1000"), Type:=xlFillDefault
    Sheets("Sheet1").Select
    With ActiveWorkbook.PivotCaches.Add( _
            SourceType:=xlConsolidation, _
            SourceData:=Array( _
                Array("Sheet1!C1:C3", "Sheet1"), _
                Array("Sheet2!C1:C3", "Sheet2"), _
                Array("Sheet3!C1:C3", "Sheet3")))

            With .CreatePivotTable(TableDestination:="", TableName _
                    :="ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1", DefaultVersion:=xlPivotTableVersion10)

                .Parent.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
                .DataPivotField.PivotItems("データの個数 / 値").Position = 1
                .AddFields RowFields:="行", ColumnFields:="ﾍﾟｰｼﾞ1"
                .PivotFields("データの個数 / 値").Function = xlSum
                .PivotFields("ﾍﾟｰｼﾞ1").Caption = "Sheet名"
                .PivotFields("行").Caption = "商品コード"
                .PivotFields("商品コード").PivotItems("(空白)").Visible = False
                .PivotFields("合計 / 値").NumberFormat = "#,##0_ "
            End With
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
    Application.CommandBars("PivotTable").Visible = False
End Sub

（ウッシ）

コナミ様

結論から言うと、同じ商品だという区別は必要ありません。
発注コードというか・・・・

別々のお客さん（Ａ男、Ｂ男）が全く同じ商品（ＡとＡ’）を頼んだとして
「’」は頼んだ商品の色やオプションが違うとして・・・・・
届ける商品を間違えないように発注時や物流時に識別するコードをつけると思いますが
そのコードだと思っていただければイメージがつきやすいかなと・・・・

発注コードという言葉だけだと分かりにくいので例として
日産の車名を例に説明します。

発注コード　　車名　　　オプション
01000　　　　リーフ　　　ナビ付　ＥＴＣ付　　　
02000　　　　リーフ
03000　　　　リーフ　　　ＥＴＣ付
04000　　　　リーフ
05000　　　　セレナHV
06000　　　　セレナHV　　　ナビ付
07000　　　　セレナHV
08000　　　　セレナHV　　ＥＴＣ付
09000　　　　ノート　　　ＥＴＣ付
01000　　　　ノート　　　ナビ付

という発注があったとします。
そして各キャンペーンを実施していて
対象車には以下のように現金をプレゼントするとします。

電気自動車　　　　90,000円
ハイブリッド車　　50,000円
ナビ付車　　　　　30,000円
ＥＴＣ付車　　　　10,000円

これを発注コード順に分けると・・・

発注コード　　現金　　　
01000　　　　90,000＋30,000＋10,000＝130,000　　　
02000　　　　90,000
03000　　　　90,000＋10,000＝100,000
04000　　　　90,000
05000　　　　50,000
06000　　　　50,000＋30,000＝80,000
07000　　　　50,000
08000　　　　50,000＋10,000＝60,000
09000　　　　10,000
01000　　　　30,000

となります。
一人で集計すれば何てこと無いのですが・・・・・

ここからは強引なルールですが、各キャンペーン毎に担当者が違う為
お金の出所が違ってしまいます。（とします。）
各担当者から発注番号をベースにいくら現金をあげるかのデータをもらいます。
もちろんＥｘｃｅｌｌでデータをもらいます。
入力フォーマットは共通のものを使うとします。
（列や行の表記は省略してしまいます）

電気自動車

　発注コード　　　現金　　　
　　01000　　　　90,000
　　02000　　　　90,000
　　03000　　　　90,000
　　04000　　　　90,000

ハイブリッド車

　発注コード　　　現金　　
　　05000　　　　50,000
　　06000　　　　50,000
　　07000　　　　50,000
　　08000　　　　50,000

ナビ付車

　発注コード　　　現金
　　01000　　　　30,000　　　
　　06000　　　　30,000
　　01000　　　　30,000

ＥＴＣ付車

　発注コード　　　現金
　　01000　　　　10,000　　　
　　03000　　　　10,000
　　08000　　　　10,000
　　09000　　　　10,000

使用のイメージとしては、「電気自動車」「ハイブリッド車」「ナビ付」「ＲＴＣ付」
とあらかじめシートを作成しておき、各担当者からもらったデータを所定の場所にコピペ
して、それを集計したいです。

集計結果は以下のようになるようにしたいです。

　　　　　　　　　　　　　　　　　　　内　　　　　　　　　訳
発注コード　　現金　　　　電　気　　ハイブリッド　　ナビ付　　　ＥＴＣ付　　　
01000　　　　130,000　　90,000　　　　　　　　　　30,000 　　　10,000　　　
02000　　　　90,000　　 90,000　　　　　　　　　　　　
03000　　　　100,000　　90,000　　　　　　　　　　　　　10,000　　　
04000　　　　90,000 90,000
05000　　　　50,000 50,000
06000　　　　80,000　　　　　　　　　50,000　　　　30,000
07000　　　　50,000　　　　　　　　　50,000
08000　　　　60,000　　　　　　　　　50,000　　　　　　　　　　10,000
09000　　　　10,000　　　　　　　　　　　　　　　　　　　　　　 10,000
01000　　　　30,000　　　　　　　　　　　　　　　　30,000　

というように現金（合計）と内訳が分かるように集計したいのです。　

使用サイクルは一ヶ月毎です。
翌月になると「01100、01200、01300」というように発注コードは変わりますので
同じコードを使うことはないです。
（ですから各担当者からもらうデータはファイルを読み込むなどではなく
　コピペでもいいかなと思っています。）

以上が説明ですが、イメージしていただけますでしょうか？

ネット環境から離れるため返信は明日の午前中からになると思いますが
何卒よろしくお願いします。

（lvrpl1127）

ウッシ様

ありがとうございます。

色々試してみたのですが、実行までたどり着けません。

どのようにしたら実行できますか？

（lvrpl1127）

こんばんは

どう試したのですか？

マクロを使った事がないっていう事でしたら、テストデータをセットしたExcelブックを開いて
Altキー + F11キーを押して VBE画面を開いて、メニューの「挿入」で標準モジュールを開いて
画面右側のエディタ画面に上記コードを貼り付けて「実行」すればピボットテーブルが出来あがります。

最初にコメントした手作業で行う処理をマクロに記録して整理したコードなので、このマクロを
実際の環境に合わせて修正するというより、手作業でも出来るという事を確認して貰いたかった
だけです。

（ウッシ）

こんばんは

発注コードの方のテストデータで試すとしたら、

Sub test1()

    Sheets(Array("電気自動車", "ハイブリッド車", "ナビ付車", "ＥＴＣ付車")).Select
    Sheets("電気自動車").Activate
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "シート名"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]<>"""",RIGHT(CELL(""filename""," & _
        "R[-1]C[-2]),LEN(CELL(""filename"",R[-1]C[-2]))-" & _
        "FIND(""]"",CELL(""filename"",R[-1]C[-2]))),"""")"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C1000"), Type:=xlFillDefault
    Sheets("Sheet1").Select
    With ActiveWorkbook.PivotCaches.Add( _
            SourceType:=xlConsolidation, _
            SourceData:=Array( _
                Array("電気自動車!C1:C3", "電気自動車"), _
                Array("ハイブリッド車!C1:C3", "ハイブリッド車"), _
                Array("ナビ付車!C1:C3", "ナビ付車"), _
                Array("ＥＴＣ付車!C1:C3", "ＥＴＣ付車")))

            With .CreatePivotTable(TableDestination:="", TableName _
                    :="ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1", DefaultVersion:=xlPivotTableVersion10)

                .Parent.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
                .DataPivotField.PivotItems("データの個数 / 値").Position = 1
                .AddFields RowFields:="行", ColumnFields:="ﾍﾟｰｼﾞ1"
                .PivotFields("データの個数 / 値").Function = xlSum
                .PivotFields("ﾍﾟｰｼﾞ1").Caption = "Sheet名"
                .PivotFields("行").Caption = "発注コード"
                On Error Resume Next
                .PivotFields("発注コード").PivotItems("(空白)").Visible = False
                .PivotFields("発注コード").PivotItems("(blank)").Visible = False
                On Error GoTo 0
                .PivotFields("データの個数 / 値").NumberFormat = "#,##0_ "
                .PivotFields("データの個数 / 値").Caption = "オプション合計"
            End With
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
    Application.CommandBars("PivotTable").Visible = False
End Sub

（ウッシ）

コメント返信：

[ 一覧(最新更新順) ]

[[20121018115916]] 『複数のシートにある同じ対象物の合計を出したい』（lvrpl1127） ページの最後に飛ぶ

[[20121018115916]]　『複数のシートにある同じ対象物の合計を出したい』（lvrpl1127）　ページの最後に飛ぶ