Excel.sys

今回は「Excelデータ抽出」について。
前に行った「Excelデータ取得」と同じようだが、この「・・抽出」の方は同じブック内にあるデータについて扱う。

これまでの項目式作成では、元データの項目を選択してきたが元データにない項目も作成できるのでそれを操作してみる。

まずデータをシート（825演習）のセルB16（項目名）から入力した。


「Excelデータ取得」ボタンのダイアログで元データ範囲を指定してから（項目式作成）をクリックして作成画面を表示する。

先に作成画面の入力内容をみてもらうと下図の通り。

左の元データの項目を右に選択するのだが、移送前のカーソル位置で元データに存在しない項目目を入力することができる。ここでは（社員コードと商品コード）という項目名を３行目に入力した。

既に（社員コードと商品コード）に対する式（=B17&C17）が図の下に見えているが、これはB列とC列をつなげたものをこの項目の値とするという指定だ。

順番としては先に図左下の項目名のチェックボックスをチェックすることで入力できるようになる。このように元データにはない項目を作ることができる。

シート名（%SHEET%）、ファイル名（％FILE%）、セル参照（％CELL=セル%）も参照できるように変数が用意されている。

他にＶＬＯＯＫＵＰなどの関数を使うことができ、別に用意したマスタテーブルから値を参照し表示できる。
ただし、この部分は直接元データの開始位置（見出し行を除く）を入力することになるので、今回のようにセルB16から始めず、A1などわかりやすいセル位置とした方がよい。

例えば、元データを利用して社員マスタを参照し、社員名と所属名を表示させるときの式は以下の通り。
（関数名の大文字・小文字は関係ない） 

ここでは、社員マスタについて（社員マスタ）と名前を定義している。（ややこしいが）
（社員マスタのイメージ）
 

出力項目は元データから選択した社員コードと、項目作成した社員名、所属名の３項目とした。
その出力結果が次の通り。
 
元データからそのまま抽出したので全件（内容が同じ行）が表示されている。

ここで関数式に戻ると、社員名の式は等号（=）が１つで、所属名の式は等号が２つある。
この違いは、等号が１つの場合は入力した式が出力セルにも設定され、２つの場合は結果の値のみ表示される違いだ。

行数が多いと関数でシートが重くなることもあるから基本は２つの等号で値の表示とする方がよいと思う。
因みに等号を入力するときは、先頭に半角でアッパーカンマ（'）を付ける。

式を表示する形式で上の出力結果を再表示すると以下のようになる。
（参照する行が自動的に展開された式が作られている）

社員名は式が設定されているが、所属名には値が入っている。

以上。

データ取得の次は、データ更新を行ってみる。

「StiLL」のボタンテンプレートから左上のボタンを貼り付け、そのボタンをクリックして「Excelデータ更新」を選択すると、設定ダイアログが表示された。

ヘルプによると、更新対象位置の項目名と入力レコード位置で指定したキー列の項目名（最大４つまでＯＫ）で同じレコードがあれば更新、なければ追加の処理となる。

ここで簡単な伝票番号のインクリメント処理を作ってみたい。
下図のように、シート上にＩＤ、伝票Ｎｏ、枝番、updateの４項目のデータを準備した。
ＩＤは１固定とし、元データと更新用データをそれぞれ用意。
更新用データで元データを更新する。
  
図に表示されていないが、セルＪ８には関数（TODAY）が入力されている。
更新用データにチョット数式と関数を使って、同日更新F（updateの変化）をみて、日付が変わったときは伝票Ｎｏをインクリメントし、同じ日付の場合は枝番をインクリメントする動きにしてみた。なお伝票Ｎｏをインクリメントするときは枝番を初期化する。（上図説明参照）

肝心のボタンの設定は次の通り。極めて簡素だ。


「Excelデータ更新」ボタンをクリックして実行すると、最初は日付が違うので、元データの伝票Ｎｏがインクリメント（＋１）された。
 
またupdateも置き換えられ、同日更新Fの値は１になった。（更新用データの伝票Ｎｏは変わらず）

もう一度「Excelデータ更新」ボタンをクリックすると。
今度は枝番がインクリメントされた。

以降、クリックする度、枝番が＋１されていく。
たぶん、明日になれば伝票Ｎｏが＋１され、２となるだろう。

