ドント方式をエクセルで確認(Excel2000以降）

昨日は、第４５回衆議院選挙の投票日でした。


さて、比例代表では、政党の得票数に応じて議席を割り当てるのですが、日本は、ドント方式を使って議席数を決定しています。

簡単に言うと、各政党の総得票数をそれぞれ１、２、３、４、という正数で順次割っていき、割った得票数の大きい政党順に議席を配分します。
得票数の多い政党にも少ない政党にも、比較的公平に議席を割り当てられるのが特徴とのことです。



それでは、実際にエクセルを使って、仕組みを確認してみましょう。

A党B党C党D党が、以下の得票数だったとしましょう。

この4党が、5議席を争っているとします。




各党の得票数を1から順番に割っていきます。

計算式をうまく使ってくださいね。





割った数字の中で、大きな数字５つまでを探します。その数字の数が議席数となります。

セル範囲の中で、5番目に大きな数字はどれでしょうか？

それは、LARGE関数を使うと答えが出ます。

今回は、B13に、答えを出してみました。350　ということが分かりましたね。






それでは、350以上の数字が一目で分かるように、セルを塗りつぶしてみましょう。

そうです。条件付き書式を使えばいいですね。

B4:E8を選択してから、条件付き書式（Excel2003以前は、書式メニューの中にあります）をクリックします。

Excel2007は、新しいルールをクリックしてください。




以下の①～③ように、条件を指定して、書式ボタンをクリックしてください。





今回は、条件に合致したセルに黄色の塗りつぶしをします。
皆さんの自由に設定してくださいね。

設定したら、OKボタンをクリックします。





黄色い色で塗りつぶされたセルが、350以上の値のセルです。

このセルの個数が、各政党の議席獲得数となります。

A党、B党ともに2議席、C党は1議席、D党は議席なしとなります。





ちなみに、議席数の数字を変更すると、それに応じて、各党の議席数が変わりますよ。

9議席にすると、A党が4議席獲得となりますね。







だい

フォルダのサイズを取得する(Visual Basic2008)

今日は、Visual Basicで、任意のフォルダのファイルサイズを取得します。

なお、Visual Studio2008を使用します。


まず、任意の名前で新規プロジェクトを作成してください。

次に、フォームに4つのコントロールを配置します。
テキストボックス2つとボタンが1つ、それにFolderBrowserDialogを使います。

FolderBrowserDialogは、フォーム上にドラッグすると、自然に下のコンポーネントトレイに移動しますよ。

コントロール名は、初期設定のまま今回は使用します。





それでは、Button1をダブルクリックしてください。
コードウィンドウが表示されます。

下のコードを入力してください。

---------------------------------------
Imports System.IO

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles Button1.Click
        With FolderBrowserDialog1
            .Description = "フォルダを選択してください"
            .RootFolder = Environment.SpecialFolder.MyComputer
            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                'テキストボックスに選択したフォルダのパスを表示
                TextBox1.Text = .SelectedPath
                Dim di As DirectoryInfo = New DirectoryInfo(.SelectedPath)
                'DirSizeを使って、ファイルサイズを取得して
                'テキストボックスに表示（書式もついでに設定）
                TextBox2.Text = Format(DirSize(di), "#,##0バイト")
            End If
        End With
    End Sub

    Private Function DirSize(ByVal d As DirectoryInfo) As Long
        Dim Size As Long = 0
        'ファイルのサイズを取得
        Dim fis As FileInfo() = d.GetFiles()
        Dim fi As FileInfo
        For Each fi In fis
            Size += fi.Length
        Next fi
        'サブフォルダのサイズを取得
        Dim dis As DirectoryInfo() = d.GetDirectories()
        For Each di As DirectoryInfo In dis
            Size += DirSize(di)
        Next di
        Return Size
    End Function
End Class
---------------------------------------

一応画像を載せておきます。





コードの解説は、上の画像をご覧ください。

フォルダのサイズを取得する関数は、Visual Basicでは提供されていないので、
自分で作るしかありません。
再帰呼び出しという手法を使うと、達成できます。

それでは、F5キーを押して、デバッグしてください。

フォームが表示されます。

選択ボタンをクリックしてください。





フォルダ選択画面が開きます。

任意のフォルダを選択してください。
ただし、ファイルサイズの大きなフォルダを指定すると、このあと表示されるまで結構時間がかかります。

ですから、最初は、サイズの小さそうなフォルダを指定するようにしてください。


今回は、マイドキュメントの中の Blog というフォルダを指定しました。





OKボタンを押すと、ほら・・・





下のテキストボックスにフォルダ内のファイルの合計サイズが表示されますね。


僕としては、エクスプローラの詳細表示では、フォルダのサイズも表示してくれるとありがたいのになあ、と思います。

いちいち、プロパティ画面を表示しないとファイルサイズが分からないのが面倒くさいです。

フォルダのポップアップを待っているのも、いまいち・・・



だい

西暦から干支を知る（Excel)

