英文:
Excel Fomrula doesn't let me press enter and displays an error?
问题
这个公式为什么会导致错误，阻止我按回车键？这个公式的各个组成部分似乎都正常工作，但当它们组合在一起时，就无法正常运行。
这是字符串：
.cict oem liab on file exp 11/10/19
这个公式的这部分是分开的：
VSTACK(TRIM(SEQUENCE(10,,0)),{" ";"/";"-"})
完整的公式：
=COUNTIF(VSTACK(TRIM(SEQUENCE(10,,0)),{" ";"/";"-"}), TRIM(MID(A2,SEQUENCE(LEN(A2)),1)))=1
错误：
我的表格
英文:
Why does this formula cause an error and prevent me from pressing enter? The individual components of the formula seem to work fine, but when combined, they do not function properly.
Here is the string:
.cict oem liab on file exp 11/10/19
This part of the formula is separate:
VSTACK(TRIM(SEQUENCE(10,,0)),{" ";"/";"-"})
Complete formula:
=COUNTIF(VSTACK(TRIM(SEQUENCE(10,,0)),{" ";"/";"-"}), TRIM(MID(A2,SEQUENCE(LEN(A2)),1)))=1
Error:
My Sheet
答案1
得分: 2
COUNTIF不允许将动态数组公式用作范围输入。它需要一个范围。
要获取所需的TRUE/FALSE列表，我们使用MATCH()：
=ISNUMBER(MATCH(TRIM(MID(A2,SEQUENCE(LEN(A2)),1)),VSTACK(SEQUENCE(10,,0),{" ";"\/";"-"}),0))
如果你只想要日期，那么使用FILTER：
=FILTER(TEXTSPLIT(A2," "),ISNUMBER(--(TEXTSPLIT(A2," "))))
英文:
COUNTIF will not allow the use of Dynamic Array formulas as range inputs. It expects a range.
To get the desired TRUE/FALSE list we use MATCH():
=ISNUMBER(MATCH(TRIM(MID(A2,SEQUENCE(LEN(A2)),1)),VSTACK(SEQUENCE(10,,0),{" ";"/";"-"}),0))
If all you want is the date then use FILTER:
=FILTER(TEXTSPLIT(A2," "),ISNUMBER(--(TEXTSPLIT(A2," "))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论