[[20040906165001]]　『Ｆセルより左の列で一番Ｆセルに近い6桁のセル』（りん）　ページの最後に飛ぶ

『Ｆセルより左の列で一番Ｆセルに近い6桁のセル』（りん）

[例えば，Ｆセル－（Ｆセルより左側の列の中で一番Ｆセルに近い6桁のセル）という計算式を教えてください。]

 （Ａ  　　　Ｂ　　　　  Ｃ　　　     Ｄ　　　    Ｅ　      Ｆ　      Ｇ　…

 20　　　    560500   　　　10  　  680900 　　　　　　   780500    
ＧセルにＦ－Ｄの値を出したい。

 10  　　　　103000 　　　 200    　200500   
ＥセルにＤ－Ｂの値を出したい。

うまく説明できないんですが，お願いします。

５桁以下及び７桁以上の値のみの行もあるのでしょうか？

ある場合でも「エラー」と表示させて良いのであればＧセルに

=IF(OR(MAX(A5:D5)<100000,MAX(A5:D5)>1000000),"エラー",F5-MAX(A5:D5))

の数式を入れれば出来ると思いますが、

７桁以上もあるが、６桁で近似値のもので計算したいのであれば、別の式を考えなければいけません（岩石)

 休憩時間しか利用できないため、最初の命題、
＞ＧセルにＦ－Ｄの値を出したい。
これについてのみ考察します。
（考察であって、完璧な解答ではありません。）

