[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『選択範囲から一番自然数に近いものを選び表示させたい 』（poponta）
小数点第1位以下の数字に注目して比較し
より整数に近くなるものを選択させたいのです。
例えば セル A1：10.12 B1：11.52 C1：8.01
と(計算式の値として）入力されている時
セル E1：8.01
を表示されるようにしたいです。
いろいろ検討してみたのですが上手く出来ず困っています。
よろしくお願いします。
＜ 使用 Excel：Excel2013、使用 OS：Windows10 ＞
=INDEX(A1:C1,MATCH(MIN(ABS(A1:C1-ROUND(A1:C1,0))),ABS(A1:C1-ROUND(A1:C1,0)),0)) （hatena） 2021/09/20(月) 17:04
引き続き何かアイディアがありましたらよろしくお願いします。
（poponta） 2021/09/20(月) 17:45
こんばんわ ＾＾ 当方では、E1に 8.01 と表示されましたですよ。 Excel 2016 365タイプ OS Win 10 でした。m(_ _)m （隠居Ｚ） 2021/09/20(月) 17:55
＞使用 Excel：Excel2013 ですからねぇ・・
E1セル =LOOKUP(1,0/(MOD(A1:C1,1)=MIN(INDEX(MOD(A1:C1,1),0))),A1:C1)
（半平太） 2021/09/20(月) 18:58
（γ） 2021/09/20(月) 18:59
A B C
1 10.12 11.52 7.99
のとき、10.12となりませんか？
7.99ですよね。
（γ） 2021/09/20(月) 19:07
γさん ご指摘ありがとうございます。
「一番自然数に近いもの」を「小数部分が小さい」と勘違いしてしまいました。
popontaさん 私の上の回答は無視してください。m(__)m
（半平太） 2021/09/20(月) 19:29
再掲します。
hatenaさんの提示された式
=INDEX(A1:C1,MATCH(MIN(ABS(A1:C1-ROUND(A1:C1,0))),ABS(A1:C1-ROUND(A1:C1,0)),0))
を配列数式として入力、
つまり、
CtrlキーとShiftキーを同時に押しながらEnterキーでセルに入力すると、
所望する結果が得られます。
# 私もその式を考えていました。
（γ） 2021/09/20(月) 20:08
Yさん hatenaさん 出来ました^^／ 配列数式初めて知りました。
皆さま本当にありがとうございました。
（poponta） 2021/09/21(火) 00:19
4.05 9.95 3.95 の場合とか＾＾； m(_ _)mm(__)mm(__)m
（隠居Ｚ） 2021/09/21(火) 06:46
また困った問題が出ていて
3つの数字のうち データなしの 0.00があると 0が選ばれてしまうのです。
0を除外する方法有りますでしょうか？？
（poponta） 2021/09/21(火) 22:51
こんばんわ ＾＾ そうだったのですね。コメント、有難う御座います。 ＞＞また困った問題が出ていて ひえ～。。。(＠_＠;)。そ、それは、お困りですね。 相済みません、わたし、数式はからっきしなので。。 他の回答者様のアドバイスをお待ちくださいね。お勉 強なので。。。考えてはみますが、お先真っ暗です＾＾； m(_ _)m （隠居Ｚ） 2021/09/21(火) 23:06
おはようございます ＾＾。。。ＶＢＡでよければ。(*^ ^*)v
内容は、hatenaさんご提案の、パクリです、パクリそこねているかも(/_;)
少数演算誤差対策は自信ありません、教えて頂くと幸甚です。何かの参考程度
に、お止め下さいませ。←ならなければ、お許しを。ゴミ箱ポイしといてくださいね。(￣▽￣)
m(_ _)m
Sheet1
|[A] |[B] |[C]
[1] | 1.65 | 3.95 | 3.95
[2] | 3.33 | 9.86 | 5.09
[3] | 7.56 | 8.67 | 4.51
[4] | 3.33 | 0.00 | 5.33
[5] | 3.33 | 8.12 | 0.00
[6] | 4.09 | 6.65 |12.65
[7] |15.97 | 4.08 | 4.20
[8] | 5.98 | 5.01 | 8.75
[9] | 6.34 | 5.15 | 5.47
[10]| 4.52 |15.27 | 4.83
[11]| 8.90 | 4.10 |12.23
[12]| 1.06 | 6.95 | 8.50
[13]| 3.49 |15.45 | 6.65
[14]| 2.80 | 3.43 |14.25
[15]| 8.50 | 7.93 |15.09
[16]|14.70 |11.32 |12.48
[17]|11.86 | 2.59 |13.57
[18]|11.50 |12.98 | 7.36
[19]| 6.59 | 9.67 | 6.10
[20]| 8.93 |10.79 | 7.52
Option Explicit
Sub OneInstanceMain()
Dim i As Long
Dim j As Long
Dim k As Long
Dim n As Long
Dim bK As Long
Dim x As Double
Dim xX As Double
Dim tMp As Double
Dim v() As Variant
Dim bKAry() As Variant
Dim aDAry() As Variant
Dim r As Range
With Worksheets("Sheet1")
Set r = Intersect(.UsedRange, .Range(.Columns(4), .Columns(.UsedRange.Columns.Count)))
If .UsedRange.Columns.Count > 3 Then
r.Clear
End If
For i = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
For j = 1 To 3
ReDim Preserve bKAry(bK), aDAry(bK)
tMp = .Cells(i, j).Value
x = Application.Round(tMp, 0)
xX = Abs(Application.Round(tMp - x, 8))
bKAry(bK) = xX
aDAry(bK) = .Cells(i, j).Address(False, False)
bK = bK + 1
If .Cells(i, j) * 1 <> 0 Then
ReDim Preserve v(n)
v(n) = xX
n = n + 1
End If
Next
x = Application.Min(v)
n = 5
For k = 0 To UBound(bKAry)
If x = bKAry(k) Then
.Cells(i, n) = .Range(aDAry(k)).Value
.Cells(i, n).Offset(, 1) = aDAry(k)
n = n + 2
End If
Next
bK = 0
n = 0
Erase bKAry, aDAry, v
Next
Set r = .UsedRange
Set r = r.Offset(, 4).Resize(, r.Columns.Count - 4)
For i = 1 To r.Columns.Count
If Not i Mod 2 = 0 Then
r.Columns(i).NumberFormat = "0.00"
End If
Next
End With
End Sub
（隠居Ｚ） 2021/09/22(水) 09:52
式でチャレンジしてみました。 E1セルに下記の式を =IFERROR(INDEX($A1:$C1, SMALL(IF(ABS($A1:$C1-ROUND($A1:$C1,0))=SMALL(ABS($A1:$C1-ROUND($A1:$C1,0)), COUNTIF($A1:$C1,"0")+1),COLUMN($A1:$C1)),COLUMN(A1))),"")
入力後、Ctrl+Shift+Enter
これを右に3列フィルドラッグ さらに下に必要な行までフィルドラッグ
参考にしたのは
Office TANAKA - Excel Tips[0より大きい最小値] http://officetanaka.net/excel/function/tips/tips33.htm
関数で複数条件で検索して複数データを取り出す:Excel関数の技 http://www.eurus.dti.ne.jp/~yoneyama/Excel/waza/fukusu_data.html
関数では超絶難解ですね。
Office365ならスピルやFilter関数などが使えるので楽できそうですが。
（hatena） 2021/09/22(水) 14:51
小数以下桁数が4桁以内なら、通貨型(Currency)を使うと誤差は生じないです。
中身は整数で、表示上小数にしているだけですので。
（hatena） 2021/09/22(水) 20:39
今回の場合、同じ計算結果を比較してますので、誤差は気にしなくてもいいように思います。
（hatena） 2021/09/22(水) 21:19
hatena さん コメントありがとうございます。今回の件、了解いたしました。後学のため どこかのサイトで、少数演算誤差は小数点以下の桁数が大きいところで 発生[何桁だったか忘れましたが＾＾；]８～１２，３桁くらいでは大丈 夫なような事が書かれていた記憶が有り、引き算で点以下８桁で丸めて みました。 また、Cdecでデシマルにするのも有効とか無効とか？？；で！ 無駄な抵抗でせうか。。。＾＾；。。。m(_ _)m ＞＞通貨型(Currency)を使うと誤差は生じないです。 知りませんでした。( ..)φメモメモ 有難う御座います。とても、勉強になります。m(_ _)m （隠居Ｚ） 2021/09/22(水) 21:40
Public Sub OneInstanceMain2()
Dim Tbl As Range
Set Tbl = ActiveSheet.Cells(1, 1).CurrentRegion
Dim r As Range
For Each r In Tbl.Rows
Dim a()
a = r.Value
Dim i As Long
For i = 1 To 3
If a(1, i) = 0 Then
a(1, i) = 1
Else
a(1, i) = Abs(CDec(a(1, i)) - Round(a(1, i)))
End If
Next
Dim m As Double
m = Application.Min(a)
Dim x(1 To 1, 1 To 3), j As Long
For i = 1 To 3
If a(1, i) = m Then
j = j + 1
x(1, j) = r.Cells(i)
End If
Next
r.Offset(, 4).Value = x
Erase x: j = 0
Next
End Sub
（hatena） 2021/09/22(水) 22:25
こんばんわ。。。＾＾ ありがとうございます。 使い方、よくわかりましたです。計算式を全て、囲めばいいのですね。 これからは、使ってみます。m(_ _)m （隠居Ｚ） 2021/09/22(水) 23:56
＞少数演算誤差は小数点以下の桁数が大きいところで ＞発生[何桁だったか忘れましたが＾＾；]８～１２，３桁くらいでは大丈 ＞夫なような事が書かれていた記憶が有り、引き算で点以下８桁で丸めてみました。
なんか引っかかりますねぇ。
有効なのは下何桁なのか、それが肝だと思います。
質問文を読む限り、それは下２桁だろうと思うのが常識的ですが、 その推測が正しければ、引き算の結果を下２桁未満で丸めれば十分です。 正しくないなら、小数演算誤差対策と言うのは理論的に存在しないです。
ただ後者の場合でも、更に常識を働かせて、いくら何でも下5桁くらいが有効桁の下限だろうと思えば
余裕を加味して下8桁で丸めると言う対策もあり得ます。
※それじゃもっと余裕を加味すれば、更に安全じゃないかと思うかも知れませんが、
数値に割り当てられているBIT数には限界がありますので、
演算誤差ゾーンに入って丸めたら対策が無意味になってしまう。
（半平太） 2021/09/23(木) 12:14
こんにちわ。＾＾ 何時も勉強させていただいております。 仰せの通りかと。m(__)m 参照したサイトでも、小数点以下何桁から誤差ゾーンだとは 明記はされてなかったように、思います。ただ、何の根拠も 御座いませんが15桁くらいは影響なし。。。かも←おおお また、悪い癖が。。。＾＾；いい加減なじじぃで済みません 実験してみないといけませんね。また暇な時にでも、やってみます。 有難うございました。m(__)m （隠居Ｚ） 2021/09/23(木) 13:01
＞ただ、何の根拠も御座いませんが15桁くらいは影響なし。。。かも
おお、勇ましいですね・・
ただ、一概に言えないんですよー。 整数部分も総BIT数の内なので、その分の余裕も見ておかないとならないので、
数式ベースの検証ですが、差が同じじゃないです。
行 ____A____ _________B_________ 1 4.05 0.0500000000000000 =ROUND(ABS(A1-ROUND(A1,0)),15) 2 10004.05 0.0499999999992720 =ROUND(ABS(A2-ROUND(A2,0)),15)
（半平太） 2021/09/23(木) 14:07
なるほど、なるほど、整数部の桁が増えるたびに、誤差が生じる ラウンドの丸め桁数が変化します。本当に気を付けないと。いけませんね 整数部、求める小数桁数、共に、桁数に、最高の注意を払い、ラウンドの 指定桁数を勘案する様にいたします。いや、本当に、有難う御座いました。 一つ、賢くなりました。(*^ ^*)v m(_ _)m （隠居Ｚ） 2021/09/23(木) 14:55
popontaさん、すみません ＾＾； （隠居Ｚ） 2021/09/22(水) 09:52 は 整数部、8桁、小数点以下2桁 までなら、行けそうですが。限界も有りますので。 ご注意、いただくか、破棄お願いいたします。 A^^; でわでわ。また機会が御座いましたら。。。m(_ _)m （隠居Ｚ） 2021/09/23(木) 15:17
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.