月ごとの集計と平均

投稿者 : ましわか     投稿日時 : 2024/07/05(Fri) 15:57:18     OS : Windows 11     EXCEL : Excel 2021

---

現在月ごとの残業時間の集計をするピポットテーブルを組んでいます。
このピポットテーブルの集計結果をもとに、月ごとの平均残業時間を集計したいと思っています。

A列に社員コードと氏名、B列から４月、５月、６月、７月、累計と集計結果がピポットテーブルで表示されます。

列指定では月が変わった際にズレが生じてしまいます。また、毎週集計結果を共有するため、平均残業時間は前月までの平均を出したいです。

完全な初心者で現在勉強しながらコードを書いているため、列指定の方法や、平均の計算方法などいいお知恵があればご教示いただければありがたいです。
よろしくお願いいたします。

投稿者 : さんこう     投稿日時 : 2024/07/05(Fri) 16:23:29

---

＞完全な初心者で現在勉強しながらコードを書いているため、列指定の方法や、平均の計算方法などいいお知恵があればご教示いただければありがたいです。
よろしくお願いいたします。

漠然としたご質問なので、なんともいえません。

＞列指定では月が変わった際にズレが生じてしまいます。

なにがズレるのでしょうか？


＞平均残業時間は前月までの平均を出したいです。

シートのレイアウトを示して、どの範囲の平均をだしたいのか明らかにしてみるといいでしょう。

できれば、現状のコードも提示してみるとよろしいかと思います。

投稿者 : ましわか     投稿日時 : 2024/07/05(Fri) 17:10:33

---

■[返信 1] さんこうさん（2024-07-05 16:23:29）の記事
＞ ＞完全な初心者で現在勉強しながらコードを書いているため、列指定の方法や、平均の計算方法などいいお知恵があればご教示いただければありがたいです。
＞ よろしくお願いいたします。
＞
＞ 漠然としたご質問なので、なんともいえません。
＞
＞ ＞列指定では月が変わった際にズレが生じてしまいます。
＞
＞ なにがズレるのでしょうか？
＞
＞
＞ ＞平均残業時間は前月までの平均を出したいです。
＞
＞ シートのレイアウトを示して、どの範囲の平均をだしたいのか明らかにしてみるといいでしょう。
＞
＞ できれば、現状のコードも提示してみるとよろしいかと思います。


すみません。質問できるレベルまで勉強して出直します。
それまでは手作業で集計します。

投稿者 : てらてら     投稿日時 : 2024/07/06(Sat) 06:17:19

---

こんにちは。

>A列に社員コードと氏名、B列から４月、５月、６月、７月、累計と集計結果がピポットテーブルで表示されます。

>列指定では月が変わった際にズレが生じてしまいます。また、毎週集計結果を共有するため、平均残業時間は前月までの平均を出したいです。

これは、以下のようなイメージなのだとしたら、データの作り方がうまくないと思います。

　　　A　　　　　 　　B　　　　C　　　D　　　 E　　　　F　　　　G　　
[社員コードと氏名]、[４月]、[５月]、[６月]、[７月]、[累計]、[集計結果]

[社員コードと氏名]、[４月]、[５月]、[６月]、[７月]、[８月]、[累計]、 [集計結果]

[社員コードと氏名]、[４月]、[５月]、[６月]、[７月]、[８月]、[９月]、 [累計]、 [集計結果]



最初から、以下のように作っておけば、列はズレません。

[社員コードと氏名]、[４月]、[５月]、[６月]、[７月]、[８月]、[９月]、[１０月]、、、、、[２月]、[３月]、[累計]、[集計結果]

ピボットテーブルを使わずに、VBAで上記のようなフォーマットに値を転記していくのが常套手段のように思います。


もちろん、列がズレる場合でも、集計を計算する事は出来ます。
その場合は、１行目の項目名から[累計]を見つけて、B列から [累計]列‐２ の平均値を計算して別の列にでも書き込めばOKでしょう。
ただ、月のデータが増えていくたびにズレていくような方法は、エラーがあった時に見つけずらいのでお勧めしません。

投稿者 : 通りすがり10     投稿日時 : 2024/07/06(Sat) 20:43:07

---

月ごとの平均残業時間を求めたい。
勤務時間や残業時間に関する元になるテーブル（シート上の表の進化系）があれば、月ごとの平均残業時間を求めるピボットテーブルをもう一枚別に作ればいいだけなのでは？
Excelのバージョンによって難しいかもしれませんが。

ピボットテーブルで、集計範囲に合計などでなく平均を出す指定もありますよ。

投稿者 : 通りすがり10     投稿日時 : 2024/07/06(Sat) 21:13:36

---

日付 名前 残業時間
2024/7/1 A 1:00:00
2024/7/5 B 1:00:00
2024/7/9 C 2:00:00
2024/7/13 A 1:30:00
2024/7/17 B 1:00:00
2024/7/21 C 0:30:00
2024/7/25 A 1:15:00
2024/7/29 B 1:00:00
2024/8/2 C 0:30:00
2024/8/6 A 0:00:00
2024/8/10 B 1:45:00
2024/8/14 C 1:30:00
2024/8/18 A 2:30:00
2024/8/22 B 1:45:00
2024/8/26 C 2:00:00
2024/8/30 A 0:30:00

この表をテーブル化してピボットテーブルで。
列に

月(日付)
だけを指定して（余分なのは列から削除）

Σ値
に平均/残業時間

とすれば月ごとの残業時間は出ます。
後はご自分の環境に合わせてください。

投稿者 : ましわか     投稿日時 : 2024/07/08(Mon) 14:42:10

---

みなさまありがとうございます。
現在やっている方法が通りすがりさんのおっしゃるように合計と平均を別に計算して印刷用のフォーマットにコピペする方法になります。
集計のたびにピポットの範囲指定を何度も繰り返すのが手間なので、簡略化できないかと考えたところでした。説明不足で申し訳ありません。

てらてらさんのおっしゃるように最初から１２か月分の枠を用意して作成してみようと思います。
ありがとうございました！

投稿者 : 通りすがり10     投稿日時 : 2024/07/09(Tue) 03:58:28

---

■[返信 6] ましわかさん（2024-07-08 14:42:10）の記事
＞ みなさまありがとうございます。
＞ 現在やっている方法が通りすがりさんのおっしゃるように合計と平均を別に計算して印刷用のフォーマットにコピペする方法になります。
＞ 集計のたびにピポットの範囲指定を何度も繰り返すのが手間なので、簡略化できないかと考えたところでした。説明不足で申し訳ありません。
＞
＞ てらてらさんのおっしゃるように最初から１２か月分の枠を用意して作成してみようと思います。
＞ ありがとうございました！

Excelシート上のテーブルと言う概念を作ったマイクロソフトが可哀そうな解決になりましたね。
テーブルについて調べたら毎回範囲を再設定する必要がない事がわかると思うのですが。
何か特殊な事情でもあったのでしょうか？