업무 중 제품군 별 프로젝트 별 사용하는 부품들의 사용 가능 여부에 대해 파악해야 할 일이 있었다. 파악 후 사용 불가능한 부품들의 경우 사용할 수 있게 부품 점검을 해야했는데 프로젝트 별 사용하는 부품의 가짓수는 최소 20개에서 많게는 700개가 넘었고 프로젝트는 다르더라도 사용하는 부품이 같은 경우가 있기에 이를 솎아내어 사용 불가능한 부품들 (검토 필요한 부품들)을 중복되지 않게 확인하고자 했다.
사용한 엑셀 함수는 아래와 같다. 함수의 정의는 마이크로소프트의 정의를 차용해왔다.
1. SUMPRODUCT
: SUMPRODUCT 함수는 해당 범위 또는 배열의 제품 합계를 반환합니다.
2. COUNTIFS
: COUNTIFS 함수는 여러 범위에 걸쳐 있는 셀에 조건을 적용하고 모든 조건이 충족되는 횟수를 계산합니다.
3. IF
: IF 함수는 가장 많이 사용하는 Excel 함수 중 하나로, 특정 값과 예상값을 논리적으로 비교할 수 있는 함수입니다.
그래서 해당 함수를 어떻게 사용하여 제품군 별 겹치는 부품들의 사용 가능 점검 여부를 확인했는지는 아래 수식과 설명 및 함께 첨부한 엑셀 파일을 확인하면 되겠다.
=SUMPRODUCT(($C$3:$C$2885=O3)*($E$3:$E$2885="N")*(1/COUNTIFS($C$3:$C$2885,$C$3:$C$2885,$D$3:$D$2885,$D$3:$D$2885,$E$3:$E$2885,$E$3:$E$2885)))
C열 : 제품군 전체 목록
D열 : 부품 전체 목록
E열 : 사용 가능 여부 ("Y" : 사용 가능 or "N" : 점검 필요)
O열 : 제품군
위의 함수를 아래와 같이 해석해본다.
"제품군 전체 목록 (C열)에서 하나의 제품군 (O열)에 대해, 부품 전체 목록 (D열)에서 점검이 필요한 항목 ("N")의 개수를 세는데 (SUMPRODUCT), 중복된 부품의 경우에는 1개로 셀 수 있도록 (1/COUNTIFS) 한다"
해당 게시물을 검색해서 들어온 분들이라면 다른 함수들은 모두 쉽게 이해할 것이라 생각되지만 COUNTIFS를 1로 나누는 것이 왜 중복된 값을 제외하는지는 살짝 의문이 들 수 있다. 아래 간단히 정리해본다.
우선 COUNTIFS는 조건에 맞는 개수를 세는 함수이다.
| A | |
| 1 | 사과 |
| 2 | 복숭아 |
| 3 | 배 |
| 4 | 사과 |
| 5 | 사과 |
| 6 | 복숭아 |
개수가 적기에 바로 알 수 있듯이 사과는 3개이고 복숭아는 2개이며 배는 1개이다. COUNTIFS를 사용하면 아래와 같다.
=COUNTIFS(A1:A6,"사과")
=COUNTIFS(A1:A6,"복숭아")
=COUNTIFS(A1:A6,"배")
각각 3, 2, 1이 나올 것이다. 그렇다면 이를 1로 나눈다면 ? 각각 1/3, 1/2, 1/1이 된다. 그리고 사과는 3개로 중복되니 SUMPRODUCT에서 1/3을 3번 더하면 (다른 표현으로는 1/3 * 3 이 되겠다) 1이 되고, 복숭이는 2개인데 1/2을 2번 더하면 1이 되고, 배는 1개이므로 그대로 1이 된다. 몇 개가 중복되던지 항목별 산출되는 개수는 1이 되는 것이다.
내용을 사용해 볼 수 있도록 아래 파일을 첨부하였고 상기 내용 이외에도 전체 프로젝트 중 프로젝트에 사용되는 부품들의 검토가 완료된 프로젝트의 수를 계산하는 수식 함수도 들어있으니 함께 참고해도 좋을 듯 하다.
'기록 > 기타 기록' 카테고리의 다른 글
| 엑셀 조건별 맞는 금액 개수 합치기 구하기 함수 (0) | 2025.03.30 |
|---|---|
| 엑셀 날짜를 숫자로 숫자를 날짜로 바꾸는 함수 방법, 원하는 날짜에서의 값 원하는 값에서의 날짜 외삽 추세하기 (0) | 2025.03.16 |
