问题描述
我有这个(非常)长的公式需要在 Google 表格中运行，但我遇到了限制错误:
I have this (insanely) long formula I need to run in Google Sheets, and I came across the limit error:
您的输入在单个单元格中包含超过 50000 个字符的最大值.
是否有解决方法?
我的公式是:
=ARRAYFORMULA(SPLIT(QUERY({B!A1:A100; ........ ; CA!DZ1:DZ100},
"select * where Col1 is not null order by Col1 asc", 0), " "))
完整公式为:pastebin.com/raw/ZCkZahpw
为 Pastebin 道歉...我在这里也有一些错误:
apologies for Pastebin... I got a few errors here too:
注意 1: 由于它是一个很长的公式，因此它的输出大小应为 ~100 行 × 3 列注意 2: 到目前为止，我设法绕过 JOIN/TEXTJOIN 超过 50000 个字符，甚至总单元格的 500000 个限制
note 1: due to fact that it's a long formula, the output from it should be of size ~100 rows × 3 columnsnote 2: so far I managed to bypass JOIN/TEXTJOIN for 50000+ characters even 500000 limits for total cells
推荐答案
更新:
我设法输入最多 323461 个字符作为公式！通过使用 + 我替换了简单的 =SUM(1) 公式和我来自这个答案的巨大公式:https://webapps.stackexchange.com/a/131019/186471
UPDATE:
I managed to enter up to 323461 characters as a formula! by using + where I replaced simple =SUM(1) formula with my huge formula from this answer: https://webapps.stackexchange.com/a/131019/186471
经过一些研究，似乎没有任何解决方法可以解决这个问题.
after some research, it looks like there isn't any workaround to pull this of.
建议的节省建议(缩短:A!A:A，删除:select *, asc，缩短:"where Col1!=''order by Col1") 将其减少了一点，其余部分在 VR {} 数组解决方案中拆分为两个公式.
recommended savings that were suggested ( shortening: A!A:A, dropping: select *, asc, shortening: "where Col1!=''order by Col1") reduced it a bit and rest was split into two formulas in VR {} array solution.
这篇关于是否有脚本可以绕过 50000 个字符的单元格公式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！