エクセル SPILLを使う方法

教えてください。
添付画像のように、C~H列の2行目に数式や値が入ってます。
3行目以降は、A列に値があればその行にC~H列の2行目の内容をコピーしたいのですが
4行目以降は数式を入れずゴーストのようにできないでしょうか？
数式の量が多い為容量が大きく数式を減らすことが目的です。
【例】
A3に値があれば、C~H列の2行目の内容をC~H列の3行目を表示
A4に値があれば、C~H列の2行目の内容をC~H列の4行目にコピー
A5に値があれば、C~H列の2行目の内容をC~H列の5行目にコピー

microsoft365のエクセルを使用しています。
よろしくお願いいたします。

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

• 回答ありがとうございます。
  イメージどおりです。
  ただ、C2~H2の値は問題ないのですが、数式ついてはコピーされてるように見えるのですが、
  計算はされていませんでした。
  やはりSPILLでは無理でしょうか？

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

• 分かりやすい回答をありがとうございます。
  先ほど、「計算されない」と書いたのですが意味合いが違いましたので訂正します。
  添付画像のように、
  C3セルに　=IF($A3="","",IF(COUNTIFS($B3,"*L*")>0,C$1,C$2))　という式が入っており
  A3が空白でない状態で、
  B3の値にLが含まれていればC1を参照
  B3の値にLが含まれていなければC2を参照
  する事を目的としています。
  C4セルに=IF($A4:$A20<>"",IF($A3="","",IF(COUNTIFS($B3,"*L*")>0,C$1,C$2)),"")
  と入力してみたのですが、全てC1の値が表示されてしまいました。
  やはり式がおかしいでしょうか？

  
  No.5の回答に寄せられた補足コメントです。 補足日時：2021/09/01 03:23

No.6 ベストアンサー 回答者： goomania 回答日時： 2021/09/01 17:32

No.5です。

>やはり式がおかしいでしょうか？
そのとおりです。
前回回答で、
>数式がある場合は、その数式そのものをSPILL範囲に対応した
>「配列数式」に記述し直した上で、IF関数を用いて･･････
と述べました。
従って、ご質問者がC3セルに記述した、
=IF($A3="","",IF(COUNTIFS($B3,"*L*")>0,C$1,C$2))･･･①
という数式をSPILL範囲に対応した配列数式に修正する必要があります。ご質問者は
=IF($A4:$A20<>"",IF($A3="","",IF(COUNTIFS($B3,"*L*")>0,C$1,C$2)),"")･･･➁
という数式を記述したがうまくいかないとおっしゃっています。
これも前回回答で、
>数式が複雑なものだと、これを配列数式に修正するのに工夫が必要な場合があります。
>「オートフィルによるコピー」と同じ結果になるように「配列数式」で作成
と述べました。
なので、数式を工夫してオートフィルでコピーしたと同じ結果になるように修正します。
添付画像をご覧ください。J3に
=IF(COUNTIFS($B3,"*L*")>0,C$1,C$2)･･･③
を記述し、下方向へオーフィルでコビーしてあります。
③をSPILL範囲に対応した配列数式にするため、「COUNTIFS関数」を「ISNUMBER関数とFIND関数の組合せ」に変更します。K3に
=IF(ISNUMBER(FIND("L",$B3:$B20)),C$1,C$2)･･･④
を記述してあります。J列とK列が同じ結果になり、④は③をSPILL範囲に対応した配列数式に修正したものとなったことが判ります。
また、➁のうち「$A4:$A20<>""」「$A3=""」は「A列に値があったら」という判定なので重複しています。
これらの問題を加味して、➁の数式を修正すると以下のようになり、C3に、
=IF($A3:$A20="","",IF(ISNUMBER(FIND("L",$B3:$B20)),C$1,C$2))･･･➄
という数式を記述すればご希望の結果になると思います。
つまり、手順とすると、
(1)従来の数式から「A列に値があったら」という判定部分を除いた数式を抽出する
(2)その数式を邪魔にならない列の3行目に記述し、オーフィルで下方向へコピー
(3)上記(2)と同じ結果となるよう(2)の数式をSPILL範囲に対応した配列数式に書き換える
(4)上記(3)で書き換えした数式を(2)の隣の列の3行目に記述して、結果を(2)と比較して一致を確認する
(5)一致が確認できたら、IF関数を用いて「A列に値があったら」という判定「$A3:$A20=""」(あるいは「$A3:$A20<>""」)を付加した数式にして完成させる
ということになります。

この回答へのお礼

回答とても分かりやすいです。
ありがとうございました。

お礼日時：2021/09/02 02:50

No.5 回答者： goomania 回答日時： 2021/09/01 00:05

No.2です。

