～時間～分 を数値として認識させる方法

質問

別のところからコピーしてきたデータは、
セルＢ2　18時間15分、
　　Ｂ3　3時間、
　　Ｂ4　30分
などの表示形式になっているテキストデータです。
これを、Ｂ2＋Ｂ3　のような、計算にそのまま用いることのできるような、セルの書式設定の方法はあるでしょうか？

逆に数値データを　～時間～分　の形式で表示させる方法は何通りか知っています。
また、別の列を用いれば、find("時間",B2)　などを使って、数値だけを抜き出す方法はわかります。

私が知りたいのはあくまでも、新たな列を作らなくても　18時間15分　を　18:15　だと認識させ、3時間　を　3:00　だと認識させ、30分　を　0:30　だと認識させる方法があるかないかです。
数値をテキストにすることは容易でも、
テキストを数値として認識させることはできませんか？

よろしくお願いします。

回答No.1

tom04 · ベストアンサー

こんばんは！

＞私が知りたいのはあくまでも、新たな列を作らなくても　
他の列に関数で表示させるのは簡単ですが、
どうしてもB列そのものを時間としてのシリアル値として認識させたい！というコトですね！
VBAでの一例です。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long
For i = 2 To Cells(Rows.Count, "B").End(xlUp).Row
With Cells(i, "B")
If InStr(.Value, "時間") > 0 Then
.Value = Replace(Replace(.Value, "時間", ":"), "分", "")
ElseIf InStr(.Value, "分") > 0 Then
.Value = "0:" & Replace(.Value, "分", "")
End If
.NumberFormatLocal = "[h]:mm"
End With
Next i
End Sub 'この行まで

※　関数でやるべきコトを単純にコードにしただけです。
他に良い方法があればごめんなさいね。m(_ _)m

お礼

こんにちは。
昨日アクセスできなくてすみません。

マクロを示してくださった、ということは、やはり、普通のエクセルの機能では
　　３時間＋２時間
という仕様が用意されていないのですね。

わざわざコードまで完成してくださいまして、ありがとうございました！

回答No.3

MackyNo1

>相手先から、何時間何分、何円、というデータをいつも（ネットで）もらうのです。
エクセルにコピペするときに、いちいち　FIND　SUBSTITUTE　MID　VALUE　などを使わなくても計算に用いることはできないか、といちるの望みをかけておりました。

すでに回答したように、特定の文字列以外ではシリアル値に変換できません。

たとえば、エクセルでもともと用意されている「3：20」のような表現は「時刻」であって、「時間」ではありません。
したがって、文字列でも時刻(時間）と認識できるのは、日本語表記なら、「３時間15分」ではなく「3時15分」のような表記にしなければなりません。
すなわち、Ctrl+Hで置換ダイアログを出して、検索する文字列に「間」と入力して「すべて置換」するような操作が実戦的には最も簡便なシリアル値に変更する方法です。
この場合でも必ず「時」と「分」が含まれていないとシリアル値に変換できませんので、このようなデータ範囲を集計するには、少しレベルの高い配列数式などを駆使する必要が出てくるわです。

同様に3000円のような文字列も数字と認識できませんので「円」を一括削除するしか方法はありません。
ちなみに円を数字として認識させる文字列は「￥123」のような形式しかありません(一般的にユーザー定義で設定するような特殊な表示形式はシリアル値と認識されません）。

お礼

お礼に反応してくださってありがとうございます。
読むのが遅くなって申し訳ございません。

やはり、エクセルはまだそこまで気がきくわけではない、ということですね。

３時間１５分、でもらったデータを、間を消すような置換をする、という方法は見落としておりました。参考になります。

すこし話がそれていきますが、欲を言えば、ユーザーがリストも定義できるのだから、リスト順位で認識してくれたらいいのに。
例えば、一、二、三、や、Ａ、Ｂ、Ｃ　など・・・。
ありがとうございました。

回答No.2

MackyNo1

＞これを、Ｂ2＋Ｂ3　のような、計算にそのまま用いることのできるような、セルの書式設定の方法はあるでしょうか？

基本的に、エクセルが時刻と認識できる書式以外の文字列の時間を関数で集計することはできません。

たとえばA1セルに入力された今回のような文字列の時間を計算可能なシリアル値に変換したいなら、以下のような数式で変換することができます。

=SUBSTITUTE(SUBSTITUTE(A1,"時間",":"),"分","")*IF(ISNUMBER((FIND("時間",A1))),1,1/24/60)

したがって、特定の範囲、たとえばA1:A3セルの文字列時間を集計したいなら、上記のA1の部分をセル範囲にした以下のようなSUM関数で計算することができます。

=SUM(SUBSTITUTE(SUBSTITUTE(A1:A3,"時間",":"),"分","")*IF(ISNUMBER((FIND("時間",A1:A3))),1,1/24/60))

配列数式ですので入力後Ctrl＋Shift+Enterで確定してください。

お礼

こんにちは。
昨日アクセスできなくてすみません。

関数を駆使する方法は私自身も慣れているのですが、
わざわざ書いてくださいましてありがとうございました。

相手先から、何時間何分、何円、というデータをいつも（ネットで）もらうのです。
エクセルにコピペするときに、いちいち　FIND　SUBSTITUTE　MID　VALUE　などを使わなくても計算に用いることはできないか、といちるの望みをかけておりました。

＞　基本的に、エクセルが時刻と認識できる書式以外の文字列の時間を関数で集計することはできません。

エクセルは商品として十何年経ってもいまだにそういう仕様なのですね…。
「私が知らないだけ」という期待はもろくも砕けました。

例えば、
変な話ですけど
Ａ1　　2月1日
Ａ2　　3月3日
のときに、
Ａ3　　=A1+A2
という計算をすることができますよね。
これは、
　　入力した時は　2月1日　でも、内部的にシリアル値に自動的に変換される
わけです。
まあ、これがうっとおしい時もありますが。

また、
Ａ4　　'３月５日
という　全角数字の日付　のテキストデータでも、
Ａ5　　=A4*2
と数式で参照すると、途端に数値データとして認識してくれます。

ＣＳＶファイルから貼り付けたときに、そのままだと数値として認識してくれないけれども、
Ｆ２などを使ってセルの中をのぞいて、もう一度エンターで確定すると、数値に生まれ変わる、
というケースもありますよね。

私は、何時間何分、でもこれと同じことを期待していたのです。
0.666666　のようなシリアル値にさえ変われば、後は書式設定によって
16:00:00　に変えることができる、と。

質問には書きませんでしたけど、何円　についても同様のことを期待していました。

マイクロソフトもなかなか気が回らないな、という印象です。
2000円＋4000円
も仕様で用意してくれたらいいのに。
（書式設定で、2000　を　2000円　と表示させながら、数値属性は残す方法は知っていますよ。TEXT関数より、こちらの方が好きです。）

後は、あまり使いたくないですけど、ユーザー定義関数を作って、別の行に、何時間何分　を参照させるか、ですね。
列の並びが変わるのでうれしくないですが・・・。

うーむ、ないものねだりをしている私が悪いのでしょうね。
かなり長いこと、募集を続けようと思います。
改めて、ありがとうございました！