エクセルで連続した増加、減少を数えたいのですが

質問

エクセル2010で、列方向に数字が入っていくのですが、4回連続して増加（減少）した場合に数字が赤字になるような書式設定をしたいのですが。
現在は例えばA列に数値を入力するとして、B,C列を挿入して、B列にはA列の値が増加したら「B列の上のセル＋１」、でなければ「０」、C列には逆に減少したら「C列の上のセル＋１」でB列もしくはC列が４以上の場合、入力セルの数値が赤字になるように書式設定（B+C＞４）してアラームを出すようにしているのですが、対象セルが多い表だと、表が横に大きくなってしまう事と、何よりも作業に非常に手間がかかるので全部のシートには適用できない状況です。
どなたか列を挿入せずに何とか上記の目的を達成する方法を教えてください。

回答No.7

kagakusuki · ベストアンサー

回答No.４です。

>ひとつ追加で質問ですが、A8はA5から数えて3回目なのに青字になっていますし、A12もA9から3回目の増加で赤字になっています。

　申し訳御座いません。うっかり、「4回連続して増加（減少）した場合」ではなく、「3回連続して増加（減少）した場合」にしてしまっておりました。
　以下の様に訂正致します。

A6セルを選択
　　↓
Excelウィンドウの上の方に並んでいるタブの中から[ホーム]タブを選択してクリック
　　↓
現れた「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択してください」欄の中にある[数式を使用して、書式設定するセルを決定]を選択してクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄に

=AND(COUNT(A2:A6)=5,A2<A3,A3<A4,A4<A5,A5<A6)

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[フォント]タブをクリック
　　↓
現れた「色」欄をクリック
　　↓
現れた色のサンプルの中にある赤色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択してください」欄の中にある[数式を使用して、書式設定するセルを決定]を選択してクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄に

=AND(COUNT(A2:A6)=5,A2>A3,A3>A4,A4>A5,A5>A6)

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[フォント]タブをクリック
　　↓
現れた「色」欄をクリック
　　↓
現れた色のサンプルの中にある青色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある「適用先」欄に入力されているセル範囲を、2箇所とも

=$A$6:$A$99

に変更する
※マウスの左ボタンを使用した範囲選択を使う事でも、「適用先」欄のセル範囲を指定する事が出来ます
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

お礼

早々のご回答、大変感謝です。
非常に丁寧な回答に恐縮してしまいます。
今週は手が出ませんが、おそらく出来ると思います。
書式設定自体は良く使っていますので。

確認後に報告させて頂きますのでBSはその時に。
感謝！！！！！

回答No.6

imogasi

