Excel2007で極端に大きい数値を探す

質問

タイトルの通り、Excel2007に貼り付けたデータ羅列の中から、極端に大きかったり小さい数値を探したいです。
例えば、MAXが100までのデータ羅列で
100 70 10 50 70 30 -10000000 20 50 300000000 50 40
このようなデータ羅列があった場合
-10000000と300000000を探す方法を探しています。

また、出来ればその極端に大きい数値を0に置き換えることが出来ればベストです。
よろしくお願いします。

回答No.9

mitarashi · ベストアンサー

#8です。お役に立つかどうかは不明ですが、#8の操作をマクロ化するとこんな感じです。
対象範囲を選択してから実行します。2番目のシートを作業用シートとします。
作業用の名前を付けて実行し、用済み後は削除しています。
マルチコア対応の最近のCPU＋Excel2007以降なら配列数式も速いかも．．．
Sub test()
Dim targetRange As Range, multiplyRange As Range

Set targetRange = Selection
ActiveWorkbook.Names.Add Name:="table", RefersTo:="=" & targetRange.Parent.Name & "!" & targetRange.Address(True, True, xlA1)
Set multiplyRange = Sheets(2).Range(targetRange.Address)
multiplyRange.FormulaArray = "=IF((table>100000) + (table<-100000),0,1)"
multiplyRange.Copy
targetRange.PasteSpecial Paste:=xlPasteValues, operation:=xlPasteSpecialOperationMultiply
multiplyRange.Clear
ActiveWorkbook.Names("table").Delete
End Sub

回答No.8

mitarashi

１．データが入った範囲に、例えばtableという名前をつけます。
２．別のシートの、上記範囲に相当する領域のセル群を選択し、
{=IF((table>100000) + (table<-100000),0,1)}
といった式を入力して、Ctrl+Shift+Returnで確定します。100000のところはお好きな数値に変更して下さい。
配列数式です。{}は自動で付きますので入力無用です。
これで、極端に大きな、または小さな値に相当するところのみ0で、他は1というマトリクスができます。
３．２で作成したマトリクス全体をコピーし、１のデータ範囲の左上隅のセルを選択し、
形式を指定してコピーで、値、乗算を指定して実行します。
これにより、極端に大きいまたは小さい数のみ0が乗算されて0になり、他は1が掛けられるので元のまま残ります。
ご参考まで。

回答No.7

MackyNo1

目的がよくわからないのですが、特定の数字範囲に入らないデータを一目で見分けたいだけなら、条件付き書式を利用するのがわかりよいかもしれません。

たとえば、プラスマイナス100未満の数字以外のセルを赤く表示させるなら、A1セルからデータ範囲を選択してホームタブの「条件付き書式」で「新しいルールから「数式を使用して・・・」を選択して「=ABS(A1)>100」と入力して「書式」ボタンから塗りつぶしを赤などにします。

ただし、このような条件付き書式を設定したシートに他のアプリケーションからデータを貼り付けるときは、右クリックから「形式を選択して貼り付け」で「テキスト」や「値」を選択して下さい。

回答No.6

MackyNo1

>データ羅列の中から、極端に大きかったり小さい数値を探したいです。

>また、出来ればその極端に大きい数値を0に置き換えることが出来ればベストです。

例えば、A列にデータが入力されている場合「=(ABS(A1)<100000)*A1」のような数式を入力すれば、極端に大きかったり小さい数値を「0」に、それ以外の数字はそのままの表示になります。

実際それらの極端な数字を「0」にして、その後どのようなことがしたいのかによって、もっと効率的な（例えば直接計算する）数式なども考えられると思います。

回答No.5

merlionXX

ANo1　merlionXXです。

> 最大値、最小値、を求めるだけでは出しきれない状態で、

いくつもあるということですか？
ならば、
=LARGE(A:A,2)　
で、2番目に大きな数が求められます。
引数の2の部分を変えれば何番目でも求められます。
同様に、
=SMALL(A:A,3)
で、3番目に小さな数が求められます。

でも並べ替えするのが一番早いと思いますが・・・。

回答No.4

yomyom01

＞極端に大きかったり小さい数値を探したい
昇順と降順で並べ替える

回答No.3

SaKaKashi

曖昧な探し方はできませんので。
並び替えをして上から順に数値をみて判断してください。

回答No.2

tom04

こんばんは！
VBAでの方法になりますので、参考にならなかったら読み流してください。

＞極端に大きかったり小さい数値を探したいです。

の部分で極端に大きい値・極端に小さい値はどの程度を指していらっしゃるのか判らないので、
勝手に100より大きい値・または0より小さい値の場合は「0」にするようにしてみました。

画面の左下にあるSheet見出しの操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので、
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　実行です）
データはA列にあるものとしています。

Sub test() 'この行から
Dim i As Long
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If IsNumeric(Cells(i, 1)) Then
If Cells(i, 1) > 100 Or Cells(i, 1) < 0 Then '←この行で「極端な値」を調整してください。
Cells(i, 1) = 0
End If
End If
Next i
End Sub 'この行まで

尚、一旦マクロを実行すると元に戻せませんので、別Sheetのコピーしてマクロを試してみてください。

以上、的外れならごめんなさいね。m(__)m

回答No.1

merlionXX

仮にデータがA列内にあるなら、
最大値は
=MAX(A:A)
最小値は
=MIN(A:A)
で、求められます。
値がわかれば、「置換え」で書き換えできますね。

お礼

回答ありがとうございます。
私の説明が不足していたため、補足させていただきます。
先ほどの数列は、解り易くするためにかなり省いていました。

100 70 10 50 70 30 -10000000 20 50 300000000 50 40....とあるのですが
例えばその中には15878456や-58755648など、極端に大きかったり小さいという特徴はあるものの様々な数値が混ざっているのです。
最大値、最小値、を求めるだけでは出しきれない状態で、置換えもいくつも値を指定しなければならない状態です。

希望としては
X<0
100<X
で、Xを検出する方法、というようなものを探しています。