Aotori7 for Excel 基本機能紹介

Aotori7 for Excel　(略称はAFE7)　はExcelのVSTOアドインです。ExcelとMySQLを利用するシステム開発ツールです。

AFE7の最新のインストラーファイルは下記のリンク先から取得できます。

AorotiForExcel_7

インストールについては「Aotori for Excel のインストール」を参照して下さい。MS Office及びWindowsのバージョンが多いため、インストール時のエラーが発生する可能性があります。足りないランタイムなど自動的に取得しインストールしますが、WindowsとOfficeのアップデートについて、インストール前に確認することは無難でしょう。

MySQLとの接続について

AFE7(Aotori7)のログイン画面からMySQL専用の接続設定、確認ができます。設定用初期パスワードは「admin」です、変更後保存できます。

※インストーラにあるcreate.sqlでAotoriの初期ユーザー、コントロールテーブルの作成が必要です、DBツールにて作って下さい。

MySQLの接続情報を正しく入力できたら、テストボタンで確認できます。「接続OK」が表示されたら「ログイン情報」よりログインできます。

基本機能について

リボンの使い方はAotori6(DBはファイル型のSqLiteを採用する)との差異はほとんどありません。

ここでは最新の機能を1つ紹介致します。

条件検索後にデータ更新、追加があれば、ボタン1つで一覧の更新できるようにリボンに「更新」ボタンを追加しました。

上記の図のように、以前の投稿「Aotori for Excel 管理応用編 -　会員管理の作成」で作成した「会員情報一覧」にて「検索条件」から会員№「A001」を指定検索した後、データが追加された場合、Aotoriリボンの「更新」ボタンをクリックするだけで追加されたデータが表示されるようになります。

以上、なにが不具合、改善要望があれば、連絡して頂ければ幸いです。

advice9801@gmail.com

2018/10/28

Aotori for Excel 管理応用編 - 給与システムの作成

方針（夢）：Do it by user!

ステップ　①

－　デザイン

作成手順及び使用方法の動画

今回、Excel2007のテンプレートから「給与計算表」をダウンロードして、ベースとして利用することになります。

この「給与計算表」ブックは、下記の3つのシートで構成されます。

シート①

「社員情報」：社員情報の一覧ですね、社員情報入力シートを追加します。「社員情報一覧」と「社員情報入力」2つに分けます。

シート②

「給与計算」：実際の勤務状況によって当月の給与を計算してます。このシートも「給与計算一覧」と「給与計算入力」2つに分けます。

シート③

「給与明細書」：給与明細書そのものです。色んな形式に変更できると思いますが、とりあえずこのままで利用します。

最終的なシステムは、「社員情報入力」、「社員情報一覧」、「給与計算入力」、「給与計算一覧」、「給与明細書」5つのシートで構成されます。

ステップ　②

－　表作成

※ファイル名「給与計算」で保存します。表名の一部になります。

「社員情報入力」と「給与計算入力」はデータ表として、「社員情報一覧」、「給与計算一覧」、「給与明細書」は参照表としてデータベースに登録します。

※テンプレートには色んな式が入ってますが、後から直す必要があります。

・「社員情報入力」表は、「社員情報」から流用したもので、項目はそのままにします。

Aotoriリボンからログイン画面を開き、新規DBファイル名で接続確認できたら、ログインします。この場合データベース新規作成となり、下記のメッセージが表示されます。

カラム名とカラム名のアドレスも入れましょう。リボンの「カラム名選択」と「Aotori Design」画面の「カラム名」ボタンを活用すれば、入力は楽です。

キーは、社員番号のみで十分でしょう。

「社員情報一覧」の作成は簡単です。「社員情報一覧」シートを選択してからAotoriリボンの「参照作成」ボタンをクリックします。「Aotori Design参照作成」画面から、先作成した表を選んで、「データ表作成」チェックを外して、「縦」に25を入力します。

下記のように、入力画面にて入力した情報一覧に反映できればOKです。

「給与計算入力」と「給与計算一覧」は上記とほぼ同じように定義します。

「給与計算入力」について、月単位で入力するため、給与支払い日をキーとして追加します。「社員情報入力」で入力した社員番号と社員名を利用したいので、Aotoriリボンの「マスタ定義」をクリックして、そのままマスタとして定義します。D列からL列は、参照項目として利用しますが、「給与計算入力」では入力しないので、非表示にしましょう。

