表中から最大値と最小値の見出しを求める方法を教えて

質問

エクセルで作成した表があります。
表中から行の最大値と最小値の列見出しを求める方法を教えてください。

表は、行に1から1000、列に(1)～(10)とそれぞれ見出しをつけ、表の中には1.000から1.500のランダムな数字があります。
行の番号をB2セルに入れると最大値と最小値の列の見出し｛例えば“(5)”というように…）を求めたいのです。行の数字は複数同じ値がある場合もあります。

良い方法がありましたら、教えてください。
よろしくお願いします。

回答No.2

cj_mover · ベストアンサー

こんにちは。お邪魔します。

例えば、

　=INDEX($D$2:$K$2,1,MATCH(MAX(INDEX($D$3:$K$1002,$B$2,0)),INDEX($D$3:$K$1002,$B$2,0),0))
　=INDEX(列見出し範囲,1,MATCH(MAX(INDEX(［データ範囲］,$B$2,0)),INDEX(［データ範囲］,$B$2,0),0))

　=INDEX($D$2:$K$2,1,MATCH(MIN(INDEX($D$3:$K$1002,$B$2,0)),INDEX($D$3:$K$1002,$B$2,0),0))
　=INDEX(列見出し範囲,1,MATCH(MIN(INDEX(［データ範囲］,$B$2,0)),INDEX(［データ範囲］,$B$2,0),0))

のような感じでしょうか。

> 行の番号をB2セルに入れると最大値と最小値の列の見出し｛例えば“(5)”というように…）を求めたいのです。

ここまではいいとして、

> 行の数字は複数同じ値がある場合もあります。

最大値・最小値が重複している場合は、
どうしたいのか、書いてありませんから悩ましいですね。

上の数式は、最大値・最小値が重複している場合でも、
最も左にある列見出し、ひとつ、を返します。
それでも良ければ普通に数式で解決できる訳ですが、
数式での回答が未だに付かない処をみると、
「最大値・最小値が重複している場合、該当する列見出しを列挙したものを返したい」
と読むのが、どうやら皆さんの解釈のようです。

関数だけでは無理なんじゃないでしょうか。
私には出来ないってだけかも知れませんが、
関数書ける人がいらっしゃったら私も勉強させて頂きたいです。

///

私に出来ることとして、
最大値・最小値が重複している場合、該当する列見出しを列挙したものを返す
ユーザー定義関数を書いてみました。

' ' 以下、【標準モジュール】に過不足なくコピペ
' ' ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

Option Explicit

　　Private Const MAXNUM As Double = 1.5
　　Private Const MINNUM As Double = 1

' '　　―――――――――――――――――――――――――――

Function MaxLookOver(データ範囲 As Range, ByVal 相対行位置 As Long, _
　　　　　　　　　　　　Optional ByVal 区切り文字 As String)
　　Dim mtxData()
　　Dim mtxFieldName()
　　Dim sBuf As String
　　Dim dblTarget ' As Double
　　Dim dblTemp ' As Double
　　Dim arrnIdxTarget() As Long
　　Dim tnXSize As Long
　　Dim cnEqv As Long
　　Dim i As Long

　　If データ範囲.Row < 2 Then
　　　　MaxLookOver = "#データ範囲!"
　　　　Exit Function
　　End If

　　mtxFieldName() = データ範囲.Rows(1).Offset(-1).Value
　　mtxData() = データ範囲.Value
　　tnXSize = UBound(mtxData, 2)
　　dblTarget = MINNUM

　　For i = 1 To tnXSize
　　　　dblTemp = mtxData(相対行位置, i)
　　　　Select Case dblTemp
　　　　Case Is > dblTarget
　　　　　　dblTarget = dblTemp
　　　　　　cnEqv = 0&
　　　　　　ReDim arrnIdxTarget(cnEqv)
　　　　　　arrnIdxTarget(cnEqv) = i
　　　　Case dblTarget
　　　　　　cnEqv = cnEqv + 1&
　　　　　　ReDim Preserve arrnIdxTarget(cnEqv)
　　　　　　arrnIdxTarget(cnEqv) = i
　　　　End Select
　　Next i
　　Erase mtxData()

　　sBuf = mtxFieldName(1, arrnIdxTarget(0))
　　For i = 1 To cnEqv
　　　　sBuf = sBuf & 区切り文字 & mtxFieldName(1, arrnIdxTarget(i))
　　Next i
　　Erase mtxFieldName(), arrnIdxTarget()

　　MaxLookOver = sBuf
End Function

' '　　―――――――――――――――――――――――――――

Function MinLookOver(データ範囲 As Range, ByVal 相対行位置 As Long, _
　　　　　　　　　　　　Optional ByVal 区切り文字 As String)
　　Dim mtxData()
　　Dim mtxFieldName()
　　Dim sBuf As String
　　Dim dblTarget ' As Double
　　Dim dblTemp ' As Double
　　Dim arrnIdxTarget() As Long
　　Dim tnXSize As Long
　　Dim cnEqv As Long
　　Dim i As Long

　　If データ範囲.Row < 2 Then
　　　　MinLookOver = "#データ範囲!"
　　　　Exit Function
　　End If

　　mtxFieldName() = データ範囲.Rows(1).Offset(-1).Value
　　mtxData() = データ範囲.Value
　　tnXSize = UBound(mtxData, 2)
　　dblTarget = MAXNUM

