[컴퓨터활용능력 1급 실기] 배열 수식 (액셀/스프레드시트) ✨

배열 수식 (액셀/스프레드시트)

개념

• 피연산자나 함수의 인수로 배열을 사용하여 여러 가지 계산을 하고 단일 결과나 여러 개의 결과를 동시에 반환하는 수식
• 수식에 사용되는 피연산자나 함수의 인수로 배열을 이용함.
• 수식을 입력할 때 [Ctrl] + [Shift] + [Enter] 를 누른다는 것만 다르고, 나머지는 일반 수식을 만드는 방법과 같음.
  • 단축키([Ctrl] + [Shift] + [Enter])가 작동하지 않을 경우?
    • [Windows] + [Spacebar] 버튼을 눌러서 한글 입력 체계를 변경하면 됨.
  • [Ctrl] + [Shift] + [Enter] 를 누르면, 수식의 앞뒤에 중괄호({})가 자동으로 입력됨.
    • 수식 입력줄이 활성화되면, 배열 수식의 {} 는 나타나지 않음.
• 배열 수식에 사용되는 배열 인수 각각은 동일한 개수의 행과 열을 가져야 함.
• 함수로 풀 수 없는 복잡한 계산을 수행하거나 워크시트의 다른 셀에 데이터를 입력하지 않고 하나의 식으로 값을 구하고자 할 때 사용

 

 

참고

• 배열 수식은 컴퓨터활용능력 1급 실기 시험에서 수험생들이 가장 어려워하는 부분 중 하나
• 주로 자료에 대한 통계를 내는 데 사용하는 수식이므로 패턴이 정해져 있다고 할 수 있음.
• 가끔 배열 수식에 조건문과 함수를 중첩하여 문제를 해결하는 난이도 높은 문제가 출제됨.
  • 배열 수식이 어려운 것이 아닌, 어려운 일반 함수식이 배열 수식이 결합된 것
• 3가지 방법 중 방법1이 가장 많이 출제되고 있음.
  • 방법1 : SUM, IF 함수를 사용하라
  • 방법2 : SUM 함수만을 사용하라
  • 방법3 : COUNT, IF 함수를 사용하라
• =SUM(IF(B11:B29=B3, 1, 0))은 =SUM(IF(B11:B29=B3, 1)) 와 같이 입력해도 됨.
  • 조건이 거짓일 경우, 실행할 문장이 생략된 것으로 이론적으로 아무것도 없는 NULL 값을 반환하라는 뜻
• AVERAGE, MIN, MEDIAN, SMALL 등의 함수를 배열 함수로 작성할 경우, 반드시 IF문과 함께 사용해야 함.
  • 그렇지 않을 경우 값이 이상하게 나올 수 있음.
• 배열 수식에서 조건에 따른 연결
  • AND 조건 : *
  • OR 조건 : +
    • IF 함수로 조건을 지정한 경우에만 사용할 수 있음.
      • (사용 예) 지역이 "과천"이거나 달성률 평가가 "달성"인 실적의 합계
        • =SUM( IF( ( $C$14:$C$25 = $F4 ) * ( LEFT($B$14:$B$25, 2)="과천" ) + ( $H$14:$H$25="달성" ), $E$14:$E$25 ) )

 

개수 구하기

조건이 1개일 때

조건이 1개일 때 배열 수식을 이용하여 개수를 구하는 방법 3가지

• 방법1 : =SUM( (조건) * 1 )
• 방법2 : =SUM( IF(조건, 1 ) )
• 방법3 : =COUNT( IF(조건, 1 ) )

 

조건이 2개일 때

조건이 2개일 때 배열 수식을 이용하여 개수를 구하는 방법 3가지

• 방법1 : =SUM( (조건1) * (조건2) )
• 방법2 : =SUM( IF(조건1, IF( 조건2, 1 ) ) )
• 방법3 : =COUNT( IF( (조건1) * (조건2), 1) )

 

 

합계 구하기

조건이 1개일 때

조건이 1개일 때 배열 수식을 이용하여 합계를 구하는 방법 2가지

• 방법1 : =SUM( (조건) * 합계를_구할_범위 )
• 방법2 : =SUM( IF( (조건), 합계를_구할_범위 ) )

※ 방법1을 방법2로 바꾸기
① 방법1의 조건 앞에 'IF'를 넣는다.
② 방법1에 사용된 '*'를 ','로 바꿔준다.
③ 방법1에 사용된 조건의 닫는 괄호 ')'를 맨 뒤로 옮긴다.

방법1 : =SUM( (B11:B29=B3) * F11:F29)
방법2 : =SUM( IF( B11:B29=B3, F11:F29 ) )

 

조건이 2개일 때

