A 回答 (10件)
- 最新から表示
- 回答順に表示
No.11
- 回答日時:
一般論として、
配列数式で SAMPRODUCT構文を、
集計目的で 用いる、
際の 基本構造は、
=SAMPRODUCT((条件1)*(条件2)[*(条件3)…]*(集計値1[+集計値2…]))です。
尚、
何故 各々の、
条件を 掛け算するか、
と いえば。
其れにより、
論理演算の Andが、
四則演算で なされるからで。
もし、
Andではなく Orを、
なしたい 場合は、
足し算を すると、
いいでしょう。
又、
第2引数を 用いて、
マイクロソフトを 信じすぎて、
苦悩せずとも。
古くから、
既に 確認された、
著作権的にも フリーな、
やり方、
第1引数内で 四則演算を、
用いて 計算すれば、
いいだけでしょう。
元より、
マイクロソフトの、
公式文章と、構文動作は、
必ずしも 一致しませんから、
公開文章を 信じ過ぎて、
苦悩するのは、
一寸 徒労かも、
知れませんよ?
まぁ、
其の 内に、
含まれる 仕様と、
いう 名の、
bugを、
解き明かして ハンドリングしきり、
利用が 叶うように、
先鋭域に 躍り出る、
事も、
又 いいのですがね。
No.10
- 回答日時:
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
- 回答日時:
No.6です。
大変失礼しました。No.6の回答は無かったこととしてください。
d-q-t-pさん、ご指摘ありがとうございました。
fujillinさんが、ご質問者に指摘された、
>計算対象が 全部のデータになっているので、計算はできますがお求めの結
>果にはならないということかと思います。
>多分、日にちで抽出したデータの中から種類を数えたいのではないかと想像
>しました。
と同じ間違いをおかしていたため、正しい結果にならないようです。
No.8
- 回答日時:
補足もなさそうだし 閉じられてもいないので一応。
=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
- 回答日時:
goomaniaさんの式は IFが入っているので 古いバージョンでは
Ctrl + Shift + Enter確定が必要です。なので「INDEX(配列,0)」を
入れる必要はないし SUMPRODUCTにする意味もないですね。 SUM
でも同じ結果になるでしょう。
TEXTJOINが入っているから Excel2016以降でないと使えないのは
いいとしても 第一引数が「""」なら CONCATでいいじゃないかとな
ります。
それは置いといて計算結果が少し変な気がします。その表だと
「B2:B31>=F2」なら 10・13・31行目は対象外なので結果は「10」
にならないといけないのでは?
No.6
- 回答日時:
>本件は、クローズいたします。
まだ、クローズされていないようなので、一応他の手段をお示しします。
ご質問者は、
>特定日付の範囲内のコードの重複しない件数を求めたいのです。
とおっしゃっているので、集計しなければいけないのは「コード」の数であって、コードはそのものは数値ではないと想像できます。
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
- 回答日時:
この質問に回答が付かない一番の理由は 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列が数値ならこれでできます。
皆様、ご教授ありがとうございました。
私のレベルで理解できない関数でしたので別の方法で対応いたします。
回答いただきました方々には申し訳ございませんでした。
本件は、クローズいたします。
No.3
- 回答日時:
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)
皆様、ご教授ありがとうございました。
私のレベルで理解できない関数でしたので別の方法で対応いたします。
回答いただきました方々には申し訳ございませんでした。
本件は、クローズいたします。
No.2
- 回答日時:
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)
※ 配列乗算になっているので、範囲を大きくしすぎると計算負荷が増大しますのでご注意。
皆様、ご教授ありがとうございました。
私のレベルで理解できない関数でしたので別の方法で対応いたします。
回答いただきました方々には申し訳ございませんでした。
本件は、クローズいたします。
No.1
- 回答日時:
こんにちは
回答がないようなので・・・
>特定日付の範囲内のコードの重複しない件数を求めたいのです。
複数の意味に解釈できるので、何をなさりたいのかはっきりしません。
ひとまず、提示されている式から判断するものとして・・・
>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~1/31)の商品の種類を求めたいのです。実際のデータには集計期間のリストは58件有り種類(求める数)は55種類ですが、
=SUMPRODUCT((範囲1=>日付1)*(範2=<日付2),1/COUNTIF(集計範囲,集計範囲))
にすると「5.641・・・」の結果でした。
また、=SUMPRODUCT((範囲1=>日付1)*(範2=<日付2),IF(集計範囲="",0,1/COUNTIF(集計範囲,集計範囲))) の場合は「#VALUE」になります。
集計期間ゃ集計範囲には空白が無い状態で(他システムからインポートして数値書式に変更)してあります。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
字面がカッコいい英単語
あなたが思う「字面がカッコいい英単語」を教えてください。
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
エクセル 時間帯の重複の有無
その他(Microsoft Office)
-
EXCELで条件に合致したデータから、さらに重複するデータを1と数える
その他(Microsoft Office)
-
Excel 重複を除いてデータ数をカウントする関数 (複数条件ありのケース)
Excel(エクセル)
-
-
4
2つの期間の重複する日数(月数)を計算するには?
Word(ワード)
-
5
SUMPRODUCTに3つ以上の条件
Excel(エクセル)
-
6
EXCEL 重複するデータを1としてカウントする方法
Excel(エクセル)
-
7
複数時間情報の重複を求める
Visual Basic(VBA)
-
8
Excel関数で重複と空白を除いてデータの件数を求める方法
Excel(エクセル)
-
9
(エクセル関数)指定期間内で重複を除いて数え上げる方法
Excel(エクセル)
-
10
エクセル 重なる時間の計算方法
その他(ソフトウェア)
-
11
時間の重複チェック
Excel(エクセル)
-
12
日付を重複を除いてカウントしたいです
Excel(エクセル)
-
13
時間帯の重複を除いた集計について
Excel(エクセル)
-
14
エクセルでの期間の重複について
Excel(エクセル)
-
15
フィルターかけた後、重複を除いてカウントしたい。 すみませんアドバイスお願いします! 取引コード 販
Excel(エクセル)
-
16
vbaで重複期間の算出
Excel(エクセル)
-
17
【Excel】 SUMPRODUCT関数の高速化
Excel(エクセル)
-
18
ExcelのSUMPRODUCTで日付の範囲を指定する方法
その他(Microsoft Office)
-
19
特定セルの内容を更新したら、その更新日を自動的に表示する方法について
Excel(エクセル)
-
20
期間重複チェックがわかりません
Visual Basic(VBA)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelでの勤怠表の関数を教えて...
-
Excelの警告について
-
考えた式の戻り値が期待通りに...
-
エクセル 入力があった場合のみ...
-
エクセルを使ってQRコードを作...
-
FからI列で期限切れ及び期限7日...
-
(マクロ)参照渡しにて、違う...
-
ExcelやLibreOffice Calcの関数...
-
今まで文字化けなく開けていたc...
-
(マクロ)値を返す時は subで...
-
エクセルについての質問です。 ...
-
エクセルでファイルの最終更新...
-
EXCELの散布図で日付が1900年に...
-
エクセルについての質問です。 ...
-
オルトキーを押しながら?
-
Excelの計算が合いません。 諸...
-
ゼロを表示
-
【Excel】 1つのセルの日にちを...
-
【Excel】年月の値によって日の...
-
Excelで特定の文字列が含まれて...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
エクセルデーターから必要な項...
-
エクセルでファイルの最終更新...
-
複数のテキストファイルをexcel...
-
Excelの複数条件の関数
-
【マクロ】ファイル名の一括変...
-
EXCELの散布図で日付が1900年に...
-
マクロの処理が遅くなった
-
Excelの時刻の不思議
-
エクセルでの2項目比較および...
-
Excelマクロで空白セルを詰めて...
-
エクセルの数式バーのフォント...
-
ExcelでASCを使って全角を半角...
-
エクセルで80万行、50列位のデ...
-
今まで文字化けなく開けていたc...
-
エクセルのことで教えてくださ...
-
エクセルVBA 月の中で、月~土...
-
Excelでの表の作り方
-
Excel セルにおけるフォント設...
-
エクセルの質問です。 F列からL...
おすすめ情報