Excelでお知恵を貸して下さい(セル内の最大値抽出方法)

質問

Excel2000使用者です。お世話になります。
A1セルからA100セルまでに数値、文字列混在のデータがあります。
この中から最大値を抽出したいのですがやり方を教えて下さい。
例えば．．こんな感じでデータだあります。

［A1］［A2］［A3］［A4］［A5］
［1］ ［2］ ［5-8］［6］［9-10］

見づらいかも知れませんが、A1,A2,A4セルが数値でA3,A5セルが文字列です。
この場合だと、A5セル内の「10」という最大値を数値として他のセルに抽出したいです。

私の乏しい知識では思い浮かびません。
どうかお知恵を拝借させて下さい。
よろしくお願いします。

回答No.1

Wendy02 · ベストアンサー

こんにちは。

文字列混在の抽出方法は、通常の一般関数では不可能だと思います。以下のコードは、文章の中からでも、最大値を探してきます。

設定の仕方:
Alt　+　 F11 (Altを押しながらF11）を押すと、Visual Basic Editor 画面が出てきます。
次に、メニューの[挿入]－[標準モジュール]と開けて、クリックすると、画面が現れますので、以下のコードを貼り付けて、再び、Alt　+　F11 で、画面を閉じます。

後は、一般関数のように、

=findfig(範囲)　....（通常、登録されていない関数は小文字になります。）

とすれば、最大値が現れます。その範囲が、文章であっても、その中から最大値を探してきます。エラー値は無視されます。ただし、Excelの既存のMAX関数のように、現在の方法では、数値を直接、引数に入れることが出来ません。オプションで、=findfig(範囲,True)で、最小値を探すようにしてみました。

　'<標準モジュール>
'--------------------------------------------
Function FindFig(範囲 As Range, Optional オプション As Boolean = False)
　 '文字列混在の中から、最大値を探し出す(オプション=Trueで最小値）
　 Dim Re As Object
　 Dim rng As Range
　 Dim Matches As Object
　 Dim Match As Object
　 Dim myValues() As Double
　 Dim i As Long
　 Set Re = CreateObject("VBScript.RegExp")
　 Set rng = 範囲
　 Application.Volatile
　 With Re
　　　.Global = True
　　　.Pattern = "(\d+)"
　　　For Each c In rng
　　　　 If Not IsError(c) Then
　　　　 Set Matches = .Execute(c.Value)
　　　　 For Each Match In Matches
　　　　　　ReDim Preserve myValues(i)
　　　　　　myValues(i) = .Replace(Match, "$1")
　　　　　　i = i + 1
　　　　 Next
　　　　End If
　　　Next c
　 End With

　 Set Re = Nothing
　 If オプション = False Then
　 FindFig = WorksheetFunction.Max(myValues)
　　Else
　 FindFig = WorksheetFunction.Min(myValues)
　 End If
End Function
'--------------------------------------------

お礼

ありがとうございます。
すばらしいです。大変参考になりました。

回答No.6

imogasi

数字以外がハイフンだけなら、データ-区切り位置-「区切り記号をハイフン」にすると、数字がセルに分離できて、ハイフンは消えます。左詰になったりしてますが、気にせず、
=MAX(A1:B5)のようにすれば、文字列数字も数とみなして、最大値を計算してくれるようです。
一度やってみてください。

お礼

ご回答感謝致します。
是非参考にさせていただきます。
ありがとうございました。

回答No.5

zenjee

No.4の訂正です。B6に入れる式の
=MAX(B1:B6)　　は
=MAX(B1:B5)　　の誤りでした。

回答No.4

zenjee

A列に入力されている文字列はハイホン（－）がないものは数字のみであり、ハイホンは1個でハイホンの右もまた数字であり、またハイホンの右の数字は仮に5字以内だという前提でお答えします。
まずB列を作業列とし、B1に次の数式を入力して下方向にコピーします。
=VALUE(IF(ISERR(FIND("-",A1,1)),A1,MID(A1,FIND("-",A1,1)+1,5)))
この式はA列の文字中ハイホンがないものはそのまま、あるものははハイホンの右の文字を取り出し、式最初のVALUE関数で数値化するという意味です。なお、FINDの部分はSEARCHでもＯＫだし、式最後の「5」というのは5文字という意味ですから10でも15でも構いません。
以上の後、例えばＢ6に　
=MAX(B1:B6)　
と入力し完了です。
これはNo.3kaisendon さんの回答に考え方はほぼ共通していますが、計算式をやや簡略化しています。
なお、A列の文字のうち、ハイホンの左が数字でなく文字（A,Bなど）である場合も成立しますが、ハイホンがなくて数字の左に文字があったりハイホンの右に文字があったりするとこの式が成立しないことはもちろんです。

回答No.3

kaisendon

こんにちは

「文字列」はご質問の例にあるように
２つの正の数字が　-　で区切られている物のみ、という前提で。

　　　　　A　　　　　B
1　　　　1　　　　　1
2　　　　2　　　　　2
3　　　5-8　　　　　8
4　　　　6　　　　　6
5　　　9-10　　　　10

どこか空いている列（どこでもいいですが、仮にB列）を作業列としました。

B1に入った式は
=IF(ISERROR(FIND("-",A1)),A1*1,MAX(LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1))
この式を下方向にフィルコピー
（フィルハンドルのダブルクリックでもいいです）

とした後に、任意のセルで　=MAX(B1:B5)
とすれば質問内容に見合う形では、ご希望の結果が得られると思います。

ただし、この方法では　あ6　10-5-9　1a 等々予期せぬ物には対応していません。
Wendy02さんの作ったユーザー定義関数ならばこれらの物にも対応しているようです＾＾

お礼

ご回答感謝致します。
是非参考にさせていただきます。
ありがとうございました。

回答No.2

prairie-gentian

難しい方法は思い浮かびませんが、結果オーライ出よければ、次のような方法はいかがでしょうか？［9-10］のように、間に入る文字が[-]であるとしてですが・・・。

該当する列を選び、「データ」「区切り位置」で「区切り文字」を「その他」(ほかのチェックはすべて外す)「-」に設定してやれば、[5]と[8]が二つの列に切り離されます。

その後で、max関数で取り出してはいかがでしょう？

お礼

ありがとうございます。
是非参考にさせていただきます。