대신 Excel에서 SUMPRODUCT 또는 SUMIFS 함수 SUM 및 IF 함수를 사용하는 방법을
이 기사에서는 Excel에서 SUMPRODUCT 및 SUMIFS 함수 대신 IF 함수를 사용하는 방법을 배웁니다.
시나리오 :
간단히 말해서, 길고 흩어져있는 데이터 세트로 작업 할 때 때때로 우리는 그 위에 몇 가지 기준이있는 숫자의 합을 찾아야합니다. 예를 들어, 특정 부서의 급여 합계를 찾거나 날짜, 이름, 부서에 대한 여러 기준을 가지고 있거나 값 이하의 급여 또는 값 이상의 수량과 같은 데이터를 숫자로 지정할 수도 있습니다. 이를 위해 일반적으로 SUMPRODUCT 또는 SUMIFS 기능을 사용합니다. 그러나 Excel 기본 기능 IF 기능과 동일한 기능을 수행한다고 믿지 않을 것입니다.
문제를 해결하는 방법?
IF 함수를 사용하여 테이블 배열에 대해 논리 연산을 수행하려면 이것이 어떻게 가능한지 생각해야합니다. Excel의 IF 함수는 매우 유용합니다. Excel 또는 다른 코딩 언어에서 어려운 작업을 수행 할 수 있습니다. IF 함수는 필수 값에 해당하는 배열의 조건을 테스트하고 결과를 True 조건에 해당하는 배열로 1로, False를 0으로 반환합니다.
이 문제에 대해 다음 함수를 사용합니다.
-
link : / math-and-trig-excel-sum-function [SUM 함수] -
link : / tips-if-condition-in-excel [IF 함수]
위의 기능과 데이터 작동에 대한 기본적인 감각이 필요합니다. 논리 연산자를 사용하여 배열의 논리 조건을 적용 할 수 있습니다. 이러한 논리 연산자는 텍스트와 숫자 모두에서 작동합니다. 다음은 일반 공식입니다. \ {} * 중괄호는 IF 함수로 배열 수식을 수행하는 마법 도구입니다.
일반 공식 :
|
\{* =
(logical_1) (logical_2) … (logical_n) , sum_array ) ) }* |
참고 : 중괄호 (\ {})의 경우 Excel에서 배열 또는 범위로 작업 할 때 Ctrl + Shift + Enter *를 사용하십시오. 이렇게하면 기본적으로 공식에 중괄호가 생성됩니다. 중괄호 문자를 하드 코딩하지 마십시오.
논리 1 : 배열 1에서 조건 1을 테스트합니다. 논리 2 : 배열 2에서 조건 2를 테스트합니다. sum_array : 배열, 연산 합계가 수행됩니다.
예 :
이 모든 것들은 이해하기 어려울 수 있습니다. 따라서 아래에 표시된 예제에서이 수식을 실행하여 테스트 해 보겠습니다. 여기에는 해당 카테고리 필드 및 수량과 함께 다른 도시로 배송 된 제품의 데이터가 있습니다. 여기에 데이터가 있고 수량이 40보다 큰 보스턴으로 전송 된 쿠키의 수량을 찾아야합니다.
데이터 테이블과 기준 테이블은 위 이미지에 나와 있습니다. 목적을 이해하기 위해 사용 된 배열에 대해 명명 된 범위를 사용했습니다. 이름이 지정된 범위는 다음과 같습니다.
여기 :
배열 A2 : A17에 대해 정의 된 도시입니다.
배열 B2 : A17에 대해 정의 된 범주입니다.
배열 C2 : C17에 대해 정의 된 수량입니다.
이제 아래 공식을 사용하여 원하는 결과를 얻을 준비가되었습니다.
공식 사용 :
|
\{ =
(City=”Boston”) (Category=”Cookies”) (Quantity>40) , Quantity)) }* |
설명 :
-
City = “Boston”: “Boston”과 일치하는 도시 범위의 값을 확인합니다.
-
Category = “Cookies”: “Cookies”와 일치하는 범주 범위의 값을 확인합니다.
-
수량> 40 : 수량 범위의 값을 ma까지 확인합니다. 수량은 합계가 필요한 배열입니다.
-
IF 함수는 모든 기준을 확인하고 별표 문자 (*)는 모든 배열 결과를 곱합니다.
SUM (IF (\ {0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0}, \ {33; 87; 58; 38; 54; 51; 28; 36; 28; 44; 23; 27; 43; 42; 33; 30}))
-
이제 IF 함수는 1에 해당하는 수량 만 반환하고 나머지는 무시됩니다.
-
SUM 함수는 SUM을 반환합니다.
이제 1에 해당하는 수량 만 더해져 결과를 얻습니다.
보시다시피 수량 43이 반환되지만 수량이 38, 36, 43 인 “Boston”에 전달 된 쿠키 주문이 3 개 있습니다. 수량이 40을 초과하는 수량의 합계가 필요했습니다. 따라서 수식은 43 만 반환합니다. 이제 다른 기준을 사용하여 도시 : “Los Angeles”및 범주 : “Bars”및 Quantity에 대한 SUM 수량이 50 미만이되도록하십시오.
공식 사용 *
|
\{* =
“Los Angeles”) (Category=”Bars”) (Quantity < 50), Quantity ) ) } |
보시다시피 수식은 86 값을 결과로 반환합니다. 수량이 44 & 42 인 조건을 만족하는 2 차수의 합계입니다.
이 문서에서는 배열 수식에서`link : / logical-formulas-excel-nested-if-function [nested IF formula]`를 단일 IF로 대체하는 방법을 설명합니다. 복잡한 수식의 복잡성을 줄이는 데 사용할 수 있습니다. 그러나이 특정 문제는`link : / summing-excel-sumifs-function [SUMIFS]`또는`link : / summing-excel-sumproduct-function [SUMPRODUCT]`function으로 쉽게 해결할 수 있습니다 .
SUMPRODUCT 함수 사용 :
SUMPRODUCT 함수는 배열에서 해당 값의 합계를 반환합니다. 따라서 우리는 배열이 True 문 값에 1을 반환하고 False 문 값에 0을 반환합니다. 따라서 마지막 합계는 모든 진술이 참인 곳에 해당합니다.
공식 사용 :
|
= ( — (City = “Boston”) , — (Category = “Cookies”) , — (Quantity > 40) , Quantity ) |
-: 모든 TRUE를 1로, False를 0으로 변환하는 데 사용되는 연산입니다.
SUMPRODUCT 함수는 위에서 설명한 SUM 및 IF 함수에서 반환 된 수량의 SUM을 다시 확인합니다.
두 번째 예와 유사하게 결과는 동일합니다.
보시다시피 SUMPRODUCT 기능은 동일한 작업을 수행 할 수 있습니다.
다음은 공식 사용에 관한 모든 관찰 참고 사항입니다.
참고 :
-
수식의 sum_array는 숫자에서만 작동합니다.
-
수식이 #VALUE 오류를 반환하는 경우 문서의 예에 표시된대로 수식에 중괄호가 있는지 확인합니다.
-
부정 (-) 문자는 값, TRUE 또는 1을 FALSE 또는 0으로, FALSE 또는 0을 TRUE 또는 1로 변경합니다.
-
같음 (=),보다 작음 (⇐),보다 큼 (>) 또는 같지 않음 (<> *)과 같은 연산은 숫자 만 사용하여 수식 내에서 수행 할 수 있습니다.
Excel에서 SUMPRODUCT 및 SUMIFS 함수 대신 IF 함수를 사용하는 방법에 대한이 기사가 설명되기를 바랍니다. 여기에서 수식 합계에 대한 더 많은 기사를 찾아보십시오. 저희 블로그가 마음에 드 셨다면 Facebook에서 fristarts와 공유하세요. 또한 Twitter와 Facebook에서 우리를 팔로우 할 수 있습니다. 우리는 여러분의 의견을 듣고 싶습니다. 우리의 작업을 개선, 보완 또는 혁신하고 더 나은 서비스를 제공 할 수있는 방법을 알려주십시오. [email protected]로 문의 해주세요
관련 기사 :
link : / summing-excel-sumproduct-function [Excel에서 SUMPRODUCT 함수 사용 방법]: Excel에서 여러 배열의 값을 곱한 후 SUM을 반환합니다.
link : / summing-sum-if-date-is-between [SUM if date is between]: 주어진 날짜 또는 기간 사이의 값의 합계를 Excel로 반환합니다.
link : / summing-sum-if-date-is-greater-than-given-date [Sum if date is greater than given date]: * Excel에서 주어진 날짜 또는 기간 이후 값의 합계를 반환합니다.
link : / summing-2-ways-to-sum-by-month-in-excel [Excel에서 월별 합계를 계산하는 2 가지 방법]: * Excel에서 주어진 특정 월 내 값의 합계를 반환합니다.
link : / summing-how-to-sum-multiple-columns-with-condition [조건을 사용하여 여러 열을 합하는 방법]: * Excel에서 조건이있는 여러 열의 값의 합계를 반환합니다.
인기 기사 :
link : / keyboard-formula-shortcuts-50-excel-shortcuts-to-increase-your-productivity [50 Excel 단축키로 생산성 향상]: 작업 속도를 높이세요. 이 50 개의 바로 가기를 사용하면 Excel에서 더 빠르게 작업 할 수 있습니다.
link : / formulas-and-functions-introduction-of-vlookup-function [Excel에서 VLOOKUP 함수 사용 방법]: 다양한 범위의 값을 조회하는 데 사용되는 Excel에서 가장 많이 사용되는 인기 함수 중 하나입니다. 및 시트. link : / tips-countif-in-microsoft-excel [Excel에서 COUNTIF 함수 사용 방법]:이 놀라운 함수를 사용하여 조건으로 값을 계산합니다. 특정 값을 계산하기 위해 데이터를 필터링 할 필요가 없습니다. Countif 기능은 대시 보드를 준비하는 데 필수적입니다.
link : / excel-formula-and-function-excel-sumif-function [Excel에서 SUMIF 함수 사용 방법]: 대시 보드의 또 다른 필수 기능입니다. 이를 통해 특정 조건에 대한 값을 합산 할 수 있습니다.