再掲・エクセル カウント

質問

前回同様の質問を致しまして、解決したかに思えたのですが、やってみたらできなかったので再掲です。

以下のようなデータがあります。str1 (or 2, 3) は文字列で、*はワイルドカードです。 つまり長い文字列の中にstr1~str3がそれぞれ入っています。

*str1*　*str2*　*str3*　*str1*　*str2*　*str3*　*str1*　*str2*　*str3*　*str1*　・・・
0.303　NaN1.271　0.507　NaN　0　2.662　0.53　0.956　1.772　・・・
0.454　1.126　1.766　0.386　0.234　0.309　NaN　1.028　0.371　0.235　・・・
0.27　NaN　NaN　0.612　NaN　0.255　2.006　0.395　0.437　0.269　・・・

ほしいデータは、各行ごとに、隣接するstr1~str3を１区切りとして、str1~3それぞれを最小値とする組がいくつあるかということです。
つまり上記の例だと一行目では、最初の　0.303　NaN1.271 では 0.303 が最小なので*str1*にカウント1が入り、次の組み合わせ 0.507　NaN　0 では 0 が最小なので *str3* にカウント1、次では 2.662　0.53　0.956 の組み合わせなので、*str2* にカウント1。ここまでで 全て１ずつカウントされることになります。
このようにstr1~3のどこに最小値がくるかを数えるには、関数だけでは厳しいでしょうか？

回答No.7

bunjii · ベストアンサー

回答No.3でSUMPRODUCT関数については後日にすると言いましたので下記の通り説明いたします。
http://okwave.jp/qa/q9116169.html の回答で下記の数式を提示しました。
A13=SUMPRODUCT((A$2:A$11=INDEX($M$2:$O$11,0,INT((COLUMN()-1)/3)+1))*1)
今回はSheet2へ作業用テーブルを作成する条件で関数を組み立てると下記のようになります。
元データのSheet1の1行目は計数用の数式を入力するものとして、2行目に　*str1*　*str2*　*str3*　の繰り返しを設定しました。
元データはA3:AD1802の30列×1800行としました。
2000行ではリソース不足で計算できませんでした。（Office 2013 64bit、RAM=4GB）
Sheet1!A1=SUMPRODUCT((Sheet1!A$3:A$1802=INDEX(Sheet2!$A$2:$J$1801,0,INT((COLUMN()-1)/3)+1))*1)
この数式の場合は配列演算部分が貼付画像の上側のような1列対1列の比較となります。
これでは拾い漏れが起ると思われますので、代替数式として下記を提言します。
Sheet1!A1=SUM((A$3:A$1802=TRANSPOSE(INDEX(Sheet2!$A$2:$K$1801,0,INT((COLUMN()-1)/3)+1)))*1)
此方は数式を確定するときにCtrl+Shift+Enterの打鍵が必要です。
配列演算部分は貼付画像の下部のように1列対1行の比較となります。
何方の比較方法を使うかはあなたの考え方次第です。
後者を選ぶと計算負荷が前者の2乗程度になるでしょう。

回答No.6

imogasi

