よっしーガーデン

エクセルで だれでもかんたん地図グラフ の作り方 その２

色を塗る仕組みの次は、地図っぽくする方法です。
理屈は単純ですが、これは、少し細かい作業になります。

○地図作成編です

１．白地図を用意します。
　地図ファイルは白黒など２色のものを用い、画像ソフトを使って、境界線と地区名のみを残し、背景は透過色にしておきます。つまり、線だけにしておきます。
（ファイルが軽くなるし、あとで、色塗りの範囲に、図として貼り付けるため。）

２．セルを縦横同じピクセルにして、格子状（メッシュ）にしておきます。
（今回は、エクセル２００７を使ってみます。）

３．シートの背景に１の白地図ファイルを設定します。

４．地区データと一覧表を用意します。
（練習のため、地図の隣に作りました。）
　色データを検索するとき、前回は地区名を使いましたが、今回は地区名の代わりに地区NO.を使います。
　一覧表の一番右に地区NO.を作っています。
　地区NO.のセルには異なる色を付けておきます。

５．地区NO.のセルをコピーして、背景を見ながら、該当地区のセルに貼り付けます。

６．全部の地区に地区NO.をコピーし終わったら、３と同じ手順で背景を削除します。

７．地区の境界線を表示するため、白地図ファイルをオブジェクト（図）として挿入します。
（色を付けた部分と挿入した図の境界線が一致するように、図の位置や大きさを調整します。）

８．図の位置が決まったら、セルにコピーしてある地区NO.を見えなくするため、文字色とセルの色を「白」または「色なし」にします。罫線も消しておきましょう。
　これで、白地図の完成です。

９．せっかくですから、地図に色を付けましょう。
　前回と同じように、条件付き書式を用いて、一覧表の色データが１の時に色を付けるように設定します。
　地区NO.が見えるといけないので、ここでは、セルの色と文字色を同じ色に設定します。　

１０．メッシュの幅が大きかったので、境界線がでこぼこになりましたが、うまく色を塗ることができました。

ここで、ワンポイントアドバイス。

○なめらかな境界線を描こうとするなら、メッシュを細かく（密に）すれば良いのですが、地区が数十箇所あるようなものでは、かなりの作業量になります。

○また、範囲指定で楽をしようとしてセル幅を小さくすると、私のような不器用な者には、逆にセル範囲の選択が難しくなり、コーピー＆貼り付けが余計に面倒になります。セル幅や文字の大きさ、表示倍率などを工夫して、一番作業しやすい画面に調整しましょう。

○背景は、表示倍率を変更しても常に同じ大きさで表示されます。したがって、元の地図ファイルの画像が小さいと、なにをやるにも神経を使うので、画像は大きいもの（いや、ちょうど良いもの）を使いましょう。

☆思うような地図が描けたなら、次は、データ一覧表の生データ欄に、いろいろなデータ項目から一発で選択できるような工夫をしてみましょう。VLOOKUP関数ではなく、HLOOKUP関数を用いると、簡単にできるようになります。

エクセルで だれでもかんたん地図グラフ の作り方

○基本編です

ここでは、１つのセルを１地区にみたてて、地図グラフの仕組みを説明します。
マクロは使いませんし、もちろんアドインソフトも使いません。
エクセルの機能と関数だけですから、誰にでもすぐに作ることができます。

１．地区の白地図を作ります。
　ここでは、１地区を１セルにするので、セル幅を大きくして罫線を引けば完成。

２．色塗りのための基準値を入力する場所と、地区データの一覧表を作ります。

３．色データの欄に、以下の数式を入力します。

　　　=if(地区データ)>=基準値,1,0)

※　地区データが基準値を満たす場合に、色データを１とし、そうでなければ色データを０にしています。

　　セルＨ８の場合は、　=if(G8>=$F$5,1,0)　

４．地区の白地図全体に、以下の条件付き書式を設定します。

（１）白地図部分のセルをＡ１からＣ３までドラッグして、地図全体を範囲指定します。

（２）「書式」→「条件付き書式」を選択し、条件の欄を「数式が」に設定します。

（エクセル２００３の場合）

（エクセル２００７の場合は「ホーム」の「条件付き書式」）

（３）数式を入力する欄に、以下を入力します。

　　　=vlookup(A1,$E$7:$H$14,4,false)=1

※　vlookup関数で、白地図の地区名と一覧表の地区名が一致する行を選び、その四列目の値（色データ）を得ています。
※　セルの値（地区名）に該当する地区の色データ＝１の時に、そのセルに好きな書式を設定することになります。

（４）好きな書式を設定します。
「書式」ボタンを押し、好みの書式（パターン）を選択します。

５．基準値に数値を入力すると、白地図の該当地区（セル）が、パターンで設定した書式（色）に変わります。

かんたんなので、ぜひ、やってみてください。

エクセルで かんたんに地図グラフを作る

地図グラフ。
白地図に、地区ごとのデータを表示するために、該当地区に色を塗って表すものです。

世界地図や都道府県の白地図、市町村や集落の位置図などいろんな図面があります。

私たちの仕事で使えそうな場面では、集落営農の話し合いを進めるために、地区内の「ほ場図（農地の図面）」に、栽培されている作物別に色を塗ったり、今年の生産計画を品種別に検討したり、遊休農地を表示したりと、とにかく役に立つ図面なのです。

今では、地区内のほ場図は集落営農を実践している地域では必須アイテムになっています。
黒板や白板として作ったり、その都度図面を壁に貼って色を塗ったりしていますが、パソコンで簡単にシミュレーションできるソフトがほしいと、ず～～～～っと考えていました。

専門業者のシステムは数百万円から数千万円とかなり高額です。農業委員会などに導入されていますが、一農家の手にとどくものではありませんし、高度な機能も必要ではなく、エクセルデータを元に色が塗れればそれでいい。

わが島根県では、10年くらい前に県がほ場図作成＆色塗りシステムを試作しましたが、パソコンのパワーがかなり必要な代物で、とても実用には至りませんでした。

今では、インターネットで簡単に安価な色塗りソフトが手に入ります。しかも、エクセルのアドインで動くという優れもののようです。
しかし、その図ときたら、四角い地域に色を塗っていくものがほとんどで、なんだか物足りない。きちんと境界線を描いた白地図に色を塗りたい！

そんな思いから、自分で作ってみることにしました。

もちろん、「エクセルの機能と関数だけを使ったもの」、というコンセプトはいつものとおり。
（エクセルが動く機械なら、誰でも自由に修正して自分用にバージョンアップできるように、マクロは使わないのが基本。）

そしてできたのが、上の地図グラフ・試作バージョン1号です。
上部中央のセルに、基準値を入力すると、その基準値以上の地区に自動的に色を塗るというもの。
基準値をいろいろ変えてみると、いちいち色が変わってゆき、結構楽しく分析やシミュレーションができます。

台帳から一覧表を作ってみた

まず、台帳のシートをたくさん入力しなければならない。
本物で実験すると、失敗したとき落ち込みそうなので、モデルでやってみる。

様式のシートを作っておいて、店ごとにシートをコピーして入力していく。

本物では、紙ベースの台帳が２００ページくらいあるのを、１シートづつ作っていくことになる。
作業は単純で、骨が折れそうだ。

この業務のきまりでは、該当する店ごとに台帳を作っておくことになっている。
さらに、その内容が変更になったときには、直ちに台帳を修正しなければならない。

そして、そのなかから毎年いくつかピックアップして巡回する必要がある。
当然、同じ店ばかり回るのは効率が悪いので、まだ回っていない新しいところをピックアップすることになる。
一覧表が必要だ。

これまでは、紙ベースの台帳をもとに、エクセルで一覧表を作っていたらしい。
エクセル管理していた事務所も、一覧表は別に入力されてたようだ。

前任から引き継いだ一覧表は３種類。（３事務所分）
内容が一緒のはずなのに、それぞれに工夫がしてあって、
・・・違ってる。

あ～、やっぱりだ。

これらの作業を一括して、みんなが知ってるエクセルで、
しかもマクロ無しで単純化したい。

「台帳を入力（修正）すると、自動的に直近の入力を一覧表に反映してくれる。」

こんなのできれば、楽になるだろうな～。
と、妄想しながら、使えそうなエクセル関数をあれこれ探したりしているのだ。

そしてみつけた！！　INDIRECT関数だ。

=INDIRECT(参照文字列)
=INDIRECT($A$3)　→ セルA3 の参照セルの値を返す

=IF($B3="","",IF(INDIRECT("○○!B3")="","",INDIRECT("○○!B3")))

参照文字列で指定したセルの内容を、表示してくれる関数なのだ。
他のシートのセルでも、見つけて取ってきてくれるので、都合がいい。

実際には、台帳に記載する店は、閉店したり、新規開店で追加されたりする。

「シートの名前を入れるだけで内容を表示し、シート名を削除すると空欄になる。」

こんな関数がほしかった。
そして、これはバッチリだ。

しかし、参照文字列にシート名をいちいち打ち込まなければらないのはつらい。
つまり、台帳の項目が単純に一列に並んでないので、セルコピーだけでは、参照文字列（セルの指定）がうまく指定できない。

打ち込んでみて分かったこと。
「これは、入力するのにメチャクチャ手間のかかる、致命的な関数じゃないか！！！！」

セルコピーで簡単に大きな表を作りたい・・・・・
参照文字列を、一定のルールで入力する関数はないもんかな～。

あった。

ADDRESS関数だ。
行番号と列番号で指定したセルの参照を「文字列で返します。」
と、説明がある。

=ADDRESS(行番号,列番号,参照の型,参照形式,シート名)

=ADDRESS(2,3,1,FALSE,"EXCEL SHEET")
→　行は絶対参照、
　　 列は相対参照の R1C1 形式のアドレスを表す文字列
　　 別のシートへの絶対参照を表す文字列で、
指定したセルの参照を返す
　 ('EXCEL SHEET'!R2C3)

=ADDRESS(3,2,,,T($B3))

セルＢ３に入力したシート名○○から、そのシートの３行２列（つまり 「○○！$B$3」）という住所を、文字列で返してくれる。
とても便利な、私のためにあるような関数だ。

いける。
この文字列で、行きたいセルの住所を作ってやれば、
「=INDIRECT(セル住所)」　ができあがる！

=IF($B3="","",IF(INDIRECT(C18)="","",INDIRECT(C18)))

一覧表が完成した。やったね。
あぁ　達成感。

ん？・・・　
以前にもこんな気分が、、、　あっ　年賀状の時だ。

本当の仕事は、これから始まるんだった～ょね。
おんなじオチで、申し訳ありません。

台帳から一覧表をつくる手順

宍道湖岸の飛行場

夕方になると湖の方から着陸する。便数が少ないので時間帯が少しずれると見ることが出来ない。
つまり、ほとんど毎日、この道路を通っている割には、着陸するところを見ていない。
たまに見かけると、夕日に輝いてとても美しい。

仕事でエクセルを使っているはなし。
３カ所の事務所がひとつに統合されたので、それぞれに作られていた３カ所分の台帳をひとつまとめることにした。
様式が決まっているので、簡単なはずだけれど、エクセルで管理しているものが１つ、紙ベースが２つ。
しかも、エクセル管理のものは、記入項目が多く設定してあり、正式な台帳とは様式が違っている。

正式な台帳のエクセル様式をつくって、新たに３つのデータを入力していくのは面倒くさいので、すでにあるエクセルにあわせることにしよう。　とすると、紙ベースの台帳には記載していない項目（あたりまえだけど）がある。
しようがない。
ボチボチやってみるか。

パワーポイントでビンゴゲームをやりたい その２

パワーポイントでアニメーションを設定する。

その前に、背景に全部の数字を配置しておく。

ビンゴって全部でいくつまで数があったっけ。
それが決まらないとエクセルで順番がつけられないよ。
まあ、後で調べればいいか。

とりあえず、６０個の升目を作っておいて、ここに順番にエクセルからセルをコピー（もちろんリンク形式で）して、いちいち貼り付けていく。
試作品ということで、面倒くさいので５個くらいでやめた。

まず、このページをいくつもコピーして、たくさんの（升目の数程度）同じページをつくっていく。
次に、「ビンゴゲームを開始し、最初の数字を引く」ページを作るため、１ページ目の作業だ。
初めの升に入っている数字だけ残してすべての数字を削除する。
そして、ひとつ残った数字を画面の中央にコピーして、それを大きく拡大する。　これが、きのうの画像だ。

次のページは、数字を２個残して、２個目の数字を同じように中央に拡大コピーしておく。
これを繰り返して、順番に数字が増えていくページの流れを作っていく。

さて、この数字達にアニメーションをつけよう。

「数字を引く」作業はクリックでいい。クリックすると、中央にビヨヨ～ンと大きな数字が出てくる。これは、アニメーションの「開始」設定で簡単だ。
無難な「ディブルゾン」で、いい感じだ。　これが、上の画像。

中央の大きな数字が出てくるときには、背景の升目には同じ数字はないほうがいい。
本来のゲームは、数字を引いたあと、その数字をあとで確認するために、黒板に書いておくじゃないか。

ってことは、大きな数字がその升目に吸い込まれていくアニメーションができれば、フム、かっこいいぞ・・・

・・・・　　・・・・・　う～ん・・・・。
ひとつの動作では無理だ。
じゃあ、中央の数字がフェードアウトするとともに、升目の数字がぼんやりと出てくるってのはどうよ！！

・・・およ、出来るじゃないか。
大きな数字にアニメーションの「終了」設定で「フェードとズーム」をつけてみる。同時に、升目にある小さい数字に「開始」設定の「フェード」をつけると、
なんと、大きな数字が徐々に消えていくと同時に、升目の定位置に同じ数字が浮かび上がってきたではないか。
形が出来ていくのは、なんとも気持ちがいいなぁ。

これで、めどが付きそうだ。
文字の飾りがエクセルに依存していてイマイチなことや、いちいちエクセルから形式を選択してコピーしなけりゃならんことなどなど、妥協点は多いけれど、一応の完成と言うところでここらで手を打っておこう。

実際の宴会は、他のゲームをやるみたいなので、乗りもイマイチってとこで。
中途半端っぽい終わり方が好きなので、このシリーズおしまいだ。

きょうのわすれもの

カメラをわすれた。
これは、先々週だったか撮った国道９号線。
前をしばらくバイクが走ってた。
すこし、ノスタルジック。

エクセルのセルデータをパワポにリンク表示させるのに、エクセルからのコピーで出来ることが分かった。
パワポからハイパーリンクでできないか、いくら考えても無理そうだったので、エクセルの解説本を眺めたり、パワーポイントの解説本を見ていたときだ。

まず、エクセルのコピーしたいセルにあわせて、コピーを選択（クリック）する。
つぎに、パワポ画面で、編集→形式を選択して貼り付け→リンク貼り付け　をクリックして貼り付ける。
こうすることで、エクセルのデータを更新すると、パワポにコピーしたデータも自動的に更新されるのだ。
わかってみれば、単純な。　「な～んだ」って話。

こうなると、あとはパワポでの盛り上げアクションで仕上げだ！！

とりあえず、ランダムに選んだ数をならべて表示しておくスペースを作った。
これは、表の挿入でできる、基本技だ。
この表に、さっきからいちいちコピーしてきたエクセル数値（テキストボックス）を配置していく。
ビンゴの選択数値の表示板、最終形態ができあがった。

さて、アニメーションを設定してみよう。・・・・・・・・
・・・・・

美しくな～い。
順番に動くけれど、おもしろくない。

アニメーションの設定→アニメーションの軌跡、その他のアニメーションの軌跡、対角線・・・・
アニメーションの設定→効果の追加、開始、・・・・・・

だめだ。

ビンゴゲーム！！開始！！　の合図で、
マウスをクリックしたら、画面全体に大きな数字が表示され、
しばらくすると、その数字が後ろにある升目に順番に並んで収まっていく。
そんな動きがほしい。

まだまだだな。

接写にたえるブタでいたい

パワーポイントでランダム関数は使えないみたいだ。
それなら、エクセルでつくった数値にリンクさせればいい。

パワーポイント内でも、ハイパーリンクで絵（数）が出てくるページに飛ばすことが出来る。
その絵に、エクセルからランダムな数値をリンクさせれば、順に表示するだけでいいような気がする。

と、構想は出来つつあるけれど・・・
エクセルのセルにリンクする方法が分からなかった。

ブタの蚊取を出した。
今日も雨模様だ。

パワーポイントでビンゴゲームをやりたい その１

また、仕事が変わった。
転勤だ。
事務所３カ所がひとつに統合された。

今度は、台帳整理が必要になった。
古い台帳が３カ所分ある。そのうち、２カ所分は紙ベースだ。
これをひとつのデータベースにしたい。
担当者が変わっても使えるようにエクセルで、しかもマクロを使わず、関数だけで一覧表作成と集計ができるようにしておきたい。

１週間かけて入力する様式を作り、そして印刷様式への転送を仕上げ、さらに１週間かけて一覧表に落とすところまで、おおむね完成した。
おかげで、いろんな関数を組み合わせて、結構イケル技を開発できた。
（これらは、今後少しずつ紹介したい）

そんな日常の中、
某移植患者のつどいを企画する機会があり、宴会ゲームの話になったとき、パワーポイントでビンゴゲームの数字を、ビロビロ～ンって出せたらおもしろいな。
と、思い立った。
実際の宴会ゲームは、他の幹事が考えることになったが、宴会関係無しでもやってみたい！
しかし、パワーポイントでランダムの数字をクリックするだけで生じさせ、アニメーションでビロビロ～ンって出すことなんて、我が頭脳では不可能だ。
ひとつづつ、やってみよう。

というわけで、今回は、エクセルを使ってランダムに重複しない数字を発生させることはできないか、やってみた。

無理だった。
エクセルの関数だけでは、こんなことはできない。工夫が必要だった。
とりあえず、１５までの数字をならべたい。

=INT(RAND( )＊15＋1)

重複する。
あたりまえだった。
整数を１５種類ならべるセル範囲の中で、前のセルで作った数（複数）に、今のセルでランダムに発生させた整数が含まれているとき、違うランダムな数を作る。
そんな関数なんか無かった。

１列ですべてを終わらせようとするから出来ない！のはわかっていた。

１から１５までを、とりあえず順にならべておき、その範囲で２カ所ずつランダムに入れ替えながら、数回実行するとそのうちグチャグチャな並びになるんじゃないかと思って、やってみた。

並べ替えが出来る関数を探したり、手動でならできるのにぃと、頭をたたいても、どうしていいのか、さっぱり浮かばなかった。
これを、年齢のせいにはしたくない。

ふと、インターネットで他のＨＰを流していたら、賢い人の技を見つけた。
並べ替えるのではなく、順番をつけていくんだ。

やってみた。
おおっ！！！

１から１５までの整数が、ランダムに、しかも重複せずに並んでいる。
この技を忘れないように、
そして、しまったところを忘れないように、ココログに残しておくことにした。

さて、
今度は、この数字をパワーポイントでビロビロ～ンって出すためには、そして、出した数字を画面の隅に順番に並べておくためには、ドーしたらいいの？

誰か教えてくださいな。