エクセル：シートを分けての集計・セルの背景色

質問

エクセルについて、質問させてください。

添付ファイルのような３つのシートを作成したいと思っています。

　※１：テスト結果入力用シート(Sheet1)
　　　　　　・受験者名、受験科目IDは、ランダムに増加。追記入力していく

　※２：最高点表示用シート(Sheet2)
　　　　　　・テストを受けた人が、いずれかの科目で欠席したことがあれば、
　　　　　　　自動的に名前のセルの背景に色を付ける
　　　　　　・同点の人がいる場合は、同点の最初の人のみを自動的に表示
　　　　　　・科目・受験者は、※１の表に追記するにつれ、自動的にどんどん
　　　　　　　増えていく"

　※３：個人平均点表示用シート
　　　　　　・テストを受けた人が、いずれかの科目で欠席したことがあれば、
　　　　　　　自動的に点数のセルの背景に色を付ける
　　　　　　・受験者は、※１の表に追記するにつれ、自動的にどんどん増えていく
　利用ソフトは、excel2010ですが、できれば、excel2003でも可能なようにできると
ありがたい所です。
　必要であれば、マクロを使ってでも、とも思うのですが、できるだけセルの数式等
でできれば、と思っています。

ご教示の程、どうぞ、よろしくお願いします。

回答No.10

KURUMITO · ベストアンサー

回答No3,4,7,8です。
シート2のD列にはB列に該当する科目IDでの最低の点数を、またE列には科目ごとの平均点を表示させたいとのことですがその場合にはA列での受験者名は無関係になりますね。
それでも良ければシート2のD3セルには次の式を入力して下方にドラッグコピーします。

=IF(ISERROR(INDEX(Sheet1!C:C,MATCH(SMALL(Sheet1!E:E,RANK(B3*1000,Sheet1!E:E,1)+1),Sheet1!E:E,0))),"",INDEX(Sheet1!C:C,MATCH(SMALL(Sheet1!E:E,RANK(B3*1000,Sheet1!E:E,1)+1),Sheet1!E:E,0)))

E3セルには次の式を入力して下方にドラッグコピーします。

=IF(B3="","",SUMIF(Sheet1!B:B,B3,Sheet1!C:C)/COUNTIF(Sheet1!B:B,B3))

なお、式が難しいとのことですが数値が昇順で並んでいるわけでもなく、科目のIDも順序良く並んでいるわけでもなく、受験者の名前も順序良く並んでいるわけでもない場合においてはMAX関数やMIN関数を使うにしても容易ではありません。むしろ1000の数値は小さい順から並べて何番目にランクされるか、2000の値は何番目にランクされるかをRANK関数で求めています。それをもとに例えば1000番台での最大値を求める場合には、2000の数値が何番目に位置し、それより一つだけ低い順位にある数値を求め、その数値をMATCH関数で検索してシート1で該当する行を求めています。
1000番台での最小値を求める場合にはRANK関数で1000の数値が何番目にランクされるかを求めて、それよりも一つ上のランクの数値のある行をMATCH関数で求めています。

お礼

ご回答ありがとうございます。
丁寧な説明に、なるほど！と思うことが多く、エクセルの奥の深さ、面白さにはまっております。

ご指摘いただきましたように、受験者名は、無関係になってしまいますね。
受験者名列を省くように、トライしてみているのですが、式のキーとなっているため（？）でしょうか・・・？
現段階でうまくいっておらず、はまってしまっています(^^;)

一番左の列に、「科目」ＩＤを、以降、「平均」「最大点数」「最低点数」とする場合について、ご教示いただけますと幸いです。

よろしくお願いいたします。

回答No.9

tom04

No.5・6です。

（1）・エクセル内に、ボタンをつけて、押せばマクロが動くように・・・
（2）・項目名や、セルの罫線も自動的に記載できると更に・・・

