[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『シフトの抽出』(小次郎)
シフト表からD勤務者だけ抽出したいです。
シフト表はF8からAJ8まで日付(31日)が入ってます。
E列には職員名(17名)
F11からAJ11まで職員のシフト名(A,B,C,Dの英字)が入っています。
別の日毎シートの行動詳細表の一つのセルに
その日のD勤務者(複数名)をすべて表示させたいです。
ご教示よろしくお願い致します。
< 使用 Excel:Excel2019、使用 OS:Windows10 >
こんばんは ^^ 回答ではありません。 予測図 Sheet1 はこんな感じなのでしょうか 間違っていましたらお許しを。。。修正情報を 教えて下さいましたら、可能でしたら書き換え ます。^^;←自信ありませんけど。。。。。 なにかの足しにでも。 Sub DummyDataMake() Dim i As Long Dim r As Range Dim Sary Dim D As Date Dim Rnum As Long Rnd -5 Sary = Array("", "A", "B", "C", "D", "", "") D = DateSerial(2020, 1, 1) With Worksheets("Sheet1") .UsedRange.Clear For i = 1 To 31 .Cells(8, i + 5) = D .Cells(8, i + 5).NumberFormatLocal = "m/d"
D = D + 1 Next For i = 11 To 11 + 17 .Cells(i, "E") = "職員-" & CStr(i - 8) Next For Each r In .Range("F11:AJ28") Rnum = Int((6 - 0 + 1) * Rnd + 0) r = Sary(Rnum) Next End With End Sub (隠居じーさん) 2020/02/03(月) 17:31
|[A]|[B]|[C]|[D]|[E] |[F]|[G]|[H]|[I]|[J]|[K]|[L]|[M]|[N]|[O] |[P] |[Q] |[R] |[S] |[T] |[U] |[V] |[W] |[X] |[Y] |[Z] |[AA]|[AB]|[AC]|[AD]|[AE]|[AF]|[AG]|[AH]|[AI]|[AJ] [1] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [2] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [3] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [4] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [5] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [6] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [7] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [8] | | | | | |2/1|2/2|2/3|2/4|2/5|2/6|2/7|2/8|2/9|2/10|2/11|2/12|2/13|2/14|2/15|2/16|2/17|2/18|2/19|2/20|2/21|2/22|2/23|2/24|2/25|2/26|2/27|2/28|2/29|3/1 |3/2 [9] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [10]| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [11]| | | | |職員-3 |B | | |D |A |D | |C | |D |B | | |A | | |A | |D |A |D |A | |C |C |B | |C | | |C [12]| | | | |職員-4 | |A | |C |B | |B | |D |B |C |D | | |C |D | |A |B | |B |A |B |D |D | |B | |A | | [13]| | | | |職員-5 | |B |A | |A | | | |C | | | | |D | | |C | |A |D | |B |C | |C |A |A |D |C |D | [14]| | | | |職員-6 |B | |A | |C | |D |C |B |A |A |D |B | |C |A |A | |A | |A |A |A |D | |D | | | |C | [15]| | | | |職員-7 |A | |C | | | |A | |B |A | |C |A |C |B |B |A | | | | | | |D | | | |D |D |D | [16]| | | | |職員-8 |C | | | | |C |C |B | |C |A |C |B |B | | |A |C |B |B | | | |C | |D |D |C |B |D |D [17]| | | | |職員-9 |B |C | |B |A |D | |A | |B | | | |A | |D |A | |B |B |A |A | |D |C |B | |C |B | |A [18]| | | | |職員-10|C | | |D |D |B |D |C |D |D |A |D | | |B |D | | |D | |A | |B | |A | |B |C |D |D |B [19]| | | | |職員-11|C | |D | | | | | |D |B |D |D |B | |C |A |B |C |D | |D | | |D |C |C |B | |A | | [20]| | | | |職員-12| | |B | |D |A | | |B | | | |A |C | |C |B | |A |D | |D | |C |D |A | |B |B |C | [21]| | | | |職員-13| |B | | |D |D |B |B |B | | |B | | |B | |C | | |C | |C |C | | |D | |C |D | | [22]| | | | |職員-14| |A | |C | |A |D | | | |C |C | | | | |C | |C | |A |C |B | | | |A | |D |B | [23]| | | | |職員-15| |B | |C | | |D |C | |B |A |A | |C |C |D |A |A | |D |C | |B |D | | |C |C |A | |B [24]| | | | |職員-16|C | | | | |D |D | | |D |B | |B | |D |C |B | |D | |B |D | |C |A |B | | |A | |B [25]| | | | |職員-17| | |A | | | | | | |C |D | | |C |B | | | |B | |D |B |D |D | | |D | |A |C | [26]| | | | |職員-18|A | |A |C | | |D | |C | |B | | |D | | |B | |A | | | | | |A | |D | | |B | [27]| | | | |職員-19|A |B | |A |A |B | | | | | | |A | | |C |A |B |B | | | |B |D |C |B |B |A |B | |A [28]| | | | |職員-20| | |C | | |B |C |B | |C |B | | |A | | | |A |B |C |C |D |A |B |D | |D | | |D |C (隠居じーさん) 2020/02/04(火) 10:49
>別の日毎シートの行動詳細表の一つのセルに >その日のD勤務者(複数名)をすべて表示させたいです。
1.その日とは、何月何日なのか、どこを見ると分かるんですか?(セル?、シート名?)
2.Excel2019 で TEXTJOIN関数 が使えるのかどうか教えてください。
(半平太) 2020/02/04(火) 11:23
半平太様ありがとうございます。
1.その日はシートに日時が振ってあり、シート毎に当該日時のD勤務者を抽出し表示させたいと思っています。
2.TEXTJOIN関数使えるようです。
拙い説明で申し訳ありませんが、どうぞよろしくお願い致します。
シフト表シート
|[A]|[B]|[C]|[D]|[E] |[F]|[G]|[H]|[I]|[J]|[K]|[L]|[M]|[N]|[O] |[P] |[Q] |[R] |[S] |[T] |[U] |[V] |[W] |[X] |[Y] |[Z] |[AA]|[AB]|[AC]|[AD]|[AE]|[AF]|[AG]|[AH]|[AI]|[AJ] [1] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [2] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [3] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [4] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [5] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [6] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [7] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [8] | | | | | |2/1|2/2|2/3|2/4|2/5|2/6|2/7|2/8|2/9|2/10|2/11|2/12|2/13|2/14|2/15|2/16|2/17|2/18|2/19|2/20|2/21|2/22|2/23|2/24|2/25|2/26|2/27|2/28|2/29|3/1 |3/2 [9] | | | | | |土 |日 |月 |火 |水 |木 |金 |土 |日 | | | | | | | | | | | | | | | | | | | | | | [10]| | | | |職員-1 |D | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [11]| | | | |職員-2 |B | |D |D |A |D | |C | |D |B | | |A | | |A | |D |A |D |A | |C |C |B | |C | | |C [12]| | | | |職員-3 |D |A | |C |B | |B | |D |B |C |D | | |C |D | |A |B | |B |A |B |D |D | |B | |A | | [13]| | | | |職員-4 |D |B |A | |A | | | |C | | | | |D | | |C | |A |D | |B |C | |C |A |A |D |C |D | [14]| | | | |職員-5 |B |D |A | |C | |D |C |B |A |A |D |B | |C |A |A | |A | |A |A |A |D | |D | | | |C | [15]| | | | |職員-6 |A | |C | | | |A | |B |A | |C |A |C |B |B |A | | | | | | |D | | | |D |D |D | [16]| | | | |職員-7 |C |D | | | |C |C |B | |C |A |C |B |B | | |A |C |B |B | | | |C | |D |D |C |B |D |D [17]| | | | |職員-8 |B |C | |B |A |D | |A | |B | | | |A | |D |A | |B |B |A |A | |D |C |B | |C |B | |A [18]| | | | |職員-9 |C | |D |D |D |B |D |C |D |D |A |D | | |B |D | | |D | |A | |B | |A | |B |C |D |D |B [19]| | | | |職員-10|C |D |D | | | | | |D |B |D |D |B | |C |A |B |C |D | |D | | |D |C |C |B | |A | | [20]| | | | |職員-11| |D |B | |D |A | | |B | | | |A |C | |C |B | |A |D | |D | |C |D |A | |B |B |C | [21]| | | | |職員-12|D |B | | |D |D |B |B |B | | |B | | |B | |C | | |C | |C |C | | |D | |C |D | | [22]| | | | |職員-13|D |A | |C | |A |D | | | |C |C | | | | |C | |C | |A |C |B | | | |A | |D |B | [23]| | | | |職員-14| |B | |C | | |D |C | |B |A |A | |C |C |D |A |A | |D |C | |B |D | | |C |C |A | |B [24]| | | | |職員-15|C | | | | |D |D | | |D |B | |B | |D |C |B | |D | |B |D | |C |A |B | | |A | |B [25]| | | | |職員-16| | |A | | | | | | |C |D | | |C |B | | | |B | |D |B |D |D | | |D | |A |C | [26]| | | | |職員-17|A | |A |C | | |D | |C | |B | | |D | | |B | |A | | | | | |A | |D | | |B |
行動詳細表2月1日シート
|[A] |[B]|[C]|[D] |[E] |[F] |[G] |[H]|[I]|[J]|[K]|[L]|[M]|[N]|[O] |[P] |[Q] |[R] |[S] |[T] |[U] |[V] |[W] |[X] |[Y] |[Z] |[AA]|[AB]|[AC]|[AD]|[AE]|[AF]|[AG]|[AH]|[AI]|[AJ] [1] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [2] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [3] | | | |A勤務者氏名|B勤務者氏名|C勤務者氏名|D勤務者氏名| | | | | | | | | | | | | | | | | | | | | | | | | | | | | [4] |0500| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [5] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [6] |0600| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [7] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [8] |0700| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [9] | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [10]|0800| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [11]| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [12]|0900| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [13]| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [14]|1000| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | [15]| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | (小次郎) 2020/02/04(火) 11:58
>行動詳細表2月1日
例えば 2020/2/1のシートは、「行動詳細表2月1日」というシート名になるんですね。
つまり、「行動詳細表」が頭にあり、かつ、2020等の「年度情報」は無いもの解釈します。 ※下案、年度情報がないと、将来どんな不具合が出るか保証しませんけども・・
1.日毎シートの左端のシート見出し選択したら、Shiftキーを押しながら、右端のシートをマウスで選択する。 ※すると、全日毎シートが選択されます(つまり、一回の操作で全シートに作用できるように「作業グループ」作る)。
2.D2:G2に A,B,C,D と文字を入れる
3.D3セル =TEXTJOIN("、",TRUE,INDEX(REPT(シフト表!$E$11:$E$27,INDEX(シフト表!$F$11:$AJ$27,0,MATCH(TEXT(REPLACE(CELL("filename",$A$1),1,FIND("]行動詳細表",CELL("fileName",$A$1))+5,""),"m/d")*1,シフト表!$F$8:$AJ$8,0))=D2),0)) 右にコピー(G3セルまで)
4.シフト表シートを選択して、「作業グループ」を解除する。
結果図(例) 「行動詳細表2月2日」シートの場合
行 _______D_______ _______________ E _______________ __ F __ _G_ 1 2 A B C D 3 職員-2、職員-12 職員-3、職員-11、職員-13、職員-17 職員-7
(半平太) 2020/02/04(火) 13:12
文中、年度情報が無いと不具合が出るかもとのお話がありましたが、
出ないためには、シートネームをどのように修正すればよろしいでしょうか。
例えば20200201、20200202的なシート名の方がよろしいでしょうか。
併せてご教示いただけましたらうれしいです。
(小次郎) 2020/02/04(火) 13:50
>文中、年度情報が無いと不具合が出るかもとのお話がありましたが、
例えば、12月に翌年1月のシフト表を作ったとしましょう。 すると「行動詳細表1月5日」シートなんてのが作られます。
常識人は、そのシートは翌年の1月5日用と考えるでしょうが、 エクセルは、同年の1月5日(つまり、11ヶ月前の日付)用だと思うでしょうね。
どっちが絶対的に正しいというものでもありません。1年前の予定を知りたい時だってなくはないですから。
単なる「20200201」をシート名にすれば紛れは全くないです。 それだと下式への変更が必要になります。
D3セル =TEXTJOIN("、",TRUE,INDEX(REPT(シフト表!$E$11:$E$27,INDEX(シフト表!$F$11:$AJ$27,0,MATCH(TEXT(REPLACE(CELL("filename",$A$1),1,FIND("]",CELL("fileName",$A$1)),""),"0-00-00")*1,シフト表!$F$8:$AJ$8,0))=D2),0))
>関数が表示されるだけでした。 それは、入力したセルの書式が「文字列」に変わっているためなので、「標準」に戻してから再度、式を入力してください。
(半平太) 2020/02/04(火) 14:22
セルの書式を標準に戻したのですが、エラーが消えません。
どこが間違っているのでしょうか。
ご教示頂けましたらうれしいです。
(小次郎) 2020/02/04(火) 15:11
そちらの状況が分からないので、下記マクロを実行して、こっちの状況を再現して頂けませんか。
1.新しいブックを一つ作成する。 2.Sheet1のシート見出しを右クリックして、「コードの表示」を選ぶ 3.画面中央の白いエリアに後記のマクロコードをコピペする。 4.Test() の第一行目にカーソルを置いて、F5キーを押下する。(マクロが実行される)
以上で、小次郎1.xlxmブックに 2020年2月1日と2日分のシートが自動的に作成され、 シフトが反映される様になっています。
Sub test() Dim NN As Long
DummyDataMake 1
For NN = 1 To 2 With Worksheets.Add(, Sheets(Sheets.Count)) .Name = "2020020" & NN .Range("D2:G2").Value = [{"A","B","C","D"}] .Range("D3:G3").FormulaLocal = _ "=TEXTJOIN(""、"",TRUE,INDEX(REPT(シフト表!$E$11:$E$27," & _ " INDEX(シフト表!$F$11:$AJ$27,0,MATCH(TEXT(REPLACE(CELL(""filename"",$A$1),1" & _ ",FIND(""]"",CELL(""fileName"",$A$1)),""""),""0-00-00"")*1,シフト表!$F$8:$AJ$8,0))=D2),0))" End With Next NN End Sub
Sub DummyDataMake(Num As Long) Dim i As Long Dim r As Range Dim Sary Dim D As Date Dim Rnum As Long Rnd -5 Sary = Array("", "A", "B", "C", "D", "", "") D = DateSerial(2020, 2, 1) With Worksheets(1) .UsedRange.Clear For i = 1 To 31 .Cells(8, i + 5) = D .Cells(8, i + 5).NumberFormatLocal = "m/d"
D = D + 1 Next For i = 11 To 11 + 16 .Cells(i, "E") = "職員-" & CStr(i - 10) Next For Each r In .Range("F11:AJ27") Rnum = Int((6 - 0 + 1) * Rnd + 0) r = Sary(Rnum) Next .Name = "シフト表" End With
Me.Parent.SaveAs "小次郎" & Num, xlOpenXMLWorkbookMacroEnabled
End Sub
(半平太) 2020/02/04(火) 17:43
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.