Excel公式,以连续获取最后一个条目 [英] Excel formula to get last entry in a row
问题描述
我有一张表格,每行以字符串标识符开头,后面是创建表格后每周的一系列值:
XXX 15 20 25
YYY 11 15 19
ZZZ 18 22 22
在另一张表上,我想从第一页(而不是全部)中有一些字符串标识符,只有该项目的最新值:
XXX 25
ZZZ 22
第二类的表格,每个字符串id可能出现在多个表单上。
我不能使用VBA,因为这必须被查看改变了)在我的Windows Phone 7(我不相信手机版本的excel支持VBA,但纠正我,如果我错了)。有没有公式可以做到这一点?
ROW范围内最后一个非空白单元格的值
= LOOKUP(2,1 /(A1:M1),A1:M1)
这将把列ZZ中的所有LAST值放在特定行
$您的主页上您应该指定一个隐藏的列ZZ包含公式:$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ c> = LOOKUP(2,1 /(A1:ZY1),A1:ZY1)
I have a sheet where each row begins with a string identifier, followed by a series of values for each week since the sheet was created:
XXX 15 20 25
YYY 11 15 19
ZZZ 18 22 22
On another sheet I would like to have some of the string identifiers from the first page (not all), and only the latest value for that item:
XXX 25
ZZZ 22
I actually want multiple sheets of the second kind, with each string id possible appearing on more than one sheet.
I can't use VBA as this has to be viewed (and values added and altered) on my Windows Phone 7 (I do not believe the mobile version of excel supports VBA, but correct me if I'm wrong). Is there a formula that's able to do this?
The value of the last non-blank cell in ROW range
=LOOKUP(2,1/(A1:M1<>""),A1:M1)
- on your master sheet you should designate a hidden column like ZZ to contain the formula:
=LOOKUP(2,1/(A1:ZY1<>""),A1:ZY1)
this will put in column ZZ all the LAST values of that specific row - on all of the other sheets that depend on your master, you can use a vlookup to retrieve the information in ZZ from the master sheet
这篇关于Excel公式,以连续获取最后一个条目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!