[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『表を縦と横に見て回数をカウント』（シマリス）
残業申請の有無の表を作ろうとしています。
"連絡簿"シートというのがあり、１ヶ月の残業時間と残業申請の有無を記入するようになっています。
A B C D E F G H I J 〜 BK BL BM BL 1 5月1日(火) 5月2日(水) 5月3日(木) 〜 5月30日(水) 5月31日(木) ←日付毎に結合 2 名前 種別 残業時間合計 申請書数 残業時間 申請書 残業時間 申請書 残業時間 申請書 〜残業時間 申請書 残業時間 申請書 3 田中 契約社員 24:05 7 1:00 有 0:00 無 2:00 無 1:45 有 4 鈴木 社員 34:30 12 0:00 無 2:00 無 1:00 有 3:00 無
これを元に、"月毎累計"のシートに「残業時間合計」と「申請書"無"で残業した回数」を一覧として出します。
"月毎累計"
A B C 1 名前 残業時間合計 申請無残業数 2 田中 24.05 5 3 鈴木 34:30 8
社員（メンバー）の数は30人ほどです。 "連絡簿"の「残業時間合計」と「申請書数」、"月毎累計"の「残業時間合計」は関数でできたのですが、 「申請書"無"で残業した回数」が計算できません。
マクロで、下記のようなものを書いてみました。
Sub 申請書無残業集計()
Dim i As Long Dim c As Long Dim sh As Worksheet Dim zt As Varian Dim r As Variant
Dim j As Long
Set sh = Worksheets("月毎累計")
With Sheets("連絡簿")
r = .Range("A3").End(xlToLeft).Column '最終列取得
z = .Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To z
zt = 0
For c = 5 To r
If .Cells(i, c).Value = "無" Then
If .Cells(i, c - 1).Value > "" Then
zt = zt + 1 '申請無し残業回数カウント
End If
End If
Next c
For j = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row
If .Cells(i, 1).Value = sh.Cells(j, 1).Value Then ' "連絡簿"の名前と"月毎累計"の名前が同じだったら
sh.Cells(j, 3).Value = zt
End If
Next j
Next i
End With
End Sub
これで何も書き出されないので、まずステップ実行してみると、
・"連絡簿"の最終列が取得されていない
・「申請無残業カウント」の変数 zt に何も格納されない
・ For j = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row
If .Cells(i, 1).Value = sh.Cells(j, 1).Value Then
sh.Cells(j, 3).Value = zt
End If
Next j
ここの部分がループはしているものの、.Cells(i, 1).Value = sh.Cells(j, 1).Value
が判定されていない
zt も 0 のまま
どこから修正すればよいでしょうか。 お手数ですがよろしくお願いいたします。
コードはよく読んでいないけど、とりあえず 変数宣言のデータ型を正しくすることと、全ての変数を記述することに加えて
r = .Cells(3, .Columns.Count).End(xlToLeft).Column '★最終列取得
z = .Range("A" & .Rows.Count).End(xlUp).Row '★Rows.COuntにもシート修飾
この２行を修正しよう。 コード全体は、今から読んでみる。
(ぶらっと)
数式で良ければ、SUMPRODUCT関数を使って =SUMPRODUCT((F4:BN4="無")*(E4:BM4>0)*(E4:BM4<=1))
(HANA)
コード全体ではなく、指摘した最終列の取得について。
間に空白セルがあった場合に備えて、行なら下から上に、列なら右から左に走査するということで ちゃんと配慮されている記述になっている・・・と思われるけど、たとえば今チェックしている３行目、 最初の人の行のケース。かならず最終の時間帯にも労働していて、最後の列に値があるならＯＫだけど 極端なケース、その人は、全く残業をしていなければ、Ｂ列の区分欄が「最終列」になる。 ということを、この方式でカバーしようとすると「全行」チェック？ それは非現実的だね。 なので、全く別の方式、シート上の使用済み領域（UsedRange）を調べる手もある。 だけど、混乱すると思うので、今回は、３行目ではなく ２行目のタイトル欄で調べるのがいいと思うよ。
(ぶらっと)
HANA様ぶらっと様 ありがとうございます。
ぶらっと様のコードでうまくいきました。 確かに全列空白の場合を想定していませんでした…
HANA様 関数をためしたのですが、"月毎累計"シートに関数を入れて参照する値を"連絡簿"にしましたができませんでした…
（シマリス）
＞できませんでした… どうなりましたか？
一旦、同じシートのどこかのセルで数式を試して貰えますか？ 結果がうまく行った場合、そのセルを切り取って 目的のシートの位置に貼り付けてください。 数式は正確に変更されると思いますので、作成してみてうまくいかなかった式と 見比べてみて貰えると良いと思います。
(HANA)
HANA様 実際は3:30になるところで48:00になります（同じシートに貼りつけた場合） 違うシートにした場合「#N/A」になります…
ところで、"月毎累計"のシートのレイアウトが大幅に変更になりました
A B C D E F G 1 名前 残業回数合計 残業時間合計 申請書有残業回数 申請書有残業時間 申請書無残業回数 申請書無残業時間
・残業回数合計：月に残業をした回数（D列+F列） ・残業時間合計：月に残業をした合計時間（E列+G列） ・申請書有残業回数：残業申請を出して残業した回数 ・申請書有残業時間：残業申請を出して残業した時間の合計 ・申請書無残業回数：残業申請を出さずに残業した回数 ・申請書無残業時間：残業申請を出さずに残業した時間の合計
このように大幅に変更になったので、とりあえず最初の要件「申請書無残業回数」に加え「申請書無残業時間」を出そうと次のコードを書きました。
Sub 残業集計()
Dim i As Long Dim c As Long Dim sh As Worksheet Dim snsi As Long Dim r As Long Dim myRow As Long Dim j As Long Dim z As Long Dim Njikan As Variant
Set sh = Worksheets("月毎累計")
myRow = sh.Range("A" & Rows.Count).End(xlUp).Row
With Sheets("連絡簿")
r = .Cells(2, .Columns.Count).End(xlToLeft).Column '最終列取得
z = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 3 To z
snasi = 0
Njikan = 0
For c = 5 To r
If .Cells(i, c).Value = "無" Then
If .Cells(i, c - 1).Value > 0 Then
snasi = snasi + 1 '申請無し残業回数カウント
Njikan = Njikan + .Cells(i, c - 1).Value
End If
End If
Next c
For j = 3 To sh.Range("A" & Rows.Count).End(xlUp).Row
If .Cells(i, 1).Value = sh.Cells(j, 1).Value Then
sh.Cells(j, 6).Value = Njikan '←残業申請無残業時間
sh.Cells(j, 7).Value = snasi '←残魚申請無回数
End If
Next j
Next i
End With
End Sub
これをすると、何故かF列とG列に同じ値（最初の例で言えば田中の値）が"月毎累計"の表の中にずらっと並びます（"連絡簿"シートに田中の値しかなかった場合）。 更に、"連絡簿"シートに鈴木の値を加えると、下記のようになります
田中の元の値：申請無回数：2、申請無時間：3:30 鈴木の元の値：申請無回数：1、申請無時間：4:00
A B C D E F G
1 名前 残業回数合計 残業時間合計 申請書有残業回数 申請書有残業時間 申請書無残業回数 申請書無残業時間 2 田中 2 3:30 3 鈴木 3 7:30 4 山田 3 7:30 （以下同じ値）
先程は名前の判定ができていたのにどうしてできなくなったのでしょうか。
お手数ですがアドバイスお願いします。
（シマリス）
最初にご提示の表が有りますが それと同じ表を新しいシートに作って試してみるとどうですか？ お手数ですが、まっさらな状態から、手入力で作ってみて下さい。
こちらでは、期待するであろう結果が得られていますが。。。
(HANA)
すみません、自己解決しました…
（シマリス）
お時間あるときにでもSUMPRODUCT関数を勉強するといいですよ。 非常に便利な関数だと思います。 使えるようになれば、今回のような場合、わざわざマクロ書くのが面倒に感じるかも。 おせっかい、ごめんなさい。
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.