表の中かの該当範囲から一番小さい数字を表示する関数

Question

以下の表の中からうまくデータを引き出すことはできますか？
該当年数は計算式が入っていて1-10の数字が表示されます。
該当	4年	55
該当が●年だった場合、下の表の範囲から一番小さい数字を表示する
表は斜めに一列の範囲
4年の場合色の範囲の４セルの中から一番小さい数字を表示する
	1年	２年	３年	４年	５年	6年	7年	8年	9年	10年
1年	60	58	95	89	69	58	55	45	60	58
２年	58	54	58	54	75	60	95	74	58
３年	75	75	47	78	32	69	68	65
４年	55	95	68	74	80	89	74
５年	32	45	39	68	40	95
6年	89	54	78	74	68
7年	78	48	70	68
8年	45	68	33
9年	66	25
10年	80
各年の範囲に名前の範囲を設定することは可能です。
マクロは利用できない環境のため、
マクロ以外でなにかいい方法がありましたら教えてください。

Answer 1

	[A]	[B]	[C]	[D]	[E]	[F]	[G]	[H]	[I]	[J]	[K]
[1]		1年	2年	3年	4年	5年	6年	7年	8年	9年	10年
[2]	1年	60	58	95	89	69	58	55	45	60	58
[3]	2年	58	54	58	54	75	60	95	74	58
[4]	3年	75	75	47	78	32	69	68	65

| [5] | 4年 | 55 | 95 | 68 | 74 | 80 | 89 | 74 | | [6] | 5年 | 32 | 45 | 39 | 68 | 40 | 95 | | [7] | 6年 | 89 | 54 | 78 | 74 | 68 | | [8] | 7年 | 78 | 48 | 70 | 68 | | [9] | 8年 | 45 | 68 | 33 | | [10] | 9年 | 66 | 25 | | [11] | 10年 | 80 | | [12] | | | | | | | | | | | | | [13] | 年 | 最少 | | [14] | 1 | 60 | | [15] | 2 | 58 | | [16] | 3 | 54 | | [17] | 4 | 55 | | [18] | 5 | 32 | | [19] | 6 | 45 | | [20] | 7 | 32 | | [21] | 8 | 45 | | [22] | 9 | 40 | | [23] | 10 | 25 | | | | | | | | | |

>INDEX関数の使い方がイマイチよくわからないため　*10^5　のべき算にいたっては

>チンプンカンプンで、実際の表の行列が変わったところにどのように当てはめていいのか

◆すぐに実際の表にあてはめようとせず、回答のとおり再現してみてください

１）回答と同じ表を同じセル位置に作成・入力してください

２）A14からA23に数字の「1」から「10」を入力してください

３）B14=MIN(INDEX((ROW($1:$10)+COLUMN($A:$J)<>A14+1)*10^5+$B$2:$K$11,))　と入力して、下にコピーしてください

◆黄色の部分の「5年」で説明しましょう

４）セルB18を指定してください、

５）すると数式バーに「=MIN(INDEX((ROW($1:$10)+COLUMN($A:$J)<>A18+1)*10^5+$B$2:$K$11,))」と表示されるはずです

６）数式バーの式のうち、　ROW($1:$10)　と指定して、「F9」をクリックしてください

７）{1;2;3;4;5;6;7;8;9;10}　になり、この行列は、1行、2行、3行・・・10行をさしています

８）次に、　COLUMN($A:$J)　を指定して、「F9」

９）{1,2,3,4,5,6,7,8,9,10}　になり、この行列は、1列、2列・・・10列をさしています

10）今度は、　ROW($1:$10)+COLUMN($A:$J)　を指定して、「F9」

11）{2,3,4,5,6,7,8,9,10,11;3,4,5,6,7,8,9,10,11,12;4,5,6,7,8,9,10,・・・・・18,19;11,12,13,14,15,16,17,18,19,20}　となり

12）1行目+1列目（2）、1行目+2列目（3）、1行目+3列目（4）、・・・・1行目+10列目（11）、2行目+1列目（3）、2行目+2列目（4）・・・10行目+10列目（20）　になっています

13）このように、何行目＋何列目を足したものが、「5年目」の場合5+1と異なる場合*10＾5（100000）します

14）これに、$B$2:$K$11　を足すと、黄色の数字以外は100000+その数字が、黄色の部分は、0+その数字=黄色い数字になります

15）それのMINは、「32」になります

16）「*10^5　のべき算にいたってはチンプンカンプン」は、該当箇所以外を除外するための方法です

17）式から、INDEXを除いて、=MIN((ROW($1:$10)+COLUMN($A:$J)<>B51+1)*10^5+$B$2:$K$11)　にして見ましょう、「#VALUE!」になります

