Excelで関数をコピペすると、途中のセルで切れてしまいます

Excelで1つの列全体を選択(例えばE列)し関数を入力すると全部のセルに同じ内容でコピペができますが、勝手に途中の行で止まってしまい、そこから下は空白になってしまいます。左側の列はもっと下までデータが書き込まれており(右列は全て空白です)、途中で止まる要素はありません。
何度やっても同じトラブルが起きてしまい、業務に支障が出ています。誰か解決策をお願いします。

No.9 ベストアンサー 回答者： トモクンアヤチャン 回答日時： 2021/08/04 08:48

№8です。

グレーアウトするのはスピル機能のためと思われます。
E列全体を選択しないで、Ｅ1セルに数式を入れ、Ｅ2以下にドラッグはできないでしょうか。

この回答へのお礼

ありがとうございます
色々試行錯誤(D列のデータが1ではなく5からなので、E5に関数を入力＆右下をダブルクリックでコピー)したら無事できました。

お礼日時：2021/08/08 11:52

No.10 回答者： goomania 回答日時： 2021/08/06 00:24

ご質問者は

>Excelで1つの列全体を選択(例えばE列)し関数を入力すると
>全部のセルに同じ内容でコピペができます
とおっしゃっていますが、手順の一部が抜けているのではないですか?
EXCEL365の環境で列全体を選択してfxを押して数式を入力しても、「全部のセルに同じ内容でコピペ」はできないと思います。
試しにご質問者が入力している数式を少し変更した以下の数式をE列全体を選択し、同様の方法で入力してみてください。
=countif(A5:A14000, Sheet2!A5)･･････(1)
E列の全てのセルに上記の数式がコピーできましたか?
コピーするのであれば、列全体を選択しfxを押して数式を入力し、「関数の挿入」のダイアログのOKボタンを押す前に、数式バーにカーソルを移動して、「CTRL+ENTER」を押す必要があると思います。
もし、上記の手順を漏らしているとすれば、ご質問者は単に「コピペの方法を勘違いしているだけ」ということになります。
つまり、他の回答者さんも指摘しておられますが、365のスピル機能をコピペと勘違いしているのだと思います。
記述する数式は
=countif(A5:A14000, Sheet2!A5:A14005)･･････(2)
という数式のようですが、試しに、列全体を選択せず、E1セルのみ選択して、上記(2)の数式を入力してみてください。列全体を選択したときと同じ結果になる気がします。
列選択→fxボタン→関数の挿入ダイアログ→CTRL+ENTERの手順でコピーすると(2)の数式は「#SPIL!」のエラーになります。後述しますが、(2)の数式は配列を返すので、連続するセルにコピーすると「スピル機能の邪魔をするな!」とEXCELから怒られるのです。
添付画像①➁をご覧ください。
365の環境で状況検証するため簡略化して、Sheet1に0～10の数値で20行のデータ(添付画像①)、Sheet2に1～10の数値で10行のデータ(添付画像➁)を作成して見ました。
E1に
=COUNTIF(A1:A10,Sheet2!A1:A10)･･････(3)
を入力します。
365ならスピル機能で10行目まで自動的に表示されますが、データの最終行は20行目まであるのに11行目以降は表示されません。ご質問者はこれ(スピル機能)をコビぺと勘違いして「途中でとまる」と思い込んでいるのではないかと想像します。
次に、F列全体を選択して
{=COUNTIF(A1:A10,Sheet2!A1:A10)}･･････(4)
という配列数式({}以外の数式を入力しCTRL+SHIFT+ENTERで確定)を入力します。
スピル機能のないバージョンではこのような方法が365のスピル機能で表示される結果に近いものになります。11行目以降は「#N/A」というエラーが表示されます。
さらに、G1に
=COUNTIF($A$1:$A$10,Sheet2!A1)･･････(5)
を入力して、下方向にオートフィル等でコピーします。
添付画像を見てお判りのように、どの数式も10行目までは同じ結果になっています。
Sheet1のデータが20件ありますが、Sheet2のデータが10件しかないので、(3)はスピル機能で10行目まで、(4)(5)の数式のどちらも、11行目以降は数式の表示結果には意味か無いことが判ります。
つまり、ご質問者がいう「勝手に途中の行で止まる」のはSheet2のデータの数によるものであって、仮に、(5)のような数式でSheet1の全ての行にコピーできたとしても、数式を記述したセルのうち、Sheet2のデータ件数を超えた行に表示される結果は「無意味」であるというのが結論になります。

ご質問者は、「勝手に途中の行で止まる」という数式を記述したシートが全体で何行のデータなのかお示しになっていません。
記述されているのはSheet1だとすると、Sheet1の最終行は何行なのでしょうか?
ご質問者のEXCELのバージョンは365とのことですので、ご質問者はあまり意識していなかも知れませんが、前述のとおり、上記(2)の数式の結果は配列が返ります。
365では数式の結果が配列となる場合、結果としてスピル機能で展開された配列が表示されます。(スピル機能のないバージョンでは配列の一番最初の項目のみ表示されます。)
つまり、スピル機能はコピペではないので、ご質問者の例では、結果は14001行の配列となり、Sheet1が14001行以上あるのなら「途中で止まる」のは当たり前だということになります。

この回答へのお礼

ありがとうございます

お礼日時：2021/08/08 11:53

No.8 回答者： トモクンアヤチャン 回答日時： 2021/08/03 19:32

スピルの機能のようです。

止まったところからドラッグでペーストしてはいけないんでしょうか。
関数を入れるのは、何度もやることではないと思います。
疑問の解決は大切ですが、できないことが分かったら前に進んでもいいんじゃないですか。

この回答へのお礼

それができずにいます。
E1のセル以下は全て関数が薄灰色の字になっていて、通常の方法でドラッグしても下のセルにコピーできません。

お礼日時：2021/08/03 21:04

No.7 回答者： chonami 回答日時： 2021/08/03 14:16

>=countif(A5:A14000, Sheet2!A5:A14005)

なるほど、やはりスピルですね。
（ちなみにフラッシュフィルは別の機能のことで値にしか使用できません）

スピルが影響してそうな気もするのですが申し訳ありませんが私は365は使用していないので、回答はここまでにしたいと思います。
情報はかなり引き出せたと思うので、どなたかから解決に結びつく回答がつくといいですね。

この回答へのお礼

ありがとうございました。

お礼日時：2021/08/03 14:19

No.6 回答者： chonami 回答日時： 2021/08/03 13:25

>関数の「fx」をクリックして関数(今回はcountif)を入力すると(左側の列にデータのある)最後の行まで同じ関数が入力されます。

365だから、スピルで入力されるのかな。
良かったらCOUNTIFの式をこちらに貼り付けてもらえますか？

さて、オートフィルタの状態でコピペしようとしているんですね？
途中で止まった状態でフィルタを解除したら空白行が入っていたりしませんか？

それと
>>一度何かのデータを入れていてそこを上書きのようなことはしてませんか？【再掲】

この回答へのお礼

関数はこんなです

=countif(A5:A14000, Sheet2!A5:A14005)

新旧2つの表でA列のデータの差異分を列に抽出しようとしています。
2つ目の数字(14000と14005)が異なるのは、データ数の関係で表の行数が異なるためです。

コピペはオートフィルタの状態ではありません。
双方の表のE列に最終行まで数字が表示されてからフィルタをかけるつもりです。

また、何かのデータを入れて上書きはしていないです。

お礼日時：2021/08/03 13:58

No.5 回答者： 銀鱗 回答日時： 2021/08/03 13:25

どうやら「フラッシュフィル」をコピペと勘違いしているようですね。

参照する先のデータが他と一致しない場合、フラッシュフィルは働きません。
13880行目の数式が表示されないなら、そこで参照するはずのデータが存在しないか、他と違う形で入力されているかです。

・・・

とりあえず、13879行から14000行のE列のセルを選択して、
　Ctrl+D
で、E13879セルの数式をE14000セルまで複製してみましょう。
簡単でしょ？名前ボックスに「E13879:E14000」と入力して、Ctrl+Dです。
もう一瞬で数式を複製できますよ。