とありましたので、
（1）についてはSheet1にコマンドボタンを配置し、
デザインモードで挿入したコマンドボタンをダブルクリックすると
Private Sub CommandButton1_Click()
End Sub
が表示されますので、その間に
前回のコード（1行目の「Sub 振り分け()」と最終行の「End Sub」以外をコピー＆ペーストすれば
おそらく動くと思います。
（コード内に数式を入れていますので、その部分がちょっと不安です）

（2）に関しては条件付き書式で可能です。
仮にSheet2のA～C列に自動で枠線を表示したい場合は
（Excel2003では）
A～C列すべてを範囲指定　→　書式　→　条件付き書式　→　数式が　→　数式欄に
=$A1<>""
として　→　書式　→　「罫線」タブ　→　「外枠」を選択しOK

これでA列に何らかのデータが入力されると、その行のC列まで枠線で囲まれます。m(_ _)m

お礼

ご回答、ありがとうございます。
いろいろなやり方があるのに、驚きと楽しさを感じています。

以下の点、よかったら教えていただけますとありがたいです。

　※作業用シートの自動作成、自動削除
　　　作業用シート名を「work」とした場合、自動的に作成し、見えないようにしたままで、作業後、
　　　この作業用シートを自動的に削除してしまう、などということは可能でしょうか？

　※科目ＩＤ毎の集計シートの自動作成
　　　Sheet5を自動的に作成し、一番左の列に、「科目ＩＤ」を、
　　　以降の列に、平均」「最大点数」「最低点数」と表示するには？
　　　・・・一番上の行に、項目名も自動的にはいると更にいいかと・・・

よろしくお願いいたしますm(__)m

回答No.8

KURUMITO

回答No3,4,7です。
今気が付いたのですがシート1の作業列でE3セルに入力した式を下方にドラッグコピーする際には12行目のセルよりもかなり下の行までドラッグしてください。下方には1000から6000までの数値が表示されるはずです。この数値が表示されていなかったためにシート2での表示が空白のままになっていたのですね。
シート1のE3セルへの入力の式は回答3で示しましたように次の式です。

=IF(B3<>"",B3*1000+C3,IF(ROW(A1)<=COUNT(B:B)+MAX(B:B)+1,(ROW(A1)-COUNT(B:B))*1000,""))

お礼

おかげさまで表示されました！

すごい！
論理式・数式でこのようなことができるとは！
英知・工夫も加えれば、出来そうにないこともできるようになる事もある、と感動しました！
　・・・私の例画像のSheet2で、科目２の記載違いも判りました(^^;)計算結果が正しいです。

ただ、これらの式が難しく、何の意味なのか、今の所、意味がいまいちわかっておりません。
可能でしたら、以下の点も、ご教授いただけますとありがたいです。

　・Sheet2のＤ列に科目ごとの最低点を記載
　・Sheet2のＥ列に科目ごとの平均点を記載
　　・・・拝見しますと、max,min等で計算しているだけではなさそうなので、
　　　　　自力では、どうも難しそうです。

度々申し訳ありませんが、よろしくお願いいたします。

回答No.7

KURUMITO

回答No3,4です。
シート1のH列に入力する式を回答3では示していませんでしたね。循環参照と表示されたのはシート2のB3セルに入力する式をシート1のH3セルに入力したことによるのでしょう。こちらの示した文章をよく読んでみてください。
シート1のH3セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(B$3:B3,B3)=1,MAX(H$2:H2)+1,"")

ところでシート1の作業列に表示されているE列からG列までは正しく表示されていますね。
したがってシート2での表示がされないとのことですが回答3で示した式が正しく入力されているのでしょうか？
シート2での作業内容を下記に示します。
A列が名前、B列が科目ID 、C列が点数とします。
初めにB列については科目IDを手で入力してください。
A3セルには次の式を入力して下方にドラッグコピーします。

=IF(ISERROR(INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!E:E,RANK((B3+1)*1000,Sheet1!E:E,1)-1),Sheet1!E:E,0))),"",INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!E:E,RANK((B3+1)*1000,Sheet1!E:E,1)-1),Sheet1!E:E,0)))

