SUMPRODUCT 複数条件の重複データ件数

SUMPRODUCT関数を使って複数条件データの重複件数を表示したいのですが、思った結果を出せません。式は下記の通りで、特定日付の範囲内のコードの重複しない件数を求めたいのです。

SUMPRODUCT((範囲1=>日付1)*(範2=<日付2),SUMPRODUCT(1/COUNTIF(集計範囲,集計範囲))

集計範囲は数字です。
SUMPRODUCT((範囲1=>日付1)*(範2=<日付2),(集計範囲,集計範囲))にすると合計計算できます。

ご教授の程お願いいたします。

No.11 回答者： nouble1 回答日時： 2021/03/01 15:24

一般論として、

配列数式で　SAMPRODUCT構文を、
集計目的で　用いる、
際の　基本構造は、
=SAMPRODUCT((条件1)*(条件2)[*(条件3)…]*(集計値1[+集計値2…]))です。


尚、

何故　各々の、
条件を　掛け算するか、
と　いえば。


其れにより、

論理演算の　Andが、
四則演算で　なされるからで。


もし、

Andではなく　Orを、
なしたい　場合は、
足し算を　すると、
いいでしょう。


又、

第2引数を　用いて、
マイクロソフトを　信じすぎて、
苦悩せずとも。


古くから、

既に　確認された、
著作権的にも　フリーな、
やり方、

第1引数内で　四則演算を、
用いて　計算すれば、
いいだけでしょう。


元より、

マイクロソフトの、
公式文章と、構文動作は、
必ずしも　一致しませんから、

公開文章を　信じ過ぎて、
苦悩するのは、

一寸　徒労かも、
知れませんよ？


まぁ、

其の　内に、
含まれる　仕様と、
いう　名の、
bugを、
解き明かして　ハンドリングしきり、

利用が　叶うように、
先鋭域に　躍り出る、
事も、

又　いいのですがね。

No.10 回答者： goomania 回答日時： 2021/02/28 09:38

No.9です。

d-q-t-pさんから頂いたご指摘を踏まえてNo.6の数式を修正してみました。

ご質問の前提が
>SUMPRODUCT関数を使って複数条件データの重複件数を表示したい
ということなので、SUMPRODUCT関数にこだわっています。

古いバージョンではCtrl + Shift + Enter確定が必要(※)です。
(※当然ですがExcelが前提です。googleスプレッドシートであれば不要です。)

=SUMPRODUCT(IFERROR(($B$2:$B$31>=$F$2)*($A$2:$A$31<=$F$3)*(LEN($C$2:$C$31)/
(LEN(TEXTJOIN("",1,IF(($B$2:$B$31>=$F$2)*($A$2:$A$31<=$F$3),$C$2:$C$31,"")))-LEN(SUBSTITUTE(TEXTJOIN("",1,IF(($B$2:$B$31>=$F$2)*($A$2:$A$31<=$F$3),$C$2:$C$31,"")),$C$2:$C$31,"")))),""))

No.6の添付画像のデータがあったとして、F4セルに記述する数式は上記のものでいかがでしょうか。

No.9 回答者： goomania 回答日時： 2021/02/27 12:16

No.6です。

大変失礼しました。
No.6の回答は無かったこととしてください。

d-q-t-pさん、ご指摘ありがとうございました。
fujillinさんが、ご質問者に指摘された、

>計算対象が　全部のデータになっているので、計算はできますがお求めの結
>果にはならないということかと思います。
>多分、日にちで抽出したデータの中から種類を数えたいのではないかと想像
>しました。

と同じ間違いをおかしていたため、正しい結果にならないようです。

No.8 回答者： d-q-t-p 回答日時： 2021/02/27 06:49

補足もなさそうだし 閉じられてもいないので一応。

=COUNT(1/(MATCH(IF((B2:B31>=F2)*(A2:A31<=F3),C2:C31,""),IF((B2:B31>=F2)*(A2:A31<=F3),C2:C31,"♪"),0)=ROW(2:31)-1))
Ctrl + Shift + Enterで確定

No.7 回答者： d-q-t-p 回答日時： 2021/02/27 06:34

goomaniaさんの式は IFが入っているので 古いバージョンでは

Ctrl + Shift + Enter確定が必要です。なので「INDEX(配列,0)」を
入れる必要はないし SUMPRODUCTにする意味もないですね。 SUM
でも同じ結果になるでしょう。

TEXTJOINが入っているから Excel2016以降でないと使えないのは
いいとしても 第一引数が「""」なら CONCATでいいじゃないかとな
ります。

それは置いといて計算結果が少し変な気がします。その表だと
「B2:B31>=F2」なら 10・13・31行目は対象外なので結果は「10」
にならないといけないのでは？

No.6 回答者： goomania 回答日時： 2021/02/26 22:35

>本件は、クローズいたします。

まだ、クローズされていないようなので、一応他の手段をお示しします。

ご質問者は、
>特定日付の範囲内のコードの重複しない件数を求めたいのです。
とおっしゃっているので、集計しなければいけないのは「コード」の数であって、コードはそのものは数値ではないと想像できます。

SUMPRODUCT関数を使うのであれば、添付画像のような表があったとして、以下のような数式で結果を表示できます。

=SUMPRODUCT(IFERROR(LEN(C2:C31)/ (LEN(TEXTJOIN("",1,INDEX(IF(($B$2:$B$31>=$F$2)*($A$2:$A$31<=$F$3),$C$2:$C$31,""),0)))-LEN(SUBSTITUTE(TEXTJOIN("",1,INDEX(IF(($B$2:$B$31>=$F$2)*($A$2:$A$31<=$F$3),$C$2:$C$31,""),0)),$C$2:$C$31,""))),""))

No.4 回答者： d-q-t-p 回答日時： 2021/02/26 09:20

この質問に回答が付かない一番の理由は fujillinさんが書いている

> 複数の意味に解釈できるので、何をなさりたいのかはっきりしません。
に集約されるんですが そこに補足が付きませんね。
Excelのバージョンも不明なままですし。

ひとまず fujillinさんの解釈が合っていると仮定するなら
=COUNT(1/FREQUENCY(IF((A$1:A$10>=E2)*(A$1:A$10<=F2),B$1:B$10),IF((A$1:A$10>=E2)*(A$1:A$10<=F2),B$1:B$10)))
Ctrl + Shift + Enterで確定

B列が数値ならこれでできます。

この回答へのお礼

皆様、ご教授ありがとうございました。
私のレベルで理解できない関数でしたので別の方法で対応いたします。
回答いただきました方々には申し訳ございませんでした。

本件は、クローズいたします。

お礼日時：2021/02/26 21:06

No.3 回答者： fujillin 回答日時： 2021/02/25 22:05

No2です。

連投失礼。
式に無駄がありました。大して変わりませんが、一応、修正しておきます。

=SUM((MMULT((INDEX((A$1:A$10>=E2)*(A$1:A$10<=F2)*(B$1:B$10),)=TRANSPOSE(INDEX((A$1:A$10>=E2)*(A$1:A$10<=F2)*(B$1:B$10),)))*(ROW(A$1:A$10)>=COLUMN($A$1:$J$1)),1*(INDEX((A$1:A$10>=E2)*(A$1:A$10<=F2)*(B$1:B$10),)>0))=1)*1)

この回答へのお礼

皆様、ご教授ありがとうございました。
私のレベルで理解できない関数でしたので別の方法で対応いたします。
回答いただきました方々には申し訳ございませんでした。

本件は、クローズいたします。

お礼日時：2021/02/26 21:07

No.2 回答者： fujillin 回答日時： 2021/02/25 21:32

No1です

＞～～の場合は「#VALUE」になります。
スピル機能がないバージョンですね。
その場合は面倒になりますが、
=SUMPRODUCT((範囲1=>日付1)*(範2=<日付2),INDEX(IF(集計範囲="",0,1/COUNTIF(集計範囲,集計範囲)),))
にして、配列数式として入力すれば算出されます。
と言っても、空白セルがある場合の対応法なので、そのまま1/COUNTIF(～～）の場合の結果と同じ値になるだけですけれど。

＞～～にすると「5.641･･･」の結果でした。
1/COUNTIF(~~)の計算対象が　全部のデータになっているので、計算はできますがお求めの結果にはならないということかと思います。
多分、日にちで抽出したデータの中から種類を数えたいのではないかと想像しました。

UNIQUE関数が使える環境なら式はかなり簡単になりますが、どうやらスピル機能もダメらしいので、直接求めようとすると相当に面倒なことになります。
作業列（行）などを利用して順次結果を求めてゆく方法を取るほうが、わかり易く計算量も少なくできます。
無理矢理に直接求めるにしても、配列計算の長い式になってしまいますし、単純に「集計範囲」のような抽象的な表現だけでは表せないので、簡単なミニチュア版で以下にサンプルを作成してみました。


なさりたいこととあっているのかどうかは不明ですが・・・

添付図は、A列が日付で、B列が集計対象のデータと仮定しています。
E列の開始日とF列の終了日の間の日付のデータを抽出し、その中でB列の値の種類数をG列に求めています。
G2セルには、以下の式を入れ、配列数式として確定してあります。（Ctrl + Shift + Enterで確定）
=SUM((MMULT((INDEX((A$1:A$10>=E2)*(A$1:A$10<=F2)*(B$1:B$10),)=TRANSPOSE(INDEX((A$1:A$10>=E2)*(A$1:A$10<=F2)*(B$1:B$10),)))*(ROW(A$1:A$10)>=COLUMN($A$1:$J$1)),(ROW(A$1:A$10)/ROW(A$1:A$10))*(INDEX((A$1:A$10>=E2)*(A$1:A$10<=F2)*(B$1:B$10),)>0))=1)*1)

※ 配列乗算になっているので、範囲を大きくしすぎると計算負荷が増大しますのでご注意。

この回答へのお礼

皆様、ご教授ありがとうございました。
私のレベルで理解できない関数でしたので別の方法で対応いたします。
回答いただきました方々には申し訳ございませんでした。

本件は、クローズいたします。

お礼日時：2021/02/26 21:07

No.1 回答者： fujillin 回答日時： 2021/02/25 11:48

こんにちは

回答がないようなので・・・

＞特定日付の範囲内のコードの重複しない件数を求めたいのです。
複数の意味に解釈できるので、何をなさりたいのかはっきりしません。
ひとまず、提示されている式から判断するものとして・・・

＞SUMPRODUCT((範囲1=>日付1)*(範2=<日付2),(集計範囲,集計範囲))
＞にすると合計計算できます。
エラーになると思いますけれど？？？
　=SUMPRODUCT((範囲1=>日付1)*(範2=<日付2),集計範囲)
であれば、合計値は求められそうに思われます。

＞SUMPRODUCT((範囲1=>日付1)*(範2=<日付2),SUMPRODUCT(1/COUNTIF(集計範囲,集計範囲))
式の意味も不明な上になさりたいことも不明なので、結果が表示できるようにするなら、
=SUMPRODUCT((範囲1=>日付1)*(範2=<日付2),1/COUNTIF(集計範囲,集計範囲))
にすれば「集計範囲」に空欄がなければ結果が表示されるでしょう。
（空欄を含む場合は、#DIV/0! になります。）

エラー回避のために、上式で空欄を外したければ、
=SUMPRODUCT((範囲1=>日付1)*(範2=<日付2),IF(集計範囲="",0,1/COUNTIF(集計範囲,集計範囲)))
としておくとか・・・

なんとなくの雰囲気では、求めたい内容とは違いそうな気がしますけれど、動作しない式しか与えられていないため、エラーが出ないようにする程度しかできませんので、悪しからず。

この回答へのお礼

ありがとうございます。

＞SUMPRODUCT((範囲1=>日付1)*(範2=<日付2),(集計範囲,集計範囲))
＞にすると合計計算できます。
上記は、コピーミスでした。
　　SUMPRODUCT((範囲1=>日付1)*(範2=<日付2),(集計範囲)で合計計算はできてます。

集計したいことは、毎日のデータリストから、一定期間（例：1/21～１／３１）の商品の種類を求めたいのです。実際のデータには集計期間のリストは５８件有り種類（求める数）は５５種類ですが、
　　=SUMPRODUCT((範囲1=>日付1)*(範2=<日付2),1/COUNTIF(集計範囲,集計範囲))
にすると「5.641･･･」の結果でした。

また、=SUMPRODUCT((範囲1=>日付1)*(範2=<日付2),IF(集計範囲="",0,1/COUNTIF(集計範囲,集計範囲)))　の場合は「#VALUE」になります。

集計期間ゃ集計範囲には空白が無い状態で（他システムからインポートして数値書式に変更）してあります。

お礼日時：2021/02/25 17:10