단가 조합 구하기

	B	C	D	E	F
2	제품	단가		조합	합계
3	포카칩	1,000		포카칩 + 양파링 + 치토스	2,300
4	양파링	800		포카칩 + 양파링	1,800
5	새우깡	700		포카칩 + 치토스	1,500
6	감자깡	600		농심새우깡 + 감자깡	1,300
7	치토스	500

 

F3 =SUMPRODUCT(N(NOT(ISERROR(SEARCH(B$3:B$7,E3,1)))),C$3:C$7)

; F3 잡아 아래로 원하는 행 만큼 복사하면 작업 끝.

 

수식설명 : 위 수식은 B3:B7의 데이타가 E열 문자열 내에 있는지 SEARCH로 탐색합니다. 탐색 결과 있으면 (is error가 not이면) 그 행의 단가를 더하지요. 따라서 B열에 없는 문자열이 F열에 제시되어도 원하는 조합합계를 냅니다.

 

조합 중 중복데이타 (예: 포카칩 + 치토스 + 치토스 = 2,000)도 감안하고 싶을 때는 아래와 같이 할 수 있겠습니다.

	B	C	D	E	F
2	제품	단가		조합	합계
3	포카칩	1,000		포카칩 + 양파링 + 치토스	2,300
4	양파링	800		포카칩 + 양파링	1,800
5	새우깡	700		포카칩 + 치토스	1,500
6	감자깡	600		농심새우깡 + 감자깡	1,300
7	치토스	500		치토스 + 치토스 + 치토스	1,500

 

F3 =SUMPRODUCT(N(NOT(ISERROR(FIND(B$3:B$7,E3,1)))),C$3:C$7)+SUMPRODUCT(N(NOT(ISERROR(FIND(B$3:B$7,E3,FIND(B$3:B$7,E3,1)+LEN(B$3:B$7))))),C$3:C$7)+SUMPRODUCT(N(NOT(ISERROR(FIND(B$3:B$7,E3,FIND(B$3:B$7,E3,FIND(B$3:B$7,E3,1)+LEN(B$3:B$7))+LEN(B$3:B$7))))),C$3:C$7)

; F3 잡아 아래로 복사하면 작업 끝.

 

이 수식은 중복 데이타를 세 개 까지 찾습니다 (예: E7셀). 한 셀에 들어가있는 문자열 중 같은 데이타가 몇 개 있는지 출력하는 함수가 있다면 간단합니다만, 제가 아는 한 그런 함수는 없으므로 이 수식 처럼 단순노가다를 해야 하지요. 이론상으로야 몇 개든 가능합니다만 조금 더 가면 '수식이 너무 깁니다'가 뜰 겁니다. 그 이상 중복을 허용해야 한다면 독립셀에 하나씩 데이타를 넣고 SUMPRODUCT와 COUNTIF를 써서 찾는 것이 정법일 겁니다. (다만 그 경우에도 '농심새우깡'을 잡아내긴 힘듭니다.)