ExcelのLARGEとSMALLについて

質問

こんにちは、冬の猫と申します。

ExcelのLARGEとSMALLについて教えてください。

添付画像のようなエクセルで、2行目に入力されている値から
一番左と一番右の「列番号」について取得したいと思っています。
ただし、1行目で「×」が付いているものは除外しないといけません。

○、×の判断を除けば、
=IF(COUNTA(2:2)=0,"",LARGE(IF(2:2<>"",COLUMN(2:2)),1))
と
=IF(COUNTA(2:2)=0,"",SMALL(IF(2:2<>"",COLUMN(2:2)),1))

で解決出来ました。

ですが、○、×の判断を入れることが出来ません・・・

添付画像であれば、
2行目最小：2　2行目最小：3

5行目最小：6　5行目最大：7

の答えが欲しいのです。

また、作業領域を追加するとしても
行間ではNGとなっており、
この場合J列以降であれば、隠し作業領域として使用できます。

マクロは使ってはいけないルール（Excel管理上の会社規定）となっており
なんとか、関数だけで実現を目指しております。

どうかよろしくお願いします。

回答No.4

bunjii · ベストアンサー

Q
○、×の判断を除けば、
=IF(COUNTA(2:2)=0,"",LARGE(IF(2:2<>"",COLUMN(2:2)),1))
と
=IF(COUNTA(2:2)=0,"",SMALL(IF(2:2<>"",COLUMN(2:2)),1))

で解決出来ました。

ですが、○、×の判断を入れることが出来ません・・・

A
単純な添削では次のような数式で良いでしょう。
=IF(COUNTA(2:2)=0,"",LARGE(IF((2:2<>"")*(1:1="○"),COLUMN(2:2)),1))
と
=IF(COUNTA(2:2)=0,"",SMALL(IF((2:2<>"")*(1:1="○"),COLUMN(2:2)),1))

論理演算のANDは四則演算の乗算に置き換えられます。

回答No.5

Chiquilin

=MATCH(0,0/(A1:I1="○")/(A2:I2<>""),0)
Ctrl + Shift + Enterで確定

=LOOKUP(1,0/(A1:I1="○")/(A2:I2<>""),COLUMN(A:I))
Enterで確定

お礼

早速のご回答有難うございます。
大変勉強になりました。

回答No.3

tom04

こんばんは！

一例です。

最小の場合
=IF(COUNTA(2:2),MIN(IF((1:1="○")*(2:2<>""),COLUMN(1:1))),"")
最大の場合
=IF(COUNTA(2:2),MAX(IF((1:1="○")*(2:2<>""),COLUMN(1:1))),"")
どちらも配列数式になりますので、Ctrl＋Shift＋Enterで確定してください。

※　該当データがない場合（×の列にしかデータがない場合）は「0」が表示されます。m(_ _)m

お礼

早速のご回答有難うございます。

AND式でやっても駄目だったのですが、四則演算で可能だったのですね・・・

大変勉強になりました。

回答No.2

bunjii

>なんとか、関数だけで実現を目指しております。
2行目の最大値
A3=LARGE(INDEX((1:1="○")*(2:2<>"")*COLUMN(1:1),0),1)
2行目の最小値
B3=LARGE(INDEX((1:1="○")*(2:2<>"")*COLUMN(1:1),0),COUNTIFS(1:1,"○",2:2,"<>"))

貼付画像はExcel 2013で検証した結果です。
Excel 2007以降のバージョンで再現できます。
最小値を求めるのにLARGE関数は不適切と思われるでしょうが対象外のセルに大きな数値を返す配列を数式内に設定することが複雑になるので対象セル数をカウントして逆順の値を抽出する手法を使いました。
尚、此処で使っているINDEX関数は配列値を加工するために使用していますのでLARGE関数へ引き渡す値は配列値になります。

お礼

早速のご回答有難うございます。

このようなやり方もあるのですね。
勉強になりました。

回答No.1

MarcoRossiItaly

K2　=min(index(column(a2:i2)+9^9*((a1:i1="×")+(a2:i2="")),))
L2　=max(index((a1:i1<>"×")*(a2:i2<>"")*column(a2:i2),))

お礼

早速のご回答有難うございます。
大変勉強になりました。