Excel 中提示不能超过256列或65536行的处理

Excel中经常使用各种函数在表格、工作表之间相互引用、调用数据的,但有时候尽管是完全按照公式的要求来输入相关项目的,还是会出现:

“此引用有问题。此文件中的公式只能引用内含256列(列IW或更少)或65536行的工作表中的单元格”这样的警告。点击确认之后,公式需要重新修改。但实际上看,公式的单元格所在的位置可并没有超过这个提示中的限制的(256列或65536行),但依然有这样的错误,是为什么呢?

图1.Excel 中提示不能超过256列或65536行警告
图1.Excel 中提示不能超过256列或65536行警告

经简单查证,发现这个是旧版本Excel的固有的一个限制,即在Office2003及之前版本的Excel中,xls格式的表格,支持最大的列为256列,最大的行为65536行。

图2.Excel2003表格最大列IV最大65536行
图3.Excel2019中表格最大列XFD最大1048576行

这样,当2003版或更低版本的xls表格中,函数、公式则会限制在相应范围内的单元格引用。而一旦公式引用了超出这个范围之外的单元格,Excel则会自动报错。特别是存在引用整行、整列的情况下,而被引用的表格是Office2007、2010及更高版本创建的文件,哪怕是xls文件,都会提示超出了范围。因为高版本Excel保存的xls文件,也远远不止256列、65536行。例如Excel2019中最大行为1048576行,即1024*1024=1048576行。如公式中包含XFD:XFD列的话,则必然超出了65536行。

解决办法:

方式一、修改文件版本

现在已经清楚是旧版本的Excel文件中存在最大表格行列数的限制,那么最简单的办法就是将当前的xls文件,使用另存为的功能把xls文件保存为xlsx文件。再重新打开xlsx文件重新输入相应的公式即可。这样的话,高版本的文件可以兼容低版本的文件,但反之则容易出现问题。毕竟现在已经是2019年了,Office2019套装也发布很久了,但还是有N多的系统、后台默认调用的还是Office2003的模版,生成的是Excel2003的表格。不可避免的会存在这样的问题。因此,简单另存为一下,即可解决问题。

方式二、对公式引用的范围手动做限制

之所以出现这样的警告,是因为在引用高版本表格的时候,默认会引用整行整列的数据,而整行整列就超出了旧版本Excel的单元格范围的限制。如公式=VLOOKUP(C1,[EXCEL演示.xlsx]Sheet1!$B:$E,4,0),默认引用了B-E列整个的区域。虽然我们有效的数据可能就那么几十、几百行,但公式并没有圈定行数,导致了警告出错。这时候,我们对公式稍作调整:

=VLOOKUP(C1,[EXCEL演示.xlsx]Sheet1!$B1:$E6000,4,0)

这样就限定了引用的范围。当然E6000这里的6000注意要包含所有的有效行数就行。万一实际上的数据已经超出了65536行呢?那只能采取方式一的办法来解决了。

如上便是提示不能超过256列或65536行的简单处理。