わえなび ワード&エクセル問題集

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】日付を表す文字列に「1」を掛けるだけでシリアル値に変換できる

数値や文字列を日付のシリアル値に変換するExcel関数として、DATE関数またはDATEVALUE関数があります。Excel関数を使うと他人に数式の意味を説明しやすいというメリットがある一方で、関数の入れ子になるなど数式が複雑になりやすいというデメリットがあります。

数値を表す文字列に1を掛ける(または0を足す)と数値に変換することができますが、日付のシリアル値も数値なので同じように変換することができます(参考:【Excel】数値と文字列の違い、数値と文字列の変換方法の総まとめ)。日付を表す文字列をシリアル値に変換するだけであれば、わざわざExcel関数を呼び出す必要はなく、1を掛けるなどの演算をするだけで良いです。

そこで、今回は日付を表す文字列をシリアル値に変換する方法について出題します。

 

 

目次

1.はじめに

(1)日付のシリアル値について

Excelでは、日付を連番(シリアル値)で管理します。日付を入力するときはシリアル値を理解しなければなりません。日付のシリアル値について、詳しくはこちらの記事をご覧ください。

 

(2)文字列を数値に変換する

Excelでは、数値に見える文字列に1をかける(または0を足す)ことによって、数値に変換することができます。数値と文字列の違い、文字列を数値に変換する方法について、詳しくはこちらの記事をご覧ください。

 

2.日付文字列をシリアル値に変換する

(1)日付文字列に1を掛ける

問題

="1/25"」という数式を入力した場合の計算結果はシリアル値と言えるか。また、シリアル値に変換して「yyyy年m月」の表示形式にしなさい。

f:id:waenavi:20190830152608j:plain

 

解説

数式として「=1/25」と入力すると割り算になってしまい、計算結果は0.04となります。これは1月25日とは無関係です。

f:id:waenavi:20190830153005j:plain

 

そこで、ダブルクォーテーションで囲みます。計算式でダブルクォーテーションで囲んだものは文字列として扱われます。文字列として入力されたものはシリアル値ではありません。したがって、この状態で表示形式を変えても、セルの表示は変わりません。

f:id:waenavi:20190830153113j:plain

 

文字列として入力された日付をシリアル値にするには、足し算・引き算・掛け算・割り算の四則演算が必要です。最も簡単な方法は1を掛けることです。

f:id:waenavi:20190830153327j:plain

 

1を掛けると5桁の数になります。年が省略されている場合は現時点での年を補ったうえでシリアル値を計算します。なお、これは数式なので再計算されます。つまり、年が変わったら補完される年も変わりシリアル値も変わります。

f:id:waenavi:20190830153457j:plain

 

これはシリアル値なので「yyyy年m月」の表示形式にすることができます。

f:id:waenavi:20190830153556j:plain

 

別解

先頭にマイナスを2つ付ける方法もあります。マイナスのマイナスで、プラスになります。他にも0を足すとか、0を引くとかいろいろありますが、要するに四則演算があればよいです

f:id:waenavi:20190830154626j:plain

 

(2)月・日を分けて入力した場合

問題

月・日を分けて入力した日付はシリアル値と言えるか。また、シリアル値にして「yyyy年m月」の表示形式にしなさい。

f:id:waenavi:20190830152743j:plain

 

解説

人間がこの表を見ると1月25日を表していることは分かりますが、Excelは2つの数字が並んでいるだけにしか見えません。月・日を分けて入力した場合、日付として認識されません。

f:id:waenavi:20190830154740j:plain

 

そこで、スラッシュを入れて連結します。「=A2&"/"&B2」と入力します(参考:【Excel】文字列結合、スペース・改行・ダブルクォーテーションの連結)。

f:id:waenavi:20190830154853j:plain

 

連結しただけの文字列は「文字列」です。文字列はシリアル値ではありません。

f:id:waenavi:20190830155104j:plain

 

1を掛けると5桁の数になります。年が省略されている場合は現時点での年を補ったうえでシリアル値を計算します。ただし、これは数式なので再計算されます。つまり、年が変わったら補完される年も変わりシリアル値も変わります。

f:id:waenavi:20190830155230j:plain

 

「yyyy年m月」の表示形式にすることもできます。

f:id:waenavi:20190830155302j:plain

 

別解

Excelで日付として認識される文字列はスラッシュだけではありません。例えば、ハイフンでもよいです。

f:id:waenavi:20190830155353j:plain

 

この場合も単に連結しただけではシリアル値になりませんので、1を掛けます。

f:id:waenavi:20190830155413j:plain

 

(3)年・月・日を分けて入力した場合

問題

1~10の数値を和暦にすることは可能か。また、年・月・日を用いてシリアル値を求め、和暦の日付の表示にしなさい。

f:id:waenavi:20190830152923j:plain

 

解説

日にちとして1~10の数値を入力したとします。

f:id:waenavi:20190830155459j:plain

 

これを和暦に変換すると明治33年になります。西暦1900年のことです。

f:id:waenavi:20190830155531j:plain

 

表示形式を標準に戻します。

f:id:waenavi:20190830155459j:plain

 

そこで、スラッシュを入れて連結します。年と月は絶対参照です。

f:id:waenavi:20190830155805j:plain

 

連結しただけの文字列は「文字列」であり、シリアル値ではありません。文字列のままでは和暦になりません。1を掛けるとシリアル値になります。

