Excel에서 이중 계산 없이 적격 항목에 대한 합계 값

Question

각각 연관된 숫자 값이 있는 텍스트 문자열 모음이 있습니다. "적격"인 항목에 대해 연관된 숫자 값을 합산해야 합니다. 텍스트 항목은 하나 이상의 지정된 대상 문자열을 포함하여 한정됩니다. 항목은 잠재적으로 여러 대상 문자열을 포함하거나 하나의 대상 문자열을 두 번 이상 포함할 수 있습니다. 그러나 항목이 대상 또는 대상 조합과 일치하는 항목을 포함하여 자격을 갖춘 경우 항목에 대해 관련 값을 한 번만 합산하고 싶습니다.

예를 들어, A1:A3 셀에는 각각 apple, banana, 가 포함되어 있고 pearB1:B3 셀에는 각각 숫자 가 포함되어 있다고 가정해 보겠습니다 1. 내 검색 대상은 a및 입니다 p. 세 개의 텍스트 항목은 모두 각각 하나 이상의 대상에 대한 인스턴스를 하나 이상 포함하므로 자격이 있습니다. B열의 관련 값을 합하면 의 결과가 반환되어야 합니다 3.

SUMIF 및 와일드카드 대상을 사용하여 이를 시도했습니다. 이 예의 공식은 다음과 같습니다.

=SUM(SUMIF(A:A, {"*a*", "*p*"}, B:B))

그러나 이렇게 하면 둘 이상의 대상과 일치하는 항목이 이중으로 계산됩니다. 이 경우 세 개 모두 포함되어 a있고 두 개도 포함되어 있으므로 p합계가 생성됩니다 5.

항목을 중복 계산하지 않고 어떻게 이를 수행할 수 있습니까?

Answer 1

하드 코딩된 수식보다는 워크시트 내의 실제 셀에 기준을 두는 것이 더 유연합니다.

당신이 사용하는 경우수직의, 셀의 연속 범위(예: H1:H2)를 이 끝까지, 그리고 다음과 같이 가정합니다.범위는수직의범위, 이것을 사용할 수 있습니다배열 수식**:

=SUM(IF(MMULT(0+ISNUMBER(SEARCH(TRANSPOSE(Criteria),Range)),ROW(Criteria)^0)>0,Values))

공식에 기준이 있어야 한다고 주장하는 경우 다음을 수행하세요.

=SUM(IF(MMULT(0+ISNUMBER(SEARCH({"p","a"},Range)),{1;1})>0,Values))

문안 인사

Answer 2

여기에 비교적 간단한 해결책이 있습니다. 연결된 값 all 을 사용하면 1원하는 결과가 생성되지만 3올바른 값이 선택되었음을 보여주기 위해 다른 값을 할당하고 좋은 측정을 위해 일치하지 않는 항목을 포함했습니다.

항목 목록은 C 열에 있고 관련 값은 D 열에 있습니다. 결과는 E1에 있습니다.

특정 함수만 와일드카드를 사용할 수 있으므로 대상 문자열에 대해 SEARCH를 사용합니다.

여러 OR 기준을 처리하는 일반적인 방법은 각 테스트의 결과를 추가하는 것입니다. 그러나 항목이 여러 기준을 충족할 수 있는 경우 이중으로 계산됩니다. 이를 해결하기 위해 집계된 기준 테스트를 검사하여 합계가 0보다 큰지 확인하고, 이것이 관련 값과 함께 사용됩니다.

이러한 종류의 수식에 사용되는 공통 함수는 다음 용어에 적용하기 전에 전체 배열에 대한 결과를 계산하기 때문에 대상 검색 문자열을 배열로 처리하는 것이 복잡해집니다. 그래서 각 기준을 별도로 다루었습니다. 더 많은 기준을 보려면 테스트 ISNUMBER(SEARCH("target",range))전에 괄호 안에 각각 다른 기준을 추가하세요 >0.

SUMPRODUCT는 배열이 아닌 일반적인 수식을 사용하여 배열 스타일 계산을 수행합니다.

E1의 공식은 다음과 같습니다.

=SUMPRODUCT(((ISNUMBER(SEARCH("p",C1:C4))+ISNUMBER(SEARCH("a",C1:C4)))>0)*D1:D4)

Answer 3

나는 이것이 공식이 될 것이라고 정말로 생각했지만 SUMPRODUCT(--, 그것을 작동시킬 수는 없습니다. 그래도 작동해야합니다 -

=SUM(IF(FREQUENCY(IF(NOT(ISERR(SEARCH({"d","g"},A2:A10))),ROW(A2:A10)),ROW(A2:A10))>0,1))

{"d","g"}검색 문자열의 배열은 어디에 있습니까?

ctrl이는 배열 수식이므로 일단 입력하고 + shft+를 눌러야 하며 entr전체 함수 주위의 수식 표시줄에 중괄호가 표시되어야 합니다.

단일 열을 검색하는 경우에만 작동합니다.

와일드카드를 고려하면 이 방법도 작동하지 않을 수도 있습니다. 어쩌면 정규식이 필요한 것일 수도 있습니다.