Excelの時刻の12時間表記

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

Excel の書式設定の仕様

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

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

違和感を感じたのは、12:00 AM12: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

ついでに確認したこと

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

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

特に変わったことは起きなかったのですが、これで分かったことは、hAM/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 = “@”」を入れてから貼り付けるようにすればよさそうです。

参考

サクラエディタで固定長を改行ありに変換

サクラエディタを使って固定長(改行なし)のテキストデータを改行ありに変換する方法を解説します。

環境

  • Windows
  • サクラエディタ(v2.4.1)※バージョンに依存しないはず

方法1. 折り返し位置に改行をつける

  1. サクラエディタで固定長(改行なし)のファイルを開 く
  2. メニューバーの[設定] > [タイプ別設定] をクリック
  3. タイプ別設定ウィンドウの[スクリーン]タブのレイアウト内で下記を指定して[OK]ボタンをクリック
    • 折り返し方法:”指定桁で折り返す”
    • 折り返し桁数:レコード長(文字数)
  4. 指定したレコード長で折り返した状態で表示される
  5. [Ctrl] + [A] で全選択
  6. メニューバーの[編集] > [折り返し位置に改行をつけてコピー]
  7. 全選択された状態で、[Ctrl] + [V] で貼り付け
  8. [Ctrl] + [S] で上書き保存

これで、指定したレコード長の固定長(改行あり)のテキストファイルが出来上がります。
上記の手順で付加される改行はCRLF になるので、LF で作成したい場合は、上書き保存する前に置換機能でCRLFLF に置換するか、名前を付けて保存で改行コードをLF で保存します。

方法2. キーマクロ

1. キーマクロファイルの作成

下記の内容を記述したテキストファイルを作成します。

S_GoFileTop(0);  // ファイルの先頭に移動
S_ReplaceAll('(.{256})', '$1¥¥r¥¥n');  // すべて置換
S_ReDraw(0);  // 再描画
  • S_ReplaceALL の第一引数に、レコード長(文字数)を指定する
  • S_ReplaceALL の第二引数に、追加する改行コードを指定する
    • CRLF の場合は ¥¥r¥¥n
    • LF の場合は ¥¥n

キーマクロのファイルは、下記の形式で保存します。

  • 改行コード:CRLF
  • 文字コード:UTF-8(BOM付)
  • ファイル名の拡張子:.mac

2. 固定長(改行なし)ファイルの変換

  1. サクラエディタで固定長(改行なし)のファイルを開く
  2. メニューバーの[ツール] > [キーマクロの読み込み] をクリック
  3. Open ウィンドウで作成したキーマクロのファイルを選択して[OK]ボタンをクリック
  4. メニューバーの[ツール] > [キーマクロの実行] をクリック
  5. [Ctrl] + [S] で上書き保存

これで、指定したレコード長の固定長(改行あり)のテキストファイルが出来上がります。
付加する改行の種類は、キーマクロのファイルで指定しておくことができます。

方法1と方法2のどちらを使うか

方法1と方法2の使い分けについて少し考えてみます。

方法1. 折り返し位置に改行をつける

感覚的に操作できるので、覚えておくと何かと便利そうです。スポットで対応する時はこちらの方法の方が素早く対応できそうです。

方法2. キーマクロ

キーマクロのファイルを準備する必要がある分、スポットでの対応には不向きかもしれませんが、定例作業や同じ作業を繰り返す場合は、一度キーマクロのファイルを準備しておけば、こちらの方が正確に素早く作業できるのではないかと思います。手順書に従って作業をする場合は、手順書とキーマクロのファイルを共有して行う感じでしょうか。

あとがき

今回解説した方法は、テキストデータに含まれる文字がASCIIコードのみであれば問題ないですが、文字コードがShift-JISでASCIIコード以外の文字を含む場合は、方法2は使えなかったりUTF-8でASCIIコード以外の文字を含む場合は、どちらも使えなかったりします。

固定長でUTF-8というのを見たことないので何ともいえませんが、UTF-8ではこの方法は使えないと考えた方が安全なのかもしれないです。