コンビニのレシートに入っている情報

コンビニのレジには年齢・性別ボタンがあるらしい(2)-ExcelでDBっぽくデータを扱おうコンビニのレシートに入っている情報をExcelの表にしてみました。データは例ですが、一枚のレシートでもいろいろな情報が入っていますね。念のためExcelの教材シート” A2元情報DB”で確認しておきましょう。

これがコンビニのレシートのデータベース
だって、何かすごいけど。

簡単なのか複雑なのか、わからないね。

121219192929393949

49

50

50

さいきん、データベースという言葉をよく聞くと思います。今回の授業ではコンビニのデータベースをみていきましょう。

シート:
A2元情報DB
Ver. 1.0 (2014/05/24) © Go Ota, 2014

1

主キー/レコード
主キー レコード

どうしてデータベースを使うのレコード:RDBの各テーブルの横の1行のデータをそれぞれ、レコードと読んでいます。
主キー:1つのテーブルの中の各レコードは必ず違うものを設定します(全く同じデータの複数のレコードは設定しない)。この時、各レコードを確実に区別するものを主キーと呼びます。一つの項目が主キーになる場合も、複数のキーが合わさって主キー(複合キー)になる場合があります。

データベースの利点
・データの検索や取り出しなどが簡単に速くできる。
・情報/データの重複が無く、また情報の誤りが少なくなる。
・情報/データの修正・変更などで楽である。
・プログラムとデータを別々に考えることができる。
・複数の人で同時に使用できる。

データベースには一般的に上記のような特徴があります。少しずつ、その利点を確認していきましょう。
そしてデータベースには、いろいろな方式がありますが、授業では現在広く使用されているリレーショナルデータベース(RDB:Relational DB)という方法を利用します。
RDBは、右図ように、複数の表(テーブル)の情報を分けて、それを関係付けながらデータを管理、利用する方法です。
例えば、店舗情報にFAX番号を追加したい場合は、店舗情報の表だけを修正すればいいことになります。また、新しい大きな区分の情報が追加した場合は、新しい表を追加することで対応できます。

2

シート:N0_キー有

RDBのルールや用語など見ていきましょう。
レコードはExcelの1行のデータと同じと考えてください。
また下記の図の店舗情報では店舗名でも各レコードの区別をすることができ、主キーとなることができますが、通常主キーは検索や他のテーブルとのリンクに使われることが多く、処理の高速化などから、数値データを使うことも多く、ここでは店舗番号のデータを追加しています。
販売情報については、レシート番号は、毎日・店ごとに1番から始まることを想定していて、そのため、店舗番号・レシート番号・販売日で、区別がつくようになります。

レコード
シート:
N0_キー有主キー
大まかに分類して表(テーブル)に分けたデータ
シート:A3_単純分割 (これはRDBではなく、単なる三つの表)

レコード

34

主キーテーブルを整理する(1)テーブルを整理する
テーブルの関連付け

では、いよいよバラバラだった表をくっつけましょう。基本的に二つのテーブルに同じ列のデータを入れて、この二つのテーブルを関連付けます(リンクします。参照します)
この例では、店舗情報と販売情報が店舗番号でリンクされます。この場合、ある店舗に複数のレシートがあることになります。

1つのレコードの中で繰り返し出てくるデータの組み合わせを、複数のレコード又は別のテーブルにする

整理(1)
整理

購入した商品が増えると、どんどん増えていく
シート:
N1_商品繰り返し無リンク: 参照

シート:
N0_キー有5

RDBはできましたが、個々のテーブルを見ると、まだ改良の余地があります。
ここでは販売情報を見直しましょう。
このテーブルの一番の問題は、1つのレコードに商品の情報が複数個あります。そのため、この商品の領域をあらかじめいくつ用意しておけばいいかわかりません。100個同時に買う人がいるかもしれません。
テーブルの整理で初めにやることは、このように同じ種類のデータが複数個レコードにある場合、それを分けてレコードを作ることです。ここでは、単品売上情報として別テーブルにして、1つの商品を1つのレコードにしています。

6

テーブルを、もっと、もっと整理する(3)テーブルを、もっと、もっと整理するテーブルを、もっと整理する(2)テーブルを、もっと整理する
N1_単品売上情報

整理(3)
整理
整理(2)
整理

シート:
N3_性別コード

シート:N2_商品マスター

