一行の中から複数の値を抜き出す。

質問

エクセルを使用し、画像の上側のような表を作っています。説明の為、短くしてありますが実際のファイルは横にずらーっと日付が並んでおり、一年で一つのファイルです。月ごとにタブを設定しています。
部屋番号も下に10ほど並んでおります。
画像を一枚に収めるために、表を二つ入れてありますが、実際にはこの二つの表は別々のファイルにしたいです。(上側の表のファイル名を名簿.xlsxlとします)

名簿.xlsxを別のエクセルファイル(宿泊一覧.xlsx)から読み込んで、
名簿.xlsxから宿泊者名を自動で抜き出して、滞在日数や泊数を計算させて表を作成させることは可能でしょうか？
画像を一枚に収めるために、表を二つ入れてありますが、実際にはこの二つの表は別々のファイルにしたいです。
名簿.xslxをどんどん更新すると、宿泊一覧にも名前が更新されるようにしたいのですが、関数だけではできないものなんでしょうか？
VBAはよくわからないので、できれば関数でやるやり方を教えて頂ければありがたいです。

回答No.8

mt2008

いっぱい回答が付いているのでいまさら感が有りますが、VBAでやる場合です。

前提は、
・宿泊者名はかならず「様」が付く
・月跨ぎは前の月の最終日は「→」（月末にチェックインの場合は宿泊者名）で終わり、次の月の1日は「→」または「退」で始まる
・年跨ぎは無い。
・名簿のシート名は「4月」～「3月」で、12か月分ちゃんとある

宿泊一覧.xlsm　に以下のコードを入れて実行してください。
宿泊一覧ブックの1枚目のシートのA2以下に名前とイン、アウトを貼り付けます。
日数や泊数はExcelの演算式で出してください。

Sub Sample()
　　Dim vData()　　
　　nCount = 0 '宿泊総回数
　　nLastRow = 5 '使用している最終行（実際の名簿に合わせて変える）

　　Application.ScreenUpdating = False
　　Workbooks.Open Filename:="C:\名簿.xlsx" '実際の保存場所に合わせて修正
　　With ActiveWorkbook
　　　　For i = 3 To nLastRow '列ごとに処理（データは3行目から有るものとしています）
　　　　　　For j = 0 To 11 '「4月」～「3月」までの月別シートごとに処理
　　　　　　　　sShtName = Month(DateAdd("M", j, "4/1")) & "月" '対象シート名
　　　　　　　　For k = 2 To 32 '日にち毎にチェック（手抜きで2月も小の月も31日分チェック）
　　　　　　　　　　sData = .Sheets(sShtName).Cells(i, k).Value
　　　　　　　　　　Select Case True
　　　　　　　　　　Case sData Like "*様" 'セルの値に「様」を含んでいればチェックイン
　　　　　　　　　　　　ReDim Preserve vData(2, nCount)
　　　　　　　　　　　　vData(0, nCount) = sData
　　　　　　　　　　　　vData(1, nCount) = .Sheets(sShtName).Cells(1, k).Value

　　　　　　　　　　Case sData = "退" 'セルの値が「退」ならチェックアウト
　　　　　　　　　　　　vData(2, nCount) = .Sheets(sShtName).Cells(1, k).Value
　　　　　　　　　　　　nCount = nCount + 1
　　　　　　　　　　End Select
　　　　　　　　Next k
　　　　　　Next j
　　　　Next i
　　　　.Close
　　End With
　　'行列を入れ替えて宿泊一覧に貼り付け
　　vData2 = Application.WorksheetFunction.Transpose(vData)
　　Worksheets(1).Range("A2:C" & nCount) = vData2
　　Application.ScreenUpdating = True
End Sub