今回はExcelの要素が多くみえたが、このような更新処理はExcelのみではできない。
もちろんＶＢＡでプログラムを作りボタンに関連付けすることはできるが、こんなに簡素にはいかない。簡素な分、逆にExcelでの設定部分が目立ってしまったのかもしれない。


続く。

きのうの続き。
出力項目の選択で、並べかえを入力した状態です。
ここでは単純に支店名と伝票NOを入れ換えてみた。

なおデータソートの順番ではないので注意のこと。
またそのときのボタンのダイアログも示します。
  

次に、条件式を使ってみる。
（条件式の作成）をクリックすると下図（イメージ）のように左辺にデータの項目名を指定し、比較演算子に続けて条件値を入力する仕組みだ。
 

ここで式の右端には選択されている項目名の型名が表示される。
また比較演算子のLikeとNot Likeは項目の型名が文字の場合のみ表示され選択可能。
ANDかORを選択した場合は次行の入力欄が表示され最大５つの条件式までを設定できる。

更に中央の（→）をクリックすると選択した項目の実データが集約され、選択肢として（→）横のプルダウンリストにセットされるので便利。

下図は支店名が集約されたプルダウンリストのイメージ


支店名にA支店を選択してＯＫボタンで設定し、ボタンを実行するとＡ支店が並べかえた順番で表示された。


続く。

本日は夏休み明けの台風襲来だ。
早めに家を出たら湘南新宿ラインはほゞ定刻通りに運行されていたので遅れることなく早く会社に着いた。

今日は、「DBデータ取得」のボタンを使ってみる。
これまで同様「StiLL」のボタンテンプレートから左上のボタンを貼り付け、そのボタンをクリックして「DBデータ取得」を選択すると、最初にデータ ソースの選択ダイアログが表示された。

このボタン処理では、ＯＤＢＣ定義（登録）されたデータベース（ソース）を利用するので、先にＯＤＢＣの設定を行うが・・・、
その前に、実はデータベースをまだ用意してなかったので、Accessにテーブルを作成する。

作成するテーブルの内容は、これまで利用してきた「月別集計表1.xlsx」の（data）シートをそのままアクセスのテーブルとして取り込む。
（この処理についてはAccessの機能である外部データ取り込みを使って作成）
データベース名はTestDatabase.accdb、テーブル名はTestdataとした。

（取り込んだテーブルのイメージ）
 
これをMicrosoft Access Driverを用いDＳNを設定する。DＳNの名前はK_TestdataDBとした。
この部分の手順については、「DBデータ取得」ボタンダイアログの（？）マークをクリックするとヘルプが表示され、その中の（注意）にAccessでのＯＤＢＣの設定方法が詳しく示されているので省略。

以上の準備を行い、データソースの選択画面は以下の通り。
 

ＯＫボタンで「DBデータ取得」ボタンの設定画面が表示されたので、下図のように入力した。


出力範囲名（DATA1）を入力すると、データが表示された領域（ここではセルD9基準）に名前が定義されるので、その後の処理でこのデータを加工する場合などにセルで範囲指定をせずとも可変領域として名前が扱えるので大変便利だ。

完成した「DBデータ取得」ボタンを実行すると、指定したセルD9からAccessのテーブル内容が表示された。

因みに出力項目は、初期状態のすべてとしたが項目選択で出力する並び順をかえることも可能だ。


続く。

今度は検索式（範囲）の設定。
文字通り出力するデータの条件を入力（指定）するところ。
ここは、先に項目式が作成されていないと（検索式作成）ボタンをクリックすることができない。

範囲入力より先に（検索式作成）ボタンを開き、項目式作成と同じ要領で条件付けを行う項目名を選択する。

（支店名）を選択して右矢印で< 検索式 >に移送すると、ダイアログ下段に選択した項目名と、その下に条件を入力する枠が表示される。
下図参考。
 

条件式には等号・不等号などの演算子やワイルドカード文字（＊）を使用することができる。
ここでは下図のように支店名に「Ｅ支店」と入力してＯＫボタンをクリックする。
 

すると、次の「条件の出力位置（検索式範囲）」の入力画面が表示された。
 

ここに条件付けした項目数（今回はひとつ）を確認してシート上の適切なセル位置を選択し設定すると、指定したセル位置に条件式が展開・表示された。

これで設定を終了し、先の表示されているデータをクリアしてボタンを実行すると、「Ｅ支店」のデータのみが表示された。

夏休み明けに続く。