【函数初级201203班】练习6---批量分月【已开贴】

石磊.

【函初201203班】- B05-石磊.


选中G2:G22  =INDEX($B:$B,SMALL(IF((MONTH($C$2:$C$71)=--SUBSTITUTE(G1,"月份",""))*($D$2:$D$71="在职"),ROW($B$2:$B$71),4^8),ROW($1:$70)))&""    三建右拉

选中G2:G22  =T(OFFSET($B$1,SMALL(IF((MONTH($C$2:$C$71)=--SUBSTITUTE(G1,"月份",""))*($D$2:$D$71="在职"),ROW($B$2:$B$71),99),ROW($1:$70))-1,))&""  三建右拉

选中G2:G22  =T(INDIRECT("B"&SMALL(IF((MONTH($C$2:$C$71)=--SUBSTITUTE(G1,"月份",""))*($D$2:$D$71="在职"),ROW($B$2:$B$71),99),ROW($1:$70))))  三建右拉

G2  =INDEX($B:$B,SMALL(IF(ISTEXT($D$2:$D$71)*(MONTH($C$2:$C$71)=COLUMN(A1)),ROW($2:$71),99),ROW(A1)))&""    三建  右拉下拉

G2 =INDIRECT("b"&SMALL(IF(ISTEXT($D$2:$D$71)*(MONTH($C$2:$C$71)=COLUMN(A1)),$A$2:$A$71+1,99),ROW(A1)))&"" 三建  右拉下拉

G2
=INDEX($B:$B,SMALL(IF((MONTH($C$2:$C$71)&$D$2:$D$71=COLUMN(A1)&"在职"),$A$2:$A$71+1,99),ROW(A1)))&""  三建  右拉下拉

lucking551

1. =INDEX($B:$B,SMALL(IF(($D$2:$D$71="在职")*(MONTH($C$2:$C$71)=COLUMN(A1))*(YEAR($C$2:$C$71)=2012),ROW($D$2:$D$71),4^8),ROW(A1)),)&""

复制代码

下拉，右拉

ljzzlan

=INDEX($B:$B,SMALL(IF((MONTH($C$2:$C$71)=--LEFT(G$1))*($D$2:$D$71="在职"),ROW($B$2:$B$71),4^8),ROW(A1)))&""

数组

lnykwrz

学习了  学习了  

笨笨四

数组
G2=INDEX($B$2:$B$72,SMALL(IF(($C$2:$C$71<=DATE(2012,COLUMN(B1),))*(-TEXT($D$2:$D$71,"0;;;\9\9\9\9\9")<=-DATE(2012,COLUMN(A1),)),ROW($1:$70),71),ROW(A1)))&""

G2=INDEX($B$2:$B$72,SMALL(IF(($C$2:$C$71<=DATE(2012,COLUMN(B2),))*($D$2:$D$71>DATE(2012,COLUMN(A3),)),ROW($1:$70),71),ROW(A1)))&""

seabearlin

老师，您好：
  
   答案如下：
1.先将月份更改成数字，再更改字符格式增加月份；年同样如此；
2.将在职替换成今天日期，再更改字符格式为"在职"；
3.输入公式:=IF(OR(AND(YEAR($C2)=$H$1,MONTH($C2)<=I$1,YEAR($D2)=$H$1,MONTH($D2)>=J$1),AND(YEAR($C2)=2011,YEAR($D2)=$H$1,MONTH($D2)>=J$1)),$B2,"")
向左向下拉即可；
具体详情如下附件，请查阅；
C27：seabearlin

夜雨江南

1. =IFERROR(INDEX($B:$B,SMALL(IF(IFERROR(MONTH($D$2:$D$71)>=(--LEFT(G$1)),1)*(MONTH($C$2:$C$71)<=(--LEFT(G$1))),ROW($2:$71)),ROW(A1))),"")

复制代码

wangjianff

G2单元格中输入
法一：{=INDIRECT("b"&SMALL(IF(IF($D$2:$D$71="在职",1,(DATE(2012,COLUMN(B1),0)-$D$2:$D$71)<0)*((DATE(2012,COLUMN(B1),0)-$C$2:$C$71)>=0),ROW($2:$71),99),ROW($A1)))&""}  
法二：{=INDEX($B:$B,SMALL(IF(IF($D$2:$D$71="在职",1,(DATE(2012,COLUMN(B1),0)-$D$2:$D$71)<0)*((DATE(2012,COLUMN(B1),0)-$C$2:$C$71)>=0),ROW($2:$71),99),ROW($A1)))&""}
法三：{=OFFSET($B$1,SMALL(IF(IF($D$2:$D$71="在职",1,(DATE(2012,COLUMN(B1),0)-$D$2:$D$71)<0)*((DATE(2012,COLUMN(B1),0)-$C$2:$C$71)>=0),ROW($1:$70),99),ROW($A1)),)&""}
向右向下拉
【函初201203班】-D31-wangjianff

新月gg

1. Sub piliang()
   
2.     Dim iend As Long, k As Integer
   
3.     Dim ir As Long, ic As Long, i As Integer
   
4.     Dim arr, brr(), iyear, imonth
   
5.     iend = Range("c" & Rows.Count).End(3).Row
   
6.     arr = Range("b2:d" & iend): ReDim brr(1 To UBound(arr))
   
7.     Range("g2:r3000").ClearContents
   
8.     For ic = 7 To 18
   
9.         For ir = 1 To UBound(arr)
   
10.             i = Left(Cells(1, ic), 2 * Len(Cells(1, ic)) - LenB(StrConv(Cells(1, ic), vbFromUnicode)))
    
11.             If arr(ir, 3) = "在职" Then
    
12.                 If Year(arr(ir, 2)) = "2011" Then
    
13.                     k = k + 1
    
14.                     brr(k) = arr(ir, 1)
    
15.                 ElseIf Year(arr(ir, 2)) = "2012" And Month(arr(ir, 2)) <= Abs(i) Then
    
16.                     k = k + 1
    
17.                     brr(k) = arr(ir, 1)
    
18.                 End If
    
19.             ElseIf Year(arr(ir, 3)) > "2011" And Month(arr(ir, 3)) > Abs(i) Then
    
20.                 If Year(arr(ir, 2)) = "2011" Then
    
21.                     k = k + 1
    
22.                     brr(k) = arr(ir, 1)
    
23.                 ElseIf Year(arr(ir, 2)) = "2012" And Month(arr(ir, 2)) <= Abs(i) Then
    
24.                     k = k + 1
    
25.                     brr(k) = arr(ir, 1)
    
26.                 End If
    
27.             End If
    
28.         Next ir
    
29.         Cells(2, ic).Resize(UBound(brr), 1) = Application.Transpose(brr)
    
30.         k = 0
    
31.     Next ic
    
32. End Sub
    
33. 【函初201203班】-E10-新月gg
    

复制代码

涧水长流

=IF(AND($C2>=VALUE("2012/1/1"),VALUE(TEXT($C2,"m"))<=COLUMN(A1)),IF($D2=$D$2,$B2,IF(VALUE(TEXT($D2,"m"))>COLUMN(A1),$B2,"")),"")
=IF(AND(VALUE(TEXT($C2,"ymm"))<=1200+COLUMN(A1),OR(CHOOSE(($D2="在职")+1,VALUE(TEXT($D2,"ymm"))>1200+COLUMN(A1),1))),$B2,"")