読者です 読者をやめる 読者になる 読者になる

ある行の最小値のn行隣の最小値が複数あるときの最小値

はじめに

この日本語悪すぎなんだけどいまいちいい言葉が思いつかない.複数あるというのは最小値が複数(0, 0, 1とか)ではなく,複数そのようなものがあるといった感じ.

問題

以下の様なExcelファイルがあった.なおセルの中身は全て数字である.

A,B,C,D
E,F,G,H
I,J,K,L
...

と言ったものだ.この時1列目と3列目,2列目と4列目は互いに意味のある列だと思って欲しい.(値ではなく意味が)対応している・関連していると言っても良いと思う.
今,3列目の最小値の行の1列目の値,4列目の最小値の行の2列目の値という2つの値を考える.これらの最小値を求めたい.実際に数値で考えてみると

1, 2, 3, 4
5, 6, 7, 8
9,10,11,12

といったデータがある時,3列目の最小値は3でありその行の1列目の最小値は1で,4列目の最小値は4でありその行の2列目の最小値は2だ.そしてそれぞれの値の最小値は1であり,これを抽出したい.

すること

Excelには条件付き書式という機能がある.
例えばある行の最小値や上位10%のセルの文字や背景の色を変更する……などといったことが出来る.知らない人はGoogle先生に任せよう.
これを用いて,さっきの条件の文字を変えたい.

解決策

Excelファイルっぽく書くと

- A B C D
1 1 2 3 4
2 5 6 7 8
3 9 10 11 12

とする.この時適用先は=$A$1:$B$3となる.ルールは=A1=MIN(INDEX($A$1:$A$3,MATCH(MIN($C$1:$C$3),$C$1:$C$3,0)), INDEX($B$1:$B$3,MATCH(MIN($D$1:$D$3),$D$1:$D$3,0)))となる.
この時の=A1はどうやら範囲の左上のセルを指定する必要があるらしく,それである.

解説

=A1=MIN(
  INDEX($A$1:$A$3,
    MATCH(MIN($C$1:$C$3),$C$1:$C$3,0)
  ),
  INDEX($B$1:$B$3,
    MATCH(MIN($D$1:$D$3),$D$1:$D$3,0)
  )
)

1つ目のMATCH関数でC1-C3の最小の値の相対的な位置が返ってくる.この時は1だ.
2つ目のMATCH関数でD1-D3の最小の値の相対的な位置が返ってくる.この時は1だ.
つまり

=A1=MIN(
  INDEX($A$1:$A$3,1),
  INDEX($B$1:$B$3,1)
)

となる.
1つ目のINDEX関数でA1-A3の1行目の値が返ってくる.この時は1だ.
2つ目のINDEX関数でB1-B3の1行目の値が返ってくる.この時は2だ.
つまり

=A1=MIN(1,2)

となる.
この時最小の値は1であり,それはA1のセルの中身であった.
こうして無事目的を果たすことが出来た.

おわりに

多分解説あってると思うんですが自身がないので間違ってたらぜひぜひ投げかけておいてください.

ちなみに僕のぶち当たった問題はもう少し(範囲的に)厄介で「ある列の最小値の行の3つ左の列」というのが3列分あってそれが3つ横に並んでいる,という感じでした.
はい,日本語が悪すぎて何言ってるかわかりませんのでExcelっぽく言うと「列Dの最小値の行の列Aの値・列Eの最小値の行の列Bの値・列Fの最小値の行の列Cの値・列Jの最小値の行の列Gの値・…・列Rの最小値の行の列Oの値」の最小値の文字色を変更したかった,と.ちなみに行数は11.
つまり範囲は=$A$1:$C$11,$G$1:$I$11,$M$1:$O$11となり,ルールは

=A1=MIN(
  INDEX($A$1:$A$11,MATCH(MIN($D$1:$D$11),$D$1:$D$11,0)),
  INDEX($B$1:$B$11,MATCH(MIN($E$1:$E$11),$E$1:$E$11,0)),
  INDEX($C$1:$C$11,MATCH(MIN($F$1:$F$11),$F$1:$F$11,0)),
  INDEX($G$1:$G$11,MATCH(MIN($J$1:$J$11),$J$1:$J$11,0)),
  INDEX($H$1:$H$11,MATCH(MIN($K$1:$K$11),$K$1:$K$11,0)),
  INDEX($I$1:$I$11,MATCH(MIN($L$1:$L$11),$L$1:$L$11,0)),
  INDEX($M$1:$M$11,MATCH(MIN($P$1:$P$11),$P$1:$P$11,0)),
  INDEX($N$1:$N$11,MATCH(MIN($Q$1:$Q$11),$Q$1:$Q$11,0)),
  INDEX($O$1:$O$11,MATCH(MIN($R$1:$R$11),$R$1:$R$11,0))
)

となった.さすがにもっと良いやり方があったのではないかとは思う.
ちなみにOFFSET関数も使おうとしたのだが

OFFSET($A$1,0,-3)             // OK
OFFSET(MIN($A$1:$A$11),0,-3)  // ill-formed

のように,どうにも関数を使えないみたいで諦めた.