人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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

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

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

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

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

●質問者: kanachan
●カテゴリ:コンピュータ
✍キーワード:3月 4月 5月 コード データ
○ 状態 :終了
└ 回答数 : 7/7件

▽最新の回答へ

1 ● ekiryu
●10ポイント

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

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

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

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

◎質問者からの返答

ありがとうございます。

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

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

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

申し訳ありません。


2 ● SALINGER
●15ポイント

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

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セルの数式を下にコピー


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

◎質問者からの返答

ありがとうございます。

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

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

申し訳ありません。


3 ● Gay_Yahng
●20ポイント

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

sheet2に

A2に =Sheet1!A2

B2に =Sheet1!B2

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

として、

C2を選んでN2までコピー

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

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

◎質問者からの返答

ありがとうございます。

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

ありがとうございます


4 ● pegasus_A
●15ポイント

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

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

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

◎質問者からの返答

ありがとうございます。

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

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

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


5 ● Mook
●19ポイント

使用方法は

(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
◎質問者からの返答

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

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

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

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

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

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


1-5件表示/7件
4.前の5件|次5件6.
関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