テーブルをもっと整理していきましょう。次の主キー(おもに複合主キー)に関連した情報に無駄がないか見ていきましょう。
現在の単品売上情報には各レコードの中に商品名と商品単価が入っていますが、これは商品番号が決まるもので、テーブルのなかに同じ”オニギリシャケ”などのデータが複数入っています。
また、これらは本来同じデータであるのに、別々のレコードに入っていて誤ってデータが設定されることも想定されます。
そこで、ここでは、商品番号から決まる商品名/商品単価を別テーブルにします。
7

いよいよ、最後の整理です。テーブルの中に、まだ無駄が無いか探してみます。
今回のテーブルでは性別については男女の文字だけでなく、1と2という数値で表しています(これは、検索や集計などの時に数値の方が処理しやすいという点からです)。この数値と男女の文字がダブっているので、性別コードのテーブルを作り、販売情報のテーブルには性別区分の数値だけを設定するようにしました。
8

完成したコンビニレシート用のRDB
完成したコンビニレシート用のコンビニレシート用のRDBの利用の利用
コンビニレシート用のコンビニのレシートのデータの使い方は次のようなものだっけ
1.各店舗での利用
各店舗での利用・日別/時間帯別売り上げ
・売れ筋商品・時間帯別売れ筋商品・客層/来客時間帯
・仕入発注
・返品計画

2.本社
本社/本店での利用
本社 本店での利用・売れ筋商品・客層/客層別売れ筋・店舗別売上
・店舗別売れ筋商品・商品組み合わせ・時期別売れ筋商品
・生産計画

コンビニのレシートの情報って何に使うんだっけ

店舗情報とキャンペーン情報のテーブルも元に戻して、これでコンビニレシート用にRDBの完成です。
次のこれを使って、いろいろデータの処理をする方法が見ていきましょう。

3.各店舗と本社各店舗と本社/本店とのや各店舗と本社 本店とのやりとりでの利用
・仕入情報
・配送情報
・返品情報
・売れ筋商品(お勧め)

これからExcelを使ってコンビニレシートRDBを操作して、必要な情報をとりだしましよう。

910

Excelでの操作
での操作:
での操作 射影

操作:
操作 射影=特定の列を取り出す
射影 特定の列を取り出す

列を削除する場合は、列を指定した後に右クリックしてから削除を選択します。

射影

シート:
1.射影.販売情報

射影はテーブルから特定の列だけを取り出す操作になります。
Excelで射影の操作をすることは比較的簡単です。もとの表から不必要な列を削除するか、別のシートに必要な列をコピーするだけです。
不要な列を削除する場合は、もとのシートを残すため、あらかじめシート全体を別のシートにコピーして、そのコピーしたシートから削除するようにしましょう。

データベースに対する操作には、「選択選択」
選択「射影射影」「結合射影 結合」の基本的なものがあります。
結合
例えば、販売情報にはいろいろなデータが入っていますが、売り上げだけに注目/計算したい場合は、支払い金額や合計点数だけを抽出してから処理すると、判りやすくなりますね。
11

シート全体をコピーする方法はa. Ctrl + A で全体を選択してからコピー
b. 表の左上をクリックして全体を選択してからコピー

12

Excelでの操作
での操作:
行の削除又はコピーでの操作 選択(1)選択

操作:
操作 選択=特定の条件に合うレコードを取り出す
選択 特定の条件に合うレコードを取り出す

選択は射影と同様にa)不必要な行を削除する。b) 必要な行をコピーすることで実現できます。

選択1220301を抽出

シート:
2.選択.販売情報S1

選択
5月19日で400円以上を抽出

シート:
2.選択.販売情報S2

例えば、特定の店舗の売り上げを計算する場合は、その店舗だけのレコードを取り出して計算する必要があります。
このような時、選択選択の操作をすることになります。
選択
選択はある列の特定の条件に合うレコードだけを取り出すことができます。また、複数の列の条件を指定して取り出すこともできます。

行の削除やコピーは、列に比べて操作するレコードの数が多くなります。そのため、上図のように「並び替え(ソート)」の機能を使って予めレコードをコピーする又は削除するものが連続して並ぶようにしておくと、操作が楽になります。

13

Excelでの操作
での操作:
フィルタ機能を使用するでの操作 選択(2-a)
選択

条件を指定する矢印が出てくる抽出するレコードの条件をチェックする14

Excelでの操作
での操作:
フィルタ機能を使用した後で計算するでの操作 選択(2-b)
選択

Excelで選択の操作をする、もう一つの方法はファイル機
能を使うことです。
フィルタ機能では、各行ごとに抽出する条件をチェックで指定することができます。慣れると、前のスライドの行を削除するなどより簡単ですね。

コピーしてから計算する

