特定の文字を含むセルとそのセルの見出しを表示したい

たとえば、社宅に住んでいる「山田」さん等を見つける場合。

Ａ１の入力蘭に「山田」と入力します。
すると、
Ａ３に「社宅Ａ」、Ｂ３に「山田太郎」
Ａ４に「社宅Ｂ」、Ｂ４に「山田一郎」
Ａ５に「社宅Ｃ」、Ｂ５に「ジェームス山田」
Ａ６に「社宅Ｄ」、Ｂ６に「山田真一郎」
Ａ７に「社宅Ｅ」、Ｂ７に「内山田次郎」
という具合に表示させたいのです。
(同一名は最大５人と考えています)

このデータはデータシートに
Ａ１に「社宅Ａ」
Ａ２に「田中次郎」(入居者１)
Ａ３に「山下賢治」(入居者２)
・
・
・
というデータになっており、
Ｂ列は、Ｂ１に「社宅Ｂ」とＢ２以降に入居者名のデータがあります。

どうしたら良いのでしょうか。

質問者からの補足コメント

• 

  なぜか触っていたらできました。
  良くわからないままできました。
  少々消化仕切れていませんがありがとうございました。

  No.5の回答に寄せられた補足コメントです。 補足日時：2018/04/06 01:14

No.1 回答者： 銀鱗 回答日時： 2018/04/04 03:46

1行に１人分のデータが入力されるように元のデータシートを修正・加工する。

それを真っ先に行いましょう。

そうすれば後は楽ちんです。
がんばれ。

元のデータが検索向けの形式でないことが面倒にさせている原因ですからね。
まずはその原因を取り除きましょう。
それに、そのように修正したほうがデータが見やすいと思います。

・・・余談・・・

楽に修正・加工するにはExcelの様々な機能を知るようにしましょう。
よく分からないのであれば、コピー＆ペーストするか、一つずつ入力し直すことになります。


ゴメンね。
関数やマクロを使って一発でやりたいのでしょうが、
根本的な問題を解決しないと後々面倒になるのが目に見えていますので、このような回答になりました。
凄く面倒なことをしなければならないことは分かっていますが、問題の解決には避けて通れないことです。
がんばってください。

この回答へのお礼

激励ありがとうございました

お礼日時：2018/04/05 23:35

No.2 回答者： fujillin 回答日時： 2018/04/04 12:41

こんにちは

関数で実現しようとすると、「詰めて表示」をXY両方向に行うことになるので、かなり面倒になります。
更に、No1様の指摘にもあるように行・列の入れ替えが絡むので複雑になりますね。
質問者様がVBAを利用できるのであれば、マクロで処理をした方が簡単かと想像します。

とはいえ、以下の仮定で関数ベースで考えてみました。
・データシートはSheet1とします。
・データの範囲はひとまずA1：J20と仮定
　（扱いやすいように小さめにしてあります）

※ ひとつの式にまとめることも不可能ではないとは思いますが、式がやたらと長くなるのとややこしくなるので、作業行を利用しています。
ひとまず抽出するシートの20行目を作業用の行に設定しました。
（実際には十分離れた行にして、目障りなら非表示にしておけばよろしいかと思います）


作業行のA20セルに以下の式を設定（配列数式）
=IFERROR(SMALL(IF(COUNTIF(OFFSET(Sheet1!$A$1:$A$20,1,COLUMN(Sheet1!$A:$J)-1),"*"&$A$1&"*"),COLUMN(Sheet1!$A:$J)),COLUMN(A1)),"")

同様にA3セルに
=IF(A$20="","",INDEX(Sheet1!$A$1:$J$1,A$20))

A4セルに（配列数式）
=IFERROR(INDEX(OFFSET(Sheet1!$A$1:$A$20,1,A$20-1),SMALL(IF(COUNTIF(OFFSET(Sheet1!$A$1,ROW($1:$10),A$20-1),"*"&$A$1&"*"),ROW($1:$10)),ROW(A1))),"")

※ A20およびA4セルの式は配列数式ですので、Ctrl+Shift+Enterで確定する必要があります。

上記の式を設定した上で、次の手順で全体にコピーします。
1）A4セルの式をA4:A18にフィルコピー
2）A3：A10を選択し、左方に必要な範囲までフィルコピー


以上で、ご質問のような結果が得られると思います。
参照範囲や作業用の行は、実際に使用する環境に合わせて調整してください。

この回答へのお礼

ちょっち位置関係がわかりませんが、
ワンステップ作業をいれるということのようです。
それだとデータの可変に伴う修正が必要になりそうですね。

お礼日時：2018/04/05 23:36

No.3 回答者： fujillin 回答日時： 2018/04/04 13:01

No2です。

コピー手順の2）選択範囲の記述を間違えてしまいました。
　A3:A10を選択し　・・・誤
　A3：A20を選択し　・・・正
です。
念のため。

No.4 回答者： tom04 回答日時： 2018/04/04 14:36

こんにちは！

↓の画像のような配置で元データはSheet1にあり、Sheet2に表示するとします。
大前提として、「各社宅」に同じ姓の重複はない！とします。

Sheet2のA3セルに
=IFERROR(INDEX(Sheet1!A$1:E$1,SMALL(IF(ISNUMBER(FIND(A$1,Sheet1!A$2:E$10)),COLUMN(A$1:E$1)),ROW(A1))),"")

