숫자를 한글로 표시하고, 수식에서 사용하기

1. 문제

아래와 같이 구간별 금액이 있고, 이를 기준으로 발주금액을 구하고자 할 때

한글로 금액을 표시하면 그 셀을 이용할 수 없으므로

숫자로 입력한 후 표기만 한글로 하면 보기에도 좋고 수식 작성 시에도 좋은 장점이 있습니다. 또 하나 구간이 변경되더라도 금액만 수정하면 되고, 수식은 수정할 필요가 없습니다.

 

2024년 발주현황.xlsx

0.02MB

 

2. 해법

가. 일반적인 수식

1월에 해당하는 3백만 원 이하의 발주 금액을 구하려면

두 가지 조건을 충족해야 하므로 SumIfs 함수를 이용하는데,

먼저 더할 범위를 입력하고, 조건 범위 1, 조건 1, 조건 범위 2, 조건 2... 식으로 입력합니다.

 

이 파일의 경우 원시 데이터가 발주서(매입) Status란 별도의 시트에 있으며,

3백만 원을 참고할 만한 셀 주소가 없으므로

=SUMIFS('발주서(매입) Status'!$E$3:$E$170,'발주서(매입) Status'!$B$3:$B$170,$H5,'발주서(매입) Status'!$E$3:$E$170,"<="&3000000)라고,

수식에 3백만 원을 직접 입력합니다.

 

나. 머리 글의 셀 주소를 이용한 수식

그렇지만 한글 3백만 원은 숫자로 표기할 수 있으므로, 3000000이라고 입력하고 표시형식만 삼백만 원 이하(건수/금액)이라고 표시하면 됩니다.

 

(1) 숫자를 한글로 표기하기

숫자를 한글로 표기하려면

사용자 지정 범주에서 지정할 수 있을 것 같은데, 처음에는 없습니다.

 

따라서, 셀 서식 - 표시 형식 탭에서 먼저 '기타'를 선택하고 숫자(한글)를 클릭합니다.

i4셀에 3000000이라고 입력하고, 아래와 같이 숫자(한글) 형식을 선택하면 삼백만까지만 표시됩니다. 왜냐하면 단위는 변할 수 있기 때문입니다. 확인 버튼을 누릅니다.

 

그리고, 사용자 지정 범주로 이동하면 맨 아래에

[DBNum4][$-ko-KR] G/표준 표시형식이 생깁니다.

 

이제 [DBNum4][$-ko-KR]G/표준 뒤에 "원 이하(건수/금액)"를 입력하면

보기에 '삼백만 원 이하(건수/금액)이라고 원하는 표기대로 됐습니다.

 

i4셀의 표시 형식을 나머지 열에도 복사해야 하므로

홈 탭 - 클립보드 그룹에서 서식 복사 명령을 더블 클릭한 후

k4, M4, O4셀을 클릭합니다.

 

그리고, 각각 5백만 원과 1천만 원을 숫자로 입력합니다.

그런데, O4셀은 1천만 원 초과이므로 사용자 지정에서 이하를 초과로 수정해야 합니다.

 

그리고 숫자 1천만을 입력하면 제대로 표시됩니다.

 

(2) 머리글 셀 주소를 이용해 수식 작성 하기

이제 수식을 입력하는데,

3백만 원 이하는 조건이 하나이지만

5백만 원 이하는 3백만 원 초과 5백만 원 이하이므로 조건이 1개 더 들어가야 합니다.

 

먼저 3백만 원 이하인 i5셀의 수식은

=SUMIFS('발주서(매입) Status'!$E$3:$E$170,'발주서(매입) Status'!$B$3:$B$170,$H5,'발주서(매입) Status'!$E$3:$E$170,"<="&3000000)

에서 3000000을 i4로 입력하는데, 아래로 내려갈 때 행이 바뀌면 안 되므로 i$4로 입력합니다.

 

i5셀의 수식을 복사해서 L5셀에 붙여 넣은 후 미만은 있으니까 초과를 입력할 수 있도록 조건범위와 조건을 복사해서 삽입하고 아래와 같이 수정합니다.

=SUMIFS('발주서(매입) Status'!$E$3:$E$170,'발주서(매입) Status'!$B$3:$B$170,$H5,'발주서(매입) Status'!$E$3:$E$170,">"&I$4,'발주서(매입) Status'!$E$3:$E$170,"<="&K$4)

 

앞부분이 초과이고, 뒷부분이 이하입니다.

 

L5셀의 수식을 N5셀에 붙이면 위치에 맞게 셀 주소가 변경됩니다.

=SUMIFS('발주서(매입) Status'!$E$3:$E$170,'발주서(매입) Status'!$B$3:$B$170,$H5,'발주서(매입) Status'!$E$3:$E$170,">"&K$4,'발주서(매입) Status'!$E$3:$E$170,"<="&M$4)

 

K4가 뒤에서 앞으로 이동했고, K4가 자신의 머리글 주소인 M4로 변경되었습니다.

 

P5셀의 수식은 i5셀의 수식을 복사한 후 이하를 초과로 부등호 기호만 수정하면 됩니다.

=SUMIFS('발주서(매입) Status'!$E$3:$E$170,'발주서(매입) Status'!$B$3:$B$170,$H5,'발주서(매입) Status'!$E$3:$E$170,">"&O$4)

 

완성된 파일은 아래와 같습니다.

 

2024년 발주현황(완성).xlsx

0.03MB