エクセル シートを指定した条件検索

質問

エクセルの条件検索について，よろしくお願いいたします。
社員のスケジュール管理を行います。

4月～3月のシート名のシートと，検索結果用のシートがあります。
4月のシートは添付画像のような構成となっています。

検索結果用のシートの2行目に検索条件を入力し，4行目以降に結果を抽出したいです。
　　A　　　　　　B　　　　　　　C
１　シート名　　業務１　　　　業務２
２　4月　　　　　会議　　　　　係

４　日付　　　　2013/4/1　　2013/4/3
５　参加者１　　AA　　　　　　KK
６　参加者２　　DS　　　　　　KA
７　参加者３　　BB
８　参加者４　　CC
９　参加者５　　AS

日付順に，該当する結果を全て表示したいのですが，VBAは使えません。
検索条件は，「会議」のみで検索する場合もあります。

良い方法がわかりませんでした。ご教授よろしくお願いいたします。

回答No.2

tom04 · ベストアンサー

No.1です。

＞VBAは職場で管理できるスタッフがいないため・・・

とありましたので、無理やり関数でやってみました。
とりあえず各シート100行目まで対応できるようにしています。

Sheetを1枚追加して作業用のSheetとします（Sheet名は「作業用」としています）

↓の画像で下側が作業用のSheetになります。
A3セルに
=IF(COUNTA(INDIRECT(結果!A$2&"!A:A"))<ROW(A1)+1,"",INDIRECT(結果!A$2&"!A"&ROW()))
これをオートフィルでずぃ～～～！っと下へコピー！

B2セルに
=INDIRECT(結果!$A2&"!"&ADDRESS(1,2*COLUMN(A1)))
これを列方向に31日分（AF列になると思います）までオートフィルでコピー！

B1セルに
=IF(COUNTIF(B3:B100,1),COLUMN(),"")
としてこれもAF1までオートフィルでコピー！

B3セルに
=IF(COUNTBLANK(結果!$B$2:$C$2)=2,"",IF(結果!$C$2="",IF(COUNTIF(INDIRECT(結果!$A$2&"!"&ADDRESS(ROW(),2*COLUMN(A1),4)&":"&ADDRESS(ROW(),2*COLUMN()-1,4)),"*"&結果!$B$2&"*"),1,""),IF(OR(COUNTIF(INDIRECT(結果!$A$2&"!"&ADDRESS(ROW(),2*COLUMN(A1),4)&":"&ADDRESS(ROW(),2*COLUMN()-1,4)),"*"&結果!$B$2&"*"),COUNTIF(INDIRECT(結果!$A$2&"!"&ADDRESS(ROW(),2*COLUMN(A1),4)&":"&ADDRESS(ROW(),2*COLUMN()-1,4)),"*"&結果!$C$2&"*")),1,"")))
という数式を入れ列・行方向にオートフィルでコピー！

これで作業用Sheetの準備は完了です。

最後に「結果」Sheetになります。
B4セルに
=IF(COUNT(作業用!1:1)<COLUMN(A1),"",INDEX(作業用!2:2,SMALL(作業用!1:1,COLUMN(A1))))
これを列方向にオートフィルでずぃ～～～！っとコピー！

A5セルに
=IF(COUNTIF(B5:AF5,"*?"),"参加者"&ROW(A1),"")
下へコピー！

最後にB5セルに
=IF(B$4="","",IF(COUNTIF(OFFSET(作業用!$A$1:$A$100,,MATCH(B$4,作業用!$2:$2,0)-1,,1),1)<ROW(A1),"",INDEX(作業用!$A$1:$A$100,SMALL(IF(OFFSET(作業用!$A$1:$A$100,,MATCH(B$4,作業用!$2:$2,0)-1,,1)=1,ROW($A$1:$A$100)),ROW(A1)))))

これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！

この画面からコピー＆ペーストする場合はB5セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

これを列・行方向にオートフィルで画像のような感じになります。

※　最初に書いたように数式のデータ範囲は各シートのAF列までの100行までとしています。m(_ _)m

お礼

ありがとうございました。

これを上手く使っていきたいと思います。

回答No.1

tom04

こんばんは！
何とか関数で！とやりかけてみましたが、ギブアップです。

お望みでないVBAになってしまいますが、一例です。

Alt＋F11キー　→　メニュー　→　標準モジュール　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub 表示() 'この行から
Dim i As Long, j As Long, cnt As Long, str As String
Dim c As Range, r As Range, myArea As Range
Dim wS As Worksheet, wS1 As Worksheet

Set wS1 = Worksheets("結果")
If wS1.Cells(2, 2) = "" Then
MsgBox "検索データが未入力です。"
wS1.Cells(2, 2).Select
Exit Sub
ElseIf wS1.Cells(2, 3) = "" Then
wS1.Cells(2, 3) = "条件入力"
End If
str = wS1.Cells(2, 1)
Set wS = Worksheets(str)
i = wS1.Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
If i > 3 Then
wS1.Rows(4 & ":" & i).ClearContents
wS1.Cells(4, 1) = "日付"
End If
cnt = 1
For j = 2 To wS.Cells(1, Columns.Count).End(xlToLeft).Column Step 2
With Range(wS.Columns(j), wS.Columns(j + 1))
Set c = .Find(what:=wS1.Cells(2, 2), LookIn:=xlValues, lookat:=xlPart)
Set r = .Find(what:=wS1.Cells(2, 3), LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Or Not r Is Nothing Then
cnt = cnt + 1
wS1.Cells(4, cnt) = wS.Cells(1, j)
End If
End With
For i = 3 To wS.Cells(Rows.Count, 1).End(xlUp).Row
Set myArea = wS.Cells(i, j).Resize(1, 2)
If Not myArea.Find(what:=wS1.Cells(2, 2), LookIn:=xlValues, lookat:=xlPart) Is Nothing Or _
Not myArea.Find(what:=wS1.Cells(2, 3), LookIn:=xlValues, lookat:=xlPart) Is Nothing Then
wS1.Cells(Rows.Count, cnt).End(xlUp).Offset(1) = wS.Cells(i, 1)
End If
Next i
Next j
wS1.Rows(4).NumberFormatLocal = "yyyy/m/d"
cnt = 0
For i = 5 To wS1.UsedRange.Rows.Count
cnt = cnt + 1
wS1.Cells(i, 1) = "参加者" & cnt
Next i
Application.ScreenUpdating = True
End Sub 'この行まで

＞検索条件は，「会議」のみで検索する場合もあります。
とありますので、「結果」SheetのB2またはC2セルのデータが含まれるものを表示するようにしています。
すなわちC3セルが空白の場合は空白セルが条件に含まれてしまいますので、
敢えて「条件入力」と表示させています。

関数でできる方法があればごめんなさいね。m(_ _)m

お礼

早速の回答ありがとうございます。
実行してみたところ，思った通りの動作ができました！
できればこのまま使いたいのですが，VBAは職場で管理できるスタッフがいないため，採用には至りません。残念・・・
ですが，この回答に感謝いたします。