Excel 문서를 쿼리하여 값이 나타나는 횟수를 확인하려고 합니다. 이를 통해 조건부 서식과 열 지도를 적용하여 날짜 동안 리소스가 사용되는 횟수를 확인할 수 있습니다.
개별 셀에 Jan1-Dec31이 있습니다. 다음과 같이 세 개의 열로 분할된 데이터를 쿼리해야 합니다.
A B C
Jan1 Jan12 Bob
Jan2 Jan10 Roger
Jan11 Jan14 Bob
수식은 A 열의 시작 날짜와 B 열의 종료 날짜를 사용하고 C 열의 리소스 이름 항목을 계산해야 합니다.
따라서 내 출력은 다음과 같이 내 히트 맵에 저장됩니다.
Bob Roger
Jan1 1 0
Jan2 1 1
Jan3 1 1
Jan4 1 1
Jan5 1 1
Jan6 1 1
Jan7 1 1
Jan8 1 1
Jan9 1 1
Jan10 1 1
Jan11 2 0
Jan12 2 0
Jan13 1 0
Jan14 1 0
각 리소스에 대해 365개의 수식을 만들지 않고도 확장할 수 있다면 좋을 것입니다.
답변1
나생각하다귀하의 질문을 올바르게 이해하고 있습니다. 내가 해석하는 방식은 "히트 맵" 입력을 확장하는 방법을 찾고 있다는 것인데, 내가 잘못 읽었을 수도 있습니다. 내가 틀렸다면 알려주세요.
이것은 SUMPRODUCT가 굉장한 전형적인 사례입니다.
=SUMPRODUCT(--($E2>=$A$2:$A$4)*($E2<=$B$2:$B$4)*(F$1=$C$2:$C$4))
위아래로 복사할 수 있습니다(이미지 참조). 실제 데이터에 맞게 범위를 편집해야 합니다.
그것은 이렇게 작동합니다. Bob의 2016년 1월 1일을 생각해 보세요. 날짜를 가져와 날짜를 시작 날짜와 비교하고 날짜가 시작 날짜 배열의 시작 날짜보다 크거나 같은지 여부에 따라 true/false 배열을 만듭니다. 그런 다음 종료 날짜 배열의 종료 날짜보다 작거나 같은 동일한 작업을 수행합니다. 그런 다음 Bob의 리소스 배열을 확인합니다. 마지막에는 세 개의 배열이 있습니다.
{참, 거짓, 거짓} * {참, 참, 참} * {참, 거짓, 참} -> {참, 거짓, 거짓}. *는 AND 연산자이므로 TRUE, TRUE, TRUE가 발생하는 모든 위치는 1이고 FALSE가 발생하면 0이 됩니다. 그런 다음 TRUE, FALSE, FALSE 배열을 1, 0, 0으로 강제 변환하고 결과를 합산합니다!
편집: 댓글에서 우리가 말하는 내용을 해결하는 방법은 다음과 같습니다.
