Googleスプレッドシートのエラーにつきまして

スプレッドシートとエクセルに詳しい方教えて下さい！

添付画像の「名簿」シートに記載された表のうち、別シート（「まとめ」シート）にて、特定の条件をG2セル（具体的にはA2セル＆B2セル）で指定し（画像では「東京且つ男性」）、その条件に合致したい人物をA4～F4に自動反映されるような数式を組んでおります。

◆G2セルの数式
=A2&B2

◆A4～F4セルの数式（いずれも固定）
=IF($G4>0,INDIRECT("名簿!"&ADDRESS($G4,COLUMN())),"")

◆G4以下（G5、G6、G7…）のセルの数式
=SUMPRODUCT((名簿!$G$4:$G$200=$G$2)*1,(COUNTIF(INDIRECT("名簿!$G$4:$G"&ROW(名簿!$G$4:$G$200)),$G$2)=ROW()-ROW($A$3))*1,ROW(名簿!$G$4:$G$200))

として組むことによって、A2セル、B２セルを変更することで地域・性別の条件で何名いるか把握するようなリストを作成しています。

こちらのエクセルデータをGoogleのスプレッドシートとしてアップロードしたところ、数式がエラーを起こしてしまい、正常に表示されなくなってしまいました。
エラー表示を見ると
「SUMPRODUCT の範囲サイズが一致しません。行数を 197、列数を 1 にしてください。実際の行数は 1、列数は 1 です。」

という表示がされているのですが、どこを直せばよいのか分からず、手詰まりになってしまっています。
エクセルで運用すれば済む話なのですが、スプレッドシートとしてアップロードすることで他のメンバーにも共有したいと思っているため、上手いことエラーを解消出来ればと考えております。

エクセルの数式が悪いのか、スプレッドシートの仕様なのかがよくわかっていないため、解決策をご教示頂けますと幸いです。
どうぞ宜しくお願いいたします。

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

• 画像が小さくて見づらいので追加で投稿します。
  （名簿シート）

  
    補足日時：2021/04/26 21:30

• 画像が小さくて見づらいので追加で投稿します。
  （まとめシート。G4セル）

  
    補足日時：2021/04/26 21:33

• 画像が小さくて見づらいので追加で投稿します。
  （Googleスプレッドシート）

  
    補足日時：2021/04/26 21:35

No.5 ベストアンサー 回答者： goomania 回答日時： 2021/04/30 16:30

No.4です。

>今回解決した方法といたしましては、①の方法を使っております。
>そして、ご記載の通り配列数式として算式に組み込んでおります。

確かに②は配列数式の回答ですが、ご質問者のお示しになられた以下のサイト
https://nyanto.jimdofree.com/
では、既に配列数式にする方法が解説されていたので、敢えて配列数式を使用しない方法としして①を投稿しました。

従って、前回回答の以下の数式は上記サイトの回答とは似て非なるものですので配列数式にする必要はなく、「少し不格好になってしまいますが、表の空きスペースにでも説明書きを添えて注意を促すこととしました。」も必要ないはずです。
ご質問者の環境で配列数式でない数式を記述して試してください。

=IFERROR(1/LARGE(INDEX((名簿!$G$4:$G$200=$G$2)/ROW($G$4:$G$200),0),ROW(A1)),0)･･････①

この回答へのお礼

ご回答ありがとうございます。

なるほどですね。
ご回答の趣旨を理解しておらず大変失礼しました。
今しがた、試してみたところ配列数式を使用せずに意図した挙動を示すことが出来ました。
こちらの方がスマートだと思いますので、是非こちらの方法を採用させて頂きます。

お礼日時：2021/04/30 16:42

No.4 回答者： goomania 回答日時： 2021/04/30 14:36

SUMPRODUCT関数の中では配列数式(CSE数式)にしなくても、配列計算が行われるはずです。

ところが、スプレッドシートの仕様かどうかは知りませんが、fujillinさんのご指摘にもあるとおり、スプレッドシートではArrayFormulaを使ってもINDIRECT関数の答えが配列を返さないようです。
つまり、EXCELでは配列を返しているためエラーにならないのですが、スプレッドシートではINDIRECT関数で返されるのは配列ではなく、単一の答えなので「SUMPRODUCT の範囲サイズが一致しません。行数を 197、列数を 1 にしてください。実際の行数は 1、列数は 1 です。」というエラーになってしまうということのようです。
ご質問者のお示しになられた以下のサイト
https://nyanto.jimdofree.com/
のSUMPRODUCT関数の解説にあるとおり、SUMPRODUCT関数では複数配列の範囲サイズを一致させる必要があり、一致しない場合「#VALUE!」が返ってしまいます。
そこで、ご質問者のニーズに沿うためにはEXCELでもスプレッドシートでも使える互換性のある数式を考える必要が生じます。
ご質問者によれば、既に解決済みとのことですが、EXCELもバージョンによって異なるため、2つほど考えてみました。

〇EXCELのバージョンを問わず、スプレッドシートと互換性のある以下の数式が使えます。
◆G4のセルの数式を

=IFERROR(1/LARGE(INDEX((名簿!$G$4:$G$200=$G$2)/ROW($G$4:$G$200),0),ROW(A1)),0)･･････①

に変更し、下方向へコピーします。
AGGREGATE関数を使いたいところですが、スプレッドシートにはAGGREGATE関数がないので、LARGE関数とINDEX関数の組合せで代用します。

〇microsoft365なら

=IFERROR(SMALL(INDEX(IFERROR(ROW($G$4:$G$200)/(名簿!$G$4:$G$200=$G$2),""),0),ROW(A1)),0)･･････②

という数式でもスプレッドシートと互換性があります。

〇microsoft365以外の場合 なら
スプレッドシートでは②の数式がそのまま使用できるのですが、EXCELでは「配列数式」(Ctrl+Shift+Enterで確定させる)とする必要があります。

この回答へのお礼

ご回答ありがとうございます。
また、ご提案ありがとうございます。
今回解決した方法といたしましては、①の方法を使っております。
そして、ご記載の通り配列数式として算式に組み込んでおります。

配列数式自体が私の周りでは馴染みが無い（そこまで使いこなせる者が少ない）ので、敬遠していたのですが、②の方法を採ることが出来ず①にしたという次第です。
配列数式に関しては、少し不格好になってしまいますが、表の空きスペースにでも説明書きを添えて注意を促すこととしました。

お礼日時：2021/04/30 15:08

No.3 回答者： fujillin 回答日時： 2021/04/27 21:39

No2です。

＞あくまでデータの共有の為スプレッドシートを活用しているに過ぎません
それって、利用者がそれぞれのエクセルにコピペするってことでしょうか？
使うのはあくまでもエクセルで、スプレッドシートでは使わないってことなのかな？
それでは、まったく「共用」にはなっていないと思われますけれど・・・

データの受け渡しができればよいだけなら、どこかのサーバを利用するなり、OneDriveで共用するなり方法はいくらでもありそうですけれど。
最悪でもメールに添付とか・・・
仮に、エクセルにコピペして使うことが前提なら、データ部分だけコピペできれば良いのでしょうから、関数の互換性などは必要ないはずと思います。


＞スプレッドシート上ではエラーになってしまっていたので、
＞その原因を探りたいと思い質問させて頂いた次第です。
スプレッドシートは知りませんが、配列計算をさせている上に、そのなかでINDIRECTのような揮発性関数で範囲指定をしているので、スプレッドシート側で範囲を特定できずにエラーとなっているのではないでしょうか？

No2に『互換性を保ちたい場合』についても触れておきましたが、読んでいないのでしょうか？
No2の方法であれば、少なくとも、配列計算は不要ですし、INDIRECT関数なども使用しないで済みます。
（私はスプレッドシート関数を知りませんので、どこまでエクセル関数と同じ書式でいけるのかはわかりませんけれど・・・）

ひとまずのご参考までに、添付図では、G2セルに
　=IF((F2=C$19)*(E2=C$20),COUNT(G$1:G1)+1,"")
を入力してフィルコピー
抽出する範囲には、A22セルに
　=IFERROR(INDEX(A:A,MATCH(ROW(A1),$G:$G,0)),"")
を入力して、フィルコピーしてあります。
もしも、IFERROR関数が使えないような場合は、
　=IF(ROW(A1)>MAX($G:$G),"",INDEX(A:A,MATCH(ROW(A1),$G:$G,0)))
でも同様の結果になります。

この回答へのお礼

ご回答ありがとうございます。

スプレッドシート上での挙動に関しては先の回答でも申し上げた通り別関数を組んだことにより解消された（意図した挙動を示した）ので、これ以上テコ入れは不要かと思いますが、選択肢が増えることは悪いことではないなので貴重なご意見として参考にさせていただきます。

お礼日時：2021/04/27 22:00

No.2 回答者： fujillin 回答日時： 2021/04/27 15:47

No1です

どうやら、意味が通じていないみたいなので・・・
　　『スプレッドシートでFilter関数を使えばよいのでは』
ってことです。


どうしても、エクセルと互換にしたければ、普通に、作業列にヒット順を表示しておいて、それを順に検索する抽出方法を用いれば可能と想像します。

この回答へのお礼

ご回答ありがとうございます。

はい、Filter関数を使用する方法も検討したのですが、こちらはMicrosoft365以外では使えないため、選択肢からは除外しています。使用を想定しているユーザー全員が365を使える環境ではないことと、全てのユーザーがスプレッドシート上で作業をするのではないためです。（あくまでデータの共有の為スプレッドシートを活用しているに過ぎません）

なので、別の方法を模索していたのですが、こちらのサイトを参考にしたところエラー表記は解消され、意図した挙動を示しました。
https://nyanto.jimdofree.com/

繰り返しで恐縮ですが、今回の質問の意図としては
「スプレッドシート上でも同じ挙動を示すと思っていたものがスプレッドシート上ではエラーになってしまっていたので、その原因を探りたいと思い質問させて頂いた次第です。」
という点なので、エラー表記が解消され、正常に動作すること自体はもちろん大切なのですが、
「SUMPRODUCT の範囲サイズが一致しません。行数を 197、列数を 1 にしてください。実際の行数は 1、列数は 1 です。」
という部分を解消する方法（何が原因でそうなっているのかを突き止めて、それの解決策）を知りたかった次第です。


どうぞ宜しくお願いいたします。

お礼日時：2021/04/27 16:11

No.1 回答者： fujillin 回答日時： 2021/04/27 00:00

こんばんは

スプレッドシートは全く存じませんけれど・・・

＞地域・性別の条件で何名いるか把握するようなリストを作成しています。
「何名いるか」ではなく、リストを抽出したいのですよね？？

スプレッドシートにはFILTER関数が存在するようなので、そちらを利用すればもっと簡単な式で求められるのではないでしょうか。
https://support.google.com/docs/answer/3093197
想像するところ、作業列や作業セルも不要になるものと思われます。

エクセルにも同様の関数はありますが、Office365でないと使えなかったと思います。（書式も若干違うようではありますが）
https://support.microsoft.com/ja-jp/office/filte …

まぁ、エクセルの場合は、関数など使わずともフィルタオプションを利用すれば、条件の組み合わせ等も自由にできますけれど・・・
https://www.moug.net/tech/exopr/0040016.html

この回答へのお礼

ご回答ありがとうございます。

はい、リストの抽出自体は名簿シート内でフィルタを設定すれば条件を指定して抽出することが可能なのでfujillin様の仰る方法で事足りると思っています。

今回の質問の目的としては、（仮に）特定条件下で抽出した結果を別シート（まとめシート）に作成したときに、スプレッドシート上でも同じ挙動を示すと思っていたものがスプレッドシート上ではエラーになってしまっていたので、その原因を探りたいと思い質問させて頂いた次第です。

まとめシートに抽出結果を別途作成した理由としては、名簿シート内のデータをフィルタ等で抽出している最中にデータの形式や中の数式を崩されるのを防ぐために、まとめシートに抽出結果だけを反映させるようにした次第です。

※こちらの名簿シートですが、教えて！goo内での説明の便宜上作成した架空のシートとなっており、実際は別の名義で作成しており数式も数か所入っているものとなっています。

お礼日時：2021/04/27 10:16