Apple IDログイン機能メンテナンスのお知らせ

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

「Excelで東証のカレンダーを作りたい」の質問画像

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

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

    「Excelで東証のカレンダーを作りたい」の補足画像1
    No.1の回答に寄せられた補足コメントです。 補足日時:2022/12/31 08:03

A 回答 (6件)

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の早とちり回答をお詫びし、訂正いたします。
「Excelで東証のカレンダーを作りたい」の回答画像6
    • good
    • 0
この回答へのお礼

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

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

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も表示されます」という結果になってしまいます。
    • good
    • 0

No2です。



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

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

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年以降も使用可能です。
「Excelで東証のカレンダーを作りたい」の回答画像3
    • good
    • 0

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日以上連休になることはない」という仮定での計算式になっています。
    • good
    • 1

こんばんは



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

「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))

以上で、営業日だけをリストアップできると思います。
「Excelで東証のカレンダーを作りたい」の回答画像1
この回答への補足あり
    • good
    • 1

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!