多条件选出重复项，index+small+if+row解决不了

ReyesZYX99

各位大侠，被一个问题困扰了许久，用index+small+if+row似乎无法达到要求。如图所示，“记录”表中有几个重复的省市名称，每个名称都有一个对应的日期和累计量指标，如果某个省市的累计量出现过0，则以后肯定不会再次出现该省市名称。现在我希望提取不重复的省市名称，并且需要满足三个条件：1是只从日期小于等于7月20日的记录里选取；2是如果出现重复项，需要提取重复项中的最后一项；3是如果一个省市的重复项最后一项是0，那么就不提取他。


语言描述的有点复杂，附上附件，希望各位能帮帮忙，万分感谢！

wangjguo44

这个问题可以用index+small+if+row解决，麻烦在于一般的去重复是从上至下去掉（从上至下首次出现的留下，再次出现的筛去），而你这个是自下而上的去重复，最后出现的留下，先出现的倒要筛去（如果出现重复项，需要提取重复项中的最后一项），再加上两个先决条件（日期小于等于7月20日的记录里选取、如果一个省市的重复项最后一项是0，那么就不提取他），所以公式就比较烦，附件公式假定《记录》表有100行数据（动态的更繁），写了个不短的公式。C列显示的文本数字，如一定要是数值型，去掉最后的&""，前面套个IF(B2="","",原公式)。

期待高手优化！


EH(Wjg)b.rar (12.11 KB, 下载次数: 164)

2016-7-28 08:12 上传

点击文件名下载附件

yeminqiang

透视表不试一试？

mathking77

可以用VBA实现吗

Sub xx()
Dim i, j As Integer, k&
For i = [A65536].End(xlUp).Row To 2 Step -1
For j = i - 1 To 1 Step -1
If CDate(Cells(i, 1)) > CDate("2016-07-20") Then
Rows(i).Delete shift:=xlUp
End If
If Cells(i, 2) = Cells(j, 2) Then
Rows(j).Delete shift:=xlUp
End If
Next j
Next i
For k = 2 To [A65536].End(xlUp).Row
If Cells(k, 3) = 0 Then
Rows(k).Delete shift:=xlUp
End If
Next k
End Sub

ReyesZYX99

mathking77 发表于 2016-7-28 08:52
可以用VBA实现吗

Sub XX()

主要是这个表要求原始记录必须全部留存，然后我也不光要提取7月20日之前的，还需要在未来不定时地提取一次当日之前的。

mathking77

ReyesZYX99 发表于 2016-7-28 09:29
主要是这个表要求原始记录必须全部留存，然后我也不光要提取7月20日之前的，还需要在未来不定时地提取一 ...

也可以用VBA保留原数据的，或者你另存为一份数据用来操作，如果数据庞大的话还是VBA比较快，你需要什么日期 只要把代码里的“2016-XX-XX"改成你要的日期就行了

ReyesZYX99

mathking77 发表于 2016-7-28 09:38
也可以用VBA保留原数据的，或者你另存为一份数据用来操作，如果数据庞大的话还是VBA比较快，你需要什么日 ...

嗯，VBA确实是可行的，感谢您！楼上wangjguo44大神的公式也非常给力，我也学习一下公式的方法。

ReyesZYX99

wangjguo44 发表于 2016-7-28 08:12
这个问题可以用index+small+if+row解决，麻烦在于一般的去重复是从上至下去掉（从上至下首次出现的留下，再 ...

十分感谢，确实如您分析的，条件比较繁琐。这个公式非常给力！中午午休的时候我再好好学习一下。

ReyesZYX99

yeminqiang 发表于 2016-7-28 08:22
透视表不试一试？

这个表我简化了，由于还有其他要求，用透视表比较麻烦，感谢！

iWenShan

此问题分两个步骤解决：
一、提取符合条件的不重复城市名称（F列）：
1.F列的公式使用"index+small+if+match"组合，提取出所有符合条件且不重复的城市名称，公式如下：
F2=IFERROR(INDEX(B:B,SMALL(IF(IFERROR(((ROW(B:B)=MATCH(B:B,B:B,))*(A:A<$E$2)),0),ROW(B:B),9E+307),ROW()-1)),"")
2.以上函数是数组函数，要使用三键结束输入。

二、求最大累积量的问题（G列和H列）：
1.G列的公式使用的是经典"index+small+if+序号"组合，先计算"有多少条数据符合条件"，再提取最后一条数据；
G2=INDEX(C:C,SMALL(IF((F2=B:B)*(C:C<>0)*(A:A<$E$2),ROW(B:B),9E+307),SUMPRODUCT((F2=B:B)*(C:C<>0)*(A:A<$E$2))))；
2.根据题主需求，建议使用"index+large+if+序号"组合，更加简洁，公式如下：
H2=INDEX(C:C,LARGE(IF((F2=B:B)*(C:C<>0)*(A:A<$E$2),ROW(B:B),0),1))；
3.使用下面那个公式，免去了计算"有多少条数据符合条件"的步骤，序号写"1",直接提取最后一个;
4.注意，改用large函数时，if函数中的错误值要从一个较大的值（9e+307）改为一个较小的值（0）；
5.以上两个都是数组函数，要使用三键结束输入。
6.具体公式使用情况见附件。
三、函数效率问题。
1.由于函数全部使用整列引用，效率非常低。
2.函数引用区域的优化跟本问题关系不大，就看题主自己了。

help（iWenShan-Solution）.zip (13.94 KB, 下载次数: 46)

2018-8-5 16:24 上传

点击文件名下载附件