excelクロス抽出を一覧表示

質問

office2007、XPです。
エクセルでデータシートから別シートにクロス抽出した一覧を作成したいです。

データシートには以下のようなデータがあるとします。実際のデータはもっと多いのですが

地域　登録番号　氏名　日本語　英語　北京語　広東語　韓国朝鮮語　スペイン語　ポルトガル語・・・・
横浜　　01 Aさん　母語　流暢　日常　できない　できない　できない　できない・・・・
川崎　　02　　　Bさん　流暢　少し　母語　できない　できない　できない　できない・・・・
東京　　03 Cさん　日常　少し　できない　できない　母語　少し　できない・・・・
相模原　04 Dさん　流暢　できない　できない　できない　できない　できない　母語・・・・

ここから言語名とレベルで抽出して、地域、登録番号、氏名だけの一覧を作成したいのです。

たとえば“日本語””流暢”で抽出して

地域　登録番号　氏名
川崎　　02　　　Bさん
相模原　04 Dさん

さらに、同一シートに行を１５行くらいずつずらして抽出先をあらかじめ設定しておき
３つの別シートから同一条件で抽出して同一シートに表示させたいです。

マクロでもかまいません。
関数やマクロでいろいろためしたのですが、
どうしてもうまくいきません。
お知恵を拝借させてください。

回答No.1

MackyNo1 · ベストアンサー

関数で対応したい場合は、以下のような数式をA11セルに入力し右方向及び下方向にオートフィルしてください(添付画像参照）。

=INDEX(A:A,SMALL(INDEX((INDEX($A$2:$J$5,,MATCH($E$10,$A$1:$J$1,0))<>$E$11)*10000+ROW($A$2:$J$5),),ROW(A1)))&""

ただし、表示データ数が多い場合は、再計算に時間がかかるのでお勧めできません。

一般操作で対応するなら、オートフィルタでデータを絞り込み、この絞り込んだデータをコピー貼り付けすれば、ご希望のデータの編集ができます。
マクロで対応したい場合は、この操作を（操作上のノウハウが少し必要ですが）、「マクロの記録」でコードを記録することで対応することができます。
マクロで対応する場合は、どの部分がわからないのか提示してください。

お礼

お返事が遅くなって申し訳ございません。
ご回答ありがとうございます。
試してみます。
またご報告いたします。

補足

ありがとうございました。
indexを重ねてmatchを入れ込めばいいんですね。
思いつきませんでした。

何度も何度もくり返すので、
関数またはマクロで設定してしまいたかったのです。

簡単にできました。
本当に助かりました。
大変勉強になりました。

回答No.2

tom04

こんばんは！
VBAでの一例です。

＞同一シートに行を１５行くらいずつずらして抽出先をあらかじめ設定しておき・・・

とありますが、仮にすべてのSheetで15名以上の場合にも対応できるように
余計なお世話かもしれませんが↓の画像で上側がSheet1にしてみました。
画像のように列方向に表を作っておくと何名でも対応できると思います。

画像の下側がSheet2以降のセル配置だとします。

Alt+F11キー　→　画面左下の「This Workbook」をダブルクリック　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

※　Sheet1のSheet名はどんな名前でもかまいませんが、Sheet見出しの一番左側に配置しておきます。

Sub test() 'この行から
Dim i, j, k, L As Long
Dim ws1 As Worksheet
Set ws1 = Worksheets(1)
Application.ScreenUpdating = False
i = ws1.UsedRange.Rows.Count
If i > 4 Then
ws1.Rows(5 & ":" & i).ClearContents
End If
For j = 1 To ws1.Cells(2, Columns.Count).End(xlToLeft).Column Step 3
For k = 2 To Worksheets.Count
For i = 2 To Worksheets(k).Cells(Rows.Count, 1).End(xlUp).Row
L = WorksheetFunction.Match(ws1.Cells(2, j), Worksheets(k).Rows(1), False)
If Worksheets(k).Cells(i, L) = ws1.Cells(2, j + 1) Then
With ws1.Cells(Rows.Count, j).End(xlUp).Offset(1)
.Value = Worksheets(k).Cells(i, 1)
With .Offset(, 1)
.Value = Worksheets(k).Cells(i, 2)
.NumberFormatLocal = "00"
End With
.Offset(, 2) = Worksheets(k).Cells(i, 3)
End With
End If
Next i
Next k
Next j
Application.ScreenUpdating = True
End Sub 'この行まで

※　Sheet1のセル配置は画像通りでないとちゃんと表示してくれません。

参考になりますかね？m(_ _)m

お礼

お返事が遅くなって申し訳ございません。
ご回答ありがとうございます。
試してみます。
またご報告いたします。

補足

実は、横ではなく、縦にずらしたかったのです。
このマクロは別データで使わせていただきます。
とても勉強になりました。
どうもありがとうございました。