VBAはダメらしいので、関数で考えてみました。
Sheet4に元データがあるとして（　A2:L４の範囲のデータ）（Sheet4である理由は何もない）今回は質問のデータをできるだけ使いました。
幸いなことに、下記で使っているSMALL関数は
（A)空白セルは無視してくれる
（B)文字列セルは無視してくれる
（含まれていても、式がエラーにならない）ので都合がよいようだ。
右の方にデータを多少加えて１２列にそろえた。
0.303　 NaN 1.271　 0.507　 NaN 0　 2.662　 0.53 0.956　 1.772　 1.507　 1.345
0.454　 1.126　 1.766　 0.386　 0.234　 0.309　 NaN　 1.028　 0.371　 0.235　 1.234　 1.111　
0.27 　NaN　 NaN　 0.612　 NaN　 0.255　 2.006　 0.395　 0.437　 0.269　 0.632　 1.056
ーー
Shee5のA2に=IF(Sheet4!A2=SMALL(OFFSET(Sheet4!$A2,0,INT((COLUMN()-1)/3)*3,1,3),1),1,"")　と入れてL2まで式を複写。（A2の＋ハンドルを意義右に引っ張る）
A2:L2の式を下方向に３行複写。（L2の＋ハンドルを下に引っ張る）
列的に１，１，１，４，４，４，７，７、７・・とづらして行くあたりが、細工のしどころか。
ーー
結果
Sheet５に
1 - - - - 1 - 1 - - - 1
1 - - - 1 - - - 1 1 - -
1 - - - - 1 - 1 - 1 - -
ーーー
後は第１列の和を
=SUM(A2:A4)+SUM(D2:D4)+SUM(G2:G4) +SUM(J2:J4)
などでだせるのでは。
最後のSUMまで１セルの式１本で出すのは、式が複雑になると思い、ここで妥協した。
仕事中などは、手早くやる必要があり、１時間ぐらいが限度だろう。だから作業列（作業シート）などを使って、手早くやる必要がある。
配列数式や「配列でなくて複数のセルをあつかえる関数」を使ってやるのは恰好がよいが、
思い付くまでに時間がかかる。
出た回答をマネして、今回の件がうまく行けば、いいじゃん、という考えも、今までの質問者には、多いようだが。

回答No.5

kagakusuki

今仮に元データの表がSheet1のA1～I4のセル範囲(その内のA1～I1は*str1*～*str3*が入力されている)に存在していて、Sheet2を作業用のシートとして使用して結果を求めるものとします。

　先ず、Sheet2のA1セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER(Sheet1!A2),Sheet1!A2=MIN(OFFSET(Sheet1!A2,,-MOD(COLUMN()-COLUMN($A:$A),3),1,3))),Sheet1!A$1,"")

　次に、Sheet2のA1セルをコピーして、Sheet2のA1～I3のセル範囲に貼り付けて下さい。
　上記の準備が整っていた場合、*str1*の列に最小値が存在するセルの個数を求める関数は次の様なものとなります。

=COUNTIF(Sheet2!$A$1:$I$3,"*str1*")

　同様に*str2*の列に最小値が存在するセルの個数を求める関数は次の様なものとなります。

=COUNTIF(Sheet2!$A$1:$I$3,"*str2*")

　同様に*str3*の列に最小値が存在するセルの個数を求める関数は次の様なものとなります。

=COUNTIF(Sheet2!$A$1:$I$3,"*str3*")

　又例えばSheet1のK1～K3セルに「str1」～「str3」の値が入力されていて、Sheet1のL1～L3セルに左隣のセルに入力されている文字列を含む項目の列に最小値が存在するセルの個数を求める場合には、Sheet1のL1セルに次の関数を入力してから、Sheet1のL1セルをコピーして、Sheet1のL2～L3のセル範囲に貼り付けて下さい。

=COUNTIF(Sheet2!$A$1:$I$3,"*"&$K1&"*")

※尚、御質問文にある値の通りですと「str1」～「str3」のカウント結果がどれも同じ3になってしまい、正しくカウントできているのか否か判り難かったため、下記の添付画像の例ではSheet1のA3セルの値を御質問文にある0.454ではなく、2.454に変更しております。

お礼

やはり作業用テーブルが必要なんですね。
ありがとうございました！

回答No.4

bunjii

>質問なのですが、これだとPQR・・・とコピーしていったときに、OFFSETの返り値が変わっていかないと思うのですが…。
ご指摘の通り4組目（J,K,L列）以降は参照できずに1組目から3組目までの繰り返しになります。
回答を急いだために見落としたようです。
この際ですから作業用テーブルを別シートに作成することにしたいと思います。
Sheet1のA列から元データがあるものとし、Sheet2のA列から各組の最小値を行毎に算出するものとします。
Sheet2!A2=MIN(OFFSET(Sheet1!$A$1,ROW()-1,MOD((COLUMN()-1),3)*3+INT((COLUMN()-1)/3)*9,1,3))
　前回の回答では列数の値の算出が　MOD((COLUMN(A2)-1),3)*3
基本的にはOFFSET関数の列数の算出を次のように変更しました。
MOD((COLUMN()-1),3)*3
　　　　↓　
MOD((COLUMN()-1),3)*3+INT((COLUMN()-1)/3)*9

