EXCEL2000を使用しています。
カレンダーを作成しています。その中に残業時間を日々入力していって最終的に月の残業時間を合計で出します。
そこまではいいのですが、残業時間を二つの種類に分けたいのです。
内容は
午後5時以降から残業タイムになります。
午後10時までは普通残業です。
午後10時以降から深夜残業となります。
そこで例えば
午後11時まで残業した場合その日の入力時間は「6」と入力します。
別のセルに「普通残業合計」「深夜残業合計」と分けて設定します。
それぞれに「普通残業合計」に「5」、「深夜残業合計」に「1」とそれぞれ自動に入るようにしたいわけです。
そしてそれぞれの残業時間も日々足ささっていくという設定にしたいのですが。
どのような関数をどのように設定すれば良いのか、詳しく教えていただきたいのですが。
なにとぞ宜しくお願い致します。

このQ&Aに関連する最新のQ&A

A 回答 (6件)

残業時間は自分で手打ちでするんですよね?


時間も5時までが定時、と毎日決まっているんですよね?

総残業時間が入ったセルをA1
普通残業時間が入ったセルをB1
深夜残業時間が入ったセルをC1とします。

B1のセルには
=IF(A1>5,5,A1)
と入力すればでませんか?
C1のところには
=(A1-5)
ででませんか?

深夜残業のところに0が表示されるのがいやなら
=IF(B1<=4,"",(A1-B1))
で空欄になりますよ。
    • good
    • 0
この回答へのお礼

大変お礼が遅れまして申し訳ございませんでした。
やっと環境も落ち着き皆様からの回答参考に試してみました。
KODAMARさんの回答では一日一日振り分けられるのですがその合計が
出ませんでした。
例えば11月1日に「1」を入力するとそれぞれのセル「普通残業」には1、「深夜残業」には0と表示されるのですが11月2日に「5」と入力すると「普通残業」には5、「深夜残業」には1と表示されるのです。
実際その場合は普通残業には「6」深夜残業に「0」と表示されたいのです。
私の質問内容も今一分かりずらいみたいでした。
もう一度ちゃんと分かりやすいように質問しなおしたいと思います。
本当にありがとうございました。

お礼日時:2001/11/19 10:42

(各行)A列   B列   C列      D列


( 1行)11月1日 6  =MAX(5,B1) =MIN(0,B1-5)
( 2行)11月2日 X 上のセルをコピー貼付け
  (中略)
(30行)11月30日 X 上のセルをコピー貼付け
(31行) 合計 =SUM(C1:C30) =SUM(D1:D30)

ずれてしまいました。以下も参照してください
C1の数式 : =MAX(5,B1)
D1の数式 : =MIN(0,B1-5)
C30の数式 : =SUM(C1:C30)
D30の数式 : =SUM(D1:D30)
    • good
    • 0
この回答へのお礼

この場をお借りしまして今回回答してくれました皆様に再度お礼を申し上げます。
本当にありがとうございました。
私の説明内容の悪さで皆様には大変ご迷惑をおかけ致しました。本当に申し訳ございませんでした。
皆様の回答を見ていますと、きっとちゃんと説明できていたなら皆さん簡単に答えられるものだと思いました。
ただ、私が技量のないだけにまた再び質問させていただきます。
もし、また回答願えましたら宜しくお願い致します。

お礼日時:2001/11/19 11:10

要は残業時間を入力(B列)すれば、自動的に普通残業(C列)と深夜残業(D列)に分けられれば良いのですよね



A列   B列   C列      D列
(1行)11月1日 6   =MAX(5,B1) =MIN(0,B1-5)
(2行)11月2日  X 上のセルをコピー貼付け
  (中略)
(30行)11月30日 X 上のセルをコピー貼付け
(31行) 合計 =SUM(C1:C30) =SUM(D1:D30)


C列とD列は数式です。B列に残業時間を入力すれば、C31のセルに普通残業時間合計が、D31に深夜残業時間の合計がアウトプットされます。

これでいいと思いますが。
    • good
    • 0
この回答へのお礼

大変お礼が遅れまして申し訳ございませんでした。
やっと環境も落ち着き皆様からの回答参考に試してみました。
皆様には貴重なお時間を費やしてもらったにもかかわらず、私の質問内容が分かりずらかったです。
もう一度質問しなおしてみたいと思います。
本当にありがとうございました。