配列数式なのでCtrl＋Shift＋Enterで確定！（←必須★)

B3セルに
=IF(A3="","",INDEX(Sheet1!A$2:E$10,MAX(IF(ISNUMBER(FIND(A$1,OFFSET(Sheet1!A$2:A$10,,MATCH(A3,Sheet1!A$1:E$1,0)-1,,1))),ROW(A$2:A$10)-1)),MATCH(A3,Sheet1!A$1:E$1,0)))

こちらも配列数式なのでCtrl＋Shift＋Enterで確定！

A3・B3セルを範囲指定　→　B3セルのフィルハンドルで下へコピーすると
画像のような感じになります。

※　万一同社宅に同じ姓の人が複数いる場合は関数ではかなり厄介になると思います。
VBAであれば可能です。m(_ _)m

この回答へのお礼

ありがとうございます。

回答者様と同じ結果になりました。

ただ、仰るとおり同じ社宅に同じ名前の人がいる場合はその人数分が表示できますが、
後にある名前の人が複数登場してしまいます。
たとえば、社宅Ａに森田和義の次の行に小山田獏、前山田ヒャダインが入居した場合、
シート２の表示件数を仮に１０行にした場合、社宅Ａが３行・前山田ヒャダインが３行と表示されてしまいます。

兄弟で同じ社宅の別々の部屋に住む場合など考えられますので、
やはり、マクロが必要になってくるのでしょうか。

お礼日時：2018/04/05 23:44

No.5 ベストアンサー 回答者： tom04 回答日時： 2018/04/06 00:08

No.4です。

やはり1列内に複数ヒットする場合があるのですね。
となると手っ取り早くVBAではどうでしょうか？

配置は前回の画像通りとします。
↓のコードをSheet2のシートもシュールにしてみてください。

Private Sub Worksheet_Change(ByVal Target As Range) '//この行から//
Dim i As Long, j As Long
Dim lastRow As Long, cnt As Long
If Target.Address = "$A$1" Then
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
If lastRow > 2 Then
Range(Cells(3, "A"), Cells(lastRow, "B")).ClearContents
End If
If Target <> "" Then
With Worksheets("Sheet1")
cnt = 2
For j = 1 To .Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To .Cells(Rows.Count, j).End(xlUp).Row
If InStr(.Cells(i, j), Target) > 0 Then
cnt = cnt + 1
Cells(cnt, "A") = .Cells(1, j)
Cells(cnt, "B") = .Cells(i, j)
End If
Next i
Next j
End With
End If
End If
End Sub '//この行まで//

これでA1セルにデータを色々入れてみてください。m(_ _)m

この回答へのお礼

ありがとうございます。

でも、私がマクロをよく分かっていなくて。
ALT+F11で開いたところにコピペして、xlsmで保存してからＡ１に入力すればいいのですよね？
前回回答していただいた関数はそのままで良いのですか？削除するのですか？

前回の関数をそのままにしてＡＬＴ＋Ｆ１１に入ってからコピペして保存して実行しましたが、
結果は変わりませんでした。

手順を教えて下さい。

お礼日時：2018/04/06 00:54

No.6 回答者： fujillin 回答日時： 2018/04/06 01:06

No2です

＞ワンステップ作業をいれるということのようです。
人が行う「作業」ではありません。
計算途中の値を一旦保管（表示）するためのセルを使用するという意味で、式が簡単にできるので用いています。
他の方も触れていますが、No2の回答は複数の該当者がいる場合も想定した関数式になっていますので、ひとつの式に纏めると複雑になりすぎるため、計算途中の値を一旦保管するようにして、式を分割することで複雑化を回避しているのです。
もちろん、計算はエクセルが全て自動的に行います。

＞それだとデータの可変に伴う修正が必要になりそうですね。
データが変わっても式そのものは変わりません。
表の構造が変わるとかあるいはデータの範囲を変えるとかするのであれば、式の修正が必要になりますが、それは大抵の関数式でも同様のことと思います。

この回答へのお礼

ご説明されている内容がシート１なのかシート２なのか伝わっていないのです。
熱弁はありがたいのですが、どの部分がどこなど、○は×シートのA5(仮)など、
具体的にお願いしたいのです。

お礼日時：2018/04/06 01:26

No.7 回答者： tom04 回答日時： 2018/04/06 08:13

No.4・5です。

＞なぜか触っていたらできました。

余計なお世話かもしれませんが・・・
No.5のVBAはチェンジイベントなのでシートモジュールにする必要があります。

①　Excel画面左下のシート見出し（「Sheet1」・「Sheet2」・・・となっているところ）の「Sheet2」上で右クリック。
②　ALT＋F11キー　→　VBE画面が表示されますので、その左側にある「Sheet2」をダブルクリック。

上記①、②いずれかの操作をするとVBE画面上にカーソルが点滅している状態になります。
これで「Sheet2」のシートモジュール記載が可能になります。

そこへ前回のコードをこのそのままコピー＆ペーストしExcel画面に戻る（VBE画面を閉じる）

あとはSheet2のA1セルに色々データをいれてみてください。
データ変更があるたびにSheet2に前回アップした画像のように表示されるはずです。m(_ _)m

この回答へのお礼

ありがとうございました。
なんとかやってみます。
マクロ出来るようになりたいのですが、機会と勉強方法が分からず困っています。

お礼日時：2018/04/10 00:11