条件付き書式で前月（前列）との増減の増減を設定できますか？

EXCELの条件付き書式について質問があります。
ある製品についてピボットテーブルで添付のような地域別の価格推移データを作成しました。
前月との価格動向が一目でイメージし易いよう条件付き書式で矢印設定できるのではないかと思いチャレンジしましたがうまく出来ません。やりたいイメージは添付例の赤丸部分のような表現ができればと思っています。前月との％比のフィールドを各月間に挿入できれば、％フィールドの値に対して条件付き書式で設定できるとも考えましたが、フィールドがやたらと増えてしまい大変です。なにかよい方法はないでしょうか？

No.6 回答者： WindFaller 回答日時： 2015/08/16 22:13

追伸：

一応、ファイルサンプルをアップロードして置きました。
本日、2015/8/16 20pmより、1週間で、最長で1週間、置いておきます。
実際のピボットテーブルではありませんが、ピボットテーブル等のテーブルを探すように作られていますが、実際に試したことはありません。

ダウンロードにつきましては、ファイルのプロパティで、ブロック解除を行ってください。そうしないと、開くことができません。

http://fast-uploader.com/file/6995278289730/
パスワードは、ここの7桁のログ番号にしています。

No.5 回答者： WindFaller 回答日時： 2015/08/16 12:10

こんにちは。

>ただ当方、マクロに不慣れで折角公開いただいたコードをどうやって使えばよいのか分かりません。やはりマクロでないと無理なのでしょうか。

思った通りでしたが、以前、ご指摘いただいたように、絶対参照と、各々の具体的数値をいれないといけない、ということになると、手作業では、かなり手間のかかる作業になってしまいます。私の回答はあくまでも、マクロのみの対処を考えていますから、それ以外では、その手作業の手間に耐えるかどうかぐらいだと思います。

私としては、VBAマクロをご希望でない方や、まったく使ったことのない方には、こちらから、あえて、お勧めするということは、いたしておりません。

goohirakiyo様では、「どうやって使えば」ということもあるかもしれませんが、今回は、マクロ自体を改良して、同じシート内のしかるべきテーブルを探して、矢印を入れるようになっていますから、（フォームコントールの)ボタンに、マクロ名(MainMacro2)を登録していただければ、とは思っています。

今後については、ご判断にお任せすることにいたします。

'//
Sub MainMacro2()
　Dim i As Integer
　Dim j As Integer
　Dim Rng As Range
　Dim myTables As Object
　Dim t As Variant
　Dim o, l, r As Range
　Dim c As Range
　i = ActiveSheet.PivotTables.Count
　j = ActiveSheet.ListObjects.Count
　If i > 0 And j > 0 Then
　　MsgBox "ピボットテーブルと他のテーブルとの混在は考えていませんので、" _
　　& "片方ずつ処理してください。", vbInformation
　　Exit Sub
　 ElseIf i > 0 Then
　　　Set myTables = ActiveSheet.PivotTables
　 ElseIf j > 0 Then
　　　Set myTables = ActiveSheet.ListObjects
　 End If
　If i = 0 And j = 0 Then MsgBox "どのオブジェクトにも該当しませんでした。", vbExclamation: Exit Sub
　For Each t In myTables
　Set r = t.DataBodyRange
　r.FormatConditions.Delete
　Application.ScreenUpdating = False
　For Each c In r
　　If VarType(c.Value) = vbDouble Then
　　　Call FormatCondMacro(c)
　　End If
　Next c
　Next t
　Application.ScreenUpdating = True
End Sub
Sub FormatCondMacro(Rng As Range)
　Dim myVal As Variant
　Dim myVal2 As Variant
　Dim i As Long
　Dim ratio As Double
　Dim flg As Boolean
　With Rng
　　If Rng.Column = 1 Then Exit Sub
　　myVal = .Offset(, -1).Value
　　myVal2 = .Value
　　If myVal = 0 Or myVal = "" Then Exit Sub
　　If myVal2 = 0 Or myVal2 = "" Then Exit Sub
　　If VarType(myVal) <> vbDouble Or VarType(myVal2) <> vbDouble Then Exit Sub
　　'浮動小数点型誤差のために補正
　　If myVal - Int(myVal) <> 0 Then myVal = Format(myVal, "0.00")
　　If myVal2 - Int(myVal2) <> 0 Then myVal2 = Format(myVal2, "0.00")
　　' ratio = Int(myVal * 0.02 * 100) / 100
　　 ratio = 0.02
　　 If Abs(CDbl(myVal) - myVal2) > ratio Then flg = True
　　
　　.FormatConditions.Delete
　　.FormatConditions.AddIconSetCondition
　　.FormatConditions(.FormatConditions.Count).SetFirstPriority
　　With .FormatConditions(1)
　　　.ReverseOrder = False
　　　.ShowIconOnly = False
　　　.IconSet = ActiveWorkbook.IconSets(xl5Arrows)
　　End With
　　　For i = 2 To 5
　　　　With .FormatConditions(1).IconCriteria(i)
　　　　　.Type = xlConditionValueNumber
　　　　　If flg Then
　　　　　.Value = myVal
　　　　　Else
　　　　　.Value = myVal + (i - 3) * ratio
　　　　　End If
　　　　　.Operator = 5 - CInt(i < 4) * 2
　　　　End With
　　　 Next i
　End With
End Sub
'///

