안녕하세요. 구독자 김**님이 질문을 하셨습니다.
첨부하신 파일 내용입니다. 사용자 정의 함수로 구하기셀 배경색을 엑셀 함수에서 알아낼 수 없기 때문에 특정 셀 배경색이 지정된 셀들의 합계값을 구할 때는 VBA로 사용자 정의 함수를 만드는 방법이 일반적으로 사용되는 방법입니다. 셀 배경색 기준으로 합계 구하기 안녕하세요. 자주 있는 질문은 아니지만 벌써 여러 번 받은 질문 중 하나를 정리하려고 합니다. 셀 배경색을 기준으로 합계를 구하기를 할 때 해결 방법입니다. 실습 파일 다운로드 완성 파일 다 hantip.net 배열 수식으로 구하기예시 파일처럼 일정한 기준으로 색상이 적용되어 있다면 함수를 이용해 값을 구하는 방법도 가능합니다. 실습 파일 다운로드 예시1.xlsx 0.01MB 완성 파일 다운로드 예시1_완성.xlsx 0.01MB 예시 파일에서 합계 값을 구하는데 사용될 셀은 [B4], [B7], [B10] 셀인데 일정한 규칙이 있습니다. 규칙은 행 숫자인 4, 7, 10을 3으로 나누었을 때 나머지 값이 1이 되는 셀들이라는 것입니다. 이런 식으로 나머지 연산을 이용해 특정한 셀들을 선택하는 이 알고리즘은 널리 사용되는 방법입니다. MOD 함수가 나머지 연산을 하는 함수입니다. IF 함수의 조건 인수에 MOD 함수식을 넣고 합계값을 구할 셀의 값만 선택해 SUM 함수로 합계를 구합니다. 하나의 셀 값만 대상으로 하는 것이 아니라 셀 범위를 대상으로 수식을 만들어야 하므로 배열 수식이 되어야 합니다. 엑셀 실무] 배열 수식 기본 사용법 제공되는 함수만을 사용해서 원하는 결과를 구할 수 없는 경우가 있습니다. 그럴때 배열 수식을 사용하면 해결할 수 있는 데이터들이 있어 준비했습니다. 처음부터 실무에 바로 적용하는 사례 hantip.net 엑셀] 배열 수식과 배열 함수 SUMPRODUCT 안녕하세요, 짤막한 강좌 한정희 강사입니다. 이번 강좌는 구독자 신**님의 질문으로 엑셀 초보분들에게 배열 수식과 SUMPRODUCT 함수 사용법을 소개하기 좋을 것 같아 허락을 받고 데이터를 그대 hantip.net [B13] 셀 수식을 지우고 새로 수식을 작성합니다. 수식을 작성합니다. 배열 수식은 수식을 끝낼 때 <Ctrl + Shift + Enter>를 눌러야 합니다. 입력된 수식은
이고, 완성된 수식은 배열 수식이므로 수식 입력줄에서 중괄호로 묶은 모습으로 나타납니다.
필터 기능과 SUBTOTAL 함수로 구하기필터 기능에 색상으로 필터하는 기능이 있고, 필터된 셀 값만 계산하는 SUBTOTAL 함수를 이용하면 값을 구할 수 있습니다. 이 방법으로 값을 구하려면 현재 작성된 문서 형태를 조금 수정해야 합니다. Sheet1 시트를 복사해서 복사된 시트에서 작업하겠습니다. 화면 아래 시트 탭에서 Sheet1 시트 이름에 마우스 커서를 두고 마우스 오른쪽 클릭해서 [이동/복사]를 선택하고, [복사본 만들기]를 선택해 복사합니다. 복사된 시트 이름을 더블 클릭해 시트 이름을 '필터'로 수정합니다. 13행 번호를 클릭해 행을 선택하고 단축키 <Ctrl + -(빼기)>를 눌러 행을 지웁니다. 4행 번호를 클릭해 행을 선택하고 단축키 <Ctrl + +(더하기)>를 눌러 행을 추가합니다. [A4] 셀에 '필드1', [B4] 셀에 '필드2', [C4] 셀에 '필드3'을 입력합니다. 필터 기능을 쓰려면 필드명이 데이터 범위 첫번째 행에 입력되어 있어야 하기 때문입니다. [A4:C13] 셀 범위를 선택한 뒤 [데이터]-[필터]를 선택합니다. 합계 값을 나타낼 수식은 데이터 범위와 한 행 떨어진 [B15] 셀에 작성합니다. [B15] 셀에 커서를 두고 직접 수식을 입력합니다.
SUBTOTAL 함수 자세한 사용법은 예전 강좌를 참고하세요. 엑셀 70강] Subtotal함수를 사용하여 실시간 부분합 구하기 오늘 강좌는 Subtotal함수에 대한 설명입니다. 실습 데이터 다운로드 ↓ Subtotal함수는 단독으로 사용하면 sum함수의 결과와 차이가 없습니다. 필터 기능과 조합 한다면 필터링 결과에 맞는 합계, hantip.net 이제 셀 배경색이 적용된 셀만 나타나도록 필터 합니다. [B4] 셀 필드2 필드명 옆 역삼각형을 눌러 필터 메뉴를 나타내고 [색 기준 필터]에서 필터할 색을 선택합니다. [B15] 셀에 셀 배경색을 기준으로 필터된 셀의 합계값이 나타납니다. 지금까지 셀 배경색을 기준으로 합계를 구하는 세가지 방법을 소개했습니다. 이 중 첫번째 사용자 정의 함수를 이용하는 방법이 여러가지 경우에 적용할 수 있는 방법입니다. 두번째 배열 수식을 이용하는 방법과 세번째 필터와 SUBTOTAL 함수를 이용하는 방법은 일정한 기준으로 나열되어 있거나 작성된 문서 형태를 바꿀 수 있어야 사용할 수 있는 방법입니다. 필요하신 분들께 도움이 되기를 바랍니다. |