はじめに
microsoft 365のエクセルを使用しています。
エクセルで、Ctrl+;で挿入される日付や、csvファイルを開いたときの日付のフォーマットを、月や日が1桁のときには、1桁目に0を入れたい派です。
2025年1月2日を例にすれば、2025/1/2ではなく、2025/01/02と表示して欲しいのです。(所々1桁だとガチャガチャになって視認性が良くないので)
フォーマットを設定したいセルを選択した状態で、ctrl+1で「セルの書式設定」を開き、「ユーザー定義」>「種類」 を “yyyy/mm/dd” に設定すれば良いのですが、それをいちいち設定する手間を省きたいので、その方法を調べました。
調べたところ、エクセルの「セルの書式設定」>「表示形式」>「日付」>「種類」で、アスタリスク(*)で始まる日付形式は、オペレーティングシステム(OS)で指定する日付/時刻の設定に応じて変わるとのこと。
オペレーティングシステムの日付の設定は、win+iで開くwindowsの設定ウィンドウで、「言語と地域」>「地域」>「地域設定」の右端の”v”をクリックすると表示される「形式を変更」を押すと表示される画面の「日付(短い形式)」で設定されるということがわかりました。
試しに、エクセルのあるセルに「2025/1/2」を入力し、「セルの書式設定」で、このセルの日付のフォーマットとして「*2012/3/14」(OSの設定に従う)を選択した状態で、上記のwindowsの設定で「日付(短い形式)」として「2017-04-05」を選択すると、選択した直後に、「2025/1/2」は「2025-1-2」に変化しました。「日付(短い形式)」として「2017-04-05」を選択したのだから「2025-01-02」となるべき所が「2025-1-2」になるのがまずおかしいです。
次に、「日付(短い形式)」として「2017/4/5」を選択しても、「2017/04/05」を選択しても「2025/1/2」と表示され、所望の「2025/01/02」と表示することはできませんでした。
「エクセルのデフォルト日付フォーマットを”yyyy/mm/dd”にする方法」という題ですが、この時点で、自分の環境(win11 23H2+microsoft365のエクセル)では、どうもできなさそうということになりました。
そこで、次の策として、できるだけ手間をかけずに、選択したセルの日付フォーマットを”yyyy/mm/dd”にする方法を考えました。
日付フォーマットを”yyyy/mm/dd”にする方法
セルを選択して、「セルの書式設定」>「ユーザー定義」>「種類」 に”yyyy/mm/dd”を設定するより手数が少ない方法としては、マクロを作成して書式を変更したいセルを選択した状態で、ショートカットキーでそのマクロを実行することぐらいだと思われるので、以下にその方法を説明します。
エクセルで開いたどのファイルでも実行可能なマクロとするためには、マクロを「個人用マクロ ブック」(PERSONAL.XLSB)に保存します。
「個人用マクロ ブック」(PERSONAL.XLSB)の保存方法
初めて「個人用マクロ ブック」(PERSONAL.XLSB)にマクロを保存する場合の方法を説明します。他の方法もあるかと思いますが、ひとつの方法です。
以下の方法で「個人用マクロ ブック」を作成するとともに、セルの日付書式を”yyyy/mm/dd”に設定するマクロを記録します。
エクセルでセルを選択した状態で、「表示」>「マクロ」>「マクロの記録」を選択します。
マクロ名を”date_fmt_yyyymmdd”等好みの名前にし、「マクロの保存先」として「個人用マクロ ブック」を選択し、必要に応じて「説明」にマクロの説明を記載し、「OK」を押します。
Ctrl+1等で「セルの書式設定」を開き、「ユーザ定義」>「種類」の欄に「yyyy/mm/dd」を記入(鍵括弧は記入しない)し、「OK」を押します。
「表示」>「マクロ」>「記録終了」を選択します。
Alt+F11(または「開発」>「Visual Basic」)でVisual Basic for Applications (VBA)のエディターを開きます。(NVIDIAのユーティリティ等でAlt+F11が別のショートカットに設定されており、そちらのショートカットキーの優先度が高い場合には、そのショートカットキーを変更しないとVBAエディターが開かないことがあります)
VBAのサブウィンドウの「プロジェクト エクスプローラー」（下図参照）で「VBAProject (PERSONAL.XLSB)」を選択した状態で、Ctrl+s、もしくはフロッピーディスクのアイコンを押すと、「%AppData%\Microsoft\Excel\XLSTART」フォルダに、”PERSONAL.XLSB”が保存されます。「%AppData%\Microsoft\Excel\XLSTART」フォルダがなければ、”XLSTART”フォルダが作成されて”PERSONAL.XLSB”が保存されます。
上記の操作でマクロを保存しなかった場合には、エクセルを閉じるときにエクセルファイルの保存の次に、「個人用マクロ ブック」の保存をするかどうかを聞かれるので、ここで「保存」を選んだ場合にも、”PERSONAL.XLSB”に作成したマクロが保存されます。
ちなみに、「%AppData%\Microsoft\Excel\XLSTART」フォルダにある全てのファイルはエクセル起動時にエクセルで開くことができるファイルは全て開かれます。
記録されたマクロは、「VBA Pjoject (PERSONAL.XLSB)」>「標準モジュール」>「Module1」に記録されます。この「Module1」が右側に開かれていないときに内容を見るには「Module1」をダブルクリックします。
“PERSONAL.XLSB – Module1（コード）”には以下のように記録されていると思います。
Sub date_fmt_yyyymmdd()
'
' date_fmt_yyyymmdd Macro
'
'
Selection.NumberFormatLocal = "yyyy/mm/dd"
End Sub行頭が’で始まっている行はコメント文で、改行だけの空行もマクロの動作には影響がありません。ですので、Sub date_fmt_yyyymmdd()の中身は
Selection.NumberFormatLocal = “yyyy/mm/dd”
だけということになります。
VBAの「プロジェクト エクスプローラー」へのファイルの表示
通常はエクセルファイルを開くとVBAの「プロジェクト エクスプローラー」にファイルが表示され、エクセルファイルを閉じるとVBAの「プロジェクト エクスプローラー」からファイル表示が消えます。
ただし、Google Driveが起動していると、なぜかエクセルファイルを閉じても「プロジェクト エクスプローラー」からファイル表示が消えませんでした。
表示が消えなくても、とりあえず不都合はないと思いますが、何らかのバグだと思いますので、そのうち解消されることを期待しています。
エクセルのキーボードショートカット
後は作成したマクロをいかに少ない手間で実行するかです。
「オプション」>「リボンのユーザー設定」>「コマンドの選択」>「マクロ」や、
「オプション」>「クイックアクセス ツール バー」>「コマンドの選択」>「マクロ」
で、作成したマクロをリボンやクイックアクセス ツール バーに登録することも考えられますが、マウスを動かしてクリックするより、キーボードを押す方が楽なのでキーボードショートカットで起動するようにします。
「表示」>「マクロ」>「マクロの表示」で表示される「マクロ」ウィンドウで、作成したマクロを選択して「オプション」ボタンを押して「ショートカット キー」を設定することもできますが、Ctrl+”アルファベットの小文字/大文字”のショートカットキーしか設定できません。また、ユーザーが作成した全てのショートカットキーを一覧で確認するのが難しくなるので、マクロ自体にショートカットキーの設定をすることにしました。
ここで、問題になるのがエクセルの既存のキーボードショートカットとかぶらないようにすることです。昔のエクセルでは、キーボードショートカットの一覧を表示する機能があった記憶があるのですが、今のエクセルにはないようです。
デフォルトのキーボードショートカットとかぶった場合には、自分が設定したキーボードショートカットが優先されます。
自分が普段使っていないキーボードショートカットとかぶったとしても、使っていないキーボードショートカットなら問題がないように思えます。しかし、自分が現在使っていないそのキーボードショートカットを将来的に使うようになったときに、どちらのキーボードショートカットを変更するかを考えたり、記憶が混乱したりするので、デフォルトで設定されているキーボードショートカットは避けるのが無難です。
microsoft365のエクセルで現在使われているキーボードショートカットをGoogle スプレッドシートのexcel_keyboard_shortcutにまとめました。抜けや間違いがあれば教えていただけるとうれしいです。
上の表で使われていないものにキーボードショートカットを割りつけるのが良いと思います。
キーボードショートカットによるマクロの実行
例えば、”Ctrl+m”で先程のSub date_fmt_yyyymmdd()を実行するようにするには、”PERSONAL.XLSB – Module1″に以下のように書きます。
Private Sub Auto_Open()
Application.OnKey "^m", "date_fmt_yyyymmdd"
End Sub
Sub date_fmt_yyyymmdd()
Selection.NumberFormatLocal = "yyyy/mm/dd"
End Sub“Auto_Open()”はエクセル起動時に自動的に実行されるSubプロシージャーです。
Application.OnKey "^m", "date_fmt_yyyymmdd"で”Ctrl+m”をdate_fmt_yyyymmdd()を実行するキーボードショートカットに設定しています。
“+”はShiftキー、”%”はAltキーを表しますので、
“^+m”なら”Ctrl+Shift+m”で実行されますし、”^%m”なら”Ctrl+Alt+m”で実行されます。
ついでに、書式を”G/標準”にするキーボードショートカットを”^+m”に
“yymmdd”という文字列で書かれた日付(250102等)をyyyy/mm/ddにするキーボードショートカットを”^j”に設定するようにした全体のマクロを以下に示します。
' ^:Ctrl, +:Shift, %:Alt
Private Sub Auto_Open()
Application.OnKey "^m", "date_fmt_yyyymmdd"
Application.OnKey "^+m", "date_fmt_std"
Application.OnKey "^j", "yymmdd2yyyymmdd"
End Sub
'日付フォーマットを yyyy/m/d等 -> yyyy/mm/dd に変更する
Sub date_fmt_yyyymmdd()
Selection.NumberFormatLocal = "yyyy/mm/dd"
End Sub
'日付フォーマットを"G/標準"に設定する
Sub date_fmt_std()
Selection.NumberFormatLocal = "G/標準"
End Sub
'yymmdd -> yyyy/mm/dd に変更する
Sub yymmdd2yyyymmdd()
Dim Counter As Integer
Dim StrYYMMDD As String
Dim StringYYYYMMDD As String
'For every row in the current selection...
For Counter = 1 To Selection.Rows.Count
StrYYMMDD = Selection.Rows(Counter)
StringYYYYMMDD = "20" & Mid(StrYYMMDD, 1, 2) & "/" & Mid(StrYYMMDD, 3, 2) & "/" & Mid(StrYYMMDD, 5, 2)
Selection.Rows(Counter).Cells(1, 1).Value = StringYYYYMMDD
Next
Selection.NumberFormatLocal = "yyyy/mm/dd"
End Subフォーマットを変更したいセルを選択した状態で、設定したキーボードショートカットを押します。
マクロ実行時の注意点
マクロの実行で変更された内容はCtrl+z（アンドゥ）で戻ることはできないので注意が必要です。マクロ実行前に戻る必要がある場合には、「保存」してからマクロを実行すると良いです。
マクロの実行許可について
最近のエクセルは”*.xlsm”ファイルでないと、マクロの保存と実行ができず、マクロを実行するには、ユーザーに許可をもらう必要があると思っていましたが、それは素性のわからぬあやしいマクロがユーザーの与り知らぬところで実行されないようにするためと思われます。
ユーザーが自分の環境のPERSONAL.XLSBに記載したマクロについては、そのような懸念がないので、”*.xlsm”ファイルでなくともマクロが実行でき、”*.csv”をエクセルで開いた場合でも、PERSONAL.XLSBに記載したマクロは実行できるということが、ちょっと発見でした。すなわち、マクロの保存は”*.xlsm”にでき、”*.xlsx”にできませんが、マクロの実行は拡張子によらずできるということです。”*.csv”ファイルでも、PERSONAL.XLSBに保存したマクロを実行することができました。
もう少し深掘りすると、エクセルの「ファイル」>「オプション」>「トラストセンター」>「トラストセンターの設定」>「信頼できる場所」に、”PERSONAL.XLSB”が保存される「%AppData%\Microsoft\Excel\XLSTART」が指定されているので、ここに保存されるファイルのマクロは実行が許可されているということのようです。
バイナリファイルである”*.xlsb”や、以前のバイナリファイルである”*.xls”にはマクロを保存することができます。
エクセルファイルの拡張子のxlsxとxlsbとxlsの違い
以下参考です。
拡張子が”.xlsx”のファイルは、Office2007から公開された「Office Open XML ファイルフォーマット」(OOXML)のファイルです。XML形式で内部がテキストとして読めるファイルで、zipファイルとしてパッケージされています。ですので、拡張子を”.zip”に変更すれば、ファイルセットが見えるようになり、UTF-8の文字コードで読むことができます。
Officeソフトのファイルで拡張子が”x”で終わる、”.docx”, “.pptx”も同様に「Office Open XML ファイルフォーマット」です。
一方、拡張子が”.xlsb”のファイルは、拡張子の末尾の”b”が表すようにバイナリ形式のファイルで、内容を読み解くには、バイナリフォーマットの仕様が必要です。(「Office Open XML ファイルフォーマット」でも内容を読み解くには仕様が必要ですが、バイナリファイルではバイナリを解釈していく手間が余分にかかります)
バイナリフォーマットにも違いがあって、”.xlsb”はExcel2007以降のBIFF12フォーマットです。
“.xls”は、Excel97～2003はBIFF8フォーマット、同じ”.xls”の拡張子でもExcel5.0/95はBIFF5フォーマットなので、”.xls”の拡張子を”.xlsb”に変更するとBIFF12として開こうとするので開けなくなります。
BIFFはBinary Interchange File Formatの略です。フォーマットの詳細は[MS-XLS]: Excel Binary File Format (.xls) Structureに記載されています。最新はProtocol Revision 12.0で、[MS-XLS].pdfを見ればバイナリフォーマットを読み解くことができますが、1125ページあります。このファイルの中にはBIFF2, BIFF3, BIFF4, BIFF5, BIFF8, BIFF12がありました。
参考にさせていただいたページ
以上
PR
コメント