excel subproduct 関数での不具合

質問

生物実験で、シグナルをカウントし、その個数をDCOUNT、SUBPRODUCTを使って、分類していま。具体的には、一つの細胞に緑のシグナルが10個、赤のシグナルが20個、青のシグナルが30個、次の細胞には緑が15個、赤が20個、青が2個と細胞を順次数えていきます。
A B C D E
1細胞 緑 赤 青 個数
2 10 20 30
3 15 20 2
4

そこで、緑が5個以上、赤が10個以上、青が20個以上の細胞の個数は、SUBPRODUCT(($A$2:$AB$1000>=5)*($B$2:$B$1000>=10)*($C$2:$C$1000>=20))として求めてきました。
今回、シグナルのパターンで1個ずつバラバラにあるのと、シグナルが5個、10個と塊である分を生物学的に分けなければならなくなりました。
具体的には、20個のシグナルがある場合、それが一つずつバラバラである場合は、20とそのまま、5個の塊が1つ、10個の塊が1つ、そして、バラバラに5個がある場合は、5c+10c+5のように記載します。
塊がある分は、別途、集計するのですが、今までと同じように表で5c+10c+5と記載した分も、cを除いて、5+10+5=20として、緑が5個以上、赤が10個以上、青が20個以上の細胞の個数を求めたいのですが、F2=SUBSTITUTE(A2、"c"、"")として、あらたに変換しても、数式ではないので、5+10+5と文字として標識されるだけだし、これをVALUEで、式に戻るかと考え、VALUE(SUBSTITUTE(A2、"c"、""))としてもエラーとなります。
何か良い方法は無いのでしょうか?
と前回質問し
標準モジュールに
Function eval(s As String)
eval = Evaluate(s)
End Function
を用意。セルに
=eval(SUBSTITUTE(A2,"c",""))
回答を頂きました。
そして、excelでtool、macro、VBEとして、insertからmoduleとして、
Function eval(s As String)
eval = Evaluate(s)
End Function
を入力しました。
そして、D2=eval(SUBSTITUTE(A2,"c",""))と入力すると上手く行かず、PERSONAL.XLS!eval(SUBSTITUTE(A2,"c",""))としたら、うまく行きました(どこかで、入力手順がおかしかったのでしょうか...)。さらに、空白セルに###とでるのを嫌い、=IF(A2="","",PERSONAL.XLS!eval(SUBSTITUTE(A2,"c","")))としました。これをD、E、F列に1から1000までコピー、ペーストしました。A、B、C列をそれぞれ、D、E、F列に返還して計算しました。これを用いて、Dが2個以上、F、Fが4個以上を
=SUBPRODUCT(($D$2:$D$1000>=2)*($E$2:$E$1000>=4)*$F$2:$F$1000>=4))で求めると、個数が異様に多くなります。
おそらく、D、E、F列に上記のIF関数を用いてることが影響しているのだと思いますが、何かよい回避方法は無いでしょうか?

回答No.1

cistronezk · ベストアンサー

質問の要点をもっと短くまとめた方が回答がより多くつくと思うのですが・・・

＞さらに、空白セルに###とでるのを嫌い、=IF(A2="","",PERSONAL.XLS!eval(SUBSTITUTE(A2,"c","")))としました

未入力のセルは「０」と同値ですが「""」は長さ0の文字列です。文字列なので数字より大きくなり、

＞=SUBPRODUCT(($D$2:$D$1000>=2)*($E$2:$E$1000>=4)*$F$2:$F$1000>=4))で求めると、個数が異様に多くなります。

という結果を招きます。たとえば「=(""<9999)」という式をセルに入れてみれば判ります。
対応としては、
=IF(A2="",0,PERSONAL.XLS!eval(SUBSTITUTE(A2,"c","")))
として、必要なら書式設定で「０」を非表示にしてみてください。

お礼

要点がまとまっていず、申し訳ありませんでした。
ご返答ありがとうございました。
うまく行きました。

回答No.4

KURUMITO

