엑셀 기능 학습을 위한 만능 SUMPRODUCT!

저번 글에서 "엑셀 함수학습의 마법같은 AGGREGATE, 하나가 실제로 19의 가치를 가질 수 있다! 》에서는 강력한 통계 기능에 대해 배웠습니다. 오늘은 SUMPRODUCT 함수에 대해 이야기해보겠습니다. sumproduct 함수는 두 열의 데이터의 합을 구할 수 있습니다. 하지만 함수를 사용할 때, 특히 논리값과 연산을 사용한 후에는 주의해야 할 사항이 많습니다. 설명을 함께 확인해 보세요.

1. SUMPRODUCT

SUMPRODUCT 함수는 정말 매우 간단합니다. 기본 함수를 설명하기 위해 두 열의 곱을 구하는 것입니다. 함수 중:

위 그림은 총 가격을 계산하기 위해 일반적으로 단가 * 수량을 계산한 다음 그 결과를 D8에 표시합니다. SUMPRODUCT 함수를 사용하면 단가와 수량을 사용하여 총 가격을 직접 계산할 수 있습니다. 수식 1은 =SUMPRODUCT(B2:B7,C2:C7)이며 결과는 D9에 표시됩니다.

이 수식에서는 단가 영역(B2:B7)과 수량 영역(C2:C7)이라는 두 개의 매개 변수가 사용됩니다. 함수의 기능은 첫 번째 매개 변수(단가)를 결합하는 것입니다. 두 번째 매개변수(수량의 데이터)를 곱한 다음 합산합니다.

2. 대부분의 오류 원인

이 기능을 사용할 때 많은 친구들이 종종 오류 값을 얻습니다. 대부분은 일관성 없는 영역 크기 선택 때문입니다. 7개의 셀과 두 번째 매개변수에는 6개의 셀만 있습니다.

SUMPRODUCT 함수를 사용할 때 각 매개변수의 면적 크기가 동일한지 확인해야 하는데 많은 친구들이 이를 눈치채지 못했습니다.

3. 또 다른 일반적인 쓰기 방법은 쉼표가 곱셈 기호(*)로 변경되는 것입니다.

이 예에서는 더 일반적인 쓰기 방법이 있습니다: =SUMPRODUCT(B2: B7*C2 :C7)

계산 결과가 =SUMPRODUCT(B2:B7,C2:C7)과 일치하는 것을 확인할 수 있습니다.

일관적인 결과로 인해 많은 친구들이 의아해하는 질문이 생겼습니다. 둘의 차이점은 무엇인가요?

4. 쉼표와 곱셈 기호(*)의 차이점

첫 번째 수식의 쉼표가 곱셈 기호(*)로만 변경되었지만 수식의 의미가 변경되었습니다. 첫 번째 수식(SUMPRODUCT(B2:B7,C2:C7))에는 두 개의 매개 변수가 있고 두 번째 수식(B2:B7*C2:C7)에는 하나의 매개 변수가 있습니다. (매개변수가 몇 개인지 확인하려면 쉼표로 구분되어 있는지 확인해야 합니다.) 첫 번째 공식에서 두 영역을 곱하는 단계는 함수로 완료됩니다. 먼저 두 영역을 지정합니다. 해당 데이터를 곱한 다음 제품을 추가합니다. 두 번째 공식에서 두 영역의 곱셈은 배열 계산에 의해 수행되며 함수는 곱 값을 더하는 한 가지 작업만 수행합니다.

의미의 변화는 어떤 영향을 미치나요?

예를 통해 살펴보겠습니다.

위 수식에는 쉼표(,)가 사용되었으며 두 개의 독립 매개변수가 있습니다. SUMPRODUCT 함수는 먼저 두 데이터 세트를 곱합니다. 곱하는 동안 데이터를 확인하고 숫자가 아닌 데이터를 0으로 처리한 다음 곱을 더합니다. 따라서 B1 "단가" 및 C1 "수량"은 0으로 처리되며 수식은 올바른 결과를 얻을 수 있습니다.

