生物実験で、シグナルをカウントし、その個数を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ベストアンサー
- 回答日時:
質問の要点をもっと短くまとめた方が回答がより多くつくと思うのですが・・・
>さらに、空白セルに###とでるのを嫌い、=IF(A2="","",PERSONAL.XLS!eval(SUBSTITUTE(A2,"c","")))としました
未入力のセルは「0」と同値ですが「""」は長さ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","")))
として、必要なら書式設定で「0」を非表示にしてみてください。
No.2
- 回答日時:
文字を一度分解しないと式が長くなります。
0以上の数値が3つ含まれていることが条件としています。
A2=5c+10c+5とします。
F2=SUBSTITUTE(A2,"c","")=5+10+5
G2=MID(A2,1,SEARCH("+",F2,1)-1)=5
H2=MID($F2,SEARCH("+",F2,1)+1,SEARCH("+",$F2,SEARCH("+",$F2,1)+1)-(SEARCH("+",F2,1)+1))=10
I2=MID($F2,SEARCH("+",$F2,SEARCH("+",$F2,1)+1)+1,LEN(F2)-(SEARCH("+",$F2,SEARCH("+",$F2,1)+1)))=5
J2=G2+H2+I2=20
これでどうでしょうか?
ご返答ありがとうございました。
実験結果は、数字だけや10cだけのように数値の個数にバリエーションがあるんです...
しかし、このようにして、数字を抽出する方法があるのを教えて頂き、誠にありがとうございました。
No.3
- 回答日時:
ちょっと気になるのですがSUBPRODUCTという関数はないでしょう。
SUMPRODUCT関数の間違えですね。ところで前の戻って恐縮ですが5c+10c+5のような結果が入力されていてそれの合計を出すのに苦労されているようですが、すべて関数で処理するようにしてはどうでしょう。A列が緑、B列が赤、C列が青、D列が合計になっていますがD列には修正後の緑、E列には修正後の赤、F列には修正後の青が関数でcの入ったデータを含めて数値として表示させるようにします。
それにはD2セルに次の式を入力してF2セルまでオートフィルコピーしたのちに下方にもオートフィルドラッグします。
=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.4
- 回答日時:
解答No3です。
いろいろなケースがあるとのことがよく知りませんでした。お示しの場合でも適応できる式としてD2セルに入力する式としては次のようにしてください。
=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(範囲)でも間違ってはいませんね。
情報を適切に表現できていず、申し訳ありませんでした。
長い式にも関わらずきっちり教えて頂き、ありがとうございました。
今回は勉強になりました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) ファイル全てを .xlsm に変更したところ、プログラムが途中で落ちてしまっています 17 2022/12/07 12:03
- Visual Basic(VBA) VBA 改行コードの取り方 1 2022/03/22 14:14
- Excel(エクセル) VBAで組み合わせ算出やCOUNTIFSの処理を高速化したいです。 4 2022/04/07 02:38
- Excel(エクセル) VBA 特定の列に入っているテキストをコピペ 2 2023/06/14 11:24
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Visual Basic(VBA) countifsについての質問 3 2023/03/08 13:45
- C言語・C++・C# C# DatagridviewにExcelシートを反映するとエラーが出る 2 2023/05/06 17:12
- Visual Basic(VBA) 別シートから年齢別の件数をカウントしたいの続き 5 2023/01/24 00:16
- オープンソース Coinmarketcap api 1 2022/05/30 15:47
- Excel(エクセル) countif関数について質問 4 2022/06/14 12:11
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
Excel 日付を比較したら、同じ...
-
Outlookを立ち上げたらGoogleロ...
-
outlookのメールが固まってしま...
-
会社PCのメールが更新されない
-
【スプレッドシート】指定の日...
-
ウィンドウィズ メモ帳で日付だ...
-
英数字のみ全角から半角に変換
-
Excelで空白以外の値がある列の...
-
Microsoft Formsの「個人情報や...
-
microsoft office
-
MicrosoftOfficeについて質問で...
-
マイクロソフト 一時使用コード...
-
outlookで宛先が異なるメールを...
-
Outlookでの時間指定送信機能に...
-
【スプレドシート】目標達成の...
-
自分の専門分野の仕事。初見で...
-
Microsoft Officeを2台目のPCに...
-
【Excel VBA】PDFを作成して,...
-
エクセルでXLOOKUP関数...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Office 2021 Professional Plus...
-
エクセルで例えば、関数を使っ...
-
Microsoft Formsの「個人情報や...
-
1つのPCに「Excel 2010」「Exc...
-
エクセルで例えば、A1に㈱ベ...
-
理由を教えてください。
-
エクセルでXLOOKUP関数...
-
マイクロソフト オフィスについて
-
VLOOKUP関数について
-
teams設定教えて下さい。 ①ビデ...
-
Googleのスプレッドシートでシ...
-
【Excel VBA】PDFを作成して,...
-
Microsoft365で写真をアルバム...
-
Outlook で宛先が複数の場合の人数
-
Excel テーブル内の空白行の削除
おすすめ情報