Excelで連続した1以上の数値の連続個数を知りたい。

質問

1以上の連続数を求める関数を教えて下さい。
　　4月　5月　6月　7月　8月　9月　連続回数
A店　1　　0　　0　　0　　1　　2　　2　
B店　0　　1　　1　　2　　2　　3　　5　
C店　1　　2　　0　　1　　1　　0　　4　（2連続×2なので4）

という感じです。4～9月に1以上が何連続続いたかを求めたいのですが妙案を伝授願います。

回答No.6

cafe_au_lait · ベストアンサー

No.4です。わかりにくくてすみませんでした。
結論から先に言えば、以下の式で大丈夫かと思います(改行は消去してください)。若干の変更があります。

=INDEX({0,0,0,2,0,0,2,3,0,0,0,2,2,2,3,4,0,0,0,2,0,0,2,3,2,2,2,4,3,3,4,
5,0,0,0,2,0,0,2,3,0,0,0,2,2,2,3,4,2,2,2,4,2,2,4,5,3,3,3,5,4,4,5,6},
SUMPRODUCT((AT5:AY5>=1)*{32,16,8,4,2,1})+1)

＞よく分からない数字
たとえば、1以上を1，それ以外を0と考えると、
A店　1　　0　　0　　0　　1　　1　
B店　0　　1　　1　　1　　1　　1　
C店　1　　1　　0　　1　　1　　0
となりますよね。これを2進数の数字と考えると、10進数に直して
A店　35
B店　31
C店　54
と見ることが出来ます。この数字がよく分からない数字です。
連続回数は上記の1と0のパターンから決まりますので、2進数の0から63に対して、連続回数がどうなるかを数えておけば、でてきたよく分からない数字を連続回数に変換することが出来ます。
上式では、（よく分からない数字と連続回数の）対応表を数式内に入れてあります。

補足

惜しい・・・。
１連続も１と表示できたら最高でした。
これだとずっと０と１連続が同じになってしまいます。

回答No.9

akina_line

こんにちは。

　私もこの種類の処理はマクロのほうが適していると思いますが、質問者様が「マクロは苦手」とのことなので、関数で考えて見ました。説明が簡単になるよう、１店で説明します。

　　4月　5月　6月　7月　8月　9月　連続回数
A店　1　　0　　0　　0　　1　　2　　2　
式1　-　「=IF(B2>=1,IF(C2>=1,1,0),0)」　　：６月以降は５月の式をコピー
式2　「=IF(B2>=1,IF(C2>=1,1,0),0)」　　　：５月～８月まで４月の式をコピー
式3　「=MAX(B3:B4)」　　　　　　　　　　　：全ての月に４月の式をコピー

　【解説】
　　式1の行は２つ並んだセルの後ろを中心に考えて「1以上か」で「前のセルも１以上か」判定して両方の条件を満たした場合、「1」を設定しています。
　　式2の行は２つ並んだセルの前を中心に考えて「1以上か」で「後ろのセルも１以上か」判定して両方の条件を満たした場合、「1」を設定しています。
　　式3の行は式1と式2のどちらかが「1」の場合「1」を設定しています。
　　最後に式3の右端に式3の行の「1」を合計する式を入れて完成です。

　分かりやすいようにデータの下に計算式の行を追加しましたが、使いやすくするためには別のシートに式を入れてデータのシートのセルを参照するようにするとよいと思います。

では。

回答No.8

cafe_au_lait

2進数は分かりますか？
たとえば010010は「20」です。左から順に32,16,8,4,2,1をかけて足せばよいです。
まず2進数の0（000000）から63（111111）までを書き出します。
次にそれを見ながら正しい連続回数を数えます。
0から63までの連続回数をカンマでつなげて書いたものを式の中に代入します。
さきほどはこちらで数えたのですが、今は時間がないのでやってみていただけますか？出来ないようでしたらあとでまた来ます。

お礼

１連続は考えなくてもよいそうなので、これでいけそうです。
ありがとうございます。

補足

２進数はわかります。
でも１連続も１とするようにすると、111001は4ということになってしましますね。ほんとは3になるようにしたい。
000001　と　000000　は　0になってしまうのは仕方がないのかもしれないです。

回答No.7

siddhaartha

貼り付ける場所ですが、Alt-F11でVBAの画面が開きますよね？
そして左側に"プロジェクト"ウィンドウがあると思います。
そこに[Microsoft Excel Objects]というのがあり、その下に
開いているBookのシートの一覧がエクスプローラ風に表示されていますよね。

その中からご質問のワークシートを見つけて、ダブルクリックすれば
右側に[Option Explicit]とだけ書かれた真っ白な画面が表示されますよね。
そこに私のマクロ Const～End Subまでを貼り付けてください。

それ以降は＃５に書いてあるとおりです。

ちなみに、C店だと何故７かですが、A店がAT5だから５行目なんですよね？
ならばB店は６行目、C店が７行目なだけです。

回答No.5