쉼표를 *로 바꾸면 수식 결과가 잘못됩니다. 왜? SUMPRODUCT 함수는 현재 제품 추가만 담당합니다. 매개변수 B2:B7*C2:C7은 배열 곱셈 연산입니다. 계산 영역에 텍스트가 포함되어 있으므로(텍스트는 곱할 수 없음) 이 배열의 계산 결과에 오류 값이 있습니다. 수식에서 "B2:B7*C2:C7"을 선택하고 F9를 누르면 B2:B7*C2:C7의 연산 결과를 볼 수 있습니다.

첫 번째(단가*수량) 연산 결과는 다음과 같습니다. 오류 값. 다음으로 SUMPRODUCT는 오류 값이 포함된 데이터를 합산하며 결과는 오류여야 합니다.

5. SUMPRODUCT

곱셈 기호(*) 사용 시 핵심 포인트

위 내용에는 두 가지 의미가 있습니다.

첫째, 쉼표를 사용하는 것과 *를 사용하는 것은 때로는 동일한 결과를 가져오지만 의미는 완전히 다르기를 바랍니다.

둘째, SUMPRODUCT 함수에서 곱셈 기호(*)를 사용할 때 두 가지 점에 주의해야 합니다. 첫째, 텍스트와 같이 계산되지 않은 내용이 있을 수 없습니다. 둘째, 두 개 이상의 배열 집합이 곱해질 경우 데이터 영역의 크기가 일정합니다. 쉼표를 사용할 때 데이터 영역의 크기가 일정한지 확인하면 됩니다.

6 사실 곱셈 기호도 더 큰 이점을 제공합니다

SUMPRODUCT 함수의 쉼표와 곱셈 기호 전후의 데이터를 A와 B로 대체하여 SUMPRODUCT(A, B)로 표현합니다. 및 SUMPRODUCT(A*B). 쉼표인 경우 A와 B는 동시에 숫자 값이거나 배열이어야 하며, 하나는 숫자 값일 수 없고 다른 하나는 곱셈 기호인 경우 A와 B가 모두 숫자일 수 있습니다. 값 또는 배열을 동시에 사용하거나 하나는 숫자 값이고 다른 하나는 배열일 수 있습니다.

즉,

쉼표,		곱하기 기호*
SUMPRODUCT (C1, B1)	√	SUMPRODUCT(C1*B1)	√
SUMPRODUCT(C1:C9,D1:D9)	√	SUMPRODUCT（C1:C9*D1:D9）	√
SUMPRODUCT（C1:D9,F1:G9）	√	SUMPRODUCT（C1:D9*F1:G9）	√
SUMPRODUCT（C1:C 9,D1)	×	SUMPRODUCT（C1:C9*D1)	√
SUMPRODUCT（C1:D9,F1)	×	SUMPRODUCT（C1:D9*F1)	√

따라서 SUMPRODUCT 함수의 적용 범위는 곱셈 기호로 확장됩니다. 아래에서 보시게 될 것은 곱셈 기호를 이용한 SUMPRODUCT 함수의 응용입니다.

7. SUMPRODUCT 공식을 이해하시나요?

위 내용을 이해하셨다면, 이 기능의 기본적인 사용법에는 문제가 없다는 의미입니다. 하지만 여러분은 여전히 ​​다음과 같은 많은 SUMPRODUCT 공식을 이해하지 못할 가능성이 높습니다.

이것은 실제로 조건부 계산 문제입니다. 이것을 다시 보세요:

이것은 다중-계산 문제입니다. 그리고 이것은:

이 표에서는 SUMPRODUCT 함수를 사용하여 거의 모든 종류의 통계 문제를 해결할 수 있으므로 하나씩 예제를 제공하지 않겠습니다. 위에 나열된 세 가지 공식만 이해할 수 있는 친구는 많지 않을 것입니다.

