Excel에서 해당 행이 다른 테이블의 데이터와 일치하고 특정 날짜 범위 내에 있는 경우 열의 값을 필터링하고 가져오고 합계를 계산하려면 어떻게 해야 합니까?

Question

좋습니다. 문맥상 이 링크를 통해 시작했지만 지금은 사용 사례에 적용하려고 하는데 막혔습니다. Excel - 다른 테이블의 다른 열과 일치하는 열을 기준으로 값 합계.

청구될 날짜, 프로젝트 및 작업 정보, 근무 시간의 시작 및 종료 시간을 포함하여 작업에 입력하는 시간별 로그가 있습니다. 이 로그는 해당 시간부터 내가 일한 시간을 계산합니다. 입력되었습니다(15분 단위로 반올림됨).

예제 데이터를 포함한 로그("timeLog")의 모양은 대략 다음과 같습니다.

	ㅏ	비	씨	디	이자형
1	날짜	프로젝트 및 작업	시작 시간	종료 시간	시간
2	2023년 1월 1일	프로젝트 1:작업 1	오전 8시	오전 10시	2
삼	2023년 1월 1일	프로젝트 2:작업 1	오전 10시	오후 4시	6
4	2023년 1월 6일	휴일 시간	오전 8시	오후 4시	8
5	2023년 1월 7일	아픈 시간	오전 8시	오후 12시	4
6	2023년 2월 6일	프로젝트 1:작업 1	오전 8시	오후 4시	8

   ^{[에서 옮겨옴이 사진.]}

다른 시트에는 모든 프로젝트와 관련 작업을 나열하는 테이블이 있습니다("프로젝트 및 작업 ID" 열). 각 프로젝트에는 연관된 유형(즉, 근무 시간, 휴일, 병가 또는 휴가)이 있습니다.

프로젝트 정보 테이블("projectTable")은 다음과 같습니다.

	ㅏ	비
1	프로젝트 및 작업 ID	유형 ID
2	프로젝트 1:작업 1	일했다
삼	프로젝트 2:작업 1	일했다
4	휴일 시간	휴일
5	아픈 시간	아픈

      ^{[에서 옮겨옴이 사진.]}

누적된 병가/휴가를 계산하려면 특정 청구 기간에 근무한 시간을 계산해야 합니다. 그러나 전체 FTE 계산에 사용하기 때문에 병가/휴가/휴일을 포함하여 제출한 전체 시간도 시간별 로그에 남아 있어야 합니다.

다음은 현재 "작업" 유형 ID를 가진 프로젝트와 연결된 청구 기간(12/26/22-1/25/23)의 모든 시간을 합산하기 위해 사용하고 있는 것입니다.

=SUM(IFERROR(IF(COUNTIFS(projectTable[Project & Task ID],FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0),projectTable[Type ID],"Worked")>0,timeLog[Hours],0),0))

먼저 FILTER를 사용하여 지정된 기간 동안의 모든 테이블 항목을 가져옵니다.

FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0)

그런 다음 COUNTIFS는 이를 확인하여 "Worked" TypeID를 가진 프로젝트가 있는지 확인합니다.

COUNTIFS(projectTable[Project & Task ID],FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0),projectTable[Type ID],"Worked")

그런 다음 IF는 각 행의 시간 값을 반환하고 IFERROR는 계속 발생하는 오류를 '수정'하며 SUM은 해당 기간의 총 "근무" 시간을 계산합니다.

그러나 이 방법은 최선의 방법은 아닌 것 같습니다. 취약하고 예상치 못한 오류를 반환하며 전체적으로 잘못된 방향으로 가고 있는 것 같습니다. 이를 수행하는 더 좋은 방법은 무엇입니까?

Answer 1

나는 귀하의 솔루션을 연구하지 않았지만 한눈에 그것이 불필요하게 복잡해 보인다는 데 동의합니다. 나는 시간별 로그에 다음을 포함하는 여섯 번째 열을 추가할 것을 제안합니다.

=VLOOKUP(B2, projectTable!$A$2:$B$5, 2, FALSE)

그러면 다음과 같이 시간별 로그의 "프로젝트 및 작업"이 프로젝트 정보 테이블의 "유형 ID"로 변환됩니다.

	ㅏ	비	씨	디	이자형	에프
1	날짜	프로젝트 및 작업	시작 시간	종료 시간	시간	유형 ID
2	2023년 1월 1일	프로젝트 1:작업 1	오전 8시	오전 10시	2	일했다
삼	2023년 1월 1일	프로젝트 2:작업 1	오전 10시	오후 4시	6	일했다
4	2023년 1월 6일	휴일 시간	오전 8시	오후 4시	8	휴일
5	2023년 1월 7일	아픈 시간	오전 8시	오후 12시	4	아픈
6	2023년 2월 6일	프로젝트 1:작업 1	오전 8시	오후 4시	8	일했다

COUNTIFS그러면 and 를 사용하는 간단한 문제가 됩니다 SUMIFS. 예를 들어 다른 곳(예: A8:B10)에서 A8:A10에 유형을 입력하고

=SUMIFS($E$2:$E$6, $A$2:$A$6,">"&DATE(2022,12,25), $A$2:$A$6,"<"&DATE(2023,1,23), $F$2:$F$6,A8)

B8에서 아래로 드래그/채우기:

	ㅏ	비
8	일했다	8
9	휴일	8
10	아픈	4

물론 하드 코딩된 날짜는 셀 참조로 대체될 수 있습니다.

---

나는 막연하게 비슷하지만 더 복잡한 대답을 했습니다.질문 8년 전.