Excel 二つの表を用いたデータ整理

質問

かなり困っています。

(1)二つのエクセル表が存在
(2)ファイル１には↓のようにデータが並んでいます

　　　Ａ列　　Ｂ列　　Ｃ列　　Ｄ列

　　　生徒Ａ　生徒Ｂ　生徒Ｃ　生徒Ｄ
行１　国語27　国語34　国語54　算数34
行２　数学36　家庭25　算数23　理科23
行３　理科55　理科44　理科21　英語44
行４　社会87　社会33　社会32
行５　英語54　

(3)ファイル２は以下のようになっています

　　　Ａ列　　Ｂ列　　Ｃ列　　Ｄ列　　Ｅ列
　　　　　　　国語　　算数　　理科　　社会
行１　生徒Ａ　27　　　36　　　55　　　87
行２　生徒Ｂ　

(4)要するに、ファイル１のデータから必要な数値を抽出して
ファイル２のデータに移し変えたいのですが、ファイルＡの中の名称とデータ数が異なる場合があることもあり、整理はコピペしかないのかと半ばあきらめています。

とはいえデータの数は4000…正直なきたいです…何かいい方法はありませんか？

回答No.9

tom04

こんばんは！
すでに色々回答が出ていますので、
参考程度で目を通してみてください。

↓の画像で説明させていただきます。
数学を算数に変更させてもらっています。
尚、科目は7科目まで、生徒数は列幅いっぱい
当方使用のExcel2003ではIV列が最終列になるはずですので
生徒数は　256列÷2の128人　までしか対応できないと思います。
科目数は増えても問題ないと思いますので、
科目数によって範囲指定の領域はアレンジしてください。

Sheet2の
科目はあらかじめ入力してあるものとします。

Sheet2の生徒名のセルA2セルに
=IF(COUNTA(Sheet1!$1:$1)>=ROW(A1),INDEX(Sheet1!$1:$1,,2*ROW(A1)-1),"")

そして、B2セルに
=IF(ISERROR(INDEX(Sheet1!$2:$8,MATCH(B$1,INDEX(Sheet1!$2:$8,,MATCH($A2,Sheet1!$1:$1,0)),0),MATCH($A2,Sheet1!$1:$1,0)+1)),"",INDEX(Sheet1!$2:$8,MATCH(B$1,INDEX(Sheet1!$2:$8,,MATCH($A2,Sheet1!$1:$1,0)),0),MATCH($A2,Sheet1!$1:$1,0)+1))
という数式を入れ列方向（科目が入力してあるセルまで）オートフィルでコピー

最後にA2～科目がある最後の列を範囲指定し、最後のセルのフィルハンドルで
下へオートフィルでコピーすると画像のような感じになります。

以上、参考になれば幸いですが、
他に良い方法があれば読み流してくださいね。m(__)m

お礼

ありがとうございます！
ぜひとも参考にさせていただきます！

皆さんのおかげでどうやら三日連続徹夜は防げそうです…

回答No.8

imogasi

#3です。補足いただいたのですが、
＞つのブックが存在し、書式は固定です。正しくはブック１からブック２の書式で指定されている項のデータを抜き出すのが目的でした。
エクセルでは書式は別の意味が決っているので、表のレイアウトとかフォーマットと表現すべきだと思います。
＞指定されている項のデータを抜き出すのが
指定されているというのはどういう状態ですか。
●ブック２に、穴埋めをすべきような、空白セルがあるのか。
そこへブック１のデータを探して、該当セルデータを持ってくるのか。
ブック２で、空白？のセルのあり場所は何かルール性が無いのか。
例えば科目の理科がデータが入ってないとか
在る科目だけとか。
●生徒の順番はブック１とブック２と違うのか。
ーー
回答すべきことの内容は、
結局データ例を少数挙げて、人間が手作業でやる場合、どういうステップになるか、文章にすれば判りよいのだ。あとは回答者がエクセル的に方法を考える。

回答No.7

a987654

ＮＯ７です。１っ箇所ポカミス修正です。
＞７．SHEET3からファイル１に移り
"ファイル１”→"ファイル２”に読み替えて下さい。
（読み替えないと元ファイルを壊します。）

回答No.6

a987654

＞データの数は4000…
このことからＥＸＣＥＬ２００７と断定して回答します。
（２００３等は２５６列が仕様の最大ですのでこの質問が不成立。
本当にこんなことをやっているのでしょうか？
ファイル１の列方向に４０００件かつ１セルの文字と数値を付けた
データの持ち方、しかも使う行数は現時点で６行、教科が増えても
１０行程度であり甚だ疑問ですが、次の機会からはファイル１
のような作り方をしないようにしましょう。

