Excel（エクセル）活用コミュの成績表作成　～横の物を縦に積み上げたい

判り難いタイトルですみません。やりたいことは次の様なことです。

試験の成績が次の様なcsv形式で与えられてます。

1行目　受験番号,氏名,英語,数学,国語
2行目　1,山田　一郎,65,55,75
3行目　2,佐藤　花子,75,50,55
以下同様で50～100名分を処理します。

作りたい成績表は１枚のシートで、

受験番号　　| 氏名
1 　　　　　　　| 山田　一郎
英語 | 65
数学 | 55
国語 | 75

受験番号　　| 氏名
2　　　　　　　| 佐藤　花子
英語 | 75
数学 | 50
国語 | 55

以下同様に続く。

条件は
1) Excel 2003でも出来ること。2007以降の固有機能は使わないで。
2) 科目名や科目数はその都度変わります。
3) 科目順序も元のファイルと成績表とで、必ずしも同じでは無いようにも出来る方法を希望。
4) 偏差値や順位などを別に計算して成績表に加えることもあります。
5) シートや作業列はいくつでも使えますが、ファイルとしては１つで完結すること。
6) なるべく柔軟に対応出来て出来ればマクロやVBAを使わずに出来る方法。indirect関数は使用可。

科目数固定なら当面の解決法は見つかっていますが、なるべくスマートな方法を希望します。

コメント(8)

最初
全て
最新の40件

[1] mixiユーザー 10月24日 12:30

目的に沿う手段なのかどうか、よく分かりませんが、コピー＆ペーストで出来そうな気がします。

「形式を選択して貼り付け(S)」の中にある「行列を入れ替える(E)」を試してみてください。

こうすれば、今横方向に並んでいる「受験番号,氏名,英語,数学,国語」が縦方向に並び、それとともに、山田一郎さんデータや佐藤花子さんデータも縦方向に並びます。

ただ、各データが単純に一列になるだけだから、トピ主さんが今必要としているレイアウトにはならないのかな？

[2] mixiユーザー 10月24日 14:30

>>1 八神かかしさん
コメント有難うございます。その方法で出来ることは確認しています。科目数などに変更があったときの作業量などを考えて、もう少しスマートな方法を、というのが元々の動機です。
いずれにしても、たとえば成績表が５行単位なら

1
1
1
1
1
2
2
2
2
2

というように同じ数字が５行ずつ並んだ列を作れれば、あとはindirect関数で容易に参照出来ます。これを作る方法は私もだいぶ悩んで、行番号を取得する =RAW() を使って出来ることまでは確かめましたが、あるサイトでもっと簡単な方法をみつけました。

A1～A5にオートフィルで1を入れ、A6に =A1+1 という関数を入れてあとは必要な数だけコピーするという方法です。常に自分自身の5行上を参照する関数が入力されます。「コロンブスの卵」的な素晴らしい発想でした。

[3] mixiユーザー 10月24日 15:14

単に与えられたパラメータに基づいて整形するだけなら、
VBAで手続き的な処理を記述した方が、考え方自体がコードに現れるため
見通しが良いように思いますし、改変時の対応も楽だと思います。

＞2) 科目名や科目数はその都度変わります。
＞3) 科目順序も元のファイルと成績表とで、必ずしも同じでは無いようにも
＞　　出来る方法を希望。
＞4) 偏差値や順位などを別に計算して成績表に加えることもあります。

この辺の条件満たすために関数式を工夫するのは、
関数式を自在に使いこなすための訓練用の題材としては良いと思います。
ですが、保守性を維持したうえでの作業の効率化という面では
VBAを使う方法と比べ、いまいちうま味が無いように思います。

＃まぁ、私自身が似たような共通部分式を何度も記述する事が大嫌いなため、
＃偏った意見なのかも知れませんが…。

[4] mixiユーザー 10月24日 17:37

Minonさんと同じ意見です。
関数で作ると柔軟な対応は難しいと思います。
ぜひVBAでやってみてください。

サンプルです。
https://box.yahoo.co.jp/guest/viewer?sid=box-l-572s4p337bi46ab3b5wiiipv54-1001&uniqid=00b3e010-4bde-4a0c-91b3-7f49b8c4153d
# CSVファイルの名前は Data.txt で同じフォルダに置いて実行します。

[7] mixiユーザー 10月24日 19:16

実際のBookで上手くいくか分かりませんが
前のBookに追記するのではなく、毎回別Bookにするのであれば
(追記するとなると、面倒な式になると思いますが・・・)
例えば、

A1セル =IF(OR((INT((ROW()-1)/(COUNTA(Sheet1!$1:$1)+1))+2)>COUNTA(Sheet1!$A:$A),MOD(ROW(),COUNTA(Sheet1!$1:$1)+1)=0),"",IF(MOD(ROW(),COUNTA(Sheet1!$1:$1)+1)=2,INDIRECT("Sheet1!A"&INT((ROW()-1)/(COUNTA(Sheet1!$1:$1)+1))+2),INDIRECT(ADDRESS(1,MOD(ROW(),COUNTA(Sheet1!$1:$1)+1),,,"Sheet1"))))

B1セル =IF(A1="","",IF(MOD(ROW(),COUNTA(Sheet1!$1:$1)+1)=1,Sheet1!$B$1,INDIRECT(ADDRESS(INT((ROW()-1)/(COUNTA(Sheet1!$1:$1)+1))+2,MOD(ROW(),COUNTA(Sheet1!$1:$1)+1),,,"Sheet1"))))

A1:B1を適当な行までコピー又はオートフィル

ただ、私もVBAでやった方が楽だと思いますね

[8] mixiユーザー 10月27日 06:13

皆様有り難うございます。
やはり「正攻法」としてはVBAでしょうね。少し暇のあるときにいろいろ試してみます。

ログインすると、みんなのコメントがもっと見れるよ