[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『シフト表から勤務時間帯ごとの合計をだしたい』（温室育ち）
はじめまして、温室育ちです。 以下のシフト表から、D列の16行目以降に時間帯ごとの出勤人数を出したいと考えています。
できれば関数で計算したいです。 =SUMPRODUCT((B3:B12=B16)*(C3:C12=A16)*(VLOOKUP(D3:D12,Sheet2!A2:C4,2,FALSE)>=C16)*(VLOOKUP(D3:D12,Sheet2!A2:C4,3,FALSE)<=C16)*1) などとしてみたのですがダメでした・・・
ご教示ください。よろしくお願いします。
sheet1
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H]
[1] | | | |日付 | | | |
[2] |名前 |役割|チーム名|3月1日|3月2日|3月3日|3月4日|3月5日
[3] |くま |α |動物園 |A | |C |C |
[4] |ぱんだ |β |サーカス|B |B | |A |
[5] |らっこ |α |テレビ | |A |B | |B
[6] |こあら |α |サーカス|C | |A | |A
[7] |きりん |α |動物園 | |A | |B |B
[8] |たぬき |α |テレビ |C |C | | |C
[9] |ぞう |β |動物園 |A | |B | |
[10]|らいおん|β |サーカス|B | |C | |A
[11]|ねこ |β |テレビ | |B | |A |
[12]|わんこ |β |動物園 | |A |B |C |
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H]
[15]|チーム名|役割|時間帯|3月1日|3月2日|3月3日|3月4日|3月5日
[16]|動物園 |α |9:00 | 1| | | |
[17]|動物園 |α |9:30 | 1| | | |
[18]|動物園 |α |10:00 | 1| | | |
[19]|動物園 |α |10:30 | 1| | | |
[20]|動物園 |α |11:00 | 1| | | |
[21]|動物園 |α |11:30 | | | | |
[22]|動物園 |α |12:00 | | | | |
[23]|動物園 |α |12:30 | | | | |
[24]|動物園 |α |13:00 | | | | |
[25]|動物園 |α |13:30 | | | | |
[26]|動物園 |β |9:00 | 1| | | |
[27]|動物園 |β |9:30 | 1| | | |
[28]|動物園 |β |10:00 | 1| | | |
[29]|動物園 |β |10:30 | 1| | | |
[30]|動物園 |β |11:00 | 1| | | |
[31]|動物園 |β |11:30 | | | | |
[32]|動物園 |β |12:00 | | | | |
[33]|動物園 |β |12:30 | | | | |
[34]|動物園 |β |13:00 | | | | |
[35]|動物園 |β |13:30 | | | | |
[36]|サーカス|α |9:00 | | | | |
[37]|サーカス|α |9:30 | | | | |
[38]|サーカス|α |10:00 | | | | |
[39]|サーカス|α |10:30 | | | | |
[40]|サーカス|α |11:00 | | | | |
[41]|サーカス|α |11:30 | | | | |
[42]|サーカス|α |12:00 | 1| | | |
[43]|サーカス|α |12:30 | 1| | | |
[44]|サーカス|α |13:00 | 1| | | |
[45]|サーカス|α |13:30 | 1| | | |
[46]|サーカス|β |9:00 | | | | |
[47]|サーカス|β |9:30 | | | | |
[48]|サーカス|β |10:00 | | | | |
[49]|サーカス|β |10:30 | 2| | | |
[50]|サーカス|β |11:00 | 2| | | |
[51]|サーカス|β |11:30 | 2| | | |
[52]|サーカス|β |12:00 | 2| | | |
[53]|サーカス|β |12:30 | | | | |
[54]|サーカス|β |13:00 | | | | |
[55]|サーカス|β |13:30 | | | | |
[56]|テレビ |α |9:00 | | | | |
[57]|テレビ |α |9:30 | | | | |
[58]|テレビ |α |10:00 | | | | |
[59]|テレビ |α |10:30 | | | | |
[60]|テレビ |α |11:00 | | | | |
[61]|テレビ |α |11:30 | | | | |
[62]|テレビ |α |12:00 | 1| | | |
[63]|テレビ |α |12:30 | 1| | | |
[64]|テレビ |α |13:00 | 1| | | |
[65]|テレビ |α |13:30 | 1| | | |
[66]|テレビ |β |9:00 | | | | |
[67]|テレビ |β |9:30 | | | | |
[68]|テレビ |β |10:00 | | | | |
[69]|テレビ |β |10:30 | | | | |
[70]|テレビ |β |11:00 | | | | |
[71]|テレビ |β |11:30 | | | | |
[72]|テレビ |β |12:00 | | | | |
[73]|テレビ |β |12:30 | | | | |
[74]|テレビ |β |13:00 | | | | |
[75]|テレビ |β |13:30 | | | | |
sheet2
|[A] |[B] |[C]
[1]|シフト|出勤時間|退勤時間
[2]|A |9:00 |11:00
[3]|B |10:30 |12:00
[4]|C |12:00 |13:30
＜ 使用 Excel：Excel2003、使用 OS：Windows7 ＞
一応、D16に下のどちらの式も正しく表示できますが、これ以上纏めたりする方法が私には思い浮かばないので上級者の方の案があれば、そちらでお願いします。
空白のセルは0と表示されるので、書式設定で0;0;"" とユーザー定義して頂くか、
2003ならツール→オプションだったかな？、の[ゼロ値のセルにゼロを表示する]のチェックを外して下さい。
結果が数字に見える文字でも良ければ、TEXT関数で=TEXT(下の式,"0;0;""""")として下さい。
D16=SUM(($B16&$A16&"A"=$B$3:$B$12&$C$3:$C$12&D$3:D$12)*($C16>=Sheet2!$B$2)*($C16<=Sheet2!$C$2))+SUM(($B16&$A16&"B"=$B$3:$B$12&$C$3:$C$12&D$3:D$12)*($C16>=Sheet2!$B$3)*($C16<=Sheet2!$C$3))+SUM(($B16&$A16&"C"=$B$3:$B$12&$C$3:$C$12&D$3:D$12)*($C16>=Sheet2!$B$4)*($C16<=Sheet2!$C$4))
[Ctrl]+[Shift]+[Enter]同時押し
下右に必要数フィルコピー
か、
D16=COUNTIFS($B$3:$B$12,$B16,$C$3:$C$12,$A16,D$3:D$12,"A")*AND($C16>=Sheet2!$B$2,$C16<=Sheet2!$C$2)+COUNTIFS($B$3:$B$12,$B16,$C$3:$C$12,$A16,D$3:D$12,"B")*AND($C16>=Sheet2!$B$3,$C16<=Sheet2!$C$3)+COUNTIFS($B$3:$B$12,$B16,$C$3:$C$12,$A16,D$3:D$12,"C")*AND($C16>=Sheet2!$B$4,$C16<=Sheet2!$C$4)
下右に必要数フィルコピー
A B C D E F G H 1 日付 2 名前 役割 チーム名3月1日 3月2日 3月3日 3月4日 3月5日 3 くま α 動物園 A C C 4 ぱんだ β サーカスB B A 5 らっこ α テレビ A B B 6 こあら α サーカスC A A 7 きりん α 動物園 A B B 8 たぬき α テレビ C C C 9 ぞう β 動物園 A B 10 らいおんβ サーカスB C A 11 ねこ β テレビ B A 12 わんこ β 動物園 A B C 13 14 15 チーム名役割 時間帯 3月1日 3月2日 3月3日 3月4日 3月5日 16 動物園 α 9:00 1 1 17 動物園 α 9:30 1 1 18 動物園 α 10:00 1 1 19 動物園 α 10:30 1 1 1 1 20 動物園 α 11:00 1 1 1 1 21 動物園 α 11:30 1 1 22 動物園 α 12:00 1 2 1 23 動物園 α 12:30 1 1 24 動物園 α 13:00 1 1 25 動物園 α 13:30 1 1 26 動物園 β 9:00 1 1 27 動物園 β 9:30 1 1 28 動物園 β 10:00 1 1 29 動物園 β 10:30 1 1 2 30 動物園 β 11:00 1 1 2 31 動物園 β 11:30 2 32 動物園 β 12:00 2 1 33 動物園 β 12:30 1 34 動物園 β 13:00 1 35 動物園 β 13:30 1 36 サーカスα 9:00 1 1 37 サーカスα 9:30 1 1 38 サーカスα 10:00 1 1 39 サーカスα 10:30 1 1 40 サーカスα 11:00 1 1 41 サーカスα 11:30 42 サーカスα 12:00 1 43 サーカスα 12:30 1 44 サーカスα 13:00 1 45 サーカスα 13:30 1 46 サーカスβ 9:00 1 1 47 サーカスβ 9:30 1 1 48 サーカスβ 10:00 1 1 49 サーカスβ 10:30 2 1 1 1 50 サーカスβ 11:00 2 1 1 1 51 サーカスβ 11:30 2 1 52 サーカスβ 12:00 2 1 1 53 サーカスβ 12:30 1 54 サーカスβ 13:00 1 55 サーカスβ 13:30 1 56 テレビ α 9:00 1 57 テレビ α 9:30 1 58 テレビ α 10:00 1 59 テレビ α 10:30 1 1 1 60 テレビ α 11:00 1 1 1 61 テレビ α 11:30 1 1 62 テレビ α 12:00 1 1 1 2 63 テレビ α 12:30 1 1 1 64 テレビ α 13:00 1 1 1 65 テレビ α 13:30 1 1 1 66 テレビ β 9:00 1 67 テレビ β 9:30 1 68 テレビ β 10:00 1 69 テレビ β 10:30 1 1 70 テレビ β 11:00 1 1 71 テレビ β 11:30 1 72 テレビ β 12:00 1 73 テレビ β 12:30 74 テレビ β 13:00 75 テレビ β 13:30
（sy） 2016/03/17(木) 07:20
おはようございます。
syさん ありがとうございます！ できました！！ ただ私の手違いで、一部条件が間違っていました。 退勤時間が11:00の場合、出勤している時刻は10:30までです。 なので、表のD20セルは空欄が正しかったのですが、この部分は自分で修正できそうです。
また、今回のケースはマクロでやった方がよいとのことでしたので、わかる方がいらっしゃいましたらマクロでのやり方もご教示ください。
よろしくおねがいします。
（温室育ち） 2016/03/17(木) 09:36
Excel2003なら
=SUMPRODUCT(($C$3:$C$12=$A16)*($B$3:$B$12=$B16)*(SUMIF(Sheet2!$A$2:$A$4,$D$3:$D$12,Sheet2!$B$2:$B$4)<=$C16)*(SUMIF(Sheet2!$A$2:$A$4,$D$3:$D$12,Sheet2!$C$2:$C$4)>=$C16))
かな？
Excel2007以降なら
=SUMPRODUCT(COUNTIFS($C$3:$C$12,$A16,$B$3:$B$12,$B16,D$3:D$12,"<>",D$3:D$12,REPT(Sheet2!$A$2:$A$4,(Sheet2!$B$2:$B$4<=$C16)*(Sheet2!$C$2:$C$4>=$C16))))
でもよさげなんだけどね。
※0表示するんで、表示形式とかで対応は必要。 （GobGob） 2016/03/17(木) 09:38
あら？ エラーがでる。。。
Excel2003修正っす
=SUMPRODUCT(($C$3:$C$12=$A16)*($B$3:$B$12=$B16)*(SUMIF(Sheet2!$A$2:$A$4,D$3:D$12,Sheet2!$B$2:$B$4)<=$C16)*(SUMIF(Sheet2!$A$2:$A$4,D$3:D$12,Sheet2!$C$2:$C$4)>=$C16)) （GobGob） 2016/03/17(木) 09:40
補足。
Sheet2で、出勤 0:00の条件が無いのが前提っす （24:00はOK） （GobGob） 2016/03/17(木) 09:54
一応、0:00対応を考えたんで。
=SUMPRODUCT(($C$3:$C$12=$A16)*($B$3:$B$12=$B16)*(SUMIF(Sheet2!$A$2:$A$4,D$3:D$12,Sheet2!$B$2:$B$4)<=$C16)*(SUMIF(Sheet2!$A$2:$A$4,D$3:D$12,Sheet2!$C$2:$C$4)>=$C16)*COUNTIF(Sheet2!$A$2:$A$4,D$3:D$12)) （GobGob） 2016/03/17(木) 10:15
本線とはまったく離れて申し訳ないんですが
>>退勤時間が11:00の場合、出勤している時刻は10:30までです。
なら、Sheet2 の退勤時刻を 10:30 と設定すればいいのでは？ Sheet1 の用途というか意味合いが不明ですが、仮に 【ステージに出ている時間】を表示するなら 後片付け時間もあるでしょうから、30分前に姿を消すというのはうなづけますし、でも、Sheet2が報酬ベースの 管理用であれば、11:00にしておくというのもわかりますが。
その場合、出勤も 9:00 だったとしても、準備とか、そういったこともあってステージに顔を出せるのは 9:30 とか そういうことはないのでしょうかね。
テーマに関係のないレスで申し訳ありません。
時間がとれたらＶＢＡで書いてみようかなと思い、読み返して、あれ？ と思いましたので。
（仮にコードを書いたとしても、βのコードはいつも、【ゴリゴリの力技】ですから、きっと syさんから さらっとスマートなものが提示されるでしょうし、βとしてはアップしない公算大ですが）
（β） 2016/03/17(木) 10:28
お世話になっております。 GobGobさんの式でもできました！ 本当にありがとうございます！！
βさんもご意見くださいましてありがとうございます。説明が悪く申し訳ありません。 βさんのおっしゃることは全くそのとおりで、退勤時間のところを10:30にしてしまえばすむことでした。 単純に既に退勤時間が11:00とされている、出来あがったシフトパターン表を、そのまま使いたかっただけです。 また、ご指摘いただいた準備や後片づけの件は、今回は考慮しなくて大丈夫です。
マクロでのやり方、是非拝見したいです。もしよろしければアップしていただけると幸いです。
よろしくおねがいします。 （温室育ち） 2016/03/17(木) 10:46
一応書いてみました。 やはり、ゴリゴリの力技コードになりました。 SHeet2の出勤時刻 30分調整をいれてあります。 詳細な検証はしていないのですが、アップします。
Sub Test()
Dim dicS As Object
Dim dicA As Object
Dim c As Range
Dim x As Long
Dim y As Long
Dim kRole As String
Dim kTeam As String
Dim kDate As Long
Dim body As Range
Dim line As Range
Dim v As Variant
Dim tZone As Date
Dim sZone As Variant
Dim k As String
Dim sCode As String
Set dicS = CreateObject("Scripting.Dictionary")
Set dicA = CreateObject("Scripting.Dictionary")
'シフト情報の辞書格納
With Sheets("Sheet2")
For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
'退勤時間は30分繰り上げ
dicS(c.Value) = Array(c.Offset(, 1).Value, c.Offset(, 2).Value - TimeSerial(0, 30, 0))
Next
End With
With Sheets("Sheet1")
x = .Range("A2").End(xlToRight).Column - 3 '元データの日付の数
'元データの情報をDicAに格納
For Each c In .Range("D3:D14").Resize(, x)
If dicS.exists(c.Value) Then 'シフトコード登録あれば
kRole = c.EntireRow.Range("B1").Value
kTeam = c.EntireRow.Range("C1").Value
kDate = c.EntireColumn.Cells(2).Value2
sCode = c.Value
k = kRole & vbTab & kTeam & vbTab & kDate & vbTab & sCode
dicA(k) = dicA(k) + 1
End If
Next
x = .Range("A15").End(xlToRight).Column - 3 '展開データの日付の数
y = .Range("A" & Rows.Count).End(xlUp).Row - 15 '展開データのデータ行数
Set body = .Range("D16").Resize(y, x) '展開データのデータ部分
ReDim v(1 To y, 1 To x) 'データ部分と同じサイズの配列生成
For Each line In body.Rows
kRole = line.EntireRow.Range("B1").Value
kTeam = line.EntireRow.Range("A1").Value
sCode = getShift(line.EntireRow.Range("C1").Value, dicS)
If dicS.exists(sCode) Then
For Each c In line.Cells
kDate = c.EntireColumn.Cells(15).Value2
k = kRole & vbTab & kTeam & vbTab & kDate & vbTab & sCode
If dicA.exists(k) Then v(c.Row - 15, c.Column - 3) = v(c.Row - 15, c.Column - 3) + dicA(k)
Next
End If
Next
body.Value = v
End With
End Sub
Private Function getShift(t As Date, dic As Object) As String
Dim d As Variant
For Each d In dic
If t >= dic(d)(0) And t <= dic(d)(1) Then
getShift = d
Exit For
End If
Next
End Function
（β） 2016/03/18(金) 12:35
お世話になっております。 βさん、コードのご提示ありがとうございます！ 確認させていただいて、また報告いたします。 本当にありがとうございました。 （温室育ち） 2016/03/18(金) 18:34
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.