現在、暇を見ながら再現回数の数式を検証中です。
できれば、何処へ数式を設定したいかも補足して頂ければ幸いです。
また、元データの大凡の行数も提示頂ければ関数での処理で実用になるか否かの判断もできると思います。（配列演算になるので負荷が大きくなります）
尚、各行の最小値の出現個数を各列（3つの列）で計数する必要があるのでしょうか？
必要があるときは1つのセルを重複してカウントされることがありますのでご承知ください。

お礼

ありがとうございます。
お示し頂いたやり方を参考に、解決いたしました。
たすかりました、もっと勉強していきたいです。

回答No.3

bunjii

>作業用テーブルがなくてもできる気がしまして、代案を募ったところでした。
質問の内容から作業用テーブルが必要な理由を述べてみます。
A列からC列について各行の最小値を1列の配列値で返す関数があれば作業用テーブルを作らなくてもカウントできます。
しかし、私が使っているExcel 2013にはそのような関数が見当たりません。
従って、作業用のテーブルを作成して目的の計数を行いました。

>是非、２つの式についての説明をお願いいたします。
行毎の最小値を算出する数式を分かり易く書けば次のようになります。
M2=MIN(A2:C2)
N2=MIN(D2:F2)
O2=MIN(G2:I2)
M2:O2を下へオートフィルコピーすると目的のテーブルができます。
しかし、質問の内容ではM2の数式を右にコピーして更に下へもコピーしなければならないと推測し、MIN関数の引数（配列）をOFFSET関数で列数と行数を指定する方法にしました。
それが次の数式です。
M2=MIN(OFFSET($A$1,ROW()-1,MOD((COLUMN(A1)-1),3)*3,1,3))
OFFSET関数は参照セルを原点として、そこからの隔たりを行と列について指定します。
また、幅と高さで列数と行数を指定できますのでOFFSET関数の戻り値は配列も可能になっています。
M2セルの配列は
OFFSET($A$1,ROW()-1,MOD(COLUMN(A1)-1,3)*3,1,3)
　↓　ROW()=2　COLUMN(A1)=1
OFFSET($A$1,2-1,MOD(1-1,3)*3,1,3)
　↓　MOD(1-1,3)*3=0　被除数（1-1）を除数（3）で除算した残り（0）の3倍
OFFSET($A$1,1,0,1,3)
　↓
A2:C2

N2セルの配列は
OFFSET($A$1,ROW()-1,MOD(COLUMN(B1)-1,3)*3,1,3)
　↓　ROW()=2　COLUMN(B1)=2
OFFSET($A$1,2-1,MOD(2-1,3)*3,1,3)
　↓　MOD(2-1,3)*3=3　被除数（2-1）を除数（3）で除算した残り（1）の3倍
OFFSET($A$1,1,3,1,3)
　↓
D2:F2
SUMPRODUCT関数については後日にさせてください。

補足

ありがとうございます。
質問なのですが、これだとPQR・・・とコピーしていったときに、OFFSETの返り値が変わっていかないと思うのですが…。
→　確認したらやはり変わりませんでした。

回答No.2

imogasi

