痔になりやすい生活習慣とは?

ある一定時間を超えた場合の超えた時間のみを合計する方法をご存知でしたら教えてください。
例えば、1日8時間労働を8.0として、その8.0を越える日の8.5だったら8.5-8.0=0.5の0.5の部分の月の合計=残業分の合計を出したいと考えています。
A列に氏名があり、B列~AF列まで1日~31日の労働時間が入力されているdateが40人分あります。
AG列に式を、入力するとすると
8.0を超える時間の合計を出すには=SUMIF(A1:AF1,">8.0",A1:AF1)でもとめられますが、
8.5と入力されていた場合の0.5部分の月の合計を算出するにはどうすればよいでしょうか。
宜しくお願い致します。

A 回答 (3件)

いくつかあると思いますが、


 
=SUMPRODUCT((A1:AF1>8)*(A1:AF1-8))

これでもできそうです。
 
 
    • good
    • 2
この回答へのお礼

ありがとうございました。
VBAのR1C1方式で入力させたいと考えていましたので
配列有りの計算方法ではなく、わかりやすいスマートな関数が希望でした。
SUMPRODUCT関数は何回か使用したことがあったのですが、色々な使い方が出来るのですね。
改めて勉強になりました。

お礼日時:2010/07/01 22:51

こんばんは!


一例です。

2行目からデータがあるとして
AF2セルに
=SUMIF(B2:AF2,">8",B2:AF2)-COUNTIF(B2:AF2,">8")*8

としてみてはどうでしょうか?

以上、参考になれば良いのですが
他に良い方法があれば読み流してくださいね。m(__)m
    • good
    • 0

=SUMIF(A1:AF1,">8.0")-COUNTIF(A1:AF1,">8.0")*8


または
=SUM(MAX(A1:AF1-8,0))
としてCtrl+Shift+Enterで配列数式にする。
または
=SUMPRODUCT(MAX(A1:AF1-8,0))
    • good
    • 0

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

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

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

Q残業時間の計算をするにはどのような関数あるいは計算式を設定すればいいの

残業時間の計算をするにはどのような関数あるいは計算式を設定すればいいのですか?
私、エクセルについてはまったくの初心者です。よろしくお願いします。

(例)
       A      B
 
1行目 出勤時間  9:00

2行目 退社時間  19:30

3行目 残業時間  2:00 ←ここの計算式を教えていただきたいのです。

             ※ ただし、定時の終了時間は17:30で、拘束時間は休憩時間を含めて
               8:30とした場合でお願いします。

Aベストアンサー

勤務時間=退社時間-出勤時間
残業時間=勤務時間-8:30
で数式を組み立てると

残業時間 B3=IF(OR(B1>0,B2>B1),MAX(B2-B1-"8:30",0),"")

時間を30分単位で切り捨てにする場合
残業時間 B3=IF(OR(B1>0,B2>B1),FLOOR(MAX(B2-B1-"8:30",0),"0:30"*1),"")

Q《Excel》ある数値を超えた時と超えない時の計算式を入れたい

上のセルから順番に105を足した数値を入力したいのですが
セルの数値が260を超える時はそこから260引いた値を表示させたいです。
(基本的に261以上になることはない)

現在は
H2+SUM(105)−(260)
もしくは
H2+SUM(105)
という感じでセルによって計算式をコピぺしながら使っているのですが、
この計算式をまとめることはできますか?

Excelはあまり詳しくないので、一番簡単な形で教えていただきたいです。
宜しくお願い致します。

Aベストアンサー

またまたお邪魔します。

No.3の
>もうひとつ、I2のセルに入っている数字を基準に、順番に1づつ足した数字を表示させたいのですが、13を超えた場合はまた1に戻りたいのですが、(数字の表示は1から13まで)

の件について、
No.3の数式そのままでMOD関数の「除数」を変えるだけで対応できるはずです。

=IF(MOD(I$2+ROW(A1),13),MOD(I$2+ROW(A1),13),13)
としてフィルハンドルで下へコピーしてみてください。

※ ROW(A1)の意味がよくわからないので・・・
試しにどこか使っていないセルに
=ROW(A1)
としてみてください。
「1」が表示されるはずです。
(列番号はA列でなくてもZ列でもどこの列番号でも構いません、単純にA列にしているだけです)

これをフィルハンドルで下へコピーしてみてください。
数式バー内で確認してもらうと
=ROW(A2) → =ROW(A3)・・・
となり、2・3・4・・・と順に表示されます。
詳しくは「ROW関数」で調べてみてください。

この際ですので、COLUMN関数も同じ使い方(列方向へフィル&コピーした場合)
になりますので、
COLUMN関数も調べてみてはどうでしょうか?m(_ _)m

またまたお邪魔します。

