たぶんIF関数になると思うのですが…


Sheet1の
A列に社員コード
B列に社員名
があります。
C列~N列には4月から3月まで各月の残業時間数が入っています。

このデータから各月45時間以上残業したものをピックアップしてSheet2に載せたいのですが
どのように計算してあげたらよいのでしょうか?
(社員コードと社員名も対象者は乗せます)

オートフィルタで4月、5月、それぞれ行っても良いのですが加工に非常に手間がかかってしまって…

お手数ですが教えてください。
よろしくお願い致します。

回答の条件
  • 1人2回まで
  • 登録:2008/11/13 09:56:39
  • 終了:2008/11/15 11:23:38

ベストアンサー

id:Gay_Yahng No.6

Gay_Yahng回答回数724ベストアンサー獲得回数262008/11/13 14:56:19

ポイント21pt

それでは

O2に =if(sum(C2:N2)=0,"","残業45時間超過")

として

O2を選んで必要な行までコピー

そして、0列をフィルタで空白以外を表示する設定にする。

 

というのはどうでしょう?

id:kanachan

なるほどです!

出来ました。

ありがとうございます

2008/11/13 16:43:50

その他の回答(6件)

id:ekiryu No.1

ekiryu回答回数37ベストアンサー獲得回数02008/11/13 10:33:20

ポイント10pt

C列~N列のMAXを出すO列を追加して、

O列をオートフィルタで45時間以上のデータを表示で抽出できると思いますが。

視覚的に45時間以上の月を確認したければ、条件付き書式設定で、

45時間以上のデータに色を付けるとか。

id:kanachan

ありがとうございます。

でもどうしても45時間以上の人たちを

別シートに出したいんですね。

教えていただいた方法は簡単でよいのですが…

申し訳ありません。

2008/11/13 11:04:01
id:SALINGER No.2

SALINGER回答回数3454ベストアンサー獲得回数9692008/11/13 11:17:24

ポイント15pt

方法として最初に思いつくのは、

1 O列に45時間以上の月がある行にしるしをつける。

2 オートフィルタでそれ以外を非表示にする。

3 シート2にコピーする。

4 45時間以下のデータを非表示にする。

という手順を踏めばできそうですが、この方法はやめて関数で実現する違う方法を紹介します。


1 まず、作業列となる列をSheet1のO列として

 O1セルの値を0

 O2セルの数式を

=IF(AND(C2<45,D2<45,E2<45,F2<45,G2<45,H2<45,I2<45,J2<45,K2<45,L2<45,M2<45,N2<45),O1,O1+1)

 にして、下にコピー


2 Shee1の1行目の見出しをSheet2の1行目の見出しとしてそのままコピー


3 Sheet2のA2セルの数式を

=IF(ISNA(INDEX(Sheet1!A:A,MATCH(ROW()-1,Sheet1!$O:$O,0))),"",INDEX(Sheet1!A:A,MATCH(ROW()-1,Sheet1!$O:$O,0)))

 にして、B2セルにもコピー


4 Sheet2のC2セルの数式を

=IF(ISNA(IF(INDEX(Sheet1!C:C,MATCH(ROW()-1,Sheet1!$O:$O,0))>=45,INDEX(Sheet1!C:C,MATCH(ROW()-1,Sheet1!$O:$O,0)),"")),"",IF(INDEX(Sheet1!C:C,MATCH(ROW()-1,Sheet1!$O:$O,0))>=45,INDEX(Sheet1!C:C,MATCH(ROW()-1,Sheet1!$O:$O,0)),""))

 にして、N2セルまでコピー


5 3と4で作ったA2:N2セルの数式を下にコピー


これで関数だけでできます。

id:kanachan

ありがとうございます。

でも残念ながら最初の式でエラーになってしまって…

よく式も確認したのですがなぜエラーになったか不明です。。。

申し訳ありません。

