Chiquilin Site■08.07.25_Excel:スペースで日付表示を揃える
ChiquilinSite
★その1:表示形式だけでは無理?
日付の表示を揃える時 表示形式をユーザー定義で「yyyy"年"mm"月"dd"日"」のようにする方が多いと思います。等幅フォントを使っていれば
これできれいに左右の幅が揃います。ただ月や日が一桁の時に表示される「0」を邪魔だと感じたことはないでしょうか。
こうできたらいいなと考えたことはありませんか? しかし「0」を「 」に置換えようと 色々試行錯誤してみたところで 表示形式だけでは無理です。何故なら
Excelの日付は 見た目はともかく実際の値は「39654」のようなシリアル値で管理されています。「年・月・日の2桁目がゼロの時」という条件は
どうあがいても表示形式では判断させることができません。面倒ですが条件付き書式と組み合わせて日付の桁数に応じて表示形式を切り替えるか、上の画像のように 別のセルに数式を使って文字列として表示させるかになります。
★その2:数式を使って表示を揃える
まずはシリアル値を一定の書式の文字列に変更する必要があります。「39654」ではどうしようもありません。この計算にはTEXT関数を使います。
■B1セル
=TEXT(A1,"g.ee.mm.dd")
これでまずは書式が揃いました。「g」の後にも「.」を入れているのがミソです。さて この時
B6セルに注目して下さい。空白セルが「M.33.01.00」になってしまいましたね。Excelでは「式の計算結果の
空白は0として扱われる」というルールがあります。シリアル値の「0」は「1900年1月0日」として扱われるので 上のような表示になってしまいます。これを避けるのは簡単で
式で計算する前に空白を文字列にしておけばいいです。
■B1セル
=TEXT(A1&"","g.ee.mm.dd")
「&」を入れることで 空白は文字列として扱われ TEXT関数の結果が文字列表示に振り分けられます。これによって「結果なし」は「
空白文字列」として返されます。
★その3:2桁目の「0」を「 」に置き換える
上記の表の2桁目の「0」に注目してみると その前の文字が必ず「.」だということに気付くと思います。これによって1桁目の「0」と区別させることが可能になります。SUBSTITUTE関数で「.」&「0」を「.」&「
」に置き換えてやります。
■B1セル
=SUBSTITUTE(TEXT(A1&"","g.ee.mm.dd"),".0",".
")
これでほぼ式は完成です。最後に頭の記号の後ろについた「.」が邪魔なので これを取り除いてやります。
■B1セル
=REPLACE(SUBSTITUTE(TEXT(A1&"","g.ee.mm.dd"),".0",".
"),2,1,"")
これで完成です。おそらく自分でわざわざ使うことはないと思いますが「人からの要望でどうしても」という時に 使う方法だと思います。
★その4:条件付き書式で表示形式を切り替える
絶対無理という訳ではないので一応追加します。正直お勧めはしません。実際やってみれば分かりますが 運用上はかなりの不備が出ます。
A1:A5セルを選択して表示形式をユーザー定義で「ge/m/d」に設定
そのままの範囲を選択した状態で[条件付き書式]→[新しいルール]
[数式を使用して、書式設定するセルを決定]を選択し
数式に「=AND(MONTH($A1)>9,DAY($A1)<10)」を入れて
[書式]の表示形式を[ユーザー定義]で「ge/m/ d」に設定
数式に「=AND(MONTH($A1)<10,DAY($A1)>9)」を入れて
[書式]の表示形式を[ユーザー定義]で「ge/ m/d」に設定
数式に「=AND(MONTH($A1)<10,DAY($A1)<10)」を入れて
[書式]の表示形式を[ユーザー定義]で「ge/ m/ d」に設定
混乱するだけなので あんまりお勧めしません。これで年一桁にも対応させたいとなると更に面倒な話になります。
→参考ファイル(040.xls)