1 日ごとに別々のワークシート (TAB) にデータが入力されているファイルがあります。レイアウトは同じですが、これらの別々の TAB から過去 1 ～ 3 か月の動的な平均を取得するソリューションを探しています。毎日新しい TAB が作成されますが、毎月手動で行うと時間がかかりすぎます。何か提案はありますか?
答え1
データ > 統合または 3D 式を試すことができます
例: 次のシートがあります Jan Feb Mar Summary
各シートに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 式を作成するために使用されます（2 番目のステップ）。
仮にLDATE保持する最後関連する日付（別のセルへの参照、または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 は除きます。LDATE はユーザーが定義します) は、次のようになります。
=AVERAGE(INDIRECT("'"&TEXT(LDATE,"DD-MM-YY")&":"&TEXT(DATE(YEAR(LDATE),MONTH(LDATE)-3,DAY(LDATE)),"DD-MM-YY")&"'!"&"A3"))
幸運を！