[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『仕事において1日の業務内容及び時間の計算をしたい』（ゆき）
1日の業務工数を計算できる関数を組んでみたい。
業務内容の項目と開始時間/終了時間(その差分から経過時間)の入力ができるシートを作成しました。
〈イメージ〉
A B C D E F
1 日付 開始時間 終了時間 経過時間 業務大項目 業務中項目
2
3
↓に1行目同様に記載が続けられる。
【ご相談内容】
上記の記載を続け、最終的に「1日においてなんの業務に何分費やしたのか」
を計算できる関数を別シートに作成したいです。
〈完成イメージ〉
業務A 業務B 業務C 業務D →業務項目が続く
1月1日 2:30 0:50 1:30 0:00
1月2日 0:00 2:30 1:00 0:30
1月3日
1月4日
↓日付が続く
※業務項目を大項目/中項目(実際作成しているのは小項目まである)ため、どのように参照で引っ張れるのか分からずに困っています。
※もし上記無視して推奨の方法などあれば押していただきたいです。
分かりにくく申し訳ございませんが優しい方、教えてください。
＜ 使用 Excel：Microsoft365、使用 OS：Windows11 ＞
（IT） 2024/01/26(金) 10:39:02
大項目・中項目・小項目を選択した場合、業務が特定できるイメージとなります。
例えば、
パターン1：Aの業務内の1の作業、そしてその中のaという業務
パターン2：Aの業務内の2の作業、そしてその中のbという業務
のように組み合わせが大量にできてしまいますが、その組み合わせごとの時間計算ができると嬉しいです。
※大中小項目それぞれ選択できる数は10選択肢程度。
シート1の日付も重複ありになります、
（ゆき） 2024/01/26(金) 10:57:49
＞※もし上記無視して推奨の方法などあれば押していただきたいです。
これってまだ表のレイアウトを、変更可と言う事でいいのですか？ 上の表の、業務大項目 業務中項目を1列に入れた方が良いかもと... パット見ですが...気がするだけかも^^;。。。 （あみな） 2024/01/26(金) 11:35:00
一列に入れたほうが良い…
そうですよね。そのほうがシンプルになりますよね。
ただそれは難しく。
集計する段階で1日の業務内容の工数時間の追加だけでなく、
中項目の項目ごとの時間集計などもしたく、そこは分けておくのが理想です。
（ゆき） 2024/01/26(金) 12:09:36
＞中項目の項目ごとの時間集計などもしたく
だぶるで必要なんですね、なるほど
もうひとつだけ確認ですが、おひとり様専用 で考えればいいんでしょうか？ （あみな） 2024/01/26(金) 12:18:54
くどいですが、元シートに個別の業務名があったほうがよいと思います。 分類方法はいくらでもあとから弾力的に修正可能です。 業務A 業務B 業務C 業務Dは、現在の元シートからは判断できないと思います。 業務A 業務B 業務C 業務Dがその中分類、大分類なんです、 と言う話なら結構ですが。（説明が分かりにくいですね、というだけですから） （xyz） 2024/01/26(金) 12:34:22
たぶんですが、複数の回答者は下記のような元表なら なんとでもしやすいねと言ってるかと思います。
●元シート
|[A] |[B] |[C] |[D] |[E] |[F]
[1] |日付 |開始時間|終了時間|経過時間|業務内容|分類
[2] |2024/1/20|9:00 |11:30 |2:30 |業務A |大項目
[3] |2024/1/20|11:30 |12:15 |0:45 |業務B |中項目
[4] |2024/1/20|13:00 |16:00 |3:00 |業務C |大項目
[5] |2024/1/20|16:00 |17:30 |1:30 |業務D |中項目
[6] |2024/1/21|9:15 |10:30 |1:15 |業務E |小項目
[7] |2024/1/21|10:30 |12:15 |1:45 |業務A |大項目
[8] |2024/1/21|13:00 |15:00 |2:00 |業務B |中項目
[9] |2024/1/21|15:00 |16:00 |1:00 |業務F |小項目
[10]|2024/1/21|16:00 |17:30 |1:30 |業務G |中項目
●別シート
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H]
[1]|日付 |業務A|業務B|業務C|業務D|業務E|業務F|業務G
[2]|2024/1/20|2:30 |0:45 |3:00 |1:30 | | |
[3]|2024/1/21|1:45 |2:00 | | |1:15 |1:00 |1:30
業務内容と、分類が、そろぞれ1列ならやりやすいですね。 これを多分ですが、複数の回答者は言ってます。(きっとです。) 業務内容と、分類は入力規則で設定してプルダウンで選択します。
※ただ、Microsoft365を使用しているので、最新の関数を巧みに使用すれば （ゆき）さんの方法でも出来るかもなので、しばらく待ってみます。 （あみな） 2024/01/26(金) 13:11:44
ここまでより詳細を記載していない中、皆様ありがとうございました。
もう少し詳細を記載してみます。
担当業務は代理店業務のため、
大項目…得意先
中項目…業務内容
※小項目は再整理を実施し、なしに変更しました。
そのため、以下のような記入を想定しています。
(得意先〇と業務〇の組み合わせが複数存在する)
得意先Aの1の業務
得意先Bの1の業務
得意先Aの2の業務
得意先Cの2の業務
上記のように得意先＆業務内容、そしてその開始/終了時間及び経過時間が下行に続いていきます。
そして、別シートにて
・(優先度高)1日の実施業務(どの得意先のどの業務を実施していたのか)
・(優先度低)一定期間における得意先ごとの工数時間
・(優先度低)一定期間における業務内容ごとの工数時間
などを計算できるようにしたかった、というのが趣旨でした。
説明が下手ですみませんでした。
（ゆき） 2024/01/26(金) 14:19:28
A B C D E F G H 1 日付 開始時間 終了時間 経過時間 業務大項目 業務中項目 業務小項目 業務名 2 1月1日 8:00 12:00 4:00 大１ 中２ 小２ 業務D 3 1月1日 13:00 17:00 4:00 大２ 中１ 小１ 業務E 4 1月2日 8:00 10:00 2:00 大１ 中１ 小１ 業務A 5 1月2日 10:00 12:00 2:00 大２ 中２ 小２ 業務H 6 1月2日 13:00 15:00 2:00 大１ 中２ 小２ 業務D 7 1月2日 15:00 17:00 2:00 大１ 中１ 小１ 業務A 8 1月3日 8:00 11:30 3:30 大２ 中１ 小１ 業務E 9 1月3日 11:30 12:00 0:30 大２ 中１ 小２ 業務F 10 1月3日 13:00 15:30 2:30 大１ 中２ 小１ 業務C 11 1月3日 15:30 17:00 1:30 大３ 中２ 小５ 該当なし
J K 1 業務分類表 2 大１中１小１ 業務A 3 大１中１小２ 業務B 4 大１中２小１ 業務C 5 大１中２小２ 業務D 6 大２中１小１ 業務E 7 大２中１小２ 業務F 8 大２中２小１ 業務G 9 大２中２小２ 業務H
M N O P Q R S T U 1 業務A 業務B 業務C 業務D 業務E 業務F 業務G 業務H 2 1月1日 0:00 0:00 0:00 4:00 4:00 0:00 0:00 0:00 3 1月2日 4:00 0:00 0:00 2:00 0:00 0:00 0:00 2:00 4 1月3日 0:00 0:00 2:30 0:00 3:30 0:30 0:00 0:00
上記のようにレイアウトされているとします（とりあえず同じシートで作ってみました）
D2=IF(B2>C2,"エラー",C2-B2)
下にコピー
H2=IFERROR(INDEX(K:K,MATCH(E2&F2&G2,J:J,0)),"該当なし")
下にコピー
N2=SUMPRODUCT(($A$2:$A$11=$M2)*($H$2:$H$11=N$1)*$D$2:$D$11)
右、下にコピー
セルの書式設定は必要に応じて行ってください。時刻で00:00とか。
M1:U4を切り取って（コピーはX）別のシートに貼り付けたら、別シートにできます。
（ゆたか） 2024/01/26(金) 14:53:04
元シートからの集計はピボットテーブルの利用が簡単だし汎用的です。 （xyz） 2024/01/26(金) 21:45:01
あれれ？、一番最初の「大項目、中項目、小項目」はやめたのですか？ う～ん、ならレイアウト変更しないと出来ないと思うけど。。。 （あみな） 2024/01/27(土) 08:00:03
> そのため、以下のような記入を想定しています。 > (得意先〇と業務〇の組み合わせが複数存在する)
なるほど。。やっと、意味が解りました。( たぶん。。。) レイアウトは、こちらで決定しちゃうけどいいですか？ 連絡がありませんので...驚異の憶測発動!!
●元シート( Sheet1 )
|[A] |[B] |[C] |[D] |[E] |[F]
[1] |日付 |開始時間|終了時間|経過時間|得意先 |業務内容
[2] |2024/1/20 |9:00 |11:30 |2:30 |得意先●|業務A
[3] |2024/1/20 |11:30 |12:15 |0:45 |得意先●|業務B
[4] |2024/1/20 |12:45 |16:00 |3:15 |得意先□|業務C
[5] |2024/1/20 |16:00 |17:30 |1:30 |得意先□|業務D
[6] |2024/1/21 |9:15 |10:30 |1:15 |得意先▲|業務E
[7] |2024/1/21 |10:30 |12:15 |1:45 |得意先●|業務A
[8] |2024/1/21 |13:00 |15:00 |2:00 |得意先●|業務B
[9] |2024/1/21 |15:00 |16:00 |1:00 |得意先▲|業務F
[10]|2024/1/21 |16:00 |17:30 |1:30 |得意先▲|業務G
[11]|2024/1/22 |8:00 |12:00 |4:00 |得意先□|業務C
[12]|2024/1/22 |13:00 |14:15 |1:15 |得意先●|業務B
[13]|2024/1/22 |14:15 |17:30 |3:15 |得意先□|業務D
[14]|2024/1/23 |8:30 |12:00 |3:30 |得意先★|業務A
[15]|2024/1/23 |13:00 |15:00 |2:00 |得意先★|業務C
[16]|2024/1/23 |15:00 |17:45 |2:45 |得意先★|業務D
[17]
[18]|全工数時間| | |32:15 |
●別シート( 案1 )
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J]
[1]| | 期間指定＼業務内容 |業務A |業務B |業務C |業務D |業務E |業務F |業務G
[2]|得意先 |開始日 |終了日 |工数時間|工数時間|工数時間|工数時間|工数時間|工数時間|工数時間
[3]|得意先● |2024/1/20 |2024/1/23|4:15 |4:00 | | | | |
[4]|得意先□ |2024/1/20 |2024/1/23| | |7:15 |4:45 | | |
[5]|得意先▲ |2024/1/20 |2024/1/23| | | | |1:15 |1:00 |1:30
[6]|得意先★ |2024/1/20 |2024/1/23|3:30 | |2:00 |2:45 | | |
[7]|
[8]|全工数時間|32:15 | |7:45 |4:00 |9:15 |7:30 |1:15 |1:00 |1:30
※「得意先名、業務名」は、「元・別」シート必ず同じにする。 ※「別シート」の [1行目]に記入する業務名と、 [A列]の得意先名の意。 ※ 時間の表示形式は [h]:mm を使用する事、h:mm では [ NG ]です。 ※ また時間は [ 0:00 ]を表示させたくない場合は、[h]:mm;; にする。
D3の式=IF(D$1<>"",SUMIFS(Sheet1!$D:$D,Sheet1!$A:$A,">="&$B3,Sheet1!$A:$A,"<="&$C3,Sheet1!$F:$F,D$1,Sheet1!$E:$E,$A3),"") 右へ下にコピペ
●別シート( 案2 )
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N] |[O] |[P] |[Q] |[R] |[S] |[T] |[U] |[V] |[W] |[X]
[1]|得意先 |開始日 |終了日 |業務内容|工数時間|比率( % )|業務内容|工数時間|比率( % )|業務内容|工数時間|比率( % )|業務内容|工数時間|比率( % )|業務内容|工数時間|比率( % )|業務内容|工数時間|比率( % )|業務内容|工数時間|比率( % )
[2]|得意先● |2024/1/20|2024/1/23|業務A |4:15 |54.8% |業務B |4:00 |100.0% |業務C | |0.0% |業務D | |0.0% |業務E | |0.0% |業務F | |0.0% |業務G | |0.0%
[3]|得意先□ |2024/1/20|2024/1/23|業務A | |0.0% |業務B | |0.0% |業務C |7:15 |78.4% |業務D |4:45 |63.3% |業務E | |0.0% |業務F | |0.0% |業務G | |0.0%
[4]|得意先▲ |2024/1/20|2024/1/23|業務A | |0.0% |業務B | |0.0% |業務C | |0.0% |業務D | |0.0% |業務E |1:15 |100.0% |業務F |1:00 |100.0% |業務G |1:30 |100.0%
[5]|得意先★ |2024/1/20|2024/1/23|業務A |3:30 |45.2% |業務B | |0.0% |業務C |2:00 |21.6% |業務D |2:45 |36.7% |業務E | |0.0% |業務F | |0.0% |業務G | |0.0%
[6]|
[7]|全工数時間|32:15 | |業務A |7:45 | |業務B |4:00 | |業務C |9:15 | |業務D |7:30 | |業務E |1:15 | |業務F |1:00 | |業務G |1:30 |
※場合によっては、比較をしたいのなら最初から別シート( 案2 ) のように比率( % )の列があった方が、解りやすいかもですね。
●別シート( 案3 )
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I]
[1]| |期間指定 ＼ 業務内容| 業務A | 業務B | 業務C
[2]|得意先 |開始日 |終了日 |工数時間| 比率(%) |工数時間| 比率(%) |工数時間| 比率(%)
[3]|得意先●|2024/1/20|2024/1/23 |4:15 |54.8% |4:00 |100.0% | |0.0%
[4]|得意先□|2024/1/20|2024/1/23 | |0.0% | |0.0% |7:15 |78.4%
[5]|得意先▲|2024/1/20|2024/1/23 | |0.0% | |0.0% | |0.0%
[6]|得意先★|2024/1/20|2024/1/23 |3:30 |45.2% | |0.0% |2:00 |21.6%
[7]|
[8]|2024年 |社員名 |全工数時間|工数時間|全体比(%)|工数時間|全体比(%)|工数時間|全体比(%)
[9]|1月度 |○○○ |32:15 |7:45 |24.0% |4:00 |12.4% |9:15 |28.7%
※ １行目の業務名のセルを、結合 [ D1:E1 ]するタイプです。 （あみな） 2024/01/27(土) 14:05:00
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.