B3セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!H:H),"",INDEX(Sheet1!B:B,MATCH(ROW(A1),Sheet1!H:H,0)))

C3セルには次の式を入力して下方にドラッグコピーします。

=IF(ISERROR(INDEX(Sheet1!C:C,MATCH(SMALL(Sheet1!E:E,RANK((B3+1)*1000,Sheet1!E:E,1)-1),Sheet1!E:E,0))),"",INDEX(Sheet1!C:C,MATCH(SMALL(Sheet1!E:E,RANK((B3+1)*1000,Sheet1!E:E,1)-1),Sheet1!E:E,0)))

上記の作業内容は実際に試験をして正しく行われることを確認しています。

回答No.6

tom04

No.5です！
たびたびごめんなさい。

前回のコードで間違いがありました。
変更箇所を指定しても判りにくいと思いますので、もう一度コードを載せておきます。
（前回のコードは無視してください）

Sub 振り分け() 'この行から
Dim i As Long, j As Long, k As Long, cnt As Long
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
Set ws1 = Worksheets("Sheet1") '←「Sheet1」は実際のシート名に！
Set ws2 = Worksheets("Sheet2") '←　こちらのシート名も
Set ws3 = Worksheets("Sheet3") '←　こっちも！
Set ws4 = Worksheets("Sheet4") '←　追加したSheetも！
On Error Resume Next
Application.ScreenUpdating = False

For k = 2 To 3 '←Sheet2～Sheet3まで
Worksheets(k).Cells.Interior.ColorIndex = xlNone
cnt = Worksheets(k).Cells(Rows.Count, 1).End(xlUp).Row
If cnt > 1 Then
Worksheets(k).Rows(2 & ":" & cnt).ClearContents
End If
Next k

For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(ws2.Columns(2), ws1.Cells(i, 2)) = 0 Then
ws2.Cells(Rows.Count, 2).End(xlUp).Offset(1) = ws1.Cells(i, 2)
End If
If WorksheetFunction.CountIf(ws3.Columns(1), ws1.Cells(i, 1)) = 0 Then
ws3.Cells(Rows.Count, 1).End(xlUp).Offset(1) = ws1.Cells(i, 1)
End If
Next i
ws2.Columns(2).Sort key1:=ws2.Cells(1, 2), order1:=xlAscending, header:=xlYes

For j = 2 To ws2.Cells(Rows.Count, 2).End(xlUp).Row
ws1.Cells(1, 1).AutoFilter field:=2, Criteria1:=ws2.Cells(j, 2)
ws1.Columns("A:D").Copy ws4.Cells(1, 1)
ws4.Columns("A:D").Sort key1:=ws4.Cells(1, 3), order1:=xlDescending, header:=xlYes
With ws2.Cells(j, 1)
.Value = ws4.Cells(2, 1)
.Offset(, 2) = ws4.Cells(2, 3)
End With
Next j

cnt = ws3.Cells(Rows.Count, 1).End(xlUp).Row
Range(ws3.Cells(2, 2), ws3.Cells(cnt, 2)).Formula = _
"=SUMIF(Sheet1!A:A,A2,Sheet1!C:C)/COUNTIF(Sheet1!A:A,A2)"
'↑　関数内の「Sheet1」は実際のSheet名に

ws4.Cells.Clear
ws1.AutoFilterMode = False

For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
If ws1.Cells(i, 4) = "有" Then
ws4.Cells(Rows.Count, 1).End(xlUp).Offset(1) = ws1.Cells(i, 1)
End If
Next i
For k = 2 To 3
For j = 2 To Worksheets(k).Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(ws4.Columns(1), Worksheets(k).Cells(j, 1)) Then
If k = 2 Then
Worksheets(k).Cells(j, 1).Interior.ColorIndex = 6
Else
Worksheets(k).Cells(j, 2).Interior.ColorIndex = 6
End If
End If
Next j
Next k
ws4.Cells.Clear
Application.ScreenUpdating = True
End Sub 'この行まで

