Googleスプレッドシートで西暦から和暦を求める。

この記事は自分用の覚え書きです。

H.K さん。

Googleスプレッドシートで、西暦から和暦を求める表を作ってみました。
今のところ、エクセルのように表示形式で和暦を表示する機能は、Googleスプレッドシートには無いようですね。

Google Apps Script でのやり方とかネット上で見かけましたが、誰もがさわれるものではありません。
誰でもさわりやすいツールにしておくには数式を使うのがよいかなと思いました。
なので、数式で和暦を求めることにしました。

使っている関数は、YEAR関数、MONTH関数、IF関数とAND関数です。

■課題

Googleスプレッドシートにもともと入っている日付で、一番古いものは2000年7月。
一番新しいものは2019年2月。2月以降の日付は今後増えていく。
西暦/月/日　の形で入力してあるセルはそのままで、和暦だと何年何月かがわかるようにしたい。日は不要。

■考え方

Googleスプレッドシート上にある一番古い日付は2000年7月ということなので、この場合は平成と令和だけを考えればよいということです。
（昭和以前は不要）

今年2019年5月1日から、元号が平成から「令和」に変わりました。
2019年は平成31年と令和1年が重なっていて、考え方が少しややこしいのでいったん横に置いておきます。

考えやすいのは2018年（平成30年）かと思います。
まずはそこから考えていきます。

2018 から平成元年である 1989 を 引いて、それに 1 を足せば2018年が平成何年なのかがわかります。
2018-1989＋1＝30 になるので平成30年です。

なお、一番古い日付は2000年7月なので、昭和が平成と重なっていることも考慮しなくてよいということです。
ということは、Googleスプレッドシート上で平成元年以降の各西暦から 1989 を 引いて、それに 1 を足せばそれぞれの西暦が平成何年なのかがわかります。

2000年は、2000-1989+1=12 で平成12年です。
このように、西暦 - 1989 + 1 の式で、2018年までは平成何年かを求めることができます。
そして、平成31年にあたる2019年は平成と令和が重なっているので、式の答えが31未満のうちは、その西暦は全て「平成」です。

ちょっと工夫が必要なのが今年2019年ですね。
2019年1月から4月までが平成31年で、5月以降は令和元年（令和1年）になります。
西暦 - 1989 + 1 の式の答えは 31 です。

このことから、次の2つが同時に成り立てば、2019年のある日付は「平成」ということになります。
A. 2019-1989+1 の答えが 31 と等しい。
B. 2019年のある日付の「月」が 4 以下。（1 から 4 までのどれか）

同じように、次の2つが同時に成り立つ場合は、2019年のその日付は「令和」ということなります。
A. 2019-1989+1 の答えが 31 と等しい。
B. 2019年のある日付の「月」が 5 以上。（5 から 12 までのどれか）

そして、令和元年が平成31年と重なっているので、2020年以降では西暦について次のことが成り立てば、その日付は「令和」になります。
・西暦 - 1989 + 1 の答えが、31 より大きい。

■これらに基づいて表を作りました。

これらを全部処理する数式を、1つのセルに入れてしまうと、それも何をしているのかパッと見てわかりづらい数式になります。
作業列をつかって処理をわけました。そうしておけば、全体を通して何をしているのが誰にでも理解しやすくなるかと思います。

仕事で作るファイルは誰のものでもありません。皆のものです。
なので、仕事で作るファイルは、できる限り誰が見ても理解しやすく作っておくべきです。

 

A列がもともと入っている日付です。

数式は以下のようになっています。
何をしているかを少しでも読み取りやすくなるように、各式をセル内改行してみました。

■各列に入っている数式

・西暦（B列）
=if(A2="","",
if(A2<>"",YEAR(A2),
))

B列では、A列をもとにYEAR関数で「西暦」だけを抜き出しています。
A列が空欄の場合はセルに何も表示しないように、IF関数で「A列のセルが空欄ならセルに何も表示しない」ということにしてあります。
というのは、A列が空欄だとB、C、E、F、G列に無用な答えがが入ってしまうからです。

・月（C列）
=if(A2="","",
if(A2<>"",MONTH(A2)
))

なので、C列以降も同様にして、数式内で参照している列のセルが空欄なら何も表示しないということにしました。
C列では、A列の日付からMONTH関数で「月」を抜き出しています。

D列の「平成元年」の 1989 は、例では手入力しています。
実際には、3行目以降は =$D$2 とかにして、先に入力した値を参照するようにしておけば、行をコピーして増やすときに便利かと思います。

・平成（E列）
=if(B2="","",
if(B2<>"",B2-D2+1
))

E列で、西暦 - 1989 + 1 の計算をして、B列の西暦が平成でいえば何年になるかを求めています。

・和暦（F列）
=IF(A2="","",
IF(E2<31,"平成"&E2,
IF(AND(E2=31,C2<=4),"平成"&E2,
IF(AND(E2=31,C2>=5),"令和"&E2-30,
IF(E2>31,"令和"&E2-30,
)))))

F列が平成か令和を判定している列です。
まずE列の「平成」の値が、31 より小さければ「平成」という文字列とE列のセルの値を結合して表示します。

次にIF関数とAND関数を使って、
A. 2019-1989+1 の答えが 31 と等しい。
B. 2019年のある日付の「月」が 4　以下。
が同時に成り立てば、「平成」という文字列とE列のセルの値を結合して表示するようにしてます。

細かいことをいえば、「"月" が 1 から 4 までのどれか」ですが、この場合は 1 から 4 までの4つの数字しかありえないので、「4以下」という数式にしてあります。

それから「令和」の年数を表示するために、
A. 2019-1989+1 の答えが 31 と等しい。
B. 2019年のある日付の「月」が 5 以上。
が同時に成り立つ場合は、文字列「令和」とE列のセルの値から 30 を引いたものを結合して表示するようにしています。
（ここも先と同じで、5 から 12 までの8つの数字しかないので、「5以上」という数式にしました。）

ここでE列の値から 30 を引いているのは、2018年が平成30年だからです。
30 を引いておかないと、令和1年になりません。

さらに、同じ考え方でE列の値が 31 より大きい場合も、令和2年以降を表示するために文字列「令和」と 30 を引いたものを結合しています。

・月（G列）
=if(C2="","",
if(C2<>"",C2,
))

最後に、G列ではC列の値を単純に参照しているだけです。「月」なので。

ここでC列と同じことをするのだから、先にC列で「月」を抜き出しておくのは不要な気もしましたが、「元の日付から西暦と月を抜き出している」ことがより見た目でわかりやすくなるかと思って、C列で「月」を抜き出して、それをG列で参照するようにしました。

このように、あることを「なぜそうするのか」あるいは「そうしないのか」について、具体的な理由があることもエクセルでファイルを作る際には大事です。たいそうな理由でなくてもよいです。先に挙げたように、「より理解しやすくするためにそうした（そうしなかった）」という程度で。

具体的な理由がないと、あとあとどこかを少し変えたいとなったときに、そもそも変えてよいのか、変えるには何に留意しておくべきかということがわかりません。
（だから、ファイルを引き継ぐ際には、その具体的な理由も引き継いでおく必要があります。）

長くなりましたが、処理の過程を作業列で分けてあるので、各列で何をしているのかも、全体を通して何をしているのかも理解しやすいと思います。