エクセルで指定した行で条件に一致したセルの右隣のセルを取得したい

下のような名前（重複なし）に対して、名前によって把握できている情報のみがカテゴリと内容の順番で行に並んでいるデータがあるとします。
（「田中」のセルがA1です。）

[ 田中 ][ 性別 ][ 男 ][ 年齢 ][ 24 ][ 出身地 ][ 東京都 ][ 資格 ][ あり ][ 経験 ][ なし ]
[ 山田 ][ 性別 ][ 女 ][ 年齢 ][ 29 ][ 資格 ][ あり ]
[ 佐藤 ][ 性別 ][ 男 ][ 年齢 ][ 23 ][ 経験 ][ なし ]
[ 鈴木 ][ 性別 ][ 女 ][ 出身地 ][ 千葉県 ][ 経験 ][ なし ]

このようなデータを下のように表示させたいのですが、どのようにすればいいのでしょうか。
（「名前」のセルがA1です。また名前に対してカテゴリが存在しない場合は,空欄、"0"、#N/A等、情報がないことが認識できれば、いずれのデータでも構いません。）

[ 名前 ][ 性別 ][ 年齢 ][ 出身地 ][ 資格 ][ 経験 ]
[ 田中 ][ 男 ]　[ 24 ]　[ 東京都 ][ あり ][ なし ]
[ 山田 ][ 女 ]　[ 29 ]　[ 　　　 ][ あり ][ 　　 ]
[ 佐藤 ][ 男 ]　[ 23 ]　[ 　　　 ][ なし ][ なし ]
[ 鈴木 ][ 女 ]　[ ]　[ 千葉県 ][ 　　 ][ あり ]

自分なりに調べてみたのですが、名前によってカテゴリの種類や場所がバラバラの為、どうしても上手くできません。

どうぞよろしくお願い致します。

No.2 ベストアンサー 回答者： 銀鱗 回答日時： 2019/12/08 03:25

エラー系の関数とMATCH関数とINDEX関数の組み合わせで出来ると思います。

まずはエラーは無いと物としてMATCH関数とINDEX関数の組み合わせでデータを抽出してみましょう。
まず、「名前」が ”田中” の行に対して、「性別」の右のセルを抽出するということで考えます。

”田中” のセルがA1セルとするならば
まずはMATCH関数で検索する値が何列目にあるのかを調べます。
　MATCH(検索値,検査範囲,検索方法)
のようにして使いますので、
　MATCH("性別",A1:K1,0)
となります。（検索方法が ”0” なのは検索値と完全一致する値を拾うためです）
これで左から何列目に ”性別” と書かれたセルがあるかを判断できます。

次に同じ範囲でその次のセルの抽出すれば良いということになるので、
ここでINDEX関数を使います。
　INDEX(範囲,範囲の中の行番号,範囲の中の列番号)
のようにして使いますので、
　INDEX(A1:K1,1, MATCH("性別",A1:K1,0)+1 )
これでいい。
範囲を右に一つずらして
　INDEX(B1:K1,1, MATCH("性別",A1:K1,0) )
としても良いでしょうが、あとから見て分かり難くなるのでお奨めはしません。

さて、MATCH関数で対象のデータを見つけられないとエラーになります。
データが無い場合は空白にするということですので、
IFERROR関数を使って、数式がエラーの場合の処理をします。
　IFERROR(値,エラー時の値)
のようにして使いますので、
　IFERROR( INDEX(A1:K1,1, MATCH("性別",A1:K1,0)+1 ) , "")
こんな感じになります。

以上です。

・・・

あとは別のシートを参照させるなどの修正を行い、
MATCH関数の「検索値」を表の中のセルを参照させ、かつ、絶対参照と相対参照を組み合わせて、
フィルハンドルをドラッグするだけで数式をコピーしてそのまま使えるようにしましょう。

難しい事ではありません。
分からなくてもちょっと調べるだけでやり方は分かると思います。

そんなわけでマクロなんて使わなくてもOK。
マクロを使うにしてもどのような手順で処理を行うかを、自身が理解していないと呪文の羅列ですからね。
似たような問題が出てきたときに対処はできませんから。


・・・余談・・・

最後まで数式を書いていますが、この数式の修正についてのコメントで終わるのは、
質問者さん自身がこの問題をどのように考えれば対処できるかということを身に付けるためです。
ここは「代わりにやって」と ”作業依頼” をする場所ではありませんからね。
自身で対処できるようになるためのアドバイスをする場所です。

自分で対処できるようにならないと解決とは言いませんよね。
他人に代わりにやってもらうことは解決ではなく問題の先送りですから。

この回答へのお礼

ご教授頂きましたMATCH関数とINDEX関数の組み合わせで問題が解決しました。
自分なりに調べたつもりでしたが力不足で見当違いな関数で解決を求めようとしていました。
ご丁寧なご回答に重ねてお礼申し上げます！

お礼日時：2019/12/08 15:32

No.5 回答者： mike_g 回答日時： 2019/12/08 08:07

添付図参照

Sheet2 において、
１．式 =Sheet1!A1 をセル A2 に入力
２．次式を入力したセル B2 を右方４列にオートフィル
￣￣=IFERROR(INDEX(Sheet1!1:1,MATCH(B$1,Sheet1!1:1,0)+1),"")
３．範囲 A2:F2 のフィルハンドルを下方にドラッグ&ペースト

この回答へのお礼

ご回答ありがとうございましす。先のご回答にて教えていただいたMATCH関数とINDEX関数の組み合わせでかいけつ出来ました。
画像を添えて頂くご丁寧なご回答に心から感謝いたします！