ＶＢＡの方法は他の方が書かれていますので、作業用シートを
使った方法を示します。
１．ファイル１にて１～６行を選択（行表示を左クリックのまま
　　１～６を選択）
２．１．の選択状態で　右クリック→"コピー"
３．作業用シート(SHEET3とします)に移り　Ａ１セルで右クリック
　　"型式を選択して貼り付ける"
４．"値"と"行列を入れ替える"の２箇所にチェックを入れ"ＯＫ"
ここまででＡ～Ｅ列４０００行のデータになっている。
５．下記のように数式を設定
　　Ｆ１セル =A1
　　Ｇ１セル =IF(B1="","",LEFT(B1,2))
　　Ｇ１セルをＨ１～Ｊ１にオートフィル
　　Ｋ１セル =IF(B1="","",VALUE(SUBSTITUTE(B1,G1,"")))
　　Ｋ１セルをＬ１～Ｎ１にオートフィル
　　ここの数字を文字で扱うならば
　　　Ｋ１セルを =IF(B1="","",SUBSTITUTE(B1,G1,""))
　　として下さい。
６．Ｆ１～Ｎ１を必要なだけ下方にオートフィル
７．SHEET3からファイル１に移り
　　・Ｂ１～Ｅ１セルに固定で国語　算数　理科　社会　を入力
　　・Ａ２セルに　=+Sheet3!F1
　　・Ｂ２セルに
　　　=IFERROR(OFFSET(Sheet3!$J1,0,MATCH(B$1,Sheet3!$G1:$J1,0)),"")
　　　Ｂ２セルをＣ２～Ｅ２にオートフィル
８．Ａ２～Ｅ２を下方に必要なだけオートフィル

以上です。
冒頭にも述べましたが、ファイル１のような表の作り方はすべきでは
ないことを重ねて進言します。

お礼

ありがとうございます！
データ数4000に関する説明が不十分でした・・・
140人×(最大)10教科×３年分という意味です。
バイト先で上からある日配られてきた資料なので詳しいことは分かりませんが…とりあえず適当に並べられているようです。

他の方の方法と同様に試してみます。

回答No.5

某HN クロメート(Chromate)（@CoalTar）

「算数」と「数学」は考えませんでした
エラー処理もしませんでした
A,B列は生徒A、C,D列は生徒B・・・
同様にA列の8行目以降に並んでいるとします。

B8セルに
=INDEX($B$1:$H$5,MATCH(B$7,INDEX($A$1:$G$5,,ROW(A1)*2-1),0),ROW(A1)*2-1)
右へ 下へオートフィル
添付図参照。

お礼

ありがとうございます！
画像も入っていて凄く分かりやすいです。

回答No.4

KURUMITO

行１と書かれているのは１行目には項目名が書かれているのですから２行目からデータがあるとします。
ファイル１とありますのはシート１であるとします。
そこでお望みの表はシート２に作成するとします。
１列目には科目名が有るとします。
Ａ２セルには次の式を入力し下方にオートフィルドラッグします。

=IF(INDEX(Sheet1!$1:$1,ROW(A1)*2-1)=0,"",INDEX(Sheet1!$1:$1,ROW(A1)*2-1))

Ｂ２セルには次の式を入力し右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(OR(B$1="",$A2="",COUNTIF(Sheet1!$1:$1,$A2)=0),"",IF(COUNTIF(INDEX(Sheet1!$A:$XX,1,MATCH($A2,Sheet1!$1:$1,0)):INDEX(Sheet1!$A:$XX,1000,MATCH($A2,Sheet1!$1:$1,0)),B$1)=0,"",INDEX(Sheet1!$A:$XX,MATCH(B$1,INDEX(Sheet1!$A:$XX,1,MATCH($A2,Sheet1!$1:$1,0)):INDEX(Sheet1!$A:$XX,1000,MATCH($A2,Sheet1!$1:$1,0)),0),MATCH($A2,Sheet1!$1:$1,0)+1)))

お礼

ありがとうございます！
サンプルデータを用いて試して見ようと思います！

回答No.3

imogasi