お礼日時:2001/11/19 11:05

使う関数はIF関数がいいと思います(個人的に結構使うので)


以下はA1のセルにその日の残業時間がかかれている場合の関数です。

普通残業時間の列 =IF(A1<6,A1,5)
深夜残業時間の列 =IF(A1>5,A1-5,0)
    • good
    • 0
この回答へのお礼

大変お礼が遅れまして申し訳ございませんでした。
やっと環境も落ち着き皆様からの回答参考に試してみました。
私の質問内容が説明不足でした。本当に申し訳ございません。
もう一度分かりやすいように質問しなおしたいと思います。
回答ありがとうございました。

お礼日時:2001/11/19 11:02

普通残業時間の合計は


  =SUMIF(残業時間の範囲,"<=5",残業時間の範囲)
     +COUNTIF(残業時間の範囲,">5")*5

深夜残業時間の合計は
  =SUMIF(残業時間の範囲,">=5",残業時間の範囲)
     -COUNTIF(残業時間の範囲,">=5")*5

でどうでしょうか?
普通残業時間が確実に5以下だと仮定していますが・・・
    • good
    • 0
この回答へのお礼

大変お礼が遅れまして申し訳ございませんでした。
やっと環境も落ち着き皆様からの回答参考に試してみました。
pesoさんの回答は私の技量では解読不能でした。
何度やってみてもエラーが出てしまいまして、あまり詳しくないので数式を入力の際に例えば「、」の記号にしても「"」の記号にしても入力要領があるのでしょうか?
本当にありがとうございました。
もう一度質問しなおしてみたいと思います。

お礼日時:2001/11/19 10:52

IFを使ってデータを分けてやったらどうでしょうか。


A1のセルに合計の残業時間が入っていて
B1に通常残業C1に深夜残業の時間を入れる場合です。

B1のセルに=IF(A1>5,5,A1)
C1のセルに=IF(A1>5,A1-5,0)
このような式を入れるとよいと思います。

式の説明は次の通りです。

B1はもしA1のセルが5以上ならB1に5を入れなさい。
違うのであればA1と同じ数値を入れなさい

C1はもしA1のセルが5以上ならA1から5を引いた数値を入れなさい
違うのであればゼロを入れなさい。

いかがなものでしょうか。
    • good
    • 0
この回答へのお礼

大変お礼が遅れまして申し訳ございませんでした。
やっと環境も落ち着き皆様からの回答参考に試してみました。
tarankoさんの回答も前の人と同様でした。
私の質問内容が分かりずらかったことを深くお詫びいたします。
本当にありがとうございました。

お礼日時:2001/11/19 10:45

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aと関連する良く見られている質問

QExcel関数での勤務時間並びに残業、深夜残業時間の計算方法

[A1]出社時間 9:00
[B1]退社時間 23:45
の値から
かなりややこしい条件があるのですが。。。
昼休憩が1時間
勤務時間が8時間で15分の休憩(9:00出社の場合18:00~18:15)
勤務時間が10時間で15分の休憩(9:00出社の場合20:15~20:30)
休憩時間は結果から引いている
出社時間には変動があります。
出社時間にかかわらず夜間作業は22:00以降となってます(退社時間が22:15に0.25)

これらの条件で
[C1]総稼動時間 13.25
[D1]時間外 5.25
[E1]夜間作業 1.75
を出せる関数を教えていただきたいのです。出来れば出社、退社に時間が入ってない場合はそれぞれ空白になるようにしていただきたいです。
ややこしいことを質問していますが、よろしくお願いします。

Aベストアンサー

●「勤務時間が8時間で」を、「昼休憩を除く勤務時間が 8 時間に達した時点で」という
 意味に捉えました。
●「総稼動時間」にも休憩は含まないと解釈しました。
●「総稼動時間」のうち 8 時間を超えた部分を「時間外」とするものと解釈しました。
●「夜間作業」も「総稼動時間」の内数(うちすう)であると解釈しました。

添付図に記入している数式は、下のとおりです。

なお ROUND 関数を適用しているのは、「オートフィルや数式で作製された時刻データを扱う場合などに小数点第 14 位、15 位あたりに発生するケースがある計算誤差」を補正するためです。小数点以下に 7 桁を残せば、0:01 単位で時刻を記録して日給 100,000 円であっても、誤差 1 円未満になると考えられます。

C3 =(b3-a3)/"1:00"
D3 12:00 など
E3 =min($B3,d3+"1:00")
F3 =($C3>1+8)*($A3+"1:00"+"8:00")
G3 =min($B3,($C3>1+8)*($A3+"1:00"+"8:15"))
H3 =($C3>1+0.25+10)*($A3+"1:00"+"0:15"+"10:00")
I3 =min($B3,($C3>1+0.25+10)*($A3+"1:00"+"0:15"+"10:15"))
J3 =round(c3-sum(e3-d3,g3-f3,i3-h3)/"1:00",7)
K3 =max(0,j3-8)
L3 =round(max(0,b3-"22:00")/"1:00",7)

●「24:00 以後の時刻を表示する可能性のある列」のセルの書式は、「h:mm」ではなく、
 「[h]:mm」を設定してください。
 「セルの書式設定 > 表示形式タブ > ユーザー定義 > 種類ボックス」に書式記号を
 入力します。
●退社が翌日になったときは、午前 1 時であれば 25:00 というふうに記入してください。
●ゼロであるセル(「0.00」、「0:00」)を空白(のような見かけ)にするには、
 「ゼロ以下の値のみを非表示にするセルの書式」として、「0.00;;」、「h:mm;;」、
 などをセル範囲に設定しておきます。
 操作の方法は、「退社時間」の書式と同じです。
● J 列などに数式を記入した瞬間に、そのセルの書式が勝手に時刻になってしまう
 ことがありますが、気にせず「0.00;;」などを設定すれば、正常な値が表示されます。

●「勤務時間が8時間で」を、「昼休憩を除く勤務時間が 8 時間に達した時点で」という
 意味に捉えました。
●「総稼動時間」にも休憩は含まないと解釈しました。
●「総稼動時間」のうち 8 時間を超えた部分を「時間外」とするものと解釈しました。
●「夜間作業」も「総稼動時間」の内数(うちすう)であると解釈しました。

添付図に記入している数式は、下のとおりです。

なお ROUND 関数を適用しているのは、「オートフィルや数式で作製された時刻データを扱う場合などに小数点第 14 位、15 位あたりに...続きを読む

Qエクセルでそれぞれ1000倍して合計する配列?式はありますか?

No.652904 「質問:エクセルは少数点以下の計算が苦手? 」でわかったことから、小数の計算(合計)を小数ではなくして行おうと思います。
A1セルからA20セルに入力された数値(小数または整数)をそれぞれ1000倍してA21セルに出したいのです。
それぞれに1000倍する作業列を作って合計すればできるのはわかりますが、できれば作業列なしでやりたいのです。
多分「配列計算」だと思うのですが、「配列」は今までやったことがないのでわかりません。
どのような計算式になるのでしょうか?

Aベストアンサー

こんにちは。maruru01です。

配列数式でなくても、

=SUMPRODUCT(A1:A20*1000)

で出来ますが。

QExcel VBA フォルダ内に複数のcsvがあり、それぞれの3行目以降を一つのExcelに集約でき

Excel VBA
フォルダ内に複数のcsvがあり、それぞれの3行目以降を一つのExcelに集約できるツールが欲しいです。

よろしくお願いします。マクロ初心者です。

①複数のcsvがフォルダ内にあり。
②ExcelのA列に、取り込むcsvのファイル名をコピー。
③csvの3行目以降を全部、D列以降にコピー(最大50列)
※csvに7行目まで、データがある場合、A1〜5セルまでは同じファイル名が表示されます。


④ ③をファイル数分、繰り返す。
⑤Excelにコピーが終わった後、D列に、あいうえお、または、かきくけこ、という文字が入っていた場合は、行ごとの削除を行う。

よろしくお願いします。

Aベストアンサー

こんばんは!

取り込むデータはcsvファイルのSheet1(シート見出しの一番左側)シートにあり、
表示するのはコード記載ブックの「Sheet1」とします。

標準モジュールです。