操作方法は前回同様です。
検証せずに投降してごめんなさいね。m(_ _)m

お礼

ご丁寧に、ありがとうございます。
やってみたら、うまくいきました(^^)

・エクセル内に、ボタンをつけて、押せばマクロが動くように・・・
・項目名や、セルの罫線も自動的に記載できると更に・・・

などと欲が出てきますね。
これも、ソフトの面白さでしょうか。

回答No.5

tom04

こんばんは！
VBAになってしまいますが、一例です。
各Sheetに数式が入っている場合
マクロを実行してしまうと、数式そのものが消えてしまいますので、
別Bookで↓のマクロを試してみてください。

まず前提条件として、
・Sheet4を作業用のSheetとして使っていますので、
　新規BookにはSheetを追加してSheet4まで配置してください。
・↓の画像のように各Sheetは1行目がタイトル行で実データは2行目以降にあるとする。
・Sheet1～Sheet3の1行目項目は入力済み。
・Sheet名が違う場合は動作しませんので、Sheet1～Sheet4は実状に合わせてください。
（今回はSheet1・Sheet2・Sheet3・Sheet4　としています）
・Sheet見出し上の配置は左側からSheet1・・・Sheet4とします。
・Sheet2・Sheet3に条件付き書式により「塗りつぶし」設定があると
　色変更がマクロではできませんので、条件付き書式の「塗りつぶし」は削除しておいてください。

上記条件で・・・
Alt＋F11キー　→　メニュー　→　挿入　→　「標準モジュール」　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub 振り分け() 'この行から
Dim i As Long, j As Long, k As Long, cnt As Long
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
Set ws1 = Worksheets("Sheet1") '←「Sheet1」は実際のシート名に！
Set ws2 = Worksheets("Sheet2") '←　こちらのシート名も
Set ws3 = Worksheets("Sheet3") '←　こっちも！
Set ws4 = Worksheets("Sheet4") '←　追加したSheetも！
On Error Resume Next
Application.ScreenUpdating = False
For k = 2 To 3
Worksheets(k).Cells.Interior.ColorIndex = xlNone
cnt = Worksheets(k).Cells(Rows.Count, 1).End(xlUp).Row
If cnt > 1 Then
Worksheets(k).Rows(2 & ":" & cnt).ClearContents
End If
Next k
For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(ws2.Columns(2), ws1.Cells(i, 2)) = 0 Then
ws2.Cells(Rows.Count, 2).End(xlUp).Offset(1) = ws1.Cells(i, 2)
End If
If WorksheetFunction.CountIf(ws3.Columns(1), ws1.Cells(i, 1)) = 0 Then
ws3.Cells(Rows.Count, 1).End(xlUp).Offset(1) = ws1.Cells(i, 1)
End If
Next i
ws2.Columns(2).Sort key1:=ws2.Cells(1, 2), order1:=xlAscending, header:=xlYes

For j = 2 To ws2.Cells(Rows.Count, 2).End(xlUp).Row
ws1.Cells(1, 1).AutoFilter field:=2, Criteria1:=ws2.Cells(j, 2)
ws1.Columns("A:D").Copy ws4.Cells(1, 1)
cnt = ws4.Cells(Rows.Count, 1).End(xlUp).Row
Range(ws4.Cells(2, 1), ws4.Cells(cnt, 3)).Sort key1:= _
ws4.Cells(1, 3), order1:=xlDescending, header:=xlGuess
With ws2.Cells(j, 1)
.Value = ws4.Cells(2, 1)
.Offset(, 2) = ws4.Cells(2, 3)
End With
Next j