SUBTOTAL関数で関数で計算する。
さあ、大変。フィルタで選択した後、SUM関数などを使って計算すると表示されていないレコードも計算対象になってしまいます。そんなとき
a) 別の場所・シートにコピーしてから計算する
コピーするとフィルタで選択したものだけが残ります。
b) SUBTOTAL関数を使用する
SUBTOAL関数はフィルターで選択されたデータだけを対象に集計してくれます。

フィルタした結果指定した条件に合う行(レコード)だけが表示される: ファイルを解除すると、再度全部の行が表示される。
シート:
2.選択.販売情報S3

15

SUBTOTAL(集計方法、範囲)

16

Excelでの操作での操作:条件に一致したデータの取り出しでの操作 結合(a)結合

操作:
操作 結合=二つのテーブルを合成してデータを取り出す
結合 二つのテーブルを合成してデータを取り出す

結合は同一のデータ種類の列を持つ二つのテーブルを、その列の値を手かがりに合成して新しいデータを作成します。
シート:.選択.販売情報S3
表の中の1列目2列目ADBの場合、テーブルを整理する必要がありましたが、実際レポートなど作成する時に、例えば店舗番号だけだと良くわかないことがあります。このような時実際の店舗名が入っていると判りやすいですね。
結合の操作は、このような時、二つのテーブルを合成してデータを取り出すことができす。

B

15=VLOOKUP(A15,$G$15:$H$18,2,FALSE)

16=VLOOKUP(A16,$G$15:$H$18,2,FALSE)

17=VLOOKUP(A17,$G$15:$H$18,2,FALSE)

3列目

VLOOKUP関数は、範囲指定した表の一番左の値を比較して、一致したレコードの値を取り出すことができます。

1718

データベース管理システムとSQL
データベース管理システムと演習1-a
販売情報テーブルをもとに、射影の操作をして下記の項目のテーブ
演習
ルを作成する。

プログラム
演習1-b
単品売上情報から10101オニギリ シャケと10103オニギリコブ
演習

アプリケーションを選択したテーブルを作成する。作成は並べ替えをしてから行を削除する。

SQL (問い合わせ言語:

データベース管理システム(DBMS: Data
Base ManagementSystem)

データベース
RDB

Structured QueryLanguage)

演習1-c
単品売上情報と商品マスターを結合して下記の項目のテーブルを作演習成する。
データベースはコンピュータ上のデータの集まりですが、それを管理しているデータベース管理システム(DBMS)があります。
実際にプログラムでデータベースを使用する場合は、このDBMSを仲介してアクセスします。特にリレーショナルデータベースではSQLというデータベースを操作する問い合わせ言語があり、この言葉を使ってデータベース管理システムに命令を出します。
SQLはいろいろなシステムやメーカーで共通なので、異なるメーカーのデータベース管理システムを使っても、プログラ
ムでのSQLは同じになります。

演習 2-a 単品売上情報から10101オニギリ シャケと10103オニギリコブを選択したテーブルを作成する。作成はフィルタを使用し、SUBTOTAL関数で小計を計算しなさい。
演習 2-b 販売情報と展墓情報を結合・射影の操作をして下記の項目のテーブルを作成する。

演習 2-c 演習1-cのテーブルをもとに、行を削除してシャケと演習 のテーブルをもとに、行を削除して10101オニギリのテーブルをもとに、行を削除して10103オニギリコブを選択したテーブルを作成する。

1920

SQLとその結果とその結果
SQL
データベース管理システム(DBMS: Dataプログラム
アプリケーション結果
射影:
射影 SELECT 店舗番号, レシート番号,販売日, 合計支払い金額, 合計点数
FROM 販売情報;
選択:
選択 SELECT 店舗番号, レシート番号,販売日, 合計支払い金額, 合計点数
FROM 販売情報 WHERE 店舗番号 =1220301;
結合:SELECT
販売情報.店舗番号, 店
結合
舗情報.店舗名, 販売情報.[レシート番号],販売情報.販売日, 販売情報.合計支払い
金額, 販売情報.合計点数 FROM 販売
情報 INNER JOIN 店舗情報 ON 販売
情報.店舗番号 = 店舗情報.店舗番号;

Base Management
System)

データベース
RDB

授業ではデータベースの操作をExcelの手作業で行いましたが、例で示した射影、選択、結合の操作をSQLで表現すると左のようになります。
これをDBMSに要求するだけで、Excelで作ったようなデータが結果として得ることができます。
SQLを知れば簡単にデータベースを操作できるようになります。

21