2008/11/13 13:53:57
id:Gay_Yahng No.3

Gay_Yahng回答回数724ベストアンサー獲得回数262008/11/13 11:30:34

ポイント20pt

ならば、ifだけでいいのでは?

sheet2に

A2に =Sheet1!A2

B2に =Sheet1!B2

C2に =IF(Sheet1!C2>=45,Sheet1!C2,"")

として、

C2を選んでN2までコピー

C2からN2を選んで必要な行までコピー

これでいいのではないかと。

id:kanachan

ありがとうございます。

非該当の人は削除する手間があるものの何とか出来ました。

ありがとうございます

2008/11/13 13:53:52
id:pegasus_A No.4

pegasus_A回答回数4ベストアンサー獲得回数02008/11/13 12:30:42

ポイント15pt

とりあえず、sheet1をコピーしてsheet2を作って、IF関数でsheet1の同一セルを参照して、

45時間以上ならそのまま表示、45時間未満なら0(または×等)で表示させるのはどうですか?

IF関数がどっさり入りますが、1つ作ってコピー&ペーストなので、さほど面倒でもないと思いますが…

id:kanachan

ありがとうございます。

私の説明不足申し訳ありません。

該当者を○×とすると何時間残業したカワからなくなるので、時間数も出してあげたいんですね。

せっかくご回答いただいていて申し訳ありません。

2008/11/13 13:55:47
id:Mook No.5

Mook回答回数1312ベストアンサー獲得回数3912008/11/13 12:45:11

ポイント19pt

使用方法は

(1)Alt+F11をおす。

(2)開いたウィンドウで、挿入⇒標準モジュールを押す。

(3)表示されたエリアに下記をコピーする。

(4)EXCELに戻ってAlt+F8を押す。

(5)名前(今回はOverWorkList)を選択してOKを押す。

以上です。


上記を実行すると、45時間以上、80時間以上、100時間以上の三つのシートができ、

O列に、オーバーした月数が表示されます。

'--------------------------------------------------------------------
Sub OverWorkList()
'--------------------------------------------------------------------
    Dim srcWS As Worksheet
    Set srcWS = ActiveSheet
    
    makeOWL srcWS, 45#
    makeOWL srcWS, 80#
    makeOWL srcWS, 100#
End Sub

'--------------------------------------------------------------------
Sub makeOWL(srcWS As Worksheet, trHour As Double)
'--------------------------------------------------------------------
    Dim dstWSName As String
    dstWSName = "残業_" & CStr(trHour)
        
    Dim dstWS As Worksheet
    On Error Resume Next
    Set dstWS = Worksheets(dstWSName)
    On Error GoTo 0
    
    If Not dstWS Is Nothing Then
        If MsgBox("[" & dstWSName & "]シートが存在します。再作成しますか?", vbYesNo, "確認") = vbNo Then
            Exit Sub
        End If
        Application.DisplayAlerts = False
        Worksheets(dstWSName).Delete
        Application.DisplayAlerts = True
    End If
    
    srcWS.Copy after:=Worksheets(Worksheets.Count)
    Set dstWS = ActiveSheet
    dstWS.name = dstWSName
    
    
    Dim lastRow As Long
    lastRow = dstWS.Range("A" & Rows.Count).End(xlUp).Row
    
    Dim r As Range
    For Each r In Range("B2:N" & lastRow)
        If r.Value < thHour Then
            r.Value = ""
        End If
    Next
    
    dstWS.Range("N1").Resize(lastRow, 1).Copy Destination:=dstWS.Range("O1")
    dstWS.Range("O1").Value = "回数"
    
    Dim i As Long
    For i = 2 To lastRow
        dstWS.Cells(i, "O").Formula = "=COUNTA(C" & i & ":N" & i & ")"
    Next
End Sub
id:kanachan

仰るとおりに作業して何とかできたのですが。。。

45時間以上をピックアップすることなく終わってしまいました。

