列Excel中同一日期范围的不同颜色 [英] Different color of same date range in a column Excel

问题描述

如果两个或更多的日期是相同的，我已经离开了经理我想要颜色不同
i am使用这个公式

  = OR（SUMPRODUCT（（B6  - ））*（B6> = INDIRECT（Table1 [Start]））*（B6 <= INDIRECT（Table1 [End]））），SUMPRODUCT（（B6< ;（B6> = INDIRECT（Table1 [S1]））*（B6 <= INDIRECT（Table1 [E1]））），SUMPRODUCT（（B6  - B6 = INDIRECT（Table1 [S2]））*（B6 <= INDIRECT（Table1 [E2]））），SUMPRODUCT（（B6  - （B6> = INDIRECT（Table1 [S4]））*（B6< = INDIRECT（Table1 [E3]））） ; = INDIRECT（Table1 [E4]））），SUMPRODUCT（（B6  - ））*（B6> = INDIRECT（Table1 [S5]））*（B6 <= INDIRECT ]））））
  

解决方案

您想要的所有列的公式是：

  = 1< SUM（COUNTIFS（INDIRECT（Table1 [Start]），< =& B6，INDIRECT（Table1 [End]），> =& B6），COUNTIFS（INDIRECT SD1]），< =& B6，INDIRECT（Table1 [ED1]），> =& B6），...），0）
  

哪里的 ... 是更多的分数数据的数目

---

但是这并不是将Excel的功能用于他们的潜力。正如我在评论中所说，较少的列允许使用相同的功能，具有更短和更准确的公式。

计数列中的公式为：

  = IF（Z5 < >，COUNTIF（$ Z $ 5：$ Z5，Z5），）
  

可以在此列上放置条件格式，以突出显示他们达到8最大值：

  = AC5> = 8 
  

然后日历中的条件格式将是：

对于重叠：

  = 1< COUNTIFS（INDIRECT（Table3 [Start]），< =& B6，INDIRECT（Table3 [End]），> =& B6）
  

对于员工：

  = SUMPRODUCT （INDIRECT（Table3 [Names]）=abc）*（INDIRECT（Table3 [Start]）< = B6）*（INDIRECT（Table3 [End]）> = B6））
  

再次更改雇员的姓名。

将数据格式化为表，引用将增加以适应新条目。由于该公式需要雇员名称，如果该雇员有1或100个条目，则它将始终是相同的颜色。

此外，还允许一个人使用表中固有的过滤器，用于对员工和日期进行排序和过滤。

I have leave manager i want to color different if two or more dates are same i am using this formula

=OR(SUMPRODUCT((B6<>"")* (B6>=INDIRECT("Table1[Start]"))*(B6<=INDIRECT("Table1[End]"))) ,SUMPRODUCT((B6<>"")* (B6>=INDIRECT("Table1[S1]"))*(B6<=INDIRECT("Table1[E1]"))) ,SUMPRODUCT((B6<>"")* (B6>=INDIRECT("Table1[S2]"))*(B6<=INDIRECT("Table1[E2]"))) ,SUMPRODUCT((B6<>"")* (B6>=INDIRECT("Table1[S3]"))*(B6<=INDIRECT("Table1[E3]"))) ,SUMPRODUCT((B6<>"")* (B6>=INDIRECT("Table1[S4]"))*(B6<=INDIRECT("Table1[E4]"))) ,SUMPRODUCT((B6<>"")* (B6>=INDIRECT("Table1[S5]"))*(B6<=INDIRECT("Table1[E5]"))))

解决方案

The formula you want with all the columns is:

=1 < SUM(COUNTIFS(INDIRECT("Table1[Start]"),"<=" & B6,INDIRECT("Table1[End]"),">="& B6),COUNTIFS(INDIRECT("Table1[SD1]"),"<=" & B6,INDIRECT("Table1[ED1]"),">="& B6),...),0)

Where the ... is more countifs for the number of groupings

---

But that is not using Excel's functions to their potential. As I said in my comments, fewer columns allows for the same functionality with shorter and more accurate formulas.

The formula in the count column is:

=IF(Z5<>"",COUNTIF($Z$5:$Z5,Z5),"")

You could put a conditional formatting on this column to highlight when they have reached the 8 max:

=AC5 >= 8

Then the conditional formatting in the calendars would be:

For the overlap:

= 1 < COUNTIFS(INDIRECT("Table3[Start]"),"<=" & B6,INDIRECT("Table3[End]"),">=" & B6)

For the employee:

=SUMPRODUCT((INDIRECT("Table3[Names]")="abc")*(INDIRECT("Table3[Start]")<=B6)*(INDIRECT("Table3[End]")>=B6))

Again Changing the name of the employee.

With the data formatted as a table the reference will grow to accommodate new entries. Since the formula requires the employee name it does not matter if there is 1 or 100 entries for that employee it will always be the same color.

Also this allow one to use the filters that are inherent in the table, to sort and filter on employees and dates.

这篇关于列Excel中同一日期范围的不同颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！