自動で３つの表から一つの表にする方法

質問

EXCELで一つのタブに　A/B/Cと３つの表があり　それぞれ国名と数値が並んでます。
問題は　AにはあるけどBCにはないや、AとCだけにある国名が有ります。
それらを　新しくDという表にABC全ての表から抜けなく全部の国名を抜き取る方法を教えていただけませんか？
毎月の集計に必要なので　できれば　関数かマクロでできれば幸いです。

回答No.6

kagakusuki

幾つか不明な点かあります。
　A、B、Cの各表中の数値は、同じ国名のものに関しては合計値を表示すれば良いのか、それとも個別に表示する様にするのか？
　1つの表中に(例えば表Aの中に)同じ国名が複数回現れている事があり得るのか否か？
　もし、1つの表中に同じ国名が複数回現れている事もあるとした場合において、国名が同じであっても数値が異なる場合があり得るのか否か？
　もし、1つの表中に国名が同じであっても数値が異なるものがあり得るとした場合において、その複数個の数値の合計値を表示するのか、或いは、その国名の数値の全てを表示するために複数行を使って個別に表示するのか、それとも、何か他の方法で表示するのか？

　それらの事が不明ですので、取り敢えずの話として、1つの表中には同じ国名が複数回現れる事は無く、A、B、Cの各表中の数値は個別に表示するものとして話を進める事に致します。（もし、この条件が誤っている場合には、補足欄等を使用して、実際にはどの様にされたいのかを御教え願います）

　今仮に、Sheet1のA列の3行目以下に国名、B列に1種類目の数値データ、C列に2種類目の数値データが入力されている表Aがあり、Sheet1のE列の3行目以下に国名、F列に3種類目の数値データ、G列に4種類目の数値データが入力されている表Bがあり、Sheet1のI列の3行目以下に国名、J列に5種類目の数値データ、K列に6種類目の数値データが入力されている表Cがあり、Sheet2のA列～G列に、表A～表Cのデータを一纏めにした表を表示させるものとします。
　又、Sheet3のB1～C5の範囲内のセルを作業用のセルに、同様にSheet3のE列とF列を作業列として使用するものとします。

　まず、Sheet3のB2セルに次の関数を入力して下さい。(表Aにおいて国名が入力されている行範囲内に含まれる行数を求める関数です)

=IF(ISNUMBER(MATCH("*?",Sheet1!$A:$A,-1)),MATCH("*?",Sheet1!$A:$A,-1)-ROW(Sheet1!$A$2),0)

　次に、Sheet3のB3セルに次の関数を入力して下さい。(表Bにおいて国名が入力されている行範囲内に含まれる行数を求める関数です)

=IF(ISNUMBER(MATCH("*?",Sheet1!$E:$E,-1)),MATCH("*?",Sheet1!$E:$E,-1)-ROW(Sheet1!$E$2),0)

　次に、Sheet3のB4セルに次の関数を入力して下さい。(表Cにおいて国名が入力されている行範囲内に含まれる行数を求める関数です)

=IF(ISNUMBER(MATCH("*?",Sheet1!$I:$I,-1)),MATCH("*?",Sheet1!$I:$I,-1)-ROW(Sheet1!$I$2),0)

　次に、Sheet3のB5セルに次の関数を入力して下さい。(表A～表Cにおいて国名が入力されている行範囲内に含まれる行数の合計を求める関数です)

=SUM($B$2:$B$4)

　次に、Sheet3のC1セルに数値の1を入力して下さい。
　次に、Sheet3のC2セルに次の関数を入力して下さい。

=IF($B2>0,SUM($B$1:$B2)+1,"")

　次に、Sheet3のC2セルをコピーして、Sheet3のC3～C4の範囲に貼り付けて下さい。
　次に、Sheet3のE1セルに次の関数を入力して下さい。

=IF(ROWS($1:1)>$B$5,"",INDEX(CHOOSE(MATCH(ROWS($1:1),$C$1:$C$3),Sheet1!$A:$A,Sheet1!$D:$D,Sheet1!$G:$G),ROWS($1:1)-LOOKUP(ROWS($1:1),$C$1:$C$3)+1+ROW(CHOOSE(MATCH(ROWS($1:1),$C$1:$C$3),Sheet1!$A$2,Sheet1!$D$2,Sheet1!$G$2)))&"")

