■ 목차
1. 배열 수식이란?
2. 엑셀에서 배열의 표현
3. 배열 수식의 계산 방식
4. 배열 수식에서 True, False의 의미
5. 배열 수식에서 And, Or 조건 구현하기
6. 배열 수식에서 우리가 알아야 하는 핵심 개념
7. 배열 수식 예제 - 구구단 한 번에 입력하기
8. 배열 수식 예제 - 판매 금액 총액 구하기
9. 배열 수식 예제 - Sum, If 배열 수식
10. 배열 수식 예제 - Sum 함수만으로 구하기
11. 배열 수식 예제 - 2가지 조건을 만족하는 합계 구하기
1. 배열 수식 예제 - 날짜에서 특정 월의 실적 합계 구하기
2. 배열 수식 예제 - "Or 조건 = 더하기" 활용하기
3. 배열 수식 예제 - 최대 값이 있는 행 번호 찾기
4. 배열 수식 예제 - 0 값을 제외한 합계 구하기
5. 배열 수식 예제 - 고유한 값의 개수 구하기
6. 배열 수식 예제 - 반별 등수 구하기
** 핵심 요약
1. 배열 수식 예제 - 날짜에서 특정 월의 실적 합계 구하기
- 날짜에서 특정 월의 실적 합계 구하기의 경우 먼저 날짜 데이터가 엑셀의 날짜 서식에 맞게 잘 들어가 있는 것이 중요합니다.
- 날짜 관련 많은 함수들을 잘 사용하기 위해서는 먼저 데이터가 날짜 형식으로 잘 입력이 되어 있어야 가능합니다.
- 가장 기본적인고 중요한 사항이고 간혹 날짜처럼 보이는(?) 데이터를 입력하는 경우가 있는데 이러 경우에는 날짜 함수를 사용할 수가 없습니다. ㅠㅠ
- 특정 월의 데이터 합계의 구하는 경우에도 기본적인 배열 수식의 원리는 동일합니다.
- 논리 연산자를 이용한 해당 여부를 구해준 배열과 합계를 구할 숫자 데이터의 배열을 배열 곱하기를 하시면 됩니다.
- 다만 특정 월을 논리 연산자로 비교하기 위해서는 Month 함수를 활용합니다.
2. 배열 수식 예제 - "Or 조건 = 더하기" 활용하기
- Or 조건을 사용하실 경우는 실제 데이터를 잘 보고 사용하시기 바랍니다.
- 논리적으로 데이터가 Or 조건을 사용할 수 있는 상황이 되는지를 확인하고 사용하는 것이 필요합니다.
- 간혹 중복이 되어 실제로 구하고자 하는 데이터 보다 훨씬 많은 데이터가 구해지는 경우가 있습니다.
- 예를 들어 "남자"이거나(Or) "서울"에 거주하는 대상자의 경우 "남자"이면서 "서울"에 거주하는 사람은
- "남자" 조건에 한 번 그리고 "서울" 조건에 다시 한 번 카운트되어 이중으로 카운트 될 수도 있습니다.
- 다음 판매 실적 중에서 "특판"과 "일반 판매" 중에서 "기획" 판매의 "판매량" 합계를 구해 보도록 하겠습니다.
- "특판" 이거나(Or 조건) "기획" 판매인 건들의 판매량 합계를 구하면 되는 문제입니다.
- 이 경우 우리는 단순히 "특판"의 판매량과 "기획"의 판매량을 더하는 것으로 판매 금액 합계를 구할 수 있습니다.
- 이 문제에서 만약에 "특판"이면서 "기획"인 판매 건이 존재를 한다면 정확한 결과를 구하기 위해서는
- "특판 And 기획"인 판매량을 한 번 더 계산을 해줘서 빼기를 해줘야 중복되어 계산되는 것을 방지할 수 있습니다.
3. 배열 수식 예제 - 최대 값이 있는 행 번호 찾기
- 최대 값이 있는 행번호를 찾는 문제입니다.
- ① If 함수를 사용하여 데이터 범위를 최대값 ( = Max(데이터 범위))과 비교해서
- ② 최대 값인 경우에는 Row 함수로 행 번호를 표시하고
- ③ 최대 값이 아닌 경우에는 공백 ( "" )으로 표시를 합니다.
- ④ 그러면 공백과 최대 값의 행번호로 구성된 배열이 만들어지고
- 그리고 이렇게 만들어진 배열에서 ⑤ Max 함수를 이용해서 최대 값을 찾아 주면 그 값이 바로 최대 값이 있는 행 번호입니다.
4. 배열 수식 예제 - 0 값을 제외한 합계 구하기
- 데이터 값 중에 숫자 0이 있으면 이를 무시하고 나머지 숫자들로 합계 혹은 평균을 구하는 문제입니다.
- ① 먼저 If 함수로 데이터의 값이 0인 경우 공백 ( = 값 없음, Null, "" )로 표시를 해줍니다.
- ② 그리고 이렇게 0이 제거된 데이터의 배열을 Sum 함수나 Average 함수로 합계나, 평균을 구해 줍니다.
- 문제 풀이를 위한 방법으로 일반적으로 잘 못 된 데이터인 경우라면 찾기/바꾸기로 0을 제거해 주고
- 진짜 데이터라면 포함하여 계산을 해주는 것이 맞을 것입니다.
5. 배열 수식 예제 - 고유한 값의 개수 구하기
- 중복된 데이터를 제외하고 고유한 데이터의 개수가 몇 개인지를 구하는 문제입니다.
- ① 데이터 범위에 대해서 "1 / Countif 함수"을 적용합니다.
- 이렇게 되면 만약 1개의 데이터가 존재를 하면 1/1, 2개의 데이터의 경우 1/2, 1/2, 3개의 데이터는 1/3, 1/3, 1/3이 되게 됩니다.
- ② Sum 함수를 이용해서 1/Countif 함수로 구해진 배열의 합계를 구하게 되면 고유한 데이터의 개수가 됩니다.
6. 배열 수식 예제 - 반별 등수 구하기
- 여러 반들이 섞여 있는 경우에 각각의 반별로 성적 순위를 구하는 문제입니다.
- ① 먼저 같은 반인지 여부를 비교하는 배열을 하나 구해 줍니다.
- ② 그리고 셀의 점수보다 점수가 큰 경우에는 True = 1, 작은 경우에는 False = 0으로 비교 연산자를 이용해서 배열을 만들고
- ③ 두 배열을 곱하게 되면 (같은 반이고 점수가 큰 경우 = 1)이 되고
- ④ 이를 Sum 함수로 합계를 구하면 같은 반에 점수가 큰 개수 나오게 되는데 여기에 "+ 1"을 하게 되면 해당 점수의 순위가 됩니다.
- 본 계산은 배열의 "다중 셀" 입력이 아닙니다. "단일 셀"입력이고 그래서 절대참조/상대참조를 신경 써주셔야 하고
- 수식을 복사하여 사용하셔야 합니다.
- 배열 수식은 엑셀의 함수로는 구하기 어려운 다양한 문제를 해결해 줄 수 있습니다.
- 하지만 단점으로 "어렵다" 입니다. 내가 만든 것을 다른 사람이 보면 어렵습니다. 그리고 내가 다시 봐도 어렵습니다.
- 그래서 가능하면 공유되는 파일에는 사용을 하지 않습니다.
- 하지만 정말이지 배열수식은 뭔가 뿌듯한 기분이 드는 문제풀이고 보람이 있는 듯 합니다.
- 아무조록 많은 풀이들을 경험하시고 나만의 풀이 방법 또한 만드시기 바랍니다.
- 고생 하셨습니다.
** 핵심 요약 : 배열수식 이란? (4/4) - 배열함수, 배열수식 다양한 예제로 알아보자
1. 배열 수식 예제 - 날짜에서 특정 월의 실적 합계 구하기
- 먼저 날짜 데이터가 엑셀 서식에 맞게 입력되어 있어야 날짜 관련 함수를 사용할 수 있다.
- 수식 : {= sum ( month( 데이터 ) = 3 ) * 판매량 ) }
2. 배열 수식 예제 - "Or 조건 = 더하기" 활용하기
- Or 조건의 배열 수식은 더하기를 해준다.
- 수식 : {= sum ( (판매구분 = "특판") * 판매량 ) + sum ( ( 기획부분 = "기획") * 판매량 ) }
3. 배열 수식 예제 - 최대 값이 있는 행 번호 찾기
- If 함수와 Max 함수로 Max 값인 경우는 행번호(Row 함수), 아닌 경우는 공백( "" )의 배열을 만들어 준다.
- 수식 : {= max ( if ( ( 판매량 = Max ( 판매량 ) ), Row(판매량), "") ) }
4. 배열 수식 예제 - 0 값을 제외한 합계 구하기
- ① 먼저 If 함수로 데이터의 값이 0인 경우 공백 ( = 값 없음, Null, "" )로 표시한다.
- ② 그리고 이렇게 0이 제거된 데이터의 배열을 Sum 함수나 Average 함수로 합계나, 평균을 구해준다.
- 수식 : {= sum (if (판매량 = 0 ), "", 판매량 ) ) }
5. 배열 수식 예제 - 고유한 값의 개수 구하기
- ① 데이터 범위에 대해서 "1 / Countif 함수"을 적용하고 ② Sum 함수로 합계를 구해준다.
- 수식 : {= sum ( 1 / countif ( 데이터 ) ) }
6. 배열 수식 예제 - 반별 등수 구하기
- ① 먼저 같은 반인지 여부를 비교하는 배열을 구해준다.
- ② 그리고 셀의 점수보다 점수가 큰 경우에는 True = 1, 작은 경우에는 False = 0으로 비교 연산자를 이용해서 배열을 만들고
- ③ 두 배열을 곱하게 되면 (같은 반이고 점수가 큰 경우 = 1)이 되고
- ④ 이를 Sum 함수로 합계를 구하면 같은 반에 점수가 큰 개수 나오게 되는데 여기에 "+ 1"을 하게 되면 해당 점수의 순위가 된다.
- 수식 : { = sum ( ( 반데이터 = 반 ) * ( 점수데이터 > 점수 ) ) + 1 }
[엑셀 특강 (개념 잡기)] - 999. 엑셀 기초 특강 목차
999. 엑셀 기초 특강 목차
[엑셀 특강 (개념 잡기)] - 001. (엑셀 기초 특강) 엑셀 데이터란? (feat. 숫자, 문자, 날짜 무엇이 다른가?) [엑셀 특강 (개념 잡기)] - 002. (엑셀 기초 특강) 엑셀 함수란? (feat. 왜 그렇게 어렵고 많아야
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글