18）=MIN((ROW($1:$10)+COLUMN($A:$J)<>B51+1)*10^5+$B$2:$K$11)　数式バーの全部を指定して、「F9」を押すと

19）「32」が表示されます、INDEXを入れた、=MIN(INDEX((ROW($1:$10)+COLUMN($A:$J)<>A14+1)*10^5+$B$2:$K$11,))だと「32」がそのまま表示されます

20）INDEX関数は、「配列式」を顕在化する働きがあるようです

◆配列数式について

１）=MIN(IF(ROW($1:$10)+COLUMN($A:$J)=B51+1,$B$2:$K$11,100))　として、一番最後の「100」はデータ数値の最大値より大きな数値であれば何でもいいです

２）この式は「配列数式」です。式を入力後、CtrlとShiftを押しながらEnterを押して式を確定させてください

３）式が確定すれば、式の両端に、｛　｝がつきます

※参考ＵＲＬ◆「配列数式」について、わかりやすく説明されています

「“達人”芳坂和行氏に学ぶ、エクセル(Excel)「配列数式」講座」

http://pc.nikkeibp.co.jp/pc21/special/hr/

★配列数式は質問者の方が、「CtrlとShiftを押しながらEnterを押す」事をしないことが多いので、「Enter」のみでよいINDEX入りの回答にしました

◆配列数式は、便利な方法です

◆しかし、多用すると計算時間がかかることになります

◆maronは会社では、INDEX入り配列数式は限定して使用しています

Answer 2

●のついている斜めの部分だけということですか？

そうだとすると、私の前の投稿は間違っています。（●より上の三角の範囲でやってます）

失礼しました。

一応修正版を出しておきます。

=SMALL(IF(ROW(1:6)+COLUMN(A:F)=A9+1,A1:F6,100),1)

でCTRL+SHIFT+Enter

 斜めということでしたら、式一つにこだわらなければ、かなり真っ当な方法で可能でしょう

例えば、A12に年を入れ、B12~K12 に

=IF($A12-COLUMN()+2>0,OFFSET(B1,$A12-COLUMN()+2,),100)

答えのセルに

=Min（B12:K12)

でよろしいかと思います。

Answer 3

◆こんな方法はいかがでしょう

B14=MIN(INDEX((ROW($1:$10)+COLUMN($A:$J)<>A14+1)*10^5+$B$2:$K$11,))

★下にコピー

	[A]	[B]	[C]	[D]	[E]	[F]	[G]	[H]	[I]	[J]	[K]
[1]		1年	2年	3年	4年	5年	6年	7年	8年	9年	10年
[2]	1年	60	58	95	89	69	58	55	45	60	58
[3]	2年	58	54	58	54	75	60	95	74	58
[4]	3年	75	75	47	78	32	69	68	65
[5]	4年	55	95	68	74	80	89	74
[6]	5年	32	45	39	68	40	95
[7]	6年	89	54	78	74	68
[8]	7年	78	48	70	68
[9]	8年	45	68	33
[10]	9年	66	25
[11]	10年	80
[12]
[13]	年	最少
[14]	1	60
[15]	2	58
[16]	3	54
[17]	4	55
[18]	5	32
[19]	6	45
[20]	7	32
[21]	8	45
[22]	9	40
[23]	10	25

Answer 4

こういうのは要するに式を作ってほしいということで丸投げです。

普通は無視するのですが、案外面白そうなのでちょっとやってみました。

A1:F6にテーブルがあり（縦横の見出しはなし）、A9にｎが入っているとして、

=SMALL(IF(ROW(1:6)+COLUMN(A:F)>A9+1,100,A1:F6),1)

として、CTRL+SHIFT+Enter

当然、配列数式です。

配列数式の意味、見出しが入った場合の変形はご自分で解明してください。

Answer 5

以下の表の中からうまくデータを引き出すことはできますか？
該当年数は計算式が入っていて1-10の数字が表示されます。
該当	4年	55
該当が●年だった場合、下の表の範囲から一番小さい数字を表示する
表は斜めに一列の範囲
4年の場合色の範囲の４セルの中から一番小さい数字を表示する
	1年	２年	３年	４年	５年	6年	7年	8年	9年	10年
1年	60	58	95	●89	69	58	55	45	60	58
２年	58	54	●58	54	75	60	95	74	58
３年	75	●75	47	78	32	69	68	65
４年	●55	95	68	74	80	89	74
５年	32	45	39	68	40	95
6年	89	54	78	74	68
7年	78	48	70	68
8年	45	68	33
9年	66	25
10年	80
各年の範囲に名前の範囲を設定することは可能です。
マクロは利用できない環境のため、
マクロ以外でなにかいい方法がありましたら教えてください。

追記：が出ないので●印をつけました。ダンダンに下がっていくような範囲のため、いい方法はないでしょうか？