f:id:waenavi:20190830155906j:plain

 

和暦の表示形式にすることも可能です。

f:id:waenavi:20190830155940j:plain

 

3.和暦の年月日を連結してシリアル値にする

問題

次の各設問について、平成25年2月5日のシリアル値を求め、西暦の日付にしなさい。

(1)和暦年2桁・月・日を入力した
(2)元号をT・S・H・Rで入力し、和暦年2桁と月を入力した
(3)元号を漢字で入力し、和暦年2桁・月・日を入力した

f:id:waenavi:20190830160124j:plain

 

解説

2桁の年の場合、西暦の下2桁なのか和暦の2桁なのかが分かりません。先頭に「H」を連結します。さらに1を掛けてシリアル値にします。

f:id:waenavi:20190830160221j:plain

 

イコールで参照します。

f:id:waenavi:20190830160320j:plain

 

西暦の日付にすることもできます。

f:id:waenavi:20190830160421j:plain

 

元号をセルに入力した場合はそれを参照します。日にちが無ければシリアル値を計算できないので、「/5」とします。さらに1を掛けてシリアル値にします。

f:id:waenavi:20190830160517j:plain

 

これで昭和でも令和でも計算できます。

f:id:waenavi:20190830160557j:plain

f:id:waenavi:20190830160559j:plain

 

元号が漢字の場合、「ggge年m月d日」の形式にします。

f:id:waenavi:20190830160627j:plain

 

4.年と月が変われば曜日も自動的に変わる

問題

セルA1の表示形式を文字列にして「2021年4月」と入力した。そして、4行目以降に1~30の連番を入力した。

f:id:waenavi:20190830163426j:plain

 

2021年4月1日~30日の曜日を表示しなさい。また、2022年11月1日~30日の曜日にしなさい。

f:id:waenavi:20190830163456j:plain

 

解説

まず、セルA1の表示形式を文字列にして「2021年4月」と入力します。

f:id:waenavi:20190830163610j:plain

 

1~30の連番を入力していますが、これは単なる数値であり、日付でもシリアル値でもありません。

f:id:waenavi:20190830163708j:plain

 

そこで、2021年4月1日のシリアル値を求めます。2021年4月を絶対参照にして、日にちと「日」を連結して2021年4月1日とします。さらに1を掛けます。

f:id:waenavi:20190830163817j:plain

 

これを日付の表示形式にすれば日付になりますが、ユーザー定義でaaaにすれば曜日だけになります。

f:id:waenavi:20190830163906j:plain

 

オートフィルをします。コピーします。これで30日までの曜日になります。

f:id:waenavi:20190830163944j:plain

 

2022年11月にするとシリアル値も変わりますから、自動的に対応する曜日になります。

f:id:waenavi:20190830164042j:plain

 

5.月日と年が逆になっているパターンの場合

(1)mm/dd/yyyy

問題

Excelで「12/01/2020」と入力しなさい。また、これをシリアル値に変換しなさい。

解説

欧米では、「12/01/2020」のように月日と年を逆にして表記することがあります。しかし、少なくとも日本語版のExcelではこの表記に対応しておらず、入力すると文字列として扱われます。入力すると表示形式は標準のままで左揃えになります。これはシリアル値ではありません。

f:id:waenavi:20200308210120j:plain

 

これに1を掛けるとエラーになります。日付の文字列として認識されていないので、1を掛けるとエラーになってしまいます。

f:id:waenavi:20200308210156j:plain

 

そこで、LEFT、RIGHT関数を用いて、年と月日を逆にして、スラッシュで連結します(参考:【Excel】LEFT関数、RIGHT関数、MID関数で文字列を抽出する方法の基本例題)。

  • =RIGHT(A1,4)&"/"&LEFT(A1,5)

f:id:waenavi:20200308210428j:plain

 

連結した文字列に1を掛けてシリアル値に変換します。

  • =(RIGHT(A1,4)&"/"&LEFT(A1,5))*1

f:id:waenavi:20200308210456j:plain

 

(2)m/d/yyyy

問題

Excelで「3/1/2020」と入力しなさい。また、これをシリアル値に変換しなさい。

解説

1を掛けるとエラーになるのはさきほどの例と同じです。

f:id:waenavi:20200308210559j:plain

 

そこで、LEFT、RIGHT、LEN関数を用いて、年と月日を逆にします。月日の文字数は全体の文字数から5文字を引きます(参考:Excelで特定の文字を検索して文字列の一部を抽出する)。

  • =RIGHT(A1,4)&"/"&LEFT(A1,LEN(A1)-5)

f:id:waenavi:20200308210620j:plain

 

連結した文字列に1を掛けてシリアル値に変換します。

  • =(RIGHT(A1,4)&"/"&LEFT(A1,LEN(A1)-5))*1

f:id:waenavi:20200308210650j:plain

 

6.TEXT関数を用いた変換について

8桁の数値を日付のシリアル値に変換するにはTEXT関数を使います。例えば、セルA1に8桁の数「20230405」を入力して2023/4/5を表すシリアル値に変換するには「=TEXT(A1,"#-00-00")*1」と入力します。

f:id:waenavi:20191029121849j:plain

 

詳しくはこちらの記事をご覧ください。

 


解説は以上です。


 

 


Copyright(C)2018-2020 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