Excel에서 이것이 가능한지 확실하지 않습니다.
다음과 같은 시트가 있습니다.
+---------------+-------+--------+-------+-------+-------+-------+-------+-------+
| | August | September |
+---------------+-------+--------+-------+-------+-------+-------+-------+-------+
| Week Starting | 04/08 | 11/08 | 18/08 | 25/08 | 01/09 | 08/09 | 15/09 | 22/09 |
+---------------+-------+--------+-------+-------+-------+-------+-------+-------+
| Monday | | | | | | | | |
+---------------+-------+--------+-------+-------+-------+-------+-------+-------+
| Tuesday | | | | | | | | |
+---------------+-------+--------+-------+-------+-------+-------+-------+-------+
| Wednesday | | | | | | | | |
+---------------+-------+--------+-------+-------+-------+-------+-------+-------+
| Thursday | | | | | | | | |
+---------------+-------+--------+-------+-------+-------+-------+-------+-------+
| Friday | | | | | | | | |
+---------------+-------+--------+-------+-------+-------+-------+-------+-------+
| Week Extra | | | | | | | | |
+---------------+-------+--------+-------+-------+-------+-------+-------+-------+
| Week Total | | | | | | | | |
+---------------+-------+--------+-------+-------+-------+-------+-------+-------+
(실제 시트에는 모든 달이 포함되어 있습니다.)
그리고 다음과 같은 원시 데이터가 있습니다.
+------+------+----------+
| DDMM | Year | Quantity |
+------+------+----------+
| 0408 | 2014 | 2 |
| 0708 | 2014 | 7 |
| 0509 | 2014 | 12 |
| 0508 | 0000 | 6 |
+------+------+----------+
원시 데이터를 구문 분석하여 달력 테이블에 추가해야 합니다. 첫 번째 데이터 행은 "04/08" 아래의 월요일에 2를 더합니다.
연도가 0000이면 올바른 주에 대한 "Week Extra"에 숫자를 추가해야 합니다.
이것이 가능한가?
감사합니다
답변1
가장 좋은 방법은 원시 테이블의 날짜를 실제 Excel 날짜로 변환하는 것입니다. 실제 Excel 날짜에서 요일에 대한 텍스트, 월에 대한 텍스트, 주 시작 날짜를 추출할 수 있으며, 이를 테이블과 일치하도록 텍스트로 다시 변환할 수 있습니다. 이를 수행하는 공식은 아래 스크린샷에 나와 있습니다(입력하기에는 너무 장황하기 때문입니다).
이를 캘린더 테이블에 가져오려면 몇 가지 작업을 수행할 수 있습니다. =getpivotdata()달력의 각 셀에 대한 값을 추출하는 데 사용할 수 있는 피벗 테이블이 한 가지 방법입니다. 대신 =sumproduct()에 오버헤드가 적기 때문에 사용하는 것을 선호합니다 . 수식은 아래 스크린샷에 나와 있으며 간단한 설명이 이어집니다.
=sumproduct()값 범위에 걸쳐 참/거짓에 대한 여러 조건을 테스트한 다음(이 경우 각 행에 대한 조건 테스트) 행에 대한 모든 조건이 참일 때 다른 범위의 결과를 합산할 수 있습니다. 여기서는 E2:E5에 J2의 DD/MM이 포함되고, F2:F5에 J1의 월 텍스트가 포함되며, G2:G5에 I3의 WorkDay 텍스트가 포함되는지 테스트합니다. 모든 조건이 true이면 C2:C5의 값을 가져와서 합산합니다. 이 공식에서 주의할 점은 테스트 중인 범위의 길이가 항상 동일해야 한다는 것입니다. DD/MM의 경우 E2:E5, 월의 경우 F2:F20을 테스트할 수 없습니다. Sumproduct에서 오류가 발생합니다.