No.4 回答者： WindFaller 回答日時： 2015/08/13 19:34

#3で、マクロコードを出しましたが、万が一、設定が分からないようでしたら、お知らせください。

いくつか方法があります。#3のコードを叩き台にして、修正すべき点は、直すことも可能ですが、レスポンスがなければ、そのままの状態で終わらせていただくこともあります。

この回答へのお礼

WindFaller様
大変お手間をお掛けさせております。ご返事遅くなり申し分かりません。
マクロコードを作成頂き恐れいります。ただ当方、マクロに不慣れで折角公開いただいたコードをどうやって使えばよいのか分かりません。やはりマクロでないと無理なのでしょうか。

お礼日時：2015/08/15 22:05

No.3 回答者： WindFaller 回答日時： 2015/08/09 21:55

こんばんは。

このまま、レスが着かないかもしれないと思って心配していました。回答側も、こういう新機能に関しては、思い切れないものがあります。
いずれにしても、本日中には、コードを出す予定にしていました。

>参照する前列のある特定のセル絶対参照となり、
それは、どうやら絶対参照にしないといけないようです。
まだ、使い勝手がよくないかとは思いますが、叩き台としてコードを出します。

ただ、まだまだ改良点はあるはずです。
もともと、ピボットテーブル（テーブル)上に使うものですから、範囲を選択するという設定は不要で、ボタンひとつで十分なのですが、現在はマウスで必要な部分を範囲選択するようにしています。

今回は、「ratio」 という部分で考えこんでしまいました。当面、私のところでは、ここが解決できませんでした。2%か$0.02なのか、それとも、全体（一応、プログラム上では、その行のみ)に対する割合の上がり幅・下がり幅で出すことは可能ではないかと思いますが、そこら辺の計算式は、質問者のgoohirakiyo様のほうが、お詳しいかと思います。

また、注意点は、比べる数字が左隣り（－1列)にない場合は、何も出ません。言い換えると、非表示列がある場合は、見かけ上、隣り合っても、現行では計算できません。

