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で質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBAで、1つのエクセルで、2つのシートからもう1つのシートに条件のある転記コードを教えてください。 1 2023/03/16 18:07
- Visual Basic(VBA) 顧客ごとに違う点検案内を作成するマクロ 4 2022/09/16 05:34
- Visual Basic(VBA) VBAを使いシート間で貼り付け 3 2023/03/14 20:53
- Excel(エクセル) エクセル関数について 2 2022/04/13 18:25
- Excel(エクセル) VBAで、シート間の転記するコードを教えてください。 4 2023/03/26 10:43
- Excel(エクセル) Excel 計算式を教えて下さい 3 2022/12/15 19:46
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- Visual Basic(VBA) 【VBA】データを入力後に,同一シート内に履歴として転記するVBAコードを教えていただきたいです。 3 2022/11/16 01:37
- Excel(エクセル) 複数セルデータを別シートの単一セルにコピーしたい。(詳細をご参照ください) 1 2022/12/14 15:08
- Excel(エクセル) 別シートに毎回異なるデータをコピーする 7 2022/06/24 09:02
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
時間によってファイル名が変わ...
-
Excelで、決まった行を繰り返し...
-
【マクロ】ファイル名の変更に...
-
WPS OFFICEでの縦書きについて
-
エクセルで 例えば 伊藤と名前...
-
Aというブックの1というシート...
-
【マクロ】毎回、ファイル名が...
-
エクセルの順位別一覧表の自動...
-
Excel2013のF6キー操作について
-
エクセルの条件付き書式につい...
-
エクセルで「-0.0」と表示さ...
-
エクセル 価格表から単価を呼び...
-
シートAで横に並んだ項目→シー...
-
【Microsoft Office Excel Comp...
-
西暦や和暦の表示をyyyymmdd表...
-
Excelが固まってしまった。
-
Excel元に戻す方法を教えてくだ...
-
文字列になっている時間をVBAで...
-
【関数】スペースがいくつ入っ...
-
Excelのセルを飛ばして入力する
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報