问题描述
2011 0
2011 15
2011 10
2011 5
2012 15
2012 10
2012 5
我想找到的是相应年份的最后一行,所以输出将是
2011 5
2012 5
任何想法如何完成?
我正在看这个并找到找到最后一行数据的方式,但是我需要知道如何一年筛选相同的。
注意:我想使用excel预定义的功能来做这个。没有宏,没有编码,没有枢轴。
编辑1
= INDEX($ B:$ B,MAX(IF(LEN($ B:$ B)大于0,ROW($ B:$ B),0)),1)
给我回答5.但是我希望它能够在一年之前消失。任何想法如何完成?
编辑2
= OFFSET(B2,MAX(IF(NOT(ISBLANK(B2:B25)),ROW(B2:B25),0)) - ROW(B2),0)
解决方案
这是你想要的吗?
$ b $ ($ A $ = $ A $ 1:$ A $ 7,ROW($ A $ 1:$ A $ 7)) )}}
你必须输入这个数组函数,所以输入将代码复制并粘贴在 {}
之间,然后按 ctrl-shift-enter
。代码假定您在单元格 A9
中有 2011
。如果然后在 A10
中键入2012,然后复制公式就可以了。
公式的基本思想是:
- 如果
A1:A7
中的任何单元格匹配A9
,将其行号添加到候选行列表 - 取候选列表的最大行号;说这是 k
- 使用索引函数返回
B1的行 k 中的值:B7
In excel I have data as below.
2011 0
2011 15
2011 10
2011 5
2012 15
2012 10
2012 5
What I want to find is the last row for the respective year so that the output would be
2011 5
2012 5
Any idea how to get this done?
I am looking at this post and found the way for finding last row data, however I need to know how to filter the same by year.
Note: I want to do this using excel pre-defined function. NO macro, NO coding, NO pivot.
Edit 1
=INDEX($B:$B,MAX(IF(LEN($B:$B)>0,ROW($B:$B),0)),1)
gives me answer 5. However I want it to fiter by year. Any idea how to get this done?
Edit 2
=OFFSET(B2,MAX(IF(NOT(ISBLANK(B2:B25)),ROW(B2:B25),0))-ROW(B2),0)
this also gives last row data.
Is this what you want?
{=INDEX($B$1:$B$7,MAX(IF(A9=$A$1:$A$7,ROW($A$1:$A$7))))}
You must enter this as an array function, so enter copy and paste the code between the {}
's and then press ctrl-shift-enter
. The code assumes you have 2011
in cell A9
. If you then type 2012 in A10
and copy the formula down it will work.
The basic idea of the formula is:
- If any cell in
A1:A7
matchesA9
, add its row number to a list of candidate rows - Take the maximum row number of the list of candidates; say this is k
- Use the index function to return the value that is in row k of
B1:B7
这篇关于在excel中查找最后一行数据[特定] / [过滤]年份的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!