SUMPRODUCT 사용 예제 - 2

엑셀보조파일,기능,함수 2008. 10. 4. 20:57


 [ SUMPRODUCT 사용 예제 - 2 ] 에 대해 알아봅니다.
   --->
   두 개의 열에서 OR 조건 개수 구하기
 
1
2
3
4
5
6
7
8
9
10
11
A B C D E F G H I J K L M
    홍길동 혹은 부산 의 개수는? 홍길동 혹은 부산 의 개수는? 한방에 모은다면?
이름 지역 홍길동? 부산 ? or 로... 숫자로 홍길동? 부산 ? or 로... 숫자로 조건1+2 [1+2]>0 ? 숫자로
홍길동 부산 TRUE TRUE TRUE 1 TRUE TRUE TRUE 1 2 TRUE 1
삼식이 제주 FALSE FALSE FALSE 0 FALSE FALSE TRUE 1 0 FALSE 0
심순애 부산 FALSE TRUE TRUE 1 FALSE TRUE TRUE 1 1 TRUE 1
홍길동 제주 TRUE FALSE TRUE 1 TRUE FALSE TRUE 1 1 TRUE 1
삼식이 부산 FALSE TRUE TRUE 1 FALSE TRUE TRUE 1 1 TRUE 1
심순애 제주 FALSE FALSE FALSE 0 FALSE FALSE TRUE 1 0 FALSE 0
홍길동 부산 TRUE TRUE TRUE 1 TRUE TRUE TRUE 1 2 TRUE 1
          5 뭔가 오류??? 7 7   5
단순히 + 를 OR 로 적용한 수식은 엉뚱한 결과가 나올 수 있습니다. 7     5


Sheet2 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003
   분홍색 수식이 있으면 수식 입력에 주의하세요 !!!배열수식이에요.
   수식만 입력하고 Ctrl + Shift + Enter 하면 {} 는 자동으로 생깁니다.
   $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No 셀주소 왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다.
