エクセル関数で、バリューエラーがでます A | B | C | D | E | F --------

エクセル関数で、バリューエラーがでます

A | B | C | D | E | F
-----------------------------------------------------
1 |通番 |日付 | 氏名 | 商品 | サイズ| 数量
-----------------------------------------------------
2 | 1 |2020年4月3日|豊臣秀吉|りんご | L | 2
-----------------------------------------------------
3 | 2 |2020年4月3日|豊臣秀吉|いちご | M | 1
-----------------------------------------------------
4 | 3 |2020年4月3日|豊臣秀吉|メロン | S | 2
-----------------------------------------------------
5 | 4 |2020年4月3日|豊臣秀吉|すいか | M | 1
-----------------------------------------------------
6 | 5 |2020年5月22日|徳川家康|メロン| M | 1
-----------------------------------------------------
7 | 6 |2020年5月22日|徳川家康|いちご| L | 2
-----------------------------------------------------
8 | 7 |2020年5月22日|徳川家康|ブドウ| S | 1

sheet4に上記のような一覧表があって、別シートに

Ａ　　 | 　 B　 |　 C | D | E | F |
-----------------------------------------------------------------------
1| 氏名 |りんご | いちご| メロン| すいか| 　　ブドウ|
-----------------------------------------------------------------------
2| |SS|S|M|L|LL|SS|S|M|L|LL|SS|S|M|L|LL|SS|S|M|L|LL|SS|S|M|L|LL|
-----------------------------------------------------------------------
3|豊臣秀吉| | | | |2 | | | | | | |1| | | | | |2| | | | | | | |

という表示をしたいです。
下記の数式を入れたのですが、ＶＡＬＵＥエラーが出ます。
正しい数式をご教示願います。

{=SUMIFS(INDIRECT("Sheet4!F"&MIN(IF((Sheet4!$C$2:$C$100)=$A$3,ROW(Sheet4!$C$2:$C$100)))):INDIRECT("Sheet4!F"&MAX(IF((Sheet4!$C$2:$C$
100)=$A$3,ROW(Sheet4!$C$2:$C$100)))),Sheet4!$D$1:$D$100,$B$1,Sheet4!$E$1:$E$100,B$2)}

わかりにくかったらすみません。
宜しくお願いします。

No.1 ベストアンサー 回答者： fujillin 回答日時： 2020/06/12 18:54

こんにちは

なさりたいことがよくわかりませんが、別シートにあるタイトルに応じた集計をしたいのかと推測しました。
別シートは上２行がタイトル行で、Ａ列が氏名、５列ずつ使って「りんご」、「いちご」・・・となっていて、（Ｆ列ではなく）Z列まであるものと仮定しました。

ご提示のサンプルだと、
　りんごＬＬ＝２、メロンＳ＝１、すいかＭ＝２
の結果となっていますが、これだと何をしたいのかまったくわからないので、勝手に
　りんごＬ＝２、いちごＭ＝１、メロンＳ＝２、すいかＭ＝１
となればいいものと解釈しました。

上記の仮定で、素直にSUMIF関数で式を組んでみれば、別シートのＢ３セルに

=SUMIFS(Sheet4!$F$2:$F$100,Sheet4!$C$2:$C$100,$A3,Sheet4!$D$2:$D$100,INDEX($1:$1,INT(COLUMN(E1)/5)*5-3),Sheet4!$E$2:$E$100,B$2)

でいけるのではと思いますけれど？
（セルの位置関係がわかり難いので、あくまでも、私の解釈が正しければですが・・・）
※ Sheet4の１行目はタイトル行と仮定しています。
　（ご提示の式では1行目も範囲に含まれているようですけれど…）
※ ０を非表示にする処理は数式内では行っていません。

ご提示の式は配列数式のようですが、なにをどうしたいのかあまり読み取れません。
（エラーになるとのことですし）
問題点はいろいろありそうなのでなんとも言えませんが、配列数式にする必要がない（と思われる）ことと、INDIRECTの計算部分でMAX、MINによって合計範囲を変えてしまい、他の範囲指定と矛盾してしまうのが一番大きな原因かと推測します。

この回答へのお礼

お礼が遅くなって申し訳ありません。質問はご理解頂いた通りでして、ご教示な通りやってみたらできました。ありがとうございます。
出来たのですか、、、
IＮDEX（$1:$1〜5-3）のくだりがどうしても理解できず、もしよろしかったら、後学の為に教えていただけませんでしょうか？

お礼日時：2020/06/16 15:10

No.4 回答者： mike_g 回答日時： 2020/06/17 07:50

[No.2お礼]へのコメント、

》 例を作る時に忘れただけで特に意味はない
そんなにシャーシャーと！(苦笑)

添附圖參照
数式の簡單化の爲に、範圍 Sheet5!C1:AA1 の空白に見える全セルには夫々左端と同じ文字列(林檎、苺、…)を入力し、且つ、[条件付き書式]で白フォント色に設定して居ます。然うして…
Sheet5 に於いて、次式を入力したセル C3 を右方にズズーッと(AA列迄)オートフィル
=MAX((氏名=$B3)*(日付=$B2)*(商品=C$1)*(ｻｲｽﾞ=C$2)*数)
【御斷はり】上式は必ず配列數式として入力の事

この回答へのお礼

ご回答ありがとうございます。悪戦苦闘していますのでしばらくお待ちください。すみません

お礼日時：2020/06/19 10:34

No.3 回答者： fujillin 回答日時： 2020/06/16 17:23

No1です

＞IＮDEX（$1:$1〜5-3）のくだりがどうしても理解できず、もしよろしかったら、
＞後学の為に教えていただけませんでしょうか？
Ｄ列（商品）に対する条件部分を表す式です。

ご提示の表（結果の方の表）の1行目は5種類のセル範囲に対して共通（結合されているのかどうかは不明）のタイトルになっていることによるものです。
例えば、Ｂ～Ｆ列ではサイズの分類ではそれぞれの列（2行目）を参照すれば良いですが、商品種別を参照する際はどの列からもＢ列を参照しなければなりません。

その調整を行った計算なので、試しにどこかのセルに
　＝INDEX($1:$1,INT(COLUMN(E1)/5)*5-3)
のような式を入力して、右方にフィルコピーしてみれば、どうなっているかがわかると思います。（少なくとも何を意味しているかは分かることでしょう）

この回答へのお礼

お礼遅くなってすみません。良くわかりました。ありがとうございました。

お礼日時：2020/06/19 10:33

No.2 回答者： mike_g 回答日時： 2020/06/12 21:20

素朴な疑問だけど、「Sheet4」には在る「日付」が「別シート」に無い理由は何ですか？

この回答へのお礼

ありがとうございます。例を作る時に忘れただけで特に意味はないです。

お礼日時：2020/06/16 15:10