엑셀 특정 문자로 시작하는 데이터 합계 및 개수 구하기 (SUMIFS, COUNTIFS, SUMPRODUCT)

 

 

데이터 분석에 필수적인 엑셀! 특정 문자로 시작하는 데이터를 어떻게 효율적으로 처리할까요? 바로 `SUMIFS`, `COUNTIFS`, `SUMPRODUCT` 함수를 활용하면 됩니다. 이 강력한 함수들을 통해 원하는 데이터만 쏙쏙 뽑아 합계와 개수를 구하는 방법, 지금 바로 알려드립니다! 궁금하시죠? 자세한 내용은 아래에서 확인하세요! 😉

1. SUMIFS 함수: 조건에 맞는 데이터 합계 구하기

`SUMIFS` 함수는 여러 조건에 맞는 셀 범위의 합계를 계산해주는 똑똑한 함수입니다. 특정 문자로 시작하는 데이터만 콕 집어 합계를 구하고 싶을 때, 와일드카드 문자 `*`와 함께 사용하면 그 효과는 배가 됩니다! ✨

SUMIFS 함수의 구문과 작동 방식

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) 형태로 사용합니다. sum_range에는 합계를 계산할 범위를 지정하고, criteria_range와 criteria 쌍을 이용해 조건을 설정합니다. "김*"과 같이 와일드카드를 활용하면 "김"으로 시작하는 모든 텍스트에 조건이 적용됩니다. 여러 쌍의 criteria_range와 criteria를 추가하여 AND 조건으로 여러 조건을 적용할 수 있다는 사실! 잊지 마세요!

SUMIFS 함수 활용 예시: 제품 판매량 분석

예를 들어, 제품 코드가 "A-"로 시작하는 제품의 판매량 합계를 구하려면 어떻게 해야 할까요? 간단합니다! =SUMIFS(판매량_범위, 제품코드_범위, "A-*")처럼 수식을 작성하면 됩니다. 만약 "A-"로 시작하는 제품 중 특정 지역(예: 서울)에서 판매된 제품의 판매량 합계만 보고 싶다면? =SUMIFS(판매량_범위, 제품코드_범위, "A-*", 지역_범위, "서울") 처럼 추가 조건을 넣어주면 끝! 정말 간편하죠?

2. COUNTIFS 함수: 조건에 맞는 데이터 개수 세기

`COUNTIFS` 함수는 여러 조건에 맞는 셀의 개수를 계산하는 함수입니다. 마찬가지로 와일드카드 문자 `*`를 활용하여 특정 문자로 시작하는 데이터의 개수를 손쉽게 파악할 수 있습니다.

COUNTIFS 함수의 구문

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...) 형태를 사용합니다. criteria_range와 criteria를 쌍으로 사용하여 조건을 설정하고, 여러 쌍을 추가하여 AND 조건으로 여러 조건을 적용할 수 있습니다.

COUNTIFS 함수 활용 예시: 고객 데이터 분석

고객 이름이 "이*"로 시작하는 고객의 수는 어떻게 셀 수 있을까요? =COUNTIFS(고객명_범위, "이*") 이렇게 간단한 수식 하나면 충분합니다! 만약 "이*"로 시작하는 고객 중 "상품A"를 구매한 고객의 수만 알고 싶다면? =COUNTIFS(고객명_범위, "이*", 상품_범위, "상품A") 처럼 추가 조건을 넣어주면 됩니다. 참 쉽죠?

3. SUMPRODUCT 함수: 강력한 배열 함수 활용

`SUMPRODUCT` 함수는 배열의 대응하는 요소들을 곱한 후 그 결과들을 더하는 함수인데요, 논리 연산과 함께 사용하면 `SUMIFS`, `COUNTIFS`보다 더 복잡하고 다양한 조건을 처리할 수 있는 만능 함수로 변신합니다!

SUMPRODUCT 함수의 구문과 작동 원리

