マトリクス表計画データから日付参照する方法

質問

office2016

workシートにマトリクス形式のデータがあります
1行目のH列からAL列までで日付記載
2019/12月の場合、H1が12/1、AL1が12/31
2行目は曜日を記載

B列にB3セルから下へアイテム名を記載
B3:アイテム１
B4:アイテム２
…
の様になっている。
アイテム名は今回の内容に関係しませんが7桁の文字列。
A列、C列からG列までは計画に関係ない文字列が記載されている

例として
アイテム１は、日付12/5が1、日付12/12が1、日付12/19が1の様に数値が入っている。
アイテム2は、日付12/5が4、日付12/12が4、日付12/19が4の様に数値が入っている。
数値が入っていないセルは空欄。
生産データなのでマイナスの数値は無し。
数値はmax100くらいまで表示されることがある。

このworkシートから、serialシートへアイテム名と各アイテムの１ケ月の合計値を
持ってきました。
A1：アイテム１ B1:3
A2：アイテム２ B2:12
…
の様な状態です。

シリアル番号に対して計画日を設定したいので、serialシートをベースにserial1のシートへシリアル番号分の行を確保し、B列に連番の構成としました。
A1:アイテム１ B1:1
A2:アイテム１ B2:2
A3:アイテム１ B3:3
A4:アイテム2 B4:1
A5:アイテム2 B5:2
A6:アイテム2 B6:3
A7:アイテム2 B7:4
A8:アイテム2 B8:5
A9:アイテム2 B9:6
A10:アイテム2 B10:7
A11:アイテム2 B11:8
A12:アイテム2 B12:9
A13:アイテム2 B13:10
A14:アイテム2 B14:11
A15:アイテム2 B15:12
A16:アイテム3 B16:1
…
の様な構成です。
このserial1シートでC列にworkシートで対応する日付をもってきたいのですが、
うまい方法が考え付きません。
考えた構成は、
１つのアイテム毎に作業用のシート(シート名はplan)を設定。
A2セルにアイテム名
B1セルからAF1セルで日付データ
workシートから1アイテムの計画データをplanシート2行目B2セルからAF2セルへコピー
空白列を削除
B3セルに１を設定、C3セルにB2+B3の計算式設定、D4セルはC2+C3、以降同様とすることで、その日の先頭のシリアル番号を表示。
アイテム１だとB3:1、C3:1、D3:1
アイテム２だとB3:1、C3:5、D3:9
と表示される。

serial1シートのCセルで
C1セル：=MATCH(B1,plan!$A$3:$AF$3)
とすると、列番号が表示されるので、日付データを持ってこられる
様になりましたが、アイテム数が多いので、処理時間が長くなります。

workシートのマトリクスデータから、serial1シートへシリアル番号に対しての日付データを早く持ってこられる計算式orマクロを教えていただきたく。
マトリクスなので配列にしたら良い様な気がしますが、スキル不足で対応できません。
マクロはベタで押してていただきたく、よろしくお願いします。

回答No.3

nishi6 · ベストアンサー

質問の意図は分かったつもりですが、
（１）serialシートにアイテムごとに集計する作業
（２）serial1シートに各アイテムを展開する作業
この２つは避けたい作業です。

（１）は関数でできるのでいいとして、
（２）を考えましたが、質問に例示のある、「4」を４行に分ける必要があるとすると、関数では考えたくありません。多分できないと思います。「数値はmax100くらいまで」とあるので、なおさらです。個人的にはこの作業が一番大変に思えました。で、マクロで対応しました。

シートは、「work」と「serial1」で、「serial」は使っていません。「work」の日付は日付属性としています。月により日数が違いますが、あまり影響はないでしょう。

また、アイテム数が分からないので、「itemNum=」に数値をセットしてください。自動認識もできますが、あえて入力としています。多目で構いません。

マクロは質問の内容を順に書いただけです。久しぶりに分かりやすい質問でした。（勘違いしてる？）当方、Win10、Excel2010です。

