早教吧 育儿知识 作业答案 考试题库 百科 知识分享

EXCEL公式内大括号里面的数字太长了怎么办?LOOKUP(Sheet1!$B3,Sheet2!$A$8:$A$97,{8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,

题目详情
EXCEL公式内大括号里面的数字太长了怎么办?
LOOKUP(Sheet1!$B3,Sheet2!$A$8:$A$97,{8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97})
▼优质解答
答案和解析
公式精简为这样:
=LOOKUP(Sheet1!$B3,Sheet2!$A$8:$A$97,ROW($8:$97))
或加入防空白判断,当Sheet1!$B3为空白时,公式也返回空白,以上公式返回的是#N/A错误值。
=IF(Sheet1!$B3,="","",LOOKUP(Sheet1!$B3,Sheet2!$A$8:$A$97,ROW($8:$97)))
公式的意思为:以Sheet1!$B3的值在Sheet2!$A$8:$A$97中查找最接近Sheet1!$B3的值且比它小的数值,找到后返回对应的8-97这个范围中的数字。
公式中的ROW($8:$97)要与单元格的实际总行数对应,如$A$8:$A$97共90行,那么ROW($8:$97)也是产生90个数字,这部份其实写作ROW($1:$90) EXCEL 也是不会提示出错的,只是结果返回的是1-90之间的数值。