「給与計算入力」画面から入力時Aotoriリボンの「マスター参照」ボタンをクリックしてマスター参照できます。

「給与計算一覧」は、入力画面を基づいて簡単に作成できます。必要の項目を選んで、ループ対象、縦方向の表示数を設定します。完成した一覧は下記のようになります。

「給与明細書」の表定義について、データが縦方向4レコード分表示するため、縦方向の重複数値4で設定し、ステップが12で設定します。

上記の図のように項目の「アドレス」を正しく設定できれば、下記の図のように「給与明細」が出力されます。4件超えた場合、「次ページ」で次のデータ表示ができます。

注意点

・「マスター定義」利用できない場合、データ定義再読み込みのためAotoriに再ログインが必要の可能性があります。

・縦方向の複数のレコードの整形出力は、理解しにくいと思いますが、かなり便利な機能です。当然ですが、横方向も利用できます。ポイントは「ステップ」数の入力ですね。

以上、お疲れ様です。

完成したDBファイル、Excelテンプレート

下記のリンクからダウンロードできます。

給与計算_2018.db

給与計算.xlsx

※AotoriForExcel本体は下記のリンクから最新版を利用してください。

AotoriForExcel_installer.zip

Aotori for Excel 基本機能紹介

Aotori for Excel　(略称はAFE6)　はExcelのVSTOアドインです。Excelとデータベースを利用するシステム開発ツールです。

最新のインストラーファイルは下記のリンク先から取得できます。

Aotori for Excel Installer Download

インストールについて、「Aotori for Excel のインストール」を参照して下さい。

基本機能　①　データベースの利用

上記のリンク先にあるAotori for Excelは、バージョン6です、データベースはSQLiteを利用します。

SQLiteのメリット

・データベースのインストール、設定は不要

・パフォーマンスは他のデータベースに負けない

・ファイル単位なのでバックアップを取るのが楽です

・数万件のデータを扱ってみた限りではMySQLと遜色が無いです

・データベースの接続は簡単、ユーザーID/パスワードあればOKです

・共有ファイルにデータベースファイルを共有すれば、

　ネットワーク経由で共有できます

・SQLite自体は著作権を放棄しパブリックドメインに帰属するため、自由に利用できます

AFE6のログイン画面

ログイン画面の通り、データベースファイルと接続用パスワード正しく入力できれば、接続は完了となります。データベースファイル存在しない場合、自動的に新規で作成されます。ログインユーザーとパスワードは別でユーザー表に定義されるので、より安全なセキュリティー対策を取ってます。

※AFE自体のライセンスは有料です。長期利用する場合、ライセンスの取得が必要です。

基本機能　②　迅速なシステム開発に最適

入力画面、検索画面のデザインがあれば、データ表示作成にはプログラムなしでできます、効率は非常に良いです。

例えば上記のデザインがあれば、その通り、簡単にデータベースの表を作成できます。作成したら、すぐに条件検索、データの保存、削除、更新、データの型チェック(一部)、サイズチェック機能が揃ってます。

基本機能　③　一括ＰＤＦ、印刷機能あるから、一覧画面及び入力画面そのまま一括で出力出来ます

別々で帳票をデザインする必要ないですが、項目絞って帳票にしたい場合、エクセルで簡単に行えます。帳票の開発も楽です。

Aotori for Excel の機能ではないですが、エクセルの基本機能を活用して、より多彩なフォームが作れます。エクセルの計算式など固有機能はそのまま利用できます、さらに複雑な部分はVBAと言う強力なツールも利用できます。

簡単に利用できることと豊富な機能を持つことは、Aotori for Excelの設計コンセプトです。いろんな場面で活躍できるように期待します。

Aotori for Excel 管理応用編 - 会員管理の改善

方針（夢）：Do it Yourself

改善　①　画像ファイルの追加

－　既存表定義の修正から

「Aotori for Excel 管理応用編 -　会員管理の作成」で作成した表を利用して、会員の写真を表示できるように修正しましょう。

ポイント

Aotori for Excelでは、Excelに画像項目を表示させるため、下記の2つの条件が必須となります。

