[[20031228061804]]　『文字列から数値を抽出』（澄男）　ページの最後に飛ぶ

『文字列から数値を抽出』（澄男）

ひとつのセルの中に、『晴れ24度』と表記してあり、
それが列としてデータが膨大にあるのです。

で、平均の気温を求めたいのですが、『晴れ』と『度』が邪魔してるのでしょうか、
平均のオートSUMが使えません…

なにか方法がありましたら、ご教授願えませんでしょうか…

 １．編集するデータを選択して
 ２．[編集]-[検索]-[検索する文字列]に「晴れ」を入力
 ３．[置換]で[置換後の文字列]に何も入力せず[すべて置換]
 ４．曇り、雨、度等も上記と同様に

 一括で、となるとマクロになるかと思いますが、私は無理　(アットホーム）
 ただし、データを変更しますので、データを別にコピーしてから行うことを勧めます。

 関数だと
 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"晴れ",""),"雨",""),"曇り",""),"度","")*1
 と入力して、下にコピー

迅速な回答ありがとうございました。
即、解決いたしました。

ただ、おっしゃるとおりデータを改ざんしてしまうのですね。
まぁ、欲は言えませんが(^^ゞ

本当にありがとうございました。

 解決したようなんで。

 >(;_;)(ケン）二日酔いで頭痛い～
 頭の痛いところで、これ、ユーザー関数作って遊びまへんか？
 　　　至って快調（弥太郎）

 よ～し、作ってみるか～。（ケン）

ユーザー関数、作っているうちに解決しちゃいました・・・。しくしく。
せっかくなので、ケン、弥太郎両氏様、わたしも参加させて？
見よう見マネ。

 Function kion(data As String)
    Dim num As String
    Dim i As Long
    Dim s As Long
    Dim f As String
    Application.Volatile

    s = Len(data)   
    For i = 1 To s

           f = Mid(StrConv(data, vbNarrow), i, 1)

        If IsNumeric(f) Or f = "-" Then
           num = num & f
        End If

    Next i

    kion = num * 1

 End Function

 (KAMIYA)

あた～っ
若旦さんに先手取られた～っ！

 　　先手必勝を狙うとった（弥太郎）

 Function avrg(data As Range)
    Dim i As Integer, Cnt As Integer
    Dim totl As Double
    Dim c, n
    Application.Volatile
    For Each c In data
        c = StrConv(c, vbNarrow)
        For i = 1 To Len(c)
            On Error Resume Next
            n = (Mid(c, i, 1)) * 1
            If n <> "" Then
                totl = totl + (Mid(c, i, Len(c) - i)) * 1
                Cnt = Cnt + 1
                n = ""
                Exit For
            End If
        Next i
    Next c
    avrg = "平均気温" & Application.WorksheetFunction. _
                    Round(totl / Cnt, 1) & "度"
    On Error GoTo 0
 End Function

 チェックチェック。
 若旦さん、これ=kion(a1:a6) と言った案配に入力旦（これシャレ）なぁ？
 へへへっ、#VALUEがでよるで～。
 　　（弥太郎）

 kion(data As String)
 なので、私のは単に「数字交じり全半角文字列」から数値だけを抜き出す
 だけですからねぇ。
 いっきに平均出すところまでは考えてませんでした。はい。
 自分でも使いたい部分だけ考えているとだめですね。
 ユーザー関数は弥太郎さんのほうが一日の長あり、ですね。
 (KAMIYA)

 ふぁ～。寝てしまった。KAMIYAさんまで出てきてる・・・
2人のを参考に勉強さしてもらおっと。
ダメダメ（ケン）

 コホン、はい、エラーは回避されましたが、晴れ２４．２度が 242と表示されます。
 これをナントカしてくれないと平均温度が１００度を超してしまします。
 お願いします。
 　　　なにかとうるさい（澄男氏の代理人）

 そうでござった。
 整数とは限りませんもんねぇ

 Function kion(data As String)
 'VER1.2

    Dim num As String
    Dim i As Long
    Dim s As Long
    Dim f As String
    Application.Volatile

    s = Len(data)
    For i = 1 To s

        f = Mid(StrConv(data, vbNarrow), i, 1)

        If IsNumeric(f) Or f = "-" Or f = "." Then
           num = num & f
        End If

    Next i

    If Right(num, 1) = "." Then
    num = Left(num, Len(num) - 1)
    End If
    kion = num * 1

 End Function

 しかし、代理人さん。
 「avrg」も暖かいところでしか使えませんよ。
 (大阪よりは寒い地方の住人KAMIYA)

あっ！

 えらいイチャモン付けられた。検証、検証。
 もうちょっと待ってくれたら、希望の星にも同じチョンボを指摘でけたかも...。　　　     他人のチョンボを待ってる   （弥太郎）

 これでどうでっか、KAMI様？
 いま、息子が帰省ましたさかいバタバタしとってチョット付け加えただけですけど。
 今年は最後の最後まで面倒見て貰いましたナ。（笑）

 それと IsNumericんとこ解説願えたら有り難いんですけど...。
 　　　神頼みの（弥太郎）

 コピーするんを忘れとった。
 Function avrg(data As Range)
    Dim i As Integer, Cnt As Integer
    Dim totl As Double
    Dim c, n
    Application.Volatile
    For Each c In data
        c = StrConv(c, vbNarrow)
        For i = 1 To Len(c)
            On Error Resume Next
            n = (Mid(c, i, 1)) * 1
            If n <> "" Then
                If Mid(c, i - 1, 1) = "-" Then
                    totl = totl - (Mid(c, i, Len(c) - i)) * 1
                Else
                    totl = totl + (Mid(c, i, Len(c) - i)) * 1
                End If
                Cnt = Cnt + 1
                n = ""
                Exit For
            End If
        Next i
    Next c
    avrg = "平均気温" & Application.WorksheetFunction. _
                    Round(totl / Cnt, 1) & "度"
    On Error GoTo 0
 End Function

 あ～だめだ、#VALUE!が消えない。まだまだグルグル回るだけ・・・
精進しなくては・・・頭グルグル（ケン）

 痛い頭を後から叩いてみましょうか～？
 　　いっぺん頭痛くなるまで呑んでみたい（弥太郎）

 いえ、 
違うんですよ。二日酔いはもうスッキリなんですが、頭が悪くて・・・
　(ケン）以前教えて頂いたAscWを使って考えていたのですが・・・

 なにおぅ！　頭悪いですんだら警察はいらんのじゃ！

 結局、今日、田舎へ出撃しようと思ったんですけど
 ヤボ用で明日にしますた。

 （ramrun）酒でも飲むか～

参考 [[20020902163649]]『123個から123だけ取り出したい』（エクセル初心者）

 VBEの[ツール]-[参照設定]より、Microsoft VBScript Regular Expressions 1.0を
 チェックしないと使えません（多分5.5でもよいと思う）。

 Function nEx(rng As Range) As Double
 Dim reg As New RegExp
 reg.Global = True
 reg.Pattern = "[^-?\d+(\.\d)?]"
 For Each ad In rng
    nEx = nEx + reg.Replace(ad.Value, "")
 Next ad
 nEx = nEx / rng.Count
 End Function

 ついに（見かねたのか）大御所様が御登場されてしまいました。
 こんなにコンパクトになるんですね。
 VBまではまだ手が回りません。ううっ。
 BASICのクセが抜けないせいか、
 どうも分岐にIFやラベルを多用してしまいます。
 精進精進。

 Function kion(data As Range)
 'VER2.1　範囲指定/平均値算出版
    Dim num As String, f As String
    Dim i As Long, flag As Long
    Dim total As Double
    Application.Volatile
    For Each c In data
        If Len(c) = 0 Then GoTo SKIP
        For i = 1 To Len(c)
            f = Mid(StrConv(c, vbNarrow), i, 1)
            If IsNumeric(f) Or f = "-" Or f = "." Then
               If num = "" Then flag = flag + 1
               num = num & f
            End If
        Next i
        If Len(num) <> 0 Then total = total + num * 1
        num = ""
 SKIP:
    Next c
    kion = Application.WorksheetFunction. _
        Round(total / flag, 1)
 End Function

 IsNumeric関数はヘルプの通りですが、
 引数が数値として認識できる場合は、Trueを、それ以外はFalseを返します。
 弥太郎さんのコードと違って、一字一字チェックしてるわけですが、
 ちょっと処理が冗長に過ぎますか。
 (KAMIYA)

 チェックチェック
 旨い事いってます。
 ただ１つだけGoTo SKIPは
 If Len(c) <> 0 Then 'GoTo SKIP
 で処理できますわなぁ。
 いいええな、以前にやたらGoTo（便利やからすぐ使うてまう）を使うてましたんやけど
 こんなん「マカロニコード」や言うて「くらはい教授」に指摘されましてナ、やむを得
 ない場合にしか使用せんようになりましたわ。
 ほれとtotal/flagですけどナ、これもくらはい教授のコードを流用してdata.Countで
 事足りるみたいですわ。勿論私のコードもですヨ。
 「流用して」と言うと聞こえが悪うおますけど、まぁ、綺麗な言葉で言うたら「パ
 クリ」ですわ、えぇ。
 そやけど、KAMIYAさんのんはflagが要るみたいですナ。ごめん。
 私のは変数Cntが不要になりましたわ。儲け儲け。
 IsNumericがヘルプ読んでもも１つピンときまへんねん。
 なんとかわたしのコードに差しこめんかいなぁと頑張ってみましたけど今んとこ入り
 まへんわ。理解でけてない証拠でっしゃろなぁ。

 チェックチェック
 くらはい教授のコード#VALUE!がでまんがな。
 またケンショウブソク言うんとちゃいまっか？（笑）
  Microsoft VBScript Regular Expressions 1.0をチェック済み
 証人でっか、ほんなら私の救世主　KAMIYAはん、どないでっか？
 それと"[^-?\d+(\.\d)?]"　は何の暗号でっか？さっぱり分かりまへん。（笑）
 酒呑みながらでもかめしまへんさかい教えておくんなはれ。
 　　チェックマン＆パクりマン（弥太郎）

 グフフ、グフフ、許容を超えたんで、思考能力が下がってるやんけ。
 ほぉれ、どないな数値を当てたんかいうてみんきゃい。
 弥っちゃんよ～　ほれっほれっ。

 （ramrun）ぼちぼちやばいで

 や、やっぱし、お、お酔いになってまっ　　かぁ～？
 ほ、ほんなら、こここれでどないでんねんんん。
 晴れ１３．５度
 　曇り１２．２度
 　雪-2.2度
 曇り５．３度

 ふ、二日酔いになった　っっっちゅうて、明日もい、い、田舎へ帰られへんで、
 パ、パ、パソコンノ　まえにすわっとおんとちゃいまっしゃろなぁ、～ん。
 ほ、ほれっほれっ　、～～ん？
 　　こっちもやばいで（やたろうう）
 ほ、ほの状態で、タ、タイピング、やって　　みナ。ふふ～。

 パソコンの前に？！
 座っとっちゃぁ、いかんのんか。
 おいらは回るところがぎょうさんあるんや。
 エロサイトとか、エロサイトとか、エロサイトとか。

 おおぅ、全角がめっさだめやんけっ。
 こんてけしょう、これできゃったとおもうにゃよ。

 （ramrun）きゃいぃ～ん

 Function nEx(rng As Range) As Double
 Dim reg As New RegExp
 reg.Global = True
 reg.Pattern = "[^-?\d+(\.\d)?]"
 For Each ad In rng
    nEx = nEx + reg.Replace(StrConv(ad.Value, vbNarrow), "")
 Next ad
 nEx = nEx / rng.Count
 End Function

’’ヒューッ’’

 で、でけたがな、てけしょう。
 ほ、ほんならなぁ、気温のしょ、しょ、小数点以下２けたっっっちゅうんは実用的
 ヤにゃ、にゃいんとちゃいめっかぁ？ヒヒ　　ヒック。
 "[^-?\d+(\.\d)?]"←ふ～っ、コピーでしのげた。
 こ、これの解説は、どない、どないなってまんねん。ん～ん？
 わ、若旦さんも、ビリーザ・賢もま、ま、待っとるんに違いないで、まんがな。
 ほ、ほな、ワシ、タイピングコーナーへ帰ってくるわ。ふ～っ。
 　　　たいぴｎＧＵ　Ｎｏアン（へたろう）

 げへへ、げへへ。ケツが青いんや。
 タイピングやってきたで。
 ちゅうても、400打点超えへんと登録できへん。
 でも最高で299打点、ミス72点だったわ。
 あやうくミスでケン坊を抜くとこやったわ。
 なぁに、最低でも240打点やて。
 ケン坊が次に起きるんは、翌日の11時ごろや。
 junちゃんも、ケン坊もおねんねやさかい、
 好きにやらせてもらいまひょか。

 何をみとるんか？
 解説はおいどんのwikiにリンクしてあるやろ？
 しゃ～ないなぁ。
 逝ってこぉぅい。

http://ryusendo.no-ip.com/cgi-bin/ykwk2/wiki.cgi?ExcelVBA

 （ramrun）青春とはこころの様相をいふのだ！

 ・・・・なんだかしばらく席をはずしているうちにい・・・。
 これを読むころには酔いがさめることを期待して、
 nExは私も最初#VALUEが出ました。
 原因はテストデータに空白行や計算に関係ない数字を含まないセルを
 含めていたためで、また、弥太郎さんご指摘の
 total/flagもそのために必要になってしまったのでした。
 どうもイレギュラーなケースを気にしだすと
 その処理をできるようにと考えてしまう[性質（たち）]なので、
 見る見るコードが膨れてしまい、ロジックが混乱してきて
 整理できる前についつい　GoTo　ラベル　を使ってしまうというのが
 真相です。美しいコードというのはまだまだです。
 (KAMIYA)

 【校内での飲酒を禁ず】なんて布告が出ませんよーに。
 って、出てもイミないか。Alt+F4

 リヤカーは引っ張っても引っ張ってものくだりでいつも転けてまう。
 あ、あれ、なんとかしてえな。
 それに、さ、最近得意のドンドンドンドンがでてけえへん。何処へ隠してしもたん？
 >240打点やて
’’うおっ！’’
 もチョットで追いつけるがな。
 ノンどっても、こんなんじゃっちゅて記録の更新しても受け付けてくれへん。
 なんかい、きろくが下がったらアカンのんかい？

 >http://ryusendo.no-ip.com/cgi-bin/ykwk2/wiki.cgi?ExcelVBA
 ええノン見つけたけど、二日酔いでもないのに　アタマ痛うなって　ヤ　メ　タ
 起きにイリに登録したからまた勉強するわ。
 　　もう寝るべ、オヤスミ、ぐろっきーやたろ

 はやいっちゅ～の。
 最近のおこちゃま（ケン坊以外）だって、まだ寝ぇへんで。

 酒屋の若旦那が「飲んだら乗るな」ちゅうとるけど、
 わてが寝るのが先や。
 （ramrun）

 Function nEx(rng As Range) As Double
 Dim reg As New RegExp
 reg.Global = True
 reg.Pattern = "[^-?\d?(\.\d)?]"
 For Each ad In rng
    nEx = nEx + Val(reg.Replace(StrConv(ad.Value, vbNarrow), ""))
 Next ad
 nEx = nEx / rng.Count
 End Function

ゲゲ～ッ

 忘年会パート2に行っている内にすごい事になってる。パート３が終わったらゆっくり
勉強さしてもらおっと。・・・・警察いらん。とほほっ（ケン）

 ramrunさんのコンパイルエラーが出るけど、なんでだろ～お♪なんでだろ～♪
（ケン）

 >VBEの[ツール]-[参照設定]より、Microsoft VBScript Regular Expressions 1.0を
 チェックしないと使えません（多分5.5でもよいと思う）。
 レスの早いのはええけど、早とちりはイケマセン。ケイサツヨビマス。
 　　　　　（弥太郎）

 なるほど、弥太郎さん有難う御座います。実に奥が深い、精進、精進。（ケン）

 KAMIYAはんIsNumericマスターしましたで。
 まあ、見ておくんなはれ。これで私のコードもすっきりしましたやろ。
 クラハイ教授のMicrosoft VBScript Regular Expressions 1.0を使おうと思うたけど
 何やら知らんうちにおんなじようになってもておりじなりてぃーがでえしまへん。
 で、結局、じぶんのコードにIsNumericをかみ合わせて旨い事いきました。

 ところで、キーボードで　/　の対角線がそのとなりのキーにおますわなぁ。
 あれってどないして入力しまんのやろ？あれこれ試してもわかりまへんわ。
 おねがいします。　おせえて。
 　　弥太郎
 Function avrg(data As Range) As String
    Dim i As Integer
    Dim totl As Double
    Dim c, n
    Application.Volatile
    For Each c In data
        For i = 1 To Len(c)
            n = (Mid(StrConv(c, vbNarrow), i, 1))
            If IsNumeric(n) Or n = "-" Then
                totl = totl + Mid(c, i, Len(c) - i) * 1
                Exit For
             End If
        Next i
    Next c
    avrg = "平均気温" & Application.Round(totl / data.Count, 1) & "度"
 End Function

 ＞ところで、キーボードで　/　の対角線がそのとなりのキーにおますわなぁ。
 ＞あれってどないして入力しまんのやろ？あれこれ試してもわかりまへんわ。
 余裕ができたので探してみました。
 「バックスラッシュ」という呼び方はNEC９８を触ってたころから知っていましたが・・・

http://www.salty1.com/backnumber/1050.html

 早速書いてあるとおりにやってみたら、エクセルではセル表示では
 バックスラッシュになるのに、数式バー内では\が表示されます。
 ちょっと不思議。
 ちなみにIMEの変換では「しゃせん」で全角「＼」には変換してくれるようです。
 (KAMIYA)

 早速のレスありがとうございます。（笑）
 そうなんです、キーボードでやると￥が出てきます。
 いいええな、こないだのくらはい教授のMicrosoft VBScript Regular Expressions 1.0
 を手ぇのうちに入れようと中身を覗いたら＼bッちゅうんがあって、これが文字列の中 
 の最初の数値を検索してくれるみたいなんですわ。
 いま、KAMIYAはんのご教授を拝見して、ハタと気付きましたわ。ATOKにも入ってる筈
 やと思うて試してみたら、おました、おました。これで心おきなく正月を迎えられま
 すわ。
 　　ほならええお年を、来年もよろしゅう（弥太郎）歌聞いてるでぇ。

コメント返信：

[ 一覧(最新更新順) ]

[[20031228061804]] 『文字列から数値を抽出』（澄男） ページの最後に飛ぶ

ゲゲ～ッ

[[20031228061804]]　『文字列から数値を抽出』（澄男）　ページの最後に飛ぶ