하루에 별도의 워크시트( TAB )에 데이터가 입력되는 파일이 있습니다. 레이아웃은 동일합니다. 저는 별도의 탭에서 지난 1~3개월의 동적 평균을 구하는 솔루션을 찾고 있습니다. 매일 새로운 TAB이 생성되고 있으며, 이를 매달 직접 수행하려면 많은 시간이 걸립니다. 어떤 제안?
답변1
데이터> 통합 또는 3D 수식을 사용해 볼 수 있습니다.
예: 다음 시트가 있습니다. Jan Feb Mar 요약
n개의 시트를 가정하면 A1에 몇 개의 숫자가 있습니다.
요약 시트로 이동 A1 =average('Jan:Mar'!A1)에 다음 수식을 입력하세요.
답변2
이를 위해 다음 2단계를 완료합니다.
1. 첫 번째 시트와 마지막 시트의 이름을 알아냅니다.
내가 이해한 대로 매일 새 시트를 추가하고 있는 것 같습니다. 시트 이름의 형식이 동일한 방식으로 지정되어 있는지 확인해야 합니다. 예를 들어 형식이 DD-MM-YY라고 가정해 보겠습니다(따라서 2020년 8월 1일은 01-08-20으로 읽혀집니다).
이 단계에서 우리는시트 범위 문자지난 3개월 동안(예:01-03-20:01-01-20), INDIRECT 공식에 사용되어 3D 공식을 생성합니다(두 번째 단계).
가정해보자날짜보유마지막관련 날짜(다른 셀에 대한 참조이거나 TODAY() 또는 TODAY()-1 등과 같은 수식일 수 있음) 이제 무엇인지 결정하세요.첫 번째해당 날짜. 마지막 날짜로부터 3*30일 전인 경우 다음을 사용합니다.
=LDATE-3*30
또는 DATE보다 정확히 3개월 전이어야 하는 경우 다음을 사용하세요.
=DATE(YEAR(LDATE),MONTH(LDATE)-3,DAY(LDATE))
이 첫 번째 날짜의 이름을 다음과 같이 지정하겠습니다.FDATE.
마지막으로 이것은 서식이 지정된 시트 범위 텍스트입니다. 이는 시트 이름의 서식을 DD-MM-YY로 지정했다고 가정하고 필요한 경우 다른 형식으로 변경하면 됩니다.
="'"&TEXT(LDATE,"DD-MM-YY")&":"&TEXT(FDATE,"DD-MM-YY")&"'!"
이제 값은 다음과 같아야 합니다.'14-07-20:14-04-20'!
전체 범위의 이름을 다음과 같이 지정하겠습니다.시트 범위.
2. 간접 + 3D 공식
INDIRECT 함수는 지정된 텍스트 문자열에서 유효한 참조를 반환하며 'Sheet1:Sheet4'!A1:B2는 Sheet1에서 Sheet4 사이의 모든 시트에 있는 모든 A1:B2 셀의 범위입니다.따라서 다음은 동적 범위의 평균을 구합니다(예를 들어 모든 A3 셀의 평균이 필요하다고 가정).
=AVERAGE(INDIRECT(SHEETRANGE&"A3"))
또는 모든 작업을 셀에서 수행하려는 경우(사용자가 정의할 LDATE는 제외):
=AVERAGE(INDIRECT("'"&TEXT(LDATE,"DD-MM-YY")&":"&TEXT(DATE(YEAR(LDATE),MONTH(LDATE)-3,DAY(LDATE)),"DD-MM-YY")&"'!"&"A3"))
행운을 빌어요!