１．項目定義　画像項目の型は、必ず「画像ファイル」であること

　　項目定義　画像項目のデータサイズは、必ずカンマ区切りの半角数字であること、例えば：100,70。Excelに出力する画像のサイズになります。

２．画像を表示しようとするセルに正しくファイルのフルパス、ファイル名をデータとしてデータベースに登録されたこと。

例：パソコンにある画像ファイル「C:\pic\IMG_20161106_120848.jpg」をExcelの「会員情報(入力)」シートの「その他①」セルに表示する場合、下記の図のように

１．項目定義の修正は、ＤＢツールPupSQLiteで、表「会員_2018_会員情報(入力)」の「その他①」の「coltype」を「画像ファイル」に修正、「colsize」を「100,70」に修正します。

２．その他①セルに「C:\aotori_source\sample\pic\IMG_20161106_120848.jpg」を保存します。

実際の表示結果は下記のようになります。

一覧のほうも出力したいですね、「会員情報一覧」シートに対応する定義表を同じように修正したら、表示できます。

※「検索条件」で会員No.を「A001」で絞ることを忘れないでね。

改善　②　複数会員の一覧表示の追加

－　シートの追加から

ステップ① シートコピー

「会員情報一覧」シートをコピーして、「全会員情報一覧」を作成します。全会員が持つ情報の一覧なので、下記のようにレイアウトも調整します。

ステップ②　Aotori for Excelアドインの「参照作成」ボタンをクリックして「Aotori Design 参照作成」画面にて、表「会員_2018_会員情報(入力)_data」を選んで、「参照マスタ確定」ボタンをクリックします。

一覧でデータ入力行えないので、「データ表作成」のチェックを外して、縦方向の重複行数を入力します。不要な行は、下記の図のように選択してから「Delete」キーを押すと削除できます。

作成直前のデザイン画面は下記のようになります。

ステップ③　登録したデータがこの一覧に表示すると、下記のようになります。

上記3ステップでかなり改善しましたね。会員管理の機能がほとんど揃ってますね。

お疲れ様でした！

完成したDBファイル、Excelテンプレート

下記のリンクからダウンロードできます。

会員_2018_改善版.db

会員_2018_改善版.xlsx
重要事項：ファイル名の修正が必要です。拡張子はそのままで、「_改善版」を消して下さい。

※AotoriForExcel本体は下記のリンクから最新版を利用してください。

AotoriForExcel_installer.zip

Aotori for Excel アドインを長期利用する場合、ライセンスの購入が必要です。
十分のご試用の上ライセンスの購入をご検討をお願い申し上げます。

Aotori for Excel 管理応用編 - 会員管理の作成

方針（夢）：What you see is What you get

ステップ　①

－　デザイン

Excelで会員管理システムを作成しました、前回と同様作成時間は15分、そのぐらい。

：）項目調整は自由です、当たり前　w 。

今回、予め入力と一覧用シート両方作成しました。

※入力シート作って、一覧は、やり取り行を25行まで増やすだけで作りました。

入力用シート：

一覧表示用シート：

ステップ　②③

－　表名を決めてAotoriにログインし、項目を決めて表を作成

ファイル名付けてファイルを「会員_2018.xlsx」で保存しました。入力用シートは「会員情報(入力)」で、一覧用シートは「会員情報一覧」で名づけしました。

Aotoriログイン画面にて、新規のデータベースファイル指定しました。※パスワード入力しない限り、データベースのパスワードは「aotori」となります。このパスワードは、DBツールログイン時必要です。

では、コントロールキーを押しながらデータベースに登録したい情報セルを選んで、データ表を作成しましょう。

選択できたら、Aotoriリボンの「表作成」ボタンをクリックして、表をデザインします。

説明：やり取り記録を増やすため、日付(年月日)と時刻(時分)をキーにしました。

ステップ　⋯

もう、ないですかね。

え～　本当ですか？

うん、本当。今回のケースは、やり取りが一覧内容になるため、本当の会員全員の情報一覧ではないため、これで完成となります。

但し、正しく会員とのやり取り情報を表示するため、検索条件が必要。