#5です。質問をＯＫＷＡＶＥで見た時は、回答なしだったので、補足要求的なことを、挙げたのですが、４人先にすでに回答があったようです。
補足有難う。しかし」私が聞いたことには答えてもらってないです。
増加の判定のロジックを聞きたかったのです。特に等しい期があった後の、考え方について疑問を持ちました。
その後自己目的で、ＶＢＡでやってみました、が相当ややこしいと思いました。
関数での、よさそうなご回答もすでに出ているのですが、それでよいと思いますが、
ＶＢＡでやってみたものを挙げてみます。他にVBAでの回答もあるが、それとはちがいます。そういうプログラムは、ふつうは多様性のあるものです。
（１）ワーク列は使いません。念のためＢ列にデータを出していましたが、外しました。
Ａ列の色を付けるだけが目的です。
Ａ－＞Ｈ列に対象を変えるときはＡをＨに変えるとよい。
（２）質問者の条件は４期連蔵増加（減少）ですが、テストデータを作りにくいので３期にしました。４期にするときは
If cp >= 2 ThenをIf cp >= 4 Thenにする。
（３）やっていることの、コメントを詳しく入れました。
（４）３期増加鏡面にセルに色を付けています「。
マクロといっても、使うほうは何もむつかしことはない。
自分のニーズに合わせた、修正はややむつかしいが類推できるものも多い（本件で３期を４期にするような例）
エラーが出たり、目的と違う結果の場合は、コードを修正するのは初心者にはむつかしいので断念することこと。
本件、減少局面についてのコードは略します。
＝＝
やり方は
（０）マクロを有効にする方法
http://www.start-macro.com/55/w/s010.html　をやってください。
あなたのそのパソコンのエクセルで、今後にわたって１度だけでよい。
（１）目的のブックの、目的のデータシートを開く。
（２）ＡＬＴ＋Ｆ１１キーを同時押し
（３）メニューの挿入をクリックー標準モジュールをクリック
（４）出てきた（ＶＢＥの）画面にかいおう回答で出ているコードをコピペ
（Ｓｕｂの行からＥｎｄ　Ｓｕｂの行までです）
（５）（Ｓｕｂ　ＥＮＤＦまでの中のコードの１行だけをクリックしておいて）５キーを押す（実行せよ、の操作）
（６）シートに戻って実行された結果を見る
コード
Sub test01()
Range("A1:A1000").Interior.ColorIndex = 0 ' 背景色だけをクリア。データは１０００行以下の例
'Exit Sub
lr = Range("A1000").End(xlUp).Row
'MsgBox lr
'--初期設定
cp = 0
f = 1 '最初は第１行
'--
For i = 2 To lr
If Cells(i, "A") > Cells(i - 1, "A") Then '増加局面
cp = cp + 1 '増加連続行数＋１
ElseIf Cells(i, "A") = Cells(i - 1, "A") Then '直前行と等しい
'何もしない
Else '直前行より減少
'Cells(i - 1, "B") = cp '増加連続行数確定
t = i - 1 '増加連続は直前行まで
If cp >= 2 Then
Range("A" & f & ":A" & t).Interior.ColorIndex = 6 '書式設定実行
End If
f = i
'--
f = i
cp = 0
End If
Next i
'
'--行処理終了(データ最終行まで来た後）
'--貯め込み情報を吐き出し
'Cells(i - 1, "B") = cp
If cp >= 2 Then
t = i - 1
Range("A" & f & ":A" & t).Interior.ColorIndex = 6 '書式設定実行.黄色にした。
End If
End Sub
'＝＝＝増加
'cpは 1
'fromはそのまま
'toはｔ＝i 現在行まで引き上げ
' 書式表示　実行しない
'＝＝＝等しい
'cpは 変化させない。1
'fromはそのまま
'toはそのまま
' 書式表示　実行しない
'＝＝＝減少
'cpは0
'fromはそのまま
'toは
' 書式表示は（確定したので）実行する
＝＝＝＝＝
しかし、鬱陶しいようであれば無視してください。ビジネスではVBAができないと手足が出ないケースに必ずぶつかると思います。

お礼

何度もお手数をおかけします。
１つ答えていないのに気付きました。
前回（上のセル）と同じ値の時にはどうすか、を決めていませんが、目的からすると継続（連続傾向）とするべきでしょうか。
但し、例えば同じ原因に起因する傾向ならその値は無視（正常値）、といった考え方になります。
どっちにしても、原因、背景、状況等々により対処が変わるのでその時々で対応することになります。
本当に異常値なら、3σ外れや管理グラフででひっかけられるようになっています。
今回は異常が発生する前段階での予防処置という考え方です。

補足

非常にご丁寧な検討を頂きありがとうございます。
これだけやっていただいたので当方の（無知による）出来るかできないかは別としても質問の内容は明確に伝えたいと思います。
先ず、当方は品質管理に携わっており、今回の目的はISOで要求されている？「統計的手法を用いた品質管理」とかいうやつで、これまでに検査値の過去最高、最低、3σ外れ等々のアラーム（赤字）がでるようにしてきたのですが、更には検査値（品質）が有る傾向で動いた時にもアラームを出したいと思ったのです。
一応その世界では3連（続）とか5連（続）とか言われているようで、連続して検査値が同じ傾向で動いた時には、製造工程に何らかの異常が起こりつつある可能性を疑いましょう、というものが有ります。
そこで各項目の試験結果が4連とか5連続で同じ方向に動いた場合、結果入力時に警告を出すことが目的です。
よって前回と同じ値の場合はどうするかは厳密には決まっていない（内容によって決めれば良い）のですが、4連の場合が分かれば　＞４とするか、＝＞４とすれば応用できるかと思いました。

具体例としては、「製品中の異物の数」や「比重」等々の検査項目が有ってA列には異物の数、B列には比重、C列には・・・と入力されていきます。
この表で、例えば、「異物の数」の規格が１ｇ当り20個以下という製品が有って20個以下なら正常品として「合格」で良いのですが、5，7，9，10個というように連続した傾向を示した時には規格には十分合格でも、生産部署に何か異常が起こりつつあるのでは？というアラームを出すことが目的です。
製品の項目数が5-10項目あるので、増減判定の列を挿入（実際には表の右端に追加）していたのでは非常に手間がかかるのと作業がやりづらくて仕方ないので改善案を質問しました。

