ExcelでA1に「年」B2に「干支」を表示する関数

Question

A1に「2023年」B2に「干支」を表示したいです。

https://www.officepro.jp/excelfunc/howto/index23.html
のサイトを参考に

=CHOOSE(MOD(YEAR(DATEVALUE(A1&"1月1日")),12)+1,"申(さる)","酉(とり)","戌(いぬ)","亥(い)","子(ね)","丑(うし)","寅(とら)","卯(う)","辰(たつ)","巳(み)","午(うま)","未(ひつじ)") 

と打ち込んでいるのですが、何度やってもVALUEのエラーになります。
A1の「年」を抜いて「2023」にしてもVALUEのエラーになります。

ChatGPTに何度聞いても「問題ありません」と返されますが、現実として返されるのはエラーです。

試しに
A1を「2023」のみにしてB2に
=CHOOSE(MOD(YEAR(A1), 12) + 1, "申", "酉", "戌", "亥", "子", "丑", "寅", "卯", "辰", "巳", "午", "未")
と打ち込むとちゃんと表示されます。

A1に「2023年」B2に「干支」を表示したいです。
A1に「年」の文字はどうしても入れたいです。「干支」を表示するにはどうすればいいでしょうか？

ご教授いただけたら幸いです。

goomania · Accepted Answer

No.5です。
前回回答で、

>「yyyy"年"」という方法はA1セルの値をシリアル値として数式を組み
>立てるという方針を定めたことになりますので、それ以外の方針の数式
>は適用できません。

と書きました。するとご質問者は、

>yyyy"年"のユーザー定義を解除して標準に戻して、･･･

と仰って、上記の方針をあっさり変更してしまいました。

方針を変更すると、もとの数式が適用できないのは、上記で述べたとおりです。前回回答で、No.4の回答の3)の数式をB1に記述して下方向へコピーと書いたのは、A1セルを「yyyy"年"」とする「方針」の時に適用する数式だからです。

ユーザー定義を解除して標準に戻したのであれば、適用する数式はNo.4さんの2)の数式になります。

従って、ご質問者が掲出された画像でいうと、C2セルに

=MID("子丑寅卯辰巳午未申酉戌亥",MOD(LEFT(A2,4)+8,12)+1,1)

という数式を記述して下方向へコピーするということになります。
※大変失礼ではありますが、No.4さんの回答はLEFT関数内のセルが一つづれているようです。上記数式に修正してください。

goomania · Answer

No.3です。
長文で失礼します。

>A列を選択してセルの書式設定からユーザー設定で「yyyy"年"」にしたら
との補足質問ですが、私の回答のどこにも上記のようなアドバイスはありません。何故そのようにされたのか判りません。

数式を作成するときは「方針」が大切です。セルの値が「数値」に見えているが実は「文字列」というよなこともあるからです。

従って、セルの値を数値として数式を組み立てるのか、文字列として数式を組み立てるのかという「方針」決めて進めていきます。

例えばNo.4さんの回答で言えば、1)、3)は数値として数式を組み立てており、2)は文字列として数式を組み立てています。

数値として組み立てる場合で日付の場合はシリアル値との関係性を理解する必要があります。「2023/1/1」はシリアル値では「44927」ですから、数値といってもシリアル値から西暦年号を取り出すには、「YEAR(シリアル値)」のような関数を用いて「2023」を取り出す必要があります。

つまり、いろいろな回答者から上記のように「方針」の異なる回答が複数示されている状況なので、ご質問者は数式の意味を理解する必要があるのです。理解せずに各回答者のアドバイスの一部を取り入れたりすると余計混乱に陥ります。

冒頭のユーザー設定で「yyyy"年"」という方法はA1セルの値をシリアル値として数式を組み立てるという方針を定めたことになりますので、それ以外の方針の数式は適用できません。

間違えてはいけないことは、数式がエラーにならないからといって正しい結果になっているとは限らないことです。
A1に「2023」とに入力して表示形式をユーザー設定で「yyyy"年"」にしたらどうなるでしょう?
「1905年」と表示されるはずです。つまり、エラーにならないとしてもこれは「1905年」の干支が求められるだけで、「2023年」の干支ではないということになります。
A1に「2023/1/1」と入力し、表示形式をユーザー設定で「yyyy"年"」とすると「2023」と表示されますが、表示形式を「G/標準」にすると「44927」と表示されます。これでNo.4さんの1)の数式をB1に入れたらどうなるでしょう?
「未(ひつじ)」と表示されるはずです。つまり、エラーにならないとしてもこれは「44927年」の干支が求められるだけで、「2023年」の干支ではないということになります。

>クエリから読み込んだ1800年からの2023年までの年表データに干支を表示させたい
とのことですが、大前提としてEXCELで日付をシリアル値として扱えるのは1900年1月1日以降ですから、シリアル値を用いて1800年から計算することは不可能です。