会員全員の情報一覧が会員情報を基づいて作成できます。(まだ今度

完成したDBファイル、Excelテンプレート

下記のリンクからダウンロードできます。

会員_2018.db

会員_2018.xlsx

※AotoriForExcel本体は下記のリンクから最新版を利用してください。

AotoriForExcel_installer.zip

Aotori for Excel 会計応用編 - 仕訳帳の使用

方針（夢）：What you see is What you get
一覧表示

－問題点と対策

「Aotori for Excel 会計応用編 -　仕訳帳の作成」で作成したExcelとSQLiteデータファイルを使ってみましょう。先ずは、「会計_2018.xlsx」ファイルをExcelで開き、AotoriリボンよりAotoriツールにログインします。

※今回は、Excel2007を使います。前回Excel2013で作ったExcelファイル、利用したDBファイルそのまま利用ですます、うん、当たり前 w。

上記の図のようにデータを入力して、｢ﾃﾞｰﾀ保存」ボタンをクリックしてデータを保存できます。

問題点1.　－重複表示できない項目

一覧画面「会計_仕入帳」シートは、入力用シートそのままを利用して、6行目を32行まで増やすだけだと、タイトル、部門、年度の複数件表示が崩れます。縦方向へ一覧表示すると、他の項目と重ねてしまいますし、重複しないで表示すると、1個しか表示できない。

対策として、一覧表示に列を追加して、部門と年度を正しく複数件表示するように一覧シートを修正します。

注意点として、「参照作成」で作り直しではなく、後からデザインを修正したい場合、ＤＢツールを利用しましょう。

最終的な表定義は、下記のようになります。

一覧シートは、下記のようになります。

問題点2.　－32件以上のデータ表示について

一覧シートのデータ表定義には、最大32レコードが表示できるよにデザインしました。では、32件以上のデータが入力された場合、どうなりますか。

そう、上記の図のように、この問題は、Aotoriアドインが解決してくれます。32件を超えた場合、改ページで表示されます。

普通のExcel表示とは違って、一覧シートには全てのデータを持つではなく、一回の表示は最大の表示行数まで表示します。残り分は改ページボタンをクリックすれば、表示されます。

問題点3.　－条件検索について

一覧シートのデフォルトデータ表示は全件表示となります。
わざわざ一覧シート、参照用テーブルを作って、条件付きで検索できればいいでしょう。

はい、Aotoriアドインで簡単に条件付き検索ができます。
SQLの知識が必要ですが、検索用SQL文だけでは、そんなに難しくないので、下記の手順で簡単に実現できます。

Aotoriアドインの「検索」グループにある「検索条件」ボタンをクリックすると、「Aotori Make Select Sql」画面が現れます。

上記の図のように「where  `3`='2010'」を既存のSQL文の後ろに付けるだけで、年度条件が作れます。「保存」ボタンをクリックすると登録できます。条件付きSQLを選択して「確定」ボタンをクリックすると、条件指定で検索結果が戻ります。

SQL自身が問題ないだけど、検索結果は予定通りにならない場合、where条件の指定誤りがあると思われます。カラム名「`3`」を括る「`」が必須ですし、条件の「'2010'」の型が文字列である場合、「'」で括る必要があります。

カラム名は、ＤＢツールで確認できます。

カラム名の確認方法：上記の図のように、「_data」が付いてない入力表にカラム名が定義されます。例えば、「年度」を指定したい場合、id「3」を指定すればＯＫです。ここでは、テーブル作成時のカラム名が「colname」列に定義されたため、分かりやすいですね、カラム名の定義は役立ちます。

年度2010の一覧表示は、予想の通り下記図のようになります。

完成したDBファイル、Excelファイル

下記のリンクからダウンロードできます。

会計_2018 - 使用.db

会計_2018.xlsx

※AotoriForExcel本体は下記のリンクから最新版を利用してください。

AotoriForExcel_installer.zip

Aotori for Excel 会計応用編 - 仕訳帳の作成

方針（夢）：What you see is What you get

ステップ　①

－デザイン

Excelで仕訳日記帳を作成しました、作成時間は15分、そのぐらい。
：）項目調整は自由です、当たり前　w 。

ステップ　②

－表名を決めてログイン

データをExcelで管理するため、①のシートでは十分できますが、場合によって「部門」を増やしたい、年度別で管理したい、別のフォーマットで出力したい⋯

はい、データベースで管理したいことですね。

ステップ②では、超簡単の方法で、とりあえずデータをデータベースに保存できるようにしましょう。

ポイント
データベースに登録するため、①のファイル名、シート名を決めましょう。
※データベースのテーブル(表)名となります。

ExcelのAotori for Excel リボンを開いてログインボタンをクリックすると、ログイン画面が現れます。「データベースファイル」を選択して、ログインします。

→インストール編を参照

ステップ　③

－項目を決めて表を作成

データベースに登録したい項目を選択して、「表作成」画面で項目の情報を編集しましょう。

ポイント
コントロールキーを押しながら項目を一気に選択しましょう。

選択したら、AOTORIリボンにある「表作成」ボタンをクリックして表を作成します。

ポイント

・カラム名は必須ではないですが、後からテーブル構成を調べたい、条件検索したい場合役に立つものですので、なるべく付けましょう。

・項目数は290個以上、数が半端ないですね、気になります。データが記入されない場合、データベースに負荷かかりすぎです。

キー項目、データサイズ、データ型(とりあえず「文字列」で)、カラム名を決めたら、「表作成」ボタンをクリックして表を作成します。

注意

「OK」を押すと、以前作成した同名の表示定義、データが全部なくなるので、要注意!

無事作成できたら、「「ファイル名_シート名」で表を作成しました」のメッセージが表示されます。

データを入力して、保存できれば、システム完成！

お疲れ様です、おめでとうございます！

ちょっと待って⋯

「品名」、「年度」を絞って検索したいです、

別の「部門」のデータも入力したいですが、どうすればいいですか。

シートを増やして、テーブルを増やして、なんとかなるでしょうか。

うん、確かになんとかなると思いますが⋯

ステップ　④

－入力用、一覧表示用表を別々で作成

数多く無駄なカラム定義、貧弱な検索機能、より多くの部門対応等の問題を解決するため、入力用と一覧表示用シートを一個増やしましょう。

「会計_仕入帳」シートをコビーで一個増やして、「会計_仕入帳(入力用)」シートを作成します。下記のように、データ行が一行でＯＫです。

入力用表のカラム数はなんと12個だけです、290個よりずいぶん減りましたね、データベースの負担がかなり軽減されます。どう考えてもこの程度最適化が必要ですね。

下記の図のようにキー項目決めて、カラム名を入力して表を作成します。

入力用画面はこれで良いので、ステップ③で作成したシートを一覧画面(参照のみ)にしましょう。

まず、「会計_仕入帳」シードを選択して、AOTORIリボンの「参照作成」ボタンをクリック、「Aotori Design参照作成」画面にて、参照できる表を選択します。「会計_2018_会計_仕入帳 (入力用)_data」を選択して、「参照マスタ確定」ボタンをクリックします。

ポイント

・参照のみですので、「データ表作成」チェックを外します。

・縦方向で複数行データを表示するため、縦ボックスに重複行数を入力します。

・値のアドレスを正しく編集しましょう。今回は、そのままで作成しますが、後程、まずい点をＤＢツールで修正します。

上記の図のように、「データ表作成」チェックを外して、「縦」のテキストボックスに数字「32」を入力します。「表作成」ボタンをクリック、参照用一覧表を作成します。

上記のメッセージの通り、以前入力したデータが削除されるので、必要があれば、ＤＢツールでバックアップを取ってください。

ステップ　⑤

－一覧表示用表の修正

ステップ④で作成した一覧について、下記の点が修正が必要。

・部門が一個しか表示できないため、修正が必要。

・重複しない項目を修正が必要。

ポイント

AOTORIリボンはSqLite(データベース)を使ってます。表定義を修正するため、ＤＢツールで表定義を修正します。

ステップ④で定義した表は、下記の図のように確認できます。

※ＤＢツールについて、フリーソフト「PupSQLite」をお勧めします。

下記のように修正します。

注意点

・カラム「id」は、「....data」表からデータ取得するためのキー項目ですので、修正できません。アドレス(coladd)、重複(colloop)の修正となります。

完成したDBファイル、Excelファイル

下記のリンクからダウンロードできます。

会計_2018.db

会計_2018.xlsx

※AotoriForExcel本体は下記のリンクから最新版を利用してください。

AotoriForExcel_installer.zip