cnt = ws3.Cells(Rows.Count, 1).End(xlUp).Row
Range(ws3.Cells(2, 2), ws3.Cells(cnt, 2)).Formula = _
"=SUMIF(Sheet1!A:A,A2,Sheet1!C:C)/COUNTIF(Sheet1!A:A,A2)"
'↑　関数内の「Sheet1」は実際のSheet名に

ws4.Cells.Clear
ws1.AutoFilterMode = False

For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
If ws1.Cells(i, 4) = "有" Then
ws4.Cells(Rows.Count, 1).End(xlUp).Offset(1) = ws1.Cells(i, 1)
End If
Next i
For k = 2 To 3
For j = 2 To Worksheets(k).Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(ws4.Columns(1), Worksheets(k).Cells(j, 1)) Then
If k = 2 Then
Worksheets(k).Cells(j, 1).Interior.ColorIndex = 6
Else
Worksheets(k).Cells(j, 2).Interior.ColorIndex = 6
End If
End If
Next j
Next k
ws4.Cells.Clear
Application.ScreenUpdating = True
End Sub 'この行まで

※　数式ではないのでSheet1のデータ変更がすぐに各Sheetに反映されません。
Sheet1の変更があるたびにマクロを実行してみてください。
（複数データ変更の場合はすべて変更後にマクロを実行した方がよいかもしれません）

参考になりますかね？m(_ _)m

お礼

ありがとうございます

回答No.4

KURUMITO

回答No3です。
シート2ではB列の科目IDは手で1，2，3，4，5のように入力する必要があります。
でないとA列やC列も空白のままです。
もしも科目IDまでも自動にしたいのでしたらシート1のH列に作業列を設けます。
シート1のH3セルには次の式を入力して下方にドラッグコピーします。
その後にシート2のB3セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!H:H),"",INDEX(Sheet1!B:B,MATCH(ROW(A1),Sheet1!H:H,0)))

こうすることで科目IDを入力する必要もなくなりますね。シート1の表だけにデータを入力することでシート2や3には自動的にデータが表示されます。

補足

ご丁寧な回答ありがとうございます。

試してみましたところ、ワークの部分を含め、Sheet1は、下記のように表示されました。

Ｈ列は、「循環参照」と表示されています。
また、Sheet2は、Ｈ列を入れる前にＢ列に科目ＩＤを手入力しても、名前と点数が
表示されないようです・・・

度々申し訳ありませんが、お知恵を拝借できますとありがたいです。
よろしくお願いいたします。

※１：テスト結果(Sheet1)
名前 科目ID 点数 欠席
太郎　　1　　100　　有　　1100　　1　太郎有　　　0
太郎　　2　　80　　　　　　2080　　　　太郎　　　　0
太郎　　3　　20　　　　　　3020　　　　太郎　　　　0
次郎　　1　　50　　　　　　1050　　2　 次郎　　　　0
次郎　　2　　100　　　　 　2100　　　　次郎　　　　0
次郎　　3　　60　　　　　　3060　　　　次郎　　　　0
次郎　　4　　80　　　　　　4080　　　　次郎　　　　0
次郎　　5　　70　　　　　　5070　　　　次郎　　　　0
三郎　　1　　100　　　　　1100　　3　　三郎　　　　0
三郎　　2　　95　　　有　　2095　　　　三郎有　　 0
Ａ列　Ｂ列　Ｃ列　Ｄ列　　 Ｅ列　 Ｆ列　Ｇ列　　　Ｈ列

回答No.3

KURUMITO

関数が複雑になりますので作業列を作って対応します。
なお、シート1からシート3では2行目に項目名が有り、3行目から下方にデータがシート1では入力されシート2や3では表示されるとします。また、科目のIDは必ずお示しのような数値で入力されるものとします。
シート1のE3セルには次の式を入力して下方にドラッグコピーします。

=IF(B3<>"",B3*1000+C3,IF(ROW(A1)<=COUNT(B:B)+MAX(B:B)+1,(ROW(A1)-COUNT(B:B))*1000,""))

