MyNoteBook weblog

Excelでマクロの登録先が勝手に変更されるということで悩んだので覚書

(1)サンプルというブックを作る。
(2)サンプルのDATAシートをブック１に移動するマクロを作る。

  Sub Macro1()
    Sheets("DATA").Select
    Sheets("DATA").Move Before:=Workbooks("Book1").Sheets(1)
    Windows("Book2").Activate
  End Sub

(3)サンプルのシートにボタンを作る。
(4)ボタンにマクロを登録する。
(5)そして実行する。

マクロを登録したのは、サンプルなのに、なぜか登録先が
Book1!Macro1 になってしまい、サンプルを上書き保存すると
次からマクロが使えない状態になってしまう。

結局、Sheets("DATA").Move ではなく、
Sheets("DATA").Copy を使うことで登録先が勝手に変更されることはなくなった。
しかし、移動と同じ動作にするためには、もともとのDATAシートを削除しなければならない。
で、シートを削除しようとすると通常は警告メッセージが出てしまうのだが、

  Application.DisplayAlerts = False
  Sheets("DATA").Select
  ActiveWindow.SelectedSheets.Delete
  Application.DisplayAlerts = True

とすることで、警告メッセージを出ないようにすることができる。

また、勉強になりました。

相関分析データを視覚化するユーティリティを作成した。
以前に作った自動集計検定２ｘ２視覚化ユーティリティとやっていることはあまり変わりない。

相関分析では、相関係数と有意差が分析データとして出力されるので、有意差に応じて線の太さを変えたり、相関係数が正の値と負の値とで線の色が変わるようになっている。

たくさんの水準について相関を分析した際に、視覚的にそれぞれの水準間の関連性がわかりやすく表示されるので、利用してみてほしい。

今回から、Flash CS4を使って作成している。
まだまだ、分からないところがあるので、これからも試行錯誤が続きそうである。

どういう仕組みなのか、説明してみる。

シート名：集計
　　Ａ　　Ｂ
１　校名　回答
２　□中
３　△中
４　○中

B2:=INDIRECT(A2&"!"&CELL("address",B2))

まず、CELL("address",B2) は、$B$2というセル番地を示している。
そして、A2セルには、”□中”という文字列が入力されているので、
=INDIRECT(A2&"!"&CELL("address",B2)) は
=INDIRECT("□中!$B$2") ということになる。
INDIRECTは、文字列をアドレスに変えてくれるので、
結局、B2セルには、= □中!$B$2 という数式が入力されていることと同じというわけ。

セル番地を参照するので、
B2:=INDIRECT(A2&"!"&CELL("address",B2))　であって
B2:=INDIRECT(A2&"!"&B2)　ではだめです。

この関数一つ作れば、あとはコピーで集計シートがいくつあっても問題なく、１つにまとまります。

いやー、長年の悩みがすっきりして、気持ちいいー！
それにしても、INDIRECT関数をもう少し、研究する必要がありそうです。

その１からのつづき。

なるべく汎用的に、できれは、マクロは使わずにやるには、どうしたらよいか。

いろいろ調べたら、INDIRECT関数とCELL関数が使えそうだということが分かった。

■INDIRECT関数とは
本来なら文字列として入力されているデータを、計算式の中で使える「セルのアドレス」に変換する。

■CELL関数とは
参照するセルの書式や位置、内容などを調べる

(1)それぞれのデータは次のようになっている。
シート名を校名と同じにしてあるところがポイント

シート名：□中
　　Ａ　　Ｂ
１　校名　回答
２　□中　ほげほげ
３　△中
４　○中

シート名：△中
　　Ａ　　Ｂ
１　校名　回答
２　□中
３　△中　ごにょごにょ
４　○中

シート名：○中
　　Ａ　　Ｂ
１　校名　回答
２　□中
３　△中
４　○中　やれやれ

(2)集計シートを作る。
シート名：集計
　　Ａ　　Ｂ
１　校名　回答
２　□中
３　△中
４　○中

(3)セルB2に次の式を入力する。
=INDIRECT(A2&"!"&CELL("address",B2))

(4)B2の数式を、B3、B4へコピーする。
B3:=INDIRECT(A3&"!"&CELL("address",B3))
B4:=INDIRECT(A4&"!"&CELL("address",B4))
となっているはず。

