この記事は以下のような方にお勧めです。
お急ぎの方は、それぞれお知りになりたい場所にお進みください！
Excelで和暦を西暦へ変換する(シリアル値・正しい表記の日付の場合)
表示形式を変更する
表示形式を変更して済むならそれが一番簡単！なのですが、その条件としてセルにシリアル値が入っていることが必要です。
Excelは日付をシリアル値で認識しています。シリアル値とは1900年1月1日を「1」として日付に設定されている値のこと。
この表のM列4行目がシリアル値で、1900年1月1日から数えて39,691日目ということです。1～3行目も、同じ値が入っています。表示形式を変えることでいろいろ見え方が変わるだけ。現在のExcelは「へいせい…」と文字を入力しても、自動でシリアル値に変換し、日付と認識します。
ただ5行目のように、既にセルの表示形式が文字列に設定されている場合、「へいせい…」と文字列を入力すると、文字列のまま、シリアル値には変わりません。
文字列の日付をシリアル値に変更し、表示形式を設定する
5行目のような模範的な文字列の日付の場合、「DATEVALUE」関数が使えます。先の表のM列6行目のセルでは、5行目の文字列を「DATEVALUE」関数を使ってシリアル値に変換しています。後は見せたい表示形式に設定すればよいだけになりましたね。
特殊な日付形式を更に特殊な形式へ変換したい(省略された日付などの場合)
さて、年号は以下の表のように略されることもありますが、その場合はご覧の通り、DATEVALUE関数ではエラーになってしまいます。
しかし会計ソフトや申告ソフトでは、こうした特殊な日付表示を扱うことは多々あるのではないでしょうか。今回も実際の作業は「魔法陣」というソフトからエクスポートしたデータを、「達人」というソフトへインポートするために必要になりました。
魔法陣からエクスポートされた日付形式は「平200831」…和暦年号の略+和暦年+月+日
達人へインポートする日付の形式は「20080831」 …西暦年+月+日
シリアル値にするためには、略された年号を置換して、年月日を区切るなど様々な方法もあると思いますが、今回私は、シリアル値に変換せず単なる数値・文字列のまま操作することにしました。結果もシリアル値にはしない為です。
和暦の使用をやめてしまえばこんな作業は不要になるのに…と思いつつ…以下が各セルに入っている数式です。
B～Cまで一つの関数にまとめることもできますが、作業列として挿入しています。
長い関数は一目で内容が判りにくいですし、あとから他の人がみてもすぐに判るようにできるだけシンプルに、修正したり、カスタマイズしたりしやすいように分けています。簡単に関数を説明すると、
B列…年号を抽出。元データの左から1文字
C列…和暦年を抽出。元データの左から2文字目と3文字目
D列…①C列で抽出した和暦年と、②その年号の元年の和から③1年マイナス(元年が1年の為)
ここではVlookup関数の詳しい説明は省きます。参照している表はHI列の表で、この元年西暦をそもそもマイナス1の数値にしてしまえば、関数内の最後の「-1」は不要です。私は後から見たときに元年と西暦が正しく一致していたほうが判りやすかと思い、このようにしました。「元年からマイナス1」と注意書きがあれば後から見た人もすぐに判るかもれしませんね。方法は様々です。
E列…D列で求めた西暦年と、A列の元データの月日を組み合わせる
さて、これでインポートに必要な形式に直せました。他にもいろいろ方法はあると申し上げましたが、では話題のChatGPTはどのような方法を教えてくれるでしょうか。何かもっと効率的なシンプルな方法を教えてくれるといいなという期待しつつ…。
ChatGPTはExcel操作の助けになるか
今回利用したのは無料で、メールアドレスの登録だけで利用できるサービスです。
https://chat.openai.com/auth/login
新規登録される場合は、「Sign up」からお進みください。
まず、こちらで以下のような質問を投げかけました。
関数での回答をお願いしたのですが、VBAコードを教えてくれました。実行してみましたが、動きません(汗)。私はマクロにさほど詳しくありません。セルの色をショートカットキーで変えられるように設定する程度。そんな私でもあきらかに間違いだろうなという箇所もあり、再度、関数での回答をお願いしました。
これはエラーにはなりませんでしたが、正しい西暦年にはなりませんでした。
複数の間違いがあるため、ひとつひとつ見ていきます。まずは年号が英字になっていますので、そこを修正。
残念ながら、これでもまだ正しい年数ではありません。この式は単純に、和暦の該当西暦の千の位と百の位の２桁と、和暦の年数を合わせているだけに見えます。
それでも少しずれている気がするので、関数の解説をお願いしました。
正しく変換はできませんでした(汗)。私の指摘の仕方が伝わりにくかったのかもしれません。
もしくは「和暦」の意味が理解不足なのかもしれません。日本語や日本の文化には弱いようです。ChatGPTの関数を私なりに修正して示してみました。
このように、ChatGPTで得られる回答は不確かなものも多いようです。質問者にもそれなりの知識が必要なこともあります。それでも、自分の発想以外のヒント・多様な発想を知る術としては面白いですね。更にその発想を明確に言語化してくれる点も役に立つのではないかと思います。
今回はExcelの操作方法や関数の生成をお願いしましたが、既存の、長くて判りにくい計算式の解説をお願いするなどの作業の方が、今のところは向いているような気がします(〇税庁の文章の要約とか…？)。
今回は無料で使用できる範囲で利用しましたが、有料のサービスではもう少し精度が上がるという話も聞きます。今後はもっと進化することを期待できるかもしれませんね。
最後に、ChatGPTをご利用の際は、質問の仕方を工夫しましょう。前提条件・明確な指示・具体的な例などを与え、不確かな回答をされた場合には、原典の提示を求めるとよいです(数か月前に質問した際には存在しない書籍を紹介されたことも)。また、情報提供の際は、漏洩にも配慮する必要があると思われます。
ChatGPTがどのようなものか表現するために、少々冗長になってしまったかもしれませんが、ご覧いただいたように、ChatGPTは丁寧に、快く説明してくれたり、間違いをきちんと認めたりします。なんだかとても憎めない、癒されるツールともいえるのではないでしょうか。皆さんもまずは無料版からAIとの会話をお試しください！
