素人でもできたデータ処理の技

まぐまぐ！メルマガ＜素人でもできたデータ処理の技＞

　第13号 2005/09/28

＜EXCEL＞

当サイトの情報をご利用になる場合は、全て自己責任でお願いします

第13号 2005/09/28

＜MySQL編　第９回目＞

　今回は、PHPでクロス集計機能を行う方法を紹介させて頂きます。

2004年の販売データを縦軸に地区、横軸に商品にして金額を集計します。その際、
地区コードを地区マスターを使って地区名に読み替えます。

今回は、横軸の項目数がいくつになるのかわからないので、先に項目数を調べて、
それをもとに、テンポラリーテーブルやSELECT文のSQL文をその都度作ります。
(for文のループで作り出しています。)

PHPプログラム

<?php

$con = mysql_connect("localhost","ユーザー名","パスワード");
mysql_select_db("データベース名", $con);

// 横軸の項目を調べます。

$sql1 = "SELECT shohin
FROM uriage_2004
GROUP BY shohin
";

$rs1 = mysql_query($sql1, $con);
$n1 = mysql_numrows($rs1);
$m1 = mysql_numfields($rs1);

// 調べた横軸の項目を使ってテンポラリーテーブルTTBL01を作成します。
// 項目名は、data1,data2,・・・と項目数(=$n1)だけ作ります。

$sql="CREATE TEMPORARY TABLE TTBL01(
tiku varchar(254),
";

for ($i=0; $i<$n1; $i++) {
$sql .= "data".$i." int,";
}

$sql .= "INDEX I_tiku (tiku))";

mysql_query($sql, $con);

// 調べた横軸の項目を使って2004年の販売実績をデータをテンポラリーテーブルの該当する項目に入れます。

$sql="INSERT INTO TTBL01
SELECT tiku
";

for ($i=0; $i<$n1; $i++) {
$sql .= ",IF(shohin='".mysql_result($rs1,$i,0)."',kingaku,0)";
}

$sql .= " FROM uriage_2004";

mysql_query($sql, $con);

// テンポラリーテーブルTTBL01を集計した結果を入れるためのテンポラリーテーブルを作成します。
// テンポラリーテーブルTTBL01と同じ構造です。

$sql="CREATE TEMPORARY TABLE TTBL02(
tiku varchar(254),
";

for ($i=0; $i<$n1; $i++) {
$sql .= "data".$i." int,";
}

$sql .= "INDEX I_tiku (tiku))";

mysql_query($sql, $con);

// TBL01を集計した結果をテンポラリーテーブルTTBL02に入れます。

$sql="INSERT INTO TTBL02
SELECT tiku
";

for ($i=0; $i<$n1; $i++) {
$sql .= ",SUM(data".$i.")";
}

$sql .= " FROM TTBL01 GROUP BY tiku";

mysql_query($sql, $con);

// TBL02の地区コードを地区名に読み替えます。

$sql="SELECT tiku_mast.tikumei
";

for ($i=0; $i<$n1; $i++) {
$sql .= ",TTB02.data".$i;
}

$sql .= " FROM TTBL02 LEFT JOIN tiku_mast ON TTBL02.tiku = tiku_mast.tikucode";

$rs = mysql_query($sql, $con);
$n = mysql_numrows($rs);
$m = mysql_numfields($rs);
mysql_close($con);

// 集計結果を表示します。

$itigyou="地区\t";

for ($i=0; $i<$n1; $i++) {
$itigyou .= mysql_result($rs1,$i,0)."\t";
}

$itigyou .= "\n";

print($itigyou);
for ($i=0; $i<$n; $i++) {
$itigyou="";

for ($j=0; $j<$m; $j++) {
$itigyou .= mysql_result($rs,$i,$j)."\t";
}

$itigyou .= "\n";
print($itigyou);
}
?>
(プログラム終わり)

　今回は、見た目が非常にややこしいプログラムになります。ただし、基本的な
部分は前回のメルマガの集計作業と同じで、今回は横軸の項目を作り出す部分が
加わっただけです。
PHPを使うと今回のようにデータの内容に応じてSQL文を自由に作り出せるので、
Accessよりも応用がききます。

　今回も前回と同じように、このファイルのURLを、Excelの“データ”　→　
“外部データの取り込み”　→“データの取り込み”のファイル名に入れてやる
と集計結果がExcelで取り込めます。
　例えば、このphpファイルをshukei.phpとした場合、ファイル名に
http:>//（URL名）/shukei.php　をいれて、タブ区切りのテキストデータを読み込む
設定をすれば取り込めます。

************************************************************************
＜編集後記＞

　MySQLとPHPでクロス集計はできますが、Accessに比べると非常に手順がややこ
しくなります。１度プログラムを作れば、２回目からはコピー・ペーストと部分
的な変更で作成できますので、作業的にはそんなに手間はかからないと思います
が、１回限りの集計を行う場合には、Accessの手軽さにはかないません。

　「メールやHomePageに貼り付けられたExcelのファイルがInternetExplorerの
中で開いてマクロがうまく作動しない」との問い合わせのメールがありました。
　私のパソコンではだいぶん前にInternetExplorerの中で開かないように設定し
てあったのでこの問題に気付きませんでした。申し訳ございません。

設定方法は、

マイコンピュータを開いて

ツール　→　フォルダオプション　→　ファイルタイプ　→　拡張子のXLSをクリック
→　詳細設定　→　同じウインドウで開くのチェックを外してＯＫ

でできます。なぜ、InternetExplorerではなくマイコンピュータで設定できるの
か不思議です。

　次回は、PHPで作成したデータを取り込むExcelのマクロを紹介させて頂きます。

************************************************************************