この回答へのお礼

ありがとうございます。
名前ボックスというのを初めて知りました。
まだ使いこなせていませんが調べてみます。

お礼日時：2021/08/07 08:12

No.4 回答者： t_fumiaki 回答日時： 2021/08/03 13:16

どういう操作をしてるのかが、イマイチ解りませんが。

>>「0」と表記されたものをフィルタで抽出しています。

フィルタで抽出した結果では隙間無く繋がっていたとしても、フィルタを外した時に空白があると、フィルタで繋がって見えていても、大元の空白行までで止まります。

この回答へのお礼

空白は途中にありませんでした。
でもアドバイスありがとうございます

お礼日時：2021/08/08 11:53

No.3 回答者： 銀鱗 回答日時： 2021/08/03 12:47

ごめんなさい。

一連の操作が良く分からないんですけど…。

E列全体を選択して関数を入力すると、E1セルに関数を使った数式を入れるわけですよね。
申し訳ないのですが、それってE1セルにしか入力されないのでコピペなんてされないよ。

どうやったらコピペされるの？
操作手順が曖昧なので原因を特定することすらできません。
正確な手順を示してください。

あと、どのような数式なのか確認する必要があります。
対応する行のセルにデータが無いとか、最大行を指定しているとかで、
数式が正常に動作しているのかもしれませんからね。
（ずっと空白が続くというセルに、コピペされたはずの数式が存在するのかを確認したいなあ）


・・・とりあえずアドバイス・・・

E列全体を選択して、E1セルに入力した数式を最後の行まで複製するのは、
　「フィル」
です。
キーボードショートカットは
　「Ctrl+D」
になります。

業務に支障が出ているのであれば、まずは社内で相談することを強く薦めます。
ホント、冗談抜きでそれが正解です。
尋ねた人にも分からないのであれば、さらに詳しい人に聞くという事になるでしょう。

この回答へのお礼

ええと、一番上の「E」をある枠の部分をクリックするとE列が全部黒くなります。その状態で関数の「fx」をクリックして関数(今回はcountif)を入力すると(左側の列にデータのある)最後の行まで同じ関数が入力されます。
今回やりたいのは、同じ形の表(行数と記載されているデータに差異あり)のある2シートでcountifを使い、差分を抽出することです(「0」と表記されたものをフィルタで抽出しています)。
社内のITヘルプデスクに問い合わせましたが、Officeはサポートしてないの一言で対応してもらえませんでした。

お礼日時：2021/08/03 12:57

No.2 回答者： chonami 回答日時： 2021/08/03 11:44

>Excelで1つの列全体を選択(例えばE列)し関数を入力すると全部のセルに同じ内容でコピペができますが

具体的にどのような手順でやってますか？
お使いのExcelのバージョンは？
一度何かのデータを入れていてそこを上書きのようなことはしてませんか？

この回答へのお礼

欄外の「E」をクリックしてE列(元は空白)全体を選択、countif関数を「fx」から選択しています。2シートにフォーマットは同じ、行数とデータ内容の異なる表が1つずつあるので、countif関数で「0」となった行をフィルタで抽出しています。それぞれD列は表の一番下までデータが入っています。F列は空白です。バージョンはMicrosoft 365 MSO (16.0.13801.20442)32ビットです。

お礼日時：2021/08/03 13:09

No.1 回答者： t_fumiaki 回答日時： 2021/08/03 11:36

その列の左右が空白だとそこで止まります。

左右のどちらかに何かが入って無いと駄目です。
Excelの仕様で、オートフィルタも同じです。

その列の右列にダミーデータを入れておくしか有りません。

この回答へのお礼

返信ありがとうございます。
ただ、右列は全て空欄ですが、左列はデータが入っています。
(たとえて言うと、D列は14000行目までデータが入っているのに、E列の関数は13880で止まってしまい、13880-14000が空欄になってしまう感じです)

お礼日時：2021/08/03 11:42