こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

-広告-

解決済みの質問

再掲・エクセル カウント 

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

以下のようなデータがあります。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を1区切りとして、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。ここまでで 全て1ずつカウントされることになります。
このようにstr1~3のどこに最小値がくるかを数えるには、関数だけでは厳しいでしょうか?

投稿日時 - 2016-01-25 16:36:48

QNo.9116926

すぐに回答ほしいです

質問者が選んだベストアンサー

回答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乗程度になるでしょう。

投稿日時 - 2016-01-26 18:47:08

ANo.7

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

-広告-
-広告-

回答(7)

ANo.6

VBAはダメらしいので、関数で考えてみました。
Sheet4に元データがあるとして( A2:L4の範囲のデータ)(Sheet4である理由は何もない)今回は質問のデータをできるだけ使いました。
幸いなことに、下記で使っているSMALL関数は
(A)空白セルは無視してくれる
(B)文字列セルは無視してくれる
(含まれていても、式がエラーにならない)ので都合がよいようだ。
右の方にデータを多少加えて12列にそろえた。
0.303 NaN1.271 0.507 NaN0 2.662 0.530.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の式を下方向に3行複写。(L2の+ハンドルを下に引っ張る)
列的に1,1,1,4,4,4,7,7、7・・とづらして行くあたりが、細工のしどころか。
ーー
結果
Sheet5に
1----1-1---1
1---1---11--
1----1-1-1--
ーーー
後は第1列の和を
=SUM(A2:A4)+SUM(D2:D4)+SUM(G2:G4) +SUM(J2:J4)
などでだせるのでは。
最後のSUMまで1セルの式1本で出すのは、式が複雑になると思い、ここで妥協した。
仕事中などは、手早くやる必要があり、1時間ぐらいが限度だろう。だから作業列(作業シート)などを使って、手早くやる必要がある。
配列数式や「配列でなくて複数のセルをあつかえる関数」を使ってやるのは恰好がよいが、
思い付くまでに時間がかかる。
出た回答をマネして、今回の件がうまく行けば、いいじゃん、という考えも、今までの質問者には、多いようだが。

投稿日時 - 2016-01-26 17:27:39

-広告-

ANo.5

 今仮に元データの表が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に変更しております。

投稿日時 - 2016-01-26 12:05:19

お礼

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

投稿日時 - 2016-01-26 14:37:25

ANo.4

>質問なのですが、これだと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つのセルを重複してカウントされることがありますのでご承知ください。

投稿日時 - 2016-01-26 09:39:44

お礼

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

投稿日時 - 2016-01-26 14:32:51

ANo.3

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

>是非、2つの式についての説明をお願いいたします。
行毎の最小値を算出する数式を分かり易く書けば次のようになります。
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関数については後日にさせてください。

投稿日時 - 2016-01-25 21:04:03

補足

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

投稿日時 - 2016-01-26 00:57:30

ANo.2

質問の表現が、私には合わず、意味がわかりにくい。
他の人はどうなんだろう。私も15年以上OKWAVEの回答をしていてそう思う。
ーー
質問者は、VBでない言語のプログラムの経験があるのではないかな。StrやNaNなんて使ているから。
それなら、関数ではなく、VBAででもやったらどうか。
関数には適さない性格の問題ともう。でも前#1の回答が出たので、素の意味などわかるのかなと思っていた。
Str1などが文字列であれば何か実際を換骨奪胎した名称を挙げたらどう?NaNもエクセルやそのVBAでは使わないのではないか。どういう性格のデータなのかな。空白ではないのかな。空白のことだと、みなして下記で修正してやってみた。
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
999はHiValueのつもり。不適当なら9の桁数を増やしてください。
ーーー
私の作ったデータ例。
データ例ぐらい質問者側で作って、質問に挙げてよ。
画像は見えても、使えないのでエキストを希望。
テスト例はないと、回答者が、たとえばテストしないで上げたら、まちがいを見もらすことももあるよ。少数例でのテストは危ないが。
私は勝手に下記のデータにしてみた。
たとえば近所に3地区(町)があり、各地区3グループがあり、1年間に何回も協議して成績点を記録した表とか。どの地無我最少が多いか?
999のところが、「NaN」のセル
質問に載っている例をそのまま使ってない(小数点以下あり数が整数とか、NaNのセル位置は例と違うなど)
A B C D E F G H I  <ーー3列筒区切って考える。列名です。
1499921412999
316221431
143149212
99951221142
791313132
81313999614
132441132
132155132
ーー
コード
標準モジュールに
Sub test01()
Dim d(3) '3組データ
Dim c(3) '各組最少数
For i = 2 To 9
'データを配列に格納
For k = 1 To 3 '列の第jブロック ’9列を仮定
For j = 1 To 3 'ブロック無いの第j番セル
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
ーー
上記結果は、第20行に
A B C 列
1266
第1、4、7グループ(第1軍団)が12、
第2、5、8グループ第2軍団が6、
第3、6、9グループ第3軍団が6、
となった。
的外れかもしれないが、その場合は許してください。

投稿日時 - 2016-01-25 18:16:15

お礼

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

投稿日時 - 2016-01-25 19:50:57

ANo.1

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

投稿日時 - 2016-01-25 17:29:59

補足

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

投稿日時 - 2016-01-25 17:49:03

-広告-
-広告-

あなたにオススメの質問

-広告-
-広告-