大体1製品で３－５項目の数値が入るので、それに対し表の右に項目ごとに2列追加して増減を判定するような方法では200-300品種のエクセルのシートに細工するのに限界を感じての質問でした。

仰るように関数だけでは手におえないケースも有ることは認識していますがマクロはちょっとやそっとでは、といった状況でてっとりばやくここで出来そうな方法を教えて頂いている次第です。
imogasiさんの質問に答えられたでしょうか？
お手数をおかけしました。

回答No.5

imogasi

入力のつど、１列挿入するという、入力データの記録のやり方法は普通しないのではないですか。
元からＢ，Ｃ列は増加や減少の記録列として確保する（他のデータ入力には使わないのでは❓あるいは基のデータが出来上がってから、１列あたり隣列に２列挿入して関数式を入れるのか。
＞B列の上のセル
とは直上行のセルですか。
全般に、行はどうなっていくのか質問ではわかりにくい。
＞A列の値が増加したら
Ａ列の「直前行！」に対し増えた場合はそうするのか。
１回落ちれば、落ちた直前のデータに対して、比較カウントするのか、０に戻って、次は減少したデータをもとに以後件数カウントのルールを当てはめるのか？
＝＝＝
関数でも使って現在やっているのだろうが、関数は別にして、Ａ列のデータがこうだから
Ｂ列の値はこうしたい、という風に説明すべきではないですか。
１例でもよいからデータがこうだから、そのデータによってこういう数値にしたいということを書いてもらえませんか。
ーー
関数よりも、ＶＢＡでやれば繰り返しや、ケース分類が自由なので、簡単な気がする。

お礼

ありがとうございます。
そろそろVBAも視野に入れて勉強する時期ということは認識しているのですが。

補足

ご回答ありがとうございます。
質問の仕方がまずくもうしわけありません。
当方VBAは使えないので困っています。

目的はA列の数値がｎ回連続で増加、減少した場合、入力値が赤字になってアラームを出すようにしたいのですが、現在はその仕掛けの為にB,C列を挿入しているのですが表が大きいので列を挿入するような方法では対処しきれないので困っています。
実際には大事な項目のみに絞って、表の右端の２列で作業して、入力列（A列）でアラームが出るように書式設定しています。

回答No.4

kagakusuki

今仮に、A1セルには項目名が入力されていて、A2からA99の範囲までが数字の入力欄として使用されるものとし、4回連続して増加した場合は数字が赤字になり、4回連続して増加した場合は数字が赤字になる様にするものとします。
　その場合、以下の様な操作を行って条件付き書式を設定して下さい。

A5セルを選択
　　↓
Excelウィンドウの上の方に並んでいるタブの中から[ホーム]タブを選択してクリック
　　↓
現れた「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択してください」欄の中にある[数式を使用して、書式設定するセルを決定]を選択してクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄に

=AND(COUNT(A2:A5)=4,A2<A3,A3<A4,A4<A5)

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[フォント]タブをクリック
　　↓
現れた「色」欄をクリック
　　↓
現れた色のサンプルの中にある赤色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択してください」欄の中にある[数式を使用して、書式設定するセルを決定]を選択してクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄に

=AND(COUNT(A2:A5)=4,A2>A3,A3>A4,A4>A5)

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[フォント]タブをクリック
　　↓
現れた「色」欄をクリック
　　↓
現れた色のサンプルの中にある青色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある「適用先」欄に入力されているセル範囲を、2箇所とも

=$A$5:$A$99

に変更する
※マウスの左ボタンを使用した範囲選択を使う事でも、「適用先」欄のセル範囲を指定する事が出来ます
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

　以上です。

お礼

ありがとうございます。
修正回答までいただき感謝！！

補足

ご回答ありがとうございます。
目的が分かりにくい質問だったようですが、ご回答の内容がまさに当方の目的でした。
増加、減少を判断するための列を挿入することなく入力列だけでの書式設定の方法を知りたかったのですが、説明が非常に悪かったようです。（反省）

ひとつ追加で質問ですが、A8はA5から数えて3回目なのに青字になっていますし、A12もA9から3回目の増加で赤字になっています。
これは単に４を5にすれば4回目に変更されるのでしょうか？
あるいはCOUNTの式の範囲も変える必要が有るのでしょうか？
出来れば、5回連続のケースはどこを変更するのかを併せて教えて頂けたら大変ありがたいのですが。
ここまで教えて頂いて試行錯誤もせずに「おんぶにだっこ」で申し訳ありませんが、ご回答の数式の意味が今一理解できないのでお手数をお掛けしますが宜しくお願い致します。

