条件を付けてランダム~エクセルVBA

以前に、参加者の俳句一覧表にし、ランダムに並べるVBAを作成した。ところが15人ほどのメンバーで一人が2句ずつ投句し、それを一覧にする。一覧にするときには一人が投句した2句を続けてエクセルに書いていく。そうやってできた一覧表をVBAをつかってランダムに並び替えてみると、何か所かで同一の人の句が並んでしまう。5回くらいマクロを実行して誰も並ばない状態にすることもしばしばであった。

そういう状況を解決しようといろいろ考えてみて、次のように考えてみた。

1.2句ずつだからAグループとBグループのように分ける。

2.AグループとBグループに分けると、これだけで同一人物の句が並ぶことは亡くなる。でも人が同じ順に出てくるというのも面白みがない。

3.そこで、グループごとにランダムに並び替える。もちろんこの場合はAとBとの分かれ目のところで同一人物が並ぶ可能性もないことはない。その時は2回目のマクロを実行することにする。

4.AとBのグループ分けは、エクセルの関数を利用して、同一人物の何番目かをカウントする。(これも句を入力するときに1,2・・・と入力しておいてもいいのだが、手間は少しでも省きたい)そこで空いている欄を利用して、エクセル関数 ‘=IF(A15=””,””,COUNTIF($A15:A15,A15)) ‘
を必要以上にコピーする。
・・・’ =IF(O15=””,””,COUNTIF($A15:O15,O15)) ・・・’。
O15に入力がなかったら空白、そうでなかったら、A15からO15までの間にO15(人物名)の数を数える、という関数である。こうしておけば1人が何句投句しても自在となる。 他の使用法もありそうだ。こうして、1,2・・というグループ分けが可能になる。

マクロについては

エクセルVBA8「並び替える」の「

6 表の列を昇順とランダムの2条件で並び替える

グループ分けとランダム

こちらの方が、確実にバラツキができていい。

マクロを使ってエクセル表の列をランダムに並び替え

エクセルで並び替えというと、行を昇順とか降順で並べるのが普通ですが、エクセルで縦書きのデータを作成してそれを並び替えるという作業が必要になった。

そこで以前にここで

エクセルでランダムに表示、条件付き書式で罫線~俳句教室

にエクセルの別シートを活用して関数で処理することをやってみた。ただこれで満足していたら、新たな問題に出くわした。つまり表の中にルビを表示しているものがあり、それを別シートに移すとそのルビのデータはなくなってしまうということだ。

やむなくエクセルのマクロを使うことにする。それで作成したのが次の 

エクセルVBA8「並び替える」

です。

行列が増えてもこのマクロで列をランダムにシャッフルすることができます。

エクセルで縦書きをすることもあまりないと思いますが、俳句教室でみなさんの俳句を集めて清句するときに、少しでも手間を省こうと思い、それ以上の手間をかけてみました。

エクセルでランダムに表示、条件付き書式で罫線~俳句教室

俳句教室では参集者が俳句を2句ずつ投句して、それを清書して(「清句」)気に入ったものに投票し感想をべるということが行われる。教室のその場で清句して清句用紙をまわしてする場合もあれば、事前に投句を集め当日までに作者名を伏せて一覧にし投票するということも行われるそうです。そのとき同一作者の作品が並ぶことを避けて順不同に書き出すということが行われます。すべての用紙が集まってから、用紙をばらばらに並び変えてから書き写せば済むことですが、エクセルを利用してランダムに表示することを考えてみました。

1.投句をランダムにシャッフルする

1.投句を入力 2.ランダムに並べる

図1.投句を入力 2.ランダムに並べる

A1には西暦の数字を入れています。書式を「ユーザー設定」で「G/標準 “年”」とすることによって、4桁の数字「2015」を入れることによって「2015年」と表示できます。B1,B2は俳句教室が行われる月日の数字を入れています。これは清句用紙て利用します。

B3から下は、「=IF(C3=””,””,ROW()-2)」という数式を入力して、C3に俳句が入力されたら1から順番に番号を表示するようにしています。「ROW()」は行数を数えています。3行目から始めているので2を引けば1となります。これにより俳句がいくつ集まったかを自動で数えることができます。会員数と投句数によって考えればよいのですが、元のシートは多くせってしておけばいいです。ここではB101まで数式をコピーしています。C列に俳句を入力していますが先頭に1から順に数字を入れているのは、ランダムがどのようになっているのかを示すために入れているだけです。

ランダムにシャッフルするために、A列のA3に「=IF(B3=””,””,RAND())」という数式を入力してからA101までコピーしています。B3に番号が表示されたら、つまり俳句が入力されたら、RAND()関数の値を入れるということです。

F列からのランダムに表示された所の説明に移ります。

F列はB列と同様の関数を入れて1から順に番号を表示させています。F3=IF(G3=””,””,ROW()-2) です。

G1=IF(A3=””,””,INDEX($C$3:$C$101,MATCH(LARGE($A$3:$A$101,ROW(A1)),$A$3:$A$101,0)))

としています。RAND()関数で表示したA列の$A$3:$A$101の中で、1番大きな数字(LARGE関数)から一致する数字、つまりG3ではROW(A1)=1だから、1番大きな数値のA列に一致MATCHする俳句をINDEX関数で求めています。G4ではROW(A2)=2となるからA列で2番目に大きなものを求めるようになります。こうして投句されたものをランダム、順不同に表示することができます。これにより受け取ったもの順に入力していても表示が自動的に順不同にすることができます。

