hetarena.com

ユーザー定義関数で解決！

先の VBA の話の実践適用例です。

例えば、「シート1」、「シート2」、…「シート10」までの 10個のワークシートにデータが入っていて、これを集計・操作した「サマリー」シートを作ることを考えてみます。 このような場面はよくあることではないかと思うのですが、ここで困るのがシートの参照の仕方です。 ご存知のように「シート1」のセル A1 は以下のように参照します。

シート1!A1

このようにワークシートはシート名で参照するので、素直に作業を進めるとワークシートのタブから名前をコピーしてセルに貼り付けるような操作が必要になってしまうわけです。 列は A、B、C、…、行は 1、2、3、… と参照できますが、ワークシートについては名前でしか参照できず、残念ながら「N番目のワークシートを参照する」みたいなワークシート関数も存在しません。

そこをもう少し表計算ソフトらしく処理するために「ワークシート名を取り出す処理」を考えようということになるのですが、それも簡単ではありません。 例えば、こちらにその方法が丁寧に説明されていますが、何だかなあ、という感じです。

こんなとき VBA を使って自作関数を作ることができれば、以下のような関数を作って呼べば済みます。

Function GetWorkSheetNameByIndex(i)
  GetWorkSheetNameByIndex = Worksheets(i).Name
End Function

1枚目のシートの名前は「=GetWorkSheetNameByIndex(1)」、2枚目は「=GetWorkSheetNameByIndex(2)」というように呼び出して使うことができます。 このようにワークシート関数だけでやろうとすると複雑になってしまう処理でも VBA を使えばシンプルでわかりやすくなる場合があります。

VBA ユーザー定義関数の再計算

ここで気をつけねばならないのですが、VBA で作成したユーザー定義関数は F9 を押しても再計算されません。 この問題に対処するために Application.Volatile という関数が用意されていて、これを GetWorkSheetNameByIndex の中で呼ぶとセル修正のたびにこの関数の呼び出しが再計算されるようになります。

しかし、どこかのセルを修正する度に再計算されることになるので、Application.Volatile を利用するかどうかは慎重に検討すべきです。 先の例ではサマリーシートを作成する段階であればデータ用シート名をそうそう頻繁には変えないでしょうから、わざわざ Application.Volatile を呼ばなくても関数を呼び出しているセルを編集し直して再計算という方法で十分でしょう。

おまけ

まあ、「同じ位置のセルを単純集計する」程度の話であれば、ユーザー定義関数を作らなくても 3-D 集計を知っておくだけで済むかも知れません。

Excel を使いこなしていくと「ワークシート関数だけではちと厳しいかな」と限界を感じるときがあると思います。 そんな時は気軽に VBA で関数を作っちゃいましょう。 作った関数はワークシート関数と同様に呼び出すことができます。 VBA 自作関数の書き方と呼び出し方を簡単に説明します。 (Excel 2007 以降を想定しています)

Visual Basic Editor を表示する

以下の手順で Visual Basic Editor を表示します。

1. Office ボタン (一番左上) から選べる「Excel のオプション」－「基本設定」で「[開発]タブをリボンに表示する」をチェックします。 続いて新たに表示された「開発」タブより「Visual Basic」を選択します。
2. Visual Basic Editor が起動するので、そこから「挿入」－「標準モジュール」を選びます。

ここまでで以下のように「Module1」が挿入されます。 この Module1 に関数を書いていくことになります。

関数を書いてみる

VBA の文法等について書き始めると収拾がつかないので、簡単にサンプルコードを書いてみます。 Visual Basic Editor の「Module1 (コード)」ウィンドウに以下のコードを書きます。 単純に 10倍の値を返す関数です。

Function myfunc(m)
  myfunc = m * 10
End Function

あとはシートに戻ってワークシート関数と同様に呼び出すだけです。

簡単ですよね！

おまけ

ちなみに VBA のコードの中からワークシート関数を呼び出すには、以下の形式を使います。

WorksheetFunction.ワークシート関数名

例えば、SUM(A1:A10) と同じことをしたい場合は以下のようになります。

Function sum_a()
  sum_a = WorksheetFunction.Sum(Range("A1:A10"))
End Function

範囲を表すのに Range 関数を使ったりするので、全く同じ感覚で使えるわけではないですが、VBA からワークシート関数を呼べるのは知っておくと良いと思います。 Range とか Cells とか Select とか使い始めるとどんどん VBA にハマっていくわけですが、ハマりたい人はとっかかりとしてこのあたりの記事を読むと良いかと思います。

