エクセル 最高値（MAX)に印をつけたい。

下のように入力されています。

A　　　　B　　　　C

１　　　１０
４　　　１０
５　　　１０
３　　　１０
５　　　１０
１　　　１０
２　　　１１
４　　　１１
５　　　１１
５　　　１１
２　　　１１


B列が１０のときのA列の最高値は５ですので、C列に１０と表示させたいのです。
B列が１１のときも同様です。

結果としては下のようになります。

A　　　　B　　　　　C

１　　　１０
４　　　１０
５　　　１０　　　１０
３　　　１０
５　　　１０　　　１０
１　　　１０
２　　　１１
４　　　１１
５　　　１１　　　１１
５　　　１１　　　１１
２　　　１１


しかし、C列には複数の「１０」が表示されます。このうち下方にあるセルつまり行番号が大きな方のセルにのみ「１０」を表示させたいのです。
「１１」も同様にひとつだけ表示させたいのです。

A　　　　B　　　　　C

１　　　１０
４　　　１０
５　　　１０
３　　　１０
５　　　１０　　　１０
１　　　１０
２　　　１１
４　　　１１
５　　　１１
５　　　１１　　　１１
２　　　１１

結果的には上のようにしたいのですが、C列にはどんな関数を入力したらよいのでしょうか？

お時間が許す方でご存知の方にご教授いただきたいと思います。

No.1 ベストアンサー 回答者： imogasi 回答日時： 2004/11/19 23:06

これは関数では難しい問題ですね。

なりふりかまわず配列数式と中間作業列を使ってやっと出来ました。（VBAなどならもう少し素直なロジックで出来そうですが）
（例データ）A1:E11。だだし基データはA2：B11。
A列　　　B列　　　C列　　　D列　　　E列
1105
4105
510510
3105
51051010
1105
2115
4115
511511
51151111
2115
（関数式）
C1に=MAX(IF($B$1:$B$11=B1,$A$1:$A$11,""))といれて
SHIFTキーとCTRLキーを押しつつ、ENTERを押す。
C11まで複写する。
結果は上記C列の通り。
（配列数式）
D1に=IF(A1=C1,B1,"")と入れてD11まで複写する。
結果は上記D列の通り。
E1に=IF(AND(D1<>"",COUNTIF($D$1:$D$11,B1)=COUNTIF($D$1:D1,B1)),B1,"")と入れてE11まで複写する。
結果は上記E列の通り。求める結果です。
C,D列は目立たない右の方の列に設定しても可。

この回答へのお礼

御礼が遅くなり申し訳ありませんでした。関数を組み合わせるといろいろなことができますね。ありがとうございました。

お礼日時：2004/11/28 16:10

No.6 回答者： Raistlin 回答日時： 2004/11/21 19:13

#3です。

書き漏らしがありました。

#3のコードはVer97で作成しています。

B列の値は、隣接するもののみを同じグループとみなします。

#3のコードはセルを直接検索しているので、対象範囲が大きくなると若干遅くなります。
Application.Volatileの前に
Dim vRng1 As Variant '値1を格納
Dim vRng2 As Variant '値2を格納
の2行を、後に
'値1、2を配列に取り込み
vRng1 = Rng1
vRng2 = Rng2
の3行を加えた後に、
Rng1(r, c)およびRng2(r, c)をそれぞれvRng1(r, c)およびvRng2(r, c)に置き換えると(r,cは元の値に合わせてください 括弧の無いRng1,Rng2は変更しません)もっさり感が減ります。


#5の「For i = 2 To d」は、「For i = 1 To d」の間違いじゃないかな・・・。

No.5 回答者： imogasi 回答日時： 2004/11/21 09:45

#1,#4です。

ＶＢＡの解を１つ。
Ｂ列のコードが１から１００００までの「数値」で有る場合の解法。
この問題に適しているロジックであり、多分ＶＢＡコードの一番行数が少ない解法であり、ソートもせず、関数も使わず、一回だけ全行読めば、処理が終わる。
文字列であればもう少し複雑に修正しないといけません。
Sub test01()
'---Ｂ列数値ごとの、最高値と、その存在する行を記憶する
Dim m(10000), mr(10000)
d = Range("a65536").End(xlUp).Row
'-----配列初期化
For j = 1 To UBound(m)
m(j) = ""
mr(j) = ""
Next j
'---
For i = 2 To d
'-----Ｂ列初出か。ならば最高値、最高値行を自分とする
If m(Cells(i, "B")) = "" Then
Cells(i, "C") = Cells(i, "B")
mr(Cells(i, "B")) = i
m(Cells(i, "B")) = Cells(i, "A")
Else
'----Ｂ列２度目以後,今までの最高以上なら、最高の地位を奪う
If Cells(i, "A") >= m(Cells(i, "B")) Then
Cells(i, "c") = Cells(i, "B")
m(Cells(i, "B")) = Cells(i, "A")
Cells(mr(Cells(i, "B")), "C") = ""
mr(Cells(i, "B")) = i
End If
End If
Next i
End Sub

No.4 回答者： imogasi 回答日時： 2004/11/21 08:59

＃１です。

