Excel 複数シートで、違う行にある値をキー項目を元に集計したい。

質問

Excelを使用した集計作業で、効率の悪さに頭を悩ませています。
自己解決出来ず、お知恵を拝借出来ればと質問させていただきます。

シートＡ・Ｂに同じ構造(列の項目数が一致)のデータを持っています。
Ａ・Ｂ両方に入力されているキー項目のデータもあれば、
どちらかのみに登録されているキー項目のデータもあります。
２つのシートで一致するキー項目の値を集計し、
シートＣに出力する事を考えています。

Ａ・Ｂ両方に入力されている場合は各値を合計し、
片方にしか登録されていない場合はそのまま値を表示したいのですが、
効率の良い手順がわかりません。是非お助け下さい！

具体的には、下記のような事を考えています。
【シートＡ】
キー項目　値１　値２　値３　値４
Ａ００１　 10　 10　 10　 10
Ａ００２　 20　 20　 20　 20
Ａ００３　 30　 30　 30　 30
Ａ００４　 40　 40　 40　 40
Ａ００５　 50　 50　 50　 50
Ａ００６　 60　 60　 60　 60
【シートＢ】
キー項目　値１　値２　値３　値４
Ａ００１　 10　 10　 10　 10
Ａ００２　 20　 20　 20　 20
Ａ００５　 50　 50　 50　 50
Ａ００７　 70　 70　 70　 70
Ａ００８　 80　 80　 80　 80
↓
【シートＣ】
キー項目　値１　値２　値３　値４
Ａ００１　 20　 20　 20　 20
Ａ００２　 40　 40　 40　 40
Ａ００３　 30　 30　 30　 30
Ａ００４　 40　 40　 40　 40
Ａ００５　 100　 100　 100　 100
Ａ００６　 60　 60　 60　 60
Ａ００７　 70　 70　 70　 70
Ａ００８　 80　 80　 80　 80

p.s.
現在は下記のような手順で作業しています。
(1)MATCH関数でシートＢのキー項目がシートＡの何行目にあるかを出力。
(2)手作業で出力された行にデータを移動し、キー項目の行数を合わせる。
(3)シートＣにキー項目をコピーし、
　値部分は『='シートＡ'!B1+'シートＢ'!B1』のような形で計算。
(4)シートＢにのみ存在するデータを結合→キー項目でソート

・・・(2)の手順で、非常に時間を要しています(x_x)

回答No.6

_mya_mya · ベストアンサー

キー項目が予め決まっているのでしたら、隣あたりを作業用セルにして
キー項目順に並べた表を作り、シートＣでそれらを合計してはどうでしょう。

１）シートＡ、シートＢの使用していない場所（出来れば同じ場所）に
　キー項目を縦、横に値１、値２…　とした表を作る。

　　　A　　　　B　　C　　D …　　　　　G　　　　H　　I　…
１　キー項目　値１　値２ 値３　　　　キー項目　値１　値２
２　Ａ００１　 10　 10　 10　　　 　Ａ００１
３　Ａ００２　 20　 20　 20　　　 　Ａ００２

２）VLOOKUP関数を使いて、作業セルに値を表示（エラーは、ISERRORで０に）

　H2
　　=IF(ISERROR(VLOOKUP($G2,$A$2:$E$7,2,FALSE)),0,VLOOKUP($G2,$A$2:$E$7,2,FALSE))
　I2
　　=IF(ISERROR(VLOOKUP($G2,$A$2:$E$7,3,FALSE)),0,VLOOKUP($G2,$A$2:$E$7,3,FALSE))
　H3
　　=IF(ISERROR(VLOOKUP($G3,$A$2:$E$7,2,FALSE)),0,VLOOKUP($G3,$A$2:$E$7,2,FALSE))

３）シートＣで、シートＡとシートＢの作業用セルの値を合計し表示

お礼

アドバイスありがとうございました！
教えていただいた数式を参考に、作業させていただき上手く行きました！

(1)シートＣにシートＡのキー項目を並べ、値部分に数式を利用する事で、シートＢのデータを取得。
(2)シートＡとシートＣをシートＤで合計。
(3)MATCH関数を利用してシートＢにのみ存在するキー項目を抜き出し、シートＤに追加。

上級者の方から見るとまだまだ効率悪い作業だと思いますが、今までの作業に比べると、楽園のようです♪

回答No.5

noname#204879

初めて試してみた結果、[ピボットテーブル レポート]の機能が使えそうですよ。