仕事をしているとプロジェクターで Excel を映して議論を重ねるような場面もあると思いますが、意外と基本的な操作を知らない人が多いようなので、そんな時にもたつかないための基本操作をまとめておきます。 Excel 2007 以降のバージョンを想定しています。

リボンを隠す

ワイド表示のディスプレイ全盛の時代にあって、ますます邪魔に感じてしまう「リボン」インターフェイスですが、隠す方法 (正確には最小化する方法) をご存知でしょうか？ タブの部分 (「ホーム」、「挿入」、…) をダブルクリックすると非表示になります。

この状態で各タブを選ぶとメニューは都度表示されます。 元の動作に戻すには再度タブをダブルクリックです。

…って、これは Excel だけじゃなくて、Microsoft Office 全般の話ですね。

ズーム

作業用のシートをプロジェクターで映してみると小さく表示されてしまうので、拡大表示をしたくなりますよね？ そんな時、わざわざ「表示」メニューを選ばなくても Ctrl＋マウスホイール回転でズーム調整することができます。

これは Microsoft Office だけじゃなくて、Windows 全般で使える技です。(Excel じゃないじゃん…)

幅の調整

CSV ファイルを開いた後の「####」表示を一列ずつ幅調整して直していたりしませんか？ 以下の手順で一括して調整できます。

1. 左上コーナー (下図) をクリックして全セルを選択
   
2. 「ホーム」－(セル)「書式」－「列の幅の自動調整」

ご存知だとは思いますが、画面表示で幅が収まっていても印刷するとはみ出すことがあるので、印刷時はプレビューで確認する必要があります。 念のため。

とりあえずこれだけ覚えておけば、プロジェクター投影時も恥ずかしくないでしょう。 「こんな操作知ってる。レベルが低い。」とお感じの方は是非こちらの記事もご覧ください。

2011年末よりメインのパソコンが 64ビット版の Windows になったのですが、「ちょっとアテが外れたなあ」と思っていたのがカメラ用 Codec の EOS 60D 対応です。 この Codec (コーデック) があると RAW で撮影した .CR2 ファイルもエクスプローラー上のアイコン表示で写真のサムネイル画像が表示されるようになるのですが、Canon から提供されている「Canon RAW Codec」は 32ビット版のみ提供するという状態が今も続いています。

メーカーから個別提供されていない場合も、Microsoft から Camera Codec Pack なるものが提供されていていくつかのモデルはこれで救われていたのですが、残念ながら最近まで 60D には対応していませんでした。 しかし、それも昔の話となり、8月に登場した 16.4.1620.0719 でやっと Microsoft Camera Codec Pack が 60D に対応してくれました。 これでエクスプローラーから写真を探すのが簡単になります。

ファイル名が一瞬わかりにくいかも知れませんが、「amd64」と付いている方が 64ビット版用です。

amazon.com で洋書を入手

最近ソフトウェア版の Kindle で技術系の洋書をいくつか読んでいます。 amazon.com から書籍をダウンロードするわけですが、

• ダウンロードによる入手はお手軽。配送料も時間もかからない。
• たいてい Kindle Edition は Paperback より安価に設定されていることが多い。
• 技術系の本ならばたいした英語力要らない。
• amazon.co.jp で買い物したことがあればサイトのつくりと操作はほぼ同じなのでとっつきやすい。

っていうあたりが Kindle で洋書を読む理由です。 根本的には、

• 訳書は原書より高価になることが多い。
• 訳されるまでにタイムラグが発生する。
• そもそも訳されていない本がたくさんある。

ということがあるわけです。 最初は辛くても「英語で本を読めるようにしておかなければ」という危機感もあったりするわけです。

例えば「プログラミング Perl」の場合、今年 2月に 4th Edition が出ていますが、日本語訳はまだされておらず、今購入できるのは 10年以上前の 3rd Edition の訳本です。 価格も 4th Edition の Kindle 版は $25.19 = 約 2千円ですが、日本語第3版は Volume 1/2 の分冊となっていて合わせて約 1万円 (5倍！) もします (4th Edition のボリュームは 3rd Edition から減ってはいないように見えます)。 Kindle の Cloud Reader を使えば手元の PC のブラウザ上で表示することが可能なので機器を持ち歩く必要もありません。 これはわかりやすい例ですが、これだけの差があると日本語の「プログラミング Perl 第4版」を待つよりも原書の方を読みたくなりませんか？

英和辞典も使える

