男女別の名列表をつくりたい

質問

エクセルで名列表をつくっています。

A列に1から40までの通し番号、B列に男女、C列に名前が入っている「名簿」シートがあります。
この「名簿」を元にして、男女別の「名列表」を別のシートに作りたいと思います。
「名列表」の方はA列に1からの通し番号（「名簿」のA列の数字とは関係ありませんので
並べ替えの必要はありません。）、B列に名前がはいります。
これが男女にわけられた２つの表（一つのシート内でかまいません）となるようにしたいです。

ただ、並べ替え等で男女別に分けるのではなく、元になる「名簿」を訂正すると自動的に
「名列表」も訂正されるというものがいいのです。

簡単な方法を教えてください。

回答No.3

taisuke555 · ベストアンサー

簡単な方法？でもないですが、関数で作る方法です。

名簿の１行目、名列表の１行目は項目名が入るものとして
名列表に以下の式を入力（C列を１列お借りします。確認後、非表示にして下さい。）
（男）
A2セル:=IF(ISNA(B2),"",1)
B2セル:=IF(ISNA(C2),"",OFFSET(名簿!$A$2,C2-1,2))
C2セル:=MATCH("男",名簿!$B$2:$B$65536,FALSE)

A3セル:=IF(ISNA(B3),"",A2+1)
B3セル:=IF(ISNA(C3),"",OFFSET(名簿!$A$2,C3-1,2))
C3セル:=MATCH("男",OFFSET(名簿!$B$2,C2,0):名簿!$B$65536,FALSE)+C2

４行目以降は３行目をコピー貼り付け（何行でも好きなだけ）
で試してみてください。

名簿シートの男女は40行固定ではなく、B列全てを対象にしています。

（女）A2～C3をE2～G3にコピーしてG列の"男"を"女"に変更する
E2セル:=IF(ISNA(G2),"",1)
F2セル:=IF(ISNA(G2),"",OFFSET(名簿!$A$2,G2-1,2))
G2セル:=MATCH("女",名簿!$B$2:$B$65536,FALSE)

E3セル:=IF(ISNA(G3),"",E2+1)
F3セル:=IF(ISNA(G3),"",OFFSET(名簿!$A$2,G3-1,2))
G3セル:=MATCH("女",OFFSET(名簿!$B$2,G2,0):名簿!$B$65536,FALSE)+G2

説明は省きますが、興味があれば補足してください。（先ずは、うまくいくかどうかですが・・・）

お礼

「男」が入っているのが上から何行目だから…その隣の列の…
というようには考えていたのですが、どんな関数を使えばいいのか
さっぱりわかりませんでした。

ありがとうございました。
早速、使わせていただきます。

回答No.2

imogasi

エクセルの質問とか回答にはは次の種類があります。
（１）手操作（中でもウイザード的なものあり）・関数・VBA（マクロ）
こう言う分類のし方のほかに
（２）バッチ処理－一旦入力を締め切って、それまでのデータに対し処理
　　　即時処理－－シートへ追加・変更・抹消の都度、「即座に」反映させ　　　る。
本問題は、手操作を望んでいないと思います。VBAを使ったのはどうでしょうか。（A）通常は関数で処理できないかと漠然と感じている質問者が多いように思います。（B)それと即時処理を望まれていますね。
しかし、（B)は関数では難しいのです。と言う理由は（１）関数は入力したとか言うキッカケを捉え難い（式を潜ませて、ある時には表示しそれ以外は空白にすることは良くやりますがそれは別でです）。VBAならその仕組みがあります。他に難しい点は（２）シートのセルに何か入力すれば、そのデータを他のセルに送る機能はありません。シートの受ける側のセルで関数式を書くことにより（例えば「＝Ａ１」）Ａ１の値をそのセルへもって来れるのです。
ところが、本質問のように、男はどの行に入るか判らないのに、即時処理をする為には、受ける側のセルの式は入力より先に入れておく必要があるので、どこのセルよりもって来たら良いか判らない本問のケースには困ってしまう。またシート１の男が増えた時シートの終わりの次に追加すべきですが、終わりの行を捕まえるのも難しい。それでもし回答が出ても、技巧的にならざるを得ないのです。
私も関数で考えましたが、今のところ回答をできません。関数による回答がでるか楽しみです。
（３）また、１つのセルに２つ以上の関数式を入れることは出来ない。VBAならそれに似たことが出来ます。
エクセルで８時間回答がなかったのは珍しく、上記が原因だからと思います。

回答No.1

tuji3

自動的に更新するには、VBAを使わなくてはいけません。
以下のようにトライしてください。

まず、データがあるシートは　Sheet1
　　　男　　　　　　　　　　Sheet2
　　　女　　　　　　　　　　Sheet3
として、各シートの１行目はタイトル行が入力されているとし、
各シートのA列は、通し番号が入力されているとします。

標準モジュールを挿入し、以下をコピー貼り付けします。
Option Explicit
Public r As Range

Sub DataChange()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim i As Integer, manRow As Integer, womanRow As Integer
Set ws1 = Sheet1
Set ws2 = Sheet2
Set ws3 = Sheet3
ws1.Select
Set r = Range("A1").CurrentRegion

'転記先のクリア
ws2.Range("B2:B41").ClearContents
ws3.Range("B2:B41").ClearContents
With r

manRow = 2: womanRow = 2

'2行目から４１行目まで繰り返す
For i = 2 To r.Rows.Count
If .Cells(i, "B") = "男" Then
ws2.Cells(manRow, "B") = .Cells(i, "C")
manRow = manRow + 1
Else
ws3.Cells(womanRow, "B") = .Cells(i, "C")
womanRow = womanRow + 1
End If
Next
End With
End Sub

次に、VBEの画面で、プロジェクトエクスプローラのSheet1をダブルクリックして以下のコードを右側のペインにコピー貼り付けします。
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 41 Then Exit Sub
If Target.Column < 2 Or Target.Column > 3 Then Exit Sub
DataChange
End Sub

これで、シート１の元のデータが訂正されるたびに、シート２、３のデータは
自動的に更新されます。

お礼

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

ただ、VBAもVBEも全くわからない私には難しすぎて最初からつまづいてしまいました。
標準モジュールの挿入の仕方さえわからないのです。

申し訳ありませんが、関数を使う方法を選ばせていただきました。