F3セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(A$3:A3,A3)=1,MAX(F$2:F2)+1,"")

G3セルには次の式を入力して下方にドラッグコピーします。

=A3&D3

これらの作業列が目障りでしたらそれらの列を選択して右クリックして「非表示」を選択すればよいでしょう。
次にシート2に移ります。
A列が名前、B列が科目ID 、C列が点数とします。
初めにB列については科目IDを手で入力してください。
A3セルには次の式を入力して下方にドラッグコピーします。

=IF(ISERROR(INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!E:E,RANK((B3+1)*1000,Sheet1!E:E,1)-1),Sheet1!E:E,0))),"",INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!E:E,RANK((B3+1)*1000,Sheet1!E:E,1)-1),Sheet1!E:E,0)))

C3セルには次の式を入力して下方にドラッグコピーします。

=IF(ISERROR(INDEX(Sheet1!C:C,MATCH(SMALL(Sheet1!E:E,RANK((B3+1)*1000,Sheet1!E:E,1)-1),Sheet1!E:E,0))),"",INDEX(Sheet1!C:C,MATCH(SMALL(Sheet1!E:E,RANK((B3+1)*1000,Sheet1!E:E,1)-1),Sheet1!E:E,0)))

次にシート3に移ります。
A列が名前でB列は平均点数です。
A3セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!F:F),"",INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!F:F,0)))

B3セルには次の式を入力して下方にドラッグコピーします。

=IF(A3="","",AVERAGEIF(Sheet1!A:A,A3,Sheet1!C:C))

小数点以下の表については適宜調整してください。

背景に色を付ける件ですがシート2ではA列を選択したのちに条件付き書式の設定で「数式を・・」で数式の窓には次の式を入力します。

=COUNTIF(Sheet1!G:G,A1&"有")>0

「書式」で塗りつぶしのタブから色を選択してOKします。

シート3ではB列を選択したのちにシート2と同じ式を入力して条件付き書式を選定します。

以上のようにすることでシート1にデータが追加されればシート2や3には自動的にデータが更新されます。
シート2の科目IDも自動にするのでしたらシート3での名前の表示と同じようにシート1に作業列を作って対応すればよいでしょう。
今後データが多くなることを考慮すれば複雑な配列数式などを使って対応することよりも作業列を使って対応することをお勧めします。また、マクロで対応するよりも迅速な対応が可能でしょう。

お礼

回答ありがとうございます。
ご教示いただきました、式をコピペしてみました。
Sheet3はうまくいったのですが、Sheet2は、空白のままです。
　　・・・私のやり方がわるいのかもしれませんが・・・

お気づきの点がありましたら、お知らせいただけますとありがたいです。

回答No.2

foomufoomu

タイトルの、「セルの背景色」をつけることは、それほど難しくありません。ワークシート関数でもできます。
問題は、「科目・受験者は・・・自動的にどんどん増えていく」の部分で、これは基本的にマクロでないとできません。Excelは「データの抽出」＝ある条件に合うデータだけを抜き出して表示する。のは、とても苦手なのです。

次の１１番あたりから、マクロを使ったデータ抽出の例があるので、参考にしてください。
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/index.html

マクロに慣れてないと、けっこう難しい処理ですよ。シート1の入力形式を「ランダムに入力」でなく、「同じ人のデータは同じ行に書く」ことにしたほうがよいと思います。

お礼

ご回答、ありがとうございました

挑戦してみたいです。
　・・・難しそうですが

回答No.1

soixante

これはマクロで処理するほうが適切に思いますよ。

例示されている画面で

欠席「有」の人に点数があるのはなぜでしょう。

太郎は科目１を欠席したという意味かと思ったのに点数が入っています。
そのあたりよく分かりません。

補足

回答ありがとうございます。

説明不足ですいません。
太郎くんは、授業を欠席したことがある＝「欠席有」、という意味で、「有」と記載しました。

マクロについても、よかったら、ご教示いただけますとありがたいです。