解答Ｎｏ3です。
いろいろなケースがあるとのことがよく知りませんでした。お示しの場合でも適応できる式としてＤ2セルに入力する式としては次のようにしてください。
=IF(AND(LEN(A2)<=3,ISNUMBER(SUBSTITUTE(A2,"c","")*1)),SUBSTITUTE(A2,"c","")*1,IF(ISNUMBER(LEFT(SUBSTITUTE(A2,"c",""),2)*1),LEFT(SUBSTITUTE(A2,"c",""),2)*1,LEFT(SUBSTITUTE(A2,"c",""),1)*1)+IF(ISNUMBER(MID(SUBSTITUTE(A2,"c",""),FIND("+",SUBSTITUTE(A2,"c",""))+1,2)*1),MID(SUBSTITUTE(A2,"c",""),FIND("+",SUBSTITUTE(A2,"c",""))+1,2)*1,MID(SUBSTITUTE(A2,"c",""),FIND("+",SUBSTITUTE(A2,"c",""))+1,1)*1)+IF(ISNUMBER(RIGHT(SUBSTITUTE(A2,"c",""),2)*1),RIGHT(SUBSTITUTE(A2,"c",""),2)*1,RIGHT(SUBSTITUTE(A2,"c",""),1)*1))
なお、SUMPRODUCT関数で<>""と使っていますのは空白でない場合という意味で使っています。データの入力のない行は除くということになります。
ISNUMBER(範囲）でも間違ってはいませんね。

お礼

情報を適切に表現できていず、申し訳ありませんでした。
長い式にも関わらずきっちり教えて頂き、ありがとうございました。
今回は勉強になりました。

回答No.3

KURUMITO

ちょっと気になるのですがSUBPRODUCTという関数はないでしょう。SUMPRODUCT関数の間違えですね。
ところで前の戻って恐縮ですが5c+10c+5のような結果が入力されていてそれの合計を出すのに苦労されているようですが、すべて関数で処理するようにしてはどうでしょう。Ａ列が緑、Ｂ列が赤、Ｃ列が青、Ｄ列が合計になっていますがＤ列には修正後の緑、Ｅ列には修正後の赤、Ｆ列には修正後の青が関数でｃの入ったデータを含めて数値として表示させるようにします。
それにはＤ2セルに次の式を入力してＦ2セルまでオートフィルコピーしたのちに下方にもオートフィルドラッグします。
=IF(A2="","",IF(ISNUMBER(A2),A2,(IF(ISNUMBER(LEFT(A2,2)*1),LEFT(A2,2)*1,LEFT(A2,1)*1)+IF(ISNUMBER(MID(A2,FIND("+",A2)+1,2)*1),MID(A2,FIND("+",A2)+1,2)*1,MID(A2,FIND("+",A2)+1,1)*1)+IF(MID(A2,LEN(A2)-1,1)="+",LEFT(A2,1)*1,LEFT(A2,2)*1))))
なお、この式では取り扱われている数値はすべて100以内の数値であるとしています。
そこでSUMPRODUCT関数ですがたとえば次のような式にすればよいでしょう。
=SUMPRODUCT(($D$2:$D$1000<>"")*($D$2:$D$1000>=5)*($E$2:$E$1000>=10)*($F$2:$F$1000>=20))
最初に<>""を使うことでセルを""で空にしても問題はありません。

お礼

ご返答ありがとうございました。
関数名が間違っていて申し訳ありませんでした。
5c+10c+5では20なるのですが、2c+5c+5では9、10c+2c+3cでは22、3cでは#valueとなってしまいます...
しかし、このようにして、数字を抽出する方法があるのを教えて頂き、誠にありがとうございました。
パズルみたいでむつかしいですネ
またSUMPRODUCT関数で、(範囲<>"")*はうまく行きましたが、勉強のためにどのような意味なのか調べましたが、分かりませんでした。もし宜しければ、教えて頂けないでしょうか?
調べると、同じような場合で=SUMPRODUCT(ISNUMBER(範囲)*。。。とする方法を見つけました。

回答No.2

tetumaru_1

文字を一度分解しないと式が長くなります。
０以上の数値が３つ含まれていることが条件としています。
Ａ２＝５c＋１０c＋５とします。
Ｆ２＝SUBSTITUTE(A2,"c","")＝５＋１０＋５
Ｇ２＝MID(A2,1,SEARCH("+",F2,1)-1)＝５
Ｈ２＝MID($F2,SEARCH("+",F2,1)+1,SEARCH("+",$F2,SEARCH("+",$F2,1)+1)-(SEARCH("+",F2,1)+1))＝１０
Ｉ２＝MID($F2,SEARCH("+",$F2,SEARCH("+",$F2,1)+1)+1,LEN(F2)-(SEARCH("+",$F2,SEARCH("+",$F2,1)+1)))＝５
Ｊ２＝G2+H2+I2＝２０
これでどうでしょうか？

お礼

ご返答ありがとうございました。
実験結果は、数字だけや10cだけのように数値の個数にバリエーションがあるんです...
しかし、このようにして、数字を抽出する方法があるのを教えて頂き、誠にありがとうございました。