行の異なる連続した値を別のシートで横軸で反映させたい

画像上下でシートが分かれています。
画像下は1週間ごとのAとBの値になっています。
これが下に続いていき今後も新しい値が追加されていきます。
この画像下の値を画像上の別シートに関数を利用して横軸で展開していきたいのですが何か簡単な方法はありますでしょうか。
新しい値がでてきてもオートフィルで画像上のシートで右方向へ展開できるのが理想です。
INDEX関数が使えそうかと思うのですが式の入力もいまいちわからずお手上げになってしまったのでよくわからず。。。
よろしくお願いいたします。

No.1 ベストアンサー 回答者： fujillin 回答日時： 2021/04/14 13:37

こんにちは

並びに規則性があるので、最初の参照位置さえ与えれば後は単純計算で求められると思います。
（例えば、参照の最初が必ずF10であるとか、あるいは目視で探して最初の位置だけどこかのセルに記入するとか）

無駄に計算量が増えますが、同じ日付を表から探して該当するデータを転記する例を以下に。
・回答の都合上同じシート内に表示しています。
・2行目の日付と10、14…行の日付はともにシリアル値であるものとします。
・日付は2000年代、データは100%を超えないものと仮定
　（日付値とデータが偶然一致することがないためです）
・元データの範囲は（ひとまず）$F$10:$L$24と仮定しています。

添付図では、B3セルに
=IFERROR(INDEX($F$10:$L$24,INT(SUMPRODUCT((ROW($F$10:$L$24)*100+COLUMN($F$10:$L$24))*($F$10:$L$24=B$2))/100)+ROW(A1)-9,MOD(SUMPRODUCT((ROW($F$10:$L$24)*100+COLUMN($F$10:$L$24))*($F$10:$L$24=B$2)),100)-5),"")

の式を入力し、B4セルにフィルコピーし、更に、B3:B4セルを右方にフィルコピーしてあります。


※ もしも、データと日付とが同じ値をとる可能性があるような場合には、
「元データの行番号のMod 4が2である」という条件を加えれば、日付の部分だけを対象として検索するようになります。（式を短くするために省略してあります）

この回答へのお礼

ご回答いただきましてありがとうございます。
こんなに長い数式が必要だとは思いもしませんでした。
そしてもっと早く質問していればよかったと後悔しております。
まだまだエクセルについては勉強が必要そうです。

お礼日時：2021/04/14 15:05

No.2 回答者： fujillin 回答日時： 2021/04/14 15:35

No1です。

＞こんなに長い数式が必要だとは思いもしませんでした。
No1にも書きましたが、日付を検索する方式なので長くなっています。

同じ方式でも、作業セルを使えれば短くすることは可能です。
B列に対応するセル位置の計算（SUMPRODUCT関数部分）を作業セルに一旦計算させておいて、それを参照しながら計算する方式にすれば、かなり簡単にできます。

あるいは、最初のセル位置との関係が分かっているなら、そのままでも規則的に計算することが可能になります。
例えば、No1のレイアウト例で、B列は必ずF10セルと対応することがあらかじめ分かっているのなら、B2セルに

=INDEX($F$10:$L$24,INT(COLUMN(G1)/7-1)*4+ROW(A2),MOD(COLUMN(G1),7)+1)

を入力して、フィルコピーすれば、大分短い式ですが、同様の結果を得ることができます。
（こちらは、検索をせずに、規則性を利用して参照先を決める方法です）

No.3 回答者： goomania 回答日時： 2021/04/14 18:49

ご質問者のご希望の「INDEX関数」を活用した回答が既になされていますが、「INDIRECT関数」を用いた回答も考えられますので、お示しします。

fujillinさんの言葉を借りれば、こちは、検索をせずに、規則性を利用して参照先を決める方法です。
sheet1に元データがあり、これをsheet2に転記する前提です。

ご質問者がお示しになった画像のように、スタート位置がsheet1のF10で、これをsheet2のO2へ転記するところから始めるのだとすると、O2に

=INDIRECT("Sheet1!R"&ROWS($1:10)+INT((COLUMNS($O:O)-1)/7)*4&"C"&COLUMN($F10)+MOD(COLUMNS($O:O)-1,7),0)･･････①

または、
=INDIRECT("Sheet1!R"&ROW(A10)+INT((COLUMN(A1)-1)/7)*4&"C"&COLUMN($F10)+MOD(COLUMN(A1)-1,7),0)･･････②

を記述し、下方向、右方向へオートフィル等でコピーしてください。

①の数式を簡単に説明すると
(1)ROWS($1:10)の部分は元データのスタート行が10行目であると指定
(2)INT((COLUMNS($O:O)-1)/7)*4の部分は転記元と転記先との行位置を調整。「転記先が7列進むごとに転記元を4行進める」を繰り返す。
(3)COLUMN($F10)の部分は元データのスタート位置がF列目であると指定
(4)MOD(COLUMNS($O:O)-1,7)の部分は転記元と転記先との列位置を調整。0～6を繰り返す。

転記元、転記先の位置の調整は上記の説明を参考に、実際のデータに基づいて変更してください。