Excelで番号管理

質問

Excel2016を使用しています。

自治会で120名、バス3台でバス旅行に行くことになり、その申し込みを受け付け、120人をオーバーする場合は抽選とすることになりました。

申し込みは最大2名とし、抽選で外れる場合は代表者と同行者が共に外れることにしました。

そこで添付のような表を作り、くじに外れた場合は、
１．その行（例：ddd、eee）を削除し、
２．番号（No）は繰り上がる
３．できれば外れたものは別シートにコピーできればいいですね

これをExcelで自動で行うにはどうすればいいのでしょうか？
マクロを組んで行うしか方法はないと思われますが、残念ながら当方マクロについては詳しくありません。

回答No.2

kagakusuki · ベストアンサー

>マクロを組んで行うしか方法はないと思われますが

　そんな事は御座いません。
　ワークシート関数と作業列を用いて抽選結果を出す事は十分に可能な事です。

>残念ながら当方マクロについては詳しくありません。

という事でしたら以下の様な方法は如何でしょうか。

　例えば、適当な使用していない列を作業列として4列(ここでは仮にF列～I列とします)使用し、適当な使用していない列(ここでは仮にK列～N列とします)に抽選結果を表示させるものとします。
　まず、F3セルに次の関数を入力して下さい。

=IF($B3="","",($B3<>"")+($C3<>""))

　次に、G3セルに次の関数を入力して下さい。

=IF($B3="","",RAND())

　次に、H3セルに次の関数を入力して下さい。

=IF(ISNUMBER($G3),SUMIF($G:$G,">="&$G3,$F:$F),"")

　次に、I3セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER($H3),$H3<=120),ROW(),"")

　次に、F3～I3のセル範囲をコピーして、F列～I列の4行目以下に貼り付けて下さい。

　次に、A2～D2のセル範囲をコピーして、K2～N2のセル範囲に貼り付けて下さい。
　次に、K3セルに次の関数を入力して下さい。

=IF(ROWS($K$3:$N3)>COUNT($I:$I),"",INDEX($A:$D,MATCH(SMALL($I:$I,ROWS($K$3:$N3)),$I:$I,0),COLUMNS($K:K))&"")

　次に、K3セルをコピーして、L3～N3のセル範囲に貼り付けて下さい。
　次に、K3～N3のセル範囲をコピーして、K列～N列の4行目以下に貼り付けて下さい。

　次に、Excelウィンドウの上の方に並んでいるタブの中から[数式]タブというタブを選択してクリックして下さい。
　次に、現れた「計算方法」グールプの中に[計算方法の設定]という電卓の形をしたボタンがありますので、そのボタンをクリックして下さい。
　次に、現れた選択肢の中にある[手動]という選択肢をクリックして下さい。

　以上で準備は完了で、後は「計算方法」グールプの中にある[再計算実行]ボタンをクリックするか、キーボードの[F9]キーを押すたびに抽選が行われ、[再計算実行]ボタンや[F9]キーを押すごとに異なる抽選結果が得られますので、最初にボタン等を押した際に現れた際に、K列～N列の表をコピーして、別の適当なセル範囲に"値のみを"貼り付けて、その貼り付けた表を抽選結果として下さい。
　何故、値のみをコピーした表を抽選結果にするのかといいますと、[再計算実行]ボタンや[F9]キーを何度も押すとそのたびに異なる抽選結果が得られるため、何度も抽選しなおすのと同じ事になり、その様な事を許していたのでは抽選を行っている人間が参加して欲しい応募者が出るまで抽選をやり直したり、抽選を行っている人間が参加して欲しくない応募者が抽選から外れるまで抽選をやり直す事も出来てしまうからです。

　尚、下記の添付画像は、120人分以上のデータを入力した表を撮影したのでは、表が大き過ぎて個々のデータの文字が小さく写ってしまう事により、文字が潰れて判別出来なくなってしまいますので、仮の話として受付人数を120人以内ではなく、12人以内とした場合の例を写したものです。(I3セルの関数中にある「$H3<=120」という部分を「$H3<=12」に変更した場合の画像です)
　11人分しか写っていませんが、これはG列の乱数の値が大きい順に参加者を選んで行った結果、偶々、12人目以内までの人数が奇数人となっていて、その次の12人目の該当者が同伴者を連れた2人組であったため、12人目を加えるためには13人目を同時に加えなくてはならず、それでは受付人数を超えてしまうため、12人目は参加者から除外しなければならなかったためです。
　例えばくじ引きで参加者を決めて行く際に、12人目以内までの人数が奇数人となっていて、その次に引いたくじに書かれていた申込者が同伴者を連れた2人組であった場合と同じ事です。

お礼

ありがとうございます。
参考にします。

回答No.3

toi_awase_01

ポイントは次の2点
・物理的な削除をしない
・フィルター機能を使う

物理的に削除しないで、削除フラグで管理すればよいと思います。
右端へ削除フラグの列を作り、削除したい行に1を立てるだけ。
タイトルと先頭行の間に初期値行を作り非表示行（3行目に作成）にしておく。
（Noは０ゼロ、氏名は空白、削除フラグは2にする）
4行目（実際のデータの初めの行）の代表者氏名のNoに
=IF(E4<>1,MAX(A3,C3)+1,MAX(A3,C3))
を設定する
同伴者Noに
=IF(AND(D4<>"",$E4<>1),A4+1,"")
をそれぞれ入れます。
後は、必要な行数分複写するだけです。

Excelのフィルター機能により、削除フラグ=1のものを表示/非表示にすれば、
わざわざ別のシートにしなくても参加者や削除者を表示できます。
削除フラグ列も入力するときだけ表示し、印刷時は非表示にすればOKです。
削除した人が復活した場合にNoのリナンバリングも削除フラグを消すことにより、
容易に自動復旧出来ます。

お礼

ありがとうございます。
参考にします。

回答No.1

_backyarD

同行者と代表者を同じ行に、そこまでは良いと思います。
このあとの抽選ですが、

1. 人数を数えるために列を追加し、それぞれ1または2を記入します。
2. 乱数を発生させる式を各行の頭か後ろに追加し、全部の行についてランダムな数字を設定します。
3. そのあと、全体を並べ替え。先頭から人数列の合計が120人になるところまでが当選。それ以下は落選

みたいな感じで、乱数発生と並べ替え、上位から所定の人数までが当選という形で抽選されてはどうでしょう？（私がマンション管理組合の役員をやった時に駐輪場抽選で使った方法です）

お礼

ありがとうございます。
参考にします。