エクセルのくし刺し集計で成績表を集計をしたい

質問

シート１枚がそれぞれ受講者の成績表だとします。
１枚のシートに項目は90程有り、レベルは１～４になっています。
１枚目のシートから１０枚目のシートまでで、
項目別の修得度の集計をしたいのです。
つまり、項目１のレベル１の個数、レベル２の個数・・・・というように。

各シートのI列にレベルを書き込んでいます。
統計するシートのJ列からN列に向かって、各項目のLv1からLv4の個数を記入し、たいのです

どのようにすればよいでしょうか。

エクセルは2003です。

皆様、宜しくお願い致します。

回答No.5

keithin · ベストアンサー

シート１からシート１０のＩ列にデータを用意。
集計シートのＫ列に
=SUMPRODUCT((SUBTOTAL(9,INDIRECT("Sheet"&ROW($A$1:$A$10)&"!RC9",FALSE))=COLUMN(A2))*1)
を記入し，右にコピー，下にコピー。

回答No.4

KURUMITO

各シートの項目の並びは同じものとします。各シートはSheet1からSheet10までとなっているとします。
そこで串刺しですが関数で一気に求めることはできませんので一度まとめのシート例えばシート１１にすべてのシートのデータを表示させ、その後にそのシートを使ってまとめの解析をすればよいでしょう。各シートの計算のもととなるデータが一か所に表示されることもまた大きなメリットであると思います。
シート１１ではA２セルから下方には各シートで使われている項目名をコピーして表示させます。
また、集計に利用するシートはシート１から１０までとする場合にはB１セルからK１セルにかけて１，２，３、…と入力します。
その上でB２セルには次の式を入力してK２セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(OR(B$1="",$A2=""),"",INDIRECT("Sheet"&B$1&"!I"&ROW(B2)))

これで各シートのI列でのレベルが表示されます。

次のまとめとしてM１セルからP１セルにかけて１，２，３，４を入力します。レベルです。
M２セルには次の式を入力してP２セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

これで各項目ごとのレベルの集計が表示されます。
最後に作業列が目障りでしたらB列からL列までを選択して右クリックし、非表示を選択すればよいでしょう。

お礼

お礼が遅れてすみません。
いろいろなやり方があるようですが、ど素人なので簡単に出来た方法を使わせて頂きました。
絶対自分では解決できない事を教えて頂ける、ＯＫwebってすばらしいですね。
ありがとうございました。

回答No.3

imogasi

補足すること。
＞シート１枚がそれぞれ受講者の成績表だとします
何枚ものシートがあるようだが、みな同じフォーマット（項目的に同じ列構成）か？
＞１枚のシートに項目は90程有り、レベルは１～４になっています。
　レベルというのは質問者だけの仕事の用語であろう。読者・他人にはわからない。
各（全？）セルのデータは、習熟レベルなどのランクの数字で、１－４のどれかが入っているのか？
各（個人？）シートの何処かのセル（番地が同じセル）に１－４間での数字が入っているのか？
シートごとに質問者なら見れば判るといった内容ではないでしょうね。コード化されているのでしょうね。
＞１枚目のシートから１０枚目のシートまでで、項目別の修得度の集計をしたいのです
例えばレベル１のシートが１０枚目のシートまでで、レベル２は１１枚目から２０枚目などか？
＞項目別の修得度の集計をしたいのです
＞項目１のレベル１の個数、レベル２の個数・・・・というように
ということは項目１の入ったセルにはレベルを表す数の１か２か３か４のどれかが入っていて、レベル１の入ったものはぜんしーとを数えて、何人か
をカウントしたいということか。
ーー
シート数を少ない数にして、項目数も少ない数にして、模擬実例を作れないのか。
画像からでは読み取りにくい。
----
簡単な例の質問にすると下記ではないか
例データ
Ｓｈｅｅｔ１
項目 レベル
a 1
b 3
c 2
Ｓｈｅｅｔ２
項目 レベル
a 3
b 2
c 2
Ｓｈｅｅｔ３
項目 レベル
a 1
b 2
c 1
ーーー

データーピボットテーブルー複数のワークシート範囲ー次へー自動ー次へ
Ｓｈｅｅｔ１で範囲指定（Ａ１：Ｂ４）ー追加ー
Ｓｈｅｅｔ２で範囲指定（Ａ１：Ｂ４）ー追加ー
Ｓｈｅｅｔ３で範囲指定（Ａ１：Ｂ４）ー追加ー
ーー
範囲一覧は
Ｓｈｅｅｔ1！$Ａ$１：$Ｂ$４
Ｓｈｅｅｔ2！$Ａ$１：$Ｂ$４
Ｓｈｅｅｔ3！$Ａ$１：$Ｂ$４
となる
ーーー
その後
次へーレイアウトボタンクリック
行には、行
列には、列と値
をＤ＆Ｄする
データは「データの個数」データの部分でダブルクリックして出たものから選ぶ）ーＯＫ－ＯＫ
ーーー
データの個数 / 値 列 値
レベル レベル 合計 総計
行 1 2 3
a 2 1 3 3
b 2 1 3 3
c 1 2 3 3
総計 3 4 2 9 9

不要列や行は非表示に出来る。

お礼

お礼が遅れてすみません。
いろいろなやり方があるようですが、ど素人なので簡単に出来た方法を使わせて頂きました。
絶対自分では解決できない事を教えて頂ける、ＯＫwebってすばらしいですね。
ありがとうございました。

回答No.2

mu2011

データシートも集計用シートと同様に４列構成にすれば串刺し計算が可能です。
（１）現在の項目行のレベル番号に基づき、各レベル列に「１」を入力し直す
（２）集計シートの先頭セルに=SUM(シート1:シート10!レベル1のセル)として縦横にコピー

回答No.1

MackyNo1

ご希望の集計をするには、複数のシートを集計するピボットテーブルを利用するのがおすすめです。

「データ」「ピボットテーブルと・・・・」で「複数のワークシート範囲」にチェックを入れ、「次へ」「次へ」で範囲の右側のアイコンをクリックして、1つのシートの項目名を含めたデータ範囲を選択して「追加」します。
同様に2つ目以降のシートのデータ範囲を選択して「追加」して、すべてのシートのデータ範囲を「追加」し「完了」ボタンをクリックします。

表示されたピボットテーブルの「値」フィールドを、「列」の部分にドラッグしてレベルの数字を追加し、「列」と表示されている部分をテーブル外にドラッグして削除します。
最後にレベルの合計値が表示されているデータフィールドで右クリックして、「フィールドの設定」で「データの個数」を選択します。