EXCEL VLOOKUPでできませんか？

質問

はじめて質問をするのでわかりづらかったらすみません。
　　　　A　　　B　　　C　　　D　　（列）
1.　　　　　名前　　●●
2.　　5月3日　150
3.　　5月8日　200
4.
5.　　　合計　350
6　
7.　　　　　名前　　××
8.　　5月2日　200
9.　　　合計　200
10.（上記と同じように50名ほど以下続く）
11.　　　
(行)　

上記のようなエクセルの表（フォームは定形で変更できず）
で　NAMEの横の個人名と合計の横にある数字（金額）を抜き出したいです。フォームとしては下記のようにするためには何か抽出するのに良い関数はありませんか？
名前　合計（万円）
●●　350　
××　200　
△△　300
個人名はC列、合計金額はB列できまっています。何行がきまっていません。
行に記載されている日付は、3日出勤の人もいれば5日出勤の人もいますので何行目という指定はできません。「名前の次にくる合計の隣のセルを抽出する」という関数がくめればいいのかと思うのですが、頭が固くうまくできません。もっとシンプルにできそうなきもしますし。
これを週次でやっていきたいので、固定の関数を入れたいのですが、
うまく思いかびません。何かあればお知らせいただければ幸いです。

回答No.3

a987654

関数だけでやる方法(ただし作業列を使います）と、ＶＢＡを書きます。
作業列はいや、ＶＢＡは・・・の人だったら流して下さい。
関数の方法
Sheet1に元になるデータSheet2に抽出および作業列を当てます。
１．Sheet2のＤ１セルに　=COUNTIF(Sheet1!A$1:A1,"名前")
　　Sheet2のＥ１セルに　=COUNTIF(Sheet1!A$1:A1,"合計")
　　Ｄ１，Ｅ１をＤ２，Ｅ２にオートフィル
２．Sheet2のＦ１，Ｇ１セルにそれぞれ　=D1、=E1
　　Sheet2のＦ２セルに　=IF(D2=D1,0,$D2)
　　Sheet2のＧ２セルに　=IF(E2=E1,0,$D2)
３．Sheet2のＨ１セルに　
　　　=IF(F1>0,OFFSET(Sheet1!$A$1,ROW()-1,1),"")
　　Ｈ１セルをＩ１セルにオートフィル
　　Ｈ１、Ｉ１セルをＨ２，Ｉ２にオートフィル
４．Ｄ２～Ｉ２セルをSheet1の最終行と同じ行以上（後にデータ
　　が増えても対応できる用にしておく）までオートフィル
５．Sheet2のＡ１セルに（４．のテーブルを3000行に想定しています）
=IF(ISNA(VLOOKUP(ROW(),F1:H3000,3,)),"",VLOOKUP(ROW(),F1:H3000,3,))
　　Sheet2のＢ１セルに　
=IF(ISNA(VLOOKUP(ROW(),G1:I3000,3,)),"",VLOOKUP(ROW(),G1:I3000,3,))
　　Ａ１，Ｂ１セルを１００行までオートフィル（最大１００人を想定）
１～５で結果が出るはずです。
なお、
ａ．元データに１行目が"名前"でない場合の対応はしてあります。
ｂ．"名前"～"名前"の間に"合計"がない場合はＢ列を空白
ｃ．"名前"～"名前"の間に"合計"が複数ある場合は最初の”合計”を
　　表示しその他は無視

ＶＢＡの方法
Sheet1に元になるデータSheet3に抽出データの表に当てます。
１００人、３０００行の対応とし、関数で述べたａ．ｂ．ｃ．の
条件は同じにしてあります。
"名前"～"名前"の間隔５０行以上空いたらブレイクする様にしています。
下記で動作確認しましたのでベタコピーでいけると思います。

Sub Macro1()
Dim namae$(100), goukei_f(100), goukei(100)
For i = 1 To 100
namae$(i) = ""
goukei_f(i) = 0
goukei(i) = 0
Next i
'
Sheets("Sheet1").Select
ii = 0
i_sv = 0
For i = 1 To 3000
If i > i_sv + 50 Then Exit For
If Cells(i, 1) = "名前" Then
i_sv = i
ii = ii + 1
namae$(ii) = Cells(i, 2)
For j = i + 1 To i + 50
If Cells(j, 1) = "名前" Then Exit For
If Cells(j, 1) = "合計" Then
goukei_f(ii) = ii
goukei(ii) = Cells(j, 2)
Exit For
End If
Next j
End If
Next i
'
Sheets("Sheet3").Select
Range("A1:B100").Select
Selection.ClearContents
For i = 1 To 100
Cells(i, 1) = namae$(i)
If goukei_f(i) > 0 Then Cells(i, 2) = goukei(i)
Next i
'
End Sub

以上参考まで

お礼

早々にご回答いただいたにもかかわらず、ご連絡が遅くなりすみません。VBAがわからないので、組むことができませんが、エクセルの関数のほうでなんとかやってみようと思っているのですが、すでにオートフィルがわからず、つまずいています。良い勉強になりますので、やってみます。データ分析、抽出方法をどのようにするか今悩んでいますが、なんとかやってみます。

本当にありがとうございます。

回答No.2

ts3m-ickw

No.1です。名前→合計の順が確定ならforループを2回も回す必要はありませんでした。
あとマクロ名が不適切だったようなので直してみました。走らせる前に回答しちゃいけませんね。

Sub tally()
Dim s1 As Worksheet
Dim s2 As Worksheet
Set s1 = Worksheets("sheet1")
Set s2 = Worksheets("sheet2")

k = 1
For i = 1 To s1.Cells.SpecialCells(xlLastCell).Row
If s1.Cells(i, "C") = "名前" Then
s2.Cells(k, "A") = s1.Cells(i, "D")
End If
If s1.Cells(i, "B") = "合計" Then
s2.Cells(k, "B") = s1.Cells(i, "C")
k = k + 1
End If
Next

End Sub

さっき書き忘れましたが、質問を投げるカテゴリがWindows→WindowsXPになっています。
ソフトウェア→Office系ソフトに投げたほうが適切だったでしょう。

回答No.1

ts3m-ickw

vlookup関数では不適切だと思います。
いろいろ考えてみましたが、名前と合計が一対一で対応しているのであれば簡単なVBAマクロを組んで実行した方が早そうです。
例えばこんなの↓

Sub debug()
Dim s1 As Worksheet
Dim s2 As Worksheet
Set s1 = Worksheets("sheet1")
Set s2 = Worksheets("sheet2")

k = 1
For i = 1 To s1.Cells.SpecialCells(xlLastCell).Row
If s1.Cells(i, "C") = "名前" Then
s2.Cells(k, "A") = s1.Cells(i, "D")
k = k + 1
End If
Next

k = 1
For i = 1 To s1.Cells.SpecialCells(xlLastCell).Row
If s1.Cells(i, "B") = "合計" Then
s2.Cells(k, "B") = s1.Cells(i, "C")
k = k + 1
End If
Next

End Sub

Sheet1を上から最終行まで、C欄に「名前」と入っていたらD欄をSheet2のA欄に並べ、
Sheet1を上から最終行まで、B欄に「合計」と入っていたらC欄をSheet2のB欄に並べるという処理をします。
単純ですが、ご要望の出力は得られるかと。