[[20090707173829]]　『MIN/MAX配列関数、複数範囲、セル参照・場合分け』（いまちゅー）　ページの最後に飛ぶ

『MIN/MAX配列関数、複数範囲、セル参照・場合分け』（いまちゅー）

こんにちは、配列関数を勉強中なのですが、つまづいてしまいました。

＜例データ＞

 　A　B　C　D　E ・・・・・・AA　AB　AC　AD　AE・・・・・　BB
1  _  2  0  21 8             15  32  45  0   3             12
2  _　10 5  0  3　　　　　　 31  32　0　12   0              0
3  _ 
4  _

となっているようなデータでA列に同行にあるデータのB-E列及びAA-AE列のデータからBBの列にあるデータよりも小さい、ただし０ではない最小値・最大値（ただしBB列が０の場合にはBB列の値に関係なく、AA-AE列の最小値・最大値を引っ張りたいのです。

つまりA1にはE1の8(最大値)かB1の2(最小値)を
A2にはAB2の32(最大値)かE1の3(最小値)を引っ張りたいと考えて｢ます。

通常の配列関数で０以外の最小値やある特定値より小さい最小値を引っ張ることはできたのですが、特定値が０となる場合とそれ以外で異なる条件となってしまう場合の配列関数式をどのように書いたらいいのかがわかりません。

よろしくお願い致します。

＜問題文を修正７月８日１１時４５分＞

 > ただしBB列が０の場合にはBB列の値に関係なく、AA-AE列の最小値を引っ張りたいのです。
 > A2にはAB2の３２を引っ張りたいと考えています

 どうしてA2は32なんでしょうか？？？

 （えくせる青年）2009/7/7 18:12

すいません。上記間違いです。

B-E列及びAA-AE列のデータからBBの列にあるデータより「小さい」０ではない最小値及び最大値の間違いでした。

また欲しい値も最小値、最大値のどちらの場合も検討しています。
A1にはE1の2を、A2にはAB2の3を引っ張りたいです（最小値の場合）

混乱させてしまって申し訳ありません。

 いまいちよくわかりませんが

 1行目で考えて

 最小値の場合

 B1:E1 AA1:AE1 の範囲でゼロをのぞいた最小値

 最大値の場合

 BB1がゼロの場合は
 B1:E1 AA1:AE1 の範囲の最大値

 BB1がゼロ以外の場合は
 G1:E1 AA1:AE1の範囲で
 BB1より小さい値の最大値

 ということなのでしょうか？

 BB1が 1 の場合最大値はどうするのか？
 マイナスの数値はあるのか？

 などなどの疑問点がありますが

 （えくせる青年）2009/7/7 19:27

ご返信ありがとうございます。
おっしゃる解釈で間違いありません。

＜なお最小値のケースにおいては、BB1がゼロでない時、最小値がB１よりも大きい時はB1値を出したいと考えています。＞

また、今回は質問の簡略化のため整数値を使っていますが、実際には年月日をシリアル値で使っています。よって１やマイナス値になることはデータの性質上ありません。データが無い場合にゼロとなるように元データを加工したデータとなっています。

それではよろしくお願い致します。

 考え方が複雑になる原因は
 > データが無い場合にゼロとなるように元データを加工したデータ
 日付なのにゼロという数値が存在することです。

 何をどのように加工したのかはよくわかりませんが
 ゼロにならないようにしたほうがいいと思います。

 あと最大値の時に
 BB1の日付より前の日付が
 B1:E1 AA1:AE1の範囲に存在しない時はどうするのでしょうか？

 （えくせる青年）2009/7/8 13:30

コメントありがとうございます。

確かにゼロにならないようにしたほうがいいですよね。
ただ、今回のデータが既にオリジナルのデータを配列関数を使ってスクリーニングしたデータになっていまして、この１次加工したデータのシートを二次加工して意味のある数値のみを新しいシートに抽出しようするプロセスを構築しています。

一枚シートを挟んで、ゼロが空白になるようにしないと数式を組めないでしょうか？
それとも配列関数の答えでゼロとなったセルをそのまま配列関数式のセル上で空白と設定することは可能でしょうか？

また再度ご質問のあった最大値に対しては、そのときも最小値の場合と同様B1の値を出したいと考えています。

ただ複雑性を排除する目的として、
B1がゼロでないとき、最大値、最小値共にB1以下が存在しないのであればゼロとするような数式でも、目的は達成可能です。

配列関数でMIN/MAXを用いる時、その上限、下限をセル参照値として設定し、かつその参照先がゼロであるかないかでその参照を適用するかしないかの場合わけが可能であれば、問題は解決すると思っています。そういった数式を組むことはできないものでしょうか？

 > BB1がゼロでない時、最小値がB１よりも大きい時はB1値を出したいと考えています。
 > そのときも最小値の場合と同様B1の値を出したいと考えています
 > B1がゼロでないとき、最大値、最小値共にB1以下が存在しないのであればゼロとするような

 後出し条件ですか？
 B1はBB1の間違いですか？

 > 配列関数でMIN/MAXを用いる時、その上限、下限をセル参照値として設定し、
 > かつその参照先がゼロであるかないかでその参照を適用するかしないかの場合わけが可能であれば

 具体的なデータで説明してください。

 > それとも配列関数の答えでゼロとなったセルをそのまま配列関数式のセル上で
 > 空白と設定することは可能でしょうか？

 どんな数式なのかわからないのではっきりしたことは回答のしようがありません。

 とりあえず
 後出し条件を無視した数式
 いくつかの疑問点に関しては想像で解釈しています。

 最大値
 =IF(BB1=0,MAX(B1:E1,AA1:AE1),IF(BB1=SMALL((B1:E1,AA1:AE1,BB1),COUNTIF(B1:E1,0)+COUNTIF 
 (AA1:AE1,0)+1),BB1,LARGE((B1:E1,AA1:AE1),COUNTIF(B1:E1,">="&BB1)+COUNTIF(AA1:AE1,">="&BB1)
 +1)))

 最小値
 =SMALL((B1:E1,AA1:AE1,BB1),COUNTIF(B1:E1,0)+COUNTIF(AA1:AE1,0)+(BB1=0)+1)

 （えくせる青年）2009/7/8 15:28

何度もありがとうございます。

失礼致しました。BB1の間違いです。後だし条件ではなく、解答くださった式で必要なものをとってくることができました。ありがとうございました。
SMALL関数、Large関数はCOUNTIFと同時に使うと必要とするものがほぼとってくることができるのですね。またCOUNTIFの使い方として">="&を初めて知りました。勉強になります。

重ね重ねありがとうございました。

コメント返信：

[ 一覧(最新更新順) ]

[[20090707173829]] 『MIN/MAX配列関数、複数範囲、セル参照・場合分け』（いまちゅー） ページの最後に飛ぶ

[[20090707173829]]　『MIN/MAX配列関数、複数範囲、セル参照・場合分け』（いまちゅー）　ページの最後に飛ぶ