是否有脚本为单元内公式绕过50000个字符? [英] Is there a script to bypass 50000 characters for in-cell formula?

问题描述

我有这个(很长)的公式需要在Google表格中运行，并且遇到了极限错误:

I have this (insanely) long formula I need to run in Google Sheets, and I came across the limit error:

出问题了

您的输入在一个单元格中最多包含50000个字符.

There was a problem

Your input contains more than the maximum of 50000 characters in a single cell.

是否有解决方法?

我的公式是:

=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 columns note 2: so far I managed to bypass JOIN/TEXTJOIN for 50000+ characters even 500000 limits for total cells

推荐答案

更新:

我设法输入了最多 323461 个字符作为公式！通过使用 CTRL + H ，其中我替换了简单的 =SUM(1) 包含来自此答案的庞大公式的公式: https://webapps.stackexchange.com/a/131019/186471

UPDATE:

I managed to enter up to 323461 characters as a formula! by using CTRL + H 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个字符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！