(5)無事に３つのシートのデータがまとまる。
シート名：集計
　　Ａ　　Ｂ
１　校名　回答
２　□中　ほげほげ
３　△中　ごにょごにょ
４　○中　やれやれ

複数の同じ形式のシートに入力されたデータを串刺し計算することは、Excelでは良く行われる。
=SUM(Sheet1:Sheet2!E3)のような書き方ができる。
しかし、それは数値での話し。
文字列を同じようにはできない。
=CONCATENATE(Sheet1:Sheet2!E3)では、エラーになってしまう。
=Sheet1!E3&Sheet2!E3とすればよいのだが、シートがたくさんあるときは面倒だ。

ずーっと悩んでいた。
(1)下のようなシートをもつファイルを３つの中学校に送付する。(本当はもっと多いけど・・・）
　　Ａ　　Ｂ
１　校名　回答
２　□中
３　△中
４　○中

(2)それぞれの中学校が自校の回答を入力して、返信する。
　□中
　　Ａ　　Ｂ
１　校名　回答
２　□中　ほげほげ
３　△中
４　○中

　△中
　　Ａ　　Ｂ
１　校名　回答
２　□中
３　△中　ごにょごにょ
４　○中

　○中
　　Ａ　　Ｂ
１　校名　回答
２　□中
３　△中
４　○中　やれやれ

(3)この３つのファイルを１つのブックに結合する。

(4)３つのシートを集計する。
　　Ａ　　Ｂ
１　校名　回答
２　□中　ほげほげ
３　△中　ごにょごにょ
４　○中　やれやれ

(3)は複数ファイルを１つのブックにまとめるマクロを使う。
問題は(4)
数値のように簡単に串刺しみたいにまとめる方法はないのか・・・。

アンケートを整理したデータは次のようであった。

【表１】
学年　組　番号　性別　問１・・・問３０
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
１　　１　１　　１　　１　　　　２
１　　１　２　　１　　２　　　　２
１　　１　３　　２　　１　　　　１

しかし、これだと、ピボットテーブルを使っても、思ったように集計する表を作成することができない。（できるかもしれないが、少なくとも私にはうまくできなかった・・・）

そこで、データを次のように整理しなおした。

【表２】
学年　組　番号　性別　問　回答
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
１　　１　１　　１　　１　　１
１　　１　１　　１　　２　　１
　　・
　　・
１　　１　１　　１　　３０　２
１　　１　２　　１　　１　　２
１　　１　２　　１　　１　　１
　　・
　　・
１　　１　２　　１　　３０　２
　　・
　　・

１つの問いに対して、１行のデータである。
これだと（その１）で書いたように、COUNTIF関数で「はい・いいえ」を数えるのは難しいが、ピボットテーブルを使えば、問いごとの集計もできるし、それを学年ごと、組ごと、性別ごとなどに集計するのも、アイテムをドラッグ＆ドロップするだけでよい。
番号をアイテムに加えれば、【表１】のように、回答者ごとのデータに整理することもできる。
難点は、【表１】に比べて、【表２】のほうが、元データ量が増えてしまうことだろうか。

同じことを示すデータであっても、データ構造をどうするかによって、処理方法に違いがでるという例である。

ピボットテーブルは、アイテムをドラッグ＆ドロップするだけで、さまざまな集計ができるので大変便利な機能だ。
アンケートの集計をするために、いろいろと試してみた。

たとえば、次のようなアンケートを考える。

　学年　（１〜６）
　組　　（１〜５）
　番号　（１〜４０）
　性別　（男：１　女：２）
　問１　（はい：１　いいえ：２）
　　　　・
　　　　・
　問３０

これを集計するための元データは、回答者を１行にして次のようになっていることが多い。

学年　組　番号　性別　問１・・・問３０
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
１　　１　１　　１　　１　　　　２
１　　１　２　　１　　２　　　　２
１　　１　３　　２　　１　　　　１

問いごとに、はい・いいえの人数をCOUNTIF関数を使って計算するには、列ごとに計算式を作ればよいので、このような整理の仕方が都合よい。

しかし、組ごと、性別ごとに集計したいとかいう場合には、新たな数式を作る必要がある。しかも、複数の条件をカウントするには、COUNTIF関数では簡単にできない。
また、集計結果は、問１・・・問３０と横に並ぶことになり、縦に並べたいときには数式をそのように書き直すとか、行列を入れ替えてコピーするなどの方法が必要になる。

特に、横並びのデータを横並びに数式を入力するのは、セルを相対的に判断して自動的に数式を書き変えてくれるからよいが、縦並びにするのは一つ一つやらなくてはならないからとても面倒な作業となる。

それで、ピボットテーブルで集計するということになるのだが、それについては（その２）で。

基本は、IF文を使います。

function myMin_1(){
	var data1;
	var data2;
	var str;

	data1 = prompt("input data1=?","");
	data2 = prompt("input data2=?","");
	if(data1>=data2){
		str ="min="+data2;
	}else{
		str ="min="+data1;
	}
	alert(str);
}

でも、便利なmin関数を使えば、ソースは短くなります。

function myMin_2(){
	var data1;
	var data2;
	var str;

	data1 = prompt("input data1=?","");
	data2 = prompt("input data2=?","");
	str = "min="+Math.min(data1,data2);
	alert(str);
}

２つの数値だからいいですが、３つ以上になったらどうしましょう。
でも、それでも１行でできてしまいます。
min関数を入れ子にしてしまいます。４つの数値の場合です。
Javaでフィッシャーの正確確率検定(前編)というサイトで知りました。こんなこともありなのですね　(＠o＠)

function myMin_3(){
	var data = new Array();;
	var str = "";

	for(i=0;i<4;i++){
		data[i] = prompt("input data["+i+"]=?","");	
	}
	min = Math.min(data[0],Math.min(data[1],Math.min(data[2],data[3])));	
	for(i in data){
		ANS += data[i]+"\n";	
	}
	str = str + "min="+min;
	alert(str);
}

もっと数値が増えたり、数値の数がそのたびに変わるとかしたら・・・。
そんなときは、繰り返しを使います。
データ数が変化しても、大きな修正は必要ありません。

function myMin_4(){
	var data = new Array();;
	var str = "";
	var min;

	for(i=0;i<5;i++){
		data[i] = prompt("input data["+i+"]=?","");	
	}
	min = data[0];
	for(i=1;i<5;i++){
		min = Math.min(min,data[i]);	
	}
	for(i in data){
		str += data[i]+"\n";	
	}
	str = str + "min="+min;
	alert(str);
}

大小を判定するだけでもいろいろな考えがあるものです。
それぞれの特徴を知って、効率よくプログラミングしたいものです。

仕事柄、いろいろな文書を作ることが多いが、堅苦しい書類もあれば、やわらかなお知らせやお便りもある。

私は、Word派だが、文書のタイトルや見出しに、ワードアートを多用するのを好まない。
タイトルや見出しは、フォントの種類と色を変え、必要であれば枠で囲んだり、下線を引いたりする。
さらに、目を引きたい場合には、ワンポイントの小さなイラストを配置する。
シンプルだが、ちょっと凝った感じに見せることができる。

でも、この小さなイラストだが、これは！というものがなかなかない。
そこで、5年くらい前からちょこちょこと作りためたものが、丁度、100種類になったので、公開することにした。（サンプルは上の画像↑）

Wordの図形描画で描いたもので、PowerPointを使って、EMPファイルに変換してある。BMPファイルとは違い、拡大してもぎざぎざにならない。
メニューから図の挿入で文書に挿入すればよい。
白黒になっているが、図はグループ化してあるので、グループを解除すれば、線の太さや色、塗りつぶしなども変更可能である。

ダウンロードは、ここをクリックしてほしい。

とにかく速いと言われるGoogle Chorme がバージョンアップしたというので、早速インストールしました。
通常はIE7を使っていますが、JavaScript-STARで大量のデータを分析するときは、Google Chormeを使います。
JavaScriptの処理速度は、圧倒的に違います。

それでも、バージョン１のとき、被験者200人、水準50項目を自動集計検定２×２で計算すると、



このようなお疲れクローム君が表示されました。

しかし、バージョン２にしたところ、あっという間に計算してしまいました。
またまた、IEには水を開けた感じでしょうか。

safariのバージョン４は試していないのですが、やっぱり、Googleおそるべし！です。