No.3の
>もうひとつ、I2のセルに入っている数字を基準に、順番に1づつ足した数字を表示させたいのですが、13を超えた場合はまた1に戻りたいのですが、(数字の表示は1から13まで)

の件について、
No.3の数式そのままでMOD関数の「除数」を変えるだけで対応できるはずです。

=IF(MOD(I$2+ROW(A1),13),MOD(I$2+ROW(A1),13),13)
としてフィルハンドルで下へコピーしてみてください。

※ ROW(A1)の意味がよくわからないので・・・
試しにどこか使っていないセルに
=ROW(A1)
としてみ...続きを読む

Qエクセル2010 深夜残業時間の計算

出社・退社の時間から、残業、深夜残業など、
残業時間を算出する数式を作成していますがどうにもうまく出来ません。

残業となる時間の切り分けは、平日は以下のようになっています。、
 基本時間→8:30~17:15
 残業→5:00~8:30 と17:15~22:00
 深夜残業→22:00~翌朝5:00
 休憩時間→0:45(C1) 基本勤務時間→8:00(B1)

例えば平日、朝6:00出社→23:00退社であれば、
早朝残業が2:30、通常残業が4:45、計7:15です。深夜残業が1:00となります。

F欄に時間外、 G欄に深夜残業としています。
現状は時間外を出すのに  =SUM(E5-D5)-$B$1-$C$1-G5 と単純にしてます。(G5は手入力)
これを自動で入力できたらと考えています。
5:00前に出勤することはほとんどありませんが、22:00を超えることはあります。
ただし、24:00を超えると翌日計算になります。

過去欄を参考にしようにも理解できなく困っています。
http://oshiete.goo.ne.jp/qa/8147465.html
よろしくお願いいたします。

出社・退社の時間から、残業、深夜残業など、
残業時間を算出する数式を作成していますがどうにもうまく出来ません。

残業となる時間の切り分けは、平日は以下のようになっています。、
 基本時間→8:30~17:15
 残業→5:00~8:30 と17:15~22:00
 深夜残業→22:00~翌朝5:00
 休憩時間→0:45(C1) 基本勤務時間→8:00(B1)

例えば平日、朝6:00出社→23:00退社であれば、
早朝残業が2:30、通常残業が4:45、計7:15です。深夜残業が1:00となります。

F欄に時間外、 G欄に深夜残業としています。
現状...続きを読む

Aベストアンサー

深夜部分の残業時間を計算するなら、以下のような数式で早朝(5:00以前)部分と夜間(22:00以降)部分の深夜残業を足し算するのが簡単です。
(退出時間が翌日になる場合は25:00のような形式で入力した場合)。

=5/24-MIN(D5,5/24)+MAX(E5,22/24)-22/24

退出時間を「25:00」ではなく通常の「1:00」のように入力する場合は、以下の式で計算することができます。

=5/24-MIN(D5,5/24)+MAX((E5<D5)+E5,22/24)-22/24

ところで、深夜部分がメインの勤務がある場合、休憩時間などは考慮しなくてもよいのでしょうか?

Qエクセルの数値がある一定の数値を越えたらO.K.と表示したい。

エクセルで表を作っていたのですが、その最後にOKかNOか判断しなければいけない欄があるのです。
たとえば、1以上になったときにその横の欄にOKと表示させ、1以下の時にはNOと表示させるような関数とか命令はあるのでしょうか?。
マクロを使えば出来るのでしょうが当方なにぶんあんまりエクセルには詳しくないので出来るだけ簡単に出来る方法があれば教えてください。

Aベストアンサー

IF関数を使って簡単に実現できます。

例えばA5のセルの中身が100以上であればOKと表示しそれ以下であればNOと表示すると仮定しますと、

=IF(A5>=100,"OK","NO")

となります。 ダブルクオーテーションの中は表示したい文字列であれば何でも結構です。

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

Qエクセルで表示形式の時刻の「0:00」を表示しないようにするには?

エクセルで残業の計算をしています。
入社時間 退社時間 残業時間
のような感じでしているのですが、
残業時間が、0:00(つまり残業0)の場合は、
何も表示しない場合は、どのような関数を書けばよいでしょうか?

ちなみに残業時間のセルには、残業時間を自動的に表示させるために、
すでに「=IF((A1="")+(B1=""),"",B1-A1-"8:30")」という式が入っています。
この式にどういう式を付け加えればよいのでしょうか?

Aベストアンサー

ただ単に表示形式を変更すればいいです。現状で書式が時刻形式の
h:mmになってると思いますので、ユーザー定義でh:mm;;と後ろにセ
ミコロンを2つ追加して下さい。負の値と0の時は何も表示しないと
いう意味になります。

Qエクセルで勤怠管理表で休憩時間を計算させるには

エクセルで、社員の出勤時間の計算をしたいのですが、
休憩時間の控除の計算式がよくわからずに困っています。