何がしたいのか、文章で表現してみること。
エクセルの質問なんだからバージョンぐらい書くこと
２０００３までなら２５６列しか使えず、データ数４０００が４０００人（ならば）はありえないだろう。４０００セル？
ーー
生徒（人別）に見て
「縦のものを横にする」だけのことか。
ーーー
２つのブックのシートのデータを比べたいのか。
１つから２つめを導出したいのか
＞ファイル１のデータから必要な数値を抽出して
ファイル２のデータに移し変えたいのですが
からはそう取れる。
＞名称とデータ数が異なる場合があることもあり
なら２のブックも既に存在するのかな。
ではどうするのか。
＞「要するに」、なんていっているが、どうしたいのかわからない。
質問の文章を読み返して、考えてみること。
ーーー
一般に表の組み換えは関数では複雑になる。全セルに数式を埋め込むことになり、データ数ガ多いとエクセルの動きが重くなるとおもう。
形式を選択して貼り付けー行列を入れ替える、では対処できないのか。
＝＝
結局VBAでも出来る人の考える課題と思う。
ブック１から質問で下の方の形式に直すのは極簡単なVBAで可能。
質問者は、匂いだけ嗅いで。
標準モジュールで
Sub test01()
Dim sh1, sh2 As Worksheet
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
'--最右列を捉える
c = sh1.Range("IV2").End(xlToLeft).Column
MsgBox c
k = 2 'シート２でだい２行から書き出し
For j = 1 To c 'A列から最右列まで
sh2.Cells(k, "A") = sh1.Cells(1, j) '生徒名書き出し
For i = 2 To 8 '７科目以内と仮定
If sh1.Cells(i, j) = "" Then Exit For 'その列で空白セルに
'空白セルに出くわすと次の列の処理へ
sh2.Cells(j + 1, i) = sh1.Cells(i, j)
Next i
k = k + 1 '次はシート２の次の行に書き出し
Next j
End Sub
これは簡単のためSheet2に書き出している。ブック２のシートにブック１のシート２結果をコピペすると質問のようになる。

お礼

4000に関する説明は上のとおりですが、やはり自分でも読み返してみると支離滅裂ですね…申し訳ありません。アドバイスありがとうございます。地道にひとつずつ方法を試してみようと思います。

念のため

WindowsExcel2007において、２つのブックが存在し、書式は固定です。
正しくはブック１からブック２の書式で指定されている項のデータを抜き出すのが目的でした。

回答No.2

noname#204879

[回答番号：No.1この回答への補足]へのコメント、

》 だからＭＡＴＣＨ関数が使えないみたいです

それは貴方の勝手な思い込みです。ナントカとハサミは使いよう、と申します。

下の例では、簡単のために、別「ファイル」でなく、同一シート内のハナシにしていますが、「ＭＡＴＣＨ関数が使え」ています。ちなみに、セル A3 は「数学」でなく「算数」に改竄しています。(^_^)

　　 A　　 B　　 C　　 D　　 E　　 F　　 G　　 H
1　生徒Ａ　　　生徒Ｂ　　　生徒Ｃ　　　生徒Ｄ
2　国語　　27　国語　　34　国語　　54　算数　　34
3　算数　　36　家庭　　25　算数　　23　理科　　23
4　理科　　55　理科　　44　理科　　21　英語　　44
5　社会　　87　社会　　33　社会　　32
6　英語　　54

　　 J　　　K　　 L　　 M　　 N　　 O　　 P
1　　　　　国語　算数　理科　社会　英語　家庭
2　生徒Ａ　　27　　36　　55　　87　　54
3　生徒Ｂ　　34　　　　　44　　33　　　　　25
4　生徒Ｃ　　54　　23　　21　　32
5　生徒Ｄ　　　　　34　　23　　　　　44

K2: =IF(ISERROR(VLOOKUP(K$1,OFFSET(INDIRECT(ADDRESS(1,MATCH($J2,$A$1:$H$1,0))),,,10,2),2,FALSE)),"",VLOOKUP(K$1,OFFSET(INDIRECT(ADDRESS(1,MATCH($J2,$A$1:$H$1,0))),,,10,2),2,FALSE))

お礼

ありがとうございます。
自省した上でmikeさんの方法を試してみようと思います。

お手数かけて申し訳ありません。

回答No.1

yokomaya

勿論ありますよ。
まず、空いている列を作業列として使いMATCHでデータの有無を調べます。
次にそれで得た行番号を用いてINDEX等で実データを抜き出すんです。
ただ4000だと同姓同名など考慮が要りますね。
ちなみに縦横がややこしいなら、条件貼付けの中に行列入れ替えがありますからそちらを先にすれば、シンプルになるかも。

補足

>>yokomayaさん

ありがとうございます。
ですが、ここで質問文の補足、というか重要なことを間違えていました。一番目のファイルは↓が正しいです。

　　　Ａ列　　Ｂ列　　Ｃ列　　Ｄ列　　Ｅ列　　　Ｆ列

　　　生徒Ａ　　　　　生徒Ｂ　　　　　生徒Ｃ　
行１　国語　　27　　　国語　　　34　　国語　　　54　
行２　数学　　36　　　家庭　　　25　　算数　　　23　
行３　理科　　55　　　理科　　　44　　理科　　　21　
行４　社会　　87　　　社会　　　33　　社会　　　32
行５　英語　　54　

だからＭＡＴＣＨ関数が使えないみたいです…