新しく出来たシートには45時間以下もすべて出てきまして。

なぜ出来ないのかわかりません><

あと最終的な(?)合計がデータの個数で計算されていまして…

これも解らないです、、、

2008/11/13 13:56:46
id:Gay_Yahng No.6

Gay_Yahng回答回数724ベストアンサー獲得回数262008/11/13 14:56:19ここでベストアンサー

ポイント21pt

それでは

O2に =if(sum(C2:N2)=0,"","残業45時間超過")

として

O2を選んで必要な行までコピー

そして、0列をフィルタで空白以外を表示する設定にする。

 

というのはどうでしょう?

id:kanachan

なるほどです!

出来ました。

ありがとうございます

2008/11/13 16:43:50
id:Mook No.7

Mook回答回数1312ベストアンサー獲得回数3912008/11/13 15:22:59

ポイント20pt

わざわざシートを分ける意味が無い回答ですが、

先に回答したものを1シートで集計するように変更しました。

(数式だけでもできる内容ですが。)


最終的に「どのような結果を得たい(そのデータをどうしたい)」という部分がもっと明確になれば

また違った回答があると思うのですが、1シートで3つのケースを分類(色で)し、集計しています。


時間の入力は数値で入力していることを期待していますが、時間で入力していたら変更が必要です。

