Excelで東証のカレンダーを作りたい

Excel Office Home and Business Premiumを使っています。
2023年の東証営業日をExcelシートのB列縦に5を12月30日にして1月の大発会までのカレンダーを作る数式を教えて頂けませんか？(土、日、祝祭日を除きます）よろしくお願いいたします。

質問者からの補足コメント

• 早速のご回答ありがとうございました。小生のやり方に問題があると思うんですが、上手くゆきません。画像を添付（小さくてごめんなさい）しましたが、2023/1/2，1/3も表示されます、原因がわかれば教えていただけませんか？

  
  No.1の回答に寄せられた補足コメントです。 補足日時：2022/12/31 08:03

No.1 回答者： fujillin 回答日時： 2022/12/30 22:35

こんばんは

添付の画像が良く見えないのでわかりませんけれど・・

「B5セルをスタートに、縦に東証の営業日だけを並べたい」
ということと解釈しました。

まず、土日以外の休業日一覧を作成しておく必要があります。
例えば、以下のようなサイトから「休業日一覧」を作成しておきます。
https://www.jpx.co.jp/corporate/about-jpx/calend …

・添付図では、仮にG列を一覧にしてあります。
　（違う列でも良いですし、別シートでも良いです。）
・B5セルに、スタート日となる 2022/12/30 を入力します。
・B6セルに以下の式を入力し、下方にフィルコピーします。
=AGGREGATE(15,6,($B$5+ROW($A$1:$A$400))/(WEEKDAY($B$5+ROW($A$1:$A$400),2)<6)/(COUNTIF(G:G,$B$5+ROW($A$1:$A$400))=0),ROW(A1))

以上で、営業日だけをリストアップできると思います。

No.2 回答者： fujillin 回答日時： 2022/12/31 11:05

No1です。

関数式の意味としては、「土日及び休日一覧(＝G列)に同じ日があるもの」を除いて算出するということになっています。
COUNTIF関数でG列に同じ日がないことをチェック条件にしていますので、G列の値はシリアル値（＝エクセルの日付型の値）である必要があります。

補足にご提示のシートの場合、G列の値が「2023/01/01（日）」のようになっていますが、多分、値が文字列になっているのではないかと想像します。
もしも、セルの書式設定で「yyyy/mm/dd (aaa)」としてご提示のような表示になっているのであれば、セルの値はシリアル値なので問題はありませんが、多分、違うであろうと推測します。
これにより、G列の値との比較が上手くいかず（＝同じ値がないと計算してしまい）、結果として「土日を除いた日」が全て表示されるようになってしまっていると考えられます。

ご提示のG列の値から曜日の部分を取り除いて日付（＝シリアル値）に変換するには、例えばF3セルに
　=LEFT(G3,LEN(G3)-3)*1
の式を入れて、下方にフィルコピーすれば可能です。
その状態でNo1の式をF列を参照するように変更するか、あるいは、F列をコピーしてG列に「値をペースト」することでG列の値を修正すれば宜しいでしょう。


ついでですが、No1の関数式は若干計算負荷が大きいので、負荷を軽くした式を記しておきます。
計算方法は同じですが、計算する日にちの範囲を縮めています。
B6セルに、
=AGGREGATE(15,6,(B5+ROW(A$1:A$9))/(WEEKDAY(B5+ROW(A$1:A$9),2)<6)/(COUNTIF(G:G,B5+ROW(A$1:A$9))=0),1)
として、下方にフィルコピー。
※ 「9日以上連休になることはない」という仮定での計算式になっています。

No.3 回答者： goomania 回答日時： 2022/12/31 17:31

fujillinさんがWEEKDAY関数を使った数式をお示しになったので、WORKDAY関数を使った数式をお示しします。

東証の休業日は以下のサイトで公表されています。
https://www.jpx.co.jp/corporate/about-jpx/calend …
このサイトから2023年の休業日の一覧をE2セル～E20セルに作成したとします。
ご質問者のご要望に従ってB5セルに「2022/12/30」を入力します。
B6セルに、

=WORKDAY($B$5,ROW(A1),$E$2:$E$20)･･････①

を記述し、下方向へフィルコピーすれば2023年の東証営業日が表示されることになります。

添付画像をご覧ください。

この例はB6セル以下に2023年の「東証営業日」を表示するようにB6セルに①の数式を記述し、下方向へコピーしています。

下方向へコピーすることにより、「2024/1/1」以降の日付も表示されることになりますが、2024年の休業日はまだ公表されておらず、E列の休業日の一覧に2024年分がないため「2024/1/1」以降は土日以外の休業日が反映されません。

休業日が公表された時点で、E21セル以下に休業日一覧を作成して①の数式の$E$2:$E$20の部分を修正すれば2024年以降も使用可能です。

No.4 回答者： fujillin 回答日時： 2022/12/31 21:35

No2です。

WORKDAY関数の存在を忘れていました。

No3様の回答の方がスマートで良いですね。
そちらをご利用なさってください。

No.5 回答者： goomania 回答日時： 2023/01/01 13:51

No.3です。

>小生のやり方に問題があると思うんですが、上手くゆきません。画像を添付
>しましたが、2023/1/2，1/3も表示されます。原因がわかれば教えて
>いただけませんか？

との補足質問がありました。

推測すると、ご質問者は日付に見えているセルの中身が「シリアル値」であるか、「文字列」であるかの区別がついていないように思います。

前回回答で「E2～E20セルに東証休業日の一覧を作成する」と書きましたが、ご質問者は東証のホームぺージにある休業日の一覧をコピペしてEXCELに貼付けしていませんか?

その方法で一覧を作成すると休業日が「文字列」になってしまいます。
休業日一覧は「シリアル値」で作成する必要があります。
添付画像をご覧ください。

E2セルは「2023/01/01(日)」と表示されており、表示だけ見ると東証のホームぺージにある休業日と何ら違いがありません。

しかし、E2の中身は「2023/1/1」となっており、これをセルの書式の表示形式をユーザー定義で「yyyy/mm/dd(aaa)」として日付と曜日を表示しているのです。こうすると、EXCELではシリアル値として認識されます。
※つまり、(曜日)は直接セルに入力するわけではありません。

このような手当を行って初めて、数式が有効に機能することになります。

つまり、これを怠ると、「2023/1/2，1/3も表示されます」という結果になってしまいます。

No.6 ベストアンサー 回答者： goomania 回答日時： 2023/01/02 08:06

No.3、No.5です。

大変失礼いたしました。
ご質問者の投稿された画像をよく見ると、12月30日は2023年12月30日のことであり、そこから日付を遡ったカレンダーを作りたいという趣旨だったのですね。
早とちりして、2022年12月30日からの昇順のカレンダーを表示する数式を投稿していました。

添付画像をご覧ください。添付画像のように東証休業日一覧を作成した場合、ご質問者の趣旨に沿ったカレンダーを作成するのであれば、B5セルに

=WORKDAY("2023/12/30"*1,1-ROW(A1),$E$2:$E$20)

という数式を記述して、下方向へコピーすればよいことになります。

No.3の早とちり回答をお詫びし、訂正いたします。

この回答へのお礼

うまく行きました、ありがとうございました、大変助かりました。

お礼日時：2023/01/09 19:48