=IF(SUM(N(LEN(A1:E1)=6)),F1-INDEX(A1:E1,MATCH(MIN(IF(LEN(A1:E1)=6,ABS(A1:E1-F1))),ABS(A1:E1-F1),0)),"該当ナシ")

 と数式バーへ入力し、Enterではなくて、　Ctrl+Shift+Enterで数式を確定します。
{}で囲まれた配列式になります。
　
「F1から　[A1:E1の範囲で６桁であり、F1との差が最小になる数値]　を除算する」
ですが、この数式では、F1が780500のとき、その差の絶対値が等しくなる数値、
780000、781000の二つががA1:E1の範囲にあった場合は左側のものが優先されます。
つまり、F1より大きい数値がどの位置にあるかによって計算結果の正負記号が変化
してしまいます。
このような場合の処理がどうなるかで数式に手を加える必要があるようですが、
私はこれが発想の限界です。
例えば、
「差の絶対値の等しい数値があるときはF1の値より大きい数値を優先させる」
という処理が思いつきません。
どなたか考察してみてくださいませ。
(KAMIYA)

 私は以下のようなことではないかと解釈してました(^_^A;
 F1に近いセル＝なるべく近接したセル
     A  	     B	      C	     D      E         F        G 	
 1  100,000  5,000	 3,000,000   200  100,000    150,000  50,000←F1-E1
 2  100,000  5,000	 3,000,000   200  130,000             30,000←E1-A1
 3  100,000  5,000	   120,000   200  150,000  2,000,000  30,000←E1-C1
 （川野鮎太郎）

 ＞ F1に近いセル＝なるべく近接したセル
読めば読むほどそのように思えてきた･･･。
(KAMIYA)

 >「差の絶対値の等しい数値があるときはF1の値より大きい数値を優先させる」
 >という処理が思いつきません。
 >どなたか考察してみてくださいませ。
 ここに反応してみました。『やはり配列数式は、考え方が難しい。』と言う事再認識させられました。
多分合っていると思います。何例かではOKでしたから・・・
{=IF(F1="","",IF(SUM(N(LEN(A1:E1)=6)),MIN(IF(MIN(IF(A1:E1>=F1,A1:E1,10^7)-F1)=MIN(ABS(A1:E1-F1)),-MIN(IF(A1:E1>=F1,A1:E1,10^6)-F1),10^7),IF(MIN(F1-IF(A1:E1<F1,A1:E1,0))=MIN(ABS(A1:E1-F1)),MIN(F1-IF(A1:E1<F1,A1:E1,0)),10^7)),F1))}
 随分と時間を要しました。また、より良い式が他に有るとは思いますが、もう考えたくありません。
(sin)  ※KAMIYAさんの式を一部無断借用（パクリ）しています。

 追加：ちょっと時間が出来たので、配列数式にしないのを考えてみました。
SUMPRODUCT関数を使ってますが、OR(LEN(A1)=6,･･･)が面倒だったので・・・
=IF(F1="","",IF(SUMPRODUCT((A1:E1>=10^5)*(A1:E1<10^6)),F1-IF(MIN(IF(RANK(F1,A1:F1,0)>1,LARGE(A1:F1,RANK(F1,A1:F1,0)-1)-F1,10^6),IF(RANK(F1,A1:F1,1)>1,F1-SMALL(A1:F1,RANK(F1,A1:F1,1)-1),10^6))=IF(RANK(F1,A1:F1,0)>1,LARGE(A1:F1,RANK(F1,A1:F1,0)-1)-F1),LARGE(A1:F1,RANK(F1,A1:F1,0)-1),SMALL(A1:F1,RANK(F1,A1:F1,1)-1)),F1))

 参りました<m(__)m>、、、
最近は式が長くなると、カッコの数が合わせられなくなり、
すぐ挫折・・・(ーー;)。
sinさん、見事！！！
(KAMIYA)

 私も、近接したセルで考えてみました(;^_^A アセアセ
 めちゃくちゃ長い式だ＿/￣|○ il||li
 配列で考えたら良いんでしょうけど、私には無理(^_^A;
 =INDIRECT(CHOOSE(RIGHT(SUBSTITUTE(IF(AND($A1>=100000,$A1<1000000),",1","")&IF(AND($B1>=100000,$B1<1000000),",2","")
&IF(AND($C1>=100000,$C1<1000000),",3","")&IF(AND($D1>=100000,$D1<1000000),",4","")&IF(AND($E1>=100000,$E1<1000000),",5","")
&IF(AND($F1>=100000,$F1<1000000),",6",""),",",,1),1),"A","B","C","D","E","F")&ROW(A1))-
INDIRECT(CHOOSE(LEFT(RIGHT(SUBSTITUTE(IF(AND($A1>=100000,$A1<1000000),",1","")&IF(AND($B1>=100000,$B1<1000000),",2","")
&IF(AND($C1>=100000,$C1<1000000),",3","")&IF(AND($D1>=100000,$D1<1000000),",4","")&IF(AND($E1>=100000,$E1<1000000),",5","")
&IF(AND($F1>=100000,$F1<1000000),",6",""),",",,1),3),1),"A","B","C","D","E","F")&ROW(A1))
 （川野鮎太郎）

 短くなったので修正(^_^A;
 =INDIRECT(CHOOSE(RIGHT(SUBSTITUTE(
 IF(LEN($A1)=6,",1","")
 &IF(LEN($B1)=6,",2","")
 &IF(LEN($C1)=6,",3","")
 &IF(LEN($D1)=6,",4","")
 &IF(LEN($E1)=6,",5","")
 &IF(LEN($F1)=6,",6",""),
 ",",,1),1),"A","B","C","D","E","F")
&ROW(A1))-INDIRECT(CHOOSE(LEFT(RIGHT(SUBSTITUTE(
 IF(LEN($A1)=6,",1","")
 &IF(LEN($B1)=6,",2","")
 &IF(LEN($C1)=6,",3","")
 &IF(LEN($D1)=6,",4","")
 &IF(LEN($E1)=6,",5","")
 &IF(LEN($F1)=6,",6",""),
 ",",,1),3),1),"A","B","C","D","E","F")&ROW(A1))
 （川野鮎太郎）

コメント返信：

[ 一覧(最新更新順) ]

[[20040906165001]] 『Ｆセルより左の列で一番Ｆセルに近い6桁のセル』（りん） ページの最後に飛ぶ

[[20040906165001]]　『Ｆセルより左の列で一番Ｆセルに近い6桁のセル』（りん）　ページの最後に飛ぶ