[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『セル同士の割り算の後条件処理』（あき）
お世話になります。
学校の先輩からこのサイトを知りました。私には分からない事だらけなので
質問させて頂きます。
今のところ表の行数は3000行程度ですが今後増えていく予定です。
R列に要素の下限、S列に要素の上限が有り、その割り算の結果をT列に
値を記録します。
T列 = R列 / S列
その結果から、T列に色を付けたいのです。
条件として T列を変数に格納して変数の下から上に引き算をして
マイナスだったら引く数のセルの背景を赤くします。
また、同じ値だった場合は引かれる値と引く値のセルの背景を黒くして
文字は白抜きにします。
次に変数を上から下に見ていき引き算の値がプラスなら何もしませんが
マイナスならセルの背景を青くします。
この時、赤いセルがいくつか連続してもスルーして背景色の無いセルまで
行き計算し処理をします。
この値は上から順に見ていくと右肩下がりで下から順に見ていくと
右肩上がりになります。
この様な処理は可能でしょうか？
教えて頂けると助かります。
よろしくお願いいたします。
補足としてデータの数は23行で25行間隔で2行目からデータが有ります。
＜ 使用 Excel：Excel2016、使用 OS：Windows10 ＞
すみません。書いてある事の意味がわかりません。 何で変数が出てくるのかもわかりません。 普通に T1に =R1/S1 とかでいいんじゃないですか？ 色塗りは、条件付き書式でも使ってやれば・・・。 （BJ） 2018/12/20(木) 13:22
また、学校に在籍して在籍中は私が管理して後輩に引き継ぐ時、書式が壊れたらとも考えられます。
なのでマクロと考えた次第です。
悪意のある操作さえしなければマクロは壊れませんので・・・
改めてお願いいたします。
（あき） 2018/12/20(木) 13:58
何を言っているのか分からないです。
・要素とは？ ・要素の下限・上限とは？ ・変数とは何ですか？ ・変数の下から上に引き算とは、何をどうするんですか？
・・疑問だらけです。
すみませんが、10行くらいの具体的サンプルを使って、 その説明に当てはめて貰えませんか？
（半平太） 2018/12/20(木) 19:27
下から引き算ーーーーーーーーーー上から引き算
t10 ーーーーーーーーーーーーーーt1
t9 ーーt10-t9 ーーーーーーーーーーt2 ーーーーt1-t2
t8 ーー t9-t8ーーーーーーーーーーーt3 ーーーーt2-t3
t7 マイナス セル赤 ーーーーーーーt4 セル赤はスルー この場合 t3ーt5
t6 同じ値 セル黒 白文字 ーーーーーt5
t5 同じ値 セル黒 白文字 ーーーーーt6
t4 ーーーーーーーーーーーーーー t7 プラス セル青
t3 マイナス セル赤 ーーーーーーー t8 セル赤はスルー この場合t7ーt9
t2 ーーーーーーーーーーーーーー t9
t1ーーーーーーーーーーーーーー t10
イメージ伝わりましたか？
言葉足らずですみません。
（あき） 2018/12/20(木) 22:01
（γ） 2018/12/20(木) 22:22
R列・ S列・ T列・下からの引き算・上からの引き算
1.2・ 1.5・ 0.800・T2-T1・・ ・・・・・・・ ・
1.5・ 2.2・ 0.682・T3-T2・・ ・・・・T1-T2
1.6・ 2.4・ 0.667・T4-T3・セル黒・・T2-T3
1.8・ 2.7・ 0.667・T5-T4・セル黒・・T3-T4
2.4・ 3.7・ 0.649・T6-T5・セル赤・・・-・ スルー
2.7・ 4.0・ 0.675・T7-T6・・ ・・・・T4-T6 セル青
3.6・ 5.8・ 0.621・T8-T7・・ ・・・・T6-T7
5.7・ 9.3・ 0.613・T9-T8・・ ・・・・T7-T8
6.0・ 9.9・ 0.606・T10-T9・セル赤・・・-・ スルー
8.0・ 13.1・ 0.610・T11-T10・・・・・T8-T10
10・ 16.6・ 0.602・・・・・・・・・・T10-T11
（あき） 2018/12/21(金) 01:12
R S T U V
1 要素の下限 要素の上限 下限/上限 下から引き算 上から引き算
2 1.2 1.5 =R2/S2 =T3-T2
3 1.5 2.2 =R3/S3 =T4-T3 =T2-T3
4 1.6 2.4 =R4/S4 =T5-T4 =T3-T4
5 1.8 2.7 =R5/S5 =T6-T5 =T4-T5
6 2.4 3.7 =R6/S6 =T7-T6 =T5-T6
7 2.7 4 =R7/S7 =T8-T7 =T6-T7
8 3.6 5.8 =R8/S8 =T9-T8 =T7-T8
9 5.7 9.3 =R9/S9 =T10-T9 =T8-T9
10 6 9.9 =R10/S10 =T11-T10 =T9-T10
11 8 13.1 =R11/S11 =T12-T11 =T10-T11
12 10 16.6 =R12/S12 =T11-T12
（TAKA） 2018/12/21(金) 10:16
>条件として T列を変数に格納して変数の下から上に引き算をして マイナスだったら引く数のセルの背景を赤くします。
→ U列の値が 0より小さかったら その行のT列セルの背景赤
>また、同じ値だった場合は引かれる値と引く値のセルの背景を黒くして 文字は白抜きにします。
→ U列の値が 0 だったら その行のT列セルとその一つ下を 背景黒 文字白
>次に変数を上から下に見ていき引き算の値がプラスなら何もしませんが マイナスならセルの背景を青くします。 この時、赤いセルがいくつか連続してもスルーして背景色の無いセルまで 行き計算し処理をします。
→ V列の値が 0より小さくて、かつ、
その行のT列セルが塗りつぶされていなかったら 背景青
ってことですか？
（TAKA） 2018/12/21(金) 10:50
Sub Macro1()
Dim i As Long, Lr As Long
With ThisWorkbook.Sheets("Sheet1")
Lr = .Cells(.Rows.Count, "R").End(xlUp).Row
.Range("R2", .Cells(Lr, "V")).Interior.Pattern = xlNone
.Range("R2", .Cells(Lr, "V")).Font.Color = vbBlack
For i = 2 To Lr
If i <> Lr Then
If .Cells(i, "U") < 0 Then
If .Cells(i, "T").Interior.Color <> vbBlack Then
.Cells(i, "T").Interior.Color = vbRed
End If
Else
If .Cells(i, "U") = 0 Then
.Cells(i, "T").Resize(2, 1).Font.Color = vbWhite
.Cells(i, "T").Resize(2, 1).Interior.Color = vbBlack
Else
If .Cells(i, "V") < 0 Then .Cells(i, "T").Interior.Color = vbBlue
End If
End If
Else
If .Cells(i - 1, "U") <> 0 And .Cells(i, "V") < 0 Then
.Cells(i, "T").Interior.Color = vbBlue
End If
End If
Next i
End With
End Sub
要素の下限 要素の上限 下限/上限 下から引き算 上から引き算
1.2 1.5 0.800 赤 -0.119
1.5 2.2 0.681 赤 -0.015 0.119
1.6 2.4 0.666 黒 0.000 0.015
1.8 2.7 0.666 黒 -0.018 0.000
2.4 3.7 0.648 0.027 0.018
2.7 4 0.675 赤 -0.055 -0.027
3.6 5.8 0.620 赤 -0.008 0.055
5.7 9.3 0.612 赤 -0.006 0.008
6 9.9 0.606 0.004 0.006
8 13.1 0.610 赤 -0.008 -0.004
10 16.6 0.602 0.008
うーん、、結果が違うので違うってことですね。 ちょっとやりたいことがしっかり理解できないので私はここまでで、、 （TAKA） 2018/12/21(金) 11:19
（あき） 2018/12/21(金) 14:37
Dim c As Range, i As Long
Columns("T:T").Interior.Pattern = xlNone
Columns("T:T").Font.ColorIndex = vbBlack
For Each c In Range("R:R").SpecialCells(2)
c.Offset(, 2).Value = Format(c.Value / c.Offset(, 1).Value, "#.###")
Next c
For i = Range("T" & Rows.Count).End(xlUp).Row To 2 Step -1
If Range("T" & i).Value > Range("T" & i - 1).Value Then
Range("T" & i - 1).Interior.Color = vbRed
ElseIf Range("T" & i).Value = Range("T" & i - 1).Value Then
Range("T" & i - 1).Interior.Color = vbBlack
Range("T" & i).Interior.Color = vbBlack
Range("T" & i - 1).Font.Color = vbWhite
Range("T" & i).Font.Color = vbWhite
End If
Next i
dt = Range("T1").Value
For i = 2 To Range("T" & Rows.Count).End(xlUp).Row
If Range("T" & i).Interior.Color <> vbRed Then
If Range("T" & i).Value > dt Then
Range("T" & i).Interior.Color = vbBlue
End If
dt = Range("T" & i).Value
End If
Next i
End Sub
（mm） 2018/12/21(金) 14:39
Dim c As Range, i As Long
Columns("T:T").Interior.Pattern = xlNone
Columns("T:T").Font.ColorIndex = vbBlack
For Each c In Range("R:R").SpecialCells(2)
♯ c.Offset(, 2).Value = Format(c.Value / c.Offset(, 1).Value, "#.###")
Next c
♯ここで実行時エラー 13
型が合いません。
初心者なりに考えて c As Range 変数ｃの型が違うのかと思いバリアントやlong等入れて
試してみましたが分かりませんでした。
どの様に修正したらよいか教えてください。
後、空白行があることを書き忘れていました。
この場合の処理ですと数値が ０同士の割り算は背景色が黒になってしまう疑念があるので
重ね重ね申し訳ございませんが、対処の方法をお教えください。
（あき） 2018/12/22(土) 01:35
2018/12/21(金) 10:50にTAKAさんから質問がされていますから、それに回答をお願いします。
> 重大なミスがありました。下方から引いていけば当然マイナスになるのは当たり前でした。 > 私が狙っていたのは引かれる数値から引く数値が低い時0.675-0.648＝0.027の様にプラスになる時に > セル背景が赤くなる。と言う事でした。 どのセルの背景を赤くするのですか？0.648のセルですか？
間違いだったのであれば、訂正後のサンプルがどのようなものか示していただけますか？ TAKAさんが示していただいたものに修正をしてもらうのが、よいと思います。 2018/12/21(金) 10:16や 2018/12/21(金) 11:19で示していただいたものです。
======= 以下、（直接の回答ではないですが、参考になると思われる）関連した話をします。
要するに、割り算結果の大小が注目することですよね。 率は、各期の達成率とイメージしてもよいと思います。 すると、普通は、 「前記に比べて当期の達成率が低下していたら、当期の達成率の背景を赤にしましょう」 とか議論するじゃないですか。 下から引いてとか、上から引いてといった計算式レベルではなく、 もっと目的に沿った人にイメージし易い説明を先にしたほうが、 他人にも考え方の正否が判別しやすいと思います。 その説明を端折ると、重大な考え違いをしていました、ということになりやすいのです。
説明の際、色を付ける対象の期を基準として記述すると紛れがないと思います。 当期からみて〜〜だったとき、当期の色を・・・・とするといった書き方です。
# コードの回答をせずに注文ばかりしていて恐縮です。 # 私がアクセスできる時間が限られているものですから。 # それと、こうした質問は内容の確認を互いにしっかりすることが大切で、 # 労力の9割方はそれだと思うからです。
誤字等修正しました。 （γ） 2018/12/22(土) 10:43
（マナ） 2018/12/22(土) 17:34
修正してみました。
色のルールは全く理解できていませんので
そのままにしてあります。
Option Explicit
Sub test()
Dim i As Long
Dim rr As Range
Dim r1 As Range, r2 As Range
Columns("T").Interior.Pattern = xlNone
Columns("T").Font.Color = vbBlack
With Columns("R").SpecialCells(xlCellTypeConstants, xlNumbers).Offset(, 2)
.Formula = "=iferror(round(R2/S2,3),"""")"
.Value = .Value
End With
Set rr = Columns("T").SpecialCells(xlCellTypeConstants, xlNumbers)
Set r1 = Cells(Rows.Count, "T").End(xlUp)
For i = r1.Row - 1 To rr(1).Row Step -1
Set r2 = Cells(i, "T")
If r2.Value <> "" Then
If r1.Value > r2.Value Then
r2.Interior.Color = vbRed
ElseIf r1.Value = r2.Value Then
r1.Interior.Color = vbBlack
r2.Interior.Color = vbBlack
r1.Font.Color = vbWhite
r2.Font.Color = vbWhite
End If
Set r1 = r2
End If
Next i
Set r1 = rr(1)
For i = r1.Row + 1 To Cells(Rows.Count, "T").End(xlUp).Row
Set r2 = Cells(i, "T")
If r2.Value <> "" Then
If r2.Interior.Color <> vbRed Then
If r2.Value > r1.Value Then
r2.Interior.Color = vbBlue
End If
Set r1 = r2
End If
End If
Next i
End Sub
（マナ） 2018/12/23(日) 14:07
正確に表明されることが大切と申し上げただけですが、悲しいと言われても困ります。
こういうことだったのですね。
R列 S列 T列 T列の
1 下限 上限 下限/上限 背景色
2 1.2 1.5 0.8
3 1.5 2.2 0.682
4 1.6 2.4 0.667 黒
5
6 1.8 2.7 0.667 黒
7 2.4 3.7 0.649 赤 (0.649 < 0.675)
8
9 2.7 4 0.675 青 (0.675 > 0.667)
10 3.6 5.8 0.621
11 5.7 9.3 0.613
12 6 9.9 0.606 赤 (0.606 < 0.611)
13 8 13.1 0.611
14 10 16.6 0.602
私なりに言い換えると、 (1)翌期に上昇するなら、当期は赤とする。 (2)翌期＝当期なら、当期も翌期も黒とする。 (3)上昇前の直前の状態と比べて、当期が上回っていたら、当期は青とする。 (4)空白行が途中に入ることもあるが、その行は無いものとして上記を適用する。 というルールと推測される。
両者が重なる時もあるので、これも念のため確認が必要かも知れない。 T列の水準を整数で標語的に書くと、 2→1→2→3→3 となったとき、最初の3は青でも黒でもあるわけだが、 青が優先する（処理が後順位なので）と考えてもよいか。
ただ、私としてはよく理解できないところがある。 (1)翌期に上昇するなら、当期は赤とする。 からして不思議に思う。 当期はまだ上昇しているわけではないので、なぜ赤なのかと思う。 一方で、青は上昇した結果の時点のセルに着色しています。
今回の提示された仕様と離れて、 私がイメージしやすいのはこんな感じのものです。 (1)前期と比較して同値なら、 当期は「黒」とする。 (2)前期と比較して上昇、かつ直前の減少幅以内なら、当期は「黄」とする。 (3)直近の累計上昇幅が、直前の減少幅を超えるなら、当期は「赤」とする。 要するに、減少基調が基本であるが、上昇になった時点のセルに、 その上昇の程度に応じて背景色をつけるというものです。
==== いずれにせよ、こうした日本語の説明をお願いしたかったですね。 また、共通のテストデータがあったほうが双方にとって有益なので、 空白行も入ったデータを提供して頂きたかった。
==== さて、コードは皆さんから既に提示頂いています。
マナさんから提示頂いたコードですが、
' With Columns("R").SpecialCells(xlCellTypeConstants, xlNumbers).Offset(, 2)
' .Formula = "=iferror(round(R2/S2,3),"""")"
' .Value = .Value
' End With
空白行があるときに値の設定が上手くいかないように思います。
例えば、
Dim area As Range
For Each area In Columns("R").SpecialCells(xlCellTypeConstants, xlNumbers).Offset(, 2).Areas
area.FormulaR1C1 = "=IFERROR(ROUND(RC[-2]/RC[-1],3),"""")"
area.Value = area.Value
Next
などの方法が一例です。
（γ） 2018/12/24(月) 08:54
＞空白行があるときに値の設定が上手くいかないように思います。
書きながら、そうだろうなとは感じでいたのですが、
あとで、確認しようと思って忘れてしまいました。
投稿した後ですが、空白行については、
この文章が気になっていました。
＞補足としてデータの数は23行で25行間隔で2行目からデータが有ります。
（マナ） 2018/12/24(月) 09:15
（γ） 2018/12/24(月) 10:47
（γ） 2018/12/25(火) 07:33
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.