お礼日時：2019/12/08 15:34

No.4 回答者： GooUserラック 回答日時： 2019/12/08 05:35

ご返事が有りませんが、とりあえずマクロを使った時のコードです。

Option Explicit

Sub Sample()

Const 元シート名 As String = "Sheet1" '実際のシート名にして下さい
Const 結果シート名 As String = "結果" '実際のシート名にして下さい
Dim シート As Worksheet
Dim カテゴリ辞書 As Object
Dim 列 As Long ' 書き出し先のカテゴリの列番号
Dim 元 As Long ' 元シートの行番号
Dim 先 As Long ' 書き出し先の行番号
Dim 番 As Long ' 元シートのカテゴリの列番号

　Set カテゴリ辞書 = CreateObject("Scripting.Dictionary")
' ↓結果シートが存在していたら結果シートを削除する処理
　For Each シート In Worksheets
　　If シート.Name = 結果シート名 Then
　　　Application.DisplayAlerts = False
　　　シート.Delete
　　　Application.DisplayAlerts = True
　　　Exit For
　　End If
　Next
' ↑結果シートが存在していたら結果シートを削除する処理
　Sheets.Add
　ActiveSheet.Name = 結果シート名
' ↓基本のカテゴリをセットする処理
　Range("A1").Value = "名前"
　Range("B1").Value = "性別"
　Range("C1").Value = "年齢"
　Range("D1").Value = "出身地"
　Range("E1").Value = "資格"
　Range("F1").Value = "経験"
' ↑基本のカテゴリをセットする処理
　For 列 = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
　　カテゴリ辞書.Add Cells(1, 列).Value, 列
　Next
　先 = 2
　Sheets(元シート名).Select
　For 元 = 1 To Cells(Rows.Count, 1).End(xlUp).Row
　　Cells(元, 1).Copy Sheets(結果シート名).Cells(先, 1)
　　番 = 2
　　Do While 番 <= Cells(元, Columns.Count).End(xlToLeft).Column
　　　If カテゴリ辞書.Exists(Cells(元, 番).Value) Then
　　　　Cells(元, 番 + 1).Copy Sheets(結果シート名).Cells(先, カテゴリ辞書.Item(Cells(元, 番).Value))
　　　Else
　　　　カテゴリ辞書.Add Cells(元, 番).Value, 列
　　　　Sheets(結果シート名).Cells(1, 列).Value = Cells(元, 番).Value
　　　　Cells(元, 番 + 1).Copy Sheets(結果シート名).Cells(先, カテゴリ辞書.Item(Cells(元, 番).Value))
　　　　列 = 列 + 1
　　　End If
　　　番 = 番 + 2
　　Loop
　　先 = 先 + 1
　Next

End Sub

' 少しだけ説明（この行以降は不要です）

'「カテゴリ辞書.Add ○, △」はカテゴリ辞書に「○」というキーと「△」という要素を登録します
'　（今回はキーにカテゴリ、要素に書き出し先の列番号をセットしています）
'「カテゴリ辞書.Exists(○)」はカテゴリ辞書のキーに「○」が登録されているか調べて、有れば「True」無ければ「False」になります
'「カテゴリ辞書.Item(○)」はカテゴリ辞書でキーが「○」の要素を返します。
'　（今回はカテゴリで書き出し先の列番号を調べています）
'「Cells(Rows.Count, ○).End(xlUp).Row」は、○番目（列番号）の列の最後の行番号になります。
'「Cells(○, Columns.Count).End(xlToLeft).Column」は、○行の最後の列番号になります。

No.3 回答者： GooUserラック 回答日時： 2019/12/08 04:59

もう一つ確認ですが、例の結果は以下になって良いですよね？

[ 名前 ][ 性別 ][ 年齢 ][ 出身地 ][ 資格 ][ 経験 ]
[ 田中 ][ 男 ]　[ 24 ]　[ 東京都 ][ あり ][ なし ]
[ 山田 ][ 女 ]　[ 29 ]　[ 　　　 ][ あり ][ 　　 ]
[ 佐藤 ][ 男 ]　[ 23 ]　[ 　　　 ][ 　　 ][ なし ]
[ 鈴木 ][ 女 ]　[ ]　[ 千葉県 ][ 　　 ][ なし ]

No.1 回答者： GooUserラック 回答日時： 2019/12/08 02:23

確認させてください

① マクロ（ＶＢＡ）を使用してもよろしいですか？
② 結果は別シートに書き出せばよろしいですか？
③ A列は名前が必ず入っている、偶数列には「カテゴリ」奇数列には左隣のカテゴリに対する「内容」が書かれている。という事でよろしいですか？
④ カテゴリは出現順に表示していけばよろしいですか？それとも書き出し先の１行目に事前に列挙しておいてそれに合わせて内容を書きただしていくのでしょうか？
前者は全てのカテゴリが必ず表示されるがカテゴリの順番が元のデータによって変わってしまいます。
後者は順番は変わりませんが追加されたカテゴリが有ると抜けてしまう事が有ります
両方の長所だけの、基本のいくつかは最初に列挙しておいてそれ以外は出現ごとに追加するという方法もあります。これでよろしいですか？

この回答へのお礼

ご返答ありがとうございます。エクセルVBAについてのご回答を別回答にて確認させて頂きました。私自身マクロの知識が不十分でしたがご回答を元に結果にたどり着くことが出来ました。感動しました。現時点では自分自身でこのようなマクロを作ることが出来ませんがVBAの勉強の必要性を感じました。本当にありがとうございました！！

お礼日時：2019/12/08 15:29