Excelを使用した集計作業で、効率の悪さに頭を悩ませています。
自己解決出来ず、お知恵を拝借出来ればと質問させていただきます。
シートA・Bに同じ構造(列の項目数が一致)のデータを持っています。
A・B両方に入力されているキー項目のデータもあれば、
どちらかのみに登録されているキー項目のデータもあります。
2つのシートで一致するキー項目の値を集計し、
シートCに出力する事を考えています。
A・B両方に入力されている場合は各値を合計し、
片方にしか登録されていない場合はそのまま値を表示したいのですが、
効率の良い手順がわかりません。是非お助け下さい!
具体的には、下記のような事を考えています。
【シートA】
キー項目 値1 値2 値3 値4
A001 10 10 10 10
A002 20 20 20 20
A003 30 30 30 30
A004 40 40 40 40
A005 50 50 50 50
A006 60 60 60 60
【シートB】
キー項目 値1 値2 値3 値4
A001 10 10 10 10
A002 20 20 20 20
A005 50 50 50 50
A007 70 70 70 70
A008 80 80 80 80
↓
【シートC】
キー項目 値1 値2 値3 値4
A001 20 20 20 20
A002 40 40 40 40
A003 30 30 30 30
A004 40 40 40 40
A005 100 100 100 100
A006 60 60 60 60
A007 70 70 70 70
A008 80 80 80 80
p.s.
現在は下記のような手順で作業しています。
(1)MATCH関数でシートBのキー項目がシートAの何行目にあるかを出力。
(2)手作業で出力された行にデータを移動し、キー項目の行数を合わせる。
(3)シートCにキー項目をコピーし、
値部分は『='シートA'!B1+'シートB'!B1』のような形で計算。
(4)シートBにのみ存在するデータを結合→キー項目でソート
・・・(2)の手順で、非常に時間を要しています(x_x)
No.6ベストアンサー
- 回答日時:
キー項目が予め決まっているのでしたら、隣あたりを作業用セルにして
キー項目順に並べた表を作り、シートCでそれらを合計してはどうでしょう。
1)シートA、シートBの使用していない場所(出来れば同じ場所)に
キー項目を縦、横に値1、値2… とした表を作る。
A B C D … G H I …
1 キー項目 値1 値2 値3 キー項目 値1 値2
2 A001 10 10 10 A001
3 A002 20 20 20 A002
2)VLOOKUP関数を使いて、作業セルに値を表示(エラーは、ISERRORで0に)
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))
3)シートCで、シートAとシートBの作業用セルの値を合計し表示
アドバイスありがとうございました!
教えていただいた数式を参考に、作業させていただき上手く行きました!
(1)シートCにシートAのキー項目を並べ、値部分に数式を利用する事で、シートBのデータを取得。
(2)シートAとシートCをシートDで合計。
(3)MATCH関数を利用してシートBにのみ存在するキー項目を抜き出し、シートDに追加。
上級者の方から見るとまだまだ効率悪い作業だと思いますが、今までの作業に比べると、楽園のようです♪
No.5
- 回答日時:
初めて試してみた結果、[ピボットテーブル レポート]の機能が使えそうですよ。
1.[データ]→[ピボットテーブルとピボットグラフ レポート]を実行
2.“複数のワークシート範囲”および“ピボットテーブル”に目玉を
入れて[次へ]をクリック
3.“指定”に目玉を入れて[次へ]をクリック
4.[範囲]ボックス内にマウスカーソルを置いて、SheetA の範囲 A1:E7
をドラッグ指定(→ SheetA!$A$1:$E$7 と表示される)
5.[追加]をクリック(→ [範囲一覧]窓内に SheetA!$A$1:$E$7 が表示
される)
6.SheetB の見出しをクリック(→ [範囲]ボックス内に SheetB! が表
示)
7.SheetB の範囲 A1:E6 をドラッグ指定(→ SheetB!$A$1:$E$6 と表示
される)
8.[追加]をクリック(→ [範囲一覧]窓内に SheetA!$A$1:$E$7 および
SheetB!$A$1:$E$6 が表示)
9.[次へ]をクリック
10.“既存のワークシート”に目玉を入れて、SheetC のセル A3 をクリ
ックした後、[完了]をクリック
すると、次のような集計表が出来上がるはず。
合計 / 値 列
行 値1 値2 値3 値4 総計
A001 20 20 20 20 80
A002 40 40 40 40 160
A003 30 30 30 30 120
A004 40 40 40 40 160
A005 100 100 100 100 400
A006 60 60 60 60 240
A007 70 70 70 70 280
A008 80 80 80 80 320
総計 440 440 440 440 1760
ご回答、ありがとうございました。m(__)m
何度か見た事はあっても、自分で作った事の無かったピボットテーブル。
なるほどこうやって使うと便利なんですね。
知識不足の為、キー項目でも値でも無い項目をどう表示させるかがわからず、
今回は利用を断念してしまいましたが、勉強になりました。
丁寧に説明していただいたのに、消化しきれず申し訳ないです。。。(謝)
No.4
- 回答日時:
No.1&No.2のhandomariです。
先ほどの方法で統合する場合は、行や列に集計に関係のない項目があってもかまいませんが、数値データ以外はデータが空欄になります。
文字データについては、統合の後で、別にコピー&貼り付けで対処するようになりそうです。
何度もアドバイスをいただきましてありがとうございました。
おかげさまで、作業効率がグンと良くなりそうです(^^)
今回は、No6のアドバイスを中心に問題を解決しましたが、
教えていただいた方法で統合してから、空白になった項目にVLOOKUP関数を使えば、
もっと効率が良いのかな、等とも考え勉強させていただきます。
No.3
- 回答日時:
シートCのセルA1から項目が並んでいるとして
セルB2に下記の数式を入力
=IF(ISNA(MATCH($A2,シートA!$A:$A,0)),0,OFFSET(シートA!$A$1,MATCH($A2,シートA!$A:$A,0)-1,MATCH(B$1,シートA!$1:$1,0)-1))+IF(ISNA(MATCH($A2,シートB!$A:$A,0)),0,OFFSET(シートB!$A$1,MATCH($A2,シートB!$A:$A,0)-1,MATCH(B$1,シートB!$1:$1,0)-1))
少々数式が長いですが、これで如何でしょうか。
この回答への補足
ご回答ありがとうございます!
多分正しく検証すれば値が表示される数式を教えていただいたのだと思うのですが、
知識不足の為、正常に動かす事が出来ません・・・orz
シートCのA列には、シートAのキー項目をコピーするのでしょうか?
多少自力で解決検討してみようと思いますが、
時間が掛かりそうなので、取り敢えず御礼まで(^^;)
No.1
- 回答日時:
メニューから、データ-統合でダイアログボックスを出して、シートAとシートBのデータ範囲を行列の項目名を含めて追加します。
そして、統合の基準を上端行と左端列のチェックをつけてOKすれば完了です。
この回答への補足
早速の回答ありがとうございます!
こんな方法があったのかと、目からウロコです。
甘えて申し訳ないのですが、実はもうひとつ困った事に直面しています。
質問の際に内容を整理しようと省いてしまったのですが、
実は、値1~値4の他に、文字列1,2のような、
集計には利用しない項目もいくつか存在しています。
これらの項目はキー項目に紐付いているデータです。
これらの項目を、一緒にシートCに持っていく事は可能でしょうか・・・
状況の補足として、
文字列A,Bはキー項目毎に一致していない場合があります。
シートAにキー項目が存在する場合はシートAのデータを優先し、
シートBにのみキー項目があるデータは、シートBのデータを利用したいと考えています。
上記条件分岐は難しいようであれば、常にシートAの文字1,2を利用し、
シートAにキー項目が存在しない場合は空白になっても良いのですが・・・
もし解決方法があればアドバイスをお願いします!m(_ _)m
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルの質問です。 F列からL...
-
作成した数式を値として表示し...
-
エクセルの文字が途中から消える
-
条件付き書式設定で罫線を引き...
-
Excel関数について教えてくださ...
-
Excelの警告について
-
Excel関数について教えてくださ...
-
ワークシートに出現したこの画...
-
エクセルのセル内に分数などの...
-
タイムスタンプとテキストから...
-
EXCELの散布図で日付が1900年に...
-
エクセルでファイルの最終更新...
-
シートの情報を別のシートへま...
-
マクロの処理が遅くなった
-
エクセルの数式バーのフォント...
-
エクセルの「条件付き書式」を...
-
エクセル日付 文字列の関数がエ...
-
Excelでの文字色
-
Excelの数字の前に入っている空...
-
Excelについて教えてください。...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報