連続値の数を求める関数

質問

サンプル画像は1日毎の売上個数データです。前日と同じ個数が売れた日数を調べる関数をご教示いただければ幸いです。A3、A4、A10の3日になります。できれば作業列を作らずに計算したいです。応用編として、前々日と同じ個数が売れた日数を調べる関数もご教示いただけないでしょうか。A4、A12の2日になります。

回答No.6

bunjii · ベストアンサー

>できれば作業列を作らずに計算したいです。
数式内で配列を使えば作業列を使わずに計算できます。
方法は1つに限りません。
比較方法は回答No.1の方法で良いでしょう。
A1:A14=A2:A15の結果はTrueとFalseの羅列になりますので数値化が必要になります。
これを0と1に変換するには次のような方法が有効です。
N(A1:A14=A2:A15)　N関数はTrue→1、False→0のように変換します。
(A1:A14=A2:A15)+0　以下の加減乗除でN関数の代替をさせることができます。
(A1:A14=A2:A15)-0
(A1:A14=A2:A15)*1
(A1:A14=A2:A15)/1
前処理した配列を合計するにはSUM関数またはSUMPRODUCT関数が有効です。
SUM関数の場合は前処理で配列値をSUM関数へ引き継ぐ必要がありますので数式の確定時にCtrl+Shift+Enterの打鍵が必要になります。
SUMPRODUCT関数の場合は数式内で配列を扱えるようになっているため数式を確定するときにEnterキーのみの打鍵で正しい値が得られます。
=SUMPRODUCT(N(A1:A14=A2:A15))
=SUM(N(A1:A14=A2:A15)) 　　Ctrl+Shift+Enterの打鍵で確定のこと
他に次のような方法もあります。
=SUM(INDEX(N(A1:A14=A2:A15),0)) 　　Enterきーのみで確定できる。

お礼

わかりやすいご説明ありがとうございました。とてもよくわかりました。

回答No.8

Nouble

済みません

再度、
誤記載を、見つけました
お詫びの上、
訂正させて、ください

誤記

アルゴリズムとしては
A1の、翌日はA2
A1の、翌日はA2
A1の、翌日はA2
　　　…
　　　…
　　　…
A14の、翌日はA15

正記
アルゴリズムとしては
A1の、翌日はA2
A2の、翌日はA3
A3の、翌日はA4
　　　…
　　　…
　　　…
A14の、翌日はA15
　　　　　　　　　　　以上

回答No.7

Nouble

お心を
留めて頂き、光栄です

+0ですね

其の前に

済みません。
先の、解説には、
誤りが、ありました

お詫びの上、
修正させて、ください

誤記

動作確認

並びの方向を、変えてみました
522217368828174

A1:A14は
52221736882817
A2:A15は
22217368828174

比較結果は
011000001000000

集計すると

正記

動作確認

並びの方向を、変えてみました
522217368828174

A1:A14は
52221736882817
A2:A15は
22217368828174

比較結果は
FTTFFFFFTFFFFFF
(※注：TはTRUEを、FはFALSEを、示す)

比較結果中
TRUEに1、FALSEに0を
与えると

011000001000000

集計すると

以上

済みませんでした。

ところで
Excelで
甲=乙
と、すると
TRUE 又はFALSE が
返されます

論理値と、いうものです
http://officetanaka.net/excel/function/tips/tips59.htm

実は此は、
此のままでは
数値的扱いが、し難い
の、です

しかし、其れは
Microsoftも
解っている事、らしく

数値と、演算させると
TRUE → 0ではない数値
FALSE → 0
と、見なしてくれます

まぁ、実際には
TRUE → 1
FALSE → 0
なのですが

でも、元の値が
何だったか
判らなくなる

そんなような、数値演算を
してしまっては、台無し
ですよね？

其処で、ですね
元の値が
変わらない
数値演算が
欲しい
と、成りますが

大丈夫です
幾つか、あります

其等の、中で
今回は
3つだけ、挙げておきます

n+0　(n|nは、任意の数)
n-0　(n|nは、任意の数)
n*1　(n|nは、任意の数)

です

さて、お待たせしました
結論です

+0ですが
論理値を、数値に
見なして、貰っている

と、言う事です

因みに、
先の、私の式も
配列数式の、内です

で、
何故、好まれるかって？

其れは
SHIFT+CTRL+ENTER
で、確定させたセルを

新たに
クリックした
其の、時の

Excelの
挙動を、見れば
一目瞭然です

端的に、言って
再度、SHIFT+CTRL+ENTER
しないと、

もう、
本当に、簡単に
たった一回
ほんの少し
クリックした
其れ、だけで

Sheetの、設計が
壊れます、から
ね

エラー表示の、バーゲンセールやー
的に、ね

プロとして、業務として、
提供した
経験が、あれば

保守性や、堅牢性は、
重要課題、ですから
ね

回答No.5

msMike

「応用編として、前々日と同じ個数が売れた日」付は、
=IFERROR(SMALL(IF(A$3:A$17=A$1:A$15,ROW(A$3:A$17),""),ROW(A1)),"") を配列数式として入力したセルを下方にズズーッとオートフィルすれば好い。

回答No.4

msMike

[No.3]の式を次のように修正しておきます。
２文字分短い? (^_^)
=IFERROR(SMALL(IF(A$2:A$16=A$1:A$15,ROW(A$2:A$16),""),ROW(A1)),"")

回答No.3

msMike

式 =IFERROR(SMALL(IF(A$2:A$16=A$1:A$15,ROW(A$1:A$15),""),ROW(A1))+1,"")
を入力したセルを下方にズズーッとオートフィル
【お断り】上式は必ず配列数式として入力のこと

回答No.2

imogasi

配列数式でよければ
例えばA１８セルに（データのあるセル範囲外に）
==SUM((A1:A15=A2:A16)*1)と入れてSHIFT+CTRL+ENTER（３つのキーを同時押しする）。
結果３
ーー
同じく前々日の場合
=SUM((A1:A15=A3:A17)*1)
と入れてSHIFT+CTRL+ENTER（３つのキーを同時押しする。）
結果２
＃１のご回答は、は配列数式を避けて、SUMPRODUCT関数でやる、常套手段かと。
私ならこういう難しいやり方を避けて、VBAで素直に判別を繰り返して、数を数える。
しかし、関数愛好家が多くてね。

お礼

こういうやり方もあるのですね。ご教示ありがとうございました。

回答No.1

Nouble

例えは、
此のデータ、なら
連続した、日数は
3日で、良い
ので、しょうか?

アルゴリズムとしては
A1の、翌日はA2
A1の、翌日はA2
A1の、翌日はA2
　　　…
　　　…
　　　…
A14の、翌日はA15

此等を、比較し
一致する数を
集計します

尚、
A0とA1，A15とA16は、
データ外との、比較
なので
比較する意味が、ありません

ので、
省きます。

具体的には
A1:A14とA2:A15を、比較し
同じ、
と、出たものに
1を、付与する
先の結果を、集計する

動作確認

並びの方向を、変えてみました
522217368828174

A1:A14は
52221736882817
A2:A15は
22217368828174

比較結果は
011000001000000

集計すると
3

行けそうですね

式ですが
アルゴリズムさえ、思い付けば
とても、簡単です

=SUMPRODUCT((A1:A14=A2:A15)+0)

1日飛びバーションは
=SUMPRODUCT((A1:A13=A3:A15)+0)

如何で、しょうか?
お役に、立てていた
なら
幸いです。

補足

ご教示ありがとうございます。後学のため教えていただきたいのですが、SUMPRODUCTの式の中で、「+0」をつけるのはどうしてでしょうか？