Sub setPlan()
　Const itemNum = 1000　'// workシートの行数(アイテム数)
　Dim rw As Integer　　 '// workシートの行カウンタ
　Dim col As Integer　　'// workシートの列カウンタ
　Dim rwWrite As Long　 '// serial1シートの行カウンタ
　Dim nDay As Integer　 '// 各日のアイテム数
　Dim n As Integer　　　'// 各日のアイテム数カウンタ
　Dim nMonth As Integer '// 同一月のアイテム数カウンタ
　Dim startDay As Date　'// 最初の日付(H3)
　Dim item As String　　'// アイテム名

　With Worksheets("work").Range("H3")
　　startDay = .Offset(-2, 0).Value
　　For rw = 1 To itemNum
　　　nMonth = 0
　　　item = .Offset(rw - 1, -6)
　　　For col = 1 To 31
　　　　nDay = .Cells(rw, col)
　　　　If nDay <> 0 Then
　　　　　With Worksheets("serial1")
　　　　　　For n = 1 To nDay　　'// 分解
　　　　　　　nMonth = nMonth + 1
　　　　　　　rwWrite = rwWrite + 1
　　　　　　　.Cells(rwWrite, 1) = item
　　　　　　　.Cells(rwWrite, 2) = nMonth
　　　　　　　.Cells(rwWrite, 3) = startDay + col - 1
　　　　　　Next
　　　　　End With
　　　　End If
　　　Next
　　Next
　End With
End Sub

お礼

ありがとうございます。
ばっちりでした。
処理速度も速く助かりました。

補足

（１）serialシートにアイテムごとに集計する作業
（２）serial1シートに各アイテムを展開する作業
→
そうなんです
（１）はたいしたことないですが、
（２）は苦労しました。マクロで対応しました。

回答No.2

kkkkkm

アイテム2が12あるのがわからないのですが3でよければ
C1に
=IFERROR(INDEX(work!$H$1:$AL$4,1,MATCH(LARGE((INDIRECT("work!$H$"& MATCH(A1,work!B:B,0) & ":$AL$" & MATCH(A1,work!B:B,0))<>"")*1/COLUMN($H$2:$AL$2),ROWS(INDIRECT("$A$1:$A" & COUNTIF($A$1:A1,A1)))),1/COLUMN($H$2:$AL$2),0)),"")

で配列数式にして下方向にコピーしてください。
Shift+CtrlでEnter

参考にしたサイト
https://nyanto.jimdo.com/エクセル関数-問題解決/条件に合うデータ全ての抽出方法1/条件合うデータ抽出2/

お礼

回答ありがとうございます。
参考にしたサイトを見て勉強します。(^^)/

補足

アイテム2が12あるのがわからないのですが
→
アイテム2は、日付12/5が4、日付12/12が4、日付12/19が4の様に数値が入っている。
４＋４＋４＝１２
です

回答No.1

imogasi

この説明では、課題がよくわからない。
簡単なシートでデータ例を作って、質問に張り付けては。
マトリックスといった、難しい言葉を使っても意味の理解の足しにならない。
普通のエクセルのシートデータは、マトリックスと言えなくもないだろう。
業界・勤務会社の慣用用語か？
＞serial1シートでC列にworkシート
これもシート名＝serial1、シート名＝workとした方が明確。
コンピュター用語でserialやworkという言葉を使うので、かえって紛らわしい。
マトリックスも本来は数学用語で固有の意味があるのが邪魔をする。
ーー
まあこの質問説明でも、わかってくれて、回答は出るかもしれないが。
それで良しとするか。

補足

この説明では、課題がよくわからない
→
下記がやりたい内容です。
serial1シートでC列にworkシートで対応する日付をもってきたい
最後にもう一度
workシートのマトリクスデータから、serial1シートへシリアル番号に対しての日付データを早く持ってこられる計算式orマクロを教えていただきたく。
と表現しています。

簡単なシートでデータ例を作って、質問に張り付けては。
→
これは、そう思いましたが、それに相当する例をあげて説明したつもりです。
最終形の構成はありませんでしたが。

マトリックスといった、難しい言葉を使っても意味の理解の足しにならない
→
マトリクスは、別に難しい言葉ではないと思います。一般用語です。