[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『条件付き書式で色を設定( VBA可 )』（あみな）
いつもお世話になります(o_ _)
複数の飛び飛びのセルから、 最小値を除外して最大値の標準化変量を算出し、
最大値の標準化変量が1.2倍以上なら、条件付き書式で色を設定したいです
宜しくお願い致します。
尚、VBAでも可でしたら、ご教授いただければ嬉しいです。
|[A]
[1]| 65
[2]|
[3]| 45
[4]|
[5]| 51
[6]|
[7]| 72 ← 色を付ける
[8]|
[9]| 59
【 補足 】
※範囲は固定で数値は5個です
※範囲の数値は、1以上が原則です
※下記の式はイメージです
・最小値を検出する
式1=IF(MIN(A1,A3,A5,A7,A9),MIN(A1,A3,A5,A7,A9),LARGE((A1,A3,A5,A7,A9),RANK(0,(A1,A3,A5,A7,A9))-1))
・平均から最小値を減算し平均値を算出する
式2=SUM(SUM(A1,A3,A5,A7,A9)-IF(MIN(A1,A3,A5,A7,A9),MIN(A1,A3,A5,A7,A9),LARGE((A1,A3,A5,A7,A9),RANK(0,(A1,A3,A5,A7,A9))-1)))/4
・最大値を検出する
式3=MAX(A1,A3,A5,A7,A9)
・最小値を除いた標準偏差を算出する
式4
=STDEV.P(LARGE((A1,A3,A5,A7,A9),1),LARGE((A1,A3,A5,A7,A9),2),LARGE((A1,A3,A5,A7,A9),3),LARGE((A1,A3,A5,A7,A9),4))
入力後、ShiftキーとCtrlキーを押しながらEnterキーで式を確定（確定後、式が{}で囲まれ配列数式に)
最大値の標準化変量を算出する
式5=STANDARDIZE(式3参照, 式2参照, 式4参照)
式1=45
式2=61.75
式3=72
式4=7.725769…
式5=1.326729…
＜ 使用 Excel：Excel2021、使用 OS：Windows11 ＞
それだと最大値ならの条件になりますので
最大値...なおかつ条件の、最小値を除外して最大値の標準化変量が1.2倍以上ならが必要になります
よろしくお願いします。
（あみな） 2023/03/14(火) 12:00:24
（ami） 2023/03/14(火) 12:07:21
|[A]|[B] |[C]
[1]| 65|変化⇒| 65
[2]| | |
[3]| 45| | 45
[4]| | |
[5]| 51| | 68
[6]| | |
[7]| 72| | 72
[8]| | |
[9]| 59| | 54
A列の範囲の数値が変化して、右[C列]に変化したとしたら
最大値の標準化変量は下がって、1.08になるので
72の値に色が付いては困るのです。（ ; ; ）
（あみな） 2023/03/14(火) 12:38:45
あまりよく分ってませんけど.... ユーザー定義関数を使っていいなら、以下でどうでしょう？
Function STANDARDIZEex(rng As Range, Optional zoom# = 1.2)
Dim minV#, maxV#, aveV#, stdevV#, stde#
minV = Application.Evaluate("MIN(FILTER(" & rng.Address(External:=True) & ",(ISNUMBER(" & rng.Address(External:=True) & ")*(" & rng.Address(External:=True) & "<>0))))")
maxV = Application.Evaluate("MAX(FILTER(" & rng.Address(External:=True) & ",ISNUMBER(" & rng.Address(External:=True) & ")))")
aveV = Application.Evaluate("LET(_c,COUNTIF(" & rng.Address(External:=True) & "," & minV & "),_c0,COUNTIF(" & rng.Address(External:=True) & ",0),(SUM(" & rng.Address(External:=True) & ")-" & minV & "*_c)/(COUNT(" & rng.Address(External:=True) & ")-_c+_c0))")
stdevV = Application.Evaluate("LET(LIST,FILTER(" & rng.Address(External:=True) & ",(" & rng.Address(External:=True) & "<>0)*(" & rng.Address(External:=True) & "<>" & minV & ")*(ISNUMBER(" & rng.Address(External:=True) & "))),STDEV.P(LIST))")
stde = (maxV - aveV) / stdevV
If stde >= zoom Then
STANDARDIZEex = maxV
Else
STANDARDIZEex = CVErr(xlErrNA)
End If
End Function
条件式：=A1=STANDARDIZEex($A$1:$A$9) （まる） 2023/03/14(火) 14:00:41
条件付き書式に配列は使えなかったきがするから、平均と標準偏差は別のセルに出しておいて 条件付き書式はSTANDARDIZEだけにしたらどうでしょう？ B11=AVERAGEIF(A1:A9,">"&MIN(A1:A9)) B12=STDEV.P(IF(A1:A9>MIN(A1:A9),A1:A9,""))’配列数式 A1:A9を選択して、条件付き書式 =STANDARDIZE(A1,$B$11,$B$12)>1.2
|[A] |[B]
[1] | 65|
[2] | |
[3] | 45|
[4] | |
[5] | 51|
[6] | |
[7] | 72|
[8] | |
[9] | 59|
[10]| |
[11]|平均 | 61.75
[12]|標準偏差|7.725769
（稲葉） 2023/03/14(火) 14:08:22
（まる）さんへ
ユーザー定義関数を使えばですね…全く忘れていました。
ここで誰かが言ってた、伝家の宝刀 Evaluateでしたか
う～ん、Evaluate が良く理解していないので勉強してきます。
（稲葉）さんへ
あっ～なるほどです、別のセルを参照して条件付き書式を入れるのですね。
いっぺんにしようと検討してましたが、ややこしくてどうしたものかと
思っておりました。数式ではお手上げで...
ちなみに、自分でお粗末ながらVBAで今作成しましたが
ボタンをいちいち押さないといけないのが手間なので
いやだな～と感じておりました。
Sub 最大値の標準化変量に応じて色付け()
Dim ws As Worksheet
Dim r As Range, data As Range
Dim n(1 To 4) As Long
Dim v As String
Dim 式2 As Double, 式3 As Double, 式4 As Double, 式5 As Double
Set ws = Sheets(1)
Set data = ws.Range("A1,A3,A5,A7,A9")
'' 最大値を検出する
式3 = Application.Max(data)
'' 最小値を除いた標準偏差を算出
n(1) = Application.Large(data, 1)
n(2) = Application.Large(data, 2)
n(3) = Application.Large(data, 3)
n(4) = Application.Large(data, 4)
式4 = Application.StDevP(n(1), n(2), n(3), n(4))
'' Debug.Print 式4; '' 7.72576857018122
'' 最小値を除いた平均値を算出
式2 = Application.Sum(n(1), n(2), n(3), n(4)) / 4
'' Debug.Print 式2 '' 61.75
'' 最大値の標準化変量を算出
式5 = Application.Standardize(式3, 式2, 式4)
'' Debug.Print 式5 '' 1.326...
For Each r In data
If r.Value = Application.Large(data, 1) Then
If 式5 >= 1.2 Then
'' Debug.Print r.Address
v = r.Address
ws.Range(v).Font.Color = vbBlue
Exit For
End If
End If
Next r
End Sub
どちらかの方法を活用させていただきます。
もしまた疑問が出たら、質問させていただきます。
本当にありがとうございました。
（あみな） 2023/03/14(火) 15:01:23
＞ボタンをいちいち押さないといけないのが手間なので
Cangeイベントでいいんじゃないかしら？
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ave As Variant
Dim Std As Variant
Dim r As Range
If Intersect(Target, Range("A1:A9")) Is Nothing Then Exit Sub
Ave = [=AVERAGEIF(A1:A9,">"&MIN(A1:A9))]
Std = [=STDEV.P(INDEX(IF(A1:A9>MIN(A1:A9),A1:A9,""),))]
With Range("A1:A9")
.Interior.Color = xlNone
For Each r In .Cells
If (r.Value - Ave) / Std > 1.2 Then
r.Interior.Color = rgbBlue
End If
Next r
End With
End Sub
（稲葉） 2023/03/14(火) 15:28:41
誤：Std = [=STDEV.P(INDEX(IF(A1:A9>MIN(A1:A9),A1:A9,""),))] 正：Std = [=STDEV.P(IF(A1:A9>MIN(A1:A9),A1:A9,""))] ごめんなさい、余計な関数入ってました （稲葉） 2023/03/14(火) 15:34:01
>最大値の標準化変量が1.2倍以上なら、条件付き書式で色を設定したいです
これを見落としていました。
式5の値を使用する
|[A]|[B]|[C]|[D]
[1]| 65| |式1| 45
[2]| | |式2| 64.75
[3]| 45| |式3| 72
[4]| | |式4|6.684871
[5]| 68| |式5|1.084539
[6]| | | |
[7]| 72| | |
[8]| | | |
[9]| 54| | |
条件書式
=AND($D$5>=1.2,A1=MAX($A$1:$A$9))
（ami） 2023/03/14(火) 16:22:41
イベントで良いですね。選択肢が多くて...どれに
しようか贅沢に悩んでます。(笑)
＞誤：Std = [=STDEV.P(INDEX(IF(A1:A9>MIN(A1:A9),A1:A9,""),))]
＞正：Std = [=STDEV.P(IF(A1:A9>MIN(A1:A9),A1:A9,""))]
因みに、どちらでもちゃんと動いてますね。
（あみな） 2023/03/14(火) 18:08:02
名前の定義で配列数式使えないから、配列のように動く数式を検討していた時の名残です・・・ すみません。
（稲葉） 2023/03/14(火) 18:11:29
条件書式の数式に入れました。
数式へ=AND($D$5>=1.2,A1=MAX($A$1:$A$9))
ちゃんと、色が付いております。
ありがとうございます。
因みに独り言を…
式4ですがわざわざ配列にしなくても返りましたｗ (ﾉ∀`*)ﾍﾟﾁ
（あみな） 2023/03/14(火) 18:19:37
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.