基于Excel 2013中上一个单元格的条件格式 [英] Conditional formatting based on the previous cell in Excel 2013

问题描述

我正在尝试使用箭头图标来表示得分的改善。由于我有多个列，因此每个单元格应该仅指示前一个单元格的斜率或下降。

如果我使用excel以数字顺序提供的一般格式：
20,30，40

Excel表示数字已经下降了30（向下指向箭头），但应该表示为增加。有人可以帮我吗？

谢谢你的时间，回顾和回答。

解决方案

有一种方法来应用基于其他单元格内容的条件格式。
这不会给你图标（如箭头），但是例如对单元格进行颜色。这可能对你来说很好。

条件格式化 - > 新规则 - > 使用公式确定要格式化的单元格

该图显示了您可能获得的示例（图标行如下所述）。

您必须在公式中使用相对引用。
检查

条件格式：显示系列的方向箭头

https://www.ablebits.com/office-addins-blog/2014/06/05/excel-conditional-formatting-icon-sets-data-bars-color-scales/ ，go到示例2.根据另一个单元格的值添加图标。

I am trying to indicate improvements in the scores with the icon set of arrows. Since I have multiple columns each cell should be indicating incline or decline based only on previous cell.

If I use a normal formatting provided by excel in the number order: 20,30, 40

Excel indicates that decline has been made with number 30 (arrow pointing downwards) however it should be indicated as increase. Can someone please help me?

thank you for your time, review and answer.

解决方案

There is a way to apply a conditional format based on other cells contents. This does not give you icons (as arrows), but colors the cells, for instance. It might be good enough for you.

Conditional Formatting -> New Rule -> Use a formula to determine which cells to format

The figure shows an example of what you may obtain (the icons row is described below).

It is essential that you use relative referencing in the formula. Check this answer.

There are other options which involve creating additional helper cells, e.g., row 4 in the figure. The amount of extra effort, and whether it is ok for you, depends on the size and structure of your worksheet.

For the row of icons, I used something similar to 2 and 3 below. Enter in C4 the formula =IF(C3>B3,3,IF(C3=B3,2,1)) and copy to the right. Then apply the Conditional Formatting -> Icon Sets, and select Show Icon Only.

Check

1. https://superuser.com/questions/591120/excel-conditional-formatting-using-icon-sets
2. Conditional formatting: Showing direction arrows for a series
3. https://www.ablebits.com/office-addins-blog/2014/06/05/excel-conditional-formatting-icon-sets-data-bars-color-scales/, go to Example 2. Add icons based on another cell's value.

这篇关于基于Excel 2013中上一个单元格的条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！