[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『条件にあったものの個数を合計したい』（初心者）
参照条件はA１です。
この条件と参照するデータは、
（A3、A4、A5）、（A7、A8、A9）、（A11、A12、A13）、…
と複数個あります。
このとき、参照データと比較して一致するのを○、一致しないものを×とすれば、
参照結果は（○、○、○）、（○、○、×）、…（×、×、×）
まで８パターンあるはずです。
そのパターンごとの集計を行いたいのです。
（○、○、○）の個数をA100に、（○、○、×）の個数をA101に、…
という感じです。
どうすればよいでしょうか？よろしくお願いします。
A100=SUMPRODUCT((MOD(ROW(A$3:A$97),4)=3)*
((N(A$3:A$97=A$1)&N(A$4:A$98=A$1)&N(A$5:A$99=A$1))=DEC2BIN(8-ROW(A1),3)))
A107までフィルダウン。
結果はそれぞれ以下のようになるはず。 A100 ○ ○ ○ A101 ○ ○ × A102 ○ × ○ A103 ○ × × A104 × ○ ○ A105 × ○ × A106 × × ○ A107 × × ×
DEC2BIN関数はアドイン関数ですので、組み込みが必要です。 ツール-->アドイン-->分析ツールにチェックを入れてください。
(ROUGE)
アドイン関数を使えば、A100が１０、A101が７等の、
それぞれのパターンの個数になるのですね。
（○○○が111、×××が000といった対応でもよいのですが、個数が欲しいのです。）
しかし、アドイン関数を使わないとダメっぽいですか？
どうも、どのＰＣがどのｵﾌｨｽのDVDかわからなくなっていて…
ほへっ？ 個数を出す数式ですよ？
アドイン関数導入が困難とのことですので、別のアプローチです。
A100=SUMPRODUCT(N(MOD(ROW(A$3:A$97),4)=3),N(((A$3:A$97=A$1)*4+(A$4:A$98=A$1)*2+(A$5:A$99=A$1))=8-ROW(A1))) A107までフィルダウン。 パターンは上記と同様です。
(ROUGE)
質問のときに例示した感じで少し試してみて、うまくいったので
確定したデータ表でやってみました。
データは、参照元がN42で、 （N45,N46,N47）,（N50,N51,N52）…（N795,N796,N797）
となったので、
N28==SUMPRODUCT(N(MOD(ROW(N$45:N$797),5)=3),N(((N$45:N$797=N$42)*5+(N$46:N$798=N$42)*3+(N$47:N$799=N$42))=8-ROW(N42)))
としてみて、これをN29〜N35まで貼り付けてみましたが、うまくいきません。
どこがいけなかったのでしょうか？
度々すいません。
N28==SUMPRODUCT(N(MOD(ROW(N$45:N$797),5)=0),
↑
N(((N$45:N$797=N$42)*4+(N$46:N$798=N$42)*2+(N$47:N$799=N$42))=8-ROW(A1))) ↑ ↑ ↑
何度もすいませんでした！
やっとできました。ありがとうございました。
N28==SUMPRODUCT(N(MOD(ROW(N$45:N$797),5)=0),
↑ ↑ ↑
のN$45はデータの先頭 N$797はデータの最後 5は、各データグループの頭
N(((N$45:N$797=N$42)*4+(N$46:N$798=N$42)*2+(N$47:N$799=N$42))=8-ROW(A1))) ↑ ↑ ↑ ↑ ↑ ↑ ↑
N$42は参照元データ 4は、データグループが３つで形成されている＋１
２は４−１ （つまり、データグループが４つで形成されていれば、５、３、１を使用？）
８は、真偽の場合の数である２＾３ のこと。
もしデータグループが４つで形成されていたら、２＾４＝１６を使用。
で大丈夫ですか？
>N$42は参照元データ 4は、データグループが３つで形成されている＋１ 4は2^(3-1)=2^2 2は2^(3-2) 1は2^(3-3)
>２は４−１ （つまり、データグループが４つで形成されていれば、５、３、１を使用？） データグループが４つで形成されていれば 2^(4-1),2(4-2),2(4-3),1・・・・8,4,2,1を使用
>８は、真偽の場合の数である２＾３ のこと。 >もしデータグループが４つで形成されていたら、２＾４＝１６を使用。 そのとおり
数式を提示した本人として、説明を加えます。
SUMPRODUCT関数は積和を求める関数ですが、配列を扱えることから使用しています。 詳しくは↓のページを見てください。 http://www.excel.studio-kazu.jp/lib/e3h/e3h.html =SUMPRODUCT(N(MOD(ROW(A$3:A$97),4)=3),N(((A$3:A$97=A$1)*4+(A$4:A$98=A$1)*2+(A$5:A$99=A$1))=8-ROW(A1))) 上記数式を分解すると、 N(MOD(ROW(A$3:A$97),4)=3) N(((A$3:A$97=A$1)*4+(A$4:A$98=A$1)*2+(A$5:A$99=A$1))=8-ROW(A1)) になります。 まずは N(MOD(ROW(A$3:A$97),4)=3) から。 これは、データ群でどこが頭のセルかを判定するものです。 ROW関数は行数を返す関数、MOD関数は除算の余りを返す関数です。 今回提示されたデータが4行おきにデータの塊の頭が出てきたので、MOD関数の第2引数に4をいれました。 その上で、余りが3になるところが頭になるため、MOD(ROW(A$3:A$97),4)=3 としました。 この数式の結果が TRUE となる場所がデータの頭になります。 N関数では、N(TRUE)=1、N(FALSE)=0 と返しますので、SUMPRODUCT関数の引数とするために使用しています。 これで、データ群の頭の特定ができました。 次に、N(((A$3:A$97=A$1)*4+(A$4:A$98=A$1)*2+(A$5:A$99=A$1))=8-ROW(A1)) これは、 N(((A$3:A$97=A$1)*2^2+(A$4:A$98=A$1)*2^1+(A$5:A$99=A$1)*2^0)=8-ROW(A1)) とも書けます。 どういうことかというと、2進数としているわけです。 また、塊のうち、使用するデータが3つであることから、左から順番に参照する範囲をひとつずつずらしています。 即ち、データの塊がすべてA1と一致するならば、2^2+2^1+2^0=7 となり、2進数で表記すると 111 となります。 2進数のまま数式で使うのは不便（最初に提示した数式のように、アドイン関数を使う、桁数の制限が厳しい等） なため、今回のように10進数で扱うことにしました。 8-ROW(A1)は、連続データで 7 6 5 4 3 2 1 0 というものが欲しかったためです。 これらは2進数であらわすと、111 110 101 100 011 010 001 000 となります。 1を○、0を×とすれば、ご希望のものと一緒ですよね？ データが4つに増えるのであれば、2進数の桁をひとつ増やしてあげればよいので、 N(MOD(ROW(A$3:A$97),5)=3) <--右の3はROW関数内のA3と連動して変化させる必要があります。 A$4:A$98だったら 4、A$5:A$99だったら 0 というように。 N(((A$3:A$97=A$1)*2^3+(A$4:A$98=A$1)*2^2+(A$5:A$99=A$1)*2^1+(A$6:A$100=A$1)*2^0)=2^4-ROW(A1)) これらを同様にSUMPRODUCT関数に導入すれば、OKです。 (ROUGE)
何度も、そして最後に大変丁寧に、ありがとうございました。
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.