假设你的数据有8000行+,我可以给你【完美~~~~完美】的效果。
首先,在M2单元格输入以下公式,三键下拉。- =IFERROR(INDEX(D$2:D$10000,SMALL(IFERROR(IF(MATCH(IF(IF(RIGHT(D$2:D$10000)="号","",D$2:D$10000)=0,"",IF(RIGHT(D$2:D$10000)="号","",D$2:D$10000)),IF(IF(RIGHT(D
- $2:D$10000)="号","",D$2:D$10000)=0,"",IF(RIGHT(D$2:D$10000)="号","",D$2:D$10000)),)=ROW($1:$9999),ROW($1:$9999)),FALSE),ROW(A2))),"")
复制代码 ,其次,在N2单元格输入以下公式,下拉- =IF(AND(M2<>"",SUM(SUMPRODUCT((D:D=M2)*(E:E="迟到")),SUMPRODUCT((D:D=M2)*(F:F="迟到")))<>0),SUM(SUMPRODUCT((D:D=M2)*(E:E="迟到")),SUMPRODUCT((D:D=M2)*(F:F="迟
- 到"))),IF(AND(M2<>"",SUM(SUMPRODUCT((D:D=M2)*(E:E="迟到")),SUMPRODUCT((D:D=M2)*(F:F="迟到")))=0),0,""))
复制代码 ,接着,一劳永逸的方法,假如你每次都是这个区域,插入模块,输入以下代码,- Sub sort()
- Range("M2:N10000").Select
- Selection.Copy
- Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationNone, SkipBlanks:=False, Transpose:=False
- With ActiveSheet.sort
- With .SortFields
- .Clear
- .Add Key:=Range("N2:N10000"), SortOn:=xlSortOnValues, Order:=xlDescending, CustomOrder:=""
- End With
- .Header = xlNo
- .Orientation = xlSortColumns
- .MatchCase = False
- .SortMethod = xlPinYin
- .SetRange Rng:=Selection
- .Apply
- End With
- End Sub
复制代码 ,插入一个按钮,指定刚刚那个宏。
用我这个方法,可以给你完美的效果。 |