Excelの関数について

質問

Excelに関する質問です。チーム戦の成績表を作成したいと思っています。１チーム6名の編成で、各チーム（全10チーム）の上位３名の合計を計算し、順位を出したいのですが、どのような式にすればよいのでしょうか？

回答No.6

noname#64582 · ベストアンサー

再びお邪魔します。＃３の者です。

＞このチーム戦の入賞者(3名）の名前がわかるようにできますでしょうか？
・・・やはり知りたいですよね(笑）。
となると、私の技術では、関数による処理で各チーム毎の上位３人の名前を取り出すのは無理なので、やっぱりマクロかとなります。

そこで以下をご提案します。
・A1セルを「チーム名」、B1 「選手名」、C1 「得点」とする。
・2行目以降、各地の担当者が送ってきたらどんどん入力していく
こうして、データのシートが出来ました。そのうえで、以下のコードを実行すれば一覧表になると思います（テスト済）。
●●●万全を期して、元ファイルは保存の上、コピーしたファイルでテストしてください。

念のため作業法も記します。
【1】エクセルから、Alt+F11でVBE画面を開き、Alt+I、M で標準モジュールを開きます。
【2】真っ白な画面が開きますが、そこに以下のコードをコピペします。（冒頭にOption Explicitの表記があれば、その下に）
【3】エクセル画面に戻り、データのシートを選択した状態から、Alt+F8　をして、実行したいマクロ名をダブルクリックしてやれば作動します。
【補足】モジュールへの貼付は１回だけで結構です。後は、データをどんどん入れていく都度にでも、データシートから、Alt+F8 でマクロを呼び出してやれば、いつでも使えます。

以下はコードです。２パターンやってみました。２通り試してみて結果を見てみてください。
なお、コピペの際は、両方とも同じモジュールに貼って構いません。

↓↓↓↓↓↓↓↓↓　ここからコピー　　↓↓↓↓↓↓↓↓↓
Sub 整列上位三人()
ActiveSheet.Copy After:=ActiveSheet
Cells(1, 1).Sort _
Key1:=Cells(1, 1), _
Order1:=xlAscending, _
Key2:=Cells(1, 3), _
Order2:=xlDescending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin

Dim p As Long
p = 2

Do
If Cells(p, 1).Value = 0 Then
Exit Do
ElseIf Cells(p, 1).Value <> 0 Then
Cells(p, 4).Value = Application.WorksheetFunction. _
CountIf(Range(Cells(2, 1), Cells(p, 1)), Cells(p, 1))
End If
p = p + 1
Loop

Cells(1, 4).Value = "順位"

Cells(1, 1).AutoFilter _
Field:=4, _
Criteria1:="<=3", _
Operator:=xlAnd, _
VisibleDropdown:=True

Cells(1, 1).CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
Worksheets.Add
ActiveSheet.Paste

With ActiveWindow
.Zoom = 85
.DisplayGridlines = False
End With

With Cells(1, 1)
.CurrentRegion.Borders.ColorIndex = 15
.Select
End With
End Sub

Sub 全員整列()

ActiveSheet.Copy After:=ActiveSheet
Cells(1, 1).Sort _
Key1:=Cells(1, 1), _
Order1:=xlAscending, _
Key2:=Cells(1, 3), _
Order2:=xlDescending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin

Dim p As Long, q As Long
Dim StartRow As Long
StartRow = 2
q = 0

For p = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(p + 1, 1).Value <> Cells(p, 1).Value Then
Range(Cells(StartRow, 1), Cells(p, 3)).Cut _
Destination:=Cells(2, 3 * q + 1)
StartRow = p + 1
q = q + 1
End If
Next

Range(Cells(1, 1), Cells(1, 3)).Copy

For p = 4 To Cells(2, 1).End(xlToRight).Column Step 3
Cells(1, p).PasteSpecial Paste:=xlPasteAll
Next

With ActiveWindow
.Zoom = 85
.DisplayGridlines = False
End With
Cells(1, 1).CurrentRegion.Borders.ColorIndex = 15

Dim r As Long, c As Long
r = Cells(1, 1).CurrentRegion.Rows.Count
c = Cells(1, 1).CurrentRegion.Columns.Count

For p = c To 3 Step -3
Range(Cells(1, p - 2), Cells(r, p)).BorderAround _
LineStyle:=xlContinuous
Next
Cells(1, 1).Select
End Sub
↑↑↑↑↑↑↑↑　ここまでコピー　↑↑↑↑↑↑↑↑
ご希望の形でなければすみません。
よろしくお願いします。

お礼

ご回答ありがとうございます。
先日ご回答いただいた分でいろいろと試してみました。＃ＮＵＭ！が表示されるので、表示しないようにする方法を探したり（中途半端で終わりましたが…）その他、いろいろ勉強しましたが、なかなか理解できないところが多くて困っております。
今回のご回答は私にとってチョット手強そうです。　やはりマクロですか…　兎に角、説明通りにできるかどうかやってみます。
素人の私に解りやすく説明していただき、本当にありがとうございます。

回答No.5

kantogun

osaka1458様へ
データベースがないと分かりませんが、通常合計計算後順位はRANK関数を使う筈です。上記内容から判断するとフィルターオブションを使って抽出後RANK関数を使われたらどうですか。その逆の可能性もあり

回答No.4

noname#64582

＃３の者です。すみません、追加です。
さきほどの回答の、F２セルに式を入力する前に以下の一文を書き忘れました。

・E2から下にチーム名を羅列。順不同で構いません。

よろしくお願いします。
なお、Ctrl + Shift + Enter で確定させた後の式には、両端に　{ 　　 } が付きますが異常ではありません。それが配列数式の印です。

お礼

ご回答、本当にありがとうございます。私にとっては難しそうですが、頑張って挑戦してみます。Excelの各種マニュアル（データベース、マクロなど）を買ってきましたが、なかなか難しいです。本を読んで勉強することも重要ですが、このコーナーは非常に助かります。感謝いたします。

補足

チーム戦の成績表を作ることが出来ました。これは便利です。ビックリしました。これで、慌てなくても正確なチーム成績の集計ができます。ありがとうございました。
ところで、もう一点、お聞きしたいのですが、このチーム戦の入賞者(3名）の名前がわかるようにできますでしょうか？（成績から判読出来ますが…）　再度の質問で申し訳ございません。よろしくお願いいたします。

回答No.3

noname#64582

＃２で回答した者です。＃２補足に則って考えますと、
・各チームの人数はバラバラ
・成績表にランダムに（各地の担当者が送ってきた順に？）記入
・それでまとめて集計したい

ということでしょうか。それでは以下のような方法も出来ます。

・A1セルを「チーム名」、B1 「選手名」、C1 「得点」とする。
・2行目以降、各地の担当者が送ってきたらどんどん入力していく
・もちろんチーム別に入力する必要なし。各チームが何人でも構わない。とにかく入れてく。
今回は60名のデータ入力をしたとします。つまり最終行は61。（データ数が異なるなら、ここは数式で61のところを適宜アレンジください）

入力後の処理（同シートに結果を出してみます。他シートならアレンジを）

・E1セルに「チーム名」、F1「１位」、G1「2位」、H1「3位」、I1「上位3人合計」、J1「順位」。

・F2セルに
=LARGE(IF($A$2:$A$61=$E2,$C$2:$C$61,""),COLUMN(A1))
と入れて、Enterを押さずに、
Ctrl + Shift + Enter で確定させてください。

・そのあと右、そして下に引っ張ります。F2:H11　に。
・あとは一緒。
I2　に SUM(F2:H2) 、J2　に　RANK(I2,$I$2:$I$11) とする。下へ引っ張る。終わり。

【ご参考】
※配列数式
http://pc.nikkeibp.co.jp/pc21/special/hr/
ご存知でなければご一読ください。概念をご存知でしたら、
第3回あたりは今回のケースに近いでしょう。

並び替えをしてもそんなに手間はかからないと思いますが、並び替えを使わないなら私なら上記のように処理します。

回答No.2

noname#64582

並び替えを用いないならばこんな方法もあります。

まず、縦に10チーム、すなわち60名の成績を並べます。

・A1セルを「チーム名」、B1 「選手名」、C1 「得点」とする。
・A2～A7 を「チームＡ」、A8～A13 を「チームB」のように、６行づつ入力。
したがって、最後は、A56～A61　で、「チームJ」となります。
・B列はそれぞれの選手名が入ります。
・C列はそれぞれの得点が入ります。

それで、
・E1セルに「チーム名」、F1「１位」、G1「2位」、H1「3位」、I1「上位3人合計」、J2「順位」。

・E2から下に、A列に入れた順番どおりにチーム名を羅列。
　E2がチームA、E3がチームB、・・・、E11がチームJ。

・F2セルに
=LARGE(OFFSET($C$2,6*ROW(A1)-6,0,6,1),COLUMN(A1))
と入力し、右に引っ張り、さらに下に引っ張る。
（F2：H11　の範囲に）

そしたら、各チームの上位3人の点数が出てきますので、

・I2セルに、
=SUM(F2:H2) とする。

・J2セルに、
=RANK(I2,$I$2:$I$11) とする。

I2:J2を選択して、11行目まで引っ張れば終わりです。

形式が違えばいろいろ方法もありそうですが、しょっちゅう作業するようでなければ、手でやってもいいかもしれません。

補足

ご回答ありがとうございます。非常によく解りました。当方の質問に不十分な部分がありましたので、再度、ご質問させて下さい。
１チームは10名と書きましたが、ルール上は3名で１チームとなっています。従って、チームにより5名があったり、8名があったりと、バラバラです。また、チームといっても、選手は各地に散らばり、各地の担当者が成績を送ってきます。集計作業は成績表にランダムに書き込んでいきます。全成績を入力後、その中から、チーム別に上位3名を抽出し、成績を集計します。再度のご質問になりますが、よろしくお願いいたします。

回答No.1

uma79

範囲指定の上、データ＞並び替えのサブウィンドーで得点のある列を
指定します。
（降順指定が普通ですね、上位が上になります）
１０チームですから１０回行えば整列終了ですね。

そっくり、次のシートに値のみのコピー（V指定）、
そのシートにはあらかじめ、上位３位までを合計する計算式を入れておく。

以上で手間ですが、出来ますよね。
そしたら、マクロオンにして、もう一度行って自動記録させます。
出来上がったところでマクロオフです。

そのマクロを最初のシートにボタンから呼べるようにすると便利です。

お礼

ご回答ありがとうございます。勉強不足も多々あり、マクロについてはまだよく解っておりません。マクロの勉強が必要ですね。ありがとうございました。