조건이 2개일 때 배열 수식을 이용하여 합계를 구하는 방법 2가지

• 방법1 : =SUM( (조건1) * (조건2) * 합계를_구할_범위 )
• 방법2 : =SUM( IF( (조건1) * (조건2), 합계를_구할_범위 ) )

 

조건이 3개일 때

조건이 3개일 때 배열 수식을 이용하여 합계를 구하는 방법 2가지

• 방법1 : =SUM( (조건1) * (조건2) * (조건3) * 합계를_구할_범위 )
• 방법2 : =SUM( IF( (조건1) * (조건2) * (조건3), 합계를_구할_범위 ) )

 

평균 구하기

조건이 1개일 때

조건이 1개일 때 배열 수식을 이용하여 평균을 구하는 방법

• 방법 : =AVERAGE( IF( 조건, 평균을_구할_범위 ) )

 

조건이 2개일 때

조건이 2개일 때 배열 수식을 이용하여 평균을 구하는 방법

• 방법 : =AVERAGE( IF( (조건1) * (조건2), 평균을_구할_범위 ) )

 

조건이 3개일 때

조건이 3개일 때 배열 수식을 이용하여 평균을 구하는 방법

• 방법 : =AVERAGE( IF( (조건1) * (조건2) * (조건3), 평균을_구할_범위 ) )

 

최대값 구하기

조건이 1개일 때

조건이 1개일 때 배열 수식을 이용하여 최대값을 구하는 방법 2가지

• 방법1 : =MAX( (조건) * 최대값을_구할_범위 )
• 방법2 : =MAX( IF( 조건, 최대값을_구할_범위 ) )

 

조건이 2개일 때

조건이 2개일 때 배열 수식을 이용하여 최대값을 구하는 방법 2가지

• 방법1 : =MAX( (조건1) * (조건2) * 최대값을_구할_범위 )
• 방법2 : =MAX( IF( (조건1) * (조건2), 최대값을_구할_범위 ) )

 

조건이 3개일 때

조건이 3개일 때 배열 수식을 이용하여 최대값을 구하는 방법 2가지

• 방법1 : =MAX( (조건1) * (조건2) * (조건3) * 최대값을_구할_범위 )
• 방법2 : =MAX( IF( (조건1) * (조건2) * (조건3), 최대값을_구할_범위 ) )

 

N번째로 큰 값 구하기

조건이 1개일 때

조건이 1개일 때 배열 수식을 이용하여 N번째로 큰 값을 구하는 방법 2가지

• 방법1 : =LARGE( (조건) * N번째_값을_구할_범위, N번째 )
• 방법2 : =LARGE( IF( 조건, N번째_값을_구할_범위 ), N번째 )

 

조건이 2개일 때

조건이 2개일 때 배열 수식을 이용하여 N번째로 큰 값을 구하는 방법 2가지

• 방법1 : =LARGE( (조건1) * (조건2) * N번째_값을_구할_범위, N번째 )
• 방법2 : =LARGE( IF( (조건1) * (조건2), N번째_값을_구할_범위 ), N번째 )

 

조건이 3개일 때

조건이 3개일 때 배열 수식을 이용하여 N번째로 큰 값을 구하는 방법 2가지

• 방법1 : =LARGE( (조건1) * (조건2) * (조건3) * N번째_값을_구할_범위, N번째 )
• 방법2 : =LARGE( IF( (조건1) * (조건2) * (조건3), N번째_값을_구할_범위 ), N번째 )

 

중간값 구하기

조건이 1개일 때

조건이 1개일 때 배열 수식을 이용하여 중간값을 구하는 방법

• 방법 : =MEDIAN( IF( 조건, 중간값을_구할_범위 ) )

 

조건이 2개일 때

조건이 2개일 때 배열 수식을 이용하여 중간값을 구하는 방법

• 방법 : =MEDIAN( IF( (조건1) * (조건2), 중간값을_구할_범위 ) )

 

조건이 3개일 때

조건이 3개일 때 배열 수식을 이용하여 중간값을 구하는 방법

• 방법 : =MEDIAN( IF( (조건1) * (조건2) * (조건3), 중간값을_구할_범위 ) )

 

행(열)의 값 구하기

조건이 1개일 때

조건이 1개일 때 배열 수식을 이용하여 행(열)의 값을 구하는 방법

• 방법 : =INDEX( 범위, MATCH( 찾을_값, 찾을_값을_포함한_범위, 0 ) )

 

조건이 2개일 때

조건이 2개일 때 배열 수식을 이용하여 행(열)의 값을 구하는 방법

• 방법 : =INDEX( 범위, MATCH( MAX( (조건) * 관련범위 ), (조건) * 관련범위, 0 ) )