siddhaartha

＃１、３です。

＞因みに、A店の4月の1セルはAT5です。
＞AT5～AY5に1以上の数値が連続する回数・・・・。

データがAT5～AY5ならば、初めの４行の数字を
こう書き換えてください(AT=46,AY=51です)

Const clnStartRow As Long = 5 'データの開始行
Const clnEndRow As Long = ？(注1) 'データの終了行
Const clnStartCol As Long = 46 'データの開始列
Const clnEndCol As Long = 51 'データの終了列

(注1)何店舗あるのか分からないので自分で入れてください。例えば、C店までなら7です。

そしてマクロをF11でVBAを開いて貼り付けて、
Private Sub Mac1()　～　End Sub　までの間の
どこかにカーソルを持っていって、F5を押すか、
再生ボタンみたいなアイコンをクリックしてください。
そしたらAZに連続数が自動で入ります。

以上、さらに分からないことがあれば聞いてください。

補足

やっぱりマクロはよくわからないです。
どこにどう貼り付けたりとかがわかってないのでわたしには無理のようです。
なぜＣ店までなら７なのかとかまったくわからないので。

ありがとうございました。

回答No.4

cafe_au_lait

月数が変わらなければ・・・
以下の式で1以上の月のパターンを表す0から63までの数字が出ます（パターンを2進数と考えて10進数に変換）。
=SUMPRODUCT((AT5:AY5>=1)*{1,2,4,8,16,32})
さらにこの数字を対応表にしたがって連続回数に変換します。
対応表をA1:A64とすれば、
=INDEX(A1:A64,SUMPRODUCT((AT5:AY5>=1)*{1,2,4,8,16,32})+1)

範囲指定ではなく数式に対応表の配列を記入することも出来ます(ヘルプを参照)。
参考までに、変換表を載せておきます(間違っていたらごめんなさい)。
0: 0,
1: 0,
2～3: 0,2,
4～7: 0,0,2,3,
8～15: 0,0,0,2,2,2,3,4,
16～31: 0,0,0,2,0,0,2,3,2,2,2,4,3,3,4,5,
32～63: 0,0,0,2,0,0,2,3,0,0,0,2,2,2,3,4,2,2,2,4,2,2,4,5,3,3,3,5,4,4,5,6

補足

対応表とはなんでしょうか？
単純に連続回数セルに=SUMPRODUCT((AT5:AY5>=1)*{1,2,4,8,16,32})と貼り付けるとよくわからない数字がでてきます。
それと=INDEX(A1:A64,SUMPRODUCT((AT5:AY5>=1)*{1,2,4,8,16,32})+1)はどこに貼り付けるのでしょうか？

回答No.3

siddhaartha

一応私もマクロ書いてみました。
データの範囲をConstで指定していますので
行が増えたり月が増えたらこの値を変更してください。
マクロが分からなければ、補足質問してください。

Const clnStartRow As Long = 2 'データの開始行
Const clnEndRow As Long = 4 'データの終了行
Const clnStartCol As Long = 2 'データの開始列
Const clnEndCol As Long = 7 'データの終了列

Private Sub Mac1()
Dim blContinue As Boolean
Dim lnCount As Long
Dim r, c As Long

For r = clnStartRow To clnEndRow
lnCount = 0
blContinue = False
For c = clnStartCol + 1 To clnEndCol
If (Cells(r, c) > 0) Then
If (blContinue = True) Then
lnCount = lnCount + 1
ElseIf (Cells(r, c - 1) > 0) Then
lnCount = lnCount + 2
blContinue = True
End If
End If
Next c
Cells(r, c) = lnCount
Next r

End Sub

補足

マクロはよくわからないです。

因みに、A店の4月の1セルはAT5です。
AT5～AY5に1以上の数値が連続する回数・・・・。

マクロですか。VBEを開いて↑を貼り付けたりしてみましたがまったくよくわかりませんでした（ＴＴ）

回答No.2

finneganswake

マクロ書いた。即席なので、読みづらいけど。
B2に４月、C2に５月…（2行に横に、ってこと。）
A2にＡ店、A3にＢ店…（A列に縦に、ってこと。）
という感じで。
マクロがわからないとたしかにつらいかも。

----------
Sub Macro1()

v = 2

Do Until Cells(v, 7) = ""
p = 2
t = 0
k = 0

Do Until p = 8

If Cells(v, p) = 0 And t = 1 Then
t = 0
End If

If Cells(v, p) <> 0 Then
t = t + 1
End If

If (Cells(v, p) = 0 Or p = 7) And t > 1 Then
k = t + k
t = 0
End If

p = p + 1

Loop

Cells(v, 10) = k

v = v + 1

Loop

End Sub

お礼

マクロはわからないです。ありがとうございました。

回答No.1

siddhaartha

関数では少し難しいかと思われます。

マクロを使用した方が簡単ですけど・・・
マクロは分かりますか？

お礼

マクロはわからないです。ありがとうございました。