エクセルでの作業

質問

表の様に本日作業No.下のセル間(D5～D14)に作業No.が入っている項目の総作業時間をD3に表示させたい時は
どうしたらいいのでしょうか？　
今まで作業列を設け合計を出していましたが列の制限数に達してしまいそうなので・・・・
アドバイスお願いします。

回答No.5

kagakusuki · ベストアンサー

或いは、別シートにデータを日付順に並べ直した上で、別シートのE列に総作業時間を表示するという方法は如何でしょうか。

【方法その1】作業列と関数を使用する方法
　今仮に、Sheet1の
A3セルに　　作業No.
B3セルに　　作業項目
C3セルに　　作業時間
D3セルに　　作業日
と入力されていて、
Sheet1のA列～D列の4行目以下に各データが入力されていて、
Sheet1のD1セルに指定の日付を入力すると、その日付がSheet2のA1セルにも表示されると共に、
Sheet1のC1セルとSheet2のC1セルに指定した日の総作業時間が表示され、
Sheet2のA2以下に指定した日の作業No.、
Sheet2のB2以下に指定した日の作業項目、
Sheet2のC2以下に指定した日の作業時間
の一覧が表示されるものとします。

　まず、Sheet1のB1セルに次の数式を入力して下さい。（※Sheet1に関しては、ANo.4と同じです）

=IF(ISNUMBER(1/DAY($D$1)/($D$1<>"")),"総作業時間","")

　次に、Sheet1のC1セルに次の数式を入力して下さい。

=IF(ISNUMBER(1/DAY($D$1)/($D$1<>"")),SUMIF($D$3:INDEX($D:$D,MATCH(9^9,$D:$D)),$D$1,$C$3:INDEX($C:$C,MATCH(9^9,$D:$D))),"日付入力→")

　次に、適当な使用していないシート（ここでは仮にSheet3とします）のA4セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER(DAY(INDEX(Sheet1!$D:$D,ROW()))),INDEX(Sheet1!$D:$D,ROW())<>""),INDEX(Sheet1!$D:$D,ROW())+COUNTIF(Sheet1!$D$3:INDEX(Sheet1!$D:$D,ROW()),"="&INDEX(Sheet1!$D:$D,ROW()))/COUNTIF(Sheet1!$D$3:INDEX(Sheet1!$D:$D,MATCH(9^9,Sheet1!$D:$D)),"="&INDEX(Sheet1!$D:$D,ROW())),"")

　次に、Sheet3のA4セルをコピーして、Sheet3のA5以下に貼り付けて下さい。
　次に、Sheet2のA2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet3!$A:$A),"",INDEX(Sheet1!A:A,MATCH(SMALL(Sheet3!$A:$A,ROWS($2:2)),Sheet3!$A:$A,0)))

　次に、Sheet2のA2セルをコピーして、Sheet2のB2～D2の範囲に貼り付けて下さい。
　次に、Sheet2のE2セルに次の数式を入力して下さい。

=IF(AND($D2<>"",COUNTIF($D$1:$D2,$D2)=1),SUMIF($D:$D,$D2,$C:$C),"")

　次に、Sheet2のA2～E2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

【方法その2】作業列は使用せず、関数のみで処理する方法（但し、処理に要する負荷は大）
　今仮に、Sheet1の
A3セルに　　作業No.
B3セルに　　作業項目
C3セルに　　作業時間
D3セルに　　作業日
と入力されていて、
Sheet1のA列～D列の4行目以下に各データが入力されていて、
Sheet1のD1セルに指定の日付を入力すると、その日付がSheet2のA1セルにも表示されると共に、
Sheet1のC1セルとSheet2のC1セルに指定した日の総作業時間が表示され、
Sheet2のA2以下に指定した日の作業No.、
Sheet2のB2以下に指定した日の作業項目、
Sheet2のC2以下に指定した日の作業時間
の一覧が表示されるものとします。

　まず、Sheet1のB1セルに次の数式を入力して下さい。（※Sheet1に関しては、ANo.4や【方法その1】と同じです）

=IF(ISNUMBER(1/DAY($D$1)/($D$1<>"")),"総作業時間","")

　次に、Sheet1のC1セルに次の数式を入力して下さい。

=IF(ISNUMBER(1/DAY($D$1)/($D$1<>"")),SUMIF($D$3:INDEX($D:$D,MATCH(9^9,$D:$D)),$D$1,$C$3:INDEX($C:$C,MATCH(9^9,$D:$D))),"日付入力→")