シリアル値でない西暦を数値として計算することは可能ですが、現在の暦が使用されるようになったのは明治6年1月1日(1873年1月1日)からで、この日はそれまで使用されていた天保暦では、明治5年12月3日に当たります。つまり、それ以前は現在の西暦で言う1月1日に新年となる訳ではないことになります。ですから干支の範囲も西暦の年号の範囲と必ずしも一致しないことになり、これを求める意味があるのか疑問です。

また、ご質問者はご自分の状況に関する情報を小出にされているようです。
当初のご質問に「クエリから読み込んだ･･･」などという説明はどこにもありません。回答者はご質問の内容からしか情報を得られないので、突然前提としていない情報を突き付けられても「聞いてないよ～」(©ダチョウ倶楽部)となるだけです。

>連続したコピー処理はしておらず、すべて各セルで手入力しても同じ結果でした。
普通は「連続してコピー処理をする」のが正しい処理です。「すべて各セルで手入力しても･･･」各セルに同じ数式が入力されているので同じ結果が返るのは当たり前です。
もう一度、B1セルにNo.4さんの回答の3)の数式を入れて、下方向へコピーしてみてください。

fujillin · Answer

こんにちは

実際に、A1にどのよう値を入力なさっているのか不明ですが・・

1）A1セルには「2023」のように数値を入力している場合。
・A1セルの書式を「0000年」としておくことで、「年」付きで表示できます。
・B2セルには、以下の式を入力
　=MID("子丑寅卯辰巳午未申酉戌亥",MOD(A1+8,12)+1,1)

2）A1セルには「2023年」のように文字として入力している場合。
・B2セルに以下の式を入力
　=MID("子丑寅卯辰巳午未申酉戌亥",MOD(LEFT(A1,4)+8,12)+1,1)

3）A1セルには「2023/1/1」のように日付として入力している場合。
・A1セルの書式を「yyyy年」としておくことで、「年」付きで表示できます。
・B2セルには、以下の式を入力
　=MID("子丑寅卯辰巳午未申酉戌亥",MOD(YEAR(C1)+8,12)+1,1)

※ 質問者様がどのようになさっているのか不明ですが、多分、上記のどれかには該当するのではないかと想像します。

goomania · Answer

ご質問者が掲出した数式のように、"1月1日"を付加してから年を抽出するという考え方なら、
=CHOOSE(MOD(YEAR((A1&"1月1日")*1), 12) + 1, "申", "酉", "戌", "亥", "子", "丑", "寅", "卯", "辰", "巳", "午", "未")

もともと年号はA1セルの左4文字だと考えるなら
=CHOOSE(MOD(LEFT(A1,4), 12) + 1, "申", "酉", "戌", "亥", "子", "丑", "寅", "卯", "辰", "巳", "午", "未")

A1セルの5文字目以降は消してしまえば数字だけになると考えるなら
=CHOOSE(MOD(REPLACE(A1,5,10,""), 12) + 1, "申", "酉", "戌", "亥", "子", "丑", "寅", "卯", "辰", "巳", "午", "未")

銀鱗 · Answer

=CHOOSE(
　　　　　MOD(
　　　　　　　　YEAR(
　　　　　　　　　　　DATEVALUE(A1&"1月1日")
　　　　　　　　　　　)
　　　　　　　　,12
　　　　　　　　)+1
　　　　　,"申(さる)","酉(とり)","戌(いぬ)","亥(い)","子(ね)","丑(うし)","寅(とら)","卯(う)","辰(たつ)","巳(み)","午(うま)","未(ひつじ)"
　　　　　)

おかしくないんだけどなあ。
むしろA1セルに2023と入力すると、おかしなことになるでしょうけどね。
A1セルに ”2023” と入力した場合、DATEVALUE関数の中で
　”20231月1日” 
となって、Excelで扱えない値になり #VALUE! エラーになるんじゃないかな。

ということで、
　A1セルが実は「数値入力」で「セルの表示形式」で ”年” を表示させている。
なんて事を推測してみる。
その場合、
　DATEVALUE(A1&"1月1日")
　↓
　DATEVALUE(A1&"年1月1日")
とすれば正常に動作しますよ。

タマタマポチ · Answer

A1でセルの書式設定からユーザー設定で「yyyy"年"」としてはいかがですか？
なお、単純に2023だけではシリアル値で1905になりますから、A1の入力値は2023/1/1などのようにする。
　
https://atmarkit.itmedia.co.jp/fwin2k/win2ktips/331cellfmt/cellfmt.html#ope

ExcelでA1に「年」B2に「干支」を表示する関数

No.5です。

No.3です。

こんにちは

ご質問者が掲出した数式のように、"1月1日"を付加してから年を抽出するという考え方なら、

=CHOOSE(

A1でセルの書式設定からユーザー設定で「yyyy"年"」としてはいかがですか？

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング