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

俳句教室では参集者が俳句を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.商品シート

図1.商品シート

検索シートは次のようにする。

図2.検索シート

図2.検索シート

商品シートでは関数式を見やすくするために、次のように範囲に名前を定義する。

図3.名前定義

図3.名前定義

範囲に名前を定義したのは赤く囲まれたA~Eに設定した。A.「分類」、B.「商品表」、C.「くだもの数」、D.「野菜数」、E.「魚数」とした。

商品表の中の「分類欄」は表記のミスを防ぐために、入力規則を設定し、リスト>元の値に「=分類」と設定する。

図4.商品表分類入力規則

図4.商品表分類入力規則

商品表は追加をするかどうか、する場合どの程度の量になるかによって商品表の名前定義の範囲を設定する必要がある。品名欄は「りんご」と「リンゴ」では違うものとして扱われるので注意が必要です。

分類で分けた表示になっていないために、分類ごとに品名を表示するための作業に移ります。

作業列1はそれぞれの品がその分類の中で何番目に記載されたかをカウントする欄です。

図5.作業列1-分類ごとのカウント

図5.作業列1-分類ごとのカウント

「くだもの」欄の赤で囲んだセルには「=IF($A6=E$5,COUNTIF($A$6:A6,E$5),””)」と入力してあります。商品表のA列の「くだもの」の表示が何個目かをカウントしています。E5~E7の項目名は分類項目をそのまま表示することに寄って、赤で囲んだE6のセルをコピーして、「E6:G20」の範囲に「形式を選択して貼り付け」>「数式」とすれば、作業列1(黄色の範囲)がすべて表示できる。品目が増えてもよい。

つづいて、作業列2は作業列1で番号付けした品目を上から順番に記述する部分です。表が小さいときは一つ一つやってもよいのですが、いくら増えるか分からないときにはこうしておくと楽です。

図6.作業列2ー分類ごとに品名を並べる

図6.作業列2ー分類ごとに品名を並べる

作業列2のくだもの欄の赤で囲んだリンゴのところには数式で「=IF(MAX(くだもの数)<ROW($A1),””,INDEX($B$6:$B$20,MATCH(ROW($A1),くだもの数,0)))」と記述しています。「くだもの数」は図3で示した通り名前を定義したものです。数式をコピーするときにこの「くだもの数」のところが分類により異なるので、とりあえず、この数式をコピーし、I6:J6に形式を選択して数式を貼り付けます。そのあと、「くだもの数」のところを野菜欄のところは「野菜数」に魚欄は「魚数」という定義した名前に変更します。そしてH6:J6をコピーして表の下まで選択し、「形式を選択して貼り付け>数式」で図6のような結果が表示できるようになります。

ここまで出来たら、検索シートの設定を行います。

図7.検索シートの分類の入力規則

図7.検索シートの分類の入力規則

商品検索シートの分類欄の入力規則を設定します。商品シートで名前を定義した「分類」を使います。入力規則の設定で入力値の種類を「リスト」とし、元の値を「=分類」と設定してOKとします。そのセルをクリックすると三角の表示が示されそれをクリックすると、

図8.分類ダウンリスト

図8.分類ダウンリスト

ダウンリストが「くだもの、野菜、魚」と表示されるので、表示させたいものを選びます。

続いて品名の欄に行くと

図9.品名入力規則

図9.品名入力規則

同様に入力規則を設定する。ここは分類欄に応じた品名の表示をする必要があるので、元の値を「=INDIRECT($A$3)」と設定します。これによりダウンリストが下のように表示できるようになります。

図10.品名ダウンリスト

図10.品名ダウンリスト

ここでは、イワシを選択してみました。さらにそのイワシのデータを表示させるために、価格と在庫の欄に次のように数式を設定します。

図11.商品データの表示

図11.商品データの表示

価格欄は「=VLOOKUP(B3,商品表,2,FALSE)」、在庫欄は「=VLOOKUP(B3,商品表,3,FALSE)」。この中でB3のデータを商品シートの「商品表」の中から検索し、その2列目、3列目を表示しなさいという数式です。「FALSE」とすることにより完全一致のデータを表示することになります。

図11.項目の多い商品データの時

図12.項目の多い商品データの時

項目の多い商品データの時は、一項目ずつ変更するのも面倒なので、「=VLOOKUP($B$10,商品表,COLUMN(C10)-COLUMN($B$10)+1,FALSE)」のようにして、列番号を「COLUMN(C10)-COLUMN($B$10)」として品名の列から何列離れているかという形にして、価格欄の数式を、横に必要なだけコピーすれば簡単に表示させることができる。