みなさんおはようございます。

今日はExcelで関数を使って干支を調べてみたいと思います。

なぜか急に思い立ってやってしまいました。

今年は「丑年」ですね（＾ｖ＾）

↓このように考えます。

西暦2009年を12で割って余りを求めると、5です。

西暦2010年を12で割って余りを求めると、6です。

西暦2011年を12で割って余りを求めると、7です。

余りが5の時は「丑」、余りが6の時は「寅」、余りが7の時は「卯」というようになりますね

余りの数字をうまく干支に割り当てていけば、西暦で十二支がわかりますね。

さて、そんなふうに数値(インデックス)で値を割り当ててくれるのがCHOOSE関数です。

=CHOOSE(インデックス,値1,値2,値3・・・）

インデックスを使って引数リストから値を選択してくれます。



さぁやってみますよ（＾ｖ＾）



はい、オートフィルでコピーして出来上がり～



いつ使うかは、？ですが、CHOOSE関数は便利ですね。



さて、話はコロッと変わりますが、

家で私がムキになっている？・・・

ゲームをご紹介します。

ゲームセンターでは今でも「太鼓の達人」は流行っていますか？（古いかなぁ）

似たようなゲームのパソコン版なのです。

興味のある方は、よかったら（＾ｖ＾）こちらからどうぞ！↓

太鼓のオワタツジン

スクロールして、曲名から「CHANVALA 1612」を選びましょう（オススメ）

難易度をキーボードから入力してGO！

・・・

・・・

そんなわけで、今日はExcel関数のお勉強でした。

みなさん、今日も一日を楽しく過ごしましょうね。GO！

mihoりん

HDDをSSDに交換 (ノートPC Vista編)

今回はVistaのノートPC編です。SSDは同じIntelの「X25-M」の80GBです。

PCは、NECの「LaVie G タイプL アドバンスト」というモデル。
これも3年ほど前のPCです。しかし、ノートPCにしては珍しくHDDをもう一台増設できるんですね。

元々はXPがプリインストールされていたPCですが、Vistaにアップグレードして使っています。
メモリは1.5GBに増設してありますが、本当は2GBは欲しいところです。

最新のVistaがプリインストールされているPCに比べればスペックは低いです。


どうせだから今回はHDDの交換と増設の方法も画像付きで解説しますね。同じ機種を使っている人はご参考に。

まずは底面のネジを外します。おっとその前にバッテリーを外すのを忘れないで下さいね。

画像の1～4のネジを外します。



1と2のネジが赤で囲んであるカバーを止めてあるネジです。



カバーを外すとHDDがあります。(画像ではすでにSSDに交換済みですが）



3と4のネジがHDDを固定しているネジです。このネジは最初に外してもカバーを外してからでもどちらでもいいです。

矢印部分を矢印方向に引くとHDDが外れます。



先ほどの5のネジを外したところにHDDを増設出来ます。（これもすでにSSDを増設済みの画像ですが）






さて、それではHDDからSSDに環境移行をします。

まずはSSDを増設部分に入れます。
そして「Acronis True Image 11 Home」で環境を移行します。

それから、バラしてHDDとSSDを交換します。あとはもう一台SSDを増設して終了です。
結構簡単でしょ？その後に増設したSSDにデータ類を移す作業がありますが・・・


今回は交換前と交換後の起動時間を計るのを忘れてしまいました。(^_^;)

しかしXPと違い、boot画面のプログレスバー(左から右に流れるバー）の回数は交換前と変わりないです。
デスクトップ画面が表示されてからは速くなっていますが。

Vistaでは「SuperFetch」と言う機能のおかげでXPに比べてHDDがボトルネックにならないんですね。


次はベンチマークです。



このPCも前回のノートPCと同じくサウスチップが「ATI SB450」。やはりこのSSD本来の性能が発揮できないですね。

ちなみに↓がHDDのベンチマークです。





このPCもこのSSD本来の性能は発揮できないけど、HDDの時に比べると快適にはなりましたよ。
でも、XPのPCよりは実感が少ないですが・・・

以前にインストールした「Photoshop CS3」が10秒で起動します。(自作PCだと3秒ですけどね）


今回はSSDをもう一台増設出来た事が収獲だったかな？



（ケン）

ユーザーアカウントの画像を手早く変更する方法(WindowsXP)

さあ、今日は、息抜きのようなネタをお送りします。

みなさんは、アカウントの画像は、何をお選びですか？

アカウントの画像は、起動したときや、スタートメニューの上端に表示されます。

ためしに、今、このブログを見ながら、確認してみてください。

画面左下のスタートボタンをクリックすると、スタートメニューが表示されますよね。

そのいちばん上を見てください。

ちなみに、僕は、こんな画像ですよ。





今日ご紹介するのは、この画像を簡単に変更する方法です。

それでは、さっそくやってみましょう。

まずは、スタートメニューのこの画像をクリックしてください。

はい、これだけです。

これで、ユーザーアカウントの画像変更のウィンドウが表示されてしまうのです。





本来なら、コントロールパネルから遷移してくるところを、こんなに近道できてしまいましたね。

あとは、好きな画像をクリックして、「画像の変更」ボタンを押すだけです。
もちろん、画像をダブルクリックするのもオーケーですよ。





この画面を閉じて、はい、おしまい。





それでは、ちゃんと画像が変更できているか確認してみましょう。

スタートボタンをクリックして、スタートメニューを表示してみましょう。


すると、ちゃんと変更されていますね。(^^)




まあ、いったい、何の意味があるのかと言われると困ってしまいますが、
気分転換ということで、今日は、これにて。



だい

結合しなくても中央揃えは可能です（Excel）

おはようございます。

今日もExcelのお勉強ですよ(^.^)

Excelの表にタイトルをつけるときに、表全体の中央にあわせて配置するために

よく「セルを結合して中央揃え」をつかいませんか？



実はムリにセルを結合しなくても、タイトル文字を表の中央に配置する方法がある

んですよ。

セルの結合により、コピー/貼り付けなどがうまくいかないなど、他の機能の邪魔

をしてしまうこともあるので、よかったら覚えてくださいね。(^.^)

表の左端のセルに（A2)　タイトルを入力し、表の横幅と同じ範囲を

選択する（A2：E2）

「セルの書式設定」ダイアログを表示する。（ショートカットキーで、Ctrl＋１）

「配置」タブをクリック→文字の配置欄で、「横位置」を「選択範囲内で中央」を選択し→OK



なんとなくC2のセルに文字が移動したように見えますが、選択してみるとわかりますが、

A2のセルから飛び出して表示されているだけです。文字自体はA2のセルにあるんですよ(^.^)

ねっこれもOKでしょう。

さぁ今日も一日楽しく過ごしましょうね。

mihoりん

「Internet Explorer 8」のお節介な機能

すでに「Internet Explorer 8」を使っている人も多いかと思いますが、新機能で非常にお節介な
機能があるんですね。

ある日、ブログを見ると何か変？

文字も大きくなっているし、キャプチャした画像の文字がボケボケになっているんです。

最初はモニタが変になったのかと思いましたが、調べてみるとメニューの「表示」に「拡大」と
いうのが「Internet Explorer 8」から追加されているんです。

それが勝手に125％になっていました。



自分では何もいじった覚えはありません。
どうも「Internet Explorer 8」では、これが勝手に変更されるらしいです。

「ふざけるなぁ！」と思いながらメニューの「表示」→「拡大」を100％に戻しました。

これが125％の時のキャプチャ画像です。文字がボケボケでしょ？



これは100％の時のキャプチャ画像です。




どうです？見比べてみると全然違うでしょ？

「Internet Explorer 8」を使っている人は変だと思ったら時々チェックしてみて下さいね。



ついでですから、「Internet Explorer」のお奨め設定についても書きますね。これは全バージョン共通です。

「インターネットのプロパティ」の「詳細設定」タブで「スムーズスクロールを使用する」の
チェックを外して「OK」をクリックしてください。




「スムーズスクロールを使用する」にチェックが入っているとスクロールした時に、CPUの負荷が
上がるんです。特に激しくスクロールするとかなりCPU使用率が上がりますよ。

「スムーズスクロールを使用する」のチェックを外すと、大体CPUの負荷は半分くらいになります。

最新の高性能なPCなら影響は少ないですが、古いPCだとかなり違ってきます。


この設定は是非やった方がいいですよ。



（ケン）

電話番号データからハイフンを取りたい！Visual Basic編

今回は、前回の記事のVisual Basic編ですよ。


前回の記事：電話番号データからハイフンを取りたい！


それでは、Visual Basicを起動して、任意の名前でプロジェクトを新規作成してください。

表示されたフォームに以下のコントロールを配置してください。
テキストボックスを2つ
ボタンを1つ

こんな感じですよ。





コントロールの名前は、初期設定のまま使用しますよ。
ただ、ボタンの表示文字列は、「ハイフンなしにする」にしておきましょう。

TextBox1に、TELを入力し、Button1をクリックすると、ハイフンなしのTELがTextBox2に表示されるというわけです。



それでは、コードを記述していきましょう。

Button1をダブルクリックしてください。

Button1_Clickイベントプロシージャが自動的に生成されますので、
その中に、以下のコードを記述してください。

    Private Sub Button1_Click(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles Button1.Click
        If TextBox1.Text = "" Then
            Exit Sub
        End If

        Dim s As String = TextBox1.Text.Replace("-", "")
        TextBox2.Text = s
    End Sub

実際には、下の図の色のついた部分だけ入力すれば結構ですよ。





コードのポイントは、
TELが入力されたTextBox1の文字列に対して、Replaceメソッドを使うと簡単に出来るという点です。
このメソッドは、名前の通り、置き換えをしてくれるものです。
ハイフンを空文字列に置き換えるので、結果的にハイフンの削除となります。

あとは、ハイフンなしの文字列をTextBox2に表示するだけですよね。


それでは、確認しましょう。F5キーを押してデバッグを開始します。


TextBox1にTELを入力してください。







そして、ボタンをクリックします。

すると・・・





ハイフンなしのTELが表示されましたね。



だい

電話番号データからハイフンを取りたい！(Excel)

みなさんこんにちは。

先日のレッスンで、Excelに入力されている電話番号データを、"-(ハイフン)"のないデータ
にしてみよう！というお勉強をしました。

ちょっとおもしろかったので、今日の記事に取り上げました。

↓下図を見てください。このようにしたいと思います。




やりかたは・・・

まずは手っ取り早く、「置換」を使ってやりましょう。

"-" を "何も無いもの"に置き変えます。



すると・・あらら↓



ハイフンを取ったら、数値データになっていまい、頭のゼロが欠けてしまいました。

・・・・

それならと思い電話番号データのセル書式を「文字列」に変えて見ましたが、

文字は文字でも結果は↓このとおり・・



といわけで、「置換」機能は使えません。

関数を使いましょう。

SUBSTITUTE(文字列、検索文字列、置換え文字列)

「文字データの置換え」をしてくれます。

↓お隣のセルにこのように入力します。



=SUBSTITUTE(C7,"-","")

はい！いいじゃないですかぁ（*^_^*）あとは数式をコピーすれば出来上がりです。



生徒さんも私も「ほっ（*^_^*）」でした。

それにしても、Excelは奥が深いですね。

mihoりん

HDDをSSDに交換 (ノートPC XP編)

「Windows7 RC レビュー」で使用したノートPCをSSDにしたところ、かなり快適なので
自宅のPCのHDDを全てSSDに交換しました。(自作PCのデータ用以外)

今回はXPのノートPC編です。SSDはIntelの「X25-M」の80GBです。

PCは「dynabook AX/940LS」というモデル。(一応メモリは1GB 積んでます）3年ほど前のPCです。
「Windows7 RC レビュー」で使用したノートPCに比べてかなりスペックが低いPCですが、
SSDでどこまで快適になるか？


さて、それではHDDからSSDに環境移行をします。

会社にあった外付けのHDDケースにSSDを入れてAcronis True Image 11 Home
でクローンを作成しようとしたら・・・・


外付けHDDケースに入れたSSDが認識しない。外付けHDDケースを他のPC(デスクトップPC）に繋いでも駄目。

デスクトップPCにSSDを直に繋ぐとちゃんと認識します。
これは外付けHDDケースとの相性か？

近くの電機屋さんで他の外付けHDDケースを買ってきてそれでやったらすんなり認識しました。
USBの外付けHDDケースでは結構相性問題があるみたいです。

無事にSSDに環境移行できたので、ノートPCのHDDと交換します。
このノートPCはネジを1本外すだけでHDDの交換が出来るので凄く楽です。


交換後に、まずは起動時間を計ってみました。

デスクトップが表示されるまで。HDD→47秒　SSD→32秒
全ての常駐ソフトが起動するまで。HDD→1分50秒　SSD→1分30秒。

このノートPCは「XPをVista風にカスタマイズ ここまで出来る！」の記事で使ったPCなので
かなりスタートアップに常駐ソフトが入っています。

ちなみに、boot画面のプログレスバー(左から右に流れるバー）の回数は10回から3回まで
短縮されました。

次はベンチマークです。



思ったより速度がでていない。これには要因があるんです。

サウスチップが「ATI SB450」(現AMD)。　これは「SATA I」なので、最大転送速度が150MB/sなんです。

これではSSD本来の性能が発揮できないですね。

ちなみに↓がHDDのベンチマークです。



大分違いますね。

SSD本来の性能は発揮できないけど、HDDの時に比べるとかなり快適にはなりましたよ。

XPのノートPCの場合、HDDの性能がボトルネックになっているんです。



（ケン）

急にインターネットがつながらなくなったときの対処法

さて、昨日、朝に生徒さんからの電話が鳴りました。

急にインターネットがつながらなくなったとのこと。アドバイスを求められました。



昨日まで接続できたのに、今朝急につながらなくなったという場合は、
大体、パソコンサイドに原因はありません。


まずは、パソコン以外の装置、つまり、そうです、モデムとルーターを疑いましょう。

モデムとルーターの電源をOFFにします。
スイッチがない場合は、電源ケーブルを本体から抜いてしまいます。

そして、そのまま10秒待ちます。ここが重要なポイントです。

いいですか、最低でも10秒は待ちましょう。


それから、おもむろに、電源を入れます。

装置が作動するまでに、約1分間かかりますので、しばらく待ちます。

装置のランプを見ていると、やがて緑系の色だけになります。
（装置によって異なりますので、このランプの色は参考程度にしてください）


それでは、改めて、パソコンでブラウザを起動してみてください。

うまくWebページが見れたら、成功です。



それ以外の原因として挙げられるのは、

■LANケーブルがきちんと差し込まれていない


このケースも、時々あります。特に、ケーブルの接続部のツメが折れている場合に多いですよ。


■パソコンに常駐しているウィルス対策ソフトのファイアーウォールが通信を遮断している。

普通は、大体問題ないのですが、まれに、この原因で接続できない場合があります。

その場合は、ファイアーウォールを無効にしてください。




だい

USBメモリに保存したファイルの管理

皆さんは人に見られたくないファイルやフォルダをどのように管理していますか？

特に持ち運び便利なUSBメモリは、落としたりしてしまったら心配ですよね。

（なるべく落とさないように注意しましょうね）

パスワード認証やデータの暗号化が出来るUSBメモリを持っていらっしゃる方は

必要ありませんが、私の持っているUSBメモリはそのような機能がないため、

「自己セキュリティ対策」を施さなければなりません。

あまり知られてはいませんが、「圧縮フォルダ」機能を使うと、フォルダに

パスワードをつけることができます。

大切なファイルをパスワード付きの圧縮フォルダにしておけば、パスワードを知っ

ている人しか中身を見ることが出来なくなるため安心です（*^_^*）

やり方は簡単ですよ。

大切なファイルの上で右クリック→送る→圧縮フォルダ(zip形式)

すると、同じ場所に圧縮フォルダが出来ますから

圧縮フォルダを右クリック→エクスプローラをクリック

圧縮フォルダのウィンドウが表示されますから→ファイルメニュー→パスワードの追加を選択





ここで好きなパスワードを指定してください。

こうしておけば、もう一度圧縮フォルダをダブルクリックし、解凍しようとした時に、パスワードを求められます。

パソコン上でこの操作をし、圧縮フォルダをUSBに保存しておけばいいですね。

元のファイルはパソコン上にありますから（*^_^*）

ただ、注意したいのはパスワードを忘れないことですよ。

外出先でUSBメモリ内のファイルを見れないのは悲しいですから・・

そんなわけで今日も一日がんばろうっと（*^_^*）

mihoりん

メーカー製PCの古いHDDの活用 (XP・Vista共通）

前回の記事でメーカー製PCのHDDを大容量に交換しましたが、今回は外した古いHDDの活用方法を
書きます。

一般的には外付けのHDDケースに入れてバックアップ用にするのがいいでしょう。
HDDケースは3.5インチ(デスクトップ用)が3,000円くらい、2.5インチ(ノートPC用)が2,000円くらいで買えます。

まずは、古いHDDをバックアップ用に使えるようにします。

メーカー製PCのHDDには「リカバリ領域」や、一部のメーカー製PCのHDDには「EISA構成」という領域が存在します。

※EISA領域は万が一のためのリカバリ領域であるという性質上、安易に「ディスクの管理」から
削除できないようになっています。

今回は、この「EISA領域」の削除方法も解説します。

　
マイコンピュータを右クリックして、「管理」→「ディスクの管理」をクリックします。
メーカー製PCの場合はいくつかのパーティションに分かれているので一つずつパーティションを削除します。









次に「「EISA構成」のパーティションを削除するんですが、右クリックしても「ヘルプ」しか現れません。





しかし、コマンドツールの 「diskpart.exe 」から削除することが可能です。

1.「ファイル名を指定して実行」に 「diskpart」と入力 して、「Enter」。
2.「list disk」と入力して、「Enter」。ディスク一覧を表示。EISA領域が存在するディスクの番号を確認する。
3.「select disk 番号」を入力して、「Enter」でディスクを選択。
4.「list partition」と入力して、「Enter」。今度はEISA領域のパーティション番号を確認。
5.「select partition 番号」を入力して、「Enter」。EISA領域のパーティションを選択。
6.「delete partition override」と入力して、「Enter」。パーティションを削除。
7.「exit」と入力して、Enter」。「diskpart.exe 」を終了。

以上の手順で「EISA領域」を削除できます。



「ディスクの管理」をみると「EISA領域」が削除されています。



あとは、「ディスクの管理」で、HDDを使えるようにします。以下は初心者向けに画像で解説しますね。

まずは「新しいパーティション」を作成します。



「新しいパーティション ウィザード」で簡単に出来ますよ。

















これで、バックアップ用で使えるようになりました。




USBの外付けHDDでは転送速度が30MB/sくらいしかでないんですね。
※理論上の最大転送速度は60MB/sですが。

だから古いHDDでも実用にはそれはど問題になりません。古いHDDはもっと遅いですけどね。(^_^;)

ただし容量の問題はありますが。最近の外付けHDDの容量は1TB以上のもありますから。

それにしても外付けHDDは安くなってますね。1TBでも10,000円以下で買えちゃいます。

こんな面倒な事をしなくても市販の外付けHDDを買った方がいいかな？(^_^;)

しかし、市販のUSBの外付けHDDは中のHDDの転送速度が100MB/sを超えているのが一般的に
なっているので、HDDの性能を活かせないのが残念ですね。

HDDの性能を活かすなら、多少高いですがeSATAで接続できる外付けHDDがお勧めです。


ちょっと話が脱線しちゃいましたが、古いHDDも捨てないで活用しましょうね。



（ケン）

お盆休みのお知らせ

信州は、今日もいい天気ですよ。

さて、13日から16日まで、お盆休みを頂きます。

ブログもお休みとなります。

もし、よろしければ、過去の記事をご覧ください。



それでは、楽しい毎日を・・・





だい

郵便番号を入力すると自動的に住所を表示する方法-その4 WebAPIの利用(Excel2002以降）

ようやく暑くなってきましたね。

今日は、郵便番号を入力すると自動的に住所を表示する方法-その4をご紹介します。

今回は、WebAPIを使用します。

郵便番号検索APIというサイトで、
無料で提供されている郵便番号から住所を検索するWEBサービスAPIを使わせていただきます。

このようなサービスを提供してくださるサイトのおかげで、
僕たちは、便利さを実感できるのです。

感謝しながら、紹介させていただきます。

なお、前提として、インターネットに接続できる環境が必須です。
（つまり、インターネットにつながっていないPCでは、今回の方法は使えません・・・）

また、この記事の最後に、サンプルファイルのURLを載せておきます。

手っ取り早く体験したい方は、そちらもどうぞ。


さて、始めますよ。

エクセルには、以下のような表があるとします。

B列の郵便番号に対応した住所がC列に表示されるようにします。



それでは、ALT + F11キーでVisual Basic Editorを起動します。

「挿入」メニューの「標準モジュール」をクリックします。


ここで大事なことがあります。

必要なプログラムの部品を使用可能にします。

「ツール」→「参照設定」とクリックします。

ダイアログボックスの一覧の中から、「　Microsoft XML v5.0　」を探し出してください。
アルファベット順に並んでいるので、かなり下のほうまでスクロールしないと出てきません。

見つけたら、左のチェックボックスにチェックを入れてください。

そして、右上のOKボタンをクリックします。






これで、仕込みは完了です。

以下のように、コードを記述してください。

Function GetAddress(post As String) As String
    Dim req As New MSXML2.XMLHTTP
    Dim rs As String
    Dim post2 As String
    
    post2 = Replace(post, "-", "")
    rs = "http://zip.cgis.biz/xml/zip.php?zn=" & post2
    req.Open "GET", rs, False
    On Error GoTo eh
    req.send
    On Error GoTo 0
    
    
    Dim doc As New MSXML2.DOMDocument
    doc.async = False
    doc.LoadXML (req.responseText)
    On Error GoTo eh2
    Dim state As String, city As String, address As String
    state = doc.ChildNodes(1).ChildNodes(9).ChildNodes(4).Attributes.Item(0).Text
    city = doc.ChildNodes(1).ChildNodes(9).ChildNodes(5).Attributes.Item(0).Text
    address = doc.ChildNodes(1).ChildNodes(9).ChildNodes(6).Attributes.Item(0).Text
    
    GetAddress = state & city & address
    Exit Function
eh:
    Exit Function
eh2:
    GetAddress = "見つかりませんでした"
End Function

今回のコードは、いわゆるユーザー定義関数というものです。
内容は、郵便番号を先ほどのサイトに送り、返ってきた情報から住所を抜き出すというものです。

郵便番号が存在しないときは、見つかりませんでしたという答えになるようにしてあります。



それでは、Excelに切り替えてください。


C列4行目に先ほど作った関数を設定します。

C列4行目を選択して、fxボタンをクリックしてください。

ダイアログボックスが表示されたら、ユーザー定義　を選択し、

その下の一覧から、GetAddress　を選択して、OKをクリックします。





続いて、B4のセルを選択してください。引数欄に、B4が入ります。

そしたら、OKをクリックしてください。





すると、ほら・・・






住所が表示されますよ。

あとは、C4の関数を下までオートフィルしてください。完成です。






この関数は、郵便番号を変更したら、即座に住所が更新されるというメリットがあります。
その点で、今までの方法よりも優れているといえます。





さて、サンプルファイル（Excelファイル　38KB)を用意しました。

興味のある方は、こちらからどうぞ。

http://ux.getuploader.com/pc_college/download/26/PostToAddress.xls

ダウンロードパスワードは、3024 です。

Excel2002,Excel2003,Excel2007で動作確認をしましたが、すべての環境での動作を保証するものではありません。
あしからず。



だい