別シートのスケジュールリストをカレンダーに反映させたい

こんばんは。
万年カレンダーに別シートのスケジュールリストを反映させる方法をうかがったところ、とても丁寧に教えてくださり何とか完成することが出来ました。
ところが、1日につき20件ほど反映させたい項目がある日があることがわかり(教えていただいた時は最大で7件くらいでしたのでカレンダーも7行/日でした)、いろいろしてみたもののうまく反映されませんでした。
カレンダーを20行/日にした場合の式を教えていただけないでしょうか？
また20行以上必要な場合、カレンダーの行をさらに増やす必要性があるため式をどのように変更すればいいか教えていただけると幸いです。

◇条件
・エクセル2016
・シート1のA列はシリアル値（＝エクセルの日付型の数値）である
・シート1のH列にはB～D列を結合させたものを文字列化している
・シート2の日付のある行番号は、3、24、45、66、87、108行目で固定である
・シート2の上記の日付行の日付はシリアル値を書式で日だけ表示している
・下記式はシート2のカレンダー7行/日の条件で教えてくださった式です。

シート2のA4セルに
=IFERROR(INDEX(Sheet1!$H:$H,AGGREGATE(15,6,ROW(A$2:A$99)/(Sheet1!$A$2:$A$99=INDEX(A:A,INT(ROW(A8)/8)*8-5)),MOD(ROW(A8),8)+1)),"")

不勉強で申し訳ありませんがどうぞ宜しくお願い致します。

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

• こんばんは。
  ご丁寧にご回答ありがとうございます。
  
  教えていただいた式で反映がされるようになりました！
  ありがとうございます！
  ただ月を変えると表示されなくなってしまいました。。
  どのように対応したらいいでしょうか？

  No.2の回答に寄せられた補足コメントです。 補足日時：2021/10/11 23:13

No.2 ベストアンサー 回答者： goomania 回答日時： 2021/10/11 14:10

ご質問者は

>万年カレンダーに別シートのスケジュールリストを反映させる方法
とおっしゃっていますが、「万年カレンダー」は以下の変更が簡単に行えるものなのでしょうか?
もし以下の２つの変更が簡単に行える仕様なら、そもそも各週の項目表示行数を固定値(例えば7行固定、20行固定)ではなく、項目数の多い週は20行、少ない週は7行などと可変にする方法もあります。
しかし、以下の変更が簡単には行えない仕組みなら、この回答は役に立たないので無視してください。
(1)行数を増やしたいとき、単に「行の追加」を行えばよく、カレンダー自体は調整しなくてもよい仕組みになっていますか
(2)現在のA列の左側に列を一列追加したとき、単に「列の追加」を行えばよく、カレンダー自体は調整しなくてもよい仕組みになっていますか

上記(1)(2)が両方ともOKなら、とりあえず、前回回答者さんが回答された数式を用いて、7行固定の表を作成してみてくだい。
その状態でA列の左側に新たな列を一列追加してみてください。前回回答者さんの数式は列追加の影響を受けないと思われますので、そのまま、各日のスケジュールが表示された状態を維持していると思います。これが確認できたら以下の手順を試してください。
(3)新たなA列の右側に日付が表示されている最初の行(3行目)に「=B3」を記述し、各日付表示行(11行目、19行目、27行目･･･)にコピー
(4)新たなA列の文字色を白色にして(3)で入力した各セルの値を「不可視」にする
(5)新たなB4セルに記述されている数式を以下の数式に修正

=IFERROR(INDEX(Sheet1!$H:$H,AGGREGATE(15,6,ROW(B$2:B$100)/(Sheet1!$A$2:$A$100=INDEX(B:B,AGGREGATE(15,6,ROW($A$2:$A$100)/($A$2:$A$100<>""),COUNTIF($A$2:$A4,"<>")))),ROW(B4)-AGGREGATE(15,6,ROW($A$2:$A$100)/($A$2:$A$100<>""),COUNTIF($A$2:$A4,"<>")))),"")

上記数式を右方向、下方向にコピーし、スケジュール表示セル全体にコピー

(6)上記手順を踏んでも表示内容が変わらないことを確認してください。
(7)上記(6)の確認がとれれば、各週の行の追加、削除が自由にできる状態になったことを意味します。この後は、週によって行数を追加したり、不要な行を削除したりすることができます。ただし、行を追加した場合は、追加行の各セルに数式をコピーすることをお忘れなく。

このようにして作成した表が添付画像の表になります。

この回答へのお礼

こんばんは。
ご回答ありがとうございます。
状況によって行を増やすことになるので、本当に助かりました！
おかげさまでカレンダーを完成させることが出来ました！
本当にありがとうございました

お礼日時：2021/10/11 23:53

No.1 回答者： fujillin 回答日時： 2021/10/07 13:36

こんにちは

前回回答者です。

＞カレンダーを20行/日にした場合の式
行数が変わる場合は、参照設定で行っている行の計算を変更すれば対応可能です。
必要なのは以下の2か所です。

◇自行の日付を取得する計算
例えば、現状ではA4:A10の範囲はA3にある日付を参照する必要があります。
これに該当する部分が、
　INDEX(A:A,INT(ROW(A8)/8)*8-5)
です。
試しに、A4に =INDEX(A:A,INT(ROW(A8)/8)*8-5) の式を入れて、各セルにフィルコピーしてみると、それぞれの日付を参照していることがわかると思います。
　INT(ROW(A8)/8)*8-5
の部分が行数を計算している部分なので、4~10行は3、12～18行は11・・となるような計算式になるように作成してあります。
これによって、それぞれのセルで「検索すべき日付」を指定できるような仕組みになっています。

◇同一日付内での順序を取得する計算
例えば、A4:A10に表示すべき内容を区別するために、そのセルが同じ日付の中で「何番目のセル」に当たるのかで区別しています。
上から順に1～7（A4が1、A5が2・・・）として数えています。
これに該当する部分が、
　MOD(ROW(A8),8)+1
です。
こちらも、試しに各セルに代入してみると、それぞれの日付のなかで、順に1～7が
表示されることで確かめることができると思います。


長々となってしまいましたが、上記の2か所の数式部分を
　・各セルから参照すべき（日付のある）行番号
　・同じ日付内のセルの順番
を示すような式に修正することで、好きなレイアウトに合わせることが可能になりますので調整してみてください。

式を作成するのが面倒なら、週単位の式にしてしまって
・日付の行番号は固定値（3とか11とか‥）
・セル内の順序は Row(A1) に指定
にしておいて、各週で調整するようにすれば、全体の式もわかり易くさらに短くすることが可能になると思います。


※ 前回の回答内容では、固定レイアウトに合わせた固定計算になっていますが、多少検討することで、一般化して、指定行数に合わせて可変にすることもできるようになるものと想像します。
そんな風にしておくと、ご質問のようにレイアウトがいろいろと変わるようであるならば、便利かもしれませんね。

この回答へのお礼

こんばんは。
今回もご回答ありがとうございます。
丁寧に教えてくださりfujillin様には感謝しております。
fujillin様とやりとりしていると関数が面白いなと思えました！
ありがとうございます！

お礼日時：2021/10/11 23:25