Category Archives: テーブル

データマッチング – 別々の顧客のエクセルデータをまとめる方法

問題:

別々の顧客のエクセルデータをまとめる方法。
4月の売上げファイルには
A氏 300円
B氏 400円(次にCが無く)
D氏 500円
E氏 200円
F氏 400円

5月のデータには
A氏 100円
B氏 400円
C氏 200円(Cは4月には無い)
D氏 300円(次にEは無く)
F氏 300円

2つのデータを下記のとおり顧客ごとににまとめて比較できるようにしたいです。
・・4月・・・ 5月
A氏 300円 ・100円
B氏 400円 ・400円
C氏 ・・・・ 200円
D氏 500円 ・300円
E氏 200円
F氏 400円 ・300円


・4月のファイルには
C氏のデータは無いが、5月にはある
かつ
4月に有ったE氏のデータが、5月にはない

http://www.superdbtool.com/download.htm
上記のソフトをダウンロードして、Excelを閉じてからインストールします。終わりましたら、Excel起動して、データの入っているファイルを開きます。(インストールガイド)

①、Excelシート上にて右クリックして、機能拡張⇒テーブル⇒「データマッチング・突合・照合・SQL」をクリックします。

 

②、下記のガイドのようにデータマッチングを行います。

ここでは、マッチングの部分のみ纏めましたが、複数のシートをひとつに纏めて、氏名も重複削除機能を使って事前に整理すれば、C氏、E氏の両方ともA列に表示できます。もっと自由にしたい場合、下記のステップで「次へ」ではなく、「SQL実行」へ行けば、条件や行列の制御などもできます。

39 SQLで2つの台帳を自由にデータマッチング

別の下記記事でデータマッチングについて簡単に纏めましたが、
http://superdbtool.com/blog/archives/103
実際にデータマッチングは様々な条件があって、そしてマッチングのソート順も
指定する必要な場合があります。

もっと自由にデータをマッチングするにはやはりSQLは一番柔軟です。
ExcelDevToolで使っているSQL文法はSqliteと同じです。
以下は例で説明します。
データ1(Aシート):

名前 年齢 臭味 婚姻 年収
たろう 58歳 趣味カメラ 既婚 年収800万…
まちこ 27歳 趣味陶芸 未婚 年収300万…
はなこ 32歳 趣味ボーリング 既婚 専業主婦…
よさく 83歳 趣味山歩き 既婚 年金生活


データ2(Bシート):

名前 出発 帰宅 行き先 備考
はなこ 1月1日出発 1月7日帰宅 行き先京都 おみやげ八つ橋…
たろう 3月4日出発 3月16日帰宅 行き先北海道 おみやげ昆布…
まちこ 12月25日出発 12月30日帰宅 行き先沖縄 おみやげシーサー…


やりたいこと:AシートのデータとBシートのデータを名前でマッチングして、データ2に存在しない
名前なら、「存在しない」と表示する

http://www.superdbtool.com/download.htm
上記のソフトをダウンロードして、Excelを閉じてからインストールします。終わりましたら、Excel起動して、データの入っているファイルを開きます。(インストールガイド)

①、Excelシート上にて右クリックして、機能拡張⇒テーブル⇒「データマッチング・突合・照合・SQL」をクリックします。
DataMatching-3-1

②、データ1にAシートのデータをタイトルを含めて選択します。
DataMatching-3-2

 ③、データ2にBシートのデータをタイトルを含めて選択します。
DataMatching-3-3

DataMatching-3-4

④、ステップ3でデータ1とデータ2の条件に「名前」を選択して、「SQL実行」をクリックします。
DataMatching-3-5

⑤、デフォルトで以下のSQLが生成されます。このSQLでは一般的なマッチングには十分です。
DataMatching-3-6

 ⑥、今回の例では「存在しない」の表示も要りますので、SQLを少し修正します。
DataMatching-3-7

SELECT
    T1.名前 AS T1_名前,
    T1.年齢 AS T1_年齢,
    T1.臭味 AS T1_臭味,
    T1.婚姻 AS T1_婚姻,
    T1.年収 AS T1_年収,
    T2.名前 AS T2_名前,
    T2.出発 AS T2_出発,
    T2.帰宅 AS T2_帰宅,
    T2.行き先 AS T2_行き先,
    T2.備考 AS T2_備考,
    (CASE
        WHEN T2.名前=” OR T2.名前 IS NULL THEN ‘存在しない’
    END) 結果
FROM T1 LEFT OUTER JOIN T2 ON

  T1.名前 = T2.名前

⑦、「実行」ボタンをクリックすれば、以下の結果が出ます。
DataMatching-3-8

 

38 エクセルでシートデータを分割

Excelで複数のSQLを一括で実行して、実行した結果を各シートへ出力することによって、一つのシートのデータを様々な条件で複数のシートへ分割できます。

ExcelDevToolを使って、下記の問題を解決する例を説明します。

Goo教えてから:C列に生徒番号、D列に氏名、E列にクラスがAからFまでランダムに入っています。
この名簿を新しいファイルに、クラスごとに別のシートにしたいです。
できればシート名も「A」「B」となっていれば助かります。
A,B列にもデータが入っていますが、今回は生徒番号、氏名、クラスのみの
ファイルにしたいです。

http://www.superdbtool.com/download.htm
①、上記のソフトをダウンロードして、Excelを閉じてからインストールします。終わりましたら、Excel起動して、データの入っているファイルを開きます。(インストールガイド)

②、本例で使うデータはExcelDevToolの擬似個人情報データ作成機能を使って作成します。
詳しくはhttp://superdbtool.com/blog/archives/30
SplitSheetData-2

②、Excel上にて分割対象データを選択して、右クリックで機能拡張⇒テーブル⇒テータマッチング/突合/照合/SQLをクリックします。
SplitSheetData-3

③、データ1は選択した範囲で、次へ
SplitSheetData-4

④、データ2はなし
SplitSheetData-5

 ⑤、SQL実行をクリックします。そうするとデータを中間DBへ登録して、SQLエディタが表示されます。
SplitSheetData-6

⑥、SQLを下記のように編集して、実行をクリックします。

SQLは数式で生成できます。例えば:


SplitSheetData-7

⑦、下記のような結果が表示されます。
SplitSheetData-8
ExcelDevToolのシート名一括変更機能を使って、
A,B…のように一括変更できます。またシート名ソート機能もあります。

36 エクセルで特定の行だけ別シートに抽出

ExcelDevToolを使って、複数の条件でデータ抽出して、特定の行だけ別シートに抽出する機能を具体例で説明します。
http://www.superdbtool.com/download.htm

例1:下記のデータに対して、科目が「英数国」のデータのみを抽出して、別シートに表示したい

科目 生徒番号 生徒名 第1回模試 到達度1 第2回模試 到達度2
英数国 10101 今川義元 62.5 A2 60.2 A2
英数国 10101 伊達政宗 50.6 B2 55.6 b1
10101 今川義元 56.9 B1 52.3 B2
10102 伊達政宗 52.5 B1 60.2 A2

①、Excelシート上にて右クリックして、機能拡張⇒テーブル⇒「複数条件でテーブルからデータ抽出」をクリックします。
CopyDataToOtherSheetByCondition-1

②、抽出条件は「条件を追加」でたくさん追加できます。条件には複数の入っているエクセルも選択できますし、固定の値も入力できます。
CopyDataToOtherSheetByCondition-3

③、抽出をクリックしたら、以下のデータが出力します。
CopyDataToOtherSheetByCondition-4

例2:下記のデータに対して、必要な列のみを抽出して、別シートに表示したい

下記サンプルをご覧ください。

また、生成したSQLをちょっと修正すれば、データマッチング画面を使ってもいいです。

科目 生徒番号 生徒名 第1回模試 到達度1 第2回模試 到達度2
英数国 10101 今川義元 62.5 A2 60.2 A2
英数国 10101 伊達政宗 50.6 B2 55.6 b1
10101 今川義元 56.9 B1 52.3 B2
10102 伊達政宗 52.5 B1 60.2 A2

予想結果:

科目 生徒名 第1回模試 第2回模試
英数国 今川義元 62.5 60.2
英数国 伊達政宗 50.6 55.6
今川義元 56.9 52.3
伊達政宗 52.5 60.2

①、Excelシート上にて右クリックして、機能拡張⇒テーブル⇒「データマッチング/突合/照合」をクリックします。
CopyDataToOtherSheetByCondition-10

②、下記のようにデータ1を選択して、データ2の空で次へ行きます。
CopyDataToOtherSheetByCondition-5
CopyDataToOtherSheetByCondition-6

③、SQL実行をクリックします。
CopyDataToOtherSheetByCondition-7

④、検索のSQLに以下のSQLを入力します。
SELECT
T1.科目,
T1.生徒名,
T1.第1回模試,
T1.第2回模試
FROM T1
CopyDataToOtherSheetByCondition-8

⑤、結果は別シートへ出力します。
CopyDataToOtherSheetByCondition-9

33 ExcelDevToolのインストール/アンインストールガイド

インストールについて、問い合わせの方もいらっしゃいましたので、注意点を説明します。
本ソフトはOffice32Bitしか利用できません。Office64Bitを利用されている方はOffice32へ切り替えてから
ご利用ください。(普段Officeのインストールメディアに両方入っています。)
32 ビット版の Office 2010 は、ほとんどの人に推奨されるオプションです。

1. ダウンロード

①、ExcelDevToolのダウンロード
基本的に、http://www.superdbtool.com/download.htm から最新版をダウンロードしてご使用ください。
Vectorからもダウンロードできます。(最新版ではない可能性があります。)
http://www.superdbtool.com/download.htm

②、ダウンロード時に署名なワーニングがあった場合、それを無視してダウンロードしていただければ問題ないです。Windows8の場合、以下の画面も出る場合があります。Office2003以前でも使えるように、発行元の設定はしていません。インストールして問題ありません。



インストール&アンインストール(Youtube動画)

2.アンインストール方法

方法1:開いているExcelをすべて閉じてからセットアップのMSIファイル(上記1にてダウンロードしたセットアップファイル)をクリックして、以下の画面で削除を選択すれば簡単にアンインストールできます。
DeleteDevTool-1

方法2:普通のWindowsソフトウェアのアンインストールと同じでWindowsの「プログラムの追加と削除」を経由してアンインストールすることもできます。

Windows8:
http://windows.microsoft.com/ja-jp/windows-8/uninstall-change-program

Windows7:
[プログラムと機能] を開くには、[スタート] ボタン [スタート] ボタンの画像、[コントロール パネル]、[プログラム]、[プログラムと機能] の順にクリックします。
http://windows.microsoft.com/ja-jp/windows/uninstall-change-program#uninstall-change-program=windows-7

WindowsXP:
[スタート] ボタンをクリックし、[コントロール パネル] をクリックして、[プログラムの追加と削除] をダブルクリックします。[現在インストールされているプログラム] ボックスで、削除するプログラム[ExcelDevTool]をクリックし、[削除] (または [変更と削除]) をクリックします。プログラムの削除を確認するメッセージが表示されたら、[はい] をクリックします。
http://support.microsoft.com/kb/307895/ja

一時的にExcelから外したい場合、下記ページをご参照ください。
COM アドイン プログラムを登録または削除する

3. インストール

①、インストール前は開いているExcelファイルをすべて閉じてから
インストールしてください。インストールが終わりましたら、Excelを起動すれば即に利用できます。
インストールファイルをダブルクリックして、インストールします。
InstallDevTool-1
InstallDevTool-2
InstallDevTool-3
InstallDevTool-3
②、インストールが終わりましたら、Excelを起動して、下記のメニューが表示されたかを確認します。
InstallDevTool-4
右クリックも同じメニューが表示されます。
Office2013 64bit版は表示できないので、予めご了承ください。アドインツールがメニューに出てこない場合、まずOfficeのバージョンは64Bitかどうかをご確認お願いします。
使用している Office のバージョンを確認する方法

③、Office2007以降を利用されているなら、下記のようにExcelDevToolのメニューをクイックアクセスツールバーに追加できます。
InstallDevTool-6

InstallDevTool-7

インストール後、拡張メニュー出てこない場合、またはエラー発生した場合、どうすればいいでしょうか。
下記をご参照ください。
http://superdbtool.com/blog/excedevtoolqa