2.別のシートに縦書き表示する。右から番号を振る。

清句用紙

図2.清句用紙

図1の黄色で塗られた部分を、縦書きに表示して、俳句の表示らしくしています。O1:T1の欄はセル結合をし、「=DATE(投句!A1,投句!B1,投句!B2)」と投句シートで俳句教室の年月日を入れたものをここで表示しています。さらに年号で表示するためにセルの表示形式を「ユーザー定義」とし、「ggge”年”m”月”d”日(“aaa”)”」として、曜日まで表示するようにしています。

縦書きなので、右から1番と番号を打ちたいので、一番左端の番号は投句数によって変わってしまいます。そこで左端の数字を

A10==MAX(投句!F3:F101)

と投句シートのランダムに並べた表の中で最大の数値を求めます。A10にしているのはその上にいくつかのマスを投票した人の名前などを略記する欄としています。B10,C10・・・・・必要なだけ、投句シートでは100句まで入力できるようにしているのでそこまで

B10=IF(A10=””,””,IF(A10-1>0,A10-1,””))

として、左隣が空欄なら空欄、そうでなければ投句数から1ずつ引いていく。最後は1とする。投句の記入順に俳句の先頭に1から番号を売っていたのに、ランダム関数によってずいぶん順番が変わっているのが分かる。

投句欄A11などは

=IF(A10=””,””,VLOOKUP(A10,投句!$F$3:$H$101,2,FALSE))

VLOOKUP関数を利用して、上の番号に一致する投句を投句シートを参照して表示する。A12の欄も同様。

3.条件付き書式を利用して罫線を投句数に合わせて枠を作る

罫線について。投句数によって罫線の空欄がたくさんできるのもあまりよくないとすれば、投句数に応じてその時ごとに罫線を引くのもありだろうが、体裁が毎回変わるのもおかしいし、前回を流用して消したり増やしたりするのも面倒。一番楽なのは、投句数に応じて罫線が自動的に増減するように最初から設定しておく。それを可能にするのが、「条件付き書式」。

A2のセルで、「条件付き書式の設定」でルールを「数式を使用して、書式設定するセルを決定」を選択。ルールの内容欄で

実線で周りを囲む

図3.実線で周りを囲む

A2=A$10<>””

つまり、A10が空白でなかったら、書式「セルを実線で囲む」。B10~コピーするために、それを「A$10<>””」とAの前に$がついていないことに注意。内容編集の時にセルをクリックすると自動的に「$A$10」となるので、Aの前の$を削除しておく

適用範囲で最大範囲まで広げる

図4.適用範囲で最大範囲まで広げる

書式を設定後、適用先を「=$A$2:$CW$2」と100句でも可能なようにセル範囲を設定する。

A3~A8は下線が点線、両サイドの縦が実線としている。A3のセルで条件付き書式、図3と同様に「A$10<>””」で、書式欄を上空線、両サイド縦実践、下線を破選とする。そして適用範囲は「=$A$3:$CW$8」とする。

A9は「A$10<>””」で書式は両サイド実線、下線実線。範囲は「=$A$9:$CW$9」。

A10~A12はA2と同様に「A$10<>””」で書式をすべて実線で囲むとする。適用範囲は「=$A$10:$CW$12」とすればよい。これで投句数に応じて自動で表もできる。

エクセルでフラッシュカード用のファイルを作ってみました。

電子黒板やスマートボードに映し出して使用できるように、エクセルでフラッシュカードを作りました。使いたい(1)用語の登録、(2)利用のしかた(順番に表示する、ランダムに表示する、設定した秒数によって自動的に切り替わるようにする)を選択する、(3)用語は1000項目まで登録できる(たぶん別ファイルに分ける方がよいかもしれないが)、登録した用語の何番から何番までの用語を表示させるのかの設定、などを行う。これにより、1時間単位、あるいは数週間単位で利用できるので一度登録したデータを繰り返し使うことができる。用語の登録も表面と裏面の登録及び表示ができるので、小テストなどに利用するという使い道もできるかもしれない。

 ここではその使用法のファイルと、実際のエクセルファイルをリンクしている。エクセルファイルで、ランダムに表示させたり、タイマーによりランダム表示などをさせるために、マクロを設定しています。マクロを有効にして利用下さい。  

使用方法

エクセルでフラッシュカードの使用法

エクセルでフラッシュカードをつくってみました&使用法

エクセルのシートに必要語句を入力するだけでフラッシュカードができます。

フラッシュカード エクセル作成 昇降順 ランダム タイマー自動

  • ファイルを開いたら、ファイル名を変えて作成して下さい。
  • ①にデータの登録。出題の範囲は表示するシートの方で設定するので、1000件は表示可能にしている。必要があれば設定し直すことも可能だが、多すぎても使いづらいかも。
  • フラッシュカードにしたい用語を登録したら、その時間に使用したいカードの最初と最後の番号を控えておく。
  • フラッシュカード用のシートは「手動 昇順・降順」「自動タイマー」「手動ランダム表示」のシートがあるので必要に応じて選択する。
  • 「手動 昇順・降順」
  • その他

 電子黒板などでの画面表示がどうなるのか分からないので、ご意見下さい。

使ってみてエラーなどが出たら、ご指摘ください。