　次に、Sheet2のD2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet1!$D$3:INDEX(Sheet1!$D:$D,MATCH(9^9,Sheet1!$D:$D))),"",SMALL(Sheet1!$D$3:INDEX(Sheet1!$D:$D,MATCH(9^9,Sheet1!$D:$D)),ROWS($2:2)))

　次に、Sheet2のA2セルに次の数式を入力して下さい。

=IF($D2="","",INDEX(Sheet1!A:A,SUMPRODUCT(ROW(Sheet1!$D$3:INDEX(Sheet1!$D:$D,MATCH(9^9,Sheet1!$D:$D)))*(Sheet1!$D$3:INDEX(Sheet1!$D:$D,MATCH(9^9,Sheet1!$D:$D))=$D2)*(COUNTIF(OFFSET(Sheet1!$D$3,,,ROW(Sheet1!$D$3:INDEX(Sheet1!$D:$D,MATCH(9^9,Sheet1!$D:$D)))-ROW(Sheet1!$D$3)+1),$D2)=COUNTIF($D$1:$D2,$D2)))))

　次に、Sheet2のA2セルをコピーして、Sheet2のB2～C2の範囲に貼り付けて下さい。
　次に、Sheet2のE2セルに次の数式を入力して下さい。（※Sheet2のE2セルに関しては、【方法その1】と同じ数式です）

=IF(AND($D2<>"",COUNTIF($D$1:$D2,$D2)=1),SUMIF($D:$D,$D2,$C:$C),"")

　次に、Sheet2のA2～E2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

お礼

使い勝手のいいシートにして頂き感謝するかぎりです。
今後は自分で考え作れるようにしたいですね、ありがとうございました。

回答No.4

kagakusuki

回答番号ANo.1、3です。

>本日行なう作業がどんな作業内容かを確認する事がやり辛くなってしまいますので

　それではD列に作業を行なう予定日を入力しておき、D1セルで日付を指定すると、C1セルに指定した日付の総作業時間を表示し、別シートに指定した日付の作業内容の一覧を表示させては如何でしょうか。

　今仮に、Sheet1の
A3セルに　　作業No.
B3セルに　　作業項目
C3セルに　　作業時間
D3セルに　　作業日
と入力されていて、
Sheet1のA列～D列の4行目以下に各データが入力されていて、
Sheet1のD1セルに指定の日付を入力すると、その日付がSheet2のA1セルにも表示されると共に、
Sheet1のC1セルとSheet2のC1セルに指定した日の総作業時間がひょうじされ、
Sheet2のA4以下に指定した日の作業No.、
Sheet2のB4以下に指定した日の作業項目、
Sheet2のC4以下に指定した日の作業時間
の一覧が表示されるものとします。

　まず、Sheet1のB1セルに次の数式を入力して下さい。

=IF(ISNUMBER(1/DAY($D$1)/($D$1<>"")),"総作業時間","")

　次に、Sheet1のC1セルに次の数式を入力して下さい。

=IF(ISNUMBER(1/DAY($D$1)/($D$1<>"")),SUMIF($D$3:INDEX($D:$D,MATCH(9^9,$D:$D)),$D$1,$C$3:INDEX($C:$C,MATCH(9^9,$D:$D))),"日付入力→")

　次に、適当な使用していないシート（ここでは仮にSheet3とします）のA2セルに次の数式を入力して下さい。

=IF(AND(Sheet2!$A$1<>"",INDEX(Sheet1!$D:$D,ROW())=Sheet2!$A$1),ROW(),"")

　次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に貼り付けて下さい。
　次に、Sheet2のA1セルに次の数式を入力して下さい。

=IF(ISNUMBER(1/DAY(Sheet1!$D$1)/(Sheet1!$D$1<>"")),Sheet1!$D$1,"")

　次に、Sheet2のA4セルに次の数式を入力して下さい。

=IF(ROWS($4:4)>COUNT(Sheet3!$A:$A),"",INDEX(Sheet1!$A:$A,SMALL(Sheet3!$A:$A,ROWS($4:4))))

　次に、Sheet2のB4セルに次の数式を入力して下さい。

=IF($A4="","",VLOOKUP($A4,Sheet1!$A:B,COLUMNS(Sheet1!$A:B),FALSE))

　次に、Sheet2のB4セルをコピーして、Sheet2のC4セルに貼り付けて下さい。
　次に、Sheet2のA4～C4の範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。

