株式会社クオリティ・エージェント

2011.12.27 13:39:28

QA通信編集員

皆さん、こんにちは。

QA通信編集員です。

いやぁ、ホント寒いですねぇ。
クリスマス寒波ってことらしいですが、
北海道の人間だけど、寒いのは本当に苦手です。 

まぁ、すでに関東近郊に出てきてからの方が
長い人生になっているので、北海道出身だろうが、
すでに関係ない感じになっているんですけどね...

さて、本日は、CSVファイルの文字コード判定して、
その後で、文字コード変換しちゃおうって話です。

以前、このブログの中で、UTF-8形式のCSVファイルをダブルクリックして開くと、
Excelでは文字化けしちゃうよって話をしたのですが、
まぁ、そのときは、エディターなどで、
文字コードをSHIFT-JISに変更すれば良いよってことを書きました。

（興味のある方は、こちらから）

...が、このひと手間が面倒に感じることもありますよね？

というのも、とあるシステムから出力されるCSVファイルが、
常に、SHIFT-JIS以外の文字コードで、
しかも、そのファイルを頻繁に使うならなおさらです。

品質管理を行うときに、
バグトラッキングシステム（BTS）を活用している企業も多いと思いますが、
多くの企業では、そのベースのシステムとして、
TracやMantis、RedmineなどのOSSを活用していることと思います。

これらのシステムの多くが海外のモノなので、
CSVファイルを出力しても、SHIFT-JIS以外の文字コードを使っていることが多いのです。

まぁ、当然と言えば、当然ですけどね... 

するってーと、
品質管理を行う上で、バグ情報を、
Excelで加工したいときには、
先ほどの 文字コード変換という
ひと手間が頻繁に発生しちゃうんですよね。

...ということで、

そのひと手間を減らすことが出来れば良いなぁ...と考えたりする訳です。

そこで、Excelのマクロ機能を使って、
CSVファイルを読み込むときに、
自動的に文字コード判定を行い、
SHIFT-JIS以外の文字コードなら、
文字コード変換して利用するように出来れば、
ちょっとしたことではあるものの、
ひと手間を減らすことが出来ちゃいますよね？

で、ネット上で、
｢Excelで文字コードを、どのように判定したらいいのかなぁ｣

...と、ググってみると...、

次のようなコードを見つけることが出来ると思います。

簡単に説明すると…

対象となっているファイルを、HTMLファイルとしてオープンして、
そのときに設定されている文字コードを返すって方法です。
返される文字コードは、
レジストリ内にある以下のキーに登録されている文字コードとなります。

HKEY_CLASSES_ROOT\MIME\Database\Charset 

まぁ、どんな文字コードがあるのかというのは、
時間のあるときにでも、
レジストリエディタ（C:\Windows\System32\regedt32.exe）を使って、
見て貰えればなぁ...と思います。

もちろん、この方法でも、大抵の場合は、うまくいきますので、
ダメって訳ではありません。

ただ、この方法は、Internet Explorerなどのブラウザでファイルを開いたときと同様に、
文字コードを正確に認識出来ない場合があったりするんですよね。

具体的には、
Unicodeのファイルで、先頭が1バイトコードの文字になっていると、
ほとんどがSHIFT-JISと認識してしまうんです。

「あれっ、正しく認識してくれないや」と思ったら、
このコードは、使わない方が良いでしょうね。

オススメの方法としては、
Linuxのnkfというコマンドの文字コード解析部分を使うのが良いと思います。

...が、さすがに、別プログラムのソースコードから
新たにコードを作るってのも手間がかかっちゃいますよね。

まぁ、そこまで手間をかけるのも大変だという方には、
以下のサイトの情報が有用だと思います。

NonSoft - 文字コード判定のサンプル(VB6)

http://homepage2.nifty.com/nonnon/SoftSample/SampleModJUDG.html 

こちらのサンプルコードでは、
すべての文字コードに対応している訳ではありませんが、
まぁ、このサンプルコードに書かれている文字コードがあれば十分でしょう。 

また、精度としても、かなり高いと思います。

ボクが使っている範囲だけですけど、
その中では、文字コード判定で間違いは発生していません。

...しかし、このサイトのように、有用な情報を出して貰えると、
とっても助かりますよねぇ。 

...とまぁ、今回は、他人のコードでのお話をしてしまいましたが、
このような形で、対応するってのも、ひとつの手ではあるってことで。 

あっ、そうそう、文字コード判定だけしても、
Excelでは、CSVファイルを読み込んだときに、
文字化けを起こしてしまいますので、
せっかくですから、ファイルの文字コード変換もしてしまいましょう。

...ということで、最後に、
文字コード変換を行うコードを書いておきます。

 ここでは、ファイルに上書きをしてしまっていますが、
必要に応じて、ファイルをコピーするなど入れてみて下さい。

大事なことを忘れていたので、追記しておきます。

