☰

シート 1# --- 前月末サマリーシート

tag-icon • tag-icon microsoft-excel worksheet-function logging tracking
シート 1# --- 前月末サマリーシート

私の目標は、機器の使用時間を追跡し、合計時間と稼働時間を反映した毎日のレポートを作成することです。

ファイルを日付別に整理できるように、1 か月分のワークブックに 31 日分のワークシートを含むファイルを作成したいと考えています。

ファイル内のワークシートの順序は次のとおりです。

最初のワークシートは、前月の時間の開始概要になります。

次の31枚のワークシートは、月の各日ごとに1から31までです。

最後のワークシートには、基本的に、翌月の開始サマリーの開始時にのみ特別な値をコピーして貼り付けるために、月の最終日の終値に関する情報が含まれます。月の日数が変わっても影響を受けません。

ワークブックを設定して、ユーザーが数式を操作することなくすぐに使用できるようにしたいと思います。ユーザーが行う必要があるのは、最初のシートに開始時間を入力し、毎日、さまざまな機器の 1 日の終了時の合計を入力することだけです。

1 列の例:

シート 1# --- 前月末サマリーシート

セルの場所:(シート1~~~~セルA1)

セルの数式: 数式なし - 月末の時間にユーザーが入力した数値100

完成した外観:

100

シート 2#--- 1 日目

セルの場所:(シート2 ~~~~セルA1)

セルの数式: 数式なし - ユーザーが終業時刻の数値を入力しました

使用: 今日の合計時間 - ユーザーが一日の終わりに入力した値 -125

セルの場所:(シート2 ~~~~セルA2)

セル数式: =+'シート 1'!A1

使用: 前日の終了時からの時間 - (シート 1 から取得した値 ~~~~ セル A1-100

セルの場所:(シート2 ~~~~セルA3)

セル式: =A1-A2

使用方法: 本日のランタイム - セル シート 2 ~A1 の値からセル シート 2 ~A2 の「上記のセル」を減算する簡単な計算 -24

完成した外観:

124

100

24

翌日は以下のように設定されます。

シート 3#--- 2 日目

セルの場所:(シート3、セルA1)

セルの数式: 数式なし - ユーザーが終業時刻の数値を入力しました

使用: 今日の合計時間 - (ユーザーが一日の終わりに入力した値)148

セルの場所:(シート3、セルA2)

セル数式: =+'シート 2'!A1

使用: 前日の終了時からの時間数 - (シート2のセルA1から取得した値 -124

セルの場所:(シート 3 - セル A3)

セル式: =A1-A2

使用方法: 今日の実行時間 - セル シート 3 の値の簡単な計算 - A1 からシート 3~A2 を引く (「同じシート上の上記のセル」) -24

完成した外観:

148

124

24

それは簡単な部分でした......

複数の数字の列を使用して 31 日分を簡単に作成できるようになりました。

シート4#、

タブを右クリックし、移動またはコピーを選択してコピーを作成します。新しいタブの名前を「シート 4」に変更します。

上記の方法で新しいワークシートを作成すると、数式は前のシートと同じになり、同じ場所 (シート 2) から情報が取得されます。

セルA1ユーザーが入力した番号であり、その番号はコピーされたシートと同じように転送されます。

セルA2次のようにコピーされます: =+'sheet 2'!A1

セルA3同じシート内のセルから計算される通常の「=A1-A2」計算としてコピーされます。問題ありません。

私は次の式を希望しますセルA2自動的に更新されます:

=+'シート3'!A1次のシートを作るときに。

手動で修正せずに、新しいシートで修正されたセルの数式を取得するにはどうすればよいでしょうか。

1 枚のシートから 40 個以上のセルの情報を抽出して次の日に転送する場合、1 か月を設定するためにこれを 1,240 回以上手動で変更するのは多すぎます。

答え1

検索と置換メニュー。http://office.microsoft.com/en-us/excel-help/find-or-replace-text-and-numbers-on-a-worksheet-HP001216390.aspx

基本的に、数式で '=+'シート 3'!A1' を検索し、'=+'シート 4'!A1' に置き換えます。私はこれまで、このような Excel スプレッドシートを数多く作成する必要がありました。1 年間で 2 回作成しましたが、これが私にとって最善の解決策でした。

答え2

ここでは、タブ名が制御されており、説明どおりに昇順の数字で終わることを前提として、必要なことを実行する方法を示します。

詳しく説明します:

  1. 自分がどのシートにいるのかを知る必要があるので、前日のシートの名前を「計算」する必要があります。XX= =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)。シート 1 では文字列 "Sheet 1" が返され、シート 2 では文字列 "Sheet 2" が返されます。

  2. ここで、この文字列を解析して数値を算出し、そこから 1 を引いて昨日の数値を取得する必要がありますが、これは 1 日目ではない場合に限ります。ええ==IF(MID(XX,7,2)>1,MID(XX,7,2)-1,1)

  3. 最後に、これが魔法ですが、関数を使用してINDIRECT「ターゲット」をその場で計算します。 =INDIRECT("'Sheet "& YY &"'!A1")

すべてをまとめ、カスタマイズする必要がある部分を太字/斜体で表示します (コード内では強調表示が機能しないため、コード マークアップは使用しませんでした)。

=間接("'シート"& IF(MID(MID(CELL("ファイル名",A1),FIND("]",CELL("ファイル名",A1))+1,256),7,2)>1,MID(MID(CELL("ファイル名",A1),FIND("]",CELL("ファイル名",A1))+1,256),7,2)-1,1) &"'!A1）

更新する必要があるもの:

  • 「シート」--シートのプレフィックス（番号の前の部分）に置き換えます
  • '7' -- 上記のプレフィックスの長さ +1 に置き換えます (または関数に置き換えます)
  • 'A1' -- 前のシートから必要なセルのアドレスに置き換えます
  • 「ファイル名」は変更しないでください。そのままにしておく必要があります（特別な予約名です）。
  • CELL 関数内の A1 を変更する必要はありません。これは任意であり、この予約名に実際に必要ではないため、変更する必要はありません。

関連情報