　　For i = 1 To tnXSize
　　　　dblTemp = mtxData(相対行位置, i)
　　　　Select Case dblTemp
　　　　Case Is < dblTarget
　　　　　　dblTarget = dblTemp
　　　　　　cnEqv = 0&
　　　　　　ReDim arrnIdxTarget(cnEqv)
　　　　　　arrnIdxTarget(cnEqv) = i
　　　　Case dblTarget
　　　　　　cnEqv = cnEqv + 1&
　　　　　　ReDim Preserve arrnIdxTarget(cnEqv)
　　　　　　arrnIdxTarget(cnEqv) = i
　　　　End Select
　　Next i
　　Erase mtxData()

　　sBuf = mtxFieldName(1, arrnIdxTarget(0))
　　For i = 1 To cnEqv
　　　　sBuf = sBuf & 区切り文字 & mtxFieldName(1, arrnIdxTarget(i))
　　Next i
　　Erase mtxFieldName(), arrnIdxTarget()

　　MinLookOver = sBuf
End Function

' ' ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

以上を【標準モジュール】に貼り付けたら、
セルに普通の数式を入力するのと同じように、

　=MaxLookOver($D$3:$K$1002,$B$2)
　=MaxLookOver(［データ範囲］,$B$2)

　=MinLookOver($D$3:$K$1002,$B$2)
　=MinLookOver(［データ範囲］,$B$2)

のように記入して普通に確定すれば、出来上がりです。

［データ範囲］には、列見出し・行見出しを含まないデータ範囲を指定します。

［データ範囲］先頭行のひとつ上の行に、列見出しがあるという前提です。

オプションで、第三引数に区切り文字を指定できます。
引数を省略すると、
　"(5)(8)"
が返る場合で例えると
　=MaxLookOver(［データ範囲］,$B$2,"-")
のように指定することで
　"(5)-(8)"
指定した区切り文字を挿入した文字列を返します。

関数の名前はデタラメですので、替えてもらった方がいいです。　

///

実際にニーズがどのようなものか、こちらの理解が至っていませんので、
何かあれば、補足欄にでも書いてみてください。

以上です。

お礼

会社から帰りマクロを使わせていただきました。
質問の足らないところを補っていただき有難うございました。理想結果が出ました。マクロの難しさを痛感しています。本当に有難うございました。

回答No.3

KURUMITO

関数で処理する方法で作業列を使って行います。
元のお示しのデータがＢ４セルからＫ４セルにかけて（１）～(10)が入力されており、Ａ５セルから下方に１からの番号が入力されているとします。
そこでＬ５セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(B5:K5,MAX(B$5:K$1500)),ROUNDDOWN(MAX(L$4:L4),-2)+100,IF(COUNTIF(B5:K5,MIN(B$5:K$1500)),MOD(SUM(L$4:L4),100)+1,""))

そこで結果の表示ですが例えばＢ２セルには最大値、Ｂ３セルには最小値とそれぞれ文字列を入力します。Ｃ２セルには最大値を、Ｃ３セルには最小値を表示させ、Ｄ列から右の列には２行目では最大値の入った列の(1),(2)などの項目名を、３行目には最小値の入った項目名を表示させます。そのためにＣ２セルには次の式を入力して右横方向にドラッグコピーしたのちに１行目下方までドラッグコピーします。

=IF(COLUMN(A1)=1,IF(ROW(A1)=1,MAX($B$5:$K$1500),MIN($B$5:$K$1500)),IF(AND(COLUMN(A1)>1,ROW(A1)=1),IF(COUNTIF($L:$L,(COLUMN(A1)-1)*100)=0,"",INDEX($B$4:$K$4,MATCH(MAX($B$5:$K$1500),INDEX($B:$K,MATCH((COLUMN(A1)-1)*100,$L:$L,0),1):INDEX($B:$K,MATCH((COLUMN(A1)-1)*100,$L:$L,0),10),0))),IF(AND(COLUMN(A1)>1,ROW(A1)=2),IF(COUNTIF($L:$L,COLUMN(A1)-1)=0,"",INDEX($B$4:$K$4,MATCH(MIN($B$5:$K$1500),INDEX($B:$K,MATCH(COLUMN(A1)-1,$L:$L,0),1):INDEX($B:$K,MATCH(COLUMN(A1)-1,$L:$L,0),10),0))),"")))

これで最大値や最小値に加えて、最大値や最小値にダブりが有る場合でも（１）、（２）などの見出しが表示されます。

お礼

お礼が遅くなり申し訳ありません。説明の至らない質問にもかかわらず回答いただき有難うございました。
関数の組み合わせで複雑なことが出来るのは勉強になりました。有難うございました。

回答No.1

Nouble

特定の列において
その最大と最小を求め
見出しとして表示したい
と、言うことでいいですか？

MAX MIN と、言う関数があります。
指定範囲中の各々の最大、最小を求め、
表してくれます。

如何でしょうか？
お役に立てていたならば幸いです。

お礼

返事遅くなり申し訳ありません。回答いただき有難うございました。

補足

最大値と最小値は求められているのですが、その数値がどの位置にあるか、見出しの値を返し知りたいのです。
解かりすらい質問でごめんなさい。