【関数】文字列形式の時刻のデータ型を「時刻」(シリアル値)に変換できる「TIMEVALUE」の使い方
AさんAさん

見た目は時刻なのに、なぜかピボットテーブルで集計した際、時間などでグループ化されない場合があります。。
こうした場合、どうすれば良いですかね?

森田森田

その場合は、関数のTIMEVALUEを活用すると良いですよ!
では、TIMEVALUEの使い方について解説していきますね。

はじめに

この記事は関数の概要を把握していることが前提です。

参考記事

関数の概要については以下の記事をご参照ください。

元データの時刻のデータ型を「文字列」→「時刻」へ変換したい場合は「TIMEVALUE」が有効

実務では、見た目のデータは時刻なのに、なぜか時刻特有の機能が使えないケースがあります。

たとえば、次のように「時刻」列を行ラベルにしたピボットテーブル作成時、自動的にグループ化されないといったイメージです。

この原因は「データ型」です。
データ型は、ざっくり「データの種類」だと思ってください。

上記の「時刻」列の数字はすべてデータ型が「文字列」になっていたため、時刻を対象にした機能を使用できませんでした。

よって、事前にデータ型を「時刻」に変換することが必要です。

こんな場合、関数の「TIMEVALUE」を使うことで、文字列化された時刻のデータ型を「時刻」へ自動的に変換することが可能となります。
ちなみに、TIMEVALUEは「タイムバリュー」と呼びます。

よって、文字列化された数字のデータ型を「時刻」へ変換したい場合はTIMEVALUEを使用していきましょう。

参考記事

文字列化された数字のデータ型を「数値」へ変換したい場合はVALUE、文字列化された日付のデータ型を「日付」へ変換したい場合はDATEVALUEを使用してください(詳細は以下の記事を参照)。


TIMEVALUEの構文

TIMEVALUEの構文は以下の通りです。

=TIMEVALUE(時刻文字列)
文字列で表された時刻を、シリアル値(0(午前0時)から0.999988426(午後11時59分59秒)までの数値)に変換します。数式を入力後に、数値を時刻表示形式に設定します。

引数名 必須 データ型 説明
時刻文字列 数値
※実質は文字列
変換対象のセル(値が文字列化された時刻)を指定します。

引数「時刻文字列」に文字列化された日付/時刻以外の値を指定すると、エラー値「#VALUE!」が返ります。
引数「時刻文字列」に指定した値に日付の情報が含まれていても無視されます。

【参考】TIMEVALUEは「日付/時刻関数」

あくまで参考情報となりますが、TIMEVALUEがリボン「数式」タブの関数ライブラリの「日付/時刻」に分類されています。

実際にTIMEVALUEを活用する際は、以下で解説しているように直接入力で挿入していきましょう。

TIMEVALUEの使用結果イメージ

TIMEVALUEで文字列化された時刻のデータ型を「時刻」へ変換したイメージは以下の通りです。
今回は「時刻」列のデータ型を「時刻」へ変換しました。

I列の結果が独特の数値になっていますが、これは「シリアル値」というExcel上の時刻/時刻を管理する数値を意味します。

シリアル値は、190011日を起点に何日目かをカウントした数値です。
シリアル値の「1」は1日(=24h)となり、時刻の場合は時間換算した結果を小数点で示します(1h=1/24h1m=1/24h/60m1s=1/24h/60m/60s)。

あとは任意の時刻の表示形式を設定すれば、時刻形式で表示されます。

なお、TIMEVALUEは原則一つの関数で一つのセルのみが変換対象です。

ベースの数式をセットしたら、他のセルへペーストしましょう。

【参考】VALUEDATEVALUETIMEVALUEの違い

類似機能の関数であるVALUEDATEVALUETIMEVALUEの違いを把握しておきましょう。

それぞれの関数でA列の値を引数にした結果は以下の通りです。

ご覧の通り、実はVALUEでもシリアル値への変換ができ、DATEVALUEは日付部分、TIMEVALUEは時刻部分にそれぞれ特化していることが分かります。

よって、整理すると次のように使い分けると良いでしょう。

  • 数値の場合:VALUE一択
  • 日付のみの場合:VALUEDATEVALUE(結果は同じ)
  • 時刻のみの場合:VALUETIMEVALUE(結果は同じ)
  • 日付+時刻の場合:必要な部分が日付+時刻ならVALUE、日付のみならDATEVALUE、時刻のみならTIMEVALUE

VALUEの対応範囲の広さが分かりますね。

TIMEVALUEの数式の挿入手順

上記の結果を得るための手順は以下の通りです。

  1. 関数を挿入するセルを選択
    ※今回はI2セル
  2. =ti」と入力
  3. サジェストから「TIMEVALUE」を選択し、「Tab」キーで確定
  4. 変換対象のセルを選択
    ※今回はC2セル
  5. Enter」キーで確定
  6. 1行目の数式をコピーし、以降のセルへペースト
    ※今回はI3~I21セルへペースト

手順②の際にIMEを半角英数モードにすること。
テーブルの場合、手順⑥は不要(全レコードへ数式が自動的にコピーされる)。

サンプルファイルで練習しよう!

可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。

サンプルファイル_ワークシート関数_TIMEVALUE.xlsx

※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
(上記リンクから登録フォームへ遷移します)

ブックを開いたら、次の手順を実施してください。(今までの解説のまとめです)

  1. 関数を挿入するセルを選択
    ※今回はI2セル
  2. =ti」と入力
  3. サジェストから「TIMEVALUE」を選択し、「Tab」キーで確定
  4. 変換対象のセルを選択
    ※今回はC2セル
  5. Enter」キーで確定
  6. 1行目の数式をコピーし、以降のセルへペースト
    ※今回はI3~I21セルへペースト

本記事の解説と同じ結果になればOKです!

さいごに

いかがでしたでしょうか?

TIMEVALUEはデータ型の変換に役立つ関数のうちの一つです。

定期的にデータ型を変換する機会があるなら、ぜひ覚えておいた方が良いですね。

なお、TIMEVALUE以外にもExcelでのデータ整形の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。

ご参考になれば幸いですm(_ _)m

森田森田

TIMEVALUEとセットで覚えておいた方が良い関数は、文字列化された数字のデータ型を「数値」へ変換するVALUEと、文字列化された日付のデータ型を「日付」へ変換するDATEVALUEです。
他にも、セルの値のデータ型を特定できるTYPEも覚えておくとベターですね。