　次に、Sheet3のF1セルに次の関数を入力して下さい。(国名を並べる順番の基準となる数値を割り振る関数です)

=IF($E1="","",IF(COUNTIF($E$1:$E1,$E1)=1,COUNTIF($E:$E,">"&$E1),""))

　次に、Sheet3のE1～F1の範囲をコピーして、同じ列の２行目以下に（表A～表Cの行数の合計値を上回るのに十分な行数となる様に）貼り付けて下さい。

　次に、Sheet2のA3セルに次の関数を入力して下さい。（Sheet3のF列の数値を利用して、国名を昇順で並べる関数です）

=IF(ROWS($3:3)>COUNT(Sheet3!$F:$F),"",INDEX(Sheet3!$E:$E,MATCH(LARGE(Sheet3!$F:$F,ROWS($3:3)),Sheet3!$F:$F,0)))

　次に、Sheet2のB3セルに次の関数を入力して下さい。（1種類目の数値を表示させるための関数です）

=IF($A3="","",IF(ISNUMBER(1/(VLOOKUP($A3,Sheet1!$A:$C,2,FALSE)<>"")),VLOOKUP($A3,Sheet1!$A:$C,2,FALSE),""))

　次に、Sheet2のC3セルに次の関数を入力して下さい。（2種類目の数値を表示させるための関数です）

=IF($A3="","",IF(ISNUMBER(1/(VLOOKUP($A3,Sheet1!$A:$C,3,FALSE)<>"")),VLOOKUP($A3,Sheet1!$A:$C,3,FALSE),""))

　次に、Sheet2のD3セルに次の関数を入力して下さい。（3種類目の数値を表示させるための関数です）

=IF($A3="","",IF(ISNUMBER(1/(VLOOKUP($A3,Sheet1!$E:$G,2,FALSE)<>"")),VLOOKUP($A3,Sheet1!$E:$G,2,FALSE),""))

　次に、Sheet2のE3セルに次の関数を入力して下さい。（4種類目の数値を表示させるための関数です）

=IF($A3="","",IF(ISNUMBER(1/(VLOOKUP($A3,Sheet1!$E:$G,3,FALSE)<>"")),VLOOKUP($A3,Sheet1!$E:$G,3,FALSE),""))

　次に、Sheet2のF3セルに次の関数を入力して下さい。（5種類目の数値を表示させるための関数です）

=IF($A3="","",IF(ISNUMBER(1/(VLOOKUP($A3,Sheet1!$I:$K,2,FALSE)<>"")),VLOOKUP($A3,Sheet1!$I:$K,2,FALSE),""))

　次に、Sheet2のG3セルに次の関数を入力して下さい。（6種類目の数値を表示させるための関数です）

=IF($A3="","",IF(ISNUMBER(1/(VLOOKUP($A3,Sheet1!$I:$K,3,FALSE)<>"")),VLOOKUP($A3,Sheet1!$I:$K,3,FALSE),""))

　次に、Sheet2のA3～G3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

　以上です。

回答No.5

kmetu

No2です。老婆心ながら、もしコードがご自身の表とうまく整合しなかった場合、マクロではありませんがそれほど手間のかからない抽出方法を追記しておきます。

まず、A/B/Cと３つの表の国名を全て利用していない列にコピーします(2007以降の場合は、Dの表の国名を表示したいところにコピーします)

Aが
国名
アメリカ
イギリス
日本

Bが
国名
アメリカ
ドイツ
日本

Cが
国名
フランス
ドイツ
イタリア

としたばあい、利用していない列(2007以降の場合は、Dの表の国名を表示したい列)に

国名　←この項目名がないと一行目が項目名と認識されてアメリカが2個出てきます
アメリカ
イギリス
日本
アメリカ
ドイツ
日本
フランス
ドイツ
イタリア

と、国名が重複したままコピーします、その後2003でしたら以下のページの「29-1 重複しない抽出」のところの操作を実行します。

