VBA Excel的比例条件要求 [英] Ratio Conditional Requirements for VBA Excel

问题描述

我有一个财务数据，表明项目的健康状况，并根据差异填充一个颜色和一个字母来表示健康。例如，如果varinace小于5％，则填充颜色绿色，字母G，> 5％但小于20％填充Y和> 20％=R。我使用if语句来得到这个。但是我需要填写整个项目的颜色。

有5个项目，这些都是要求：

1. 如果5个项目中的任何一个都具有R，则整体健康状况为R。


2. 如果1/5项目为Y和4/5 =G，那么整体健康状况为G


3. 如果2/5 =Y和3/5G，则整体健康状况为Y


4. 如果3/5 =Y和2/5G，则整体健康状况为Y


5. 如果4/5 =Y和1 / 5G然后Y


6. 如果5/5 =Y，那么Y


7. 如果5/5 =G 然后G

所以，如果第一个项目是G，第二个项目是Y第三个项目是Y，4个项目是G和第五个项目是RI希望它为了整体健康而R。

我已经尝试使用if语句，但是我要做的组合的数量太多了。

如何使用VBA excel创建这种比率要求？

谢谢

解决方案

您可以创建一个变量来计算每个Y，R和G，然后根据您的规则，简单地设置一个等于r，y或g的单元格：

  Option Explicit 
 
 Sub getcounts（）
 
 Dim g As Long 
 Dim r As Long 
 Dim y As Long 
 Dim ovrallhealth As Range 
 Dim variancerange As Range 
 Set variancerange = Sheets（1）.Range（A1：A5）
设置ovrallhealth = sheet（1）.Range（B1）
 
y = Application.WorksheetFunction.CountIf（variancerange，y）
g = Application.WorksheetFunction.CountIf（variancerange，g） 
r = Application.WorksheetFunction.CountIf（variancerange，r）
 
如果r> 0然后
 ovrallhealth =R
 ElseIf y = 1然后g = 4然后
 ovrallhealth =G
 ElseIf y = 2然后g = 3然后
 ovrallhealth =Y
 ElseIf y = 3然后g = 2然后
 ovrallhealth =Y
 ElseIf y = 4然后g = 1然后
 ovrallhealth =Y
 ElseIf y = 5然后
 ovrallhealth =Y
 ElseIf g = 5然后
 ovrallhealth =G
结束如果
 
结束Sub 
  

I have a financial data that indicates the health of the project and depending on the variance it populates a color and a letter to indicate the health. For example, if the varinace is less than 5% it populates color green with letter "G", >5% but less than 20% populates "Y" and >20% = "R". I've used the if statements to get that. however I need to populate a color of overall projects.

There are 5 projects and these are the requirements:

1. If any of the 5 projects have "R" then the overall health is "R"
2. If 1/5 project is "Y" and 4/5 = "G" then the overall health is "G"
3. If 2/5 = "Y" and 3/5 "G" then the overall health is "Y"
4. If 3/5 = "Y"and 2/5 "G" then overall health is "Y"
5. If 4/5 = "Y" and 1/5 "G" Then "Y"
6. If 5/5 = "Y" then "Y"
7. If 5/5 = "G" then "G"

So, if 1st project is G, 2nd project is Y 3rd project is Y, 4 Project is G and 5th project is R I want it to popoulate "R" for overall health.

I've tried using if statements but the amount of combination i have to do is too much.

How do i use VBA excel to create this kind of "ratio" requirements?

Thank you

解决方案

You can create a variable for that counts each Y, R, and G and then do a simple if elseif to set a cell equal to r, y, or g based on your rules:

Option Explicit

Sub getcounts()

Dim g As Long
Dim r As Long
Dim y As Long
Dim ovrallhealth As Range
Dim variancerange As Range
Set variancerange = Sheets(1).Range("A1:A5")
Set ovrallhealth = Sheets(1).Range("B1")

y = Application.WorksheetFunction.CountIf(variancerange, "y")
g = Application.WorksheetFunction.CountIf(variancerange, "g")
r = Application.WorksheetFunction.CountIf(variancerange, "r")

If r > 0 Then
   ovrallhealth = "R"
ElseIf y = 1 And g = 4 Then
   ovrallhealth = "G"
ElseIf y = 2 And g = 3 Then
   ovrallhealth = "Y"
ElseIf y = 3 And g = 2 Then
   ovrallhealth = "Y"
ElseIf y = 4 And g = 1 Then
   ovrallhealth = "Y"
ElseIf y = 5 Then
   ovrallhealth = "Y"
ElseIf g = 5 Then
   ovrallhealth = "G"
End If

End Sub

这篇关于VBA Excel的比例条件要求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！