　後は、Sheet１のC4以下に各作業の所要時間を入力し、Sheet１のD1セルに日付を入力してから、Sheet1のC1セルに表示される総作業時間を見ながら、Sheet１のD4以下に作業を行なう予定日を割り振って行けば良いと思います。
　尚、Sheet2のA4セルに入力する数式を次の様な数式と差し替えますと、計算処理に要する負荷は大きくなりますが、Sheet3のA列を使用する事なく、Sheet2の一覧を表示させる事が出来ます。

=IF(ROWS($4:4)>COUNTIF(Sheet1!$D:$D,$A$1),"",INDEX(Sheet1!$A:$A,SUMPRODUCT(ROW(Sheet1!$D$3:INDEX(Sheet1!$D:$D,MATCH(9^9,Sheet1!$D:$D)))*(Sheet1!$D$3:INDEX(Sheet1!$D:$D,MATCH(9^9,Sheet1!$D:$D))=$A$1)*(COUNTIF(OFFSET(Sheet1!$D$3,,,ROW(Sheet1!$D$3:INDEX(Sheet1!$D:$D,MATCH(9^9,Sheet1!$D:$D)))-ROW(Sheet1!$D$3)+1),$A$1)=ROWS($4:4)))))

回答No.3

kagakusuki

回答番号ANo.1です。
　又、総作業時間を求める関数を、次の様なものとすれば、作業No.を入力する行を、必ずしもA列と同じ行にしなくとも良くなります。（別に同じ行に入力しても構いませんし、D4以下であれば、どの行に入力しても構いませんから、途中に空欄や、A列にはない文字列を挟んで、順不同に並べても構いません）

=SUMPRODUCT(INDEX($C:$C,ROW($C$4)+1):INDEX($C:$C,MATCH(9^9,$C:$C))*(COUNTIF($D$4:INDEX($D:$D,MATCH(9^9,$D:$D)),INDEX($A:$A,ROW($A$4)+1):INDEX($A:$A,MATCH(9^9,$C:$C)))>0)*1)

お礼

2度もアドバイス頂きありがとうございます。
回答3だとスクロールせずに本日行う作業No.が一目で判り大変便利だと思いますが
本日行なう作業がどんな作業内容かを確認する事がやり辛くなってしまいますので
作業No.入力時に行指定する事でフィルター機能を使って確認する事を考えていました。
関数等を新たに組み込めば解決できる問題かも知れませんが
入力間違えのリスクもありますが回答No.1を使用したいと思います。

回答No.2

kamikami30

本日の作業No.という項目が、それぞれの作業を行ったかどうかという意味しかなしていないと考えたのですが、いかがでしょうか？

サンプルを作ってキャプチャーしたものを添付してみました。

以下、ご説明させていただきます。

B4～K17のセルには
データの入力規則で、○と×のみ入力できるようにしてあります。

L4のセルには
=IF(B4="○",$B$3,0)+IF(C4="○",$C$3,0)+IF(D4="○",$D$3,0)+IF(E4="○",$E$3,0)+IF(F4="○",$F$3,0)+IF(G4="○",$G$3,0)+IF(H4="○",$H$3,0)+IF(I4="○",$I$3,0)+IF(J4="○",$J$3,0)+IF(K4="○",$K$3,0)

続いてL5のセルには
=IF(B5="○",$B$3,0)+IF(C5="○",$C$3,0)+IF(D5="○",$D$3,0)+IF(E5="○",$E$3,0)+IF(F5="○",$F$3,0)+IF(G5="○",$G$3,0)+IF(H5="○",$H$3,0)+IF(I5="○",$I$3,0)+IF(J5="○",$J$3,0)+IF(K5="○",$K$3,0)

質問のエクセルで、日別にデータを管理されているようでしたら、このように作成していただくと、
毎日マウス操作のみで、データの入力を完了でき、マウスのスクロールのみで日々のデータをスクロールして確認していただけると思います。

お礼

アドバイスありがとうございます。
作業効率を考え○×での数式を考えて頂きうれしい限りです
ただ作業項目数が1000以上もあり一覧にするだけでも列が足りなくなってしまいます。
今回の件では使用できませんが、何かの時に役立つと思っています
ありがとうございました。

回答No.1

kagakusuki

=SUMIF($D$4:INDEX($D:$D,MATCH(MAX($C:$C)+1,$C:$C)),">0",$C$4:INDEX($C:$C,MATCH(MAX($C:$C)+1,$C:$C)))

で如何でしょうか。
　尚、総作業時間時間を表示するセルが、D3セルではなく、D列以外にあるセルとした場合には、以下のように簡略化できます。

=SUMIF($D:$D,">0",$C:$C)