office365 溢出问题

awesome-123

我在用office365 溢出功能写函数的时候，发现，有些编写特别复杂的公式在插入很多行或者列的时候，公式计算结果会发生变化，公式中引用我也找不到具体问题在哪，这种情况只在公式本身及其复杂的时候才会出现；复杂程度类似下面这种：




这些截图中我只是在每个区域的前2x4区域内有内容，其他的内容是自动溢出的，但是我搞不明白，为啥一旦插入超过一百多列以后，公式有些就会出现问题。


具体的公式内容：
单元格BO151：35
单元格BO151：5

单元格BO151：
=IFERROR(IF(MAX(IF(INDIRECT(ADDRESS(ROW()+1,COLUMN(),4,1)&CHAR(58)&ADDRESS(ROW()+IF(ISERROR(VALUE(BN151))=FALSE,VALUE(BN151),1)*IF(COUNT(ROW(INDIRECT(BN151)))>1,COUNT(ROW(INDIRECT(BN151))),1)*IF(COUNT(COLUMN(INDIRECT(BN151)))>1,COUNT(COLUMN(INDIRECT(BN151))),1),COLUMN(),4,1))="",0,1)*(ROW(INDIRECT(ADDRESS(ROW()+1,COLUMN(),4,1)&CHAR(58)&ADDRESS(ROW()+IF(ISERROR(VALUE(BN151))=FALSE,VALUE(BN151),1)*IF(COUNT(ROW(INDIRECT(BN151)))>1,COUNT(ROW(INDIRECT(BN151))),1)*IF(COUNT(COLUMN(INDIRECT(BN151)))>1,COUNT(COLUMN(INDIRECT(BN151))),1),COLUMN(),4,1)))-MIN(ROW(INDIRECT(ADDRESS(ROW()+1,COLUMN(),4,1)&CHAR(58)&ADDRESS(ROW()+IF(ISERROR(VALUE(BN151))=FALSE,VALUE(BN151),1)*IF(COUNT(ROW(INDIRECT(BN151)))>1,COUNT(ROW(INDIRECT(BN151))),1)*IF(COUNT(COLUMN(INDIRECT(BN151)))>1,COUNT(COLUMN(INDIRECT(BN151))),1),COLUMN(),4,1))))+1))=0,CHAR(45),ADDRESS(ROW()+1,COLUMN(),4,1)&CHAR(58)&ADDRESS(ROW()+IF(MAX(IF(INDIRECT(ADDRESS(ROW()+1,COLUMN(),4,1)&CHAR(58)&ADDRESS(ROW()+IF(ISERROR(VALUE(BN151))=FALSE,VALUE(BN151),1)*IF(COUNT(ROW(INDIRECT(BN151)))>1,COUNT(ROW(INDIRECT(BN151))),1)*IF(COUNT(COLUMN(INDIRECT(BN151)))>1,COUNT(COLUMN(INDIRECT(BN151))),1),COLUMN(),4,1))="",0,1)*(ROW(INDIRECT(ADDRESS(ROW()+1,COLUMN(),4,1)&CHAR(58)&ADDRESS(ROW()+IF(ISERROR(VALUE(BN151))=FALSE,VALUE(BN151),1)*IF(COUNT(ROW(INDIRECT(BN151)))>1,COUNT(ROW(INDIRECT(BN151))),1)*IF(COUNT(COLUMN(INDIRECT(BN151)))>1,COUNT(COLUMN(INDIRECT(BN151))),1),COLUMN(),4,1)))-MIN(ROW(INDIRECT(ADDRESS(ROW()+1,COLUMN(),4,1)&CHAR(58)&ADDRESS(ROW()+IF(ISERROR(VALUE(BN151))=FALSE,VALUE(BN151),1)*IF(COUNT(ROW(INDIRECT(BN151)))>1,COUNT(ROW(INDIRECT(BN151))),1)*IF(COUNT(COLUMN(INDIRECT(BN151)))>1,COUNT(COLUMN(INDIRECT(BN151))),1),COLUMN(),4,1))))+1))=0,CHAR(45),MAX(IF(INDIRECT(ADDRESS(ROW()+1,COLUMN(),4,1)&CHAR(58)&ADDRESS(ROW()+IF(ISERROR(VALUE(BN151))=FALSE,VALUE(BN151),1)*IF(COUNT(ROW(INDIRECT(BN151)))>1,COUNT(ROW(INDIRECT(BN151))),1)*IF(COUNT(COLUMN(INDIRECT(BN151)))>1,COUNT(COLUMN(INDIRECT(BN151))),1),COLUMN(),4,1))="",0,1)*(ROW(INDIRECT(ADDRESS(ROW()+1,COLUMN(),4,1)&CHAR(58)&ADDRESS(ROW()+IF(ISERROR(VALUE(BN151))=FALSE,VALUE(BN151),1)*IF(COUNT(ROW(INDIRECT(BN151)))>1,COUNT(ROW(INDIRECT(BN151))),1)*IF(COUNT(COLUMN(INDIRECT(BN151)))>1,COUNT(COLUMN(INDIRECT(BN151))),1),COLUMN(),4,1)))-MIN(ROW(INDIRECT(ADDRESS(ROW()+1,COLUMN(),4,1)&CHAR(58)&ADDRESS(ROW()+IF(ISERROR(VALUE(BN151))=FALSE,VALUE(BN151),1)*IF(COUNT(ROW(INDIRECT(BN151)))>1,COUNT(ROW(INDIRECT(BN151))),1)*IF(COUNT(COLUMN(INDIRECT(BN151)))>1,COUNT(COLUMN(INDIRECT(BN151))),1),COLUMN(),4,1))))+1))),COLUMN(),4,1)),"")

单元格BO152：
=IFERROR(ROW(INDIRECT(ADDRESS(ROW()+1,COLUMN(),4,1)&CHAR(58)&ADDRESS(ROW()+IF(ISERROR(VALUE(BN151))=FALSE,VALUE(BN151),1)*IF(COUNT(ROW(INDIRECT(BN151)))>1,COUNT(ROW(INDIRECT(BN151))),1)*IF(COUNT(COLUMN(INDIRECT(BN151)))>1,COUNT(COLUMN(INDIRECT(BN151))),1),COLUMN(),4,1)))-MIN(ROW(INDIRECT(ADDRESS(ROW()+1,COLUMN(),4,1)&CHAR(58)&ADDRESS(ROW()+IF(ISERROR(VALUE(BN151))=FALSE,VALUE(BN151),1)*IF(COUNT(ROW(INDIRECT(BN151)))>1,COUNT(ROW(INDIRECT(BN151))),1)*IF(COUNT(COLUMN(INDIRECT(BN151)))>1,COUNT(COLUMN(INDIRECT(BN151))),1),COLUMN(),4,1))))+1,"")

单元格BP151：
=IFERROR(IF(MAX(IF(INDIRECT(ADDRESS(ROW(),COLUMN()+1,4,1)&CHAR(58)&ADDRESS(ROW(),COLUMN()+IF(ISERROR(VALUE(BN152))=FALSE,VALUE(BN152),1)*IF(COUNT(ROW(INDIRECT(BN152)))>1,COUNT(ROW(INDIRECT(BN152))),1)*IF(COUNT(COLUMN(INDIRECT(BN152)))>1,COUNT(COLUMN(INDIRECT(BN152))),1),4,1))="",0,1)*(COLUMN(INDIRECT(ADDRESS(ROW(),COLUMN()+1,4,1)&CHAR(58)&ADDRESS(ROW(),COLUMN()+IF(ISERROR(VALUE(BN152))=FALSE,VALUE(BN152),1)*IF(COUNT(ROW(INDIRECT(BN152)))>1,COUNT(ROW(INDIRECT(BN152))),1)*IF(COUNT(COLUMN(INDIRECT(BN152)))>1,COUNT(COLUMN(INDIRECT(BN152))),1),4,1)))-MIN(COLUMN(INDIRECT(ADDRESS(ROW(),COLUMN()+1,4,1)&CHAR(58)&ADDRESS(ROW(),COLUMN()+IF(ISERROR(VALUE(BN152))=FALSE,VALUE(BN152),1)*IF(COUNT(ROW(INDIRECT(BN152)))>1,COUNT(ROW(INDIRECT(BN152))),1)*IF(COUNT(COLUMN(INDIRECT(BN152)))>1,COUNT(COLUMN(INDIRECT(BN152))),1),4,1))))+1))=0,CHAR(45),ADDRESS(ROW(),COLUMN()+1,4,1)&CHAR(58)&ADDRESS(ROW(),COLUMN()+IF(MAX(IF(INDIRECT(ADDRESS(ROW(),COLUMN()+1,4,1)&CHAR(58)&ADDRESS(ROW(),COLUMN()+IF(ISERROR(VALUE(BN152))=FALSE,VALUE(BN152),1)*IF(COUNT(ROW(INDIRECT(BN152)))>1,COUNT(ROW(INDIRECT(BN152))),1)*IF(COUNT(COLUMN(INDIRECT(BN152)))>1,COUNT(COLUMN(INDIRECT(BN152))),1),4,1))="",0,1)*(COLUMN(INDIRECT(ADDRESS(ROW(),COLUMN()+1,4,1)&CHAR(58)&ADDRESS(ROW(),COLUMN()+IF(ISERROR(VALUE(BN152))=FALSE,VALUE(BN152),1)*IF(COUNT(ROW(INDIRECT(BN152)))>1,COUNT(ROW(INDIRECT(BN152))),1)*IF(COUNT(COLUMN(INDIRECT(BN152)))>1,COUNT(COLUMN(INDIRECT(BN152))),1),4,1)))-MIN(COLUMN(INDIRECT(ADDRESS(ROW(),COLUMN()+1,4,1)&CHAR(58)&ADDRESS(ROW(),COLUMN()+IF(ISERROR(VALUE(BN152))=FALSE,VALUE(BN152),1)*IF(COUNT(ROW(INDIRECT(BN152)))>1,COUNT(ROW(INDIRECT(BN152))),1)*IF(COUNT(COLUMN(INDIRECT(BN152)))>1,COUNT(COLUMN(INDIRECT(BN152))),1),4,1))))+1))=0,CHAR(45),MAX(IF(INDIRECT(ADDRESS(ROW(),COLUMN()+1,4,1)&CHAR(58)&ADDRESS(ROW(),COLUMN()+IF(ISERROR(VALUE(BN152))=FALSE,VALUE(BN152),1)*IF(COUNT(ROW(INDIRECT(BN152)))>1,COUNT(ROW(INDIRECT(BN152))),1)*IF(COUNT(COLUMN(INDIRECT(BN152)))>1,COUNT(COLUMN(INDIRECT(BN152))),1),4,1))="",0,1)*(COLUMN(INDIRECT(ADDRESS(ROW(),COLUMN()+1,4,1)&CHAR(58)&ADDRESS(ROW(),COLUMN()+IF(ISERROR(VALUE(BN152))=FALSE,VALUE(BN152),1)*IF(COUNT(ROW(INDIRECT(BN152)))>1,COUNT(ROW(INDIRECT(BN152))),1)*IF(COUNT(COLUMN(INDIRECT(BN152)))>1,COUNT(COLUMN(INDIRECT(BN152))),1),4,1)))-MIN(COLUMN(INDIRECT(ADDRESS(ROW(),COLUMN()+1,4,1)&CHAR(58)&ADDRESS(ROW(),COLUMN()+IF(ISERROR(VALUE(BN152))=FALSE,VALUE(BN152),1)*IF(COUNT(ROW(INDIRECT(BN152)))>1,COUNT(ROW(INDIRECT(BN152))),1)*IF(COUNT(COLUMN(INDIRECT(BN152)))>1,COUNT(COLUMN(INDIRECT(BN152))),1),4,1))))+1))),4,1)),"")