'標準モジュールに貼り付けます。
'//
Sub MainMacro()
Dim c As Range
If TypeName(Selection) <> "Range" Then
　 MsgBox "範囲を選択してください。", vbExclamation
　 Exit Sub
End If
For Each c In Selection
　If VarType(c.Value) = vbDouble Then
　　FormatCondMacro c
　End If
Next
End Sub
Sub FormatCondMacro(rng As Range)
　Dim myVal As Variant
　Dim myVal2 As Variant
　Dim i As Long
　Dim ratio As Double
　Dim flg As Boolean
　With rng
　　If rng.Column = 1 Then Exit Sub
　　myVal = .Offset(, -1).Value
　　myVal2 = .Value
　　If myVal = 0 Or myVal = "" Then Exit Sub
　　If myVal2 = 0 Or myVal2 = "" Then Exit Sub
　　If VarType(myVal) <> vbDouble Or VarType(myVal2) <> vbDouble Then Exit Sub
　　'浮動小数点型誤差のために補正
　　If myVal - Int(myVal) <> 0 Then myVal = Format(myVal, "0.00")
　　If myVal2 - Int(myVal2) <> 0 Then myVal2 = Format(myVal2, "0.00")
　　'' ratio = Int(myVal * 0.02 * 100) / 100　'2%
　　 ratio = 0.02　''$0.02 差額
　　 If Abs(CDbl(myVal) - myVal2) > ratio Then flg = True
　　
　　.FormatConditions.Delete
　　.FormatConditions.AddIconSetCondition
　　.FormatConditions(.FormatConditions.Count).SetFirstPriority
　　With .FormatConditions(1)
　　　.ReverseOrder = False
　　　.ShowIconOnly = False
　　　.IconSet = ActiveWorkbook.IconSets(xl5Arrows)
　　End With
　　　For i = 2 To 5
　　　　With .FormatConditions(1).IconCriteria(i)
　　　　　.Type = xlConditionValueNumber
　　　　　If flg Then
　　　　　.Value = myVal
　　　　　Else
　　　　　.Value = myVal + (i - 3) * ratio
　　　　　End If
　　　　　.Operator = 5 - CInt(i < 4) * 2
　　　　End With
　　　 Next i
　End With
End Sub
'///

No.2 回答者： WindFaller 回答日時： 2015/08/08 14:43

追伸：

最初、前の数値に対する 2%で計算してみましたが、うまく行かないのです、それを、$ 0.02にしてみました。なんとなく落ち着くような気がします。よくご覧になってみてください。

この回答へのお礼

WindFaller様
こんばんは。No.1,No.2とも、当法の例を用いた丁寧なご教示ありがとうございます。感謝感激です。
No.2のご回答例は当方のしたい事そのものです。が、No.1のご説明も読んだのですが実際にどうやってやるのかが当方には分かりませんでした。個別のセルについて条件付き書式を適用では、前列の数値を参照して「+0.02以上は上矢印」とかの指定まではできました。問題は列全体に条件付き書式を適用しようとすると、参照する前列のある特定のセル絶対参照となり、変な結果になってしまいます。どのように解決できるかご教示いただけると助かります。

お礼日時：2015/08/09 19:07

No.1 回答者： WindFaller 回答日時： 2015/08/08 13:23

こんにちは。

本日、この質問を眺めていました。
この手のものは手をつけたことがありませんでしたが、自分なりに考えてみました。

私の考え方は、個別の2つのセルに対して、下げ幅、上げ幅だけの判定を、値で指定して、マクロで書き込むという方法です。本来、全体に対する、下げ幅・上げ幅をパーセンテージで判定しているようですが、単に、数値化すればよいのですが、そこに、人為的な判断が含まれていると思うからです。（そうではないかもしれませんが、今は分かりません。)

本来、添付画像がなければ、そのまま出していたところですが、画像の中の以下の部分が気になりました。
中国の 2.42 ->2.43 は、横ばい矢印になっています。この判定を計算で可能か、今のマクロでは数値のみでは判定できないのです。おそらく、これは、人が判断したもののはずです。

添付の図は、そのイレギュラーな部分です。本来なら、同じ値が入れば、問題は何もないのですが、数値の幅の判定をどうやって決めるかということです。むろん、画像のような計数化することは問題はないのです。0.01 を足しているだけです。（等号記号等は、たぶん、そのままで行けるはずです)
問題は、0.01 を足さなくても、他は可能になっているということもあるのです。

たぶん、質問者さんは、ご自身の中で了解しているかもしれませんが、こちらでは、判断がつかないのです。
ある程度の自動化を目的とすなら、その判断を計数・数値化をしなくてはなりません。かと言って、しかし、あまり面倒な数値化を求められても、計画倒れになってしまいます。この点を、もしご理解いただけたら、アドバイスをください。