Excel INDEX MATCH MATCH가 SUMPRODUCT와 결합되었나요?

Question

다음은 여러분이 시도해 볼 의향이 있다고 생각하는 방법입니다.

=SUM(XLOOKUP($G$2:$G3,$A$2:$A$4,INDEX($B$2:$E$4,,MATCH(H$1,$B$1:$E$1,0)))*H$2:H$3)

(저는 몇 가지 흥미로운 방법을 찾았지만 이 공식보다 더 길고 설명되어 있으며 INDEX/MATCH접근 방식에 익숙해 보이므로 사용하고 유지 관리하기가 쉬울 것입니다.)

따라서 작성된 수식에 대해 데이터 테이블을 "급여 테이블"의 경우 A1:E4에 복사하고 "직원/시간" 테이블의 경우 G1:J3에 복사했습니다. 수식의 주소 지정은 해당 범위에 적용됩니다. 정확한 상황에 맞게 조정하고 싶을 것입니다.

끝까지 읽으십시오. 내가 출력 테이블에 대해 어리석게 이야기하고 있다고 해서 당황하지 마십시오.(질문과 답변을 다시 읽을 때 "직원/시간" 테이블의 데이터 라인 아래에 결과를 갖고 싶다는 것을 알았습니다.) 위 공식은 공식 출력 테이블을 생성하는 동안 만든 참조를 수정했기 때문에 해당 요구에 적합합니다. 이므로 필요에 따라 붙여넣을 수 있습니다.)

=H1출력 테이블을 작성하기 위해 출력 테이블의 왼쪽 및 상단 헤더(이름 및 월)를 채우는 것과 같은 수식을 사용했습니다 . 이는 분명히 귀하에게 알려질 것이므로 귀하가 설정한 계산에 대해 필요에 따라 쉽게 조정할 수 있습니다. 마찬가지로, 나는 =SUM(M2#)첫 번째 행의 공식이 생성하는 전체 SPILL 배열을 참조하기 때문에 조정이 필요하지 않다는 장점이 있는 늪지 표준 합산 공식을 사용했습니다 .

내가 시도한 모든 접근 방식의 실제 어려움은 다음과 같은 공식으로 끝날 것이라는 점이었습니다.둘최종 출력에 SPILL 배열이 포함되어 있으며 Excel에서는 그러한 항목에 대해 보상하지 않습니다. 각각 고유한 용도가 있기 때문에 출력 테이블의 비용 영역을 구성하는 셀 블록의 왼쪽 상단 셀에 단일 수식을 가질 수 있는 단일 SPILL 배열로 미세 조정할 수 없습니다. SPILL 어레이를 가로가 아닌 아래로 배치하기로 결정한 후 문제의 "핵심"인 직원당 월별 비용에 대한 준비가 되었습니다.

특정 직업에 종사하는 각 직원의 월별 비용인 "고기"의 경우 공식을 사용합니다.

먼저 INDEX/MATCH:(현재 첫 번째 달 열)은 출력 테이블의 월을 "Rate Table" 열과 일치시키고 달러 영역의 열 2가 필요하다는 것을 찾습니다. 달러 영역을 INDEX-ing하면 외부의 1열 x 3행 XLOOKUP()조회 배열에 해당하는 1열 x 3행의 출력 배열 크기가 제공됩니다. 그렇게 일치하지 않으면 오류가 발생합니다. 그런 다음 출력 테이블에 나열된 모든 직원에 대한 출력을 얻기 위해 XLOOKUP()배열(이 경우)을 사용하는 직원의 이름을 찾습니다 . $L2#원하는 대로 아래로 유출됩니다.

다음으로 출력 테이블의 각 열에 수식을 복사하고 매월 모든 직원에 대한 비용을 입력하면 합계가 업데이트됩니다.

(그런데 "2월 21일"에 대한 예제 출력 합계가 올바르지 않기 때문에 수식에서 $380 대신 $440(15*$12=$180, 20*$13=$260, $180+$260=$440)이 생성됩니다.

따라서 문제를 해결하려면 출력 테이블에 있는 첫 번째 수식을 선택하고 를 누르고 F2-Edit수식을 강조 표시하고 복사한 다음 "직원/시간" 테이블로 이동하고 시간 데이터 뒤의 첫 번째 빈 행으로 이동하면 됩니다. "=SUM("을 입력하고 복사한 수식을 붙여넣은 다음 ")"를 입력하고 Enter 키를 누릅니다. "직원/시간" 테이블의 데이터와 일치하도록 참조를 조정한 후 셀에는 불필요한 출력 테이블에 대해 계산된 정보의 합계가 포함되었습니다. 셀을 복사하여 오른쪽에 붙여넣으면 원하는 결과가 나왔습니다.

마지막 생각: 총 비용 셀에 걸쳐 하나의 SPILL 어레이를 사용하는 데 도움이 되지 않았습니다. 슬프다. 사용되는 열이 아무리 많아도 복사하여 붙여넣어야 합니다.

Answer 1