单元格BP152：

=IFERROR(ADDRESS(MIN(ROW(INDIRECT(BO151)))+MIN(IF(MMULT(IF(INDIRECT(ADDRESS(MIN(ROW(INDIRECT(BO151))),MIN(COLUMN(INDIRECT(BP151))),4,1)&CHAR(58)&ADDRESS(MAX(ROW(INDIRECT(BO151))),MAX(COLUMN(INDIRECT(BP151))),4,1))="",0,1),TRANSPOSE(IF(COLUMN(INDIRECT(ADDRESS(MIN(ROW(INDIRECT(BO151))),MIN(COLUMN(INDIRECT(BP151))),4,1)&CHAR(58)&ADDRESS(MAX(ROW(INDIRECT(BO151))),MAX(COLUMN(INDIRECT(BP151))),4,1)))>0,1,0)))=0,"",ROW(INDIRECT(ADDRESS(MIN(ROW(INDIRECT(BO151))),MIN(COLUMN(INDIRECT(BP151)))+MIN(IF(MMULT(TRANSPOSE(IF(ROW(INDIRECT(ADDRESS(MIN(ROW(INDIRECT(BO151))),MIN(COLUMN(INDIRECT(BP151))),4,1)&CHAR(58)&ADDRESS(MAX(ROW(INDIRECT(BO151))),MAX(COLUMN(INDIRECT(BP151))),4,1)))>0,1,0)),IF(INDIRECT(ADDRESS(MIN(ROW(INDIRECT(BO151))),MIN(COLUMN(INDIRECT(BP151))),4,1)&CHAR(58)&ADDRESS(MAX(ROW(INDIRECT(BO151))),MAX(COLUMN(INDIRECT(BP151))),4,1))="",0,1))=0,"",COLUMN(INDIRECT(ADDRESS(MIN(ROW(INDIRECT(BO151))),MIN(COLUMN(INDIRECT(BP151))),4,1)&CHAR(58)&ADDRESS(MAX(ROW(INDIRECT(BO151))),MAX(COLUMN(INDIRECT(BP151))),4,1)))-MIN(COLUMN(INDIRECT(ADDRESS(MIN(ROW(INDIRECT(BO151))),MIN(COLUMN(INDIRECT(BP151))),4,1)&CHAR(58)&ADDRESS(MAX(ROW(INDIRECT(BO151))),MAX(COLUMN(INDIRECT(BP151))),4,1))))+1))-1,4,1)&CHAR(58)&ADDRESS(MAX(ROW(INDIRECT(BO151))),MAX(COLUMN(INDIRECT(BP151))),4,1)))-MIN(ROW(INDIRECT(ADDRESS(MIN(ROW(INDIRECT(BO151))),MIN(COLUMN(INDIRECT(BP151))),4,1)&CHAR(58)&ADDRESS(MAX(ROW(INDIRECT(BO151))),MAX(COLUMN(INDIRECT(BP151))),4,1))))+1))-1,MIN(COLUMN(INDIRECT(BP151))),4,1)&CHAR(58)&ADDRESS(MIN(ROW(INDIRECT(BO151)))+MAX(IF(MMULT(IF(INDIRECT(ADDRESS(MIN(ROW(INDIRECT(BO151))),MIN(COLUMN(INDIRECT(BP151))),4,1)&CHAR(58)&ADDRESS(MAX(ROW(INDIRECT(BO151))),MAX(COLUMN(INDIRECT(BP151))),4,1))="",0,1),TRANSPOSE(IF(COLUMN(INDIRECT(ADDRESS(MIN(ROW(INDIRECT(BO151))),MIN(COLUMN(INDIRECT(BP151))),4,1)&CHAR(58)&ADDRESS(MAX(ROW(INDIRECT(BO151))),MAX(COLUMN(INDIRECT(BP151))),4,1)))>0,1,0)))=0,"",ROW(INDIRECT(ADDRESS(MIN(ROW(INDIRECT(BO151))),MIN(COLUMN(INDIRECT(BP151))),4,1)&CHAR(58)&ADDRESS(MAX(ROW(INDIRECT(BO151))),MAX(COLUMN(INDIRECT(BP151))),4,1)))-MIN(ROW(INDIRECT(ADDRESS(MIN(ROW(INDIRECT(BO151))),MIN(COLUMN(INDIRECT(BP151))),4,1)&CHAR(58)&ADDRESS(MAX(ROW(INDIRECT(BO151))),MAX(COLUMN(INDIRECT(BP151))),4,1))))+1))-1,MIN(COLUMN(INDIRECT(BP151)))+MAX(IF(MMULT(TRANSPOSE(IF(ROW(INDIRECT(ADDRESS(MIN(ROW(INDIRECT(BO151))),MIN(COLUMN(INDIRECT(BP151))),4,1)&CHAR(58)&ADDRESS(MAX(ROW(INDIRECT(BO151))),MAX(COLUMN(INDIRECT(BP151))),4,1)))>0,1,0)),IF(INDIRECT(ADDRESS(MIN(ROW(INDIRECT(BO151))),MIN(COLUMN(INDIRECT(BP151))),4,1)&CHAR(58)&ADDRESS(MAX(ROW(INDIRECT(BO151))),MAX(COLUMN(INDIRECT(BP151))),4,1))="",0,1))=0,"",COLUMN(INDIRECT(ADDRESS(MIN(ROW(INDIRECT(BO151))),MIN(COLUMN(INDIRECT(BP151))),4,1)&CHAR(58)&ADDRESS(MAX(ROW(INDIRECT(BO151))),MAX(COLUMN(INDIRECT(BP151))),4,1)))-MIN(COLUMN(INDIRECT(ADDRESS(MIN(ROW(INDIRECT(BO151))),MIN(COLUMN(INDIRECT(BP151))),4,1)&CHAR(58)&ADDRESS(MAX(ROW(INDIRECT(BO151))),MAX(COLUMN(INDIRECT(BP151))),4,1))))+1))-1,4,1),"")

单元格BQ151：
=TRANSPOSE(ROW(INDIRECT(ADDRESS(1,1,4,1)&CHAR(58)&ADDRESS(BN152,1,4,1)))-MIN(ROW(INDIRECT(ADDRESS(1,1,4,1)&CHAR(58)&ADDRESS(BN152,1,4,1))))+1)

awesome-123

单元格BQ152：=IFERROR(INDEX(IF(IFERROR(IFERROR(COMBIN(INDIRECT(BO151),INDIRECT(BP151)),0)*IFERROR(COMBIN(LARGE(INDIRECT(BO151),ROW(INDIRECT(BO151))-MIN(ROW(INDIRECT(BO151)))+1),LARGE(INDIRECT(BP151),COLUMN(INDIRECT(BP151))-MIN(COLUMN(INDIRECT(BP151)))+1)),0)/IFERROR(COMBIN(INDIRECT(BO151),INDIRECT(BP151)),0),"")=0,"",IFERROR(IFERROR(COMBIN(INDIRECT(BO151),INDIRECT(BP151)),0)*IFERROR(COMBIN(LARGE(INDIRECT(BO151),ROW(INDIRECT(BO151))-MIN(ROW(INDIRECT(BO151)))+1),LARGE(INDIRECT(BP151),COLUMN(INDIRECT(BP151))-MIN(COLUMN(INDIRECT(BP151)))+1)),0)/IFERROR(COMBIN(INDIRECT(BO151),INDIRECT(BP151)),0),"")),MMULT(IF(TRANSPOSE(ROW(INDIRECT(BO151))-MIN(COLUMN(INDIRECT(BO151)))+1)-(ROW(INDIRECT(BO151))-MIN(ROW(INDIRECT(BO151)))+1)>=0,1,0),IF(IFERROR(COMBIN(INDIRECT(BO151),INDIRECT(BP151)),0)=0,1,0))+(ROW(INDIRECT(BO151))-MIN(ROW(INDIRECT(BO151)))+1),IF(ROW(INDIRECT(BO151))>0,1,0)*(COLUMN(INDIRECT(BP151))-MIN(COLUMN(INDIRECT(BP151)))+1)),"")


