[[20210125174006]] 『SUMPRODUCTを使わずに同じ結果を取得したい』(NS) >>BOT

[ 初めての方へ | 一覧(最新更新順) |

| 全文検索 | 過去ログ ]

 

『SUMPRODUCTを使わずに同じ結果を取得したい』(NS)

Excel初心者です。
ApachePOIを使って関数の作成を行っているのですが、グラフ作成の際SUMPRODUCTがエラーを起こしてしまうので以下の関数をSUMPRODUCTを使わずに再現する式をご教授いただければと思います。よろしくお願いいたします<(_ _)>

=IF(SUMPRODUCT(SUBTOTAL(9,INDIRECT("R"&ROW(R5:R7)))*(I5:I7=W17)*(X5:X7="●"))=0,"",(SUMPRODUCT(SUBTOTAL(9,INDIRECT("R"&ROW(R5:R7)))*(I5:I7=W17)*(X5:X7="●")))/1000000)

< 使用 Excel:Excel2016、使用 OS:Windows10 >


 その SUMPRODUCT の式は、フィルターをかけた状態で条件に合うものを計算するんですよね?
 本当にデータは5〜7行のたった3行なんですか?

 一応、作業列を使って・・・例)Y列

 Y5 =IF(R5="","",IF(SUBTOTAL(9,R5),1,""))
 下コピー

 =SUMIFS(R5:R7,I5:I7,W17,X5:X7,"●",Y5:Y7,1)/1000000
 表示形式で「0」を非表示

 または
 =IF(COUNTIFS(I5:I7,W17,X5:X7,"●",Y5:Y7,1),SUMIFS(R5:R7,I5:I7,W17,X5:X7,"●",Y5:Y7,1)/1000000,"")

 これで同じ結果になりませんか?

 以上
(笑) 2021/01/25(月) 20:00

 以前、POIがらみの質問があり、その時もSUMPRODUCT関数がワークしない、と言うものだった。

 SUBTOTALとINDIRECTの組合せは、配列を扱う数式でやるしかないと思っていますが(私はですが)
 SUMPRODUCTが使えないなら、SUMでもやれる。

 何故ならそのSUMPRODUTは足し算しかやってないので。
 (掛け算があるじゃないかと思うかも知れませんが、第一引数自体が勝手にやっていること)

 ただし、SUMを使う場合、INDEX関数をかませないと#VALUE!エラーになってしまう。

 そこで、こうなる。(それで解決するといいですね・・)
      ↓
 = IF(SUM(INDEX(SUBTOTAL(9,INDIRECT("R"&ROW(R5:R7)))*(I5:I7=W17)*(X5:X7="●"),0))=0,"",SUM(INDEX(SUBTOTAL(9,INDIRECT("R"&ROW(R5:R7)))*(I5:I7=W17)*(X5:X7="●"),0))/1000000)

(半平太) 2021/01/25(月) 20:06


笑 様
半平太 様

ご回答ありがとうございました。お二方の方法で同じ結果を表示でき、
かつPOI上でエラーにならないことを確認できました!

質問した当日に回答を頂けてとても助かりました。
本当にありがとうございます。

>データは5〜7行のたった3行

サンプルデータを使って作成した関数をそのまま
持ってきていたので3行のままとなってしまっていました。
(レコード数に応じて行数が変わるようになっています)

極端に少ないせいで混乱を招いていたらお詫び申し上げます。
(NS) 2021/01/26(火) 11:08


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.