OPTION 4
▶1：クロス集計で年間合計表を作成
ここでは売上の年間集計をクロス集計を使用して作成します。クロス集計はエクセルのピボットテーブルと同様の考えのものです。
▶2：クロス集計で年間合計表の作成手順
▶3：完成
❙ 月別の売上集計 完成図
▶4：手順１
❙ 集計画面をエクセルで作成
【１】新しいシートを追加し集計画面を作成します。
※ここでは下図のようにします。
【2】赤枠の範囲を印刷範囲として設定します。
【3】「シート名」を付けます。※ここでは「SUM」とします。
【4】ブラウザで表示するための設定をします。画面の上で右クリックし
「XCuteナビゲーション」を選択、次に「ひな型ナビ」をクリックします。
【5】「OK」をクリックします。
【6】次のメッセージも「OK」をクリックします。
【7】ブラウザでデータをやり取りするために必要なFORMタグを設定
します。画面の上で右クリックし「Xcuteナビゲーション」をクリック「ひな型ナビ」
をクリックします。
【8】「OK」をクリックします。
【9】「OK」をクリックします。
【10】「OK」をクリックします。
XCuteは、ExcelのHTML変換を利用しているため、Excelで作成した画面に入力フィールドやボタンなどの機能を追加するための「タグ差込」が必要です。自動作成した画面には、この「タグ差込」も自動的に設定されていますが、シート追加で作成した場合には、このタグ差込がないため設定が必要になります。「タグ差込」は、ひな型シートの印刷範囲の右上に「;htmltag」と「FORM」タグ、右下に「/FORM」タグが設定されます。ブラウザでデータをやり取りする時にはこれらのタグが必要となります。
▶5：手順2
❙ Accessのクエリでテーブルを作成
ここでは、基本操作で自動作成した「UriageDB.mdb」を使用します。作成中のアプリ「Uriage」は、閉じずに開いたままとします。
【１】データベース「UriageDB.mdb」を開きます。
既に作成済みのテーブル一覧が表示されます。
※ここで使用するAccessは、基本操作で作成したフォルダにあります。
【2】グラフ用のクエリを作成します。「作成」タブをクリックし
「クエリデザイン」ボタンをクリックするとクエリ作成画面が表示されます。
【3】右側の「テーブルの追加」ウインドウからテーブルをダブルクリック
します。選択したテーブルが左側のクエリエリアに表示されます。
※ここでは「Uriage」をダブルクリックします。
【4】クエリエリアにあるテーブルからグラフ作成に必要なフィールドを
選択します。必要なフィールドをダブルクリックで、下に追加されます。
※ここでは「日付」・「得意先」・「金額」・「消費税」を選択します。
【5】得意先をグループ化し、金額と消費税を合計します。
「クエリデザイン」タブをクリック→「集計」ボタンをクリックします。
【6】下の「集計」を赤枠の内容に変更します。
・日付 ：グループ化
・得意先：グループ化
・金額 ：グループ化
・消費税：合計
※ここでは日付、得意先ごとに金額と消費税を合計する設定にします。
※「集計」ボタンを選択すると下の集計に集計する種類が選択できるプルダウンが表示される
【7】日付フィールドを「年」と「月」に分けます。「FORMAT」関数で
「年」をMID関数で「月」を設定します。図の赤枠に修正します。
ここでは日付を年と月に分け、年ごとの月売上画面を作成するようにしています。そのためクエリで、日付を年と月に分けたフィールドを作成します。
FORMAT関数
「フィールドの表示名」:FORMAT([フィールド名],形式）
年:Format([日付],”YYYY”)
MID関数
「フィールドの表示名」:MID([日付],開始位置,桁数)
月:MID([日付],6,2)
※日付は、YYYY/MM/DDの10桁になります。
月を求めるには開始位置を6桁目からとなりますので、「６」とし必要な桁数が2桁のため「2」とします。
【8】クエリに名前を付け保存します。クエリエリアの右上にある「✖」を
クリックし、表示されたメッセージの「はい」をクリックします
【9】名前を付けて保存ウィンドウの「クエリ名」に名前を入力し、
「OK」をクリックします。※ここでは「Q_SUM」としています。
【10】クエリの作成が終了しました。Accessを閉じてください。
▶6：手順3
❙ 集計画面用のレポートとテーブルを作成
【１】集計画面用のレポートとテーブルを作成しましょう。
パネルの赤枠あたりで右クリックします
【2】「レポート新規登録」をクリックします。
【3】新規レポート名に「レポート名」を入力し「新規登録」をクリック
します。※ここでは「SUM」とします。
【4】レポートが追加されます
【5】レポートに「テーブル」を追加します。
追加したレポートを右クリックし「テーブル追加」を選択します。
【6】「レポートへテーブルを追加」ウィンドウが開きます。
データベースの種類から「Access(*mdb)」をダブルクリックします。
【7】ファイル選択ウィンドウが開きます。データベースをダブルクリック
します。※ここでは「UriageDB.mdb」をダブルクリックします。
【8】テーブルの選択ウィンドウが開きます。テーブル名を選択し、
ダブルクリックします。※ここでは「Q_Sum」をダブルクリックします。
【9】次にひな型シート「集計画面」と作成したテーブルのリンク設定を
します。リンクする範囲を選択します。
※ここではセルR13C3:R13C18まで選択します。
【10】テーブル「Q_SUM」を選択し、右クリックします。
【11】ひな型の設定ウインドウが開きます。図の赤枠に設定します。
※ここではひな型シートを「SUM」・「全行自動選択」を選択、読書範
「R13C3:R13C18」とします。
【12】「OK」をクリックします。
▶7：手順4
❙ テーブルにクロス集計のリンク設定
【１】最初に基本操作でも設定した通常のリンク設定を「年」と「得意先」
に行います。「年」のリンクを設定し、セルR13C3を選択します。
【2】パネスの「年」のセル位置を右クリックします。
表示された中から「セル位置(R13C3)」を選択します。
【3】次に、得意先のリンク設定をします。セルR13C5を選択します。
【4】パネスの「得意先」のセル位置を右クリックし、表示された中から
「セル位置(R13C5)」を選択します。
【5】次に、クロス集計の見出し部分になる「月」に「Cross」のリンク
設定をします。集計画面の見出し「月」になる箇所の「01」を選択します。
※ここではセルR12C7を選択します
【6】パネスの「月」のセル位置を右クリックし、表示された中から
「セル位置(R12C7)」を選択します。
【7】「OK」をクリ九します。
【8】リンクが「Cross」となります。これでクロス集計の見出し
部分の設定が終了しました。
【9】次にクロス集計の値部分になる「合計」に「C_Val」のリンク設定を
します。合計を書出すセル「R13C7」を選択します。
【10】パネスの「金額の合計」のセル位置を右クリックし、表示された
中から「セル位置(R13C7)」を選択します。
【11】金額の合計のリンクのプルダウンから「C_Val」を選択します。
【12】クロス集計はデータを集計させるため、データの書出しに順位の
設定が必要です。ソートのプルダウンから「昇順」を選択し、得意先＝昇順1,年＝昇順2、
月＝昇順３とします。これでクロス集計のリンク設定が終了しました。
▶8：手順5
❙ 「年」を指定してデータを書出すボタンを作成
集計画面で集計させる「年」を指定できるようにします。
最初に「年」を入力するエリアを作成し、そのあとでデータを書き出すボタンを作成していきます。
【１】年を入力するせるを右クリックします。※ここではセルR11C5を
右クリックします。次にXcuteナビゲーションをクリック→入力エリア(文字)をクリックします。
【2】表示されたメッセージの「OK」をクリックします。
【3】データの入力規則ウィンドウが開きます。
4桁以上入力できないよう、下記に修正します。
・入力値の種類：文字列（長さ指定）
・データ ：次の値に等しい
・長さ ：４
値を修正後、OKをクリックします。
【4】データを書出すボタンを作成します。
赤枠の「集計」を右クリックします。
【5】「XCuteナビゲーション」をクリックし、
次に 「送信ボタン（WriteReport)」 をクリックします。
【6】「OK」をクリックします。
【7】次に入力した「年」のみ書出すようFILTER設定します。
セルR2C8を右クリックします。
【8】「XCuteナビゲーション」をクリックし、「FILTERfieldname=」
をクリックします。
【9】「OK」をクリックします。
【10】セルR2C8の設定内容を赤字に修正します。
書出しボタンとフィルタの設定が終了しました。
=IF(R11C5<>“”,“FILTER年=" & R11C5,"GONEXT")
FILTERは、条件に合ったデータに絞ることができます。IF関数などにより、指定セル内容と条件が一致した場合は、データを読み書きし、一致しない場合はGONEXTにより右隣りのセルに移動します。2行目のセルR2C8以降にFILTER設定がある場合は、空白のセルになるまで順次その設定が実行されます。
▶9：手順6
❙ 前画面へ戻る・ボタンを作成
【１】 前の画面へ戻る・ボタンを作成します。
赤枠の「前画面へ」を右クリックします。
【2】「XCuteナビゲーション」をクリックし、次に「リンク・ボタン」を
クリックします。
【3】「OK」をクリックします。
【4】ボタンの動作設定をします。
開発エリアに設定されたセルをクリックし、赤字に修正します。
="|TYPE=BUTTON|ONCLICK=location.href='procgi.exe?P="&R1C5&"&WriteReport=UriageList';"
▶10：手順7
❙ 検索画面に月別集計ボタンを作成
【１】検索画面に月別集計ボタンを作成します。
図を参考にリンクボタンを作成しましょう
【2】作成したボタンの動作設定を修正します。
CENTER:="|TYPE=BUTTON|Style=|ONCLICK=location.href='procgi.exe?P="&R1C5&"&WriteSheet=SUM’;”
これでクロス集計が完成したので確認してみましょう。検索画面の上で右クリックし「XCuteナビゲーション」→「EXCELシートをブラウザで開く」の順でクリックします。
▶11：手順8
❙ 最初から今年の売上集計データを書出す
検索画面の月別集計ボタンをクリックした時に、今年の年を入力せず最初から今年の集計データを書出すようにもできます。ここでは「TODAY関数」・「TEXT関数」参照を組み合わせています。
【１】本日を関数で求めます。セルを選択し＝TODAY()と入力します。
※ここではセルR5C5に入力します。
【2】本日から年だけ抽出します。
セルを選択し＝TEXT(R5C5,”YYYY”)と入力します。
※ここではセルR11C5に入力します。
※これで最初から今年の「年」が表示されます。
これでクロス集計が完成したので確認してみましょう。
検索画面の上で右クリック「XCuteナビゲーション」→「EXCELシートをブラウザで開く」の順でクリックします。
▶12：その他
❙ 条件付き書式で当月を色分けする
クラウドシートでは、エクセルの機能を使用できます。ここでは条件付き書式を使用し、当月を色分けしています。
【１】TODAY関数で求めた本日から月を求めます。
セルを選択し＝TEXT(R5C5,”MM”)と入力します。
※月だけが表示されます。
【2】色分けする範囲を選択します。※ここではセルR13C7:R13C18
【3】「ホーム」タブ→「条件付き書式」→「新しいルール」の順に
クリックします。図の「書式ルールの変種」ウィンドウが開くので赤枠のように設定します。
【4】書式ボタンをクリックします。
【5】セルの書式設定ウィンドウが開くので「塗りつぶし」タブから
背景色を選択し「OK」をクリックします。
【6】書式ルールの編集ウィンドウに戻りますので「OK」を
クリックします。
【7】条件付き書式ルールの管理ウィンドウが開きます。
「OK」をクリックします。