这个不需要上传文件，只要你有office365，只要按单元格的位置写好了对应的公式，然后无论在生什么位置复制公式，都会浮现同样的数据，你可以通过调节BN151，BN152 的大小，对函数区域的具体内容改变，不需要改公式，这也是我最开始的目的，一般写公式需要考虑引用位置，这个区域内的全部内容最开始的来源都是BN151和BN152这两个单元格，所以我就想做个一劳永逸的法子。只改数字不改公式，内容随数字的改变自动扩展。

希望能有大神给我解答一下？我可以付费，有知道的可以请回复我

awesome-123

这个不需要上传文件，只要你有office365，只要按单元格的位置写好了对应的公式，然后无论在生什么位置复制公式，都会浮现同样的数据，你可以通过调节BN151，BN152 的大小，对函数区域的具体内容改变，不需要改公式，这也是我最开始的目的，一般写公式需要考虑引用位置，这个区域内的全部内容最开始的来源都是BN151和BN152这两个单元格，所以我就想做个一劳永逸的法子。只改数字不改公式，内容随数字的改变自动扩展。

希望能有大神给我解答一下？我可以付费，有知道的可以请回复我

awesome-123

单元格BQ152：=IFERROR(INDEX(IF(IFERROR(IFERROR(COMBIN(INDIRECT(BO151),INDIRECT(BP151)),0)*IFERROR(COMBIN(LARGE(INDIRECT(BO151),ROW(INDIRECT(BO151))-MIN(ROW(INDIRECT(BO151)))+1),LARGE(INDIRECT(BP151),COLUMN(INDIRECT(BP151))-MIN(COLUMN(INDIRECT(BP151)))+1)),0)/IFERROR(COMBIN(INDIRECT(BO151),INDIRECT(BP151)),0),"")=0,"",IFERROR(IFERROR(COMBIN(INDIRECT(BO151),INDIRECT(BP151)),0)*IFERROR(COMBIN(LARGE(INDIRECT(BO151),ROW(INDIRECT(BO151))-MIN(ROW(INDIRECT(BO151)))+1),LARGE(INDIRECT(BP151),COLUMN(INDIRECT(BP151))-MIN(COLUMN(INDIRECT(BP151)))+1)),0)/IFERROR(COMBIN(INDIRECT(BO151),INDIRECT(BP151)),0),"")),MMULT(IF(TRANSPOSE(ROW(INDIRECT(BO151))-MIN(COLUMN(INDIRECT(BO151)))+1)-(ROW(INDIRECT(BO151))-MIN(ROW(INDIRECT(BO151)))+1)>=0,1,0),IF(IFERROR(COMBIN(INDIRECT(BO151),INDIRECT(BP151)),0)=0,1,0))+(ROW(INDIRECT(BO151))-MIN(ROW(INDIRECT(BO151)))+1),IF(ROW(INDIRECT(BO151))>0,1,0)*(COLUMN(INDIRECT(BP151))-MIN(COLUMN(INDIRECT(BP151)))+1)),"")

