Excelに関する質問です。 あるデータベースのテキストファイルをExcelで開こうとしました。 そのファイルの時刻は「0822(am 8:22)」「0008(am 0:08)」と表示されているのですが、Excelで開くと、「0822」→822、「0008」→8 と表記されています。 セルの書式設定のユーザー定義にて見た目を「0822」「0008」と表示することは可能でした。しかし当初の目論みとしてはLEFT関数で「時」を抽出し、RIGHT関数で「分」を抽出した後、「時」と「分を60で割ったもの」を足すことで、24時間表記を可能にし、各時間帯にでた商品の数をカウントすることを考えておりました。 実際には、LEFT関数で左の2桁を表記させようとすると「0822」は「82」となってしまいます。「0008」にいたっては「8」となり、「0」を認識してもらえません。 このような状況をふまえて、時刻の4桁表記を24時間表記へ変換させるようなうまい方法はありませんでしょうか? 皆さんのお知恵を貸していただきたいと思います。

回答の条件
  • 1人2回まで
  • 登録:2008/05/03 22:52:04
  • 終了:2008/05/10 22:55:02

回答(5件)

id:virtual No.1

virtual回答回数1139ベストアンサー獲得回数1282008/05/03 23:06:40

ポイント20pt
=INT(A1/100)/24+(A1-INT(A1/100)*100)/24/60

変換する対象の数字がA1セルに入力されているとして、上記の式で表示される数字を書式設定で時刻にすれば24時間表記に変換することができます。

id:kiyonorida

なるほど。私の稚筆のために聞きたかったことが伝わりにくかったことが残念ですが、ものすごく勉強になりました。こういう方法で24時間表記可能なんですね。参考になりました。ありがとうございます。

2008/05/05 10:36:08
id:pahoo No.2

pahoo回答回数5960ベストアンサー獲得回数6332008/05/03 23:06:44

ポイント20pt

Excelで開いたときに、整数と認識されているようですね。この認識処理はそのままにしておくことにします。

そのファイルの時刻が A1 に入っているとしたら、B1 に下記のような式を書けば、"0822" を得ることができます。

=RIGHT("0000" & FIXED(A1,0),4)

あとは、LEFT関数なり RIGHT関数なりで時分を取り出して下さい。

id:kiyonorida

”&”の利用は思いつきませんでした。 こうすると確かに表記できました。 その後の処理も簡単ですね。ありがとうございます

2008/05/05 10:37:06
id:wizemperor No.3

wizemperor回答回数379ベストアンサー獲得回数522008/05/03 23:08:09

ポイント20pt

他にもっといい方法があるかもしれませんが、とりあえず。


1.0000を追加

2.右から4文字取得

3.2の左から2文字、右から2文字を取得

4.24時間表記に変更


A1に時刻がはいってるとすれば、次のような感じ。

=LEFT(RIGHT("0000"&A1,4),2)

=RIGHT(RIGHT("0000"&A1,4),2)

元のファイルにAM/PMの区別がないなら、汎用性のあるやりかたはないと思いますが、

とりあえず、時・分は上記のような方法でできると思います。

id:kiyonorida

私の意図していた状況がとても簡易に表現できますね。大変助かりました。ありがとうございました。

2008/05/05 10:37:58
id:taknt No.4

きゃづみぃ回答回数13481ベストアンサー獲得回数11982008/05/03 23:13:06

ポイント20pt

A1に値が入っているとして

=RIGHT("0000" & A1,4)

で 4桁の文字列となります。

この RIGHT("0000" & A1,4)を利用して

LEFT(RIGHT("0000" & A1,4),2)とすれば 時間が取得でき、

また

RIGHT("00" & A1,2)とすれば 分が取得できます。

id:kiyonorida

私の意図していた状況がとてもさらに簡易に表現できますね。大変助かりました。ありがとうございました。

2008/05/05 10:42:19
id:minubow No.5

minubow回答回数216ベストアンサー獲得回数52008/05/04 00:09:30

ポイント20pt

100で割り算して、答えの部分が「時」で、余りの部分を「分」とすればよいのではないでしょうか?

数式では、「時」・・・round(A1/100,0)

「分」・・・mod(A1,100)

という具合です。

id:kiyonorida

そうですね。4桁表記ができてしまえば、この表現の方がエレガンスかもしれません。参考になります。ありがとうございました

2008/05/05 10:42:17
  • id:virtual
    見た目だけ24時4桁表記にするなら書式設定のユーザ定義で下記のようにすればできます。

    0#":"0#
  • id:virtual
    あくまでもRIGHT関数とLEFT関数を使って時と分を分けることが目的なら他の回答のとおりなのですが。。。

    >「時」と「分を60で割ったもの」を足すことで、24時間表記を可能

    01:30 -> 1.50 という変換ですよね?
    これが最終目的だったら、私の回答をふまえて、

    =INT(A1/100)+(A1-INT(A1/100)*100)/60

    あるいは、回答5をふまえて、(こちらの方がエレガントですね)

    =round(A1/100,0)+mod(A1,100)/60

    でできますよ。これは4桁表記ができていなくてもOK。単に0~2359までの整数を変換するだけですから。
    入力された時刻フォーマットが正しく、かつ時刻シーケンスさえ正しければいいのなら、

    =A1/100

    だけでも充分目的を果たせると思います。整数部が時刻で小数部が分。
    (01:30 -> 1.30 という変換になります。)

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません