
スプレッドシートとエクセルに詳しい方教えて下さい!
添付画像の「名簿」シートに記載された表のうち、別シート(「まとめ」シート)にて、特定の条件を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セル、B2セルを変更することで地域・性別の条件で何名いるか把握するようなリストを作成しています。
こちらのエクセルデータをGoogleのスプレッドシートとしてアップロードしたところ、数式がエラーを起こしてしまい、正常に表示されなくなってしまいました。
エラー表示を見ると
「SUMPRODUCT の範囲サイズが一致しません。行数を 197、列数を 1 にしてください。実際の行数は 1、列数は 1 です。」
という表示がされているのですが、どこを直せばよいのか分からず、手詰まりになってしまっています。
エクセルで運用すれば済む話なのですが、スプレッドシートとしてアップロードすることで他のメンバーにも共有したいと思っているため、上手いことエラーを解消出来ればと考えております。
エクセルの数式が悪いのか、スプレッドシートの仕様なのかがよくわかっていないため、解決策をご教示頂けますと幸いです。
どうぞ宜しくお願いいたします。

No.5ベストアンサー
- 回答日時:
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)・・・・・・①
ご回答ありがとうございます。
なるほどですね。
ご回答の趣旨を理解しておらず大変失礼しました。
今しがた、試してみたところ配列数式を使用せずに意図した挙動を示すことが出来ました。
こちらの方がスマートだと思いますので、是非こちらの方法を採用させて頂きます。
No.4
- 回答日時:
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で確定させる)とする必要があります。
ご回答ありがとうございます。
また、ご提案ありがとうございます。
今回解決した方法といたしましては、①の方法を使っております。
そして、ご記載の通り配列数式として算式に組み込んでおります。
配列数式自体が私の周りでは馴染みが無い(そこまで使いこなせる者が少ない)ので、敬遠していたのですが、②の方法を採ることが出来ず①にしたという次第です。
配列数式に関しては、少し不格好になってしまいますが、表の空きスペースにでも説明書きを添えて注意を促すこととしました。
No.3
- 回答日時:
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)))
でも同様の結果になります。

ご回答ありがとうございます。
スプレッドシート上での挙動に関しては先の回答でも申し上げた通り別関数を組んだことにより解消された(意図した挙動を示した)ので、これ以上テコ入れは不要かと思いますが、選択肢が増えることは悪いことではないなので貴重なご意見として参考にさせていただきます。
No.2
- 回答日時:
No1です
どうやら、意味が通じていないみたいなので・・・
『スプレッドシートでFilter関数を使えばよいのでは』
ってことです。
どうしても、エクセルと互換にしたければ、普通に、作業列にヒット順を表示しておいて、それを順に検索する抽出方法を用いれば可能と想像します。
ご回答ありがとうございます。
はい、Filter関数を使用する方法も検討したのですが、こちらはMicrosoft365以外では使えないため、選択肢からは除外しています。使用を想定しているユーザー全員が365を使える環境ではないことと、全てのユーザーがスプレッドシート上で作業をするのではないためです。(あくまでデータの共有の為スプレッドシートを活用しているに過ぎません)
なので、別の方法を模索していたのですが、こちらのサイトを参考にしたところエラー表記は解消され、意図した挙動を示しました。
https://nyanto.jimdofree.com/
繰り返しで恐縮ですが、今回の質問の意図としては
「スプレッドシート上でも同じ挙動を示すと思っていたものがスプレッドシート上ではエラーになってしまっていたので、その原因を探りたいと思い質問させて頂いた次第です。」
という点なので、エラー表記が解消され、正常に動作すること自体はもちろん大切なのですが、
「SUMPRODUCT の範囲サイズが一致しません。行数を 197、列数を 1 にしてください。実際の行数は 1、列数は 1 です。」
という部分を解消する方法(何が原因でそうなっているのかを突き止めて、それの解決策)を知りたかった次第です。
どうぞ宜しくお願いいたします。
No.1
- 回答日時:
こんばんは
スプレッドシートは全く存じませんけれど・・・
>地域・性別の条件で何名いるか把握するようなリストを作成しています。
「何名いるか」ではなく、リストを抽出したいのですよね??
スプレッドシートには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内での説明の便宜上作成した架空のシートとなっており、実際は別の名義で作成しており数式も数か所入っているものとなっています。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
今、見られている記事はコレ!
-
弁護士が解説!あなたの声を行政に届ける「パブリックコメント」制度のすべて
社会に対する意見や不満、疑問。それを発信する場所は、SNSやブログ、そしてニュースサイトのコメント欄など多岐にわたる。教えて!gooでも「ヤフコメ民について」というタイトルのトピックがあり、この投稿の通り、...
-
弁護士が語る「合法と違法を分けるオンラインカジノのシンプルな線引き」
「お金を賭けたら違法です」ーーこう答えたのは富士見坂法律事務所の井上義之弁護士。オンラインカジノが違法となるかどうかの基準は、このように非常にシンプルである。しかし2025年にはいって、違法賭博事件が相次...
-
釣りと密漁の違いは?知らなかったでは済まされない?事前にできることは?
知らなかったでは済まされないのが法律の世界であるが、全てを知ってから何かをするには少々手間がかかるし、最悪始めることすらできずに終わってしまうこともあり得る。教えてgooでも「釣りと密漁の境目はどこです...
-
カスハラとクレームの違いは?カスハラの法的責任は?企業がとるべき対応は?
東京都が、客からの迷惑行為などを称した「カスタマーハラスメント」、いわゆる「カスハラ」の防止を目的とした条例を、全国で初めて成立させた。条例に罰則はなく、2025年4月1日から施行される。 この動きは自治体...
-
なぜ批判コメントをするの?その心理と向き合い方をカウンセラーにきいた!
今や生活に必要不可欠となったインターネット。手軽に情報を得られるだけでなく、ネットを介したコミュニケーションも一般的となった。それと同時に顕在化しているのが、他者に対する辛らつな意見だ。ネットニュース...
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
LibreOffice Clalc(またはエク...
-
【マクロ】excelファイルを開く...
-
【関数】3つのセルの中で最新...
-
【マクロ】数式を入力したい。...
-
Office2021のエクセルで米国株...
-
エクセルのリストについて
-
【マクロ】【相談】Excelブック...
-
【マクロ】元データと同じお客...
-
【画像あり】オートフィルター...
-
エクセルの関数について
-
【マクロ】左のブックと右のブ...
-
他のシートの検索
-
【マクロ】実行時エラー '424':...
-
エクセルのVBAで集計をしたい
-
vba テキストボックスとリフト...
-
【マクロ】【配列】3つのシー...
-
【関数】=EXACT(a1,b1) a1とb1...
-
【マクロ画像あり】❶1つの条件...
-
エクセルの複雑なシフト表から...
-
【マクロ】変数に入れるコード...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【マクロ】元データと同じお客...
-
エクセルの関数について
-
【画像あり】オートフィルター...
-
エクセルのVBAで集計をしたい
-
エクセルのリストについて
-
【マクロ】数式を入力したい。...
-
【マクロ】【相談】Excelブック...
-
Office2021のエクセルで米国株...
-
【マクロ】実行時エラー '424':...
-
他のシートの検索
-
エクセルの複雑なシフト表から...
-
【マクロ】【配列】3つのシー...
-
vba テキストボックスとリフト...
-
【マクロ】左のブックと右のブ...
-
【マクロ】変数に入れるコード...
-
エクセルシートの見出しの文字...
-
【マクロ】別ファイルへマクロ...
-
【関数】同じ関数なのに、エラ...
-
Amazonでマイクロソフトオフィ...
-
ページが変なふうに切れる
おすすめ情報
画像が小さくて見づらいので追加で投稿します。
(名簿シート)
画像が小さくて見づらいので追加で投稿します。
(まとめシート。G4セル)
画像が小さくて見づらいので追加で投稿します。
(Googleスプレッドシート)