这个不需要上传文件，只要你有office365，只要按单元格的位置写好了对应的公式，然后无论在生什么位置复制公式，都会浮现同样的数据，你可以通过调节BN151，BN152 的大小，对函数区域的具体内容改变，不需要改公式，这也是我最开始的目的，一般写公式需要考虑引用位置，这个区域内的全部内容最开始的来源都是BN151和BN152这两个单元格，所以我就想做个一劳永逸的法子。只改数字不改公式，内容随数字的改变自动扩展。

希望能有大神给我解答一下？我可以付费，有知道的可以请回复我

jiangxiaoyun

卧槽，你真是个人才。这个函数公式，大神都写不赢你。没有之一。

awesome-123

jiangxiaoyun 发表于 2024-3-27 10:59
卧槽，你真是个人才。这个函数公式，大神都写不赢你。没有之一。

还有最后一个公式没贴出来，那个才是出问题的，不过帖子字数有限制，我发回复里面了，现在审核还没通过。

gufengaoyue

默默握了个蓸

咔咔乱坠

有点离谱
计算出现问题是不是公式太复杂，电脑带不动啊

awesome-123

咔咔乱坠 发表于 2024-3-27 11:22
有点离谱
计算出现问题是不是公式太复杂，电脑带不动啊

不会，直接写不会有问题，是写完了以后插入列，公式偏移以后才出现的问题，插入几列也没事，只有超过几十列上百列才会有部分公式有问题

ruyujh

这是我见过最长的公式