Sub Sample1()
Dim i As Long, cnt As Long, lastRow As Long, lastCol As Long
Dim myPath As String, fN As String, myRng As Range
Dim wB As Workbook, ws1 As Worksheet, wS2 As Worksheet
Application.ScreenUpdating = False
Set ws1 = ThisWorkbook.Worksheets("Sheet1")
ws1.Cells.Clear
myPath = "保存場所のパス" & "/\"
fN = Dir(myPath & "*.csv")
Do Until fN = ""
Workbooks.Open myPath & fN
Set wB = ActiveWorkbook
Set wS2 = wB.Worksheets(1)
lastRow = wS2.Cells(Rows.Count, "A").End(xlUp).Row
If lastRow > 2 Then
For i = 3 To lastRow
lastCol = wS2.Cells(i, Columns.Count).End(xlToLeft).Column
cnt = cnt + 1
ws1.Cells(cnt, "A") = Left(fN, InStr(fN, ".") - 1)
ws1.Cells(cnt, "D").Resize(, lastCol).Value = wS2.Cells(i, "A").Resize(, lastCol).Value
Next i
End If
fN = Dir()
wB.Close savechanges:=False
Loop
'▼行削除処理
For i = 1 To ws1.Cells(Rows.Count, "A").End(xlUp).Row
If InStr(ws1.Cells(i, "D"), "あいうえお") > 0 Or InStr(ws1.Cells(i, "D"), "かきくけこ") > 0 Then
If myRng Is Nothing Then
Set myRng = ws1.Cells(i, "D")
Else
Set myRng = Union(myRng, ws1.Cells(i, "D"))
End If
End If
Next i
If Not myRng Is Nothing Then
myRng.EntireRow.Delete
End If
Application.ScreenUpdating = True
MsgBox "完了"
End Sub

※ コード内の「保存場所のパス」の部分は
実際のパスに書き換えてください。m(_ _)m

こんばんは!

取り込むデータはcsvファイルのSheet1(シート見出しの一番左側)シートにあり、
表示するのはコード記載ブックの「Sheet1」とします。

標準モジュールです。

Sub Sample1()
Dim i As Long, cnt As Long, lastRow As Long, lastCol As Long
Dim myPath As String, fN As String, myRng As Range
Dim wB As Workbook, ws1 As Worksheet, wS2 As Worksheet
Application.ScreenUpdating = False
Set ws1 = ThisWorkbook.Worksheets("Sheet1")
ws1.Cells.Cle...続きを読む

Qエクセル セルをそれぞれ分ける方法

一つのセルに、
4154あいうえお商事
4654かきくけこ建設
53123さしすせそ(株)
3234たちつてと有限会社
8746なにぬねの
6546はひふへほ商事
8674まみむめも電気
864やゆよ商事
554らりるれろ板金
と入力されているのを
     ↓
4154  あいうえお商事
4654  かきくけこ建設
53123  さしすせそ(株)
3234  たちつてと有限会社
8746  なにぬねの
6546  はひふへほ商事
8674  まみむめも電気
864  やゆよ商事
554  らりるれろ板金
二つのセルにそれぞれ分ける方法を教えてください。

Aベストアンサー

#4のmaronです
◆こちらの式のほうがいいかも
B1=LOOKUP(10^10,LEFT(A1,ROW($1:$10))*1)

★ROW($1:$10)
$1:$ の後の数字は、前の数字の最大桁数より大きい数字、ここでは「10」にしました

Qエクセルでそれぞれの合計を纏めたい

以下のようなデータ(膨大です)があるのですが、

9月1日 A(商品名)XA(単価)YA(売上数)ZA(単価×売上数)
    B     XB    YB     ZB
    C     XC    YC     ZC
 …
9月2日 A     XA    YA     ZA
    B     XB    YB     ZB
    C     XC    YC     ZC    
 …

表にするとかえって分かりにくいですが、要するに商品が400種類くらいあって、9月1日~30日の間にそれぞれ売上があったというわけです。
その400種類の商品の9月分のそれぞれの売上個数の合計と、それぞれの売上金額の合計を算出したいのです。つまり

9月
A  XA  YA合計  ZA合計
B  XB  YB合計  ZB合計
C  XC  YC合計  ZC合計

というように纏まった表を作りたいのです。
説明が下手で本当に恐縮ですが、エクセルで私の望むような計算はできますでしょうか?

Aベストアンサー

ピボットテーブルでやれそうな気がします。

ピボットテーブルの作成方法は参考URLを見てください。
行のフィールドに品番、単価。
列のフィールドは何もなし。
データアイテムに売上数、売上金額。
とすればよいのでは。

参考URL:http://kokoro.kir.jp/excel/pivottable.html


このカテゴリの人気Q&Aランキング

おすすめ情報