Accessを使ったExcel表の作り方

Accessを使ってExcelの表を作っていきます

参考:Accessを使ってExcel表を作る必要性とは

概要

作成したい帳票を上の画像とします。
Accessから出力したデータは「元データ」シートに貼り付けて、「集計表」シートにvlookupで引用する方針になります。

以下が、イメージ図になります。

参考:データを引用する(VLOOKUP関数)

作り方

Accessで何のデータを出力しないといけないかを決める

まず最初のステップで考えることは「Accessで何のデータを作らないといけないのか」です。

コツはEXCELの数字が「何で括られているか」を考えてください。

上記の表の場合、「社員別」「商品別」「週別」という分類で、「売上数字」と「目標数字」が括られています。

社員の「所属支店」も項目にありますが、この場合、「所属支店」は社員に紐づく属性情報なので、「社員別」の中にあると見なします。

Accessで出力するデータは、これらの項目(社員別、商品別、週別)でグループ化した売上数字を出力する必要があるということになります。

Accessでクエリを組み、出力する

仮に第1週目のデータを下記とします。

トランザクションとして最低限あると思われるのは、誰が(社員)、どこに(お客様)、何を(商品)、いつ(年月日)、いくらで売ったのか(売上数字)は、あると考えられます。

これらのデータを先ほど想定した通り、社員別、商品別、週別でグループ化をして、売上の合計を持ってきます。

ここで、1つ注意点があるとすれば、年月日で週を決定することは出来ない点です。

欲しい数字は各週の終了時点での累計数字です。
当該週に上げた売上数字ではありません。

つまり、集計をする度にその時点での売上を全部足しこむ想定になります。
都度「週」の項目を書き換える運用ですね。

5週目であれば下記のようになります。

参考:クエリの名前の決め方のコツ(ルール3に該当します)

加えて1点、特別に気を付けなければいけないことがあります。

それは「ユニークコード」を作る必要があることです。
そのユニークコードを用いて、Excelでvlookupの引用に利用します。

1つ目のクエリで必要最低限のデータをグループ化して、2つ目のクエリでユニークコードを付与する流れです。

そうして出来上がったデータは下記になります。

Excelファイル上で連携できるようにする

Accessで作成したデータはExcelで下図のように貼り付けます。
「元データ」シートに貼り付けしていますね。

これを以下の関数で引用します。

赤枠に入る関数式
=VLOOKUP(A20&”P01″&”1週目”,元データ!$A:$E,5,FALSE)
※P01は商品コードを指します。

J列の2つ目の商品(緑枠)であれば
=VLOOKUP(A20&”P02″&”1週目”,元データ!$A:$E,5,FALSE)

佐藤さん(青枠)であれば
=VLOOKUP(A21&”P01″&”1週目”,元データ!$A:$E,5,FALSE)

2週目であれば、当然1週目の部分が2週目に変わります。
=VLOOKUP(A20&”P01″&”2週目”,元データ!$A:$E,5,FALSE)

このようにvlookup関数の検索値の値を少しずつ変えていくことで引用される値が適宜変わります。

この方法の良いところは、最初の1名分を作成したら、あとの人数分は関数をコピペすることで対応できる点です。

ここまで来れば、後は最後のひとひねりです。

それは、上画像のような「かっこ悪い表示」を何とかすることです。

これは、5週目まで全て数字を引用できていれば発生しませんが、1週目時点など月半ばでは引用できていない数字がエラーになっていまいます。

この場合、if関数とiserror関数、そして条件付き書式を利用します。

=IF(ISERROR(VLOOKUP(A20&”P01″&”2週目”,元データ!$A:$E,5,FALSE)),0,VLOOKUP(A20&”P01″&”2週目”,元データ!$A:$E,5,FALSE))

()の中身は、大半がさきほどの2週目の場合の式と同じですね。
分かりやすく簡素にすると以下です。

=IF(ISERROR(2週目)),0,2週目)
意味:2週目の式でエラーが出るなら0、エラーが出ないなら2週目の式を適用する

ここで「あれ?なんで0なの? 空白にすればすっきり綺麗でいいじゃないか」と思う方がいるかもしれません。
しかし、それでは何か別の処理をする際に問題が起こりやすいのです。

例えば、その後、社員別の順位を横に表示することになったとします。
その場合、上手くRank関数が取れません。

これが空白ではなく、0という数字が入力されていれば問題は解決します。
このフィールドは「数字のフィールドである」ということを示す意味でも0の入力はあった方が良いのです。

そして、0の数字を消すために、条件付き書式での設定を行います。

セルの数字が0ならば、フォントの色を白にする、という設定です。

これでAccessを用いた集計表の作成は完成です。

フォーマットを作成するのは、少し手が込んでいるかもしれませんが、日々作っていく作業はAccessから出力して「元データ」シートに貼るだけになります。

また、別軸での分析を求められる際の労力はかなり削減されることになります。

ぜひ日々の生産性を高める中で、ご活用ください。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です