SUMPRODUCT(array1, [array2], ...) 형태로 사용합니다. 각 배열의 같은 위치에 있는 값들을 곱한 후, 모든 결과를 더합니다. 논리 연산 결과(TRUE/FALSE)는 숫자(1/0)로 변환되어 계산에 사용되는데, 이를 활용하여 조건부 합계를 구할 수 있습니다.

SUMPRODUCT 함수 활용 예시: 특정 문자로 시작하는 데이터 합계

특정 문자로 시작하는 데이터의 합계를 구하려면 =SUMPRODUCT((LEFT(데이터_범위, 1)="특정문자") * 수량_범위)와 같이 수식을 작성합니다. LEFT 함수로 첫 글자를 추출하고 특정 문자와 비교하여 TRUE(1) 또는 FALSE(0)를 반환하는데, 이 결과와 수량을 곱하여 조건에 맞는 값만 합산됩니다. 대단하죠?!

SUMPRODUCT 함수의 장점: 유연성과 강력함

`SUMPRODUCT` 함수는 AND, OR 조건 등 다양한 조건 조합을 지원하고, 배열 수식 없이도 배열 연산이 가능합니다. `SUMIFS`, `COUNTIFS`보다 유연하게 조건을 지정할 수 있다는 것도 큰 장점입니다.

4. SUMIF & COUNTIF: 단일 조건 처리

`SUMIFS`와 `COUNTIFS`의 단일 조건 버전인 `SUMIF`와 `COUNTIF` 함수도 있습니다. 조건이 하나일 때는 이 함수들을 사용하는 것이 더 간편할 수 있습니다. SUMIF(range, criteria, [sum_range]) 와 COUNTIF(range, criteria) 형태로 사용하며, `*` 와일드카드도 동일하게 적용됩니다. 예를 들어, "김*"으로 시작하는 이름에 해당하는 판매량 합계는 =SUMIF(이름_범위, "김*", 판매량_범위)로 계산할 수 있습니다.

5. LEFT, RIGHT, MID 함수: 문자열 추출

특정 문자로 시작하는지 여부 뿐 아니라, 문자열의 일부를 추출하여 조건을 설정해야 할 때도 있습니다. 이럴 때 `LEFT`, `RIGHT`, `MID` 함수가 유용합니다. LEFT(text, num_chars)는 텍스트 왼쪽에서 지정된 개수만큼 문자를 추출하고, RIGHT(text, num_chars)는 오른쪽에서, MID(text, start_num, num_chars)는 지정된 위치에서 시작하여 지정된 개수만큼 문자를 추출합니다. 예를 들어, 제품 코드의 2~4번째 문자가 "ABC"인 제품의 판매량 합계를 구하려면 =SUMIF(제품코드_범위, "??ABC*", 판매량_범위) 와 같이 와일드카드와 함께 사용하거나, =SUMPRODUCT((MID(제품코드_범위,2,3)="ABC")*판매량_범위) 와 같이 `SUMPRODUCT` 함수와 함께 사용할 수 있습니다.

6. 실무 활용 팁: 다양한 조건 조합 및 배열 상수 활용

`SUMIFS`와 `COUNTIFS`는 여러 조건을 AND 조건으로 결합하지만, `SUMPRODUCT`를 활용하면 OR 조건이나 더 복잡한 조건도 구현할 수 있습니다. 또한, 배열 상수 `{"김","이"}` 와 같은 형태를 사용하면 여러 값에 대한 조건을 한 번에 적용할 수 있습니다. 예를 들어, 성이 "김" 또는 "이" 인 사원들의 판매량 합계는 =SUMPRODUCT(SUMIF(이름_범위,{"김*","이*"},판매량_범위)) 와 같이 계산할 수 있습니다.

7. 마무리: 엑셀 마스터への 길

`SUMIFS`, `COUNTIFS`, `SUMPRODUCT` 함수와 와일드카드, 문자열 함수, 배열 상수 활용법을 익히면 엑셀 데이터 분석 능력이 비약적으로 향상될 것입니다. 다양한 예제를 통해 연습하고 자신만의 활용법을 개발해 보세요! 엑셀 마스터를 향한 여러분의 여정을 응원합니다! 👍