SUMPRODUCT 함수를 사용하는 방법을 이해하지만 여전히 이러한 공식을 이해하지 못하고 문제를 직접 해결하는 데 사용하는 이유는 무엇입니까? 그 이유는 논리값과 배열이라는 두 가지 지식 포인트를 이해하지 못하기 때문입니다.

8. 논리값과 배열에 대해 조금 알아보세요.

먼저 논리값에 대해 이야기해 보겠습니다. TRUE와 FALSE라는 두 가지 논리값만 있습니다. 수식에서 일종의 비교나 판단을 수행하면 논리 값이 생성됩니다. SUMPRODUCT((I2:I22="Zhang San")*1)을 예로 들어보겠습니다. 여기서 (I2:I22="Zhang San") ) ) 판결입니다. 일반적으로 우리는 셀을 기준으로 판단합니다. I2 = "Zhang San"은 셀 I2의 내용이 "Zhang San"인지 판단하는 것을 의미하며, 그렇지 않으면 FALSE를 얻습니다. 판단하기 위해 영역을 사용할 때 실제로는 배열인 데이터 세트를 얻게 됩니다. 예를 들어 I2:I22="Zhang San"은 일련의 논리값을 얻습니다. F9를 사용하여 계산 결과를 볼 수 있습니다.

관심이 있는 경우 영업사원과 결과 값을 하나씩 비교할 수 있습니다. 하나, 그리고 당신은 Zhang San의 대응하는 모든 것이 TRUE라는 것을 알게 될 것입니다.

논리값은 직접 합산할 수 없기 때문에 숫자로 변환해야 합니다. 변환 방법은 논리값에 대해 덧셈, 뺄셈, 곱셈, 나눗셈 등의 연산을 수행하는 것입니다. 이 공식에서 *1에는 다음과 같은 효과가 있습니다.

*1 연산 후에 TRUE는 1이 되고 FALSE는 0이 됩니다. 이유는 묻지 마세요. 엑셀에서는 이렇게 규정하고 있고, 논리값과 숫자의 대응관계는 이렇습니다.

좋아요, 먼저 논리값 세트를 얻은 다음 *1을 사용하여 이를 숫자 세트로 바꾼 다음 이를 합산하여 조건에 따라 계산하는 목적을 달성합니다. 이제 =SUMPRODUCT((I2:I22="Zhang San")*1) 공식을 이해하셨나요?

이제 우리는 논리값을 이해하고 배열 연산의 첫 번째 원리를 이해합니다. 숫자 그룹이 숫자로 계산될 때 숫자 그룹의 각 데이터는 각각 이 숫자로 계산됩니다. 이것이 바로 지금 공식에서 계산되는 방법입니다.

9. SUMPRODUCT 공식은 아무리 복잡해도 다 이해할 수 있어요!

두 번째 공식을 살펴보겠습니다: =SUMPRODUCT((MONTH(F2:F22)=3)*(H2:H22="二分店")*(G2:G22="衬衫")*J2:J22).

이 공식의 길이를 보지 마십시오. 방금 배운 지식을 사용하여 이 공식을 풀 수 있습니다. 수식에는 여전히 하나의 매개변수만 있지만 이 매개변수는 4개의 배열로 구성되며 그 중 3개는 논리값, 즉 (MONTH(F2:F22)=3), (H2:H22="Second Branch") 및 (G2: G22="셔츠"). 이 세 가지 논리값 세트는 세 가지 판단을 완료하고 월 = 3, 매장 = 두 번째 지점, 이름 = 셔츠의 세 가지 조건에 해당합니다. 구체적인 내용은 사진과 같습니다.

빽빽하게 들어차 있는 것처럼 보이지만 곱셈 연산을 하고 나면 1과 0이 잔뜩 나옵니다.

