BLOG　Tokyo

とりあえずのアンケートフォームのようなものを作らないといけない。WEB上の方が回答者にとっては便利かと思うが、どうしてもエクセルでやりたいのだという。

アンケートフォームに限らず、回答フォームにエクセルを利用しているケースをよく見かける。たとえば、旅行に行くときの旅行者情報もエクセルで送られてくる。

エクセルでフォームを作成する場合のポイントは３つに絞った。

１．シートを保護する
　パスワードで保護することもできるし、パスワードなしにして、回答欄以外を入力できないように設定することも出来る。

２．無駄な行・列を非表示にする
　回答に関係のあるセルだけ表示させる。プルダウンメニューの参照先などを見せたくない、チェックボックスの値リンク先を見せたくない場合に使います。

３．チェックボックスで複数回答を可能にする
　プルダウンからは一つしか選択できない。あてはまるものをいくつでも回答してもらいたい場合は、チェックボックスを使う。エクセルでも可能なんです。

2008/6/6から2008/8/11までの日数を求めたい場合や、宿泊数を求めたい場合がある。

そんな時は、Datedif関数だ。

=Datedif(2008/6/6,2008/8/11,"D")
↓
66

となる。これは宿泊数で、全日数をカウントしたい場合は+1をすればよい。

例
=Datedif(A1,B1,"D")+1
↓
67

"D"はDayの略だが、"(ダブルクオテーション)で括らなければエラーになる。

2006/8/27の隣の列に「日」などの曜日を表示し、曜日ごとの分析などをしたい場合がある。

簡単な方法がある。

=TEXT(A1,"aaa")

とすれば、「日」と省略形が表示される。

aを一つ増やして

=TEXT(A1,"aaaa")

とすると「日曜日」になる。

お試しあれ。

参考リンク

ID RID NAME
----------------------------
10 10 HongKong
11 " " GangZhou
12 12 Beijin
13 " " Shanghai
14 " " Tongkou
15 " " Torphan

ID、RID、NAME列があって、RIDがある列だけの表にしたい場合がある。これが1000行とかになるとかなり面倒だ。これを毎日ルーチンでやらないといけない場合、うんざりだ。簡単に出来ないだろうか。

やっぱりVBAだろう。意外に簡単。実行するとこうなる。

ID RID NAME
----------------------------
10 10 HongKong
12 12 Beijin

最近は情報の漏洩とかでエクセルにもパスワードをかける場合が多い。しかし、最終的には解除しなければいけないんだが、100個くらいもあると結構面倒くさい。開くときにパスワードを入力、保護を解除するときにパスワード入力だ。そこで、一括で出来ないかいろいろ工夫してみた。ネットで調べるとunprotectメソッドが主流だが、これだと、ツール-オプション-セキュリティ-パスワードに設定されているものを解除することは不可能だ。

エクセルを使っていて行と列を入れ替えたい場合がある。コピーしてきたやつを、貼り付けて、カンマ区切りに分けて、それを改行区切りで１レコードとしてから、インポートしたい場合など。メールのアドレス帳とか結構便利。エクセルにデフォルトでそんな機能があります。

実務で表を作るときに、完成形が見えないままとりあえず作成してみて、ある程度できた段階で、どういう形の表が見やすいか・説得力があるかなどから完成形を決定するということがあります。

複雑な資料ほど、こういうケースがあるような印象を持っています。

そんなときに、行と列とを入れ替えたいというケースが少なからず発生します。

とりあえず作成しはじめたときに、行でいいと思っていた項目が、実は列にある方がわかりやすいというような場合です。

▼操作手順：行と列を入れ替える
元の表を範囲選択
　↓
メニュー［編集］－［コピー］をクリック
　↓
右下方向に十分な空白のある1つのセルを選択
　↓
メニュー［編集］－［形式を選択して貼り付け］をクリック
　↓
［形式を選択して貼り付け］ダイアログ－［行列を入れ替える］チェックをOnに
　↓
［形式を選択して貼り付け］ダイアログ－［OK］ボタンをクリック

この操作を行えば、
元の表の
　行が列に
　列が行に
なります。

ドロップダウンリストを使いたい場合がよくある。他のワークシートに選択肢をつくるのが普通だが、エラーが出る場合が多い。そこで、INDIRECT関数を使うと便利だ。

メニュー［データ］－［入力規則］をクリック
　↓
［データの入力規則］ダイアログ－［設定］タブをクリック
　↓
［条件の設定］欄
　－［入力値の種類］コンボボックスから「リスト」を選択
　↓
［元の値］欄に「=INDIRECT("Sheet1!A1:A47")」と入力
　↓
［データの入力規則］ダイアログ－［OK］ボタンをクリック

参考

ホームページでデータベースの表示をしたい場合がある。PHPやCGIを使う方法が一般的だが、それらが使えるサーバーが必要になるし、ぼちぼちプログラミングの知識も必要になる。もっと簡単に表示することはできないのだろうか。そこで登場するのがデータバインドで、CSVファイルと簡単なスクリプトで実現可能だ。

