EXCELの条件付き書式について質問があります。
ある製品についてピボットテーブルで添付のような地域別の価格推移データを作成しました。
前月との価格動向が一目でイメージし易いよう条件付き書式で矢印設定できるのではないかと思いチャレンジしましたがうまく出来ません。やりたいイメージは添付例の赤丸部分のような表現ができればと思っています。前月との%比のフィールドを各月間に挿入できれば、%フィールドの値に対して条件付き書式で設定できるとも考えましたが、フィールドがやたらと増えてしまい大変です。なにかよい方法はないでしょうか?
A 回答 (6件)
- 最新から表示
- 回答順に表示
No.6
- 回答日時:
追伸:
一応、ファイルサンプルをアップロードして置きました。
本日、2015/8/16 20pmより、1週間で、最長で1週間、置いておきます。
実際のピボットテーブルではありませんが、ピボットテーブル等のテーブルを探すように作られていますが、実際に試したことはありません。
ダウンロードにつきましては、ファイルのプロパティで、ブロック解除を行ってください。そうしないと、開くことができません。
http://fast-uploader.com/file/6995278289730/
パスワードは、ここの7桁のログ番号にしています。
No.5
- 回答日時:
こんにちは。
>ただ当方、マクロに不慣れで折角公開いただいたコードをどうやって使えばよいのか分かりません。やはりマクロでないと無理なのでしょうか。
思った通りでしたが、以前、ご指摘いただいたように、絶対参照と、各々の具体的数値をいれないといけない、ということになると、手作業では、かなり手間のかかる作業になってしまいます。私の回答はあくまでも、マクロのみの対処を考えていますから、それ以外では、その手作業の手間に耐えるかどうかぐらいだと思います。
私としては、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
- 回答日時:
#3で、マクロコードを出しましたが、万が一、設定が分からないようでしたら、お知らせください。
いくつか方法があります。#3のコードを叩き台にして、修正すべき点は、直すことも可能ですが、レスポンスがなければ、そのままの状態で終わらせていただくこともあります。WindFaller様
大変お手間をお掛けさせております。ご返事遅くなり申し分かりません。
マクロコードを作成頂き恐れいります。ただ当方、マクロに不慣れで折角公開いただいたコードをどうやって使えばよいのか分かりません。やはりマクロでないと無理なのでしょうか。
No.3
- 回答日時:
こんばんは。
このまま、レスが着かないかもしれないと思って心配していました。回答側も、こういう新機能に関しては、思い切れないものがあります。
いずれにしても、本日中には、コードを出す予定にしていました。
>参照する前列のある特定のセル絶対参照となり、
それは、どうやら絶対参照にしないといけないようです。
まだ、使い勝手がよくないかとは思いますが、叩き台としてコードを出します。
ただ、まだまだ改良点はあるはずです。
もともと、ピボットテーブル(テーブル)上に使うものですから、範囲を選択するという設定は不要で、ボタンひとつで十分なのですが、現在はマウスで必要な部分を範囲選択するようにしています。
今回は、「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
- 回答日時:
追伸:
最初、前の数値に対する 2%で計算してみましたが、うまく行かないのです、それを、$ 0.02にしてみました。なんとなく落ち着くような気がします。よくご覧になってみてください。
WindFaller様
こんばんは。No.1,No.2とも、当法の例を用いた丁寧なご教示ありがとうございます。感謝感激です。
No.2のご回答例は当方のしたい事そのものです。が、No.1のご説明も読んだのですが実際にどうやってやるのかが当方には分かりませんでした。個別のセルについて条件付き書式を適用では、前列の数値を参照して「+0.02以上は上矢印」とかの指定まではできました。問題は列全体に条件付き書式を適用しようとすると、参照する前列のある特定のセル絶対参照となり、変な結果になってしまいます。どのように解決できるかご教示いただけると助かります。
No.1
- 回答日時:
こんにちは。
本日、この質問を眺めていました。
この手のものは手をつけたことがありませんでしたが、自分なりに考えてみました。
私の考え方は、個別の2つのセルに対して、下げ幅、上げ幅だけの判定を、値で指定して、マクロで書き込むという方法です。本来、全体に対する、下げ幅・上げ幅をパーセンテージで判定しているようですが、単に、数値化すればよいのですが、そこに、人為的な判断が含まれていると思うからです。(そうではないかもしれませんが、今は分かりません。)
本来、添付画像がなければ、そのまま出していたところですが、画像の中の以下の部分が気になりました。
中国の 2.42 ->2.43 は、横ばい矢印になっています。この判定を計算で可能か、今のマクロでは数値のみでは判定できないのです。おそらく、これは、人が判断したもののはずです。
添付の図は、そのイレギュラーな部分です。本来なら、同じ値が入れば、問題は何もないのですが、数値の幅の判定をどうやって決めるかということです。むろん、画像のような計数化することは問題はないのです。0.01 を足しているだけです。(等号記号等は、たぶん、そのままで行けるはずです)
問題は、0.01 を足さなくても、他は可能になっているということもあるのです。
たぶん、質問者さんは、ご自身の中で了解しているかもしれませんが、こちらでは、判断がつかないのです。
ある程度の自動化を目的とすなら、その判断を計数・数値化をしなくてはなりません。かと言って、しかし、あまり面倒な数値化を求められても、計画倒れになってしまいます。この点を、もしご理解いただけたら、アドバイスをください。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ExcelでASCを使って全角を半角...
-
今まで文字化けなく開けていたc...
-
Microsoft 365Excelの見開きペ...
-
Excel関数について教えてくださ...
-
マクロの処理が遅くなった
-
スプレッドシートで指定された...
-
エクセルの質問です。 F列からL...
-
Excel関数について教えてくださ...
-
エクセルのセル内に分数などの...
-
作成した数式を値として表示し...
-
ワークシートに出現したこの画...
-
条件付き書式設定で罫線を引き...
-
EXCELの散布図で日付が1900年に...
-
タイムスタンプとテキストから...
-
シートの情報を別のシートへま...
-
Excelでの文字色
-
【マクロ】VLOOKUPにて参照元に...
-
エクセルの文字が途中から消える
-
エクセルの数式バーのフォント...
-
エクセルでファイルの最終更新...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報