Excel２０１６でシフト表からのデータ抽出

質問

質問です。
Excelで添付ファイルの、左のような、シフト表から
「▽」のスタッフ（A～D）を抽出して、
右のような書式の、
黄色い部分に、「▽」のスタッフ（A～D)を
自動的に入力するようにすることは可能でしょうか？

関数で教えていただけると助かります。
VBAで教えていただけても助かります。

回答No.8

masnoske · ベストアンサー

VBAを使うなら，スタッフ記号求めるユーザー定義関数（スタッフ関数）を作成してワークシートで使う方法が簡単だと思います．
考え方は
(1) 日付列から▽を見つける．
(2) ▽が見つかった行を求める．
(3) (2)で見つけた行と A列が交差するセルの値（＝スタッフ）を求める．

関数名をスタッフとし，パラメータに日付列番号を入力します．
まず，VBAの標準モジュールに以下の関数を作成します．

Public Function スタッフ(ByVal 日付列番号 As Long) As String
Dim ▽セル As Range
Dim ▽行 As Range

Set ▽セル = Columns(日付列番号).Find(What:="▽")
Set ▽行 = Rows(▽セル.Row)
スタッフ = Intersect(Range(”A:A"), ▽行).Value
End Function

K3以降に日付が入力されているとして，M3に以下の関数を入力します．
=スタッフ(K3+1)

この関数を M4以降にコピーすれば OKです．

お礼

皆さんありがとうございます。
ただ、僕の理解が追い付かないです。
YOUTUBEのExcel兄さんや金子さんで
VBAやエクエル勉強
始めたばかりなので、
実務に生かせるよう、
皆さんからいただいた回答
参考に頑張ります。
　どなたかからの方法で実現出来たら、必ず報告
します、エクセルの先輩方！
よろしくお願いします。

回答No.7

masnoske

Excel2016を使用していないので動作確認はできていませんが，以下のように XLOOKUP関数を使えば可能なのではないでしょうか．
=XLOOKUP("▽",日付の列,A:A)

問題は日付の列をどうやって求めるかです．
日付が 1行目に入力されていると仮定した場合，M2に以下の数式を入力すれば日付が入力されている列をアルファベットで特定できます．
=SUBSTITUTE(ADDRESS(1,K2+1,4,1),1,)

この結果は Bになるので，以下の数式で B:Bに変換できます．
（*:*の *を Bに置き換えています）
=SUBSTITUTE("*:*","*",SUBSTITUTE(ADDRESS(1,K2+1,4,1),1,) )

最終的には M2に以下の数式を入力し，M3以降にコピーすれば良いのではないかと思います（INDIRECTで B:Bという文字列を参照に変換しています）．
=XLOOKUP("▽",INDIRECT(SUBSTITUTE("*:*","*",SUBSTITUTE(ADDRESS(1,K2+1,4,1),1,) )),A:A)

日付は 5日以降も続くと思いますので，上記の数式を入力したあとに必要なだけ列を挿入すれば良いかと思います．

お礼

ありがとうございました。
大変助かりました。

回答No.6

imogasi

例データ　Ｓｈｅｅｔ１で
人\日 1 2 3 4 5 6 7
A ▼
B ▼
C ▼ ▼
D ▼ ▼ ▼
Ｓｈｅｅｔ１は
１行目Ｂ列より日付数字のみ。
（数字は日だけ。日付シリアル値ではないので注意。）
Ｂ２：から当番の日に▼が入る。
ーーー
Ｓｈｅｅｔ２　Ａ２から下に日付の数字。
（数字は日だけ。日付シリアル値ではないので注意。）
標準モジュールに
Sub test01()
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
'---
sh1.Activate
sh1.Range("B2").CurrentRegion.Select
'----
For Each cl In Selection
If cl.Row <> 1 Then '日付行でなければ
If cl = "▼" Then
m = sh1.Cells(cl.Row, "A") '人
d = sh1.Cells(cl.Column) '日
MsgBox d & " " & m
Set r = sh2.Range("A1:A100").Find(what:=d)
sh2.Cells(r.Row, "c") = m
End If
End If
Next
End Sub
実行
Ｓｈｅｅｔ２のＡ１：Ｂ○に
1 D
2 C
3 D
4 D
5 A
6 B
7 C
8
ーーー
この課題は、エクセルの表の「組み換え」問題です。
そもそも適当な操作が設けられていないと思う。
関数でも、やっとこさ、できるが、式は易しくはない。
　いろいろこういう問題に今後ぶつかりそうなら、ＶＢＡを勉強すべきと思うので、参考に上げてみる。
ＶＢＡでは、Ｆｏｒ　Ｅａｃｈで、範囲全セルを簡単にとらえられるので、後はＳｈｅｅｔ１における、見出し行・見出し列のデータを採って、Ｓｈｅｅｔ２で、Ｃｅｌｌｓ（計算後の行、計算後の列）の位置へ、再現するだけです。

お礼

ありがとうございました。
大変助かりました。

回答No.5

kkkkkm

No3の補足です。
日付なので連続していると思いますが仮に中抜けなどがある場合を考えると以下の方がいいかもしれません。
K列の日と1行目の日が最初に一致したデータを取り出します。

=IFERROR(INDEX($A$3:$A$6,MATCH("▽", OFFSET($B$3:$B$6,0,MATCH($K2,$B$1:$F$1,0)-1),0),1),"")

お礼

ありがとうございました。
大変助かりました。

回答No.4

kkkkkm

No2の訂正です。
▽がない場合#N/Aにならないように以下のようにしてください。
=IFERROR(INDEX($A$3:$A$6,MATCH("▽", OFFSET($B$3:$B$6,0,ROW(1:1)-1),0),1),"")

お礼

ありがとうございました。
大変助かりました。

回答No.3

msMike

添附圖參照(Excel 2019)
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

お礼

ありがとうございました。
大変助かりました。

回答No.2

kkkkkm

左の表がA1からF6までだとして、右の1で表示したいセル(画像だとM2だと思います)に以下の式を入れて下にコピーして試してみてください

=INDEX($A$3:$A$6,MATCH("▽", OFFSET($B$3:$B$6,0,ROW(1:1)-1),0),1)

お礼

ありがとうございました。
大変助かりました。

回答No.1

aokii

以下の方法でいかがでしょう。
11月がA1セルの場合、
G3セル　=A3　と入力してG6セルまで下にドラッグコピー
B7セル　=VLOOKUP("▽",B3:$G6,6,FALSE)　と入力してF7セルまで右にドラッグコピー
I2～I6セルにそれぞれ　日　月　火　水　木　と入力
J2セルに　=B7
J3セルに　=C7
J4セルに　=D7
J5セルに　=E7
J6セルに　=F7