BLOG　Tokyo

会議のテーブルのレイアウトや座席表など作りたい時が年に2，3回あります。だけどそのためにマイクロソフトvisioを買うのもどうかと思うのでやっぱり方眼紙のように使えるエクセルを思いつくわけです。だけどあのグリッド線が長方形だとイマイチですよね。やっぱ正方形でないと。

できますよ。簡単に。

全体選択をして、列の幅を調整すればいいのです。

エクセルって表計算っていうよりこういう使い方がすごいですよね。

調査などの回収資料をエクセル入力で行うとき、数字を全角で入力してあったりで不備があることが多い。入力制限をしておくのが根本にはあるが、回収後でもなんとかならないだろうか。

JIS関数と、ASC（アスキー）関数を使えば簡単だ。

■半角文字を全角にする
JIS（ジス）関数
=JIS(文字列)

■全角文字を半角にする
ASC（アスキー）関数
=ASC(文字列)

出典：日経PC21

Access2007で11MくらいあるCSVファイルをインポートしようとしたら、下記のエラーメッセージがでた。

'Exceute TempImexSpec'メソッドは失敗しました:'_WizHook'オブジェクト

なんじゃと思って定義などをいろいろいじくったがすべて失敗。CSVファイルをExcel2007に変換してからインポートするとすんなり成功しました。

アクセスより、エクセルのほうが職場のユーザに説明しやすい場合が多いです。アクセスなら簡単なのに、エクセルで伝えようとすると？？？なじぶんですが、大抵のことは両方で出来るのです。それは好みによるところが多く、結局エクセルユーザというのが現実かなぁと思います。

さて、先月シート１で作業している名簿と今月シート2で作業している名簿を合算して「重複をなくしたい」というニーズはよくあります。顧客番号を管理しない場合はよく起こる現象ではないでしょうか。そこで役に立つのがCOUNTIF関数です。

同じ列にコピペして、COUNTIF関数で、そのセルに入っているデータをカウントすれば、「１」ならばユニーク、「２」以上ならば、重複していることがわかります。あとは、それを見て手入力で行削除するか、VBAで2以上ならば、行削除というマクロをつくれば自動化までそう遠くはありません。

さて、B列（B:B）にある重複を探す例です。B2のセルにあるデータの重複をB列全体から探すという内容になっています。

=countif(B:B,B2)

参考：ALL　About　Excelで重複データをチェック

【関連エントリー】
【ACCESS】重複しているデータと重複していないデータに分割する
【ACCESS】重複するレコードを見つけて削除する

あるDBにcsvファイルを一括インポートする仕事があって作業していると日付の形式が不正でインポートできない。みると、誕生日が19441111となっている。たぶん、1944/11/11にしなきゃいけない。1万件くらいあるので、関数でズバっと楽したい。どうしたらよいか。

変換前　19441111
変換後　1944/11/11

この場合は、誕生日（B1)の隣の列に一行追加してB2に変化後のデータを表示させるとして、TEXT関数を使うと簡単だ。

=TEXT(B1,"0!/00!/00")

とすればよい。

参考：Excel(エクセル)基本講座：日付の表示形式

出席リストやアンケート用紙で参加人数やQ1で３を答えた人は何人いるかなど、あるセルになんらかのデータが入っている行のみカウントしたい場合がある。そんな時はcountA関数を使えば簡単だ。

使い方）
=COUNTA(範囲指定)
例）
=COUNTA(B3:B8)

Aサイトのアクセス件数とBサイトのアクセス件数の月別推移を見てどのような影響があるか、説明したいときがある。比べる対象サイトが増えれば増えるだけ、より説得力のある相関関係を示したい。

そんな時は、correl関数で相関係数を求めるとよい。

2 つの配列データの相関係数を返します。相関係数は、2 つの特性の関係を判断するときに使用します。たとえば、各地域の平均気温とエアコンの普及率の相関関数を調べることができます。

CORREL(配列 1,配列 2)

配列 1 データが入力されたセル範囲を指定します。

配列 2 もう一方のデータが入力されたセル範囲を指定します。

エクセル：ヘルプより

棒グラフに折れ線グラフを重ねて表示したい場合などで、目盛りのスケールが違うので思ったようなグラフにならない場合がある。そんな時は2軸グラフを使うといい。

グラフ内でデータ系列（折れ線グラフならば、折れ線）を選択し、右クリック。

「データ系列の書式設定」⇒「軸」⇒「第2軸」で設定可能。

軸の目盛りの設定は、第2軸を選択し、右クリック。

「書式の設定」の、「表示形式」あるいは「目盛」で微調整可能です。

とりあえずのアンケートフォームのようなものを作らないといけない。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にワイルドカード文字を使用できる。

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

条件式の指定方法

　条件式　　　　　　　　意味

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

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

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

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

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

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

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

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

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

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

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