エクセルで名列表をつくっています。
A列に1から40までの通し番号、B列に男女、C列に名前が入っている「名簿」シートがあります。
この「名簿」を元にして、男女別の「名列表」を別のシートに作りたいと思います。
「名列表」の方はA列に1からの通し番号(「名簿」のA列の数字とは関係ありませんので
並べ替えの必要はありません。)、B列に名前がはいります。
これが男女にわけられた2つの表(一つのシート内でかまいません)となるようにしたいです。
ただ、並べ替え等で男女別に分けるのではなく、元になる「名簿」を訂正すると自動的に
「名列表」も訂正されるというものがいいのです。
簡単な方法を教えてください。
No.3ベストアンサー
- 回答日時:
簡単な方法?でもないですが、関数で作る方法です。
名簿の1行目、名列表の1行目は項目名が入るものとして
名列表に以下の式を入力(C列を1列お借りします。確認後、非表示にして下さい。)
(男)
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
4行目以降は3行目をコピー貼り付け(何行でも好きなだけ)
で試してみてください。
名簿シートの男女は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
- 回答日時:
エクセルの質問とか回答にはは次の種類があります。
(1)手操作(中でもウイザード的なものあり)・関数・VBA(マクロ)
こう言う分類のし方のほかに
(2)バッチ処理-一旦入力を締め切って、それまでのデータに対し処理
即時処理--シートへ追加・変更・抹消の都度、「即座に」反映させ る。
本問題は、手操作を望んでいないと思います。VBAを使ったのはどうでしょうか。(A)通常は関数で処理できないかと漠然と感じている質問者が多いように思います。(B)それと即時処理を望まれていますね。
しかし、(B)は関数では難しいのです。と言う理由は(1)関数は入力したとか言うキッカケを捉え難い(式を潜ませて、ある時には表示しそれ以外は空白にすることは良くやりますがそれは別でです)。VBAならその仕組みがあります。他に難しい点は(2)シートのセルに何か入力すれば、そのデータを他のセルに送る機能はありません。シートの受ける側のセルで関数式を書くことにより(例えば「=A1」)A1の値をそのセルへもって来れるのです。
ところが、本質問のように、男はどの行に入るか判らないのに、即時処理をする為には、受ける側のセルの式は入力より先に入れておく必要があるので、どこのセルよりもって来たら良いか判らない本問のケースには困ってしまう。またシート1の男が増えた時シートの終わりの次に追加すべきですが、終わりの行を捕まえるのも難しい。それでもし回答が出ても、技巧的にならざるを得ないのです。
私も関数で考えましたが、今のところ回答をできません。関数による回答がでるか楽しみです。
(3)また、1つのセルに2つ以上の関数式を入れることは出来ない。VBAならそれに似たことが出来ます。
エクセルで8時間回答がなかったのは珍しく、上記が原因だからと思います。
No.1
- 回答日時:
自動的に更新するには、VBAを使わなくてはいけません。
以下のようにトライしてください。
まず、データがあるシートは Sheet1
男 Sheet2
女 Sheet3
として、各シートの1行目はタイトル行が入力されているとし、
各シートの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行目から41行目まで繰り返す
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
これで、シート1の元のデータが訂正されるたびに、シート2、3のデータは
自動的に更新されます。
回答ありがとうございました。
ただ、VBAもVBEも全くわからない私には難しすぎて最初からつまづいてしまいました。
標準モジュールの挿入の仕方さえわからないのです。
申し訳ありませんが、関数を使う方法を選ばせていただきました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルの大きなシートでグラフを見つける 4 2022/07/28 10:07
- Excel(エクセル) EXCEL 関数を教えてください。(A列の同じ値が複数ある場合vlookupで出来ますか) 4 2022/12/07 20:54
- Excel(エクセル) Excel>マクロ>特定のセルで同じ情報が登録されている行を1行にまとめたい(文字連結) 6 2023/01/05 16:30
- Excel(エクセル) エクセルシートのデータを1列飛ばしで別ブックのシートに貼り付けるマクロが知りたい 2 2023/06/05 22:37
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
- Excel(エクセル) マクロか関数で処理したいのですが、教えて頂けませんか。 8 2022/10/31 15:18
- Visual Basic(VBA) VBAで最新のデータを別シートに転記する方法をお教えください。 3 2022/04/07 19:20
- Excel(エクセル) エクセルの複数ブックのシートを1つまとめたい 都道府県ごとに47ブックがあり、そのシートのデータを1 5 2022/11/15 14:57
- Excel(エクセル) 【マクロ】マクロが保存されているエクセルとは、別のエクセルブックの全シートの非表示列を再表示したい 1 2022/12/24 20:48
- Excel(エクセル) Excelで質問です! 現在マクロを勉強中の初心者です。 以下のような表から、会社名が空白のもの以外 2 2022/06/14 12:16
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelはなんで先頭の0を消すん...
-
Excel元に戻す方法を教えてくだ...
-
【Microsoft Office Excel Comp...
-
Excelが固まってしまった。
-
西暦や和暦の表示をyyyymmdd表...
-
Excel 2019 のピボットテーブル...
-
【関数】スペースがいくつ入っ...
-
【Excel】セル内の時間帯が特定...
-
excelの不要な行の削除ができな...
-
Excelのオートフィル
-
別シートからの文字を変更
-
Excelのセルを飛ばして入力する
-
Excel初心者です。 詳しい方、...
-
エクセルの行の抽出について質...
-
Excel初心者です。 詳しい方、...
-
【マクロ】エクセルにかいてあ...
-
EXACT関数とIF関数の組み合わせ...
-
スプレッドシート クエリ関数 1...
-
エクセルで指定した日付、店舗...
-
Excelのグラフ軸について
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報