データを扱う時のいろいろな操作 1
http://www11.plala.or.jp/koma_Excel/contents3/mame3029/mame302901.html#%E9%87%8D%E8%A4%87%E3%81%97%E3%81%AA%E3%81%84%E6%8A%BD%E5%87%BA

2007以降の場合は以下のページ

重複しないデータに置き換える　
http://www4.synapse.ne.jp/yone/excel2010/excel2010_filter24.html

これで、全ての国名が抽出されます。

慣れたらそれほど時間がかからず作業ができると思いますので、マクロがうまくいかなかった場合挑戦してみてください。

あと、数値の合計はSUMIF関数を利用してください。

回答No.4

kmetu

No3　間違いです

列ではなく行でした

一行目に項目名があり二行目から国名などの実際のデータが入力されているものと仮定しています。こちらも適宜ご自身の表の行に合わせて変更してください。

です。

回答No.3

kmetu

No2　補足です。

一列目に項目名があり二列目から国名などの実際のデータが入力されているものと仮定しています。こちらも適宜ご自身の表の列数に合わせて変更してください。

回答No.2

kmetu

仮にシート名はSheet7として、それぞれの国名がA列C列E列その国名に対する数値がB列D列F列にあるとします。

新しく全て抜き取った結果をG列に、加算した数値をH列に表示するとします。
また、一時的にI列を使用します。

上記の条件で以下のコードになります。各列は適宜ご自身の列に変更してください。

Sub Sample()

Dim A_BottomRow As Long
Dim C_BottomRow As Long
Dim E_BottomRow As Long

Sheets("Sheet7").Activate
With Sheets("Sheet7")
A_BottomRow = .Range("A" & Rows.Count).End(xlUp).Row
C_BottomRow = .Range("C" & Rows.Count).End(xlUp).Row
E_BottomRow = .Range("E" & Rows.Count).End(xlUp).Row

.Range("A1:A" & A_BottomRow).Copy Destination:=.Range("I1:I" & A_BottomRow)
.Range("C2:C" & C_BottomRow).Copy Destination:=.Range("I" & A_BottomRow + 1 & ":I" & C_BottomRow)
.Range("E2:E" & E_BottomRow).Copy Destination:=.Range("I" & A_BottomRow + C_BottomRow & ":I" & C_BottomRow + E_BottomRow)
.Range("I1:I" & .Range("I" & Rows.Count).End(xlUp).Row).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=.Range("G1"), Unique:=True
.Range("I1:I" & .Range("I" & Rows.Count).End(xlUp).Row).ClearContents
.Range("H2").Formula = "=SUMIF(A:E,G2,B:F)"
.Range("H2").Select
Selection.AutoFill Destination:=.Range("H2:H" & .Range("G" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault
End With

End Sub

回答No.1

tom04

こんにちは！

＞一つのタブに・・・
とあるのは一つのSheetに！という解釈での一例です。

表のレイアウトが判らないので勝手に↓の画像のような配置の状態でVBAでやってみました。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストし、Excel画面に戻りマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, j As Long, endRow As Long
Application.ScreenUpdating = False
endRow = Cells(Rows.Count, "J").End(xlUp).Row
If endRow > 1 Then
Range(Cells(2, "J"), Cells(endRow, "K")).ClearContents
End If
For j = 1 To 7 Step 3
endRow = Cells(Rows.Count, j).End(xlUp).Row
Range(Cells(2, j), Cells(endRow, j)).Copy Cells(Rows.Count, "J").End(xlUp).Offset(1)
Next j
For i = Cells(Rows.Count, "J").End(xlUp).Row To 2 Step -1
If WorksheetFunction.CountIf(Range("J:J"), Cells(i, "J")) > 1 Then
Cells(i, "J").Delete shift:=xlUp
End If
Next i
endRow = Cells(Rows.Count, "J").End(xlUp).Row
With Range(Cells(2, "K"), Cells(endRow, "K"))
.Formula = "=SUMIF(A:G,J2,B:H)"
.Value = .Value
End With
Application.ScreenUpdating = True
End Sub 'この行まで

※　行・列の配置は適宜変更してください。
※　「数値」列のデータは合計しています。m（_ _）m