質問の表現が、私には合わず、意味がわかりにくい。
他の人はどうなんだろう。私も１５年以上ＯＫＷＡＶＥの回答をしていてそう思う。
ーー
質問者は、ＶＢでない言語のプログラムの経験があるのではないかな。ＳｔｒやNaNなんて使ているから。
それなら、関数ではなく、ＶＢＡででもやったらどうか。
関数には適さない性格の問題ともう。でも前＃１の回答が出たので、素の意味などわかるのかなと思っていた。
Ｓｔｒ１などが文字列であれば何か実際を換骨奪胎した名称を挙げたらどう？NaNもエクセルやそのＶＢＡでは使わないのではないか。どういう性格のデータなのかな。空白ではないのかな。空白のことだと、みなして下記で修正してやってみた。
Sub tet02()
Dim cl As Range
' d = Replace(Cells(2, 3), "a", 999) 'OK
'd = Replace(Cells(2, 3), 5, 999) 'OK
'd = Replace(Cells(2, 3), "", 999) 'no　＜－－失敗
For Each cl In Range("A2:I9")
If IsEmpty(cl) = True Then
cl.Value = 999
End If
Next
End Sub
９９９はＨｉＶａｌｕｅのつもり。不適当なら９の桁数を増やしてください。
ーーー
私の作ったデータ例。
データ例ぐらい質問者側で作って、質問に挙げてよ。
画像は見えても、使えないのでエキストを希望。
テスト例はないと、回答者が、たとえばテストしないで上げたら、まちがいを見もらすことももあるよ。少数例でのテストは危ないが。
私は勝手に下記のデータにしてみた。
たとえば近所に３地区（町）があり、各地区３グループがあり、１年間に何回も協議して成績点を記録した表とか。どの地無我最少が多いか？
９９９のところが、「NaN」のセル
質問に載っている例をそのまま使ってない（小数点以下あり数が整数とか、ＮａＮのセル位置は例と違うなど）
Ａ　Ｂ　Ｃ　Ｄ　Ｅ　Ｆ　Ｇ　Ｈ　Ｉ　　＜ーー３列筒区切って考える。列名です。
1 4 999 2 1 4 1 2 999
3 1 6 2 2 1 4 3 1
1 4 3 1 4 9 2 1 2
999 5 1 2 2 1 1 4 2
7 9 1 3 1 3 1 3 2
8 1 3 1 3 999 6 1 4
1 3 2 4 4 1 1 3 2
1 3 2 1 5 5 1 3 2
ーー
コード
標準モジュールに
Sub test01()
Dim d(3) '３組データ
Dim c(3) '各組最少数
For i = 2 To 9
'データを配列に格納
For k = 1 To 3 '列の第ｊブロック　’９列を仮定
For j = 1 To 3 'ブロック無いの第ｊ番セル
d(j) = Cells(i, (k - 1) * 3 + j)
Next j
'--
'最小値の位置を探索
x = d(1)
xr = 1
For j = 2 To 3
If d(j) < x Then
x = d(j)
xr = j
End If
Next j
'--
'MsgBox xr
c(xr) = c(xr) + 1
Next k
Next i
For i = 1 To 3
Cells(20, i) = c(i)
Next i
End Sub
ーー
上記結果は、第２０行に
Ａ　Ｂ　Ｃ　列
12 6 6
第１、４、７グループ（第1軍団）が１２、
第２、５、８グループ第２軍団が６、
第３、６、９グループ第３軍団が６、
となった。
的外れかもしれないが、その場合は許してください。

お礼

ありがとうございます。
質問が分かりづらかったとのこと、すみません。
VBAは使い方がさっぱりです。この問題は関数だけで解決するには向かないんですね。ありがとうございました。

回答No.1

bunjii

>前回同様の質問を致しまして、解決したかに思えたのですが、やってみたらできなかったので再掲です。
前回の質問とは次のリンクのことですか？
http://okwave.jp/qa/q9116169.html
前回質問の回答No.2のことでしたら助言できます。
しかし、解決できなかった内容を説明して頂かないと追加の説明または代案を提示できません。
数式の追加説明であれば理解できない部分について補足してください。

補足

ありがとうございます。
数式について理解が及ばないため、貼り付けでは動かなかったことと、やはり作業用テーブルがなくてもできる気がしまして、代案を募ったところでした。是非、２つの式についての説明をお願いいたします。