サンプルのコードの中で、ADODB.Streamを利用して、
ファイルの読み書きをしているのですが、
ExcelのVBAマクロの中で使用する場合には、
Microsoft Visual Basic のメニュー｢ツール｣－｢参照設定｣ で、
「Mocrosoft ActiveX Data Objects」 をチェックしておく必要があります。 

いくつかバージョンもありますので、
環境に合ったバージョンを選択して下さいね。

では。

文字コード判定 | 文字コード変換 | Excel | CSV | UTF-8 | SHIFT-JIS | UNICODE | JIS

2010.11.19 21:52:44

QA通信編集員

皆さん、こんにちは。

QA通信編集員です。

なんか今日は朝からバタバタし続けていました。
ようやくひと段落ついたので、ブログでも書いてみようかと....。

最近、Excelのネタをポロポロと出していますが、とある人から『そんなにネタ出しして大丈夫なの？』と言われました。
ネタ切れ起こすんじゃないってコトではなく、仕事がなくなるんじゃないって話なんですが、皆さんはどう思います？

ボクは、仕事柄色んな会社で、色んなネタをぶちまけています。

もちろん、仕事が無くなる可能性はゼロではありませんが、全然心配していません。
というのも、ボクが、お客様先やこのブログで出しているネタは、一般的には知られていることなんかもありますし、あくまでも過去のボクの情報でしかありませんからね。

ボクには、まだまだ覚えなきゃいけないことや興味のあることなんてたくさんあります。そんな中で、ちょろっとネタを出したところで、何の問題もないんです。

だって、誰かが、そのネタを覚えて、本当に使いこなして、身に付くのはちょっと先の話であり、そのちょっと先のころには、ボクはちょっとだけ新しいことを覚えているハズ（？）なので、その誰かよりは、ちょっとだけ先に進んで、更に新しいことをネタとして話すことが出来ているからなんです。

なので、これからもどんどんネタをぶちまけていこうと思いますので、リクエストとかあれば、是非、聞かせて下さいね。（リクエストは、こちらから）

さて、今日も、またしてもExcelネタです。最近作ったマクロの中で、問題になったことを書きたいと思います。

えー、以前、以下のようなコードでファイルを保存する（今回は、CSVファイルではないです）マクロを作りました。

このままでは、問題があるので、そのまま使わないで下さいね。

さて、ちょっとコードを解説していきましょう。

Application.Versionは、使用しているExcelのバージョンを取り出すものです。
比較している『12』という数値は、Excel2007のバージョンを指しています。
要は、Excel2007よりも前のバージョンで実行しているときと、Excel2007で動作させているときで、保存方法を変えようというものですね。

で、ここにも、ちょっとしたTipsがあります。
Application.VersionをVal関数を使って、数値に変換したうえで、比較していますよね？
Application.Versionは、文字列でバージョン番号を返すのですが、例えば、以下のようなコードがあったとしましょう。

まぁ、今やあまり使うことはありませんが..."7.0"は、Excel2002のバージョン番号です。
このときに、Application.Versionが、Excel2007のバージョン番号である12.0"を返したとしたならば、文字列比較では、"7.0"の方が大きいと判断されてしまうのです。

なので、Application.Versionでバージョン番号を比較する場合には、文字列での比較ではなく、Val関数で、バージョン番号を数値に変換した上で、比較しておいた方が良いということなんですね。

さて、バージョン番号を比較して、そのあとに、それぞれのバージョンごとに、ActiveWorkbook.SaveAsで、ファイルを保存しています。
このとき、Excel2007以前のバージョンでは、FileFormatのオプションはないため、指定してはいけません。

まぁ、Excel2007では、デフォルトで.xlsx形式で保存されてしまうため、過去バージョンでも保存が出来るようにしたいだけですから、特に問題はありません。
ちなみに、もし、FileFormatを指定してしまうと、Excel2003などの古いバージョンではエラーのために、実行することが出来ない場合がありますので注意が必要です。

Excel2007では、FileFormatのオプションで、xlExcel8という定数を指定しています。このxlExcel8は、Excel2007の保存のときの『Excel 97 - 2003ブック』での保存のことを指しています。まぁ、他にもExcel2007では、細かいバージョンでの保存も出来るので、ヘルプなどを参考にしてみて下さい。

で、問題というのは、ここにあって、xlExcel8という定数にあります。

Excel2007では何にも問題ありませんが、Excel2003などの前のバージョンのときには、この定数が定義されておらず、エラーになってしまうことがありますので、注意しましょう。

なお、回避策としては、このxlExcel8を、その値である『56』で置き換えてしまうか、もしくは、改めてxlExcel8を定数として定義し直す必要があります。まぁ、気軽に出来るのは、『56』で置き換えた方が良いかも知れませんね。

人によっては、『いやいやいや、絶対に定義し直すべきだ！』という人もいますが、その場合には、Excel2007では、面倒なことになってしまう可能性もあるので、まぁ、使いやすい方で対応してみて下さいね。

ということで、修正したコードは、次のようになります。

