☰

첫 번째 * 첫 번째 대신 첫 번째 * 마지막을 곱하는 Sumproduct

tag-icon • tag-icon microsoft-excel microsoft-excel-2007
첫 번째 * 첫 번째 대신 첫 번째 * 마지막을 곱하는 Sumproduct

다음을 수행하기 위해 Excel 2007에서 일부 함수나 수식을 찾고 있습니다.

두 줄의 숫자가 있습니다.

1 -1  2 5 10 
1  2 -1 2  5

나는 sumproduct가 수행하는 것과 유사한 작업을 수행하고 싶습니다. 즉, 각 열의 항목을 여러 개로 합산한 다음 합계를 더하는 것입니다. 그러나 행 중 하나의 첫 번째 항목을 다른 행의 마지막 항목으로 곱한 다음 두 번째 항목을 마지막 두 번째 항목으로 곱하고 싶습니다.

그래서:

1 * 5 + -1 * 2 + 2 * -1 + 5 * 2 + 10 * 1

그냥 대신에

1 * 1 + -1 * 2 + ...

이 역순 합계 제품 유형 계산을 수행하는 합리적인 방법이 있습니까?

나는 내가 원하는 각 합계 제품에 대해 역순으로 추가 행을 생성할 필요가 없는 것을 선호합니다.

답변1

다음 수식을 사용하여 다른 행(예: 행 3)을 추가할 수 있습니다.

=INDEX($A$2:$J$2,COUNTA($A$2:$J$2)-COLUMN()+1)

그런 다음 SUMPRODUCT첫 번째와 세 번째 행을 수행하십시오.

단 하나의 진술만으로는 그것을 할 수 있는 방법을 볼 수 없습니다

답변2

예제가 A1:E1및 에 배치되어 있다고 가정하면 A2:E2다음은 두 번째 범위가 공급되는 방식을 수정하여 단일 수식에서 필요한 작업을 수행합니다 SUMPRODUCT().
(명확성을 높이고 스크롤 막대를 방지하기 위해 줄 바꿈)

=SUMPRODUCT(A1:E1,
            N(OFFSET(A2:E2,0,COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1))
           )

구성 요소를 간단히 살펴보면 다음과 같습니다.

OFFSET(A2:E2,0,COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1)

SUMPRODUCT()이는 범위에 걸쳐 효과적으로 반복됩니다( 에 의해 배열로 사용되기 때문에 ) A2:E2. 동일한 행( )에서 그렇게 0하지만 에 의해 계산된 수평 오프셋을 사용합니다 . 이는 1보다 작은 열(즉, 마지막 항목) COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1의 열 수에서 시작하여 0(즉, 첫 번째 항목)으로 줄어듭니다. A2:E2따라서 배열로 읽으면 역순으로 읽혀집니다.

N()이는 빈 셀, 텍스트 등이 0으로 읽혀지는지 확인하고 #VALUE!이러한 경우 오류를 방지하기 위해 호출 로 래핑됩니다 . 만약 너라면원하다오류를 생성하려면 N().

SUMPRODUCT()그런 다음 사용할 두 번째 어레이로 에 추가하면 됩니다 .

답변3

시작하는 데 도움이 되는 UDF는 다음과 같습니다.

Function SUMPRODREV(rForward As Range, rBackward As Range) As Double

    Dim i As Long, j As Long
    Dim vaForw As Variant
    Dim vaBack As Variant
    Dim dReturn As Double

    'put range values into arrays
    vaForw = rForward.Value: vaBack = rBackward.Value

    'if only 1 row, multiply columns
    If UBound(vaForw, 1) = 1 Then
        For i = LBound(vaForw, 2) To UBound(vaForw, 2)
            dReturn = dReturn + (vaForw(1, i) * vaBack(1, UBound(vaForw, 2) - (i - 1)))
        Next i
    Else 'if only 1 column, multiply rows
        For i = LBound(vaForw, 1) To UBound(vaForw, 1)
            dReturn = dReturn + (vaForw(i, 1) * vaBack(UBound(vaForw, 1) - (i - 1), 1))
        Next i
    End If

    SUMPRODREV = dReturn

End Function

답변4

다음은 필요한 작업을 수행하는 단일 수식이지만 이를 입력하는 데는 요령이 있습니다.

셀 범위가 다음과 같다고 가정해 보세요. 범위 이름을 "상단"과 "하단"으로 지정했습니다.

여기에 이미지 설명을 입력하세요

1
빈 셀 2개를 강조 표시합니다. 제 경우에는 다음과 같이 B4:C4 셀을 선택했습니다.

여기에 이미지 설명을 입력하세요

2
이것을 에 입력하세요.수식 입력줄배열 수식으로( Ctrl+ Shift+ 누르기 Enter):

=SUM(top*INDEX(bottom,1,LARGE(COLUMN(bottom),COLUMN(top))))

또는 다음과 같습니다(명명된 범위를 사용하지 않음).

=SUM(A1:J1*INDEX(A2:J2,1,LARGE(COLUMN(A2:J2),COLUMN(A1:J1))))

여기에 이미지 설명을 입력하세요

위와 같은 결과를 얻게 됩니다. 일반 SUMPRODUCT수익률 220.

단점은 글꼴 색상을 변경하거나 열이나 행을 숨겨서 추가 셀을 숨겨야 한다는 것입니다. 수식의 두 번째 부분(INDEX 부분)을 강제로 "이동"하거나 값 배열을 반환하려면 추가 셀이 필요합니다.

관련 정보