下のような名前(重複なし)に対して、名前によって把握できている情報のみがカテゴリと内容の順番で行に並んでいるデータがあるとします。
(「田中」のセルがA1です。)
[ 田中 ][ 性別 ][ 男 ][ 年齢 ][ 24 ][ 出身地 ][ 東京都 ][ 資格 ][ あり ][ 経験 ][ なし ]
[ 山田 ][ 性別 ][ 女 ][ 年齢 ][ 29 ][ 資格 ][ あり ]
[ 佐藤 ][ 性別 ][ 男 ][ 年齢 ][ 23 ][ 経験 ][ なし ]
[ 鈴木 ][ 性別 ][ 女 ][ 出身地 ][ 千葉県 ][ 経験 ][ なし ]
このようなデータを下のように表示させたいのですが、どのようにすればいいのでしょうか。
(「名前」のセルがA1です。また名前に対してカテゴリが存在しない場合は,空欄、"0"、#N/A等、情報がないことが認識できれば、いずれのデータでも構いません。)
[ 名前 ][ 性別 ][ 年齢 ][ 出身地 ][ 資格 ][ 経験 ]
[ 田中 ][ 男 ] [ 24 ] [ 東京都 ][ あり ][ なし ]
[ 山田 ][ 女 ] [ 29 ] [ ][ あり ][ ]
[ 佐藤 ][ 男 ] [ 23 ] [ ][ なし ][ なし ]
[ 鈴木 ][ 女 ] [ ] [ 千葉県 ][ ][ あり ]
自分なりに調べてみたのですが、名前によってカテゴリの種類や場所がバラバラの為、どうしても上手くできません。
どうぞよろしくお願い致します。
No.2ベストアンサー
- 回答日時:
エラー系の関数と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関数の組み合わせで問題が解決しました。
自分なりに調べたつもりでしたが力不足で見当違いな関数で解決を求めようとしていました。
ご丁寧なご回答に重ねてお礼申し上げます!
No.5
- 回答日時:
添付図参照
Sheet2 において、
1.式 =Sheet1!A1 をセル A2 に入力
2.次式を入力したセル B2 を右方4列にオートフィル
 ̄ ̄=IFERROR(INDEX(Sheet1!1:1,MATCH(B$1,Sheet1!1:1,0)+1),"")
3.範囲 A2:F2 のフィルハンドルを下方にドラッグ&ペースト
ご回答ありがとうございましす。先のご回答にて教えていただいたMATCH関数とINDEX関数の組み合わせでかいけつ出来ました。
画像を添えて頂くご丁寧なご回答に心から感謝いたします!
No.4
- 回答日時:
ご返事が有りませんが、とりあえずマクロを使った時のコードです。
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
- 回答日時:
もう一つ確認ですが、例の結果は以下になって良いですよね?
[ 名前 ][ 性別 ][ 年齢 ][ 出身地 ][ 資格 ][ 経験 ]
[ 田中 ][ 男 ] [ 24 ] [ 東京都 ][ あり ][ なし ]
[ 山田 ][ 女 ] [ 29 ] [ ][ あり ][ ]
[ 佐藤 ][ 男 ] [ 23 ] [ ][ ][ なし ]
[ 鈴木 ][ 女 ] [ ] [ 千葉県 ][ ][ なし ]
No.1
- 回答日時:
確認させてください
① マクロ(VBA)を使用してもよろしいですか?
② 結果は別シートに書き出せばよろしいですか?
③ A列は名前が必ず入っている、偶数列には「カテゴリ」奇数列には左隣のカテゴリに対する「内容」が書かれている。という事でよろしいですか?
④ カテゴリは出現順に表示していけばよろしいですか?それとも書き出し先の1行目に事前に列挙しておいてそれに合わせて内容を書きただしていくのでしょうか?
前者は全てのカテゴリが必ず表示されるがカテゴリの順番が元のデータによって変わってしまいます。
後者は順番は変わりませんが追加されたカテゴリが有ると抜けてしまう事が有ります
両方の長所だけの、基本のいくつかは最初に列挙しておいてそれ以外は出現ごとに追加するという方法もあります。これでよろしいですか?
ご返答ありがとうございます。エクセルVBAについてのご回答を別回答にて確認させて頂きました。私自身マクロの知識が不十分でしたがご回答を元に結果にたどり着くことが出来ました。感動しました。現時点では自分自身でこのようなマクロを作ることが出来ませんがVBAの勉強の必要性を感じました。本当にありがとうございました!!
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- C言語・C++・C# c言語の問題です 2 2023/07/21 10:51
- PHP PHPでCSVを出力するさいに、ループの中で前の行の値を変更したい 1 2022/10/27 14:21
- Excel(エクセル) Excelマクロ 差分抽出の方法が知りたいです。 2 2023/03/07 13:25
- 転職 長く続けられる好条件の求人でしょうか? 3 2023/07/12 18:45
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
- Excel(エクセル) EXCEL 関数を教えてください。(A列の同じ値が複数ある場合vlookupで出来ますか) 4 2022/12/07 20:54
- Excel(エクセル) 【関数】【マクロ】データの転記の方法について 2 2023/07/26 15:22
- Excel(エクセル) 【VBA】指定フォルダに格納中のテキストファイルをエクセルで処理し結果のエクセルを新規フォルダに保存 1 2022/03/25 14:19
- Visual Basic(VBA) Excel VBA 最終行を取得しVlookup関数をコピーする方法をコーディングで教えてください。 3 2023/05/11 13:14
- Excel(エクセル) 【エクセル」 特定のセルで条件抽出した列を、別シートに上から詰めて表示したい。 8 2022/04/08 16:00
このQ&Aを見た人はこんなQ&Aも見ています
-
好きな人を振り向かせるためにしたこと
大好きな人と会話のきっかけを少しでも作りたい、意識してもらいたい…! 振り向かせるためにどんなことをしたことがありますか?
-
スマホに会話を聞かれているな!?と思ったことありますか?
スマートフォンで検索はしてないのに、友達と話していた製品の広告が直後に出てきたりすることってありませんか? こんな感じでスマホに会話を聞かれているかも!?と思ったエピソードってありますか?
-
これが怖いの自分だけ?というものありますか?
人によって怖いもの(恐怖症)ありませんか? 怖いものには、怖くなったきっかけやエピソードがあって聞いてみるとそんな感覚もあるのかと新しい発見があって面白いです。
-
前回の年越しの瞬間、何してた?
いよいよ2025年がやってきますね。 年越しのスタイルは人それぞれ。 2024年を迎える瞬間は何をしていましたか?
-
店員も客も斜め上を行くデパートの福袋
シュールを通り越して店員も客も斜め上を行くデパートの福袋に入ってそうなものを教えて下さい。 よかったらレビューもしてください。
-
ある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?
Excel(エクセル)
-
エクセルで条件に一致したセルの隣のセルを取得したい
その他(Microsoft Office)
-
Excel 表から値をさがして隣のセルの値を返す
Windows Vista・XP
-
-
4
エクセルで条件に一致したベツシートのセルの隣のセルを取得したい
Excel(エクセル)
-
5
excelの特定のセルの隣のセル指定について
その他(Microsoft Office)
-
6
複数の候補列から、検索値と一致するものの隣セルを反映したい
Excel(エクセル)
-
7
Excel検索した値の一つ右のセルの内容を返す関数
その他(Microsoft Office)
-
8
自分の左隣のセル
Excel(エクセル)
-
9
エクセル 同じ値を探して隣の数値をコピーする
Excel(エクセル)
-
10
[初心者です]VBAで指定列からAを検索し、発見したら隣のセルに値0を入れるマクロ。
Access(アクセス)
-
11
あるセルに特定の文字列を打つと、他のセルに決められた文字が自動入力するように
Excel(エクセル)
-
12
EXcel HLOOKUP関数の検索で得た値の1つ右隣りのセルの値を求める関数
その他(Microsoft Office)
-
13
エクセルで隣(右or左)のセルと同じ文字色にしたい
Excel(エクセル)
-
14
Excelで、検索してヒットしたセルの隣のセルに値を入力したい
Excel(エクセル)
-
15
Excel ○のついた右隣のセルの種類をカウントするには?
その他(Microsoft Office)
-
16
完全一致したら代入するマクロを教えてください
Excel(エクセル)
-
17
VBAで条件に一致するセルの隣の値をまとめる方法
Excel(エクセル)
-
18
Excel マクロ VBA 別シートのセルを検索し、該当するセルの右にあるセルを入力させる方法 s
Visual Basic(VBA)
-
19
【Excel関数】値が合致するセルの隣のセルを表示させたい
Excel(エクセル)
-
20
【VBA】特定列に文字が入っていたらそのセル行をコピーしてマスターブックの同じ行に貼り付けたい
その他(Microsoft Office)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・「みんな教えて! 選手権!!」開催のお知らせ
- ・漫画をレンタルでお得に読める!
- ・「黒歴史」教えて下さい
- ・2024年においていきたいもの
- ・我が家のお雑煮スタイル、教えて下さい
- ・店員も客も斜め上を行くデパートの福袋
- ・食べられるかと思ったけど…ダメでした
- ・【大喜利】【投稿~12/28】こんなおせち料理は嫌だ
- ・前回の年越しの瞬間、何してた?
- ・【お題】マッチョ習字
- ・モテ期を経験した方いらっしゃいますか?
- ・一番最初にネットにつないだのはいつ?
- ・好きな人を振り向かせるためにしたこと
- ・【選手権お題その2】この漫画の2コマ目を考えてください
- ・2024年に成し遂げたこと
- ・3分あったら何をしますか?
- ・何歳が一番楽しかった?
- ・治せない「クセ」を教えてください
- ・【大喜利】【投稿~12/17】 ありそうだけど絶対に無いことわざ
- ・【選手権お題その1】これってもしかして自分だけかもしれないな…と思うあるあるを教えてください
- ・集合写真、どこに映る?
- ・自分の通っていた小学校のあるある
- ・フォントについて教えてください!
- ・これが怖いの自分だけ?というものありますか?
- ・スマホに会話を聞かれているな!?と思ったことありますか?
- ・それもChatGPT!?と驚いた使用方法を教えてください
- ・見学に行くとしたら【天国】と【地獄】どっち?
- ・これまでで一番「情けなかったとき」はいつですか?
- ・この人頭いいなと思ったエピソード
- ・あなたの「必」の書き順を教えてください
- ・10代と話して驚いたこと
- ・14歳の自分に衝撃の事実を告げてください
- ・人生最悪の忘れ物
- ・あなたの習慣について教えてください!!
- ・都道府県穴埋めゲーム
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ファイル名の変更
-
vba Excelのタブをプログラムか...
-
エクセルでセルに入力する前は...
-
Excelで項目の種類ごとに番号を...
-
エクセルを使ってQRコードを作...
-
エクセルのセルをクリックする...
-
Excelファイルを開くと私だけVA...
-
【Excel】 1つのセルの日にちを...
-
① 【Excel】チェックボックス E...
-
Excel for MacでFEPが勝手に切...
-
【マクロ】複数の日付データをY...
-
カーソルを合わせてる時のみ行...
-
エクセルでデータを消して保存...
-
文字列1"文字列2"文字列3を文字...
-
エクセルで作った表が印刷する...
-
エクセルで、数字ではない値(...
-
excelVBAについて。
-
最高値の日付を抽出する方法
-
【VBA】使ってたクエリの接続を...
-
今まで文字化けなく開けていたc...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで、数字ではない値(...
-
Excel いい方法教えてください。
-
納期順に勝手に並べ替えられる...
-
エクセルで作成した書類の印刷...
-
Excel初心者です、Excelの日付...
-
エクセルで作った表が印刷する...
-
実務の処理について。
-
AM8:30から翌朝8:30まで勤務す...
-
Excelのデータの入力規則の問題...
-
Excelの罫線を消す方法
-
桁をセルで区切って計算をした...
-
スプレッドシート(Excelでも良...
-
VLOOKUP関数で複数条件を設定に...
-
Excel初心者です。 Excelでやり...
-
エクセルでAのセルに「家電」と...
-
ファイルとフォルダの移動につ...
-
XMLHTTP60で前日のデータが取れ...
-
ファイルパスについて。
-
エクセルの数式について教えて...
-
スプレッドシートで適切な条件...
おすすめ情報