タグ: Excel

Excelの時刻の12時間表記

Excel の時刻の12時間表記に違和感を感じたという話です。
結論を先に書くと、英語圏の時刻の表現はこうなのだそうです。

Excel の書式設定の仕様

時刻の書式設定 h:mm（24時間表記）とh:mm AM/PM（12時間表記）の表示のされ方を比較してみます。

  • C列とD列の比較です
  • B列に値を入れ、C列とD列はB列を参照しています
Excel time 1

違和感を感じたのは、12:00 AM と12:00 PM です。
感覚的に24時間表記の0時と12時のどちらに該当するのか分からないです。その理由は具体的に以下によるものだと思われます。

  • 12:00 AM の1分前は11:59 AM ではなく11:59 PM
  • 12:00 PM の1分前は11:59 PM ではなく11:59 AM

ついでに確認したこと

書式設定のh とAM/PM の関係が気になったので、書式設定がh と h AM/PM だけのパターンも追加してみました。

  • E列とF列を追加しました（B列を参照しています）
Excel time 2

特に変わったことは起きなかったのですが、これで分かったことは、h はAM/PM の有無で表現が変わるということです。
F列のAM とPM が表示されないだけの状態にはならないようです。

Microsoft のサイトにもこの辺のことが仕様として書かれていました。

AM と PM     形式に AM または PM が含まれている場合、時間は 12 時間のクロックに基づいており、”AM” または “A” は午前 0 時から正午までの時刻を示し、”PM” または “P” は正午から午前 0 時までの時刻を示します。 含まれていない場合、24 時間表示に基づきます。

Microsoft サポート

ちなみに、h AM/PM は「このセルに適用されている数値の書式は誤解を招く可能性があります」という警告を表示してくれます。
その通りだと思います。

午前と午後の定義、時刻の表現のされ方

時刻表記には何かしらの規格がありそうだったので、この仕様の根拠となる情報がないか検索して調べてたのですが、結論としては冒頭で述べた英語圏の時刻の表現のようです。規格は見つかりませんでした。

調べる過程で時刻の扱いについて興味深い話がいろいろあったので、記録として記載しておきます。

  • 午前と午後の定義
    • 正午（太陽が地平線より上で子午線を通過する時刻）より前が午前（AM）、後が午後（PM）
    • 0時から12時間後までが午前、12時から12時間後までが午後ではない
  • 23時間表記の0時と12時の表現
    • 0時は「午前0時」、「午後12時」のどちらで表現してもよい
    • 12時は「午後0時」、「午前12時」のどちらで表現してもよい
  • アナログ時計の影響
    • アナログ時計が時刻を1から12の数字で表現されていることから、以下のような表現が慣習的に使われている
      • 0:30 を「午前12時30分」と表現
      • 12:30 を「午後12時30分」と表現
  • 英語圏の時刻の表現
    • 0:00 を「12:00 AM」
    • 12:00 を「12:00 PM」

あとがき

時刻を正確に伝えるのであれば、24時間表記（0時〜23時）で表現すれば間違えなさそうです。

ただ、19:00 であれば「午後7時」と表現した方が、相手に19時－12時＝7時（午後）のような計算をさせずに済むので、日常会話では午前/午後を付けた12時間表記で表現した方がよさそうです。
問題の0時台と12時台ですが、これは午前/午後ではなく昼/夜を付けた12時間表記で表現すれば間違えて伝わることを避けることができそうです。

Excel 以外はどうなのだろうと思い、Google スプレッドシートとLibre Ofiice を確認してみるとExcel と同じでした。Excel だけの独自仕様ではないようです。

英語圏では、システムで時刻をAM/PMで表現したデータを正しく時系列順で表示できているのだろうか？ということが気になったので、とある米国企業が提供する運用監視ツールを見てみました。
一覧上の日時を表示する項目がデフォルトで英語圏の時刻の表現になっていましたが、データの並び順は正しい状態になっていました。「2023/9/14 11:59 PM」の1分後が「2023/9/15 12:00 AM」、「2023/9/15 12:59 AM」の1分後が「2023/9/15 1:00 AM」です。違和感ありまくりです。

参考

Excelで10進数→16進数の変換

はじめに

Excelで10進数を16進数に変換する方法を整理してみました。

3つの方法がありましたが、使い方や変換できる10進数の範囲が異なっていたので、意識して使い分けするとよさそうです。

関数名10進数（最小）10進数（最大）
ExcelDec2Hex-549,755,813,888549,755,813,887
VBAhex-2,147,483,6482,147,483,647
PowerShell[convert]::ToString-9,223,372,036,854,775,8089,223,372,036,854,775,807

3つ目のConvert クラスのToString メソッドは、Excel VBA のPowerShell コマンドを使って呼び出して使います。
指定する10進数はInt64（64ビット符号付き整数）で、3つの中で一番大きい数値を変換することができます。

使い方

Excel のDec2Hex 関数

Dec2Hex 関数に10進数の値を指定します。例えば、10進数の値が1234567890 の場合、セルに以下のように入力します。

=Dec2Hex(1234567890)

Excel VBA のhex 関数

Excel VBA のエディタ上で以下のようにコードを記述して使います。

Dim lngDec As Long
Dim strHex As String
lngDec = 1234567890
strHex = hex(lngDec)

PowerShell のConvert クラスのToString メソッド

Excel VBA のエディタ上で以下の関数を作り、呼び出して使います。

Function Dec2HexCustom(pDec As Currency) As String
  Dim objShell As Object
  Dim strCmd As String
  Dim strHex As String

  strCmd = "[convert]::ToString(" & pDec & ",16)"
  Set objShell = CreateObject("WScript.Shell").Exec("powershell -Command " & strCmd)
  strHex = objShell.StdOut.ReadAll
  Dec2HexCustom = Left(strHex, Len(strHex) - 2)
End Function
  • (6行目) PowerShell のコマンドは [convert]::ToString(1234567890, 16)
  • (7行目) PowerShell のコマンドを呼び出して変換している
  • (9行目) PowerShell のコマンドの実行結果を標準出力で受け取ると末尾に改行(CRLF)が付加されるので、改行(CRLF)に該当する右2文字を除去している

あとがき

Excel VBA でPowerShell のコマンドを実行すると、処理がもたついてあまりパフォーマンスがよくなかったです。
Excel の表から複数行を読み込んで繰り返し実行する場合は、小さい数値はhex 関数にやらせて、大きい数値のみPowerShell のConvert クラスのToString メソッドを使うように実装した方がよさそうです。

その他、注意事項として、変換後の16進数の値をセルに貼り付ける場合、変換後の16進数の値が「数字 + “E” + 数字」だと、値が指数表記と誤認されて正しくセットされなかったりします。
例えば、「12345678E90」の場合は「1.2345678E+97」に変換され、「1234567E890」の場合は変換されずそのままセットされるなど、何かと厄介です。
貼り付け先のセルの書式を文字列にすれば解消できるので、元から文字列にしておくか、値を貼り付ける直前に文字列にする処理「.Cells(x, y).NumberFormatLocal = “@”」を入れてから貼り付けるようにすればよさそうです。

参考