エクセルの配列関数の制限について

質問

エクセルの配列関数の制限について

Ｖ列にＸ列・Ｙ列・Ｚ列・ＡＡ列・ＡＢ列・ＡＣ列・ＡＥ列・ＡＧ列・Ｐ列を参照した配列関数を
下のように入力しています。

{=SUM((Y$1:Y$500=F7)*(Z$1:Z$500=H7)*(AA$1:AA$500)*(P7="○"),(AB$1:AB$500=F7)
*(AC$1:AC$500=H7)*(AE$1:AE$500=H7)*(AG$1:AG$500)*(P7="×"))}

このＹ列・Ｚ列・ＡＡ列・ＡＢ列・ＡＣ列・ＡＥ列・ＡＧ列は、ぞれぞれ200行くらいしか
文字が入力されていない場合には、配列関数の結果がうまく表示されました。

これらのセルの200行以降から300行・400行と項目を増やしていったところ、
配列関数の結果がうまく表示されないセルが出てきました。

これは、配列関数の参照するセルに制限があるということでしょうか？

回答No.6

MackyNo1 · ベストアンサー

>SUMPRODUCT関数でも同様に0が表示されました。

F9キーで再計算しても「０」と表示されるなら、集計対象のセルがないということです。

＞配列数式の結果で0が表示された件について、Ｙ列・Ｚ列の文字に半角と全角の括弧
「()」と「（）」が混在していたことが原因と判明しました！

この意味が不明ですが（仮に混在しているとしても「０」と表示されることはない）、今回の「０」が表示される原因は解明できたということでしょうか？

＞再計算されるまでに10分～15分かかり、メールやインターネットや他のエクセルなどが
使用できずに固まってしまいます。そのため、手動で再計算されるようにして何とか
使用しています。

再計算に1分以上かかることはほとんど考えられないのですが、いずれにしろ今回のようなデータ範囲の広い配列数式を多数入力するのは全く実用に堪えないと思いますので、数式部分を適宜「値」化して（古いデータを修正する場合は必要に応じて計算式を少しずつオートフィルする）、必要最小限の数式だけを残して作業することをお勧めします（VBAを使用しても計算に時間がかかる可能性が高いと思います）。

補足

教えていただいた数式を入力した結果「#VALUE!」が表示されました。

２つのSUMPRODUCT関数を足した数式であり、１つ目のSUMPRODUCT関数だけを
入力した結果、正しく表示されないというセルに「0」が表示されました。

○と×の条件に応じた数値を抽出したいと思っていたのですが、×の要素は他の
セルに何とか表示できていたので、○の要素だけで考えてみました。

１つ目のSUMPRODUCT関数の計算結果に「0」が表示されるというのは、
MackyNo1さんがおっしゃる通り「集計対象のセルがない」ということでした。

具体的には括弧の半角と全角との違いでした。
Ｆ列・Ｙ列に「長野」と同じ文字列があり、
Ｈ列に「東北(宮城)」(括弧が半角)とあり、
Ｚ列に「東北（宮城）」(括弧が全角)とあった場合に
Ｖ列にＡＡ列の値が表示されないといった内容でした。

回答No.7

MackyNo1

>教えていただいた数式を入力した結果「#VALUE!」が表示されました

このエラーが出る原因は数式の問題ではなく、元データのAA列またはAG列に文字列または#VALUEエラーが含まれているときの状態です。

たとえば、それらのデータ範囲に数字以外のデータや「""」で表示されるような空白文字列などが入っていないでしょうか？

補足

ＡＡ列とＡＧ列に空白セルはありますが、数式を組んでその結果が「数字以外のデータや「""」で
表示されるような空白文字列」にはなっていません。

回答No.5

某HN クロメート(Chromate)（@CoalTar）

>これは、配列関数の参照するセルに制限があるということでしょうか？
配列関数とは配列を扱う関数と解釈しており、質問は配列数式のことでしょうね。
で、ご質問の数式は問題ないと思われますが、この数式を多数コピーして使っているのでしょうか？

多少の改善案
=IF(AND(P7<>"○",P7<>"×"),"",
IF(P7="○",SUMPRODUCT((Y$1:Y$500=F7)*(Z$1:Z$500=H7),AA$1:AA$500),
SUMPRODUCT((AB$1:AB$500=F7)*(AC$1:AC$500=H7)*(AE$1:AE$500=H7),AG$1:AG$500)))

