エクセルで複数列のデータをまとめる方法

質問

あまりエクセル詳しくないので教えて下さい＞＜
下記のようなデータを一覧にまとめたいです！

列A　　　列B　　　 列D　　列E　　　列G 　　列H
名前　　英語　　名前　　　数学　　名前　　国語
A　　　　98　　　　J　　　　　98　　　　ZZ　　　98
B　　　　96　　　　K　　　　　97　　　　K　　　 97
J　　　　92　　　　B　　　　　97　　　　B　　　　95
：　　　　：　　　　　：　　　　　：　　　　　：　　　　：

なぜか各列にデータが分かれていて、各4、5万人分のデータがバラバラの列になっているので、

列A　　列B　　列D　　・・・
名前　 国語　 数学・・・
A　　　　97　　　98・・・
B　　　　98　　　76・・・

上記のように纏めたいです！
ただ、名前順にソートしても教科別に受験人数や受験者が違ったりするので…
何か効率的な方法はないでしょうか？

ご教授頂けると嬉しいです。
宜しくお願い致します！

回答No.9

mu2011

NO3です。
前回の回答で（１）元データをSheet1、集計用シートをA2以下にコピーが説明不足となっていますので訂正します。
集計シートのA2に以下にコピーするのは名前リスト（名前重複していない一覧データ）です。
この名前一覧データが無い場合、次の様な操作を行う事で重複のない名前データを作成できます。
（１）各科目別の名前列を集計シートのＡ列にコピー
（２）Ａ列を選択→データタブ→重複の削除

回答No.8

tom04

こんばんは！
Sheet1は↓の画像のような配置になっている訳ですよね？
一応そういうことだとして、VBAでの一例です。

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

Sub test() 'この行から
Dim i, j, k, L As Long
Dim ws1, ws2 As Worksheet
Set ws1 = Worksheets("sheet1") '←「sheet1」の部分は実際のSheet名に！
Set ws2 = Worksheets("sheet2")
For j = 2 To ws1.Cells(1, Columns.Count).End(xlToLeft).Column Step 3
ws2.Cells(1, 1) = ws1.Cells(1, 1)
ws2.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1) = ws1.Cells(1, j)
Next j
For j = 1 To ws1.Cells(1, Columns.Count).End(xlToLeft).Column Step 3
For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(ws2.Columns(1), ws1.Cells(i, j)) = 0 Then
ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1) = ws1.Cells(i, j)
End If
Next i
Next j
For k = 2 To ws2.Cells(Rows.Count, 1).End(xlUp).Row
For L = 2 To ws2.Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To ws1.Cells(i, Columns.Count).End(xlToLeft).Column Step 3
If ws2.Cells(k, 1) = ws1.Cells(i, j - 1) And ws1.Cells(1, j) = ws2.Cells(1, L) Then
ws2.Cells(k, L) = ws1.Cells(i, j)
End If
Next j
Next i
Next L
Next k
End Sub 'この行まで

尚、For～Nextを多用していますので、時間が少しかかるかもしれません。
参考になれば良いのですが・・・m(_ _)m

回答No.7

imogasi

これはまさに表の組み換えの問題です。
関数では式が理解不能ぐらい複雑になるでしょう。
私ならVBAで下記のようにします。しかし質問者はVBAどころではないと思うので書いても無駄かと思うが。
エクセル２００７以後（行数制限が緩和された）なら
（１）A,B列、D,E列、G,H列・・データを氏名と科目と点数の組のデータ（他シートのA,B、C３列データとして）が縦に続いた形にします。
（２）それをA列（名前）でソートします
（３）同じ名前の人の行が近くにまとまるので、人ごと＋科目順ごと＋点数の表に組み替えます。
＞教科別に受験人数や受験者が違ったりするので・・
その人のその科目の行が現れなければ空白なりにします。少し手を加えれば０点と受験してないの区別をすることは出来る。
ーー
言いたいことはVBA（プログラム）向けの課題だということです。
エクセルでデータを作るときに、後々を考えてない責めが出ています。
ーー
既に機能名が出ている、エクセルの、データー統合やピボットも元のデータを保存したまま（合計などしないで）で処理するのは目的に入っていないように思うのですが。

回答No.6

MackyNo1

>恐縮ですがエラー処理の簡単な方法がありましたらご教授頂きたいです。

ご使用のエクセルが２００７なら、すでに回答したようにIFERROR関数を利用するのが簡単です。

=IFERROR(INDEX(Sheet1!$A$2:$N$10000,MATCH($A2,INDEX(Sheet1!$A$2:$N$10000,,MATCH(B$1,Sheet1!$A$1:$N$1,0)-1),0),MATCH(B$1,Sheet1!$A$1:$N$1,0)),"")

回答No.5

KURUMITO

シート1にお示しの元の表があるとします。
名前も自動的に表示させるようにするためにシート1には3列の作業列を設けます。
J2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTIF(A$2:A2,A2)=1,MAX(J$1:J1)+1,"")

K1セルには次の式を入力します。

=MAX(J:J)

K2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTIF(A:A,D2)+COUNTIF(D$2:D2,D2)=1,MAX(K$1:K1)+1,"")