제품 결과는 이렇습니다. 2개의 1은 실제로 3월 두 번째 지점의 셔츠 데이터 2개에 해당합니다. 이때 수식은 논리값의 집합(이미 0과 1)에 데이터의 집합(수량)을 곱한 뒤 합을 완성하는 함수가 된다.

이 예에서는 배열 계산의 두 번째 원칙에 주의해야 합니다. 다중(2개 포함) 배열(이 경우 4개 배열)을 계산할 때 배열에서 해당 위치의 데이터가 계산되고, 배열은 다음을 포함해야 합니다. 데이터 개수는 동일합니다.

이제 우리는 배열 계산의 두 가지 원칙을 이해했습니다. 간단한 예를 들어 설명하겠습니다.

배열(A1:A9)에 데이터(B1)를 곱할 때 이 그룹의 차이점은 무엇입니까?

두 개의 배열(A1:A9 및 B1:B9)을 곱하면 첫 번째 숫자 그룹과 두 번째 숫자 그룹의 해당 위치에 있는 데이터가 곱해집니다.

이제 이 공식을 다시 살펴보세요 =SUMPRODUCT((MONTH(F2:F22)=3)*(H2:H22="두 번째 지점")*(G2:G22="셔츠")*J2:J22) , 그러면 두통이 덜할 것입니다. =SUMPRODUCT((MONTH(F2:F22)=3)*(H2:H22="二分店")*(G2:G22="衬衫")*J2:J22)，应该没那么头疼了吧。

其实数组的计算原则还有更加复杂的情况，有兴趣的朋友可以看看相关的资料，这里了解简单原理即可。再来看看第三个公式：=SUMPRODUCT((H2:H22=H2)*(G2:G22={"毛衣","衬衫"})*L2:L22)。这个公式看上去和前面两个有点区别，(G2:G22={"毛衣","衬衫"})这部分用到了常量数组，其实这个公式本来应该是这样写的：=SUMPRODUCT((H2:H22=H2)*(G2:G22="毛衣")*L2:L22)+SUMPRODUCT((H2:H22=H2)*(G2:G22="衬衫")*L2:L22)

사실 배열의 계산 원리는 더 복잡합니다. 관심 있는 친구들은 여기에서 간단한 원리만 이해하면 됩니다. 세 번째 공식인 =SUMPRODUCT((H2:H22=H2)*(G2:G22={"sweater", "shirt"})*L2:L22)를 살펴보겠습니다. 이 수식은 이전 두 수식과 약간 다릅니다. (G2:G22={"Sweater", "Shirt"}) 이 부분은 상수 배열을 사용합니다. 실제로 이 수식은 다음과 같이 작성되어야 합니다. : =SUMPRODUCT((H2:H22=H2)*(G2:G22="스웨터")*L2:L22)+SUMPRODUCT((H2:H22=H2)*(G2:G22="셔츠")* L2 :L22).

이 공식은 두 가지 SUMPRODUCT 함수를 사용합니다. 첫 번째는 본점의 스웨터 판매량을 계산하는 것이고, 두 번째는 본점의 셔츠 판매량을 각각 이해할 수 있어야 합니다. 갈라져. 두 SUMPRODUCT 함수는 "스웨터"와 "셔츠"를 제외하고는 완전히 동일합니다. 이 경우 상수 배열을 사용하여 두 내용을 합치면 수식을 더 간단하게 만들 수 있습니다.

오늘의 설명을 통해 우리는 일반적인 SUMPRODUCT 수식을 이해하고 SUMPRODUCT 함수의 곱셈 기호 사용에 대한 혼란을 해결했으며 SUMPRODUCT 함수의 더 많은 응용을 마스터했습니다. 또한 논리값과 배열에 대한 몇 가지 기본 사항을 배웠습니다. 이는 다른 복잡한 수식을 이해하는 데 도움이 됩니다.

관련 학습 권장 사항: excel 튜토리얼

🎜

위 내용은 엑셀 기능 학습을 위한 만능 SUMPRODUCT!의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!