男女別名簿

質問

関数が複雑すぎて分からなくなりました。教えていただきたいです。
混合名簿から、男女別名簿を自動で作成したいです。クエリではなくできれば数式で…
完成イメージは図の通りです。
＜条件＞ ・混合名簿シートの通番は、１年１組１番なら、１１０１とするが、いかなる数字でも対応できるようにする（通番は関係ない？）。
・１年男子、１年女子、２年男子、２年女子、３年男子、３年女子のシートを作り、それぞれに数式を入力する。
・現状全学年３クラスだが、増えても大丈夫なようにする。１クラスが４０人を超えることはない。男女比はだいたい５：５。
・混合名簿シートを変えたら自動で上の各学年男女別シートも更新される。
・１クラス（男子または女子）の枠は、２５人を最大とする。例えば、図のように、３年１組の男子が２２人であれば、A25：A27の列は空白（iferror）で、A28の列のところに3年２組の男子の最初の生徒が来る。

お手数おかけして申し訳ありません。よろしくお願いします。

回答No.1

SI299792 · ベストアンサー

混合名簿シート：シート名は「混合名簿」とします。
999 行迄を想定しています。もっと多い場合、下式の$999を増やして下さい。

学年男女別シート
A1 1～3 の数字にします。
B1:年
A3: =QUOTIENT(ROW()-3,25)+1
B3: =IFERROR(INDEX(混合名簿!D:D,SMALL(IF((混合名簿!$B$2:$B$999=$A$1)*(混合名簿!$C$2:$C$999=$A3)*(混合名簿!$G$2:$G$999=$C$1),ROW($B$2:$B$999)),MOD(ROW()-3,25)+1)),"")
Ctrl＋Shift ＋Enter で配列数式にして下さい。数式に{}が付きます。
纏めて必要なだけ下へコピペ。
例えば、３クラスだったら77行目までコピペします。

A1: データの入力規則、リスト
元の値: 1,2,3
C1: データの入力規則、リスト
元の値: 男,女
にして、プルダウンで選べるようにすれば、学年別男女別にシートを作る必要はありません。

回答No.3

imogasi

#2です。imogasi方式でやってみた。
例データ　小生の勝手な内容のデータ例がありますが。
A1:D12
学年 氏名 氏名カナ 男女
1 木村 キムラ 1
1 山田 ヤマダ 1
1 佐野 　サノ 1
2 近藤 　コンドウ 2
3 山下 　ヤマシタ 1
1 富野 　トミノ 2
2 山本 　ヤマモト 1
3 今井 　イマイ 2
1 川田 　カワタ 1
2 東田 　ヒガシダ 2
3 西田 　ニシダ 1
このまま、この部分をコピペしてデーター区切り位置、で操作すると、シートデータになると思います。画像で挙げるのでなく、質問には、こういう方式でデータ例をつくってほしい。
F2の式　=IF(AND($A2=1,$D2=1),A2,"")
I2まで式の複写
J2の式　=IF(AND($A2=1,$D2=1),MAX($J$1:J1)+1,"")
F2:J2を範囲指定して、J2で＋ハンドルを出し、下方向に引っ張る。
J列だけでもよいが。
年 氏名 氏名カナ 男女 該当連番
1 木村 キムラ 1 1
1 山田 ヤマダ 1 2
1 佐野 サノ 1 3

1 川田 カワタ 1 4
ーーー
L2に式　=IFERROR(INDEX($F$2:$I$12,MATCH(ROW()-1,$J$2:$J$12,0),COLUMN()-11),"")
O列まで式複写。
L2:O2を範囲指定して、O２で＋ハンドルを出して、下へ１１行分、引っ張る。
結果
年 氏名 氏名カナ 男女
1 木村 キムラ 1
1 山田 ヤマダ 1
1 佐野 サノ 1
1 川田 カワタ 1
＝＝
質問と違うところは、同一シートに出てしまっているところ。
Sheet2に出すなら、Sheet2の式において、Sheet1のセルの番地の前にSheet1！を加えれば よい。Row（）やColumn（）はSheet2の情報を取るのだから、注意（そのまま）
＝＝
上記J列を使わない関数式を考えるのが難しいので、作業列を使った。
関数式では、中間のデータ変数を、式中で使えない。VBAなら当たり前に使うのだが。
使わない場合は、配列数式、Sumproduct、その他関数の組み合わせ、などを使うのかなと思う。

お礼

ありがとうございました😭

回答No.2

imogasi

＞関数が複雑すぎて分からなくなりました。
関数を使って、表の組み換え（本件は、抜出し・フィルタ―・タイプ）をやるには式が複雑になる。
ーー
それよりも、操作のフィルタ―・詳細設定ででできないか考えるべきだ。
ただし抽出先を元データと別シートに指定できない。
まず同一シートの別セル範囲に、抜出後のデータを作って、手操作で各シートにコピペするべきだ。
ーー
作業列を使えば、１年男子だけ、などを抜出しは、ＩＦ関数で出来る。
しかし該当データが途中ブランク行データも混じり、それを最上行から、詰めるのが
厄介（imogasi方式が１つのやり方）で、１ステップ必要だ。これを嫌がる者が多くて、そういうことを分かってないように思う。
＝＝
元々
・操作
・VBAや
・データベース言語（Accessなど）や
・クエリ機能でやるべき問題を、あえて
難しい方法でやりたいと言っていることを自覚すること。
ーー
作業列も使わず、関数の１式で抜出する回答も過去質問やWEB記事にある。
それを見て、＞関数が複雑すぎて分からなくなりました、と言っているのかなと思う。自力で、１・2日で思いつくものではない。出来ればエクセル関数の上級だと思う。