L1セルには次の式を入力します。

=MAX(K:K)

L2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTIF(A:A,G2)+COUNTIF(D:D,G2)+COUNTIF(G$2:G2,G2)=1,MAX(L$1:L1)+1,"")

そこでお求めの表をシート2に表示させるとしてA1セルには名前と入力してB1セルには国語、C1セルには数学などと入力します。

A2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTIF(Sheet1!J:J,ROW(A1))>0,INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!J:J,0)),IF(COUNTIF(Sheet1!K:K,ROW(A1))>0,INDEX(Sheet1!D:D,MATCH(ROW(A1),Sheet1!K:K,0)),IF(COUNTIF(Sheet1!L:L,ROW(A1))>0,INDEX(Sheet1!G:G,MATCH(ROW(A1),Sheet1!L:L,0)),"")))

名前が表示されます。
次にB2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(ISERROR(INDEX(Sheet1!$A:$H,MATCH($A2,INDEX(Sheet1!$A:$H,1,MATCH(B$1,Sheet1!$1:$1,0)-1):INDEX(Sheet1!$A:$H,10000,MATCH(B$1,Sheet1!$1:$1,0)-1),0),MATCH(B$1,Sheet1!$1:$1,0))),"",INDEX(Sheet1!$A:$H,MATCH($A2,INDEX(Sheet1!$A:$H,1,MATCH(B$1,Sheet1!$1:$1,0)-1):INDEX(Sheet1!$A:$H,10000,MATCH(B$1,Sheet1!$1:$1,0)-1),0),MATCH(B$1,Sheet1!$1:$1,0)))

シート2では教科の並べる1行での順番は任意に行うことができます。

回答No.4

keithin

どこか空セル（たとえばシート２の）を選んでおいて，データメニュー(データタブ）の「統合」というのを開始します
AB列を選んで追加
DE列を選んで追加
GH列を選んで追加
して，上端行と左端列にチェックしてOKすると，それだけで名寄せからマトメまで一気に自動でやってくれます。

名前リストを事前に準備したり，関数に苦労する必要は全くありません。

＃ただし元のデータがご相談のように（添付の画像のように）実はきれいに作成されていないときは，ご自分でご質問にまさに書いたように，表の体裁を整える必要があります。

回答No.3

mu2011

名前リストが有る前提ですが、以下の方法で可能です。
（１）元データをSheet1、集計用シートをA2以下にコピー
（２）B2に=IF(COUNTIF(Sheet1!A:A,$A2),VLOOKUP($A2,Sheet1!A:B,2,FALSE),"")として下方向にコピー
　　　C2に=IF(COUNTIF(Sheet1!D:D,$A2),VLOOKUP($A2,Sheet1!D:E,2,FALSE),"")として下方向にコピー
　　以降、科目別に数式内の列番号を各々調整して下さい。

お礼

ご助言有難うございました。
B列はうまく反映されているようなのですが、C列の名前と得点がずれてしまいます…

テストを受けているメンバーが重複している場合とどちらか片方の列にしか存在しないメンバーがいるのでおかしくなってしまうのでしょうか…

重ね重ね恐縮ですが、必ずしもメンバー重複だけではない場合の調整についてもご助言頂けると助かります。

回答No.2

MackyNo1

元データがSheet1にあるなら、まとめシートのB2セルに以下の式を入力して右方向及び下方向にオートフィルしてください。

=INDEX(Sheet1!$A$2:$N$10000,MATCH($A2,INDEX(Sheet1!$A$2:$N$10000,,MATCH(B$1,Sheet1!$A$1:$N$1,0)-1),0),MATCH(B$1,Sheet1!$A$1:$N$1,0))

データがない場合は＃N/Aのエラーが表示されますので、たとえばIFERROR関数などで適宜エラー処理をしてください。

=IFERROR(上記の式,"")

＃エクセルのバージョンが記載されていませんが（ひとまず２００７以降のバージョンの対応策を示しました）、エクセルのバージョンによって使用できる機能や操作方法が大きく異なりますので、質問の際には必ずバージョンを明記するようにしましょう。

お礼

エクセルがあまり得意ではないので、エラー処理がうまくいかず…
ご助言頂いて有難うございました。

＃質問が初めてだったので勝手がわからず申し訳ありません。
エクセルのバージョンは2007です。

恐縮ですがエラー処理の簡単な方法がありましたらご教授頂きたいです。

回答No.1

mt2008

ピボットテーブルで分析するデータのある場所を「複数のワークシート範囲」として、A:B列、C:D列、E:F列を登録すれば御望みの表示になると思います。

ただし、同姓同名が居ない事が前提ですし、4,5万人分のデータを処理するとなると相当時間が掛かりそうですが……。

＃提示されている元データと纏めたい表の例で、内容が異なっているのは手違いでしょうか？

お礼

ピボットでは参照出来ないデータ、データが多過ぎると言われてしまいました…

＃例は例として記載をしていたので、ニュアンスが伝わればと思い特に他意は御座いません。

まだ奮闘中ですが、ご助言有難うございました！