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

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

そこで以前にここで

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

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

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

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

です。

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

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

大根の初栽培の初収穫〜大根干し

今年初めて大根を栽培しました。
この畑は太陽熱養生処理をしたところです。
今日抜いたのが約半分です。これらは割と早い段階から土から首を出していて、今日の収穫時点でほとんどが20cm内外首を覗かせていたものです。それにしてもここまで長くなっているとは思いませんでした。
1本だけ先が何かにあたっていたのか、変形してますが、初めてにしては上出来と自分で褒めています。
写真の中で一番長いのが54cmほどありました。重さは計っていません。
どうやって干せばよいのかもよくわからず、とりあえずこんな調子にぶら下げてみました。
たくあん漬けまでやってみたいのですが。

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

俳句教室では参集者が俳句を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」とすればよい。これで投句数に応じて自動で表もできる。

「地域再生〜住民による村づくり」講師: 柳谷公民館長 豊重哲郎さん

「地域再生〜住民による村づくり」と題しての講演会が新庄村ふるさと文化祭「生涯学習推進大会」の中で行われた。講師は鹿児島県串良町柳谷公民館長 豊重哲郎さんです。
以下はその時のメモ書きなので不正確・不十分とは思いつつも参考になる言葉がたくさんあるのでここに掲載しておく。

鰻の養殖もされてきた。ヘルプアイ創設。うなぎエキス。
人口300人の集落の公民館長。74歳。
やねだん。
20年間のテレビ映像。放棄地をサマイモ畑に。焼酎のブランド「やねだん」まで作る。若者のちからも結集
アーティストも集まってくる。
人材創世塾。
村の医療費は平均の6割以下。
韓国でもテレビ放映。
韓国ゴルフクラブ第1の企業がやねだんを取り入れた居酒屋をつくる。

自分が動かないと人は動かない。
 集落300世帯の人の名前をすべてフルネームで言える。
行政に頼らない集落づくり。自主財源を作る。6次産業的なブランドづくり。
休耕地を利用しさつまいも。3−4年は人を本気にさせるまでが大変だった。自ら耕作。
地域づくりには補欠はいない。
焼酎「やねだん」は15年前の5倍。
石破大臣も日本一のモデルと絶賛。

29歳でUターンし、ウナギ事業をするが3000万の借金。なんとか返済し鰻も好調。54歳。
まちひとそうせいの視察として地方創生大臣石破も感動。

300人の村で500頭の牛、3000頭の豚。
教育文化づくり。
会費制では地域づくりは限度がある。
高齢者を生きた福祉として地域に出てもらう。
8割の人が自分たちのことだと認識すること。
人の名前を呼んであげる。半径100mの人の顔を覚え名前で呼んであげる。
家族での基本は名前で呼ぶことに。
地域づくりは笑顔にならなかったら楽しくない。
イベントだけでは地域づくりはできない。
人数の少ないところでできるのは名前をよべること。感謝の言葉も出てくる。
フルネーム、笑顔、快話
目配り、気配り、心配り。
円満な輪
補欠はいないという部活指導。村作りも一緒。立つ位置を変える。

企業会計原則
感動と感謝

高齢者部(わくわくクラブ)、青少年育成部、婦人部・壮年部、文化部、畜産部、事業部。
これに全ての人が所属する。
人口の分布をグラフにして全戸に配布する。320人でスタートし
グラフを見ることによって、これからを考える。
自分を送ってくれるのは誰か?地域にお世話になるしかない。
高齢者が子供に話聞かせることで高齢者づくりにも生かす。
地域でできることをやっていく。
5歳以下の未就学児が9人に増えてきた。
空き家を整備して芸術家を招いている。空き家が1年中、ギャラリーになっている。アーティストの仕事ぶりを人にも見せる。
空き家をただで借りて「やねだん」では迎賓館としている。8号館までできている。地域の人で改修してきた。
畜産部では土着菌開発に力を入れている。
焼酎「やねだん」は地域名だから売れる。
6年前から韓国から交流。唐辛子栽培。韓国では唐辛子を小さいうちから食べているからメタボがいない。唐辛子はイノシシ・猿の被害にも合わない。
土作りには微生物が必要。ミミズのいるところでいい作物ができる。ミミズの糞は皆丸い。1年間でペットボトル2リットル15本分出している。ミネラルも多い。丸いから隙間もあり根が入り込みやすい。
爺ちゃんばあちゃんを学園に入れて学ばせる。悪い子もいなくなる。
こんなことを3−5年かけてやってみる。
集落の人たちでボランティアで地域おこし、公園を作る。高校生が動き祖父母が動く。補助金頼らぬ地域おこし。資金は唐芋でつくる。
1町歩のからいもうえ3時間で終了。

子供の発達。
0−7 感覚・意志力
8−14歳 感情を育む
15ー20歳 思考力・判断力ー親へのメッセージ発信
これが地域活動
サンセットウォーキングー高校生が小さい子を見守って。
てらこやーわからないところを見つける。収益の一部を謝礼として使い先生の指導を受ける。
収益金で介護カーも購入して配布している。

地域再生リーダーへの一言提言
優れた人材は社会の財産。
慌てるな急ぐな近道するなー土台づくり
ヒーローでなくていい、黒子でいい。
感動と感謝。目配り・気配り・心配り
地域はやっぱり笑わないと
ビジネス感覚と地域経営学を共有し情熱で人を動かす
地域活性化には行政依存がまだまだ必要であり、行政に大いなるエールを贈りたい。

集落で死者を送り葬儀代もただ、香典もしない。地域で墓を守る
健常者だけが公民館に集まるのではなくサロンにし、葬儀場にし、レクレーション場にする。
家の無くなった人にも公民館を宿泊施設にもして、みんなが集まれるように。
楽しみましょう。やってやれないことはない。

花梨の実情熱こめて赤くなり #jhaiku ジャム作り

花梨の実情熱こめて赤くなり #jhaiku

先日収穫した花梨の実。
2回目のジャムづくり。
黄色い花梨の実が、熱を加えると赤くなる。
5個のカリンを使って、2つに割って種を取り出し、皮も削ぎ、実はいちょう切りにスライスし、電気圧力鍋の調理はここまでのラインギリギリまでに、種・皮・実を入れひたひたになるまで水を追加して、高圧で30分。
圧力ピンが下がってから、ザルで水分だけを別の鍋に取る。
ざっと煮汁が1kgあったので、砂糖を約半分の量の500g加え、レモン汁30ccも入れて煮立てていく。
途中約半分を飲料ジュース用に取り除く。
残りをさらに煮詰めてジャムにする。

今年はたくさんの実が取れたので煮詰めた後の実を気軽に捨てることができる。昨年は少なくてその実も更にミキサーにかけてジャムとして食べていたのだが。

カリンとハヤトウリの収穫

霜が降り始めたからか、ハヤトウリの蔓や実が傷み始めた。そろそろ限界かと残っていたものをすべて収穫しました。
1本のつるからこれだけ(今までにだいぶ消費もしてますが)取れるのだからすごいものです。
昨年不作だったカリンも今年はたくさん実をつけてくれました。それも全て収穫です。すでに1度ジャムにしたので、あと何回か作れそうです。部屋の中においていても甘い香りがしてきて、ホッとします。