あまり知られていないようですが、iPhone/iPad/Android で Kindle アプリを使えば英和辞典も無償で利用できます。 これがまたとてもお手軽に使用できるわけですが、標準の英和辞書は「Progressive English-Japanese Dictionary」とのことで、小学館の「プログレッシブ英和辞典」のようです。 残念ながら Cloud Reader や Windows アプリケーションで英和辞書は利用できないのですが、最初から Kindle の「Library」に入っている「The New Oxford American Dictionary」も Cloud Reader では同様の扱いの様で出版社からのライセンスによる制限と推測されます。 (追記：ハードウェア Kindle やスマホ用アプリに比べて制限があるのは Amazon の戦略かも知れませんね。)

簡単に英和辞典の使用手順を書いておきます。

iOS の例

単語を押さえ続けると辞書が表示されます。 そこで「i」(図中赤丸) を押すと辞書の選択画面が出てくるので、「英語－日本語」を選びます。

確認画面が出るので「ダウンロード」をタップすればダウンロードが始まり、ダウンロード後に英和辞典が使用可能となります。

ただし、iOS 版では辞書の選択は記憶してくれないようで、毎回英和辞書切り替えをやらねばならないようです(涙)。 ま、本当は英英辞書を使いこなしたいところですけどね。 → 追記：気がついたら英和がデフォルトとなってました。再起動が必要？

Android の例

Android 版でも同様に辞書選択が可能ですが、最初から英和辞典の項目が表示されています。 逆に英英辞典はダウンロードしなければならないです。 また、最後に使った辞書を記憶してくれるので、毎回選び直す必要はありません。

それよりも Android 版ではアカウントの扱いでハマります。 amazon.co.jp と amazon.com で同じ ID (メールアドレス) とパスワードを使用していると amazon.co.jp の方が優先されてしまい、amazon.com で購入した書籍を読むことができません。 このときは以下の手順で amazon.com と紐付け直します。

1. Kindle アプリ側で「設定」－「この端末の登録を解除する」
2. amazon.com のパスワードを変更
3. 変更したパスワードを用いて Kindle を設定し直し。

さて、何を読もう

とりあえず、自分の読んだ本の中で、(私のように！) 最新技術に乗り遅れている方にお勧めの本を挙げておきます。 短めで安価または無償なので、Kindle 洋書読みデビューには最適です。 興味があれば是非 Kindle で洋書読みを始めてみてください。

「HTML5 for Publishers」

「for Publishers」ではありますがサンプルコードも多く載せられていて、HTML5 の Canvas、Geolocation、Audio/Video についての入門書としてはとても良いと思います。

「jQuery Compressed」

一定の Web 関連知識があり jQuery をこれから始める人には最適です。

英語が苦手と思っている人も継続して英語に触れていれば力はつくので頑張ってみましょう。 Perl プログラマの方は是非 Programming Perl から電子書籍生活を始めてみてください。

パソコンの台数が増え、ファイルの整理が必要になっていたため、休暇中に思い立って家庭用 NAS を購入しました。fit-PC2 を長時間使うと結構発熱するのと、何よりミラーされていないという点が不安です。なので、専用のネットワークストレージ製品を購入しました。購入したのは IO DATA の HDL2-A4.0 です。 バッファロー LS-WX4.0TL/R1J や NETGEAR ReadyNAS DUOv2 も検討しましたが、LS-WX4.0TL/R1J は NAS Navigator2 という専用ソフト導入が嫌なのでパス、NETGEAR は家庭用 NAS向けと謳われている WesternDigital Red の 2TB が「6～9日以内に発送」だったのであきらめ、結局 HDL2-A4.0 を購入しました。 Amazon プライムに慣れてしまうと 1週間も待てず、思い立ったが吉日で、NAS 向け HD を諦めるならば、ベアボーンよりも既製品という判断です。

HDL2-A シリーズは常時稼動オッケー

というわけで注文当日に到着しました。 ディスクは Seagate の ST2000DL001 というモデルが 2つ入っていました。 Seagate のホームページで情報を見つけることができなかったのですが、IO DATA いわく「24時間稼動オッケー」とのことなので、あまり耐久性について心配する必要なないのでしょう。たぶん。

起動してみる

早速起動してみます。 Mac は所有しておらず、Windows 環境での話のみになりますがご了承ください。

DHCP 環境であれば、電源を入れるだけで「\\landisk-******」でアクセスできる状態になります。 ここで「******」は MAC アドレス (16進) の下 6桁で、MAC アドレス自体は底面に貼られたラベルで確認できます。 NTP サーバーも規定値として ntp.nict.jp が設定されており、「\\landisk-******」の下に見える作成済み初期フォルダを使う限りはセットアップを行う必要はなさそうです。

ちなみに初期フォルダは以下の通りです。

disk

いわゆるファイル共有用

contents

DLNA コンテンツ用

itunes

iTunes サーバー機能用

contents と itunes フォルダももちろん共有設定されていて、エクスプローラーからアクセスできます。 このフォルダに置いておくとそれぞれ DLNA サーバー機能、iTunes サーバー機能によって公開されることになります。 ただし、iTunes サーバーの方はファイルを公開するためにデータベースを更新する必要があります (後述)。

セットアップ

セットアップしなくてもそのまま使えそうですが、「landisk-******」は覚えにくいので変えておきたいところです。 また、当初の目的を忘れずに RAID 1 (ミラーリング) に設定し直さなければなりません。

セットアップには付属 CD を使うのですが、初期設定用の「Magical Finder」はインストールをせずに CD から起動して使用することができます。 このあたり嬉しいつくりです。 マニュアルでは「Magical Finder」で初期設定を行ってからブラウザで詳細設定を行う流れになっていますが、実際のところ、DHCP ＋ Windows の環境であれば「http://landisk-******/」で詳細設定画面にアクセスできるので、CD を使わずいきなりブラウザで設定し始めることもできると思います。 ちなみに初期パスワードは空です。

さて、RAID の組み換えですが、ブラウザでログインし、「システム」－「ディスク」－「フォーマット」で行います。 RAID 1 を選択して実行するとフォーマット自体はすぐに終了しますが、リビルドが延々と続くことになります。 リビルド中に共有フォルダを利用することは可能ですが、以下の点に注意が必要です。

• 本来のパフォーマンスより落ちる
• ミラーができていない

ちなみに私の場合、リビルドは 5時間半かかりました。 マニュアルに書いてある「約 3時間」は HDL2-A2.0 ＝ 2GB モデルでの測定値なので注意が必要です。

iTunes サーバー機能と DLNA サーバー機能

初期設定では先述の通り、DLNA用フォルダと iTunes用フォルダが分かれていますが、1つのフォルダで兼ねることもできます。 詳細設定画面からの共有フォルダの設定で「DLNA共有」と「iTunes共有」の両方をチェックしておくだけです。 曲について 2つのコピーを持っておくのはディスクの無駄なので、私も itunes フォルダを DLNA と兼用にしてみました。

実際に使ってみると、どちらのサーバー機能もすんなり使用することができました。 DLNA については Windows Media Player や骨董品と化している Sony NetJuke で試しましたが、特に問題ありません。 ただ、サーバー名の後ろに「.Planex」と付くのが気になると言えば気になります。 まあ、そういうものなのでしょう。

iTunes の方は下の図の様に「共有」の部分に表示されます。 「dragon」が HDL2-A4.0 です。

特に「ホームシェアリング」を作成しておく必要はありません。 ちょっと気になるのが iTunes サーバー上の楽曲の表示はリスト表示のみしかできないところです。 曲が増えてくると使いにくそうです。

また、iTunes サーバーについてはフォルダにファイルをコピーした後にデータベースの更新を行わなければなりません。 データベース更新をスケジュールして実行することもできますが、 1日 1回までなので手動更新と併用することになると思います。 初期設定では自動更新はスケジュールされていないので、フォルダにファイルを入れた後、更新を行わないと曲が見えないことになります。

DLNA も iTunes も曲が増えたらどうなるかはやってみないとわからない部分があります。 画像や動画の扱いについてはまだ試していません。

まとめ

その他にも以下のような機能があります。

• FTP サーバ
• BitTorrent クライアント
• メールによるアラート通知
• UPnP を使った外部へのフォルダ公開機能 (ブラウザによるアクセス)
• USB ポートへ接続したディスク共有
• USB ポートへ接続したプリンタ／スキャナ等の利用 (クライアントにソフト導入が必要)
• UPS との連携
• Active Directory との連携
• Wake on LAN

全部使うつもりはありませんが、アラートメールの設定ぐらいはしておきたいところです。 全体としては今のところとても満足しています。 ハードディスクのトラブルは起こるものなので、ミラーされている共有ドライブがあるというのはそれなりに安心ですね。

最後に気になる点を 1つ。 1つの共有フォルダの設定を変更したら、別のフォルダの変更をする前に必ずサービスを再起動しなければなりません。 続けて複数フォルダの変更をするときはまどろっこしいです。 再起動し忘れを防ぐためにそのようなインターフェイスになっているのでしょうが、まとめて変更して最後にサービス再起動というやり方もできるようにして欲しいところです。