Excel 怎么用数组公式批量计算
一个公式处理一片区域,不用一格一格下拉。讲清编辑栏入口、老版本组合键与新版本溢出的区别、整列引用和合并单元格几个坑,以及怎么抽查验证。
Excel 里最容易被劝退的一类公式,就是一次算出多个结果的那种。想给一整列都套同一个判断、想按条件汇总一段区间,普通公式只能一格一格往下拉。数组公式干的就是这件事:一个公式,同时处理一片区域。
入口在哪
数组公式没有单独的菜单,入口就在编辑栏。区别只在按下确认键的方式:老版本要按组合键确认,公式两边会自动出现花括号;新版本支持动态数组,普通回车就会把结果铺开成一片。判断自己用的是哪种,输入一个会返回多个值的公式按下回车就知道,只出一个格子就是老版本。
- 老版本:输入完按组合键确认,公式两侧的花括号是系统加的,不能手打。
- 新版本:直接回车,结果自动向下向右溢出,被占用的格子会报溢出错误。
- 不确定版本,就在设置或关于里看版本号,也可以在空白格试一条会返回多个值的公式。
具体步骤
- 先想清楚要一次算出几个结果。是一个数字、一列数字,还是一张表,决定了公式要写成什么样。
- 把区域当成整体来写,比如用整段区间做条件判断,而不是写单格引用。
- 老版本按住组合键确认。松手后编辑栏里会出现花括号,说明被识别成数组公式了。
- 新版本直接回车,看结果往下铺了几行。铺开的范围就是溢出区域,选中它左上角那一格就能看到完整公式。
- 要在别的公式里引用这片结果,用左上角单元格加井号引用整片区域,比重新框选稳。
- 写完选中公式里的一段,按求值键看中间结果,确认每一步算出来的东西和你想的一样。
几个坑
- 手打花括号没用。手动输入的花括号会被当成文本,公式不会按数组计算。
- 整列引用在数组公式里非常慢。整列几百万个格子一起算,文件会明显卡,把区间收窄到实际数据范围。
- 新版本的溢出区域下方如果有内容,公式会直接报溢出错误。清空下方格子,或者把公式挪到空旷的位置。
- 合并单元格会挡住溢出。要铺开结果的区域不能有合并格。
- 数组常量里的分隔符跟系统区域设置有关,列与列、行与行的分隔符在不同环境下不一样,照抄网上的写法经常报错。
- 把新版本写好的公式复制给用老版本的同事,可能只剩下一个值,因为对方的环境不认溢出。
怎么验证
验证分两层。先看结果个数对不对:说要出一列,就该铺满一列,少一格多一格都说明区域划错了。再抽查数值:挑两三行,用最笨的单格公式单独算一遍,跟数组公式的结果对上。最后按显示公式的快捷键,把整张表切成公式视图,检查每条数组公式覆盖的范围是不是你想要的那一段,避免多算或少算了行。
常见问题
为什么按回车只出来一个结果
多半是老版本环境。改成按组合键确认,让系统把它识别成数组公式。如果组合键也没用,检查是不是在编辑状态下按的,要退出编辑、光标停在单元格上再按。
出现溢出错误怎么处理
这个错误的意思是结果铺不开,被下面的内容挡住了。把溢出区域下方的格子清空,或者把公式挪到右侧和下方都空着的位置。如果确实需要在原位置出结果,就得改写成只返回单个值的普通公式。
数组公式能不能像普通公式一样往下拉
老版本的数组公式本身已经覆盖一片区域,再往下拉会报错,因为一片区域不能套一片区域。新版本的溢出公式也不要下拉,它自己会铺开,下拉反而会生成重复结果。
一句话总结:数组公式的入口就是编辑栏,老版本用组合键确认、新版本直接回车溢出,写的时候收窄区域、留空溢出位置,再用单格公式抽查两三行数值。