엑셀에서 배열함수까지 완벽하게 이해하면 거의 도사급이라는 얘기를 들을 수 있을 것이다.
그 만큼 이해하기가 까다롭고 함수마다 조금씩 차이가 있기 때문이다.
엑셀에서 배열이란 숫자를 담을 수 있는 수 개의 저장 장소를 의미하며
이 장소는 컴퓨터 내부 메모리에 존재하며 컴퓨터가 알아서 만들었다 없앴다 하는 공간이다.
배열함수는 지정한 영역내의 데이터를 순차적으로 비교하고 계산하며
그 각각의 계산값이 존재하는 경우(0도 포함) 배열을 하나씩 증가시키며
증가시킨 배열에 계산값을 담아둔다.
영역내의 모든 작업이 끝난 후에 배열함수는 배열내에 저장된 계산값을 이용해서
함수를 수행하고 그 결과값을 최종 반환한다.
일단 배열 함수는 하나 이상의 지정한 영역(한개의 열 혹은 행)에서 조건에 맞는 데이타를 골라
계산하고 계산값을 이용해서 함수를 수행할 때 사용한다.
배열 함수는 수식을 입력한 후에 Control + Shift + Enter 키를 쳐서 완성하는데
이때 수식 앞 뒤에 중괄호 { } 가 나타나게 된다.
배열 함수의 형태는 {=함수(계산식)} 의 형태를 갖게 된다.
계산식은 *, /, +, - 의 연산자로 구성된 수식으로 나타내며
계산식내의 항목들은 숫자 및 데이터와 0혹은 1의 값을 갖는 조건식으로 구성된다.
배열함수내에 조건식은 (영역=비교값) 으로 표시하며 참일 경우 1 을 거짓일 경우 0 이 된다
배열함수내에 조건식은 하나 이상 존재할 수 있으며
개개의 조건식은 반드시 각각 괄호( ) 로 씌워져야 한다.
함수에 따라 IF 절이 필요할 수도 있고 필요 없을 수도 있다.
IF 절을 사용할 경우 IF 절의 결과가 참일 경우 배열이 증가되고 아닐 경우에는 증가되지 않는다.
배열함수의 계산 절차는
1. 결과치를 담을 배열을 준비시키고
2. 영역내의 데이터를 하나씩 비교 분석해서 배열을 증가시키고 결과치를 배열에 담는다.
3. 배열에 담긴 결과치를 가지고 함수값을 계산한다.
라고 이해하면 될 것이다.
위 시트는 제 4행을 제목행으로 하여 각 컬럼헤딩으로 이름정의 되어 있다.
자 16행을 보자.
16행은 대리점 (대리점으로 이름정의된 C5 부터 C12 영역)중 에서 값이 B16
즉 대리점명이 "제1점"인 행의 판매액의 합을 구하는 수식이다.
식을 보면 {=SUM((대리점=B16)*판매액)} 으로 되어있다.
{ } 중괄호는 수식을 입력한 후에 Control + Shift + Enter 키를 쳐서 자동으로 생성된 것이다.
즉 이것은 배열 함수로 계산한다는 의미이다.
이렇게 하지 않으면 오류가 나타난다.
{=SUM()} 함수 내부를 보면 (대리점=B16)*판매액 이 함수의 인수로 들어가 있다.
이 뜻은 대리점 이름 영역을 처음부터 끝까지 하나씩 살펴보면서
데이타가 B16 과 동일하면 (대리점=B16) 의 값은 1 이 되므로(대리점=B16)*판매액 의 값은
1 * 판매액 즉 해당 행의 판매액 값이 되고 배열이 하나 증가하고 배열에 이 값이 들어가게 된다.
만약 해당 데이터가 B16 과 다르면 (대리점=B16) 의 값은 0 이되므로 (대리점=B16)*판매액 의 값도
0 이 되어 배열이 하나 증가하고 증가한 배열에는 0 이 들어가게 된다.
범위내 모든 데이터의 검색과 계산이 끝나면 {=SUM()} 함수에 의해서 배열내 모든 데이터를 더한 값을
반환한다.
17행은 두가지 조건이 OR 로 연결된 SUM 배열 함수이다.
대리점이 제1점 혹은 제2점인 경우에 판매액을 합하는 것인데 즉 두 대리점의 판매액의 합을 뜻한다.
식을 보면 {=SUM(((대리점=B17)+(대리점=C17))*판매액)} 이다.
{=SUM()} 함수의 인수를 보면 ((대리점=B17) + (대리점=C17))* 판매액 로 되어있다.
파란색 부분을 보면 두개의 ()로 묶여있는 조건이 + 로 연결되어 있고
즉 두개의 조건이 크게() 로 또 묶여 있다.
따라서 ( )로 묶여 있는 조건 둘 중에 하나가 참이라면 1 + 0 혹은 0 + 1 이 되므로
( ) 의 값은 1이 되므로 해당 행의 값은 1 * 판매액 이므로 판매액이 배열에 들어가고.
만약 두개의 조건이 모두 거짓이라면 (0 + 0 ) * 판매액 이 되므로 0 이 배열에 들어간다.
주의할 것은 OR 인 경우에는 두개의 조건이 동일한 열에서 비교되어야 한다.
그렇지 않으면 1+1 = 2 의 결과가 나와 뜻하지 않은 값을 얻을 수 있다.
역시 최종적으로{=SUM()} 함수에 의해서 배열내의 모든 값을 더한 결과가 반환된다.
18행은 두가지 조건이 AND 로 연결된 SUM 배열 함수이다.
대리점이 제1점이고 제품명이 MP3인 경우에 판매액을 합하는 것으로
식을 보면 {=SUM((대리점=B18)*(제품명=C18)*판매액)} 이다.
{=SUM()} 함수의 인수를 보면(대리점=B18) * (대리점=C18)* 판매액 로 되어있다.
이 경우에는 두 조건이 * 로 연결되어 있고 마지막 판매액까지 계속 * 로 연결되어 있다.
따라서 조건 둘 중에 하나만 거짓이라도 1 * 0 * 판매액 혹은 0 * 1 * 판매액 이 되므로
0 이 배열에 들어가게 되고
둘 다 참이어야 1 * 1 이 되어 최종 결과값이 1 * 1 * 판매액이 배열에 들어가게 된다.
19행은 COUNT (데이터 갯수세기) 함수를 배열함수로 계산한 것이다.
대리점이 "제1점" 에서 제품명이 "MP3" 인 행의 갯수를 구하는 것인데.
함수 식은 {=COUNT(IF((대리점=B19)*(제품명=C19),1))} 이다.
COUNT 함수는 IF 절을 가져야 한다.
왜냐하면 COUNT 함수 는 데이터 영역(여기서는 배열)의 갯수를 반환하기 때문에
IF 절을 사용하지 않고 {=COUNT((대리점=B19)*(제품명=C19)*1)} 이렇게 하면
조건이 거짓인 경우 0 이 배열에 들어가게 되어 정확한 수치가 나오지 않는다.
** 명심해야 할 것은 배열함수는 일단 배열을 만든 후에 배열의 내용으로 함수를 수행한다 **
{=COUNT( )} 함수의 내부 인수는 IF( (대리점=B19) * (제품명=C19) , 1) 이다.
IF 절의 기능에 따라 (대리점=B19) * (제품명=C19) 가 1 인 경우에만
배열을 하나 추가해서 1 이라는 숫자를 배열에 넣게 된다.
단 여기서는 배열 함수이므로 AND 대신에 * 를 사용하여 두 조건의 곱이 1 일 경우에만
배열을 증가시기고 값을 넣게된다.
만약 (대리점=B19) * (제품명=C19) 가 1 인 경우에는 ( 1 * 1 )
배열은 하나 증가하고 1 이라는 값이 들어가게 되며
(대리점=B19) * (제품명=C19) 가 0 인 경우에는 (1 * 0, 0 * 1, 0 * 0)
아무 작업도 하지 않기 때문에 배열의 갯수가 증가하지 않게 된다.
최종적으로 {=COUNT()} 함수에 의해 배열의 갯수를 세어
그 값을 반환하게 된다.
20행은 위 18에서 공부한 SUM 배열함수를 이용해서 19행의 COUNT(IF()) 배열함수와
동일한 기능을 하는 함수를 구현해 보았다.
함수식은 {=SUM((대리점=B20)*(제품명=C20)*1)} 이다.
18행에서는 조건 (대리점=B18) * (제품명=C18) 가 참일 경우에 1 * 판매액 을 하여
해당 판매액의 값을 배열에 넣었는데 이것을 COUNT 기능으로 하려면1 * 1 을 해서
참인 경우에는 1 을 배열에 넣고 거짓인 경우에는 0 * 1 = 0 을 배열에 넣게 되므로
{=SUM()} 함수에 의해 배열내 모든 값을 더해 참의 갯수를 반환하게 하는 것이다.
이 경우에는 배열의 갯수는 조건 영역의 갯수와 같으며 전체에 0 혹은 1 값이 들어가게 된다.
21행은 조건에 합치하는 데이터 중에 최대값을 구하는 함수인데
제품명이 MP3 인 행의 판매수량 중 가장 큰 수량을 찾는 것이다.
함수식은 {=MAX((제품명=C21)*수량)} 으로
{=MAX()} 함수내의 조건(제품명=C21) 가 참인 경우 1 * 수량 이 배열에 들어가고
조건이 거짓인 경우 0 * 수량 이 배열에 들어가게 된다.
최종적으로 {=MAX()} 함수에 의해서 배열내의 최대 값이 반환되게 된다.
22행은 조건에 합치하는 데이터 중에 최소값을 구하는 함수인데
제품명이 MP3 인 행의 판매수량 중 가장 작은 수량을 찾는 것이다.
내용은 위의 {=MAX()} 함수와 반대이기 때문에 MAX 만 MIN 으로 바꾸면 될 것 같지만
그렇게 하면 결과치는 영원히 0 이 나오게 된다.
왜냐하면 MAX 함수는 배열 중에 결과가 FALSE 여서 0 이 포함되어도 최대치를 구하는 것이기
때문에 상관이 없지만 만약 MIN 함수인 경우에 FALSE 에 의해 0 값이 배열에 포함 되어 있다면
최소치를 구하는 것이기 때문에 항상 0 을 반환하게 되는 것이다.
따라서 IF 절을 사용해서 조건에 맞지 아니하면 배열에 아무 짓도 하지 않아
배열내에 FALSE 로 인해 발생한 0 값이 포함되지 않도록 해 주어야 하는 것이다.
함수식은 {=MIN(IF((제품명=C22),수량))} 으로
{=MIN()} 함수내의 IF 절의 조건 (제품명=C22) 가 참인 경우에만 수량값이 배열에 추가되고
조건이 거짓인 경우 아무것도 하지 않아 배열을 변경하지 않게 된다.
최종적으로 {=MIN()} 함수에 의해서 배열내의 최소값이 반환되게 된다.
다음 23행은 MP3 의 판매수량 중에 제 2위(두번째로 많은 판매수량)을 찾는 함수이다.
함수는 LARGE(데이터(여기서는배열),순위)함수를 배열함수로 사용했다.
함수식은 {=LARGE((제품명=C23)*수량,2)} 이다.
즉 (제품명=C23) 조건이 참이면1*수량 이 배열에 들어가고
거짓이면 0*수량 이 배열에 들어가며 최종적으로 {=LARGE()} 함수에 의해
배열내 데이터 중 2번째로 큰 값을 반환되게 된다.
다음 24행은 MP3 의 판매액 중에 밑에서 두번째로 작은 판매액을 찾는 함수이다.
함수는 SMALL(데이터(여기서는배열),순위)함수를 배열함수로 사용했다.
역시 위의 {=LARGE()} 함수와 반대지만 그냥 LARGE를 SMALL 로 바꾼다고 될 일은 아니다.
위의 {=MIN()} 함수처럼 FALSE 의 경우 0 이 들어가게 되어 잘못된 결과가 나오게 된다.
따라서 IF 절을 사용해서 FALSE 인 경우 배열을 추가하지 못하도록 하여야 한다.
함수식은 {=SMALL(IF((제품명=C24),판매액),2)} 이다.
즉 (제품명=C24) 조건이 참이면 해당 행의 판매액이 배열에 들어가고
거짓이면 아무것도 하지 않아 {=SMALL()} 함수에 의해
배열내 데이터 중 2번째로 작은 값이 반환되게 된다.
다음 25행은 MP3 의 판매 단가의 평균을 구하는 것이다.
함수는 AVERAGE( )함수를 배열함수로 사용했다.
역시 조건값이 FALSE 의 경우 0 이 들어가게 되어 잘못된 결과가 나올 수 있으므로
IF 절을 사용해서 FALSE 인 경우 배열에 값이 들어가지 않도록 하여야 한다.
함수식은 {=AVERAGE(IF((제품명=C25),단가))} 이다.
즉 (제품명=C25) 조건이 참이면 해당 행의 단가가 배열에 들어가고
거짓이면 아무것도 하지 않아 {=AVERAGE()} 함수에 의해
배열 내 데이터의 평균값을 반환하게 된다.
다음 26행은 MP3 의 판매액 중 최소 최대 그 딱 가운데의 금액을 구하는 것이다.
함수는 MEDIAN( )함수를 배열함수로 사용했다.
역시 조건값이 FALSE 의 경우 0 이 들어가게 되면 잘못된 결과가 나오게 된다.
따라서 IF 절을 사용해서 FALSE 인 경우 배열을 추가하지 못하도록 하여야 한다.
함수식은 {=MEDIAN(IF((제품명=C26),판매액))} 이다.
즉 (제품명=C26) 조건이 참이면 해당 행의 판매액이 배열에 들어가고
거짓이면 아무것도 하지 않아 {=MEDIAN()} 함수에 의해
배열 내 데이터 중 딱 가운데 위치한 값을 반환하게 된다.
덧글
{=SUM((대리점=B16)*판매액)}를 예를들어 말입니다. 이 중간의 '대리점'이나 '판매액'은 대체 어떻게 사용하신건가요? 범위 들어갈 부분에 문자로 쓰셨는데 이것이 실제로 변수를 쓰신건지 아니면
설명상 쉽게하기 위해 일부로 범위대신 문자를 넣으신건지가 궁금합니다.
앞에서 올린 '이름정의'에 관한 부분을 자세히 공부해 보시면 이해가 되실 겁니다.
조금 설명을 드리면 특정 데이터 영역을 '대리점' 혹은 '판매액' 으로 이름정의를 하게 되면
위처럼 공식에 정의된 이름을 사용할 수 있습니다.
즉 '대리점' 은 C5:C12 까지의 영역을 정의한 이름이며 '판매액' 은 H5:H12 의 영역을 정의한 것입니다.
혹, 한가지만 더 질문해도 괜찬을련지요.
행추가를 했을시. 자료의 행이 추가될때마다
그 자료의 개수를 파악하는 함수의 범위값도 계속해서 수정되어야하는데
혹시 이것에 대해 팁을 좀 주실수 있는지..?
함수내에 영역 범위가 지정되었을 경우
행 혹은 열을 추가한 후 범위를 참조하는 모든 함수의 범위값이 자동으로 수정하도록 하려면
그냥 최 하단행에 그냥 데이터를 입력하거나 최 우측 빈 열에 데이터를 입력하면 인식하지 못하는 것으로
알고 있습니다.
함수의 영역값을 자동으로 증가시키기 위해서는 행(열) 중간에 삽입을 하거나
마지막 행(열)을 복사한 후 아래에 삽입 시키는 방법으로 작업을 하셔야 합니다.
매크로를 기능을 이용해서 행 복사 -> 복사한 행 삽입의 작업을 특정키로 정의한 후에
작업을 하시면 편리하게 사용하실 수 있을 것입니다.
offset을 사용해서 처리했습니다
control (대조군 값)을 0으로 두고, 증가값을 구하고 싶을 때는 어떤 함수를 써야 할까요?
예를들어 대조군 값이 100이고, sample값이 101, 110, 130 이라면요.
아. 제가 바뻐서 요즘 블러그에 못들어 왔는데 .. 질문이 있네요..?
근데 .. 제가 멍청해서인지 원겸님의 질문을 도저히 이해를 못하겠습니다.
프로그램 코드 하나를 짜려면 얼마나 많은 시간을 사람과의 이해와 소통에 할해하는 줄 아십니까?
일단 질문을 하시려면 제가 이해할 수 있도록 구체적으로 설명을 해 주십시요.
어떤 업무이고 어떤 데이타가 있는데 어떤 목적으로 어떤 결과가 나와야 한다고 말입니다.
그래도 제가 알아 들을 수 있을 지 없을 지는 장담을 못드립니다.
죄송합니다.
배열함수 연습하고 있는데 위의 식같이 입력하면 그냥 텍스트로 표시되는데.....
{=sum((대리점=b16)*판매액)} <<< ctr+shi+ent 해도 이렇게만 표시되는데요 뭔 때문에 그런가요
셀서식은 일반으로 잡혀 있는데요 ??
보시면 좀 갈켜 주세요
감사합니다
하하하 {=SUM(.....)} 를 입력하는 게 아니고요.
=SUM(....) 를 입력한 후에 ctrl+Shift+Enter 를 치면 { } 가 자동으로 앞뒤에 붙으며 배열함수로 인지하는 것입니다.
죄송합니다. 답변이 조금 늦었습니다.
ㅎㅎㅎㅎㅎㅎㅎㅎㅎ 초보라서
개속 ctrl shift enter 눌러도 안되서 ㅠ ㅋㅋ 감사합니다