今回の記事では
日付の表示を思った通りに表示してくれる関数って無いの？
思い通りに表示してくれたら便利何だけどどうしたら良いかわからない
日付の表示を色々と変えてみたいけど数字になったりして上手く出来ない
関数とかを使って自分が考えている通りに表示してさせる方法って無いの？
こういった疑問に答えます
✔日付の表示方法を自由に操る関数のテーマ
この記事を書いているのはOfficeを約20年間活用しながら
ITを活用したエステの経営マネジメントしている私の経験を基に
「日付の表示方法を自由に操る関数のテーマ」に関して実体験に基づいて解説しています
今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい
目次を使って読みたい項目へ
日付の表示方法を思い通りにするにはTEXT関数とDATEVALUE関数で解決出来ます
日付の表示方法を自由に操るには2種類の関数を使いこなすことが重要です
なぜなら、1種類の関数では思い通りに表現するには限界があるからです
これから日付の表示方法(フォーマット)を設定する関数について解説していきますが日付に関して先に押さえておきたいポイントがあるので簡単に説明します
まず、日付は「シリアル値」という連続した数字で出来ています
シリアル値に関しては「日付の正体はシリアル値という数字」という記事で紹介しているので良く分からない場合は参考にしてみて下さい
日付を計算(日数を計算するなど)に使うのであれば「シリアル値のまま表示形式を変更」するのが正解です
日付を文字列として使いたい場合は「関数などを利用」するのが正解です
シリアル値は計算に使えて、文字列は当然ですが計算出来ないのでその違いがることだけは覚えておくことが日付を扱う上では大切です
日付を文字列にする方法
では、日付を文字列として扱う方法について解説していきましょう
今回は日付を「表示形式で設定する方法」と「TEXT関数で変換する方法」を紹介します
まず、Excelでは文字列のように「2022年1月1日」と入力しても自動的に日付として変換されてしまいます
今回の例も右側の文字列のように入力した結果、自動的に表示形式を切り替えて日付として変換されています
そのため数式バーはどの書き方でも日付である「2022/1/1」となっていますね
5行目の「Jan-22」も自動変換された結果ですが、どの日付の正体も「2022/1/1」です
この日付を文字列として扱うためには表示形式を先に「文字列に設定」しておく必要がります
手順はすごく簡単です
重要なのは「入力前に表示設定を変更」しておくことです
今回は入力対象の「B2セル～B5のセル範囲を選択」→「ホームタブの数値グループにある数値の書式をクリック」→「文字列」をクリックすれば完了です
そのあとで順番に「2022年1月1日」のように入力すればOKです
文字列である証拠に文字が左揃えになり、数式バーにも表示されている内容そのままですね
これが一番簡単な方法です
では、次はTEXT関数を使って文字列に変更する方法を見ていきましょう
設定内容はこんな感じです
テキスト関数の基本的な使い方は「=TEXT(値,表示形式)」です
凄くシンプルですよね
今回の式も見てみましょう「=TEXT(A2,“yyyy年m月d日”)」変換したい対象を設定して、表示したい内容を設定しています
これを日本語にすると「=TEXT(A2のセルの内容を,「yyyy年m月d日」という表示にしてね)」という意味です
「yyyy=年数を4桁で表示」「m=月数を1桁で表示」「d=日数を1桁で表示」という意味になります
その後に年月日の文字になっているので数字+文字の「2022年1月1日」と表示されるという訳です
「y=年(Year)」「m=月(Month)」「d=日(Day)」の頭文字なので意味は分かりやすいですよね
その他の設定内容は次の通りです
ポイントになるのは各表示設定の内容を工夫することです
目的に合わせて2つの方法を使い分けてみましょう
文字列を日付にする方法
続いて文字列を日付に変換する方法について見ていきましょう
日付を連番で表現している時はTEXT関数を使って日付に変換するとスムースです
こんな風に日付を「20220101」のように連番で表現することは実務でもよくあります
Excel上この状態はただの数字です
この数字を日付に変換するにはTEXT関数を使って日付に変換するのが手っ取り早いやり方です
設定内容を見ると「=TEXT(A2,“0000!/00!/00”)」ですね
TEXT関数の使い方はさっきと同じなのでこの式を日本語にしてみましょう
「=TEXT(A2セルの内容を,「yyyy/mm/dd」形式で表示してね)」という意味です
ポイントになるのは「0000!/00!/00」です
日付と言うと「YMD」で表現したくなりますが今回は「数字の塊」なので「数字+/」をくっつけて日付にすればいいので「YMDではなく0」としています
「0は数字1文字分」を表しています
次の「!(エクスクラメーション)」を「/(スラッシュ)」の前にくっつけている意味は「/」をそのまま「/」として扱ってねという意味があります
「/」は計算式では「割る」という意味の比較演算子です
つまり「0000/00/00」だと「4桁の数÷2桁の数字÷2桁の数字」という意味になってしまうので「/」をそのまま活かして「0000/00/00」と表示してねという意味になります
ちょっと難しいので今は「!」マークが必要なんだなと思ってもらえれば十分です
こうすることで「日付を表す連番が日付っぽく見える」ようになりました
ただし、実はこれ日付じゃありませんもう一度よく見てみましょう
文字が左によっていますよね
「日付や数字は右寄り」で「文字列は左寄り」というルールがExcelにはあります
これを日付として扱うためにはこの文字列をシリアル値にしてあげれば問題が解決できます
その設定方法を見てみましょう
設定方法はこれだけですごく簡単です
DATEVALUE(デイトバリュー)関数は対象を日付に変換してくれる関数です
今回の「=DATEVALUE(B2)」の式を日本語にすると「=DATEVALUE(B2のセルを日付に変換してね)」」という意味なので説明いらずですね
仕上げに表示形式を日付にしてあげればOKですね
ただ、今回の方法だと列を分けないといけないのでTEXT関数とDATEVALUE関数をネストさせた場合も見ておきましょう
設定方法はこんな感じです
すごく簡単ですね
式は「=DATEVALUE(TEXT(A2,”0000!/00!/00″))」なので、DATEVALUE関数の中に最初のTEXT関数を入れてネストさせただけです
これで1回で日付に変換することが出来ます
もし、シリアル値で表示された場合は、表示形式を日付にすれば大丈夫ですよ
区切り位置で変換
最後に関数を使わない方法も紹介しておきます
やり方はExcelの「区切り位置」機能を使うという方法です
日付に変更したい文字列を範囲選択しておきます
「データタブ」の「データツールグループにある区切り位置ボタン」をクリック
「区切り位置指定ウィザード」が開くので「次へ」をクリック
「次へ」ボタンをクリック
「日付」のラジオボタンをクリック→表示先をクリックして「E2のセル」をクリックします
「完了」ボタンをクリック
※表示先を設定しない場合は連番が直接書き換わります
完了すると日付に変換されます
関数を使うよりも簡単なので目的などに応じて使い分けてみて下さい
繰り返しになりますが、日付の表示方法を自由に操るには2種類の関数を使いこなすことが重要です
日付を数字の組み合わせなどで表現するには色々な関数の組み合わせが必要です
日付を色々な表現方法で表示するには、色々な方法があることを知っていることが大切です
なぜなら、日付はシリアル値なので目的に合わせた関数などを組み合わせる必要があるからです
それでは、具体的な方法について見ていきましょう
TEXT関数で日付を連番に変換する
今回は日付を連続した数字に変換する方法です
さっきとは真逆の方法ですね
設定方法は簡単です
何度も設定しているTEXT関数を上手く使えば1回で終わります
数式は「=TEXT(A2,”yyyymmdd”)」で「A2のセルから年月日を連続した値で表示してね」という意味です
次は年月日の数字を工夫してそれぞれの数字を抜き出す方法について見てみましょう
今回の目的は「年数は下2桁」「月数と日数は2桁表示」で数字を各列に抜き出す方法です
言葉だけだと分かりにくいので完成形を見てみましょう
こんな風に日付から各数字を抜き出してみましょう
まずは、MID関数とYEAR関数を使って「年数の下2桁」を抜き出す方法です
MID関数は指定された範囲の文字列を抜き出してくれる関数です
MID関数の使い方については「MID関数などを活用して特定の文字を簡単抜き出し」などの記事で解説しているので良く分からない場合は合わせて読んでみて下さい
今回の式は「=MID(YEAR(A2),3,4)」なのでこれを日本語にすると「=MID(YEAR(A2セルの年数を取得して),年数の3文字目～,4文字目を表示してね)」
という意味になります
3～4文字目なので「年数の下2桁を表示出来た」という訳です
特に難しくはないですね
次はTEXT関数とMONTH関数・DAY関数を組み合わせて各数字を2桁で表示する方法です
この組み合わせも簡単です
式は「=TEXT(MONTH(A2),“00”)」なのでこれを日本語にすると「=TEXT(MONTH(A2セルの月数を取得してね),表示方法は「00」という2桁でね)」という意味です
「0」を2つ並べることで1桁の数字の前に「0」を表示してくれます
日数の設定は同じなので説明は省略しますね
最後は日付を「-(ハイフン)」で区切る方法です
この設定はすごく簡単です
今回の場合は、さっきの年月日の数字を「&」と「-」で繋げただです
もし、年月日の数字を1つの列だけで表示したい場合は、各セルの位置にそれぞれの関数を設定すればOKです
つまり、日付を色々な方法で表示したい場合は、日付を取得してくれる関数などを上手に組み合わせることが重要になってきます
繰り返しになりますが、日付を色々な表現方法で表示するには、色々な方法があることを知っていることが大切です
オススメのExcel関数の解説書 5選
関数を本で学ぶ時は次に上げるものがオススメです
本を購入をオススメしている理由と各本の紹介に関して、下記の記事で詳しく解説しているので合わせて読んでみてください
コメントを残す