如何添加上方平均值条件格式
Contents
[
Hide
]
可能的使用场景
在Microsoft Excel或Google Sheets等工具中使用上方平均值条件格式,是一种快速且直观的突出显示数据的方式,特别是突出高于平均值的范围内的值。以下是原因:
- 快速识别趋势:帮助你瞬间找到表现出色的值,无需手动计算平均值或扫描数字。
- 简化数据分析:无需计算或输入公式,这是应用逻辑条件格式的自动方式,节省时间。
- 增强视觉效果:色彩编码帮助使你的电子表格更易阅读,也更具视觉吸引力,尤其在演示时。
- 支持决策:快速识别高于平均值的数值可以推动行动,例如奖励表现优异的员工或调查为何某些产品表现优越。
如何使用Excel添加高于平均值的条件格式
以下是分步骤在Excel中添加高于平均值的条件格式的方法:
- 选择要应用格式的单元格区域。例如:A1:A20。
- 转到功能区的首页标签。
- 在样式组中点击“条件格式”。
- 浮动到“顶部/底部规则”。
- 选择“高于平均值…”
- 出现的对话框会自动检测“格式化高于平均值的单元格”。你可以通过点击“与…”旁边的下拉菜单来更改格式样式(例如选择填充颜色或自定义格式)。
- 点击“确定”。你选择的区域中高于该区域平均值的所有单元格将被高亮显示。
如何使用Aspose.Cells for .NET添加高于平均值的条件格式
Aspose.Cells完全支持在运行时对XLSX格式的Microsoft Excel 2007及更高版本提供的条件格式。这个例子展示了用于不同属性集的高于平均值条件格式的操作。
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
private void TestAboveAverage() | |
{ | |
// Instantiate a workbook object | |
Workbook book = new Workbook(); | |
// Create a worksheet object and get the first worksheet | |
Worksheet _sheet = book.Worksheets[0]; | |
AddAboveAverage(_sheet); | |
AddAboveAverage2(_sheet); | |
AddAboveAverage3(_sheet); | |
book.Save(filePath + "AboveAverage.xlsx"); | |
} | |
// This method implements the AboveAverage conditional formatting type. | |
private void AddAboveAverage(Worksheet _sheet) | |
{ | |
FormatConditionCollection conds = GetFormatCondition("A11:C12", Color.Tomato, _sheet); | |
int idx = conds.AddCondition(FormatConditionType.AboveAverage); | |
FormatCondition cond = conds[idx]; | |
cond.Style.BackgroundColor = Color.Pink; | |
cond.Style.Pattern = BackgroundType.Solid; | |
} | |
// This method implements an AboveAverage conditional formatting type with some custom attributes. | |
private void AddAboveAverage2(Worksheet _sheet) | |
{ | |
FormatConditionCollection conds = GetFormatCondition("A13:C14", Color.Empty, _sheet); | |
int idx = conds.AddCondition(FormatConditionType.AboveAverage); | |
FormatCondition cond = conds[idx]; | |
cond.AboveAverage.IsAboveAverage = false; | |
cond.AboveAverage.IsEqualAverage = true; | |
cond.Style.BackgroundColor = Color.Pink; | |
cond.Style.Pattern = BackgroundType.Solid; | |
} | |
// This method implements an AboveAverage conditional formatting type with some custom attributes. | |
private void AddAboveAverage3(Worksheet _sheet) | |
{ | |
FormatConditionCollection conds = GetFormatCondition("A15:C16", Color.Empty, _sheet); | |
int idx = conds.AddCondition(FormatConditionType.AboveAverage); | |
FormatCondition cond = conds[idx]; | |
cond.AboveAverage.IsAboveAverage = false; | |
cond.AboveAverage.IsEqualAverage = true; | |
cond.AboveAverage.StdDev = 3; | |
cond.Style.BackgroundColor = Color.Pink; | |
cond.Style.Pattern = BackgroundType.Solid; | |
} | |
// This method adds formatted conditions. | |
private FormatConditionCollection GetFormatCondition(string cellAreaName, Color color, Worksheet _sheet) | |
{ | |
// Adds an empty conditional formattings | |
int index = _sheet.ConditionalFormattings.Add(); | |
// Get the formatted conditions | |
FormatConditionCollection formatConditions = _sheet.ConditionalFormattings[index]; | |
// Get the cell area calling the custom GetCellAreaByName method | |
CellArea area = GetCellAreaByName(cellAreaName); | |
// Add the formatted conditions cell area. | |
formatConditions.AddArea(area); | |
// Call the custom FillCell method | |
FillCell(cellAreaName, color, _sheet); | |
// Return the formatted conditions | |
return formatConditions; | |
} | |
// This method specifies the cell shading color for the conditional formattings cellarea range. | |
private void FillCell(string cellAreaName, Color color, Worksheet _sheet) | |
{ | |
CellArea area = GetCellAreaByName(cellAreaName); | |
int k = 0; | |
for (int i = area.StartColumn; i <= area.EndColumn; i++) | |
{ | |
for (int j = area.StartRow; j <= area.EndRow; j++) | |
{ | |
Cell c = _sheet.Cells[j, i]; | |
if (!color.IsEmpty) | |
{ | |
Style s = c.GetStyle(); | |
s.ForegroundColor = color; | |
s.Pattern = BackgroundType.Solid; | |
c.SetStyle(s); | |
} | |
// Set some random values to the cells in the cellarea range | |
int value = j + i + k; | |
c.PutValue(value); | |
k++; | |
} | |
} | |
} | |
// This method specifies the CellArea range (start row, start col, end row, end col etc.) | |
// For the conditional formatting | |
internal static CellArea GetCellAreaByName(string s) | |
{ | |
CellArea area = new CellArea(); | |
string[] strCellRange = s.Replace("$", "").Split(':'); | |
int column; | |
CellsHelper.CellNameToIndex(strCellRange[0], out area.StartRow, out column); | |
area.StartColumn = column; | |
if (strCellRange.Length == 1) | |
{ | |
area.EndRow = area.StartRow; | |
area.EndColumn = area.StartColumn; | |
} | |
else | |
{ | |
CellsHelper.CellNameToIndex(strCellRange[1], out area.EndRow, out column); | |
area.EndColumn = column; | |
} | |
return area; | |
} |