(if の部分の数値(45#、80#、100#)をそれぞれ(1.875、3.3333、4.1666)に変更)

Option Explicit

'--------------------------------------------------------------------
Sub OverWorkList()
'--------------------------------------------------------------------
    Const dstWSName = "残業集計"
    
    Dim srcWS As Worksheet
    Set srcWS = ActiveSheet
    
    Dim dstWS As Worksheet
    On Error Resume Next
    Set dstWS = Worksheets(dstWSName)
    On Error GoTo 0
    
    If Not dstWS Is Nothing Then
        If MsgBox("[" & dstWSName & "]シートが存在します。再作成しますか?", vbYesNo, "確認") = vbNo Then
            Exit Sub
        End If
        Application.DisplayAlerts = False
        Worksheets(dstWSName).Delete
        Application.DisplayAlerts = True
    End If
    
    srcWS.Copy after:=Worksheets(Worksheets.Count)
    Set dstWS = ActiveSheet
    dstWS.name = dstWSName
    
    Dim lastRow As Long
    lastRow = dstWS.Range("A" & Rows.Count).End(xlUp).Row
    
    dstWS.Range("N1").Resize(lastRow, 1).Copy Destination:=dstWS.Range("O1")
    dstWS.Range("N1").Resize(lastRow, 1).Copy Destination:=dstWS.Range("P1")
    dstWS.Range("N1").Resize(lastRow, 1).Copy Destination:=dstWS.Range("Q1")
    dstWS.Range("O1").Value = "45~80"
    dstWS.Range("P1").Value = "80~100"
    dstWS.Range("Q1").Value = "100~"
    
    Application.Calculation = xlCalculationManual
    Dim i As Long
    For i = 2 To lastRow
        dstWS.Cells(i, "Q").Formula = "=COUNTIF(C" & i & ":N" & i & ","">=100"")"
        dstWS.Cells(i, "P").Formula = "=COUNTIF(C" & i & ":N" & i & ","">=80"")-Q" & i
        dstWS.Cells(i, "O").Formula = "=COUNTIF(C" & i & ":N" & i & ","">=45"")-P" & i & "-Q" & i
    Next

    Dim r As Range
    For Each r In Range("B2:N" & lastRow)
        If r.Value < 45# Then
            r.Font.ColorIndex = 16
        ElseIf r.Value < 80# Then
            r.Interior.ColorIndex = 19
        ElseIf r.Value < 100# Then
            r.Interior.ColorIndex = 44
        Else
            r.Interior.ColorIndex = 26
        End If
    Next
    Application.Calculation = xlCalculationAutomatic
End Sub
id:kanachan

ありがとうございます。

ただ、、、何故かVBA入れた途端データが倍以上の重さになりまして…見事にフリーズしてしまいました…

せっかく教えていただいたコードなので何とか使用したいと思ったのですが、結果的に関数になってしまいました。

もう少し軽いデータでためさせていただきます。

返ってすみません。

ありがとうございます。

2008/11/13 16:50:45
  • id:Mook
    やっぱり VBA はいやなんですよね?

    条件付書式で値が45以上を色付けするだけでは駄目ですか?
  • id:Gay_Yahng
    sheet2の書式はどんなのがいいんでしょう?
    A列に4月の45時間残業オーバーした人の一覧、B列に5月・・・・って感じでしょうか?
  • id:kanachan
    お二人の仰るとおりです。
    VBAが解れば早いんだろうなぁ…と思いつつ毎回理解できず…出来れば避けたいVBA。
    よく式も送ってくださるんですがVBAはその式をどこにどう貼り付けるかがわからなくて。。。

    また、Sheet2のデータは
    A列は対象者のコード
    B列は対象者の氏名
    C列は4月の45時間以上残業時間。
    D列は5月の45時間以上残業時間


    というふうになります。
    条件付書式は最初からやりたいところなんですが…この45時間以上をピックしたあとにそこから更に80時間以上と100時間以上を抽出するときに使う予定なので…

    45時間以上のときはSheet2に出したいです。
    よろしくお願い致します。
  • id:Mook
    前の質問で回答した際に、コメントで VBA の使用の仕方を説明したんですができなかったんでしょうか。
    (ネットで使い方を検索すればやり方は見つかると思いますが。)

    ちなみにVBAを記述したものは「式」とは言わず、「コード」といいます。
    VBAでよければ、回答しますが・・・。
  • id:Mook
    一応、VBAの実行手順とあわせて回答しました。
    実行すると45時間以上、80時間以上、100時間以上の3つのシートを作成します。

    VBAが実行できない場合は、EXCELの
    「ツール」⇒「オプション」⇒「セキュリティ」タブ⇒「マクロのセキュリティ」
    を確認してみてください。

    もしここが「高」以上だったら、「中」にしてEXCELを再起動し起動時にセキュリティ警告
    が表示されたら「マクロを有効にする」を選択してください。
  • id:Mook
    シートのコピーをベースにしているので対象者だけをリストしたければ、
    オートフィルタでO列が0のものを表示して、全削除すれば対象者のリストになると思います。
  • id:kanachan
    式といわず、コードと言うんですね!知りませんでした。
    以前教えていただいたものも試してはいるのですが失敗ばかりで…
    マクロは有効になっているので問題ないはずなんですが。。。

    たしかにシート3つは出来ました。
    この方法なら楽だなぁ…たぶん。とは思ったのですが、45時間以上の中に80時間以上の人も100時間以上の人も出てくるのではないかと思います。
    シート増やすのでしたら45時間以上のみピックアップするほうがデータも重くならないような…???
    と思うのは間違いでしょうか?
  • id:SALINGER
    O(アルファベット)を0(数字)で入力していませんか?
    使われているのは簡単な数式だけなので、コピーして貼り付けるだけです。
    私の環境ではちゃんと機能していますので、式に問題は無いはずです。
  • id:kanachan
    人数としては多くないはずなのですが既にデータが3MBと表示されていて重いと言えば重いんですね…
    たぶんピボットテーブルやったり平均値出したりしているからだと思うのですが。
    ですから、今回の45時間以上、80時間以上、100時間以上も1つのシートで済ませたくて…

    いつもながらお手数ばかりですみません。
  • id:Mook
    時間のデータは整数を想定したのですが、時間形式「hh:mm」のような形で入っていたでしょうか。
    であれば下から11行くらいのところを

    For Each r In Range("B2:N" & lastRow)

    の前に一行追加して

    thHour = thHour / 24#
    For Each r In Range("B2:N" & lastRow)

    としてください。




    >シート増やすのでしたら45時間以上のみピックアップするほうがデータも重くならないような…???
    >と思うのは間違いでしょうか?
    これは、その通りだと思いますが、1枚のシートはそんなにデータがあるのですか?
    それは仕様(やりたいことの定義)しだいです。

    そのようにしたければコードの修正をしますがが、そのようにしたいのですか?
    あと、該当しない人の行は削除したほうが良いですか?
  • id:SALINGER
    ここです。
    thHour → trHour
  • id:Mook
    あっか、本当だ。ミスですね。
    SALINGER さん、ありがとうございます。

    (やっぱり、Option Explicit は必要だな・・・・。)

    時間入力だった場合は、
       trHour= trHour/ 24#
    を追加してください。

    O列は時間の合計ではなく、何ヶ月オーバーの月があるかの集計です。
    違うものを出したかったら式を変更すれば、好きなように出せます。
  • id:pegasus_A
    すいません、ちょっと簡単に書きすぎました。
    3で回答された方と同じことを書いたつもりで、すでに回答もあがってたのですね。
    ちょっと確認です。
    非該当の人とは、どの月も45時間を越えなかった人ですね?
    これを削除する理由は何でしょう?
    印刷での一覧性を考えてなら、6ヶ月の合計の列を作って、フィルタでの対応で十分な気もしますが…?
  • id:Mook
    >ただ、、、何故かVBA入れた途端データが倍以上の重さになりまして…見事にフリーズしてしまいました…
    倍以上の重さってどういう意味でしょうか。ファイルサイズですか?

    お使いのPCのスペック(CPUのクロックとメモリサイズ)はどのようなものをお使いでしょうか。
    また、データ量(行数)はどのくらいありますか?

    環境やデータ量によってはフリーズしたのではなく、処理をしている可能性もあります。
    (待てば結果が出る。)

    まぁ目的を達成したのであれば、VBAに固執する必要もありませんので、関数をお使いください。
  • id:airplant
    トラックバックに載せておきましたが、フィルタオプションの設定で要望のことは簡単にできそうです。
  • id:Gay_Yahng
    知らなかった。

  • id:SALINGER
    いろいろな方法が提示されて質問者様が混乱したかもしれませんが、個々の方法には向き不向きがあるので
    今後Excelで一般的な問題を解決するときにとるべき方法としてご参考までにまとめておきます。

    1 オートフィルターや書式設定、条件付書式で実現。
     データには手を加えず見かけを変えて対処します(フィルターで条件に合わない行を非表示にするなど)。デメリットはシートの数だけ同じ手順を踏まないといけません。

    2 数式で関数を使って実現
     データ個々から新しいデータを作成します。一度数式を作成してしまえば、個々のセルの変更がすぐに反映されます。ただし、データが大きくなるとレスポンスが悪くなります。

    3 VBAでコードを書いて実現。
     データをまとめて新しいデータを作成します。たくさんのシートやブックがあっても対応することができます。それなりに知識が必要。

    問題によりとることができない方法もありますが、大雑把にまとめると
    1回できればいい。それほど複雑ではない。 → 1
    セルの値の変化に直ぐに対応させたい。 → 2
    複数あるシートを凡庸的に処理させたい。複雑な処理を簡単に行いたい。 →3

    大雑把に分類しましたが、ユーザー定義関数を作るとか個々の方法を組み合わせる方法もあるのでこの限りではありませんが、今後の参考になればと思います。
  • id:Gay_Yahng
    今回の場合は関数だけで表示するSALINGERさんの方法がいいんでしょうね。
    私の2回目の回答のフィルタはsheet1を更新したらフィルタをかけなおさないといけません。

この質問への反応(ブックマークコメント)

トラックバック

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません