町の店屋さんが使える  「レジもどきの売上管理」

匠への技

 説明の前にお詫びがあります。
ExcelVBAを使わない手法で公開することをお約束していたのですが、2回目にして鉄則を破ってしまいました。
どうしてもExcelの基本機能、関数だけではチト面白みが無くVBAでガリっとやっちゃいました。
ちなみに、関数を中心としている趣旨からVBAは公開しない方が・・・・・・・・と思い保護しました。が数件の強力なリクエストに根負けし「VBAあらかると」に掲載しました。VBAはそちらを参考下さい。

− 町の店屋さんが使える 「レジもどきの売上管理」とは −

 第2弾は、9種類の関数を使っています。
この作品の基礎は、Excel95デビュー(VBA Editorがリリースされた頃と記憶)当時に知り合いの商店主さんから依頼を受けて作った作品を思い出しながらアレンジしました。
一風「レジ」の感じのするブック。データの付加価値を考えると時間や平均といった付帯する項目がありますが省略させていただきました。分析用としては、その日の売上が商品分類毎に集計される仕組みです。また、商品分類毎に過去2営業日分の集計を可能とし売上推移や当日の売上状況などをシートからリアルタイムに判断することができます。レジをしながら売上状況を手に取るようにわかる仕組みはやはりExcel機能の組み合わせならではと思います。

基本画面とアウトライン
 タイトル「WebMambow商店のパソコンレジスター」です。ブックの中には2つのシートで構成されています。日次的に使う「レジ」とデータ累積を管理する「DB」です。
取引日、セルB7は西暦4桁、C7は月、D7は日です。運用は月中であれば日のみの入力です。年、月、日は、売上日の年月日が変わる都度入力です。商品分類名は、コンボボックス機能を活用しています。商品テーブルを範囲とした商品名のリスト選択です。1日の入力作業は、開始準備前として売上日の入力と売上時の商品分類名のリスト選択と金額の入力が基本です。
1回のレジでの明細数の限度は30としました。サンプルの表では尻が切れていますがセル行44が最終です。
売上データ入力は、売上入力欄から行います。レシート明細へは直接入力を行いません。マクロの力 「Push」ボタンによって売上入力欄からレシート欄へ売上データが自動転記されるようになっています。
入力中にお客さんから「財布を忘れたのでまた来ます。」のような事態を想定して「Cancel」ボタン(データ入力前に戻す)も用意。
カウンターに並べられた商品の入力をすべて終えたら代金を徴求し「Regi」ボタンでデータ累積シート「DB」へ自動転記。
シート「DB」のデータがベースとなり、本日の売上、前日、前々日、只今の売上累積といった集計をリアルタイムに評価することができます。

尚、マクロ実行は動きを確認していただくためにメッセージを促して進行するように作っています。

<画面デザイン>
 ●シート「レジ」


 ●シート「DB」



     ※後続の章「シナリオに基づく関数の説明」への戻り先です。

        シナリオ1   シナリオ2   シナリオ3   シナリオ4   シナリオ5   シナリオ6  



 使用する関数の種類    この色は新たに用いた関数です。
  1. IF(論理式,真の条件,偽の条件)
  2. VLOOKUP(検索値,範囲,列番号,検索の型)
  3. OR(論理式1,論理式2,...)
  4. ROUND(数値,桁数)
  5. INDIRECT(参照文字列,参照形式)
  6. SUM(数値1,数値2,...)   → 特に※配列数式に注視
  7. SUMIF(範囲,検索条件,合計範囲)
  8. DATE(年,月,日)
  9. COUNTA(値1,値2,...)

※配列数式とは?    (当日、前日、前々日の売上を求めるときに定義しています。)
1つまたは複数の値セットに基づいて複数の計算を実行し、1つまたは複数の結果を返す数式。配列式は中括弧{}で囲まれ、Ctrl + Shift キーを押しながら Enter キー押下により配列数式が定義されます。

ということを頭のどこかに入れておいて次に進みましょう。


 表作成のシナリオ
  1. 商品分類は、リストボックスからテーブル参照
  2. 本日の来客数をカウント
  3. 1取引の小計、消費税、合計を求める
  4. レシート欄は売上入力欄から「push」ボタンで自動転記
  5. 本日の売上状況、前日の売上状況、前々日の売上状況を表示
  6. 累積での売上状況を表示


 シナリオに基づく関数解説
■ シナリオ 「1」                   》 基本画面とアウトラインへJUMP

@コンボボックスの機能で商品テーブル(I13〜J26の範囲)から商品分類名を抽出する。
 コンボボックスは、選択した商品分類名と一致する相対番号を返します。その代入は、セルH7(「Regi」ボタンの陰にあります。)にセットされるように仕組んでいます。 ※コンボボックスの詳細設定は ここをクリック
コンボボックスから選択する商品分類名は、相対番号(レジキーと一致)のみ取得されます。コンボボックスに表示される商品分類名はどこにも引用されません。そこでコンボボックスに隠されているセルE7にはH7の値を検索値としてVLOOKUP関数を定義しています。セルE7の定義は以下の通りです。
  =IF(H7<>"",VLOOKUP(H7,I14:J26,2),"")  IF関数VLOOKUP関数の組み合わせです。コンボボックスで選択した商品分類名の相対位置H7が空欄でなかったらVLOOKUP関数を実行し、空欄だったらNULL(ヌル)を代入します。ここでの論理式 
H7<>"" はコンボボックスから商品分類名を選択しているか否かの確認チェックを行っています。VLOOKUP関数は、コンボボックスで選択された商品分類名のセル位置に該当する値 H7 を検索値として、商品テーブルのレジキー範囲 I14:J26 から該当する位置の ,2 レジキーの次の項目、2番目の商品分類名を引用しセルE7へ代入します。


■ シナリオ 「2」                   》 基本画面とアウトラインへJUMP
A本日の来客者数を求める。
 ここは、マクロの力を借りています。「Regi」ボタンで+1カウントアップさせています。但し、日付を変更したときの判断を吟味していませんので次の日に切り替える時はこの本日件数を0入力する必要があります。


■ シナリオ 「3」                   》 基本画面とアウトラインへJUMP
Bレシートの小計を求める。
セルE11の関数式から見てみましょう。
  =IF(OR(SUM(G15:G44)=0),"",SUM(G15:G44))  レシートの金額計が0の時、空欄にします。その要件を上に示す関数式で定義しています。IF関数OR関数SUM関数の組み合わせです。
OR関数とは、いずれかの引数が TRUE のとき、TRUE を返します。引数がすべて FALSE である場合は、FALSE を返します。 つまり、SUM(G15:G44)の集計値=0ゼロかどうかを判定し=0だったら真の条件に定義する "" を代入、=0 でない時はSUM(G15:G44)レシート欄の金額の集計値を代入します。明細がない場合は常に空欄となります。このようにOR関数は、SUMの計算結果、0を抑止する時によく使います。


C消費税を求める。   消費税セルF11の関数式です。
小計のセルE11が0以外の時に消費税5%を計算し、0の時は空欄とします。小数点の丸めは四捨五入で定義です。
  =IF(E11<>"",ROUND(E11*5%,0),"")   論理式 E11<>"" 小計セルE11がNULLでない時にROUND(E11*5%,0)小計に5%を乗じ円未満四捨五入の計算を行います。以外は "" NULLを代入します。

D小計+消費税で合計を求める。   消費税セルG11の関数式です。
小計セルE11と消費税セルF11の加算式です。小計、消費税同様に0の時は空欄にすることが条件です。ここでよく見ていただきたいのはOR関数の複合条件です。

  =IF(OR(E11="",F11=""),"",E11+F11) 小計でのOR関数の説明で理解されたと思いますがここでのOR関数は小計と消費税E11="",F11=""の2つの条件で判断しています。小計セルも消費税セルもNULLだったら合計セルG11へNULLを代入、どちらかが""以外の時,E11+F11の計算値が代入されます。


■ シナリオ 「4」                   》 基本画面とアウトラインへJUMP
Eカウンターにある1人分の商品の入力作業を完了したら、合計金額を徴求し次の人のレジの用意をしなければなりません。それが「Regi」ボタンです。
シナリオ2と同様にマクロの力を借りています。この機能は、レシートにある明細をシートDBへ転記させ、次の人の準備としてレシート明細の初期化を行っています。また、本日の来客数へ+1カウントアップしています。


■ シナリオ 「5」                   》 基本画面とアウトラインへJUMP
F商品分類毎に本日の売上状況、前日の売上状況、前々日の売上状況を求める。
VOL-2の要チェック箇所の到来です。まず当日の売上日配品セルK15の関数式を見てみます。
  {=SUM((INDIRECT("DB!$B$2:$B$"&COUNTA(DB!$A:$A))=DATE($B$7,$C$7,$D$7))*
       (INDIRECT("DB!$D$2:$D$"&COUNTA(DB!$A:$A))=J15)*INDIRECT("DB!$E$2:$E$"&COUNTA(DB!$A:$A)))}


チョット、ちょっと..... 誤字がありますよ!{} * 何ですか?それと複雑にすればいいと言った問題じゃないでしょ!
とおっしゃらず、VOL-2の要チェック箇所の1つですから。

Excelでは配列数式を定義した表記を{}で表します。乗算ではなくAND条件です。特徴としてシート「DB」の最終行判断をINDIRECT関数COUNTA関数を組み合わせて求めています。ここは要チェックと思います。最終行を判断せずにExcelの行範囲の限界値65,536で定義してもかまいません。しかし、無駄な行を指定すると再計算に時間がかかり非効率のシートができあがります。(本人は面倒さから以外と65536で定義している。あしからず...)
関数式を分解して説明します。
配列数式{=SUMは()内の合計範囲の集計なので省略します。その合計範囲の条件として1つめが日付の絞り込みです。 (INDIRECT("DB!$B$2:$B$"&COUNTA(DB!$A:$A))=DATE($B$7,$C$7,$D$7)) セルB7,C7,D7は営業当日の日付です。
シートDBのデータリスト内からその日付にフィックスするデータをまず抽出する条件がこの式です。(INDIRECT("DB!$B$2:$B$"&COUNTA(DB!$A:$A))のうち、  COUNTA(DB!$A:$A) はA列(伝票番号)の空白行を除くセル行の数を得ます。ようするにシートDBのデータの最終行の取得です。  "DB!$B$2:$B$"&COUNTA(DB!$A:$A) シートDBの絶対セルB2からB列のCOUNTAで求められた最終行を範囲として =DATE($B$7,$C$7,$D$7) の当日日付の配列範囲がDATE関数で定義され、指定する営業当日の日付と一致するセル行が求められます。要するに合計対象となる行が求められたことになります。
次に *(INDIRECT("DB!$D$2:$D$"&COUNTA(DB!$A:$A))=J15) は、AND条件 * で前述の日付で絞り込んだセル行の商品分類名が一致する条件を検索します。 INDIRECT("DB!$D$2:$D$"&COUNTA(DB!$A:$A) は、シートDBのD列の商品分類名を判断する範囲です。日付と同様なので省略します。 =J15 はシート「レジ」の商品テーブルの商品分類名で集計する基準セル「日配品」を指しています。
サンプルシートの日付が2002/1/3です。条件を文書にすると、シート「DB」のリストから日付が「2002/1/3」の取引で商品分類名「日配品」を検索し、最後のAND条件となる *INDIRECT("DB!$E$2:$E$"&COUNTA(DB!$A:$A)) シート「DB」のE列の該当する行の金額を集計しなさい。といった関数式です。 超ハイテクですよね。WebMambowもビックリです。


次に前日分を求める場合ですが、次の関数書式はセルL15に記入されているものです。

=SUM((INDIRECT("DB!$b$2:$b$"&COUNTA(DB!$A:$A))=(DATE($B$7,$C$7,$D$7))-1)*
(INDIRECT("DB!$D$2:$D$"&COUNTA(DB!$A:$A))=J15)*INDIRECT("DB!$e$2:$e$"&COUNTA(DB!$A:$A)))


ポイントは、 (DATE($B$7,$C$7,$D$7))-1) 日付から-1日を減算した値、1日前と判定しています。セルM15の前々日集計は-2日を減算した値です。日配品の行15で定義した関数書式をK15,L15,M15からK26,L26,M26まで複写して完成です。

前日、前々日の定義は、DATE関数以外当日と同じ書式になりますので説明を省略します。


■ シナリオ 「6」                   》 基本画面とアウトラインへJUMP
G今までの売上状況を求める。
まず、売上累計の求めるセルN15の関数式から見てみます。
=SUMIF(INDIRECT("DB!$D$2:$D$"&COUNTA(DB!$A:$A)),J15,INDIRECT("DB!$E$2:$E$"&COUNTA(DB!$A:$A)))
内容は、本日、前日、前々日の商品分類毎集計と変わりはありません。
但し、SUM関数での条件抜き出しではなく、SUMIF関数を使っています。ほか考え方は、シナリオ5と同じですので省略します。

以上がブックの説明です。

 もひとつ おまけ
セルk13に本日の日付を表示させる関数を定義しています。

  =TEXT(DATE(B7,C7,D7),"mm/dd")

 DATE関数で求めた日付を月/日の表示形式で表示させる関数です。TEXT関数はいろいろな局面での用途が多いですので覚えて損のない関数です。


○「匠への技」VOL-2で作成したExcelブックのダウンロード

  演習できるExcelブックを用意しています。下図のダウンロードボタンをクリックして下さい。

     Excel Book Name: takumivol-002.xls  Download file: takumivol-002.lhz