名前マネージャで動的印刷範囲を設定して、別のワークシートから値を返さない数式を含む行を無視するようにしました。実際のデータの最後の 8 行をスキップする以外は、ほとんどすべて正常に動作します。私が使用している OFFSET 数式は次のとおりです。
=OFFSET('調達ログ'!$A$1,0,0,COUNT(IF('調達ログ'!$B$8:$B$10003<>0,'調達ログ'!$B$8:$B$10003)),24)
最後の 8 行が失われるのはなぜかわかりません。範囲を 8 行目から開始したことと関係があるのでしょうか? 範囲を B1 から開始するように変更したところ、最後の 7 行がスキップされるようになりました。困惑しています。どなたか助けていただければ幸いです。
[ファイルへのリンクはこちら][1]。仕組みとしては、新しいデータが [データのエクスポート] タブに貼り付けられ、数式を介して他の 2 つのタブに取り込まれます。動的印刷で無視しようとしているのは、数式が入った空白のセルです。
答え1
リンクが提供された後に追加されました:
はい、最初の 7 行は COUNT() されておらず、8 行目 (B8 自体) にはテキストがあるためカウントに加算されないため、8 行が問題です。ただし、前述のように、2 番目の範囲を変更しても修正できません。COUNT() の結果に 8 を加算するだけです。したがって、以下の最後に示す数式のように、7 を加算する代わりに 8 を加算します。これですべての問題が解決します。
(補足: 両方の範囲を B1 (ではなく B8) に変更すると、不足している行が 7 に変更される理由は、B6 の値が数値であり、そのようにカウントされるため、不足している 8 行が不足している 7 行のみになるからです。)
答え2
示唆されているようにharrymc、この問題の答えを確実に得るには、さらに情報が必要であり、スクリーンショットがあれば便利かもしれません。
ただし、B1:whereever の範囲ではなく、範囲 (B8:B10003) をテストしているようです。その領域には動的マテリアルが何も含まれていないためです。実際、その内容によって計算が狂う可能性もあります。ここまでは順調です。
しかし、行の欠落数から、ダイナミック レンジは A8 などではなく A1 から始まると思われるため、テストされていない行にあるものはすべて印刷するつもりであることが示唆されます。欠落している 8 と B1:B7 の 7 は完全に一致していませんが、関連する別の質問では、他の回答者と同じことをしているがうまくいかないと述べており、他のテキストでは、他の回答者とほとんど同じことをしているだけだと示唆しています。したがって、ここでも同じ不正確さが当てはまると仮定して、先に進みます。
動的データは行 8 から始まり、列 B は適切な印刷範囲を作成するための便利で正確なソースであるようです。また、レポートの開始として、おそらくそのポイントより上の 7 行の一部またはすべてを印刷し、ヘッダーとしても印刷することになるでしょう (ただし、おそらくそうはならないでしょう。行数が多いと、1,000 行か 2,000 行が失われるでしょう)。
その範囲内のデータは数値か不要なデータであると想定されるため、 はCOUNT()最適で は必要ありませんCOUNTA()。違いは、後者はテキスト セルもカウントすることです。したがって、最初の潜在的な問題は、おそらく 10,000 行のうち、8 行程度が実際にはテキストであるということです。「方法」は、ここではその原因を解決するためにのみ重要であり (明らかに予想外であるため)、それが原因であるかどうかを検討するための前兆ではありません。したがって、その範囲で各関数を使用する 2 つの数式を設定し、結果が同じかどうかを確認します。同じでない場合は、列に赤字のテキスト エントリがいくつかあります。それらはカウントされないため、範囲は終了する必要がある場所からその数だけ不足します。率直に言って、それがいかにありそうにないと考えても、それが間違いなく最も可能性の高い問題の原因です。また、それが問題の一部で、残りが何か他のものである可能性もあります。テストを実行してください。
次に、正確な範囲をテストすることは確かに重要なので、そうすることは称賛に値します。ただし、それは必ずしも実際の印刷領域を生成する範囲とまったく同じではありません。それだけでは不十分です。たとえば、A1 から印刷を開始するとすると、計算された合計の約 7 行が行 1 ～ 7 に使用され、実際のデータには使用されません。言い換えると、カウントが 2,433 であるとします。記述どおり、範囲は A1:X2433 になりますが、行 8 ～ 10003 に 2,433 行あることを意味するため、実際にはその数に加えて 7 行必要になります。したがって、計算で 2,433 個の項目が見つかった場合、実際に必要なのは範囲 A1:X2440 であり、A1:X2433 ではありません。それだけでレポートから 7 行が削減されます。
これを数式にどのように適用しますか? 関数の出力にこれらの 7 行を追加する必要がありますCOUNT()。閉じ括弧の直後に次のようになります。
=OFFSET('Procurement Log'!$A$1,0,0,COUNT(IF('Procurement Log'!$B$8:$B$10003<>0,'Procurement Log'!$B$8:$B$10003)) +7, 24)
（「24」の直前）。
私の予想では、これで不足している行のうち 7 行を取得できるはずです。また、残りの行を取得するためのテキスト エントリが、9,996 個の列 B セルのどこかに存在する可能性もあります。
最初の使用時に参照を B8 から B1 に変更すると、行 1 ～ 7 にコンテンツがある場合は行がカウントに追加されます (テストでの使用では、それらをカウントするだけではカウントに追加されないため)。行が失われるのであって、増えるのではないとおっしゃっているので、これは問題ではありません。2 回目の使用時に変更すると、最初の 7 つは数値ではない可能性が高いため、おそらく (実際のスプレッドシート ページを正確に確認する必要があります) 行が失われます。Excel は要素を要素と照合しますが、アドレスをアドレスと照合しません。つまり、範囲の最初の 7 つの要素が数値以外の場合、それらの FALSE はテスト範囲の数値エントリ (B8 から始まり、おそらくそれ自体が数値エントリ) と照合されます。したがって、対象の範囲の最初の 7 つの要素はカウントから除外され、動的印刷範囲は 7 行足りなくなります。
範囲を一致させたまま、親関数 ( ) の結果に 7 を追加すると、COUNT()比較を台無しにすることなく目的を達成できます。これを実行すると、非常に適切に見えるようになります。関数内の何も変更せずCOUNT()、必要な 7 を後に追加するだけです。
最後に、気づいていない方のために言っておきますが、おそらく、数式が行の連続したブロックで値を返し、ある時点で値が返されなくなり、その後は行の残りの部分で値が返されないという状況だと思います。つまり、行 23、24、2438、2499、2900 にはデータがありません。これら 5 つのデータだけであれば、行 8 ～ 12 にあります。つまり、これを行の「ハンティング アンド ペッキング」に使用しているのではなく、最初の行から最初の空の行までを印刷するブロックとして扱い、それ以降は気にしないということです。このアプローチは、行全体にわたって値が見つかった場合、まったく機能しません。
また、返されたデータの間にある空白行が問題の原因ではないこと、および、FILTER()それらすべてを連続したグループにまとめるための のような数式を使用しても、すでにそうなっているため、何も起こらないことも意味します。(そして、それが完全に間違っていて、間に空白行が点在している場合は、それが問題の原因であり、FILTER()それに対処するときです。)