回答No.3

f272

私ならそういうことをするマクロを使います。

これはSheet1のA列とSheet2のB列に適用する例です。

Sub test4()
Set rng = Worksheets("Sheet1").Range("A1")
Call test4sub(rng)
Set rng = Worksheets("Sheet2").Range("B1")
Call test4sub(rng)
End Sub

Sub test4sub(rng)
r1 = rng.Value
count = 0
Do
Set rng = rng.Offset(1)
If IsEmpty(rng) Then Exit Do
R0 = r1
r1 = rng.Value
If count > 0 Then
If R0 < r1 Then
count = count + 1
Else
count = -1
End If
ElseIf count < 0 Then
If R0 > r1 Then
count = count - 1
Else
count = 1
End If
ElseIf count = 0 Then
If R0 < r1 Then
count = 1
ElseIf R0 > r1 Then
count = -1
End If
End If
If Abs(count) >= 4 Then rng.Font.ColorIndex = 3
' Rng.Offset(, 1) = count
Loop
End Sub

お礼

ご丁寧な回答ありがとうございます。
但し、当方マクロの知識なく、上記が正常に動くという自信が全くなく・・・・
折角教えていたのに申し訳ありません。トホホ・・・

回答No.2

tsubu-yuki

えーと。

> 例えばA列に数値を入力するとして
ということは、Ａ列以外に挿入することもあり得ると？

> B,C列を挿入して
これは、列全体？それとも行単位？
まぁ、そのあとのくだりを見る限り行単位でしょうけれど。

> B列もしくはC列が４以上の場合
> 書式設定（B+C＞４）して
ＢかＣが４以上？Ｂ＋Ｃが４を超える？どちらでしょう？

> 作業に非常に手間がかかる
そうでしょうねぇ。そういう作り方をしてるようですし。

> 列を挿入せずに
あれ。列は挿入するのが前提なのではないのですか？
だとしたら、冒頭の
> A列に数値を入力するとして、B,C列を挿入して
が狂っちゃいますね。
というわけで、冒頭に戻る。

> 上記の目的
結局、何が目的でしょう？
> 4回連続して増加（減少）した場合に数字が赤字になるような書式設定
コレですか？
だとしたら、例えばＥ１セルに設定するとしたら、条件付き書式で
　　=OR(AND(E1>D1,D1>C1,C1>B1,B1>A1),AND(E1<D1,D1<C1,C1<B1,B1<A1))
の式を入力、文字色を赤にしてやればきっとできます。
列方向に「４連続で増加または減少」ですから、
Ａ～Ｄ列がこれに該当することは無いはずですし、
日本語的に言う「以上・以下」という条件は当てはまりませんから
（いわゆる「イコール（同値）」は増加にも減少にも含まれません）、
冒頭の
> 4回連続して増加（減少）した場合に数字が赤字になるような書式設定
だけなら、この式でいけるということです。

条件が違うのであれば、
もう少し日本語的にまとめて補足、あるいは再質問ください。
どれが「本当の条件」なのかさっぱりわかりません。

お礼

ありがとうございました。
やりたいことに対する回答のようですので頑張ってみます。

回答No.1

panacon

セルB2に、=if(A1-A2<0,1,0)と入力します。次にB2のフィルハンドルをつかんでA列と同じだけ下方にドラッグして式をコピーします。次に、セルC5に、=B2+B3+B4+B5と入力して、同時に条件付き初期でC5のセルが4の時に赤字にするようにします。このC5のフィルハンドルをつかんで下方にドラッグしてA列と同じ位置にします。条件付き書式は、A列とB列にもC列の値を見て赤字にすればもっと良いと思います。
C5以降の値は最大が4です。これが4となったときは、4回連続でA列の値が減少したことになります。

お礼

早々のご回答ありがとうございます。
質問の仕方がまずかったので折角ご回答頂いたのですが、目的が違っていて
＞どなたか列を挿入せずに何とか・・・
ということでB,C列は現在他の項目の数値が入っています。
この目的の為に増加、減少を判断するためにB,C列を挿入しているので、何とか列の挿入をしない方法が知りたいのです。

ご回答に感謝いたします。