１．[データ]→[ピボットテーブルとピボットグラフ レポート]を実行
２．“複数のワークシート範囲”および“ピボットテーブル”に目玉を
　　入れて[次へ]をクリック
３．“指定”に目玉を入れて[次へ]をクリック
４．[範囲]ボックス内にマウスカーソルを置いて、SheetA の範囲 A1:E7
　　をドラッグ指定（→ SheetA!$A$1:$E$7 と表示される）
５．[追加]をクリック（→ [範囲一覧]窓内に SheetA!$A$1:$E$7 が表示
　　される）
６．SheetB の見出しをクリック（→ [範囲]ボックス内に SheetB! が表
　　示）
７．SheetB の範囲 A1:E6 をドラッグ指定（→ SheetB!$A$1:$E$6 と表示
　　される）
８．[追加]をクリック（→ [範囲一覧]窓内に SheetA!$A$1:$E$7 および
　　SheetB!$A$1:$E$6 が表示）
９．[次へ]をクリック
10．“既存のワークシート”に目玉を入れて、SheetC のセル A3 をクリ
　　ックした後、[完了]をクリック

すると、次のような集計表が出来上がるはず。

合計 / 値　　列
行　　　　　 値１　値２　値３　値４　総計
Ａ００１　　　 20　　20　　20　　20　　80
Ａ００２　　　 40　　40　　40　　40　 160
Ａ００３　　　 30　　30　　30　　30　 120
Ａ００４　　　 40　　40　　40　　40　 160
Ａ００５　　　100　 100　 100　 100　 400
Ａ００６　　　 60　　60　　60　　60　 240
Ａ００７　　　 70　　70　　70　　70　 280
Ａ００８　　　 80　　80　　80　　80　 320
総計　　　　　440　 440　 440　 440　1760

お礼

ご回答、ありがとうございました。m(__)m
何度か見た事はあっても、自分で作った事の無かったピボットテーブル。
なるほどこうやって使うと便利なんですね。

知識不足の為、キー項目でも値でも無い項目をどう表示させるかがわからず、
今回は利用を断念してしまいましたが、勉強になりました。
丁寧に説明していただいたのに、消化しきれず申し訳ないです。。。(謝)

回答No.4

handomari

No.1&No.2のhandomariです。

先ほどの方法で統合する場合は、行や列に集計に関係のない項目があってもかまいませんが、数値データ以外はデータが空欄になります。

文字データについては、統合の後で、別にコピー＆貼り付けで対処するようになりそうです。

お礼

何度もアドバイスをいただきましてありがとうございました。
おかげさまで、作業効率がグンと良くなりそうです(^^)

今回は、No6のアドバイスを中心に問題を解決しましたが、
教えていただいた方法で統合してから、空白になった項目にVLOOKUP関数を使えば、
もっと効率が良いのかな、等とも考え勉強させていただきます。

回答No.3

nobu555

シートＣのセルA1から項目が並んでいるとして
セルB2に下記の数式を入力

=IF(ISNA(MATCH($A2,シートＡ!$A:$A,0)),0,OFFSET(シートＡ!$A$1,MATCH($A2,シートＡ!$A:$A,0)-1,MATCH(B$1,シートＡ!$1:$1,0)-1))+IF(ISNA(MATCH($A2,シートＢ!$A:$A,0)),0,OFFSET(シートＢ!$A$1,MATCH($A2,シートＢ!$A:$A,0)-1,MATCH(B$1,シートＢ!$1:$1,0)-1))

少々数式が長いですが、これで如何でしょうか。

補足

ご回答ありがとうございます！
多分正しく検証すれば値が表示される数式を教えていただいたのだと思うのですが、
知識不足の為、正常に動かす事が出来ません・・・orz

シートＣのＡ列には、シートＡのキー項目をコピーするのでしょうか？

多少自力で解決検討してみようと思いますが、
時間が掛かりそうなので、取り敢えず御礼まで(^^;)

回答No.2

handomari

No.1の補足です。

メニュー操作する前に、統合結果が出力される位置にアクティブセルを置いておいてください。

回答No.1

handomari

メニューから、データ－統合でダイアログボックスを出して、シートＡとシートＢのデータ範囲を行列の項目名を含めて追加します。
そして、統合の基準を上端行と左端列のチェックをつけてＯＫすれば完了です。

補足

早速の回答ありがとうございます！
こんな方法があったのかと、目からウロコです。

甘えて申し訳ないのですが、実はもうひとつ困った事に直面しています。
質問の際に内容を整理しようと省いてしまったのですが、
実は、値１～値４の他に、文字列１,２のような、
集計には利用しない項目もいくつか存在しています。
これらの項目はキー項目に紐付いているデータです。
これらの項目を、一緒にシートＣに持っていく事は可能でしょうか・・・

状況の補足として、
文字列Ａ,Ｂはキー項目毎に一致していない場合があります。
シートＡにキー項目が存在する場合はシートＡのデータを優先し、
シートＢにのみキー項目があるデータは、シートＢのデータを利用したいと考えています。
上記条件分岐は難しいようであれば、常にシートＡの文字１,２を利用し、
シートＡにキー項目が存在しない場合は空白になっても良いのですが・・・

もし解決方法があればアドバイスをお願いします！m(_ _)m