ということで、今日は、このへんで。

Excel | SaveAs | FileFormat | Version | Val

2010.11.18 16:46:44

QA通信編集員

皆さん、こんにちは。

QA通信編集員です。

昨日、柳田法務大臣が、国会の答弁では2つの言葉だけ覚えときゃいいって話で問題になってましたよね。
かたや、裁判員裁判で、一般の裁判員の方々が、死刑の求刑で思い悩まなきゃいかん訳ですよ。
人を『死』なせてしまうってことを、やっすーい裁判員としての報酬だけで、悩んでいる訳ですよね。それが、いくら国民から選ばれたとは言え、たっかーい議員報酬貰っているお偉い人が、そんなかるーい話しか出来ないようなら、辞めてしまえって感じですよね。

立場によって、その言葉の重さは違うのは理解しますしね。だからこそ、その立場になったときには、良く考えなきゃイカンってことなんですよ。

あー、腹が立ってきたので、愚痴はやめましょう。

さて、なんか最近、Excelでマクロを書きまくっている気がします。特にVBAが好きとか、そんな訳では決してないんですけどね。

まぁ、仕方なく...。そんな中で、作ったマクロの一部をご紹介です。

テストとかをやっていると、試験表をExcelとかでテストエンジニアの方たちに渡して、試験を実施してもらいますよね。
そんなときに、集計する必要が出てくると思うんですけど、皆さんはどのような形で集計していますか？

良く見受けられるのが、ファイルサーバ上のファイルを直接指定して、リンクを貼っちゃう形。
けど、この方法だと、『リンク先が見つかりません』といったメッセージが出たことってありません？

もちろん、全然変更していなければ問題はありませんが、なかなかそういう訳にもいかず、フォルダ構成を変えたり、ファイルの内容をちょっと変更したり、何よりも、プロジェクト終了後とかに、フォルダ整理とかしちゃいますよね。

すると、途端にこのメッセージに出会うことになっちゃいます。

『過去には縛られないのサ...』というカッコいい方は、この先は読まなくても大丈夫！あなたなら何でもやれる。

ボクは、基本的に貧乏性なので、過去のモノは大切にしたいので、集計のときには、マクロで集計するようにしておいて、極力リンクは貼らないようにしています。

そんなとき、ファイルを１ヶ所に集めますよね。
しかし、ひとつひとつのファイルを開いて、合計値を取って、別な集計用のファイルに書き出してというのも芸がありません。

そこで登場するのが、今回のマクロって訳ですね。
まぁそこそこ使える関数にしているつもりなので、良かったら是非使ってみて下さい。

それでは、まずは、コードを見てみましょう。

そんなに難しいものはないですよね。

まず最初に、ファイルオープンダイアログを開いて、そこで、フォルダを指定させています。
ここにも、引数で、色々と変化を持たせることも出来ちゃいます。

次に、指定されたフォルダを対象にして、特定の名前を持ったファイルを検索します。
この関数内では、『FILENAME_SUFFIX』というグローバル変数を使って、特定の文字が含まれるファイルを探すという訳です。
例えば、テストを実施していると、『...試験表.xls』とか、『...試験項目表.xls』とか、そんな名前を付けますよね。
それらを検索するようにしておけば、それらのファイルを対象にして、検索しちゃうのです。

見つかったファイルは、次々に配列に入れていき、保存するようにしていき、最後に、その配列を返すようにすれば、呼び出し元では、それらの配列を使って、ファイルの操作を行うようにするって形です。

さて、いかがだったでしょうか？

Excel | FileDialog | Dir

2010.11.16 14:14:57

QA通信編集員

こんにちは。

QA通信編集員です。

二日酔いの中、無事に午前中の打ち合わせが終了しました。
昨日（というか、今日ですね）のAM2:30に家に帰り、その後、この午前中の打ち合わせで使用する資料を作り上げ、7部ほど印刷しているうちに、いつの間にか深い眠りに落ち、今朝は、AM7:00に起床して、打ち合わせに挑んでました。

AM11:30ごろに打ち合わせが終わったところで、以前に、このブログのCSVファイルへ書きだそうで紹介したCSVファイルへの出力方法で、ダメ出し（バグがあるとかではないので、前の方法でも大丈夫ですからね）が出たので、別の方法でも出力出来るんだよってマクロを作り終えたところです。

そんな呑んだくれ人間が書いたコードですが、せっかくなので、ご紹介しておこうかと...。

まず、前回のCSVファイルへ書きだそうのどこがダメだったのかを説明しますね。

前回のブログの中では、ActiveSheet.SaveAsというメソッドを利用してCSVファイルの形式で出力するというものです。まぁ、いわゆるExcelの標準の『名前を付けて保存』と同等のことを、マクロの中で行っていました。

この方法で出力した場合、Excelでは、縦方向は書き出されている行数分×横方向に書き出されているカラム数分という四角形で切り出しを行います。このため、カラム数分に満たない行については、不足している部分が、すべて『,（カンマ）』がついてしまうのです。