(복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데,
익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.		 결과 수식을
1 C3 =A3="홍길동" TRUE
2   C3  셀의 수식을 여기에 복사하세요 -→ C3:C9    
3 D3 =B3="부산" TRUE
4   D3  셀의 수식을 여기에 복사하세요 -→ D3:D9    
5 E3 =OR(C3,D3) TRUE
6   E3  셀의 수식을 여기에 복사하세요 -→ E3:E9    
7 F3 =E3*1 1
8   F3  셀의 수식을 여기에 복사하세요 -→ F3:F9    
9 G3:G9 =A3:A9="홍길동"
셀 주소 모두를 선택하고 입력하세요		 TRUE
10 H3:H9 =B3:B9="부산"
셀 주소 모두를 선택하고 입력하세요		 TRUE
11 I3:I9 =OR(G3:G9,H3:H9)
셀 주소 모두를 선택하고 입력하세요		 TRUE
12 J3:J9 =I3:I9*1
셀 주소 모두를 선택하고 입력하세요		 1
13 K3 =(A3="홍길동")+(B3="부산") 2
14   K3  셀의 수식을 여기에 복사하세요 -→ K3:K9    
15 L3 =K3>0 TRUE
16   L3  셀의 수식을 여기에 복사하세요 -→ L3:L9    
17 M3 =--L3 1
18   M3  셀의 수식을 여기에 복사하세요 -→ M3:M9    
19 F10 =SUM(F3:F9) 5
20   F10  셀의 수식을 여기에 복사하세요 -→ F10,J10:K10,M10    
21 J11 =SUMPRODUCT((A3:A9="홍길동")+(B3:B9="부산"))
 [참고] 생각치 못한 엉뚱한 결과입니다.		 7
22 M11 =SUMPRODUCT(--((A3:A9="홍길동")+(B3:B9="부산")>0))
 [참고] 올바른 결과입니다.		 5

   보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다.  
   첨부파일이 없습니다, 원하는 부분을 시트(셀)에 붙여 넣으세요.
   분홍색 수식이 있으면 수식 입력에 주의하세요 !!!
     도움이 되시기를 바랍니다.

   참고로 배열수식은...
   수식의 결과가 하나가 아닌 여러개의 결과를 가집니다.
   배열수식의 사용의 주된 용도는
   그 결과중 특정한 몇 번째 값을 가져오는 등의 용도입니다.
   수식을 입력할 때 수식입력줄에 {} 기호가 나오지 않는 것은
   수식을 입력할때 {} 문자를 사용자가 입력하는 것을 방지함이 그 목적이며
   이미 입력된 수식의 내용이 수식입력줄에서 {} 로 둘러싸이는 것은
   수식 입력을 배열수식 형태로 입력했다(컨트롤+시프트+엔터)는 의미이고
   이미 입력된 수식의 수식입력줄에 {} 가 보이지 않는것은
   실제 수식 내용이 배열수식일지라도
   사용자가 임의로(혹은 실수로) 일반수식처럼 입력했다는 의미입니다.

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

수식에서 사용하는 -- 의 역할 예제 입니다.  (1) 2009.02.10
매크로 보안으로 안전하게 인증서 -1 인증서 만들기 사용하기 지우기  (0) 2009.01.23
엑셀 VBE 에서 나오는 오류! VBE 메서드에서 _Application 개체의 오류가...  (1) 2008.11.07
SUMPRODUCT 사용 예제 - 1  (1) 2008.10.04
엑셀 2003 2007 동시에 설치하기  (4) 2008.10.03
오즈맨의 Sheet to Web 2.2 (Sheet2Web)  (3) 2008.10.02
숫자 문자 혼용한 숫자에 콤마를 삽입하는 사용자 정의함수  (0) 2008.09.24
Posted by 오즈맨스머프

SUMPRODUCT 사용 예제 - 1

엑셀보조파일,기능,함수 2008. 10. 4. 20:41


 [ SUMPRODUCT 사용 예제 - 1 ] 에 대해 알아봅니다.
   --->
   하나의 열에서 OR 조건 개수 구하기
 
1
2
3
4
5
6
7
8
9
10
11
A B C D E F G
  홍길동 혹은 삼식이 의 개수는? 한방에 모은다면?
이름 홍길동? 삼식이? 둘의 합 홍길동? 삼식이? 둘의 합
홍길동 TRUE FALSE 1 TRUE FALSE 1
삼식이 FALSE TRUE 1 FALSE TRUE 1
심순애 FALSE FALSE 0 FALSE FALSE 0
홍길동 TRUE FALSE 1 TRUE FALSE 1
삼식이 FALSE TRUE 1 FALSE TRUE 1
심순애 FALSE FALSE 0 FALSE FALSE 0
홍길동 TRUE FALSE 1 TRUE FALSE 1
  5   5     5
하나의 열에 or 조건을 사용하면 원하는 결과가 나옵니다.


Sheet1 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003
   분홍색 수식이 있으면 수식 입력에 주의하세요 !!!배열수식이에요.
   수식만 입력하고 Ctrl + Shift + Enter 하면 {} 는 자동으로 생깁니다.
   $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No 셀주소 왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다.
(복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데,
익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.		 결과 수식을
1 B3 =A3="홍길동" TRUE
2   B3  셀의 수식을 여기에 복사하세요 -→ B3:B9    
3 C3 =A3="삼식이" FALSE
4   C3  셀의 수식을 여기에 복사하세요 -→ C3:C9    
5 D3 =B3+C3 1
6   D3  셀의 수식을 여기에 복사하세요 -→ D3:D9,G3:G9    
7 E3:E9 =A3:A9="홍길동"
셀 주소 모두를 선택하고 입력하세요		 TRUE
8 F3:F9 =A3:A9="삼식이"
셀 주소 모두를 선택하고 입력하세요		 FALSE
9 B10 =SUMPRODUCT((A3:A9="홍길동")+(A3:A9="삼식이")) 5
10 D10 =COUNTIF(A3:A9,"홍길동")+COUNTIF(A3:A9,"삼식이") 5
11 G10 =SUM(G3:G9) 5

   보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다.  
   첨부파일이 없습니다, 원하는 부분을 시트(셀)에 붙여 넣으세요.
   분홍색 수식이 있으면 수식 입력에 주의하세요 !!!
     도움이 되시기를 바랍니다.

   참고로 배열수식은...
   수식의 결과가 하나가 아닌 여러개의 결과를 가집니다.
   배열수식의 사용의 주된 용도는
   그 결과중 특정한 몇 번째 값을 가져오는 등의 용도입니다.
   수식을 입력할 때 수식입력줄에 {} 기호가 나오지 않는 것은
   수식을 입력할때 {} 문자를 사용자가 입력하는 것을 방지함이 그 목적이며
   이미 입력된 수식의 내용이 수식입력줄에서 {} 로 둘러싸이는 것은
   수식 입력을 배열수식 형태로 입력했다(컨트롤+시프트+엔터)는 의미이고
   이미 입력된 수식의 수식입력줄에 {} 가 보이지 않는것은
   실제 수식 내용이 배열수식일지라도
   사용자가 임의로(혹은 실수로) 일반수식처럼 입력했다는 의미입니다.

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

매크로 보안으로 안전하게 인증서 -1 인증서 만들기 사용하기 지우기  (0) 2009.01.23
엑셀 VBE 에서 나오는 오류! VBE 메서드에서 _Application 개체의 오류가...  (1) 2008.11.07
SUMPRODUCT 사용 예제 - 2  (0) 2008.10.04
엑셀 2003 2007 동시에 설치하기  (4) 2008.10.03
오즈맨의 Sheet to Web 2.2 (Sheet2Web)  (3) 2008.10.02
숫자 문자 혼용한 숫자에 콤마를 삽입하는 사용자 정의함수  (0) 2008.09.24
배열수식의 해설 - 3,4  (0) 2008.09.24
Posted by 오즈맨스머프

배열수식의 해설 - 3,4

엑셀보조파일,기능,함수 2008. 9. 24. 00:51


 [ 배열수식의 해설 - 3,4 ] 에 대해 알아봅니다.
 
1 
2 
3 
4 
5 
6 
7 
8 
9 
10 
11 
12 
13 
14 
15 
16 
17 
18 
19 
20 
21 
22 
23 
24 
25 
26 
27 
28 
29 
30 
31 
32 
33 
34 
35 
36 
37 
38 
39 
40 
ABCDEFGHIJK
(3)   =SUMPRODUCT((영역=조건),(영역=조건),(합계영역))
=SUMPRODUCT((A3:A11=B16),(B3:B11=C16),(C3:C11))
지역담당자금액FALSE0FALSE0금액000 * 0 * 0
부산홍길동10TRUE0TRUE0101000 * 0 * 10
제주심순애20FALSE0FALSE0202000 * 0 * 20
대구홍길동30FALSE0TRUE0303000 * 0 * 30
독도심순애40FALSE0FALSE0404000 * 0 * 40
부산홍길동50TRUE0TRUE0505000 * 0 * 50
제주심순애60FALSE0FALSE0606000 * 0 * 60
대구홍길동70FALSE0TRUE0707000 * 0 * 70
독도심순애80FALSE0FALSE0808000 * 0 * 80
계360조건1조건2합계영역0합계   [=조건1*조건2*합계]
{=IF(ISNUMBER(H3:H11),H3:H11,0)}
{=IF(ISNUMBER(F3:F11),F3:F11,0)}
조건지역담당자{=IF(ISNUMBER(D3:D11),D3:D11,0)}
부산홍길동SUMPRODUCT 함수는 각 인수를 1:1 로 곱하되
[[숫자가 아니면 모두 0 으로 변경합니다]]
TRUE , FALSE 에 +-*/ 연산을 안하므로 0 으로 계산합니다.
합계해설
0해설 3=SUMPRODUCT((A3:A11=B16),(B3:B11=C16),(C3:C11))
60해설 4=SUMPRODUCT(--(A3:A11=B16),--(B3:B11=C16),(C3:C11))
논리값
(TRUE,FALSE)을
그대로 두고
함수에 적용함과
논리값
(TRUE,FALSE)에
 -- 연산을 함이
서로 다른 결과를
 가져옵니다.		(4)   =SUMPRODUCT(--(영역=조건),--(영역=조건),(합계영역))
=SUMPRODUCT(--(A3:A11=B16),--(B3:B11=C16),(C3:C11))
0000금액000 * 0 * 0
11111010101 * 1 * 10
0000202000 * 0 * 20
0011303000 * 1 * 30
0000404000 * 0 * 40
11115050501 * 1 * 50
0000606000 * 0 * 60
0011707000 * 1 * 70
0000808000 * 0 * 80
조건1조건2합계영역60합계   [=조건1*조건2*합계]
{=IF(ISNUMBER(--(H24:H32)),--(H24:H32),0)}
{=IF(ISNUMBER(--(F24:F32)),--(F24:F32),0)}
{=IF(ISNUMBER(--(D24:D32)),--(D24:D32),0)}
TRUE , FALSE 에 -- 연산을 하므로 1 로 계산합니다.
논리결과(TRUE,FALSE)에 -1을 두번 곱합니다.
그러나 사실은 [[부호변경 역할을 두번 수행합니다.]]
숫자가 아닌것은 모두 0 으로 인식합니다(첫 행의 문자 "금액"은 0 으로 인식합니다)


Sheet3 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003
   분홍색 수식이 있으면 수식 입력에 주의하세요 !!!배열수식이에요.
   수식만 입력하고 Ctrl + Shift + Enter 하면 {} 는 자동으로 생깁니다.
   $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No셀주소왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다.
(복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데,
익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.		결과수식을
1D3:D11=(A3:A11=B16)
셀 주소 모두를 선택하고 입력하세요		FALSE
2E3:E11=IF(ISNUMBER(D3:D11),D3:D11,0)
셀 주소 모두를 선택하고 입력하세요		0
3 E3  셀의 수식을 여기에 복사하세요 -→ E3,G3,I3  
4F3:F11=(B3:B11=C16)
셀 주소 모두를 선택하고 입력하세요		FALSE
5H3:H11=(C3:C11)
셀 주소 모두를 선택하고 입력하세요		금액
6J3:J11=E3:E11*G3:G11*I3:I11
셀 주소 모두를 선택하고 입력하세요		0
7 J3  셀의 수식을 여기에 복사하세요 -→ J3,J24  
8K3=E3&" * "&G3&" * "&I30 * 0 * 0
9 K3  셀의 수식을 여기에 복사하세요 -→ K3:K11,K24:K32  
10J12=SUM(J3:J11)0
11 J12  셀의 수식을 여기에 복사하세요 -→ J12,J33  
12D24:D32=--(A3:A11=B16)
셀 주소 모두를 선택하고 입력하세요		0
13E24:E32=IF(ISNUMBER(--(D24:D32)),--(D24:D32),0)
셀 주소 모두를 선택하고 입력하세요		0
14 E24  셀의 수식을 여기에 복사하세요 -→ E24,G24,I24  
15F24:F32=--(B3:B11=C16)
셀 주소 모두를 선택하고 입력하세요		0
16H24:H32=(C3:C11)
셀 주소 모두를 선택하고 입력하세요		금액

   보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다.   
   첨부파일이 없습니다, 원하는 부분을 시트(셀)에 붙여 넣으세요.
   분홍색 수식이 있으면 수식 입력에 주의하세요 !!!
     도움이 되시기를 바랍니다.

   참고로 배열수식은...
   수식의 결과가 하나가 아닌 여러개의 결과를 가집니다.
   배열수식의 사용의 주된 용도는
   그 결과중 특정한 몇 번째 값을 가져오는 등의 용도입니다.
   수식을 입력할 때 수식입력줄에 {} 기호가 나오지 않는 것은
   수식을 입력할때 {} 문자를 사용자가 입력하는 것을 방지함이 그 목적이며
   이미 입력된 수식의 내용이 수식입력줄에서 {} 로 둘러싸이는 것은
   수식 입력을 배열수식 형태로 입력했다(컨트롤+시프트+엔터)는 의미이고
   이미 입력된 수식의 수식입력줄에 {} 가 보이지 않는것은
   실제 수식 내용이 배열수식일지라도
   사용자가 임의로(혹은 실수로) 일반수식처럼 입력했다는 의미입니다.

     합계영역을 지정하기 않으면 조건에 맞는 개수를 구합니다.

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

엑셀 2003 2007 동시에 설치하기  (4) 2008.10.03
오즈맨의 Sheet to Web 2.2 (Sheet2Web)  (3) 2008.10.02
숫자 문자 혼용한 숫자에 콤마를 삽입하는 사용자 정의함수  (0) 2008.09.24
배열수식의 해설 - 1,2  (0) 2008.09.24
배열수식의 응용-1  (0) 2008.09.24
연속적으로 이어지는 셀 주소 만드는 사용자 함수 (ShrStrings)  (0) 2008.09.18
숫자를 영어로 나타내는 사용자 함수 (NUM2DOLLAR)  (0) 2008.09.09
Posted by 오즈맨스머프

배열수식의 해설 - 1,2

엑셀보조파일,기능,함수 2008. 9. 24. 00:51


 [ 배열수식의 해설 - 1,2 ] 에 대해 알아봅니다.
 
1 
2 
3 
4 
5 
6 
7 
8 
9 
10 
11 
12 
13 
14 
15 
16 
17 
18 
19 
20 
21 
22 
23 
24 
25 
26 
27 
28 
29 
30 
31 
32 
33 
34 
35 
36 
37 
38 
39 
40 
ABCDEF
(1)   {=SUM((영역=조건)*(영역=조건)*(합계영역))}
{=SUM((A3:A11=B16)*(B3:B11=C16)*(C3:C11))}
지역담당자금액#VALUE!=(지역=부산)*(담당자=홍길동)*금액=(FALSE) * (FALSE) * 금액
부산홍길동1010=(부산=부산)*(홍길동=홍길동)*10=(TRUE) * (TRUE) * 10
제주심순애200=(제주=부산)*(심순애=홍길동)*20=(FALSE) * (FALSE) * 20
대구홍길동300=(대구=부산)*(홍길동=홍길동)*30=(FALSE) * (TRUE) * 30
독도심순애400=(독도=부산)*(심순애=홍길동)*40=(FALSE) * (FALSE) * 40
부산홍길동5050=(부산=부산)*(홍길동=홍길동)*50=(TRUE) * (TRUE) * 50
제주심순애600=(제주=부산)*(심순애=홍길동)*60=(FALSE) * (FALSE) * 60
대구홍길동700=(대구=부산)*(홍길동=홍길동)*70=(FALSE) * (TRUE) * 70
독도심순애800=(독도=부산)*(심순애=홍길동)*80=(FALSE) * (FALSE) * 80
계360수식내용1수식내용2수식내용3
{="=("&(A3:A11=B16)&") * ("&(B3:B11=C16)&") * "&C3:C11}
{="=("&A3:A11&"="&B16&")*("&B3:B11&"="&C16&")*"&(C3:C11)}
조건지역담당자{=(A3:A11=B16)*(B3:B11=C16)*(C3:C11)}
부산홍길동#VALUE!합계   오류=SUM(D3:D11)
합을 구할 영역은 반드시 숫자 이어야하겠지요. 첫 행에서 당연 오류가 납니다
합계해설이는 SUMPRODUCT 도 마찬가지입니다
#VALUE!해설 1수식{=SUM((A3:A11=B16)*(B3:B11=C16)*(C3:C11))}
#VALUE!수식오류=SUMPRODUCT((A3:A11=B16)*(B3:B11=C16)*(C3:C11))
60해설 2수식{=SUM((A4:A11=B16)*(B4:B11=C16)*(C4:C11))}
60수식=SUMPRODUCT((A4:A11=B16)*(B4:B11=C16)*(C4:C11))
첫 행에 논리값과 문자를 곱한게 문제입니다(2)   {=SUM((영역=조건)*(영역=조건)*(합계영역))}
{=SUM((A4:A11=B16)*(B4:B11=C16)*(C4:C11))}
[[ 합계영역에는 숫자만 있습니다 ]]
10(부산=부산)*(홍길동=홍길동)*10(TRUE) * (TRUE) * 10
0(제주=부산)*(심순애=홍길동)*20(FALSE) * (FALSE) * 20
0(대구=부산)*(홍길동=홍길동)*30(FALSE) * (TRUE) * 30
0(독도=부산)*(심순애=홍길동)*40(FALSE) * (FALSE) * 40
50(부산=부산)*(홍길동=홍길동)*50(TRUE) * (TRUE) * 50
0(제주=부산)*(심순애=홍길동)*60(FALSE) * (FALSE) * 60
0(대구=부산)*(홍길동=홍길동)*70(FALSE) * (TRUE) * 70
0(독도=부산)*(심순애=홍길동)*80(FALSE) * (FALSE) * 80
수식내용1수식내용2수식내용3
{="("&(A4:A11=B16)&") * ("&(B4:B11=C16)&") * "&C4:C11}
{="("&A4:A11&"="&B16&")*("&B4:B11&"="&C16&")*"&(C4:C11)}
{=(A4:A11=B16)*(B4:B11=C16)*(C4:C11)}
60합계   =SUM(D27:D34)
TRUE , FALSE 에 +-*/ 연산을 하면 1,0 으로 계산합니다.


Sheet2 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003
   분홍색 수식이 있으면 수식 입력에 주의하세요 !!!배열수식이에요.
   수식만 입력하고 Ctrl + Shift + Enter 하면 {} 는 자동으로 생깁니다.
   $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No셀주소왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다.
(복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데,
익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.		결과수식을
1D3:D11=(A3:A11=B16)*(B3:B11=C16)*(C3:C11)
셀 주소 모두를 선택하고 입력하세요		#VALUE!
2E3:E11="=("&A3:A11&"="&B16&")*("&B3:B11&"="&C16&")*"&(C3:C11)
셀 주소 모두를 선택하고 입력하세요		=(지역=부산)*(담당자=홍길동)*금액
3F3:F11="=("&(A3:A11=B16)&") * ("&(B3:B11=C16)&") * "&C3:C11
셀 주소 모두를 선택하고 입력하세요		=(FALSE) * (FALSE) * 금액
4D16=SUM(D3:D11)#VALUE!
5B19=SUM((A3:A11=B16)*(B3:B11=C16)*(C3:C11))#VALUE!
6B20=SUMPRODUCT((A3:A11=B16)*(B3:B11=C16)*(C3:C11))#VALUE!
7B21=SUM((A4:A11=B16)*(B4:B11=C16)*(C4:C11))60
8B22=SUMPRODUCT((A4:A11=B16)*(B4:B11=C16)*(C4:C11))60
9D27:D34=(A4:A11=B16)*(B4:B11=C16)*(C4:C11)
셀 주소 모두를 선택하고 입력하세요		10
10E27:E34="("&A4:A11&"="&B16&")*("&B4:B11&"="&C16&")*"&(C4:C11)
셀 주소 모두를 선택하고 입력하세요		(부산=부산)*(홍길동=홍길동)*10
11F27:F34="("&(A4:A11=B16)&") * ("&(B4:B11=C16)&") * "&C4:C11
셀 주소 모두를 선택하고 입력하세요		(TRUE) * (TRUE) * 10
12D39=SUM(D27:D34)60

   보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다.   
   첨부파일이 없습니다, 원하는 부분을 시트(셀)에 붙여 넣으세요.
   분홍색 수식이 있으면 수식 입력에 주의하세요 !!!
     도움이 되시기를 바랍니다.

   참고로 배열수식은...
   수식의 결과가 하나가 아닌 여러개의 결과를 가집니다.
   배열수식의 사용의 주된 용도는
   그 결과중 특정한 몇 번째 값을 가져오는 등의 용도입니다.
   수식을 입력할 때 수식입력줄에 {} 기호가 나오지 않는 것은
   수식을 입력할때 {} 문자를 사용자가 입력하는 것을 방지함이 그 목적이며
   이미 입력된 수식의 내용이 수식입력줄에서 {} 로 둘러싸이는 것은
   수식 입력을 배열수식 형태로 입력했다(컨트롤+시프트+엔터)는 의미이고
   이미 입력된 수식의 수식입력줄에 {} 가 보이지 않는것은
   실제 수식 내용이 배열수식일지라도
   사용자가 임의로(혹은 실수로) 일반수식처럼 입력했다는 의미입니다.

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

오즈맨의 Sheet to Web 2.2 (Sheet2Web)  (3) 2008.10.02
숫자 문자 혼용한 숫자에 콤마를 삽입하는 사용자 정의함수  (0) 2008.09.24
배열수식의 해설 - 3,4  (0) 2008.09.24
배열수식의 응용-1  (0) 2008.09.24
연속적으로 이어지는 셀 주소 만드는 사용자 함수 (ShrStrings)  (0) 2008.09.18
숫자를 영어로 나타내는 사용자 함수 (NUM2DOLLAR)  (0) 2008.09.09
여러 영역의 문자열 합치는 사용자 함수 (STRJOIN)  (0) 2008.09.07
Posted by 오즈맨스머프

배열수식의 응용-1

엑셀보조파일,기능,함수 2008. 9. 24. 00:50


 [ 배열수식의 기본 ] 에 대해 알아봅니다.
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ABCDEFGH
배열수식이란홍길동이 광주에서 근무한 횟수는?한방에 모은다면?
이름근무처이름=홍길동?근무처=광주?둘의 곱이름=홍길동?근무처=광주?둘의 곱
홍길동광주TRUETRUE1TRUETRUE1
삼식이대전FALSEFALSE0FALSEFALSE0
심순애광주FALSETRUE0FALSETRUE0
홍길동대전TRUEFALSE0TRUEFALSE0
삼식이광주FALSETRUE0FALSETRUE0
심순애대전FALSEFALSE0FALSEFALSE0
홍길동광주TRUETRUE1TRUETRUE1
삼식이대전FALSEFALSE0FALSEFALSE0
심순애대전FALSEFALSE0FALSEFALSE0
합계2합계2
참고로 엑셀에서는 TRUE/FALSE에 +-*/를 직접 계산하면 1/0 으로 인식합니다
한방에 모은 수식을 하나로 작성한다면 오른쪽과 같습니다
예제의 배열수식은 각행(열)의 연산을 한 다음 그들의 합을 구하는 방법입니다.		2
2


Sheet1 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003
   분홍색 수식이 있으면 수식 입력에 주의하세요 !!!배열수식이에요.
   수식만 입력하고 Ctrl + Shift + Enter 하면 {} 는 자동으로 생깁니다.
   $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No셀주소왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다.
(복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데,
익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.		결과수식을
1C3=A3="홍길동"TRUE
2 C3  셀의 수식을 여기에 복사하세요 -→ C3:C11  
3D3=B3="광주"TRUE
4 D3  셀의 수식을 여기에 복사하세요 -→ D3:D11  
5E3=C3*D31
6 E3  셀의 수식을 여기에 복사하세요 -→ E3:E11  
7F3:F11=A3:A11="홍길동"
셀 주소 모두를 선택하고 입력하세요		TRUE
8G3:G11=B3:B11="광주"
셀 주소 모두를 선택하고 입력하세요		TRUE
9H3:H11=F3:F11*G3:G11
셀 주소 모두를 선택하고 입력하세요		1
10E12=SUM(E3:E11)2
11 E12  셀의 수식을 여기에 복사하세요 -→ E12,H12  
12H14=SUM((A3:A11="홍길동")*(B3:B11="광주"))2
13H15=SUMPRODUCT((A3:A11="홍길동")*(B3:B11="광주"))2

   보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다.  
   첨부파일이 없습니다, 원하는 부분을 시트(셀)에 붙여 넣으세요.
   분홍색 수식이 있으면 수식 입력에 주의하세요 !!!
     도움이 되시기를 바랍니다.

   참고로 배열수식은...
   수식의 결과가 하나가 아닌 여러개의 결과를 가집니다.
   배열수식의 사용의 주된 용도는
   그 결과중 특정한 몇 번째 값을 가져오는 등의 용도입니다.
   수식을 입력할 때 수식입력줄에 {} 기호가 나오지 않는 것은
   수식을 입력할때 {} 문자를 사용자가 입력하는 것을 방지함이 그 목적이며
   이미 입력된 수식의 내용이 수식입력줄에서 {} 로 둘러싸이는 것은
   수식 입력을 배열수식 형태로 입력했다(컨트롤+시프트+엔터)는 의미이고
   이미 입력된 수식의 수식입력줄에 {} 가 보이지 않는것은
   실제 수식 내용이 배열수식일지라도
   사용자가 임의로(혹은 실수로) 일반수식처럼 입력했다는 의미입니다.

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

숫자 문자 혼용한 숫자에 콤마를 삽입하는 사용자 정의함수  (0) 2008.09.24
배열수식의 해설 - 3,4  (0) 2008.09.24
배열수식의 해설 - 1,2  (0) 2008.09.24
연속적으로 이어지는 셀 주소 만드는 사용자 함수 (ShrStrings)  (0) 2008.09.18
숫자를 영어로 나타내는 사용자 함수 (NUM2DOLLAR)  (0) 2008.09.09
여러 영역의 문자열 합치는 사용자 함수 (STRJOIN)  (0) 2008.09.07
엑셀 2007 에 있는 SUMIFS 를 2003 에서 ... SUMIFS2003  (1) 2008.09.05
Posted by 오즈맨스머프

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

엑셀보조파일,기능,함수 2008. 9. 3. 16:47


  안녕하세요. 오즈맨 입니다.
  배열수식의 내용을 알아봅니다.    시트명은 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
저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

엑셀 2007 에 있는 SUMIFS 를 2003 에서 ... SUMIFS2003  (1) 2008.09.05
엑셀 2007 에 있는 COUNTIFS 를 2003 에서 ... COUNTIFS2003  (0) 2008.09.05
엑셀 2007 에 있는 IFERROR 를 2003 에서 ... IFERROR2003  (1) 2008.09.04
오즈맨의 Sheet to Web 2.00 (Sheet2Web)  (0) 2008.08.28
오즈맨의 Sheet to Web 1.9 Up  (0) 2008.08.19
엑셀 2007 2003 동시에 깔려있을때 xls 지정하기  (0) 2008.07.13
Office. 자동 고침 옵션에 한/영 자동 고침이 없으면  (0) 2008.07.11
Posted by 오즈맨스머프

티스토리툴바