サンプル

ちなみに、インターネットエクスプローラ（IE）でしか動作確認が出来ていない。IEでもポップアップブロックが出る場合があるので許可する必要がある。Firefox上では動かない。誰にでもみせたいと思う場合は、PHPやCGIを使うほうがベター。

出張期間の日数を計算したい場合がある。

例えば、2005年1月1日 から 2006年1月1日までの日数を求めたい場合

=DATEDIF( "2005/1/1", "2006/1/1", "D" )

これに１加える。

=DATEDIF( "2005/1/1", "2006/1/1", "D" )　+　1

基本操作なんですが、ワークシートが見えなくなったなんて質問が来ます。状況によりますが、作業ウィンドウが単にずれている場合は、

ウィンドウ-整列-作業ウィンドウの整列にチェック

で解決です。

空行を見つけて選択するには、

編集→ジャンプ→セルの選択→空白セル

で空白セルを含む行が選択される。これを削除するにはマクロを使うと早そうだ。

エクセルで範囲を指定する場合、A1:C3なんて指定の仕方をするがVBAではエラーになってしまう。VBAでは下記のとおり。

Range("A" & 1, "C" & 3).Copy

以前に複数のブックから１枚にまとめるマクロを作りましたが、今回は同じブック内のシートを１枚にまとめます。シート丸ごとのコピーを対象シートの最後に貼り付けることはどうやらできないみたいなので＊、For..Nextを使って、一枚一枚シートの記入範囲を調べて、対象シートにコピーしていきます。

＊WorksheetsとRangeが別オブジェクトだからだと推測します。

複数のシートをブックに分割したい場合ありますよね。前のエントリーのVBAと併せて使うと出身別ブックを一発でつくることも可能です。

ある表があって、都道府県が連続していたとします。都道府県別シートに分割したいなんてこと、よくありますよね。選択して、コピー、シート名の変更。これを47回繰り返さないといけません。面倒くさいですよね。
そんな作業を一発で解決できます。

＊都道府県がばらばらでも、並び替え機能を使えば、簡単にソートできます。

ひとつのシートじゃなくて、別々のシートにまとめたい？

むしろそういう場合の方が多いかもしれません。複数のブックを別シートにまとめるマクロ作っちゃいました。

同じ形式のファイルを一つのエクセルファイルにまとめて、データベースとして使う。あるいはアクセスにエクスポートなんてことをしたい場合もあります。

こんなときは、一つのワークシートにまとめちゃうマクロを作るのが便利です。
同一フォルダ内にtenkiフォルダを作成して、そこにまとめたいエクセルファイルを保存して、マクロを実行するだけ。楽勝です。

B4に入っている「2006/9/14」をセルの日付表示で「9月14日」と表示させていてその「9月14日」という文字列をA1に反映させたい時、

Range("A1") = Range("B4").Value

とやってしまうとA1には「2006/9/14」と表示される。Valueプロパティはセルに入力された値を返します。
なので、この場合はTextプロパティを使います。

Range("A1") = Range("B4").Text

Textプロパティはセルに表示されている文字列をそのまま取得することができます。

VBA関数っていうのがあって、ワークシートで使える関数をそのままVBAでは使えない場合もある*。結構不便だよなぁ。一緒にして欲しいけど。。**
同じ働きをしても微妙に違うので注意が必要です。

日時を求めるVBA関数とワークシート関数

意味　　　　　　　　　　　VBA関数　　　　　　ワークシート関数
現在の日付と時刻を求める　　Now関数　　　　　　　NOW関数
現在の日付を求める　　　　　Date関数　　　　　　TODAY関数
現在の時刻を求める　　　　　Time関数　　　　　　　なし

*Mid関数等はワークシート関数同様に使えます。
**WorksheetFunctionオブジェクトの後に「.」（ピリオド）とワークシート関数名を記述することで、ほとんどのワークシート関数をVBA上で利用して計算結果を取得することができるようです。ただし、LEFT関数、LEN関数はVBA関数にもあるので使えません。（なんじゃそりゃって感じだけど。。。）

検索、置換を行う際は、引数Wahtにワイルドカード文字を使用できる。

ワイルドカード文字
？　任意の１文字
＊　任意の文字列

条件式の指定方法

　条件式　　　　　　　　意味

=ジャマイカ 　　ジャマイカと等しい

=ジャマイカ* 　　ジャマイカで始まる

=*ジャマイカ 　　ジャマイカで終わる

=*ジャマイカ*　　ジャマイカを含む

<>ジャマイカ 　　ジャマイカと等しくない

<>ジャマイカ*　　ジャマイカで始まらない

<>*ジャマイカ　　ジャマイカで終わらない

<>*ジャマイカ* 　ジャマイカを含まない

最近は仕事でエクセルを多用している。なので、エクセルのカテゴリーをつくりました。これからこのネタのエントリーが多くなると思いますが、無視してください。自分用のメモです。

コードを改行 _(半角スペース＋アンダースコア)

ワークシートを非表示 書式->シート->表示しない