숙고

엑셀에서 배열을 이용하게 되면 편하긴 하지만, 배열이 많아지게 되면 연산이 너무 오래걸려 개인적으로 배열 함수를 좋아하지 않습니다.

ex) {=large(if(조건.......)}

그래서 배열을 사용하지 않는 방법을 찾아보았습니다.

large 함수에 조건 걸기

https://www.extendoffice.com/documents/excel/4938-excel-large-if-multiple-conditions.html

sumifs, countifs 는 존재하는데, largeifs 는 없습니다.

해당 방식을 응용해서 largeifs 처럼 사용할 수 있을 듯 싶습니다.

링크된 내용을 이용하여 위의 내용을 아래의 요약처럼 바꿔볼 수 있습니다.

우측에 연두색의 값을 조정하면 자동으로 변경됩니다.

이 것을 위해서 약간의 계산이 필요합니다.

쉽게 따라할 수 있도록 한 셀에 수식을 몰아넣지 않고 단계별로 작성하였습니다.

옆에 계산할 수 있도록 칸을 만들어 수식을 입력하도록 하겠습니다.

열번호는 해당 row() 값을 나타냅니다. 개인적으로 매우 유용한 함수라고 생각됩니다.

열번호는 해당 열값을 참조하기 위해서 만들었습니다.

년도, 월, 일은 요약의 우측 상단에 녹색배경값과 비교하기 위함입니다. 

전월구분1은 요약에서 '전월 잔고'를 구하기 위한 첫번째 함수 입니다. 녹색배경 값보다 이전 내용이라면 '전월'이라고 표시됩니다. 년 : 2019, 월 : 2 일 때 DATE(2019,2,1) 는 2019-2-1 입니다. 따라서 DATE함수 값에서 '-1'를 입력해서 DATE(2019,2,1)-1 : 2019-01-31 로 됩니다. DATE함수 값보다 작거나 같으면 '전월' 이라는 글자가 나오게 됩니다.

전월구분2는 이 글의 상단의 링크를 이용한 함수 법입니다. 실제 최종 '전월 잔고'의 열번호를 얻을 수 있습니다. 첫번째 조건 '$B$2:$B$19=B2' 는 이름이 같을 때 조건입니다. '홍길동' 이름일 때 TRUE 값(숫자로는 1)을 얻을 수 있습니다. 그 외에는 FALSE 값(숫자로는 0)이 됩니다. 두번째 조건 '$N$2:$N$19="전월"' 은 해당 범위에서 '전월' 이 있을 때 TRUE 값을 얻을 수 있습니다. 세번째 조건 '$J$2:$J$19' 는 해당 값을 곱해줍니다.

조건1*조건2*조건3 의 결과로는 이름이 홍길동이면서 '전월'인 열번호를 나타냅니다.

이 때 SUMPRODUCT와 LARGE 함수를 이용하여 조건1*조건2*조건3 의 값 중 가장 큰 값을 구할 수 있습니다.

전월최종 구분은 최종적인 '전월' 을 구해줍니다.

위의 값들을 가지고 요약정리 하면 다음과 같습니다.

번외로 전월구분2와 전월최종을 만들지 않고 하는 방법도 있습니다.

전월 열번호에 SUMPRODUCT 함수를 넣는 방법입니다.

해당하는 이름의 열번호를 '전월 열번호'에서 얻습니다(VLOOKUP 함수 이용). 이 열번호를 가지고 INDEX 함수를 통해 잔금 위치를 가져오는 방식입니다.

* SUMPRODUCT 함수 범위 설정할 때 조건1, 조건2, 조건3의 범위는 같아야 합니다. (꼭!)

* 조건 1 범위 : A1:A5

* 조건 2 범위 : B1:B5

* 조건 3 범위 : C1:C4

* 이렇게 되면 에러가 뜹니다. 조건 3의 범위를 C1:C5 로 변경해줍니다.

* SUMIFS, COUNTIFS 도 마찬가지로 범위는 동일해야 합니다.

=IFERROR(INDEX(찾을범위,1/LARGE(INDEX(((조건범위)=조건값)/ROW(조건범위),),ROW(A1)),나타낼 열주소),"")

IFERROR(값, "") : 계산이 끝나고 값이 없을 때 공란("")으로 표시

INDEX(찾을 범위 : index 함수 범위로써 자료를 찾을 범위

LARGE : 특정 범위 내에서 설정된 순위 값을 표시

조건 범위 : 특정 범위에서 특정한 값을 찾기 위한 범위

조건 값 : 특정 범위에서 찾을 값 (셀 위치나 특정 값("내용") 설정 가능)

INDEX(((조건범위)=조건값)/ROW(조건범위),) : 조건 범위에서 조건 값을 일치하는 셀만 계산해서 LARGE 함수로 전달

(아직 정확한 사용법 파악이 안되어 있어 수식 흐름으로 대락적인 역할만 파악한 상태. 설명이 틀릴 수도 있다는 것을 염두해줬으면 함)

ROW(A1) : Large 함수에서 나타낼 순위, 셀마다 순위를 다르게 해줘야 값이 나온다. 그냥 편하게 ROW() 값으로 뽑아내면 위에서부터 1, 2, 3... 순서로 설정이 된다.

1/LARGE : 계산한 결과를 ROW 값으로 나타내기 위함

나타낼 열주소 : 처음 INDEX 함수의 나타낼 열 주소로써 해당 셀에서 표현하고자 하는 열을 설정한다. 연속한 열을 표시할 때는 COLUMN() 함수로 COLUMN()-1 같이 활용하면 추가적인 작업이 줄어든다.

large 함수가 아닌 small 함수를 사용하면 역순으로 나온다