지점명별 품명별 합계금액 구하기(데이터 유효성 검사, SUMIFS 함수)

판매내역을 지점명과 품명을 기준으로 집계하여 보기 좋게 나타내는 방법을 소개하겠습니다.

 

실습 파일 다운로드

지점명별 품명별 합계금액 구하기.xlsx

0.01MB

완성 파일 다운로드

완성파일.xlsx

0.02MB

 

지점별로 묶어 지점을 선택하면 해당하는 지점이 거래한 품명별 판매금액이 나타나도록 설정을 합니다.

 

다른 강좌에서는 피벗 테이블을 이용해 나타냈었습니다.

 

지점명별 품명별 합계금액 구하기(피벗테이블)

이번 강좌에서는 데이터 유효성 검사 기능으로 지점명 목록을 표시하고, 사용자가 지점명을 선택하면 품명별 판매금액 합계를 SUMIFS 함수로 수식을 작성해 나타내 보겠습니다.

 

 

시트 Sheet1에 판매금액 데이터가 있습니다.

새 시트를 하나 추가해 지점별 품명별 판매금액을 나타냅니다.

 

시트 탭에서 시트 추가 아이콘을 눌러 새 시트를 삽입니다.

새로 추가된 시트 Sheet2 [A1] 셀에 '지점명'을 입력합니다.

품명을 중복된 값을 제외하고 하나씩 나타내기 위해 Sheet1 시트 [C1:C57] 셀 범위를 복사해 Sheet2 시트 [A3] 셀에 복사해 붙여넣기 합니다.

중복된 품명을 제거하기 위해 범위 선택된 상태에서 [데이터]-[데이터 도구]-[중복된 항목 제거]를 선택합니다.

[확인]을 눌러 중복 값을 제거 합니다.

사용자가 지점명을 입력하지 않고 나타난 목록에서 선택하도록 데이터 유효성 검사 기능을 쓰기 위해 Sheet1 시트에서 지점명 [B1:B57] 셀 범위를 복사해 Sheet2 [H1] 셀에 붙여넣습니다.

[H1] 셀에 붙여넣은 지점명은 사용자가 직접 사용하는 부분이 아니라 데이터 유효성 검사 기능에서 사용될 부분입니다.

사용자는 볼 수 없도록 숨길 예정입니다.

 

중복 항목을 제거하기 위해 범위 선택된 상태에서 [중복된 항목 제거]를 실행합니다.

선택하기 쉽도록 지점명을 가나다라 순서대로 정렬합니다.

[데이터]-[정렬 및 필터]에서 [텍스트 오름차순 정렬]을 선택합니다.

[B1] 셀을 선택하고, [데이터 유효성 검사]를 선택합니다.

[데이터 유효성] 대화상자 [설정] 탭에서 [제한 대상]은 '목록'을 선택하고 [원본]에 [H2:H18] 셀 범위를 선택해 입력합니다.

[확인]을 누릅니다.

다음 작업을 위해 '가야점'을 선택해 나타납니다.

[B3] 셀에 '합계 금액'을 입력합니다.

 

[B4] 셀에 커서를 두고 수식을 작성합니다.

=su까지 입력해 나타낸 함수 목록에서 SUMIFS 함수를 선택합니다.

단축키 <Shift + F3>을 눌러 [함수 인수] 대화상자를 나타냅니다.

Sum_range 인수에 Sheet1 시트 [F2:F57] 셀 범위를 선택해 나타내고 <F4>키를 눌러 절대 참조 합니다.

Criteria_range1 인수에 Sheet1 시트 [B2:B57] 셀 범위를 선택해 나타내고 <F4>키를 눌러 절대 참조 합니다.

Criteria1 인수에 Sheet2 시트 [B1] 셀을 선택해 나타내고 <F4>키를 눌러 절대 참조 합니다.

Criteria_range2 인수에 Sheet1 시트 [C2:C57] 셀 범위를 선택해 나타내고 <F4>키를 눌러 절대 참조 합니다.

Criteria2 인수에 Sheet2 시트 [A4] 셀을 선택해 나타냅니다.

Criteria2 인수는 다른 인수와 달리 상대 참조를 유지합니다.

[확인]을 눌러 결과를 나타냅니다.

[B4] 셀 채우기 핸들을 끌어 [B10] 셀까지 채웁니다.

 

기능은 모두 구현했습니다.

 

서식 설정을 하겠습니다.

 

[A3] 셀을 선택하고 [홈]-[클립보드]-[서식 복사]를 더블 클릭합니다.

[서식 복사] 항목을 더블 클릭하면 여러 셀이나 셀 범위에 서식 복사 기능을 적용할 수 있습니다.

[A1] 셀을 클릭해 서식 복사를 적용하고, [B3] 셀도 클릭합니다.

그리고 나서 다시 [서식 복사]를 클릭해 서식 복사 기능을 해제합니다.

 

[B4:B10] 셀 범위를 선택하고 [홈]-[표시 형식]-[쉼표 스타일]을 선택합니다.

A열 머리글과 B열 머리글 사이에서 커서를 두고 커서에 좌우 화살표 모양이 나타나면 더블 클릭해 A열 너비를 입력된 내용에 따라 적당히 조절합니다.

H열 머리글을 클릭한 뒤 마우스 오른쪽 단추를 눌러 메뉴를 표시하고 [숨기기]를 선택합니다.

[B1], [B4:B10] 셀 범위를 선택하고 [홈]-[글꼴]-[테두리] 옆 드롭다운 단추를 눌러 [모든 테두리]를 선택합니다.

 

모두 완성했습니다.

 

필요하신 분께 도움되길 바라며 강의 마칩니다.

수고하셨습니다.