Excelのマクロについて質問です

質問

マクロを用いて、図のように”月”という名前がついている列にある数値の中から絶対値の最大値を読み取り、E1に書き出し、その数値があるセルの2つ右隣の数値をF1に書き出したいです。（この場合はE1に10、F1に5を書き出したい。）”月”は必ずしもA1にあるわけではなく何列目かの1行目にあります。
わかる方、回答よろしくお願いします。

回答No.4

keithin · ベストアンサー

sub macro1()
　dim c as range
　dim mx, mn

　set c = range("1:1").find(what:="月", lookin:=xlvalues, lookat:=xlwhole)
　if c is nothing then
　　msgbox "月 not found"
　　exit sub
　end if

　with application.worksheetfunction
　　mx = .max(c.entirecolumn)
　　mn = -.min(c.entirecolumn)

　　if mx >= mn then
　　　range("E1") = mx
　　　range("F1") = .vlookup(mx, c.resize(65536,3), 3, false)
　　else
　　　range("E1") = mn
　　　range("F1") = .vlookup(-mn, c.resize(65536, 3), 3, false)
　　end if
　end with
end sub

＃参考
同じロジックを単純に関数だけで実現するには
Ｅ１：
=MAX(MAX(INDEX(A:D,0,MATCH("月",1:1,0))),-MIN(INDEX(A:D,0,MATCH("月",1:1,0))))

Ｆ１：
=VLOOKUP(IF(MAX(INDEX(A:D,0,MATCH("月",1:1,0)))=E1,E1,-E1),OFFSET(INDEX(1:1,MATCH("月",1:1,0)),0,0,65536,3),3,FALSE)

お礼

回答ありがとうございます。

回答No.3

KURUMITO

マクロを使うこともないでしょう。次の式で対応できますね。
E1セルには次の式を入力します。

=MAX(MAX(INDEX($A:$D,2,MATCH("月",$A$1:$D$1,0)):INDEX($A:$D,1000,MATCH("月",$A$1:$D$1,0))),-MIN((INDEX($A:$D,2,MATCH("月",$A$1:$D$1,0)):INDEX($A:$D,1000,MATCH("月",$A$1:$D$1,0)))))

F1セルには次の式を入力します。

=IF(COUNTIF(INDEX($A:$C,1,MATCH("月",$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH("月",$A$1:$C$1,0)),E1)>0,INDEX($A:$C,MATCH(E1,INDEX($A:$C,1,MATCH("月",$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH("月",$A$1:$C$1,0)),0),MATCH("月",$A$1:$C$1,0)+2),IF(COUNTIF(INDEX($A:$C,1,MATCH("月",$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH("月",$A$1:$C$1,0)),-E1)>0,INDEX($A:$C,MATCH(-E1,INDEX($A:$C,1,MATCH("月",$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH("月",$A$1:$C$1,0)),0),MATCH("月",$A$1:$C$1,0)+2),""))

お礼

回答ありがとうございます。

回答No.2

K Kazz（@JazzCorp）

パクリが不完全だったので訂正します。

E1に、（訂正）
=MAX(MAX($A:$A),ABS(MIN($A:$A)))

F1に、（変更なし）
=VLOOKUP(IF(MAX($A:$A)>ABS(MIN($A:$A)),MAX($A:$A),MIN($A:$A)),$A:$C,3,FALSE)

＜＜パクリソース＞＞
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1425493379
「正の数＋負の数」が記載されている場合、「絶対値」で見て最大の数を、そのままの数で引っ張ってくるにはどのような関数を使用したら良いでしょうか？

お礼

回答ありがとうございます。

回答No.1

K Kazz（@JazzCorp）

列が固定なら、そして真黒じゃないけど、

E1に、
=ABS(IF(MAX($A:$A)>ABS(MIN($A:$A)),MAX($A:$A),MIN($A:$A)))

F1に、
=VLOOKUP(IF(MAX($A:$A)>ABS(MIN($A:$A)),MAX($A:$A),MIN($A:$A)),$A:$C,3,FALSE)