英文:
How can I automatically formulate a column while adding values to another? EXCEL
问题
我在Excel中有一列公式，它从日期X到今天返回所有的工作日，并自动填充，右侧还有一列，应用了COUNTIF公式，基于第一列，但是单元格不像以前一样自动填充，而是需要拖动以完成公式，我希望这也能自动完成。
第一列的公式是：=WORKDAY(MIN(Data\[Fecha_Completa\]-1);SEQUENCE(NETWORKDAYS(MIN(Data\[Fecha_Completa\]-1);MAX(Data\[Fecha_Completa\]-1)))，而第二列（不会自动填充）是：=IF(COUNTIFS(Data\[Fecha_Completa\];A2)=0;A2;"")，其中解释了，如果第一列的日期在数据库（Data[Fecha_Completa]）中找到，我希望Excel不返回任何内容，但如果没有找到，我希望Excel返回相同的日期。然后问题是第二列不会像第一列那样自动填充。
正如您在第一张图片中可以看到，该值的公式存在，但一旦数据库中包括更多日期（如您在第二张图片中所看到的，当我们到了二月份时），公式就停止工作，强制我将其拖动下去。
我尝试将公式应用于整列，但这不是我们要寻找的，因为文件会更重。
英文:
I have a column formulated in excel that returns all the business days from date X to today and it is automatically filled in, and there is a column to its right in which a COUNTIF formula is applied based on this first column , but the cells are not filled automatically as it happened before, but you have to drag down to complete the formulas, and I want this to be automatic too.
The first column is fulfilled with the following formula: =WORKDAY(MIN(Data\[Fecha_Completa\]-1);SEQUENCE(NETWORKDAYS(MIN(Data\[Fecha_Completa\]-1);MAX(Data\[Fecha_Completa\]-1)))), and the second one (which doesn´t fulfill automatically): =IF(COUNTIFS(Data\[Fecha_Completa\];A2)=0;A2;""); in which it is explained that, if the date of the first column is found in the database (Data[fechacompleta]), I want excel to return me nothing, but if it isn´t found, I want excel to return me the same date. Then the problem is that the second column isn´t fulfilled automatically at the same time as the first does.
As you may appreciate in the first image, the formula exists for that value, but once more dates are included in the database (as you may see in the second picture when we come to February) formula stop working and obliges me to drag it down.
I have tried to applicate the formula to the whole column but that isn´t what we are looking for as the file would be heavier.
答案1
得分: 0
要基于动态溢出数组创建公式，请在范围引用中使用 `#`。因此，使用 `A2#` 而不是 `A2` 将自动将公式溢出到与 A2 中的公式相同的大小。
=IF(COUNTIFS(Data\[Fecha_Completa\];A2#)=0;A2#;"")
英文:
To base a formula on a dynamically-spilled array, use a # in your range references. Therefore using A2# instead of A2 will automatically spill the formula to the same size as the formula in A2.
=IF(COUNTIFS(Data\[Fecha_Completa\];A2#)=0;A2#;"")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论