是否有任何内置函数或方法可以直接在Excel中排除重叠记录。特别是基于时间戳。请参考以下示例。第2行和第4行(示例中)重叠,我试图排除重叠记录。我尝试使用排名函数排除重复记录,但无法排除。
范例:
S.NO SQ Date [Start Time] [End Time]
1 1 1/9/2015 18:00 23:55
1 1 1/9/2015 19:00 22:55
1 2 1/9/2015 19:00 23:59
1 2 1/9/2015 19:00 21:59
预期输出:
S.NO SQ Date [Start Time][End Time] Expected Out put Comments
1 1 1/9/2015 18:00 23:55 5.92
1 1 1/9/2015 19:00 22:55 0 Exclude this record
1 2 1/9/2015 19:00 23:59 4.98
1 2 1/9/2015 19:00 21:59 0 Exclude this record
2条答案
按热度按时间toiithl61#
所提供的数据样本似乎可能未涵盖所有相关可能性,将“重叠”解释为完全在范围内(扣除边界),假设
S.NO
在A1中,仅需考虑相邻条目,则:在F2中,并向下复制以适合,并将ColumnF格式化:
和左对齐,并使用:
在G2中,并复制到适合的生产:
一旦在F1和G1中填充了标签。
flvtvl502#
将数据范围设为Excel表格:在示例Table 1中。在表的新列中,使用Excel 365中的新FILTER命令输入以下公式:
=CHOOSECOLS(筛选器(表1 [[序号]:[结束时间]],([日期]=[@日期])([开始时间]〈[ @结束时间])([结束时间]〉[@开始时间]),"”),2)
如果公式产生的记录超过1笔,则此公式将产生#Spill!错误(错误码9)。此错误可用于后续行程,例如设定格式化的条件。
请注意:表的ColumnNames中不允许有空格;排除表格中的新列,否则会发生循环引用。如果需要,可以添加更多条件以生成唯一的记录。乘法符号 * 表示AND条件。CHOOSECOLS用于防止右侧列中有任何数据时出现溢出错误。
如果新列在表之外,则最后一行可能导致多行答案,而不是溢出错误。如果未填充行中所有已使用的列单元格,则会导致#value错误