배열수식, SUMPRODUCT 의 속도를 올리자

안녕하세요. 오즈맨 입니다.   배열수식의 내용을 알아봅니다.    시트명은 Korean 입니다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 19994 19995 19996 19997 19998 19999 20000

A B C D E F G H I J K L M 1989-08/17 3955 형식1=SUMPRODUCT(--(조건)) 1995-06/10 16045 형식1=SUMPRODUCT(--(조건)) 1973-04/27 3955 형식2=SUMPRODUCT((조건)*1) 1963-06/13 16045 형식2=SUMPRODUCT((조건)*1) 1982-10/14 3955 형식3 배열수식=SUM(IF(조건,1,""))} 1967-01/07 16045 형식3 배열수식=SUM(IF(조건,1,""))} 1975-05/12 각 형식 별 소요시간 계산 (총 10 회)  단위 초/ % 평균 1954-01/16 형식 1 2.047 2.031 2.063 2.063 2.063 2.094 2.063 2.063 2.047 2.031 2.056 1957-01/22 1955-02/19 형식 2 2.078 2.047 2.078 2.078 2.109 2.094 2.094 2.109 2.125 2.063 2.088 1975-03/13 1과의 차이 0.031 0.016 0.016 0.016 0.047 0.000 0.031 0.047 0.078 0.031 0.031 1985-05/03 비율 1.53% 0.77% 0.76% 0.76% 2.27% 0.00% 1.52% 2.27% 3.82% 1.54% 1.52% 1956-02/24 1997-08/05 형식 3 2.172 2.219 2.203 2.188 2.250 2.203 2.172 2.172 2.203 2.203 2.198 1989-08/17 1과의 차이 0.125 0.188 0.141 0.125 0.188 0.109 0.109 0.109 0.156 0.172 0.142 1950-10/02 비율 6.11% 9.23% 6.82% 6.06% 9.09% 5.22% 5.30% 5.30% 7.63% 8.46% 6.92% 1954-06/07 1992-05/25 1958-03/01                      준비한 자료    1949.10.30~2007.4.27 까지의 불규칙한 날짜 20,000 개    경과기간이 50년 이하, 초과 를 구분해서 개수를 구함    부득이하게    하나의 조건으로 sumproduct 혹은 배열수식 sum(if( 를 사용할 경우 자료입니다 2005-02/07 1990-09/04 1972-09/25 1951-03/20 1957-10/06

Korean 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003    분홍색 수식이 있으면 수식 입력에 주의하세요 !!!배열수식이에요.    수식만 입력하고 Ctrl + Shift + Enter 하면 {} 는 자동으로 생깁니다.    $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No	셀주소	왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다. (복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데, 익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.	결과	수식을
1	C11	=C10-C$8	0.031
2		C11  셀의 수식을 여기에 복사하세요 -→ C11:L11,C15:L15
3	C12	=C11/C$8	1.53%
4		C12  셀의 수식을 여기에 복사하세요 -→ C12:L12,C16:L16
5	D1	=SUMPRODUCT(--(DATEDIF(A1:A20000,TODAY(),"y")>50))	3955
6	D2	=SUMPRODUCT(--(DATEDIF(A1:A20000,TODAY(),"y")< =50))	16045
7	D3	=SUMPRODUCT((DATEDIF(A1:A20000,TODAY(),"y")>50)*1)	3955
8	D4	=SUMPRODUCT((DATEDIF(A1:A20000,TODAY(),"y")< =50)*1)	16045
9	D5	=SUM(IF(DATEDIF(A1:A20000,TODAY(),"y")>50,1,""))	3955
10	D6	=SUM(IF(DATEDIF(A1:A20000,TODAY(),"y")< =50,1,""))	16045
11	M8	=AVERAGE(C8:L8)	2.056
12		M8  셀의 수식을 여기에 복사하세요 -→ M8,M10:M12,M14:M16
		sumproduct 앞의 -- 는 뺄셈이 아니라 단순히 ((부호변경))을 함으로써 논리값(True/False)을 1/0 으로 변경합니다. 뺄셈이 아니므로 소요시간이 덜 걸리는 잇점이 있습니다.
보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다.      첨부파일이 없습니다, 원하는 부분을 시트(셀)에 붙여 넣으세요.    분홍색 수식이 있으면 수식 입력에 주의하세요 !!!    ※ 만일 수식에 사용된 공백때문에 오류(다른 값)가 나오면 공백을 확인바랍니다.

-- 사용한 코드입니다 -- Option Explicit
Sub x()
Dim t(1 To 6) As Double, i As Double
Dim Answ(1 To 9, 1 To 1)
Dim rng As Range
Sheet1.Range("o1") = "."
Sheet1.Range("b1:b2").ClearContents
Sheet2.Range("o1") = "."
Sheet2.Range("b1:b2").ClearContents
Range("b1").Formula = "=SUMPRODUCT(--(DATEDIF(A1:A20000,TODAY(),""y"")>50))"
Range("b2").Formula = "=SUMPRODUCT(--(DATEDIF(A1:A20000,TODAY(),""y"")<=50))"
    t(1) = Timer
    For i = 1 To 10
    Application.Calculate
    Next
    t(2) = Timer    
Range("b1").Formula = "=SUMPRODUCT((DATEDIF(A1:A20000,TODAY(),""y"")>50)*1)"
Range("b2").Formula = "=SUMPRODUCT((DATEDIF(A1:A20000,TODAY(),""y"")<=50)*1)"
    t(3) = Timer
    For i = 1 To 10
    Application.Calculate
    Next
    t(4) = Timer
Range("b1").FormulaArray = "=SUM(IF(DATEDIF(A1:A20000,TODAY(),""y"")>50,1,""""))"
Range("b2").FormulaArray = "=SUM(IF(DATEDIF(A1:A20000,TODAY(),""y"")<=50,1,""""))"
    t(5) = Timer
    For i = 1 To 10
    Application.Calculate
    Next
    t(6) = Timer
Answ(1, 1) = (t(2) - t(1)) / 24 / 60 / 60 '첫결과
Answ(3, 1) = (t(4) - t(3)) / 24 / 60 / 60 '결과2
Answ(5, 1) = (t(6) - t(5)) / 24 / 60 / 60 '결과3
    Set rng = Range("iv1").End(xlToLeft).Offset(, 1)
    rng.Resize(5, 1).NumberFormat = "[s].000"
    rng.Resize(5, 1) = Answ
Erase t
Erase Answ
Set rng = Nothing
Range("b1:b2").ClearContents
Beep
End Sub