データが今以上増えない時は、増えてもその都度作業をする覚悟の場合は下記が判りやすく、良さそうです。
データはＡ２以下に入っているとする。
（作業列）どこでも良いかＣ列とする。
第1行は空白として、Ｃ２に１、Ｃ３に２といれ、Ｃ２：Ｃ３を範囲指定して、＋ハンドルを出し、最終行まで引っ張る。行順に連番が入ります。
（ソート、データ－並べ替え）
Ａ２：Ｃ１１を範囲指定し、
第１キー列Ｂ昇順
第２キー列Ａ昇順
第３キー列Ｃ昇順
でソートします
1101
1106
3104
4102
5103
5105
2117
4118
5119
51110
となります。
Ｄ２に=IF(B2=B3,"",B2)といれ、最終行まで複写します。
Ｄ７が１０、Ｄ１１が１１になります。
Ｄ２：Ｄ１１について、自身のセルに形式を選択－値で
貼りつけて、Ｄ列の関数式を消します。
（ソート）Ａ２：Ｄ１１をＣ列で昇順でソートします。
（結果）Ａ２：Ｄ１１
1101
4102
5103
3104
510510
1106
2117
4118
5119
5111011
Ｃ列は削除するもよし。
以上をマクロ化すると
Sub Macro1()
d = Range("a65536").End(xlUp).Row
'--------
Range("C2") = 1
Range("C3") = 2
Range("C2:C3").Select
Selection.AutoFill Destination:=Range(Cells(2, "C"), Cells(d, "C")), Type:=xlFillDefault
'-------
Range(Cells(2, "A"), Cells(d, "C")).Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("A2") _
, Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending
For i = 1 To d
If Cells(i, "B") = Cells(i + 1, "B") Then
Else
Cells(i, "D") = Cells(i, "B")
End If
Next i
'------
Range(Cells(2, "A"), Cells(d, "D")).Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending
End Sub

No.3 回答者： Raistlin 回答日時： 2004/11/20 18:06

お呼びで無いかも知れませんが、ユーザー定義関数での作例を書き込みます。

=fndgrpmax($A$1:$A$11,$B$1:$B$11)
のようにして呼び出してください。
暴走はなさそうなので、特にエラー処理はしていません。
長くなるのでコメントも最小限です。
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋
Option Base 1
Function FndGrpMax(Rng1 As Range, Rng2 As Range)
Dim CurRow As Long '関数が書き込まれた相対行位置
Dim vCurGrpVal As Variant '現在グループの種類格納
Dim dCurGrpMax As Double '現在グループの最大値格納
Dim lGrpMaxRow As Long '現在グループの最大値相対行格納
Dim lGrpFr As Long '値2グループの始まり格納
Dim lGrpEn As Long '値2グループの終わり格納
Dim i As Long 'ループカウンタ

Application.Volatile
'関数が書き込まれた行の相対位置と属するグループ値を取得
CurRow = Application.Caller.Row - Rng2.Row + 1
vCurGrpVal = Rng2(CurRow, 1)
'同じグループ値が続く範囲を上方向に検索して設定
lGrpFr = CurRow
For i = CurRow To 2 Step -1
If Rng2(i - 1, 1) = vCurGrpVal Then
lGrpFr = i - 1
Else
Exit For
End If
Next i
'同じグループ値が続く範囲を下方向に検索して設定
lGrpEn = CurRow
For i = CurRow To Rng2.Rows.Count - 1
If Rng2(i + 1, 1) = vCurGrpVal Then
lGrpEn = i + 1
Else
Exit For
End If
Next i
'同グループ内での最大値を求める
With Rng1
dCurGrpMax = Application.WorksheetFunction.Max(.Range(.Cells(lGrpFr, 1), .Cells(lGrpEn, 1)))
End With
'同グループ内を上から順に見て行き、最大値に一致したらグループ最大値相対行を更新
For i = lGrpFr To lGrpEn
If Rng1(i, 1) = dCurGrpMax Then lGrpMaxRow = i
Next i
'グループ最大値相対行と関数書き込み相対行を比較して関数の戻り値を設定
If CurRow = lGrpMaxRow Then
FndGrpMax = vCurGrpVal
Else
FndGrpMax = ""
End If
End Function
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋
作業列がいらないぐらいしかメリット無いかも。

この回答へのお礼

御礼が遅くなり申し訳ありませんでした。自分で関数が作れたらよいですね。ＶＢＡがなかなか難しいのでゆっくり理解していきます。ありがとうございました。

お礼日時：2004/11/28 16:17

No.2 回答者： misatoanna 回答日時： 2004/11/19 23:45

Ｃ列だけの処理はわかりませんので、作業列を使いますが.....、

例題のように、最高値に対応するデータ全体を見て、同じ数値の中で最初に現われ
るデータを表示する場合でしたら、
C1 =IF(RANK($A$1:$A$11,$A$1:$A$11)=1,$B$1:$B$11,"")
D1 =IF(COUNTIF($C$1:C1,C1)=1,C1,"")
で、いずれも下方にコピーしてＤ列を使います。

もし、最高値に対応するデータが 11,10,10,12,10 のように並ぶこともあり、直前
に表示された最高値データと異なるたびに表示する(下表参照)場合は、
C1 =IF(RANK($A$1:$A$11,$A$1:$A$11)<>1,"",$B$1:$B$11)
C2 =IF(RANK($A$1:$A$11,$A$1:$A$11)=1,$B$1:$B$11,C1)　で下方にコピー
D1 =C1
D2 =IF(C2=C1,"",C2)　で下方にコピー
で、いずれも下方にコピーしてＤ列を使います。

＜下表＞
　 A 　　B　　　取得結果
　１　　１０
　４　　１０
　５　　１１　　１１
　３　　１０
　５　　１０　　１０
　１　　１０
　５　　１０
　４　　１１
　５　　１２　　１２
　５　　１０　　１０
　２　　１１

この回答へのお礼

御礼が遅くなり申し訳ありませんでした。参考にさせていただきました。ありがとうございました。

お礼日時：2004/11/28 16:13