ご質問者は
>数式ついてはコピーされてるように見えるのですが、
>計算はされていませんでした。
とおっしゃっていますが、「計算されていない」とはどういう意味でしょうか?
計算されていないなら何らかのエラーになってしまうのでしょうか?
そうではないように思います。
前回回答の但し書きで述べましたが、ご質問者が入力したC2～H2の中に数式がある場合、前回回答の方法では、「オートフィルによるコピー」とは異なり、あくまでもC2～H2の表示内容が3行目以降にコピーされることになります。つまり、計算されていないのではなく、3行目以降にはC2～H2(2行目)で既に計算された結果(全て同じ値)がコピーされる訳です。
いろいろ試した結果、添付画像のように、C2～H2に数式がある場合は、その数式そのものをSPILL範囲に対応した「配列数式」に記述し直した上で、IF関数を用いてA列に値があるかどうかを判定する数式と組合わせるという方法をとれば解決できることが判りました。
ただ、C2～H2に記述された数式が複雑なものだと、これを配列数式に修正するのに工夫が必要な場合があります。全ての数式を「オートフィルによるコピー」と同じ結果になるように「配列数式」で作成できるかどうかは、知識不足のため私にはわかりません。
もし困難な数式にあたったら、このサイトでご質問されることで解決できるかも知れません。

No.4 回答者： トモクンアヤチャン 回答日時： 2021/08/31 18:09

少し変えてみます。

Ｋ2:Ｑ12に参照元の表があります。
A2とＫ2には” ”（半角スペース）が入っています。
C2:H2には＝VLOOKUP($A2,$K$2:$Q$12,COLUMN()-1,0)が入っています。結果は1,2,3,4,5,6です。
C3:H3には=IF($A3:$A20="","",VLOOKUP($A3:$A20,$K$2:$Q$12,COLUMN()-1,0))が入っています。結果はa1,a2,a3,a4,a5,a6です。
C4:H20までスピルされています。
その結果、単にＣ2:Ｈ2がコピーされているのではなく、参照元の表が複写されます。いかがでしょうか。
2行目から、A2=IF($A2:$A20="","",VLOOKUP($A2:$A20,$K$2:$Q$12,COLUMN()-1,0))とやっても同じです。

No.3 回答者： トモクンアヤチャン 回答日時： 2021/08/31 16:47

（補足2021/08/31 12:49）の「数式についてはコピーされてるように見えるのですが、計算はされていませんでした。

」
とありますが、どのセルがコピーされており、どのセルが計算されていないのでしょうか。私のｴｸｾﾙでは、№2さんのやり方でできるようです
C3=IF($A3:$A20="","",C$2)をH3までコピー。
C4:H20までゴースト（グレー表示）が入っています。
Ａ3～A20までに何か入れるとその行のC:H列に2行目と同じ値が表示されます。
（少し式は違いますが同じ意味です。）

No.2 回答者： goomania 回答日時： 2021/08/30 22:39

ご質問者のご希望は、

>A列に値があればその行にC~H列の2行目の内容をコピーしたい
ということなので、SPILLの範囲を想定して、まず、A列の何行目まで値が入力されるのかを考えなくてはいけません。
仮に20行目まで値が入力されるとして、「A列に値があればその行にC~H列の2行目の内容をコピー」するなら、
C3セルに
=IF($A3:$A20<>"",C2,"")
という数式を記述し、H列まで、右方向へコピーします。
この状態で、A3、A4、A5に「あ」「い」「う」と入力してみます。
すると添付画像のように、数式を入力していない4行目、5行目もSPILL機能により、C列～H列に値が表示されることが確認できます。
C3を選択状態にすると、数式バーに数式が表示される(画像①)のですが、C4を選択状態にすると数式のゴーストが確認できます(画像➁)。
つまり20行目まではゴースト状態で「動的配列数式」が入力されていることになり、これでご質問者の希望が実現できるはずです。
ただし、ご質問者の希望が「数式」の下方向へのコピーである場合、「オートフィルによる下方向へのコピー」をイメージしているとすれば、SPILL機能による結果とは異なることになります。
例えば、C2セルに「=ROW(A1)」という数式が入力されていたとして、「オートフィルによる下方向へのコピー」なら、C3、C4、C5･･･は2、３、4･･･となるはずですが、この回答の方法ではC3、C4、C5･･･は1、1、1･･･となり、ずっと1のままです。

No.1 回答者： fujillin 回答日時： 2021/08/30 20:54

こんばんは

私の環境ではSPILL機能が使えないので、検証はできませんけれど・・・

＞4行目以降は数式を入れずゴーストのようにできないでしょうか？
縦方向にスピルさせたいということでしょうか？
例えば、A列に値がある範囲にスピルさせたいのであれば、C3セルに
　=IF($A$3:$A$10<>"",C$2,"")
とか。

A列などは関係なく、単にC3:C10にスピルさせたいのなら
　=IF(C$2="","",IF(ROW($A$3:$A$10),C$2,""))
とか。

＞C~H列の2行目の内容をC~H列の4行目にコピー
あるいは、横方向（C~H列）にスピルさせたいということなのでしょうか？
もしそうであるなら、C3セルに
　=IF(C$2:H$2<>"",C$2:H$2,"")
とか。

縦横まとめてスピルすることもできるのではないかと想像しますけれど、何分テストできる環境がないので、この辺りでやめておきます。

※ 検証できていませんので、うまくいかなかったならご容赦ください。