文字だけで書くと、良く分からないですよね...(^_^;

以下のようなファイルがあったとします。

このファイルをCSVファイル形式で出力すると、以下のようになります。

まぁ、縦横のサイズをちゃんと考えて、出力してくれるって訳ですわ。
これはこれで正しいものなのですが、見た目も含めて、ちょっと『,（カンマ）』が邪魔くさいってのは確かにありますよね。

ということで、この後ろについている『,（カンマ）』を取って欲しいというものです。
実際には、1行目だけが、1カラム分しか使っておらず、それ以外は、すべてのカラム分出力するって形です。

で、以下のような関数を作って、その関数を呼び出すようにしてみました。

とまぁ、こんな感じです。

ファイルをオープンし、そこに各セルを『,（カンマ）』で連結し、行単位で出力をしていくというものです。

一旦、strWorkという文字列に格納しているのは、以降がすべて空白ばっかりだったら破棄するようにするために、利用しており、空白が続いて、その後のセルが設定されているのであれば、その途中も含めてちゃんと書き出すようにしなければいけないため、このようなアルゴリズムにしているということですね。

なお、このマクロの中では、Printステートメントを利用していますが、書き出すだけならば、Writeステートメントというのもあります。しかし、Writeステートメントで書き出すと、書き出した文字列の両端に、『"（ダブルクォーテーション）』が付けられてしまいますので、このダブルクォーテーションを出力しないようにするために、Printステートメントを利用しています。

また、On Error GoTo...というステートメントも使って、ファイルオープン後に、エラーが発生してしまった場合に、ちゃんとファイルをクローズするために利用しています。まぁ、マクロが終了すれば、ファイルも閉じられるので、そんなに気にする必要もないのですが、安全のために、このような処理を追加しています。

Excel | CSV | SaveAs | Open | Write | Print | ダブルクォーテーション | カンマ | On Error GoTo

2010.11.12 23:14:03

QA通信編集員

皆さん、こんばんは。

QA通信編集員です。

来週の月曜日に沖縄県に行くことになりました。日曜日に出発です。もちろん、仕事なんですけどね...(泣
とはいえ、青い海と青い空が見えると、テンションが上がってくるので、なんとか頑張ります！

さて、今日は、Excel関連のネタです。
タイトルにあるSYLKファイルについてですが、以前、UTF-8形式のCSVファイルやTXTファイルはエクセルで文字化けしますというネタと同じように、結構有名なネタなんですが、最近ボクの身の回りで話が出てきたので、この話をしたいと思います。

で、皆さんは、CSVファイルを開いたときに、次のようなメッセージが出てきたことはありませんか？

『SYLKってなんじゃい！』

と思った方、そりゃ、知らない人も多いですよねぇ。

SYLK（シルク）ファイルは Symbolic Link Fileの略で、むかーし、マイクロソフトのMultiplan（マルチプラン）という初期の表計算ソフトで利用されていたフォーマットなんですね。

では、どんなときに、このメッセージが出るのかというと、ファイルの先頭に『ID』という文字が含まれているCSVファイルを含むテキストファイルをExcelで開くと、このメッセージが表示されます。
Windowsを使っている方の多くは、CSVファイルがExcelに関連付けされていると思いますが、このときに、CSVファイルをエクスプローラーなどからダブルクリックすると、Excelが起動されて、CSVファイルを読み込みますよね。すると、先頭にこの『ID』の文字があると、SYLK形式のファイルだとExcelが勘違いして、SYLK形式のフォーマットで解釈をしようとしてしまうという訳です。ところが、SYLK形式のファイルにはなっていないので、Excelが、『SYLK形式ちゃうやん。読み込めないやん！』とメッセージを出してくるって訳ですね。

『おいおい、アホちゃうか？』

と思ったアナタ。
そう本当は、どうにかして欲しい。
マイクロソフトも、そのあたりは、ちゃんと認識はしているのですが、たぶん直しはしないでしょうね。

http://support.microsoft.com/kb/323626/ja

じゃぁ、どうすれば良いのかというと、上のリンク先にも解決方法が書かれていますが、以下のような方法があります。

• シングルクォーテーション（'）を付ける（ex. 'ID）
• 小文字に置き換える（ex. ID --> id）
• ダブルクォーテーションで囲む（ex. "ID"）
• 違う文字に置き換える（ex. ID --> hogehoge）

とまぁ、こんな感じです。

最近は、あんまりCSVファイルを使うことも少なくなってきているので、この現象に出会うことも少なくなってきているとは思いますが、システム間のデータ受け渡しで使っている方も多いことでしょう。ただ、テキストファイルとして利用していれば、何にも問題ありませんし、Excelで編集しようと思わなければ、何にも問題にはならないということですよね。

けど、タイトル欄の先頭に『ID』と付けることも少なからずあるので、まぁ、頭の片隅にでも置いておけば良いでしょうね。

ということで、今日は、このへんで。

Excel | CSV | SYLK | Symbolic Link File

2010.11.08 13:01:00

QA通信編集員

こんにちは。

QA通信編集員です。

今日も爽やかな日ですね。
しかし、ボクは、仕事が溜まりまくってしまい、爽やかさを感じることが出来ない状況です。
頭のリフレッシュのために、こうやってブログをかくぐらいしかないというのも寂しいモンです...（泣

さて、本日は、Excelのネタにしますね。

Excelファイルにシートがたくさんあると、そのシートを探すのも大変ですよね？そんなときに使えるマクロです。

例えば、以下のようなシーンが考えられますよね？

• 『A』というシートを操作するために、ワークブックの中に『A』というシートがあるのかをチェックしたい
• 『A』というシートが無ければ、新しく『A』というシートを作りたい。
• 『A』というシートがあれば、削除したい

などなど...。まぁ、Excelではシート上で操作を行っていくので、このようなシーンはありますよね？
そんなときに、このマクロで、まずは、シートの存在を確認しましょうってモノです。

それではさっそくコードを見てみましょう。

短いコードですので、関数などにする必要もないかも知れませんけどね。
まぁ、それでも、関数として登録しておくと、いつでも便利に使えるので、有効活用出来ます。

ちょっとその利用例も見てみましょう。

これは、シート『A』が存在している場合、そのシートの操作を行うために、アクティブな状態にします。
シートが無い場合には、シートの一番後ろに新しくシートを追加し、シート名を『A』にするというものです。まぁ、そのあとは、そのシートを対象に、何か操作をしていきましょうというものですね。

もちろん、この内容までも関数化しておいても利用価値はあるかも知れませんね。

では、本日は、このへんで。

Excel | ActiveWorkbook.Sheets.Count | Worksheets.Add.Move | Activate | シート追加

2010.11.06 20:49:42

QA通信編集員

こんばんは。

QA通信編集員です。

さて、本日は、Excelネタです。

Excelは、ご存じのとおり、CSVファイルを扱うことが出来ますね。
このブログの中でも、UTF-8形式のCSVファイルが開けないネタを書いたことがありますが、CSVファイルにExcelが関連付けされていれば、ダブルクリックなどでオープンすることも出来ます。

そんなCSVファイルですが、このファイルは、カンマで区切られただけのテキストファイルですので、普通にテキストエディタでの編集することは出来ますが、ベタ書きされているファイルを編集することは難しいですよね。そこで、利用するのがExcelなどの表計算ソフトということになります。

さて、そんなCSVファイルなのですが、使った後も、CSVファイルとして保存しておきたいときもありますよね。特に、他のシステムなどで簡易的にデータのやり取りを行いたい場合などには、CSVファイルはとっても便利に使えます。そこで、CSVファイルとして保存することになるのですが、ExcelからCSVファイルに保存するときには、いくつか注意しなければいけない点があります。

• ファイル保存時に、ファイルの種類にCSVファイルを指定しなければいけない。
• 複数のシートがある場合には、シートごとにCSVファイルに保存しなければいけない。
  このとき、CSV出力したファイル名に、対象ファイル名が変更されてしまうことに注意が必要。

まぁ、注意点といっても、大した話ではないんですけどね。

ということで、CSVファイルへの出力方法です。

この方法は、Excelのファイル出力機能(SaveAsメソッドは、『名前を付けて保存する』という機能ですね）を使って出力しています。もちろん、テキストファイルへと1行づつ書き出す方法もありますが、まぁ、設定されている値を編集しない限りは、Excelのファイル出力機能を使って書き出すのが簡単で良いでしょうね。

さて、このコードの中で、Application.DisplayAlertsというのがありますが、これは、警告メッセージの出力を制御するものです。
例えば、Filenameで指定しているファイルが存在している場合には、『上書きしますか？』といったメッセージが表示されますよね？もし、そのようなメッセージが出てしまうと、処理がストップしてしまうので、このApplication.DisplayAlertsをFalseに設定し、メッセージの表示を抑制してしまおうというものなのです。

ということで、今日は、このへんで。

SaveAs | Excel | CSV | DisplayAlerts

2010.04.13 17:27:19

QA通信編集員

こんにちは。

QA通信編集員です。

今朝、テレビの情報番組の中で、「チンパンジーにも助け合いの心があるのが実験で分かった」というニュースが流れていました。ボックスの中にチンパンジーを入れて、それぞれのボックスの前には、ジュースとステッキを置くという実験で、ボックス前にジュースがあるチンパンジーが「取って！」みたいな仕草をすると、ボックス前にステッキがあるチンパンジーが取ってあげていました。

その実験の前に、お互いのチンパンジーが、交互に手伝うと両方ともジュースを飲めたって実験もやっていた訳なので、「本当に助け合いか？何か貰えると思ったんじゃね？」とか、「野生でもやるのか？」とか疑問は残りますが...。まぁ、無償の助け合いらしいです。

私も日々悩んで、周りの人に助けられっぱなしなので、そのうち何か恩返し出来ればと思います。
もう少し時間はかかりそうですが...。

さて...、今回は、前々回の記事で、CSVファイルのインポート処理のネタを書きましたが、その後、質問などもあったので、補足しておこうかと...。

タイトルのとおり、UTF-8形式のテキストファイル（CSVファイルやTXTファイル）をエクセルで読み込むと文字化けしてしまいます。

このため、前々回の記事のCSVファイルのインポート処理の関数を使っても、文字化けしてしまいます。
Microsoftのサイトにも、以下のような記事があります。

http://support.microsoft.com/kb/821863/ja

これは、Excel の仕様に基づく制限事項となっているので、バグでは無いらしいんですけどね...。

しかし、データベースからの出力のときに、UTF-8での出力というのも多いので、何とかして欲しいというのが、皆さんの本音ではないでしょうか？少なくても私は、直してほしいですねぇ。

このネタは、結構有名なのですが、それでも、たまに問い合わせを受けます。

対処方法は、ぜんぜん簡単です。

エディター（文字コードを変更出来る必要があります）を使って、一旦、ファイルを開き、SHIFT-JIS形式に変換後、ファイルを保存すれば、その後は、エクセルで開くことが出来ます。要は、UTF-8形式の文字コードを、SHIFT-JIS形式の文字コードに変換すれば大丈夫ということですね。

で、せっかくならば、文字コード変換するコードを追加した方が楽ですよね？

ということで、もう少しすると、このコードを変更すると思いますので、そのときに、UTF-8の文字コード読み込みと、変換処理を行うコードを追加したものを作ってみたいと思いますので、そのときには、改めてお話をさせてもらえればと思います。

文字コード | UTF-8 | Excel | SHIFT-JIS | CSV | TXT

2009.11.09 00:00:00

QA通信編集員

皆さん、おはようございます。

QA通信編集員です。

今日もとても気持ちいいですね。

天気が良いとテンションが50％増しです。逆に雨は、50％減ですが...。

今日は、電車で移動しながら、この記事を書いています。といっても、新幹線などでゆっくり移動中って訳ではなく、地下鉄移動中に慌ただしく書いています。

「他に時間があるだろう！」とか思われますよね。

そうなんですよね、たぶん、他に時間を取ろうと思えば、取れるとは思うのですが、ボクは、勝手に空き時間休暇制度(?)を導入しているので、比較的に空いている時間が無いんですよねぇ...。空き時間休暇制度って、ホントにちょっとした空き時間だけが休暇で、それ以外は、全部仕事って制度を勝手に決めてるんです。

で、その空き時間でQA通信を書いているって感じなんです。
なので、土日だろうが、平日の真昼間だろうが、真夜中だろうが、このQA通信を書いているんですけどね。 

さてさて、本日は、前回の続きで、実際に集計処理をしちゃいましょうってネタです。

集計処理といっても、大した話ではありません。要は、リストから個数を抜き出して、表にしちゃいましょうってコトです。

良く関数を使って、「=COUNTIF(xx:xx,"○")」とか、「=COUNTA(xx:xx)」とか書きますよね？

これと同じ処理を行います。しかも、自動で。
コードを見る前に、ちょっと仕様を確認しましょう。

ここでは、以下の3つのシートを使います。

• データが入っているシート
• 集計値を出力するシート
• 項目名が記載されているシート

集計する際に、問題になるのが、リスト内には存在しない項目名です。

もちろん、リスト内に存在しない項目名は必要ないという場合もありますが、集計する際には、その項目が、0件であるということも重要な情報となります。このため、「項目名が記載されているシート」に、すべての項目名を記載しておき、そこから項目名を取りだそうというものです。

先頭の行には、項目数が入っており、次には、「データが入っているシート」内でのカラム名、項目名と続きます。まぁ、このあたりはプログラムの作り方次第なので、応用して作りなおして貰えればと思います。

「集計値を出力するシート」は、先にデータが存在している可能性もありますので、最初にシート内のデータを削除します。これは、各パラメータが変更されても、それに対応出来るようにする意味も込めています。なお、集計自体は、Excelの集計関数であるCOUNTIFを使用します。

最後に、集計した表からグラフ（ドーナツ型）を作成します。通常、グラフを作成すると、0件のデータも表示してしまいますが、グラフ内では、この0件のデータを表示させてしまうと、ごちゃごちゃしてしまいますので、0件データは削除するようにします。

たったこれだけです。

手作業では、グラフの作成にちょっと手間がかかるかも知れないので、5分から10分ぐらいの作業ですね。ただ、多くのデータの集計を行い、グラフを作成する作業をすると、すぐに1時間ぐらいの作業時間になってしまうでしょうね。

それではさっそくそのコードを見てみましょう。

前回のOKボタン押下時に呼び出しているTallyDataという関数です。

ちょっと長いですが、ご容赦下さい。

これで、集計した表とグラフの作成が出来ます。

ちょっと冗長になっている部分もあるとは思いますが、ご勘弁を...。
もっと他にも体裁を整えるコードなどもあったのですが、省略させてもらいました。

ご質問などあれば、是非、お聞かせ下さい。（ご質問は、こちらから）

Excel | COUNTIF | COUNTA | 集計

2009.10.31 00:00:00

QA通信編集員

皆さん、こんばんは。

QA通信編集員です。

今日、友人から11月4日の東京ドームのチケットあるよって、電話がかかってきて気づきましたが、今日から日本シリーズが始まりましたね。阪神は、もう来年に向かっているので、あんまり興味はないのですが、日本ハム頑張れって感じですが...もうすでに1敗してしまいましたね...(T_T)

ボクは、北海道の出身でして、北海道の人たちは、日本ハムが来るまでの間は、6割、7割ぐらいの人たちは巨人ファンだったはずなのですが、今ではすっかり日ハムファンばかりです。
ボクの父親も母親も、巨人ファンだったはずだったんですけどね、いつの間にか日ハムファンになってました...。

けど、やっぱり地元に野球だけじゃなく、スポーツチームがあるってのは良いですねぇ。
コンサドーレ札幌も頑張れよって感じです。

さて、今日は、Formの使い方のネタ出しをしたいと思います。

文字列を入力させたり、何かを選択させたりと、Formを使うことで対応が出来ます。
メッセージやファイルを開く、ファイルの保存といったダイアログは、良く使うダイアログなので、関数などが準備されています。

が、何か自分が考えたことが出来ない場合には、Formを利用して、自分自身で作っちゃいましょう。

Formの作り方については、どこかのサイトを参考にして作って貰えればと思いますが、ここでは、次のようなFormを作ります。

まず、どのような仕様なのかを説明しておきます。

ワークブックに「集計－」で始まるシートがある場合に、「集計－」以降の文字列を対象に、データを集計するような仕組みを作りたいと考えました。そこで、このダイアログでは、「集計－」で始まるシート名を検索して、それ以降の文字列を、（リストボックスへ）一覧表示するようにします。

リストボックスは、複数選択出来るようにしますが、1つ1つの項目を選択していては面倒なので、チェックボックスを使って、全選択や全解除が行えるようにしたいと思います。単にすべてを選択したり、すべての選択を解除したりすることが目的なので、チェックボックスである必要はなく、ボタンにしちゃっても構わないんですけどね、まぁ、ここではチェックボックスにしておきました。

Formを表示させる場合、呼び出し元のプログラムの中で、以下のように書きます。

SelectItemというのが、このFormの名前です。
Showメソッドを使い、表示します。（消すときには、Hideメソッドです）

さて、呼び出し元でShowメソッドを使うと、Formが表示されるのですが、Form側では、最初にInitializeイベントが発生します。この初期化イベントは、表示よりも前に発生するイベントですので、Formに何かを設定する場合には、このイベント処理の中で、行うようにします。

それでは、コードを見てみましょう。

なお、この初期化処理の中では、リストボックスやチェックボックスに設定を行っていますが、このとき、「リストボックスにアイテムが設定されたよ」とか、「チェックボックスにチェックされたよ」というイベントも発生します。

リストボックスにアイテムが設定されたよというイベントに対しては、何も処理を行っていませんが、「チェックボックスが選択されたよ」というイベントに対しては、以下のような処理を行っています。

これは、チェックボックスがクリックされたときのイベントですね。

この処理の中で、チェックされた状態になったのであれば、リストボックス内のアイテムを全部選択状態にしています。

初期化 | Form | Excel

2009.10.17 00:00:00

QA通信編集員

皆さん、こんにちは。

QA通信編集員です。

うちの会社も、今日（10月17日）でちょうど2周年を迎えることになりました。
これもひとえに皆さまのおかげです。本当に、感謝、感謝ですね。

もう2年かぁ...と思いつつも、まだ2年。日々勉強の毎日です。
まだまだ厳しい状況が続いていますが、今が踏ん張りどきでもあるので、もっと頑張んなきゃいかんと思う次第です。

QA通信も、毎日は配信出来ていませんが、もっと多くのネタを出せるように頑張んなきゃいけないですね。

さて、今日のネタも、かなり使えるネタなので、是非、使いまくって下さい。

マクロを使って色んな処理を行わせていると、たまに欲しくなってくるのがパラメータです。
例えば、何か処理を行ったときに、その行の色を変更したいとします。以前の「【Excel】指定されたセルが変更されたときに自動的に処理する」でのネタのときと同じような状態ですね。

このとき、最初は、グレーだったけど、そのうち、グレーじゃなくて、オレンジ色にしたいなぁ...と。

もちろん、この記事を読んでいる方のようにマクロを知っている方であれば、VBAのコードをちょこちょこっと書きかえれば良いので、何にも問題ないと思いますが、このようなことを誰もが出来る訳ではないですよね？

そんなときに、別のシートに、色を設定出来るようにしておき、その色が変更されたら、マクロで処理するときに、その色を使って処理が行えると、とても便利です。使う人には、このシートのここの部分を変更してねって教えておけば大丈夫です。つまりは、このシートの使い方を、どこかに書いておけばよいのです。

パラメータの設定シートは、こんな感じです。

ようは、このシートのデータを読み込み、マクロの内部で使用できるようにしちゃいましょうって話です。
もちろん、必要に応じて、パラメータは、どんどん追加することも出来ます。

このシートからのデータの読み込みを関数として用意してみました。

制限事項というか、このプログラムでは、色を取得するときには、項目名に【色】という文字がどこかに入っている必要があります。もちろん、あまり使われないような文字列にしても問題ありませんので、そのあたりは、適当に修正して使ってみてください。

設定シートを作らなければいけないという手間はありますが、ユーザーに合わせたカスタマイズが出来ますってことで、それなりに使い道はあるのではないでしょうか？

Excel | パラメータ設定

2009.10.08 00:00:00

QA通信編集員

皆さんは、Excelを使うことって多いでしょうか？

私は、仕事の中で、Excelを使うことがとても多いです。
まぁ、表計算ソフトなので、データ集計したり、集計データから分析を行ったりと色々と使っています。 

で、このとき、何度も同じようなコトを行う場合には、VBAのマクロを作ってコマンドとして使えるようにしています。もちろん、関数で実現出来るのであれば、関数優先ではありますが...。

というのも、仮に1回あたり10分かかるような作業があったとすれば、10回同じ作業を行う予定なら100分もかかりますからね。それなら、その100分を使って、先にマクロにしておこうって考えです。まぁ、「後に残した方が得ぢゃん！」ってコトですね。マクロを作って、何かしら残しておけば、後から、別の用途にも応用出来たりもしますし。

そこで、今まで色々と作ったものの中から、QA通信の中で、Tips集みたいな形でご紹介出来ればと思っています。

冗長なコードなどがあるかも知れませんし、もっと他にも良い方法があるよというのもあるかも知れませんが、ちょっとでも、皆さんの参考になればと思います。（あっ、別な良い方法があるのであれば、コメント等で教えて貰えると助かります。まだまだ勉強中なもので...）

ということで、前置きはこのぐらいにして...。

それでは、Excelシリーズの第一弾を始めましょう。
最初は、タイトルにも書いているように、指定されたセルが変更されたときの処理についてです。

皆さんの中には、Excelでタスクリストのような何らかのステータスを管理することってありませんか？
私は、年齢と共に記憶力が低下しつつあるので...（泣、
ToDoリストとして、やるべき作業を書き出すようにしています。

以下の図は、そのToDoリストの画像です。

Cのカラムに、Openとか、Closeとか、Cancel...などなど、色々なステータスを設定するようになっています。
下の「Status」や「Priority」の表は、選択項目になっているものを見えるようにしたものですが、「Status」や「Priority」は、リストから選択するようにしています。（通常は、別のシートにして、見えないようにしているんですけどね）

さて、このようなExcelファイルって、良く見かけますよね？

このようなファイルでは、ステータスが変更されたときに、その行の色を変えていたりしませんか？
具体的には、上図の3行目のように、CloseやCancelといった終了項目の行を、グレーアウトすることです。その他にも、終了日を設定したりするかも知れませんね。

まさに、私も同じことをやっています。

1. Statusを変更する。（リストから選択する）
2. CloseやCancelなど終了した場合に、その行を選択する。
3. 背景色のアイコンを選択し、色をグレーに変更する（あるいは、マウスの右ボタンクリックから「セルの書式設定」で背景色を変更）
4. 終了日を設定するセルに、終了した日（今日の日付）を入力する

こんな些細な作業は、Excelに慣れている方なら、ほんの10秒もかからないでしょうね。
しかし、その作業を、毎回やることを考えてみましょう。例えば、500件あったならば、10秒 * 500件 = 5,000秒（1.38...時間）にもなります。
そう、実は、トータルすると結構な時間を、この些細な作業に割いていたりするんです。

ということで、こんな単純な作業であるからこそ、マクロにしてしまいましょう。

仕様としては、こんな感じ。
以下のステータスになったときに、その行の色を変更する。

Excelでは、ワークシートの内容が変更されたときには、Worksheet_Change(ByVal Target As Range)が呼び出されます。これは、ワークシート内のセルが変更されたときに、必ず呼び出される関数です。すべての変更が対象となるので、この関数内では、最初に必ず該当箇所だけが処理対象となるようにチェックを入れる必要があります。

で、次のようなコードを書いてみました。

さぁ、これで10秒の作業からStatusを選択するだけの作業に変わりましたね。
もしかすると、年間で1時間ぐらいの節約になったかも...（笑

もちろん、この使い方は、その他にも、色んな応用方法が考えられそうですよね？

Worksheet_Change | Excel