パートさんが多いので、労働時間により休憩時間が異なり、下記のようにしています。
4時間30分未満=0分
4時間30分以上=30分
6時間以上=45分
8時間以上=60分

現在使用しているデータの時間表示は、出社「9:00」退社「17:00」延べ時間「8:00」というような、60進法の時分表示です。
これを、10進法表示にはしない方法で計算したいです。

現在の表では、
Aセル=出社時刻、Bセル=退社時刻、Cセル=述べ就労時間、Dセル=休憩時間、Eセル=勤務時間(←述べ就労時間-休憩時間)
にしているのですが、

Dセルの休憩時間が自動計算設定の仕方がわからず、述べ就労時間を見ながら手入力しています。(かなり手間なのと、間違いのもとなので)
これを自動計算できるようにしたいのですが・・

教えて頂けたら大変助かります。
宜しくお願いします。

Aベストアンサー

IF文で
=IF(C2<"4:30"*1,0,IF(C2<"6:00"*1,"0:30"*1,IF(C2<"8:00"*1,"0:45"*1,"1:00"*1)))

Q8時間以上だったら1時間マイナスする関数

勤務時間が8時間を越えると、1時間マイナスするという関数を作りたいのですがうまくいきません。
開始 終了 勤務時間
20:00 05:00 8:00

としたいと思っています。

どういう関数を使えば良いのでしょうか。

Aベストアンサー

20:00~29:00(翌日05:00)ということでしょうか?
B2が29:00と入力なら
=B2-A2-((B2-A2)>1/3)/24
解説:
(B2-A2)>1/3の部分が終了-開始と1/3日(24時間/3で8時間)の比較で
8時間を越えると1(TRUE)、8時間以内なら0(FALSE)となります。
時間からすると1は1日(24時間)を表すので24で割ってます。

B2が05:00と入力なら
=B2-A2+(B2<A2)-((B2-A2+(B2<A2))>1/3)/24
解説:
追加した(B2<A2)が時刻が逆転の判定で1日(24時間)を追加しています。

※29:00と表示したい場合は、セルの書式設定の表示形式をユーザー定義にして
[hh]:mm
としてください。24時間以上の時刻が表示できます。

Qエクセル出退勤表で時間外と深夜時間外の関数

エクセルで出退勤表を作りたいと思っています。
所定労働時間は10:00~18:30で休憩1時間入り、7:30です。
18:30~22:00までは時間外労働時間、22:00~翌5:00までは深夜時間外です。例えば出勤時間10:00、退勤時間23:00とすると、時間外は3:30で深夜時間外は1:00です。
セルの縦列には
A:日付(例3/11で表示)
B:曜日
C:出勤時間(例10:00で表示)
D:退勤時間(例23:30で表示)
E:所定労働時間(7:30で固定で休憩の1時間を含んでいない)
F:時間外労働時間(例3:00で表示)
G:深夜時間外(例2:30で表示)

Q1:カレンダーの暦通り、曜日をBセルに自動表示させる方法はありますか?
Q2:Fセルの18:30~22:00までの時間外労働時間を計算する関数を教えて下さい。Q3:Gセルの22:00~翌5:00までの深夜残業時間を計算する関数を教えて下さい。
Q4:出勤時間と退勤時間を入力すれば時間外と深夜時間外が表示されるようにしたいのですが可能ですか?
よろしくお願いします。

エクセルで出退勤表を作りたいと思っています。
所定労働時間は10:00~18:30で休憩1時間入り、7:30です。
18:30~22:00までは時間外労働時間、22:00~翌5:00までは深夜時間外です。例えば出勤時間10:00、退勤時間23:00とすると、時間外は3:30で深夜時間外は1:00です。
セルの縦列には
A:日付(例3/11で表示)
B:曜日
C:出勤時間(例10:00で表示)
D:退勤時間(例23:30で表示)
E:所定労働時間(7:30で固定で休憩の1時間を含んでいない)
F:時間外労働時間(例3:00で表示)
G:深夜時間外(例2:30で...続きを読む

Aベストアンサー

>Q1:カレンダーの暦通り、曜日をBセルに自動表示させる方法はありますか?
B2=TEXT(A2,"aaa")

>Q2:Fセルの18:30~22:00までの時間外労働時間を計算する関数を教えて下さい。
F2=IF(OR(C2="",D2=""),"",MAX(MIN(D2+(D2<C2),22/24)-C2-17/48,0))

>Q3:Gセルの22:00~翌5:00までの深夜残業時間を計算する関数を教えて下さい。
G2=IF(OR(C2="",D2=""),"",MAX(D2+(D2<C2),22/24)-22/24)

※D2+(D2<C2) C2=10:00で、D2=5:00の場合にD2=27:00とします。
※22/24="22:00"=22時のこと
※17/48=8.5/24="08:30"=8時間半(休憩時間を含む所定労働時間)のこと

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング