販促レポート：＜エクセル＞日時データとの結合で起こる数字の羅列？を防ぐ方法｜TEXT関数

販促レポート

2018/12/27　Excel小技

＜エクセル＞日時データとの結合で起こる数字の羅列？を防ぐ方法｜TEXT関数

エクセルデータを成型する際、日時データと他データを結合→意味不明な数字の羅列？？という経験はないですか。この意味不明な数字は「シリアル値」と呼ばれ、TEXT関数の「書式指定」で補正できます。

シリアル値とは何か？
＜エクセル＞日時データとの結合で起こる数字の羅列？を防ぐ方法｜TEXT関数

エクセルデータを成型する際、データと「日付や時間」を一つのセルで結合しようとすると、意味不明な数字の羅列に？？こんな現象が起こった経験ないですか。

この意味不明な数字の羅列は、シリアル値と呼ばれるものです。
シリアル値は、1990年1月1日を起点に計算されており、例えば「43313」は43313日後となり、2018年8月1日になります。日付や時間は、このシリアル値とパソコンに内蔵されているカレンダーを照らし合わせて表示されます。しかし、結合して他のデータが混在したりすると、日付形式で表示させることができなくなり、元のシリアル値のまま結果が返されてしまいます。

日付や時間のデータを正しい表示形式のまま、ほかのデータと結合したい場合は、TEXT関数を使うと解決できます。その方法については、次段でご説明いたします。

シリアル値を通常の日付や時間の表示に修正する方法
＜エクセル＞日時データとの結合で起こる数字の羅列？を防ぐ方法｜TEXT関数

このように、CONCATENATE関数で、日時データを他のデータと結合しようとすると、日付と時間の部分がシリアル値になってしまい、なんだか良くわからないデータになってしまいます。

＜エクセル＞日時データとの結合で起こる数字の羅列？を防ぐ方法｜TEXT関数

これを正しく表示させるには、TEXT関数を使います。TEXT関数を使えば、指定したセルを任意の表示形式に置き換えることができます。日付と時間の間には、見やすいように半角スペースを入れています。

=CONCATENATE(TEXT(B4,"yyyy年m月d日(aaa)")," ",TEXT(C4,"hh:mm")," ",D4,"@",E4)

すると、このように、日付と時間が正常に表示された状態になりました。あとは、セル右下の [＋] を摘まみ連続コピーし、下までドラッグ。

＜エクセル＞日時データとの結合で起こる数字の羅列？を防ぐ方法｜TEXT関数

ちなみに、日付や時間を他の書式にしたい場合は、[セルの書式設定]をクリック。

＜エクセル＞日時データとの結合で起こる数字の羅列？を防ぐ方法｜TEXT関数

[ユーザー定義]から時間や日付に該当するものを選び、「種類」の欄に書かれた文字をコピペして使いましょう。セルの書式設定では表示プレビューがされないため、少々面倒です。以下に定義→表示形式を記載しておきますので、参考にしてみてください。

日付の設定

定義：西暦yyyy年(ggge年) m月d日(aaaa)
表示：西暦2019年(平成31年)1月1日(火曜日)

定義：yyyy,mmm,d
表示：2019,Jan,1

定義：yyyy/mm/dd
表示：2019/1/1

時間の設定

定義：hh時
表示：12時

定義：hh:mm:ss
表示：12:00:00

セミナーや講座のデータをエクセルで管理することの多い総務・事務の方には重宝される便利ワザではないでしょうか？シリアル値の概念を理解してしまえば、CONCATENATE関数だけでなく、他の方法にも応用して活用できます。ぜひ、周りに知らない方がいたら教えてあげてください。
※Excel2013（Windows版）で操作しています。
※Excel2013以外のバージョンだと、項目名が異なる場合があるのでご注意ください。
※画像キャプチャに記載されている内容は架空のデータです。

＜ライタープロフィール＞

担当ライター：ryusuke
WordPressサイト制作／Web集客の専門家。大手広告代理店にて、百貨店や出版社のリスティング広告運用を担当。その後独立、広告代理店で培ったSEOやデータ分析の知見を活かし、個人メディアを運営する傍らフリーのコンテンツライターとして活動中。執筆テーマは、集客やマーケティングなどビジネス関連、グルメや音楽関連。
公式ブログ
 公式フェイスブック

販促レポートは、特集記事や販売促進コラム、オフィスでの問題解決など、皆さんのビジネスに少しでも役立つ情報をお届け。編集長の弊社代表と様々な分野で活躍する若手ライター陣によって、2008年より地道に運営されております。

★このページを友達に伝えよう：

FOLLOW @pqnavi

Follow @pqnavi

LIKE @pqnavicom