短い幅のセルに関数で日付を表示させる方法とセル結合の注意点

知ってたら得するかもしれないExcelの癖-その1
この記事は約5分で読めます。

知っていると得するかもしれないExcelの癖」シリーズ第一回、テーマは「日付型とセル結合について」になります。
Excel 2016バージョン1902を使用して確認しています。


Excelで短いセル幅のシートを作る時があると思います。
例えば、短い数字の数表を作る場合などです。

こうしたケースで何も考えずに、日付型の関数(例えば「=NOW()」をセルに設定してしまうと「#」表示になってしまいます。

そもそも日付型はご存知のように1900/1/1日を1とした時の本日に至るまでの日数でデータが持たれているので数値型なのですが、日付のような文字列を入力するとExcelが仕様にもとづき判断をして日付型に自動変換してくれますが、こうした自動変換もExcelの癖のひとつです。
この自動変換で良く知られているのが丁目番地の部分だけの住所を単純にコピペすると日付になってしまう事例です。

スポンサーリンク

セルを結合すると困ること

このような場合、セル結合をすると表示できるのですが、下記の点で困ることになります。

  • セル結合がある列をコピーする場合、一部の列だけ列コピーすると、セル結合の部分はコピーされない。
  • 同じ行の中に複数のセル結合がある場合、隣のセル結合との間が十分に広く空いていないと「この操作は結合したセルには行えません。」注意エラーが表示されてコピーされない。

コピー元が1つのセルであれば、貼り付け先がセル結合であっても貼付けできますが、コピー元がセル範囲の場合はづきのような制約があります。

  • コピー元がセル範囲の場合
    • 貼り付け先は、コピーもとのセル範囲に含まれるセル数と同じセル数の範囲の中にセル結合のセルの一部が被っていてはならない。
    • 貼り付け先のセル範囲の中にあるセル結合のセルすべてがコピーもとのセル範囲で置き換えられるのであれば貼付けすることができる。

B2:D4がコピーもとのセル範囲になります。

H5:I5にセル結合がある場合、コピーできないセルは5ヶ所です。

※セル結合された場所に張り付けする場合は「既にデータがあります、置き換えますか?」[はい・いいえ]ボタンで選択できるので可としています。

とってもややこしいので、是非一度ご自分で確認して見ていただけると理解が深まることと存じます。

書式を文字列にすると困ること

日付を直接セルに入力する場合はそれで良いのですが、関数を使用している場合はセル書式の設定を文字列にしたり、「’」を先頭に付けたりすると、関数が表示されてしまいそのままでは日付として表示されません。

当たり前のことを書いて恐縮ですが、これは次につなげるための前説になります。

TEXT関数を使う

日付型関数をそのままセットできないためにTEXT関数をかぶせて、Excelにはテキスト型(文字列)と認識させることで解決することができます。

=TEXT(NOW(),"yyyy/m/d")

この形であれば、短いセル幅であっても日付を表示させることができます。

日付を表示させるためにセル書式を「d」にする場合

少し話がそれるのですが、幅が短いセルのために、最小限の情報として「日」だけを表示するために、表示正式をユーザ定義で「d」にする場合もあることと存じます。

この書式を設定したセルは注意をしないと危険です。

というのは、誤ってこの書式のセルに計算する数式を設定してしまうと次のような結果となり、要らない手間をかけることになります。

30+3=2

繰り上がりが表示されない31進数での計算結果になってしまいますので、注意が必要です。

なおこの書式で30と入力したセルをyear()関数、month()関数で調べると1900/1/30と自動変換していることが解ります。


セル結合したセルを別のセル結合したセルに値貼付けする際の注意点

【追記】2019/3/29
同様の趣旨の記事を見かけますが、パターンを整理して置きたく追記致しました。

セル結合されたセルだけを値貼付けする場合

「セル結合A」B2:C2をコピーして、セル結合BからDの各セル結合先に貼り付けします。

値貼付け数式貼付け普通貼付け
セル結合B
セル結合C×(1)×(3)
セル結合D×(2)

×になった時のエラーメッセージのパターン

  1. 「コピー領域と貼り付け領域のサイズが違うため、これをここに貼り付けることができません。貼り付け領域内のいずれか1つのセルを選ぶか、または同じサイズの領域を選び、もう一度貼り付けてください。」
  2. 「この操作を行うには、すべての結合セルを同じサイズにする必要があります。」に続けて1.のメッセージが表示されます。
  3. 「この操作は結合したセルには行えません」

セル結合及び周りのセルを一緒に値貼付けする場合

セル結合Aを含む、B2:E4のセル範囲を、(それぞれ違う形の)セル結合を含む同じセル範囲に貼り付けてみます。

値貼付け数式貼付け普通貼り付け
セル結合B×(2)
セル結合C×(2)
セル結合D×(2)

▲– セル結合C・Dの形をセル結合Aの形で置き換えてしまうために〇ではなく▲にしました。

以上の結果から、書式をコピーしない「数式貼り付け」がセル結合をコピーする際には向いていそうです。
ただ、セル結合Cの場合の動きが周りのセルを含まない時と異なるのはなぜなのでしょうか?気になるところです。

同じように値貼付けも、セル結合Bに対してなぜ(周りを含むと)駄目なのでしょうか?//

以上、最後までご一読いただきありがとうございました。