あとは、回答者方の質問の返答待ちということで。

回答No.4

MackyNo1

>「うまく表示されない」というのは、0が表示されるということです。

まず確認ですが、循環参照の数式ということはないでしょうか？

また、すべてのセルに提示された配列数式を入力されているのでしたら、かなり計算負荷が高い式になっていますが、データを更新後に再計算されるまでの時間はどの程度かかりますか？

一般的に、このような配列数式を多用すると、エクセルがフリーズしたり、シートの動きが重くなるなどの観点から、お勧めできませんので、必要に応じて数式を値化して最小限の数式で対応するようにした方が良いと思います。

ちなみに数式を確認するため０と表示されているセルに以下のような式を入力するとどのような値になるでしょうか？（H7、F7は適宜変更）

=SUMPRODUCT((Y$1:Y$500=F7)*(Z$1:Z$500=H7)*(AA$1:AA$500)*(P7="○"))+SUMPRODUCT((AB$1:AB$500=F7)*(AC$1:AC$500=H7)*(AE$1:AE$500=H7)*(AG$1:AG$500)*(P7="×"))

もし「0」になるのであればH7、F7、P7の値に間違いがないか調べてみてください。

補足

SUMPRODUCT関数でも同様に0が表示されました。

配列数式の結果で0が表示された件について、Ｙ列・Ｚ列の文字に半角と全角の括弧
「()」と「（）」が混在していたことが原因と判明しました！

再計算されるまでに10分～15分かかり、メールやインターネットや他のエクセルなどが
使用できずに固まってしまいます。そのため、手動で再計算されるようにして何とか
使用しています。

回答No.3

Wendy02

>配列関数の結果がうまく表示されないセルが出てきました。

Excelのバージョンも問題がありますが、Excel 2003までは、基本的に、SUM関数の場合は、セルの延セル数が、約5,400個の制限があります。Excel 2002以上は、名目的な制限はないものの、事実上の限界という形で、この制限が現れます。Excel2002以上でも、この制限数は頭に入れておいて配列数式を作らなくてはなりません。

それと、もう一つの問題は、一般数式は、空のセルは無視しますから、仮に、A:Aにしても、最後の行のあるセルまでの範囲しか認識しない機能がありますが、配列数式は、空のセルは、0と認識されます。そうすると、セルのコマ数が増えてしまいます。

>このＹ列・Ｚ列・ＡＡ列・ＡＢ列・ＡＣ列・ＡＥ列・ＡＧ列は、ぞれぞれ200行くらいしか
>文字が入力されていない場合には、配列関数の結果がうまく表示されました。

数式のメモリ制限が起きているかもしれません。セルひとつに対して、8 Byte 使用していますが、○や×ひとつで、2 Byte で、合計10 Byte になってしまいます。合計、10 × 5,400 = 54,000 Byte ということになります。

SUMPRODUCT 関数と、配列の確定の違いは、SUMPRODUCT 関数は、数式内の引数の数式を最初に処理するという仕組みがありますから、数式を分散すれば、多少は軽くなることもあります。二つの数式があるなら、二つ別々に計算させる方法のほうがより負担が低いです。

しかし、本格的に数式を作るなら、なるべく配列数式を使わないか、データベース関数で作る方が安定しているのではないかと思います。FREQUENCTY関数やMODE関数など配列数式が免れないような数式は、マクロで処理しなくてはならないこともあります。

お礼

小規模の集計に配列数式やＳＵＭＰＲＯＤＵＣＴ関数を使用して、
大規模の集計にはデータベース関数やマクロが必要なようですね。

すこしずつ覚えていきます。

回答No.2

sige1701

原因については、MackyNo1さんが回答しているとおりだと思います

=SUMPRODUCT((Y$1:Y$500=F7)*(Z$1:Z$500=H7)*(P7="○"),AA$1:AA$500)+SUMPRODUCT((AB$1:AB$500=F7)
*(AC$1:AC$500=H7)*(AG$1:AG$500)*(P7="×"),AG$1:AG$500)

で解消できると思います

回答No.1

MackyNo1

＞配列関数の結果がうまく表示されないセルが出てきました。

数式自体に問題があるわけではありません。

具体的にどのようにうまく表示されないのでしょうか？

提示した数式が#VALUEエラーになるのであれば、AA列またはAG列に数字以外の文字列データ（左寄せで表示される）が入っていることが考えられます。

補足

「うまく表示されない」というのは、0が表示されるということです。