【超便利】月・年の選択だけで、自動更新するカレンダーのつくりかた|#ArrayFormula関数 #Indirect関数 #スプレッドシート


以前も、ArrayFormula関数をつかって、自動カレンダーを作成する方法を紹介しました。

【スプレッドシート】ArrayFormula関数を使って、日付入力を自動化しよう

2019.02.05

今回はその発展版、ドロップダウンリストの月・年を選ぶだけで、自動更新するカレンダーです。

➡︎ 答えを見る

A2 =ArrayFormula(row(indirect(date(D3,C3,1)&”:”&eomonth(date(D3,C3,1),0))))
このファイルはこちら

月・年の選択だけで、日付が入力されるなんて便利ね!

ドロップダウンの作り方を知らない方は、以下の記事を参考にして下さいね(^^)

【スプレッドシート】ドロップ(プル)ダウンリストのつくりかた|動的参照, INDIRECT

2020.01.28


Step # 1:月初と、月末の日付を取得する


  1.  Date関数で、月初の日付を取得する

  2.  B2 = Date(E3,D3,1)

    = Date(年, 月, 日)
      Date(年, 月, 日)の形で、その日付を日付形式(年/月/日)にしてセルに返します。年と月はプルダウンで指定したD3,E3から、日は月初の日付なので、当然「1」ですね。


  3.   Eomonth関数で、月末の日付を取得する

  4.  B2 = Eomonth(Date(E3,D3,1),0)

    EndOfMonth (エンド・オブ・マンス) 関数は、名前の通り月末の日付を求めます。


    = Eomonth(”年/月/日”, -2〜2
     ”年/月/日”に最終日を求めたい月と、その年を入力します。日は何でもOKです。”年/月/日”は日付形式なので、date関数を置くこともできます。

     -2〜2は、入力月から見て、「先々月(-2)」から「再来月(2)」まで、最終日を取得する月を再調整します。0はその中央、つまり入力月そのままの最終日を取得をします。



    Step # 2:月初から月末まで、整列させる


    あとは月初から月末まで、整列させるのかな?
    そうね。例えば、1〜31の羅列なら、=ArrayFormula(Row(1:31))を使うわ。これを利用しましょう。
    =ArrayFormula(Row(月初:月末))でいいの?
    そうなんだけど、それだけだとエラーになるの。実際に見てみましょう!

    1.   月初と月末に、さっきの を入れ、A1に入力すると…

    2. A1 = ArrayFormula(Row( Date(E3,D3,1) : Eomonth(Date(E3,D3,1),0)))
      範囲ではないという理由で、エラーが表示されました。


    3.   どうして範囲ではないの?

    4. エラーとなった範囲、 

      Date(E3,D3,1) : Eomonth(Date(E3,D3,1),0)
      とは、つまり

      2018/4/1 : 2018/4/31
      のことですね。これが範囲認識されないのは、日付は範囲を示す数字だと認識されないからです。

      一方で、実は形式が日付表示となっているだけで、本来日付は数字で作られています。

      2018/4/1なら「43191」、2018/4/31は「43220」といった具合です。

      以下の手順で設定を手動変更できます。

       手順1: B2, C2セル選択状態にする
       手順2: タブ>表示形式>数字>「0(数字表記の意味)」をクリック


      Row関数は、数を範囲だと認識するから、2018/4/1を43191だったり、2018/4/31を43220に変えればいいのね。
      上図みたいに、タブから設定を変更するの?
      うんん。Date/Eomonth関数は、その式の中で数字を日付形式にするの。だから、その式中で、別の関数を使って数字形式に戻すしかないのよ。

      Step # 3:indirect関数で、日付を数字形式に【完成】


      先ほどの関数に、日付を数字表記に変えるindirect関数を混ぜて、A2に入力します。

      A2 = ArrayFormula(row(indirect( date(D3,C3,1)&”:”& eomonth(date(D3,C3,1),0))))
       indirect関数のお陰で、範囲が数字として認識されましたね。きちんと動作しています。式中の「&」は文字列同士をくっつける役割です。



      indirect関数で、この「” “」(=二重引用符)の有無は重要よ。
      確かに、date(D3,C3,1)には「” “」がないけど、真ん中は「”:”」になってる。
      「” “」がある場合、文字列で情報を取得するの。一方、 「” “」なしは真の意味での情報取得よ!
      真の意味!?
      Date関数には「” “」が付いてないでしょう?これはDate関数の答え(=2018/4/1)を真の意味(=本来の数字表記)で取得したいからなの。一方、「”:”」なのは、この点をただの文字として取得するってことね。
      へぇ。じゃあ、二重引用符「” “」なしで、「2018/4/1」を真の意味(= 数字表記)に変更できるんだ。
      その通りよ。Eomonth関数も、これで日付を数字に変更してるの。詳しくは次の記事を参考にしてね。

      【スプレッドシート】INDIRECT|セル上の情報(セル番・シート名)を介して、別のセル・シートを参照(#動的参照)

      2020.01.23