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

Question

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以上が何連続続いたかを求めたいのですが妙案を伝授願います。

cafe_au_lait · Accepted Answer

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に対して、連続回数がどうなるかを数えておけば、でてきたよく分からない数字を連続回数に変換することが出来ます。
上式では、（よく分からない数字と連続回数の）対応表を数式内に入れてあります。

akina_line · Answer

こんにちは。

　私もこの種類の処理はマクロのほうが適していると思いますが、質問者様が「マクロは苦手」とのことなので、関数で考えて見ました。説明が簡単になるよう、１店で説明します。

　　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」を合計する式を入れて完成です。

　分かりやすいようにデータの下に計算式の行を追加しましたが、使いやすくするためには別のシートに式を入れてデータのシートのセルを参照するようにするとよいと思います。

では。

cafe_au_lait · Answer

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

siddhaartha · Answer

貼り付ける場所ですが、Alt-F11でVBAの画面が開きますよね？
そして左側に"プロジェクト"ウィンドウがあると思います。
そこに[Microsoft Excel Objects]というのがあり、その下に
開いているBookのシートの一覧がエクスプローラ風に表示されていますよね。

その中からご質問のワークシートを見つけて、ダブルクリックすれば
右側に[Option Explicit]とだけ書かれた真っ白な画面が表示されますよね。
そこに私のマクロ Const～End Subまでを貼り付けてください。

それ以降は＃５に書いてあるとおりです。

ちなみに、C店だと何故７かですが、A店がAT5だから５行目なんですよね？
ならばB店は６行目、C店が７行目なだけです。

siddhaartha · Answer

＃１、３です。

＞因みに、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に連続数が自動で入ります。

以上、さらに分からないことがあれば聞いてください。

cafe_au_lait · Answer

月数が変わらなければ・・・
以下の式で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

siddhaartha · Answer

一応私もマクロ書いてみました。
データの範囲を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

finneganswake · Answer

マクロ書いた。即席なので、読みづらいけど。
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

siddhaartha · Answer

関数では少し難しいかと思われます。

マクロを使用した方が簡単ですけど・・・
マクロは分かりますか？

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

No.4です。

この回答への補足

こんにちは。

2進数は分かりますか？

この回答への補足

貼り付ける場所ですが、Alt-F11でVBAの画面が開きますよね？

＃１、３です。

この回答への補足

月数が変わらなければ・・・

この回答への補足

一応私もマクロ書いてみました。

この回答への補足

マクロ書いた。

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

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング